function viewtran($_POST)
{
# Get vars
foreach ($_POST as $key => $value) {
${$key} = $value;
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($prd, "string", 1, 14, "Invalid Period number.");
$v->isOk($accnt, "string", 1, 5, "Invalid Accounts Selection.");
if ($accnt == 'slct') {
if (isset($stkids)) {
foreach ($stkids as $key => $stkid) {
$v->isOk($stkid, "num", 1, 20, "Invalid Stock code.");
}
} else {
return "<li class=err>ERROR : Please select at least one Stock Item.</li>" . slctacc();
}
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class=err>" . $e["msg"];
}
$confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Get the ids
if ($accnt == 'all') {
$stkids = array();
db_connect();
$sql = "SELECT stkid FROM stock WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$stkids[] = $ac['stkid'];
}
} else {
return "<li calss=err> There are no Stock Items yet in Cubit.";
}
}
# Period name
$prdname = prdname($prd);
$hide = "";
$trans = "";
foreach ($stkids as $key => $stkid) {
$stkRs = get("cubit", "*", "stock", "stkid", $stkid);
$stk = pg_fetch_array($stkRs);
# Get balances
$idRs = get($prd, "max(id), min(id)", "stkledger", "yrdb='" . YR_DB . "' AND stkid", $stkid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "qty, (bqty - qty) as bqty, trantype, (balance - csamt) as balance", "stkledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($prd, "balance", "stkledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
/*
if($bal['trantype'] == 'dt'){
$bal['bqty'] = ($bal['bqty'] + $bal['qty']);
}else{
$bal['bqty'] = ($bal['bqty'] - $bal['qty']);
}
*/
} else {
if ($prd != PRD_DB) {
continue;
}
$balRs = get("cubit", "csamt as balance, units as bqty", "stock", "stkid", $stkid);
$bal = pg_fetch_array($balRs);
$cbal['balance'] = 0;
$cbal['bqty'] = 0;
}
$balance = sprint($bal['balance']);
$hide .= "<input type=hidden name=stkids[] value='{$stkid}'>";
$trans .= "<tr><td colspan=5><b>({$stk['stkcod']}) {$stk['stkdes']}</b></td></tr>";
$trans .= "<tr><td><br></td><td>Balance Brought Forward</td><td align=right>{$bal['bqty']}</td><td align=right>{$balance} </td><td align=right>{$balance} </td></tr>";
# --> transactio reding comes here <--- #
$dbal['balance'] = 0;
$dbal['bqty'] = 0;
$tranRs = nget($prd, "*", "stkledger", "yrdb='" . YR_DB . "' AND stkid", $stkid . " ORDER BY id ASC");
while ($tran = pg_fetch_array($tranRs)) {
$dbal['balance'] += $tran['csamt'];
$dbal['bqty'] += $tran['qty'];
# sprinting
$tran['csamt'] = sprint($tran['csamt']);
$tran['balance'] = sprint($tran['balance']);
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$trans .= "<tr><td>{$tran['edate']}</td><td>{$tran['details']}</td><td>{$tran['qty']}</td><td align=right>{$tran['csamt']}</td><td align=right>{$tran['balance']}</td></tr>";
}
$dbal['balance'] = sprint($dbal['balance']);
$trans .= "<tr><td><br></td><td>Total for period {$prdname} to Date :</td><td align=right>{$dbal['bqty']}</td><td align=right>{$dbal['balance']} </td><td align=right>{$dbal['balance']} </td></tr>";
$trans .= "<tr><td colspan=5><br></td></tr>";
}
$sp = " ";
$view = "\n\t<center>\n\t<h3>Inventory Ledger</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=75%>\n\t<tr><th>DATE</th><th>DETAILS</th><th>QTY</th><th>COST AMOUNT</th><th>BALANCE</th></tr>\n\t{$trans}\n\t</table>\n\t<p>";
include "temp.xls.php";
Stream("Ledger", $view);
}
function viewtran($_POST)
{
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($prd, "string", 1, 14, "Invalid Period number.");
$v->isOk($accnt, "string", 1, 5, "Invalid Accounts Selection.");
$v->isOk($year, "string", 1, 10, "Invalid year.");
if ($accnt == 'slct') {
if (isset($stkids)) {
foreach ($stkids as $key => $stkid) {
$v->isOk($stkid, "num", 1, 20, "Invalid Stock code.");
}
} else {
return "<li class='err'>ERROR : Please select at least one Stock Item.</li>" . slctacc();
}
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Get the ids
if ($accnt == 'all') {
$stkids = array();
db_connect();
$sql = "SELECT stkid FROM stock WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$stkids[] = $ac['stkid'];
}
} else {
return "<li calss='err'> There are no Stock Items yet in Cubit.</li>";
}
}
$hide = "";
# Period name
$prds = array();
if ($tprd < $fprd) {
for ($i = $fprd; $i <= 12; ++$i) {
$prds[] = $i;
}
for ($i = 1; $i <= $tprd; ++$i) {
$prds[] = $i;
}
} else {
for ($i = $fprd; $i <= $tprd; ++$i) {
$prds[] = $i;
}
}
$trans = "";
foreach ($stkids as $key => $stkid) {
$stkRs = get("cubit", "*", "stock", "stkid", $stkid);
$stk = pg_fetch_array($stkRs);
$trans .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><h3>{$stk['stkcod']} - {$stk['stkdes']}</h3></td>\n\t\t\t</tr>";
$hide .= "<input type='hidden' name='stkids[]' value='{$stkid}'>";
foreach ($prds as $prd) {
# Period name
$prdname = prdname($prd);
# Get balances
$idRs = get($prd, "max(id), min(id)", "stkledger", "yrdb='{$year}' AND stkid", $stkid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "qty, (bqty - qty) as bqty, trantype, (balance - csamt) as balance", "stkledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($prd, "balance", "stkledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
/*
if($bal['trantype'] == 'dt'){
$bal['bqty'] = ($bal['bqty'] + $bal['qty']);
}else{
$bal['bqty'] = ($bal['bqty'] - $bal['qty']);
}
*/
} else {
if ($prd != PRD_DB) {
continue;
}
$balRs = get("cubit", "csamt as balance, units as bqty", "stock", "stkid", $stkid);
$bal = pg_fetch_array($balRs);
$cbal['balance'] = 0;
$cbal['bqty'] = 0;
}
$balance = sprint($bal['balance']);
$get_yr = "SELECT yrname FROM core.year WHERE yrdb = '{$year}' LIMIT 1";
$run_yr = db_exec($get_yr) or errDie("Unable to get year information.");
if (pg_numrows($run_yr) > 0) {
$yarr = pg_fetch_array($run_yr);
$tmpyear = substr($yarr['yrname'], 1);
} else {
$tmpyear = date("Y") - 1;
}
$get_last_prdmap = "SELECT period FROM core.prdmap WHERE month = '12'";
$run_last_prdmap = db_exec($get_last_prdmap) or errDie("Unable to get last month period");
if (pg_numrows($run_last_prdmap) > 0) {
$lastperiod = pg_fetch_result($run_last_prdmap, 0, 0);
}
$get_current_prd = "SELECT period FROM core.prdmap WHERE month = '{$prd}' LIMIT 1";
$run_current_prd = db_exec($get_current_prd) or errDie("Unable to get period information.");
if (pg_fetch_array($run_current_prd) > 0) {
$currentperiod = pg_fetch_result($run_current_prd, 0, 0);
if ((int) $lastperiod >= (int) $currentperiod) {
$show_year = $tmpyear - 1;
} else {
$show_year = $tmpyear;
}
}
$trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='5'><b>({$stk['stkcod']}) {$stk['stkdes']}</b></td>\n\t\t\t\t</tr>";
$trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td>{$show_year}-{$prd}-01<br></td>\n\t\t\t\t\t<td>Balance Brought Forward</td>\n\t\t\t\t\t<td align='right'>{$bal['bqty']}</td>\n\t\t\t\t\t<td align='right'>{$balance} </td>\n\t\t\t\t\t<td align='right'>{$balance} </td>\n\t\t\t\t</tr>";
# --> transactio reding comes here <--- #
$dbal['balance'] = 0;
$dbal['bqty'] = 0;
$tranRs = nget($prd, "*", "stkledger", "yrdb='{$year}' AND stkid", $stkid . " ORDER BY id ASC");
while ($tran = pg_fetch_array($tranRs)) {
$dbal['balance'] += $tran['csamt'];
$dbal['bqty'] += $tran['qty'];
# sprinting
$tran['csamt'] = sprint($tran['csamt']);
$tran['balance'] = sprint($tran['balance']);
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$trans .= "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td>{$tran['edate']}</td>\n\t\t\t\t\t\t<td>{$tran['details']}</td>\n\t\t\t\t\t\t<td>{$tran['qty']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['csamt']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['balance']}</td>\n\t\t\t\t\t</tr>";
}
$dbal['balance'] = sprint($dbal['balance']);
$trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td><br></td>\n\t\t\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t\t\t<td align='right'>{$dbal['bqty']}</td>\n\t\t\t\t\t<td align='right'>{$dbal['balance']} </td>\n\t\t\t\t\t<td align='right'>{$dbal['balance']} </td>\n\t\t\t\t</tr>";
$trans .= "<tr><td colspan='5'><br></td></tr>";
}
}
$sp = " ";
$view = "\n\t\t<center>\n\t\t<h3>Inventory Ledger</h3>\n\t\t<table " . TMPL_tblDflts . " width='75%'>\n\t\t\t<tr>\n\t\t\t\t<th>DATE</th>\n\t\t\t\t<th>DETAILS</th>\n\t\t\t\t<th>QTY</th>\n\t\t\t\t<th>COST AMOUNT</th>\n\t\t\t\t<th>BALANCE</th>\n\t\t\t</tr>\n\t\t\t{$trans}\n\t\t</table>";
include "temp.xls.php";
Stream("Ledger", $view);
}
function viewtran($_POST, $pure = false)
{
extract($_POST);
require_lib("validate");
$v = new validate();
$v->isOk($fprd, "string", 1, 14, "Invalid from period number.");
$v->isOk($tprd, "string", 1, 14, "Invalid to period number.");
$v->isOk($accnt, "string", 1, 5, "Invalid Accounts Selection.");
if ($accnt == 'slct') {
if (isset($stkids)) {
foreach ($stkids as $key => $stkid) {
$v->isOk($stkid, "num", 1, 20, "Invalid Stock code.");
}
} else {
return "<li class='err'>ERROR : Please select at least one Stock Item.</li>" . slctacc($_POST);
}
}
if ($v->isError()) {
return $v->genErrors();
}
if ($accnt == 'all') {
$stkids = array();
db_connect();
$sql = "SELECT stkid FROM stock WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$stkids[] = $ac['stkid'];
}
} else {
return "<li calss='err'> There are no Stock Items yet in Cubit.</li>";
}
}
# Period name
$prds = array();
if ($tprd < $fprd) {
for ($i = $fprd; $i <= 12; ++$i) {
$prds[] = $i;
}
for ($i = 1; $i <= $tprd; ++$i) {
$prds[] = $i;
}
} else {
for ($i = $fprd; $i <= $tprd; ++$i) {
$prds[] = $i;
}
}
$hide = "";
$sp = " ";
$trans = "";
foreach ($stkids as $key => $stkid) {
$stkRs = get("cubit", "*", "stock", "stkid", $stkid);
$stk = pg_fetch_array($stkRs);
$trans .= "\n\t\t<tr>\n\t\t\t<td colspan='8' align='center'><h3>{$stk['stkcod']} - {$stk['stkdes']}</h3></td>\n\t\t</tr>";
$hide .= "<input type='hidden' name='stkids[]' value='{$stkid}'>";
foreach ($prds as $prd) {
$prdname = getMonthName($prd);
$trans .= "\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<th colspan='8'>{$prdname}</th>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<th>DATE</th>\n\t\t\t\t\t\t\t\t<th>DETAILS</th>\n\t\t\t\t\t\t\t\t<th>QTY</th>\n\t\t\t\t\t\t\t\t<th>COST AMOUNT</th>\n\t\t\t\t\t\t\t\t<th>BALANCE</th>\n\t\t\t\t\t\t\t</tr>";
$idRs = get($prd, "max(id), min(id)", "stkledger", "yrdb='" . YR_DB . "' AND stkid", $stkid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "qty, (bqty - qty) as bqty, trantype, (balance - csamt) as balance", "stkledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($prd, "balance", "stkledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
/*
if($bal['trantype'] == 'dt'){
$bal['bqty'] = ($bal['bqty'] + $bal['qty']);
}else{
$bal['bqty'] = ($bal['bqty'] - $bal['qty']);
}
*/
} else {
$balRs = get("cubit", "csamt as balance, units as bqty", "stock", "stkid", $stkid);
$bal = pg_fetch_array($balRs);
$cbal['balance'] = 0;
$cbal['bqty'] = 0;
}
$balance = sprint($bal['balance']);
$trans .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td colspan='5'><b>({$stk['stkcod']}) {$stk['stkdes']}</b></td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td> </td>\n\t\t\t\t\t\t\t\t<td>Balance Brought Forward</td>\n\t\t\t\t\t\t\t\t<td align='right'>" . sprint3($bal['bqty']) . "</td>\n\t\t\t\t\t\t\t\t<td> </td>\n\t\t\t\t\t\t\t\t<td align='right'>" . sprint($balance) . "</td>\n\t\t\t\t\t\t\t</tr>";
$dbal['balance'] = 0;
$dbal['bqty'] = 0;
$qtytotal = 0;
$tranRs = nget($prd, "*", "stkledger", "yrdb='" . YR_DB . "' AND stkid", $stkid . " ORDER BY edate,id ASC");
while ($tran = pg_fetch_array($tranRs)) {
$dbal['balance'] += $tran['csamt'];
$dbal['bqty'] = $tran['bqty'];
$qtytotal += $tran['qty'];
# sprinting
$tran['csamt'] = sprint($tran['csamt']);
$tran['balance'] = sprint($tran['balance']);
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$balance += $tran["csamt"];
$trans .= "\n\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t\t<td>{$tran['edate']}</td>\n\t\t\t\t\t\t\t\t\t<td>{$tran['details']}</td>\n\t\t\t\t\t\t\t\t\t<td align='right'>" . sprint3($tran['qty']) . "</td>\n\t\t\t\t\t\t\t\t\t<td align='right'>{$tran['csamt']}</td>\n\t\t\t\t\t\t\t\t\t<td align='right'>" . sprint($balance) . "</td>\n\t\t\t\t\t\t\t\t</tr>";
}
$dbal['balance'] = sprint($dbal['balance']);
//$qtytotal was $dbal[bqty]
$trans .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td> </td>\n\t\t\t\t\t\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t\t\t\t\t\t<td align='right'>" . sprint3($qtytotal) . "</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$dbal['balance']}</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$dbal['balance']}</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t" . TBL_BR;
}
}
$view = "";
if (!$pure) {
$view .= "\n\t\t\t\t\t<center>\n\t\t\t\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t\t\t\t<input type='hidden' name='key' value='excel'>\n\t\t\t\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\n\t\t\t\t\t\t<input type='hidden' name='fprd' value='{$fprd}'>\n\t\t\t\t\t\t<input type='hidden' name='tprd' value='{$tprd}'>\n\t\t\t\t\t\t<input type='hidden' name='continue' value='yes'>\n\t\t\t\t\t\t{$hide}\n\t\t\t\t\t\t<h3>Inventory Ledger</h3>";
}
$view .= "\n\t<table " . TMPL_tblDflts . " width=75%>\n\t{$trans}";
if (!$pure) {
$view .= "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</form>";
}
$view .= "\n\t</table>";
if (!$pure) {
$view .= "\n\t\t<p>\n\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t<tr><td><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<th>Quick Links</th>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='index-reports.php'>Financials</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='index-reports-other.php'>Other Reports</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='../main.php'>Main Menu</td>\n\t\t\t</tr>\n\t\t</table>";
}
return $view;
}
