$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";