X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sales%2Fincludes%2Fdb%2Fsales_order_db.inc;h=eb5665ab71c46165a324d3da8c31661bc826855d;hb=46c5f7a65a7659a44ae8254c63152074363d3987;hp=b12011d447b04390e1a888884c931253810408a5;hpb=35f482e2a9246960de37e5f1d975c734e08951e6;p=fa-stable.git diff --git a/sales/includes/db/sales_order_db.inc b/sales/includes/db/sales_order_db.inc index b12011d4..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) + 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,10 +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($del_date) . "," . + db_escape($order->payment) . "," . + db_escape($total). ")"; db_query($sql, "order Cannot be Added"); @@ -89,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) @@ -117,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); @@ -139,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'); } @@ -155,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(); @@ -170,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 ." + 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 . " 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"); @@ -238,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) @@ -267,48 +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.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.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']; @@ -342,10 +347,10 @@ function read_sales_order($order_no, &$order, $trans_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 @@ -384,7 +389,7 @@ 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"); @@ -404,28 +409,25 @@ 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"); } //--------------------------------------------------------------------------------------------------------------- -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 = ".db_escape($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 @@ -436,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 = ".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); @@ -466,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, " @@ -481,4 +485,85 @@ function get_branch_to_order($customer_id, $branch_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