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 , 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 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 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 setCompanyList() { $db = new DB_RPTS(); $db1 = new DB_RPTS(); $db->query("SELECT DISTINCT companyID from OwnerCompany inner join Owner on Owner.ownerID=OwnerCompany.ownerID where Owner.rptopID <> ''"); $this->tpl->set_block("rptsTemplate", "Company", "cBlk"); for ($i = 0; $db->next_record(); $i++) { $companyID = $db->f("companyID"); $company = new Company(); if ($company->selectRecord($companyID)) { //$setPersonArray($person); $this->tpl->set_var(companyName, $company->getCompanyName()); $this->tpl->set_var(companyID, $companyID); } $this->tpl->parse("cBlk", "Company", true); } }
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; }
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 USING ( tdID )
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 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 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; }
" 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 )". " INNER JOIN Location". " USING ( odID )".
function Main() { $this->formArray['currentDate'] = date("F d, Y"); $MunicipalityCityDetails = new SoapObject(NCCBIZ . "MunicipalityCityDetails.php", "urn:Object"); #test values //$this->formArray['municipalityCityID']=1; if (!($xmlStr = $MunicipalityCityDetails->getMunicipalityCityDetails($this->formArray['municipalityCityID']))) { #echo($xmlStr); exit("xml failed for municipality"); //header("Location: ".$this->sess->url("ViewSOA.php")."&status=2"); } else { if (!($domDoc = domxml_open_mem($xmlStr))) { echo "error xmlDoc"; } else { $MunicipalityCity = new MunicipalityCity(); $MunicipalityCity->parseDomDocument($domDoc); $this->formArray['municipality'] = $MunicipalityCity->getDescription(); } } if ($this->formArray['personID'] != "") { $person = new Person(); $person->selectRecord($this->formArray['personID']); $this->tpl->set_var(ownerName, $person->getFullName()); $this->tpl->set_var(ownerNo, $person->getTin()); $address = $person->addressArray[0]; if (is_object($address)) { $this->tpl->set_var(ownerAddress, $address->getNumber() . " " . $address->getStreet() . " " . $address->getBarangay() . " " . $address->getDistrict() . " " . $address->getMunicipalitycity() . " " . $address->getProvince()); } else { $this->tpl->set_var(ownerAddress, ""); } $db = new DB_RPTS(); $sql = "SELECT rptopID FROM Owner inner join OwnerPerson on Owner.ownerID=OwnerPerson.ownerID WHERE Owner.rptopID <> '' AND OwnerPerson.personID=" . $this->formArray['personID']; $db->query($sql); } else { $company = new Company(); $company->selectRecord($this->formArray['companyID']); $this->tpl->set_var(ownerName, $company->getCompanyName()); $this->tpl->set_var(ownerNo, $company->getCompanyID()); $address = $company->addressArray[0]; $this->tpl->set_var(ownerAddress, $address->getNumber() . " " . $address->getStreet() . " " . $address->getBarangay() . " " . $address->getDistrict() . " " . $address->getMunicipalitycity() . " " . $address->getProvince()); $db = new DB_RPTS(); $sql = "SELECT rptopID FROM Owner inner join OwnerPerson on Owner.ownerID=OwnerPerson.ownerID WHERE Owner.rptopID <> '' AND OwnerPerson.personID=" . $this->formArray['companyID']; $db->query($sql); } $ypos = 325; $this->tpl->set_block("rptsTemplate", "ROW", "RowBlk"); for ($i = 0; $db->next_record(); $i++) { $rptopID = $db->f("rptopID"); $RPTOPDetails = new SoapObject(NCCBIZ . "RPTOPDetails.php", "urn:Object"); if (!($xmlStr = $RPTOPDetails->getRPTOP($rptopID))) { // exit("xml failed for RPTOP"); header("Location: " . $this->sess->url("ViewSOA.php") . "&status=1"); } else { //echo $xmlStr; if (!($domDoc = domxml_open_mem($xmlStr))) { $this->tpl->set_block("rptsTemplate", "OwnerListTable", "OwnerListTableBlock"); $this->tpl->set_var("OwnerListTableBlock", "error xmlDoc"); } else { $rptop = new RPTOP(); $td = new TD(); $rptop->parseDomDocument($domDoc); foreach ($rptop as $key => $value) { $this->formArray['payableYear'] = $rptop->getTaxableYear(); $rptopID = $rptop->getRptopID(); if ($key == "tdArray") { $tdCtr = 0; if (count($value)) { foreach ($value as $tkey => $tvalue) { $td->selectRecord($tvalue->getTdID()); $assessedValue = number_format($td->getAssessedValue(), 2, ".", ""); $propertyType = $td->getPropertyType(); $TaxDeclarationNumber = $td->getTaxDeclarationNumber(); $afsID = $td->getAfsID(); $afs = new AFS(); $afs->selectRecord($afsID); $od = new OD(); $od->selectRecord($afs->getOdID()); $addr = $od->getLocationAddress(); if (count($addr)) { $location = strtoupper($addr->getNumber() . " " . substr($addr->getBarangay(), 0, 4) . " " . substr($addr->getMunicipalityCity(), 0, 3) . " " . substr($addr->getProvince(), 0, 3)); $munCityID = $addr->getMunicipalityCityID(); } if (count($afs->landArray)) { foreach ($afs->landArray as $afsKey => $afsValue) { $actualUse = $afsValue->getActualUse(); $landActualUses = new LandActualUses(); $landActualUses->selectRecord($actualUse); $Code = $landActualUses->getCode(); } } if (count($afs->improvementsBuildingsArray)) { foreach ($afs->improvementsBuildingsArray as $afsKey => $afsValue) { $actualUse = $afsValue->getActualUse(); $improvementsBuildingsActualUses = new improvementsBuildingsActualUses(); $improvementsBuildingsActualUses->selectRecord($actualUse); $Code = $improvementsBuildingsActualUses->getCode(); } } if ($munCityID == $this->formArray['municipalityCityID']) { $this->tpl->set_var(location, $location); $this->tpl->set_var("class", $Code); $this->tpl->set_var(kind, strtoupper(substr($propertyType, 0, 4))); $this->tpl->set_var(currentTDNo, $TaxDeclarationNumber); $this->tpl->set_var(municipality, $addr->getMunicipalityCity()); //$dues = new Dues(); //$dues->create($td->getTdID(),"2003"); //$totTaxDue += $dues->getSEF()+$dues->getBasic(); $dues = new Dues($tvalue->getTdID(), $rptop->getTaxableYear(), $assessedValue); $paymentPeriod = $dues->getPaymentMode(); $totalTaxDue = $dues->getPaidBasic($paymentPeriod) + $dues->getPaidSEF($paymentPeriod) + $dues->getPaidIdle($paymentPeriod); if ($dues->getAmnesty()) { $dues->setPctPenalty(0.0); } else { $totalTaxDue += $dues->getPenalty($paymentPeriod); } if ($dues->getIsDiscount()) { $taxDue->setDiscount($totalTaxDue); $totalTaxDue -= $dues->getDiscount(); } $interest = $dues->getPctPenalty(); if ($interest > 0 && $paymentPeriod != "Annual") { $paymentPeriod = "Annual"; } $basic = number_format($dues->getPaidBasic(), "2", ".", ""); $this->tpl->set_var(basic, $basic); $totBasic += $basic; $sef = number_format($dues->getPaidSEF(), "2", ".", ""); $this->tpl->set_var(sef, $sef); $totSEF += number_format($sef, "2", ".", ""); $this->tpl->set_var(total, number_format($sef + $basic, "2", ".", "")); $this->tpl->set_var(marketValue, number_format($afs->getTotalMarketValue(), 2)); $totMarketValue += $afs->getTotalMarketValue(); $this->tpl->set_var(assessedValue, number_format($afs->getTotalAssessedValue(), 2)); $totAssessedValue += $afs->getTotalAssessedValue(); $pIndexNo = $afs->getPropertyIndexNumber(); if ($pIndexNo == "") { $pIndexNo = "No value specified"; } $ypos = $ypos - 10; $this->tpl->set_var(ypos, $ypos); $this->tpl->set_var(pin, $pIndexNo); $this->tpl->parse("RowBlk", "ROW", true); } } #end foreach($value) } #end if coun value $this->tpl->set_var(totalMarketValue, number_format($totMarketValue, 2)); $this->tpl->set_var(totalAssessedValue, number_format($totAssessedValue, 2)); $this->tpl->set_var(totalBasic, number_format($totBasic, 2)); $this->tpl->set_var(totalSEF, number_format($totSEF, 2)); $this->tpl->set_var(totalTaxDue, number_format($totalTaxDue, 2)); } } } } } $this->setForm(); $this->tpl->set_var("Session", $this->sess->url("")); /* $this->tpl->parse("templatePage", "rptsTemplate"); $this->tpl->finish("templatePage"); $this->tpl->p("templatePage"); */ $this->tpl->parse("templatePage", "rptsTemplate"); $this->tpl->finish("templatePage"); $rptrpdf = new PDFWriter(); $rptrpdf->setOutputXML($this->tpl->get('templatePage'), "string"); $rptrpdf->writePDF("viewSOA.pdf"); }
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 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 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 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 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 Main() { $this->formArray['currentDate'] = date("F d, Y"); $MunicipalityCityDetails = new SoapObject(NCCBIZ . "MunicipalityCityDetails.php", "urn:Object"); #test values //$this->formArray['municipalityCityID']=1; if (!($xmlStr = $MunicipalityCityDetails->getMunicipalityCityDetails($this->formArray['municipalityCityID']))) { #echo($xmlStr); //exit("xml failed for municipality"); header("Location: " . $this->sess->url("ViewSOA.php") . "&status=2"); } else { if (!($domDoc = domxml_open_mem($xmlStr))) { echo "error xmlDoc"; } else { $MunicipalityCity = new MunicipalityCity(); $MunicipalityCity->parseDomDocument($domDoc); $this->formArray['municipality'] = $MunicipalityCity->getDescription(); } } #test values //$this->formArray['ownerID']=5; #echo("ownerID=".$this->formArray['ownerID']."<br>"); // $this->displayOwnerList($this->formArray['ownerID']); #test values //$this->formArray["rptopID"]=15; if ($this->formArray['personID'] != "") { $person = new Person(); $person->selectRecord($this->formArray['personID']); $this->tpl->set_var(ownerName, $person->getFullName()); $this->tpl->set_var(ownerNo, $person->getTin()); $address = $person->addressArray[0]; $this->tpl->set_var(ownerAddress, $address->getNumber() . " " . $address->getStreet() . " " . $address->getBarangay() . " " . $address->getDistrict() . " " . $address->getMunicipalitycity() . " " . $address->getProvince()); $db = new DB_RPTS(); $sql = "SELECT rptopID FROM Owner inner join OwnerPerson on Owner.ownerID=OwnerPerson.ownerID WHERE Owner.rptopID <> '' AND OwnerPerson.personID=" . $this->formArray['personID']; $db->query($sql); } else { $company = new Company(); $company->selectRecord($this->formArray['companyID']); $this->tpl->set_var(ownerName, $company->getCompanyName()); $this->tpl->set_var(ownerNo, $company->getCompanyID()); $address = $company->addressArray[0]; $this->tpl->set_var(ownerAddress, $address->getNumber() . " " . $address->getStreet() . " " . $address->getBarangay() . " " . $address->getDistrict() . " " . $address->getMunicipalitycity() . " " . $address->getProvince()); $db = new DB_RPTS(); $sql = "SELECT rptopID FROM Owner inner join OwnerPerson on Owner.ownerID=OwnerPerson.ownerID WHERE Owner.rptopID <> '' AND OwnerPerson.personID=" . $this->formArray['companyID']; $db->query($sql); } /*$person = new Person(); $person->selectRecord($this->formArray['personID']); $this->tpl->set_var(ownerName,$person->getFullName()); $this->tpl->set_var(ownerNo,$person->getTin()); $address = $person->addressArray[0]; $this->tpl->set_var(ownerAddress,$address->getNumber() ." ".$address->getStreet()." ".$address->getBarangay()." ".$address->getDistrict()." ".$address->getMunicipalitycity()." ".$address->getProvince()); $db = new DB_RPTS(); $sql = "SELECT rptopID FROM Owner inner join OwnerPerson on Owner.ownerID=OwnerPerson.ownerID WHERE Owner.rptopID <> '' AND OwnerPerson.personID=".$this->formArray['personID']; $db->query($sql);*/ $this->tpl->set_block("rptsTemplate", "ROW", "RowBlk"); for ($i = 0; $db->next_record(); $i++) { $rptopID = $db->f("rptopID"); $RPTOPDetails = new SoapObject(NCCBIZ . "RPTOPDetails.php", "urn:Object"); if (!($xmlStr = $RPTOPDetails->getRPTOP($rptopID))) { // exit("xml failed for RPTOP"); header("Location: " . $this->sess->url("ViewSOA.php") . "&status=1"); } else { //echo $xmlStr; if (!($domDoc = domxml_open_mem($xmlStr))) { $this->tpl->set_block("rptsTemplate", "OwnerListTable", "OwnerListTableBlock"); $this->tpl->set_var("OwnerListTableBlock", "error xmlDoc"); } else { $rptop = new RPTOP(); $td = new TD(); $rptop->parseDomDocument($domDoc); foreach ($rptop as $key => $value) { $this->formArray['payableYear'] = $rptop->getTaxableYear(); $rptopID = $rptop->getRptopID(); if ($key == "tdArray") { $tdCtr = 0; if (count($value)) { foreach ($value as $tkey => $tvalue) { $td->selectRecord($tvalue->getTdID()); $assessedValue = number_format($td->getAssessedValue(), 2, ".", ""); $propertyType = $td->getPropertyType(); $TaxDeclarationNumber = $td->getTaxDeclarationNumber(); // $this->tpl->set_var(kind,$propertyType); // $this->tpl->set_var(currentTDNo,$TaxDeclarationNumber); /* $dues = new Dues(); $dues->create($td->getTdID(), "","","","2003"); $this->tpl->set_var(basic,$dues->getBasic()); $totBasic += $dues->getBasic(); $this->tpl->set_var(sef,$dues->getSEF()); $totSEF += $dues->getSEF(); $this->tpl->set_var(total,$dues->getSEF()+$dues->getBasic()); $totTaxDue += $dues->getSEF()+$dues->getBasic();*/ $afsID = $td->getAfsID(); $afs = new AFS(); $afs->selectRecord($afsID); $od = new OD(); $od->selectRecord($afs->getOdID()); $addr = $od->getLocationAddress(); if (count($addr)) { $location = $addr->getFullAddress(); // $this->tpl->set_var(location,$addr->getFullAddress()); $munCityID = $addr->getMunicipalityCityID(); // if($munCityID == $this->formArray['municipalityCityID']) // $this->tpl->set_var(municipality,$addr->getMunicipalityCity()); } if (count($afs->landArray)) { foreach ($afs->landArray as $afsKey => $afsValue) { $actualUse = $afsValue->getActualUse(); $landActualUses = new LandActualUses(); $landActualUses->selectRecord($actualUse); //$this->tpl->set_var("class",$landActualUses->getCode()); $Code = $landActualUses->getCode(); } } if (count($afs->improvementsBuildingsArray)) { foreach ($afs->improvementsBuildingsArray as $afsKey => $afsValue) { $actualUse = $afsValue->getActualUse(); $improvementsBuildingsActualUses = new improvementsBuildingsActualUses(); $improvementsBuildingsActualUses->selectRecord($actualUse); //$this->tpl->set_var("class",$improvementsBuildingsActualUses->getCode()); $Code = $improvementsBuildingsActualUses->getCode(); } } //echo $afs->get // echo $munCityID ."==". $this->formArray['municipalityCityID']."<br>"; if ($munCityID == $this->formArray['municipalityCityID']) { $this->tpl->set_var(location, $location); $this->tpl->set_var("class", $Code); $this->tpl->set_var(kind, $propertyType); $this->tpl->set_var(currentTDNo, $TaxDeclarationNumber); $this->tpl->set_var(municipality, $addr->getMunicipalityCity()); $dues = new Dues(); $dues->create($td->getTdID(), "", "", "", "2003"); $totTaxDue += $dues->getSEF() + $dues->getBasic(); $basic = number_format($dues->getBasic(), "2", ".", ""); $this->tpl->set_var(basic, $basic); $totBasic += $basic; $sef = number_format($dues->getSEF(), "2", ".", ""); $this->tpl->set_var(sef, $sef); $totSEF += number_format($sef, "2", ".", ""); $this->tpl->set_var(total, number_format($sef + $basic, "2", ".", "")); $this->tpl->set_var(marketValue, number_format($afs->getTotalMarketValue(), 2)); $totMarketValue += $afs->getTotalMarketValue(); $this->tpl->set_var(assessedValue, number_format($afs->getTotalAssessedValue(), 2)); $totAssessedValue += $afs->getTotalAssessedValue(); $pIndexNo = $afs->getPropertyIndexNumber(); if ($pIndexNo == "") { $pIndexNo = "No value specified"; } $this->tpl->set_var(pin, $pIndexNo); //echo $afs->getTotalMarketValue(); /* if(count($afs->landArray)){ foreach($afs->landArray as $afsKey => $afsValue){ $this->tpl->set_var(pin,$afsValue->getPropertyIndexNumber()); } } */ $this->tpl->parse("RowBlk", "ROW", true); } //else{ // $this->tpl->set_var("RowBlk",""); // } } #end foreach($value) } #end if coun value $this->tpl->set_var(totalMarketValue, number_format($totMarketValue, 2)); $this->tpl->set_var(totalAssessedValue, number_format($totAssessedValue, 2)); $this->tpl->set_var(totalBasic, number_format($totBasic, 2)); $this->tpl->set_var(totalSEF, number_format($totSEF, 2)); $this->tpl->set_var(totalTaxDue, number_format($totTaxDue, 2)); } } } // $this->tpl->parse("RowBlk","ROW",true); } } // $owner /* $RPTOPDetails = new SoapObject(NCCBIZ."RPTOPDetails.php", "urn:Object"); if (!$xmlStr = $RPTOPDetails->getRPTOP($this->formArray["rptopID"])){ //exit("xml failed for RPTOP"); header("Location: ".$this->sess->url("ViewSOA.php")."&status=1"); } else{ //echo $xmlStr; if(!$domDoc = domxml_open_mem($xmlStr)) { $this->tpl->set_block("rptsTemplate", "OwnerListTable", "OwnerListTableBlock"); $this->tpl->set_var("OwnerListTableBlock", "error xmlDoc"); } else { $rptop = new RPTOP; $td = new TD(); $rptop->parseDomDocument($domDoc); $status_report = ""; foreach($rptop as $key => $value){ $this->formArray['payableYear'] = $rptop->getTaxableYear(); $rptopID = $rptop->getRptopID(); if($key=="tdArray"){ $tdCtr = 0; if (count($value)){ $this->tpl->set_block("rptsTemplate","ROW","RowBlk"); foreach($value as $tkey => $tvalue){ $td->selectRecord($tvalue->getTdID()); $assessedValue = number_format($td->getAssessedValue(),2,".",""); $propertyType = $td->getPropertyType(); $TaxDeclarationNumber = $td->getTaxDeclarationNumber(); //$tdCtr++; $this->tpl->set_var(kind,$propertyType); $this->tpl->set_var(currentTDNo,$TaxDeclarationNumber); /* $dues = new Dues(); $dues->create($td->getTdID(), "","","","2003"); $this->tpl->set_var(basic,$dues->getBasic()); $totBasic += $dues->getBasic(); $this->tpl->set_var(sef,$dues->getSEF()); $totSEF += $dues->getSEF(); $this->tpl->set_var(total,$dues->getSEF()+$dues->getBasic()); $totTaxDue += $dues->getSEF()+$dues->getBasic();*/ /* $afsID = $td->getAfsID(); $afs = new AFS(); $afs->selectRecord($afsID); $od = new OD(); $od->selectRecord($afs->getOdID()); $addr = $od->getLocationAddress(); if(count($addr)){ $this->tpl->set_var(location,$addr->getFullAddress()); $munCityID = $addr->getMunicipalityCityID(); if($munCityID == $this->formArray['municipalityCityID']) $this->tpl->set_var(municipality,$addr->getMunicipalityCity()); } if(count($afs->landArray)){ foreach($afs->landArray as $afsKey => $afsValue){ $actualUse = $afsValue->getActualUse(); $landActualUses = new LandActualUses(); $landActualUses->selectRecord($actualUse); $this->tpl->set_var("class",$landActualUses->getCode()); } } if(count($afs->improvementsBuildingsArray)){ foreach($afs->improvementsBuildingsArray as $afsKey => $afsValue){ $actualUse = $afsValue->getActualUse(); $improvementsBuildingsActualUses = new improvementsBuildingsActualUses(); $improvementsBuildingsActualUses->selectRecord($actualUse); $this->tpl->set_var("class",$improvementsBuildingsActualUses->getCode()); } } //echo $afs->get if($munCityID == $this->formArray['municipalityCityID']){ $dues = new Dues(); $dues->create($td->getTdID(), "","","","2003"); $this->tpl->set_var(basic,number_format($dues->getBasic(),"2",".","")); $totBasic += $dues->getBasic(); $this->tpl->set_var(sef,number_format($dues->getSEF(),"2",".","")); $totSEF += $dues->getSEF(); $this->tpl->set_var(total,number_format($dues->getSEF()+$dues->getBasic(),"2",".","")); $pIndexNo = $afs->getPropertyIndexNumber(); if($pIndexNo==""){ $pIndexNo = "No value specified"; } $this->tpl->set_var(pin,$pIndexNo); $this->tpl->set_var(marketValue,$afs->getTotalMarketValue()); $totMarketValue += $afs->getTotalMarketValue(); $this->tpl->set_var(assessedValue,$afs->getTotalAssessedValue()); $totAssessedValue += $afs->getTotalAssessedValue(); //echo $afs->getTotalMarketValue(); /* if(count($afs->landArray)){ foreach($afs->landArray as $afsKey => $afsValue){ $this->tpl->set_var(pin,$afsValue->getPropertyIndexNumber()); } } */ /* $this->tpl->parse("RowBlk","ROW",true); }else{ $this->tpl->set_var("RowBlk",""); } }#end foreach($value) }#end if coun value // $this->tpl->set_block("rptsTemplate","STAT",sBlk); // $this->tpl->set_var(status_report,$status_report); // $this->tpl->parse(sBlk,"STAT",true); $this->tpl->set_var(totalMarketValue,$totMarketValue); $this->tpl->set_var(totalAssessedValue,$totAssessedValue); $this->tpl->set_var(totalBasic,$totBasic); $this->tpl->set_var(totalSEF,$totSEF); $this->tpl->set_var(totalTaxDue,$totTaxDue); } } } }*/ $this->setForm(); $this->tpl->set_var("Session", $this->sess->url("")); $this->tpl->parse("templatePage", "rptsTemplate"); $this->tpl->finish("templatePage"); $this->tpl->p("templatePage"); }
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 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; } }
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 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 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 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; } }