$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());
$results = db::MySqlSubmitQuery($q->toString());
if ($results) {
$resultRow = mysql_fetch_assoc($results);
$total = $resultRow["total"];
$inTotal = $resultRow["inTotal"];
$outTotal = $resultRow["outTotal"];
//reset columns and add paging and sorting
$q->COLUMNS(tbl_transactions::tableName() . "." . tbl_transactions::getPrimaryKeyField() . "," . tbl_transactions::tableName() . "." . tbl_transactions::$tdate . "," . tbl_transactions::tableName() . "." . tbl_transactions::$in_amount . "," . tbl_transactions::tableName() . "." . tbl_transactions::$out_amount . "," . tbl_transactions::tableName() . "." . tbl_transactions::$category . "," . tbl_transactions::tableName() . "." . tbl_transactions::$description . "," . tbl_transactions::tableName() . "." . tbl_transactions::$ttype . "," . tbl_transactions::tableName() . "." . tbl_transactions::$tmonth . "," . tbl_transactions::tableName() . "." . tbl_transactions::$tyear . "," . tbl_transactions::tableName() . "." . tbl_transactions::$tday . "," . tbl_transactions::tableName() . "." . tbl_transactions::$istransfer . "," . tbl_transactions::tableName() . "." . tbl_transactions::$tag_id . "," . tbl_transactions::tableName() . "." . tbl_transactions::$store_id . "," . tbl_stores::tableName() . "." . tbl_stores::$store_name . "," . tbl_tags::tableName() . "." . tbl_tags::$tag_name . "");
$q->ADD_SORT($sortName, $sortOrder);
$q->LIMIT($offset, $recordsPerPage);
Debug($q->toString());
$pagedResults = db::MySqlSubmitQuery($q->toString());
if ($pagedResults) {
while ($row = mysql_fetch_array($pagedResults, MYSQL_ASSOC)) {
$cell = array($row[tbl_transactions::$tdate], $row[tbl_transactions::$in_amount], $row[tbl_transactions::$out_amount], $row[tbl_transactions::$ttype], $row[tbl_transactions::$category], $row[tbl_transactions::$description], $row[tbl_tags::$tag_name], $row[tbl_transactions::$tag_id], $row[tbl_stores::$store_name], $row[tbl_transactions::$store_id]);
array_push($rows, array("id" => $row["id"], "cell" => $cell));
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.';
$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();
$object->label = $row[tbl_tags::$tag_name];
$object->value = $row[$sumExpenseColumnName];
$object->link = "JavaScript: getTransactionsBy('" . $groupBy . "','" . $row[$groupBy] . "');";
array_push($data, $object);
}
}
echo '{
"chart": {
"caption": "Expenditure - percentage by ' . $groupBy . '",
"bgcolor": "FFFFFF",
$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"] . "');"));
}
}
echo '{"chart": {
"caption": "Monthly income/expenses",
"subCaption": "",
"xAxisName": "Month",
"yAxisName": "Amount ($)",
"numberPrefix": "$",
