function strDBValueConvert_DateTime($dteValue)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
if (is_null($dteValue)) {
$strValue = 'null';
} else {
$strValue = strPrepDateTime($dteValue);
}
return $strValue;
}
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 strMonthBetween($lMonth, $lYear)
{
//-------------------------------------------------------------------------
// return a string that represents the timespan encompased by a month
// in mySQL date format
//-------------------------------------------------------------------------
$strStartDate = strPrepDateTime(dteMonthStart($lMonth, $lYear));
$strEndDate = strPrepDateTime(dteMonthEnd($lMonth, $lYear));
return $strStartDate . ' AND ' . $strEndDate;
}
public function sponsorPaymentHistory($bViaSponID)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
$clsACO = new madmin_aco();
if ($bViaSponID) {
if (is_null($this->lSponID)) {
screamForHelp('CLASS NOT INITIALIZED<br></b>error on <b>line: </b>' . __LINE__ . '<br><b>file:</b> ' . __FILE__ . '<br><b>function:</b> ' . __FUNCTION__);
}
$strWhere = " gi_lSponsorID = {$this->lSponID} ";
} else {
if (is_null($this->lFID)) {
screamForHelp('CLASS NOT INITIALIZED<br></b>error on <b>line: </b>' . __LINE__ . '<br><b>file:</b> ' . __FILE__ . '<br><b>function:</b> ' . __FUNCTION__);
}
$strWhere = " gi_lForeignID = {$this->lFID} ";
}
if ($this->bUseDateRange) {
$strWhereDate = ' AND (sd_dteDonation BETWEEN ' . strPrepDate($this->dteStartDate) . '
AND ' . strPrepDateTime($this->dteEndDate) . ') ';
} else {
$strWhereDate = '';
}
$this->paymentHistory = array();
$sqlStr = "SELECT\n gi_lKeyID,\n\n gi_lForeignID, gi_lSponsorID, gi_curAmnt, gi_lACOID,\n gi_dteDonation,\n gi_strCheckNum,\n gi_lPaymentType, listPayType.lgen_strListItem AS strPaymentType,\n gi_bRetired,\n\n gi_lOriginID, gi_lLastUpdateID,\n UNIX_TIMESTAMP(gi_dteOrigin) AS dteOrigin,\n UNIX_TIMESTAMP(gi_dteLastUpdate) AS dteLastUpdate,\n\n aco_strFlag, aco_strName, aco_strCurrencySymbol,\n\n pDon.pe_bBiz AS bDonorBiz, pDon.pe_strFName AS strDonorFName, pDon.pe_strLName AS strDonorLName,\n\n usersC.us_strFirstName AS strCFName, usersC.us_strLastName AS strCLName,\n usersL.us_strFirstName AS strLFName, usersL.us_strLastName AS strLLName\n\n FROM gifts\n INNER JOIN admin_aco ON gi_lACOID = aco_lKeyID\n INNER JOIN people_names AS pDon ON gi_lForeignID = pDon.pe_lKeyID\n\n INNER JOIN admin_users AS usersC ON gi_lOriginID = usersC.us_lKeyID\n INNER JOIN admin_users AS usersL ON gi_lLastUpdateID = usersL.us_lKeyID\n\n LEFT JOIN lists_generic AS listPayType ON gi_lPaymentType = listPayType.lgen_lKeyID\n WHERE {$strWhere} {$strWhereDate} AND NOT gi_bRetired;";
$query = $this->db->query($sqlStr);
$this->lPayTot = $this->lPayHistoryACOCnt = $numRows = $query->num_rows();
if ($numRows == 0) {
$this->payHistory = null;
} else {
$idx = 0;
foreach ($query->result() as $row) {
$this->payHistory[$idx] = new stdClass();
$this->payHistory[$idx]->lKeyID = $row->gi_lKeyID;
$this->payHistory[$idx]->lPayerID = $row->gi_lForeignID;
$this->payHistory[$idx]->lSponsorID = $row->gi_lSponsorID;
$this->payHistory[$idx]->curPayment = $row->gi_curAmnt;
$this->payHistory[$idx]->lACOID = $row->gi_lACOID;
$this->payHistory[$idx]->dtePayment = dteMySQLDate2Unix($row->gi_dteDonation);
$this->payHistory[$idx]->bDonorBiz = $row->bDonorBiz;
$this->payHistory[$idx]->strPayerFName = $row->strDonorFName;
$this->payHistory[$idx]->strPayerLName = $row->strDonorLName;
$this->payHistory[$idx]->strPayerSafeNameFL = htmlspecialchars($row->strDonorFName . ' ' . $row->strDonorLName);
$this->payHistory[$idx]->strACO = $row->aco_strName;
$this->payHistory[$idx]->strCurSymbol = $row->aco_strCurrencySymbol;
$this->payHistory[$idx]->strFlag = $row->aco_strFlag;
$this->payHistory[$idx]->strFlagImage = $clsACO->strFlagImage($row->aco_strFlag, $row->aco_strName);
++$idx;
}
}
}
function strActivelyEnrolledDuringTimeFrameWhere(&$cprog, $dteStart, $dteEnd)
{
//---------------------------------------------------------------------
// 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($dteEnd);
$mdteFirstSec = strPrepDateTime($dteStart);
$strEFNPre = $cprog->strETableFNPrefix;
$strDateStart = $strEFNPre . '_dteStart';
$strDateEnd = $strEFNPre . '_dteEnd';
return " AND ({$strDateStart} <= {$mdteLastSec})\n AND (({$strDateEnd} IS NULL) OR ({$strDateEnd} >= {$mdteFirstSec})) \n";
}
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');
}
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;
}
}
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();
}
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;
}
