purchasing/includes/db/suppalloc_db.inc

   1 <?php
   2 /**********************************************************************
   3     Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU General Public License, GPL,
   5         as published by the Free Software Foundation, either version 3
   6         of the License, or (at your option) any later version.
   7     This program is distributed in the hope that it will be useful,
   8     but WITHOUT ANY WARRANTY; without even the implied warranty of
   9     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  10     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
  11 ***********************************************************************/
  12 //----------------------------------------------------------------------------------------
  13
  14 function add_supp_allocation($amount, $trans_type_from, $trans_no_from,
  15         $trans_type_to, $trans_no_to, $date_)
  16 {
  17         $date = date2sql($date_);
  18         $sql = "INSERT INTO ".TB_PREF."supp_allocations (
  19                 amt, date_alloc,
  20                 trans_type_from, trans_no_from, trans_no_to, trans_type_to)
  21                 VALUES (".db_escape($amount).", '$date', "
  22                 .db_escape($trans_type_from).", ".db_escape($trans_no_from).", "
  23                 .db_escape($trans_no_to).", ".db_escape($trans_type_to).")";
  24
  25         db_query($sql, "A supplier allocation could not be added to the database");
  26 }
  27
  28 //----------------------------------------------------------------------------------------
  29
  30
  31 function delete_supp_allocation($trans_id)
  32 {
  33         $sql = "DELETE FROM ".TB_PREF."supp_allocations WHERE id = ".db_escape($trans_id);
  34         db_query($sql, "The existing allocation $trans_id could not be deleted");
  35 }
  36
  37 //----------------------------------------------------------------------------------------
  38
  39 function get_supp_trans_allocation_balance($trans_type, $trans_no)
  40 {
  41         $sql = "SELECT (ov_amount+ov_gst-ov_discount-alloc) AS BalToAllocate
  42                 FROM ".TB_PREF."supp_trans WHERE trans_no="
  43                 .db_escape($trans_no)." AND type=".db_escape($trans_type);
  44         $result = db_query($sql,"calculate the allocation");
  45         $myrow = db_fetch_row($result);
  46
  47         return $myrow[0];
  48 }
  49
  50 //----------------------------------------------------------------------------------------
  51
  52 function update_supp_trans_allocation($trans_type, $trans_no, $alloc)
  53 {
  54         $sql = "UPDATE ".TB_PREF."supp_trans SET alloc = alloc + ".db_escape($alloc)."
  55                 WHERE type=".db_escape($trans_type)." AND trans_no = ".db_escape($trans_no);
  56         db_query($sql, "The supp transaction record could not be modified for the allocation against it");
  57 }
  58
  59 //-------------------------------------------------------------------------------------------------------------
  60
  61 function void_supp_allocations($type, $type_no, $date="")
  62 {
  63         return clear_supp_alloctions($type, $type_no, $date);
  64 }
  65
  66 //-------------------------------------------------------------------------------------------------------------
  67
  68 function clear_supp_alloctions($type, $type_no, $date="")
  69 {
  70         // clear any allocations for this transaction
  71         $sql = "SELECT * FROM ".TB_PREF."supp_allocations
  72                 WHERE (trans_type_from=".db_escape($type)." AND trans_no_from=".db_escape($type_no).")
  73                 OR (trans_type_to=".db_escape($type)." AND trans_no_to=".db_escape($type_no).")";
  74         $result = db_query($sql, "could not void supp transactions for type=$type and trans_no=$type_no");
  75
  76         while ($row = db_fetch($result))
  77         {
  78                 $sql = "UPDATE ".TB_PREF."supp_trans SET alloc=alloc - " . $row['amt'] . "
  79                         WHERE (type= " . $row['trans_type_from'] . " AND trans_no=" . $row['trans_no_from'] . ")
  80                         OR (type=" . $row['trans_type_to'] . " AND trans_no=" . $row['trans_no_to'] . ")";
  81                 //$sql = "UPDATE ".TB_PREF."supp_trans SET alloc=alloc - " . $row['amt'] . "
  82                 //      WHERE type=" . $row['trans_type_to'] . " AND trans_no=" . $row['trans_no_to'];
  83                 db_query($sql, "could not clear allocation");
  84                 // 2008-09-20 Joe Hunt
  85                 if ($date != "")
  86                         exchange_variation($type, $type_no, $row['trans_type_to'], $row['trans_no_to'], $date,
  87                                 $row['amt'], PT_SUPPLIER, true);
  88                 //////////////////////
  89         }
  90
  91
  92         // remove any allocations for this transaction
  93         $sql = "DELETE FROM ".TB_PREF."supp_allocations
  94                 WHERE (trans_type_from=".db_escape($type)." AND trans_no_from=".db_escape($type_no).")
  95                 OR (trans_type_to=".db_escape($type)." AND trans_no_to=".db_escape($type_no).")";
  96
  97         db_query($sql, "could not void supp transactions for type=$type and trans_no=$type_no");
  98 }
  99
 100 //----------------------------------------------------------------------------------------
 101 function get_alloc_supp_sql($extra_fields=null, $extra_conditions=null, $extra_tables=null)
 102 {
 103         $sql = "SELECT
 104                 trans.type,
 105                 trans.trans_no,
 106                 trans.reference,
 107                 trans.tran_date,
 108                 supplier.supp_name,
 109                 supplier.curr_code,
 110                 ov_amount+ov_gst+ov_discount AS Total,
 111                 trans.alloc,
 112                 trans.due_date,
 113                 trans.supplier_id,
 114                 supplier.address";
 115 /*      $sql = "SELECT trans.*,
 116                 ov_amount+ov_gst+ov_discount AS Total,
 117                 supplier.supp_name, supplier.address,
 118                 supplier.curr_code ";
 119 */
 120         if ($extra_fields)
 121                 $sql .= ", $extra_fields ";
 122
 123         $sql .= " FROM ".TB_PREF."supp_trans as trans, ".TB_PREF."suppliers as supplier";
 124         if ($extra_tables)
 125                 $sql .= " ,$extra_tables ";
 126
 127         $sql .= " WHERE trans.supplier_id=supplier.supplier_id";
 128
 129         if ($extra_conditions)
 130                 $sql .= " AND $extra_conditions";
 131
 132         return $sql;
 133 }
 134
 135
 136 //-------------------------------------------------------------------------------------------------------------
 137
 138 function get_allocatable_from_supp_sql($supplier_id, $settled)
 139 {
 140         $settled_sql = "";
 141         if (!$settled)
 142         {
 143                 $settled_sql = "AND round(ABS(ov_amount+ov_gst+ov_discount)-alloc,6) > 0";
 144         }
 145
 146         $supp_sql = "";
 147         if ($supplier_id != null)
 148                 $supp_sql = " AND trans.supplier_id = ".db_escape($supplier_id);
 149
 150         $sql = get_alloc_supp_sql("round(ABS(ov_amount+ov_gst+ov_discount)-alloc,6) <= 0 AS settled",
 151                 "(type=".ST_SUPPAYMENT." OR type=".ST_SUPPCREDIT." OR type=".ST_BANKPAYMENT.") AND (ov_amount < 0) " . $settled_sql . $supp_sql);
 152
 153         return $sql;
 154 }
 155
 156 //-------------------------------------------------------------------------------------------------------------
 157
 158 function get_allocatable_to_supp_transactions($supplier_id, $trans_no=null, $type=null)
 159 {
 160         if ($trans_no != null && $type!= null)
 161         {
 162                 $sql = get_alloc_supp_sql("amt, supp_reference", "trans.trans_no = alloc.trans_no_to
 163                         AND trans.type = alloc.trans_type_to
 164                         AND alloc.trans_no_from=".db_escape($trans_no)."
 165                         AND alloc.trans_type_from=".db_escape($type)."
 166                         AND trans.supplier_id=".db_escape($supplier_id),
 167                         TB_PREF."supp_allocations as alloc");
 168         }
 169         else
 170         {
 171                 $sql = get_alloc_supp_sql(null, "round(ABS(ov_amount+ov_gst+ov_discount)-alloc,6) > 0
 172                         AND trans.type != ".ST_SUPPAYMENT."
 173                         AND trans.supplier_id=".db_escape($supplier_id));
 174         }
 175
 176         return db_query($sql." ORDER BY trans_no", "Cannot retreive alloc to transactions");
 177 }
 178
 179 function get_sql_for_supplier_allocation_inquiry()
 180 {
 181         $date_after = date2sql($_POST['TransAfterDate']);
 182         $date_to = date2sql($_POST['TransToDate']);
 183
 184     $sql = "SELECT
 185                 trans.type,
 186                 trans.trans_no,
 187                 trans.reference,
 188                 supplier.supp_name,
 189                 trans.supp_reference,
 190         trans.tran_date,
 191                 trans.due_date,
 192                 supplier.curr_code,
 193         (trans.ov_amount + trans.ov_gst  + trans.ov_discount) AS TotalAmount,
 194                 trans.alloc AS Allocated,
 195                 ((trans.type = ".ST_SUPPINVOICE." OR trans.type = ".ST_SUPPCREDIT.") AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue
 196         FROM "
 197                         .TB_PREF."supp_trans as trans, "
 198                         .TB_PREF."suppliers as supplier
 199         WHERE supplier.supplier_id = trans.supplier_id
 200         AND trans.tran_date >= '$date_after'
 201         AND trans.tran_date <= '$date_to'";
 202
 203         if ($_POST['supplier_id'] != ALL_TEXT)
 204                 $sql .= " AND trans.supplier_id = ".db_escape($_POST['supplier_id']);
 205         if (isset($_POST['filterType']) && $_POST['filterType'] != ALL_TEXT)
 206         {
 207                 if (($_POST['filterType'] == '1') || ($_POST['filterType'] == '2'))
 208                 {
 209                         $sql .= " AND trans.type = ".ST_SUPPINVOICE." ";
 210                 }
 211                 elseif ($_POST['filterType'] == '3')
 212                 {
 213                         $sql .= " AND trans.type = ".ST_SUPPAYMENT." ";
 214                 }
 215                 elseif (($_POST['filterType'] == '4') || ($_POST['filterType'] == '5'))
 216                 {
 217                         $sql .= " AND trans.type = ".ST_SUPPCREDIT." ";
 218                 }
 219
 220                 if (($_POST['filterType'] == '2') || ($_POST['filterType'] == '5'))
 221                 {
 222                         $today =  date2sql(Today());
 223                         $sql .= " AND trans.due_date < '$today' ";
 224                 }
 225         }
 226
 227         if (!check_value('showSettled'))
 228         {
 229                 $sql .= " AND (round(abs(ov_amount + ov_gst + ov_discount) - alloc,6) != 0) ";
 230         }
 231         return $sql;
 232 }
 233 ?>