{
$from = date2sql($from);
$to = date2sql($to);
- $sql = "SELECT account, ".TB_PREF."chart_master.account_name, sum(amount) AS amt FROM
- ".TB_PREF."gl_trans,".TB_PREF."chart_master WHERE
- ".TB_PREF."gl_trans.account = ".TB_PREF."chart_master.account_code AND
- (dimension_id = ".db_escape($id)." OR dimension2_id = ".db_escape($id).") AND
- tran_date >= '$from' AND tran_date <= '$to' GROUP BY account";
+ $sql = "SELECT account, coa.account_name, sum(amount) AS amt
+ FROM "
+ .TB_PREF."gl_trans trans,"
+ .TB_PREF."chart_master coa
+ WHERE
+ trans.account = coa.account_code
+ AND (dimension_id = ".db_escape($id)." OR dimension2_id = ".db_escape($id).")
+ AND tran_date >= '$from' AND tran_date <= '$to' GROUP BY account";
return db_query($sql, "Transactions could not be calculated");
}
function get_dimension_balance($id, $from, $to)
{
$id = db_escape($id);
- $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans WHERE tran_date >= '" .
- date2sql($from) . "' AND
- tran_date <= '" . date2sql($to) . "' AND (dimension_id = $id OR dimension2_id = $id)";
+ $sql = "SELECT SUM(amount)
+ FROM ".TB_PREF."gl_trans
+ WHERE
+ tran_date >= '" . date2sql($from) . "'
+ AND tran_date <= '" . date2sql($to) . "'
+ AND (dimension_id = $id OR dimension2_id = $id)";
$res = db_query($sql, "Sum of transactions could not be calculated");
$row = db_fetch_row($res);
begin_transaction();
if ($old_id !== $id)
{
- $sql = "SELECT id FROM ".TB_PREF."chart_types WHERE parent = ".db_escape($old_id);
+ $sql = "SELECT id
+ FROM ".TB_PREF."chart_types
+ WHERE parent = ".db_escape($old_id);
$result = db_query($sql, "could not get account type");
." WHERE id = '".$myrow['id']."'";
db_query($sql, "could not update account type");
}
- $sql = "SELECT account_code FROM ".TB_PREF."chart_master WHERE account_type = ".db_escape($old_id);
+ $sql = "SELECT account_code
+ FROM ".TB_PREF."chart_master
+ WHERE account_type = ".db_escape($old_id);
$result = db_query($sql, "could not get account");
while ($myrow = db_fetch($result))
{
- $sql = "UPDATE ".TB_PREF."chart_master SET account_type=".db_escape($id)
+ $sql = "UPDATE ".TB_PREF."chart_master
+ SET account_type=".db_escape($id)
." WHERE account_code = '".$myrow['account_code']."'";
db_query($sql, "could not update account");
}
}
- $sql = "UPDATE ".TB_PREF."chart_types SET id=".db_escape($id) .", name=".db_escape($name).",
- class_id=".db_escape($class_id).", parent=".db_escape($parent)
+ $sql = "UPDATE ".TB_PREF."chart_types
+ SET id=".db_escape($id) .", name=".db_escape($name).",
+ class_id=".db_escape($class_id).", parent=".db_escape($parent)
." WHERE id = ".db_escape($old_id);
$ret = db_query($sql, "could not update account type");
function get_gl_accounts($from=null, $to=null, $type=null)
{
- $sql = "SELECT ".TB_PREF."chart_master.*,".TB_PREF."chart_types.name AS AccountTypeName
- FROM ".TB_PREF."chart_master,".TB_PREF."chart_types
- WHERE ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id";
+ $sql = "SELECT coa.*, act_type.name AS AccountTypeName
+ FROM "
+ .TB_PREF."chart_master coa,"
+ .TB_PREF."chart_types act_type
+ WHERE coa.account_type=act_type.id";
if ($from != null)
- $sql .= " AND ".TB_PREF."chart_master.account_code >= ".db_escape($from);
+ $sql .= " AND coa.account_code >= ".db_escape($from);
if ($to != null)
- $sql .= " AND ".TB_PREF."chart_master.account_code <= ".db_escape($to);
+ $sql .= " AND coa.account_code <= ".db_escape($to);
if ($type != null)
$sql .= " AND account_type=".db_escape($type);
$sql .= " ORDER BY account_code";
function is_account_balancesheet($code)
{
- $sql = "SELECT ".TB_PREF."chart_class.ctype FROM ".TB_PREF."chart_class, "
- .TB_PREF."chart_types, ".TB_PREF."chart_master
- WHERE ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id AND
- ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid
- AND ".TB_PREF."chart_master.account_code=".db_escape($code);
+ $sql = "SELECT act_class.ctype
+ FROM "
+ .TB_PREF."chart_class act_class, "
+ .TB_PREF."chart_types act_type, "
+ .TB_PREF."chart_master coa
+ WHERE coa.account_type=act_type.id
+ AND act_type.class_id=act_class.cid
+ AND coa.account_code=".db_escape($code);
$result = db_query($sql,"could not retreive the account class for $code");
$row = db_fetch_row($result);
function gl_account_in_company_defaults($acc)
{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."sys_prefs WHERE (name='debtors_act'
+ $sql= "SELECT COUNT(*)
+ FROM ".TB_PREF."sys_prefs
+ WHERE (name='debtors_act'
OR name='pyt_discount_act'
OR name='creditors_act'
OR name='bank_charge_act'
{
$acc = db_escape($acc);
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_category WHERE
- dflt_inventory_act=$acc
- OR dflt_cogs_act=$acc
- OR dflt_adjustment_act=$acc
- OR dflt_sales_act=$acc";
+ $sql= "SELECT COUNT(*)
+ FROM ".TB_PREF."stock_category
+ WHERE dflt_inventory_act=$acc
+ OR dflt_cogs_act=$acc
+ OR dflt_adjustment_act=$acc
+ OR dflt_sales_act=$acc";
$result = db_query($sql,"Couldn't test for existing stock category GL codes");
$myrow = db_fetch_row($result);
{
$acc = db_escape($acc);
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_master WHERE
- inventory_account=$acc
- OR cogs_account=$acc
- OR adjustment_account=$acc
- OR sales_account=$acc";
+ $sql= "SELECT COUNT(*)
+ FROM ".TB_PREF."stock_master
+ WHERE inventory_account=$acc
+ OR cogs_account=$acc
+ OR adjustment_account=$acc
+ OR sales_account=$acc";
$result = db_query($sql,"Couldn't test for existing stock GL codes");
$myrow = db_fetch_row($result);
{
$acc = db_escape($acc);
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."tax_types WHERE sales_gl_code=$acc OR purchasing_gl_code=$acc";
+ $sql= "SELECT COUNT(*)
+ FROM ".TB_PREF."tax_types
+ WHERE sales_gl_code=$acc
+ OR purchasing_gl_code=$acc";
$result = db_query($sql,"Couldn't test for existing tax GL codes");
$myrow = db_fetch_row($result);
{
$acc = db_escape($acc);
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."cust_branch WHERE
- sales_account=$acc
- OR sales_discount_account=$acc
- OR receivables_account=$acc
- OR payment_discount_account=$acc";
+ $sql= "SELECT COUNT(*)
+ FROM ".TB_PREF."cust_branch
+ WHERE sales_account=$acc
+ OR sales_discount_account=$acc
+ OR receivables_account=$acc
+ OR payment_discount_account=$acc";
$result = db_query($sql,"Couldn't test for existing cust branch GL codes");
$myrow = db_fetch_row($result);
{
$acc = db_escape($acc);
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."suppliers WHERE
- purchase_account=$acc
- OR payment_discount_account=$acc
- OR payable_account=$acc";
+ $sql= "SELECT COUNT(*)
+ FROM ".TB_PREF."suppliers
+ WHERE purchase_account=$acc
+ OR payment_discount_account=$acc
+ OR payable_account=$acc";
$result = db_query($sql,"Couldn't test for existing suppliers GL codes");
$myrow = db_fetch_row($result);
{
$acc = db_escape($acc);
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."quick_entry_lines WHERE
- dest_id=$acc AND UPPER(LEFT(action, 1)) <> 'T'";
+ $sql=
+ "SELECT COUNT(*)
+ FROM ".TB_PREF."quick_entry_lines
+ WHERE dest_id=$acc AND UPPER(LEFT(action, 1)) <> 'T'";
$result = db_query($sql,"Couldn't test for existing Quick Entry Line GL codes");
$myrow = db_fetch_row($result);
//
function is_subledger_account($account)
{
- $sql = "SELECT 1 FROM ".TB_PREF."cust_branch WHERE receivables_account=".db_escape($account)
- ." UNION SELECT -1 FROM ".TB_PREF."suppliers WHERE payable_account=".db_escape($account);
+ $sql = "SELECT 1
+ FROM ".TB_PREF."cust_branch
+ WHERE receivables_account=".db_escape($account)
+ ." UNION
+ SELECT -1
+ FROM ".TB_PREF."suppliers
+ WHERE payable_account=".db_escape($account);
$result = db_query($sql,"Couldn't test AR/AP account");
$myrow = db_fetch_row($result);
function get_subaccount_data($code_id, $person_id)
{
$sql = "SELECT debtor_ref as name, branch_code as id
- FROM ".TB_PREF."cust_branch branch LEFT JOIN ".TB_PREF."debtors_master d ON branch.debtor_no = d.debtor_no
- WHERE branch.receivables_account=".db_escape($code_id)." AND d.debtor_no=".db_escape($person_id)
- ." UNION SELECT supp_ref as name, '' as id FROM ".TB_PREF."suppliers supp
- WHERE supplier_id=".db_escape($person_id)." AND payable_account=".db_escape($code_id);
+ FROM ".TB_PREF."cust_branch branch
+ LEFT JOIN ".TB_PREF."debtors_master d ON branch.debtor_no = d.debtor_no
+ WHERE branch.receivables_account=".db_escape($code_id)
+ ." AND d.debtor_no=".db_escape($person_id)
+ ." UNION SELECT supp_ref as name, '' as id
+ FROM ".TB_PREF."suppliers supp
+ WHERE supplier_id=".db_escape($person_id)."
+ AND payable_account=".db_escape($code_id);
$result = db_query($sql, 'cannot retrieve counterparty name');
return db_fetch($result);
function get_subaccount_name($code_id, $person_id)
{
- $sql = "SELECT debtor_ref as ref FROM ".TB_PREF."cust_branch branch LEFT JOIN ".TB_PREF."debtors_master d ON branch.debtor_no = d.debtor_no
- WHERE branch.receivables_account=".db_escape($code_id)." AND d.debtor_no=".db_escape($person_id)
- ." UNION SELECT supp_ref as ref FROM ".TB_PREF."suppliers supp
- WHERE payable_account=".db_escape($code_id)." AND supplier_id=".db_escape($person_id);
+ $sql = "SELECT debtor_ref as ref
+ FROM ".TB_PREF."cust_branch branch
+ LEFT JOIN ".TB_PREF."debtors_master d ON branch.debtor_no = d.debtor_no
+ WHERE branch.receivables_account=".db_escape($code_id)
+ ." AND d.debtor_no=".db_escape($person_id)
+ ." UNION SELECT supp_ref as ref
+ FROM ".TB_PREF."suppliers supp
+ WHERE payable_account=".db_escape($code_id)."
+ AND supplier_id=".db_escape($person_id);
$result = db_query($sql, 'cannot retrieve counterparty name');
$row = db_fetch($result);
function get_quick_entry_lines($qid)
{
- $sql = "SELECT ".TB_PREF."quick_entry_lines.*, ".TB_PREF."chart_master.account_name,
- ".TB_PREF."tax_types.name as tax_name
- FROM ".TB_PREF."quick_entry_lines
- LEFT JOIN ".TB_PREF."chart_master ON
- ".TB_PREF."quick_entry_lines.dest_id = ".TB_PREF."chart_master.account_code
- LEFT JOIN ".TB_PREF."tax_types ON
- ".TB_PREF."quick_entry_lines.dest_id = ".TB_PREF."tax_types.id
+ $sql = "SELECT line.*, coa.account_name, taxtype.name as tax_name
+ FROM ".TB_PREF."quick_entry_lines line
+ LEFT JOIN ".TB_PREF."chart_master coa ON line.dest_id = coa.account_code
+ LEFT JOIN ".TB_PREF."tax_types taxtype ON line.dest_id = taxtype.id
WHERE
qid=".db_escape($qid)." ORDER by id";
$sql = "SELECT type, trans_no, ref, trans_date,
amount, person_id, person_type_id, reconciled, id
FROM ".TB_PREF."bank_trans
- WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($bank_account) . "
+ WHERE bank_act = ".db_escape($bank_account) . "
AND (reconciled IS NULL OR reconciled='". date2sql($date) ."')
- ORDER BY trans_date,".TB_PREF."bank_trans.id";
-// or ORDER BY reconciled desc, trans_date,".TB_PREF."bank_trans.id";
+ ORDER BY trans_date, id";
return $sql;
}
function exists_bank_trans($type, $type_no)
{
- $sql = "SELECT trans_no FROM ".TB_PREF."bank_trans WHERE type=".db_escape($type)
+ $sql = "SELECT trans_no
+ FROM ".TB_PREF."bank_trans
+ WHERE type=".db_escape($type)
." AND trans_no=".db_escape($type_no);
$result = db_query($sql, "Cannot retreive a bank transaction");
{
$from = date2sql($from);
$to = date2sql($to);
- $sql = "SELECT t.* FROM "
- .TB_PREF."bank_trans t LEFT JOIN ".TB_PREF."voided v ON t.type=v.type AND t.trans_no=v.id
+ $sql = "SELECT t.*
+ FROM ".TB_PREF."bank_trans t
+ LEFT JOIN ".TB_PREF."voided v ON t.type=v.type AND t.trans_no=v.id
WHERE t.bank_act = ".db_escape($bank_account) . "
- AND ISNULL(v.date_)
- AND trans_date >= '$from'
- AND trans_date <= '$to'
- AND amount != 0
+ AND ISNULL(v.date_)
+ AND trans_date >= '$from'
+ AND trans_date <= '$to'
+ AND amount != 0
ORDER BY trans_date, t.id";
return db_query($sql,"The transactions for '" . $bank_account . "' could not be retrieved");
function get_balance_before_for_bank_account($bank_account, $from)
{
$from = date2sql($from);
- $sql = "SELECT SUM(amount) FROM ".TB_PREF."bank_trans WHERE bank_act="
- .db_escape($bank_account) . "
- AND trans_date < '$from'";
+ $sql = "SELECT SUM(amount)
+ FROM ".TB_PREF."bank_trans
+ WHERE bank_act=".db_escape($bank_account) . "
+ AND trans_date < '$from'";
$before_qty = db_query($sql, "The starting balance on hand could not be calculated");
$bfw_row = db_fetch_row($before_qty);
return $bfw_row[0];
function get_gl_trans_value($account, $type, $trans_no)
{
- $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans WHERE account="
- .db_escape($account)." AND type=".db_escape($type)
- ." AND type_no=".db_escape($trans_no);
+ $sql = "SELECT SUM(amount)
+ FROM ".TB_PREF."gl_trans
+ WHERE account=".db_escape($account)
+ ." AND type=".db_escape($type)
+ ." AND type_no=".db_escape($trans_no);
$result = db_query($sql, "query for gl trans value");
if (!$nested)
begin_transaction();
- $sql = "UPDATE ".TB_PREF."bank_trans SET amount=0
- WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no);
+ $sql = "UPDATE ".TB_PREF."bank_trans
+ SET amount=0
+ WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no);
$result = db_query($sql, "could not void bank transactions for type=$type and trans_no=$type_no");
$balance += $delta_amount;
- $sql = "SELECT sum(amount) as amount, trans_date FROM ".TB_PREF."bank_trans WHERE bank_act=".db_escape($bank_account);
+ $sql = "SELECT sum(amount) as amount, trans_date
+ FROM ".TB_PREF."bank_trans
+ WHERE bank_act=".db_escape($bank_account);
if ($date)
{
$date = date2sql($date);
// We have to calculate all the currency accounts belonging to the GL account
// upto $date_ and calculate with the exchange rates. And then compare with the GL account balance.
// 2010-02-23 Joe Hunt with help of Ary Wibowo
- $sql = "SELECT SUM(bt.amount) AS for_amount, ba.bank_curr_code
+ $sql = "SELECT SUM(bt.amount) AS for_amount, ba.bank_curr_code
FROM ".TB_PREF."bank_trans bt, ".TB_PREF."bank_accounts ba
WHERE ba.id = bt.bank_act AND ba.account_code = ".db_escape($account)." AND bt.trans_date<='".date2sql($date_)."'
- GROUP BY ba.bank_curr_code";
+ GROUP BY ba.bank_curr_code";
$result = db_query($sql, "Transactions for bank account $acc_id could not be calculated");
while ($row = db_fetch($result))
{
{
$to = date2sql($date);
- $sql = "SELECT SUM(IF(t.type =".ST_CUSTCREDIT." OR t.type = ".ST_CUSTPAYMENT." OR t.type = ".ST_BANKDEPOSIT." OR t.type = ".ST_JOURNAL.",
+ $sql = "SELECT SUM(IF(t.type IN(". implode(',', array(ST_CUSTCREDIT, ST_CUSTPAYMENT, ST_BANKDEPOSIT, ST_JOURNAL))."),
-(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount),
(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount))) AS amount,
dt.debtor_no, dt.name, dt.curr_code, b.receivables_account
function get_exchange_rate($rate_id)
{
$sql = "SELECT * FROM ".TB_PREF."exchange_rates WHERE id=".db_escape($rate_id);
- $result = db_query($sql, "could not get exchange rate for $rate_id");
+ $result = db_query($sql, "could not get exchange rate for $rate_id");
return db_fetch($result);
}
function get_date_exchange_rate($curr_code, $date_)
{
$date = date2sql($date_);
- $sql = "SELECT rate_buy FROM ".TB_PREF."exchange_rates WHERE curr_code=".db_escape($curr_code)
- ." AND date_='$date'";
- $result = db_query($sql, "could not get exchange rate for $curr_code - $date_");
+ $sql = "SELECT rate_buy
+ FROM ".TB_PREF."exchange_rates
+ WHERE curr_code=".db_escape($curr_code)
+ ." AND date_='$date'";
+ $result = db_query($sql, "could not get exchange rate for $curr_code - $date_");
if(db_num_rows($result) == 0)
return 0;
{
$date = date2sql($date_);
- $sql = "SELECT rate_buy, max(date_) as date_ FROM ".TB_PREF."exchange_rates WHERE curr_code = ".db_escape($curr_code)."
+ $sql = "SELECT rate_buy, max(date_) as date_
+ FROM ".TB_PREF."exchange_rates
+ WHERE curr_code = ".db_escape($curr_code)."
AND date_ <= '$date' GROUP BY rate_buy ORDER BY date_ Desc LIMIT 1";
$result = db_query($sql, "could not query exchange rates");
{
if (is_company_currency($curr_code))
display_db_error("Exchange rates cannot be set for company currency", "", true);
-
+
$date = date2sql($date_);
-
+
$sql = "UPDATE ".TB_PREF."exchange_rates SET rate_buy=$buy_rate, rate_sell=".db_escape($sell_rate)
." WHERE curr_code=".db_escape($curr_code)." AND date_='$date'";
-
+
db_query($sql, "could not add exchange rate for $curr_code");
}
display_db_error("Exchange rates cannot be set for company currency", "", true);
$date = date2sql($date_);
-
+
$sql = "INSERT INTO ".TB_PREF."exchange_rates (curr_code, date_, rate_buy, rate_sell)
VALUES (".db_escape($curr_code).", '$date', ".db_escape($buy_rate)
.", ".db_escape($sell_rate).")";
function get_sql_for_exchange_rates($curr)
{
- $sql = "SELECT date_, rate_buy, id FROM "
- .TB_PREF."exchange_rates "
+ $sql = "SELECT date_, rate_buy, id
+ FROM ".TB_PREF."exchange_rates "
."WHERE curr_code=".db_escape($curr)."
ORDER BY date_ DESC";
- return $sql;
+ return $sql;
}
if ($to_date != "")
$sql .= " AND tran_date < '$to'";
if ($dimension != 0)
- $sql .= " AND dimension_id = ".($dimension<0?0:db_escape($dimension));
+ $sql .= " AND dimension_id = ".($dimension<0 ? 0 : db_escape($dimension));
if ($dimension2 != 0)
- $sql .= " AND dimension2_id = ".($dimension2<0?0:db_escape($dimension2));
+ $sql .= " AND dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2));
$result = db_query($sql, "The starting balance for account $account could not be calculated");
if ($to_date != "")
$sql .= " AND tran_date <= '$to'";
if ($dimension != 0)
- $sql .= " AND dimension_id = ".($dimension<0?0:db_escape($dimension));
+ $sql .= " AND dimension_id = ".($dimension<0 ? 0 : db_escape($dimension));
if ($dimension2 != 0)
- $sql .= " AND dimension2_id = ".($dimension2<0?0:db_escape($dimension2));
+ $sql .= " AND dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2));
$result = db_query($sql, "Transactions for account $account could not be calculated");
//----------------------------------------------------------------------------------------------------
function get_balance($account, $dimension, $dimension2, $from, $to, $from_incl=true, $to_incl=true)
{
- $sql = "SELECT SUM(IF(amount >= 0, amount, 0)) as debit,
- SUM(IF(amount < 0, -amount, 0)) as credit, SUM(amount) as balance
- FROM ".TB_PREF."gl_trans,".TB_PREF."chart_master,"
- .TB_PREF."chart_types, ".TB_PREF."chart_class
- WHERE ".TB_PREF."gl_trans.account=".TB_PREF."chart_master.account_code AND "
- .TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id
- AND ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid AND";
-
- if ($account != null)
- $sql .= " account=".db_escape($account)." AND";
- if ($dimension != 0)
- $sql .= " dimension_id = ".($dimension<0?0:db_escape($dimension))." AND";
- if ($dimension2 != 0)
- $sql .= " dimension2_id = ".($dimension2<0?0:db_escape($dimension2))." AND";
$from_date = date2sql($from);
- if ($from_incl)
- $sql .= " tran_date >= '$from_date' AND";
- else
- $sql .= " tran_date > IF(ctype>0 AND ctype<".CL_INCOME.", '0000-00-00', '$from_date') AND";
$to_date = date2sql($to);
- if ($to_incl)
- $sql .= " tran_date <= '$to_date' ";
- else
- $sql .= " tran_date < '$to_date' ";
+
+ $sql = "SELECT SUM(IF(amount >= 0, amount, 0)) as debit,
+ SUM(IF(amount < 0, -amount, 0)) as credit,
+ SUM(amount) as balance
+ FROM ".TB_PREF."gl_trans trans,"
+ .TB_PREF."chart_master coa,"
+ .TB_PREF."chart_types act_type, "
+ .TB_PREF."chart_class act_class
+ WHERE trans.account=coa.account_code
+ AND coa.account_type=act_type.id
+ AND act_type.class_id=act_class.cid"
+ ." AND ".($from_incl ? "tran_date >= '$from_date'" : "tran_date > IF(ctype>0 AND ctype<".CL_INCOME.", '0000-00-00', '$from_date')")
+ ." AND ".($to_incl ? "tran_date <= '$to_date'" : "tran_date < '$to_date'")
+ .($account == null ? '' : " AND account=".db_escape($account))
+ .($dimension == 0 ? '' : " AND dimension_id = ".($dimension<0 ? 0 : db_escape($dimension)))
+ .($dimension2 == 0 ? '' : " AND dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2)));
$result = db_query($sql,"No general ledger accounts were returned");
$from = date2sql($from_date);
$to = date2sql($to_date);
- $sql = "SELECT SUM(amount) FROM ".TB_PREF."budget_trans
+ $sql = "SELECT SUM(amount)
+ FROM ".TB_PREF."budget_trans
WHERE account=".db_escape($account);
if ($from_date != "")
$sql .= " AND tran_date >= '$from' ";
function get_trans_tax_details($trans_type, $trans_no)
{
- $sql = "SELECT ".TB_PREF."trans_tax_details.*, "
- .TB_PREF."tax_types.name AS tax_type_name, "
- .TB_PREF."trans_tax_details.rate AS effective_rate, "
- .TB_PREF."tax_types.rate AS rate
- FROM ".TB_PREF."trans_tax_details,".TB_PREF."tax_types
- WHERE trans_type = ".db_escape($trans_type)."
+ $sql = "SELECT tax_details.*,
+ tax_type.name AS tax_type_name,
+ tax_details.rate AS effective_rate,
+ tax_type.rate AS rate
+ FROM ".TB_PREF."trans_tax_details tax_details,
+ ".TB_PREF."tax_types tax_type
+ WHERE
+ trans_type = ".db_escape($trans_type)."
AND trans_no = ".db_escape($trans_no)."
AND (net_amount != 0 OR amount != 0)
- AND ".TB_PREF."tax_types.id = ".TB_PREF."trans_tax_details.tax_type_id";
+ AND tax_type.id = tax_details.tax_type_id";
return db_query($sql, "The transaction tax details could not be retrieved");
}
function get_already_delivered($stock_id, $location, $trans_no)
{
- $sql = "SELECT ".TB_PREF."stock_moves.qty
+ $sql = "SELECT qty
FROM ".TB_PREF."stock_moves
- WHERE ".TB_PREF."stock_moves.stock_id = ".db_escape($stock_id)."
- AND ".TB_PREF."stock_moves.loc_code = ".db_escape($location)."
+ WHERE stock_id = ".db_escape($stock_id)."
+ AND loc_code = ".db_escape($location)."
AND type=".ST_CUSTDELIVERY." AND trans_no=".db_escape($trans_no);
$result = db_query($sql, "Could not get stock moves");
$row = db_fetch_row($result);
$cost = db_fetch_row($result);
// Adjusting QOH valuation
- $sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves
+ $sql = "SELECT SUM(qty)
+ FROM ".TB_PREF."stock_moves
WHERE stock_id=".db_escape($stock_id)." AND
trans_id<'$from' GROUP BY stock_id";
$result = db_query($sql, "The deliveries could not be updated");
function get_purchases_from_trans($stock_id, $from)
{
// Calculate All inward stock moves i.e. qty > 0
- $sql = "SELECT SUM(qty), SUM(qty*standard_cost) FROM ".TB_PREF."stock_moves
+ $sql = "SELECT SUM(qty), SUM(qty*standard_cost)
+ FROM ".TB_PREF."stock_moves
WHERE stock_id=".db_escape($stock_id)." AND qty > 0 AND
trans_id>'$from' GROUP BY stock_id";
$result = db_query($sql, "Could not get get_purchases_from_trans");
function get_stock_moves($type, $type_no)
{
- $sql = "SELECT ".TB_PREF."stock_moves.*, ".TB_PREF."stock_master.description, "
- .TB_PREF."stock_master.units,".TB_PREF."locations.location_name,"
- .TB_PREF."stock_master.material_cost + "
- .TB_PREF."stock_master.labour_cost + "
- .TB_PREF."stock_master.overhead_cost AS FixedStandardCost
- FROM ".TB_PREF."stock_moves,".TB_PREF."locations,".TB_PREF."stock_master
- WHERE ".TB_PREF."stock_moves.stock_id = ".TB_PREF."stock_master.stock_id
- AND ".TB_PREF."locations.loc_code=".TB_PREF."stock_moves.loc_code
- AND type=".db_escape($type)." AND trans_no=".db_escape($type_no)." ORDER BY trans_id";
+ $sql = "SELECT move.*, item.description, item.units, stock.location_name,
+ item.material_cost + item.labour_cost + item.overhead_cost AS FixedStandardCost
+ FROM ".TB_PREF."stock_moves move,"
+ .TB_PREF."locations stock,"
+ .TB_PREF."stock_master item
+ WHERE move.stock_id = item.stock_id
+ AND stock.loc_code=move.loc_code
+ AND type=".db_escape($type)
+ ." AND trans_no=".db_escape($type_no)
+ ." ORDER BY trans_id";
return db_query($sql, "Could not get stock moves");
}
function void_stock_move($type, $type_no)
{
- $sql = "SELECT move.*, supplier.supplier_id from ".TB_PREF."stock_moves move
+ $sql = "SELECT move.*, supplier.supplier_id
+ FROM ".TB_PREF."stock_moves move
LEFT JOIN ".TB_PREF."supp_trans credit ON credit.trans_no=move.trans_no AND credit.type=move.type
LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=move.trans_no AND 25=move.type
LEFT JOIN ".TB_PREF."suppliers supplier ON IFNULL(grn.supplier_id, credit.supplier_id)=supplier.supplier_id
- WHERE move.type=".db_escape($type)." AND move.trans_no=".db_escape($type_no);
+ WHERE move.type=".db_escape($type)." AND move.trans_no=".db_escape($type_no);
$result = db_query($sql, "Could not void stock moves");
while ($row = db_fetch($result))
$unit_cost, -$row["qty"], sql2date($row["tran_date"]));
}
}
- $sql = "DELETE FROM ".TB_PREF."stock_moves WHERE type=".db_escape($type)
- ." AND trans_no=".db_escape($type_no);
+ $sql = "DELETE FROM ".TB_PREF."stock_moves
+ WHERE type=".db_escape($type)
+ ." AND trans_no=".db_escape($type_no);
db_query($sql, "The stock movement cannot be delated");
}
function get_location_name($loc_code)
{
- $sql = "SELECT location_name FROM ".TB_PREF."locations WHERE loc_code="
- .db_escape($loc_code);
+ $sql = "SELECT location_name FROM ".TB_PREF."locations
+ WHERE loc_code=".db_escape($loc_code);
$result = db_query($sql, "could not retreive the location name for $loc_code");
function get_mb_flag($stock_id)
{
- $sql = "SELECT mb_flag FROM ".TB_PREF."stock_master WHERE stock_id = "
- .db_escape($stock_id);
+ $sql = "SELECT mb_flag FROM ".TB_PREF."stock_master
+ WHERE stock_id = ".db_escape($stock_id);
$result = db_query($sql, "retreive mb_flag from item");
if (db_num_rows($result) == 0)
//----------------------------------------------------------------------------------------
function get_demand_qty($stock_id, $location)
{
- $sql = "SELECT SUM(".TB_PREF."sales_order_details.quantity - "
- .TB_PREF."sales_order_details.qty_sent) AS QtyDemand
- FROM ".TB_PREF."sales_order_details,
- ".TB_PREF."sales_orders
- WHERE ".TB_PREF."sales_order_details.order_no="
- .TB_PREF."sales_orders.order_no AND ".TB_PREF."sales_orders.trans_type=".ST_SALESORDER." AND
- ".TB_PREF."sales_orders.trans_type=".TB_PREF."sales_order_details.trans_type AND ";
+ $sql = "SELECT SUM(line.quantity - line.qty_sent) AS QtyDemand
+ FROM ".TB_PREF."sales_order_details line,
+ ".TB_PREF."sales_orders sorder
+ WHERE line.order_no=sorder.order_no AND sorder.trans_type=".ST_SALESORDER
+ ." AND sorder.trans_type=line.trans_type"
+ ." AND line.stk_code = ".db_escape($stock_id);
if ($location != "")
- $sql .= TB_PREF."sales_orders.from_stk_loc =".db_escape($location)." AND ";
- $sql .= TB_PREF."sales_order_details.stk_code = ".db_escape($stock_id);
+ $sql .= " AND sorder.from_stk_loc =".db_escape($location);
$result = db_query($sql,"No transactions were returned");
$row = db_fetch($result);
function load_stock_levels($location)
{
- global $qoh_stock;
$date = date2sql(Today());
- $sql = "SELECT stock_id, SUM(qty) FROM ".TB_PREF."stock_moves WHERE tran_date <= '$date'";
- if ($location != '') $sql .= " AND loc_code = ".db_escape($location);
+ $qoh_stock = array();
+ $sql = "SELECT stock_id, SUM(qty)
+ FROM ".TB_PREF."stock_moves
+ WHERE tran_date <= '$date'";
+ if ($location != '')
+ $sql .= " AND loc_code = ".db_escape($location);
$sql .= " GROUP BY stock_id";
+
$result = db_query($sql, "QOH calculation failed");
- while ($row = db_fetch($result)) {
+ while ($row = db_fetch($result))
$qoh_stock[$row[0]] = $row[1];
- }
+
+ return $qoh_stock;
}
// recursion fixed by Tom Moulton. Max 10 recursion levels.
function stock_demand_manufacture($stock_id, $qty, $demand_id, $location, $level=0)
{
global $bom_list, $qoh_stock;
+
$demand = 0.0;
if ($level > 10) {
display_warning("BOM Too many Manufacturing levels deep $level");
return $demand;
}
// Load all stock levels (stock moves) into $qoh_stock
- if ($qoh_stock == NULL) {
- $qoh_stock = array();
- load_stock_levels($location);
- }
- if (empty($qoh_stock[$stock_id])) $stock_qty = 0;
- else $stock_qty = $qoh_stock[$stock_id];
- if ($qty <= $stock_qty) return $demand;
+ if ($qoh_stock == NULL)
+ $qoh_stock = load_stock_levels($location);
+
+ if (empty($qoh_stock[$stock_id]))
+ $stock_qty = 0;
+ else
+ $stock_qty = $qoh_stock[$stock_id];
+
+ if ($qty <= $stock_qty)
+ return $demand;
$bom = @$bom_list[$stock_id];
if ($bom == NULL) {
$sql = "SELECT parent, component, quantity FROM "
}
db_free_result($result);
$bom_list[$stock_id] = $bom;
- }
+ }
$len = count($bom);
$i = 0;
while ($i < $len) {
function get_demand_asm_qty($stock_id, $location)
{
$demand_qty = 0.0;
- $sql = "SELECT ".TB_PREF."sales_order_details.stk_code, SUM(".TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_sent)
- AS Demmand
- FROM ".TB_PREF."sales_order_details,
- ".TB_PREF."sales_orders,
- ".TB_PREF."stock_master
- WHERE ".TB_PREF."sales_orders.order_no = ".TB_PREF."sales_order_details.order_no AND
- ".TB_PREF."sales_orders.trans_type=".ST_SALESORDER." AND
- ".TB_PREF."sales_orders.trans_type=".TB_PREF."sales_order_details.trans_type AND ";
+ $sql = "SELECT line.stk_code, SUM(line.quantity-line.qty_sent) AS Demmand
+ FROM ".TB_PREF."sales_order_details line,
+ ".TB_PREF."sales_orders sorder,
+ ".TB_PREF."stock_master item
+ WHERE sorder.order_no = line.order_no
+ AND sorder.trans_type=".ST_SALESORDER
+ ." AND sorder.trans_type=line.trans_type
+ AND line.quantity-line.qty_sent > 0
+ AND item.stock_id=line.stk_code
+ AND item.mb_flag='M'";
if ($location != "")
- $sql .= TB_PREF."sales_orders.from_stk_loc =".db_escape($location)." AND ";
- $sql .= TB_PREF."sales_order_details.quantity-".TB_PREF."sales_order_details.qty_sent > 0 AND
- ".TB_PREF."stock_master.stock_id=".TB_PREF."sales_order_details.stk_code AND
- ".TB_PREF."stock_master.mb_flag='M'
- GROUP BY ".TB_PREF."sales_order_details.stk_code";
+ $sql .= " AND sorder.from_stk_loc =".db_escape($location);
+ $sql .= " GROUP BY line.stk_code";
$result = db_query($sql, "No transactions were returned");
while ($row = db_fetch_row($result)) {
$demand_qty += stock_demand_manufacture($row[0], $row[1], $stock_id, $location);
function get_on_porder_qty($stock_id, $location)
{
- $sql = "SELECT SUM(".TB_PREF."purch_order_details.quantity_ordered - "
- .TB_PREF."purch_order_details.quantity_received) AS qoo
- FROM ".TB_PREF."purch_order_details INNER JOIN "
- .TB_PREF."purch_orders ON ".TB_PREF."purch_order_details.order_no=".TB_PREF."purch_orders.order_no
- WHERE ".TB_PREF."purch_order_details.item_code=".db_escape($stock_id)." ";
+ $sql = "SELECT SUM(line.quantity_ordered - line.quantity_received) AS qoo
+ FROM ".TB_PREF."purch_order_details line
+ INNER JOIN ".TB_PREF."purch_orders po ON line.order_no=po.order_no
+ WHERE line.item_code=".db_escape($stock_id);
if ($location != "")
- $sql .= "AND ".TB_PREF."purch_orders.into_stock_location=".db_escape($location)." ";
- $sql .= "AND ".TB_PREF."purch_order_details.item_code=".db_escape($stock_id);
+ $sql .= " AND po.into_stock_location=".db_escape($location);
+ $sql .= " AND line.item_code=".db_escape($stock_id);
$qoo_result = db_query($sql,"could not receive quantity on order for item");
if (db_num_rows($qoo_result) == 1)
function get_on_worder_qty($stock_id, $location)
{
- $sql = "SELECT SUM((".TB_PREF."workorders.units_reqd-".TB_PREF."workorders.units_issued) *
- (".TB_PREF."wo_requirements.units_req-".TB_PREF."wo_requirements.units_issued)) AS qoo
- FROM ".TB_PREF."wo_requirements INNER JOIN ".TB_PREF."workorders
- ON ".TB_PREF."wo_requirements.workorder_id=".TB_PREF."workorders.id
- WHERE ".TB_PREF."wo_requirements.stock_id=".db_escape($stock_id)." ";
+ $sql = "SELECT SUM((wo.units_reqd-wo.units_issued) * (req.units_req-req.units_issued)) AS qoo
+ FROM ".TB_PREF."wo_requirements req
+ INNER JOIN ".TB_PREF."workorders wo ON req.workorder_id=wo.id
+ WHERE req.stock_id=".db_escape($stock_id)
+ ." AND wo.released=1";
if ($location != "")
- $sql .= "AND ".TB_PREF."wo_requirements.loc_code=".db_escape($location)." ";
- $sql .= "AND ".TB_PREF."workorders.released=1";
+ $sql .= " AND req.loc_code=".db_escape($location);
+
$qoo_result = db_query($sql,"could not receive quantity on order for item");
if (db_num_rows($qoo_result) == 1)
{
$flag = get_mb_flag($stock_id);
if ($flag == 'M')
{
- $sql = "SELECT SUM((".TB_PREF."workorders.units_reqd-".TB_PREF."workorders.units_issued)) AS qoo
- FROM ".TB_PREF."workorders
- WHERE ".TB_PREF."workorders.stock_id=".db_escape($stock_id)." ";
- if ($location != "")
- $sql .= "AND ".TB_PREF."workorders.loc_code=".db_escape($location)." ";
- $sql .= "AND ".TB_PREF."workorders.released=1";
+ $sql = "SELECT SUM((units_reqd-units_issued)) AS qoo
+ FROM ".TB_PREF."workorders
+ WHERE stock_id=".db_escape($stock_id)
+ ." AND released=1";
+
+ if ($location != "")
+ $sql .= " AND loc_code=".db_escape($location);
+
$qoo_result = db_query($sql,"could not receive quantity on order for item");
if (db_num_rows($qoo_result) == 1)
{
function get_bom($item)
{
- $sql = "SELECT ".TB_PREF."bom.*, ".TB_PREF."locations.location_name, ".TB_PREF."workcentres.name AS WorkCentreDescription,
- ".TB_PREF."stock_master.description, ".TB_PREF."stock_master.mb_flag AS ResourceType,
- ".TB_PREF."stock_master.material_cost+ ".TB_PREF."stock_master.labour_cost+".TB_PREF."stock_master.overhead_cost AS standard_cost, units,
- ".TB_PREF."bom.quantity * (".TB_PREF."stock_master.material_cost+ ".TB_PREF."stock_master.labour_cost+ ".TB_PREF."stock_master.overhead_cost) AS ComponentCost
- FROM (".TB_PREF."workcentres, ".TB_PREF."locations, ".TB_PREF."bom) INNER JOIN ".TB_PREF."stock_master ON ".TB_PREF."bom.component = ".TB_PREF."stock_master.stock_id
- WHERE ".TB_PREF."bom.parent = ".db_escape($item)."
- AND ".TB_PREF."workcentres.id=".TB_PREF."bom.workcentre_added
- AND ".TB_PREF."bom.loc_code = ".TB_PREF."locations.loc_code ORDER BY ".TB_PREF."bom.id";
+ $sql = "SELECT bom.*, loc.location_name,
+ centre.name AS WorkCentreDescription,
+ item.description, item.mb_flag AS ResourceType,
+ item.material_cost+item.labour_cost+item.overhead_cost AS standard_cost, units,
+ bom.quantity * (item.material_cost+ item.labour_cost+ item.overhead_cost) AS ComponentCost
+ FROM ".TB_PREF."workcentres centre,
+ ".TB_PREF."locations loc,
+ ".TB_PREF."bom bom
+ INNER JOIN ".TB_PREF."stock_master item ON bom.component = item.stock_id
+ WHERE bom.parent = ".db_escape($item)."
+ AND centre.id=bom.workcentre_added
+ AND bom.loc_code = loc.loc_code ORDER BY bom.id";
return db_query($sql, "The bill of material could not be retrieved");
}
function get_component_from_bom($selected_id)
{
- $sql = "SELECT ".TB_PREF."bom.*,".TB_PREF."stock_master.description FROM "
- .TB_PREF."bom,".TB_PREF."stock_master
+ $sql = "SELECT bom.*, item.description
+ FROM ".TB_PREF."bom bom,"
+ .TB_PREF."stock_master item
WHERE id=".db_escape($selected_id)."
- AND ".TB_PREF."stock_master.stock_id=".TB_PREF."bom.component";
+ AND item.stock_id=bom.component";
$result = db_query($sql, "could not get bom");
return db_fetch($result);
function is_component_already_on_bom($component, $workcentre_added, $loc_code, $selected_parent)
{
- $sql = "SELECT component FROM ".TB_PREF."bom
+ $sql = "SELECT component
+ FROM ".TB_PREF."bom
WHERE parent=".db_escape($selected_parent)."
AND component=".db_escape($component) . "
AND workcentre_added=".db_escape($workcentre_added) . "
$sql = "INSERT INTO ".TB_PREF."prices (stock_id, sales_type_id, curr_abrev, price)
VALUES (".db_escape($stock_id).", ".db_escape($sales_type_id)
.", ".db_escape($curr_abrev).", ".db_escape($price).")";
-
- db_query($sql,"an item price could not be added");
+
+ db_query($sql,"an item price could not be added");
}
function update_item_price($price_id, $sales_type_id, $curr_abrev, $price)
$sql = "UPDATE ".TB_PREF."prices SET sales_type_id=".db_escape($sales_type_id).",
curr_abrev=".db_escape($curr_abrev).",
price=".db_escape($price)." WHERE id=".db_escape($price_id);
-
- db_query($sql,"an item price could not be updated");
+
+ db_query($sql,"an item price could not be updated");
}
function delete_item_price($price_id)
{
$sql="DELETE FROM ".TB_PREF."prices WHERE id= ".db_escape($price_id);
- db_query($sql,"an item price could not be deleted");
+ db_query($sql,"an item price could not be deleted");
}
function get_prices($stock_id)
{
- $sql = "SELECT ".TB_PREF."sales_types.sales_type, ".TB_PREF."prices.*
- FROM ".TB_PREF."prices, ".TB_PREF."sales_types
- WHERE ".TB_PREF."prices.sales_type_id = ".TB_PREF."sales_types.id
+ $sql = "SELECT pricelist.sales_type, price.*
+ FROM ".TB_PREF."prices price, "
+ .TB_PREF."sales_types pricelist
+ WHERE price.sales_type_id = pricelist.id
AND stock_id=".db_escape($stock_id)
- ." ORDER BY curr_abrev, sales_type_id";
-
+ ." ORDER BY curr_abrev, sales_type_id";
+
return db_query($sql,"item prices could not be retreived");
}
function get_stock_price($price_id)
{
$sql = "SELECT * FROM ".TB_PREF."prices WHERE id=".db_escape($price_id);
-
+
$result = db_query($sql,"price could not be retreived");
-
+
return db_fetch($result);
}
$sql = "SELECT * FROM ".TB_PREF."prices WHERE stock_id=".db_escape($stock_id)."
AND sales_type_id=".db_escape($type)."
AND curr_abrev=".db_escape($currency);
-
+
$result = db_query($sql,"price could not be retreived");
-
+
return db_fetch($result);
}
function get_items_purchasing_data($stock_id)
{
- $sql = "SELECT ".TB_PREF."purch_data.*,".TB_PREF."suppliers.supp_name,"
- .TB_PREF."suppliers.curr_code
- FROM ".TB_PREF."purch_data INNER JOIN ".TB_PREF."suppliers
- ON ".TB_PREF."purch_data.supplier_id=".TB_PREF."suppliers.supplier_id
+ $sql = "SELECT pdata.*, supplier.supp_name, supplier.curr_code
+ FROM ".TB_PREF."purch_data pdata
+ INNER JOIN ".TB_PREF."suppliers supplier ON pdata.supplier_id=supplier.supplier_id
WHERE stock_id = ".db_escape($stock_id);
return db_query($sql, "The supplier purchasing details for the selected part could not be retrieved");
function get_item_purchasing_data($selected_id, $stock_id)
{
- $sql = "SELECT ".TB_PREF."purch_data.*,".TB_PREF."suppliers.supp_name FROM ".TB_PREF."purch_data
- INNER JOIN ".TB_PREF."suppliers ON ".TB_PREF."purch_data.supplier_id=".TB_PREF."suppliers.supplier_id
- WHERE ".TB_PREF."purch_data.supplier_id=".db_escape($selected_id)."
- AND ".TB_PREF."purch_data.stock_id=".db_escape($stock_id);
+ $sql = "SELECT pdata.*, supplier.supp_name
+ FROM ".TB_PREF."purch_data pdata
+ INNER JOIN ".TB_PREF."suppliers supplier ON pdata.supplier_id=supplier.supplier_id
+ WHERE pata.supplier_id=".db_escape($selected_id)."
+ AND pdata.stock_id=".db_escape($stock_id);
$result = db_query($sql, "The supplier purchasing details for the selected supplier and item could not be retrieved");
return db_fetch($result);
}
-
-
$last_cost)
{
$mb_flag = get_mb_flag($stock_id);
-
+
$update_no = -1;
if (is_service($mb_flag))
{
//display_db_error("Cannot do cost update for Service item : $stock_id", "");
-
+
//Chaitanya
$sql = "UPDATE ".TB_PREF."stock_master SET material_cost=".db_escape($material_cost)."
WHERE stock_id=".db_escape($stock_id);
-
+
db_query($sql,"The cost details for the inventory item could not be updated");
-
+
return $update_no;
-
- }
-
+ }
+
begin_transaction();
-
+
$sql = "UPDATE ".TB_PREF."stock_master SET material_cost=".db_escape($material_cost).",
labour_cost=".db_escape($labour_cost).",
overhead_cost=".db_escape($overhead_cost).",
db_query($sql,"The cost details for the inventory item could not be updated");
$qoh = get_qoh_on_date($stock_id);
-
+
$date_ = Today();
if (!is_date_in_fiscalyear($date_))
$date_ = end_fiscalyear();
function calculate_reorder_level($location, $line, &$st_ids, &$st_names, &$st_num, &$st_reorder)
{
- $sql = "SELECT ".TB_PREF."loc_stock.*, ".TB_PREF."locations.location_name, ".TB_PREF."locations.email
- FROM ".TB_PREF."loc_stock, ".TB_PREF."locations
- WHERE ".TB_PREF."loc_stock.loc_code=".TB_PREF."locations.loc_code
- AND ".TB_PREF."loc_stock.stock_id = '" . $line->stock_id . "'
- AND ".TB_PREF."loc_stock.loc_code = '" . $location . "'";
+ $sql = "SELECT stock.*, loc.location_name, loc.email
+ FROM ".TB_PREF."loc_stock stock,"
+ .TB_PREF."locations loc
+ WHERE stock.loc_code=loc.loc_code
+ AND stock.stock_id = '" . $line->stock_id . "'
+ AND stock.loc_code = '" . $location . "'";
$res = db_query($sql,"a location could not be retreived");
$loc = db_fetch($res);
if ($loc['email'] != "")
function send_reorder_email($loc, $st_ids, $st_names, $st_num, $st_reorder)
{
global $path_to_root;
-
+
require_once($path_to_root . "/reporting/includes/class.mail.inc");
$company = get_company_prefs();
$mail = new email($company['coy_name'], $company['email']);
function get_additional_issues($woid)
{
- $sql = "SELECT ".TB_PREF."wo_issues.*, ".TB_PREF."wo_issue_items.*
- FROM ".TB_PREF."wo_issues, ".TB_PREF."wo_issue_items
- WHERE ".TB_PREF."wo_issues.issue_no=".TB_PREF."wo_issue_items.issue_id
- AND ".TB_PREF."wo_issues.workorder_id=".db_escape($woid)
- ." ORDER BY ".TB_PREF."wo_issue_items.id";
+ $sql = "SELECT issue.*, item.*
+ FROM ".TB_PREF."wo_issues issue, "
+ .TB_PREF."wo_issue_items item
+ WHERE issue.issue_no=item.issue_id
+ AND issue.workorder_id=".db_escape($woid)
+ ." ORDER BY item.id";
return db_query($sql, "The work order issues could not be retrieved");
}
//--------------------------------------------------------------------------------------
function get_work_order_issue($issue_no)
{
- $sql = "SELECT DISTINCT ".TB_PREF."wo_issues.*, ".TB_PREF."workorders.stock_id,
- ".TB_PREF."stock_master.description, ".TB_PREF."locations.location_name, "
- .TB_PREF."workcentres.name AS WorkCentreName
- FROM ".TB_PREF."wo_issues, ".TB_PREF."workorders, ".TB_PREF."stock_master, "
- .TB_PREF."locations, ".TB_PREF."workcentres
+ $sql = "SELECT DISTINCT issue.*, wo.stock_id,
+ item.description, loc.location_name, center.name AS WorkCentreName
+ FROM ".TB_PREF."wo_issues issue,"
+ .TB_PREF."workorders wo,"
+ .TB_PREF."stock_master item,"
+ .TB_PREF."locations loc,"
+ .TB_PREF."workcentres center
WHERE issue_no=".db_escape($issue_no)."
- AND ".TB_PREF."workorders.id = ".TB_PREF."wo_issues.workorder_id
- AND ".TB_PREF."locations.loc_code = ".TB_PREF."wo_issues.loc_code
- AND ".TB_PREF."workcentres.id = ".TB_PREF."wo_issues.workcentre_id
- AND ".TB_PREF."stock_master.stock_id = ".TB_PREF."workorders.stock_id";
+ AND wo.id = issue.workorder_id
+ AND loc.loc_code = issue.loc_code
+ AND center.id = issue.workcentre_id
+ AND item.stock_id = wo.stock_id";
$result = db_query($sql, "A work order issue could not be retrieved");
return db_fetch($result);
function get_work_order_issue_details($issue_no)
{
- $sql = "SELECT ".TB_PREF."wo_issue_items.*,"
- .TB_PREF."stock_master.description, ".TB_PREF."stock_master.units
- FROM ".TB_PREF."wo_issue_items, ".TB_PREF."stock_master
+ $sql = "SELECT issue.*, item.description, item.units
+ FROM ".TB_PREF."wo_issue_items issue,"
+ .TB_PREF."stock_master item
WHERE issue_id=".db_escape($issue_no)."
- AND ".TB_PREF."stock_master.stock_id=".TB_PREF."wo_issue_items.stock_id
- ORDER BY ".TB_PREF."wo_issue_items.id";
+ AND item.stock_id=issue.stock_id
+ ORDER BY issue.id";
return db_query($sql, "The work order issue items could not be retrieved");
}
if ($total_cost != 0)
add_gl_trans_std_cost(ST_WORKORDER, $woid, $date_, $issue["inventory_account"],
0, 0, $date_.": "._("Reversed the issue to")." ".$stockitem["description"],
- -$total_cost);
+ -$total_cost);
//Chaitanya : Shifted below void all related stock moves
void_stock_move(ST_MANUISSUE, $type_no);
function get_work_order_produce($id)
{
- $sql = "SELECT ".TB_PREF."wo_manufacture.*,".TB_PREF."workorders.stock_id, "
- .TB_PREF."stock_master.description AS StockDescription
- FROM ".TB_PREF."wo_manufacture, ".TB_PREF."workorders, ".TB_PREF."stock_master
- WHERE ".TB_PREF."wo_manufacture.workorder_id=".TB_PREF."workorders.id
- AND ".TB_PREF."stock_master.stock_id=".TB_PREF."workorders.stock_id
- AND ".TB_PREF."wo_manufacture.id=".db_escape($id);
+ $sql = "SELECT prod.*, wo.stock_id, item.description AS StockDescription
+ FROM ".TB_PREF."wo_manufacture prod,"
+ .TB_PREF."workorders wo,"
+ .TB_PREF."stock_master item
+ WHERE prod.workorder_id=wo.id
+ AND item.stock_id=wo.stock_id
+ AND prod.id=".db_escape($id);
$result = db_query($sql, "The work order production could not be retrieved");
return db_fetch($result);
***********************************************************************/
function get_wo_requirements($woid)
{
- $sql = "SELECT ".TB_PREF."wo_requirements.*, ".TB_PREF."stock_master.description,
- ".TB_PREF."stock_master.mb_flag,
- ".TB_PREF."locations.location_name,
- ".TB_PREF."workcentres.name AS WorkCentreDescription FROM
- (".TB_PREF."wo_requirements, ".TB_PREF."locations, "
- .TB_PREF."workcentres) INNER JOIN ".TB_PREF."stock_master ON
- ".TB_PREF."wo_requirements.stock_id = ".TB_PREF."stock_master.stock_id
+ $sql = "SELECT req.*, item.description, item.mb_flag, loc.location_name,
+ center.name AS WorkCentreDescription
+ FROM (".TB_PREF."wo_requirements req,"
+ .TB_PREF."locations loc,"
+ .TB_PREF."workcentres center)
+ INNER JOIN ".TB_PREF."stock_master item ON req.stock_id=item.stock_id
WHERE workorder_id=".db_escape($woid)."
- AND ".TB_PREF."locations.loc_code = ".TB_PREF."wo_requirements.loc_code
- AND ".TB_PREF."workcentres.id=workcentre";
+ AND loc.loc_code = req.loc_code
+ AND center.id=workcentre";
return db_query($sql, "The work order requirements could not be retrieved");
}
function create_wo_requirements($woid, $stock_id)
{
// create Work Order Requirements based on the bom
- $result = get_bom($stock_id);
-
+ $result = get_bom($stock_id);
+
while ($myrow = db_fetch($result))
{
-
+
$sql = "INSERT INTO ".TB_PREF."wo_requirements (workorder_id, stock_id, workcentre, units_req, loc_code)
VALUES (".db_escape($woid).", '" .
$myrow["component"] . "', '" .
$myrow["workcentre_added"] . "', '" .
$myrow["quantity"] . "', '" .
$myrow["loc_code"] . "')";
-
+
db_query($sql, "The work order requirements could not be added");
- }
+ }
}
//--------------------------------------------------------------------------------------
{
$sql = "UPDATE ".TB_PREF."wo_requirements SET units_issued = units_issued + ".db_escape($quantity)."
WHERE workorder_id = ".db_escape($woid)." AND stock_id = ".db_escape($stock_id);
-
+
db_query($sql, "The work requirements issued quantity couldn't be updated");
}
db_query($sql, "The work requirements issued quantity couldn't be voided");
}
-//--------------------------------------------------------------------------------------
-
if (!isset($labour) || ($labour == ""))
$labour = 0;
add_labour_cost($stock_id, $units_reqd, $date_, $labour);
-
+
$sql = "INSERT INTO ".TB_PREF."workorders (wo_ref, loc_code, units_reqd, units_issued, stock_id,
type, additional_costs, date_, released_date, required_by, released, closed)
VALUES (".db_escape($wo_ref).", ".db_escape($loc_code).", ".db_escape($units_reqd)
add_stock_move(ST_WORKORDER, $bom_item["component"], $woid,
$bom_item["loc_code"], $date_, $wo_ref, -$item_quantity, $UnitCost, $UnitCost);
}
-
+
// -------------------------------------------------------------------------
//Negative Stock Handling
$qoh = get_qoh_on_date($stock_id);
$bom_accounts = get_stock_gl_code($bom_item["component"]);
$bom_cost = $bom_item["ComponentCost"] * $units_reqd;
-
+
$memo = $bom_item["quantity"] ." * ".$bom_item["description"];
if ($advanced)
{
0, 0, $memo, -$total_cost);
}
-//--------------------------------------------------------------------------------------
-
$mcost = update_average_material_cost($supplier, $entered_grn->item_code,
$entered_grn->chg_price, $entered_grn->this_quantity_inv, $date);
- $sql = "SELECT ".TB_PREF."grn_batch.*, ".TB_PREF."grn_items.*
- FROM ".TB_PREF."grn_batch, ".TB_PREF."grn_items
- WHERE ".TB_PREF."grn_items.grn_batch_id=".TB_PREF."grn_batch.id
- AND ".TB_PREF."grn_items.id=".db_escape($entered_grn->id)."
- AND ".TB_PREF."grn_items.item_code=".db_escape($entered_grn->item_code);
+ $sql = "SELECT grn.*, item.*
+ FROM ".TB_PREF."grn_batch grn,"
+ .TB_PREF."grn_items item
+ WHERE item.grn_batch_id=grn.id
+ AND item.id=".db_escape($entered_grn->id)."
+ AND item.item_code=".db_escape($entered_grn->item_code);
+
$result = db_query($sql, "Could not retreive GRNS");
$myrow = db_fetch($result);
function get_grn_item_detail($grn_item_no)
{
- $sql = "SELECT ".TB_PREF."grn_items.*, ".TB_PREF."purch_order_details.unit_price,
- ".TB_PREF."grn_items.qty_recd - ".TB_PREF."grn_items.quantity_inv AS QtyOstdg,
- ".TB_PREF."purch_order_details.std_cost_unit
- FROM ".TB_PREF."grn_items, ".TB_PREF."purch_order_details, ".TB_PREF."stock_master
- WHERE ".TB_PREF."grn_items.po_detail_item=".TB_PREF."purch_order_details.po_detail_item
- AND ".TB_PREF."stock_master.stock_id=".TB_PREF."grn_items.item_code
- AND ".TB_PREF."grn_items.id=".db_escape($grn_item_no);
+ $sql = "SELECT grn.*, po.unit_price, grn.qty_recd - grn.quantity_inv AS QtyOstdg,
+ po.std_cost_unit
+ FROM ".TB_PREF."grn_items grn,"
+ .TB_PREF."purch_order_details po,"
+ .TB_PREF."stock_master item
+ WHERE grn.po_detail_item=po.po_detail_item
+ AND item.stock_id=grn.item_code
+ AND grn.id=".db_escape($grn_item_no);
$result = db_query($sql, "could not retreive grn item details");
return db_fetch($result);
function exists_grn_on_invoices($grn_batch)
{
- $sql = "SELECT ".TB_PREF."supp_invoice_items.id FROM ".TB_PREF."supp_invoice_items,".TB_PREF."grn_items
- WHERE ".TB_PREF."supp_invoice_items.grn_item_id=".TB_PREF."grn_items.id
+ $sql = "SELECT inv.id
+ FROM ".TB_PREF."supp_invoice_items inv,"
+ .TB_PREF."grn_items grn
+ WHERE inv.grn_item_id=grn.id
AND quantity != 0
AND grn_batch_id=".db_escape($grn_batch);
$result = db_query($sql, "Cannot query GRNs");
$unit_price = $row[1]; //Added by Rasmus
- $sql = "SELECT delivery_date FROM ".TB_PREF."grn_batch,".TB_PREF."grn_items WHERE
- ".TB_PREF."grn_batch.id = ".TB_PREF."grn_items.grn_batch_id AND "
- .TB_PREF."grn_items.id=".db_escape($id);
+ $sql = "SELECT delivery_date
+ FROM ".TB_PREF."grn_batch grn,"
+ .TB_PREF."grn_items line
+ WHERE
+ grn.id = line.grn_batch_id AND line.id=".db_escape($id);
$result = db_query($sql, "The old delivery date from the received record cout not be retrieved");
$row = db_fetch_row($result);
$date = $row[0];
function get_po_invoices_credits($po_number)
{
- $sql = "SELECT DISTINCT ".TB_PREF."supp_trans.trans_no, ".TB_PREF."supp_trans.type,
- ov_amount+ov_discount+ov_gst AS Total,
- ".TB_PREF."supp_trans.tran_date
- FROM ".TB_PREF."supp_trans, ".TB_PREF."supp_invoice_items, "
- .TB_PREF."purch_order_details, ".TB_PREF."purch_orders
- WHERE ".TB_PREF."supp_invoice_items.supp_trans_no = ".TB_PREF."supp_trans.trans_no
- AND ".TB_PREF."supp_invoice_items.supp_trans_type = ".TB_PREF."supp_trans.type
- AND ".TB_PREF."supp_invoice_items.po_detail_item_id = ".TB_PREF."purch_order_details.po_detail_item
- AND ".TB_PREF."purch_orders.supplier_id = ".TB_PREF."supp_trans.supplier_id
- AND ".TB_PREF."purch_orders.order_no = ".TB_PREF."purch_order_details.order_no
- AND ".TB_PREF."purch_order_details.order_no = ".db_escape($po_number);
+ $sql = "SELECT DISTINCT trans.trans_no, trans.type, ov_amount+ov_discount+ov_gst AS Total,
+ trans.tran_date
+ FROM ".TB_PREF."supp_trans trans,"
+ .TB_PREF."supp_invoice_items line,"
+ .TB_PREF."purch_order_details poline,"
+ .TB_PREF."purch_orders po
+ WHERE line.supp_trans_no = trans.trans_no
+ AND line.supp_trans_type = trans.type
+ AND line.po_detail_item_id = poline.po_detail_item
+ AND po.supplier_id = trans.supplier_id
+ AND po.order_no = poline.order_no
+ AND poline.order_no = ".db_escape($po_number);
return db_query($sql, "The invoices/credits for the po $po_number could not be retreived");
}
function read_supp_invoice($trans_no, $trans_type, &$supp_trans)
{
- $sql = "SELECT ".TB_PREF."supp_trans.*, supp_name
- FROM ".TB_PREF."supp_trans,".TB_PREF."suppliers
+ $sql = "SELECT trans.*, supp_name
+ FROM ".TB_PREF."supp_trans trans,"
+ .TB_PREF."suppliers sup
WHERE trans_no = ".db_escape($trans_no)." AND type = ".db_escape($trans_type)."
- AND ".TB_PREF."suppliers.supplier_id=".TB_PREF."supp_trans.supplier_id";
+ AND sup.supplier_id=trans.supplier_id";
$result = db_query($sql, "Cannot retreive a supplier transaction");
function get_matching_invoice_item($stock_id, $po_item_id)
{
- $sql = "SELECT *, tran_date FROM ".TB_PREF."supp_invoice_items, ".TB_PREF."supp_trans
+ $sql = "SELECT *, tran_date
+ FROM ".TB_PREF."supp_invoice_items,"
+ .TB_PREF."supp_trans
WHERE supp_trans_type = ".ST_SUPPINVOICE." AND stock_id = "
.db_escape($stock_id)." AND po_detail_item_id = ".db_escape($po_item_id)."
AND supp_trans_no = trans_no";
update_average_material_cost($grn["supplier_id"], $myrow["item_code"],
$myrow["unit_price"], -$myrow["QtyOstdg"], Today());
-
+
$price = $myrow['unit_price'];
if ($supp['tax_included'])
$price = get_tax_free_price_for_item($myrow['item_code'], $myrow['unit_price'], $supp['tax_group_id'], $supp['tax_included']);
add_stock_move(ST_SUPPRECEIVE, $myrow["item_code"], $myrow['grn_batch_id'], $grn['loc_code'], sql2date($grn["delivery_date"]), "",
-$myrow["QtyOstdg"], $myrow['std_cost_unit'], $price);
-
+
$clearing_act = get_company_pref('grn_clearing_act');
if ($clearing_act) { // otherwise GRN clearing account is not used
if (is_inventory_item($myrow['item_code']))
-$myrow['QtyOstdg'] * $price, $grn["supplier_id"], "", 0, _("GRN Removal"));
$total += add_gl_trans_supplier(ST_SUPPRECEIVE, $myrow['grn_batch_id'], $date, $clearing_act,
0, 0, -$total, null, "", 0, _("GRN Removal"));
- }
+ }
}
commit_transaction();
function read_po_header($order_no, &$order)
{
- $sql = "SELECT ".TB_PREF."purch_orders.*, "
- .TB_PREF."suppliers.*, "
- .TB_PREF."locations.location_name
- FROM ".TB_PREF."purch_orders, ".TB_PREF."suppliers, ".TB_PREF."locations
- WHERE ".TB_PREF."purch_orders.supplier_id = ".TB_PREF."suppliers.supplier_id
- AND ".TB_PREF."locations.loc_code = into_stock_location
- AND ".TB_PREF."purch_orders.order_no = ".db_escape($order_no);
+ $sql = "SELECT po.*, supplier.*, loc.location_name
+ FROM ".TB_PREF."purch_orders po,"
+ .TB_PREF."suppliers supplier,"
+ .TB_PREF."locations loc
+ WHERE po.supplier_id = supplier.supplier_id
+ AND loc.loc_code = into_stock_location
+ AND po.order_no = ".db_escape($order_no);
$result = db_query($sql, "The order cannot be retrieved");
{
/*now populate the line po array with the purchase order details records */
- $sql = "SELECT ".TB_PREF."purch_order_details.*, units
- FROM ".TB_PREF."purch_order_details
- LEFT JOIN ".TB_PREF."stock_master
- ON ".TB_PREF."purch_order_details.item_code=".TB_PREF."stock_master.stock_id
+ $sql = "SELECT poline.*, units
+ FROM ".TB_PREF."purch_order_details poline
+ LEFT JOIN ".TB_PREF."stock_master item ON poline.item_code=item.stock_id
WHERE order_no =".db_escape($order_no);
if ($open_items_only)
- $sql .= " AND (".TB_PREF."purch_order_details.quantity_ordered > ".TB_PREF."purch_order_details.quantity_received) ";
+ $sql .= " AND (poline.quantity_ordered > poline.quantity_received) ";
$sql .= " ORDER BY po_detail_item";
function get_supp_trans($trans_no, $trans_type=-1, $supplier_id=null)
{
- $sql = "SELECT ".TB_PREF."supp_trans.*, (".TB_PREF."supp_trans.ov_amount+".TB_PREF."supp_trans.ov_gst+".TB_PREF."supp_trans.ov_discount) AS Total,
- ".TB_PREF."suppliers.supp_name AS supplier_name, ".TB_PREF."suppliers.curr_code AS curr_code ";
+ $sql = "SELECT trans.*, (trans.ov_amount+trans.ov_gst+trans.ov_discount) AS Total,
+ supplier.supp_name AS supplier_name, supplier.curr_code AS curr_code ";
if ($trans_type == ST_SUPPAYMENT || $trans_type == ST_BANKPAYMENT)
{
// it's a payment so also get the bank account
- $sql .= ", ".TB_PREF."bank_accounts.bank_name, ".TB_PREF."bank_accounts.bank_account_name, ".TB_PREF."bank_accounts.bank_curr_code,
- ".TB_PREF."bank_accounts.account_type AS BankTransType, ".TB_PREF."bank_trans.amount AS bank_amount,
- ".TB_PREF."bank_trans.ref ";
+ $sql .= ", account.bank_name, account.bank_account_name, account.bank_curr_code,
+ account.account_type AS BankTransType, bank_trans.amount AS bank_amount,
+ bank_trans.ref ";
}
- $sql .= " FROM ".TB_PREF."supp_trans, ".TB_PREF."suppliers ";
+ $sql .= " FROM ".TB_PREF."supp_trans trans,"
+ .TB_PREF."suppliers supplier";
if ($trans_type == ST_SUPPAYMENT || $trans_type == ST_BANKPAYMENT)
{
// it's a payment so also get the bank account
- $sql .= ", ".TB_PREF."bank_trans, ".TB_PREF."bank_accounts";
+ $sql .= ", "
+ .TB_PREF."bank_trans bank_trans,"
+ .TB_PREF."bank_accounts account";
}
- $sql .= " WHERE ".TB_PREF."supp_trans.trans_no=".db_escape($trans_no)."
- AND ".TB_PREF."supp_trans.supplier_id=".TB_PREF."suppliers.supplier_id";
+ $sql .= " WHERE trans.trans_no=".db_escape($trans_no)."
+ AND trans.supplier_id=supplier.supplier_id";
if (isset($supplier_id))
- $sql .= " AND ".TB_PREF."supp_trans.supplier_id=".db_escape($supplier_id);
+ $sql .= " AND trans.supplier_id=".db_escape($supplier_id);
if ($trans_type > -1)
- $sql .= " AND ".TB_PREF."supp_trans.type=".db_escape($trans_type);
+ $sql .= " AND trans.type=".db_escape($trans_type);
if ($trans_type == ST_SUPPAYMENT || $trans_type == ST_BANKPAYMENT)
{
// it's a payment so also get the bank account
- $sql .= " AND ".TB_PREF."bank_trans.trans_no =".db_escape($trans_no)."
- AND ".TB_PREF."bank_trans.type=".db_escape($trans_type)."
- AND ".TB_PREF."bank_accounts.id=".TB_PREF."bank_trans.bank_act ";
+ $sql .= " AND bank_trans.trans_no =".db_escape($trans_no)."
+ AND bank_trans.type=".db_escape($trans_type)."
+ AND account.id=bank_trans.bank_act ";
}
$result = db_query($sql, "Cannot retreive a supplier transaction");
// Revomed allocated from sql
if ($all)
- $value = "(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + "
- .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + "
- .TB_PREF."debtor_trans.ov_discount)";
- else
- $value = "(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + "
- .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + "
- .TB_PREF."debtor_trans.ov_discount - ".TB_PREF."debtor_trans.alloc)";
+ $value = "(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount)";
+ else
+ $value = "(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount - alloc)";
$sign = "IF(`type` IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT,ST_JOURNAL))."), -1, 1)";
- $due = "IF (".TB_PREF."debtor_trans.type=".ST_SALESINVOICE.",".TB_PREF."debtor_trans.due_date,".TB_PREF."debtor_trans.tran_date)";
- $sql = "SELECT ".TB_PREF."debtor_trans.type, ".TB_PREF."debtor_trans.reference,
- ".TB_PREF."debtor_trans.tran_date,
+ $due = "IF (type=".ST_SALESINVOICE.", due_date, tran_date)";
+
+ $sql = "SELECT type, reference, tran_date,
$sign*$value as Balance,
IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$sign*$value,0) AS Due,
IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays1,$sign*$value,0) AS Overdue1,
IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays2,$sign*$value,0) AS Overdue2
- FROM ".TB_PREF."debtors_master,
- ".TB_PREF."debtor_trans
+ FROM ".TB_PREF."debtor_trans trans
+
+ WHERE type <> ".ST_CUSTDELIVERY."
+ AND debtor_no = $customer_id
+ AND tran_date <= '$todate'
+ AND ABS(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) > " . FLOAT_COMP_DELTA;
- WHERE ".TB_PREF."debtor_trans.type <> ".ST_CUSTDELIVERY."
- AND ".TB_PREF."debtors_master.debtor_no = ".TB_PREF."debtor_trans.debtor_no
- AND ".TB_PREF."debtor_trans.debtor_no = $customer_id
- AND ".TB_PREF."debtor_trans.tran_date <= '$todate'
- AND ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + "
- .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + "
- .TB_PREF."debtor_trans.ov_discount) > " . FLOAT_COMP_DELTA;
if (!$all)
- $sql .= "AND ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + "
- .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + "
- .TB_PREF."debtor_trans.ov_discount - ".TB_PREF."debtor_trans.alloc) > " . FLOAT_COMP_DELTA;
- $sql .= "ORDER BY ".TB_PREF."debtor_trans.tran_date";
+ $sql .= "AND ABS(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount - alloc) > " . FLOAT_COMP_DELTA;
+ $sql .= "ORDER BY tran_date";
- return db_query($sql, "The customer details could not be retrieved");
+ return db_query($sql, "The customer transactions could not be retrieved");
}
//----------------------------------------------------------------------------------------------------
function get_customer_details_for_report($area=0, $salesid=0)
{
- $sql = "SELECT ".TB_PREF."debtors_master.debtor_no,
- ".TB_PREF."debtors_master.name,
- ".TB_PREF."debtors_master.address,
- ".TB_PREF."debtors_master.curr_code,
- ".TB_PREF."debtors_master.dimension_id,
- ".TB_PREF."debtors_master.dimension2_id,
- ".TB_PREF."debtors_master.notes,
- ".TB_PREF."sales_types.sales_type,
- ".TB_PREF."cust_branch.branch_code,
- ".TB_PREF."cust_branch.br_name,
- ".TB_PREF."cust_branch.br_address,
- ".TB_PREF."cust_branch.br_post_address,
- ".TB_PREF."cust_branch.area,
- ".TB_PREF."cust_branch.salesman,
- ".TB_PREF."areas.description,
- ".TB_PREF."salesman.salesman_name
- FROM ".TB_PREF."debtors_master
- INNER JOIN ".TB_PREF."cust_branch
- ON ".TB_PREF."debtors_master.debtor_no=".TB_PREF."cust_branch.debtor_no
- INNER JOIN ".TB_PREF."sales_types
- ON ".TB_PREF."debtors_master.sales_type=".TB_PREF."sales_types.id
- INNER JOIN ".TB_PREF."areas
- ON ".TB_PREF."cust_branch.area = ".TB_PREF."areas.area_code
- INNER JOIN ".TB_PREF."salesman
- ON ".TB_PREF."cust_branch.salesman=".TB_PREF."salesman.salesman_code
- WHERE ".TB_PREF."debtors_master.inactive = 0";
+ $sql = "SELECT debtor.debtor_no,
+ debtor.name,
+ debtor.address,
+ debtor.curr_code,
+ debtor.dimension_id,
+ debtor.dimension2_id,
+ debtor.notes,
+ pricelist.sales_type,
+ branch.branch_code,
+ branch.br_name,
+ branch.br_address,
+ branch.br_post_address,
+ branch.area,
+ branch.salesman,
+ area.description,
+ salesman.salesman_name
+ FROM ".TB_PREF."debtors_master debtor
+ INNER JOIN ".TB_PREF."cust_branch branch ON debtor.debtor_no=branch.debtor_no
+ INNER JOIN ".TB_PREF."sales_types pricelist ON debtor.sales_type=pricelist.id
+ INNER JOIN ".TB_PREF."areas area ON branch.area = area.area_code
+ INNER JOIN ".TB_PREF."salesman salesman ON branch.salesman=salesman.salesman_code
+ WHERE debtor.inactive = 0";
if ($area != 0)
{
if ($salesid != 0)
- $sql .= " AND ".TB_PREF."salesman.salesman_code=".db_escape($salesid)."
- AND ".TB_PREF."areas.area_code=".db_escape($area);
+ $sql .= " AND salesman.salesman_code=".db_escape($salesid)."
+ AND area.area_code=".db_escape($area);
else
- $sql .= " AND ".TB_PREF."areas.area_code=".db_escape($area);
+ $sql .= " AND area.area_code=".db_escape($area);
}
elseif ($salesid != 0)
- $sql .= " AND ".TB_PREF."salesman.salesman_code=".db_escape($salesid);
+ $sql .= " AND salesman.salesman_code=".db_escape($salesid);
$sql .= " ORDER BY description,
- ".TB_PREF."salesman.salesman_name,
- ".TB_PREF."debtors_master.debtor_no,
- ".TB_PREF."cust_branch.branch_code";
+ salesman.salesman_name,
+ debtor.debtor_no,
+ branch.branch_code";
return db_query($sql,"No transactions were returned");
}
function get_contacts_for_branch($branch)
{
$sql = "SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type
- FROM ".TB_PREF."crm_persons p,".TB_PREF."crm_contacts r WHERE r.person_id=p.id AND r.type='cust_branch'
+ FROM ".TB_PREF."crm_persons p,"
+ .TB_PREF."crm_contacts r
+ WHERE r.person_id=p.id AND r.type='cust_branch'
AND r.entity_id=".db_escape($branch);
$res = db_query($sql, "can't retrieve branch contacts");
$results = array();
function fetch_items($category=0)
{
- $sql = "SELECT ".TB_PREF."stock_master.stock_id, ".TB_PREF."stock_master.description AS name,
- ".TB_PREF."stock_master.material_cost+".TB_PREF."stock_master.labour_cost+".TB_PREF."stock_master.overhead_cost AS Standardcost,
- ".TB_PREF."stock_master.category_id,".TB_PREF."stock_master.units,
- ".TB_PREF."stock_category.description
- FROM ".TB_PREF."stock_master,
- ".TB_PREF."stock_category
- WHERE ".TB_PREF."stock_master.category_id=".TB_PREF."stock_category.category_id AND NOT ".TB_PREF."stock_master.inactive";
+ $sql = "SELECT item.stock_id, item.description AS name,
+ item.material_cost+item.labour_cost+item.overhead_cost AS Standardcost,
+ item.category_id,item.units,
+ category.description
+ FROM ".TB_PREF."stock_master item,
+ ".TB_PREF."stock_category category
+ WHERE item.category_id=category.category_id AND NOT item.inactive";
if ($category != 0)
- $sql .= " AND ".TB_PREF."stock_category.category_id = ".db_escape($category);
- $sql .= " ORDER BY ".TB_PREF."stock_master.category_id,
- ".TB_PREF."stock_master.stock_id";
+ $sql .= " AND category.category_id = ".db_escape($category);
+ $sql .= " ORDER BY item.category_id,
+ item.stock_id";
return db_query($sql,"No transactions were returned");
}
{
$sql = "SELECT i.item_code AS kit_code, i.description AS kit_name, c.category_id AS cat_id, c.description AS cat_name, count(*)>1 AS kit
FROM
- ".TB_PREF."item_codes i
- LEFT JOIN
- ".TB_PREF."stock_category c
- ON i.category_id=c.category_id";
- $sql .= " WHERE !i.is_foreign AND i.item_code!=i.stock_id";
+ ".TB_PREF."item_codes i
+ LEFT JOIN ".TB_PREF."stock_category c ON i.category_id=c.category_id
+ WHERE !i.is_foreign AND i.item_code!=i.stock_id";
if ($category != 0)
$sql .= " AND c.category_id = ".db_escape($category);
$sql .= " GROUP BY i.item_code";
$fromdate = date2sql($from);
$todate = date2sql($to);
- $sql= "SELECT ".TB_PREF."sales_orders.order_no,
- ".TB_PREF."sales_orders.debtor_no,
- ".TB_PREF."sales_orders.branch_code,
- ".TB_PREF."sales_orders.customer_ref,
- ".TB_PREF."sales_orders.ord_date,
- ".TB_PREF."sales_orders.from_stk_loc,
- ".TB_PREF."sales_orders.delivery_date,
- ".TB_PREF."sales_order_details.stk_code,
- ".TB_PREF."stock_master.description,
- ".TB_PREF."stock_master.units,
- ".TB_PREF."sales_order_details.quantity,
- ".TB_PREF."sales_order_details.qty_sent
- FROM ".TB_PREF."sales_orders
- INNER JOIN ".TB_PREF."sales_order_details
- ON (".TB_PREF."sales_orders.order_no = ".TB_PREF."sales_order_details.order_no
- AND ".TB_PREF."sales_orders.trans_type = ".TB_PREF."sales_order_details.trans_type
- AND ".TB_PREF."sales_orders.trans_type = ".ST_SALESORDER.")
- INNER JOIN ".TB_PREF."stock_master
- ON ".TB_PREF."sales_order_details.stk_code = ".TB_PREF."stock_master.stock_id
- WHERE ".TB_PREF."sales_orders.ord_date >='$fromdate'
- AND ".TB_PREF."sales_orders.ord_date <='$todate'";
+ $sql= "SELECT sorder.order_no,
+ sorder.debtor_no,
+ sorder.branch_code,
+ sorder.customer_ref,
+ sorder.ord_date,
+ sorder.from_stk_loc,
+ sorder.delivery_date,
+ line.stk_code,
+ item.description,
+ item.units,
+ line.quantity,
+ line.qty_sent
+ FROM ".TB_PREF."sales_orders sorder
+ INNER JOIN ".TB_PREF."sales_order_details line
+ ON sorder.order_no = line.order_no
+ AND sorder.trans_type = line.trans_type
+ AND sorder.trans_type = ".ST_SALESORDER."
+ INNER JOIN ".TB_PREF."stock_master item
+ ON line.stk_code = item.stock_id
+ WHERE sorder.ord_date >='$fromdate'
+ AND sorder.ord_date <='$todate'";
if ($category > 0)
- $sql .= " AND ".TB_PREF."stock_master.category_id=".db_escape($category);
+ $sql .= " AND item.category_id=".db_escape($category);
if ($location != null)
- $sql .= " AND ".TB_PREF."sales_orders.from_stk_loc=".db_escape($location);
+ $sql .= " AND sorder.from_stk_loc=".db_escape($location);
if ($backorder)
- $sql .= " AND ".TB_PREF."sales_order_details.quantity - ".TB_PREF."sales_order_details.qty_sent > 0";
- $sql .= " ORDER BY ".TB_PREF."sales_orders.order_no";
+ $sql .= " AND line.quantity - line.qty_sent > 0";
+ $sql .= " ORDER BY sorder.order_no";
return db_query($sql, "Error getting order details");
}
function getTransactions($debtorno, $date, $show_also_allocated)
{
- $sql = "SELECT ".TB_PREF."debtor_trans.*,
- (".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight +
- ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount)
- AS TotalAmount, ".TB_PREF."debtor_trans.alloc AS Allocated,
- ((".TB_PREF."debtor_trans.type = ".ST_SALESINVOICE.")
- AND ".TB_PREF."debtor_trans.due_date < '$date') AS OverDue
- FROM ".TB_PREF."debtor_trans
- WHERE ".TB_PREF."debtor_trans.tran_date <= '$date' AND ".TB_PREF."debtor_trans.debtor_no = ".db_escape($debtorno)."
- AND ".TB_PREF."debtor_trans.type <> ".ST_CUSTDELIVERY."
- AND ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight +
- ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount) > 1e-6";
+ $sql = "SELECT *,
+ (ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) AS TotalAmount, alloc AS Allocated,
+ ((type = ".ST_SALESINVOICE.") AND due_date < '$date') AS OverDue
+ FROM ".TB_PREF."debtor_trans
+ WHERE tran_date <= '$date' AND debtor_no = ".db_escape($debtorno)."
+ AND type <> ".ST_CUSTDELIVERY."
+ AND ABS(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) > ". FLOAT_COMP_DELTA;
+
if (!$show_also_allocated)
- $sql .= " AND ABS(ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight +
- ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount) - alloc) > 1e-6";
- $sql .= " ORDER BY ".TB_PREF."debtor_trans.tran_date";
+ $sql .= " AND ABS(ABS(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) - alloc) > ". FLOAT_COMP_DELTA;
+ $sql .= " ORDER BY tran_date";
return db_query($sql,"No transactions were returned");
}
//----------------------------------------------------------------------------------------------------
function get_receipt($type, $trans_no)
{
- $sql = "SELECT ".TB_PREF."debtor_trans.*,
- (".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight +
- ".TB_PREF."debtor_trans.ov_freight_tax) AS Total,
- ".TB_PREF."debtor_trans.ov_discount,
- ".TB_PREF."debtors_master.name AS DebtorName, ".TB_PREF."debtors_master.debtor_ref,
- ".TB_PREF."debtors_master.curr_code, ".TB_PREF."debtors_master.payment_terms, "
- .TB_PREF."debtors_master.tax_id AS tax_id,
- ".TB_PREF."debtors_master.address
- FROM ".TB_PREF."debtor_trans, ".TB_PREF."debtors_master
- WHERE ".TB_PREF."debtor_trans.debtor_no = ".TB_PREF."debtors_master.debtor_no
- AND ".TB_PREF."debtor_trans.type = ".db_escape($type)."
- AND ".TB_PREF."debtor_trans.trans_no = ".db_escape($trans_no);
+ $sql = "SELECT trans.*,
+ (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax) AS Total,
+ trans.ov_discount,
+ debtor.name AS DebtorName,
+ debtor.debtor_ref,
+ debtor.curr_code,
+ debtor.payment_terms,
+ debtor.tax_id AS tax_id,
+ debtor.address
+ FROM ".TB_PREF."debtor_trans trans,"
+ .TB_PREF."debtors_master debtor
+ WHERE trans.debtor_no = debtor.debtor_no
+ AND trans.type = ".db_escape($type)."
+ AND trans.trans_no = ".db_escape($trans_no);
$result = db_query($sql, "The remittance cannot be retrieved");
if (db_num_rows($result) == 0)
return false;
{
$to = date2sql($to);
- $sql = "SELECT SUM(IF(".TB_PREF."supp_trans.type = ".ST_SUPPINVOICE." OR ".TB_PREF."supp_trans.type = ".ST_BANKDEPOSIT.",
- (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount), 0)) AS charges,
- SUM(IF(".TB_PREF."supp_trans.type <> ".ST_SUPPINVOICE." AND ".TB_PREF."supp_trans.type <> ".ST_BANKDEPOSIT.",
- (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount), 0)) AS credits,
- SUM(".TB_PREF."supp_trans.alloc) AS Allocated,
- SUM(IF(".TB_PREF."supp_trans.type = ".ST_SUPPINVOICE." OR ".TB_PREF."supp_trans.type = ".ST_BANKDEPOSIT.",
- (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ".TB_PREF."supp_trans.alloc),
- (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount + ".TB_PREF."supp_trans.alloc))) AS OutStanding
+ $sql = "SELECT
+ SUM(IF(type IN(".ST_SUPPINVOICE.",".ST_BANKDEPOSIT."), (ov_amount + ov_gst + ov_discount), 0)) AS charges,
+ SUM(IF(type NOT IN(".ST_SUPPINVOICE.",".ST_BANKDEPOSIT."), (ov_amount + ov_gst + ov_discount), 0)) AS credits,
+ SUM(alloc) AS Allocated,
+ SUM(IF(type IN(".ST_SUPPINVOICE.",".ST_BANKDEPOSIT."), (ov_amount + ov_gst + ov_discount - alloc),
+ (ov_amount + ov_gst + ov_discount + alloc))) AS OutStanding
FROM ".TB_PREF."supp_trans
- WHERE ".TB_PREF."supp_trans.tran_date < '$to'
- AND ".TB_PREF."supp_trans.supplier_id = '$supplier_id' GROUP BY supplier_id";
+ WHERE tran_date < '$to'
+ AND supplier_id = '$supplier_id' GROUP BY supplier_id";
$result = db_query($sql,"No transactions were returned");
return db_fetch($result);
$from = date2sql($from);
$to = date2sql($to);
- $sql = "SELECT ".TB_PREF."supp_trans.*,
- (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount)
- AS TotalAmount, ".TB_PREF."supp_trans.alloc AS Allocated,
- ((".TB_PREF."supp_trans.type = ".ST_SUPPINVOICE.")
- AND ".TB_PREF."supp_trans.due_date < '$to') AS OverDue
- FROM ".TB_PREF."supp_trans
- WHERE ".TB_PREF."supp_trans.tran_date >= '$from' AND ".TB_PREF."supp_trans.tran_date <= '$to'
- AND ".TB_PREF."supp_trans.supplier_id = '$supplier_id' AND ".TB_PREF."supp_trans.ov_amount!=0
- ORDER BY ".TB_PREF."supp_trans.tran_date";
+ $sql = "SELECT *,
+ (ov_amount + ov_gst + ov_discount) AS TotalAmount,
+ alloc AS Allocated,
+ ((type = ".ST_SUPPINVOICE.") AND due_date < '$to') AS OverDue
+ FROM ".TB_PREF."supp_trans
+ WHERE tran_date >= '$from' AND tran_date <= '$to'
+ AND supplier_id = '$supplier_id' AND ov_amount!=0
+ ORDER BY tran_date";
$TransResult = db_query($sql,"No transactions were returned");
// Revomed allocated from sql
if ($all)
- $value = "(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount)";
+ $value = "(trans.ov_amount + trans.ov_gst + trans.ov_discount)";
else
- $value = "IF (".TB_PREF."supp_trans.type=".ST_SUPPINVOICE." OR ".TB_PREF."supp_trans.type=".ST_BANKDEPOSIT.",
- (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount - ".TB_PREF."supp_trans.alloc),
- (".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount + ".TB_PREF."supp_trans.alloc))";
- $due = "IF (".TB_PREF."supp_trans.type=".ST_SUPPINVOICE." OR ".TB_PREF."supp_trans.type=".ST_SUPPCREDIT.",".TB_PREF."supp_trans.due_date,".TB_PREF."supp_trans.tran_date)";
- $sql = "SELECT ".TB_PREF."supp_trans.type,
- ".TB_PREF."supp_trans.reference,
- ".TB_PREF."supp_trans.tran_date,
+ $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))";
+ $due = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_SUPPCREDIT.",trans.due_date,trans.tran_date)";
+ $sql = "SELECT trans.type,
+ trans.reference,
+ trans.tran_date,
$value as Balance,
IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$value,0) AS Due,
IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays1,$value,0) AS Overdue1,
IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays2,$value,0) AS Overdue2
- FROM ".TB_PREF."suppliers,
- ".TB_PREF."payment_terms,
- ".TB_PREF."supp_trans
+ FROM ".TB_PREF."suppliers supplier,
+ ".TB_PREF."supp_trans trans
- WHERE ".TB_PREF."suppliers.payment_terms = ".TB_PREF."payment_terms.terms_indicator
- AND ".TB_PREF."suppliers.supplier_id = ".TB_PREF."supp_trans.supplier_id
- AND ".TB_PREF."supp_trans.supplier_id = $supplier_id
- AND ".TB_PREF."supp_trans.tran_date <= '$todate'
- AND ABS(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount) > ".FLOAT_COMP_DELTA." ";
+ WHERE supplier.supplier_id = trans.supplier_id
+ AND trans.supplier_id = $supplier_id
+ AND trans.tran_date <= '$todate'
+ AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) > ".FLOAT_COMP_DELTA;
if (!$all)
- $sql .= "AND ABS(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount) - ".TB_PREF."supp_trans.alloc > ".FLOAT_COMP_DELTA." ";
- $sql .= "ORDER BY ".TB_PREF."supp_trans.tran_date";
+ $sql .= " AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) - trans.alloc > ".FLOAT_COMP_DELTA;
+ $sql .= " ORDER BY trans.tran_date";
return db_query($sql, "The supplier details could not be retrieved");
$date = date2sql($date);
$dec = user_price_dec();
- $sql = "SELECT ".TB_PREF."supp_trans.supp_reference,
- ".TB_PREF."supp_trans.tran_date,
- ".TB_PREF."supp_trans.due_date,
- ".TB_PREF."supp_trans.trans_no,
- ".TB_PREF."supp_trans.type,
- ".TB_PREF."supp_trans.rate,
- (ABS(".TB_PREF."supp_trans.ov_amount) + ABS(".TB_PREF."supp_trans.ov_gst) - ".TB_PREF."supp_trans.alloc) AS Balance,
- (ABS(".TB_PREF."supp_trans.ov_amount) + ABS(".TB_PREF."supp_trans.ov_gst) ) AS TranTotal
+ $sql = "SELECT supp_reference, tran_date, due_date, trans_no, type, rate,
+ (ABS( ov_amount) + ABS( ov_gst) - alloc) AS Balance,
+ (ABS( ov_amount) + ABS( ov_gst) ) AS TranTotal
FROM ".TB_PREF."supp_trans
- WHERE ".TB_PREF."supp_trans.supplier_id = '" . $supplier . "'
- AND ROUND(ABS(".TB_PREF."supp_trans.ov_amount),$dec) + ROUND(ABS(".TB_PREF."supp_trans.ov_gst),$dec) -
- ROUND(".TB_PREF."supp_trans.alloc,$dec) != 0
- AND ".TB_PREF."supp_trans.tran_date <='" . $date . "'
- ORDER BY ".TB_PREF."supp_trans.type,
- ".TB_PREF."supp_trans.trans_no";
+ WHERE supplier_id = '$supplier'
+ AND ROUND(ABS( ov_amount),$dec) + ROUND(ABS( ov_gst),$dec) -
+ ROUND( alloc,$dec) != 0
+ AND tran_date <='$date'
+ ORDER BY type,
+ trans_no";
return db_query($sql, "No transactions were returned");
}
function getTransactions($fromsupp)
{
- $sql = "SELECT ".TB_PREF."grn_batch.id,
+ $sql = "SELECT grn.id,
order_no,
- ".TB_PREF."grn_batch.supplier_id,
- ".TB_PREF."suppliers.supp_name,
- ".TB_PREF."grn_items.item_code,
- ".TB_PREF."grn_items.description,
+ grn.supplier_id,
+ supplier.supp_name,
+ item.item_code,
+ item.description,
qty_recd,
quantity_inv,
std_cost_unit,
act_price,
unit_price
- FROM ".TB_PREF."grn_items,
- ".TB_PREF."grn_batch,
- ".TB_PREF."purch_order_details,
- ".TB_PREF."suppliers
- WHERE ".TB_PREF."grn_batch.supplier_id=".TB_PREF."suppliers.supplier_id
- AND ".TB_PREF."grn_batch.id = ".TB_PREF."grn_items.grn_batch_id
- AND ".TB_PREF."grn_items.po_detail_item = ".TB_PREF."purch_order_details.po_detail_item
- AND qty_recd-quantity_inv <>0 ";
+ FROM ".TB_PREF."grn_items item,
+ ".TB_PREF."grn_batch grn,
+ ".TB_PREF."purch_order_details poline,
+ ".TB_PREF."suppliers supplier
+ WHERE grn.supplier_id=supplier.supplier_id
+ AND grn.id = item.grn_batch_id
+ AND item.po_detail_item = poline.po_detail_item
+ AND qty_recd-quantity_inv!=0";
if ($fromsupp != ALL_TEXT)
- $sql .= "AND ".TB_PREF."grn_batch.supplier_id =".db_escape($fromsupp)." ";
- $sql .= "ORDER BY ".TB_PREF."grn_batch.supplier_id,
- ".TB_PREF."grn_batch.id";
+ $sql .= " AND grn.supplier_id =".db_escape($fromsupp);
+
+ $sql .= " ORDER BY grn.supplier_id, grn.id";
return db_query($sql, "No transactions were returned");
}
function get_supplier_details_for_report()
{
- $sql = "SELECT ".TB_PREF."suppliers.supplier_id,
- ".TB_PREF."suppliers.supp_name,
- ".TB_PREF."suppliers.address,
- ".TB_PREF."suppliers.supp_address,
- ".TB_PREF."suppliers.supp_ref,
- ".TB_PREF."suppliers.contact,
- ".TB_PREF."suppliers.curr_code,
- ".TB_PREF."suppliers.dimension_id,
- ".TB_PREF."suppliers.dimension2_id,
- ".TB_PREF."suppliers.notes,
- ".TB_PREF."suppliers.gst_no
- FROM ".TB_PREF."suppliers
- WHERE inactive = 0
- ORDER BY supp_name";
+ $sql = "SELECT supplier_id, supp_name, address, supp_address, supp_ref,
+ contact, curr_code, dimension_id, dimension2_id, notes, gst_no
+ FROM ".TB_PREF."suppliers
+ WHERE inactive = 0
+ ORDER BY supp_name";
return db_query($sql,"No transactions were returned");
}
//----------------------------------------------------------------------------------------------------
function get_po($order_no)
{
- $sql = "SELECT ".TB_PREF."purch_orders.*, ".TB_PREF."suppliers.supp_name, "
- .TB_PREF."suppliers.supp_account_no,".TB_PREF."suppliers.tax_included,".TB_PREF."suppliers.gst_no AS tax_id,
- ".TB_PREF."suppliers.curr_code, ".TB_PREF."suppliers.payment_terms, ".TB_PREF."locations.location_name,
- ".TB_PREF."suppliers.address, ".TB_PREF."suppliers.contact, ".TB_PREF."suppliers.tax_group_id
- FROM ".TB_PREF."purch_orders, ".TB_PREF."suppliers, ".TB_PREF."locations
- WHERE ".TB_PREF."purch_orders.supplier_id = ".TB_PREF."suppliers.supplier_id
- AND ".TB_PREF."locations.loc_code = into_stock_location
- AND ".TB_PREF."purch_orders.order_no = ".db_escape($order_no);
+ $sql = "SELECT po.*, supplier.supp_name, supplier.supp_account_no,supplier.tax_included,
+ supplier.gst_no AS tax_id,
+ supplier.curr_code, supplier.payment_terms, loc.location_name,
+ supplier.address, supplier.contact, supplier.tax_group_id
+ FROM ".TB_PREF."purch_orders po,"
+ .TB_PREF."suppliers supplier,"
+ .TB_PREF."locations loc
+ WHERE po.supplier_id = supplier.supplier_id
+ AND loc.loc_code = into_stock_location
+ AND po.order_no = ".db_escape($order_no);
$result = db_query($sql, "The order cannot be retrieved");
return db_fetch($result);
}
function get_po_details($order_no)
{
- $sql = "SELECT ".TB_PREF."purch_order_details.*, units
- FROM ".TB_PREF."purch_order_details
- LEFT JOIN ".TB_PREF."stock_master
- ON ".TB_PREF."purch_order_details.item_code=".TB_PREF."stock_master.stock_id
+ $sql = "SELECT poline.*, units
+ FROM ".TB_PREF."purch_order_details poline
+ LEFT JOIN ".TB_PREF."stock_master item ON poline.item_code=item.stock_id
WHERE order_no =".db_escape($order_no)." ";
$sql .= " ORDER BY po_detail_item";
return db_query($sql, "Retreive order Line Items");
//----------------------------------------------------------------------------------------------------
function get_remittance($type, $trans_no)
{
- $sql = "SELECT ".TB_PREF."supp_trans.*,
- (".TB_PREF."supp_trans.ov_amount+".TB_PREF."supp_trans.ov_gst) AS Total,
- ".TB_PREF."supp_trans.ov_discount,
- ".TB_PREF."suppliers.supp_name, ".TB_PREF."suppliers.supp_account_no,
- ".TB_PREF."suppliers.curr_code, ".TB_PREF."suppliers.payment_terms, ".TB_PREF."suppliers.gst_no AS tax_id,
- ".TB_PREF."suppliers.address
- FROM ".TB_PREF."supp_trans, ".TB_PREF."suppliers
- WHERE ".TB_PREF."supp_trans.supplier_id = ".TB_PREF."suppliers.supplier_id
- AND ".TB_PREF."supp_trans.type = ".db_escape($type)."
- AND ".TB_PREF."supp_trans.trans_no = ".db_escape($trans_no);
+ $sql = "SELECT trans.*,
+ (trans.ov_amount+trans.ov_gst) AS Total,
+ trans.ov_discount,
+ supplier.supp_name, supplier.supp_account_no,
+ supplier.curr_code, supplier.payment_terms, supplier.gst_no AS tax_id,
+ supplier.address
+ FROM "
+ .TB_PREF."supp_trans trans,"
+ .TB_PREF."suppliers supplier
+ WHERE trans.supplier_id = supplier.supplier_id
+ AND trans.type = ".db_escape($type)."
+ AND trans.trans_no = ".db_escape($trans_no);
$result = db_query($sql, "The remittance cannot be retrieved");
if (db_num_rows($result) == 0)
return false;
if ($myrow['type'] == ST_SUPPRECEIVE)
{
// Has the supplier invoice increased the receival price?
- $sql = "SELECT DISTINCT act_price FROM ".TB_PREF."purch_order_details pod INNER JOIN ".TB_PREF."grn_batch grn ON pod.order_no =
+ $sql = "SELECT DISTINCT act_price
+ FROM ".TB_PREF."purch_order_details pod
+ INNER JOIN ".TB_PREF."grn_batch grn ON pod.order_no =
grn.purch_order_no WHERE grn.id = ".$myrow['trans_no']." AND pod.item_code = '$stock_id'";
$result = db_query($sql, "Could not retrieve act_price from purch_order_details");
$row = db_fetch_row($result);
return 0;
return $tot_cost / $count;
}
-
+
function getTransactions($category, $location, $date)
{
$date = date2sql($date);
-
- $sql = "SELECT ".TB_PREF."stock_master.category_id,
- ".TB_PREF."stock_category.description AS cat_description,
- ".TB_PREF."stock_master.stock_id,
- ".TB_PREF."stock_master.units,
- ".TB_PREF."stock_master.description, ".TB_PREF."stock_master.inactive,
- ".TB_PREF."stock_moves.loc_code,
- SUM(".TB_PREF."stock_moves.qty) AS QtyOnHand,
- ".TB_PREF."stock_master.material_cost + ".TB_PREF."stock_master.labour_cost + ".TB_PREF."stock_master.overhead_cost AS UnitCost,
- SUM(".TB_PREF."stock_moves.qty) *(".TB_PREF."stock_master.material_cost + ".TB_PREF."stock_master.labour_cost + ".TB_PREF."stock_master.overhead_cost) AS ItemTotal
- FROM ".TB_PREF."stock_master,
- ".TB_PREF."stock_category,
- ".TB_PREF."stock_moves
- WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."stock_moves.stock_id
- AND ".TB_PREF."stock_master.category_id=".TB_PREF."stock_category.category_id
- AND ".TB_PREF."stock_master.mb_flag<>'D'
- AND ".TB_PREF."stock_moves.tran_date <= '$date'
- GROUP BY ".TB_PREF."stock_master.category_id,
- ".TB_PREF."stock_category.description, ";
+
+ $sql = "SELECT item.category_id,
+ category.description AS cat_description,
+ item.stock_id,
+ item.units,
+ item.description, item.inactive,
+ move.loc_code,
+ SUM(move.qty) AS QtyOnHand,
+ item.material_cost + item.labour_cost + item.overhead_cost AS UnitCost,
+ SUM(move.qty) *(item.material_cost + item.labour_cost + item.overhead_cost) AS ItemTotal
+ FROM "
+ .TB_PREF."stock_master item,"
+ .TB_PREF."stock_category category,"
+ .TB_PREF."stock_moves move
+ WHERE item.stock_id=move.stock_id
+ AND item.category_id=category.category_id
+ AND item.mb_flag<>'D'
+ AND move.tran_date <= '$date'
+ GROUP BY item.category_id,
+ category.description, ";
if ($location != 'all')
- $sql .= TB_PREF."stock_moves.loc_code, ";
- $sql .= TB_PREF."stock_master.stock_id,
- ".TB_PREF."stock_master.description
- HAVING SUM(".TB_PREF."stock_moves.qty) != 0";
+ $sql .= "move.loc_code, ";
+ $sql .= "item.stock_id,
+ item.description
+ HAVING SUM(move.qty) != 0";
if ($category != 0)
- $sql .= " AND ".TB_PREF."stock_master.category_id = ".db_escape($category);
+ $sql .= " AND item.category_id = ".db_escape($category);
if ($location != 'all')
- $sql .= " AND ".TB_PREF."stock_moves.loc_code = ".db_escape($location);
- $sql .= " ORDER BY ".TB_PREF."stock_master.category_id,
- ".TB_PREF."stock_master.stock_id";
+ $sql .= " AND move.loc_code = ".db_escape($location);
+ $sql .= " ORDER BY item.category_id,
+ item.stock_id";
return db_query($sql,"No transactions were returned");
}
function getTransactions($category, $location)
{
- $sql = "SELECT ".TB_PREF."stock_master.category_id,
- ".TB_PREF."stock_category.description AS cat_description,
- ".TB_PREF."stock_master.stock_id,
- ".TB_PREF."stock_master.description, ".TB_PREF."stock_master.inactive,
- IF(".TB_PREF."stock_moves.stock_id IS NULL, '', ".TB_PREF."stock_moves.loc_code) AS loc_code,
- SUM(IF(".TB_PREF."stock_moves.stock_id IS NULL,0,".TB_PREF."stock_moves.qty)) AS qty_on_hand
- FROM (".TB_PREF."stock_master,
- ".TB_PREF."stock_category)
- LEFT JOIN ".TB_PREF."stock_moves ON
- (".TB_PREF."stock_master.stock_id=".TB_PREF."stock_moves.stock_id)
- WHERE ".TB_PREF."stock_master.category_id=".TB_PREF."stock_category.category_id
- AND (".TB_PREF."stock_master.mb_flag='B' OR ".TB_PREF."stock_master.mb_flag='M')";
+ $sql = "SELECT item.category_id,
+ category.description AS cat_description,
+ item.stock_id,
+ item.description, item.inactive,
+ IF(move.stock_id IS NULL, '', move.loc_code) AS loc_code,
+ SUM(IFNULL(move.stock_id, 0)) AS qty_on_hand
+ FROM (".TB_PREF."stock_master item,"
+ .TB_PREF."stock_category category)
+ LEFT JOIN ".TB_PREF."stock_moves move ON item.stock_id=move.stock_id
+ WHERE item.category_id=category.category_id
+ AND (item.mb_flag='B' OR item.mb_flag='M')";
if ($category != 0)
- $sql .= " AND ".TB_PREF."stock_master.category_id = ".db_escape($category);
+ $sql .= " AND item.category_id = ".db_escape($category);
if ($location != 'all')
- $sql .= " AND IF(".TB_PREF."stock_moves.stock_id IS NULL, '1=1',".TB_PREF."stock_moves.loc_code = ".db_escape($location).")";
- $sql .= " GROUP BY ".TB_PREF."stock_master.category_id,
- ".TB_PREF."stock_category.description,
- ".TB_PREF."stock_master.stock_id,
- ".TB_PREF."stock_master.description
- ORDER BY ".TB_PREF."stock_master.category_id,
- ".TB_PREF."stock_master.stock_id";
+ $sql .= " AND IF(move.stock_id IS NULL, '1=1',move.loc_code = ".db_escape($location).")";
+ $sql .= " GROUP BY item.category_id,
+ category.description,
+ item.stock_id,
+ item.description
+ ORDER BY item.category_id,
+ item.stock_id";
return db_query($sql,"No transactions were returned");
function getTransactions($category, $location, $item_like)
{
- $sql = "SELECT ".TB_PREF."stock_master.category_id,
- ".TB_PREF."stock_category.description AS cat_description,
- ".TB_PREF."stock_master.stock_id,
- ".TB_PREF."stock_master.units,
- ".TB_PREF."stock_master.description, ".TB_PREF."stock_master.inactive,
- IF(".TB_PREF."stock_moves.stock_id IS NULL, '', ".TB_PREF."stock_moves.loc_code) AS loc_code,
- SUM(IF(".TB_PREF."stock_moves.stock_id IS NULL,0,".TB_PREF."stock_moves.qty)) AS QtyOnHand
- FROM (".TB_PREF."stock_master,
- ".TB_PREF."stock_category)
- LEFT JOIN ".TB_PREF."stock_moves ON
- (".TB_PREF."stock_master.stock_id=".TB_PREF."stock_moves.stock_id)
- WHERE ".TB_PREF."stock_master.category_id=".TB_PREF."stock_category.category_id
- AND (".TB_PREF."stock_master.mb_flag='B' OR ".TB_PREF."stock_master.mb_flag='M')";
+ $sql = "SELECT item.category_id,
+ category.description AS cat_description,
+ item.stock_id, item.units,
+ item.description, item.inactive,
+ IF(move.stock_id IS NULL, '', move.loc_code) AS loc_code,
+ SUM(IF(move.stock_id IS NULL,0,move.qty)) AS QtyOnHand
+ FROM ("
+ .TB_PREF."stock_master item,"
+ .TB_PREF."stock_category category)
+ LEFT JOIN ".TB_PREF."stock_moves move ON item.stock_id=move.stock_id
+ WHERE item.category_id=category.category_id
+ AND (item.mb_flag='B' OR item.mb_flag='M')";
if ($category != 0)
- $sql .= " AND ".TB_PREF."stock_master.category_id = ".db_escape($category);
+ $sql .= " AND item.category_id = ".db_escape($category);
if ($location != 'all')
- $sql .= " AND IF(".TB_PREF."stock_moves.stock_id IS NULL, '1=1',".TB_PREF."stock_moves.loc_code = ".db_escape($location).")";
+ $sql .= " AND IF(move.stock_id IS NULL, '1=1',move.loc_code = ".db_escape($location).")";
if($item_like)
{
$regexp = null;
if(sscanf($item_like, "/%s", $regexp)==1)
- $sql .= " AND ".TB_PREF."stock_master.stock_id RLIKE ".db_escape($regexp);
+ $sql .= " AND item.stock_id RLIKE ".db_escape($regexp);
else
- $sql .= " AND ".TB_PREF."stock_master.stock_id LIKE ".db_escape($item_like);
+ $sql .= " AND item.stock_id LIKE ".db_escape($item_like);
}
- $sql .= " GROUP BY ".TB_PREF."stock_master.category_id,
- ".TB_PREF."stock_category.description,
- ".TB_PREF."stock_master.stock_id,
- ".TB_PREF."stock_master.description
- ORDER BY ".TB_PREF."stock_master.category_id,
- ".TB_PREF."stock_master.stock_id";
+ $sql .= " GROUP BY item.category_id,
+ category.description,
+ item.stock_id,
+ item.description
+ ORDER BY item.category_id,
+ item.stock_id";
return db_query($sql,"No transactions were returned");
}
$from = date2sql($from);
$to = date2sql($to);
- $sql = "SELECT ".TB_PREF."grn_batch.id batch_no,
- ".TB_PREF."grn_batch.supplier_id,
- ".TB_PREF."purch_order_details.*,
- ".TB_PREF."stock_master.description,
- ".TB_PREF."grn_items.qty_recd,
- ".TB_PREF."grn_items.quantity_inv,
- ".TB_PREF."grn_items.id grn_item_id
- FROM ".TB_PREF."stock_master,
- ".TB_PREF."purch_order_details,
- ".TB_PREF."grn_batch,
- ".TB_PREF."grn_items
- WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."purch_order_details.item_code
- AND ".TB_PREF."grn_batch.purch_order_no=".TB_PREF."purch_order_details.order_no
- AND ".TB_PREF."grn_batch.id = ".TB_PREF."grn_items.grn_batch_id
- AND ".TB_PREF."grn_items.po_detail_item = ".TB_PREF."purch_order_details.po_detail_item
- AND ".TB_PREF."grn_items.qty_recd>0
- AND ".TB_PREF."grn_batch.delivery_date>='$from'
- AND ".TB_PREF."grn_batch.delivery_date<='$to'
- ORDER BY ".TB_PREF."stock_master.stock_id, ".TB_PREF."grn_batch.delivery_date";
+ $sql = "SELECT grn.id batch_no,
+ grn.supplier_id,
+ poline.*,
+ item.description,
+ grn_line.qty_recd,
+ grn_line.quantity_inv,
+ grn_line.id grn_item_id
+ FROM "
+ .TB_PREF."stock_master item,"
+ .TB_PREF."purch_order_details poline,"
+ .TB_PREF."grn_batch grn,"
+ .TB_PREF."grn_items grn_line
+ WHERE item.stock_id=poline.item_code
+ AND grn.purch_order_no=poline.order_no
+ AND grn.id = grn_line.grn_batch_id
+ AND grn_line.po_detail_item = poline.po_detail_item
+ AND grn_line.qty_recd>0
+ AND grn.delivery_date>='$from'
+ AND grn.delivery_date<='$to'
+ ORDER BY item.stock_id, grn.delivery_date";
return db_query($sql,"No transactions were returned");
function getSuppInvDetails($grn_item_id)
{
$sql = "SELECT
- ".TB_PREF."supp_invoice_items.supp_trans_no inv_no,
- ".TB_PREF."supp_invoice_items.quantity inv_qty,
- ".TB_PREF."supp_trans.rate,
- IF (".TB_PREF."supp_trans.tax_included = 1, ".TB_PREF."supp_invoice_items.unit_price - ".TB_PREF."supp_invoice_items.unit_tax, ".TB_PREF."supp_invoice_items.unit_price) inv_price
- FROM ".TB_PREF."grn_items, ".TB_PREF."supp_trans, ".TB_PREF."supp_invoice_items
- WHERE ".TB_PREF."grn_items.id = ".TB_PREF."supp_invoice_items.grn_item_id
- AND ".TB_PREF."grn_items.po_detail_item = ".TB_PREF."supp_invoice_items.po_detail_item_id
- AND ".TB_PREF."grn_items.item_code = ".TB_PREF."supp_invoice_items.stock_id
- AND ".TB_PREF."supp_trans.type = ".TB_PREF."supp_invoice_items.supp_trans_type
- AND ".TB_PREF."supp_trans.trans_no = ".TB_PREF."supp_invoice_items.supp_trans_no
- AND ".TB_PREF."supp_invoice_items.supp_trans_type = 20
- AND ".TB_PREF."supp_invoice_items.grn_item_id = ".$grn_item_id."
- ORDER BY ".TB_PREF."supp_invoice_items.id asc";
+ inv_line.supp_trans_no inv_no,
+ inv_line.quantity inv_qty,
+ inv.rate,
+ IF (inv.tax_included = 1, inv_line.unit_price - inv_line.unit_tax, inv_line.unit_price) inv_price
+ FROM "
+ .TB_PREF."grn_items grn_line,"
+ .TB_PREF."supp_trans inv,"
+ .TB_PREF."supp_invoice_items inv_line
+ WHERE grn_line.id = inv_line.grn_item_id
+ AND grn_line.po_detail_item = inv_line.po_detail_item_id
+ AND grn_line.item_code = inv_line.stock_id
+ AND inv.type = inv_line.supp_trans_type
+ AND inv.trans_no = inv_line.supp_trans_no
+ AND inv_line.supp_trans_type = 20
+ AND inv_line.grn_item_id = ".$grn_item_id."
+ ORDER BY inv_line.id asc";
return db_query($sql,"No transactions were returned");
}
function getTransactions($from, $to)
{
- $sql = "SELECT ".TB_PREF."bom.parent,
- ".TB_PREF."bom.component,
- ".TB_PREF."stock_master.description as CompDescription,
- ".TB_PREF."bom.quantity,
- ".TB_PREF."bom.loc_code,
- ".TB_PREF."bom.workcentre_added
- FROM
- ".TB_PREF."stock_master,
- ".TB_PREF."bom
- WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."bom.component
- AND ".TB_PREF."bom.parent >= ".db_escape($from)."
- AND ".TB_PREF."bom.parent <= ".db_escape($to)."
+ $sql = "SELECT bom.parent,
+ bom.component,
+ item.description as CompDescription,
+ bom.quantity,
+ bom.loc_code,
+ bom.workcentre_added
+ FROM "
+ .TB_PREF."stock_master item,"
+ .TB_PREF."bom bom
+ WHERE item.stock_id=bom.component
+ AND bom.parent >= ".db_escape($from)."
+ AND bom.parent <= ".db_escape($to)."
ORDER BY
- ".TB_PREF."bom.parent,
- ".TB_PREF."bom.component";
+ bom.parent,
+ bom.component";
return db_query($sql,"No transactions were returned");
}
{
$from = date2sql($from);
$to = date2sql($to);
- $sql = "SELECT ".TB_PREF."bank_trans.*, ".TB_PREF."comments.memo_
- FROM ".TB_PREF."bank_trans LEFT JOIN ".TB_PREF."comments ON
- (".TB_PREF."bank_trans.type = ".TB_PREF."comments.type
- AND ".TB_PREF."bank_trans.trans_no = ".TB_PREF."comments.id)
- WHERE ".TB_PREF."bank_trans.bank_act = '$account'
+ $sql = "SELECT trans.*, com.memo_
+ FROM "
+ .TB_PREF."bank_trans trans
+ LEFT JOIN ".TB_PREF."comments com ON trans.type = com.type AND trans.trans_no = com.id
+ WHERE trans.bank_act = '$account'
AND trans_date >= '$from'
AND trans_date <= '$to'
- ORDER BY trans_date,".TB_PREF."bank_trans.id";
+ ORDER BY trans_date,trans.id";
return db_query($sql,"The transactions for '$account' could not be retrieved");
}
function get_branch($branch_id)
{
- $sql = "SELECT ".TB_PREF."cust_branch.*,".TB_PREF."salesman.salesman_name
- FROM ".TB_PREF."cust_branch, ".TB_PREF."salesman
- WHERE ".TB_PREF."cust_branch.salesman=".TB_PREF."salesman.salesman_code
- AND branch_code=".db_escape($branch_id);
-
+ $sql = "SELECT branch.*, salesman.salesman_name
+ FROM "
+ .TB_PREF."cust_branch branch,"
+ .TB_PREF."salesman salesman
+ WHERE branch.salesman=salesman.salesman_code
+ AND branch_code=".db_escape($branch_id);
+
$result = db_query($sql, "Cannot retreive a customer branch");
-
+
return db_fetch($result);
}
'customer.general');
$sql = "SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type
- FROM ".TB_PREF."crm_persons p,"
- .TB_PREF."crm_contacts r WHERE r.person_id=p.id AND ((r.type='cust_branch'
+ FROM "
+ .TB_PREF."crm_persons p,"
+ .TB_PREF."crm_contacts r
+ WHERE r.person_id=p.id AND ((r.type='cust_branch'
AND r.entity_id=".db_escape($branch_code).')';
if($customer_id) {
$sql .= " OR (r.type='customer' AND r.entity_id=".db_escape($customer_id).")";
{
$sql = "SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type
FROM ".TB_PREF."crm_persons p,"
- .TB_PREF."crm_contacts r WHERE r.person_id=p.id AND r.type='cust_branch'
+ .TB_PREF."crm_contacts r
+ WHERE r.person_id=p.id AND r.type='cust_branch'
AND r.entity_id=".db_escape($branch_code);
if ($action)
if($customer_id) {
$sql = "($sql) UNION (SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type
FROM ".TB_PREF."crm_persons p,"
- .TB_PREF."crm_contacts r WHERE r.person_id=p.id AND r.type='customer'
+ .TB_PREF."crm_contacts r
+ WHERE r.person_id=p.id AND r.type='customer'
AND r.entity_id=".db_escape($customer_id);
if ($action)
$sql .= ' AND (r.action='.db_escape($action).($default ? " OR r.action='general'":'').')';
$sql .= ')';
}
-
+
$res = db_query($sql, "can't retrieve branch contacts");
$results = array();
$type = '';
{
$sql = "INSERT INTO ".TB_PREF."credit_status (reason_description, dissallow_invoices)
VALUES (".db_escape($description).",".db_escape($disallow_invoicing).")";
-
- db_query($sql, "could not add credit status");
+
+ db_query($sql, "could not add credit status");
}
function update_credit_status($status_id, $description, $disallow_invoicing)
{
$sql = "UPDATE ".TB_PREF."credit_status SET reason_description=".db_escape($description).",
dissallow_invoices=".db_escape($disallow_invoicing)." WHERE id=".db_escape($status_id);
-
- db_query($sql, "could not update credit status");
+
+ db_query($sql, "could not update credit status");
}
function get_all_credit_status($all=false)
if (!$all) $sql .= " WHERE !inactive";
return db_query($sql, "could not get all credit status");
-}
+}
function get_credit_status($status_id)
{
$sql = "SELECT * FROM ".TB_PREF."credit_status WHERE id=".db_escape($status_id);
-
+
$result = db_query($sql, "could not get credit status");
-
+
return db_fetch($result);
}
function delete_credit_status($status_id)
{
$sql="DELETE FROM ".TB_PREF."credit_status WHERE id=".db_escape($status_id);
-
- db_query($sql, "could not delete credit status");
+
+ db_query($sql, "could not delete credit status");
}
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=".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";
+ $sql = "SELECT debtor.name, debtor.curr_code, branch.br_name
+ FROM "
+ .TB_PREF."debtors_master debtor,"
+ .TB_PREF."cust_branch branch,"
+ .TB_PREF."debtor_trans trans
+ WHERE trans.type=".db_escape($type)." AND trans.trans_no=".db_escape($type_no)."
+ AND debtor.debtor_no = trans.debtor_no
+ AND branch.branch_code = trans.branch_code";
$result = db_query($sql, "could not get customer details from trans");
return db_fetch($result);
if (!is_array($debtor_trans_no))
$debtor_trans_no = array( 0=>$debtor_trans_no );
- $sql = "SELECT ".TB_PREF."debtor_trans_details.*,
- ".TB_PREF."debtor_trans_details.unit_price+".TB_PREF."debtor_trans_details.unit_tax AS FullUnitPrice,
- ".TB_PREF."debtor_trans_details.description As StockDescription,
- ".TB_PREF."stock_master.units, ".TB_PREF."stock_master.mb_flag
- FROM ".TB_PREF."debtor_trans_details, ".TB_PREF."stock_master
+ $sql = "SELECT line.*,
+ line.unit_price+line.unit_tax AS FullUnitPrice,
+ line.description As StockDescription,
+ item.units, item.mb_flag
+ FROM "
+ .TB_PREF."debtor_trans_details line,"
+ .TB_PREF."stock_master item
WHERE (";
$tr=array();
$sql.= ") AND debtor_trans_type=".db_escape($debtor_trans_type)."
- AND ".TB_PREF."stock_master.stock_id=".TB_PREF."debtor_trans_details.stock_id
+ AND item.stock_id=line.stock_id
ORDER BY id";
return db_query($sql, "The debtor transaction detail could not be queried");
}
function recurrent_invoice_count($id)
{
- $sql1 = "SELECT branch.* FROM ".TB_PREF."recurrent_invoices rec
- LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.group_no AND NOT branch.inactive
+ $sql1 = "SELECT branch.*
+ FROM ".TB_PREF."recurrent_invoices rec
+ LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.group_no AND NOT branch.inactive
LEFT JOIN ".TB_PREF."debtors_master debtor ON debtor.debtor_no = branch.debtor_no AND NOT debtor.inactive
WHERE rec.debtor_no=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id);
- $sql2 = "SELECT branch.* FROM ".TB_PREF."recurrent_invoices rec
- LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.branch_code AND NOT branch.inactive
+ $sql2 = "SELECT branch.*
+ FROM ".TB_PREF."recurrent_invoices rec
+ LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.branch_code AND NOT branch.inactive
LEFT JOIN ".TB_PREF."debtors_master debtor ON rec.debtor_no = debtor.debtor_no AND NOT debtor.inactive
WHERE rec.debtor_no!=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id);
$errors = 0;
$inv = get_recurrent_invoice($id);
- $sql1 = "SELECT debtor.curr_code FROM ".TB_PREF."recurrent_invoices rec
- LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.group_no AND NOT branch.inactive
+ $sql1 = "SELECT debtor.curr_code
+ FROM ".TB_PREF."recurrent_invoices rec
+ LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.group_no AND NOT branch.inactive
LEFT JOIN ".TB_PREF."debtors_master debtor ON debtor.debtor_no = branch.debtor_no AND NOT debtor.inactive
WHERE rec.debtor_no=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id);
- $sql2 = "SELECT debtor.curr_code FROM ".TB_PREF."recurrent_invoices rec
- LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.branch_code AND NOT branch.inactive
+ $sql2 = "SELECT debtor.curr_code
+ FROM ".TB_PREF."recurrent_invoices rec
+ LEFT JOIN ".TB_PREF."cust_branch branch ON rec.group_no = branch.branch_code AND NOT branch.inactive
LEFT JOIN ".TB_PREF."debtors_master debtor ON rec.debtor_no = debtor.debtor_no AND NOT debtor.inactive
WHERE rec.debtor_no!=0 AND !ISNULL(debtor.debtor_no) AND rec.id=".db_escape($id);
projects / fa-stable.git / commitdiff