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; } } }