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)); } } } echo json_encode(array(inTotal => $inTotal, outTotal => $outTotal, page => $pageNumber, total => $total . '', rows => $rows));
$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] . "');"; array_push($data, $object); } } echo '{