$tag2 = $_POST[tbl_transactions::$tag_id . "2"]; $store2 = $_POST[tbl_transactions::$store_id . "2"]; if ($in + $in2 != $originalIn) { throw new Exception("new amounts are not equal to original total amount of '" . $originalIn . "'"); } if ($out + $out2 != $originalOut) { throw new Exception("new amounts are not equal to original total amount of '" . $originalOut . "'"); } $insert = new MySqlInsert(); $insert->INTO(tbl_transactions::tableName()); $insert->COLUMNS(array(tbl_transactions::$tdate, tbl_transactions::$in_amount, tbl_transactions::$out_amount, tbl_transactions::$category, tbl_transactions::$description, tbl_transactions::$ttype, tbl_transactions::$tyear, tbl_transactions::$tmonth, tbl_transactions::$tday, tbl_transactions::$tag_id, tbl_transactions::$store_id)); $insert->ADD_ROW(array($date, $in2, $out2, $cat2, $desc2, $type, $year, $month, $day, $tag2, $store2)); Debug($insert->toString()); } $update = new MySqlUpdate(); $update->TABLE(tbl_transactions::tableName()); $update->ADD_COLUMN_VALUE_PAIR(tbl_transactions::$in_amount, $in); $update->ADD_COLUMN_VALUE_PAIR(tbl_transactions::$out_amount, $out); $update->ADD_COLUMN_VALUE_PAIR(tbl_transactions::$category, $cat); $update->ADD_COLUMN_VALUE_PAIR(tbl_transactions::$description, $desc); $update->ADD_COLUMN_VALUE_PAIR(tbl_transactions::$tag_id, $tag); $update->ADD_COLUMN_VALUE_PAIR(tbl_transactions::$store_id, $store); $update->ADD_CONDITION(tbl_transactions::getPrimaryKeyField() . "=" . $id); Debug($update->toString()); if ($insert) { db::MySqlSubmitTransaction(array($update->toString(), $insert->toString())); } else { db::MySqlSubmitQuery($update->toString()); } echo "Success."; } catch (Exception $e) {
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 '{
$date_month = "m"; $date_day = "d"; $transactionType = $_POST["transactionsType"]; $filename = $_FILES["file"]["tmp_name"]; $delimiter = ","; Debug("File type is '" . $transactionType . "'"); if ($_FILES["file"]["error"] > 0) { echo "Error: " . $_FILES["file"]["error"] . "<br>"; } else { Debug("Upload: " . $_FILES["file"]["name"]); Debug("Type: " . $_FILES["file"]["type"]); Debug("Size: " . $_FILES["file"]["size"] / 1024 . " kB"); Debug("Stored in: " . $filename); try { $insertQuery = new MySqlInsert(); $insertQuery->INTO(tbl_transactions::tableName()); $insertQuery->COLUMNS(array(tbl_transactions::$tdate, tbl_transactions::$in_amount, tbl_transactions::$out_amount, tbl_transactions::$category, tbl_transactions::$description, tbl_transactions::$ttype, tbl_transactions::$tmonth, tbl_transactions::$tyear, tbl_transactions::$tday)); if (($handle = fopen($filename, 'r')) !== FALSE) { while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE) { $record_date = ""; $record_in_amount = ""; $record_out_amount = ""; $record_category = ""; $record_description = ""; $record_type = ""; $record_month = ""; $record_year = ""; $record_day = ""; if ($transactionType == db_enum_ttype::c_visa) { //("date", "description", "value") $record_date = date($date_format, strtotime(getRowColumnValue($row, 0)));
$updateStore->TABLE(tbl_transactions::tableName()); $updateStore->ADD_COLUMN_VALUE_PAIR(tbl_transactions::$store_id, $value["id"]); $updateStore->ADD_COLUMN_VALUE_PAIR(tbl_transactions::$tag_id, $value["default_tag_id"]); $updateStore->ADD_CONDITION(alphaNumericLowerMySql(tbl_transactions::$description) . " LIKE '%" . alphaNumericLower($value["name"]) . "%'"); //$updateStore->ADD_CONDITION(tbl_transactions::$in_amount . "=0"); $updateStore->ADD_CONDITION(tbl_transactions::$istransfer . "=0"); $updateStore->ADD_CONDITION(tbl_transactions::$store_id . "='" . db_cache_store::$_none["id"] . "'"); db::MySqlSubmitQuery($updateStore->toString()); } } echo 'tagging complete. '; } else { if (!is_null($_GET["reset-tagging"])) { //reset all tags to other $resetTag = new MySqlUpdate(); $resetTag->TABLE(tbl_transactions::tableName()); $resetTag->ADD_COLUMN_VALUE_PAIR(tbl_transactions::$tag, db_enum_tag::c_none); $resetTag->ADD_COLUMN_VALUE_PAIR(tbl_transactions::$store_id, db_cache_store::$_none["id"]); $resetTag->ADD_COLUMN_VALUE_PAIR(tbl_transactions::$istransfer, "0"); db::MySqlSubmitQuery($resetTag->toString()); echo 'tag reset complete.'; } } function alphaNumericLower($text) { return strtolower(str_ireplace('\'', '', str_ireplace('#', '', str_ireplace('(', '', str_ireplace(')', '', str_ireplace('-', '', str_ireplace(' ', '', trim($text)))))))); } function alphaNumericLowerMySql($fieldName) { return 'LOWER(REPLACE(REPLACE(REPLACE(REPLACE(' . $fieldName . ', " ", ""),"-",""),"#",""),"\'",""))'; }
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"] . "');")); } } echo '{"chart": {