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 get_debtor_trans_allocation_balance($trans_type, $trans_no)
  38 {
  39
  40         $sql = "SELECT (ov_amount+ov_gst+ov_freight+ov_freight_tax-ov_discount-alloc) AS BalToAllocate
  41                 FROM ".TB_PREF."debtor_trans WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type);
  42         $result = db_query($sql,"calculate the allocation");
  43         $myrow = db_fetch_row($result);
  44
  45         return $myrow[0];
  46 }
  47
  48 //----------------------------------------------------------------------------------------
  49
  50 function update_debtor_trans_allocation($trans_type, $trans_no, $alloc)
  51 {
  52         if ($trans_type == ST_SALESORDER)
  53                 return;
  54         else
  55                 $sql = "UPDATE ".TB_PREF."debtor_trans SET alloc = alloc + $alloc
  56                         WHERE type=".db_escape($trans_type)." AND trans_no = ".db_escape($trans_no);
  57         db_query($sql, "The debtor transaction record could not be modified for the allocation against it");
  58 }
  59
  60 //-------------------------------------------------------------------------------------------------------------
  61
  62 function void_cust_allocations($type, $type_no, $date="")
  63 {
  64         return clear_cust_alloctions($type, $type_no, $date);
  65 }
  66
  67 //-------------------------------------------------------------------------------------------------------------
  68
  69 function clear_cust_alloctions($type, $type_no, $date="")
  70 {
  71         if ($type == ST_SALESORDER)
  72                 return;
  73         // clear any allocations for this transaction
  74         $sql = "SELECT * FROM ".TB_PREF."cust_allocations
  75                 WHERE (trans_type_from=".db_escape($type)." AND trans_no_from=".db_escape($type_no).")
  76                 OR (trans_type_to=".db_escape($type)." AND trans_no_to=".db_escape($type_no).")";
  77         $result = db_query($sql, "could not void debtor transactions for type=$type and trans_no=$type_no");
  78
  79         while ($row = db_fetch($result))
  80         {
  81                 $sql = "UPDATE ".TB_PREF."debtor_trans SET alloc=alloc - " . $row['amt'] . "
  82                         WHERE (type= " . $row['trans_type_from'] . " AND trans_no=" . $row['trans_no_from'] . ")
  83                         OR (type=" . $row['trans_type_to'] . " AND trans_no=" . $row['trans_no_to'] . ")";
  84                 db_query($sql, "could not clear allocation");
  85                 // 2008-09-20 Joe Hunt
  86                 if (($date != "") && ($row['trans_type_to'] != ST_SALESORDER))
  87                         exchange_variation($type, $type_no, $row['trans_type_to'], $row['trans_no_to'], $date,
  88                                 $row['amt'], PT_CUSTOMER, true);
  89                 //////////////////////
  90         }
  91
  92
  93         // remove any allocations for this transaction
  94         $sql = "DELETE FROM ".TB_PREF."cust_allocations
  95                 WHERE (trans_type_from=".db_escape($type)." AND trans_no_from=".db_escape($type_no).")
  96                 OR (trans_type_to=".db_escape($type)." AND trans_no_to=".db_escape($type_no).")";
  97
  98         db_query($sql, "could not void debtor transactions for type=$type and trans_no=$type_no");
  99 }
 100 //----------------------------------------------------------------------------------------
 101
 102 function get_alloc_trans_sql($extra_fields=null, $extra_conditions=null, $extra_tables=null)
 103 {
 104         $sql = "SELECT
 105                 trans.type,
 106                 trans.trans_no,
 107                 trans.reference,
 108                 trans.tran_date,
 109                 debtor.name AS DebtorName,
 110                 debtor.curr_code,
 111                 ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total,
 112                 trans.alloc,
 113                 trans.due_date,
 114                 debtor.address,
 115                 trans.version ";
 116
 117         if ($extra_fields)
 118                 $sql .= ", $extra_fields ";
 119
 120         $sql .= " FROM ".TB_PREF."debtor_trans as trans, "
 121                                 .TB_PREF."debtors_master as debtor";
 122         if ($extra_tables)
 123                 $sql .= ",$extra_tables ";
 124
 125         $sql .= " WHERE trans.debtor_no=debtor.debtor_no";
 126
 127         if ($extra_conditions)
 128                 $sql .= " AND $extra_conditions ";
 129
 130         return $sql;
 131 }
 132
 133 //----------------------------------------------------------------------------------------
 134
 135 function get_alloc_order_sql($extra_fields=null, $extra_conditions=null, $extra_tables=null)
 136 {
 137         $sql = "SELECT
 138                 so.trans_type as type,
 139                 so.order_no as trans_no,
 140                 so.reference,
 141                 so.ord_date as tran_date,
 142                 debtor.name AS DebtorName,
 143                 debtor.curr_code,
 144                 Sum(line.unit_price*line.quantity*(1-line.discount_percent))+freight_cost as Total,
 145                 Sum(alloc.amt) as alloc,
 146                 so.delivery_date as due_date,
 147                 debtor.address,
 148                 so.version,
 149
 150                 Sum(line.qty_sent) AS TotDelivered,
 151                 Sum(line.quantity) AS TotQuantity";
 152
 153         if ($extra_fields)
 154                 $sql .= ", $extra_fields ";
 155
 156         $sql .= " FROM ".TB_PREF."sales_orders as so
 157                                 LEFT JOIN ".TB_PREF."cust_allocations as alloc
 158                                         ON alloc.trans_type_to=".ST_SALESORDER
 159                                         ." AND so.order_no = alloc.trans_no_to,"
 160                                 .TB_PREF."sales_order_details as line,"
 161                                 .TB_PREF."debtors_master as debtor";
 162
 163         if ($extra_tables)
 164                 $sql .= ",$extra_tables ";
 165
 166         $sql .= " WHERE so.debtor_no=debtor.debtor_no
 167                                 AND so.trans_type = ".ST_SALESORDER."
 168                                 AND so.order_no = line.order_no
 169                                 AND so.trans_type = line.trans_type";
 170
 171         if ($extra_conditions)
 172                 $sql .= " AND $extra_conditions ";
 173
 174         $sql .= " GROUP BY so.order_no,
 175                                 so.debtor_no,
 176                                 so.branch_code,
 177                                 so.customer_ref,
 178                                 so.ord_date,
 179                                 so.deliver_to";
 180         return $sql;
 181 }
 182
 183 //-------------------------------------------------------------------------------------------------------------
 184
 185 function get_allocatable_sales_orders($customer_id, $trans_no=null, $type=null)
 186 {
 187         if ($trans_no != null and $type != null)
 188         {
 189                 $sql = get_alloc_order_sql("alloc.amt", "alloc.trans_no_from=$trans_no
 190                         AND alloc.trans_type_from=$type
 191                         AND so.debtor_no=".db_escape($customer_id));
 192         }
 193         else
 194         {
 195                 $sql = get_alloc_order_sql(null, "so.debtor_no=".db_escape($customer_id));
 196         }
 197 //display_notification($sql);
 198         return db_query($sql." ORDER BY so.order_no", "Cannot retreive alloc to orders");
 199 }
 200
 201
 202 //-------------------------------------------------------------------------------------------------------------
 203
 204 function get_allocatable_from_cust_sql($customer_id, $settled)
 205 {
 206         $settled_sql = "";
 207         if (!$settled)
 208         {
 209                 $settled_sql = " AND (round(ov_amount+ov_gst+ov_freight+ov_freight_tax-ov_discount-alloc,6) > 0)";
 210         }
 211         $cust_sql = "";
 212         if ($customer_id != null)
 213                 $cust_sql = " AND trans.debtor_no = ".db_escape($customer_id);
 214
 215         $sql = get_alloc_trans_sql("round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) <= 0 AS settled",
 216                 "(type=".ST_CUSTPAYMENT." OR type=".ST_CUSTCREDIT." OR type=".ST_BANKDEPOSIT.") AND (trans.ov_amount > 0) " . $settled_sql . $cust_sql);
 217
 218         return $sql;
 219 }
 220
 221 //-------------------------------------------------------------------------------------------------------------
 222
 223 function get_allocatable_to_cust_transactions($customer_id, $trans_no=null, $type=null)
 224 {
 225         if ($trans_no != null and $type != null)
 226         {
 227                 $sql = get_alloc_trans_sql("amt", "trans.trans_no = alloc.trans_no_to
 228                         AND trans.type = alloc.trans_type_to
 229                         AND alloc.trans_no_from=$trans_no
 230                         AND alloc.trans_type_from=$type
 231                         AND trans.debtor_no=".db_escape($customer_id),
 232                         "".TB_PREF."cust_allocations as alloc");
 233         }
 234         else
 235         {
 236                 $sql = get_alloc_trans_sql(null, "round(ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount-alloc,6) > 0
 237                         AND trans.type <> " . ST_CUSTPAYMENT . "
 238                         AND trans.type <> " . ST_BANKDEPOSIT . "
 239                         AND trans.type <> " . ST_CUSTCREDIT . "
 240                         AND trans.type <> " . ST_CUSTDELIVERY . "
 241                         AND trans.debtor_no=".db_escape($customer_id));
 242         }
 243
 244         return db_query($sql." ORDER BY trans_no", "Cannot retreive alloc to transactions");
 245 }
 246
 247 function get_sql_for_customer_allocation_inquiry()
 248 {
 249         $data_after = date2sql($_POST['TransAfterDate']);
 250         $date_to = date2sql($_POST['TransToDate']);
 251
 252         $sql = "SELECT
 253                 trans.type,
 254                 trans.trans_no,
 255                 trans.reference,
 256                 trans.order_,
 257                 trans.tran_date,
 258                 trans.due_date,
 259                 debtor.name,
 260                 debtor.curr_code,
 261         (trans.ov_amount + trans.ov_gst + trans.ov_freight
 262                         + trans.ov_freight_tax + trans.ov_discount)     AS TotalAmount,
 263                 trans.alloc AS Allocated,
 264                 ((trans.type = ".ST_SALESINVOICE.")
 265                         AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue
 266         FROM "
 267                         .TB_PREF."debtor_trans as trans, "
 268                         .TB_PREF."debtors_master as debtor
 269         WHERE debtor.debtor_no = trans.debtor_no
 270                         AND (trans.ov_amount + trans.ov_gst + trans.ov_freight
 271                                 + trans.ov_freight_tax + trans.ov_discount != 0)
 272                 AND trans.tran_date >= '$data_after'
 273                 AND trans.tran_date <= '$date_to'";
 274
 275         if ($_POST['customer_id'] != ALL_TEXT)
 276                 $sql .= " AND trans.debtor_no = ".db_escape($_POST['customer_id']);
 277
 278         if (isset($_POST['filterType']) && $_POST['filterType'] != ALL_TEXT)
 279         {
 280                 if ($_POST['filterType'] == '1' || $_POST['filterType'] == '2')
 281                 {
 282                         $sql .= " AND trans.type = ".ST_SALESINVOICE." ";
 283                 }
 284                 elseif ($_POST['filterType'] == '3')
 285                 {
 286                         $sql .= " AND trans.type = " . ST_CUSTPAYMENT;
 287                 }
 288                 elseif ($_POST['filterType'] == '4')
 289                 {
 290                         $sql .= " AND trans.type = ".ST_CUSTCREDIT." ";
 291                 }
 292
 293         if ($_POST['filterType'] == '2')
 294         {
 295                 $today =  date2sql(Today());
 296                 $sql .= " AND trans.due_date < '$today'
 297                                 AND (round(abs(trans.ov_amount + "
 298                                 ."trans.ov_gst + trans.ov_freight + "
 299                                 ."trans.ov_freight_tax + trans.ov_discount) - trans.alloc,6) > 0) ";
 300         }
 301         }
 302         else
 303         {
 304             $sql .= " AND trans.type <> ".ST_CUSTDELIVERY." ";
 305         }
 306
 307
 308         if (!check_value('showSettled'))
 309         {
 310                 $sql .= " AND (round(abs(trans.ov_amount + trans.ov_gst + "
 311                 ."trans.ov_freight + trans.ov_freight_tax + "
 312                 ."trans.ov_discount) - trans.alloc,6) != 0) ";
 313         }
 314         return $sql;
 315 }
 316 ?>