require "settings.php";
require "core-settings.php";
require "libs/ext.lib.php";
if (isset($_GET) && isset($_POST)) {
array_merge($_POST, $_GET);
}
if (isset($_POST["key"])) {
switch ($_POST["key"]) {
case "cconfirm":
$OUTPUT = cconfirm($_POST);
break;
case "cprewrite":
$OUTPUT = cprewrite();
break;
case "cwrite":
$OUTPUT = cwrite($_POST);
break;
default:
if (isset($_GET["invid"])) {
$OUTPUT = cdetails($_GET);
} else {
$OUTPUT = "<li class=err>Invalid use of module.";
}
}
} else {
if (isset($_GET["invid"])) {
$OUTPUT = cdetails($_GET);
} else {
$OUTPUT = "<li class=err>Invalid use of module.";
}
}
function run()
{
extract($_REQUEST);
// Update returns
$sql = "\r\n\tSELECT id, item_id, timestamp, asset_id, qty\r\n\tFROM cubit.hire_trans_returned WHERE processed='f'";
$return_rslt = db_exec($sql) or errDie("Unable to retrieve returned items");
while ($return_data = pg_fetch_array($return_rslt)) {
$sql = "\r\n\t\tSELECT max(id) FROM hire.assets_hired\r\n\t\tWHERE asset_id='{$return_data['asset_id']}' AND return_time IS NULL";
$ahid_rslt = db_exec($sql) or errDie("Unable to retrieve last rental.");
$ahid = pg_fetch_result($ahid_rslt, 0);
if (!empty($ahid)) {
$sql = "\r\n\t\t\tUPDATE hire.assets_hired SET return_time='{$return_data['timestamp']}',\r\n\t\t\t\treturned_qty='{$return_data['qty']}'\r\n\t\t\tWHERE id='{$ahid}'";
db_exec($sql) or errDie("Unable to update returns.");
}
$sql = "DELETE FROM hire.hire_invitems WHERE asset_id='{$return_data['asset_id']}'";
db_exec($sql) or errDie("Unable to remove item.");
$sql = "\r\n\t\tUPDATE cubit.hire_trans_returned SET processed='t'\r\n\t\tWHERE id='{$return_data['id']}'";
db_exec($sql) or errDie("Unable to update processed");
}
$sql = "\r\n\tSELECT id, customers.cusnum, customers.surname, customers.paddr1,\r\n\t\tcustomers.vatnum, customers.bankid, customers.accno, customers.addr1,\r\n\t\tcustomers.tel, order_num, timestamp, discount, discount_perc, user_id,\r\n\t\tsubtotal, vat,\ttotal, deposit, delivery, departments.deptname,\r\n\t\tdepartments.debtacc, users.username\r\n\tFROM cubit.hire_trans\r\n\t\tLEFT JOIN cubit.customers ON hire_trans.cusnum=customers.cusnum\r\n\t\tLEFT JOIN exten.departments ON customers.deptid=departments.deptid\r\n\t\tLEFT JOIN cubit.users ON hire_trans.user_id=users.userid\r\n\tWHERE done='t' AND user_id='{$user_id}' AND customers.cusnum > 0 AND processed='0'";
$trans_rslt = db_exec($sql) or errDie("Unable to retrieve transactions.");
$hire_nums = array();
while ($trans_data = pg_fetch_array($trans_rslt)) {
$deptid = 2;
$time = strtotime($trans_data["timestamp"]);
$sql = "SELECT deptname FROM exten.departments WHERE deptid='{$deptid}'";
$deptname_rslt = db_exec($sql) or errDie("Unable to retrieve department");
$deptname = pg_fetch_result($deptname_rslt, 0);
// Create hire note
$sql = "\r\n\t\tINSERT INTO hire.hire_invoices (deptid, cusnum, cordno, ordno,\r\n\t\t\tchrgvat, terms, salespn, odate, printed, comm, done, username,\r\n\t\t\tdeptname, cusacc, cusname, surname, cusaddr, cusordno, cusvatno,\r\n\t\t\tprd, invnum, div, prints, disc, discp, delchrg, subtot, traddisc,\r\n\t\t\tbalance, vat, total, discount, delivery, nbal, rdelchrg, serd,\r\n\t\t\tpcash, pcheque, pcc, rounding, pchange, delvat, pcredit, vatnum,\r\n\t\t\ttelno, systime, deposit_type, deposit_amt, custom_txt, collection,\r\n\t\t\tbranch_addr, timestamp, hire_invid, revision)\r\n\t\tVALUES ('{$deptid}', '{$trans_data['cusnum']}', '', '', 'inc', '0', '2',\r\n\t\t\t'" . date("Y-m-d", $time) . "', 'y', '', 'y', '{$trans_data['username']}',\r\n\t\t\t'{$deptname}', '{$trans_data['accno']}', '', '{$trans_data['surname']}',\r\n\t\t\t'{$trans_data['addr1']}', '{$trans_data['order_num']}',\r\n\t\t\t'{$trans_data['vatnum']}', '" . PRD_DB . "', '{$trans_data['id']}',\r\n\t\t\t'" . USER_DIV . "', '0', '{$trans_data['discount']}',\r\n\t\t\t'{$trans_data['discount_perc']}', '{$trans_data['delivery']}',\r\n\t\t\t'{$trans_data['subtotal']}', '{$trans_data['discount']}', '0.00',\r\n\t\t\t'{$trans_data['vat']}', '{$trans_data['total']}',\r\n\t\t\t'{$trans_data['discount']}', '{$trans_data['delivery']}', '0.00', '0.00',\r\n\t\t\t'', '100', '100', '100', '100', '100', '0', '0.00',\r\n\t\t\t'{$trans_data['vatnum']}', '{$trans_data['tel']}',\r\n\t\t\t'{$trans_data['timestamp']}', 'CSH', '{$trans_data['deposit']}', '',\r\n\t\t\t'Client Collect', '0', current_timestamp, '0', '0')";
db_exec($sql) or errDie("Unable to create hire note.");
$hire_invid = pglib_lastid("hire.hire_invoices", "invid");
$hire_nums[$trans_data["id"]] = $hire_invid;
$sql = "\r\n\t\tSELECT count(id) FROM cubit.hire_trans_items\r\n\t\tWHERE hire_id='{$trans_data['id']}'";
$count_rslt = db_exec($sql) or errDie("Unable to retrieve rental count.");
$count_rental = pg_fetch_result($count_rslt, 0);
$sql = "\r\n\t\tSELECT count(id) FROM cubit.video_stock_items\r\n\t\tWHERE hire_id='{$trans_data['id']}'";
$count_rslt = db_exec($sql) or errDie("Unable to retrieve stock count.");
$count_stock = pg_fetch_result($count_rslt, 0);
$sql = "\r\n\t\tSELECT count(id) FROM cubit.hire_trans_contracts\r\n\t\tWHERE hire_id='{$trans_data['id']}'";
$count_rslt = db_exec($sql) or errDie("Unable to retrieve contract count.");
$count_contract = pg_fetch_result($count_rslt, 0);
$count = $count_rental + $count_stock + $count_contract;
if (empty($count)) {
$sql = "DELETE FROM cubit.hire_trans WHERE id='{$trans_data['id']}'";
db_exec($sql) or errDie("Unable to remove empty transaction.");
continue;
}
pglib_transaction("BEGIN");
// Create invoice
$invnum = divlastid("inv", USER_DIV);
$accid = qryAccountsNum(1050, 00);
$accid = $accid["accid"];
$sql = "\r\n\t\tINSERT INTO cubit.nons_invoices (cusname, cusaddr, cusvatno, chrgvat,\r\n\t\t\tsdate, done, username, prd, invnum, div, cusid, typ, subtot,\r\n\t\t\tbalance, vat, total, ctyp, accid, tval, cordno, odate, salespn,\r\n\t\t\tsystime, bankid, ncdate, cusnum, discount, cash)\r\n\t\tVALUES ('{$trans_data['surname']}', '{$trans_data['paddr1']}',\r\n\t\t\t'{$trans_data['vatnum']}', 'yes', '{$trans_data['timestamp']}', 'n',\r\n\t\t\t'{$user_id}', '" . PRD_DB . "', '{$invnum}', '" . USER_DIV . "',\r\n\t\t\t'{$trans_data['cusnum']}', 'inv', '{$trans_data['subtotal']}',\r\n\t\t\t'{$trans_data['total']}', '{$trans_data['vat']}', '{$trans_data['total']}',\r\n\t\t\t's', '{$accid}', '2', '{$trans_data['order_num']}',\r\n\t\t\t'{$trans_data['timestamp']}', 'General', '{$trans_data['timestamp']}',\r\n\t\t\t'{$trans_data['bankid']}', '{$trans_data['timestamp']}',\r\n\t\t\t'{$trans_data['cusnum']}', '{$trans_data['discount']}' ,\r\n\t\t\t'{$trans_data['total']}')";
db_exec($sql) or errDie("Unable to create new non stock invoice.");
// Retrieve invoice id
$invid = lastinvid();
// Retrieve individual items
$sql = "\r\n\t\tSELECT hire_trans_items.id, asset_id, basis, from_date, to_date, qty, total_days, total,\r\n\t\t\tdiscount_perc, assets.des AS asset_name, returned, grpid\r\n\t\tFROM cubit.hire_trans_items\r\n\t\t\tLEFT JOIN cubit.assets ON hire_trans_items.asset_id=assets.id\r\n\t\tWHERE hire_id='{$trans_data['id']}'";
$items_rslt = db_exec($sql) or errDie("Unable to retrieve items.");
$stkaccs = array();
while ($items_data = pg_fetch_array($items_rslt)) {
$description = "{$items_data['asset_name']} Rented Out";
$unitcost = $items_data["total"] / $items_data["qty"];
// Add to hire note
$sql = "\r\n\t\t\tINSERT INTO hire.hire_invitems (invid, qty, amt, unitcost,\r\n\t\t\t\tfrom_date, to_date, asset_id, basis, hours, weeks, days,\r\n\t\t\t\tmonths, half_day, total_days)\r\n\t\t\tVALUES ('{$hire_invid}', '{$items_data['qty']}', '{$items_data['total']}',\r\n\t\t\t\t'{$unitcost}', '{$items_data['from_date']}', '{$items_data['to_date']}',\r\n\t\t\t\t'{$items_data['asset_id']}', '{$items_data['basis']}', '0',\r\n\t\t\t\t'0', '{$items_data['total_days']}', '0', '0', '{$items_data['total_days']}')";
db_exec($sql) or errDie("Unable to create rental items.");
$item_id = pglib_lastid("hire.hire_invitems", "id");
// Add to assets hired
$sql = "\r\n\t\t\tINSERT INTO hire.assets_hired (invid, asset_id, qty, hired_time,\r\n\t\t\t\tcust_id, item_id, invnum, value, basis, discount, weekends)\r\n\t\t\tVALUES ('{$hire_invid}', '{$items_data['asset_id']}', '{$items_data['qty']}',\r\n\t\t\t\t'{$trans_data['timestamp']}', '{$trans_data['cusnum']}', '{$item_id}',\r\n\t\t\t\t'{$trans_data['id']}', '{$items_data['total']}', '{$items_data['basis']}',\r\n\t\t\t\t'0.00', '0')";
db_exec($sql) or errDie("Unable to add to assets hired.");
// Add to non stock invoice
$sql = "\r\n\t\t\tINSERT INTO cubit.nons_inv_items (invid, qty, description, div,\r\n\t\t\t\tamt, unitcost, accid, vatex)\r\n\t\t\tVALUES ('{$invid}', '{$items_data['qty']}', '{$description}',\r\n\t\t\t\t'" . USER_DIV . "', '{$items_data['total']}', '{$unitcost}', '{$accid}',\r\n\t\t\t\t'2')";
db_exec($sql) or errDie("Unable to create non stock item.");
$id = pglib_lastid("cubit.nons_inv_items", "id");
$stkaccs[$id] = $accid;
$sql = "\r\n\t\t\tSELECT {$items_data['basis']} FROM hire.basis_prices\r\n\t\t\tWHERE assetid='{$items_data['asset_id']}'";
$rate_rslt = db_exec($sql) or errDie("Unable to retrieve rate.");
$rate = pg_fetch_result($rate_rslt, 0);
$rate = empty($rate) ? 0.0 : $rate;
$sql = "\r\n\t\t\tSELECT serial, des FROM cubit.assets\r\n\t\t\tWHERE id='{$items_data['asset_id']}'";
$asset_rslt = db_exec($sql) or errDie("Unable to retrieve assets.");
$asset_data = pg_fetch_array($asset_rslt);
$sql = "\r\n\t\t\tINSERT INTO hire.hire_nons_inv_items (invid, qty, description, div,\r\n\t\t\t\tamt, unitcost, accid, vatex, cunitcost, asset_id, item_id,\r\n\t\t\t\thired_days, rate)\r\n\t\t\tVALUES ('{$hire_invid}', '{$items_data['qty']}', '({$asset_data['serial']}) " . "{$asset_data['des']} hired from {$items_data['from_date']} to " . "{$items_data['to_date']}.', '" . USER_DIV . "', '{$items_data['total']}',\r\n\t\t\t\t'{$unitcost}', '{$accid}', '2', '{$unitcost}',\r\n\t\t\t\t'{$items_data['asset_id']}', '{$item_id}', '{$items_data['total_days']}',\r\n\t\t\t\t'{$rate}')";
db_exec($sql) or errDie("Unable to create invoice item.");
// Add up revenue
$sql = "\r\n\t\t\tINSERT INTO hire.revenue (group_id, asset_id, total, discount,\r\n\t\t\t\thire_invnum, inv_invnum, cusname)\r\n\t\t\tVALUES ('{$items_data['grpid']}', '{$items_data['asset_id']}',\r\n\t\t\t\t'{$items_data['total']}', '0', '0',\r\n\t\t\t\t'0', '{$trans_data['surname']}')";
db_exec($sql) or errDie("Unable to update revenue");
// Flag as processed
# $sql = "
# UPDATE cubit.hire_trans_returned SET processed=true
# WHERE item_id='$items_data[id]'";
# db_exec($sql) or errDie("Unable to process returned item.");
}
$sql = "\r\n\t\tSELECT id, stock_id, stkcod, stkdes, qty, unitprice, total, cost_price\r\n\t\tFROM cubit.video_stock_items\r\n\t\t\tLEFT JOIN cubit.stock ON video_stock_items.stock_id=stock.stkid\r\n\t\tWHERE hire_id='{$trans_data['id']}'";
$stock_rslt = db_exec($sql) or errDie("Unable to retrieve stock.");
$cost_prices = array();
while ($stock_data = pg_fetch_array($stock_rslt)) {
$inventory_acc = qryAccountsNum(6350, 00);
$inventory_acc = $inventory_acc["accid"];
$description = "({$stock_data['stkcod']}) {$stock_data['stkdes']} Sold";
$sql = "\r\n\t\t\tINSERT INTO cubit.nons_inv_items (invid, qty, description, div,\r\n\t\t\t\tamt, unitcost, accid, vatex)\r\n\t\t\tVALUES ('{$invid}', '{$stock_data['qty']}', '{$description}',\r\n\t\t\t\t'" . USER_DIV . "', '{$stock_data['total']}', '{$stock_data['unitprice']}',\r\n\t\t\t\t'{$inventory_acc}', '2')";
db_exec($sql) or errDie("Unable to create non stock item.");
$nonsinv_id = pglib_lastid("cubit.nons_inv_items", "id");
$cost_prices[$nonsinv_id] = $stock_data["cost_price"];
// Update stock balance
$sql = "\r\n\t\t\tUPDATE cubit.stock SET units=(units-'{$stock_data['qty']}')\r\n\t\t\tWHERE stkid='{$stock_data['stock_id']}'";
db_exec($sql) or errDie("Unable to update stock balance.");
}
$sql = "\r\n\t\tSELECT id, stock_id, hire_id, qty, unitprice, total, cost_price, hire_trans_contracts.units\r\n\t\tFROM cubit.hire_trans_contracts\r\n\t\t\tLEFT JOIN cubit.stock ON hire_trans_contracts.stock_id=stock.stkid\r\n\t\tWHERE hire_id='{$trans_data['id']}'";
$contract_rslt = db_exec($sql) or errDie("Unable to retrieve contracts.");
while ($contract_data = pg_fetch_array($contract_rslt)) {
$hs_acc = qryAccountsNum(1050, 00);
$hs_acc = $hs_acc["accid"];
$description = "Contract {$stock_data['stkdes']} Sold";
$sql = "\r\n\t\t\tINSERT INTO cubit.nons_inv_items (invid, qty, description, div, amt,\r\n\t\t\t\tunitcost, accid, vatex)\r\n\t\t\tVALUES ('{$invid}', '{$contract_data['qty']}', '{$description}', '" . USER_DIV . "',\r\n\t\t\t\t'{$contract_data['total']}', '{$contract_data['unitprice']}', '{$hs_acc}', '2')";
db_exec($sql) or errDie("Unable to create non stock item.");
// Update stock balance
$sql = "\r\n\t\t\tUPDATE cubit.stock SET units=(units-'{$contract_data['qty']}')\r\n\t\t\tWHERE stkid='{$contract_data['stock_id']}'";
db_exec($sql) or errDie("Unable to update stock balance.");
}
print cwrite(array("invid" => $invid, "ctyp" => "s", "cusnum" => $trans_data["cusnum"], "stkaccs" => $stkaccs, "cost_prices" => $cost_prices));
$sql = "UPDATE cubit.hire_trans SET processed='1' WHERE id='{$trans_data['id']}'";
db_exec($sql) or errDie("Unable to update transaction item.");
#$sql = "DELETE FROM cubit.hire_trans WHERE id='$trans_data[id]'";
#db_exec($sql) or errDie("Unable to remove transaction item.");
}
pglib_transaction("COMMIT");
return enter();
}
// Navigation logic
if (isset($_REQUEST["button"])) {
list($button) = array_keys($_REQUEST["button"]);
switch ($button) {
case "update_qty":
$OUTPUT = update_qty();
break;
}
} else {
if (isset($_REQUEST["key"])) {
switch ($_REQUEST["key"]) {
case "cconfirm":
$OUTPUT = cconfirm();
break;
case "cwrite":
$OUTPUT = cwrite();
break;
}
} else {
$OUTPUT = "<li class='err'>Invalid use of module.</li>";
}
}
require "../template.php";
# Customer Confirm
function cconfirm($errors = "")
{
extract($_REQUEST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($invid, "num", 1, 20, "Invalid Invoice number.");
