includes/db/allocations_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 // Functions below are unified interface to allocations (should supersede currently separate cust/supp allocations in a future)
  14 //
  15 //----------------------------------------------------------------------------------------
  16 //
  17 //      Returns table of payments currently allocated to selected transaction.
  18 //
  19 function get_payments_for($trans_no, $trans_type)
  20 {
  21         $allocations = array();
  22
  23         $sql = "(SELECT * FROM ".TB_PREF."cust_allocations
  24                         WHERE (trans_type_to=".db_escape($trans_type)." AND trans_no_to=".db_escape($trans_no)."))
  25                 UNION
  26                         (SELECT * FROM ".TB_PREF."supp_allocations
  27                         WHERE (trans_type_to=".db_escape($trans_type)." AND trans_no_to=".db_escape($trans_no)."))";
  28         $result = db_query($sql, "error reading current allocations");
  29
  30         while($dat = db_fetch($result))
  31         {
  32                 $allocations[] = $dat;
  33         }
  34
  35         return $allocations;
  36 }
  37
  38 //
  39 //      Unified inteface to (re)allocate payments to supp/cust/transaction
  40 //
  41 function allocate_payment($type_from, $no_from, $type_to, $no_to, $amount, $date)
  42 {
  43         $date = date2sql($date); // FIXME types
  44         if (in_array($type_to, array(ST_SALESORDER, ST_CUSTCREDIT, ST_CUSTDELIVERY, ST_SALESINVOICE)))
  45         {
  46                 $allocations = 'cust_allocations';
  47                 $payments='debtor_trans';
  48                 $transactions = $type_to == ST_SALESORDER ? 'sales_orders' : 'debtor_trans';
  49                 $where = $type_to == ST_SALESORDER ? "order_no=".db_escape($no_to) : "type=".db_escape($type_to)." AND trans_no=".db_escape($no_to);
  50         }
  51         else
  52         {
  53                 $allocations = 'supp_allocations';
  54                 $payments='supp_trans';
  55                 $transactions = $type_to == ST_PURCHORDER ? 'purch_orders' : 'supp_trans';
  56                 $where = $type_to == ST_PURCHORDER ? "order_no=".db_escape($no_to) : "type=".db_escape($type_to)." AND trans_no=".db_escape($no_to);
  57         }
  58
  59         // add/update/delete allocation
  60         if (floatcmp($amount, 0) != 0)
  61         {
  62                 $sql = "REPLACE ".TB_PREF.$allocations." SET amt = ". db_escape($amount).",date_alloc='$date'"
  63                         .",trans_type_from=".db_escape($type_from).",trans_no_from=".db_escape($no_from)
  64                         .",trans_type_to=".db_escape($type_to).",trans_no_to=".db_escape($no_to);
  65         }
  66         else {
  67                 $sql = "DELETE FROM ".TB_PREF.$allocations."
  68                         WHERE trans_type_from=".db_escape($type_from)." AND trans_no_from=".db_escape($no_from)
  69                 ." AND trans_type_to=".db_escape($type_to)." AND trans_no_to=".db_escape($no_to);
  70         }
  71         db_query($sql, "The existing allocations could not be updated");
  72
  73         // referesh allocation summaries
  74         $sql = "UPDATE ".TB_PREF.$transactions." trans,
  75                                 (SELECT SUM(amt) amt FROM ".TB_PREF.$allocations." WHERE trans_type_to=".db_escape($type_to)." AND trans_no_to=".db_escape($no_to).") paym
  76                          SET alloc = paym.amt
  77                          WHERE $where";
  78         db_query($sql, "The transaction allocations could not be updated");
  79
  80         $sql = "UPDATE ".TB_PREF.$payments." trans,
  81                                 (SELECT SUM(amt) amt FROM ".TB_PREF.$allocations." WHERE trans_type_from=".db_escape($type_from)." AND trans_no_from=".db_escape($no_from).") paym
  82                          SET alloc = paym.amt
  83                          WHERE type=".db_escape($type_from)." AND trans_no=".db_escape($no_from);
  84
  85         return db_query($sql, "The payment allocations could not be updated");
  86 }
  87
  88 //----------------------------------------------------------------------------------------
  89 //
  90 //      Reallocates allocations to selected transaction.
  91 //  $allocs is table of payments which should be reallocated
  92 //  $free_remainder should be true if old allacations should be freed when not allocated to new transaction.
  93 //
  94 function reallocate_payments($trans_no, $trans_type, $alloc_date, $max_alloc, $allocs, $free_remainder=true)
  95 {
  96
  97         foreach($allocs as $alloc)
  98         {
  99                 $amount = min($alloc['amt'], $max_alloc);
 100                 $remainder = $alloc['amt'] - $amount;
 101
 102                 $max_alloc = floatcmp($max_alloc, $amount) > 0 ? $max_alloc-$amount : 0;
 103
 104                 $same_to =  $trans_type == $alloc['trans_type_to'] && $trans_no == $alloc['trans_no_to'];
 105                 if (!$same_to || ($remainder > 0))
 106                 {
 107                         allocate_payment($alloc['trans_type_from'], $alloc['trans_no_from'], $trans_type, $trans_no, $amount, $alloc_date);
 108                 }
 109                 if (!$same_to && ($remainder > 0 || $free_remainder))
 110                 {
 111                         allocate_payment($alloc['trans_type_from'], $alloc['trans_no_from'],
 112                                 $alloc['trans_type_to'], $alloc['trans_no_to'], $free_remainder ? 0 : $remainder, $alloc_date);
 113                 }
 114                 if (!$free_remainder && $max_alloc==0)
 115                         break;
 116         }
 117 }