<?php
///////////////////////////////////////////////////////////////////////////////////////////////////
// CREATE SQL FOR COPYING INVOICES ////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////
// refresh report status
refreshReportStatus('extractMainDataFromDb', 'runningMainSQL', 0);
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Insert($reportTable);

$reportCopyInfo->setMainTable($DBTRd_orders,           	'ord');
$reportCopyInfo->addJoinTable($DBTRd_customers,			'ownCust',			'ord.idCustomerOwner=ownCust.id',				'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_corporates,		'ownCorp',			'ord.idCorporateOwner=ownCorp.id',				'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_corporateOffices,	'ownCorpOffice',	'ord.idCorporateOwnerOffice=ownCorpOffice.id',	'LEFT');


$cols = [
	'ord_id' =>                            	'ord.id',
	'ord_convertedId' =>                   	'ord.convertedId',
	'ord_number' =>                        	'ord.orderNumber',
	'ord_code' =>                          	'ord.orderCode',
	'ord_idAutoProcessRule' =>				'ord.idAutoProcessRule',
	'ord_typeCode' =>                      	'ord.orderType',
	'ord_flagCode' =>                      	'ord.flag',
	'ord_servicesTypeCode' =>              	'ord.servicesType',
	'ord_date' =>                          	'ord.date',
	'ord_startDate' =>                     	'ord.startDate',
	'ord_endDate' =>                       	'ord.endDate',
	'ord_ownerCustomerId' =>               	'ord.idCustomerOwner',
	'ord_ownerCustomerProcessedRemarks' => 	'ownCust.processedRemarks',
	'ord_pax' =>                           	'ord.pax',
	'ord_ownerCorporateId' =>              	'ord.idCorporateOwner',
	'ord_ownerCorporateAlias' =>            'ownCorp.alias',
	'ord_ownerCorporateProcessedRemarks' => 'ownCorp.processedRemarks',
	'ord_ownerCorporateOfficeId' =>        	'ord.idCorporateOwnerOffice',
	'ord_ownerCorporateOfficeName' =>      	'ownCorpOffice.name',
	'ord_ownerCorporateOfficeCostCenter' => 'ord.corporateOwnerOfficeCostCenter',
	'ord_contactPersonId' =>               	'ord.idContactPerson',
	'ord_description' =>                   	'ord.description',
	'ord_comments' =>                      	'ord.comments',
	'ord_notes' =>							'ord.notes',
	'ord_statusOpCode' =>                  	'ord.statusOp',
	'ord_statusFinCode' =>                 	'ord.statusFin',
	'ord_miceProjectId' =>                 	'ord.idMiceProject',
	'ord_genDocsAssoc' =>					'ord.genDocsAssoc',
	'ord_addTime' =>                       	'ord.addTime',
	'ord_lastUpdateTime' =>                	'ord.lastUpdateTime',
	'ord_updatesCount' =>                  	'ord.updatesCount',

	'ord_userId' =>                        	'ord.idUser',
	'ord_deptId' =>                        	'ord.idDepartment',
	'ord_officeId' =>                      	'ord.idOffice',
	'ord_addUserId' =>                     	'ord.idAddUser',
	'ord_updateUserId' =>                  	'ord.idUpdateUser',

];

$reportCopyInfo->setStructureColumns($cols);

$reportCopyInfo->addStructureColumn("ord_dateYM", 					"DATE_FORMAT(ord.date, '%Y-%m-01')", "ord");
$reportCopyInfo->addStructureColumn("ord_dateY", 			   		"YEAR(ord.date)", "ord");
$reportCopyInfo->addStructureColumn("ord_startDateYM", 				"DATE_FORMAT(ord.startDate, '%Y-%m-01')", "ord");
$reportCopyInfo->addStructureColumn("ord_startDateY", 			   	"YEAR(ord.startDate)", "ord");
$reportCopyInfo->addStructureColumn("ord_endDateYM", 				"DATE_FORMAT(ord.endDate, '%Y-%m-01')", "ord");
$reportCopyInfo->addStructureColumn("ord_endDateY", 			   	"YEAR(ord.endDate)", "ord");
$reportCopyInfo->addStructureColumn("ord_ownerCustomerName", 		"CONCAT(ownCust.lastNameStd, ' ', ownCust.firstNameStd)", "ownCust");

$reportCopyInfo->setActiveColumns($availableTableColumns);

// ADD FILTERS ////////////////////////////////////////////////////////////////////////////////////

if ($reportFilters['orderDate']['startDate']) {
	$reportCopyInfo->addFilter('ord.date', '>=', $reportFilters['orderDate']['startDate']);
}
if ($reportFilters['orderDate']['endDate']) {
	$reportCopyInfo->addFilter('ord.date', '<=', $reportFilters['orderDate']['endDate']);
}

if ($reportFilters['orderStartDate']['startDate']) {
	$reportCopyInfo->addFilter('ord.startDate', '>=', $reportFilters['orderStartDate']['startDate']);
}
if ($reportFilters['orderStartDate']['endDate']) {
	$reportCopyInfo->addFilter('ord.startDate', '<=', $reportFilters['orderStartDate']['endDate']);
}

if ($reportFilters['orderEndDate']['startDate']) {
	$reportCopyInfo->addFilter('ord.endDate', '>=', $reportFilters['orderEndDate']['startDate']);
}
if ($reportFilters['orderEndDate']['endDate']) {
	$reportCopyInfo->addFilter('ord.endDate', '<=', $reportFilters['orderEndDate']['endDate']);
}

if ($reportFilters['idMiceProject']) {
	$reportCopyInfo->addFilter('ord.idMiceProject', '=', $reportFilters['idMiceProject']);
}

if ($reportFilters['orderStatusOp'] and count($reportFilters['orderStatusOp']) < 4) {
	$reportCopyInfo->addFilter('ord.statusOp', 'IN', array_keys($reportFilters['orderStatusOp']));
}

if ($reportFilters['orderStatusFin'] and count($reportFilters['orderStatusFin']) < 3) {
	$reportCopyInfo->addFilter('ord.statusFin', 'IN', array_keys($reportFilters['orderStatusFin']));
}

$reportCopyInfo->populate();