$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] . "');";