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