$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());
$results = db::MySqlSubmitQuery($q->toString());
if ($results) {
$resultRow = mysql_fetch_assoc($results);
*/
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();
$object->label = $row[tbl_tags::$tag_name];
$object->value = $row[$sumExpenseColumnName];
$object->link = "JavaScript: getTransactionsBy('" . $groupBy . "','" . $row[$groupBy] . "');";
