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) {
echo $e->getMessage();
}
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": {
