X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=purchasing%2Fincludes%2Fdb%2Fsuppliers_db.inc;h=d66c89fc7941fb20c3912d1175eb0413b366fb2a;hb=f11c9c79c03710fa081caa53c2c754d8de04c922;hp=61cf2bdce8a8194ab8e4bdda72024cbffa38679d;hpb=fbf51ab0febfd0885620abcab8738339d156ff45;p=fa-stable.git diff --git a/purchasing/includes/db/suppliers_db.inc b/purchasing/includes/db/suppliers_db.inc index 61cf2bdc..d66c89fc 100644 --- a/purchasing/includes/db/suppliers_db.inc +++ b/purchasing/includes/db/suppliers_db.inc @@ -13,13 +13,13 @@ 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) - VALUES (".db_escape($_POST['supp_name']). ", " + tax_group_id, tax_included) + VALUES (".db_escape($supp_name). ", " .db_escape($supp_ref). ", " .db_escape($address) . ", " .db_escape($supp_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,9 +180,33 @@ function is_new_supplier($id) return !key_in_foreign_table($id, $tables, 'supplier_id'); } -function update_supp_tax_algorithm($supplier_id, $tax_algorithm) +function get_supplier_currency($supplier_id) { - $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"); + $sql = "SELECT curr_code FROM ".TB_PREF."suppliers WHERE supplier_id = ".db_escape($supplier_id); + + $result = db_query($sql, "Retreive currency of supplier $supplier_id"); + + $myrow=db_fetch_row($result); + return $myrow[0]; } + +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."); +} +