<?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 'id':				$columnsValuesList[$thisCol]='repoGenHis.id';break;
		case 'idUser':			$columnsValuesList[$thisCol]='repoGenHis.idUser';break;

		//case 'userName':		$columnsValuesList[$thisCol]='';break;
		//case 'userOfficeId':	$columnsValuesList[$thisCol]='';break;
		//case 'userOfficeName':$columnsValuesList[$thisCol]='';break;
		//case 'idDepartment':	$columnsValuesList[$thisCol]='';break;
		//case 'departmentName':$columnsValuesList[$thisCol]='';break;

		case 'idReport':		$columnsValuesList[$thisCol]='repoGenHis.idReport';break;

		case 'idCategory':		$columnsValuesList[$thisCol]='reports.idCategory';break;

		//case 'categoryName':	$columnsValuesList[$thisCol]='';break;
		//case 'categoryDescription':	$columnsValuesList[$thisCol]='';break;

		case 'sourceCode':		$columnsValuesList[$thisCol]='reports.source';break;
		//case 'sourceName':	$columnsValuesList[$thisCol]='';break;
		case 'name':			$columnsValuesList[$thisCol]='reports.name';break;
		case 'description':		$columnsValuesList[$thisCol]='reports.description';break;
		case 'platformCode':	$columnsValuesList[$thisCol]='reports.platform';break;
		//case 'platformName':	$columnsValuesList[$thisCol]='';break;
		case 'formatCode':		$columnsValuesList[$thisCol]='reports.format';break;
		//case 'formatName':		$columnsValuesList[$thisCol]='';break;
		case 'extension':		$columnsValuesList[$thisCol]='reports.extension';break;

		case 'startTime':		$columnsValuesList[$thisCol]='IF(repoGenHis.startTime, FROM_UNIXTIME(repoGenHis.startTime), NULL)';break;
		case 'endTime':			$columnsValuesList[$thisCol]='IF(repoGenHis.endTime, FROM_UNIXTIME(repoGenHis.endTime), NULL)';break;
		case 'executionTime':	$columnsValuesList[$thisCol]='repoGenHis.endTime-repoGenHis.startTime';break;
		case 'expireTime':		$columnsValuesList[$thisCol]='IF(repoGenHis.expireTime, FROM_UNIXTIME(repoGenHis.expireTime), NULL)';break;
		case 'addTime':			$columnsValuesList[$thisCol]='FROM_UNIXTIME(repoGenHis.addTime)';break;
		/* commented colums are updated in reportData.php */
	}

}

// GENERATE SQL FOR COPYING CUSTOMERS /////////////////////////////////////////////////////////////
$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 `$DBTReportsGenHistory` AS repoGenHis
";

if(
	$columnsValuesList['idCategory'] || $columnsValuesList['sourceCode'] || $columnsValuesList['name'] ||
	$columnsValuesList['description'] || $columnsValuesList['platformCode'] || $columnsValuesList['formatCode'] ||
	$columnsValuesList['extension']
){
	$populateTableSQL.="
		LEFT JOIN `$DBTReports` AS reports ON
			repoGenHis.`idReport`=reports.`id`
	";
}

if($reportFilters['idOffice'] && !$reportFilters['idUser']){
	$populateTableSQL.="
		LEFT JOIN `$DBTUsers` AS users ON
			repoGenHis.`idUser`=users.`id`
			AND users.`idOffice`='{$reportFilters['idOffice']}'
	";
}

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

// FILTER: addTime
if ( $reportFilters['addTime']['startDate'] )
	$populateTableSQL.=" AND repoGenHis.addTime>='".$reportFilters['addTime']['startDateStamp']."'";
if ( $reportFilters['addTime']['endDate'] )
	$populateTableSQL.=" AND repoGenHis.addTime<='".$reportFilters['addTime']['endDateStamp']."'";
// FILTER: user/userOffice
if($reportFilters['idUser'])
	$populateTableSQL.=" AND repoGenHis.idUser='{$reportFilters['idUser']}'";
elseif($reportFilters['idOffice'])
	$populateTableSQL.=" AND users.`id` IS NOT NULL";



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

// EXECUTE SQL ////////////////////////////////////////////////////////////////////////////////////
$populateTableEx=$db->Execute($populateTableSQL);

// check sql
if ($db->ErrorNo()) {
	killReport("Could not run report SQL: ".$populateTableSQL);
}

?>