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 //      Update supplier trans alloc field according to current status of supp_allocations
  52 //
  53 function update_supp_trans_allocation($trans_type, $trans_no)
  54 {
  55         $sql = "UPDATE `".TB_PREF.($trans_type==ST_PURCHORDER ? 'purch_orders' : 'supp_trans')."` trans,
  56                         (SELECT sum(amt) amt from ".TB_PREF."supp_allocations
  57                                 WHERE (trans_type_to=".db_escape($trans_type)." AND trans_no_to=".db_escape($trans_no).")
  58                                 OR (trans_type_from=".db_escape($trans_type)." AND trans_no_from=".db_escape($trans_no).")) allocated
  59                 SET
  60                         trans.alloc=IFNULL(allocated.amt, 0)
  61                 WHERE " . ($trans_type==ST_PURCHORDER ?
  62                           "trans.order_no=".db_escape($trans_no)
  63                         : "trans.type=".db_escape($trans_type)." AND trans.trans_no=".db_escape($trans_no));
  64
  65         db_query($sql, "The supp transaction record could not be modified for the allocation against it");
  66 }
  67
  68
  69 //-------------------------------------------------------------------------------------------------------------
  70
  71 function void_supp_allocations($type, $type_no, $date="")
  72 {
  73         return clear_supp_alloctions($type, $type_no, $date);
  74 }
  75
  76 //-------------------------------------------------------------------------------------------------------------
  77
  78 function clear_supp_alloctions($type, $type_no, $date="")
  79 {
  80         $sql = "UPDATE  ".TB_PREF."supp_allocations ca
  81                                 LEFT JOIN ".TB_PREF."supp_trans paym ON ca.trans_type_from=paym.type AND ca.trans_no_from=paym.trans_no
  82                                 LEFT JOIN ".TB_PREF."supp_trans st ON ca.trans_type_to=st.type AND ca.trans_no_to=st.trans_no
  83                                 LEFT JOIN ".TB_PREF."purch_orders po ON ca.trans_type_to=".ST_PURCHORDER." AND ca.trans_no_to=po.order_no
  84                         SET paym.alloc=paym.alloc - ca.amt,
  85                                 st.alloc=st.alloc -  ca.amt,
  86                                 po.alloc=po.alloc -  ca.amt
  87                         WHERE  (ca.trans_type_from=".db_escape($type)." AND ca.trans_no_from=".db_escape($type_no).")
  88                                 OR (ca.trans_type_to=".db_escape($type)." AND ca.trans_no_to=".db_escape($type_no).")";
  89                 db_query($sql, "could not clear allocation");
  90
  91         // remove any allocations for this transaction
  92         $sql = "DELETE FROM ".TB_PREF."supp_allocations
  93                         WHERE  (trans_type_from=".db_escape($type)." AND trans_no_from=".db_escape($type_no).")
  94                                 OR (trans_type_to=".db_escape($type)." AND trans_no_to=".db_escape($type_no).")";
  95
  96         db_query($sql, "could not void supp transactions for type=$type and trans_no=$type_no");
  97 // is this necessary?
  98 //      if ($date != "")
  99 //              exchange_variation($type, $type_no, $row['trans_type_to'], $row['trans_no_to'], $date,
 100 //                      $row['amt'], PT_SUPPLIER, true);
 101 }
 102 //-------------------------------------------------------------------------------------------------------------
 103
 104 function get_allocatable_from_supp_sql($supplier_id=null, $settled)
 105 {
 106         $sql = "SELECT
 107                 trans.type,
 108                 trans.trans_no,
 109                 IF(trans.supp_reference='',trans.reference,trans.supp_reference) as reference,
 110                 trans.tran_date,
 111                 supplier.supp_name,
 112                 supplier.curr_code,
 113                 ov_amount+ov_gst+ov_discount AS Total,
 114                 trans.alloc,
 115                 trans.due_date,
 116                 trans.supplier_id,
 117                 supplier.address,
 118                 round(abs(ov_amount+ov_gst+ov_discount)-alloc,6) <= 0 AS settled
 119          FROM "
 120                 .TB_PREF."supp_trans as trans, "
 121                 .TB_PREF."suppliers as supplier"
 122         ." WHERE trans.supplier_id=supplier.supplier_id
 123                 AND type IN(".ST_SUPPAYMENT.",".ST_SUPPCREDIT.",".ST_BANKPAYMENT.") AND (trans.ov_amount < 0)";
 124
 125         if (!$settled)
 126                 $sql .= " AND (round(abs(ov_amount+ov_gst+ov_discount)-alloc,6) > 0)";
 127
 128         if ($supplier_id != null)
 129                 $sql .= " AND supplier.supplier_id = ".db_escape($supplier_id);
 130
 131         return $sql;
 132 }
 133
 134 function get_allocatable_purch_orders($supplier_id = null, $trans_no=null, $type=null)
 135 {
 136         $sql = "SELECT
 137                 ".ST_PURCHORDER." as type,
 138                 porder.order_no as trans_no,
 139                 porder.reference,
 140                 porder.ord_date,
 141                 supplier.supp_name AS DebtorName,
 142                 supplier.curr_code,
 143                 total as Total,
 144                 porder.alloc,
 145                 porder.ord_date as due_date,
 146                 supplier.address,
 147                 amt,
 148                 requisition_no as supp_ref
 149                 FROM ".TB_PREF."purch_orders as porder
 150                         LEFT JOIN ".TB_PREF."suppliers as supplier ON porder.supplier_id = supplier.supplier_id
 151                         LEFT JOIN ".TB_PREF."supp_allocations as alloc ON porder.order_no = alloc.trans_no_to AND alloc.trans_type_to=".ST_PURCHORDER."
 152                         LEFT JOIN ".TB_PREF."grn_batch as grn ON porder.order_no = grn.purch_order_no
 153                 WHERE total>0";
 154
 155         if ($trans_no != null and $type != null)
 156         {
 157                 $sql .= " AND alloc.trans_no_from=".db_escape($trans_no)."
 158                                   AND alloc.trans_type_from=".db_escape($type);
 159         }
 160         else
 161         {
 162                 $sql .= " AND round(prep_amount) > 0 AND ISNULL(grn.purch_order_no)"; // only sales orders with prepayment level set and no yet received
 163         }
 164         if ($supplier_id)
 165                 $sql .= " AND porder.supplier_id=".db_escape($supplier_id);
 166
 167         $sql .= " GROUP BY porder.order_no, grn.purch_order_no";
 168
 169         return $sql;
 170 }
 171 //-------------------------------------------------------------------------------------------------------------
 172
 173 function get_allocatable_to_supp_transactions($supplier_id=null, $trans_no=null, $type=null)
 174 {
 175         $sql = "SELECT
 176                 trans.type,
 177                 trans.trans_no,
 178                 IF(trans.supp_reference='',trans.reference,trans.supp_reference) as reference,
 179                 trans.tran_date,
 180                 supplier.supp_name,
 181                 supplier.curr_code,
 182                 ov_amount+ov_gst+ov_discount AS Total,
 183                 trans.alloc,
 184                 trans.due_date,
 185                 trans.supplier_id,
 186                 amt,
 187                 supp_reference
 188
 189          FROM ".TB_PREF."supp_trans as trans
 190                         LEFT JOIN ".TB_PREF."supp_allocations as alloc ON trans.trans_no = alloc.trans_no_to AND trans.type = alloc.trans_type_to,"
 191                         .TB_PREF."suppliers as supplier
 192          WHERE
 193                  trans.supplier_id=supplier.supplier_id";
 194         if ($supplier_id)
 195                 $sql .= " AND supplier.supplier_id=".db_escape($supplier_id);
 196
 197         if ($trans_no != null and $type != null)
 198         {
 199                 $sql .= " AND alloc.trans_no_from=".db_escape($trans_no)."
 200                                   AND alloc.trans_type_from=".db_escape($type);
 201         }
 202         else
 203         {
 204                 $sql .= " AND round(ov_amount+ov_gst+ov_discount-alloc,6) > 0
 205                         AND trans.type NOT IN (".implode(',',array(ST_SUPPAYMENT, ST_BANKPAYMENT)).")";
 206                 $sql .= " GROUP BY type, trans_no";
 207         }
 208
 209         $orders = get_allocatable_purch_orders($supplier_id, $trans_no, $type);
 210         $sql = "($sql ORDER BY trans_no) \nUNION \n($orders)";
 211 //_vd($sql);
 212         return db_query($sql." ORDER BY due_date", "Cannot retreive alloc to transactions");
 213 }
 214 //-------------------------------------------------------------------------------------------------------------
 215
 216 function get_allocatable_from_supp_transactions($supplier_id, $trans_no=null, $type=null)
 217 {
 218         $sql = "SELECT
 219                 trans.type,
 220                 trans.trans_no,
 221                 IF(trans.supp_reference='',trans.reference,trans.supp_reference) as reference,
 222                 trans.tran_date,
 223                 supplier.supp_name,
 224                 supplier.curr_code,
 225                 ov_amount+ov_gst+ov_discount AS Total,
 226                 trans.alloc,
 227                 trans.due_date,
 228                 trans.supplier_id,
 229                 supplier.address,
 230                 amt,
 231                 supp_reference
 232          FROM  ".TB_PREF."supp_trans as trans,"
 233                         .TB_PREF."suppliers as supplier,"
 234                         .TB_PREF."supp_allocations as alloc
 235          WHERE trans.supplier_id=supplier.supplier_id
 236                         AND trans.trans_no = alloc.trans_no_from
 237                         AND trans.type = alloc.trans_type_from";
 238
 239         if ($trans_no != null and $type != null)
 240         {
 241                 $sql .= " AND alloc.trans_no_to=".db_escape($trans_no)."
 242                                   AND alloc.trans_type_to=".db_escape($type);
 243         }
 244         else
 245         {
 246                 $sql .= " AND round(ABS(ov_amount+ov_gst+ov_discount)-alloc,6) > 0
 247                         AND trans.type NOT IN (".implode(',',array(ST_SUPPAYMENT,ST_BANKPAYMENT)).")";
 248                 $sql .= " GROUP BY type, trans_no";
 249         }
 250
 251         return db_query($sql." ORDER BY due_date", "Cannot retreive alloc to transactions");
 252 }
 253
 254
 255 function get_sql_for_supplier_allocation_inquiry($from, $to, $filter, $supplier_id, $all=false)
 256 {
 257         $date_after = date2sql($from);
 258         $date_to = date2sql($to);
 259
 260     $sql = "SELECT
 261                 trans.type,
 262                 trans.trans_no,
 263                 trans.reference,
 264                 supplier.supp_name,
 265                 trans.supp_reference,
 266         trans.tran_date,
 267                 trans.due_date,
 268                 supplier.curr_code,
 269         (trans.ov_amount + trans.ov_gst  + trans.ov_discount) AS TotalAmount,
 270                 trans.alloc AS Allocated,
 271                 ((trans.type = ".ST_SUPPINVOICE." OR trans.type = ".ST_SUPPCREDIT.") AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue,
 272                 trans.supplier_id
 273         FROM "
 274                         .TB_PREF."supp_trans as trans, "
 275                         .TB_PREF."suppliers as supplier
 276         WHERE supplier.supplier_id = trans.supplier_id
 277         AND trans.tran_date >= '$date_after'
 278         AND trans.tran_date <= '$date_to'";
 279
 280         if ($supplier_id != ALL_TEXT)
 281                 $sql .= " AND trans.supplier_id = ".db_escape($supplier_id);
 282
 283         if ($filter != ALL_TEXT)
 284         {
 285                 if (($filter == '1') || ($filter == '2'))
 286                 {
 287                         $sql .= " AND trans.type = ".ST_SUPPINVOICE." ";
 288                 }
 289                 elseif ($filter == '3')
 290                 {
 291                         $sql .= " AND trans.type = ".ST_SUPPAYMENT." ";
 292                 }
 293                 elseif (($filter == '4') || ($filter == '5'))
 294                 {
 295                         $sql .= " AND trans.type = ".ST_SUPPCREDIT." ";
 296                 }
 297
 298                 if (($filter == '2') || ($filter == '5'))
 299                 {
 300                         $today =  date2sql(Today());
 301                         $sql .= " AND trans.due_date < '$today' ";
 302                 }
 303         }
 304
 305         if (!$all)
 306         {
 307                 $sql .= " AND (round(abs(ov_amount + ov_gst + ov_discount) - alloc,6) != 0) ";
 308         }
 309         return $sql;
 310 }
 311 ?>