sales/includes/db/sales_order_db.inc

   1 <?php
   2
   3 //----------------------------------------------------------------------------------------
   4 function get_demand_qty($stockid, $location)
   5 {
   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'";
  12
  13     $TransResult = db_query($sql,"No transactions were returned");
  14         $DemandRow = db_fetch($TransResult);
  15         return $DemandRow['QtyDemand'];
  16 }
  17
  18 function get_demand_asm_qty($stockid, $location)
  19 {
  20         $sql = "SELECT SUM((".TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_invoiced)*".TB_PREF."bom.quantity)
  21                                    AS Dem
  22                                    FROM ".TB_PREF."sales_order_details,
  23                                                 ".TB_PREF."sales_orders,
  24                                                 ".TB_PREF."bom,
  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'";
  33
  34     $TransResult = db_query($sql,"No transactions were returned");
  35         if (db_num_rows($TransResult)==1)
  36         {
  37                 $DemandRow = db_fetch_row($TransResult);
  38                 $DemandQty = $DemandRow[0];
  39         }
  40         else
  41                 $DemandQty = 0.0;
  42
  43     return $DemandQty;
  44 }
  45
  46 function add_sales_order($order)
  47 {
  48         global $loc_notification, $path_to_root;
  49
  50         begin_transaction();
  51
  52         if ($order->direct_invoice)
  53                 $del_date = date2sql($order->orig_order_date);
  54         else
  55                 $del_date = date2sql($order->delivery_date);
  56
  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 ."', '" .
  68                         $del_date . "')";
  69
  70         db_query($sql, "order Cannot be Added");
  71
  72         $order_no = db_insert_id();
  73         if ($loc_notification == 1)
  74         {
  75                 include_once($path_to_root . "/inventory/includes/inventory_db.inc");
  76                 $st_ids = array();
  77                 $st_names = array();
  78                 $st_num = array();
  79                 $st_reorder = array();
  80         }
  81         foreach ($order->line_items as $line)
  82         {
  83                 if ($line->Deleted == false)
  84                 {
  85                         if ($loc_notification == 1 && is_inventory_item($line->stock_id))
  86                         {
  87                                 $sql = "SELECT ".TB_PREF."loc_stock.*, ".TB_PREF."locations.location_name, ".TB_PREF."locations.email
  88                                         FROM ".TB_PREF."loc_stock, ".TB_PREF."locations
  89                                         WHERE ".TB_PREF."loc_stock.loc_code=".TB_PREF."locations.loc_code
  90                                         AND ".TB_PREF."loc_stock.stock_id = '" . $line->stock_id . "'
  91                                         AND ".TB_PREF."loc_stock.loc_code = '" . $order->Location . "'";
  92                                 $res = db_query($sql,"a location could not be retreived");
  93                                 $loc = db_fetch($res);
  94                                 if ($loc['email'] != "")
  95                                 {
  96                                         $qoh = get_qoh_on_date($line->stock_id, $order->Location);
  97                                         $qoh -= get_demand_qty($line->stock_id, $order->Location);
  98                                         $qoh -= get_demand_asm_qty($line->stock_id, $order->Location);
  99                                         $qoh -= $line->quantity;
 100                                         if ($qoh < $loc['reorder_level'])
 101                                         {
 102                                                 $st_ids[] = $line->stock_id;
 103                                                 $st_names[] = $line->item_description;
 104                                                 $st_num[] = $qoh - $loc['reorder_level'];
 105                                                 $st_reorder[] = $loc['reorder_level'];
 106                                         }
 107                                 }
 108                         }
 109                         $sql = "INSERT INTO ".TB_PREF."sales_order_details (order_no, stk_code, description, unit_price, quantity, discount_percent) VALUES (";
 110                         $sql .= $order_no .
 111                                         ",'$line->stock_id', '$line->item_description', $line->price,
 112                                         $line->quantity,
 113                                         $line->discount_percent)";
 114                         db_query($sql, "order Details Cannot be Added");
 115
 116                 } /* inserted line items into sales order details */
 117         }
 118         add_forms_for_sys_type(systypes::sales_order(), $order_no);
 119
 120         commit_transaction();
 121
 122         if ($loc_notification == 1 && count($st_ids) > 0)
 123         {
 124                 require_once($path_to_root . "/reporting/includes/class.mail.inc");
 125                 $company = get_company_prefs();
 126                 $mail = new email($company['coy_name'], $company['email']);
 127                 $from = $company['coy_name'] . " <" . $company['email'] . ">";
 128                 $to = $loc['location_name'] . " <" . $loc['email'] . ">";
 129                 $subject = _("Stocks below Re-Order Level at " . $loc['location_name']);
 130                 $msg = "\n";
 131                 for ($i = 0; $i < count($st_ids); $i++)
 132                         $msg .= $st_ids[$i] . " " . $st_names[$i] . ", " . _("Re-Order Level") . ": " . $st_reorder[$i] . ", " . _("Below") . ": " . $st_num[$i] . "\n";
 133                 $msg .= "\n" . _("Please reorder") . "\n\n";
 134                 $msg .= $company['coy_name'];
 135                 $mail->to($to);
 136                 $mail->subject($subject);
 137                 $mail->text($msg);
 138                 $ret = $mail->send();
 139         }
 140         return $order_no;
 141 }
 142
 143 //----------------------------------------------------------------------------------------
 144
 145 function delete_sales_order($order_no)
 146 {
 147         begin_transaction();
 148
 149         $sql = "DELETE FROM ".TB_PREF."sales_orders WHERE order_no=" . $order_no;
 150         db_query($sql, "order Header Delete");
 151
 152         $sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" . $order_no;
 153         db_query($sql, "order Detail Delete");
 154
 155         delete_forms_for_systype(systypes::sales_order(), $order_no);
 156
 157         commit_transaction();
 158 }
 159
 160 //----------------------------------------------------------------------------------------
 161
 162 function update_sales_order($order_no, $order)
 163 {
 164         global $loc_notification, $path_to_root;
 165
 166         $del_date = date2sql($order->delivery_date);
 167         $ord_date = date2sql($order->orig_order_date);
 168
 169         begin_transaction();
 170
 171         $sql = "UPDATE ".TB_PREF."sales_orders SET debtor_no = '" . $order->customer_id . "',
 172                 branch_code = '" . $order->Branch . "',
 173                 customer_ref = '". $order->cust_ref ."',
 174                 Comments = '". db_escape($order->Comments) ."', ord_date = '" . $ord_date . "',
 175                 order_type = '" . $order->default_sales_type . "', ship_via = " . $order->ship_via .",
 176                 deliver_to = '" . $order->deliver_to . "', delivery_address = '" . $order->delivery_address . "',
 177                 contact_phone = '" . $order->phone . "',
 178                 contact_email = '" . $order->email . "', freight_cost = " . $order->freight_cost .",
 179                 from_stk_loc = '" . $order->Location ."', delivery_date = '" . $del_date . "'
 180                 WHERE order_no=" . $order_no;
 181
 182         db_query($sql, "order Cannot be Updated");
 183
 184         $sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" . $order_no;
 185
 186         db_query($sql, "Old order Cannot be Deleted");
 187
 188         if ($loc_notification == 1)
 189         {
 190                 include_once($path_to_root . "/inventory/includes/inventory_db.inc");
 191                 $st_ids = array();
 192                 $st_names = array();
 193                 $st_num = array();
 194                 $st_reorder = array();
 195         }
 196         foreach ($order->line_items as $line)
 197         {
 198                 if ($line->Deleted == false)
 199                 {
 200                         if ($loc_notification == 1 && is_inventory_item($line->stock_id))
 201                         {
 202                                 $sql = "SELECT ".TB_PREF."loc_stock.*, ".TB_PREF."locations.location_name, ".TB_PREF."locations.email
 203                                         FROM ".TB_PREF."loc_stock, ".TB_PREF."locations
 204                                         WHERE ".TB_PREF."loc_stock.loc_code=".TB_PREF."locations.loc_code
 205                                         AND ".TB_PREF."loc_stock.stock_id = '" . $line->stock_id . "'
 206                                         AND ".TB_PREF."loc_stock.loc_code = '" . $order->Location . "'";
 207                                 $res = db_query($sql,"a location could not be retreived");
 208                                 $loc = db_fetch($res);
 209                                 if ($loc['email'] != "")
 210                                 {
 211                                         $qoh = get_qoh_on_date($line->stock_id, $order->Location);
 212                                         $qoh -= get_demand_qty($line->stock_id, $order->Location);
 213                                         $qoh -= get_demand_asm_qty($line->stock_id, $order->Location);
 214                                         $qoh -= $line->quantity;
 215                                         if ($qoh < $loc['reorder_level'])
 216                                         {
 217                                                 $st_ids[] = $line->stock_id;
 218                                                 $st_names[] = $line->item_description;
 219                                                 $st_num[] = $qoh - $loc['reorder_level'];
 220                                                 $st_reorder[] = $loc['reorder_level'];
 221                                         }
 222                                 }
 223                         }
 224
 225                         $sql = "INSERT INTO ".TB_PREF."sales_order_details (order_no, stk_code,  description, unit_price, quantity, discount_percent, qty_invoiced) VALUES (";
 226
 227                         $sql .= $order_no . ",'" . $line->stock_id . "','" . $line->item_description . "', " . $line->price . ", " . $line->quantity . ", " . $line->discount_percent . ", " . $line->qty_inv . " )";
 228
 229                         db_query($sql, "Old order Cannot be Inserted");
 230
 231                 } /* inserted line items into sales order details */
 232         }
 233         commit_transaction();
 234         if ($loc_notification == 1 && count($st_ids) > 0)
 235         {
 236                 require_once($path_to_root . "/reporting/includes/class.mail.inc");
 237                 $company = get_company_prefs();
 238                 $mail = new email($company['coy_name'], $company['email']);
 239                 $from = $company['coy_name'] . " <" . $company['email'] . ">";
 240                 $to = $loc['location_name'] . " <" . $loc['email'] . ">";
 241                 $subject = _("Stocks below Re-Order Level at " . $loc['location_name']);
 242                 $msg = "\n";
 243                 for ($i = 0; $i < count($st_ids); $i++)
 244                         $msg .= $st_ids[$i] . " " . $st_names[$i] . ", " . _("Re-Order Level") . ": " . $st_reorder[$i] . ", " . _("Below") . ": " . $st_num[$i] . "\n";
 245                 $msg .= "\n" . _("Please reorder") . "\n\n";
 246                 $msg .= $company['coy_name'];
 247                 $mail->to($to);
 248                 $mail->subject($subject);
 249                 $mail->text($msg);
 250                 $ret = $mail->send();
 251         }
 252 }
 253
 254 //----------------------------------------------------------------------------------------
 255
 256 function get_sales_order($order_no)
 257 {
 258         $sql = "SELECT ".TB_PREF."sales_orders.*, ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."locations.location_name,
 259                 ".TB_PREF."debtors_master.payment_terms, ".TB_PREF."debtors_master.discount, ".TB_PREF."sales_types.sales_type, ".TB_PREF."shippers.shipper_name,
 260                 ".TB_PREF."tax_groups.name AS tax_group_name , ".TB_PREF."tax_groups.id AS tax_group_id
 261                 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
 262                         WHERE ".TB_PREF."sales_orders.order_type=".TB_PREF."sales_types.id
 263                                 AND ".TB_PREF."cust_branch.branch_code = ".TB_PREF."sales_orders.branch_code
 264                                 AND ".TB_PREF."cust_branch.tax_group_id = ".TB_PREF."tax_groups.id
 265                                 AND ".TB_PREF."sales_orders.debtor_no = ".TB_PREF."debtors_master.debtor_no
 266                                 AND ".TB_PREF."locations.loc_code = ".TB_PREF."sales_orders.from_stk_loc
 267                                 AND ".TB_PREF."shippers.shipper_id = ".TB_PREF."sales_orders.ship_via
 268                                 AND ".TB_PREF."sales_orders.order_no = " . $order_no;
 269
 270         $result = db_query($sql, "order Retreival");
 271
 272         $num = db_num_rows($result);
 273         if ($num > 1)
 274         {
 275                 display_db_error("FATAL : sales order query returned a duplicate - " . db_num_rows($result), $sql, true);
 276         }
 277         else if ($num == 1)
 278         {
 279                 return db_fetch($result);
 280         }
 281         else
 282                 display_db_error("FATAL : sales order return nothing - " . db_num_rows($result), $sql, true);
 283
 284 }
 285
 286 //----------------------------------------------------------------------------------------
 287
 288 function read_sales_order($order_no, &$order, $skip_completed_items=false)
 289 {
 290         $myrow = get_sales_order($order_no);
 291
 292         $order->customer_id = $myrow["debtor_no"];
 293         $order->Branch = $myrow["branch_code"];
 294         $order->customer_name = $myrow["name"];
 295         $order->cust_ref = $myrow["customer_ref"];
 296         $order->default_sales_type =$myrow["order_type"];
 297         $order->sales_type_name =$myrow["sales_type"];
 298         $order->customer_currency = $myrow["curr_code"];
 299         $order->default_discount = $myrow["discount"];
 300
 301         $order->Comments = $myrow["comments"];
 302
 303         $order->ship_via = $myrow["ship_via"];
 304         $order->deliver_to = $myrow["deliver_to"];
 305         $order->delivery_date = sql2date($myrow["delivery_date"]);
 306         $order->freight_cost = $myrow["freight_cost"];
 307         $order->delivery_address = $myrow["delivery_address"];
 308         $order->phone = $myrow["contact_phone"];
 309         $order->email = $myrow["contact_email"];
 310         $order->Location = $myrow["from_stk_loc"];
 311         $order->location_name = $myrow["location_name"];
 312         $order->orig_order_date = sql2date($myrow["ord_date"]);
 313
 314         $order->tax_group_name = $myrow["tax_group_name"];
 315         $order->tax_group_id = $myrow["tax_group_id"];
 316
 317         $sql = "SELECT stk_code, unit_price, ".TB_PREF."sales_order_details.description,
 318                 ".TB_PREF."sales_order_details.quantity, ".TB_PREF."sales_order_details.id, discount_percent,
 319                 qty_invoiced, ".TB_PREF."stock_master.units,
 320                 ".TB_PREF."stock_master.material_cost + ".TB_PREF."stock_master.labour_cost + ".TB_PREF."stock_master.overhead_cost AS standard_cost
 321                 FROM ".TB_PREF."sales_order_details, ".TB_PREF."stock_master
 322                         WHERE ".TB_PREF."sales_order_details.stk_code = ".TB_PREF."stock_master.stock_id
 323                                 AND order_no =" . $order_no;
 324
 325         if ($skip_completed_items)
 326                 $sql .= "
 327                         AND ".TB_PREF."sales_order_details.quantity - ".TB_PREF."sales_order_details.qty_invoiced > 0 ";
 328         $sql .= " ORDER BY ".TB_PREF."sales_order_details.id";
 329
 330         $result = db_query($sql, "Retreive order Line Items");
 331
 332         if (db_num_rows($result) > 0)
 333         {
 334
 335                 while ($myrow = db_fetch($result))
 336                 {
 337                         $order->add_to_cart($order->lines_on_order+1, $myrow["id"], $myrow["stk_code"],$myrow["quantity"],
 338                                 $myrow["unit_price"], $myrow["discount_percent"],
 339                                 $myrow["qty_invoiced"], $myrow["standard_cost"], $myrow["description"]);
 340                 }
 341         }
 342
 343         return true;
 344 }
 345
 346 //----------------------------------------------------------------------------------------
 347
 348 function sales_order_has_invoices($order_no)
 349 {
 350         $sql = "SELECT COUNT(*) FROM ".TB_PREF."debtor_trans WHERE order_=$order_no";
 351
 352         $result = db_query($sql, "could not query for sales order usage");
 353
 354         $row = db_fetch_row($result);
 355
 356         return ($row[0] > 0);
 357 }
 358
 359 //----------------------------------------------------------------------------------------
 360
 361 function close_sales_order($order_no)
 362 {
 363         // set the quantity of each item to the already invoiced quantity. this will mark item as closed.
 364         $sql = "UPDATE ".TB_PREF."sales_order_details
 365                 SET quantity = qty_invoiced
 366                         WHERE order_no = $order_no";
 367
 368         db_query($sql, "The sales order detail record could not be updated");
 369 }
 370
 371 //----------------------------------------------------------------------------------------
 372
 373 function dispatch_sales_order_item($order_no, $id, $qty_dispatched)
 374 {
 375         $sql = "UPDATE ".TB_PREF."sales_order_details
 376                 SET qty_invoiced = qty_invoiced + $qty_dispatched ";
 377         $sql .= " WHERE order_no = $order_no
 378                 AND id = $id";
 379
 380         db_query($sql, "The sales order detail record could not be updated");
 381 }
 382
 383 //---------------------------------------------------------------------------------------------------------------
 384
 385 function get_invoice_duedate($debtorno, $invdate)
 386 {
 387         if (!is_date($invdate))
 388         {
 389                 return Today();
 390         }
 391     $sql = "SELECT ".TB_PREF."debtors_master.debtor_no, ".TB_PREF."debtors_master.payment_terms, ".TB_PREF."payment_terms.* FROM ".TB_PREF."debtors_master,
 392                 ".TB_PREF."payment_terms WHERE ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator AND
 393                 ".TB_PREF."debtors_master.debtor_no = '$debtorno'";
 394
 395     $result = db_query($sql,"The customer details could not be retrieved");
 396     $myrow = db_fetch($result);
 397
 398     if (db_num_rows($result) == 0)
 399         return $invdate;
 400     if ($myrow['day_in_following_month'] > 0)
 401         $duedate = add_days(end_month($invdate), $myrow['day_in_following_month']);
 402         else
 403         $duedate = add_days($invdate, $myrow['days_before_due']);
 404     return $duedate;
 405 }
 406
 407
 408 ?>