X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sales%2Fincludes%2Fdb%2Fsales_order_db.inc;h=4ccc0fe810f327f0d3b2459079ec77169c49ef80;hb=a52174853a7922acd6626ac61032ec7674bce9cd;hp=fd6dd9856f8f72e659c524e286280f882c189b87;hpb=303eb17e9cdd9702eee9fdfcaee51e654a1da541;p=fa-stable.git diff --git a/sales/includes/db/sales_order_db.inc b/sales/includes/db/sales_order_db.inc index fd6dd985..4ccc0fe8 100644 --- a/sales/includes/db/sales_order_db.inc +++ b/sales/includes/db/sales_order_db.inc @@ -19,9 +19,10 @@ function add_sales_order(&$order) $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"); @@ -156,6 +157,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,12 +173,12 @@ 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). " + payment_terms = " .db_escape($order->payment). ", + total = ". db_escape($total) ." WHERE order_no=" . $order_no ." AND trans_type=".$order->trans_type." AND version=".$version; db_query($sql, "order Cannot be Updated, this can be concurrent edition conflict"); @@ -269,33 +271,32 @@ 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"); @@ -346,7 +347,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 @@ -391,7 +392,11 @@ function sales_order_has_deliveries($order_no) $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); } //---------------------------------------------------------------------------------------- @@ -430,26 +435,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); @@ -461,9 +469,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, " @@ -515,6 +521,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')