/**
* Convert a date/time string to Excel time
*
* @param string $dateValue Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
* @return float|FALSE Excel date/time serial value
*/
public static function stringToExcel($dateValue = '')
{
if (strlen($dateValue) < 2) {
return false;
}
if (!preg_match('/^(\\d{1,4}[ \\.\\/\\-][A-Z]{3,9}([ \\.\\/\\-]\\d{1,4})?|[A-Z]{3,9}[ \\.\\/\\-]\\d{1,4}([ \\.\\/\\-]\\d{1,4})?|\\d{1,4}[ \\.\\/\\-]\\d{1,4}([ \\.\\/\\-]\\d{1,4})?)( \\d{1,2}:\\d{1,2}(:\\d{1,2})?)?$/iu', $dateValue)) {
return false;
}
$dateValueNew = \PHPExcel\Calculation\DateTime::DATEVALUE($dateValue);
if ($dateValueNew === \PHPExcel\Calculation\Functions::VALUE()) {
return false;
}
if (strpos($dateValue, ':') !== false) {
$timeValue = \PHPExcel\Calculation\DateTime::TIMEVALUE($dateValue);
if ($timeValue === \PHPExcel\Calculation\Functions::VALUE()) {
return false;
}
$dateValueNew += $timeValue;
}
return $dateValueNew;
}
/**
* Convert a dynamic rule daterange to a custom filter range expression for ease of calculation
*
* @param string $dynamicRuleType
* @param AutoFilter\Column &$filterColumn
* @return mixed[]
*/
private function dynamicFilterDateRange($dynamicRuleType, &$filterColumn)
{
$rDateType = \PHPExcel\Calculation\Functions::getReturnDateType();
\PHPExcel\Calculation\Functions::setReturnDateType(\PHPExcel\Calculation\Functions::RETURNDATE_PHP_NUMERIC);
$val = $maxVal = null;
$ruleValues = array();
$baseDate = \PHPExcel\Calculation\DateTime::DATENOW();
// Calculate start/end dates for the required date range based on current date
switch ($dynamicRuleType) {
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK:
$baseDate = strtotime('-7 days', $baseDate);
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK:
$baseDate = strtotime('-7 days', $baseDate);
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH:
$baseDate = strtotime('-1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH:
$baseDate = strtotime('+1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER:
$baseDate = strtotime('-3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER:
$baseDate = strtotime('+3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR:
$baseDate = strtotime('-1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR:
$baseDate = strtotime('+1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
}
switch ($dynamicRuleType) {
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_TODAY:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW:
$maxVal = (int) \PHPExcel\Shared\Date::PHPtoExcel(strtotime('+1 day', $baseDate));
$val = (int) \PHPExcel\Shared\Date::PHPToExcel($baseDate);
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE:
$maxVal = (int) \PHPExcel\Shared\Date::PHPtoExcel(strtotime('+1 day', $baseDate));
$val = (int) \PHPExcel\Shared\Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR:
$maxVal = (int) \PHPExcel\Shared\Date::PHPToExcel(gmmktime(0, 0, 0, 31, 12, date('Y', $baseDate)));
++$maxVal;
$val = (int) \PHPExcel\Shared\Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER:
$thisMonth = date('m', $baseDate);
$thisQuarter = floor(--$thisMonth / 3);
$maxVal = (int) \PHPExcel\Shared\Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), (1 + $thisQuarter) * 3, date('Y', $baseDate)));
++$maxVal;
$val = (int) \PHPExcel\Shared\Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1 + $thisQuarter * 3, date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH:
$maxVal = (int) \PHPExcel\Shared\Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), date('m', $baseDate), date('Y', $baseDate)));
++$maxVal;
$val = (int) \PHPExcel\Shared\Date::PHPToExcel(gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK:
$dayOfWeek = date('w', $baseDate);
$val = (int) \PHPExcel\Shared\Date::PHPToExcel($baseDate) - $dayOfWeek;
$maxVal = $val + 7;
break;
}
switch ($dynamicRuleType) {
// Adjust Today dates for Yesterday and Tomorrow
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY:
--$maxVal;
--$val;
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW:
++$maxVal;
++$val;
break;
}
// Set the filter column rule attributes ready for writing
$filterColumn->setAttributes(array('val' => $val, 'maxVal' => $maxVal));
// Set the rules for identifying rows for hide/show
$ruleValues[] = array('operator' => AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL, 'value' => $val);
$ruleValues[] = array('operator' => AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN, 'value' => $maxVal);
\PHPExcel\Calculation\Functions::setReturnDateType($rDateType);
return array('method' => 'filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_AND));
}