<?php
///////////////////////////////////////////////////////////////////////////////////////////////////
// CREATE SQL FOR COPYING USERS ///////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////
// refresh report status
refreshReportStatus('extractMainDataFromDb', 'runningMainSQL', 0);


// INSERT THE ACCESS FOR THE USER THAT DON'T HAVE PROFILE SET /////////////////////////////////////
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Insert($reportTable);
$reportCopyInfo->setMainTable($DBTUsers,				'user');
$reportCopyInfo->addJoinTable($DBTUsersAccess,			'access',		'access.idUser = user.id');

$cols = [
	'idUser'					=> 'user.id',
	'firstName'					=> 'user.firstName',
	'lastName'					=> 'user.lastName',
	'userProcessedRemarks'		=> 'user.processedRemarks',
	'idOffice'					=> 'user.idOffice',
	'idDepartment'				=> 'user.idDepartment',
	'idProfile'					=> 'user.idUsersAccessProfile',
	'idSection'					=> 'access.idSection',
	'type'						=> 'access.type',
	'optionValue'				=> 'access.optionValue',
];

$reportCopyInfo->setStructureColumns($cols);
$reportCopyInfo->addStructureColumn("id",     	"CONCAT(user.id, '|', access.idSection)", 		"access");

$reportCopyInfo->setActiveColumns($availableTableColumns);

// FILTERS ////////////////////////////////////////////////////////////////////////////////////////
if ($reportFilters['user']['id']) {
	$reportCopyInfo->addFilter('user.id', '=', $reportFilters['user']['id']);
}
// filter the users that don't have profile access
$reportCopyInfo->addFilter('user.idUsersAccessProfile', '=', 0);
// filter the users that are deleted
$reportCopyInfo->addFilter('user.deleted', '=', 0);

$reportCopyInfo->populate();


// INSERT THE ACCESS FOR THE USER THAT HAVE PROFILE SET ///////////////////////////////////////////
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Insert($reportTable);
$reportCopyInfo->setMainTable($DBTUsers,					'user');
$reportCopyInfo->addJoinTable($DBTUsersProfilesAccess,		'profile',		'profile.idProfile = user.idUsersAccessProfile');

$cols = [
	'idUser'					=> 'user.id',
	'firstName'					=> 'user.firstName',
	'lastName'					=> 'user.lastName',
	'userProcessedRemarks'		=> 'user.processedRemarks',
	'idOffice'					=> 'user.idOffice',
	'idDepartment'				=> 'user.idDepartment',
	'idProfile'					=> 'user.idUsersAccessProfile',
	'idSection'					=> 'profile.idSection',
	'type'						=> 'profile.type',
	'optionValue'				=> 'profile.optionValue',
];

$reportCopyInfo->setStructureColumns($cols);
$reportCopyInfo->addStructureColumn("id",     	"CONCAT(user.id, '|', profile.idSection)", 		"profile");

$reportCopyInfo->setActiveColumns($availableTableColumns);

// FILTERS ////////////////////////////////////////////////////////////////////////////////////////
if ($reportFilters['user']['id']) {
	$reportCopyInfo->addFilter('user.id', '=', $reportFilters['user']['id']);
}
// filter the users that have profile access
$reportCopyInfo->addFilter('user.idUsersAccessProfile', '>', 0);
// filter the users that are deleted
$reportCopyInfo->addFilter('user.deleted', '=', 0);

$reportCopyInfo->populate();


