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

$reportCopyInfo = new \App\Reports\Db\ManageRecords\Insert($reportTable);
$reportCopyInfo->setMainTable($DBTRd_corporateContracts,	'cont');
$reportCopyInfo->addJoinTable($DBTRd_corporates,       	'corp',        		'cont.idCorporate=corp.id', 		'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_users,             'user',     		'cont.idResponsibleUser=user.id', 	'LEFT');

$cols = [
	'cont_id'				=> 'cont.id',
	'cont_number'			=> 'cont.contractNumber',
	'cont_date'				=> 'cont.contractDate',
	'cont_typeCode'			=> 'cont.type',
	'cont_expireDate'		=> 'cont.expireDate',
	'cont_active'			=> 'cont.active',
	'cont_serviceCode'		=> 'cont.serviceCode',
	'cont_content'			=> 'cont.content',
	'cont_fileName'			=> 'cont.fileName',

	'user_respIdUser'		=> 'cont.idResponsibleUser',
	'user_respIdOffice' 	=> 'user.idOffice',
	'user_respIdDepartment' => 'user.idDepartment',

	'corp_idOriginal'		=> 'cont.idCorporate',

	'user_idAddUser'		=> 'cont.idAddUser',
	'user_idUpdateUser'		=> 'cont.idUpdateUser',

	'cont_addTime'			=> 'cont.addTime',
	'cont_lastUpdateTime'	=> 'cont.lastUpdateTime',
	'cont_updatesCount'		=> 'cont.updatesCount',

];
$reportCopyInfo->setStructureColumns($cols);

$reportCopyInfo->addStructureColumn("corp_id", 					"IF(corp.idRealCorporate, corp.idRealCorporate, corp.id)", 	"corp");
$reportCopyInfo->addStructureColumn("corp_duplicated", 			"IF(corp.idRealCorporate, 1, 0)", 							"corp");
$reportCopyInfo->addStructureColumn("user_respUserName", 		"CONCAT(user.firstName, ' ', user.lastName)", 				"user");

$reportCopyInfo->setActiveColumns($availableTableColumns);


// ADD FILTERS ////////////////////////////////////////////////////////////////////////////////////
// contract date
if ($reportFilters['contractDate']['startDate']) {
	$reportCopyInfo->addFilter('cont.contractDate', '>=', $reportFilters['contractDate']['start']);
}
if ($reportFilters['contractDate']['endDate']) {
	$reportCopyInfo->addFilter('cont.contractDate', '<=', $reportFilters['contractDate']['end']);
}

// expire date
if ($reportFilters['expireDate']['startDate']) {
	$reportCopyInfo->addFilter('cont.expireDate', '>=', $reportFilters['expireDate']['start']);
}
if ($reportFilters['expireDate']['endDate']) {
	$reportCopyInfo->addFilter('cont.expireDate', '<=', $reportFilters['expireDate']['end']);
}

// add date
if ($reportFilters['addDate']['startDate']) {
	$reportCopyInfo->addFilter('cont.addTime', '>=', $reportFilters['addDate']['start']." 00:00:00");
}
if ($reportFilters['addDate']['endDate']) {
	$reportCopyInfo->addFilter('cont.addTime', '<=', $reportFilters['addDate']['end']." 23:59:59");
}

// contract responsible user
if ($reportFilters['responsibleUser']['idUser']) {
	$reportCopyInfo->addFilter('cont.idResponsibleUser' , '=', $reportFilters['responsibleUser']['idUser']);
}
elseif ($reportFilters['responsibleUser']['idOffice']) {
	$reportCopyInfo->addFilter('user.idOffice' , '=', $reportFilters['responsibleUser']['idOffice']);
}

// corporate
if ($reportExtraFilters['corporate']) {
	$reportCopyInfo->addFilter('cont.idCorporate', 'IN', $reportExtraFilters['corporate']);
}


// filter deleted corporate contracts as the deleted ones can also be found in rd tables
$reportCopyInfo->addFilter('cont.deleted', '=', 0);

$reportCopyInfo->populate();


