X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sales%2Fincludes%2Fdb%2Fcust_trans_db.inc;h=f9b77aa5c3885c84357965ff66e82c7c698c6101;hb=502c2fc8bb683df7737e6700c984d7584405355a;hp=fab74ab6e6ec4f4ee94501a65d820755c10223dc;hpb=ddadb47f2620ce6902ad4694ce6512568862ba05;p=fa-stable.git diff --git a/sales/includes/db/cust_trans_db.inc b/sales/includes/db/cust_trans_db.inc index fab74ab6..f9b77aa5 100644 --- a/sales/includes/db/cust_trans_db.inc +++ b/sales/includes/db/cust_trans_db.inc @@ -16,7 +16,7 @@ function get_parent_trans($trans_type, $trans_no) { $sql = 'SELECT trans_link FROM '.TB_PREF.'debtor_trans WHERE - (trans_no=' .$trans_no. ' AND type='.$trans_type.' AND trans_link!=0)'; + (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'); @@ -28,7 +28,7 @@ function get_parent_trans($trans_type, $trans_no) { // invoice: find batch invoice parent trans. $sql = 'SELECT trans_no FROM '.TB_PREF.'debtor_trans WHERE - (trans_link='.$trans_no.' AND type='. get_parent_type($trans_type) .')'; + (trans_link='.db_escape($trans_no).' AND type='. get_parent_type($trans_type) .')'; $result = db_query($sql, 'Delivery links cannot be retrieved'); @@ -47,11 +47,10 @@ function get_parent_trans($trans_type, $trans_no) { function update_customer_trans_version($type, $versions) { $sql= 'UPDATE '.TB_PREF. 'debtor_trans SET version=version+1 - WHERE type='.$type. ' AND ('; + WHERE type='.db_escape($type).' AND ('; foreach ($versions as $trans_no=>$version) - $where[] = '(trans_no='.$trans_no. - ' AND version='.$version.')'; + $where[] = '(trans_no='.db_escape($trans_no).' AND version='.$version.')'; $sql .= implode(' OR ', $where) .')'; @@ -68,7 +67,7 @@ function get_customer_trans_version($type, $trans_no) { $trans_no = array( $trans_no ); $sql= 'SELECT trans_no, version FROM '.TB_PREF. 'debtor_trans - WHERE type='.$type.' AND ('; + WHERE type='.db_escape($type).' AND ('; foreach ($trans_no as $key=>$trans) $trans_no[$key] = 'trans_no='.$trans_no[$key]; @@ -117,23 +116,23 @@ function write_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo, ov_gst, ov_freight, ov_freight_tax, rate, ship_via, alloc, trans_link, dimension_id, dimension2_id - ) VALUES ($trans_no, $trans_type, + ) VALUES ($trans_no, ".db_escape($trans_type).", ".db_escape($debtor_no).", ".db_escape($BranchNo).", '$SQLDate', '$SQLDueDate', ".db_escape($reference).", - ".db_escape($sales_type).", $order_no, $Total, ".db_escape($discount).", $Tax, + ".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).", - $dimension_id, $dimension2_id)"; + ".db_escape($dimension_id).", ".db_escape($dimension2_id).")"; } else { // may be optional argument should stay unchanged ? $sql = "UPDATE ".TB_PREF."debtor_trans SET debtor_no=".db_escape($debtor_no)." , branch_code=".db_escape($BranchNo).", tran_date='$SQLDate', due_date='$SQLDueDate', - reference=".db_escape($reference).", tpe=".db_escape($sales_type).", order_=$order_no, + 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, - dimension_id=$dimension_id, dimension2_id=$dimension2_id - WHERE trans_no=$trans_no AND type=$trans_type"; + dimension_id=".db_escape($dimension_id).", dimension2_id=".db_escape($dimension2_id)." + WHERE trans_no=$trans_no AND type=".db_escape($trans_type); } db_query($sql, "The debtor transaction record could not be inserted"); @@ -181,8 +180,8 @@ function get_customer_trans($trans_id, $trans_type) $sql .= ", ".TB_PREF."shippers, ".TB_PREF."sales_types, ".TB_PREF."cust_branch, ".TB_PREF."tax_groups "; } - $sql .= " WHERE ".TB_PREF."debtor_trans.trans_no=$trans_id - AND ".TB_PREF."debtor_trans.type=$trans_type + $sql .= " WHERE ".TB_PREF."debtor_trans.trans_no=".db_escape($trans_id)." + AND ".TB_PREF."debtor_trans.type=".db_escape($trans_type)." AND ".TB_PREF."debtor_trans.debtor_no=".TB_PREF."debtors_master.debtor_no"; if ($trans_type == ST_CUSTPAYMENT) { @@ -223,8 +222,8 @@ function get_customer_trans($trans_id, $trans_type) function exists_customer_trans($type, $type_no) { - $sql = "SELECT trans_no FROM ".TB_PREF."debtor_trans WHERE type=$type - AND trans_no=$type_no"; + $sql = "SELECT trans_no FROM ".TB_PREF."debtor_trans WHERE type=".db_escape($type)." + AND trans_no=".db_escape($type_no); $result = db_query($sql, "Cannot retreive a debtor transaction"); @@ -237,7 +236,7 @@ function exists_customer_trans($type, $type_no) function get_customer_trans_order($type, $type_no) { - $sql = "SELECT order_ FROM ".TB_PREF."debtor_trans WHERE type=$type AND trans_no=$type_no"; + $sql = "SELECT order_ FROM ".TB_PREF."debtor_trans WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no); $result = db_query($sql, "The debtor transaction could not be queried"); @@ -248,11 +247,18 @@ 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_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 FROM ".TB_PREF."debtors_master,".TB_PREF."cust_branch,".TB_PREF."debtor_trans - WHERE ".TB_PREF."debtor_trans.type=$type AND ".TB_PREF."debtor_trans.trans_no=$type_no + WHERE ".TB_PREF."debtor_trans.type=".db_escape($type)." AND ".TB_PREF."debtor_trans.trans_no=".db_escape($type_no)." AND ".TB_PREF."debtors_master.debtor_no = ".TB_PREF."debtor_trans.debtor_no AND ".TB_PREF."cust_branch.branch_code = ".TB_PREF."debtor_trans.branch_code"; @@ -266,7 +272,7 @@ function void_customer_trans($type, $type_no) { // clear all values and mark as void $sql = "UPDATE ".TB_PREF."debtor_trans SET ov_amount=0, ov_discount=0, ov_gst=0, ov_freight=0, - ov_freight_tax=0, alloc=0, version=version+1 WHERE type=$type AND trans_no=$type_no"; + ov_freight_tax=0, alloc=0, version=version+1 WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no); db_query($sql, "could not void debtor transactions for type=$type and trans_no=$type_no"); } @@ -294,10 +300,144 @@ 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=$type AND trans_no=$type_no", + 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']); + $date_to = date2sql($_POST['TransToDate']); + + $sql = "SELECT + trans.type, + trans.trans_no, + trans.order_, + trans.reference, + trans.tran_date, + trans.due_date, + debtor.name, + branch.br_name, + debtor.curr_code, + (trans.ov_amount + trans.ov_gst + trans.ov_freight + + trans.ov_freight_tax + trans.ov_discount) AS TotalAmount, "; + if ($_POST['filterType'] != ALL_TEXT) + $sql .= "@bal := @bal+(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount), "; + +// else +// $sql .= "IF(trans.type=".ST_CUSTDELIVERY.",'', IF(trans.type=".ST_SALESINVOICE." OR trans.type=".ST_BANKPAYMENT.",@bal := @bal+ +// (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount), @bal := @bal- +// (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount))) , "; + $sql .= "trans.alloc AS Allocated, + ((trans.type = ".ST_SALESINVOICE.") + AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue + FROM " + .TB_PREF."debtor_trans as trans, " + .TB_PREF."debtors_master as debtor, " + .TB_PREF."cust_branch as branch + WHERE debtor.debtor_no = trans.debtor_no + AND trans.tran_date >= '$date_after' + AND trans.tran_date <= '$date_to' + AND trans.branch_code = branch.branch_code"; + + if ($_POST['customer_id'] != ALL_TEXT) + $sql .= " AND trans.debtor_no = ".db_escape($_POST['customer_id']); + + if ($_POST['filterType'] != ALL_TEXT) + { + if ($_POST['filterType'] == '1') + { + $sql .= " AND (trans.type = ".ST_SALESINVOICE." OR trans.type = ".ST_BANKPAYMENT.") "; + } + elseif ($_POST['filterType'] == '2') + { + $sql .= " AND (trans.type = ".ST_SALESINVOICE.") "; + } + elseif ($_POST['filterType'] == '3') + { + $sql .= " AND (trans.type = " . ST_CUSTPAYMENT + ." OR trans.type = ".ST_BANKDEPOSIT.") "; + } + elseif ($_POST['filterType'] == '4') + { + $sql .= " AND trans.type = ".ST_CUSTCREDIT." "; + } + elseif ($_POST['filterType'] == '5') + { + $sql .= " AND trans.type = ".ST_CUSTDELIVERY." "; + } + + if ($_POST['filterType'] == '2') + { + $today = date2sql(Today()); + $sql .= " AND trans.due_date < '$today' + AND (trans.ov_amount + trans.ov_gst + trans.ov_freight_tax + + trans.ov_freight + trans.ov_discount - trans.alloc > 0) "; + } + } + return $sql; +} + +function get_sql_for_sales_deliveries_view($selected_customer, $selected_stock_item) +{ + $sql = "SELECT trans.trans_no, + debtor.name, + branch.branch_code, + branch.br_name, + sorder.deliver_to, + trans.reference, + sorder.customer_ref, + trans.tran_date, + trans.due_date, + (ov_amount+ov_gst+ov_freight+ov_freight_tax) AS DeliveryValue, + debtor.curr_code, + Sum(line.quantity-line.qty_done) AS Outstanding, + Sum(line.qty_done) AS Done + FROM " + .TB_PREF."sales_orders as sorder, " + .TB_PREF."debtor_trans as trans, " + .TB_PREF."debtor_trans_details as line, " + .TB_PREF."debtors_master as debtor, " + .TB_PREF."cust_branch as branch + WHERE + sorder.order_no = trans.order_ AND + trans.debtor_no = debtor.debtor_no + AND trans.type = ".ST_CUSTDELIVERY." + AND line.debtor_trans_no = trans.trans_no + AND line.debtor_trans_type = trans.type + AND trans.branch_code = branch.branch_code + AND trans.debtor_no = branch.debtor_no "; + + if ($_POST['OutstandingOnly'] == true) { + $sql .= " AND line.qty_done < line.quantity "; + } + + //figure out the sql required from the inputs available + if (isset($_POST['DeliveryNumber']) && $_POST['DeliveryNumber'] != "") + { + $delivery = "%".$_POST['DeliveryNumber']; + $sql .= " AND trans.trans_no LIKE ".db_escape($delivery); + $sql .= " GROUP BY trans.trans_no"; + } + else + { + $sql .= " AND trans.tran_date >= '".date2sql($_POST['DeliveryAfterDate'])."'"; + $sql .= " AND trans.tran_date <= '".date2sql($_POST['DeliveryToDate'])."'"; + + if ($selected_customer != -1) + $sql .= " AND trans.debtor_no=".db_escape($selected_customer)." "; + + if (isset($selected_stock_item)) + $sql .= " AND line.stock_id=".db_escape($selected_stock_item)." "; + + if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != ALL_TEXT) + $sql .= " AND sorder.from_stk_loc = ".db_escape($_POST['StockLocation'])." "; + + $sql .= " GROUP BY trans.trans_no "; + + } //end no delivery number selected + return $sql; +} ?> \ No newline at end of file