function doBuildup()
{
$sqlSelectTD = "SELECT tdID , propertyID , propertyType, cancelsTDNumber ,\r\n\t\t\t\t\t\tcanceledByTDNumber , taxBeginsWithTheYear ,\r\n\t\t\t\t\t\tceasesWithTheYear\r\n\t\t\t\t\t\tFROM TD\r\n\t\t\t\t\t\tORDER BY tdID ASC";
$db1 = new DB_RPTS($sqlSelectTD);
$db2 = new DB_RPTS();
$ctr = 0;
while ($db1->next_record()) {
$td = $db1->Record;
$sqlProperty = sprintf("select assessedValue from %s where propertyID = '%s'", $td['propertyType'], $td['tdID']);
printf("{$sqlProperty}<br>\n");
$db2->query($sqlProperty);
if ($db2->next_record()) {
$td['assessedValue'] = $db2->f("assessedValue");
printf("Assessed Value is %s<br>\n", $td['assessedValue']);
if ($td['canceledByTDNumber'] == NULL) {
$today = getdate();
$td['ceasesWithTheYear'] = $today['year'];
}
for ($i = $td['taxBeginsWithTheYear']; $i <= $td['ceasesWithTheYear']; $i++) {
printf("Making due for td#%s for year %s<br>\n", $td['tdID'], $i);
new Dues($td['tdID'], $i, $td['assessedValue']);
printf("done<br>\n");
$ctr++;
}
}
}
printf("there should be {$ctr} records<br>\n");
}
function getActualUses($var, &$tpl)
{
if ($var == "") {
return;
}
$db = new DB_RPTS();
$sql = "SELECT * FROM " . $var . "ActualUses ORDER BY " . $var . "ActualUses.description ASC ";
$tpl->set_block(report, ACTUALUSE, aBlk);
$db->query($sql);
$firstChar = strtolower(substr($var, 0, 1));
$restOfTheString = substr($var, 1);
$keyVar = $firstChar . $restOfTheString . "ActualUsesID";
while ($db->next_record()) {
foreach ($db->Record as $key => $value) {
switch ($key) {
case "description":
$tpl->set_var(actualUseDesc, $value);
break;
case "code":
$tpl->set_var(actualUseCode, $value);
break;
case $keyVar:
$tpl->set_var(actualUseID, $value);
break;
default:
break;
}
//echo "$key=>$value <br>";
}
$tpl->parse(aBlk, ACTUALUSE, true);
}
}
function getOwnerName($ownerID)
{
$db = new DB_RPTS();
// person
$sql = "SELECT " . "Person.firstName as firstName " . ",Person.middleName as middleName " . ", Person.lastName as lastName " . "FROM" . " " . PERSON_TABLE . ", " . OWNER_PERSON_TABLE . " WHERE" . " " . PERSON_TABLE . ".personID = " . OWNER_PERSON_TABLE . ".personID" . " AND" . " " . OWNER_PERSON_TABLE . ".ownerID = " . $ownerID;
$db->query($sql);
while ($db->next_record()) {
if ($db->f("middleName") != "") {
$middleInitial = substr($db->f("middleName"), 0, 1) . ".";
$fullName = $db->f("firstName") . " " . $middleInitial . " " . $db->f("lastName");
} else {
$fullName = $db->f("firstName") . " " . $db->f("lastName");
}
$ownerNamesArray[] = $fullName;
}
unset($db);
$db = new DB_RPTS();
// company
$sql = "SELECT " . "Company.companyName " . "FROM" . " " . COMPANY_TABLE . ", " . OWNER_COMPANY_TABLE . " WHERE" . " " . COMPANY_TABLE . ".companyID = " . OWNER_COMPANY_TABLE . ".companyID" . " AND" . " " . OWNER_COMPANY_TABLE . ".ownerID = " . $ownerID;
$db->query($sql);
while ($db->next_record()) {
$ownerNamesArray[] = $db->f("companyName");
}
if (is_array($ownerNamesArray)) {
sort($ownerNamesArray);
reset($ownerNamesArray);
return implode(", ", $ownerNamesArray);
} else {
return false;
}
}
function checkTD($tdNum = 0, &$xmlNode)
{
printf("checkTD(): Starting<br>\n");
// Get the TD Information
$sqlSelectTD = "SELECT tdID, cancelsTDNumber, taxBeginsWithTheYear, ceasesWithTheYear\n\t\t FROM TD\n\t\t WHERE taxDeclarationNumber = '{$tdNum}'";
printf("checkTD(): {$sqlSelectTD}<br>\n");
$dbTD = new DB_RPTS($sqlSelectTD);
if ($dbTD->next_record()) {
$tdID = $dbTD->f("tdID");
$cancelsTD = $dbTD->f("cancelsTDNumber");
$taxBegins = $dbTD->f("taxBeginsWithTheYear");
$taxEnds = $dbTD->f("ceasesWithTheYear");
if ($taxEnds == 0) {
// TD is current and has no ending
$today = getdate();
$taxEnds = $today['year'];
}
// check if the TD still has unpaid dues
$unpaidSinceFirst = false;
$sqlSelectDues = "SELECT Year(dueDate) as dueYear from dues\n\t\t\t\twhere tdID = '{$tdID}' and paidInFull = 0\n\t\t\t\torder by dueYear";
printf("checkTD(): {$sqlSelectDues}<br>\n");
$dbDues = new DB_RPTS($sqlSelectDues);
if ($dbDues->num_rows() > 0) {
// prepare a new node for this TD
$node = $this->xmlSOA->create_element("TD");
$node->set_attribute("Number", $tdNum);
// attach node to xmlNode
printf("checkTD(): Attaching a TDdues node<br>\n");
$tdNode = $xmlNode->append_child($node);
} else {
printf("checkTD(): found no dues<br>\n");
}
// get each unpaid due
while ($dbDues->next_record()) {
$dueYear = $dbDues->f('dueYear');
$due = new Dues($tdID, $dueYear);
// attach to TD Node
$dueNode = $this->xmlSOA->create_element("TaxDues");
$dueNode->set_attribute("Year", $dueYear);
$dueNode->set_attribute("Amount", $due->getTotalDue());
if ($due->getPctPenalty() > 0.0) {
$dueNode->set_attribute("PenaltyPerCent", $due->getPctPenalty());
}
$newnode = $tdNode->append_child($dueNode);
printf("checkTD(): Attaching a dues node<br>\n");
if ($dueYear == $taxBegins) {
$unpaidSinceFirst = true;
}
}
// if first year dues are unpaid
// check cancelled TD
if ($unpaidSinceFirst) {
$this->checkTD($cancelsTD, $tdNode);
}
}
printf("checkTD(): Done<br>\n");
}
function getEndingYear()
{
$sql = "SELECT * FROM " . DUE_TABLE . " ORDER BY dueDate DESC LIMIT 1;";
$db = new DB_RPTS();
$db->query($sql);
if ($db->next_record()) {
$dueDate = $db->f("dueDate");
$endingYear = date("Y", strtotime($dueDate));
return $endingYear;
}
return false;
}
function setMunicipalityList()
{
$db = new DB_RPTS();
$db->query("SELECT * FROM MunicipalityCity order by description");
$this->tpl->set_block("rptsTemplate", "MUNICIPALITY", "municipalityBlk");
for ($i = 0; $db->next_record(); $i++) {
foreach ($db->Record as $k => $v) {
$this->tpl->set_var("mun_{$k}", $v);
}
$this->tpl->parse(municipalityBlk, MUNICIPALITY, true);
}
}
function Main()
{
$db = new DB_RPTS();
//$this->formArray['month']= 10; //ex Month january
//$this->formArray['year']= 2003; //ex Month january
/*$sql = "SELECT DISTINCT payments.dueID FROM payments INNER JOIN dues ON payments.dueID = dues.dueID
WHERE MONTH( dues.dueDate ) = '01'";
*/
$sql = "SELECT sum( amount ) as amount , YEAR(dues.dueDate) as yearDue, sum( payments.penalty ) as penalty , sum( discount ) as discount, \r\n\t\t\tcollectionDate FROM collections INNER JOIN collectionPayments ON collections.collectionID = collectionPayments.collectionID INNER JOIN payments \r\n\t\t\tON collectionPayments.paymentID = payments.paymentID INNER JOIN dues ON payments.dueID = dues.dueID where \r\n\t\t\tMONTH(collectionDate) = '" . $this->formArray['month'] . "' AND YEAR(collectionDate) = '" . $this->formArray['year'] . "' GROUP BY ( collectionDate ) order by collectionDate desc;";
$ypos = 460;
$this->tpl->set_block("rptsTemplate", "ROW", "rBlk");
$db->query($sql);
if (count($db->num_rows()) > 0) {
for ($i = 0; $db->next_record(); $i++) {
if ($db->f("yearDue") == date("Y")) {
$this->tpl->set_var(currentYear, $db->f("amount"));
$this->tpl->set_var(prevYear, "0.00");
} else {
$this->tpl->set_var(prevYear, $db->f("amount"));
$this->tpl->set_var(currentYear, "0.00");
}
$this->tpl->set_var($db->Record);
$this->tpl->set_var(ypos, $ypos);
$ypos = $ypos - 10;
$this->tpl->parse("rBlk", "ROW", true);
}
}
$this->tpl->parse("report", "rptsTemplate");
$this->tpl->finish("report");
// $this->tpl->p("report");
$rptrpdf = new PDFWriter();
$rptrpdf->setOutputXML($this->tpl->get('report'), "string");
$rptrpdf->writePDF("collectionReport2.pdf");
}
function doBuildup()
{
$sqlSelectTD = "SELECT tdID , TD.propertyID , cancelsTDNumber ,\r\n\t\t\t\t\t\tcanceledByTDNumber , taxBeginsWithTheYear ,\r\n\t\t\t\t\t\tceasesWithTheYear, assessedValue\r\n\t\t\t\t\t\tFROM TD,Property\r\n\t\t\t\t\t\tWHERE TD.propertyID = Property.propertyID\r\n\t\t\t\t\t\tORDER BY tdID ASC";
$db1 = new DB_RPTS($sqlSelectTD);
$ctr = 0;
while ($db1->next_record()) {
$td = $db1->Record;
if ($td['canceledByTDNumber'] == NULL) {
$today = getdate();
$td['ceasesWithTheYear'] = $today['year'];
}
for ($i = $td['taxBeginsWithTheYear']; $i <= $td['ceasesWithTheYear']; $i++) {
printf("Making due for td#%s for year %s<br>\n", $td['tdID'], $i);
new Dues($td['tdID'], $i, $td['assessedValue']);
printf("done<br>\n");
$ctr++;
}
}
printf("there should be {$ctr} records<br>\n");
}
function Main()
{
$eRPTSSettings = new eRPTSSettings();
if ($eRPTSSettings->selectRecord(1)) {
$this->tpl->set_var("lguType", strtoupper($eRPTSSettings->getLguType()));
$this->tpl->set_var("lguName", strtoupper($eRPTSSettings->getLguName()));
}
$dbTD = new DB_RPTS();
$dbBacktaxTD = new DB_RPTS();
$dbPaymentBacktaxTD = new DB_RPTS();
$dbPaymentTD = new DB_RPTS();
$dbDues = new DB_RPTS();
// gather TD's
$sql = "SELECT Due.dueID as dueID, " . "Due.tdID as tdID, " . "Due.dueType as dueType, " . "Due.dueDate as dueDate, " . "Due.basicTax as basicTax, " . "Due.basicTaxRate as basicTaxRate, " . "Due.sefTax as sefTax, " . "Due.sefTaxRate as sefTaxRate, " . "Due.idleTax as idleTax, " . "Due.idleTaxRate as idleTaxRate, " . "TD.afsID as afsID, " . "TD.propertyType as propertyType, " . "TD.taxDeclarationNumber as taxDeclarationNumber, " . "AFS.odID as odID, " . "AFS.arpNumber as arpNumber, " . "AFS.propertyIndexNumber as propertyIndexNumber, " . "AFS.taxability as taxability, " . "AFS.effectivity as effectivity, " . "AFS.totalMarketValue as totalMarketValue, " . "AFS.totalAssessedValue as totalAssessedValue " . "FROM Due, TD, AFS " . "WHERE Due.tdID = TD.tdID " . "AND TD.afsID = AFS.afsID " . "AND AFS.archive != 'true' " . "AND TD.archive != 'true' " . "AND Due.dueType = 'Annual' " . "AND YEAR(Due.dueDate) <= YEAR(NOW()) " . "AND TD.propertyType LIKE '" . $this->formArray["classification"] . "' " . "GROUP BY Due.tdID, YEAR(Due.dueDate) " . "ORDER BY Due.dueDate DESC";
$dbTD->query($sql);
if ($dbTD->nf() > 0) {
// gather BacktaxTD's
while ($dbTD->next_record()) {
$sqlBacktaxTD = "SELECT backtaxTDID, " . "tdID, " . "tdNumber, " . "startYear, " . "endYear, " . "startQuarter, " . "assessedValue, " . "basicRate, " . "sefRate, " . "basicTax, " . "sefTax, " . "idleTax, " . "penalties, " . "paid, " . "balance, " . "total " . "FROM BacktaxTD " . "WHERE tdID='" . $dbTD->f("tdID") . "' " . "ORDER BY startYear ASC ";
$dbBacktaxTD->query($sqlBacktaxTD);
if ($dbBacktaxTD->nf() > 0) {
while ($dbBacktaxTD->next_record()) {
$backtaxTDRecordArray[] = $dbBacktaxTD->Record;
}
}
$tdRecordArray[] = array("td" => $dbTD->Record, "backtaxTD" => $backtaxTDRecordArray);
unset($backtaxTDRecordArray);
}
}
if (is_array($tdRecordArray)) {
// verify whether each TD Record or BacktaxTD Record is delinquent by comparing it to Payment
// if it is not in Payment it is considered delinquent
// if amountPaid is insufficient to amountDue, it is considered delinquent
foreach ($tdRecordArray as $recordArray) {
$basicAmountDelinquent = 0;
$sefAmountDelinquent = 0;
$penalty = 0;
$totalDelinquent = 0;
// find delinquent backtaxTD's
if (is_array($recordArray["backtaxTD"])) {
foreach ($recordArray["backtaxTD"] as $backtaxTDRecord) {
$basicAmountDelinquent = 0;
$sefAmountDelinquent = 0;
$penalty = 0;
$totalDelinquent = 0;
$sqlPaymentBacktaxTD = "SELECT paymentID, " . "dueType, " . "backtaxTDID, " . "taxDue, " . "earlyPaymentDiscount, " . "advancedPaymentDiscount, " . "penalty, " . "amnesty, " . "balanceDue, " . "amountPaid, " . "dueDate, " . "paymentDate " . "FROM Payment " . "WHERE backtaxTDID='" . $backtaxTDRecord["backtaxTDID"] . "' " . "AND status!='cancelled'";
$dbPaymentBacktaxTD->query($sqlPaymentBacktaxTD);
if ($dbPaymentBacktaxTD->nf() > 0) {
if ($dbPaymentBacktaxTD->next_record()) {
// if amountPaid is less than balanceDue, it is delinquent
if ($dbPaymentBacktaxTD->f("amountPaid") < $dbPaymentBacktaxTD->f("balanceDue")) {
$paidBasic = $backtaxTDRecord["basicRate"] / ($backtaxTDRecord["basicRate"] + $backtaxTDRecord["sefRate"]) * $dbPaymentBacktaxTD->f("amountPaid");
$paidSef = $backtaxTDRecord["sefRate"] / ($backtaxTDRecord["basicRate"] + $backtaxTDRecord["sefRate"]) * $dbPaymentBacktaxTD->f("amountPaid");
$basicAmountDelinquent = $backtaxTDRecord["basicTax"] - $paidBasic;
$sefAmountDelinquent = $backtaxTDRecord["sefTax"] - $paidSef;
$backtaxTD = new BacktaxTD();
$backtaxTD->selectRecord("", $backtaxTDRecord["backtaxTDID"], "");
$backtaxTD->calculatePenalty(date("Y-m-d"));
$penalty = $backtaxTD->getPenalties();
$totalDelinquent = $basicAmountDelinquent + $sefAmountDelinquent + $penalty;
$delinquentRecordArray[] = array("propertyIndexNumber" => "", "taxDeclarationNumber" => $backtaxTDRecord["tdNumber"], "yearDelinquent" => $backtaxTDRecord["startYear"], "basicAmountDelinquent" => $basicAmountDelinquent, "sefAmountDelinquent" => $sefAmountDelinquent, "penalty" => $penalty, "totalDelinquent" => $totalDelinquent);
}
}
} else {
$paidBasic = $backtaxTDRecord["basicRate"] / ($backtaxTDRecord["basicRate"] + $backtaxTDRecord["sefRate"]) * $backtaxTDRecord["paid"];
$paidSef = $backtaxTDRecord["sefRate"] / ($backtaxTDRecord["basicRate"] + $backtaxTDRecord["sefRate"]) * $backtaxTDRecord["paid"];
$basicAmountDelinquent = $backtaxTDRecord["basicTax"] - $paidBasic;
$sefAmountDelinquent = $backtaxTDRecord["sefTax"] - $paidSef;
$backtaxTD = new BacktaxTD();
$backtaxTD->selectRecord("", $backtaxTDRecord["backtaxTDID"], "");
$backtaxTD->calculatePenalty(date("Y-m-d"));
$penalty = $backtaxTD->getPenalties();
$totalDelinquent = $basicAmountDelinquent + $sefAmountDelinquent + $penalty;
$delinquentRecordArray[] = array("propertyIndexNumber" => "", "taxDeclarationNumber" => $backtaxTDRecord["tdNumber"], "yearDelinquent" => $backtaxTDRecord["startYear"], "basicAmountDelinquent" => $basicAmountDelinquent, "sefAmountDelinquent" => $sefAmountDelinquent, "penalty" => $penalty, "totalDelinquent" => $totalDelinquent);
}
}
}
// find delinquent TD's
if (is_array($recordArray["td"])) {
$tdRecord = $recordArray["td"];
$basicAmountDelinquent = 0;
$sefAmountDelinquent = 0;
$penalty = 0;
$totalDelinquent = 0;
$sqlPaymentTD = "SELECT paymentID, " . "dueID, " . "dueType, " . "tdID, " . "taxDue, " . "earlyPaymentDiscount, " . "advancedPaymentDiscount, " . "penalty, " . "amnesty, " . "balanceDue, " . "amountPaid, " . "dueDate, " . "paymentDate " . "FROM Payment " . "WHERE tdID='" . $tdRecord["tdID"] . "' " . "AND status!='cancelled'";
$dbPaymentTD->query($sqlPaymentTD);
if ($dbPaymentTD->nf() > 0) {
while ($dbPaymentTD->next_record()) {
if ($dbPaymentTD->f("dueType") == "Annual") {
if ($dbPaymentTD->f("amountPaid") < $dbPaymentTD->f("balanceDue")) {
$paidBasic = $tdRecord["basicRate"] / ($tdRecord["basicRate"] + $tdRecord["sefRate"]) * $dbPaymentTD->f("amountPaid");
$paidSef = $tdRecord["sefRate"] / ($tdRecord["basicRate"] + $tdRecord["sefRate"]) * $dbPaymentTD->f("amountPaid");
$basicAmountDelinquent = $tdRecord["basicTax"] - $paidBasic;
$sefAmountDelinquent = $tdRecord["sefTax"] - $paidSef;
$due = new Due();
$due->selectRecord($tdRecord["dueID"]);
$due = $this->computePenalty(date("Y-m-d"), $due);
$penalty = $due->getPenalty();
$totalDelinquent = $basicAmountDelinquent + $sefAmountDelinquent + $penalty;
$delinquentRecordArray[] = array("propertyIndexNumber" => $tdRecord["propertyIndexNumber"], "taxDeclarationNumber" => $tdRecord["taxDeclarationNumber"], "yearDelinquent" => date("Y", strtotime($tdRecord["dueDate"])), "basicAmountDelinquent" => $basicAmountDelinquent, "sefAmountDelinquent" => $sefAmountDelinquent, "penalty" => $penalty, "totalDelinquent" => $totalDelinquent);
}
break;
} else {
$tmpPaidQuarterDues[] = $dbPaymentTD->f("dueID");
}
}
if (is_array($tmpPaidQuarterDues)) {
// not all quarters have been paid if less than four
if (count($tmpPaidQuarterDues) < 4) {
foreach ($tmpPaidQuarterDues as $dueID) {
$sqlDueCondition .= " AND Due.dueID!='" . $dueID . "' ";
}
// grab unpaid quarterly dues that have not been paid before quarterly dueDate
$sqlDues = "SELECT Due.dueID as dueID, " . "Due.dueType as dueType, " . "Due.tdID as tdID " . "FROM Due, TD, AFS " . "WHERE TD.afsID = AFS.afsID " . "AND AFS.archive != 'true' " . "AND TD.archive != 'true' " . "AND Due.dueType != 'Annual' " . "AND Due.tdID = '" . $tdRecord["tdID"] . "'" . "AND TD.propertyType LIKE '" . $this->formArray["classification"] . "' " . "AND YEAR(Due.dueDate) <= " . date("Y", strtotime($tdRecord["dueDate"])) . " " . "AND Due.dueDate <= '" . date("Y-m-d") . "' " . $sqlDueCondition . " " . "GROUP BY Due.tdID, Due.dueDate " . "ORDER BY Due.dueDate DESC";
$dbDues->query($sqlDues);
while ($dbDues->next_record()) {
$due = new Due();
$due->selectRecord($dbDues->f("dueID"));
$basicAmountDelinquent += $due->getBasicTax();
$sefAmountDelinquent += $due->getSefTax();
$due = $this->computePenalty(date("Y-m-d"), $due);
$penalty += $due->getPenalty();
}
$totalDelinquent = $basicAmountDelinquent + $sefAmountDelinquent + $penalty;
$delinquentRecordArray[] = array("propertyIndexNumber" => $tdRecord["propertyIndexNumber"], "taxDeclarationNumber" => $tdRecord["taxDeclarationNumber"], "yearDelinquent" => date("Y", strtotime($tdRecord["dueDate"])), "basicAmountDelinquent" => $basicAmountDelinquent, "sefAmountDelinquent" => $sefAmountDelinquent, "penalty" => $penalty, "totalDelinquent" => $totalDelinquent);
}
unset($sqlDueCondition);
unset($tmpPaidQuarterDues);
}
} else {
$basicAmountDelinquent = $tdRecord["basicTax"];
$sefAmountDelinquent = $tdRecord["sefTax"];
$due = new Due();
$due->selectRecord($tdRecord["dueID"]);
$due = $this->computePenalty(date("Y-m-d"), $due);
$penalty = $due->getPenalty();
$totalDelinquent = $basicAmountDelinquent + $sefAmountDelinquent + $penalty;
$delinquentRecordArray[] = array("propertyIndexNumber" => $tdRecord["propertyIndexNumber"], "taxDeclarationNumber" => $tdRecord["taxDeclarationNumber"], "yearDelinquent" => date("Y", strtotime($tdRecord["dueDate"])), "basicAmountDelinquent" => $basicAmountDelinquent, "sefAmountDelinquent" => $sefAmountDelinquent, "penalty" => $penalty, "totalDelinquent" => $totalDelinquent);
}
}
}
}
// print page
if (is_array($delinquentRecordArray)) {
$ypos = 615;
$decrementYposBy = 12;
$linesPerPage = 42;
$count = count($delinquentRecordArray);
$numOfPages = ceil($count / $linesPerPage);
$pageCtr = 1;
$lineCtr = 1;
$recordCtr = 1;
$this->tpl->set_var("classification", $this->formArray["classification"]);
$this->tpl->set_var("numOfPages", $numOfPages);
$this->tpl->set_block("rptsTemplate", "Page", "PageBlock");
$this->tpl->set_block("Page", "Row", "RowBlock");
$this->tpl->set_block("Page", "Totals", "TotalsBlock");
$totalsArray = array("totalBasicAmountDelinquent" => 0, "totalSefAmountDelinquent" => 0, "totalPenalty" => 0, "totalTotalDelinquent" => 0);
foreach ($delinquentRecordArray as $recordArray) {
$this->tpl->set_var("ypos", $ypos);
$this->tpl->set_var("recordCtr", $recordCtr);
// write values
$totalsArray["totalBasicAmountDelinquent"] += un_number_format($recordArray["basicAmountDelinquent"]);
$totalsArray["totalSefAmountDelinquent"] += un_number_format($recordArray["sefAmountDelinquent"]);
$totalsArray["totalPenalty"] += un_number_format($recordArray["penalty"]);
$totalsArray["totalTotalDelinquent"] += un_number_format($recordArray["totalDelinquent"]);
foreach ($recordArray as $key => $value) {
switch ($key) {
case "basicAmountDelinquent":
case "sefAmountDelinquent":
case "penalty":
case "totalDelinquent":
$value = formatCurrency($value);
break;
}
$this->tpl->set_var($key, $value);
}
$this->tpl->parse("RowBlock", "Row", true);
if ($recordCtr == count($delinquentRecordArray) || $lineCtr == $linesPerPage) {
if ($pageCtr == $numOfPages) {
foreach ($totalsArray as $key => $value) {
$value = formatCurrency($value);
$this->tpl->set_var($key, $value);
}
$this->tpl->parse("TotalsBlock", "Totals", true);
} else {
$this->tpl->set_var("TotalsBlock", "");
}
$this->tpl->set_var("page", $pageCtr);
$this->tpl->parse("PageBlock", "Page", true);
$this->tpl->set_var("RowBlock", "");
$this->tpl->set_var("TotalsBlock", "");
$ypos = 615;
$lineCtr = 0;
$pageCtr++;
}
$lineCtr++;
$recordCtr++;
$ypos = $ypos - $decrementYposBy;
}
$this->tpl->parse("templatePage", "rptsTemplate");
$this->tpl->finish("templatePage");
$testpdf = new PDFWriter();
$testpdf->setOutputXML($this->tpl->get("templatePage"), "test");
if (isset($this->formArray["print"])) {
$testpdf->writePDF($name);
//,$this->formArray["print"]);
} else {
$testpdf->writePDF($name);
}
} else {
exit("no delinquent collectible records to display");
}
}
function purge()
{
$rptsDB = new DB_RPTS();
$rptsDB2 = new DB_RPTS();
$sqlselect = "SELECT collectionID FROM collections WHERE receiptNum = '' OR receiptNum is NULL;";
$rptsDB->query($sqlselect);
$due = new Dues();
while ($rptsDB->next_record()) {
$collectionID = $rptsDB->f("collectionID");
$this->collectionID = $collectionID;
$this->isStoredInDatabase = true;
$sqlselect2 = "select payments.paymentID, payments.dueID from payments \n \t\t\t\t\t\tinner join collectionPayments on payments.paymentID=collectionPayments.paymentID \n where collectionPayments.collectionID ={$collectionID}";
#echo("$sqlselect2<br>");
$rptsDB2->query($sqlselect2);
$payment = new Payment();
while ($rptsDB2->next_record()) {
$payment->setPaymentID($rptsDB2->f("paymentID"));
$payment->selectRecord();
// if Payment is BacktaxTD, FLAG BacktaxTD paidStatus as '' for false / UNPAID
if ($strstr = strstr($payment->dueType, ",")) {
$backtaxTDID = strstr($strstr, "=");
$backtaxTDID = str_replace("=", "", $backtaxTDID);
$backtaxTD = new BacktaxTD();
$backtaxTD->selectRecord("", $backtaxTDID);
$backtaxTD->setBacktaxTDID($backtaxTDID);
$backtaxTD->setTotalPaid(0);
$backtaxTD->updatePaidStatus("");
$backtaxTD->updateRecord();
}
$dueID = $rptsDB2->f("dueID");
$due->setDueID($dueID);
$due->select();
$due->resetPayments();
$due->store();
}
if (!$this->deleteRecord()) {
return false;
}
}
return true;
}
function displayOwnerPersonList()
{
// list all Person Owners within limit and alphaFilter who DO NOT have an RPTOP for
// the selected taxableYear
$rptopBatchRecords = new RPTOPBatchRecords();
$db = new DB_RPTS();
if ($this->formArray["alphaFilter"] != "%") {
$condition = "WHERE " . PERSON_TABLE . ".personType NOT LIKE 'propertyAdmin'" . "AND " . PERSON_TABLE . ".lastName LIKE '" . $this->formArray["alphaFilter"] . "%'";
} else {
$notRegExp = "";
foreach ($this->alphaFilterListArray as $alphaFilter) {
if ($alphaFilter != "%") {
if ($notRegExp != "") {
$notRegExp .= "|";
}
$notRegExp .= strtoupper($alphaFilter) . "|" . strtolower($alphaFilter);
}
}
$condition = "WHERE " . PERSON_TABLE . ".personType NOT LIKE 'propertyAdmin'" . "AND " . PERSON_TABLE . ".lastName NOT REGEXP '^" . $notRegExp . "'";
}
$condition .= " ORDER BY " . PERSON_TABLE . ".lastName, " . PERSON_TABLE . ".firstName, " . PERSON_TABLE . ".middleName ASC;";
$sql = "SELECT " . PERSON_TABLE . ".personID as personID, " . PERSON_TABLE . ".lastName as lastName, " . PERSON_TABLE . ".firstName as firstName, " . PERSON_TABLE . ".middleName as middleName " . "FROM " . PERSON_TABLE . " " . $condition;
$db->query($sql);
if (!$db->next_record()) {
$this->tpl->set_var("total", 0);
$this->removeTplBlock("OwnerListColumns");
$this->removeTplBlock("OwnerList");
} else {
$this->removeTplBlock("OwnerListEmpty");
$this->tpl->set_block("rptsTemplate", "OwnerList", "OwnerListBlock");
while ($db->next_record()) {
if ($ownerCtr < $this->formArray["limit"]) {
// check if owner has no other RPTOPs generated for the year
if (!$rptopBatchRecords->getOwnerRPTOPArray($db->f("personID"), "Person", $this->formArray["taxableYear"])) {
// check if owner has TDs for year <-- bottleneck
$tdArray = $rptopBatchRecords->getTDListOf($db->f("personID"), "Person", $this->formArray["taxableYear"]);
if (is_array($tdArray)) {
$ownerCtr++;
if ($db->f("lastName") == "" && $db->f("firstName") == "") {
$ownerName = "BLANK OWNER";
} else {
$ownerName = $db->f("lastName") . ", " . $db->f("firstName") . " " . $db->f("middleName");
}
$this->tpl->set_var("ownerName", $ownerName);
$this->tpl->set_var("personOrCompanyID", $db->f("personID"));
$this->tpl->set_var("i", $ownerCtr);
$this->tpl->parse("OwnerListBlock", "OwnerList", true);
}
}
} else {
break;
}
}
$this->tpl->set_var("total", $ownerCtr);
}
}
function selectRecord($addressID)
{
if ($addressID == "") {
return;
}
//$this->setDB();
$db = new DB_RPTS();
$sql = sprintf("SELECT * FROM %s WHERE addressID=%s;", ADDRESS_TABLE, $addressID);
$db->query($sql);
$company = new Address();
if ($db->next_record()) {
$this->addressID = $db->f("addressID");
$this->number = $db->f("number");
$this->street = $db->f("street");
$this->barangay = $db->f("barangay");
$this->district = $db->f("district");
$this->municipalityCity = $db->f("municipalityCity");
$this->province = $db->f("province");
$this->setDomDocument();
$ret = true;
} else {
$ret = false;
}
return $ret;
}
function Main()
{
$eRPTSSettings = new eRPTSSettings();
if ($eRPTSSettings->selectRecord(1)) {
$this->tpl->set_var("lguType", strtoupper($eRPTSSettings->getLguType()));
$this->tpl->set_var("lguName", strtoupper($eRPTSSettings->getLguName()));
}
$dbTD = new DB_RPTS();
$dbBacktaxTD = new DB_RPTS();
$dbPaymentBacktaxTD = new DB_RPTS();
$dbPaymentTD = new DB_RPTS();
$dbCollectionBacktaxTD = new DB_RPTS();
$dbCollectionTD = new DB_RPTS();
$dbDue = new DB_RPTS();
// gather TD's
$sql = "SELECT Due.dueID as dueID, " . "Due.tdID as tdID, " . "Due.dueType as dueType, " . "Due.dueDate as dueDate, " . "Due.basicTax as basicTax, " . "Due.basicTaxRate as basicTaxRate, " . "Due.sefTax as sefTax, " . "Due.sefTaxRate as sefTaxRate, " . "Due.idleTax as idleTax, " . "Due.idleTaxRate as idleTaxRate, " . "TD.afsID as afsID, " . "TD.propertyType as propertyType, " . "TD.taxDeclarationNumber as taxDeclarationNumber, " . "AFS.odID as odID, " . "AFS.arpNumber as arpNumber, " . "AFS.propertyIndexNumber as propertyIndexNumber, " . "AFS.taxability as taxability, " . "AFS.effectivity as effectivity, " . "AFS.totalMarketValue as totalMarketValue, " . "AFS.totalAssessedValue as totalAssessedValue " . "FROM Due, TD, AFS " . "WHERE Due.tdID = TD.tdID " . "AND TD.afsID = AFS.afsID " . "AND AFS.archive != 'true' " . "AND TD.archive != 'true' " . "AND Due.dueType = 'Annual' " . "AND TD.propertyType LIKE '" . $this->formArray["classification"] . "' " . "GROUP BY Due.tdID, YEAR(Due.dueDate) " . "ORDER BY Due.dueDate DESC";
$dbTD->query($sql);
if ($dbTD->nf() > 0) {
// gather BacktaxTD's
while ($dbTD->next_record()) {
$sqlBacktaxTD = "SELECT backtaxTDID, " . "tdID, " . "tdNumber, " . "startYear, " . "endYear, " . "startQuarter, " . "assessedValue, " . "basicRate, " . "sefRate, " . "basicTax, " . "sefTax, " . "idleTax, " . "penalties, " . "paid, " . "balance, " . "total " . "FROM BacktaxTD " . "WHERE tdID='" . $dbTD->f("tdID") . "' " . "ORDER BY startYear ASC ";
$dbBacktaxTD->query($sqlBacktaxTD);
if ($dbBacktaxTD->nf() > 0) {
while ($dbBacktaxTD->next_record()) {
$backtaxTDRecordArray[] = $dbBacktaxTD->Record;
}
}
$tdRecordArray[] = array("td" => $dbTD->Record, "backtaxTD" => $backtaxTDRecordArray);
unset($backtaxTDRecordArray);
}
}
if (is_array($tdRecordArray)) {
foreach ($tdRecordArray as $recordArray) {
// gather backtaxTD details
if (is_array($recordArray["backtaxTD"])) {
foreach ($recordArray["backtaxTD"] as $backtaxTDRecord) {
$paidBasic = 0;
$paidSEF = 0;
$paidPenalty = 0;
$totalTaxCollected = 0;
$basic = 0;
$sef = 0;
$penalty = 0;
$totalCollectible = 0;
// get totalTaxCollected
$sqlPaymentBacktaxTD = "SELECT paymentID, " . "dueType, " . "backtaxTDID, " . "taxDue, " . "earlyPaymentDiscount, " . "advancedPaymentDiscount, " . "penalty, " . "amnesty, " . "balanceDue, " . "amountPaid, " . "dueDate, " . "paymentDate " . "FROM Payment " . "WHERE backtaxTDID='" . $backtaxTDRecord["backtaxTDID"] . "' " . "AND status!='cancelled'";
$dbPaymentBacktaxTD->query($sqlPaymentBacktaxTD);
if ($dbPaymentBacktaxTD->nf() > 0) {
while ($dbPaymentBacktaxTD->next_record()) {
$sqlCollectionBacktaxTD = "SELECT * " . "FROM Collection " . "WHERE paymentID='" . $dbPaymentBacktaxTD->f("paymentID") . "' " . "AND status!='cancelled' ";
$dbCollectionBacktaxTD->query($sqlCollectionBacktaxTD);
if ($dbCollectionBacktaxTD->nf() > 0) {
while ($dbCollectionBacktaxTD->next_record()) {
switch ($dbCollectionBacktaxTD->f("taxType")) {
case "basic":
if ($dbCollectionBacktaxTD->f("amnesty") != "true") {
$paidBasic += $dbCollectionBacktaxTD->f("amountPaid") - $dbCollectionBacktaxTD->f("penalty");
$paidPenalty += $dbCollectionBacktaxTD->f("penalty");
$totalTaxCollected += $dbCollectionBacktaxTD->f("amountPaid");
} else {
$paidBasic += $dbCollectionBacktaxTD->f("amountPaid");
$totalTaxCollected += $paidBasic;
}
break;
case "sef":
if ($dbCollectionBacktaxTD->f("amnesty") != "true") {
$paidSEF += $dbCollectionBacktaxTD->f("amountPaid") - $dbCollectionBacktaxTD->f("penalty");
$paidPenalty += $dbCollectionBacktaxTD->f("penalty");
$totalTaxCollected += $dbCollectionBacktaxTD->f("amountPaid");
} else {
$paidSEF += $dbCollectionBacktaxTD->f("amountPaid");
$totalTaxCollected += $paidSEF;
}
break;
}
}
}
}
}
// get totalCollectible
$basic = $backtaxTDRecord["basicTax"];
$sef = $backtaxTDRecord["sefTax"];
$backtaxTD = new BacktaxTD();
$backtaxTD->selectRecord("", $backtaxTDRecord["backtaxTDID"], "");
$backtaxTD->calculatePenalty(date("Y-m-d"));
$penalty = $backtaxTD->getPenalties();
$totalCollectible = $basic + $sef + $penalty;
$lineRecordArray[] = array("propertyIndexNumber" => "TD#:" . $backtaxTDRecord["tdNumber"] . " (" . $backtaxTDRecord["startYear"] . ")", "tdNumber" => $backtaxTDRecord["tdNumber"], "paidBasic" => $paidBasic, "paidSEF" => $paidSEF, "paidPenalty" => $paidPenalty, "totalTaxCollected" => $totalTaxCollected, "basic" => $basic, "sef" => $sef, "penalty" => $penalty, "totalCollectible" => $totalCollectible);
}
}
$paidBasic = 0;
$paidSEF = 0;
$paidPenalty = 0;
$totalTaxCollected = 0;
$basic = 0;
$sef = 0;
$penalty = 0;
$totalCollectible = 0;
// gather TD details
if (is_array($recordArray["td"])) {
$tdRecord = $recordArray["td"];
// get totalTaxCollected
$sqlPaymentTD = "SELECT paymentID, " . "dueType, " . "backtaxTDID, " . "taxDue, " . "earlyPaymentDiscount, " . "advancedPaymentDiscount, " . "penalty, " . "amnesty, " . "balanceDue, " . "amountPaid, " . "dueDate, " . "paymentDate " . "FROM Payment " . "WHERE tdID='" . $tdRecord["tdID"] . "' " . "AND status!='cancelled'";
$dbPaymentTD->query($sqlPaymentTD);
if ($dbPaymentTD->nf() > 0) {
while ($dbPaymentTD->next_record()) {
$sqlCollectionTD = "SELECT * " . "FROM Collection " . "WHERE paymentID='" . $dbPaymentTD->f("paymentID") . "' " . "AND status!='cancelled' ";
$dbCollectionTD->query($sqlCollectionTD);
if ($dbCollectionTD->nf() > 0) {
while ($dbCollectionTD->next_record()) {
switch ($dbCollectionTD->f("taxType")) {
case "basic":
if ($dbCollectionTD->f("amnesty") != "true") {
$paidBasic += $dbCollectionTD->f("amountPaid") - $dbCollectionTD->f("penalty");
$paidPenalty += $dbCollectionTD->f("penalty");
$totalTaxCollected += $dbCollectionTD->f("amountPaid");
} else {
$paidBasic += $dbCollectionTD->f("amountPaid");
$totalTaxCollected += $paidBasic;
}
break;
case "sef":
if ($dbCollectionTD->f("amnesty") != "true") {
$paidSEF += $dbCollectionTD->f("amountPaid") - $dbCollectionTD->f("penalty");
$paidPenalty += $dbCollectionTD->f("penalty");
$totalTaxCollected += $dbCollectionTD->f("amountPaid");
} else {
$paidSEF += $dbCollectionTD->f("amountPaid");
$totalTaxCollected += $paidSEF;
}
break;
}
}
}
}
}
// get totalCollectible
$due = new Due();
if ($due->selectRecord($tdRecord["dueID"])) {
$basic = $due->getBasicTax();
$sef = $due->getSefTax();
$due = $this->computePenalty(date("Y-m-d"), $due);
$penalty = $due->getPenalty();
$totalCollectible = $basic + $sef + $penalty;
}
$lineRecordArray[] = array("propertyIndexNumber" => $tdRecord["propertyIndexNumber"], "tdNumber" => $tdRecord["taxDeclarationNumber"], "paidBasic" => $paidBasic, "paidSEF" => $paidSEF, "paidPenalty" => $paidPenalty, "totalTaxCollected" => $totalTaxCollected, "basic" => $basic, "sef" => $sef, "penalty" => $penalty, "totalCollectible" => $totalCollectible);
}
}
}
// print page
if (is_array($lineRecordArray)) {
$ypos = 400;
$decrementYposBy = 12;
$linesPerPage = 20;
$count = count($lineRecordArray);
$numOfPages = ceil($count / $linesPerPage);
$pageCtr = 1;
$lineCtr = 1;
$recordCtr = 1;
$this->tpl->set_var("classification", $this->formArray["classification"]);
$this->tpl->set_var("numOfPages", $numOfPages);
$this->tpl->set_block("rptsTemplate", "Page", "PageBlock");
$this->tpl->set_block("Page", "Row", "RowBlock");
$this->tpl->set_block("Page", "Totals", "TotalsBlock");
$totalsArray = array("totalPaidBasic" => 0, "totalPaidSEF" => 0, "totalPaidPenalty" => 0, "totalTotalTaxCollected" => 0, "totalBasic" => 0, "totalSef" => 0, "totalPenalty" => 0, "totalTotalCollectible" => 0);
foreach ($lineRecordArray as $recordArray) {
$this->tpl->set_var("ypos", $ypos);
$this->tpl->set_var("recordCtr", $recordCtr);
// tally totals
$totalsArray["totalPaidBasic"] += un_number_format($recordArray["paidBasic"]);
$totalsArray["totalPaidSEF"] += un_number_format($recordArray["paidSEF"]);
$totalsArray["totalPaidPenalty"] += un_number_format($recordArray["paidPenalty"]);
$totalsArray["totalTotalTaxCollected"] += un_number_format($recordArray["totalTaxCollected"]);
$totalsArray["totalBasic"] += un_number_format($recordArray["basic"]);
$totalsArray["totalSef"] += un_number_format($recordArray["sef"]);
$totalsArray["totalPenalty"] += un_number_format($recordArray["penalty"]);
$totalsArray["totalTotalCollectible"] += un_number_format($recordArray["totalCollectible"]);
// write values
foreach ($recordArray as $key => $value) {
switch ($key) {
case "paidBasic":
case "paidSEF":
case "paidPenalty":
case "totalTaxCollected":
case "basic":
case "sef":
case "penalty":
case "totalCollectible":
$value = formatCurrency($value);
break;
}
$this->tpl->set_var($key, $value);
}
$this->tpl->parse("RowBlock", "Row", true);
if ($recordCtr == count($lineRecordArray) || $lineCtr == $linesPerPage) {
if ($pageCtr == $numOfPages) {
foreach ($totalsArray as $key => $value) {
$value = formatCurrency($value);
$this->tpl->set_var($key, $value);
}
$this->tpl->parse("TotalsBlock", "Totals", true);
} else {
$this->tpl->set_var("TotalsBlock", "");
}
$this->tpl->set_var("page", $pageCtr);
$this->tpl->parse("PageBlock", "Page", true);
$this->tpl->set_var("RowBlock", "");
$this->tpl->set_var("TotalsBlock", "");
$ypos = 400;
$lineCtr = 0;
$pageCtr++;
}
$lineCtr++;
$recordCtr++;
$ypos = $ypos - $decrementYposBy;
}
$this->tpl->parse("templatePage", "rptsTemplate");
$this->tpl->finish("templatePage");
$testpdf = new PDFWriter();
$testpdf->setOutputXML($this->tpl->get("templatePage"), "test");
if (isset($this->formArray["print"])) {
$testpdf->writePDF($name);
//,$this->formArray["print"]);
} else {
$testpdf->writePDF($name);
}
} else {
exit("no records to display");
}
}
<?
Header('Content-Type: image/png');
include_once("web/prepend.php");
include_once('web/panachart.php');
$db = new DB_RPTS;//DB_SelectLGU("erpts-test");
$tmpdb = new DB_RPTS;//DB_SelectLGU("erpts-test");
$sql = "SELECT SUM( Collection.amountPaid ) AS amountPaid, Payment.paymentID AS paymentID, TD.tdID, description".
" FROM Collection".
" INNER JOIN Payment ON Payment.paymentID = Collection.paymentID".
" INNER JOIN TD".
" USING ( tdID )".
" INNER JOIN AFS".
" USING ( afsID )".
" INNER JOIN Location".
" USING ( odID )".
" INNER JOIN LocationAddress".
" USING ( locationAddressID )".
" INNER JOIN Barangay".
" USING ( barangayID )".
" WHERE Collection.status != 'cancelled' AND Payment.tdID != 0".
" GROUP BY Barangay.barangayID";
//echo $sql;
$db->query($sql);
for($i=0;$db->next_record();$i++){
$amount[$db->f("description")] = $db->f("amountPaid");
//echo $db->f("amountPaid")."<br>";
}
//td=0
function computeTotalNonCash($ldesc, $var)
{
$db = new DB_RPTS();
$sql = "SELECT sum(payments.amount) as paidTotal, sum(payments.penalty) as paidPenalty" . " FROM collections" . " INNER JOIN collectionPayments ON collectionPayments.collectionID = collections.collectionID" . " INNER JOIN payments ON payments.paymentID = collectionPayments.paymentID" . " INNER JOIN dues ON dues.dueID = payments.dueID" . " INNER JOIN TD on dues.tdID = TD.tdID" . " INNER JOIN AFS ON AFS.afsID=TD.afsID" . " INNER JOIN Location ON Location.odID=AFS.odID" . " INNER JOIN LocationAddress ON LocationAddress.locationAddressID=Location.locationAddressID" . " INNER JOIN {$var} on {$var}.afsID=TD.afsID" . " INNER JOIN " . $var . "ActualUses on " . $var . ".actualUse=" . strtolower($var) . "ActualUsesID " . " WHERE " . $var . "ActualUses.description = '{$ldesc}'" . " AND LocationAddress.province=" . $this->formArray['provinceID'] . " AND collections.kindOfPayment <> 'cash'";
$db->query($sql);
switch ($var) {
case "Land":
$this->totalNonCashLand = 0;
$this->totalNonCashLand = $db->f("paidTotal") + $db->f("paidPenalty");
break;
case "ImprovementsBuildings":
$this->totalNonCashImprovementsBuildings = 0;
$this->totalNonCashImprovementsBuildings = $db->f("paidTotal") + $db->f("paidPenalty");
break;
case "Machineries":
$this->totalNonCashMachineries = 0;
$this->totalNonCashMachineries = $db->f("paidTotal") + $db->f("paidPenalty");
break;
case "PlantsTrees":
$this->totalNonCashPlantsTrees = 0;
$this->totalNonCashPlantsTrees = $db->f("paidTotal") + $db->f("paidPenalty");
break;
}
}
function getOwnerNameFromOwnerID($ownerID)
{
$dbPerson = new DB_RPTS();
$dbCompany = new DB_RPTS();
$ownerName = "";
$sqlPerson = "SELECT " . " OwnerPerson.ownerID, " . " Person.personID AS personID, Person.lastName AS lastName, " . " Person.middleName AS middleName, Person.firstName " . " FROM Person, OwnerPerson " . " WHERE Person.personID = OwnerPerson.personID AND OwnerPerson.ownerID ='" . $ownerID . "' " . " GROUP BY Person.personID ";
$sqlCompany = "SELECT " . " OwnerCompany.ownerID, " . " Company.companyID AS companyID, " . " Company.companyName AS companyName " . " FROM Company, OwnerCompany " . " WHERE Company.companyID = OwnerCompany.companyID AND OwnerCompany.ownerID ='" . $ownerID . "' " . " GROUP BY Company.companyID ";
$dbPerson->query($sqlPerson);
while ($dbPerson->next_record()) {
$fullName = $dbPerson->f("firstName");
$fullName .= " ";
if ($dbPerson->f("middleName") != "") {
$fullName .= substr($dbPerson->f("middleName"), 0, 1) . ".";
}
$fullName .= " ";
$fullName .= $dbPerson->f("lastName");
$personNameArray[] = $fullName;
}
$dbCompany->query($sqlCompany);
while ($dbCompany->next_record()) {
$companyName = $dbCompany->f("companyName");
$companyNameArray[] = $company;
}
if (is_array($personNameArray)) {
foreach ($personNameArray as $fullName) {
if ($ownerName != "") {
$ownerName .= ", ";
}
$ownerName .= $fullName;
}
}
if (is_array($companyNameArray)) {
foreach ($companyNameArray as $companyName) {
if ($ownerName != "") {
$ownerName .= ", ";
}
$ownerName .= $companyName;
}
}
return $ownerName;
}
function computeTotalNonCash($ldesc, $var)
{
$db = new DB_RPTS();
$sql = "SELECT sum(payments.amount) as paidTotal, sum(payments.penalty) as paidPenalty" . " FROM collections" . " INNER JOIN collectionPayments ON collectionPayments.collectionID = collections.collectionID" . " INNER JOIN payments ON payments.paymentID = collectionPayments.paymentID" . " INNER JOIN dues ON dues.dueID = payments.dueID" . " INNER JOIN TD on dues.tdID = TD.tdID" . " INNER JOIN {$var} on {$var}.afsID=TD.afsID" . " INNER JOIN " . $var . "ActualUses on " . $var . ".actualUse=" . strtolower($var) . "ActualUsesID " . " WHERE " . $var . "ActualUses.description = '{$ldesc}'" . " AND collections.kindOfPayment <> 'cash' AND collections.municipality=" . $this->formArray['municipalityCityID'] . " AND collections.collectionDate BETWEEN '" . $this->formArray['startDate'] . "' AND '" . $this->formArray['endDate'] . "'";
$db->query($sql);
switch ($var) {
case "Land":
$this->totalNonCashLand = 0;
$this->totalNonCashLand = $db->f("paidTotal") + $db->f("paidPenalty");
break;
case "ImprovementsBuildings":
$this->totalNonCashImprovementsBuildings = 0;
$this->totalNonCashImprovementsBuildings = $db->f("paidTotal") + $db->f("paidPenalty");
break;
case "Machineries":
$this->totalNonCashMachineries = 0;
$this->totalNonCashMachineries = $db->f("paidTotal") + $db->f("paidPenalty");
break;
case "PlantsTrees":
$this->totalNonCashPlantsTrees = 0;
$this->totalNonCashPlantsTrees = $db->f("paidTotal") + $db->f("paidPenalty");
break;
}
}
function create($tdID, $taxableYear)
{
//create a DB object
$rptsDB = new DB_RPTS();
//prepare an SQL select statement
//verbosity is provided as a reference
$sqlselect = "SELECT dueID, basic, penalty, sef, idle, tdID, YEAR(dueDate) as taxableYear,\r\n currentdate,paidBasic,paidSEF,paidPenalty, paidIdle,\r\n paymentMode, paidQuarters, amnesty \r\n FROM dues where tdID = '{$tdID}' and YEAR(dueDate) = '{$taxableYear}'";
//query the database
$queryID = $rptsDB->query($sqlselect);
//assert that there should only be one result
if ($rptsDB->next_record()) {
$this->dueID = $rptsDB->f("dueID");
$this->basic = $rptsDB->f("basic");
$this->penalty = $rptsDB->f("penalty");
$this->sef = $rptsDB->f("sef");
$this->idle = $rptsDB->f("idle");
$this->tdID = $rptsDB->f("tdID");
$this->setDueDate($rptsDB->f("taxableYear"));
$this->updateDate = $rptsDB->f("currentDate");
$this->paidBasic = $rptsDB->f("paidBasic");
$this->paidPenalty = $rptsDB->f("paidPenalty");
$this->paidSEF = $rptsDB->f("paidSEF");
$this->paidIdle = $rptsDB->f("paidIdle");
$this->paymentMode = $rptsDB->f("paymentMode");
$this->paidQuarters = $rptsDB->f("paidQuarters");
$this->amnesty = $rptsDB->f("amnesty");
$this->isStoredInDatabase = true;
$this->computePenalty($this->paymentMode);
// set Discount ON if ff condition is met:
if ($taxableYear == date("Y") && date("n") <= $this->discountPeriod && $this->paymentMode == "Annual") {
$isDiscount = true;
} else {
$isDiscount = false;
}
$this->setIsDiscount($isDiscount);
#$this->setIsDiscount($this->paymentMode=="Annual");
if ($this->getIdleStatus() == 1) {
$this->setIdle($assessedValue);
}
return true;
} else {
// due item was not found to be in the database
//echo("not found");
return false;
}
}
function Main()
{
$eRPTSSettings = new eRPTSSettings();
if ($eRPTSSettings->selectRecord(1)) {
$this->tpl->set_var("lguType", strtoupper($eRPTSSettings->getLguType()));
$this->tpl->set_var("lguName", strtoupper($eRPTSSettings->getLguName()));
}
$dbPayment = new DB_RPTS();
$dbTD = new DB_RPTS();
$dbBacktaxTD = new DB_RPTS();
$dbDue = new DB_RPTS();
$dbPaymentTD = new DB_RPTS();
$dbPaymentBacktaxTD = new DB_RPTS();
$dbCollection = new DB_RPTS();
// gather unique TD's and BacktaxTD's from Payments for year
$sqlPayment = "SELECT paymentID, " . "tdID, " . "backtaxTDID " . "FROM Payment " . "WHERE status!='cancelled' " . "AND YEAR(paymentDate) LIKE '" . $this->formArray["year"] . "' " . "GROUP BY tdID, backtaxTDID " . "ORDER BY paymentID DESC ";
$dbPayment->query($sqlPayment);
if ($dbPayment->nf() > 0) {
while ($dbPayment->next_record()) {
$year = "";
$pageRecord = array("propertyIndexNumber" => "", "tdNumber" => "", "paidBasic" => 0, "paidSef" => 0, "discount" => 0, "basicSef" => 0, "yearDel" => "", "basicDel" => 0, "sefDel" => 0, "penalty" => 0, "totalDel" => 0, "totalAmount" => 0);
if ($dbPayment->f("backtaxTDID") != 0) {
// ------- start of BacktaxTD condition ------------------------- //
// gather BacktaxTD details
$sqlBacktaxTD = "SELECT tdNumber as tdNumber, " . "startYear as year " . "FROM BacktaxTD " . "WHERE " . "backtaxTDID = '" . $dbPayment->f("backtaxTDID") . "' ";
$dbBacktaxTD->query($sqlBacktaxTD);
if ($dbBacktaxTD->next_record()) {
$pageRecord["tdNumber"] = $dbBacktaxTD->f("tdNumber");
$year = $dbBacktaxTD->f("year");
}
// gather Payments for BacktaxTD for this year
$sqlPaymentBacktaxTD = "SELECT * " . "FROM Payment WHERE " . "backtaxTDID='" . $dbPayment->f("backtaxTDID") . "' " . "AND YEAR(paymentDate) LIKE '" . $this->formArray["year"] . "' " . "AND status!='cancelled' " . "ORDER BY paymentID DESC ";
$dbPaymentBacktaxTD->query($sqlPaymentBacktaxTD);
if ($dbPaymentBacktaxTD->nf() > 0) {
while ($dbPaymentBacktaxTD->next_record()) {
// gather Collections for Payment
$sqlCollection = "SELECT * " . "FROM Collection " . "WHERE paymentID='" . $dbPaymentBacktaxTD->f("paymentID") . "' " . "AND Collection.status!='cancelled' " . "ORDER BY collectionID DESC ";
$dbCollection->query($sqlCollection);
if ($dbCollection->nf() > 0) {
while ($dbCollection->next_record()) {
switch ($dbCollection->f("taxType")) {
case "basic":
// paidBasic
$paidBasic = 0;
if ($dbCollection->f("amnesty") != "true") {
$paidBasic = $dbCollection->f("amountPaid") - $dbCollection->f("penalty");
// penalty, basicDel and totalDel
if ($dbCollection->f("penalty") > 0) {
$pageRecord["yearDel"] = $year;
// basicDel
$basicDel = 0;
$basicDel = $dbCollection->f("taxDue");
// penalty
$penalty = 0;
$penalty = $dbCollection->f("penalty");
// totalDel
$totalDel = $basicDel + $penalty;
$pageRecord["basicDel"] += $basicDel;
$pageRecord["penalty"] += $penalty;
$pageRecord["totalDel"] += $totalDel;
// totalAmount
$pageRecord["totalAmount"] += $penalty;
}
}
$paidBasic = $paidBasic + ($dbCollection->f("earlyPaymentDiscount") + $dbCollection->f("advancedPaymentDiscount"));
// discount
$discount = $dbCollection->f("earlyPaymentDiscount") + $dbCollection->f("advancedPaymentDiscount");
// basicSef
$basicSef = $paidBasic - $discount;
$pageRecord["paidBasic"] += $paidBasic;
$pageRecord["discount"] += $discount;
$pageRecord["basicSef"] += $basicSef;
// totalAmount
$pageRecord["totalAmount"] += $basicSef;
break;
case "sef":
// paidSef
$paidSef = 0;
if ($dbCollection->f("amnesty") != "true") {
$paidSef = $dbCollection->f("amountPaid") - $dbCollection->f("penalty");
// penalty, sefDel and totalDel
if ($dbCollection->f("penalty") > 0) {
$pageRecord["yearDel"] = $year;
// sefDel
$sefDel = 0;
$sefDel = $dbCollection->f("taxDue");
// penalty
$penalty = 0;
$penalty = $dbCollection->f("penalty");
// totalDel
$totalDel = $sefDel + $penalty;
$pageRecord["sefDel"] += $sefDel;
$pageRecord["penalty"] += $penalty;
$pageRecord["totalDel"] += $totalDel;
// totalAmount
$pageRecord["totalAmount"] += $penalty;
}
}
$paidSef = $paidSef + ($dbCollection->f("earlyPaymentDiscount") + $dbCollection->f("advancedPaymentDiscount"));
// discount
$discount = $dbCollection->f("earlyPaymentDiscount") + $dbCollection->f("advancedPaymentDiscount");
// basicSef
$basicSef = $paidSef - $discount;
$pageRecord["paidSef"] += $paidSef;
$pageRecord["discount"] += $discount;
$pageRecord["basicSef"] += $basicSef;
// totalAmount
$pageRecord["totalAmount"] += $basicSef;
break;
}
}
}
}
}
// ------- start of BacktaxTD condition ------------------------- //
} else {
if ($dbPayment->f("tdID") != 0) {
// ------- start of TD condition ------------------------- //
// gather TD details
$sqlTD = "SELECT AFS.propertyIndexNumber as propertyIndexNumber, " . "TD.taxDeclarationNumber as taxDeclarationNumber " . "FROM TD, AFS " . "WHERE " . "TD.afsID = AFS.afsID " . "AND TD.tdID='" . $dbPayment->f("tdID") . "' " . "AND TD.archive!='true' " . "AND AFS.archive!='true' ";
$dbTD->query($sqlTD);
if ($dbTD->next_record()) {
$pageRecord["propertyIndexNumber"] = $dbTD->f("propertyIndexNumber");
$pageRecord["tdNumber"] = $dbTD->f("taxDeclarationNumber");
}
// gather dueDate Year from Due
$sqlDue = "SELECT YEAR(dueDate) as year " . "FROM Due " . "WHERE tdID='" . $dbPayment->f("tdID") . "' " . "AND dueType='Annual' ";
$dbDue->query($sqlDue);
if ($dbDue->next_record()) {
$year = $dbDue->f("year");
}
// gather Payments for TD for this year
$sqlPaymentTD = "SELECT * " . "FROM Payment WHERE " . "tdID='" . $dbPayment->f("tdID") . "' " . "AND YEAR(paymentDate) LIKE '" . $this->formArray["year"] . "' " . "AND status!='cancelled' " . "ORDER BY paymentID DESC ";
$dbPaymentTD->query($sqlPaymentTD);
if ($dbPaymentTD->nf() > 0) {
while ($dbPaymentTD->next_record()) {
// gather Collections for Payment
$sqlCollection = "SELECT * " . "FROM Collection " . "WHERE paymentID='" . $dbPaymentTD->f("paymentID") . "' " . "AND Collection.status!='cancelled' " . "ORDER BY collectionID DESC ";
$dbCollection->query($sqlCollection);
if ($dbCollection->nf() > 0) {
while ($dbCollection->next_record()) {
switch ($dbCollection->f("taxType")) {
case "basic":
// paidBasic
$paidBasic = 0;
if ($dbCollection->f("amnesty") != "true") {
$paidBasic = $dbCollection->f("amountPaid") - $dbCollection->f("penalty");
// penalty, basicDel and totalDel
if ($dbCollection->f("penalty") > 0) {
$pageRecord["yearDel"] = $year;
// basicDel
$basicDel = 0;
$basicDel = $dbCollection->f("taxDue");
// penalty
$penalty = 0;
$penalty = $dbCollection->f("penalty");
// totalDel
$totalDel = $basicDel + $penalty;
$pageRecord["basicDel"] += $basicDel;
$pageRecord["penalty"] += $penalty;
$pageRecord["totalDel"] += $totalDel;
// totalAmount
$pageRecord["totalAmount"] += $penalty;
}
}
$paidBasic = $paidBasic + ($dbCollection->f("earlyPaymentDiscount") + $dbCollection->f("advancedPaymentDiscount"));
// discount
$discount = $dbCollection->f("earlyPaymentDiscount") + $dbCollection->f("advancedPaymentDiscount");
// basicSef
$basicSef = $paidBasic - $discount;
$pageRecord["paidBasic"] += $paidBasic;
$pageRecord["discount"] += $discount;
$pageRecord["basicSef"] += $basicSef;
// totalAmount
$pageRecord["totalAmount"] += $basicSef;
break;
case "sef":
// paidSef
$paidSef = 0;
if ($dbCollection->f("amnesty") != "true") {
$paidSef = $dbCollection->f("amountPaid") - $dbCollection->f("penalty");
// penalty, sefDel and totalDel
if ($dbCollection->f("penalty") > 0) {
$pageRecord["yearDel"] = $year;
// sefDel
$sefDel = 0;
$sefDel = $dbCollection->f("taxDue");
// penalty
$penalty = 0;
$penalty = $dbCollection->f("penalty");
// totalDel
$totalDel = $sefDel + $penalty;
$pageRecord["sefDel"] += $sefDel;
$pageRecord["penalty"] += $penalty;
$pageRecord["totalDel"] += $totalDel;
// totalAmount
$pageRecord["totalAmount"] += $penalty;
}
}
$paidSef = $paidSef + ($dbCollection->f("earlyPaymentDiscount") + $dbCollection->f("advancedPaymentDiscount"));
// discount
$discount = $dbCollection->f("earlyPaymentDiscount") + $dbCollection->f("advancedPaymentDiscount");
// basicSef
$basicSef = $paidSef - $discount;
$pageRecord["paidSef"] += $paidSef;
$pageRecord["discount"] += $discount;
$pageRecord["basicSef"] += $basicSef;
// totalAmount
$pageRecord["totalAmount"] += $basicSef;
break;
}
}
}
}
}
// ------- end of TD condition ------------------------- //
}
}
$pageRecordArrayList[] = $pageRecord;
unset($pageRecord);
}
if (is_array($pageRecordArrayList)) {
$ypos = 426;
$decrementYposBy = 12;
$linesPerPage = 24;
$count = count($pageRecordArrayList);
$numOfPages = ceil($count / $linesPerPage);
$pageCtr = 1;
$lineCtr = 1;
$recordCtr = 1;
$this->tpl->set_var("year", $this->formArray["year"]);
$this->tpl->set_var("numOfPages", $numOfPages);
$this->tpl->set_block("rptsTemplate", "Page", "PageBlock");
$this->tpl->set_block("Page", "Row", "RowBlock");
$this->tpl->set_block("Page", "Totals", "TotalsBlock");
$totalsArray = array("totalPaidBasic" => 0, "totalPaidSef" => 0, "totalDiscount" => 0, "totalBasicSef" => 0, "totalBasicDel" => 0, "totalSefDel" => 0, "totalPenalty" => 0, "totalTotalDel" => 0, "totalTotalAmount" => 0);
foreach ($pageRecordArrayList as $recordArray) {
$this->tpl->set_var("ypos", $ypos);
$this->tpl->set_var("recordCtr", $recordCtr);
// write values
$totalsArray["totalPaidBasic"] += un_number_format($recordArray["paidBasic"]);
$totalsArray["totalPaidSef"] += un_number_format($recordArray["paidSef"]);
$totalsArray["totalDiscount"] += un_number_format($recordArray["discount"]);
$totalsArray["totalBasicSef"] += un_number_format($recordArray["basicSef"]);
$totalsArray["totalBasicDel"] += un_number_format($recordArray["basicDel"]);
$totalsArray["totalSefDel"] += un_number_format($recordArray["sefDel"]);
$totalsArray["totalPenalty"] += un_number_format($recordArray["penalty"]);
$totalsArray["totalTotalDel"] += un_number_format($recordArray["totalDel"]);
$totalsArray["totalTotalAmount"] += un_number_format($recordArray["totalAmount"]);
foreach ($recordArray as $key => $value) {
switch ($key) {
case "paidBasic":
case "paidSef":
case "discount":
case "basicSef":
case "basicDel":
case "sefDel":
case "penalty":
case "totalDel":
case "totalAmount":
$value = formatCurrency($value);
break;
}
$this->tpl->set_var($key, $value);
}
$this->tpl->parse("RowBlock", "Row", true);
if ($recordCtr == count($pageRecordArrayList) || $lineCtr == $linesPerPage) {
if ($pageCtr == $numOfPages) {
foreach ($totalsArray as $key => $value) {
$value = formatCurrency($value);
$this->tpl->set_var($key, $value);
}
$this->tpl->parse("TotalsBlock", "Totals", true);
} else {
$this->tpl->set_var("TotalsBlock", "");
}
$this->tpl->set_var("page", $pageCtr);
$this->tpl->parse("PageBlock", "Page", true);
$this->tpl->set_var("RowBlock", "");
$this->tpl->set_var("TotalsBlock", "");
$ypos = 426;
$lineCtr = 0;
$pageCtr++;
}
$lineCtr++;
$recordCtr++;
$ypos = $ypos - $decrementYposBy;
}
$this->tpl->parse("templatePage", "rptsTemplate");
$this->tpl->finish("templatePage");
} else {
exit("no collections to list for " . $this->formArray["year"]);
}
} else {
exit("no collections to list for " . $this->formArray["year"]);
}
$this->tpl->parse("templatePage", "rptsTemplate");
$this->tpl->finish("templatePage");
$testpdf = new PDFWriter();
$testpdf->setOutputXML($this->tpl->get("templatePage"), "test");
if (isset($this->formArray["print"])) {
$testpdf->writePDF($name);
//,$this->formArray["print"]);
} else {
$testpdf->writePDF($name);
}
}
function Main()
{
switch ($this->formArray["formAction"]) {
case "view":
$ODList = new SoapObject(NCCBIZ . "ODList.php", "urn:Object");
// paging
$condition = " WHERE ";
$condition .= " " . OD_TABLE . ".odID=" . AFS_TABLE . ".odID ";
$condition .= " AND " . AFS_TABLE . ".taxability='Taxable' ";
$condition .= $this->filterArchives();
$sqlCount = "SELECT COUNT(" . OD_TABLE . ".odID) as count FROM ";
$sqlCount .= OD_TABLE . ", ";
$sqlCount .= AFS_TABLE . " ";
$sqlCount .= $condition;
$dbCount = new DB_RPTS();
$dbCount->query($sqlCount);
if ($dbCount->next_record()) {
$count = $dbCount->f("count");
$numOfPages = ceil($count / PAGE_BY);
$this->tpl->set_var("currentPage", $this->formArray["page"]);
$this->tpl->set_var("numOfPages", $numOfPages);
$this->tpl->set_block("rptsTemplate", "PageListOne", "PageListOneBlock");
for ($p = 1; $p <= $numOfPages; $p++) {
$this->tpl->set_var("page", $p);
$this->initSelected("page", $p);
$this->tpl->parse("PageListOneBlock", "PageListOne", true);
}
} else {
$this->tpl->set_block("rptsTemplate", "PageNavigationOne", "PageNavigationOneBlock");
$this->tpl->set_var("PageNavigationOneBlock", "");
$count = 0;
}
// listing
$sql = "SELECT " . OD_TABLE . ".odID as odID, ";
$sql .= AFS_TABLE . ".afsID as afsID FROM ";
$sql .= OD_TABLE . ", ";
$sql .= AFS_TABLE . " ";
if ($this->formArray["page"] > 0) {
$initialLimit = ($this->formArray["page"] - 1) * PAGE_BY;
$condition .= " LIMIT " . $initialLimit . "," . PAGE_BY;
}
$sql = $sql . $condition;
$db = new DB_RPTS();
$db->query($sql);
$odRecords = new ODRecords();
while ($db->next_record()) {
$od = new OD();
$od->selectRecord($db->f("odID"));
$odRecords->arrayList[] = $od;
}
if (!is_array($odRecords->getArrayList())) {
$this->tpl->set_block("rptsTemplate", "NotFound", "NotFoundBlock");
$this->tpl->set_var("message", "properties not found");
$this->tpl->parse("NotFoundBlock", "NotFound", true);
$this->tpl->set_block("rptsTemplate", "Report", "ReportBlock");
$this->tpl->set_var("ReportBlock", "");
} else {
$list = $odRecords->getArrayList();
if (count($list)) {
$this->tpl->set_block("rptsTemplate", "NotFound", "NotFoundBlock");
$this->tpl->set_var("NotFoundBlock", "");
$this->tpl->set_block("rptsTemplate", "ReportList", "ReportListBlock");
foreach ($list as $key => $value) {
$afs = $this->getAFSDetails($value->getOdID());
if (is_object($afs)) {
$this->tpl->set_var("taxability", $afs->getTaxability());
if ($afs->getTaxability() == "Taxable") {
//$this->formArray["odID"] = $value->getOdID();
//$this->formArray["afsID"] = $afs->getAfsID();
$td = new TD();
if ($td->selectRecord("", $this->formArray["afsID"])) {
$this->tpl->set_var("taxDeclarationNumber", $td->getTaxDeclarationNumber());
} else {
$this->tpl->set_var("taxDeclarationNumber", "");
}
$locationAddress = $value->locationAddress->getFullAddress();
$this->tpl->set_var("locationAddress", $locationAddress);
$this->tpl->set_var("area", $value->getLandArea());
$this->tpl->set_var("propertyIndexNumber", $afs->getPropertyIndexNumber());
$this->tpl->set_var("marketValue", number_format($afs->getTotalMarketValue()));
$this->tpl->set_var("assessedValue", number_format($afs->getTotalAssessedValue(), 2, '.', ','));
$landList = $afs->getLandArray();
$plantsTreesList = $afs->getPlantsTreesArray();
$improvementsBuildingsList = $afs->getImprovementsBuildingsArray();
$machineriesList = $afs->getMachineriesArray();
$kind = "";
$class = "";
$remarks = "";
if (count($landList)) {
$kind = "Land";
$land = $landList[0];
$class = $land->getClassification();
$landClasses = new LandClasses();
$landClasses->selectRecord($class);
$class = $landClasses->getDescription();
$remarks = $land->getMemoranda();
} else {
if (count($plantsTreesList)) {
$kind = "Land";
$plantsTrees = $plantsTreesList[0];
$class = $plantsTrees->getProductClass();
$plantsTreesClasses = new PlantsTreesClasses();
$plantsTreesClasses->selectRecord($class);
$class = $plantsTreesClasses->getDescription();
$remarks = $plantsTrees->getMemoranda();
} else {
if (count($improvementsBuildingsList)) {
$kind = "Improvements/Buildings";
$improvementsBuildings = $improvementsBuildingsList[0];
$class = $improvementsBuildings->getBuildingClassification();
$improvementsBuildingsClasses = new ImprovementsBuildingsClasses();
$improvementsBuildingsClasses->selectRecord($class);
$class = $improvementsBuildingsClasses->getDescription();
$remarks = $improvementsBuildings->getMemoranda();
} else {
if (count($machineriesList)) {
$kind = "Machineries";
$machineries = $machineriesList[0];
$class = $machineries->getKind();
$remarks = $machineries->getMemoranda();
}
}
}
}
$this->tpl->set_var("kind", $kind);
$this->tpl->set_var("classification", $class);
$this->tpl->set_var("remarks", $remarks);
$oValue = $value->owner;
if (count($oValue->personArray)) {
$firstOwner = $oValue->personArray[0]->getLastName();
$firstOwner .= ", ";
$firstOwner .= $oValue->personArray[0]->getFirstName();
$firstOwner .= " ";
$firstOwner .= substr($oValue->personArray[0]->getMiddleName(), 0, 1) . ".";
$pAddress = $oValue->personArray[0]->addressArray ? $oValue->personArray[0]->addressArray[0]->getFullAddress() : "no address";
$firstOwnerAddress = $pAddress;
$firstOwnerTelephone = $oValue->personArray[0]->getTelephone();
$this->tpl->set_var("firstOwner", $firstOwner);
$this->tpl->set_var("firstOwnerAddress", $firstOwnerAddress);
//$this->tpl->set_var("firstOwnerTelephone", $firstOwnerTelephone);
}
if (count($oValue->companyArray)) {
if ($firstOwner == "") {
$firstOwner = $oValue->companyArray[0]->getCompanyName();
$cAddress = $oValue->companyArray[0]->addressArray ? $oValue->companyArray[0]->addressArray[0]->getFullAddress() : "no \taddress";
$firstOwnerAddress = $cAddress;
$firstOwnerTelephone = $oValue->companyArray[0]->getTelephone();
$this->tpl->set_var("firstOwner", $firstOwner);
$this->tpl->set_var("firstOwnerAddress", $firstOwnerAddress);
//$this->tpl->set_var("firstOwnerTelephone", $firstOwnerTelephone);
}
}
if ($firstOwner != "") {
$this->tpl->set_var("none", "");
if (count($oValue->personArray) + count($oValue->companyArray) > 1) {
$this->tpl->set_var("andOthers", "(and others)");
} else {
$this->tpl->set_var("andOthers", "");
}
} else {
$this->tpl->set_var("none", "none");
$this->tpl->set_var("firstOwner", "");
$this->tpl->set_var("andOthers", "");
$this->tpl->set_var("firstOwnerAddress", "");
$this->tpl->set_var("firstOwnerTelephone", "");
}
$this->tpl->parse("ReportListBlock", "ReportList", true);
}
}
}
}
}
break;
default:
$this->tpl->set_block("rptsTemplate", "NotFound", "NotFoundBlock");
$this->tpl->set_var("message", "select a location to view report");
$this->tpl->parse("NotFoundBlock", "NotFound", true);
$this->tpl->set_block("rptsTemplate", "Report", "ReportBlock");
$this->tpl->set_var("ReportBlock", "");
break;
}
$this->setForm();
$this->tpl->set_var("Session", $this->sess->url(""));
$this->tpl->set_var("rpts_Session", $this->sess->id);
$this->tpl->parse("templatePage", "rptsTemplate");
$this->tpl->finish("templatePage");
$this->tpl->p("templatePage");
}
function getTaxRollArrayList()
{
$db = new DB_RPTS();
$db2 = new DB_RPTS();
$db3 = new DB_RPTS();
// filter rptopID by location
// and sort by owner
$sql = "SELECT Owner.ownerID as ownerID, Owner.rptopID as rptopID" . " FROM Owner" . " WHERE Owner.rptopID!=''";
$db->query($sql);
while ($db->next_record()) {
$sql = "SELECT RPTOPTD.tdID as tdID " . ",TD.taxDeclarationNumber as taxDeclarationNumber " . ",TD.propertyType as propertyType " . ", AFS.afsID as afsID " . ", AFS.totalAssessedValue as totalAssessedValue " . ", Barangay.description as barangay " . "FROM RPTOPTD, TD, AFS, Location, LocationAddress, Barangay " . "WHERE " . "TD.tdID = RPTOPTD.tdID " . "AND TD.afsID = AFS.afsID " . "AND AFS.odID = Location.odID " . "AND LocationAddress.locationAddressID = Location.locationAddressID " . "AND LocationAddress.barangayID = Barangay.barangayID " . "AND Barangay.barangayID = " . $this->formArray["barangayID"] . " " . "AND RPTOPTD.rptopID = " . $db->f("rptopID");
$db2->query($sql);
$i = 0;
while ($db2->next_record()) {
$basicTax = "";
$sefTax = "";
$sql = "SELECT" . " Due.basicTax as basicTax" . ", Due.sefTax as sefTax" . " FROM Due" . " WHERE" . " Due.dueType='Annual'" . " AND Due.tdID=" . $db2->f("tdID");
$db3->query($sql);
while ($db3->next_record()) {
$basicTax += $db3->f("basicTax");
$sefTax += $db3->f("sefTax");
}
if ($basicTax != "" && $sefTax != "") {
$ownerName = $this->getOwnerName($db->f("ownerID"));
$taxRollRecordArray = array("barangay" => $db2->f("barangay"), "tdID" => $db2->f("tdID"), "ownerName" => $ownerName, "taxDeclarationNumber" => $db2->f("taxDeclarationNumber"), "actualUse" => $this->getActualUse($db2->f("afsID"), $db2->f("propertyType")), "assessedValue" => $db2->f("totalAssessedValue"), "basicTax" => $basicTax, "sefTax" => $sefTax, "total" => $basicTax + $sefTax);
$taxRollArrayList[trim($ownerName) . $i] = $taxRollRecordArray;
$i++;
}
}
}
if (is_array($taxRollArrayList)) {
ksort($taxRollArrayList);
reset($taxRollArrayList);
return $taxRollArrayList;
} else {
return false;
}
}
function filterByClassification($condition)
{
$condition = str_replace("WHERE", "AND ", $condition);
// landActualUses
$sql = "SELECT " . OD_TABLE . ".odID as odID, " . AFS_TABLE . ".afsID as afsID, " . LAND_TABLE . ".actualUse as actualUse FROM " . OD_TABLE . ", " . LOCATION_TABLE . ", " . LOCATIONADDRESS_TABLE . ", " . BARANGAY_TABLE . ", " . DISTRICT_TABLE . ", " . MUNICIPALITYCITY_TABLE . ", " . PROVINCE_TABLE . ", " . AFS_TABLE . ", " . LAND_TABLE . ", " . LAND_ACTUALUSES_TABLE . " WHERE " . OD_TABLE . ".odID = " . AFS_TABLE . ".odID AND " . AFS_TABLE . ".afsID = " . LAND_TABLE . ".afsID AND " . LAND_TABLE . ".actualUse = " . LAND_ACTUALUSES_TABLE . ".landActualUsesID AND " . OD_TABLE . ".odID = " . LOCATION_TABLE . ".odID AND " . LOCATION_TABLE . ".odID = " . OD_TABLE . ".odID AND " . LOCATIONADDRESS_TABLE . ".locationAddressID = " . LOCATION_TABLE . ".locationAddressID AND " . BARANGAY_TABLE . ".barangayID = " . LOCATIONADDRESS_TABLE . ".barangayID AND " . DISTRICT_TABLE . ".districtID = " . LOCATIONADDRESS_TABLE . ".district AND " . MUNICIPALITYCITY_TABLE . ".municipalityCityID = " . LOCATIONADDRESS_TABLE . ".municipalityCity AND " . PROVINCE_TABLE . ".provinceID = " . LOCATIONADDRESS_TABLE . ".province AND " . LAND_ACTUALUSES_TABLE . ".reportCode = '" . $this->formArray["reportCode"] . "' ";
$sql .= $condition;
$dbLand = new DB_RPTS();
$dbLand->query($sql);
while ($dbLand->next_record()) {
$odIDArray[] = $dbLand->f("odID");
}
// plantsTrees ActualUses
$sql = "SELECT " . OD_TABLE . ".odID as odID, " . AFS_TABLE . ".afsID as afsID, " . PLANTSTREES_TABLE . ".actualUse as actualUse FROM " . OD_TABLE . ", " . LOCATION_TABLE . ", " . LOCATIONADDRESS_TABLE . ", " . BARANGAY_TABLE . ", " . DISTRICT_TABLE . ", " . MUNICIPALITYCITY_TABLE . ", " . PROVINCE_TABLE . ", " . AFS_TABLE . ", " . PLANTSTREES_TABLE . ", " . PLANTSTREES_ACTUALUSES_TABLE . " WHERE " . OD_TABLE . ".odID = " . AFS_TABLE . ".odID AND " . AFS_TABLE . ".afsID = " . PLANTSTREES_TABLE . ".afsID AND " . PLANTSTREES_TABLE . ".actualUse = " . PLANTSTREES_ACTUALUSES_TABLE . ".plantsTreesActualUsesID AND " . OD_TABLE . ".odID = " . LOCATION_TABLE . ".odID AND " . LOCATION_TABLE . ".odID = " . OD_TABLE . ".odID AND " . LOCATIONADDRESS_TABLE . ".locationAddressID = " . LOCATION_TABLE . ".locationAddressID AND " . BARANGAY_TABLE . ".barangayID = " . LOCATIONADDRESS_TABLE . ".barangayID AND " . DISTRICT_TABLE . ".districtID = " . LOCATIONADDRESS_TABLE . ".district AND " . MUNICIPALITYCITY_TABLE . ".municipalityCityID = " . LOCATIONADDRESS_TABLE . ".municipalityCity AND " . PROVINCE_TABLE . ".provinceID = " . LOCATIONADDRESS_TABLE . ".province AND " . PLANTSTREES_ACTUALUSES_TABLE . ".reportCode = '" . $this->formArray["reportCode"] . "' ";
$sql .= $condition;
$dbPlantsTrees = new DB_RPTS();
$dbPlantsTrees->query($sql);
while ($dbPlantsTrees->next_record()) {
$odIDArray[] = $dbPlantsTrees->f("odID");
}
// improvementsBuildings ActualUses
$sql = "SELECT " . OD_TABLE . ".odID, " . AFS_TABLE . ".afsID, " . IMPROVEMENTSBUILDINGS_TABLE . ".actualUse FROM " . OD_TABLE . ", " . LOCATION_TABLE . ", " . LOCATIONADDRESS_TABLE . ", " . BARANGAY_TABLE . ", " . DISTRICT_TABLE . ", " . MUNICIPALITYCITY_TABLE . ", " . PROVINCE_TABLE . ", " . AFS_TABLE . ", " . IMPROVEMENTSBUILDINGS_TABLE . ", " . IMPROVEMENTSBUILDINGS_ACTUALUSES_TABLE . " WHERE " . OD_TABLE . ".odID = " . AFS_TABLE . ".odID AND " . AFS_TABLE . ".afsID = " . IMPROVEMENTSBUILDINGS_TABLE . ".afsID AND " . IMPROVEMENTSBUILDINGS_TABLE . ".actualUse = " . IMPROVEMENTSBUILDINGS_ACTUALUSES_TABLE . ".improvementsBuildingsActualUsesID AND " . OD_TABLE . ".odID = " . LOCATION_TABLE . ".odID AND " . LOCATION_TABLE . ".odID = " . OD_TABLE . ".odID AND " . LOCATIONADDRESS_TABLE . ".locationAddressID = " . LOCATION_TABLE . ".locationAddressID AND " . BARANGAY_TABLE . ".barangayID = " . LOCATIONADDRESS_TABLE . ".barangayID AND " . DISTRICT_TABLE . ".districtID = " . LOCATIONADDRESS_TABLE . ".district AND " . MUNICIPALITYCITY_TABLE . ".municipalityCityID = " . LOCATIONADDRESS_TABLE . ".municipalityCity AND " . PROVINCE_TABLE . ".provinceID = " . LOCATIONADDRESS_TABLE . ".province AND " . IMPROVEMENTSBUILDINGS_ACTUALUSES_TABLE . ".reportCode = '" . $this->formArray["reportCode"] . "' ";
$sql .= $condition;
$dbImprovementsBuildings = new DB_RPTS();
$dbImprovementsBuildings->query($sql);
while ($dbImprovementsBuildings->next_record()) {
$odIDArray[] = $dbImprovementsBuildings->f("odID");
}
// machineries ActualUses
$sql = "SELECT " . OD_TABLE . ".odID, " . AFS_TABLE . ".afsID, " . MACHINERIES_TABLE . ".actualUse FROM " . OD_TABLE . ", " . LOCATION_TABLE . ", " . LOCATIONADDRESS_TABLE . ", " . BARANGAY_TABLE . ", " . DISTRICT_TABLE . ", " . MUNICIPALITYCITY_TABLE . ", " . PROVINCE_TABLE . ", " . AFS_TABLE . ", " . MACHINERIES_TABLE . ", " . MACHINERIES_ACTUALUSES_TABLE . " WHERE " . OD_TABLE . ".odID = " . AFS_TABLE . ".odID AND " . AFS_TABLE . ".afsID = " . MACHINERIES_TABLE . ".afsID AND " . MACHINERIES_TABLE . ".actualUse = " . MACHINERIES_ACTUALUSES_TABLE . ".machineriesActualUsesID AND " . OD_TABLE . ".odID = " . LOCATION_TABLE . ".odID AND " . LOCATION_TABLE . ".odID = " . OD_TABLE . ".odID AND " . LOCATIONADDRESS_TABLE . ".locationAddressID = " . LOCATION_TABLE . ".locationAddressID AND " . BARANGAY_TABLE . ".barangayID = " . LOCATIONADDRESS_TABLE . ".barangayID AND " . DISTRICT_TABLE . ".districtID = " . LOCATIONADDRESS_TABLE . ".district AND " . MUNICIPALITYCITY_TABLE . ".municipalityCityID = " . LOCATIONADDRESS_TABLE . ".municipalityCity AND " . PROVINCE_TABLE . ".provinceID = " . LOCATIONADDRESS_TABLE . ".province AND " . MACHINERIES_ACTUALUSES_TABLE . ".reportCode = '" . $this->formArray["reportCode"] . "' ";
$sql .= $condition;
$dbMachineries = new DB_RPTS();
$dbMachineries->query($sql);
while ($dbMachineries->next_record()) {
$odIDArray[] = $dbMachineries->f("odID");
}
if (is_array($odIDArray)) {
$odIDArrayUnique = array_unique($odIDArray);
return $odIDArrayUnique;
} else {
return false;
}
}
function run($barangayID)
{
// get od list by barangay
$this->formArray["progressLog"] = "gathering RPUs...";
$db = new DB_RPTS();
$sql = "SELECT " . OD_TABLE . ".odID as odID " . "FROM " . OD_TABLE . " " . "LEFT JOIN " . LOCATION_TABLE . " " . "ON " . OD_TABLE . ".odID = " . LOCATION_TABLE . ".odID " . "LEFT JOIN " . LOCATIONADDRESS_TABLE . " " . "ON " . LOCATION_TABLE . ".locationAddressID=" . LOCATIONADDRESS_TABLE . ".locationAddressID " . "LEFT JOIN " . BARANGAY_TABLE . " " . "ON " . BARANGAY_TABLE . ".barangayID=" . LOCATIONADDRESS_TABLE . ".barangayID " . "LEFT JOIN " . ODHISTORY_TABLE . " " . "ON " . OD_TABLE . ".odID = " . ODHISTORY_TABLE . ".previousODID " . "WHERE " . BARANGAY_TABLE . ".barangayID='" . fixQuotes($barangayID) . "' " . "AND " . ODHISTORY_TABLE . ".previousODID IS NULL " . "AND ((" . OD_TABLE . ".transactionCode='GR' AND " . OD_TABLE . ".dateCreated <= " . fixQuotes($this->formArray["countBackDaysTimeStr"]) . ") " . "OR (" . OD_TABLE . ".transactionCode!='GR'))";
$db->query($sql);
$this->formArray["totalCreated"] = 0;
$ctr = 0;
$this->formArray["progressLog"] = "RPUs gathered...";
$total = $db->nf();
while ($db->next_record()) {
$ctr++;
// NCC Modification checked and implemented by K2 : November 17, 2005
// details:
// commented out link 659 that uses $this->RunGeneralRevision();
// added line 660 that uses $this->RunGeneralRevisionBrgy();
//$newODID = $this->RunGeneralRevision($db->f("odID"),$this->userID,"GR");
$newODID = $this->RunGeneralRevisionBrgy($db->f("odID"), $this->userID, "GR");
if ($newODID == "" || $newODID == false || is_numeric($newODID) == false) {
// skipped
$this->formArray["progressLog"] = "[" . $ctr . "/" . $total . "]...[skipped odID:" . $db->f("odID") . "]" . "\n" . $this->formArray["progressLog"];
} else {
// ran
$this->formArray["progressLog"] = "[" . $ctr . "/" . $total . "]...[archived odID:" . $db->f("odID") . " | created odID:" . $newODID . "]" . "\n" . $this->formArray["progressLog"];
$this->formArray["totalCreated"]++;
}
}
$this->formArray["totalArchived"] = $db->num_rows();
}
function createRecord($paymentID)
{
//create a DB object
$rptsDB = new DB_RPTS();
//prepare an SQL select statement
$sqlselect = "select paymentID, application, penalty, sef, basic, dueID\r\n FROM payments where paymentID = {$paymentID}";
//query the database
$rptsDB->query($sqlselect);
if ($rptsDB->next_record()) {
$this->paymentID = $rptsDB->f("paymentID");
$this->amount = $rptsDB->f("amount");
$this->application = $rptsDB->f("application");
$this->dueType = $rptsDB->f("dueType");
$this->receiptNum = $rptsDB->f("receiptNum");
$this->dueID = $rptsDB->f("dueID");
$this->isStoredInDatabase = true;
return true;
} else {
// you tried to create a payment that did not exist
// this should not happen
return false;
}
}
function runSQLDump($sqlArray)
{
if (is_array($sqlArray)) {
foreach ($sqlArray as $sql) {
$db = new DB_RPTS();
$db->query($sql);
}
}
}
<?
Header('Content-Type: image/png');
include_once("web/prepend.php");
include_once('web/panachart.php');
$tpl=new rpts_Template();
$tpl->set_file(array(report=>"Form.htm"));
$db = new DB_RPTS;//DB_SelectLGU("erpts-test");
// $tmpdb = new DB_SelectLGU("erpts-test");
$db->query("SELECT * FROM Barangay ORDER BY description");
$tpl->set_block(report,BRGY,bBlk);
if($db->num_rows()>0){
for($r=0;$db->next_record();$r++){
$tpl->set_var(barangayID,$db->f("barangayID"));
$tpl->set_var(barangayName,$db->f("description"));
$tpl->parse(bBlk,BRGY,true);
}
}
$tpl->set_var(fileName,$PHP_SELF);
if($isSubmit){
if($brgyID==0){
$sql = "SELECT SUM( Collection.amountPaid ) AS amountPaid, Payment.paymentID AS paymentID, TD.tdID, ".ucwords($classification)."ActualUses.code as description
FROM Collection
INNER JOIN Payment ON Payment.paymentID = Collection.paymentID
INNER JOIN TD
function getTDIDArrayFromTDNumber($tdNumber)
{
$sql = "SELECT tdID FROM " . TD_TABLE . " WHERE taxDeclarationNumber LIKE '" . $tdNumber . "'";
$db = new DB_RPTS();
$db->query($sql);
while ($db->next_record()) {
$tdIDArray[] = $db->f("tdID");
}
return $tdIDArray;
}
function deleteRPTOP($rptopID)
{
if ($rptopID == "") {
return false;
}
unset($db);
$db = new DB_RPTS();
$sql = "SELECT ownerID FROM " . OWNER_TABLE . " WHERE rptopID='" . fixQuotes($rptopID) . "'";
$db->query($sql);
if ($db->next_record()) {
$ownerID = $db->f("ownerID");
$sql = "DELETE FROM " . RPTOP_TABLE . " WHERE rptopID='" . fixQuotes($rptopID) . "'";
$db->query($sql);
$sql = "DELETE FROM " . RPTOPTD_TABLE . " WHERE rptopID='" . fixQuotes($rptopID) . "'";
$db->query($sql);
$sql = "DELETE FROM " . OWNER_PERSON_TABLE . " WHERE ownerID = '" . $ownerID . "'";
$db->query($sql);
$sql = "DELETE FROM " . OWNER_COMPANY_TABLE . " WHERE ownerID = '" . $ownerID . "'";
$db->query($sql);
$sql = "DELETE FROM " . OWNER_TABLE . " WHERE rptopID='" . $rptopID . "'";
$db->query($sql);
}
/*
general SQL flow for deleting RPTOP (just for reference)
$ownerID = SELECT ownerID FROM Owner WHERE rptopID=$rptopID;
DELETE FROM RPTOP WHERE rptopID=$rptopID;
DELETE FROM RPTOPTD WHERE rptopID=$rptopID;
DELETE FROM OwnerPerson WHERE ownerID = $ownerID;
DELETE FROM OwnerCompany WHERE ownerID = $ownerID;
DELETE FROM Owner WHERE rptopID = $rptopID;
*/
return false;
}
function searchAndSortOwnerArrayListFromPayment()
{
$db = new DB_RPTS();
// search ownerPerson
$sql = "SELECT " . PAYMENT_TABLE . ".ownerID as ownerID FROM " . PAYMENT_TABLE . ", " . OWNER_PERSON_TABLE . ", " . PERSON_TABLE . " " . "WHERE " . PAYMENT_TABLE . ".ownerID=" . OWNER_PERSON_TABLE . ".ownerID " . "AND " . OWNER_PERSON_TABLE . ".personID = " . PERSON_TABLE . ".personID " . "AND " . "(" . PERSON_TABLE . ".lastName LIKE '%" . $this->formArray["searchKey"] . "%' " . "OR " . PERSON_TABLE . ".firstName LIKE '%" . $this->formArray["searchKey"] . "%' " . "OR " . PERSON_TABLE . ".middleName LIKE '%" . $this->formArray["searchKey"] . "%' " . ");";
$db->query($sql);
while ($db->next_record()) {
$ownerIDArray[] = $db->f("ownerID");
}
// search ownerCompany
$sql = "SELECT " . PAYMENT_TABLE . ".ownerID FROM " . PAYMENT_TABLE . ", " . OWNER_COMPANY_TABLE . ", " . COMPANY_TABLE . " " . "WHERE " . PAYMENT_TABLE . ".ownerID=" . OWNER_COMPANY_TABLE . ".ownerID " . "AND " . OWNER_COMPANY_TABLE . ".companyID = " . COMPANY_TABLE . ".companyID " . "AND " . "(" . COMPANY_TABLE . ".companyName LIKE '%" . $this->formArray["searchKey"] . "%' " . ");";
$db->query($sql);
while ($db->next_record()) {
$ownerIDArray[] = $db->f("ownerID");
}
if (is_array($ownerIDArray)) {
$ownerIDArrayUnique = array_unique($ownerIDArray);
$sortedOwnerIDArray = $this->getSortedOwnerIDArray($ownerIDArrayUnique);
return $sortedOwnerIDArray;
} else {
return false;
}
}
function selectODRecords($condition)
{
// improvementsBuildings ActualUses
$this->formArray["reportCode"] = "RE";
$sql = "SELECT " . OD_TABLE . ".odID as odID, " . AFS_TABLE . ".afsID as afsID, " . AFS_TABLE . ".totalMarketValue as totalMarketValue, " . IMPROVEMENTSBUILDINGS_TABLE . ".actualUse as actualUse, " . IMPROVEMENTSBUILDINGS_ACTUALUSES_TABLE . ".reportCode as reportCode FROM " . OD_TABLE . ", " . LOCATION_TABLE . ", " . LOCATIONADDRESS_TABLE . ", " . BARANGAY_TABLE . ", " . DISTRICT_TABLE . ", " . MUNICIPALITYCITY_TABLE . ", " . PROVINCE_TABLE . ", " . AFS_TABLE . ", " . IMPROVEMENTSBUILDINGS_TABLE . ", " . IMPROVEMENTSBUILDINGS_ACTUALUSES_TABLE . " WHERE " . OD_TABLE . ".odID = " . AFS_TABLE . ".odID AND " . AFS_TABLE . ".afsID = " . IMPROVEMENTSBUILDINGS_TABLE . ".afsID AND " . IMPROVEMENTSBUILDINGS_TABLE . ".actualUse = " . IMPROVEMENTSBUILDINGS_ACTUALUSES_TABLE . ".improvementsBuildingsActualUsesID AND " . OD_TABLE . ".odID = " . LOCATION_TABLE . ".odID AND " . LOCATION_TABLE . ".odID = " . OD_TABLE . ".odID AND " . LOCATIONADDRESS_TABLE . ".locationAddressID = " . LOCATION_TABLE . ".locationAddressID AND " . BARANGAY_TABLE . ".barangayID = " . LOCATIONADDRESS_TABLE . ".barangayID AND " . DISTRICT_TABLE . ".districtID = " . LOCATIONADDRESS_TABLE . ".district AND " . MUNICIPALITYCITY_TABLE . ".municipalityCityID = " . LOCATIONADDRESS_TABLE . ".municipalityCity AND " . PROVINCE_TABLE . ".provinceID = " . LOCATIONADDRESS_TABLE . ".province AND " . IMPROVEMENTSBUILDINGS_ACTUALUSES_TABLE . ".reportCode = '" . $this->formArray["reportCode"] . "' AND " . IMPROVEMENTSBUILDINGS_TABLE . ".adjustedMarketValue < 175000 ";
$sql .= $condition;
$db = new DB_RPTS();
$db->query($sql);
while ($db->next_record()) {
$odIDArray[] = $db->f("odID");
}
if (is_array($odIDArray)) {
$odIDArray = array_unique($odIDArray);
return $odIDArray;
} else {
return false;
}
}
