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;
     }
 }
예제 #2
0
 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);
 }
예제 #4
0
 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;
 }