sales/includes/db/sales_order_db.inc

   1 <?php
   2
   3 //----------------------------------------------------------------------------------------
   4
   5 function add_sales_order($order)
   6 {
   7         begin_transaction();
   8
   9         if ($order->direct_invoice)
  10                 $del_date = date2sql($order->orig_order_date);
  11         else
  12                 $del_date = date2sql($order->delivery_date);
  13
  14         $sql = "INSERT INTO ".TB_PREF."sales_orders (debtor_no, branch_code, customer_ref, Comments, ord_date,
  15                 order_type, ship_via, deliver_to, delivery_address, contact_phone,
  16                 contact_email, freight_cost, from_stk_loc, delivery_date)
  17                 VALUES ('" . $order->customer_id . "', '" . $order->Branch . "', '".
  18                         $order->cust_ref ."','". db_escape($order->Comments) ."','" .
  19                         date2sql($order->orig_order_date) . "', '" .
  20                         $order->default_sales_type . "', " .
  21                         $_POST['ship_via'] .",'" . $order->deliver_to . "', '" .
  22                         $order->delivery_address . "', '" .
  23                         $order->phone . "', '" . $order->email . "', " .
  24                         $order->freight_cost .", '" . $order->Location ."', '" .
  25                         $del_date . "')";
  26
  27         db_query($sql, "order Cannot be Added");
  28
  29         $order_no = db_insert_id();
  30
  31         foreach ($order->line_items as $stock_item)
  32         {
  33                 $sql = "INSERT INTO ".TB_PREF."sales_order_details (order_no, stk_code, description, unit_price, quantity, discount_percent) VALUES (";
  34                 $sql .= $order_no .
  35                                 ",'$stock_item->stock_id', '$stock_item->item_description', $stock_item->price,
  36                                 $stock_item->quantity,
  37                                 $stock_item->discount_percent)";
  38                 db_query($sql, "order Details Cannot be Added");
  39
  40         } /* inserted line items into sales order details */
  41
  42         add_forms_for_sys_type(systypes::sales_order(), $order_no);
  43
  44         commit_transaction();
  45
  46         return $order_no;
  47 }
  48
  49 //----------------------------------------------------------------------------------------
  50
  51 function delete_sales_order($order_no)
  52 {
  53         begin_transaction();
  54
  55         $sql = "DELETE FROM ".TB_PREF."sales_orders WHERE order_no=" . $order_no;
  56         db_query($sql, "order Header Delete");
  57
  58         $sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" . $order_no;
  59         db_query($sql, "order Detail Delete");
  60
  61         delete_forms_for_systype(systypes::sales_order(), $order_no);
  62
  63         commit_transaction();
  64 }
  65
  66 //----------------------------------------------------------------------------------------
  67
  68 function update_sales_order($order_no, $order)
  69 {
  70         $del_date = date2sql($order->delivery_date);
  71         $ord_date = date2sql($order->orig_order_date);
  72
  73         begin_transaction();
  74
  75         $sql = "UPDATE ".TB_PREF."sales_orders SET debtor_no = '" . $order->customer_id . "',
  76                 branch_code = '" . $order->Branch . "',
  77                 customer_ref = '". $order->cust_ref ."',
  78                 Comments = '". db_escape($order->Comments) ."', ord_date = '" . $ord_date . "',
  79                 order_type = '" . $order->default_sales_type . "', ship_via = " . $order->ship_via .",
  80                 deliver_to = '" . $order->deliver_to . "', delivery_address = '" . $order->delivery_address . "',
  81                 contact_phone = '" . $order->phone . "',
  82                 contact_email = '" . $order->email . "', freight_cost = " . $order->freight_cost .",
  83                 from_stk_loc = '" . $order->Location ."', delivery_date = '" . $del_date . "'
  84                 WHERE order_no=" . $order_no;
  85
  86         db_query($sql, "order Cannot be Updated");
  87
  88         $sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" . $order_no;
  89
  90         db_query($sql, "Old order Cannot be Deleted");
  91
  92         foreach ($order->line_items as $stock_item)
  93         {
  94
  95                 $sql = "INSERT INTO ".TB_PREF."sales_order_details (order_no, stk_code,  description, unit_price, quantity, discount_percent, qty_invoiced) VALUES (";
  96
  97                 $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 . " )";
  98
  99                 db_query($sql, "Old order Cannot be Inserted");
 100
 101         } /* inserted line items into sales order details */
 102
 103         commit_transaction();
 104 }
 105
 106 //----------------------------------------------------------------------------------------
 107
 108 function get_sales_order($order_no)
 109 {
 110         $sql = "SELECT ".TB_PREF."sales_orders.*, ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."locations.location_name,
 111                 ".TB_PREF."debtors_master.payment_terms, ".TB_PREF."debtors_master.discount, ".TB_PREF."sales_types.sales_type, ".TB_PREF."shippers.shipper_name,
 112                 ".TB_PREF."tax_groups.name AS tax_group_name , ".TB_PREF."tax_groups.id AS tax_group_id
 113                 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
 114                         WHERE ".TB_PREF."sales_orders.order_type=".TB_PREF."sales_types.id
 115                                 AND ".TB_PREF."cust_branch.branch_code = ".TB_PREF."sales_orders.branch_code
 116                                 AND ".TB_PREF."cust_branch.tax_group_id = ".TB_PREF."tax_groups.id
 117                                 AND ".TB_PREF."sales_orders.debtor_no = ".TB_PREF."debtors_master.debtor_no
 118                                 AND ".TB_PREF."locations.loc_code = ".TB_PREF."sales_orders.from_stk_loc
 119                                 AND ".TB_PREF."shippers.shipper_id = ".TB_PREF."sales_orders.ship_via
 120                                 AND ".TB_PREF."sales_orders.order_no = " . $order_no;
 121
 122         $result = db_query($sql, "order Retreival");
 123
 124         $num = db_num_rows($result);
 125         if ($num > 1)
 126         {
 127                 display_db_error("FATAL : sales order query returned a duplicate - " . db_num_rows($result), $sql, true);
 128         }
 129         else if ($num == 1)
 130         {
 131                 return db_fetch($result);
 132         }
 133         else
 134                 display_db_error("FATAL : sales order return nothing - " . db_num_rows($result), $sql, true);
 135
 136 }
 137
 138 //----------------------------------------------------------------------------------------
 139
 140 function read_sales_order($order_no, &$order, $skip_completed_items=false)
 141 {
 142         $myrow = get_sales_order($order_no);
 143
 144         $order->customer_id = $myrow["debtor_no"];
 145         $order->Branch = $myrow["branch_code"];
 146         $order->customer_name = $myrow["name"];
 147         $order->cust_ref = $myrow["customer_ref"];
 148         $order->default_sales_type =$myrow["order_type"];
 149         $order->sales_type_name =$myrow["sales_type"];
 150         $order->customer_currency = $myrow["curr_code"];
 151         $order->default_discount = $myrow["discount"];
 152
 153         $order->Comments = $myrow["comments"];
 154
 155         $order->ship_via = $myrow["ship_via"];
 156         $order->deliver_to = $myrow["deliver_to"];
 157         $order->delivery_date = sql2date($myrow["delivery_date"]);
 158         $order->freight_cost = $myrow["freight_cost"];
 159         $order->delivery_address = $myrow["delivery_address"];
 160         $order->phone = $myrow["contact_phone"];
 161         $order->email = $myrow["contact_email"];
 162         $order->Location = $myrow["from_stk_loc"];
 163         $order->location_name = $myrow["location_name"];
 164         $order->orig_order_date = sql2date($myrow["ord_date"]);
 165
 166         $order->tax_group_name = $myrow["tax_group_name"];
 167         $order->tax_group_id = $myrow["tax_group_id"];
 168
 169         $sql = "SELECT stk_code, unit_price, ".TB_PREF."sales_order_details.description,
 170                 ".TB_PREF."sales_order_details.quantity, discount_percent,
 171                 qty_invoiced, ".TB_PREF."stock_master.units,
 172                 ".TB_PREF."stock_master.material_cost + ".TB_PREF."stock_master.labour_cost + ".TB_PREF."stock_master.overhead_cost AS standard_cost
 173                 FROM ".TB_PREF."sales_order_details, ".TB_PREF."stock_master
 174                         WHERE ".TB_PREF."sales_order_details.stk_code = ".TB_PREF."stock_master.stock_id
 175                                 AND order_no =" . $order_no;
 176
 177         if ($skip_completed_items)
 178                 $sql .= "
 179                         AND ".TB_PREF."sales_order_details.quantity - ".TB_PREF."sales_order_details.qty_invoiced > 0 ";
 180
 181         $result = db_query($sql, "Retreive order Line Items");
 182
 183         if (db_num_rows($result) > 0)
 184         {
 185
 186                 while ($myrow = db_fetch($result))
 187                 {
 188                         $order->add_to_cart($myrow["stk_code"],$myrow["quantity"],
 189                                 $myrow["unit_price"], $myrow["discount_percent"],
 190                                 $myrow["qty_invoiced"], $myrow["standard_cost"], $myrow["description"]);
 191                 }
 192         }
 193
 194         return true;
 195 }
 196
 197 //----------------------------------------------------------------------------------------
 198
 199 function sales_order_has_invoices($order_no)
 200 {
 201         $sql = "SELECT COUNT(*) FROM ".TB_PREF."debtor_trans WHERE order_=$order_no";
 202
 203         $result = db_query($sql, "could not query for sales order usage");
 204
 205         $row = db_fetch_row($result);
 206
 207         return ($row[0] > 0);
 208 }
 209
 210 //----------------------------------------------------------------------------------------
 211
 212 function close_sales_order($order_no)
 213 {
 214         // set the quantity of each item to the already invoiced quantity. this will mark item as closed.
 215         $sql = "UPDATE ".TB_PREF."sales_order_details
 216                 SET quantity = qty_invoiced
 217                         WHERE order_no = $order_no";
 218
 219         db_query($sql, "The sales order detail record could not be updated");
 220 }
 221
 222 //----------------------------------------------------------------------------------------
 223
 224 function dispatch_sales_order_item($order_no, $stock_id, $qty_dispatched)
 225 {
 226         $sql = "UPDATE ".TB_PREF."sales_order_details
 227                 SET qty_invoiced = qty_invoiced + $qty_dispatched ";
 228         $sql .= " WHERE order_no = $order_no
 229                 AND stk_code = '$stock_id'";
 230
 231         db_query($sql, "The sales order detail record could not be updated");
 232 }
 233
 234 //---------------------------------------------------------------------------------------------------------------
 235
 236 function get_invoice_duedate($debtorno, $invdate)
 237 {
 238         if (!is_date($invdate))
 239         {
 240                 return Today();
 241         }
 242     $sql = "SELECT ".TB_PREF."debtors_master.debtor_no, ".TB_PREF."debtors_master.payment_terms, ".TB_PREF."payment_terms.* FROM ".TB_PREF."debtors_master,
 243                 ".TB_PREF."payment_terms WHERE ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator AND
 244                 ".TB_PREF."debtors_master.debtor_no = '$debtorno'";
 245
 246     $result = db_query($sql,"The customer details could not be retrieved");
 247     $myrow = db_fetch($result);
 248
 249     if (db_num_rows($result) == 0)
 250         return $invdate;
 251     if ($myrow['day_in_following_month'] > 0)
 252         $duedate = add_days(end_month($invdate), $myrow['day_in_following_month']);
 253         else
 254         $duedate = add_days($invdate, $myrow['days_before_due']);
 255     return $duedate;
 256 }
 257
 258
 259 ?>