X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sales%2Fincludes%2Fdb%2Fsales_order_db.inc;h=eb5665ab71c46165a324d3da8c31661bc826855d;hb=0d0fd1bc61f435acf0d4e4bd6eba719e1b10e07b;hp=55d9b8ff990b58e6ab83924d17c9bfd9795e5582;hpb=95303d7b5280820af76ddbd8908b120813f1e763;p=fa-stable.git diff --git a/sales/includes/db/sales_order_db.inc b/sales/includes/db/sales_order_db.inc index 55d9b8ff..eb5665ab 100644 --- a/sales/includes/db/sales_order_db.inc +++ b/sales/includes/db/sales_order_db.inc @@ -12,18 +12,21 @@ //---------------------------------------------------------------------------------------- function add_sales_order(&$order) { - global $loc_notification, $path_to_root; + global $loc_notification, $path_to_root, $Refs; begin_transaction(); - + hook_db_prewrite($order, $order->trans_type); + $order_no = get_next_trans_no($order->trans_type); $del_date = date2sql($order->due_date); $order_type = 0; // this is default on new order - $sql = "INSERT INTO ".TB_PREF."sales_orders (type, debtor_no, branch_code, customer_ref, comments, ord_date, + $total = $order->get_trans_total(); + $sql = "INSERT INTO ".TB_PREF."sales_orders (order_no, type, debtor_no, trans_type, branch_code, customer_ref, reference, comments, ord_date, order_type, ship_via, deliver_to, delivery_address, contact_phone, - contact_email, freight_cost, from_stk_loc, delivery_date) - VALUES (" .db_escape($order_type) . "," . db_escape($order->customer_id) . - ", " . db_escape($order->Branch) . ", ". + freight_cost, from_stk_loc, delivery_date, payment_terms, total) + VALUES (" .db_escape($order_no) . "," .db_escape($order_type) . "," . db_escape($order->customer_id) . + ", " .db_escape($order->trans_type) . "," .db_escape($order->Branch) . ", ". db_escape($order->cust_ref) .",". + db_escape($order->reference) .",". db_escape($order->Comments) .",'" . date2sql($order->document_date) . "', " . db_escape($order->sales_type) . ", " . @@ -31,14 +34,14 @@ function add_sales_order(&$order) db_escape($order->deliver_to) . "," . db_escape($order->delivery_address) . ", " . db_escape($order->phone) . ", " . - db_escape($order->email) . ", " . db_escape($order->freight_cost) .", " . db_escape($order->Location) .", " . - db_escape($del_date) . ")"; + db_escape($del_date) . "," . + db_escape($order->payment) . "," . + db_escape($total). ")"; db_query($sql, "order Cannot be Added"); - $order_no = db_insert_id(); $order->trans_no = array($order_no=>0); if ($loc_notification == 1) @@ -76,8 +79,8 @@ function add_sales_order(&$order) } } - $sql = "INSERT INTO ".TB_PREF."sales_order_details (order_no, stk_code, description, unit_price, quantity, discount_percent) VALUES ("; - $sql .= $order_no . + $sql = "INSERT INTO ".TB_PREF."sales_order_details (order_no, trans_type, stk_code, description, unit_price, quantity, discount_percent) VALUES ("; + $sql .= $order_no . ",".$order->trans_type . ",".db_escape($line->stock_id).", " .db_escape($line->item_description).", $line->price, $line->quantity, @@ -86,7 +89,10 @@ function add_sales_order(&$order) } /* inserted line items into sales order details */ - add_audit_trail(30, $order_no, $order->document_date); + add_audit_trail($order->trans_type, $order_no, $order->document_date); + $Refs->save($order->trans_type, $order_no, $order->reference); + + hook_db_postwrite($order, $order->trans_type); commit_transaction(); if ($loc_notification == 1 && count($st_ids) > 0) @@ -112,17 +118,23 @@ function add_sales_order(&$order) //---------------------------------------------------------------------------------------- -function delete_sales_order($order_no) +function delete_sales_order($order_no, $trans_type) { begin_transaction(); + hook_db_prevoid($trans_type, $order_no); + + $sql = "DELETE FROM ".TB_PREF."sales_orders WHERE order_no=" . db_escape($order_no) + . " AND trans_type=".db_escape($trans_type); - $sql = "DELETE FROM ".TB_PREF."sales_orders WHERE order_no=" . $order_no; db_query($sql, "order Header Delete"); - $sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" . $order_no; + $sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" + .db_escape($order_no) . " AND trans_type=".db_escape($trans_type); db_query($sql, "order Detail Delete"); - add_audit_trail(30, $order_no, Today(), _("Deleted.")); + delete_reference($trans_type, $order_no); + + add_audit_trail($trans_type, $order_no, Today(), _("Deleted.")); commit_transaction(); } @@ -132,8 +144,8 @@ function delete_sales_order($order_no) function update_sales_order_version($order) { foreach ($order as $so_num => $so_ver) { - $sql= 'UPDATE '.TB_PREF.'sales_orders SET version=version+1 WHERE order_no='. $so_num. - ' AND version='.$so_ver; + $sql= 'UPDATE '.TB_PREF.'sales_orders SET version=version+1 WHERE order_no='. db_escape($so_num). + ' AND version='.$so_ver . " AND trans_type=30"; db_query($sql, 'Concurrent editing conflict while sales order update'); } } @@ -142,19 +154,21 @@ function update_sales_order_version($order) function update_sales_order($order) { - global $loc_notification, $path_to_root; + global $loc_notification, $path_to_root, $Refs; $del_date = date2sql($order->due_date); $ord_date = date2sql($order->document_date); $order_no = key($order->trans_no); $version= current($order->trans_no); + $total = $order->get_trans_total(); begin_transaction(); - $sql = "UPDATE ".TB_PREF."sales_orders SET type =".$order->so_type." , + $sql = "UPDATE ".TB_PREF."sales_orders SET type =".db_escape($order->so_type)." , debtor_no = " . db_escape($order->customer_id) . ", branch_code = " . db_escape($order->Branch) . ", customer_ref = ". db_escape($order->cust_ref) .", + reference = ". db_escape($order->reference) .", comments = ". db_escape($order->Comments) .", ord_date = " . db_escape($ord_date) . ", order_type = " .db_escape($order->sales_type) . ", @@ -162,16 +176,17 @@ function update_sales_order($order) deliver_to = " . db_escape($order->deliver_to) . ", delivery_address = " . db_escape($order->delivery_address) . ", contact_phone = " .db_escape($order->phone) . ", - contact_email = " .db_escape($order->email) . ", freight_cost = " .db_escape($order->freight_cost) .", from_stk_loc = " .db_escape($order->Location) .", delivery_date = " .db_escape($del_date). ", - version = ".($version+1)." - WHERE order_no=" . $order_no ." - AND version=".$version; + version = ".($version+1).", + payment_terms = " .db_escape($order->payment). ", + total = ". db_escape($total) ." + WHERE order_no=" . db_escape($order_no) ." + AND trans_type=".$order->trans_type." AND version=".$version; db_query($sql, "order Cannot be Updated, this can be concurrent edition conflict"); - $sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" . $order_no; + $sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no =" . db_escape($order_no) . " AND trans_type=".$order->trans_type; db_query($sql, "Old order Cannot be Deleted"); @@ -193,8 +208,8 @@ function update_sales_order($order) FROM ".TB_PREF."loc_stock, " .TB_PREF."locations WHERE ".TB_PREF."loc_stock.loc_code=".TB_PREF."locations.loc_code - AND ".TB_PREF."loc_stock.stock_id = '" . $line->stock_id . "' - AND ".TB_PREF."loc_stock.loc_code = '" . $order->Location . "'"; + AND ".TB_PREF."loc_stock.stock_id = ".db_escape($line->stock_id)." + AND ".TB_PREF."loc_stock.loc_code = ".db_escape($order->Location); $res = db_query($sql,"a location could not be retreived"); $loc = db_fetch($res); if ($loc['email'] != "") @@ -213,10 +228,11 @@ function update_sales_order($order) } } $sql = "INSERT INTO ".TB_PREF."sales_order_details - (order_no, stk_code, description, unit_price, quantity, + (id, order_no, trans_type, stk_code, description, unit_price, quantity, discount_percent, qty_sent) VALUES ("; - $sql .= $order_no . "," + $sql .= db_escape($line->id ? $line->id : 0) . "," + .$order_no . ",".$order->trans_type."," .db_escape($line->stock_id) . "," .db_escape($line->item_description) . ", " .db_escape($line->price) . ", " @@ -228,7 +244,8 @@ function update_sales_order($order) } /* inserted line items into sales order details */ - add_audit_trail(30, $order_no, $order->document_date, _("Updated.")); + add_audit_trail($order->trans_type, $order_no, $order->document_date, _("Updated.")); + $Refs->save($order->trans_type, $order_no, $order->reference); commit_transaction(); if ($loc_notification == 1 && count($st_ids) > 0) { @@ -254,54 +271,54 @@ function update_sales_order($order) //---------------------------------------------------------------------------------------- -function get_sales_order_header($order_no) +function get_sales_order_header($order_no, $trans_type) { - $sql = "SELECT ".TB_PREF."sales_orders.*, " - .TB_PREF."debtors_master.name, " - .TB_PREF."debtors_master.curr_code, " - .TB_PREF."debtors_master.email AS master_email, " - .TB_PREF."locations.location_name, " - .TB_PREF."debtors_master.payment_terms, " - .TB_PREF."debtors_master.discount, " - .TB_PREF."sales_types.sales_type, " - .TB_PREF."sales_types.id AS sales_type_id, " - .TB_PREF."sales_types.tax_included, " - .TB_PREF."shippers.shipper_name, " - .TB_PREF."tax_groups.name AS tax_group_name , " - .TB_PREF."tax_groups.id AS tax_group_id - 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 - WHERE ".TB_PREF."sales_orders.order_type=".TB_PREF."sales_types.id - AND ".TB_PREF."cust_branch.branch_code = ".TB_PREF."sales_orders.branch_code - AND ".TB_PREF."cust_branch.tax_group_id = ".TB_PREF."tax_groups.id - AND ".TB_PREF."sales_orders.debtor_no = ".TB_PREF."debtors_master.debtor_no - AND ".TB_PREF."locations.loc_code = ".TB_PREF."sales_orders.from_stk_loc - AND ".TB_PREF."shippers.shipper_id = ".TB_PREF."sales_orders.ship_via - AND ".TB_PREF."sales_orders.order_no = " . $order_no ; + $sql = "SELECT sorder.*, " + ."cust.name, " + ."cust.curr_code, " + ."loc.location_name, " + ."cust.discount, " + ."stype.sales_type, " + ."stype.id AS sales_type_id, " + ."stype.tax_included, " + ."ship.shipper_name, " + ."tax_group.name AS tax_group_name , " + ."tax_group.id AS tax_group_id " + ."FROM ".TB_PREF."sales_orders sorder, " + .TB_PREF."debtors_master cust," + .TB_PREF."sales_types stype, " + .TB_PREF."tax_groups tax_group, " + .TB_PREF."cust_branch branch," + .TB_PREF."locations loc, " + .TB_PREF."shippers ship + WHERE sorder.order_type=stype.id + AND branch.branch_code = sorder.branch_code + AND branch.tax_group_id = tax_group.id + AND sorder.debtor_no = cust.debtor_no + AND loc.loc_code = sorder.from_stk_loc + AND ship.shipper_id = sorder.ship_via + AND sorder.trans_type = " . db_escape($trans_type) ." + AND sorder.order_no = " . db_escape($order_no ); + $result = db_query($sql, "order Retreival"); $num = db_num_rows($result); if ($num > 1) { - display_db_error("FATAL : sales order query returned a duplicate - " . db_num_rows($result), $sql, true); + display_warning("You have duplicate document in database: (type:$trans_type, number:$order_no)."); } else if ($num == 1) { return db_fetch($result); } else - display_db_error("FATAL : sales order return nothing - " . db_num_rows($result), $sql, true); + display_warning("You have missing or invalid sales document in database (type:$trans_type, number:$order_no)."); } //---------------------------------------------------------------------------------------- -function get_sales_order_details($order_no) { +function get_sales_order_details($order_no, $trans_type) { $sql = "SELECT id, stk_code, unit_price, " .TB_PREF."sales_order_details.description," .TB_PREF."sales_order_details.quantity, @@ -313,25 +330,27 @@ function get_sales_order_details($order_no) { .TB_PREF."stock_master.overhead_cost AS standard_cost FROM ".TB_PREF."sales_order_details, ".TB_PREF."stock_master WHERE ".TB_PREF."sales_order_details.stk_code = ".TB_PREF."stock_master.stock_id - AND order_no =" . $order_no . " ORDER BY id"; + AND order_no =" . db_escape($order_no) + ." AND trans_type = " . db_escape($trans_type) . " ORDER BY id"; return db_query($sql, "Retreive order Line Items"); } //---------------------------------------------------------------------------------------- -function read_sales_order($order_no, &$order) +function read_sales_order($order_no, &$order, $trans_type) { - $myrow = get_sales_order_header($order_no); - $order->trans_type = 30; + $myrow = get_sales_order_header($order_no, $trans_type); + + $order->trans_type = $myrow['trans_type']; $order->so_type = $myrow["type"]; $order->trans_no = array($order_no=> $myrow["version"]); $order->set_customer($myrow["debtor_no"], $myrow["name"], - $myrow["curr_code"], $myrow["discount"]); + $myrow["curr_code"], $myrow["discount"], $myrow["payment_terms"]); $order->set_branch($myrow["branch_code"], $myrow["tax_group_id"], - $myrow["tax_group_name"], $myrow["contact_phone"], $myrow["contact_email"]); + $myrow["tax_group_name"], $myrow["contact_phone"]); $order->set_sales_type($myrow["sales_type_id"], $myrow["sales_type"], $myrow["tax_included"], 0); // no default price calculations on edit @@ -343,11 +362,12 @@ function read_sales_order($order_no, &$order) $order->cust_ref = $myrow["customer_ref"]; $order->sales_type =$myrow["order_type"]; + $order->reference = $myrow["reference"]; $order->Comments = $myrow["comments"]; $order->due_date = sql2date($myrow["delivery_date"]); $order->document_date = sql2date($myrow["ord_date"]); - $result = get_sales_order_details($order_no); + $result = get_sales_order_details($order_no, $order->trans_type); if (db_num_rows($result) > 0) { $line_no=0; @@ -368,13 +388,18 @@ function read_sales_order($order_no, &$order) function sales_order_has_deliveries($order_no) { $sql = "SELECT SUM(qty_sent) FROM ".TB_PREF. - "sales_order_details WHERE order_no=$order_no"; + "sales_order_details WHERE order_no=".db_escape($order_no) + ." AND trans_type=".ST_SALESORDER; $result = db_query($sql, "could not query for sales order usage"); $row = db_fetch_row($result); - return ($row[0] > 0); + if ($row[0] > 0) + return true; // 2010-04-21 added check for eventually voided deliveries, Joe Hunt + $sql = "SELECT order_ FROM ".TB_PREF."debtor_trans WHERE type=".ST_CUSTDELIVERY." AND order_=".db_escape($order_no); + $result = db_query($sql,"The related delivery notes could not be retreived"); + return (db_num_rows($result) > 0); } //---------------------------------------------------------------------------------------- @@ -383,30 +408,26 @@ function close_sales_order($order_no) { // set the quantity of each item to the already sent quantity. this will mark item as closed. $sql = "UPDATE ".TB_PREF."sales_order_details - SET quantity = qty_sent, - type = 0, - WHERE order_no = $order_no"; + SET quantity = qty_sent WHERE order_no = ".db_escape($order_no) + ." AND trans_type=".ST_SALESORDER; db_query($sql, "The sales order detail record could not be updated"); } //--------------------------------------------------------------------------------------------------------------- -function get_invoice_duedate($debtorno, $invdate) +function get_invoice_duedate($terms, $invdate) { if (!is_date($invdate)) { return new_doc_date(); } - $sql = "SELECT ".TB_PREF."debtors_master.debtor_no, ".TB_PREF."debtors_master.payment_terms, ".TB_PREF."payment_terms.* FROM ".TB_PREF."debtors_master, - ".TB_PREF."payment_terms WHERE ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator AND - ".TB_PREF."debtors_master.debtor_no = '$debtorno'"; - - $result = db_query($sql,"The customer details could not be retrieved"); - $myrow = db_fetch($result); - - if (db_num_rows($result) == 0) + + $myrow = get_payment_terms($terms); + + if (!$myrow) return $invdate; + if ($myrow['day_in_following_month'] > 0) $duedate = add_days(end_month($invdate), $myrow['day_in_following_month']); else @@ -417,25 +438,29 @@ function get_invoice_duedate($debtorno, $invdate) function get_customer_to_order($customer_id) { // Now check to ensure this account is not on hold */ - $sql = "SELECT ".TB_PREF."debtors_master.name, " - .TB_PREF."debtors_master.address, " - .TB_PREF."credit_status.dissallow_invoices, " - .TB_PREF."debtors_master.sales_type AS salestype, " - .TB_PREF."debtors_master.dimension_id, " - .TB_PREF."debtors_master.dimension2_id, " - .TB_PREF."sales_types.sales_type, " - .TB_PREF."sales_types.tax_included, " - .TB_PREF."sales_types.factor, " - .TB_PREF."debtors_master.curr_code, " - .TB_PREF."debtors_master.discount," - .TB_PREF."debtors_master.pymt_discount - FROM ".TB_PREF."debtors_master, " + $sql = "SELECT cust.name, + cust.address, " + .TB_PREF."credit_status.dissallow_invoices, + cust.sales_type AS salestype, + cust.dimension_id, + cust.dimension2_id, + stype.sales_type, + stype.tax_included, + stype.factor, + cust.curr_code, + cust.discount, + cust.payment_terms, + cust.pymt_discount, + cust.credit_limit - Sum(IFNULL(IF(trans.type=11 OR trans.type=12 OR trans.type=2, + -1, 1) * (ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount),0)) as cur_credit + FROM ".TB_PREF."debtors_master cust + LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.type!=".ST_CUSTDELIVERY." AND trans.debtor_no = cust.debtor_no," .TB_PREF."credit_status, " - .TB_PREF."sales_types - WHERE ".TB_PREF."debtors_master.sales_type=" - .TB_PREF."sales_types.id - AND ".TB_PREF."debtors_master.credit_status=".TB_PREF."credit_status.id - AND ".TB_PREF."debtors_master.debtor_no = '" . $customer_id . "'"; + .TB_PREF."sales_types stype + WHERE cust.sales_type=stype.id + AND cust.credit_status=".TB_PREF."credit_status.id + AND cust.debtor_no = ".db_escape($customer_id) + ." GROUP by cust.debtor_no"; $result =db_query($sql,"Customer Record Retreive"); return db_fetch($result); @@ -447,9 +472,7 @@ function get_branch_to_order($customer_id, $branch_id) { $sql = "SELECT ".TB_PREF."cust_branch.br_name, " .TB_PREF."cust_branch.br_address, " .TB_PREF."cust_branch.br_post_address, " - .TB_PREF."cust_branch.phone, " - .TB_PREF."cust_branch.email, - default_location, location_name, default_ship_via, " + ." default_location, location_name, default_ship_via, " .TB_PREF."tax_groups.name AS tax_group_name, " .TB_PREF."tax_groups.id AS tax_group_id FROM ".TB_PREF."cust_branch, " @@ -457,9 +480,90 @@ function get_branch_to_order($customer_id, $branch_id) { .TB_PREF."locations WHERE ".TB_PREF."cust_branch.tax_group_id = ".TB_PREF."tax_groups.id AND ".TB_PREF."locations.loc_code=default_location - AND ".TB_PREF."cust_branch.branch_code='" . $branch_id . "' - AND ".TB_PREF."cust_branch.debtor_no = '" . $customer_id . "'"; + AND ".TB_PREF."cust_branch.branch_code=".db_escape($branch_id)." + AND ".TB_PREF."cust_branch.debtor_no = ".db_escape($customer_id); return db_query($sql,"Customer Branch Record Retreive"); } + +function get_sql_for_sales_orders_view($selected_customer, $trans_type) +{ + global $selected_stock_item; + + $sql = "SELECT + sorder.order_no, + sorder.reference, + debtor.name, + branch.br_name," + .($_POST['order_view_mode']=='InvoiceTemplates' + || $_POST['order_view_mode']=='DeliveryTemplates' ? + "sorder.comments, " : "sorder.customer_ref, ") + ."sorder.ord_date, + sorder.delivery_date, + sorder.deliver_to, + Sum(line.unit_price*line.quantity*(1-line.discount_percent))+freight_cost AS OrderValue, + sorder.type, + debtor.curr_code, + Sum(line.qty_sent) AS TotDelivered, + Sum(line.quantity) AS TotQuantity + FROM ".TB_PREF."sales_orders as sorder, " + .TB_PREF."sales_order_details as line, " + .TB_PREF."debtors_master as debtor, " + .TB_PREF."cust_branch as branch + WHERE sorder.order_no = line.order_no + AND sorder.trans_type = line.trans_type + AND sorder.trans_type = ".db_escape($trans_type)." + AND sorder.debtor_no = debtor.debtor_no + AND sorder.branch_code = branch.branch_code + AND debtor.debtor_no = branch.debtor_no"; + + if (isset($_POST['OrderNumber']) && $_POST['OrderNumber'] != "") + { + // search orders with number like + $number_like = "%".$_POST['OrderNumber']; + $sql .= " AND sorder.order_no LIKE ".db_escape($number_like) + ." GROUP BY sorder.order_no"; + } + elseif (isset($_POST['OrderReference']) && $_POST['OrderReference'] != "") + { + // search orders with reference like + $number_like = "%".$_POST['OrderReference']."%"; + $sql .= " AND sorder.reference LIKE ".db_escape($number_like) + ." GROUP BY sorder.order_no"; + } + else // ... or select inquiry constraints + { + if ($_POST['order_view_mode']!='DeliveryTemplates' && $_POST['order_view_mode']!='InvoiceTemplates') + { + $date_after = date2sql($_POST['OrdersAfterDate']); + $date_before = date2sql($_POST['OrdersToDate']); + + $sql .= " AND sorder.ord_date >= '$date_after'" + ." AND sorder.ord_date <= '$date_before'"; + } + if ($trans_type == ST_SALESQUOTE && !check_value('show_all')) + $sql .= " AND sorder.delivery_date >= '".date2sql(Today())."'"; + if ($selected_customer != -1) + $sql .= " AND sorder.debtor_no=".db_escape($selected_customer); + + if (isset($selected_stock_item)) + $sql .= " AND line.stk_code=".db_escape($selected_stock_item); + + if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != ALL_TEXT) + $sql .= " AND sorder.from_stk_loc = ".db_escape($_POST['StockLocation']); + + if ($_POST['order_view_mode']=='OutstandingOnly') + $sql .= " AND line.qty_sent < line.quantity"; + elseif ($_POST['order_view_mode']=='InvoiceTemplates' || $_POST['order_view_mode']=='DeliveryTemplates') + $sql .= " AND sorder.type=1"; + + $sql .= " GROUP BY sorder.order_no, + sorder.debtor_no, + sorder.branch_code, + sorder.customer_ref, + sorder.ord_date, + sorder.deliver_to"; + } + return $sql; +} ?> \ No newline at end of file