$orgstreet3 = $org->street3;
$orgcity = $org->city;
$orgstate = $org->state;
$orgcountry_name = $org->country_name;
$orgemail = $org->email;
$orgurl = $org->url;
$orgtel1 = $org->tel_1;
$orgtel2 = $org->tel_2;
$orgfax = $org->fax;
$periodfrom_id = $_POST['periodfrom_id'];
$periodto_id = $_POST['periodto_id'];
$reportlevel = $_POST['reportlevel'];
$showzero = $_POST['showzero'];
$showaccountcode = $_POST['showaccountcode'];
$period = new Period();
if ($period->fetchPeriod($periodfrom_id)) {
$header[1] = $period->period_name;
} else {
$header[1] = "Unknown";
}
if ($period->fetchPeriod($periodto_id)) {
$header[2] = $period->period_name;
} else {
$header[2] = "Unknown";
}
if ($showaccountcode == "on") {
$orderby = "ORDER BY ac.classtype,a.accountcode_full";
} else {
$orderby = "ORDER BY ac.classtype,a.accountcode_full,a.placeholder desc,a.accounts_name";
}
$sql = "SELECT a.accountcode_full,a.accounts_id,a.accounts_name,ac.classtype, a.treelevel,a.placeholder,a.parentaccounts_id\r\n\t\tFROM {$tableaccounts} a \r\n\t\tINNER JOIN {$tableaccountgroup} ag on ag.accountgroup_id=a.accountgroup_id\r\n\t\tINNER JOIN {$tableaccountclass} ac on ag.accountclass_id=ac.accountclass_id\r\n\t\tWHERE ac.classtype in ('5A','6L','7E') and a.treelevel<={$reportlevel}\r\n\t\t{$orderby} ";
public function savePeriod()
{
$this->log->showLog(2, "Access savePeriod");
global $xoopsDB, $saveHandler, $createdby, $timestamp;
$tablename = "sim_period";
$insertCount = $saveHandler->ReturnInsertCount();
$this->log->showLog(3, "Start Insert({$insertCount} records)");
if ($insertCount > 0) {
$arrfield = array("period_name", "period_year", "period_month", "isactive", "defaultlevel", "created", "createdby", "updated", "updatedby");
$arrfieldtype = array('%s', '%d', '%d', '%d', '%d', '%s', '%d', '%s', '%d');
// Yes there are INSERTs to perform...
for ($currentRecord = 0; $currentRecord < $insertCount; $currentRecord++) {
$arrvalue = array($saveHandler->ReturnInsertField($currentRecord, "period_name"), $saveHandler->ReturnInsertField($currentRecord, "period_year"), $saveHandler->ReturnInsertField($currentRecord, "period_month"), $saveHandler->ReturnInsertField($currentRecord, "isactive"), $saveHandler->ReturnInsertField($currentRecord, "defaultlevel"), $timestamp, $createdby, $timestamp, $createdby);
$controlvalue = $saveHandler->ReturnInsertField($currentRecord, "period_name");
$this->InsertRecord($tablename, $arrfield, $arrvalue, $arrfieldtype, $controlvalue, "period_id");
// Now we execute this query
}
}
$updateCount = $saveHandler->ReturnUpdateCount();
$this->log->showLog(3, "Start update({$updateCount} records)");
if ($updateCount > 0) {
$arrfield = array("period_name", "period_year", "period_month", "isactive", "defaultlevel", "updated", "updatedby", "isdeleted");
$arrfieldtype = array('%s', '%d', '%d', '%d', '%d', '%s', '%d', '%d');
// Yes there are UPDATEs to perform...
for ($currentRecord = 0; $currentRecord < $updateCount; $currentRecord++) {
$arrvalue = array($saveHandler->ReturnUpdateField($currentRecord, "period_name"), $saveHandler->ReturnUpdateField($currentRecord, "period_year"), $saveHandler->ReturnUpdateField($currentRecord, "period_month"), $saveHandler->ReturnUpdateField($currentRecord, "isactive"), $saveHandler->ReturnUpdateField($currentRecord, "defaultlevel"), $timestamp, $createdby, $saveHandler->ReturnUpdateField($currentRecord, "isdeleted"));
$controlvalue = $saveHandler->ReturnUpdateField($currentRecord, "period_name");
$this->UpdateRecord($tablename, "period_id", $saveHandler->ReturnUpdateField($currentRecord), $arrfield, $arrvalue, $arrfieldtype, $controlvalue);
}
}
$ispurge = 0;
$deleteCount = $saveHandler->ReturnDeleteCount();
$this->log->showLog(3, "Start delete/purge({$deleteCount} records)");
include "class/Period.inc.php";
$o = new Period();
if ($deleteCount > 0) {
for ($currentRecord = 0; $currentRecord < $deleteCount; $currentRecord++) {
$record_id = $saveHandler->ReturnDeleteField($currentRecord);
$o->fetchPeriod($record_id);
$controlvalue = $o->period_name;
$isdeleted = $o->isdeleted;
if ($o->allowDelete($record_id)) {
$this->DeleteRecord("sim_period", "period_id", $record_id, $controlvalue, $isdeleted);
} else {
$this->failfeedback .= "Cannot delete period: {$controlvalue} <br/>";
}
}
}
//$this->failfeedback.="asdasdpasd<br/>\n";
//$this->failfeedback.="123 3443<br/>\n";
//$this->failfeedback.="234 45656523 234<br/>\n";
if ($this->failfeedback != "") {
$this->failfeedback .= "Warning!<br/>\n" . $this->failfeedback;
}
$saveHandler->setErrorMessage($this->failfeedback);
$saveHandler->CompleteSave();
}
$periodfrom_id = $_GET['periodfrom_id'];
$periodto_id = $_GET['periodto_id'];
$showbatchno = $_GET['showbatchno'];
}
//$periodfrom_id=1;
//$periodto_id=2;
if ($periodfrom_id == 0 && $periodto_id == 0) {
$year = date("Y", time());
$month = date("m", time());
//$datefrom="$year-$month-01" ;
//$dateto ="$year-$month-31" ;
$datefrom = getMonth(date("Ymd", time()), 0);
$dateto = getMonth(date("Ymd", time()), 1);
} else {
//getdatefrom
$period->fetchPeriod($periodfrom_id);
if (strlen($period->period_month) == 1) {
$period->period_month = '0' . $period->period_month;
}
$datefrom = "{$period->period_year}-{$period->period_month}-01";
//getdateto
$period->fetchPeriod($periodto_id);
if (strlen($period->period_month) == 1) {
$period->period_month = '0' . $period->period_month;
}
$dateto = "{$period->period_year}-{$period->period_month}-31";
}
if ($periodfrom_id == 0) {
$datefrom = "0000-01-01";
$pdf->datefrom = "";
} else {
$periodfrom_id = $_POST['periodfrom_id'];
$periodto_id = $_POST['periodto_id'];
$period = new Period();
$arrayperiod = $period->getPeriodArray($periodfrom_id, $periodto_id);
$reportlevel = $_POST['reportlevel'];
$showzero = $_POST['showzero'];
$showaccountcode = $_POST['showaccountcode'];
$m = 0;
$allsubsql = "";
$hiddenctrlstyle = "onclick=alert(this.id) type='hidden'";
echo " <table border='1'>";
EOF;
echo "<tr>";
echo "<td><a onclick='clearAll()' title='Clear all marks'>[X]</a></td></td><td><B><U>Account</U></B></td>";
foreach ($arrayperiod as $id) {
if ($period->fetchPeriod($id)) {
$monthname[$m] = $period->period_name;
$monthid[$m] = $id;
$lastdate[$m] = getLastDayByMonth($period->period_name);
echo "<td style='text-align:right'><b><u>{$period->period_name}\r\n <input {$hiddenctrlstyle} id='period_{$m}' value='{$period->period_name}'></u></b></td>";
$allsubsql .= "case when (SELECT sum(t.amt) FROM {$tablebatch} b\r\n inner join {$tabletransaction} t on t.batch_id=b.batch_id\r\n where t.accounts_id=a.accounts_id and b.batchdate <='" . $lastdate[$m] . "' and b.iscomplete=1 and t.branch_id = {$organization_id}) is null\r\n\t\tthen 0 else (SELECT sum(t.amt) FROM {$tablebatch} b\r\n inner join {$tabletransaction} t on t.batch_id=b.batch_id\r\n where t.accounts_id=a.accounts_id and b.batchdate <='" . $lastdate[$m] . "' and b.iscomplete=1 and t.branch_id = {$organization_id}) end\r\n\t\t as transactionamt" . $m . ",";
}
$m++;
}
echo "</tr>";
$allsubsql = substr_replace($allsubsql, "", -1);
if ($showaccountcode == "on") {
$orderby = "ORDER BY ac.classtype,a.accountcode_full";
} else {
$orderby = "ORDER BY ac.classtype,a.accountcode_full,a.placeholder desc,a.accounts_name";
}
include_once "class/Accounts.php";
include_once "class/BankReconcilation.php";
$period = new Period();
$pdf = new PDF('P', 'mm', 'A4');
$acc = new Accounts();
$pdf->AliasNbPages();
$pdf->SetAutoPageBreak(true, $pdf->pagefooterheight + 1);
$currentbankreconcilation_id = $_GET['bankreconcilation_id'];
$r = new BankReconcilation();
$r->fetchBankReconcilation($currentbankreconcilation_id);
$accounts_id = $r->accounts_id;
$period_id = $r->period_id;
//$periodfrom_id=1;
//$periodto_id=2;
//getdatefrom
$period->fetchPeriod($period_id);
if (strlen($period->period_month) == 1) {
$period->period_month = '0' . $period->period_month;
}
$datefrom = "{$period->period_year}-{$period->period_month}-01";
//getdateto
$dateto = "{$period->period_year}-{$period->period_month}-31";
$pdf->datefrom = $datefrom;
$pdf->dateto = $dateto;
$acc->fetchAccounts($accounts_id);
$pdf->headeraccounts_code = $acc->accountcode_full;
$pdf->headeraccounts_name = $acc->accounts_name;
// order by a.accountcode_full,b.batchdate,b.batchno,t.seqno,t.reference_id desc
//get required data
//
$sql = "SELECT a.batchdate,a.batchno,a.accountcode_full,a.accounts_id,a.accounts_name,a.document_no2,a.amt,\r\n\t\ta.refamt,a.refaccounts_name,a.refaccounts_code,a.batch_id,a.bpartner_name,a.refbpartner_name,a.bpartner_id,\r\n\t\ta.refbpartner_id,a.refaccounts_id,a.bankreconcilation_id\r\n\t\tFROM (\r\n\t\tSELECT b.batchdate,b.batchno,a.accountcode_full,a.accounts_id,a.accounts_name,concat(t.document_no2,t2.document_no2) as document_no2,t2.amt *-1 as amt,\r\n\t\tt.amt as refamt,a2.accounts_name as refaccounts_name,a2.accounts_code as refaccounts_code,b.batch_id,\r\n\t\tbp.bpartner_name,bp2.bpartner_name as refbpartner_name,bp.bpartner_id,bp2.bpartner_id as refbpartner_id,\r\n\t\ta2.accounts_id as refaccounts_id,t.bankreconcilation_id,t.reconciledate\r\n\t\tFROM {$tablebatch} b\r\n\t\tINNER JOIN {$tabletransaction} t on b.batch_id=t.batch_id\r\n\t\tINNER JOIN {$tablebpartner} bp on t.bpartner_id=bp.bpartner_id\r\n\t\tINNER JOIN {$tableaccounts} a on a.accounts_id=t.accounts_id\r\n\t\tINNER JOIN {$tabletransaction} t2 on t.reference_id=t2.trans_id\r\n\t\tINNER JOIN {$tablebpartner} bp2 on t2.bpartner_id=bp2.bpartner_id\r\n\t\tINNER JOIN {$tableaccounts} a2 on a2.accounts_id=t2.accounts_id\r\n\t\tWHERE (b.batchdate < '{$dateto}' OR b.batchdate = '{$dateto}')\r\n\t\t and t.accounts_id={$accounts_id} and b.iscomplete=1\r\n\t\tand t.reference_id>0 and\r\n (t.bankreconcilation_id={$currentbankreconcilation_id}\r\n or t.bankreconcilation_id=0\r\n or (t.bankreconcilation_id>0 and t.reconciledate > '{$r->bankreconcilationdate}') )\r\n\t\tUNION\r\n\t\tSELECT b.batchdate,b.batchno,a2.accountcode_full,a2.accounts_id,a2.accounts_name,concat(t.document_no2,t2.document_no2) as document_no2,t.amt * -1,\r\n\t\tt2.amt as refamt,a.accounts_name as refaccounts_name,a.accounts_code as refaccounts_code,b.batch_id,\r\n\t\tbp2.bpartner_name,bp.bpartner_name as refbpartner_name,bp2.bpartner_id,bp.bpartner_id as refbpartner_id,\r\n\t\ta.accounts_id as refaccounts_id,t2.bankreconcilation_id,t2.reconciledate\r\n\t\tFROM {$tablebatch} b\r\n\t\tINNER JOIN {$tabletransaction} t on b.batch_id=t.batch_id\r\n\t\tINNER JOIN {$tablebpartner} bp on t.bpartner_id=bp.bpartner_id\r\n\t\tINNER JOIN {$tableaccounts} a on a.accounts_id=t.accounts_id\r\n\t\tINNER JOIN {$tabletransaction} t2 on t.reference_id=t2.trans_id\r\n\t\tINNER JOIN {$tablebpartner} bp2 on t2.bpartner_id=bp2.bpartner_id\r\n\t\tINNER JOIN {$tableaccounts} a2 on a2.accounts_id=t2.accounts_id\r\n\t\tWHERE (b.batchdate < '{$dateto}' OR b.batchdate = '{$dateto}')\r\n\t\tand t2.accounts_id={$accounts_id} and b.iscomplete=1\r\n\t\tand t.reference_id>0 and (t2.bankreconcilation_id={$currentbankreconcilation_id}\r\n or t2.bankreconcilation_id=0\r\n or (t2.bankreconcilation_id>0 and t2.reconciledate > '{$r->bankreconcilationdate}') )\r\n\t\t) a where a.accounts_id > 0 and a.batch_id>0 ORDER BY\r\n\t\t case when (a.bankreconcilation_id <> {$currentbankreconcilation_id} and a.reconciledate >'{$r->bankreconcilationdate}')\r\n or a.bankreconcilation_id=0\r\n then 0 else 1 end, a.batchdate,a.batchno";
