X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=purchasing%2Fincludes%2Fdb%2Fsuppliers_db.inc;h=602b86bf60049a52f50d3c385785510480909acb;hb=2f3375b4493c1b1e0b17c2801298275f22f8d76e;hp=6b7d0cde787265665cfc21c5a188a0bf6f5f0f43;hpb=13367be8aaf5175295f7aab7160370458dad1824;p=fa-stable.git diff --git a/purchasing/includes/db/suppliers_db.inc b/purchasing/includes/db/suppliers_db.inc index 6b7d0cde..602b86bf 100644 --- a/purchasing/includes/db/suppliers_db.inc +++ b/purchasing/includes/db/suppliers_db.inc @@ -13,12 +13,12 @@ function add_supplier($supp_name, $supp_ref, $address, $supp_address, $gst_no, $website, $supp_account_no, $bank_account, $credit_limit, $dimension_id, $dimension2_id, $curr_code, $payment_terms, $payable_account, $purchase_account, $payment_discount_account, - $notes, $tax_group_id, $tax_included, $tax_algorithm) + $notes, $tax_group_id, $tax_included) { $sql = "INSERT INTO ".TB_PREF."suppliers (supp_name, supp_ref, address, supp_address, gst_no, website, supp_account_no, bank_account, credit_limit, dimension_id, dimension2_id, curr_code, payment_terms, payable_account, purchase_account, payment_discount_account, notes, - tax_group_id, tax_included, tax_algorithm) + tax_group_id, tax_included) VALUES (".db_escape($supp_name). ", " .db_escape($supp_ref). ", " .db_escape($address) . ", " @@ -37,8 +37,7 @@ function add_supplier($supp_name, $supp_ref, $address, $supp_address, $gst_no, .db_escape($payment_discount_account). ", " .db_escape($notes). ", " .db_escape($tax_group_id). ", " - .db_escape($tax_included). ", " - .db_escape($tax_algorithm). ")"; + .db_escape($tax_included). ")"; db_query($sql,"The supplier could not be added"); } @@ -46,7 +45,7 @@ function add_supplier($supp_name, $supp_ref, $address, $supp_address, $gst_no, function update_supplier($supplier_id, $supp_name, $supp_ref, $address, $supp_address, $gst_no, $website, $supp_account_no, $bank_account, $credit_limit, $dimension_id, $dimension2_id, $curr_code, $payment_terms, $payable_account, $purchase_account, $payment_discount_account, - $notes, $tax_group_id, $tax_included, $tax_algorithm) + $notes, $tax_group_id, $tax_included) { $sql = "UPDATE ".TB_PREF."suppliers SET supp_name=".db_escape($supp_name) . ", supp_ref=".db_escape($supp_ref) . ", @@ -66,8 +65,7 @@ function update_supplier($supplier_id, $supp_name, $supp_ref, $address, $supp_ad payment_discount_account=".db_escape($payment_discount_account) . ", notes=".db_escape($notes) . ", tax_group_id=".db_escape($tax_group_id). ", - tax_included=".db_escape($tax_included).", - tax_algorithm=".db_escape($tax_algorithm) + tax_included=".db_escape($tax_included) ." WHERE supplier_id = ".db_escape($supplier_id); db_query($sql,"The supplier could not be updated"); @@ -93,17 +91,18 @@ function get_supplier_details($supplier_id, $to=null, $all=true) if ($all) $value = "(trans.ov_amount + trans.ov_gst + trans.ov_discount)"; else - $value = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_BANKDEPOSIT.", - (trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc), - (trans.ov_amount + trans.ov_gst + trans.ov_discount + trans.alloc))"; + $value = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_BANKDEPOSIT." + OR (trans.type=".ST_JOURNAL." AND (trans.ov_amount + trans.ov_gst + trans.ov_discount)>0), 1, -1)* + ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) - trans.alloc"; + $due = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_SUPPCREDIT.",trans.due_date,trans.tran_date)"; $sql = "SELECT supp.supp_name, supp.curr_code, ".TB_PREF."payment_terms.terms, Sum(IFNULL($value,0)) AS Balance, - Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= 0,$value,0)) AS Due, - Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $past1,$value,0)) AS Overdue1, - Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $past2,$value,0)) AS Overdue2, + Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$value,0)) AS Due, + Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $past1,$value,0)) AS Overdue1, + Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $past2,$value,0)) AS Overdue2, supp.credit_limit - Sum(IFNULL(IF(trans.type=".ST_SUPPCREDIT.", -1, 1) * (ov_amount + ov_gst + ov_discount),0)) as cur_credit, supp.tax_group_id @@ -150,7 +149,7 @@ function get_supplier_name($supplier_id) function get_supplier_accounts($supplier_id) { - $sql = "SELECT payable_account,purchase_account,payment_discount_account FROM ".TB_PREF."suppliers WHERE supplier_id=".db_escape($supplier_id); + $sql = "SELECT payable_account,purchase_account,payment_discount_account, dimension_id, dimension2_id FROM ".TB_PREF."suppliers WHERE supplier_id=".db_escape($supplier_id); $result = db_query($sql, "could not get supplier"); @@ -181,13 +180,6 @@ function is_new_supplier($id) return !key_in_foreign_table($id, $tables, 'supplier_id'); } -function update_supp_tax_algorithm($supplier_id, $tax_algorithm) -{ - $sql = "UPDATE ".TB_PREF."suppliers SET tax_algorithm=".db_escape($tax_algorithm) - . " WHERE supplier_id=".db_escape($supplier_id); - return db_query($sql, "cannot update supplier's tax calculation algorithm"); -} - function get_supplier_currency($supplier_id) { $sql = "SELECT curr_code FROM ".TB_PREF."suppliers WHERE supplier_id = ".db_escape($supplier_id); @@ -195,6 +187,26 @@ function get_supplier_currency($supplier_id) $result = db_query($sql, "Retreive currency of supplier $supplier_id"); $myrow=db_fetch_row($result); - return $myrow[0]; + return $myrow ? $myrow[0] : get_company_currency(); +} + +function get_suppliers_search($supplier) +{ + global $SysPrefs; + + if (isset($SysPrefs->max_rows_in_search)) + $limit = $SysPrefs->max_rows_in_search; + else + $limit = 10; + + $sql = "SELECT supplier_id, supp_name, supp_ref, address, gst_no + FROM ".TB_PREF."suppliers + WHERE (supp_name LIKE " . db_escape("%" . $supplier. "%") . " OR + supp_ref LIKE " . db_escape("%" . $supplier. "%") . " OR + address LIKE " . db_escape("%" . $supplier. "%") . " OR + gst_no LIKE " . db_escape("%" . $supplier. "%") . ") + ORDER BY supp_name LIMIT 0,".(int)($limit); + + return db_query($sql, "Failed in retreiving supplier list."); }