sales/includes/db/custalloc_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_cust_allocation($amount, $trans_type_from, $trans_no_from,
  15         $trans_type_to, $trans_no_to)
  16 {
  17         $sql = "INSERT INTO ".TB_PREF."cust_allocations (
  18                 amt, date_alloc,
  19                 trans_type_from, trans_no_from, trans_no_to, trans_type_to)
  20                 VALUES ($amount, Now(), $trans_type_from, $trans_no_from, $trans_no_to, $trans_type_to)";
  21
  22         db_query($sql, "A customer allocation could not be added to the database");
  23 }
  24
  25 //----------------------------------------------------------------------------------------
  26
  27
  28 function delete_cust_allocation($trans_id)
  29 {
  30         $sql = "DELETE FROM ".TB_PREF."cust_allocations WHERE id = " . $trans_id;
  31         return db_query($sql, "The existing allocation $trans_id could not be deleted");
  32 }
  33
  34 //----------------------------------------------------------------------------------------
  35
  36 function get_DebtorTrans_allocation_balance($trans_type, $trans_no)
  37 {
  38
  39         $sql = "SELECT (ov_amount+ov_gst+ov_freight+ov_freight_tax-ov_discount-alloc) AS BalToAllocate
  40                 FROM ".TB_PREF."debtor_trans WHERE trans_no=$trans_no AND type=$trans_type";
  41         $result = db_query($sql,"calculate the allocation");
  42         $myrow = db_fetch_row($result);
  43
  44         return $myrow[0];
  45 }
  46
  47 //----------------------------------------------------------------------------------------
  48
  49 function update_debtor_trans_allocation($trans_type, $trans_no, $alloc)
  50 {
  51         $sql = "UPDATE ".TB_PREF."debtor_trans SET alloc = alloc + $alloc
  52                 WHERE type=$trans_type AND trans_no = $trans_no";
  53         db_query($sql, "The debtor transaction record could not be modified for the allocation against it");
  54 }
  55
  56 //-------------------------------------------------------------------------------------------------------------
  57
  58 function void_cust_allocations($type, $type_no, $date="")
  59 {
  60         return clear_cust_alloctions($type, $type_no, $date);
  61 }
  62
  63 //-------------------------------------------------------------------------------------------------------------
  64
  65 function clear_cust_alloctions($type, $type_no, $date="")
  66 {
  67         // clear any allocations for this transaction
  68         $sql = "SELECT * FROM ".TB_PREF."cust_allocations
  69                 WHERE (trans_type_from=$type AND trans_no_from=$type_no)
  70                 OR (trans_type_to=$type AND trans_no_to=$type_no)";
  71         $result = db_query($sql, "could not void debtor transactions for type=$type and trans_no=$type_no");
  72
  73         while ($row = db_fetch($result))
  74         {
  75                 $sql = "UPDATE ".TB_PREF."debtor_trans SET alloc=alloc - " . $row['amt'] . "
  76                         WHERE (type= " . $row['trans_type_from'] . " AND trans_no=" . $row['trans_no_from'] . ")
  77                         OR (type=" . $row['trans_type_to'] . " AND trans_no=" . $row['trans_no_to'] . ")";
  78                 db_query($sql, "could not clear allocation");
  79                 // 2008-09-20 Joe Hunt
  80                 if ($date != "")
  81                         exchange_variation($type, $type_no, $row['trans_type_to'], $row['trans_no_to'], $date,
  82                                 $row['amt'], payment_person_types::customer(), true);
  83                 //////////////////////
  84         }
  85
  86
  87         // remove any allocations for this transaction
  88         $sql = "DELETE FROM ".TB_PREF."cust_allocations
  89                 WHERE (trans_type_from=$type AND trans_no_from=$type_no)
  90                 OR (trans_type_to=$type AND trans_no_to=$type_no)";
  91
  92         db_query($sql, "could not void debtor transactions for type=$type and trans_no=$type_no");
  93 }
  94 //----------------------------------------------------------------------------------------
  95
  96 function get_alloc_trans_sql($extra_fields=null, $extra_conditions=null, $extra_tables=null)
  97 {
  98         $sql = "SELECT
  99                 trans.type,
 100                 trans.trans_no,
 101                 trans.reference,
 102                 trans.tran_date,
 103                 debtor.name AS DebtorName,
 104                 debtor.curr_code,
 105                 ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total,
 106                 trans.alloc,
 107                 trans.due_date,
 108                 debtor.address,
 109                 trans.version ";
 110
 111         if ($extra_fields)
 112                 $sql .= ", $extra_fields ";
 113
 114         $sql .= " FROM ".TB_PREF."debtor_trans as trans, "
 115                                 .TB_PREF."debtors_master as debtor";
 116         if ($extra_tables)
 117                 $sql .= ",$extra_tables ";
 118
 119         $sql .= " WHERE trans.debtor_no=debtor.debtor_no";
 120
 121         if ($extra_conditions)
 122                 $sql .= " AND $extra_conditions ";
 123
 124         return $sql;
 125 }
 126
 127
 128 //-------------------------------------------------------------------------------------------------------------
 129
 130 function get_allocatable_from_cust_sql($customer_id, $settled)
 131 {
 132         $settled_sql = "";
 133         if (!$settled)
 134         {
 135                 $settled_sql = " AND (round(ov_amount+ov_gst+ov_freight+ov_freight_tax-ov_discount-alloc,6) > 0)";
 136         }
 137         $cust_sql = "";
 138         if ($customer_id != null)
 139                 $cust_sql = " AND trans.debtor_no = $customer_id";
 140
 141         $sql = get_alloc_trans_sql("round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) <= 0 AS settled",
 142                 "(type=12 OR type=11 OR type=2) AND (trans.ov_amount > 0) " . $settled_sql . $cust_sql);
 143
 144         return $sql;
 145 }
 146
 147 //-------------------------------------------------------------------------------------------------------------
 148
 149 function get_allocatable_to_cust_transactions($customer_id, $trans_no=null, $type=null)
 150 {
 151         if ($trans_no != null and $type != null)
 152         {
 153                 $sql = get_alloc_trans_sql("amt", "trans.trans_no = alloc.trans_no_to
 154                         AND trans.type = alloc.trans_type_to
 155                         AND alloc.trans_no_from=$trans_no
 156                         AND alloc.trans_type_from=$type
 157                         AND trans.debtor_no=$customer_id",
 158                         "".TB_PREF."cust_allocations as alloc");
 159         }
 160         else
 161         {
 162                 $sql = get_alloc_trans_sql(null, "round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) > 0
 163                         AND trans.type != " . systypes::cust_payment() . "
 164                         AND trans.type != " . systypes::bank_deposit() . "
 165                         AND trans.type != 11
 166                         AND trans.type != 13
 167                         AND trans.debtor_no=$customer_id");
 168         }
 169
 170         return db_query($sql." ORDER BY trans_no", "Cannot retreive alloc to transactions");
 171 }
 172
 173
 174 ?>