X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sales%2Fincludes%2Fdb%2Fsales_order_db.inc;h=eb5665ab71c46165a324d3da8c31661bc826855d;hb=0d0fd1bc61f435acf0d4e4bd6eba719e1b10e07b;hp=cf7b668239a203d784d52a1032bb0c4cc15fc4f9;hpb=5f06887dedd4d14701864fb72994d7e20352086d;p=fa-stable.git diff --git a/sales/includes/db/sales_order_db.inc b/sales/includes/db/sales_order_db.inc index cf7b6682..eb5665ab 100644 --- a/sales/includes/db/sales_order_db.inc +++ b/sales/includes/db/sales_order_db.inc @@ -15,13 +15,14 @@ function add_sales_order(&$order) 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 + $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, payment_terms) + 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) .",". @@ -33,11 +34,11 @@ 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($order->payment) . ")"; + db_escape($order->payment) . "," . + db_escape($total). ")"; db_query($sql, "order Cannot be Added"); @@ -90,6 +91,8 @@ function add_sales_order(&$order) 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) @@ -118,6 +121,7 @@ function add_sales_order(&$order) 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); @@ -140,7 +144,7 @@ function delete_sales_order($order_no, $trans_type) 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. + $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'); } @@ -156,6 +160,7 @@ function update_sales_order($order) $ord_date = date2sql($order->document_date); $order_no = key($order->trans_no); $version= current($order->trans_no); + $total = $order->get_trans_total(); begin_transaction(); @@ -171,17 +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).", - payment_terms = " .db_escape($order->payment). " - WHERE order_no=" . $order_no ." + 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 . " AND trans_type=".$order->trans_type; + $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"); @@ -223,10 +228,11 @@ function update_sales_order($order) } } $sql = "INSERT INTO ".TB_PREF."sales_order_details - (order_no, trans_type, 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 . ",".$order->trans_type."," + $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) . ", " @@ -239,7 +245,6 @@ function update_sales_order($order) } /* inserted line items into sales order details */ add_audit_trail($order->trans_type, $order_no, $order->document_date, _("Updated.")); - $Refs->delete($order->trans_type, $order_no); $Refs->save($order->trans_type, $order_no, $order->reference); commit_transaction(); if ($loc_notification == 1 && count($st_ids) > 0) @@ -268,47 +273,46 @@ function update_sales_order($order) 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.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.trans_type = " . db_escape($trans_type) ." - AND ".TB_PREF."sales_orders.order_no = " . db_escape($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)."); } @@ -335,6 +339,7 @@ function get_sales_order_details($order_no, $trans_type) { function read_sales_order($order_no, &$order, $trans_type) { + $myrow = get_sales_order_header($order_no, $trans_type); $order->trans_type = $myrow['trans_type']; @@ -345,7 +350,7 @@ function read_sales_order($order_no, &$order, $trans_type) $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 @@ -384,13 +389,17 @@ function sales_order_has_deliveries($order_no) { $sql = "SELECT SUM(qty_sent) FROM ".TB_PREF. "sales_order_details WHERE order_no=".db_escape($order_no) - ." AND trans_type=".ST_SALESORDER.""; + ." 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); } //---------------------------------------------------------------------------------------- @@ -400,7 +409,7 @@ 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 WHERE order_no = ".db_escape($order_no) - ." AND trans_type=".ST_SALESORDER.""; + ." AND trans_type=".ST_SALESORDER; db_query($sql, "The sales order detail record could not be updated"); } @@ -429,26 +438,29 @@ function get_invoice_duedate($terms, $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.payment_terms," - .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 = ".db_escape($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); @@ -460,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, " @@ -502,7 +512,7 @@ function get_sql_for_sales_orders_view($selected_customer, $trans_type) .TB_PREF."cust_branch as branch WHERE sorder.order_no = line.order_no AND sorder.trans_type = line.trans_type - AND sorder.trans_type = $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"; @@ -514,6 +524,13 @@ function get_sql_for_sales_orders_view($selected_customer, $trans_type) $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')