function createSinkTransaction($ledgerEntries, $loginId, $txDate)
{
$txDateString = $txDate->format('m/d/Y');
$sinkTransaction = new Transaction();
$sinkTransaction->Init_transaction($loginId, "EOM auto-sink", $txDateString, $txDateString, '', NULL, NULL, NULL, NULL, 1, 0, 0, -1, 1, '', NULL, -1, -1, 0.0, $ledgerEntries, array());
return $sinkTransaction;
}
public static function Get_transaction_list($account_id, $start_date, $end_date, $limit, $search_text, $total_period, &$error, &$warning)
{
$trans_list = array();
// VALIDATION
$error = '';
$start_time = parse_date($start_date);
$end_time = parse_date($end_date);
if ($start_time == -1) {
$error = 'Invalid start date';
} elseif ($end_time == -1) {
$error = 'Invalid end date';
} elseif ($limit < 0) {
$error = 'Limit must be 0 (no limit) or greater';
}
if ($error != '') {
//echo $error;
return $trans_list;
}
$sql = "SELECT account_debit, equation_side \n" . "FROM Accounts WHERE account_id = :account_id";
$account_debit = '';
$equation_side = '';
$pdo = db_connect_pdo();
$ps = $pdo->prepare($sql);
$ps->bindParam(':account_id', $account_id);
$t1 = microtime(true);
$success = $ps->execute();
$t2 = microtime(true);
if (!$success) {
echo get_pdo_error($ps);
return;
}
if ($row = $ps->fetch(PDO::FETCH_ASSOC)) {
$account_debit = $row['account_debit'];
$equation_side = $row['equation_side'];
} else {
return $trans_list;
}
//empty list
// Convert to mysql dates
$start_date_sql = convert_date($start_date, 1);
$end_date_sql = convert_date($end_date, 1);
$search_text_sql = '';
if ($search_text != '') {
// Search for a search string across several fields.
$search_text_sql = " and( t.trans_descr like '%{$search_text}%' " . " OR t.trans_vendor like '%{$search_text}%' " . "\tOR t.trans_comment like '%{$search_text}%' )\n";
}
/*
The ledger amount has to have the correct sign. If the ledger
entry is for an account with the same normal balance
(account debit) as the searched account, then it will be positive;
otherwise it will be negative.
The accounts are displayed by trans_date, but the totals
are calculated based on the accounting_date. This pulls up
any sub-accounts of the specified account. It is sorted in
reverse order so that the limit clause can be used for the most
recent dates.
*/
$sql = "SELECT t.trans_id, trans_descr, trans_date, accounting_date, " . " trans_vendor, trans_comment, check_number, gas_miles, " . " gas_gallons, trans_status, a.account_name, le.ledger_id, " . " aa.audit_id, aa.account_balance as audit_balance, " . " a2.account_name as account2_name, " . " a2.account_id as a2_account_id, a.account_id, " . " t.budget_date, t.exclude_from_budget, " . " (ledger_amount * a.account_debit * :account_debit) as amount \n" . "FROM Transactions t \n" . "inner join Ledger_Entries le on " . "\tle.trans_id = t.trans_id " . "inner join Accounts a on " . "\tle.account_id = a.account_id " . "left join Accounts a2 on " . "\ta.account_parent_id = a2.account_id \n" . "left join Account_Audits aa ON " . "\taa.ledger_id = le.ledger_id " . "\tAND a.account_id = :account_id \n" . "WHERE (a.account_id = :account_id " . " or a2.account_id = :account_id " . " or a2.account_parent_id = :account_id ) " . " and accounting_date >= :start_date_sql " . " and accounting_date <= :end_date_sql \n" . $search_text_sql . "ORDER BY accounting_date DESC, t.trans_id DESC, le.ledger_id DESC \n";
if ($limit > 0) {
$sql .= "limit {$limit} ";
}
$ps = $pdo->prepare($sql);
$ps->bindParam(':account_debit', $account_debit);
$ps->bindParam(':account_id', $account_id);
$ps->bindParam(':start_date_sql', $start_date_sql);
$ps->bindParam(':end_date_sql', $end_date_sql);
$t3 = microtime(true);
$success = $ps->execute();
$t4 = microtime(true);
global $execTime, $readTime;
$execTime += $t2 - $t1 + $t4 - $t3;
if (!$success) {
echo get_pdo_error($ps);
return;
}
// Iterate through all the transactions
$i = 0;
while ($row = $ps->fetch(PDO::FETCH_ASSOC)) {
$account_display = $row['account_name'];
if (!is_null($row['account2_name']) && $row['a2_account_id'] != $account_id && $row['account_id'] != $account_id) {
// there is a parent account which is not
// the currently selected one; display it.
$account_display = $row['account2_name'] . ':' . $account_display;
}
$accountingTime = strtotime($row['accounting_date']);
$budgetTime = strtotime($row['budget_date']);
$priorMonth = 0;
if ($budgetTime < $accountingTime) {
$priorMonth = 1;
}
$trans = new Transaction();
$trans->Init_transaction($_SESSION['login_id'], $row['trans_descr'], convert_date($row['trans_date'], 2), convert_date($row['accounting_date'], 2), $row['trans_vendor'], $row['trans_comment'], $row['check_number'], $row['gas_miles'], $row['gas_gallons'], $row['trans_status'], $priorMonth, $row['exclude_from_budget'], $row['trans_id'], 1, $account_display, $row['amount'], $row['ledger_id'], $row['audit_id'], $row['audit_balance']);
$trans_list[$i] = $trans;
$i++;
$MAX_ROWS = 1000;
if ($i > $MAX_ROWS) {
// Avoid memory errors
$warning = "Warning: truncated data to {$MAX_ROWS} rows";
break;
}
}
$t5 = microtime(true);
$readTime += $t5 - $t4;
// The array order must be reversed (by the array key)
krsort($trans_list);
// Loop through the transactions & calculate the totals
$running_total = 0.0;
$i = 0;
$last_date = NULL;
foreach ($trans_list as $trans) {
$start_date_total = NULL;
if ($equation_side == 'R') {
// Only do period totals for RHS accounts (revenue & expenses)
// The min_date for the total depends on each transaction;
// It is the first of the month or first of the year of the accounting date.
$start_date_arr = explode('/', $trans->get_accounting_date(false));
switch ($total_period) {
case 'month':
// total starting from 1st of start_date month
$start_date_total = $start_date_arr[2] . '-' . $start_date_arr[0] . '-01';
break;
case 'year':
$start_date_total = $start_date_arr[2] . '-01-01';
break;
case 'visible':
// filter by visible dates
$start_date_total = $start_date_sql;
}
}
// Only query the account total for the first balance
if ($i == 0) {
$trans->Set_trans_balance($account_id, $account_debit, $start_date_total);
$running_total = $trans->get_ledger_total(true);
} else {
if ($last_date != $start_date_total) {
// new period of data; reset the running total
$running_total = $trans->get_ledger_amount(true);
} else {
// add to previous total
$running_total += $trans->get_ledger_amount(true);
}
$trans->set_ledger_total($running_total);
}
$i++;
$last_date = $start_date_total;
}
$pdo = null;
return $trans_list;
}
$ledger->setAccountData($_POST['accountR_id'][$i]);
$ledger->amount = $_POST['amountR'][$i];
$ledgerR_list[] = $ledger;
}
}
//nl2br (print_r ($ledgerL_list));
//nl2br (print_r ($ledgerR_list));
$excludeBudget = 0;
$priorMonth = 0;
if (isset($_POST['prior_month'])) {
$priorMonth = 1;
}
if (isset($_POST['exclude_budget'])) {
$excludeBudget = 1;
}
$trans->Init_transaction($_SESSION['login_id'], $_POST['trans_descr'], $_POST['trans_date'], $_POST['accounting_date'], $_POST['trans_vendor'], $_POST['trans_comment'], $_POST['check_number'], $_POST['gas_miles'], $_POST['gas_gallons'], $_POST['trans_status'], $priorMonth, $excludeBudget, $_POST['trans_id'], $_POST['repeat_count'], '', NULL, -1, -1, 0.0, $ledgerL_list, $ledgerR_list);
$error = $trans->Validate();
if ($error == '') {
if ($mode == 'save') {
$error = $trans->Save_repeat_transactions();
} else {
$error = $trans->Delete_transaction();
}
}
if ($error == '') {
//successful save; reset for a new transaction
$trans = new Transaction();
}
}
$error1 = $error;
$warning = '';
