public function Save($pdo, &$ps)
{
if ($this->m_budget_id < 1) {
// INSERT
$sql = 'INSERT INTO Budget (account_id, budget_month, budget_amount,' . ' budget_comment) ' . 'VALUES (:account_id, :budget_month, :budget_amount, ' . ' :budget_comment)';
if ($ps == NULL) {
$ps = $pdo->prepare($sql);
}
$ps->bindParam(':account_id', $this->m_account_id);
$dateString = $this->m_budget_month->format('Y-m-d');
$ps->bindParam(':budget_month', $dateString);
} else {
// UPDATE
$sql = 'UPDATE Budget set budget_amount = :budget_amount, ' . 'budget_comment = :budget_comment ' . 'WHERE budget_id = :budget_id';
if ($ps == NULL) {
$ps = $pdo->prepare($sql);
}
$ps->bindParam(':budget_id', $this->m_budget_id);
}
$ps->bindParam(':budget_amount', $this->m_budget_amount);
$ps->bindParam(':budget_comment', $this->m_budget_comment, PDO::PARAM_STR);
$t2 = microtime(true);
$success = $ps->execute();
$t3 = microtime(true);
global $execTime;
$execTime += $t3 - $t2;
if (!$success) {
return get_pdo_error($ps);
}
return '';
}
public static function Get_savings_accounts($login_id, array &$account_list)
{
$sql = 'SELECT a.account_id, concat(parent.account_name, \':\', a.account_name) ' . 'as account_name ' . 'FROM Accounts a ' . 'INNER JOIN Accounts parent ON ' . ' parent.account_id = a.account_parent_id ' . 'WHERE a.is_savings = 1 AND a.login_id = :login_id ' . 'ORDER BY concat(parent.account_name, \':\', a.account_name) ';
$pdo = db_connect_pdo();
$ps = $pdo->prepare($sql);
$ps->bindParam(':login_id', $login_id);
$success = $ps->execute();
if (!$success) {
return get_pdo_error($ps);
}
while ($row = $ps->fetch(PDO::FETCH_ASSOC)) {
$account_list[$row['account_id']] = $row['account_name'];
}
return '';
}
private function Set_trans_balance($account_id, $account_debit, $min_date)
{
$sql = "SELECT sum(ledger_amount * a.account_debit * {$account_debit})" . " as balance \n" . "FROM Ledger_Entries le \n" . "INNER JOIN Transactions t 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" . "WHERE (a.account_id = :account_id OR " . " a2.account_id = :account_id OR " . " a2.account_parent_id = :account_id) " . " AND (t.accounting_date < :accounting_date " . "\t\tOR (t.accounting_date = :accounting_date " . "\t\t\tAND (t.trans_id < :trans_id " . "\t\t\t\tOR (t.trans_id = :trans_id " . "\t\t\t\t\tAND le.ledger_id < :ledger_id ) ) ) )";
if (!is_null($min_date)) {
// doing a period total, so add a minimum accounting date
$sql .= "\n\tAND t.accounting_date >= '{$min_date}' ";
}
// Time the query
$time = microtime(true);
$pdo = db_connect_pdo();
$ps = $pdo->prepare($sql);
$ps->bindParam(':account_id', $account_id);
$accounting_date_val = $this->get_accounting_date_sql();
$ps->bindParam(':accounting_date', $accounting_date_val);
$ps->bindParam(':trans_id', $this->m_trans_id);
$ledger_id_val = $this->get_ledger_id();
$ps->bindParam(':ledger_id', $ledger_id_val);
$success = $ps->execute();
if (!$success) {
echo get_pdo_error($ps);
return;
}
// Successful query
$row = $ps->fetch(PDO::FETCH_NUM);
$elapsed = round((microtime(true) - $time) * 1000, 0);
//echo "Select time: $elapsed". "ms";
if ($row) {
$this->m_ledger_total = $row[0] + $this->get_ledger_amount(true);
} else {
$this->m_ledger_total = 0.0;
}
// no rows found
}
public static function Authenticate($user, $password)
{
$sql = "SELECT login_id, default_account_id, login_admin, " . " display_name, default_summary1, default_summary2, " . " car_account_id, bad_login_count, locked \n" . "FROM Logins \n" . "WHERE login_user = :user " . " AND login_password = MD5(:password) ";
$result = false;
$pdo = db_connect_pdo();
// use transaction because an update will follow the select
$pdo->beginTransaction();
$ps = $pdo->prepare($sql);
$ps->bindParam(':user', $user);
$ps->bindParam(':password', $password);
$success = $ps->execute();
if (!$success) {
return get_pdo_error($ps);
}
if ($row = $ps->fetch(PDO::FETCH_ASSOC)) {
// found login & correct password. Check for lockout.
$locked = $row['locked'];
if ($locked > 0) {
$result = "The account '{$user}' is locked!";
} else {
$_SESSION['login_id'] = $row['login_id'];
$_SESSION['login_user'] = $_POST['login_user'];
$_SESSION['default_account_id'] = $row['default_account_id'];
$_SESSION['default_summary1'] = $row['default_summary1'];
$_SESSION['default_summary2'] = $row['default_summary2'];
$_SESSION['car_account_id'] = $row['car_account_id'];
$_SESSION['login_admin'] = $row['login_admin'];
$_SESSION['display_name'] = $row['display_name'];
$result = true;
// on success, wipe the bad login count
self::Set_bad_login_count($pdo, $user, 0);
}
} else {
// bad login
$result = "Incorrect username & password";
// find the relevant user login_id
$login_id = self::Find_login_id($pdo, $user);
if (is_numeric($login_id) && $login_id > 0) {
// Load user object, get bad count, then increment it.
$login = new Login();
$login->Load_login($login_id);
$bad_count = $login->get_bad_login_count();
$bad_count++;
$error = self::Set_bad_login_count($pdo, $user, $bad_count);
if ($bad_count >= self::$MAX_AUTH_FAILURES) {
$result = "The account '{$user}' has been locked!";
}
if ($error != '') {
$result = $error;
}
} else {
if (strlen($login_id) > 5) {
$result = "Problem finding login_id: " . $login_id;
}
}
}
// End bad login
$pdo->commit();
return $result;
}
public function Delete_account_audit()
{
$error = '';
if ($this->m_audit_id < 0) {
return "Unable to delete audit record; not yet initialized.";
}
$sql = "DELETE FROM AccountAudits " . "WHERE audit_id = :audit_id ";
$pdo = db_connect_pdo();
$ps = $pdo->prepare($sql);
$ps->bindParam(':audit_id', $this->m_audit_id);
$success = $ps->execute();
if (!$success) {
return get_pdo_error($ps);
}
$count = $ps->rowCount();
if ($count != 1) {
return 'Error: audit delete affected ' . $count . ' rows';
}
// Success; set audit_id to -1
$this->m_audit_id = -1;
return '';
}