Moved all SQL statements from PHP files into relevant *_db.inc files.

[fa-stable.git] / gl / bank_account_reconcile.php

diff --git a/gl/bank_account_reconcile.php b/gl/bank_account_reconcile.php
index b91bed9eb612c17f5710c0452af92193f078ff93..f59ee696adfc0b9a4e3099f22951837e720addce 100644 (file)
--- a/gl/bank_account_reconcile.php
+++ b/gl/bank_account_reconcile.php
@@ -30,7 +30,7 @@ if ($use_date_picker)
 
 add_js_file('reconcile.js');
 
-page(_("Reconcile Bank Account"), false, false, "", $js);
+page(_($help_context = "Reconcile Bank Account"), false, false, "", $js);
 
 check_db_has_bank_accounts(_("There are no bank accounts defined in the system."));
 
@@ -108,7 +108,8 @@ function change_tpl_flag($reconcile_id)
 {
        global  $Ajax;
 
-       if (!check_date()) 
+       if (!check_date() 
+               && check_value("rec_".$reconcile_id)) // temporary fix
                return false;
 
        if (get_post('bank_date')=='')  // new reconciliation
@@ -117,16 +118,10 @@ function change_tpl_flag($reconcile_id)
        $_POST['bank_date'] = date2sql(get_post('reconcile_date'));
        $reconcile_value = check_value("rec_".$reconcile_id) 
                                                ? ("'".$_POST['bank_date'] ."'") : 'NULL';
-       $sql = "UPDATE ".TB_PREF."bank_trans SET reconciled=$reconcile_value WHERE id=$reconcile_id";
-
-       db_query($sql, "Can't change reconciliation status");
-       // save last reconcilation status (date, end balance)
-    $sql2="UPDATE ".TB_PREF."bank_accounts SET last_reconciled_date='"
-                       .date2sql($_POST["reconcile_date"])."',
-           ending_reconcile_balance=".input_num("end_balance")
-                       ." WHERE id=".$_POST["bank_account"];
-
-       $result = db_query($sql2,"Error updating reconciliation information");
+       
+       update_reconciled_values($reconcile_id, $reconcile_value, $_POST['reconcile_date'],
+               input_num('end_balance'), $_POST['bank_account']);
+               
        $Ajax->activate('reconciled');
        $Ajax->activate('difference');
        return true;
@@ -175,17 +170,7 @@ bank_reconciliation_list_cells(_("Bank Statement:"), get_post('bank_account'),
 end_row();
 end_table();
 
-$date = date2sql(get_post('reconcile_date'));
-
-$sql = "SELECT MAX(reconciled) as last_date,
-                SUM(IF(reconciled<='$date', amount, 0)) as end_balance,
-                SUM(IF(reconciled<'$date', amount, 0)) as beg_balance,
-                SUM(amount) as total
-       FROM ".TB_PREF."bank_trans trans
-       WHERE bank_act=".$_POST['bank_account'];
-//     ." AND trans.reconciled IS NOT NULL";
-
-$result = db_query($sql,"Cannot retrieve reconciliation data");
+$result = get_max_reconciled(get_post('reconcile_date'), $_POST['bank_account']);
 
 if ($row = db_fetch($result)) {
        $_POST["reconciled"] = price_format($row["end_balance"]-$row["beg_balance"]);
@@ -196,11 +181,8 @@ if ($row = db_fetch($result)) {
                $_POST["end_balance"] = price_format($row["end_balance"]);
                if (get_post('bank_date')) {
                        // if it is the last updated bank statement retrieve ending balance
-                       $sql = "SELECT ending_reconcile_balance
-                               FROM ".TB_PREF."bank_accounts WHERE id=".$_POST['bank_account']
-                               . " AND last_reconciled_date='".$_POST['bank_date']."'";
-                       $result = db_query($sql,"Cannot retrieve last reconciliation");
-                       $row = db_fetch($result);
+
+                       $row = get_ending_reconciled($_POST['bank_account'], $_POST['bank_date']);
                        if($row) {
                                $_POST["end_balance"] = price_format($row["ending_reconcile_balance"]);
                        }
@@ -241,13 +223,7 @@ echo "<hr>";
 if (!isset($_POST['bank_account']))
     $_POST['bank_account'] = "";
 
-$sql = "SELECT type, trans_no, ref, trans_date, 
-                               amount, person_id, person_type_id, reconciled, id
-               FROM ".TB_PREF."bank_trans
-               WHERE ".TB_PREF."bank_trans.bank_act = '" . $_POST['bank_account'] . "'
-                       AND (reconciled IS NULL OR reconciled='". $date ."')
-               ORDER BY trans_date,".TB_PREF."bank_trans.id";
-// or  ORDER BY reconciled desc, trans_date,".TB_PREF."bank_trans.id";
+$sql = get_sql_for_bank_account_reconcile($_POST['bank_account'], get_post('reconcile_date'));
 
 $act = get_bank_account($_POST["bank_account"]);
 display_heading($act['bank_account_name']." - ".$act['bank_curr_code']);
@@ -266,7 +242,7 @@ display_heading($act['bank_account_name']." - ".$act['bank_curr_code']);
           );
        $table =& new_db_pager('trans_tbl', $sql, $cols);
 
-       $table->width = "60%";
+       $table->width = "80%";
        display_db_pager($table);
 
 br(1);