<?php

// refresh report status
refreshReportStatus('extractMainDataFromDb', 'composingSQL', 0);

///////////////////////////////////////////////////////////////////////////////////////////////////
// CREATE SQL FOR COPYING SUPPLIERS ///////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////

// ASSOCIATE FOR EACH COLUMN THE FIELD FROM THE ORIGINAL DATA /////////////////////////////////////
$columnsValuesList= [];
foreach($tableColumns as $thisCol=>$colDet) {

	switch ($thisCol) {
		case 'module':				$columnsValuesList[$thisCol]='`visits`.`module`';break;
		case 'action':				$columnsValuesList[$thisCol]='`visits`.`action`';break;
		case 'entityType':			$columnsValuesList[$thisCol]='`visits`.`entityType`';break;
		case 'entityOperation':		$columnsValuesList[$thisCol]='`visits`.`entityOperation`';break;
		case 'idEntity':			$columnsValuesList[$thisCol]='`visits`.`idEntity`';break;
		case 'extraData':			$columnsValuesList[$thisCol]='`visits`.`extraData`';break;
		case 'extraDataList':		$columnsValuesList[$thisCol]='`visits`.`extraData`';break;
		case 'timestamp':			$columnsValuesList[$thisCol]='`visits`.`timestamp`';break;
		case 'datetime':			$columnsValuesList[$thisCol]='FROM_UNIXTIME(`visits`.`timestamp`)';break;

		case 'idUser':				$columnsValuesList[$thisCol]='`visits`.`idUser`';break;
		case 'userName':			$columnsValuesList[$thisCol]="CONCAT(`users`.`lastName`,' ',`users`.`firstName`)";break;
		case 'userOfficeId':		$columnsValuesList[$thisCol]='`users`.`idOffice`';break;
		case 'userDepartmentId':	$columnsValuesList[$thisCol]='`users`.`idDepartment`';break;
		case 'activeUser':			$columnsValuesList[$thisCol]='!`users`.`deleted`';break;

	}

}

// get interval month tables list
$tablesList = \App\Utils\Db\TablesUtilsCached::getInstance()->getExistingMonthTablesByInterval($DBTUserTraces, $reportFilters['timestamp']['startDateSql'], $reportFilters['timestamp']['endDateSql']);

// GENERATE SQL FOR COPYING CUSTOMERS /////////////////////////////////////////////////////////////
$index=0;$totalBulkResults=count($tablesList);
if($tablesList){
	foreach($tablesList as $tableName){

		$index++;

		$populateTableSQL="INSERT INTO `$reportTable` ( ";

		// add column names
		$idx=0;
		foreach($columnsValuesList as $colName=>$colValue) {
			$idx++;
			if ($idx>1) $populateTableSQL.=", ";
			$populateTableSQL.="`$colName`";
		}
		$populateTableSQL.=") ";

		// add SELECT clause
		$populateTableSQL.=" SELECT ";

		// add columns values
		$idx=0;
		foreach($columnsValuesList as $colName=>$colValue) {
			$idx++;
			if ($idx>1) $populateTableSQL.=", ";
			$populateTableSQL.="$colValue";
		}


		// add FROM clause
		$populateTableSQL.="
			FROM `$tableName` AS visits
			LEFT JOIN `$DBTUsers` AS users ON `visits`.`idUser`=`users`.`id`
		";

		// APPLY FILTERS //////////////////////////////////////////////////////////////////////////
		$populateTableSQL.=" WHERE 1 ";

		// FILTER: addTime
		if ($reportFilters['timestamp']['startDate'] and $reportFilters['timestamp']['endDate']) {
			$populateTableSQL.=" AND (visits.timestamp BETWEEN '".$reportFilters['timestamp']['startDateStamp']."' AND '".$reportFilters['timestamp']['endDateStamp']."')";
		} else {
			if ($reportFilters['timestamp']['startDate']) $populateTableSQL.=" AND visits.timestamp>='".$reportFilters['timestamp']['startDateStamp']."'";
			if ($reportFilters['timestamp']['endDate']) $populateTableSQL.=" AND visits.timestamp<='".$reportFilters['timestamp']['endDateStamp']."'";
		}

		// FILTER: office/user
		if ($reportFilters['user']['idUser']) {
			$populateTableSQL.=" AND visits.idUser='".$reportFilters['user']['idUser']."'";
		} elseif ($reportFilters['user']['idOffice']) {
			$populateTableSQL.=" AND users.idOffice='".$reportFilters['user']['idOffice']."'";
		}

		// FILTER: text filters (entity type and entity id)
		if ($reportFilters['action'])		 	$populateTableSQL.=" AND visits.action='".$reportFilters['action']."'";
		if ($reportFilters['entityType'])	 	$populateTableSQL.=" AND visits.entityType='".$reportFilters['entityType']."'";
		if ($reportFilters['entityOperation']) 	$populateTableSQL.=" AND visits.entityOperation='".$reportFilters['entityOperation']."'";
		if ($reportFilters['idEntity']) 		$populateTableSQL.=" AND visits.idEntity='".$reportFilters['idEntity']."'";

		// ORDER BY
		$populateTableSQL.=" ORDER BY visits.timestamp ASC";

		// EXECUTE SQL ////////////////////////////////////////////////////////////////////////////////////
		$populateTableEx=$db->Execute($populateTableSQL);
		if ($db->ErrorNo()) killReport("Could not run report SQL: ".$populateTableSQL);

		// refresh report status
		refreshReportStatus('extractMainDataFromDb', 'runningMainSQL', $index/$totalBulkResults*100);

	}

}

?>