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

$reportCopyInfo = new \App\Reports\Db\ManageRecords\Insert($reportTable);
$reportCopyInfo->setMainTable($DBTRd_supplierInvoiceRows,	'invRow');
$reportCopyInfo->addJoinTable($DBTRd_supplierInvoices,		'inv',				'invRow.idInvoice=inv.id',						'INNER');
$reportCopyInfo->addJoinTable($DBTRd_users,             	'invRespUser',    	'inv.idUser=invRespUser.id',					'INNER');

$reportCopyInfo->addJoinTable($DBTRd_services,				'serv',				'invRow.idService=serv.id',						'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_orders,				'ord',				'invRow.idOrder=ord.id',						'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_suppliers,				'supp',				'inv.idSupplier=supp.id',						'LEFT');

$cols = [
	'inv_id'	=>								'invRow.idInvoice',
	'inv_number'	=>							'inv.invoiceNumber',
	'inv_series'	=>							'inv.invoiceSeries',
	'inv_date'=>								'inv.invoiceDate',
	'inv_dueDate'=>								'inv.dueDate',
	'inv_fulfillmentDate'	=>					'inv.fulfillmentDate',
	'inv_estPayDate'	=>						'inv.estPayDate',
	'inv_lastPaymentDate'	=>					'inv.lastPaymentDate',
	'inv_statusOpCode'	=>						'inv.statusOp',
	'inv_statusFinCode'=>						'inv.statusFin',
	'inv_formOfPaymentCode'	=>					'inv.formOfPayment',
	'inv_remarks'	=>							'inv.remarks',
	'inv_processedRemarks'	=>					'inv.processedRemarks',
	'inv_comments'	=>							'inv.invoiceComments',
	'inv_addTime'	=>							'inv.addTime',
	'inv_blockTime'	=>							'inv.blockTime',
	'inv_lastUpdateTime'	=>					'inv.lastUpdateTime',
	'inv_updatesCount'	=>						'inv.updatesCount',

	'inv_respUserId'	=>						'inv.idUser',
	'inv_respOfficeId'	=>						'inv.idOffice',
	'inv_respDepartmentId'	=>					'invRespUser.idDepartment',
	'inv_addUserId'	=>							'inv.idAddUser',
	'inv_blockUserId'	=>						'inv.idBlockUser',

	'invRow_id'	=> 								'invRow.id',
	'invRow_serviceCode'	=> 					'invRow.serviceCode',
	'invRow_description'	=> 					'invRow.description',
	'invRow_typeCode'	=> 						'invRow.rowType',
	'invRow_externalReservationId'	=> 			'invRow.externalReservationId',
	'invRow_externalReservationDate'	=> 		'invRow.externalReservationDate',
	'invRow_confirmationNumber'	=> 				'invRow.confirmationNumber',
	'invRow_checkIn'	=> 						'invRow.checkIn',
    'invRow_checkOut'	=> 						'invRow.checkOut',
	'invRow_contractNumber'	=> 					'invRow.contractNumber',
	'invRow_ticketNo'	=> 						'invRow.ticketNo',
	'invRow_countryCode'	=> 					'invRow.countryCode',
	'invRow_cityId'	=> 							'invRow.cityId',
	'invRow_orderCode'	=> 						'invRow.orderCode',
	'invRow_orderNumber'	=> 					'invRow.orderNumber',
	'invRow_supplierProductId'	=>				'invRow.idSupplierProduct',
	'invRow_passengerId'	=>					'invRow.idPassenger',

	'invRow_measureUnit'	=>					'invRow.measureUnit',
	'invRow_quantity'	=> 						'invRow.quantity',
	'inv_currency'	=>							'inv.currency',
	'inv_defCurrencyExchRate' => 				'inv.currencyRate',
	'inv_workCurrencyExchRate' => 				'inv.workCurrencyRate',
	'invRow_unitValue'	=> 						'invRow.rowUnitValue',
	'invRow_value'	=> 							'invRow.rowValue',
	'invRow_vat'	=> 							'invRow.rowVat',
	'invRow_vatText'	=> 						'invRow.vatText',

	'serv_id'	=>								'invRow.idService',
	'serv_startDate'	=>						'serv.startDate',
	'serv_endDate'	=>							'serv.endDate',
	'serv_statusOpCode'	=>						'serv.statusOp',
	'serv_statusFinCode'	=>					'serv.statusFin',
	'serv_remarks'	=>							'serv.remarks',
	'serv_processedRemarks'	=>					'serv.processedRemarks',

	'order_id'	=>								'invRow.idOrder',
	'order_idConverted' =>						'ord.convertedId',
	'order_number' => 							'ord.orderNumber',
	'order_code' =>  							'ord.orderCode',
	'order_date' =>								'ord.date',
	'order_startDate' =>						'ord.startDate',
	'order_endDate' =>							'ord.endDate',
	'order_miceProjectId' =>					'ord.idMiceProject',

	'supp_originalId'	=>						'inv.idSupplier',
	'supp_bankAccountId'	=>					'inv.idSupplierBankAccount',
	'bal_id'	=>								'invRow.idBalance',
];

$reportCopyInfo->setStructureColumns($cols);

$reportCopyInfo->addStructureColumn("inv_dateYM", 				"DATE_FORMAT(inv.invoiceDate, '%Y-%m-01')", "inv");
$reportCopyInfo->addStructureColumn("inv_dateY", 				"DATE_FORMAT(inv.invoiceDate, '%Y-01-01')", "inv");
$reportCopyInfo->addStructureColumn("inv_dueDateYM", 			"DATE_FORMAT(inv.dueDate, '%Y-%m-01')", "inv");
$reportCopyInfo->addStructureColumn("inv_dueDateY", 			"DATE_FORMAT(inv.dueDate, '%Y-01-01')", "inv");
$reportCopyInfo->addStructureColumn("inv_dueDays", 				"DATEDIFF(inv.dueDate, inv.invoiceDate)", "inv");
$reportCopyInfo->addStructureColumn("inv_duePassedDays", 		"DATEDIFF(CURDATE(), inv.dueDate)", "inv");
$reportCopyInfo->addStructureColumn("inv_respUserName", 		"CONCAT(invRespUser.firstName, ' ', invRespUser.lastName)", "invRespUser");

$reportCopyInfo->addStructureColumn("invRow_vatPercent", 		"invRow.vatPercent/100", "invRow");
$reportCopyInfo->addStructureColumn("invRow_total", 			"invRow.rowValue + invRow.rowVat", "invRow");
$reportCopyInfo->addStructureColumn("invRow_valueDefCurr", 		"invRow.rowValue*inv.currencyRate", "inv");
$reportCopyInfo->addStructureColumn("invRow_vatDefCurr", 		"invRow.rowVat*inv.currencyRate", "inv");
$reportCopyInfo->addStructureColumn("invRow_totalDefCurr", 		"(invRow.rowValue+invRow.rowVat)*inv.currencyRate", "inv");
$reportCopyInfo->addStructureColumn("invRow_valueWorkCurr", 	"invRow.rowValue*inv.currencyRate/inv.workCurrencyRate", "inv");
$reportCopyInfo->addStructureColumn("invRow_vatWorkCurr", 		"invRow.rowVat*inv.currencyRate/inv.workCurrencyRate", "inv");
$reportCopyInfo->addStructureColumn("invRow_totalWorkCurr", 	"(invRow.rowValue+invRow.rowVat)*inv.currencyRate/inv.workCurrencyRate", "inv");
$reportCopyInfo->addStructureColumn("invRow_payedPercent", 		"inv.payedValue/inv.invoiceTotal", "inv");

$reportCopyInfo->addStructureColumn("supp_id",					"IF(supp.idRealSupplier, supp.idRealSupplier, inv.idSupplier)", "supp");
$reportCopyInfo->addStructureColumn("supp_duplicated", 			"IF(supp.idRealSupplier, 1, 0)", "supp");


$reportCopyInfo->setActiveColumns($availableTableColumns);

// ADD FILTERS ////////////////////////////////////////////////////////////////////////////////////
if ($reportFilters['responsibleUser']['idUser']) {
	$reportCopyInfo->addFilter('inv.idUser' , '=', $reportFilters['responsibleUser']['idUser']);
}
elseif ($reportFilters['responsibleUser']['idOffice']) {
	$reportCopyInfo->addFilter('inv.idOffice' , '=', $reportFilters['responsibleUser']['idOffice']);
}
if ($reportFilters['responsibleDepartment']['id']) {
	$reportCopyInfo->addFilter('invRespUser.idDepartment' , '=', $reportFilters['responsibleDepartment']['id']);
}
if ($reportFilters['supplierInvoiceDate']['startDate']) {
	$reportCopyInfo->addFilter('inv.invoiceDate', '>=', $reportFilters['supplierInvoiceDate']['startDate']);
}
if ($reportFilters['supplierInvoiceDate']['endDate']) {
	$reportCopyInfo->addFilter('inv.invoiceDate', '<=',$reportFilters['supplierInvoiceDate']['endDate']);
}
if ($reportFilters['supplierInvoiceDueDate']['startDate']) {
	$reportCopyInfo->addFilter('inv.dueDate', '>=', $reportFilters['supplierInvoiceDueDate']['startDate']);
}
if ($reportFilters['supplierInvoiceDueDate']['endDate']) {
	$reportCopyInfo->addFilter('inv.dueDate', '<=',$reportFilters['supplierInvoiceDueDate']['endDate']);
}
if ($reportExtraFilters['idSuppliers']) {
	$reportCopyInfo->addFilter('inv.idSupplier', 'IN', $reportExtraFilters['idSuppliers']);
}

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

$reportCopyInfo->populate();

