3 //----------------------------------------------------------------------------------------
4 function get_demand_qty($stockid, $location)
6 $sql = "SELECT SUM(".TB_PREF."sales_order_details.quantity - ".TB_PREF."sales_order_details.qty_invoiced) AS QtyDemand
7 FROM ".TB_PREF."sales_order_details,
8 ".TB_PREF."sales_orders
9 WHERE ".TB_PREF."sales_order_details.order_no=".TB_PREF."sales_orders.order_no AND
10 ".TB_PREF."sales_orders.from_stk_loc ='$location' AND
11 ".TB_PREF."sales_order_details.stk_code = '$stockid'";
13 $TransResult = db_query($sql,"No transactions were returned");
14 $DemandRow = db_fetch($TransResult);
15 return $DemandRow['QtyDemand'];
18 function get_demand_asm_qty($stockid, $location)
20 $sql = "SELECT SUM((".TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_invoiced)*".TB_PREF."bom.quantity)
22 FROM ".TB_PREF."sales_order_details,
23 ".TB_PREF."sales_orders,
25 ".TB_PREF."stock_master
26 WHERE ".TB_PREF."sales_order_details.stk_code=".TB_PREF."bom.parent AND
27 ".TB_PREF."sales_orders.order_no = ".TB_PREF."sales_order_details.order_no AND
28 ".TB_PREF."sales_orders.from_stk_loc='$location' AND
29 ".TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_invoiced > 0 AND
30 ".TB_PREF."bom.component='$stockid' AND
31 ".TB_PREF."stock_master.stock_id=".TB_PREF."bom.parent AND
32 ".TB_PREF."stock_master.mb_flag='A'";
34 $TransResult = db_query($sql,"No transactions were returned");
35 if (db_num_rows($TransResult)==1)
37 $DemandRow = db_fetch_row($TransResult);
38 $DemandQty = $DemandRow[0];
46 function add_sales_order($order)
48 global $loc_notification, $path_to_root;
52 if ($order->direct_invoice)
53 $del_date = date2sql($order->orig_order_date);
55 $del_date = date2sql($order->delivery_date);
57 $sql = "INSERT INTO ".TB_PREF."sales_orders (debtor_no, branch_code, customer_ref, Comments, ord_date,
58 order_type, ship_via, deliver_to, delivery_address, contact_phone,
59 contact_email, freight_cost, from_stk_loc, delivery_date)
60 VALUES ('" . $order->customer_id . "', '" . $order->Branch . "', '".
61 $order->cust_ref ."','". db_escape($order->Comments) ."','" .
62 date2sql($order->orig_order_date) . "', '" .
63 $order->default_sales_type . "', " .
64 $_POST['ship_via'] .",'" . $order->deliver_to . "', '" .
65 $order->delivery_address . "', '" .
66 $order->phone . "', '" . $order->email . "', " .
67 $order->freight_cost .", '" . $order->Location ."', '" .
70 db_query($sql, "order Cannot be Added");
72 $order_no = db_insert_id();
73 if ($loc_notification == 1)
75 include_once($path_to_root . "/inventory/includes/inventory_db.inc");
79 $st_reorder = array();
81 foreach ($order->line_items as $stock_item)
83 if ($loc_notification == 1 && is_inventory_item($stock_item->stock_id))
85 $sql = "SELECT ".TB_PREF."loc_stock.*, ".TB_PREF."locations.location_name, ".TB_PREF."locations.email
86 FROM ".TB_PREF."loc_stock, ".TB_PREF."locations
87 WHERE ".TB_PREF."loc_stock.loc_code=".TB_PREF."locations.loc_code
88 AND ".TB_PREF."loc_stock.stock_id = '" . $stock_item->stock_id . "'
89 AND ".TB_PREF."loc_stock.loc_code = '" . $order->Location . "'";
90 $res = db_query($sql,"a location could not be retreived");
91 $loc = db_fetch($res);
92 if ($loc['email'] != "")
94 $qoh = get_qoh_on_date($stock_item->stock_id, $order->Location);
95 $qoh -= get_demand_qty($stock_item->stock_id, $order->Location);
96 $qoh -= get_demand_asm_qty($stock_item->stock_id, $order->Location);
97 $qoh -= $stock_item->quantity;
98 if ($qoh < $loc['reorder_level'])
100 $st_ids[] = $stock_item->stock_id;
101 $st_names[] = $stock_item->item_description;
102 $st_num[] = $qoh - $loc['reorder_level'];
103 $st_reorder[] = $loc['reorder_level'];
107 $sql = "INSERT INTO ".TB_PREF."sales_order_details (order_no, stk_code, description, unit_price, quantity, discount_percent) VALUES (";
109 ",'$stock_item->stock_id', '$stock_item->item_description', $stock_item->price,
110 $stock_item->quantity,
111 $stock_item->discount_percent)";
112 db_query($sql, "order Details Cannot be Added");
114 } /* inserted line items into sales order details */
116 add_forms_for_sys_type(systypes::sales_order(), $order_no);
118 commit_transaction();
120 if ($loc_notification == 1 && count($st_ids) > 0)
122 require_once($path_to_root . "/reporting/includes/class.mail.inc");
123 $company = get_company_prefs();
124 $mail = new email($company['coy_name'], $company['email']);
125 $from = $company['coy_name'] . " <" . $company['email'] . ">";
126 $to = $loc['location_name'] . " <" . $loc['email'] . ">";
127 $subject = _("Stocks below Re-Order Level at " . $loc['location_name']);
129 for ($i = 0; $i < count($st_ids); $i++)
130 $msg .= $st_ids[$i] . " " . $st_names[$i] . ", " . _("Re-Order Level") . ": " . $st_reorder[$i] . ", " . _("Below") . ": " . $st_num[$i] . "\n";
131 $msg .= "\n" . _("Please reorder") . "\n\n";
132 $msg .= $company['coy_name'];
134 $mail->subject($subject);
136 $ret = $mail->send();
141 //----------------------------------------------------------------------------------------
143 function delete_sales_order($order_no)
147 $sql = "DELETE FROM ".TB_PREF."sales_orders WHERE order_no=" . $order_no;
148 db_query($sql, "order Header Delete");
150 $sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" . $order_no;
151 db_query($sql, "order Detail Delete");
153 delete_forms_for_systype(systypes::sales_order(), $order_no);
155 commit_transaction();
158 //----------------------------------------------------------------------------------------
160 function update_sales_order($order_no, $order)
162 global $loc_notification, $path_to_root;
164 $del_date = date2sql($order->delivery_date);
165 $ord_date = date2sql($order->orig_order_date);
169 $sql = "UPDATE ".TB_PREF."sales_orders SET debtor_no = '" . $order->customer_id . "',
170 branch_code = '" . $order->Branch . "',
171 customer_ref = '". $order->cust_ref ."',
172 Comments = '". db_escape($order->Comments) ."', ord_date = '" . $ord_date . "',
173 order_type = '" . $order->default_sales_type . "', ship_via = " . $order->ship_via .",
174 deliver_to = '" . $order->deliver_to . "', delivery_address = '" . $order->delivery_address . "',
175 contact_phone = '" . $order->phone . "',
176 contact_email = '" . $order->email . "', freight_cost = " . $order->freight_cost .",
177 from_stk_loc = '" . $order->Location ."', delivery_date = '" . $del_date . "'
178 WHERE order_no=" . $order_no;
180 db_query($sql, "order Cannot be Updated");
182 $sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" . $order_no;
184 db_query($sql, "Old order Cannot be Deleted");
186 if ($loc_notification == 1)
188 include_once($path_to_root . "/inventory/includes/inventory_db.inc");
192 $st_reorder = array();
194 foreach ($order->line_items as $stock_item)
196 if ($loc_notification == 1 && is_inventory_item($stock_item->stock_id))
198 $sql = "SELECT ".TB_PREF."loc_stock.*, ".TB_PREF."locations.location_name, ".TB_PREF."locations.email
199 FROM ".TB_PREF."loc_stock, ".TB_PREF."locations
200 WHERE ".TB_PREF."loc_stock.loc_code=".TB_PREF."locations.loc_code
201 AND ".TB_PREF."loc_stock.stock_id = '" . $stock_item->stock_id . "'
202 AND ".TB_PREF."loc_stock.loc_code = '" . $order->Location . "'";
203 $res = db_query($sql,"a location could not be retreived");
204 $loc = db_fetch($res);
205 if ($loc['email'] != "")
207 $qoh = get_qoh_on_date($stock_item->stock_id, $order->Location);
208 $qoh -= get_demand_qty($stock_item->stock_id, $order->Location);
209 $qoh -= get_demand_asm_qty($stock_item->stock_id, $order->Location);
210 $qoh -= $stock_item->quantity;
211 if ($qoh < $loc['reorder_level'])
213 $st_ids[] = $stock_item->stock_id;
214 $st_names[] = $stock_item->item_description;
215 $st_num[] = $qoh - $loc['reorder_level'];
216 $st_reorder[] = $loc['reorder_level'];
221 $sql = "INSERT INTO ".TB_PREF."sales_order_details (order_no, stk_code, description, unit_price, quantity, discount_percent, qty_invoiced) VALUES (";
223 $sql .= $order_no . ",'" . $stock_item->stock_id . "','" . $stock_item->item_description . "', " . $stock_item->price . ", " . $stock_item->quantity . ", " . $stock_item->discount_percent . ", " . $stock_item->qty_inv . " )";
225 db_query($sql, "Old order Cannot be Inserted");
227 } /* inserted line items into sales order details */
229 commit_transaction();
230 if ($loc_notification == 1 && count($st_ids) > 0)
232 require_once($path_to_root . "/reporting/includes/class.mail.inc");
233 $company = get_company_prefs();
234 $mail = new email($company['coy_name'], $company['email']);
235 $from = $company['coy_name'] . " <" . $company['email'] . ">";
236 $to = $loc['location_name'] . " <" . $loc['email'] . ">";
237 $subject = _("Stocks below Re-Order Level at " . $loc['location_name']);
239 for ($i = 0; $i < count($st_ids); $i++)
240 $msg .= $st_ids[$i] . " " . $st_names[$i] . ", " . _("Re-Order Level") . ": " . $st_reorder[$i] . ", " . _("Below") . ": " . $st_num[$i] . "\n";
241 $msg .= "\n" . _("Please reorder") . "\n\n";
242 $msg .= $company['coy_name'];
244 $mail->subject($subject);
246 $ret = $mail->send();
250 //----------------------------------------------------------------------------------------
252 function get_sales_order($order_no)
254 $sql = "SELECT ".TB_PREF."sales_orders.*, ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."locations.location_name,
255 ".TB_PREF."debtors_master.payment_terms, ".TB_PREF."debtors_master.discount, ".TB_PREF."sales_types.sales_type, ".TB_PREF."shippers.shipper_name,
256 ".TB_PREF."tax_groups.name AS tax_group_name , ".TB_PREF."tax_groups.id AS tax_group_id
257 FROM ".TB_PREF."sales_orders, ".TB_PREF."debtors_master, ".TB_PREF."sales_types, ".TB_PREF."tax_groups, ".TB_PREF."cust_branch, ".TB_PREF."locations, ".TB_PREF."shippers
258 WHERE ".TB_PREF."sales_orders.order_type=".TB_PREF."sales_types.id
259 AND ".TB_PREF."cust_branch.branch_code = ".TB_PREF."sales_orders.branch_code
260 AND ".TB_PREF."cust_branch.tax_group_id = ".TB_PREF."tax_groups.id
261 AND ".TB_PREF."sales_orders.debtor_no = ".TB_PREF."debtors_master.debtor_no
262 AND ".TB_PREF."locations.loc_code = ".TB_PREF."sales_orders.from_stk_loc
263 AND ".TB_PREF."shippers.shipper_id = ".TB_PREF."sales_orders.ship_via
264 AND ".TB_PREF."sales_orders.order_no = " . $order_no;
266 $result = db_query($sql, "order Retreival");
268 $num = db_num_rows($result);
271 display_db_error("FATAL : sales order query returned a duplicate - " . db_num_rows($result), $sql, true);
275 return db_fetch($result);
278 display_db_error("FATAL : sales order return nothing - " . db_num_rows($result), $sql, true);
282 //----------------------------------------------------------------------------------------
284 function read_sales_order($order_no, &$order, $skip_completed_items=false)
286 $myrow = get_sales_order($order_no);
288 $order->customer_id = $myrow["debtor_no"];
289 $order->Branch = $myrow["branch_code"];
290 $order->customer_name = $myrow["name"];
291 $order->cust_ref = $myrow["customer_ref"];
292 $order->default_sales_type =$myrow["order_type"];
293 $order->sales_type_name =$myrow["sales_type"];
294 $order->customer_currency = $myrow["curr_code"];
295 $order->default_discount = $myrow["discount"];
297 $order->Comments = $myrow["comments"];
299 $order->ship_via = $myrow["ship_via"];
300 $order->deliver_to = $myrow["deliver_to"];
301 $order->delivery_date = sql2date($myrow["delivery_date"]);
302 $order->freight_cost = $myrow["freight_cost"];
303 $order->delivery_address = $myrow["delivery_address"];
304 $order->phone = $myrow["contact_phone"];
305 $order->email = $myrow["contact_email"];
306 $order->Location = $myrow["from_stk_loc"];
307 $order->location_name = $myrow["location_name"];
308 $order->orig_order_date = sql2date($myrow["ord_date"]);
310 $order->tax_group_name = $myrow["tax_group_name"];
311 $order->tax_group_id = $myrow["tax_group_id"];
313 $sql = "SELECT stk_code, unit_price, ".TB_PREF."sales_order_details.description,
314 ".TB_PREF."sales_order_details.quantity, discount_percent,
315 qty_invoiced, ".TB_PREF."stock_master.units,
316 ".TB_PREF."stock_master.material_cost + ".TB_PREF."stock_master.labour_cost + ".TB_PREF."stock_master.overhead_cost AS standard_cost
317 FROM ".TB_PREF."sales_order_details, ".TB_PREF."stock_master
318 WHERE ".TB_PREF."sales_order_details.stk_code = ".TB_PREF."stock_master.stock_id
319 AND order_no =" . $order_no;
321 if ($skip_completed_items)
323 AND ".TB_PREF."sales_order_details.quantity - ".TB_PREF."sales_order_details.qty_invoiced > 0 ";
325 $result = db_query($sql, "Retreive order Line Items");
327 if (db_num_rows($result) > 0)
330 while ($myrow = db_fetch($result))
332 $order->add_to_cart($myrow["stk_code"],$myrow["quantity"],
333 $myrow["unit_price"], $myrow["discount_percent"],
334 $myrow["qty_invoiced"], $myrow["standard_cost"], $myrow["description"]);
341 //----------------------------------------------------------------------------------------
343 function sales_order_has_invoices($order_no)
345 $sql = "SELECT COUNT(*) FROM ".TB_PREF."debtor_trans WHERE order_=$order_no";
347 $result = db_query($sql, "could not query for sales order usage");
349 $row = db_fetch_row($result);
351 return ($row[0] > 0);
354 //----------------------------------------------------------------------------------------
356 function close_sales_order($order_no)
358 // set the quantity of each item to the already invoiced quantity. this will mark item as closed.
359 $sql = "UPDATE ".TB_PREF."sales_order_details
360 SET quantity = qty_invoiced
361 WHERE order_no = $order_no";
363 db_query($sql, "The sales order detail record could not be updated");
366 //----------------------------------------------------------------------------------------
368 function dispatch_sales_order_item($order_no, $stock_id, $qty_dispatched)
370 $sql = "UPDATE ".TB_PREF."sales_order_details
371 SET qty_invoiced = qty_invoiced + $qty_dispatched ";
372 $sql .= " WHERE order_no = $order_no
373 AND stk_code = '$stock_id'";
375 db_query($sql, "The sales order detail record could not be updated");
378 //---------------------------------------------------------------------------------------------------------------
380 function get_invoice_duedate($debtorno, $invdate)
382 if (!is_date($invdate))
386 $sql = "SELECT ".TB_PREF."debtors_master.debtor_no, ".TB_PREF."debtors_master.payment_terms, ".TB_PREF."payment_terms.* FROM ".TB_PREF."debtors_master,
387 ".TB_PREF."payment_terms WHERE ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator AND
388 ".TB_PREF."debtors_master.debtor_no = '$debtorno'";
390 $result = db_query($sql,"The customer details could not be retrieved");
391 $myrow = db_fetch($result);
393 if (db_num_rows($result) == 0)
395 if ($myrow['day_in_following_month'] > 0)
396 $duedate = add_days(end_month($invdate), $myrow['day_in_following_month']);
398 $duedate = add_days($invdate, $myrow['days_before_due']);