purchasing/includes/db/grn_db.inc

   1 <?php
   2
   3 //-------------------------------------------------------------------------------------------------------------
   4
   5 function add_grn(&$po, $date_, $reference, $location)
   6 {
   7         begin_transaction();
   8
   9         $grn = add_grn_batch($po->order_no, $po->supplier_id, $reference, $location, $date_);
  10
  11         foreach ($po->line_items as $order_line)
  12         {
  13
  14                 if ($order_line->receive_qty != 0 && $order_line->receive_qty != "" && isset($order_line->receive_qty))
  15                 {
  16
  17                         /*Update sales_order_details for the new quantity received and the standard cost used for postings to GL and recorded in the stock movements for FIFO/LIFO stocks valuations*/
  18
  19                         if ($order_line->qty_received == 0)
  20                         {
  21                                 /*This must be the first receipt of goods against this line */
  22                            /*Need to get the standard cost as it is now so we can process GL jorunals later*/
  23                            $order_line->standard_cost = get_standard_cost($order_line->stock_id);
  24                         }
  25
  26                         /*Need to insert a grn item */
  27
  28                         $grn_item = add_grn_detail_item($grn, $order_line->po_detail_rec,
  29                                 $order_line->stock_id, $order_line->item_description,
  30                                 $order_line->standard_cost,     $order_line->receive_qty);
  31
  32                         /* Update location stock records - NB  a po cannot be entered for a service/kit parts */
  33
  34             add_stock_move(25, $order_line->stock_id, $grn, $location, $date_, "",
  35                 $order_line->receive_qty, $order_line->standard_cost,
  36                 $po->supplier_id, 1, $order_line->price);
  37
  38                 } /*quantity received is != 0 */
  39         } /*end of order_line loop */
  40
  41         add_forms_for_sys_type(25, $grn, $location);
  42
  43         references::save_last($reference, 25);
  44
  45         commit_transaction();
  46
  47         return $grn;
  48 }
  49
  50 //----------------------------------------------------------------------------------------
  51
  52 function add_grn_batch($po_number, $supplier_id, $reference, $location, $date_)
  53 {
  54         $date = date2sql($date_);
  55
  56         $sql = "INSERT INTO ".TB_PREF."grn_batch (purch_order_no, delivery_date, supplier_id, reference, loc_code)
  57                         VALUES ($po_number, '$date', '$supplier_id', '$reference', '$location')";
  58
  59         db_query($sql, "A grn batch record could not be inserted.");
  60
  61         return db_insert_id();
  62 }
  63
  64 //-------------------------------------------------------------------------------------------------------------
  65
  66 function add_grn_detail_item($grn_batch_id, $po_detail_item, $item_code, $description, $standard_unit_cost,
  67         $quantity_received)
  68 {
  69         $sql = "UPDATE ".TB_PREF."purch_order_details
  70         SET quantity_received = quantity_received + $quantity_received,
  71         std_cost_unit=$standard_unit_cost
  72         WHERE po_detail_item = $po_detail_item";
  73
  74         db_query($sql, "a purchase order details record could not be updated. This receipt of goods has not been processed ");
  75
  76         $sql = "INSERT INTO ".TB_PREF."grn_items (grn_batch_id, po_detail_item, item_code, description, qty_recd)
  77                 VALUES ($grn_batch_id, $po_detail_item, '$item_code', '$description', $quantity_received)";
  78
  79         db_query($sql, "A GRN detail item could not be inserted.");
  80
  81         return db_insert_id();
  82 }
  83
  84 //----------------------------------------------------------------------------------------
  85
  86 function get_grn_items($grn_batch_id=0, $supplier_id="", $outstanding_only=false,
  87         $is_invoiced_only=false)
  88 {
  89     $sql = "SELECT ".TB_PREF."grn_batch.*, ".TB_PREF."grn_items.*, ".TB_PREF."purch_order_details.unit_price,
  90                 ".TB_PREF."purch_order_details.std_cost_unit, units
  91         FROM ".TB_PREF."grn_batch, ".TB_PREF."grn_items, ".TB_PREF."purch_order_details, ".TB_PREF."stock_master
  92         WHERE ".TB_PREF."grn_items.grn_batch_id=".TB_PREF."grn_batch.id
  93                 AND ".TB_PREF."grn_items.po_detail_item=".TB_PREF."purch_order_details.po_detail_item
  94         AND ".TB_PREF."stock_master.stock_id=".TB_PREF."grn_items.item_code ";
  95
  96         if ($grn_batch_id != 0)
  97                 $sql .= " AND ".TB_PREF."grn_batch.id=$grn_batch_id AND ".TB_PREF."grn_items.grn_batch_id=$grn_batch_id ";
  98
  99         if ($is_invoiced_only)
 100                 $sql .= " AND ".TB_PREF."grn_items.quantity_inv > 0";
 101
 102         if ($outstanding_only)
 103         $sql .= " AND ".TB_PREF."grn_items.qty_recd - ".TB_PREF."grn_items.quantity_inv > 0";
 104
 105         if ($supplier_id != "")
 106                 $sql .= " AND ".TB_PREF."grn_batch.supplier_id ='$supplier_id' ";
 107
 108         $sql .= " ORDER BY ".TB_PREF."grn_batch.delivery_date, ".TB_PREF."grn_batch.id, ".TB_PREF."grn_items.id";
 109
 110         return db_query($sql, "Could not retreive GRNS");
 111 }
 112
 113 //----------------------------------------------------------------------------------------
 114
 115 // get the details for a given grn item
 116
 117 function get_grn_item_detail($grn_item_no)
 118 {
 119         $sql = "SELECT ".TB_PREF."grn_items.*, ".TB_PREF."purch_order_details.unit_price,
 120         ".TB_PREF."grn_items.qty_recd - ".TB_PREF."grn_items.quantity_inv AS QtyOstdg,
 121         ".TB_PREF."purch_order_details.std_cost_unit
 122                 FROM ".TB_PREF."grn_items, ".TB_PREF."purch_order_details, ".TB_PREF."stock_master
 123                 WHERE ".TB_PREF."grn_items.po_detail_item=".TB_PREF."purch_order_details.po_detail_item
 124                         AND ".TB_PREF."stock_master.stock_id=".TB_PREF."grn_items.item_code
 125                         AND ".TB_PREF."grn_items.id=$grn_item_no";
 126
 127         $result = db_query($sql, "could not retreive grn item details");
 128         return db_fetch($result);
 129 }
 130
 131 //----------------------------------------------------------------------------------------
 132
 133 function read_grn_items_to_order($grn_batch, &$order)
 134 {
 135         $result = get_grn_items($grn_batch);
 136
 137         if (db_num_rows($result) > 0)
 138         {
 139
 140                 while ($myrow = db_fetch($result))
 141                 {
 142
 143                         if (is_null($myrow["units"]))
 144                         {
 145                                 $units = "";
 146                         }
 147                         else
 148                         {
 149                                 $units = $myrow["units"];
 150                         }
 151
 152                         $order->add_to_order($order->lines_on_order+1, $myrow["item_code"],
 153                                 1,$myrow["description"], $myrow["unit_price"],$units,
 154                                 sql2date($myrow["delivery_date"]), $myrow["quantity_inv"],
 155                                 $myrow["qty_recd"]);
 156
 157                         $order->line_items[$order->lines_on_order]->po_detail_rec = $myrow["po_detail_item"];
 158                 } /* line po from purchase order details */
 159         } //end of checks on returned data set
 160 }
 161
 162 //----------------------------------------------------------------------------------------
 163
 164 // read a grn into an order class
 165
 166 function read_grn($grn_batch, &$order)
 167 {
 168         $sql= "SELECT * FROM ".TB_PREF."grn_batch WHERE id=$grn_batch";
 169
 170         $result = db_query($sql, "The grn sent is not valid");
 171
 172         $row = db_fetch($result);
 173         $po_number = $row["purch_order_no"];
 174
 175         $result = read_po_header($po_number, $order);
 176
 177         if ($result)
 178         {
 179
 180                 $order->orig_order_date = sql2date($row["delivery_date"]);
 181                 $order->location = $row["loc_code"];
 182                 $order->reference = $row["reference"];
 183
 184                 read_grn_items_to_order($grn_batch, $order);
 185         }
 186 }
 187
 188 //----------------------------------------------------------------------------------------------------------
 189
 190 // get the GRNs (batch info not details) for a given po number
 191
 192 function get_po_grns($po_number)
 193 {
 194     $sql = "SELECT * FROM ".TB_PREF."grn_batch WHERE purch_order_no=$po_number";
 195
 196         return db_query($sql, "The grns for the po $po_number could not be retreived");
 197 }
 198
 199 //----------------------------------------------------------------------------------------------------------
 200
 201 function exists_grn($grn_batch)
 202 {
 203         $sql = "SELECT id FROM ".TB_PREF."grn_batch WHERE id=$grn_batch";
 204         $result = db_query($sql, "Cannot retreive a grn");
 205
 206     return (db_num_rows($result) > 0);
 207 }
 208
 209 //----------------------------------------------------------------------------------------------------------
 210
 211 function exists_grn_on_invoices($grn_batch)
 212 {
 213         $sql = "SELECT ".TB_PREF."supp_invoice_items.id FROM ".TB_PREF."supp_invoice_items,".TB_PREF."grn_items
 214                 WHERE ".TB_PREF."supp_invoice_items.grn_item_id=".TB_PREF."grn_items.id
 215                 AND quantity != 0
 216                 AND grn_batch_id=$grn_batch";
 217         $result = db_query($sql, "Cannot query GRNs");
 218
 219     return (db_num_rows($result) > 0);
 220 }
 221
 222 //----------------------------------------------------------------------------------------------------------
 223
 224 function void_grn($grn_batch)
 225 {
 226         // if this grn is references on any invoices/credit notes, then it
 227         // can't be voided
 228         if (exists_grn_on_invoices($grn_batch))
 229                 return false;
 230
 231         begin_transaction();
 232
 233         void_bank_trans(25, $grn_batch, true);
 234         void_gl_trans(25, $grn_batch, true);
 235
 236         // clear the quantities of the grn items in the POs and invoices
 237         $result = get_grn_items($grn_batch);
 238
 239     if (db_num_rows($result) > 0)
 240     {
 241
 242         while ($myrow = db_fetch($result))
 243         {
 244
 245                 $sql = "UPDATE ".TB_PREF."purch_order_details
 246                 SET quantity_received = quantity_received - " . $myrow["qty_recd"] . "
 247                 WHERE po_detail_item = " . $myrow["po_detail_item"];
 248
 249                 db_query($sql, "a purchase order details record could not be voided.");
 250         }
 251     }
 252
 253         // clear the quantities in the grn items
 254         $sql = "UPDATE ".TB_PREF."grn_items SET qty_recd=0, quantity_inv=0
 255                 WHERE grn_batch_id=$grn_batch";
 256
 257         db_query($sql, "A grn detail item could not be voided.");
 258
 259     // clear the stock move items
 260     void_stock_move(25, $grn_batch);
 261
 262         commit_transaction();
 263
 264         return true;
 265 }
 266
 267 //----------------------------------------------------------------------------------------------------------
 268
 269 ?>