private function strCommonClientSQL()
{
//-----------------------------------------------------------------------
//
//-----------------------------------------------------------------------
global $glUserID, $gstrPassPhrase;
$client =& $this->patients[0];
$sqlCommon = 'cr_strFName = AES_ENCRYPT(' . strPrepStr($client->strFName) . ', SHA2(' . strPrepStr($gstrPassPhrase) . ',256)),
cr_strMName = AES_ENCRYPT(' . strPrepStr($client->strMName) . ', SHA2(' . strPrepStr($gstrPassPhrase) . ',256)),
cr_strLName = AES_ENCRYPT(' . strPrepStr($client->strLName) . ', SHA2(' . strPrepStr($gstrPassPhrase) . ',256)),
cr_strBio = AES_ENCRYPT(' . strPrepStr($client->strBio) . ', SHA2(' . strPrepStr($gstrPassPhrase) . ',256)),
cr_strAddr1 = AES_ENCRYPT(' . strPrepStr($client->strAddr1) . ', SHA2(' . strPrepStr($gstrPassPhrase) . ',256)),
cr_strAddr2 = AES_ENCRYPT(' . strPrepStr($client->strAddr2) . ', SHA2(' . strPrepStr($gstrPassPhrase) . ',256)),
cr_strCity = AES_ENCRYPT(' . strPrepStr($client->strCity) . ', SHA2(' . strPrepStr($gstrPassPhrase) . ',256)),
cr_strState = AES_ENCRYPT(' . strPrepStr($client->strState) . ', SHA2(' . strPrepStr($gstrPassPhrase) . ',256)),
cr_strCountry = AES_ENCRYPT(' . strPrepStr($client->strCountry) . ', SHA2(' . strPrepStr($gstrPassPhrase) . ',256)),
cr_strZip = AES_ENCRYPT(' . strPrepStr($client->strZip) . ', SHA2(' . strPrepStr($gstrPassPhrase) . ',256)),
cr_strPhone = AES_ENCRYPT(' . strPrepStr($client->strPhone) . ', SHA2(' . strPrepStr($gstrPassPhrase) . ',256)),
cr_strCell = AES_ENCRYPT(' . strPrepStr($client->strCell) . ', SHA2(' . strPrepStr($gstrPassPhrase) . ',256)),
cr_strEmail = AES_ENCRYPT(' . strPrepStr($client->strEmail) . ', SHA2(' . strPrepStr($gstrPassPhrase) . ',256)),
cr_dteBirth = ' . strPrepStr($client->dteBirth) . ',
cr_dteEnrollment = ' . strPrepDate($client->dteEnrollment) . ',
cr_enumGender = ' . strPrepStr($client->enumGender) . ',
cr_lMaxSponsors = ' . $client->lMaxSponsors . ',
cr_lLocationID = ' . $client->lLocationID . ',
cr_lAttributedTo = ' . (is_null($client->lAttribID) ? 'null' : $client->lAttribID) . ',
cr_lVocID = ' . $client->lVocID . ",\n cr_lLastUpdateID = {$glUserID} ";
return $sqlCommon;
}
function strDBValueConvert_Date($dteValue)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
if (is_null($dteValue)) {
$strValue = 'null';
} else {
$strValue = strPrepDate($dteValue);
}
return $strValue;
}
public function updateEventDate($lEventDateID, $dteEvent, $bmysqlDate = false)
{
//-----------------------------------------------------------------------
//
//-----------------------------------------------------------------------
if ($bmysqlDate) {
$strEDate = strPrepStr($dteEvent);
} else {
$strEDate = strPrepDate($dteEvent);
}
$sqlStr = "UPDATE vol_events_dates\n SET\n ved_dteEvent = {$strEDate}\n WHERE ved_lKeyID={$lEventDateID};";
$this->db->query($sqlStr);
}
function strActivelyEnrolledDuringMonthWhere(&$cprog, $lMonth, $lYear)
{
//---------------------------------------------------------------------
// find the enrollees whose start date is before the end of the month
// and whose inactive date is either null or after the first of the
// month.
//---------------------------------------------------------------------
$lMaxDays = lDaysInMonth($lMonth, $lYear);
$dteLastSecOfMonth = mktime(23, 59, 59, $lMonth, $lMaxDays, $lYear);
$mdteLastSec = strPrepDateTime($dteLastSecOfMonth);
$mdteFirstSec = strPrepDate(mktime(0, 0, 0, $lMonth, 1, $lYear));
$strEFNPre = $cprog->strETableFNPrefix;
$strDateStart = $strEFNPre . '_dteStart';
$strDateEnd = $strEFNPre . '_dteEnd';
return " AND ({$strDateStart} <= {$mdteLastSec})\n AND (({$strDateEnd} IS NULL) OR ({$strDateEnd} >= {$mdteFirstSec})) \n";
}
private function strSQLPayCommon()
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
global $glUserID;
return 'gi_curAmnt = ' . number_format($this->paymentRec[0]->curPaymentAmnt, 2, '.', '') . ',
gi_strCheckNum = ' . strPrepStr($this->paymentRec[0]->strCheckNum) . ',
gi_lPaymentType = ' . (int) $this->paymentRec[0]->lPaymentType . ',
gi_lACOID = ' . (int) $this->paymentRec[0]->lACOID . ',
gi_dteDonation = ' . strPrepDate($this->paymentRec[0]->dtePayment) . ",\n gi_lLastUpdateID = {$glUserID},\n gi_dteLastUpdate = NOW() ";
}
public function inactivateSponsorship($lSponID, $dteInactive, $lTermTypeID)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
global $glUserID;
$sqlStr = 'UPDATE sponsor
SET sp_bInactive = 1,
sp_dteInactive = ' . strPrepDate($dteInactive) . ",\n sp_lInactiveCatID= {$lTermTypeID},\n sp_lLastUpdateID={$glUserID}\n WHERE sp_lKeyID={$lSponID};";
$this->db->query($sqlStr);
}
function strVolScheduleReport(&$sRpt, $reportID, $bReport, $lStartRec, $lRecsPerPage)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
global $gdteNow;
$lVolID = $sRpt->lVolID;
$strShowType = $sRpt->strShowType;
$clsVols = new mvol();
$clsVols->loadVolRecsViaVolID($lVolID, true);
if ($strShowType == 'cur') {
$strLabel = 'Current and future';
$strWhere = ' AND ved_dteEvent >= ' . strPrepDate($gdteNow) . ' ';
} elseif ($strShowType == 'past') {
$strLabel = 'Past';
$strWhere = ' AND ved_dteEvent < ' . strPrepDate($gdteNow) . ' ';
} else {
$strLabel = 'Entire';
$strWhere = ' ';
}
if ($bReport) {
$strOut = $strLabel . ' schedule for the volunteer ' . $clsVols->volRecs[0]->strSafeNameFL . '<br><br>';
$strLimit = " LIMIT {$lStartRec}, {$lRecsPerPage} ";
} else {
$strExport = '';
$strLimit = '';
}
$sqlStr = "SELECT\n vsa_lKeyID,\n vsa_lEventDateShiftID, vsa_strNotes, vsa_dHoursWorked,\n vs_strShiftName,\n TIME_FORMAT(vs_dteShiftStartTime, '%l:%i %p') AS dteStartTime,\n vs_enumDuration AS strDuration,\n ved_lVolEventID, ved_dteEvent,\n vem_strEventName, vs_lEventDateID\n FROM vol_events\n INNER JOIN vol_events_dates ON vem_lKeyID = ved_lVolEventID\n INNER JOIN vol_events_dates_shifts ON ved_lKeyID = vs_lEventDateID\n INNER JOIN vol_events_dates_shifts_assign ON vs_lKeyID = vsa_lEventDateShiftID\n WHERE\n NOT vem_bRetired\n AND NOT vs_bRetired\n AND NOT vsa_bRetired\n AND vsa_lVolID={$lVolID}\n {$strWhere}\n ORDER BY ved_dteEvent, vs_dteShiftStartTime, vem_strEventName, vem_lKeyID\n {$strLimit};";
$query = $this->db->query($sqlStr);
$lNumRows = $query->num_rows();
if ($lNumRows == 0) {
return $strOut . '<br><br><i>There are no records that match your search criteria.</i>';
}
if ($bReport) {
$strOut .= $this->strVolScheduleRptHTML($query, $strLabel, $clsVols->volRecs[0]->strSafeNameFL, $clsVols->volRecs[0]->lKeyID);
return $strOut;
} else {
$strExport = $this->strVolScheduleRptExport($query, $strLabel, $clsVols->volRecs[0]->strSafeNameFL, $clsVols->volRecs[0]->lKeyID);
return $strExport;
}
}
private function strSQLCommonClientStatusEntry()
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
return '
csh_lStatusID = ' . $this->clientStatus[0]->lStatusID . ',
csh_bIncludeNotesInPacket = ' . ($this->clientStatus[0]->bIncludeNotesInPacket ? '1' : '0') . ',
csh_dteStatusDate = ' . strPrepDate($this->clientStatus[0]->dteStatus) . ',
csh_strStatusTxt = ' . strPrepStr($this->clientStatus[0]->strStatusTxt) . ' ';
}
private function strRptTimeFrameWhere($sRpt)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
$this->extractTimeFrameRptOpts($sRpt, $lACO, $bUseACO, $enumInc, $dteStart, $dteEnd, $enumSort, $curMin, $curMax, $lYear);
$strOut = ' AND NOT gi_bRetired ' . "\n";
if (!is_null($dteStart)) {
$strOut .= '
AND gi_dteDonation BETWEEN ' . strPrepDate($dteStart) . ' AND ' . strPrepDateTime($dteEnd) . "\n";
}
if (!is_null($curMin)) {
$strOut .= "\n AND gi_curAmnt BETWEEN {$curMin} AND {$curMax} \n";
}
if ($bUseACO) {
$strOut .= " AND gi_lACOID={$lACO} \n";
}
if (!is_null($enumInc)) {
switch ($enumInc) {
case 'all':
break;
case 'spon':
case 'gift':
$strOut .= ' AND gi_lSponsorID IS ' . ($enumInc == 'spon' ? ' NOT ' : '') . " NULL \n";
break;
default:
screamForHelp($enumInc . ': invalid report option<br>error on line <b>-- ' . __LINE__ . ' --</b>,<br>file ' . __FILE__ . ',<br>function ' . __FUNCTION__);
break;
}
}
if (!is_null($lYear)) {
$strOut .= " AND YEAR(gi_dteDonation)={$lYear} \n";
}
if ($sRpt->rptName == CENUM_REPORTNAME_GIFTACCOUNT) {
$strOut .= ' AND ga_lKeyID IN (' . implode(',', $sRpt->acctIDs) . ') ';
} elseif ($sRpt->rptName == CENUM_REPORTNAME_GIFTCAMP) {
$strOut .= ' AND gc_lKeyID IN (' . implode(',', $sRpt->campIDs) . ') ';
}
return $strOut;
}
function tf_getDateRanges($opts, &$formDates)
{
//-------------------------------------------------------------------------
//
//-------------------------------------------------------------------------
global $gdteNow, $gbDateFormatUS;
$formDates = new stdClass();
$formDates->bError = false;
$formDates->strRptRange = $_REQUEST[$opts->strCTRL_Prefix . 'rdo_TimePeriod'];
$formDates->strDateRange = '';
$bAddTextDate = true;
switch ($formDates->strRptRange) {
case 'DDL':
$bAddTextDate = false;
$formDates->bUseDateRange = true;
$formDates->sDDL = $_REQUEST[$opts->strCTRL_Prefix . 'ddl_TimeFrame'];
determineDateRange((int) $formDates->sDDL, $gdteNow, $formDates->dteStart, $formDates->dteEnd, $formDates->strDateRange);
break;
case 'User':
$formDates->bUseDateRange = true;
$strDate = trim($_POST[$opts->strSDateFN]);
MDY_ViaUserForm($strDate, $lMon, $lDay, $lYear, $gbDateFormatUS);
$formDates->dteStart = strtotime($lMon . '/' . $lDay . '/' . $lYear);
$strDate = trim($_POST[$opts->strEDateFN]);
MDY_ViaUserForm($strDate, $lMon, $lDay, $lYear, $gbDateFormatUS);
$formDates->dteEnd = strtotime($lMon . '/' . $lDay . '/' . $lYear . ' 23:59:59');
break;
case 'None':
$formDates->bUseDateRange = false;
$formDates->dteStart = strtotime('1/1/1970');
$formDates->dteEnd = strtotime('1/18/2038');
// end of Unix epoch
$formDates->strDateRange = "No Date Range";
$bAddTextDate = false;
break;
default:
$formDates->bError = true;
echoT('
<br><font color="red">Please click on a <b><i>radio button</b></i> (<input type="radio">)
from the data selection list to run your search.<br><br></font>
You can click on your browser\'s <b><i>back</b></i> button and try again.<br><br>');
break;
}
if ($formDates->bUseDateRange) {
$formDates->strBetween = ' BETWEEN ' . strPrepDate($formDates->dteStart) . ' AND ' . strPrepDateTime($formDates->dteEnd) . ' ';
} else {
$formDates->strBetween = ' BETWEEN "0000-00-00" AND "9999-00-00" ';
}
if ($bAddTextDate) {
if ($gbDateFormatUS) {
$formDates->strDateRange = date('m/d/Y', $formDates->dteStart) . ' - ' . date('m/d/Y', $formDates->dteEnd);
} else {
$formDates->strDateRange = date('d/m/Y', $formDates->dteStart) . ' - ' . date('d/m/Y', $formDates->dteEnd);
}
}
}
function scheduleExport(&$sRpt)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
global $gclsChapterVoc;
$strDateFormat = strMysqlDateFormat(false);
$this->entireEventInfo($sRpt->lEventIDs, $lNumEvents, $events);
$strTempTable = 'tmpExp';
$this->buildEventScheduleExportTable($strTempTable);
foreach ($events as $event) {
$rowFields = array();
$fieldIDX = 0;
$this->setExportField($rowFields, $fieldIDX, 'texp_lEventID', (string) $event->lKeyID);
$this->setExportField($rowFields, $fieldIDX, 'texp_strEventName', strPrepStr($event->strEventName));
$this->setExportField($rowFields, $fieldIDX, 'texp_strDescription', strPrepStr($event->strDescription));
$this->setExportField($rowFields, $fieldIDX, 'texp_dteEventStartDate', strPrepDate($event->dteEventStart));
$this->setExportField($rowFields, $fieldIDX, 'texp_dteEventEndDate', strPrepDate($event->dteEventEnd));
$this->setExportField($rowFields, $fieldIDX, 'texp_strLocation', strPrepStr($event->strLocation));
$this->setExportField($rowFields, $fieldIDX, 'texp_strContact', strPrepStr($event->strContact));
$this->setExportField($rowFields, $fieldIDX, 'texp_strPhone', strPrepStr($event->strPhone));
$this->setExportField($rowFields, $fieldIDX, 'texp_strEmail', strPrepStr($event->strEmail));
$this->setExportField($rowFields, $fieldIDX, 'texp_strWebSite', strPrepStr($event->strWebSite));
if ($event->lNumDates > 0) {
foreach ($event->dates as $edate) {
$this->setExportField($rowFields, $fieldIDX, 'texp_lDateID', (string) $edate->lKeyID);
$this->setExportField($rowFields, $fieldIDX, 'texp_dteEventDate', strPrepDate($edate->dteEvent));
if ($edate->lNumShifts > 0) {
foreach ($edate->shifts as $shift) {
$this->setExportField($rowFields, $fieldIDX, 'texp_lShiftID', (string) $shift->lKeyID);
$this->setExportField($rowFields, $fieldIDX, 'texp_strShiftName', strPrepStr($shift->strShiftName));
$this->setExportField($rowFields, $fieldIDX, 'texp_strShiftDescription', strPrepStr($shift->strDescription));
$this->setExportField($rowFields, $fieldIDX, 'texp_dteShiftStartTime', strPrepDateTime($shift->dteEventStartTime));
$this->setExportField($rowFields, $fieldIDX, 'texp_enumDuration', strPrepStr($shift->enumDuration));
$this->setExportField($rowFields, $fieldIDX, 'texp_lNumVolsNeeded', (string) $shift->lNumVolsNeeded);
if ($shift->lNumVols > 0) {
foreach ($shift->vols as $vol) {
$this->setExportField($rowFields, $fieldIDX, 'texp_lVolID', (string) $vol->lVolID);
$this->setExportField($rowFields, $fieldIDX, 'texp_lPeopleID', (string) $vol->lPeopleID);
$this->setExportField($rowFields, $fieldIDX, 'texp_strFName', strPrepStr($vol->strFName));
$this->setExportField($rowFields, $fieldIDX, 'texp_strLName', strPrepStr($vol->strLName));
$this->setExportField($rowFields, $fieldIDX, 'texp_strAddr1', strPrepStr($vol->strAddr1));
$this->setExportField($rowFields, $fieldIDX, 'texp_strAddr2', strPrepStr($vol->strAddr2));
$this->setExportField($rowFields, $fieldIDX, 'texp_strCity', strPrepStr($vol->strCity));
$this->setExportField($rowFields, $fieldIDX, 'texp_strState', strPrepStr($vol->strState));
$this->setExportField($rowFields, $fieldIDX, 'texp_strCountry', strPrepStr($vol->strCountry));
$this->setExportField($rowFields, $fieldIDX, 'texp_strZip', strPrepStr($vol->strZip));
$this->setExportField($rowFields, $fieldIDX, 'texp_strVolPhone', strPrepStr($vol->strPhone));
$this->setExportField($rowFields, $fieldIDX, 'texp_strCell', strPrepStr($vol->strCell));
$this->setExportField($rowFields, $fieldIDX, 'texp_strVolEmail', strPrepStr($vol->strEmail));
$this->writeTempScheduleExportRow($strTempTable, $rowFields);
}
} else {
$this->writeTempScheduleExportRow($strTempTable, $rowFields);
}
$this->expClearVolFields($rowFields);
}
$this->expClearShiftFields($rowFields);
} else {
$this->writeTempScheduleExportRow($strTempTable, $rowFields);
}
}
$this->expClearDateFields($rowFields);
} else {
$this->writeTempScheduleExportRow($strTempTable, $rowFields);
}
}
$sqlStr = "SELECT\n texp_lEventID AS `event ID`,\n texp_strEventName AS `Event Name`,\n texp_strDescription AS `Event Description`,\n DATE_FORMAT(texp_dteEventStartDate, {$strDateFormat}) AS `Event Start Date`,\n DATE_FORMAT(texp_dteEventEndDate, {$strDateFormat}) AS `Event End Date`,\n texp_strLocation AS `Event Location`,\n texp_strContact AS `Event Contact`,\n texp_strPhone AS `Contact Phone`,\n texp_strEmail AS `Contact Email`,\n texp_strWebSite AS `Event Web Site`,\n texp_lDateID AS `event date ID`,\n DATE_FORMAT(texp_dteEventDate, {$strDateFormat}) AS `Event Date`,\n DATE_FORMAT(texp_dteEventDate, '%W') AS `Event Day of Week`,\n texp_lShiftID AS `shift ID`,\n texp_strShiftName AS `Shift Name`,\n texp_strShiftDescription AS `Shift Description`,\n DATE_FORMAT(texp_dteShiftStartTime, '%l:%i %p') AS `Shift Start Time`,\n texp_enumDuration AS `Duration`,\n texp_lNumVolsNeeded AS `Num. Vols Needed`,\n texp_lVolID AS `volunteer ID`,\n texp_lPeopleID AS `people ID`,\n texp_strFName AS `Vol. First Name`,\n texp_strLName AS `Vol. Last Name`,\n texp_strAddr1 AS `Vol. Address 1`,\n texp_strAddr2 AS `Vol. Address 2`,\n texp_strCity AS `Vol. City`,\n texp_strState AS `Vol. {$gclsChapterVoc->vocState}`,\n texp_strCountry AS `Vol. Country`,\n texp_strZip AS `Vol. {$gclsChapterVoc->vocZip}`,\n texp_strVolPhone AS `Vol. Phone`,\n texp_strCell AS `Vol. Cell`,\n texp_strVolEmail AS `Vol. Email`\n\n FROM {$strTempTable}\n WHERE 1\n ORDER BY texp_lKeyID;";
$query = $this->db->query($sqlStr);
return $this->dbutil->csv_from_result($query);
}
function setGiftsToDeposit($lDepositID)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
if (!bTestForURLHack('showFinancials')) {
return;
}
$displayData = array();
// models/helpers
$this->load->model('donations/mdonations', 'clsGifts');
$this->load->model('financials/mdeposits', 'clsDeposits');
$this->load->model('admin/madmin_aco', 'clsACO');
$params = array('enumStyle' => 'enpRptC');
$this->load->library('generic_rpt', $params);
// load deposit
$this->clsDeposits->strWhereExtra = " AND dl_lKeyID={$lDepositID} ";
$this->clsDeposits->loadDepositReports();
$displayData['deposit'] = $deposit =& $this->clsDeposits->deposits[0];
// load qualifying donations
$this->clsGifts->sqlExtraWhere = " AND gi_lACOID={$deposit->lACOID}\n AND gi_lDepositLogID IS NULL\n AND NOT gi_bGIK\n AND gi_dteDonation BETWEEN " . strPrepDate($deposit->dteStart) . ' AND ' . strPrepDateTime($deposit->dteEnd) . ' ';
$this->clsGifts->sqlExtraSort = ' ORDER BY listPayType.lgen_strListItem, gi_dteDonation, gi_curAmnt, gi_lKeyID ';
$this->clsGifts->loadGifts();
$displayData['gifts'] =& $this->clsGifts->gifts;
$displayData['lNumGifts'] = $this->clsGifts->lNumGifts;
$displayData['lDepositID'] = $lDepositID;
//--------------------------
// breadcrumbs
//--------------------------
$displayData['contextSummary'] = $this->clsDeposits->depositHTMLSummary();
$displayData['pageTitle'] = anchor('main/menu/financials', 'Financials/Grants', 'class="breadcrumb"') . ' | ' . anchor('financials/deposit_log/view', 'Deposit Log', 'class="breadcrumb"') . ' | ' . anchor('financials/deposits_add_edit/addDeposit', 'Add Deposit Report', 'class="breadcrumb"') . ' | Select Donations';
$displayData['title'] = CS_PROGNAME . ' | Financials';
$displayData['nav'] = $this->mnav_brain_jar->navData();
$displayData['mainTemplate'] = 'financials/deposit_gifts_select_view';
$this->load->vars($displayData);
$this->load->view('template');
}
private function sqlCommonTestLogEntry(&$testInfo)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
global $glUserID;
return '
cptl_dtePreTest = ' . strPrepDate($testInfo->dtePreTest) . ',
cptl_dtePostTest = ' . strPrepDate($testInfo->dtePostTest) . ",\n cptl_lLastUpdateID = {$glUserID},\n cptl_dteLastUpdate = NOW() ";
}
function bPledgeFulfilledByDate($pDate, $lPledgeID, $lACOID, &$lGiftID)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
$lGiftID = null;
$sqlStr = "SELECT gi_lKeyID\n FROM gifts\n WHERE NOT gi_bRetired\n AND gi_lPledgeID = {$lPledgeID}\n AND gi_lACOID = {$lACOID}\n AND gi_dteDonation = " . strPrepDate($pDate) . ';';
$query = $this->db->query($sqlStr);
if ($query->num_rows() > 0) {
$row = $query->row();
$lGiftID = $row->gi_lKeyID;
return true;
} else {
return false;
}
}
function strVolHoursDetailReportPage(&$sRpt, $bReport, $lStartRec, $lRecsPerPage, $lVolID, $lEventID)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
global $genumDateFormat;
$cEvent = new mvol_events();
$cEvent->loadEventsViaEID($lEventID);
$strEventName = $cEvent->events[0]->strEventName;
$dteEventStart = $cEvent->events[0]->dteEventStart;
$cVol = new mvol();
$cVol->loadVolRecsViaVolID($lVolID, true);
$strName = htmlspecialchars($cVol->volRecs[0]->strLName . ', ' . $cVol->volRecs[0]->strFName);
if ($bReport) {
$strOut = $this->strVolHrsDetailLabel($sRpt, $lEventID, $strEventName, $lVolID, $strName);
$strLimit = " LIMIT {$lStartRec}, {$lRecsPerPage} ";
} else {
$strExport = '';
$strLimit = '';
}
$sqlStr = 'SELECT
vsa_dHoursWorked, vs_strShiftName, vs_enumDuration,
ved_lKeyID, ved_dteEvent,
TIME_FORMAT(vs_dteShiftStartTime, \'%l:%i %p\') AS dteStartTime
FROM vol_events_dates_shifts_assign
INNER JOIN vol_events_dates_shifts ON vsa_lEventDateShiftID = vs_lKeyID
INNER JOIN vol_events_dates ON vs_lEventDateID = ved_lKeyID
WHERE NOT vsa_bRetired
AND NOT vs_bRetired
AND (ved_dteEvent BETWEEN ' . strPrepDate($sRpt->dteStart) . ' AND ' . strPrepDateTime($sRpt->dteEnd) . " )\n AND vsa_lVolID={$lVolID}\n AND vsa_dHoursWorked > 0\n AND ved_lVolEventID={$lEventID}\n ORDER BY ved_dteEvent, vs_dteShiftStartTime\n {$strLimit};";
$query = $this->db->query($sqlStr);
$lNumRows = $query->num_rows();
if ($lNumRows == 0) {
return $strOut . '<br><br><i>There are no records that match your search criteria.</i>';
}
if ($bReport) {
$strOut .= $this->strVolHoursDetailRptHTML($query, $sRpt->reportID);
return $strOut;
} else {
$strExport = $this->strVolHoursDetailRptExport($query, $sRpt->reportID);
return $strExport;
}
}
private function sqlCommonGroupParent()
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
global $glUserID;
$clsList = $this->arrGroupList[0];
return 'gp_strGroupName = ' . strPrepStr($clsList->strGroupName) . ',
gp_bTempGroup = ' . ($clsList->bTempGroup ? '1' : '0') . ',
gp_bGeneric1 = ' . ($clsList->gp_bGeneric1 ? '1' : '0') . ',
gp_lGeneric1 = ' . (is_null($clsList->gp_lGeneric1) ? 'null' : (int) $clsList->lGeneric1) . ',
gp_dteExpire = ' . strPrepDate($clsList->dteExpire) . ',
gp_strNotes = ' . strPrepStr($clsList->strNotes) . ",\n gp_lLastUpdateID = {$glUserID} ";
}
function lAddNewDeposit()
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
global $glUserID;
$deposit =& $this->deposits[0];
$sqlStr = 'INSERT INTO deposit_log
SET ' . $this->strSqlCommonAddEdit() . ",\n dl_lACOID = {$deposit->lACOID},\n dl_lOriginID = {$glUserID},\n dl_dteStart = " . strPrepDate($deposit->dteStart) . ',
dl_dteEnd = ' . strPrepDateTime($deposit->dteEnd) . ',
dl_dteOrigin = NOW(),
dl_bRetired = 0;';
$this->db->query($sqlStr);
return $this->db->insert_id();
}
function lNumClientsInAgeRange($bActive, $lAgeStart, $lAgeEnd)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
global $gdteNow;
$mysqlDteBase = str_replace("'", '', strPrepDate($gdteNow));
dateRangeYears($mysqlDteBase, $lAgeStart, $lAgeEnd, $mysqlStartDate, $mysqlEndDate);
if ($bActive) {
$strInnerExtra = '
INNER JOIN client_status ON csh_lClientID = cr_lKeyID
INNER JOIN lists_client_status_entries ON csh_lStatusID = cst_lKeyID
';
$strSubQuery = '
-- ---------------------------------------
-- subquery to find most current status
-- ---------------------------------------
AND csh_lKeyID=(SELECT csh_lKeyID
FROM client_status
WHERE csh_lClientID=cr_lKeyID
AND NOT csh_bRetired
ORDER BY csh_dteStatusDate DESC, csh_lKeyID DESC
LIMIT 0,1)
';
$strExtraClientWhere = ' AND cst_bShowInDir ';
} else {
$strInnerExtra = $strSubQuery = '';
$strExtraClientWhere = '';
}
$sqlStr = "SELECT COUNT(*) AS lNumClients\n FROM client_records\n {$strInnerExtra}\n WHERE 1\n AND NOT cr_bRetired\n {$strSubQuery}\n {$strExtraClientWhere}\n AND cr_dteBirth > " . strPrepStr($mysqlStartDate) . '
AND cr_dteBirth <=' . strPrepStr($mysqlEndDate) . ';';
$query = $this->db->query($sqlStr);
$row = $query->row();
return $row->lNumClients;
}
public function lInsertReminderDate($lRemID, $dteStart, $dteEnd)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
$sqlStr = "INSERT INTO reminders_dates\n SET\n rd_lRemID = {$lRemID},\n rd_dteDisplayDate = " . strPrepDate($dteStart) . ',
rd_dteEndDisplayDate = ' . strPrepDate($dteEnd) . ';';
$result = mysql_query($sqlStr);
if (bSQLError('SQL error on line ' . __LINE__ . ', file ' . __FILE__ . ', function ' . __FUNCTION__, $sqlStr)) {
screamForHelp('Unexpected SQL error');
}
return mysql_insert_id();
}
public function clientXfer($lClientID, $lOldLocID, $lOldStatCatID, $lOldVocID, $lNewLocID, $lNewStatCatID, $lNewVocID, $dteEDate)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
global $glUserID;
$sqlStr = "INSERT INTO client_xfers\n SET\n cx_lClientID = {$lClientID},\n cx_lOldLocID = {$lOldLocID},\n cx_lOldStatCatID = {$lOldStatCatID},\n cx_lOldVocID = {$lOldVocID},\n cx_lNewLocID = {$lNewLocID},\n cx_lNewStatCatID = {$lNewStatCatID},\n cx_lNewVocID = {$lNewVocID},\n cx_lLastUpdateID = {$glUserID},\n cl_dteEffectiveDate = " . strPrepDate($dteEDate) . ',
cx_dteLastUpdate = NOW();';
$this->db->query($sqlStr);
$sqlStr = "UPDATE client_records\n SET\n cr_lLocationID = {$lNewLocID},\n cr_lStatusCatID = {$lNewStatCatID},\n cr_lVocID = {$lNewVocID},\n cr_lLastUpdateID = {$glUserID},\n cr_dteLastUpdate = NOW()\n WHERE cr_lKeyID={$lClientID};";
$this->db->query($sqlStr);
}
function strClientAgeReport(&$sRpt, $lStartRec, $lRecsPerPage)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
global $gdteNow;
$cClient = new mclients();
$strOut = '';
$mysqlDteBase = str_replace("'", '', strPrepDate($gdteNow));
dateRangeYears($mysqlDteBase, $sRpt->lStartAge, $sRpt->lEndAge, $mysqlStartDate, $mysqlEndDate);
$cClient->strClientLimit = " LIMIT {$lStartRec}, {$lRecsPerPage} ";
$cClient->strExtraClientWhere = "\n AND cst_bShowInDir\n AND cr_dteBirth > " . strPrepStr($mysqlStartDate) . '
AND cr_dteBirth <=' . strPrepStr($mysqlEndDate) . ' ';
$cClient->loadClientsGeneric();
if ($cClient->lNumClients == 0) {
return '<i><br>There are no clients who meet your search criteria.</i>';
} else {
return $this->strBasicClientRpt($cClient, 'Client Age: ' . htmlspecialchars($sRpt->label));
}
}
function addTSEntry($lTSLogID, $dteEntry, &$entry)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
global $glUserID;
$sqlStr = 'INSERT INTO staff_ts_log_entry
SET
tsle_dteLogEntry = ' . strPrepDate($dteEntry) . ',
tsle_tmTimeIn = ' . strPrepStr(strMinutesToMySQLTime($entry->timeIn)) . ',
tsle_tmTimeOut = ' . strPrepStr(strMinutesToMySQLTime($entry->timeOut)) . ',
tsle_strNotes = ' . strPrepStr($entry->notes) . ',
tsle_lLocationID = ' . $entry->location . ",\n tsle_lTSLogID = {$lTSLogID},\n tsle_lOriginID = {$glUserID},\n tsle_lLastUpdateID = {$glUserID},\n tsle_dteOrigin = NOW(),\n tsle_dteLastUpdate = NOW();";
$query = $this->db->query($sqlStr);
return (int) $this->db->insert_id();
}
