include_once("../php/include-request.php"); verifyAuthorized(); $action = getMandatoryRequestValue("action"); $runRuleSql = " UPDATE record SET category_id = (SELECT category_id FROM rule WHERE id = ?) WHERE id IN ( SELECT record.id FROM record, account a, rule, category c1, category_group g1, category c2, category_group g2 WHERE record.label LIKE rule.regex AND rule.id = ? AND rule.category_id = c1.id AND c1.category_group_id = g1.id AND g1.expense = record.expense AND record.category_id = c2.id AND c2.category_group_id = g2.id AND g2.system = ? AND c1.id != c2.id AND (rule.account_id IS null OR rule.account_id = record.account_id) AND record.account_id = a.id AND rule.household_id = a.household_id AND rule.household_id = ? )" ; initDatabaseConnection(); if($action == "delete-rule") { $id = getMandatoryRequestValue("id"); $rowCount = dbUpdate("DELETE FROM rule WHERE id = ?", $id); if($rowCount) { requestOk("Deleted $rowCount rule"); } } else if($action == "save-rule") { $id = getOptionalRequestValue("id", null); $regex = getMandatoryRequestValue("regex"); $accountId = getOptionalRequestValue("accountId", null); $categoryId = getMandatoryRequestValue("categoryId"); $sortOrder = getOptionalRequestValue("sortOrder", null); if($id == null) { // new rule $rowCount = dbUpdate(" INSERT INTO rule( household_id, regex, account_id, category_id ) VALUES (?, ?, ?, ?)", getHouseholdId(), $regex, $accountId, $categoryId ); if($rowCount) { requestOk("Created $rowCount new rule"); } } else { // save rule $rowCount = dbUpdate(" UPDATE rule SET regex = ?, account_id = ?, category_id = ?, sort_order = ? WHERE id = ? AND household_id = ? ", $regex, $accountId, $categoryId, $sortOrder, $id, getHouseholdId() ); if($rowCount) { requestOk("Updated $rowCount rule"); } } } else if($action == "run-rule") { $id = getMandatoryRequestValue("id"); $system = (getMandatoryRequestValue("system") == "true" ? 1 : 0); $rowCount = dbUpdate($runRuleSql, $id, $id, $system, getHouseholdId()); if($rowCount !== false) { requestOk("$rowCount records updated"); } } else if($action == "run-all-rules") { $rows = dbQuery("SELECT id FROM rule_sort WHERE household_id = ?", getHouseholdId()); $system = 1; $updates = 0; foreach($rows as $row) { $rowCount = dbUpdate($runRuleSql, $row['id'], $row['id'], $system, getHouseholdId()); if($rowCount !== false) { $updates += $rowCount; } else { die(); } } requestOk("$updates records updated"); } else if($action == "get-rule-stats") { $id = getMandatoryRequestValue("id"); $rows = dbQuery("SELECT * FROM rule_ext WHERE id = ? AND household_id = ?", $id, getHouseholdId()); if($rows) { $response = $rows[0]; requestOk(json_encode($response, JSON_PRETTY_PRINT)); } } else if($action == "show-records") { $label = getMandatoryRequestValue("label"); $expense = getMandatoryRequestValue("expense"); requestOk(""); ?>
| =$label?>, =($expense ? "Expense" : "Income")?> | |||
|---|---|---|---|
| Date | Account | Amount | Comment |
| =formatDate($row['year'], $row['month'], $row['day'])?> | =$row['account_name']?> | =$row['amount']?> | =$row['comment']?> |
| =count($rows)?> records | |||