function Main()
{
$db = new DB_RPTS();
$startDate = array();
$endDate = array();
$db->query("SELECT DISTINCT YEAR(dues.dueDate) as dueYear FROM dues");
for ($i = 0; $db->next_record(); $i++) {
$startDate[$i][0] = $db->f("dueYear") . "-01-01";
$endDate[$i][0] = $db->f("dueYear") . "-03-31";
$startDate[$i][1] = $db->f("dueYear") . "-04-01";
$endDate[$i][1] = $db->f("dueYear") . "-06-30";
$startDate[$i][2] = $db->f("dueYear") . "-07-01";
$endDate[$i][2] = $db->f("dueYear") . "-09-30";
$startDate[$i][3] = $db->f("dueYear") . "-10-01";
$endDate[$i][3] = $db->f("dueYear") . "-12-31";
}
$ypos = 625;
$this->tpl->set_block("rptsTemplate", "ROW", "rBlk");
for ($i = 0; $i < count($startDate); $i++) {
for ($j = 0; $j < count($startDate[$i]); $j++) {
$sql = "SELECT sum( basic ) AS basic, sum( sef ) AS sef, sum( penalty ) AS penalty, sum( paidBasic ) AS paidBasic, \r\n\t\t\t\tsum(paidSEF) as paidSEF,sum( paidPenalty ) AS paidPenalty FROM dues WHERE dues.dueDate between '" . $startDate[$i][$j] . "' AND '" . $endDate[$i][$j] . "' GROUP BY (dues.dueDate) ";
$db->query($sql);
if ($db->next_record()) {
$this->tpl->set_var($db->Record);
list($year[$i][$j], $month[$i][$j], $day[$i][$j]) = explode("-", $startDate[$i][$j]);
if ($month[$i][$j] == '01') {
$quarter = $year[$i][$j] . " Quarter 1 ";
} elseif ($month[$i][$j] == '04') {
$quarter = $year[$i][$j] . " Quarter 2 ";
} elseif ($month[$i][$j] == '07') {
$quarter = $year[$i][$j] . " Quarter 3 ";
} elseif ($month[$i][$j] == '10') {
$quarter = $year[$i][$j] . " Quarter 3 ";
}
}
$this->tpl->set_var(quarter, $quarter);
$this->tpl->set_var(ypos, $ypos);
$ypos = $ypos - 4;
}
$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 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 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 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 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 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 getTDHistory()
{
printf("getTDHistory(): Starting<br>\n");
$propertyTypes = array("Land", "PlantsTrees", "ImprovementsBuildings", "Machineries");
$db = new DB_RPTS();
for ($i = 0; $i < 4; $i++) {
$sqlSelectTD = sprintf("SELECT tdID, taxDeclarationNumber, cancelsTDNumber\n\t\t\t\t FROM TD LEFT JOIN Land using (propertyID) LEFT JOIN AFS using (AFSID)\n\t\t\t \t\t LEFT JOIN OD using (ODID) LEFT JOIN Owner using (ODID)\n\t\t\t\t\t LEFT JOIN OwnerPerson using (OwnerID) LEFT JOIN %s\n\t\t\t\t\t USING (PersonID)\n\t\t\t\tWHERE %s.%sID = '%s' and propertyType='%s' AND canceledByTDNumber=''\n\t\t\t\torder by tdID", $this->ownerType, $this->ownerType, $this->ownerType, $this->entityID, $propertyTypes[$i]);
printf("getTDHistory(): {$sqlSelectTD}<br>\n");
$db->query($sqlSelectTD);
while ($db->next_record()) {
// get the individual TD's and get its information
$tdNum = $db->f("taxDeclarationNumber");
$tdID = $db->f("tdID");
printf("getTDHistory(): got onewith TD# %s (%s)<br>\n", $tdNum, $tdID);
$this->checkTD($tdNum, $this->tdHistory);
}
}
printf("getTDHistory(): Done<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");
}
}
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;
}
<?
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 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();
}
" 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
$sql2 = "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 BacktaxTD".
" USING ( backtaxTDID )".
" INNER JOIN TD".
" USING (tdID)".
" INNER JOIN AFS".
" USING ( afsID )".
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 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 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 runSQLDump($sqlArray)
{
if (is_array($sqlArray)) {
foreach ($sqlArray as $sql) {
$db = new DB_RPTS();
$db->query($sql);
}
}
}
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 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 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 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;
}
}
