<?php
///////////////////////////////////////////////////////////////////////////////////////////////////
// CREATE SQL FOR COPYING SUPPLIER PRODUCTS LIST //////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////
// refresh report status
refreshReportStatus('extractMainDataFromDb', 'runningMainSQL', 0);
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Insert($reportTable);
$reportCopyInfo->setMainTable($DBTRd_supplierProducts,	'prod');
$reportCopyInfo->addJoinTable($DBTRd_users,				'addUser',	'prod.idAddUser=addUser.id', 'LEFT');

$cols = [
	'prod_id'					=> 'prod.id',
	'prod_serviceCode'			=> 'prod.serviceCode',
	'prod_productName'			=> 'prod.productName',
	'prod_category'				=> 'prod.category',
	'prod_chainCode'			=> 'prod.chainCode',
	'prod_comments'				=> 'prod.comments',
	'prod_remarks'				=> 'prod.remarks',
	'prod_processedRemarks'		=> 'prod.processedRemarks',

	'prod_localization'				=> 'prod.localization',
	'prod_address'					=> 'prod.address',
	'prod_departureCountryCode'		=> 'prod.departureCountryCode',
	'prod_departureCityId'			=> 'prod.departureCityId',
	'prod_destinationCountryCode'	=> 'prod.destinationCountryCode',
	'prod_destinationCityId'		=> 'prod.destinationCityId',

	'prod_telephone'		=> 'prod.telephone',
	'prod_fax'				=> 'prod.fax',
	'prod_email'			=> 'prod.email',
	'prod_website'			=> 'prod.website',

	'realProd_id'			=> 'prod.idRealSupplierProduct',

	'prod_idAddUser'		=> 'prod.idAddUser',
	'prod_idAddOffice'		=> 'addUser.idOffice',
	'prod_addTime'			=> 'prod.addTime',
	'prod_idUpdateUser'		=> 'prod.idUpdateUser',
	'prod_lastUpdateTime'	=> 'prod.lastUpdateTime',
	'prod_updatesCount'		=> 'prod.updatesCount',
];

$reportCopyInfo->setStructureColumns($cols);
$reportCopyInfo->addStructureColumn("prod_duplicated", 			"IF(prod.idRealSupplierProduct, 1, 0)");
$reportCopyInfo->addStructureColumn("prod_addUserName", 		"CONCAT(addUser.lastName, ' ', addUser.firstName)", 'addUser');

$reportCopyInfo->setActiveColumns($availableTableColumns);

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

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

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

$reportCopyInfo->populate();
