From 1481d782477692fd972cc1c13b6585a95971769f Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Fri, 25 Jun 2010 22:13:43 +0000 Subject: [PATCH] Fixed sales database design to ensure document relations consistency on line level. --- admin/db/fiscalyears_db.inc | 2 +- admin/db/voiding_db.inc | 9 +- reporting/includes/header2.inc | 22 +--- sales/customer_invoice.php | 8 +- sales/includes/cart_class.inc | 37 +++--- sales/includes/db/cust_trans_db.inc | 76 +---------- sales/includes/db/cust_trans_details_db.inc | 12 +- sales/includes/db/payment_db.inc | 2 +- sales/includes/db/sales_credit_db.inc | 6 +- sales/includes/db/sales_delivery_db.inc | 4 +- sales/includes/db/sales_invoice_db.inc | 27 ++-- sales/includes/sales_db.inc | 137 ++++++++++++++------ sales/view/view_invoice.php | 2 +- sales/view/view_sales_order.php | 78 ++++++----- 14 files changed, 199 insertions(+), 223 deletions(-) diff --git a/admin/db/fiscalyears_db.inc b/admin/db/fiscalyears_db.inc index ddbe19d0..ebf6d477 100644 --- a/admin/db/fiscalyears_db.inc +++ b/admin/db/fiscalyears_db.inc @@ -236,7 +236,7 @@ function delete_this_fiscalyear($selected_id) { if ($row['type'] == ST_SALESINVOICE) { - $deliveries = get_parent_trans(ST_SALESINVOICE,$row['trans_no']); + $deliveries = get_sales_parent_numbers($row['type'], $row['trans_no']); foreach ($deliveries as $delivery) { $sql = "DELETE FROM ".TB_PREF."debtor_trans_details WHERE debtor_trans_no = $delivery AND debtor_trans_type = ".ST_CUSTDELIVERY; diff --git a/admin/db/voiding_db.inc b/admin/db/voiding_db.inc index 6bda4029..62165c1f 100644 --- a/admin/db/voiding_db.inc +++ b/admin/db/voiding_db.inc @@ -45,13 +45,10 @@ function void_transaction($type, $type_no, $date_, $memo_) return false; if ($type == ST_CUSTDELIVERY) // added 04 Oct 2008 by Joe Hunt. If delivery note has a not voided invoice, then NO. { - $delivery = get_customer_trans($type_no, $type); - if ($delivery['trans_link'] != 0) - { - if (get_voided_entry(ST_SALESINVOICE, $delivery['trans_link']) === false) + $childs = get_sales_child_trans($type_no, $type); + if ($childs && db_num_rows($childs)) return false; - } - } + } post_void_customer_trans($type, $type_no); break; diff --git a/reporting/includes/header2.inc b/reporting/includes/header2.inc index 362ebaac..c7b5db0a 100644 --- a/reporting/includes/header2.inc +++ b/reporting/includes/header2.inc @@ -246,22 +246,14 @@ $col += $width; if ($doctype == ST_SALESINVOICE) { - $deliveries = get_parent_trans(ST_SALESINVOICE, $this->formData['trans_no']); - $line = ""; - foreach ($deliveries as $delivery) + $deliveries = get_sales_parent_numbers(ST_SALESINVOICE, $this->formData['trans_no']); + if ($print_invoice_no == 0) { - if ($print_invoice_no == 0) - { - $ref = get_reference(ST_CUSTDELIVERY, $delivery); - if ($ref) - $delivery = $ref; - } - if ($line == "") - $line .= "$delivery"; - else - $line .= ",$delivery"; - } - $this->TextWrap($col, $this->row, $width, $line, 'C'); + foreach($deliveries as $n => $delivery) { + $deliveries[$n] = get_reference(ST_CUSTDELIVERY, $delivery); + } + } + $this->TextWrap($col, $this->row, $width, implode(',', $deliveries), 'C'); } elseif ($doctype == ST_CUSTDELIVERY) { diff --git a/sales/customer_invoice.php b/sales/customer_invoice.php index c4ef21f1..152ca1e5 100644 --- a/sales/customer_invoice.php +++ b/sales/customer_invoice.php @@ -128,7 +128,7 @@ if ( (isset($_GET['DeliveryNumber']) && ($_GET['DeliveryNumber'] > 0) ) } elseif (isset($_GET['ModifyInvoice']) && $_GET['ModifyInvoice'] > 0) { - if ( get_parent_trans(ST_SALESINVOICE, $_GET['ModifyInvoice']) == 0) { // 1.xx compatibility hack + if ( get_sales_parent_numbers(ST_SALESINVOICE, $_GET['ModifyInvoice']) == 0) { // 1.xx compatibility hack echo"

" . _("There are no delivery notes for this invoice.
Most likely this invoice was created in Front Accounting version prior to 2.0 and therefore can not be modified.") . "
"; @@ -226,8 +226,10 @@ function copy_to_cart() $cart->freight_cost = input_num('ChargeFreightCost'); $cart->document_date = $_POST['InvoiceDate']; $cart->due_date = $_POST['due_date']; - $cart->payment = $_POST['payment']; - $cart->payment_terms = get_payment_terms($_POST['payment']); + if ($cart->pos != -1) { + $cart->payment = $_POST['payment']; + $cart->payment_terms = get_payment_terms($_POST['payment']); + } $cart->Comments = $_POST['Comments']; if ($_SESSION['Items']->trans_no == 0) $cart->reference = $_POST['ref']; diff --git a/sales/includes/cart_class.inc b/sales/includes/cart_class.inc index 4831c4a4..34db947a 100644 --- a/sales/includes/cart_class.inc +++ b/sales/includes/cart_class.inc @@ -55,7 +55,6 @@ class cart var $Location; var $location_name; var $order_no; // the original order number - var $trans_link = 0; var $ship_via; var $freight_cost = 0; @@ -112,8 +111,6 @@ class cart for($line_no = 0; $line_no < count($this->line_items); $line_no++) { $line = &$this->line_items[$line_no]; $line->src_id = $line->id; // save src line ids for update -// $line->qty_min = 0; -// $line->qty_max = $line->quantity - $line->qty_done; $line->qty_dispatched = $type == ST_CUSTCREDIT ? '0' : $line->quantity - $line->qty_done; $line->qty_old = 0; } @@ -138,24 +135,32 @@ class cart // function set_parent_constraints($sodata, $src_no) { + $srcdetails = get_sales_parent_lines($this->trans_type, $src_no); + $src_type = get_parent_type($this->trans_type); - if ($src_type == ST_SALESORDER || $src_type == 0) { // get src data from sales_orders + if ($src_type == ST_SALESORDER || $src_type == 0) { $this->src_docs = array( $sodata['order_no']=>$sodata['version']); - $srcdetails = get_sales_order_details($this->order_no, $this->trans_type); } else { // get src_data from debtor_trans - $this->src_docs = get_customer_trans_version($src_type, - get_parent_trans($this->trans_type, $src_no)); - $srcdetails = get_customer_trans_details($src_type, array_keys($this->src_docs)); + $srcnum = array(); + foreach($srcdetails as $line) { + $srcnum = $line['debtor_trans_no']; + } + $this->src_docs = get_customer_trans_version($src_type, array_values($srcnum)); } // calculate & save: qtys on other docs and free qtys on src doc - for($line_no = 0; $srcline = db_fetch($srcdetails); $line_no++) { + $line_no = 0; + // Loop speed optimisation below depends on fact + // that child line_items contains subset of parent lines in _the_same_ order ! + while ($line_no < count($this->line_items) && $srcline = db_fetch($srcdetails)) { $line = &$this->line_items[$line_no]; - - $line->src_id = $srcline['id']; // save src line ids for update - if ($this->trans_type == ST_SALESINVOICE) - $line->src_no = $srcline['debtor_trans_no']; - $line->qty_old = $line->qty_dispatched = $line->quantity; - $line->quantity += $srcline['quantity'] - $srcline['qty_done']; // add free qty on src doc + if ($srcline['id'] == $line->src_id) { + if ($this->trans_type == ST_SALESINVOICE) + $line->src_no = $srcline['debtor_trans_no']; + $line->qty_old = $line->qty_dispatched = $line->quantity; + $line->quantity += $srcline['quantity'] - $srcline['qty_done']; // add free qty on src doc + $line_no++; + break; + } } } //------------------------------------------------------------------------- @@ -181,7 +186,7 @@ class cart $this->delivery_address = $sodata["delivery_address"]; } // child transaction reedition - update with parent info unless it is freehand - if (!$no_edit && !($type==ST_CUSTCREDIT && $this->trans_link==0)) + if (!$no_edit) $this->set_parent_constraints($sodata, $trans_no[0]); } // prepare qtys for derivative document entry (not used in display) diff --git a/sales/includes/db/cust_trans_db.inc b/sales/includes/db/cust_trans_db.inc index ef693a6c..13493b73 100644 --- a/sales/includes/db/cust_trans_db.inc +++ b/sales/includes/db/cust_trans_db.inc @@ -9,38 +9,6 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the License here . ***********************************************************************/ -//------------------------------------------------------------------------------ -// Retreive parent document number(s) for given transaction -// -function get_parent_trans($trans_type, $trans_no) { - - $sql = 'SELECT trans_link FROM - '.TB_PREF.'debtor_trans WHERE - (trans_no='.db_escape($trans_no).' AND type='.db_escape($trans_type).' AND trans_link!=0)'; - - $result = db_query($sql, 'Parent document numbers cannot be retrieved'); - - if (db_num_rows($result)) { - $link = db_fetch($result); - return array($link['trans_link']); - } - if ($trans_type!=ST_SALESINVOICE) return 0; // this is credit note with no parent invoice - // invoice: find batch invoice parent trans. - $sql = 'SELECT trans_no FROM - '.TB_PREF.'debtor_trans WHERE - (trans_link='.db_escape($trans_no).' AND type='. get_parent_type($trans_type) .')'; - - $result = db_query($sql, 'Delivery links cannot be retrieved'); - - $delivery = array(); - if(db_num_rows($result)>0) { - while($link = db_fetch($result)) { - $delivery[] = $link['trans_no']; - } - } - return count($delivery) ? $delivery : 0; -} - //---------------------------------------------------------------------------------------- // Mark changes in debtor_trans_details // @@ -87,7 +55,7 @@ function get_customer_trans_version($type, $trans_no) { // date_ is display date (non-sql) function write_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo, $date_, $reference, $Total, $discount=0, $Tax=0, $Freight=0, $FreightTax=0, - $sales_type=0, $order_no=0, $trans_link=0, $ship_via=0, $due_date="", + $sales_type=0, $order_no=0, $ship_via=0, $due_date="", $AllocAmt=0, $rate=0, $dimension_id=0, $dimension2_id=0, $payment_terms=null) { $new = $trans_no==0; @@ -114,14 +82,14 @@ function write_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo, reference, tpe, order_, ov_amount, ov_discount, ov_gst, ov_freight, ov_freight_tax, - rate, ship_via, alloc, trans_link, + rate, ship_via, alloc, dimension_id, dimension2_id, payment_terms ) VALUES ($trans_no, ".db_escape($trans_type).", ".db_escape($debtor_no).", ".db_escape($BranchNo).", '$SQLDate', '$SQLDueDate', ".db_escape($reference).", ".db_escape($sales_type).", ".db_escape($order_no).", $Total, ".db_escape($discount).", $Tax, ".db_escape($Freight).", - $FreightTax, $rate, ".db_escape($ship_via).", $AllocAmt, ".db_escape($trans_link).", + $FreightTax, $rate, ".db_escape($ship_via).", $AllocAmt, ".db_escape($dimension_id).", ".db_escape($dimension2_id).", " .db_escape($payment_terms).")"; } else { // may be optional argument should stay unchanged ? @@ -131,7 +99,7 @@ function write_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo, reference=".db_escape($reference).", tpe=".db_escape($sales_type).", order_=".db_escape($order_no).", ov_amount=$Total, ov_discount=".db_escape($discount).", ov_gst=$Tax, ov_freight=".db_escape($Freight).", ov_freight_tax=$FreightTax, rate=$rate, - ship_via=".db_escape($ship_via).", alloc=$AllocAmt, trans_link=$trans_link, + ship_via=".db_escape($ship_via).", alloc=$AllocAmt, dimension_id=".db_escape($dimension_id).", dimension2_id=".db_escape($dimension2_id).", payment_terms=".db_escape($payment_terms)." WHERE trans_no=$trans_no AND type=".db_escape($trans_type); @@ -147,7 +115,7 @@ function write_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo, function reinsert_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo, $date_, $reference, $Total, $discount=0, $Tax=0, $Freight=0, $FreightTax=0, - $sales_type=0, $order_no=0, $trans_link=0, $ship_via=0, $due_date="", + $sales_type=0, $order_no=0, $ship_via=0, $due_date="", $AllocAmt=0, $rate=0, $dimension_id=0, $dimension2_id=0) { if ($trans_no == '') @@ -173,14 +141,14 @@ function reinsert_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo, reference, tpe, order_, ov_amount, ov_discount, ov_gst, ov_freight, ov_freight_tax, - rate, ship_via, alloc, trans_link, + rate, ship_via, alloc, dimension_id, dimension2_id ) VALUES ($trans_no, ".db_escape($trans_type).", ".db_escape($debtor_no).", ".db_escape($BranchNo).", '$SQLDate', '$SQLDueDate', ".db_escape($reference).", ".db_escape($sales_type).", ".db_escape($order_no).", $Total, ".db_escape($discount).", $Tax, ".db_escape($Freight).", - $FreightTax, $rate, ".db_escape($ship_via).", $AllocAmt, ".db_escape($trans_link).", + $FreightTax, $rate, ".db_escape($ship_via).", $AllocAmt, ".db_escape($dimension_id).", ".db_escape($dimension2_id).")"; db_query($sql, "The debtor transaction record could not be inserted"); @@ -297,27 +265,6 @@ function get_customer_trans_order($type, $type_no) //---------------------------------------------------------------------------------------- -function get_related_documents($type, $trans_no) -{ - $sql = "SELECT * FROM ".TB_PREF."debtor_trans WHERE type=$type AND order_=".db_escape($trans_no); - return db_query($sql,"The related documents could not be retreived"); -} -//---------------------------------------------------------------------------------------- - -function get_related_credits($inv_numbers) -{ - // FIXME - credit notes retrieved here should be those linked to invoices containing - // at least one line from this order - - - $sql = "SELECT * FROM ".TB_PREF."debtor_trans WHERE type=".ST_CUSTCREDIT - ." AND trans_link IN(". implode(',', array_values($inv_numbers)).")"; - - return db_query($sql,"The related credit notes could not be retreived"); -} - -//---------------------------------------------------------------------------------------- - function get_customer_details_from_trans($type, $type_no) { $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."cust_branch.br_name @@ -370,15 +317,6 @@ function post_void_customer_trans($type, $type_no) //---------------------------------------------------------------------------------------- -function get_customer_trans_link($type, $type_no) -{ - $row = db_query("SELECT trans_link from ".TB_PREF."debtor_trans - WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no), - "could not get transaction link for type=$type and trans_no=$type_no"); - return $row[0]; -} -//---------------------------------------------------------------------------------------- - function get_sql_for_customer_inquiry() { $date_after = date2sql($_POST['TransAfterDate']); diff --git a/sales/includes/db/cust_trans_details_db.inc b/sales/includes/db/cust_trans_details_db.inc index 86310ffa..4300e94b 100644 --- a/sales/includes/db/cust_trans_details_db.inc +++ b/sales/includes/db/cust_trans_details_db.inc @@ -41,7 +41,7 @@ if (!is_array($debtor_trans_no)) function void_customer_trans_details($type, $type_no) { $sql = "UPDATE ".TB_PREF."debtor_trans_details SET quantity=0, unit_price=0, - unit_tax=0, discount_percent=0, standard_cost=0 + unit_tax=0, discount_percent=0, standard_cost=0, src_id=0 WHERE debtor_trans_no=".db_escape($type_no)." AND debtor_trans_type=".db_escape($type); @@ -53,7 +53,7 @@ function void_customer_trans_details($type, $type_no) //---------------------------------------------------------------------------------------- function write_customer_trans_detail_item($debtor_trans_type, $debtor_trans_no, $stock_id, $description, - $quantity, $unit_price, $unit_tax, $discount_percent, $std_cost, $line_id=0) + $quantity, $unit_price, $unit_tax, $discount_percent, $std_cost, $src_id, $line_id=0) { if ($line_id!=0) $sql = "UPDATE ".TB_PREF."debtor_trans_details SET @@ -63,15 +63,17 @@ function write_customer_trans_detail_item($debtor_trans_type, $debtor_trans_no, unit_price=$unit_price, unit_tax=$unit_tax, discount_percent=$discount_percent, - standard_cost=$std_cost WHERE + standard_cost=$std_cost, + src_id=".db_escape($src_id)." WHERE id=".db_escape($line_id); else $sql = "INSERT INTO ".TB_PREF."debtor_trans_details (debtor_trans_no, debtor_trans_type, stock_id, description, quantity, unit_price, - unit_tax, discount_percent, standard_cost) + unit_tax, discount_percent, standard_cost, src_id) VALUES (".db_escape($debtor_trans_no).", ".db_escape($debtor_trans_type).", ".db_escape($stock_id). ", ".db_escape($description).", - $quantity, $unit_price, $unit_tax, $discount_percent, $std_cost)"; + $quantity, $unit_price, $unit_tax, + $discount_percent, $std_cost,".db_escape($src_id).")"; db_query($sql, "The debtor transaction detail could not be written"); } diff --git a/sales/includes/db/payment_db.inc b/sales/includes/db/payment_db.inc index a73bc8ad..2f9e5ac2 100644 --- a/sales/includes/db/payment_db.inc +++ b/sales/includes/db/payment_db.inc @@ -22,7 +22,7 @@ function write_customer_payment($trans_no, $customer_id, $branch_id, $bank_accou $company_record = get_company_prefs(); $payment_no = write_customer_trans(ST_CUSTPAYMENT, $trans_no, $customer_id, $branch_id, - $date_, $ref, $amount, $discount, 0, 0, 0, 0, 0, 0, 0, "", 0, $rate); + $date_, $ref, $amount, $discount, 0, 0, 0, 0, 0, 0, "", 0, $rate); $bank_gl_account = get_bank_gl_account($bank_account); diff --git a/sales/includes/db/sales_credit_db.inc b/sales/includes/db/sales_credit_db.inc index 1d44fcb6..6f188153 100644 --- a/sales/includes/db/sales_credit_db.inc +++ b/sales/includes/db/sales_credit_db.inc @@ -71,15 +71,13 @@ function write_credit_note(&$credit_note, $write_off_acc) $credit_note->Branch, $credit_date, $credit_note->reference, $credit_note_total, 0, $items_added_tax, $credit_note->freight_cost, $freight_added_tax, - $credit_note->sales_type, - $credit_note->order_no, $credit_invoice, $credit_note->ship_via, + $credit_note->sales_type, $credit_note->order_no, $credit_note->ship_via, null, $alloc, 0, $credit_note->dimension_id, $credit_note->dimension2_id, $credit_note->payment); // 2008-06-14 extra $alloc, 2008-11-12 dimension_id Joe Hunt if ($trans_no==0) { $credit_note->trans_no = array($credit_no=>0); - set_document_parent($credit_note); } else { delete_comments(ST_CUSTCREDIT, $credit_no); void_cust_allocations(ST_CUSTCREDIT, $credit_no, $credit_date); @@ -114,7 +112,7 @@ function write_credit_note(&$credit_note, $write_off_acc) write_customer_trans_detail_item(ST_CUSTCREDIT, $credit_no, $credit_line->stock_id, $credit_line->item_description, $credit_line->qty_dispatched, $credit_line->line_price(), $line_tax, $credit_line->discount_percent, - $credit_line->standard_cost, $trans_no==0 ? 0: $credit_line->id); + $credit_line->src_id, $credit_line->standard_cost, $trans_no==0 ? 0: $credit_line->id); add_credit_movements_item($credit_note, $credit_line, $credit_type, $line_taxfree_price+$line_tax, $credit_invoice); diff --git a/sales/includes/db/sales_delivery_db.inc b/sales/includes/db/sales_delivery_db.inc index db34deda..8678aae8 100644 --- a/sales/includes/db/sales_delivery_db.inc +++ b/sales/includes/db/sales_delivery_db.inc @@ -42,7 +42,7 @@ function write_sales_delivery(&$delivery,$bo_policy) $delivery->tax_included ? 0 : $tax_total-$freight_tax, $delivery->freight_cost, $delivery->tax_included ? 0 : $freight_tax, - $delivery->sales_type, $delivery->order_no, 0, + $delivery->sales_type, $delivery->order_no, $delivery->ship_via, $delivery->due_date, 0, 0, $delivery->dimension_id, $delivery->dimension2_id, $delivery->payment); @@ -72,7 +72,7 @@ function write_sales_delivery(&$delivery,$bo_policy) write_customer_trans_detail_item(ST_CUSTDELIVERY, $delivery_no, $delivery_line->stock_id, $delivery_line->item_description, $delivery_line->qty_dispatched, $delivery_line->line_price(), $line_tax, - $delivery_line->discount_percent, $delivery_line->standard_cost, + $delivery_line->discount_percent, $delivery_line->standard_cost, $delivery_line->src_id, $trans_no ? $delivery_line->id : 0); // Now update sales_order_details for the quantity delivered diff --git a/sales/includes/db/sales_invoice_db.inc b/sales/includes/db/sales_invoice_db.inc index bd191780..3b540d55 100644 --- a/sales/includes/db/sales_invoice_db.inc +++ b/sales/includes/db/sales_invoice_db.inc @@ -35,10 +35,6 @@ function write_sales_invoice(&$invoice) $items_total = $invoice->get_items_total_dispatch(); $freight_tax = $invoice->get_shipping_tax(); - $delivery_no = $invoice->src_docs; - if (is_array($delivery_no)) - $delivery_no = 0; - update_customer_trans_version(get_parent_type(ST_SALESINVOICE), $invoice->src_docs); $ov_gst = 0; @@ -71,14 +67,13 @@ function write_sales_invoice(&$invoice) $invoice_no = write_customer_trans(ST_SALESINVOICE, $trans_no, $invoice->customer_id, $invoice->Branch, $date_, $invoice->reference, $items_total, 0, $items_added_tax, $invoice->freight_cost, $freight_added_tax, - $invoice->sales_type, $sales_order, $delivery_no, - $invoice->ship_via, $invoice->due_date, $alloc, 0, $invoice->dimension_id, + $invoice->sales_type, $sales_order, $invoice->ship_via, + $invoice->due_date, $alloc, 0, $invoice->dimension_id, $invoice->dimension2_id, $invoice->payment); // 2008-06-14 extra $alloc, 2008-11-12 added dimension_id Joe Hunt if ($trans_no == 0) { $invoice->trans_no = array($invoice_no=>0); - set_document_parent($invoice); } else { delete_comments(ST_SALESINVOICE, $invoice_no); void_gl_trans(ST_SALESINVOICE, $invoice_no, true); @@ -99,7 +94,7 @@ function write_sales_invoice(&$invoice) write_customer_trans_detail_item(ST_SALESINVOICE, $invoice_no, $invoice_line->stock_id, $invoice_line->item_description, $invoice_line->qty_dispatched, $invoice_line->line_price(), $line_tax, $invoice_line->discount_percent, - $invoice_line->standard_cost, + $invoice_line->standard_cost, $invoice_line->src_id, $trans_no ? $invoice_line->id : 0); // Update delivery items for the quantity invoiced @@ -202,9 +197,8 @@ function void_sales_invoice($type, $type_no) void_gl_trans($type, $type_no, true); // reverse all the changes in parent document(s) - $items_result = get_customer_trans_details($type, $type_no); - $deliveries = get_parent_trans($type, $type_no); + $deliveries = get_sales_parent_numbers($type, $type_no); if ($deliveries !== 0) { if (count($deliveries) == 1 && get_reference(ST_CUSTDELIVERY, $deliveries[0]) == "auto") @@ -213,16 +207,15 @@ function void_sales_invoice($type, $type_no) $date_ = Today(); add_audit_trail(ST_CUSTDELIVERY, $deliveries[0], $date_, _("Voided.")); add_voided_entry(ST_CUSTDELIVERY, $deliveries[0], $date_, ""); - } - else + } + else { - $srcdetails = get_customer_trans_details(get_parent_type($type), $deliveries); - while ($row = db_fetch($items_result)) { - $src_line = db_fetch($srcdetails); - update_parent_line($type, $src_line['id'], -$row['quantity']); + $srcdetails = get_sales_parent_lines($type, $type_no); + while ($row = db_fetch($src_details)) { + update_parent_line($type, $row['id'], -$row['quantity']); } - } + } } // clear details after they've been reversed in the sales order void_customer_trans_details($type, $type_no); diff --git a/sales/includes/sales_db.inc b/sales/includes/sales_db.inc index 0503abd3..f66b2093 100644 --- a/sales/includes/sales_db.inc +++ b/sales/includes/sales_db.inc @@ -196,48 +196,6 @@ function get_kit_price($item_code, $currency, $sales_type_id, $factor=null, return $kit_price; } -//----------------------------------------------------------------------------- - -function set_document_parent($cart) -{ - $inv_no = key($cart->trans_no); - - if (count($cart->src_docs) == 1) { - - // if this child document has only one parent - update child link - $src = array_keys($cart->src_docs); - $del_no = reset($src); - - $sql = 'UPDATE '.TB_PREF.'debtor_trans SET trans_link = ' . $del_no . - ' WHERE type='.db_escape($cart->trans_type).' AND trans_no='. $inv_no ; - db_query($sql, 'Child document link cannot be updated'); - - } - if ($cart->trans_type != ST_SALESINVOICE) - return 0; - - // the rest is batch invoice specific - - foreach ($cart->line_items as $line) { - if ($line->quantity != $line->qty_dispatched) { - return 1; // this is partial invoice - } - } - - $sql = 'UPDATE '.TB_PREF.'debtor_trans SET trans_link = ' . $inv_no . - ' WHERE type='.get_parent_type($cart->trans_type).' AND ('; - - $deliveries = array_keys($cart->src_docs); - - foreach ($deliveries as $key=>$del) - $deliveries[$key] = 'trans_no='.$del; - - $sql .= implode(' OR ', $deliveries) . ')'; - db_query($sql, 'Delivery links cannot be updated'); - - return 0; // batch or complete invoice -} - //-------------------------------------------------------------------------------------------------- function update_parent_line($doc_type, $line_id, $qty_dispatched, $auto=false) { @@ -351,4 +309,99 @@ function read_sales_trans($doc_type, $trans_no, &$cart) return true; } //---------------------------------------------------------------------------------------- + +function get_sales_child_lines($trans_type, $trans_no, $lines=true) +{ + if (!get_child_type($trans_type)) + return false; + if (!is_array($trans_no)) { + $trans_no = array($trans_no); + } + + $trtbl = $trans_type == ST_SALESORDER ? "sales_order_details" : "debtor_trans_details"; + + $rel = $trans_type == ST_SALESORDER ? "trans.trans_type=$trans_type" + . " AND trans.order_no IN(". implode(',', array_values($trans_no)).")" + : "trans.debtor_trans_type=$trans_type" + . " AND trans.debtor_trans_no IN(". implode(',', array_values($trans_no)).")"; + + $sql = "SELECT child.* + FROM + ".TB_PREF."debtor_trans_details child, + ".TB_PREF."$trtbl trans + WHERE + $rel AND trans.id=child.src_id"; + + if (!$lines) + $sql .= " GROUP BY child.debtor_trans_no"; + + $sql .= " ORDER BY child.debtor_trans_no"; + + return db_query($sql, "can't retrieve child trans"); +} + +function get_sales_child_numbers($trans_type, $trans_no) +{ + $trans = array(); + $res = get_sales_child_lines($trans_type, $trans_no, false); + while ($line = db_fetch($res)) { + $trans[] = $line['debtor_trans_no']; + } + return $trans; +} + +function get_sales_parent_lines($trans_type, $trans_no, $lines=true) +{ + $partype = get_parent_type($trans_type); + + if (!$partype) + return false; + + $partbl = $partype == ST_SALESORDER ? "sales_order_details" : "debtor_trans_details"; + $parent_no = $partype == ST_SALESORDER ? "parent.order_no" : "parent.debtor_trans_no"; + $sql = "SELECT parent.* + FROM + ".TB_PREF."$partbl parent + LEFT JOIN ".TB_PREF."debtor_trans_details trans + ON trans.src_id=parent.id + WHERE + trans.debtor_trans_type=$trans_type AND trans.debtor_trans_no=$trans_no"; + if (!$lines) + $sql .= " GROUP BY $parent_no"; + + $sql .= " ORDER BY $parent_no"; + + return db_query($sql, "can't retrieve child trans"); + +} + +function get_sales_parent_numbers($trans_type, $trans_no) +{ + $trans = array(); + $res = get_sales_parent_lines($trans_type, $trans_no, false); + while ($line = db_fetch($res)) + $trans[] = $line[$trans_type==ST_CUSTDELIVERY ? 'order_no' : 'debtor_trans_no']; + return $trans; +} + +//---------------------------------------------------------------------------------------- + +function get_sales_child_documents($trans_type, $trans_no) +{ + // FIXME - credit notes retrieved here should be those linked to invoices containing + // at least one line from related invoice + + if (!count($trans_no)) + return false; + $childs = get_sales_child_numbers($trans_type, $trans_no, false); + if (!count($childs)) + return false; + + $sql = "SELECT * FROM ".TB_PREF."debtor_trans + WHERE type=".get_child_type($trans_type)." AND trans_no IN(". implode(',', array_values($childs)).")"; + + return db_query($sql,"The related credit notes could not be retreived"); +} + + ?> \ No newline at end of file diff --git a/sales/view/view_invoice.php b/sales/view/view_invoice.php index 4fc4ce12..2add8a65 100644 --- a/sales/view/view_invoice.php +++ b/sales/view/view_invoice.php @@ -96,7 +96,7 @@ start_row(); label_cells(_("Invoice Date"), sql2date($myrow["tran_date"]), "class='tableheader2'", "nowrap"); label_cells(_("Due Date"), sql2date($myrow["due_date"]), "class='tableheader2'", "nowrap"); label_cells(_("Deliveries"), get_customer_trans_view_str(ST_CUSTDELIVERY, - get_parent_trans(ST_SALESINVOICE,$trans_id)), "class='tableheader2'"); + get_sales_parent_numbers(ST_SALESINVOICE, $trans_id)), "class='tableheader2'"); end_row(); comments_display_row(ST_SALESINVOICE, $trans_id); end_table(); diff --git a/sales/view/view_sales_order.php b/sales/view/view_sales_order.php index f9d0629c..a9e780e2 100644 --- a/sales/view/view_sales_order.php +++ b/sales/view/view_sales_order.php @@ -94,26 +94,26 @@ if ($_GET['trans_type'] != ST_SALESQUOTE) $th = array(_("#"), _("Ref"), _("Date"), _("Total")); table_header($th); - $result = get_related_documents(ST_CUSTDELIVERY, $_GET['trans_no']); - - $delivery_total = 0; - $k = 0; $dn_numbers = array(); - - while ($del_row = db_fetch($result)) - { + $delivery_total = 0; - alt_table_row_color($k); - $dn_numbers[] = $del_row["trans_link"]; - $this_total = $del_row["ov_freight"]+ $del_row["ov_amount"] + $del_row["ov_freight_tax"] + $del_row["ov_gst"] ; - $delivery_total += $this_total; + if ($result = get_sales_child_documents(ST_SALESORDER, $_GET['trans_no'])) { - label_cell(get_customer_trans_view_str($del_row["type"], $del_row["trans_no"])); - label_cell($del_row["reference"]); - label_cell(sql2date($del_row["tran_date"])); - amount_cell($this_total); - end_row(); + $k = 0; + while ($del_row = db_fetch($result)) + { + alt_table_row_color($k); + $dn_numbers[] = $del_row["trans_no"]; + $this_total = $del_row["ov_freight"]+ $del_row["ov_amount"] + $del_row["ov_freight_tax"] + $del_row["ov_gst"] ; + $delivery_total += $this_total; + + label_cell(get_customer_trans_view_str($del_row["type"], $del_row["trans_no"])); + label_cell($del_row["reference"]); + label_cell(sql2date($del_row["tran_date"])); + amount_cell($this_total); + end_row(); + } } label_row(null, price_format($delivery_total), " ", "colspan=4 align=right"); @@ -127,32 +127,28 @@ if ($_GET['trans_type'] != ST_SALESQUOTE) $th = array(_("#"), _("Ref"), _("Date"), _("Total")); table_header($th); - $sql = "SELECT * FROM ".TB_PREF."debtor_trans WHERE type=".ST_SALESINVOICE - ." AND trans_no IN(". implode(',', array_values($dn_numbers)).")"; - - $result = get_related_documents(ST_SALESINVOICE, $_GET['trans_no']); - - $invoices_total = 0; - $k = 0; $inv_numbers = array(); + $invoices_total = 0; - while ($inv_row = db_fetch($result)) - { + if ($result = get_sales_child_documents(ST_CUSTDELIVERY, $dn_numbers)) { - alt_table_row_color($k); + $k = 0; - $this_total = $inv_row["ov_freight"] + $inv_row["ov_freight_tax"] + $inv_row["ov_gst"] + $inv_row["ov_amount"]; - $invoices_total += $this_total; + while ($inv_row = db_fetch($result)) + { + alt_table_row_color($k); - $inv_numbers[] = $inv_row["trans_no"]; - label_cell(get_customer_trans_view_str($inv_row["type"], $inv_row["trans_no"])); - label_cell($inv_row["reference"]); - label_cell(sql2date($inv_row["tran_date"])); - amount_cell($this_total); - end_row(); + $this_total = $inv_row["ov_freight"] + $inv_row["ov_freight_tax"] + $inv_row["ov_gst"] + $inv_row["ov_amount"]; + $invoices_total += $this_total; + $inv_numbers[] = $inv_row["trans_no"]; + label_cell(get_customer_trans_view_str($inv_row["type"], $inv_row["trans_no"])); + label_cell($inv_row["reference"]); + label_cell(sql2date($inv_row["tran_date"])); + amount_cell($this_total); + end_row(); + } } - label_row(null, price_format($invoices_total), " ", "colspan=4 align=right"); end_table(); @@ -162,10 +158,10 @@ if ($_GET['trans_type'] != ST_SALESQUOTE) start_table(TABLESTYLE); $th = array(_("#"), _("Ref"), _("Date"), _("Total")); table_header($th); - if(count($inv_numbers)) { - $result = get_related_credits($inv_numbers); - - $credits_total = 0; + + $credits_total = 0; + + if ($result = get_sales_child_documents(ST_SALESINVOICE, $inv_numbers)) { $k = 0; while ($credits_row = db_fetch($result)) @@ -184,9 +180,9 @@ if ($_GET['trans_type'] != ST_SALESQUOTE) } - label_row(null, "" . price_format(-$credits_total) . "", - " ", "colspan=4 align=right"); } + label_row(null, "" . price_format(-$credits_total) . "", + " ", "colspan=4 align=right"); end_table(); -- 2.30.2