$inTotal = 0; $outTotal = 0; $pageNumber = is_null($_GET["page"]) ? 1 : $_GET["page"]; $page = $pageNumber - 1; $recordsPerPage = is_null($_GET["rp"]) ? 10 : $_GET["rp"]; $offset = $page * $recordsPerPage; $sortName = is_null($_GET["sortname"]) ? "tdate" : $_GET["sortname"]; $sortOrder = is_null($_GET["sortorder"]) ? "desc" : $_GET["sortorder"]; $searchQuery = is_null($_GET["query"]) ? null : trim($_GET["query"]); $searchQueryType = is_null($_GET["qtype"]) ? null : $_GET["qtype"]; $searchCondition = ""; if (!(is_null($searchQuery) || empty($searchQuery)) && !(is_null($searchQueryType) || empty($searchQueryType))) { $searchCondition = $searchQueryType . " LIKE '%" . $searchQuery . "%'"; } $valueCondition = $_GET["type"] == $transaction_type->income ? "in_amount>0 AND out_amount=0" : ($_GET["type"] == $transaction_type->expenses ? "in_amount=0 AND out_amount>0" : ""); $q = new MySqlSelect(); $q->COLUMNS("COUNT(*) as total, SUM(" . tbl_transactions::$in_amount . ") as inTotal, SUM(" . tbl_transactions::$out_amount . ") as outTotal"); $q->FROM(tbl_transactions::tableName()); $q->JOIN(tbl_stores::tableName()); $q->JOIN(tbl_tags::tableName()); if (!empty($valueCondition)) { $q->ADD_CONDITION($valueCondition); } $q->ADD_CONDITION("tdate BETWEEN " . $fromDate . " AND " . $toDate); if (!empty($searchCondition)) { $q->ADD_CONDITION($searchCondition); } $q->ADD_CONDITION(tbl_transactions::$istransfer . "='0'"); $q->ADD_CONDITION(tbl_transactions::tableName() . "." . tbl_transactions::$store_id . "=" . tbl_stores::tableName() . "." . tbl_stores::getPrimaryKeyField()); $q->ADD_CONDITION(tbl_transactions::tableName() . "." . tbl_transactions::$tag_id . "=" . tbl_tags::tableName() . "." . tbl_tags::getPrimaryKeyField()); //Debug($q->toString());
require_once "constants.php"; $cache = "<?php"; //special handling for stores table (creating stores cache) $selectStores = new MySqlSelect(); $selectStores->COLUMNS("*"); $selectStores->FROM("stores"); $selectStores->ADD_SORT("store_name", "desc"); $storesResult = db::MySqlSubmitQuery($selectStores->toString()); if ($storesResult) { $cache .= $newLine . "class db_cache_store{"; while ($storesResultRow = mysql_fetch_array($storesResult, MYSQL_ASSOC)) { $cache .= $newLine . " public static \$" . "_" . cleanName($storesResultRow["store_name"]) . " = array(id => " . $storesResultRow["id"] . " , name => '" . $storesResultRow["store_name"] . "', default_tag_id => '" . $storesResultRow["default_tag_id"] . "');"; } $cache .= $newLine . "}"; } //special handling for tags table (creating stores cache) $selectTags = new MySqlSelect(); $selectTags->COLUMNS("*"); $selectTags->FROM("tags"); $selectTags->ADD_SORT("tag_name", "desc"); $tagsResult = db::MySqlSubmitQuery($selectTags->toString()); if ($tagsResult) { $cache .= $newLine . "class db_cache_tag{"; while ($tagsResultRow = mysql_fetch_array($tagsResult, MYSQL_ASSOC)) { $cache .= $newLine . " public static \$" . "_" . cleanName($tagsResultRow["tag_name"]) . " = array(id => " . $tagsResultRow["id"] . " , name => '" . $tagsResultRow["tag_name"] . "');"; } $cache .= $newLine . "}"; } $cache .= $newLine . "?>"; file_put_contents("cache.php", $cache); echo 'complete.';
/* This file fetches transaction data from the database based on a start and end Month/Year and returns the data back as a JSON object that can be used to render a pie chart. */ require_once "include_files.php"; header('Content-Type: application/json'); $monthFrom = intval($_GET["monthFrom"]); $monthTo = intval($_GET["monthTo"]); $yearFrom = intval($_GET["yearFrom"]); $yearTo = intval($_GET["yearTo"]); $groupBy = $_GET["groupBy"]; Debug($groupBy); $fromDate = $yearFrom . str_pad($monthFrom, 2, "0", STR_PAD_LEFT) . "01"; $toDate = $yearTo . str_pad($monthTo, 2, "0", STR_PAD_LEFT) . getLastDayOfMonth($monthTo, $yearTo); $sumExpenseColumnName = "expense"; $select = new MySqlSelect(); $select->COLUMNS(tbl_tags::$tag_name . ", SUM(" . tbl_transactions::$out_amount . ") as " . $sumExpenseColumnName); $select->FROM(tbl_transactions::tableName()); $select->JOIN(tbl_tags::tableName()); $select->ADD_CONDITION(tbl_transactions::$in_amount . "='0'"); $select->ADD_CONDITION("tdate BETWEEN " . $fromDate . " AND " . $toDate); $select->ADD_CONDITION(tbl_transactions::$istransfer . "='0'"); $select->ADD_CONDITION(tbl_transactions::tableName() . "." . tbl_transactions::$tag_id . "=" . tbl_tags::tableName() . "." . tbl_tags::getPrimaryKeyField()); $select->GROUPBY($groupBy); $select->ADD_SORT($sumExpenseColumnName, "DESC"); Debug($select->toString()); $results = db::MySqlSubmitQuery($select->toString()); $data = array(); if ($results) { while ($row = mysql_fetch_array($results, MYSQL_ASSOC)) { $object = new stdClass();
This file fetches transaction data from the database based on a start and end Month/Year and returns the data back as a JSON object that can be used to render a bar chart. */ require_once "include_files.php"; header('Content-Type: application/json'); $monthFrom = intval($_GET["monthFrom"]); $monthTo = intval($_GET["monthTo"]); $yearFrom = intval($_GET["yearFrom"]); $yearTo = intval($_GET["yearTo"]); $months = array(1 => "Jan", 2 => "Feb", 3 => "Mar", 4 => "Apr", 5 => "May", 6 => "Jun", 7 => "Jul", 8 => "Aug", 9 => "Sep", 10 => "Oct", 11 => "Nov", 12 => "Dec"); $fromDate = $yearFrom . str_pad($monthFrom, 2, "0", STR_PAD_LEFT) . "01"; $toDate = $yearTo . str_pad($monthTo, 2, "0", STR_PAD_LEFT) . getLastDayOfMonth($monthTo, $yearTo); // Query $incomeData = array(); $expenseData = array(); $labels = array(); $q = new MySqlSelect(); $q->COLUMNS("SUM( in_amount ) AS income, SUM( out_amount ) AS expenses, tmonth , tyear"); $q->FROM(tbl_transactions::tableName()); $q->ADD_CONDITION(tbl_transactions::$istransfer . "='0'"); $q->ADD_CONDITION("tdate BETWEEN " . $fromDate . " AND " . $toDate); $q->GROUPBY("tmonth"); $q->ADD_SORT("tyear,tmonth", "ASC"); Debug($q->toString()); $results = db::MySqlSubmitQuery($q->toString(), $link); Debug($results); if ($results) { while ($row = mysql_fetch_array($results, MYSQL_ASSOC)) { array_push($labels, array("label" => $months[$row["tmonth"]] . "-" . $row["tyear"])); array_push($incomeData, array("value" => $row["income"], "link" => "JavaScript: getTransactions('" . $transaction_type->income . "','" . $row["tmonth"] . "','" . $row["tyear"] . "');")); array_push($expenseData, array("value" => $row["expenses"], "link" => "JavaScript: getTransactions('" . $transaction_type->expenses . "','" . $row["tmonth"] . "','" . $row["tyear"] . "');")); }