<?php

///////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////
// LOOP THROUGH EACH CUSTOM FILTER AND APPLY ALL OF THEM IN ORDER /////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////
$customFilterIndex=0;
if (is_array($reportCustomFilters)) {
	$customFilterCount=count($reportCustomFilters);
	foreach($reportCustomFilters as $column=>$filterDetails) {

		// refresh report status
		refreshReportStatus('customFilters', 'applyCustomFilters', $customFilterIndex/$customFilterCount*100);
		$customFilterIndex++;

		///////////////////////////////////////////////////////////////////////////////////////////
		// APPLY FILTER ON COLUMN $column /////////////////////////////////////////////////////////
		///////////////////////////////////////////////////////////////////////////////////////////

		// reset the sql for filtering
		$applyCustomFilterReportSQL='';

		$columnType=$columnsConfig[$column]['type'];
		if (is_numeric($column)) {
			$columnType='string';
			$column='CUSTOM_COLUMN_'.$column;
		}

		switch ($columnType) {

			// 'boolean' //////////////////////////////////////////////////////////////////////////
			case 'boolean': {
				// if only one value chosen (either 'true' or 'false')
				if ( $filterDetails['boolean'] and count($filterDetails['boolean'])==1) {
					if ($filterDetails['boolean'][0])
						$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`!=0";
					if ($filterDetails['boolean'][1])
						$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`=0";
				}
				break;
			}

			// 'price', 'numeric' /////////////////////////////////////////////////////////////////
			case 'price': case 'numeric': {

				// make it compatible with previous versions (no operation was set)
				if (!$filterDetails['operation']) {
					if ($filterDetails['valuesList']) $filterDetails['operation']='values';
					else $filterDetails['operation']='inclClosedInterval';
				}

				// switch by operation
				switch ($filterDetails['operation']) {
					// operation = 'inclClosedInterval --------------------------------------------
					case 'inclClosedInterval':{
						if ( is_numeric($filterDetails['minValue']) and is_numeric($filterDetails['maxValue']) ) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`<'".$filterDetails['minValue']."' OR `".$column."`>'".$filterDetails['maxValue']."'";
						} elseif ( is_numeric($filterDetails['minValue']) ) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`<'".$filterDetails['minValue']."'";
						} elseif ( is_numeric($filterDetails['maxValue']) ) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`>'".$filterDetails['maxValue']."'";
						}
						break;
					}
					// operation = 'inclOpenInterval ----------------------------------------------
					case 'inclOpenInterval':{
						if ( is_numeric($filterDetails['minValue']) and is_numeric($filterDetails['maxValue']) ) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`<='".$filterDetails['minValue']."' OR `".$column."`>='".$filterDetails['maxValue']."'";
						} elseif ( is_numeric($filterDetails['minValue']) ) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`<='".$filterDetails['minValue']."'";
						} elseif ( is_numeric($filterDetails['maxValue']) ) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`>='".$filterDetails['maxValue']."'";
						}
						break;
					}
					// operation = 'exclClosedInterval --------------------------------------------
					case 'exclClosedInterval':{
						if ( is_numeric($filterDetails['minValue']) and is_numeric($filterDetails['maxValue']) ) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`>'".$filterDetails['minValue']."' AND `".$column."`<'".$filterDetails['maxValue']."'";
						} elseif ( is_numeric($filterDetails['minValue']) ) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`>'".$filterDetails['minValue']."'";
						} elseif ( is_numeric($filterDetails['maxValue']) ) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`<'".$filterDetails['maxValue']."'";
						}
						break;
					}
					// operation = 'exclOpenInterval ----------------------------------------------
					case 'exclOpenInterval':{
						if ( is_numeric($filterDetails['minValue']) and is_numeric($filterDetails['maxValue']) ) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`>='".$filterDetails['minValue']."' AND `".$column."`<='".$filterDetails['maxValue']."'";
						} elseif ( is_numeric($filterDetails['minValue']) ) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`>='".$filterDetails['minValue']."'";
						} elseif ( is_numeric($filterDetails['maxValue']) ) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`<='".$filterDetails['maxValue']."'";
						}
						break;
					}
					// operation = 'equal ---------------------------------------------------------
					case 'equals':{
						if ( is_numeric($filterDetails['value']) ) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`!='".$filterDetails['value']."'";
						}
						break;
					}
					// operation = 'notEqual ------------------------------------------------------
					case 'notEquals':{
						if ( is_numeric($filterDetails['value']) ) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`='".$filterDetails['value']."'";
						}
						break;
					}
					// operation = 'values --------------------------------------------------------
					case 'values':{
						$filteringValues= [];
						if ($filterDetails['valuesList']) {
							foreach($filterDetails['valuesList'] as $elem) {
								if ($elem['checked']) {
									if ($elem['element']=='_') {
										$filteringValues[]="''";
									} else {
										$filteringValues[]="'".$elem['element']."'";
									}
								}
							}
							if ($filteringValues) {
								$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."` NOT IN (".implode(", ", $filteringValues).")";
							}
						}
						break;
					}
					// operation = 'differentFromValues -------------------------------------------
					case 'differentFromValues':{
						$filteringValues = [];
						if ($filterDetails['valuesList']) {
							foreach ($filterDetails['valuesList'] as $elem) {
								if ($elem['checked']) {
									if ($elem['element'] == '_') {
										$filteringValues[]="''";
									}
									else {
										$filteringValues[]="'".$elem['element']."'";
									}
								}
							}
							if ($filteringValues) {
								$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."` IN (".implode(", ", $filteringValues).")";
							}
						}
						break;
					}
				}

				break;

			}

			// 'percent' //////////////////////////////////////////////////////////////////////////
			case 'percent': {
				// both limits
				if (is_numeric($filterDetails['minValue']) and is_numeric($filterDetails['maxValue'])) {
					$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`<'".($filterDetails['minValue']/100)."' OR `".$column."`>'".($filterDetails['maxValue']/100)."'";
				} else {
					// only min limit
					if (is_numeric($filterDetails['minValue'])) {
						$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`<'".($filterDetails['minValue']/100)."'";
					}
					// only max limit
					if (is_numeric($filterDetails['maxValue'])) {
						$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`>'".($filterDetails['maxValue']/100)."'";
					}
				}
				break;
			}

			// 'string' ///////////////////////////////////////////////////////////////////////////
			case 'string': {

				// FILTER: checkboxes -------------------------------------------------------------
				if ($filterDetails['operation'] == "values") {
					$filteringValues = [];
					if ($filterDetails['valuesList']) {
						foreach ($filterDetails['valuesList'] as $elem) {
							if ($elem['checked']) {
								if ($elem['element'] == '_') {
									$filteringValues[] = "`$column` NOT LIKE ''";
								} else {
									$filteringValues[] = "`$column` NOT LIKE '".$elem['element']."'";
								}
							}
						}
						if ($filteringValues) {
							$applyCustomFilterReportSQL = "DELETE FROM `$reportTable` WHERE ".implode(" AND ", $filteringValues);
						}
					}
				}
				elseif ($filterDetails['operation'] == "differentFromValues") {
					$filteringValues = [];
					if ($filterDetails['valuesList']) {
						foreach ($filterDetails['valuesList'] as $elem) {
							if ($elem['checked']) {
								if ($elem['element'] == '_') {
									$filteringValues[] = "`$column` LIKE ''";
								} else {
									$filteringValues[] = "`$column` LIKE '".$elem['element']."'";
								}
							}
						}
						if ($filteringValues) {
							$applyCustomFilterReportSQL = "DELETE FROM `$reportTable` WHERE ".implode(" OR ", $filteringValues);
						}
					}
				}
				// FILTER: one value --------------------------------------------------------------
				else {
					// if nothing inputed in the text filter - skip this filter
					if ( !strlen($filterDetails['text']) ) break;
					// replace '_' with '' [blank]
					$textFilter=$filterDetails['text'];
					if ($textFilter=='_') $textFilter='';
					// attach each possible operator
					if ($filterDetails['operation']=='equals') $applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."` NOT LIKE '".$textFilter."'";
					if ($filterDetails['operation']=='starts' and $textFilter) $applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."` NOT LIKE '".$textFilter."%'";
					if ($filterDetails['operation']=='contains' and $textFilter) $applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."` NOT LIKE '%".$textFilter."%'";
					if ($filterDetails['operation']=='notEquals') $applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."` LIKE '".$textFilter."'";



				}

				break;

			}

			// 'date' / 'yearMonth' / 'year' //////////////////////////////////////////////////////
			case 'date': case 'yearMonth': case 'year': {

				// DATE (ANNIVERSARY MODE) --------------------------------------------------------
				if ($columnType=="date" and $filterDetails['searchMode']=="anniversary") { // TODO: we will drop the use of anniversary - it is not used (tested all customers on Feb 2024)

					// IF BOTH MIN AND MAX ANNIVERSARY DAYS ARE SET -------------------------------
					if ($filterDetails['minAnniv_day'] and $filterDetails['maxAnniv_day']) {
						// create monthDay strings for both limits
						$startAnniv=dateElementZeroFill($filterDetails['minAnniv_month']).dateElementZeroFill($filterDetails['minAnniv_day']);
						$endAnniv=dateElementZeroFill($filterDetails['maxAnniv_month']).dateElementZeroFill($filterDetails['maxAnniv_day']);
						// check the order of the start/end anniversary dates
						if ($startAnniv<=$endAnniv)
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE DATE_FORMAT(`".$column."`, '%m%d')<'".$startAnniv."' OR DATE_FORMAT(`".$column."`, '%m%d')>'".$endAnniv."'";
						else
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE (DATE_FORMAT(`".$column."`, '%m%d')<'".$startAnniv."' AND DATE_FORMAT(`".$column."`, '%m%d')>'".$endAnniv."') OR `".$column."`='0000-00-00'";

					}
					// ONLY START ANNIVERSARY DATE IS SET -----------------------------------------
					elseif ($filterDetails['minAnniv_day']) {
						$startAnniv=dateElementZeroFill($filterDetails['minAnniv_month']).dateElementZeroFill($filterDetails['minAnniv_day']);
						$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE DATE_FORMAT(`".$column."`, '%m%d')<'".$startAnniv."' OR `".$column."`='0000-00-00'";
					}
					// ONLY END ANNIVERSARY DATE IS SET -------------------------------------------
					elseif ($filterDetails['maxAnniv_day']) {
						$endAnniv=dateElementZeroFill($filterDetails['maxAnniv_month']).dateElementZeroFill($filterDetails['maxAnniv_day']);
						$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE DATE_FORMAT(`".$column."`, '%m%d')>'".$endAnniv."' OR `".$column."`='0000-00-00'";
					}

			 	}
			 	// DATE (INTERVAL), YEAR MONTH, YEAR ----------------------------------------------
			 	else {

			 		// start and end date in mysql format
					$startDateMysql=standard2mysqlDateConversion($filterDetails['startDate']);
					$endDateMysql=standard2mysqlDateConversion($filterDetails['endDate']);

					// filter start/end date (in case of yearMonth)
					if ($columnType=="yearMonth") {
						// edit start date
						if ($startDateMysql) {
							$startDateMysql[8]=0;$startDateMysql[9]=1;
						}
						// edit end date
						if ($endDateMysql) {
							$endDateMysql[8]=3;$endDateMysql[9]=1;
						}
					}
					// filter start/end date (in case of year)
					if ($columnType=="year") {
						// edit start date
						if ($startDateMysql) {
							$startDateMysql[8]=0;$startDateMysql[9]=1;
							$startDateMysql[5]=0;$startDateMysql[6]=1;
						}
						// edit end date
						if ($endDateMysql) {
							$endDateMysql[8]=3;$endDateMysql[9]=1;
							$endDateMysql[5]=1;$endDateMysql[6]=2;
						}
					}

					// --- APPLY LIMITS ---

					// if both limits
					if ($startDateMysql and $endDateMysql) {
						$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE (`{$column}`<'".$startDateMysql."' OR `".$column."`>'".$endDateMysql."') OR `{$column}` IS NULL";
					} else {
						// only min limit
						if ($startDateMysql) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `{$column}`<'".$startDateMysql."' OR `{$column}` IS NULL";
						}
						// only max limit
						if ($endDateMysql) {
							$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `{$column}`>'".$endDateMysql."' AND `{$column}` IS NOT NULL";
						}
					}

			 	}

				break;
			}


			// 'datetime' /////////////////////////////////////////////////////////////////////////
			case 'datetime': {

				// startTime
				$startTime=0;
				$startDateMysql=standard2mysqlDateConversion($filterDetails['startDate']);
				if ($startDateMysql) {
					$startTime = $startDateMysql.' 00:00:00';
				}

				// end time
				$endTime=0;
				$endDateMysql=standard2mysqlDateConversion($filterDetails['endDate']);
				if ($endDateMysql) {
					$endTime=$endDateMysql.' 23:59:59';
				}

				// if both limits
				if ($startTime and $endTime) {
					$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`<'".$startTime."' OR `".$column."`>'".$endTime."' OR `".$column."` IS NULL";
				} else {
					// only min limit
					if ($startTime) {
						$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`<'".$startTime."' OR `".$column."` IS NULL";
					}
					// only max limit
					if ($endTime) {
						$applyCustomFilterReportSQL="DELETE FROM `$reportTable` WHERE `".$column."`>'".$endTime."'";
					}
				}

				break;
			}

		}

		// RUN SQL FOR FILTERING //////////////////////////////////////////////////////////////////
		if ($applyCustomFilterReportSQL) {
			$applyCustomFilterReportEx=$db->Execute($applyCustomFilterReportSQL);
			// kill report if sql fails
			if ($db->ErrorNo()) killReport("Could not apply custom filter. SQL: ".$applyCustomFilterReportSQL);
		}

	}
}


