public function strSponsorPastDueReport(&$cLocal, &$sRpt, $reportID, $bReport, &$displayData)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
$lMonthsPastDue = $sRpt->lMonthsPastDue;
$lACOID = $sRpt->lACOID;
$bIncludeInactive = $sRpt->bIncludeInactive;
$cLocal->clsSpon->listOfSponsors($bIncludeInactive);
$lNumSponsors = count($cLocal->clsSpon->sponsorList);
$cLocal->clsACO->loadCountries(false, true, true, $lACOID);
if ($bReport) {
$displayData['cACO'] =& $cLocal->clsACO->countries[0];
$displayData['lMonthsPastDue'] = $lMonthsPastDue;
$displayData['bIncludeInactive'] = $bIncludeInactive;
$lNumPastDue = 0;
$pastDue = array();
if ($lNumSponsors > 0) {
foreach ($cLocal->clsSpon->sponsorList as $sponRec) {
$lSponID = $sponRec->lSponID;
if ($sponRec->lCommitmentACO == $lACOID) {
$curCommit = $sponRec->curCommitment;
$curPayments = $cLocal->clsSCP->curCumulativeSponVia_ACOID(false, null, true, $lSponID, $lACOID);
$curCharges = $cLocal->clsSCP->curCumulativeChargeVia_ACOID($lSponID, $lACOID);
if ($curCharges - $curPayments >= $lMonthsPastDue * $curCommit) {
$cLocal->clsSpon->sponsorInfoViaID($lSponID);
$sRec = $cLocal->clsSpon->sponInfo[0];
$pastDue[$lNumPastDue] = new stdClass();
$pd =& $pastDue[$lNumPastDue];
$pd->lSponID = $lSponID;
$pd->curCharges = $curCharges;
$pd->curPayments = $curPayments;
$pd->curCommit = $curCommit;
$pd->bInactive = $sponRec->bInactive;
$pd->lForeignID = $sRec->lForeignID;
$pd->strSponsorFName = $sRec->strSponsorFName;
$pd->strSponsorLName = $sRec->strSponsorLName;
$pd->bSponBiz = $sRec->bSponBiz;
$pd->strSponSafeNameFL = $sRec->strSponSafeNameFL;
$pd->strSponSafeNameLF = $sRec->strSponSafeNameLF;
$pd->lClientID = $sRec->lClientID;
$pd->lSponsorProgID = $sRec->lSponsorProgID;
$pd->strSponProgram = $sRec->strSponProgram;
$pd->strClientFName = $sRec->strClientFName;
$pd->strClientLName = $sRec->strClientLName;
$pd->strClientSafeNameFL = $sRec->strClientSafeNameFL;
$pd->strClientSafeNameLF = $sRec->strClientSafeNameLF;
++$lNumPastDue;
}
}
}
}
$displayData['lTotRecs'] = $displayData['lNumPastDue'] = $lNumPastDue;
$displayData['pastDue'] =& $pastDue;
} else {
$rptExport = '';
// create temporary table to hold foreign ID of report results
$sqlStr = 'DROP TABLE IF EXISTS tmpPastDue;';
$this->db->query($sqlStr);
$sqlStr = 'CREATE TEMPORARY TABLE tmpPastDue ( -- TEMPORARY
pd_lKeyID int(11) NOT NULL AUTO_INCREMENT,
pd_lSponID int(11) NOT NULL DEFAULT \'0\',
pd_curCommitment decimal(10,2) NOT NULL DEFAULT \'0.00\',
pd_curCharges decimal(10,2) NOT NULL DEFAULT \'0.00\',
pd_curPayments decimal(10,2) NOT NULL DEFAULT \'0.00\',
pd_curBalanceDue decimal(10,2) NOT NULL DEFAULT \'0.00\',
PRIMARY KEY (pd_lKeyID),
KEY pd_lSponID (pd_lSponID)
) ENGINE=MyISAM;';
$this->db->query($sqlStr);
if ($lNumSponsors > 0) {
foreach ($cLocal->clsSpon->sponsorList as $sponRec) {
$lSponID = $sponRec->lSponID;
if ($sponRec->lCommitmentACO == $lACOID) {
$curCommit = $sponRec->curCommitment;
$curPayments = $cLocal->clsSCP->curCumulativeSponVia_ACOID(false, null, true, $lSponID, $lACOID);
$curCharges = $cLocal->clsSCP->curCumulativeChargeVia_ACOID($lSponID, $lACOID);
$curBalance = $curCharges - $curPayments;
if ($curBalance >= $lMonthsPastDue * $curCommit) {
$sqlStr = "INSERT INTO tmpPastDue\n SET pd_lSponID = {$lSponID},\n pd_curCommitment = {$curCommit},\n pd_curCharges = {$curCharges},\n pd_curPayments = {$curPayments},\n pd_curBalanceDue = {$curBalance};";
$this->db->query($sqlStr);
}
}
}
$sqlStr = 'SELECT
pd_curCharges AS `Charges`,
pd_curPayments AS `Payments`,
pd_curBalanceDue AS `Balance Due`, ' . strExportFields_Sponsor() . '
FROM tmpPastDue
INNER JOIN sponsor ON sp_lKeyID = pd_lKeyID
INNER JOIN people_names AS sponpeep ON sponpeep.pe_lKeyID = sp_lForeignID
INNER JOIN admin_aco AS commitACO ON commitACO.aco_lKeyID = sp_lCommitmentACO
INNER JOIN lists_sponsorship_programs ON sc_lKeyID = sp_lSponsorProgramID
LEFT JOIN client_records ON cr_lKeyID = sp_lClientID
LEFT JOIN client_location ON cr_lLocationID = cl_lKeyID
LEFT JOIN lists_client_vocab ON cv_lKeyID = cr_lVocID
LEFT JOIN people_names AS honpeep ON honpeep.pe_lKeyID = sp_lHonoreeID
LEFT JOIN lists_generic AS atab ON sp_lAttributedTo = lgen_lKeyID
ORDER BY sponpeep.pe_strLName, sponpeep.pe_strFName, sponpeep.pe_strMName, sp_lKeyID;';
$query = $this->db->query($sqlStr);
$rptExport = $this->dbutil->csv_from_result($query);
}
return $rptExport;
}
}
public function strExportSpon()
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
$strDateFormat = strMysqlDateFormat(false);
$strDateTimeFormat = strMysqlDateFormat(true);
$sqlStr = 'SELECT ' . strExportFields_Sponsor() . ",\n DATE_FORMAT(sp_dteOrigin, {$strDateTimeFormat}) AS `Record Creation Date`,\n DATE_FORMAT(sp_dteLastUpdate, {$strDateTimeFormat}) AS `Record Last Update`\n FROM sponsor\n INNER JOIN people_names AS sponpeep ON sponpeep.pe_lKeyID = sp_lForeignID\n INNER JOIN lists_sponsorship_programs ON sp_lSponsorProgramID = sc_lKeyID\n INNER JOIN admin_aco AS commitACO ON commitACO.aco_lKeyID = sp_lCommitmentACO\n LEFT JOIN client_records ON cr_lKeyID = sp_lClientID\n LEFT JOIN client_location ON cr_lLocationID = cl_lKeyID\n LEFT JOIN lists_client_vocab ON cr_lVocID = cv_lKeyID\n LEFT JOIN people_names AS honpeep ON honpeep.pe_lKeyID = sp_lHonoreeID\n LEFT JOIN lists_generic AS atab ON sp_lAttributedTo = lgen_lKeyID\n WHERE NOT sponpeep.pe_bRetired\n AND NOT sp_bRetired\n {$this->strSponsorshipWhere}\n ORDER BY sp_lKeyID;";
$query = $this->db->query($sqlStr);
return $this->dbutil->csv_from_result($query);
}
function strSponViaLocExport(&$sRpt)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
$lLocID = $sRpt->lLocID;
$bShowInactive = $sRpt->bShowInactive;
$sqlStr = 'SELECT ' . strExportFields_Sponsor() . "\n FROM sponsor\n INNER JOIN people_names AS sponpeep ON sponpeep.pe_lKeyID = sp_lForeignID\n INNER JOIN admin_aco AS commitACO ON commitACO.aco_lKeyID = sp_lCommitmentACO\n INNER JOIN lists_sponsorship_programs ON sc_lKeyID = sp_lSponsorProgramID\n LEFT JOIN client_records ON cr_lKeyID = sp_lClientID\n LEFT JOIN client_location ON cr_lLocationID = cl_lKeyID\n LEFT JOIN lists_client_vocab ON cv_lKeyID = cr_lVocID\n LEFT JOIN people_names AS honpeep ON honpeep.pe_lKeyID = sp_lHonoreeID\n LEFT JOIN lists_generic AS atab ON sp_lAttributedTo = lgen_lKeyID\n WHERE NOT sp_bRetired\n AND (cr_lLocationID={$lLocID}) " . ($bShowInactive ? '' : ' AND NOT sp_bInactive ') . "\n ORDER BY sp_lKeyID;";
$query = $this->db->query($sqlStr);
return $this->dbutil->csv_from_result($query);
}
private function strGroupRptExport_Sponsor(&$strGroupsReviewed, $bShowAny)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
global $genumDateFormat;
$sqlStr = 'SELECT ' . strExportFields_Sponsor() . '
FROM tmpGroupMatch
INNER JOIN sponsor ON sp_lKeyID = gm_lForeignID
INNER JOIN people_names AS sponpeep ON sponpeep.pe_lKeyID = sp_lForeignID
INNER JOIN lists_sponsorship_programs ON sp_lSponsorProgramID = sc_lKeyID
INNER JOIN admin_aco AS commitACO ON commitACO.aco_lKeyID = sp_lCommitmentACO
LEFT JOIN client_records ON cr_lKeyID = sp_lClientID
LEFT JOIN client_location ON cr_lLocationID = cl_lKeyID
LEFT JOIN lists_client_vocab ON cv_lKeyID = cr_lVocID
LEFT JOIN people_names AS honpeep ON honpeep.pe_lKeyID = sp_lHonoreeID
LEFT JOIN lists_generic AS atab ON sp_lAttributedTo = lgen_lKeyID
ORDER BY gm_lKeyID;';
$query = $this->db->query($sqlStr);
$rptExport = $this->dbutil->csv_from_result($query);
if ($this->config->item('dl_addExportRptInfo')) {
$rptExport .= strPrepStr(CS_PROGNAME . " export\n" . 'Created ' . date($genumDateFormat . ' H:i:s e') . "\n" . 'Sponsors who are in ' . ($bShowAny ? 'any' : 'all') . ' of these groups: ' . $strGroupsReviewed, null, '"');
}
return $rptExport;
}
