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(), ".db_escape($trans_type_from).", ".db_escape($trans_no_from).", ".db_escape($trans_no_to)
  21                 .", ".db_escape($trans_type_to).")";
  22
  23         db_query($sql, "A customer allocation could not be added to the database");
  24 }
  25
  26 //----------------------------------------------------------------------------------------
  27
  28
  29 function delete_cust_allocation($trans_id)
  30 {
  31         $sql = "DELETE FROM ".TB_PREF."cust_allocations WHERE id = ".db_escape($trans_id);
  32         return db_query($sql, "The existing allocation $trans_id could not be deleted");
  33 }
  34
  35 //----------------------------------------------------------------------------------------
  36
  37 function update_debtor_trans_allocation($trans_type, $trans_no, $alloc)
  38 {
  39         $sql = "UPDATE ".TB_PREF."debtor_trans SET alloc = alloc + $alloc
  40                 WHERE type=".db_escape($trans_type)." AND trans_no = ".db_escape($trans_no);
  41         db_query($sql, "The debtor transaction record could not be modified for the allocation against it");
  42 }
  43
  44 //-------------------------------------------------------------------------------------------------------------
  45
  46 function void_cust_allocations($type, $type_no, $date="")
  47 {
  48         return clear_cust_alloctions($type, $type_no, $date);
  49 }
  50
  51 //-------------------------------------------------------------------------------------------------------------
  52
  53 function clear_cust_alloctions($type, $type_no, $date="")
  54 {
  55         // clear any allocations for this transaction
  56         $sql = "SELECT * FROM ".TB_PREF."cust_allocations
  57                 WHERE (trans_type_from=".db_escape($type)." AND trans_no_from=".db_escape($type_no).")
  58                 OR (trans_type_to=".db_escape($type)." AND trans_no_to=".db_escape($type_no).")";
  59         $result = db_query($sql, "could not void debtor transactions for type=$type and trans_no=$type_no");
  60
  61         while ($row = db_fetch($result))
  62         {
  63                 $sql = "UPDATE ".TB_PREF."debtor_trans SET alloc=alloc - " . $row['amt'] . "
  64                         WHERE (type= " . $row['trans_type_from'] . " AND trans_no=" . $row['trans_no_from'] . ")
  65                         OR (type=" . $row['trans_type_to'] . " AND trans_no=" . $row['trans_no_to'] . ")";
  66                 db_query($sql, "could not clear allocation");
  67                 // 2008-09-20 Joe Hunt
  68                 if ($date != "")
  69                         exchange_variation($type, $type_no, $row['trans_type_to'], $row['trans_no_to'], $date,
  70                                 $row['amt'], PT_CUSTOMER, true);
  71                 //////////////////////
  72         }
  73
  74
  75         // remove any allocations for this transaction
  76         $sql = "DELETE FROM ".TB_PREF."cust_allocations
  77                 WHERE (trans_type_from=".db_escape($type)." AND trans_no_from=".db_escape($type_no).")
  78                 OR (trans_type_to=".db_escape($type)." AND trans_no_to=".db_escape($type_no).")";
  79
  80         db_query($sql, "could not void debtor transactions for type=$type and trans_no=$type_no");
  81 }
  82 //----------------------------------------------------------------------------------------
  83
  84 function get_alloc_trans_sql($extra_fields=null, $extra_conditions=null, $extra_tables=null)
  85 {
  86         $sql = "SELECT
  87                 trans.type,
  88                 trans.trans_no,
  89                 trans.reference,
  90                 trans.tran_date,
  91                 debtor.name AS DebtorName,
  92                 debtor.curr_code,
  93                 ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total,
  94                 trans.alloc,
  95                 trans.due_date,
  96                 debtor.address,
  97                 trans.version ";
  98
  99         if ($extra_fields)
 100                 $sql .= ", $extra_fields ";
 101
 102         $sql .= " FROM ".TB_PREF."debtor_trans as trans, "
 103                                 .TB_PREF."debtors_master as debtor";
 104         if ($extra_tables)
 105                 $sql .= ",$extra_tables ";
 106
 107         $sql .= " WHERE trans.debtor_no=debtor.debtor_no";
 108
 109         if ($extra_conditions)
 110                 $sql .= " AND $extra_conditions ";
 111
 112         return $sql;
 113 }
 114
 115
 116 //-------------------------------------------------------------------------------------------------------------
 117
 118 function get_allocatable_from_cust_sql($customer_id, $settled)
 119 {
 120         $settled_sql = "";
 121         if (!$settled)
 122         {
 123                 $settled_sql = " AND (round(ov_amount+ov_gst+ov_freight+ov_freight_tax-ov_discount-alloc,6) > 0)";
 124         }
 125         $cust_sql = "";
 126         if ($customer_id != null)
 127                 $cust_sql = " AND trans.debtor_no = ".db_escape($customer_id);
 128
 129         $sql = get_alloc_trans_sql("round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) <= 0 AS settled",
 130                 "(type=".ST_CUSTPAYMENT." OR type=".ST_CUSTCREDIT." OR type=".ST_BANKDEPOSIT.") AND (trans.ov_amount > 0) " . $settled_sql . $cust_sql);
 131
 132         return $sql;
 133 }
 134
 135 //-------------------------------------------------------------------------------------------------------------
 136
 137 function get_allocatable_to_cust_transactions($customer_id, $trans_no=null, $type=null)
 138 {
 139         if ($trans_no != null and $type != null)
 140         {
 141                 $sql = get_alloc_trans_sql("amt", "trans.trans_no = alloc.trans_no_to
 142                         AND trans.type = alloc.trans_type_to
 143                         AND alloc.trans_no_from=".db_escape($trans_no)."
 144                         AND alloc.trans_type_from=".db_escape($type)."
 145                         AND trans.debtor_no=".db_escape($customer_id),
 146                         "".TB_PREF."cust_allocations as alloc");
 147         }
 148         else
 149         {
 150                 $sql = get_alloc_trans_sql(null, "round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) > 0
 151                         AND trans.type <> " . ST_CUSTPAYMENT . "
 152                         AND trans.type <> " . ST_BANKDEPOSIT . "
 153                         AND trans.type <> " . ST_CUSTCREDIT . "
 154                         AND trans.type <> " . ST_CUSTDELIVERY . "
 155                         AND trans.debtor_no=".db_escape($customer_id));
 156         }
 157
 158         return db_query($sql." ORDER BY trans_no", "Cannot retreive alloc to transactions");
 159 }
 160
 161 //-------------------------------------------------------------------------------------------------------------
 162
 163 function get_allocatable_from_cust_transactions($customer_id, $trans_no=null, $type=null)
 164 {
 165         if ($trans_no != null and $type != null)
 166         {
 167                 $sql = get_alloc_trans_sql("amt", "trans.trans_no = alloc.trans_no_from
 168                         AND trans.type = alloc.trans_type_from
 169                         AND alloc.trans_no_to=".db_escape($trans_no)."
 170                         AND alloc.trans_type_to=".db_escape($type)."
 171                         AND trans.debtor_no=".db_escape($customer_id),
 172                         "".TB_PREF."cust_allocations as alloc");
 173         }
 174         else
 175         {
 176                 $sql = get_alloc_trans_sql(null, "round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) > 0
 177                         AND trans.type <> " . ST_CUSTPAYMENT . "
 178                         AND trans.type <> " . ST_BANKDEPOSIT . "
 179                         AND trans.type <> " . ST_CUSTCREDIT . "
 180                         AND trans.type <> " . ST_CUSTDELIVERY . "
 181                         AND trans.debtor_no=".db_escape($customer_id));
 182         }
 183
 184         return db_query($sql." ORDER BY trans_no", "Cannot retreive alloc to transactions");
 185 }
 186
 187 function get_sql_for_customer_allocation_inquiry()
 188 {
 189         $data_after = date2sql($_POST['TransAfterDate']);
 190         $date_to = date2sql($_POST['TransToDate']);
 191
 192         $sql = "SELECT
 193                 trans.type,
 194                 trans.trans_no,
 195                 trans.reference,
 196                 trans.order_,
 197                 trans.tran_date,
 198                 trans.due_date,
 199                 debtor.name,
 200                 debtor.curr_code,
 201         (trans.ov_amount + trans.ov_gst + trans.ov_freight
 202                         + trans.ov_freight_tax + trans.ov_discount)     AS TotalAmount,
 203                 trans.alloc AS Allocated,
 204                 ((trans.type = ".ST_SALESINVOICE.")
 205                         AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue,
 206                 trans.debtor_no
 207         FROM "
 208                         .TB_PREF."debtor_trans as trans, "
 209                         .TB_PREF."debtors_master as debtor
 210         WHERE debtor.debtor_no = trans.debtor_no
 211                         AND (trans.ov_amount + trans.ov_gst + trans.ov_freight
 212                                 + trans.ov_freight_tax + trans.ov_discount != 0)
 213                 AND trans.tran_date >= '$data_after'
 214                 AND trans.tran_date <= '$date_to'";
 215
 216         if ($_POST['customer_id'] != ALL_TEXT)
 217                 $sql .= " AND trans.debtor_no = ".db_escape($_POST['customer_id']);
 218
 219         if (isset($_POST['filterType']) && $_POST['filterType'] != ALL_TEXT)
 220         {
 221                 if ($_POST['filterType'] == '1' || $_POST['filterType'] == '2')
 222                 {
 223                         $sql .= " AND trans.type = ".ST_SALESINVOICE." ";
 224                 }
 225                 elseif ($_POST['filterType'] == '3')
 226                 {
 227                         $sql .= " AND trans.type = " . ST_CUSTPAYMENT;
 228                 }
 229                 elseif ($_POST['filterType'] == '4')
 230                 {
 231                         $sql .= " AND trans.type = ".ST_CUSTCREDIT." ";
 232                 }
 233
 234         if ($_POST['filterType'] == '2')
 235         {
 236                 $today =  date2sql(Today());
 237                 $sql .= " AND trans.due_date < '$today'
 238                                 AND (round(abs(trans.ov_amount + "
 239                                 ."trans.ov_gst + trans.ov_freight + "
 240                                 ."trans.ov_freight_tax + trans.ov_discount) - trans.alloc,6) > 0) ";
 241         }
 242         }
 243         else
 244         {
 245             $sql .= " AND trans.type <> ".ST_CUSTDELIVERY." ";
 246         }
 247
 248
 249         if (!check_value('showSettled'))
 250         {
 251                 $sql .= " AND (round(abs(trans.ov_amount + trans.ov_gst + "
 252                 ."trans.ov_freight + trans.ov_freight_tax + "
 253                 ."trans.ov_discount) - trans.alloc,6) != 0) ";
 254         }
 255         return $sql;
 256 }
 257
 258 function credit_sales_invoice_allocate($invoice_no, $credit_no, $amount, $date)
 259 {
 260
 261         $sql = "SELECT ov_freight+ov_gst+ov_amount+ov_freight_tax as total, alloc FROM ".TB_PREF."debtor_trans
 262                 WHERE (`type`=".ST_SALESINVOICE." AND trans_no=".db_escape($invoice_no).")";
 263         $result = db_query($sql, "can't retrieve invoice totals");
 264         $invoice = db_fetch($result);
 265         $free = $invoice['total'] - $invoice['alloc'];
 266
 267         if ($free < $amount) {
 268          // if there is not enough unallocated amount - remove some payment allocations
 269                 $sql = "SELECT * FROM ".TB_PREF."cust_allocations
 270                         WHERE (trans_type_to=".ST_SALESINVOICE." AND trans_no_to=".db_escape($invoice_no).")
 271                         AND trans_type_from <> ".ST_CUSTCREDIT;
 272                 $result = db_query($sql, "can't retrieve invoice allocations");
 273
 274                 while(($free < $amount) && ($alloc = db_fetch($result))) {
 275                         $unalloc = min($alloc['amt'], $amount-$free);
 276                         update_debtor_trans_allocation($alloc['trans_type_to'], $alloc['trans_no_to'],
 277                                 -$unalloc);
 278                         update_debtor_trans_allocation($alloc['trans_type_from'], $alloc['trans_no_from'],
 279                                 -$unalloc);
 280
 281                         delete_cust_allocation($alloc['id']);
 282                         if ($unalloc < $alloc['amt'])
 283                                 add_cust_allocation($alloc['amt']-$unalloc, $alloc['trans_type_from'],
 284                                         $alloc['trans_no_from'], ST_SALESINVOICE, $invoice_no);
 285
 286                         $free += $unalloc;
 287                 }
 288         }
 289         if ($free < $amount) {
 290                 // this should never happen unless sparse credit notices were allocated to
 291                 // the invoice, or summarized freight costs on credit notes is more than those on invoice.
 292                 display_error(_("Unsuspected overallocation happened due to sparse credit notes exists for this invoice.
 293  Check all credit notes allocated to this invoice for summarized freight charges."));
 294                 return false;
 295         }
 296         update_debtor_trans_allocation(ST_SALESINVOICE, $invoice_no, $amount);
 297         update_debtor_trans_allocation(ST_CUSTCREDIT, $credit_no, $amount);
 298         add_cust_allocation($amount, ST_CUSTCREDIT, $credit_no, ST_SALESINVOICE, $invoice_no);
 299
 300         exchange_variation(ST_CUSTCREDIT, $credit_no, ST_SALESINVOICE, $invoice_no, $date,
 301                 $amount, PT_CUSTOMER);
 302         return true;
 303 }
 304
 305 ?>