public function loadClientsGeneric()
 {
     //-----------------------------------------------------------------------
     //
     //-----------------------------------------------------------------------
     global $glclsDTDateFormat;
     customClient_AAYHF_01($strInnerAAYHF, $strFieldsAAYHF, $bAAYHF_Beacon);
     $strOrder = $this->strClientOrder;
     if ($strOrder == '') {
         $strOrder = ' cl_strLocation, cr_strLName, cr_strFName, cr_strMName, cr_lKeyID ';
     }
     $clsDateTime = new dl_date_time();
     $sqlStr = "SELECT\n               cr_lKeyID, cr_strFName, cr_strMName, cr_strLName,\n               cr_dteEnrollment,\n               cr_dteBirth, cr_dteDeath, cr_enumGender,\n               cr_lLocationID, cl_strCountry, cr_lStatusCatID, cr_lVocID,\n               cr_lMaxSponsors, cr_strBio, cr_bNoLongerAtLocation,\n               cr_bRetired, cr_lOriginID, cr_lLastUpdateID,\n               UNIX_TIMESTAMP(cr_dteOrigin)     AS dteOrigin,\n               UNIX_TIMESTAMP(cr_dteLastUpdate) AS dteLastUpdate,\n               cl_strLocation, cl_bEnableEMR,\n               cr_lAttributedTo, lgen_strListItem,\n\n               cr_strAddr1,      cr_strAddr2,\n               cr_strCity,       cr_strState,      cr_strCountry,\n               cr_strZip,        cr_strPhone,      cr_strCell, cr_strEmail,\n\n               -- ------------------------\n               -- vocabulary\n               -- ------------------------\n               cv_strVocTitle,\n               cv_strVocClientS, cv_strVocClientP,\n               cv_strVocSponsorS, cv_strVocSponsorP,\n               cv_strVocLocS, cv_strVocLocP,\n               cv_strVocSubLocS, cv_strVocSubLocP,\n\n               -- ------------------------\n               -- current status\n               -- ------------------------\n               csh_lKeyID, csh_lStatusID,\n               csh_dteStatusDate,\n               csh_bIncludeNotesInPacket, csh_strStatusTxt,\n\n               cst_lClientStatusCatID, cst_strStatus, cst_bAllowSponsorship,\n               cst_bShowInDir, cst_bDefault,\n               curStatSCat.csc_strCatName     AS curStat_strCatName,\n               defSCat.csc_strCatName         AS defStat_strCatName,\n\n               uc.us_strFirstName AS strUCFName, uc.us_strLastName AS strUCLName,\n               ul.us_strFirstName AS strULFName, ul.us_strLastName AS strULLName\n\n               {$strFieldsAAYHF}\n\n            FROM client_records\n               {$this->strInnerExtra}\n               {$strInnerAAYHF}\n               LEFT JOIN client_location                    ON cr_lLocationID = cl_lKeyID\n               LEFT JOIN lists_client_vocab                 ON cr_lVocID      = cv_lKeyID\n\n               LEFT JOIN client_status                      ON csh_lClientID   = cr_lKeyID\n               LEFT JOIN lists_client_status_entries        ON csh_lStatusID   = cst_lKeyID\n               LEFT JOIN client_status_cats AS curStatSCat  ON cst_lClientStatusCatID = curStatSCat.csc_lKeyID\n               LEFT JOIN client_status_cats AS defSCat      ON cr_lStatusCatID        = defSCat.csc_lKeyID\n\n               INNER JOIN admin_users  AS uc ON uc.us_lKeyID=cr_lOriginID\n               INNER JOIN admin_users  AS ul ON ul.us_lKeyID=cr_lLastUpdateID\n\n               LEFT  JOIN lists_generic      ON cr_lAttributedTo=lgen_lKeyID\n            WHERE 1\n               AND NOT cr_bRetired\n\n                 -- ---------------------------------------\n                 -- subquery to find most current status\n                 -- ---------------------------------------\n               AND csh_lKeyID=(SELECT csh_lKeyID\n                               FROM client_status\n                               WHERE csh_lClientID=cr_lKeyID\n                                  AND NOT csh_bRetired\n                               ORDER BY csh_dteStatusDate DESC, csh_lKeyID DESC\n                               LIMIT 0,1)\n\n               {$this->strExtraClientWhere}\n            ORDER BY {$strOrder}\n            {$this->strClientLimit};";
     $query = $this->db->query($sqlStr);
     $this->lNumClients = $numRows = $query->num_rows();
     $this->clients = array();
     if ($numRows == 0) {
         $this->clients[0] = new stdClass();
         $client =& $this->clients[0];
         $client->lKeyID = $client->strFName = $client->strMName = $client->strLName = $client->dteEnrollment = $client->dteBirth = $client->dteDeath = $client->enumGender = $client->lLocationID = $client->lStatusCatID = $client->lVocID = $client->lMaxSponsors = $client->strBio = $client->lAttribID = $client->strAttrib = $client->bRetired = $client->lOriginID = $client->lLastUpdateID = $client->dteOrigin = $client->dteLastUpdate = $client->strLocation = $client->strLocCountry = $client->strAddr1 = $client->strAddr2 = $client->strCity = $client->strState = $client->strCountry = $client->strZip = $client->strPhone = $client->strCell = $client->strEmail = $client->strAddress = $client->cv_strVocTitle = $client->cv_strVocClientS = $client->cv_strVocClientP = $client->cv_strVocSponsorS = $client->cv_strVocSponsorP = $client->cv_strVocLocS = $client->cv_strVocLocP = $client->cv_strVocSubLocS = $client->cv_strVocSubLocP = $client->curStat_lStatRecKeyID = $client->curStat_lStatusID = $client->curStat_dteStatus = $client->curStat_bIncludeNotesInPacket = $client->curStat_strStatusNotes = $client->curStat_lClientStatusCatID = $client->curStat_strStatus = $client->curStat_bAllowSponsorship = $client->curStat_bShowInDir = $client->curStat_bDefaultStatus = $client->curStat_strStatusCatName = null;
     } else {
         $idx = 0;
         foreach ($query->result() as $row) {
             $this->clients[$idx] = new stdClass();
             $client =& $this->clients[$idx];
             $client->lKeyID = (int) $row->cr_lKeyID;
             $client->strFName = $row->cr_strFName;
             $client->strMName = $row->cr_strMName;
             $client->strLName = $row->cr_strLName;
             $client->strSafeName = htmlspecialchars($row->cr_strFName . ' ' . $row->cr_strLName);
             $client->strSafeNameLF = htmlspecialchars($row->cr_strLName . ', ' . $row->cr_strFName);
             $client->dteEnrollment = dteMySQLDate2Unix($row->cr_dteEnrollment);
             $client->dteBirth = $mySQLdteBirth = $row->cr_dteBirth;
             $client->dteDeath = $row->cr_dteDeath;
             $client->enumGender = $row->cr_enumGender;
             $client->lLocationID = $row->cr_lLocationID;
             $client->lStatusCatID = $row->cr_lStatusCatID;
             $client->strStatusCatName = $row->defStat_strCatName;
             //------------------------------
             // client address/contact
             //------------------------------
             $client->strAddr1 = $row->cr_strAddr1;
             $client->strAddr2 = $row->cr_strAddr2;
             $client->strCity = $row->cr_strCity;
             $client->strState = $row->cr_strState;
             $client->strCountry = $row->cr_strCountry;
             $client->strZip = $row->cr_strZip;
             $client->strPhone = $row->cr_strPhone;
             $client->strCell = $row->cr_strCell;
             $client->strEmail = $row->cr_strEmail;
             $client->strEmailFormatted = strBuildEmailLink($client->strEmail, '', false, '');
             $client->strAddress = strBuildAddress($client->strAddr1, $client->strAddr2, $client->strCity, $client->strState, $client->strCountry, $client->strZip, true);
             //------------------------------
             // client age/birth day info
             //------------------------------
             if (is_null($mySQLdteBirth)) {
                 $client->objClientBirth = null;
                 $client->lAgeYears = null;
                 $client->strClientAgeBDay = '(age n/a)';
             } else {
                 $client->objClientBirth = new dl_date_time();
                 $client->objClientBirth->setDateViaMySQL(0, $mySQLdteBirth);
                 $client->strClientAgeBDay = $client->objClientBirth->strPeopleAge(0, $mySQLdteBirth, $client->lAgeYears, $glclsDTDateFormat);
             }
             $client->lVocID = $row->cr_lVocID;
             $client->lMaxSponsors = $row->cr_lMaxSponsors;
             $client->strBio = $row->cr_strBio;
             $client->bRetired = $row->cr_bRetired;
             $client->lOriginID = $row->cr_lOriginID;
             $client->lLastUpdateID = $row->cr_lLastUpdateID;
             $client->strLocation = $row->cl_strLocation;
             $client->strLocCountry = $row->cl_strCountry;
             $client->bEnableEMR = $row->cl_bEnableEMR;
             $client->cv_strVocTitle = $row->cv_strVocTitle;
             $client->cv_strVocClientS = $row->cv_strVocClientS;
             $client->cv_strVocClientP = $row->cv_strVocClientP;
             $client->cv_strVocSponsorS = $row->cv_strVocSponsorS;
             $client->cv_strVocSponsorP = $row->cv_strVocSponsorP;
             $client->cv_strVocLocS = $row->cv_strVocLocS;
             $client->cv_strVocLocP = $row->cv_strVocLocP;
             $client->cv_strVocSubLocS = $row->cv_strVocSubLocS;
             $client->cv_strVocSubLocP = $row->cv_strVocSubLocP;
             //---------------------------------
             // status fields - current status
             //---------------------------------
             $client->curStat_lStatRecKeyID = $row->csh_lKeyID;
             $client->curStat_lStatusID = $row->csh_lStatusID;
             $client->curStat_dteStatus = dteMySQLDate2Unix($row->csh_dteStatusDate);
             $client->curStat_bIncludeNotesInPacket = $row->csh_bIncludeNotesInPacket;
             $client->curStat_strStatusNotes = $row->csh_strStatusTxt;
             $client->curStat_lClientStatusCatID = $row->cst_lClientStatusCatID;
             $client->curStat_strStatus = $row->cst_strStatus;
             $client->curStat_bAllowSponsorship = $row->cst_bAllowSponsorship;
             $client->curStat_bShowInDir = $row->cst_bShowInDir;
             $client->curStat_bDefaultStatus = $row->cst_bDefault;
             $client->curStat_strStatusCatName = $row->curStat_strCatName;
             $client->lAttribID = $row->cr_lAttributedTo;
             $client->strAttrib = $row->lgen_strListItem;
             $client->dteOrigin = $row->dteOrigin;
             $client->dteLastUpdate = $row->dteLastUpdate;
             $client->ucstrFName = $row->strUCFName;
             $client->ucstrLName = $row->strUCLName;
             $client->ulstrFName = $row->strULFName;
             $client->ulstrLName = $row->strULLName;
             $client->strFlagsTable = '';
             //------------------
             // sponsors
             //------------------
             $this->loadSponsorshipInfo($client->lKeyID, true, $client->lNumSponsors, $client->sponsors);
             //------------------
             // custom fields
             //------------------
             addClientFields_AAYHF_Beacon($bAAYHF_Beacon, $client, $row);
             ++$idx;
         }
     }
     if ($this->bDebug) {
         $this->dumpClientRecs();
     }
 }
 function loadBaseEFieldRecs(&$cprog, &$lNumERecs, &$erecs, $sqlWhere, $strOrder = '')
 {
     //---------------------------------------------------------------------
     //
     //---------------------------------------------------------------------
     $strETable = $cprog->strEnrollmentTable;
     $strEFNPrefix = $cprog->strETableFNPrefix;
     if ($strOrder == '') {
         $strOrder = 'ETable.' . $strEFNPrefix . '_dteStart, ETable.' . $strEFNPrefix . '_dteEnd, ETable.' . $strEFNPrefix . '_lKeyID ';
     }
     customClient_AAYHF_01($strInnerAAYHF, $strFieldsAAYHF, $bAAYHF_Beacon);
     $sqlStr = 'SELECT
          ETable.' . $strEFNPrefix . '_lKeyID                   AS lKeyID,
          ETable.' . $strEFNPrefix . '_lForeignKey              AS lForeignKey,
          ETable.' . $strEFNPrefix . '_lOriginID                AS lOriginID,
          ETable.' . $strEFNPrefix . '_dteOrigin                AS dteOrigin,
          ETable.' . $strEFNPrefix . '_lLastUpdateID            AS lLastUpdateID,
          ETable.' . $strEFNPrefix . '_dteLastUpdate            AS dteLastUpdate,
          ETable.' . $strEFNPrefix . '_dteStart                 AS dteMysqlStart,
          ETable.' . $strEFNPrefix . '_dteEnd                   AS dteMysqlEnd,
          ETable.' . $strEFNPrefix . "_bCurrentlyEnrolled       AS bCurrentlyEnrolled,\n             cr_strLName, cr_strFName, cr_dteEnrollment,\n             cr_dteBirth\n             {$strFieldsAAYHF}\n\n         FROM {$strETable} AS ETable\n            INNER JOIN client_records ON ETable.{$strEFNPrefix}" . "_lForeignKey = cr_lKeyID\n            {$strInnerAAYHF}\n         WHERE NOT ETable.{$strEFNPrefix}" . "_bRetired {$sqlWhere}\n         ORDER BY {$strOrder};";
     $query = $this->db->query($sqlStr);
     $lNumERecs = $query->num_rows();
     $erecs = array();
     if ($lNumERecs == 0) {
         $erecs[0] = new stdClass();
         $erec =& $erecs[0];
         $erec->lKeyID = $erec->lForeignKey = $erec->lClientID = $erec->dteMysqlStart = $erec->dteMysqlEnd = $erec->dteStart = $erec->dteEnd = $erec->bCurrentlyEnrolled = $erec->strClientFName = $erec->strClientLName = $erec->dteEnrolled = $erec->mysqlDteBirth = $erec->lOriginID = $erec->dteOrigin = $erec->lLastUpdateID = $erec->dteLastUpdate = null;
     } else {
         $idx = 0;
         foreach ($query->result() as $row) {
             $erecs[$idx] = new stdClass();
             $erec =& $erecs[$idx];
             $erec->lKeyID = (int) $row->lKeyID;
             $erec->lClientID = (int) $row->lForeignKey;
             $erec->dteMysqlStart = $row->dteMysqlStart;
             $erec->dteMysqlEnd = $row->dteMysqlEnd;
             $erec->dteStart = dteMySQLDate2Unix($row->dteMysqlStart);
             $erec->dteEnd = dteMySQLDate2Unix($row->dteMysqlEnd);
             $erec->bCurrentlyEnrolled = (bool) $row->bCurrentlyEnrolled;
             $erec->strClientFName = $row->cr_strFName;
             $erec->strClientLName = $row->cr_strLName;
             $erec->dteEnrolled = dteMySQLDate2Unix($row->cr_dteEnrollment);
             $erec->mysqlDteBirth = $row->cr_dteBirth;
             $erec->lOriginID = (int) $row->lOriginID;
             $erec->dteOrigin = (int) $row->dteOrigin;
             $erec->lLastUpdateID = (int) $row->lLastUpdateID;
             $erec->dteLastUpdate = (int) $row->dteLastUpdate;
             addClientFields_AAYHF_Beacon($bAAYHF_Beacon, $erec, $row);
             ++$idx;
         }
     }
 }