<?php
///////////////////////////////////////////////////////////////////////////////////////////////////
// CREATE SQL FOR COPYING CORPORATE CONTACT PERSONS LIST //////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////
// refresh report status
refreshReportStatus('extractMainDataFromDb', 'runningMainSQL', 0);
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Insert($reportTable);
$reportCopyInfo->setMainTable($DBTRd_contactPersons,	'contact');
$reportCopyInfo->addJoinTable($DBTRd_corporates,		'corp',		'corp.id=contact.idCorporate',	'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_users,				'addUser',	'contact.idAddUser=addUser.id', 'LEFT');

$cols = [
	'contact_id'				=> 'contact.id',
	'contact_titleCode'			=> 'contact.title',
	'contact_firstName'			=> 'contact.firstName',
	'contact_lastName'			=> 'contact.lastName',
	'contact_department'		=> 'contact.department',
	'contact_job'				=> 'contact.job',
	'contact_role'				=> 'contact.role',
	'contact_comments'			=> 'contact.comments',
	'contact_remarks'			=> 'contact.remarks',
	'contact_processedRemarks'	=> 'contact.processedRemarks',

	'contact_birthday'			=> 'contact.birthday',
	'contact_email'				=> 'contact.email',
	'contact_telephone'			=> 'contact.telephone',
	'contact_telephoneMobile'	=> 'contact.telephoneMobile',
	'contact_fax'				=> 'contact.fax',

	'contact_addTime'			=> 'contact.addTime',
	'contact_idAddUser'			=> 'contact.idAddUser',
	'contact_addOfficeId'		=> 'addUser.idOffice',
	'contact_lastUpdateTime'	=> 'contact.lastUpdateTime',
	'contact_idUpdateUser'		=> 'contact.idUpdateUser',
	'contact_updatesCount'		=> 'contact.updatesCount',

	'corp_idOriginal'			=> 'contact.idCorporate',
	'corp_idOffice'				=> 'contact.idCorporateOffice',
];

$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("contact_addUserName", 		"CONCAT(addUser.lastName, ' ', addUser.firstName)", 'addUser');

$reportCopyInfo->setActiveColumns($availableTableColumns);

// ADD FILTERS ////////////////////////////////////////////////////////////////////////////////////
if ($reportFilters['addDate']['startDate']) {
	$reportCopyInfo->addFilter('contact.addTime', '>=', $reportFilters['addDate']['startDate']." 00:00:00");
}
if ($reportFilters['addDate']['endDate']) {
	$reportCopyInfo->addFilter('contact.addTime', '<=', $reportFilters['addDate']['endDate']." 23:59:59");
}

if ($reportFilters['addOfficeUser']['idUser']) {
	$reportCopyInfo->addFilter('contact.idAddUser' , '=', $reportFilters['addOfficeUser']['idUser']);
}
elseif ($reportFilters['addOfficeUser']['idOffice']) {
	$reportCopyInfo->addFilter('addUser.idOffice' , '=', $reportFilters['addOfficeUser']['idOffice']);
}

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

$reportCopyInfo->addFilter('contact.type', '=', 'corporate');
$reportCopyInfo->addFilter('contact.deleted', '=', '0');

$reportCopyInfo->populate();
