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

$reportCopyInfo = new \App\Reports\Db\ManageRecords\Insert($reportTable);
$reportCopyInfo->setMainTable($DBTRd_clientInvoices,    'inv') ;
$reportCopyInfo->addJoinTable($DBTRd_docSeries,         'docSeries',   'inv.idInvoiceSeries=docSeries.id', 'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_corporates,        'corp',        'inv.idCorporate=corp.id', 'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_corporateOffices,  'corpOffices', 'inv.idCorporateOffice=corpOffices.id', 'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_customers,         'cust',        'inv.idCustomer=cust.id', 'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_users,             'respUser',    'inv.idUser=respUser.id');
$reportCopyInfo->addJoinTable($DBTRd_users,             'addUser',     'inv.idAddUser=addUser.id', 'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_users,             'issueUser',   'inv.idIssueUser=issueUser.id', 'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_users,             'voidUser',    'inv.idVoidUser=voidUser.id', 'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_offices,           'respOffice',  'inv.idOffice=respOffice.id', 'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_autoProcessRules,  'apRules',     'inv.idAutoProcessRule=apRules.id', 'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_departments,       'respDept',    'respUser.idDepartment=respDept.id', 'LEFT');

$cols = [
	'invoiceId' => 					'inv.id',
	'invoiceNumber'	=> 				'inv.invoiceNumber',
	'invoiceDate' => 				'inv.invoiceDate',
	'invoiceSeriesId' => 			'inv.idInvoiceSeries',
	'invoiceSeriesCode' => 			'docSeries.code',
	'invoiceDueDate' => 			'inv.dueDate',
	'invoiceFulfillmentDate' => 	'inv.fulfillmentDate',
	'invoiceComments' => 			'inv.comments',
 	'invoiceRemarks' => 			'inv.remarks',
	'invoiceProcessedRemarks' => 	'inv.processedRemarks',
	'invoiceFormOfPayment' => 		'inv.formOfPayment',
	'invoiceTemporary' => 			'inv.temporary',
	'invoiceCumulativeInvoice' => 	'inv.cumulativeInvoice',
	'invoiceRoundTotal' => 			'inv.roundTotal',
	'invoiceVoidReasonCode' => 		'inv.voidReason',
	'autoProcessRuleId' => 			'inv.idAutoProcessRule',
	'autoProcessRuleName' => 		'apRules.name',
	'genDocsAssoc' =>				'inv.genDocsAssoc',

	'corporateSelectedOfficeId' => 		'inv.idCorporateOffice',
	'corporateSelectedOfficeName' => 	'corpOffices.name',
	'corporateCountryCode' =>			'corpOffices.countryCode',
	'corporateCityId' => 				'corpOffices.cityId',
	'corporateDomainId' =>            	'corp.idDomain',
	'corporateDomainCategoryId' =>    	'corp.idDomainCategory',
	'corporateOriginalId' => 			'inv.idCorporate',
	'customerOriginalId' => 			'inv.idCustomer',

	'invoiceBase' => 					'inv.invoiceValue',
	'invoiceVat' => 					'inv.invoiceVat',
	'invoiceTotal' => 					'inv.invoiceTotal',
	'invoiceCurrency' => 				'inv.currency',
	'invoiceDefCurrencyExchRate' => 	'inv.currencyRate',
	'invoiceWorkCurrencyExchRate' => 	'inv.workCurrencyRate',
	'cashedStatus' => 					'inv.cashedStatus',
	'cashedValue' => 					'inv.cashedTotal',
	'lastCashingDocDate' => 			'inv.lastCashingDate',

	'userId' => 			'inv.idUser',
	'userOfficeId' => 		'inv.idOffice',
	'userOfficeName' => 	'respOffice.name',
	'userDepartmentId' => 	'respUser.idDepartment',
	'userDepartmentName' => 'respDept.name',
	'addUserId' => 			'inv.idAddUser',
	'issueUserId' => 		'inv.idIssueUser',
	'voidUserId' => 		'inv.idVoidUser',

	'addTime' => 		'inv.addTime',
	'issueTime' => 		'inv.issueTime',
	'voidTime' => 		'inv.voidTime',
	'lastUpdateTime' => 'inv.lastUpdateTime',
	'updatesCount' => 	'inv.updatesCount'
];

$reportCopyInfo->setStructureColumns($cols);

$reportCopyInfo->addStructureColumn("invoiceDateYM", 			"DATE_FORMAT(inv.invoiceDate, '%Y-%m-01')", "inv");
$reportCopyInfo->addStructureColumn("invoiceDateY", 			"DATE_FORMAT(inv.invoiceDate, '%Y-01-01')", "inv");
$reportCopyInfo->addStructureColumn("invoiceDueDateYM", 		"DATE_FORMAT(inv.dueDate, '%Y-%m-01')", "inv");
$reportCopyInfo->addStructureColumn("invoiceDueDateY", 			"DATE_FORMAT(inv.dueDate, '%Y-01-01')", "inv");
$reportCopyInfo->addStructureColumn("invoiceDueDays", 			"DATEDIFF(inv.dueDate, inv.invoiceDate)", "inv");
$reportCopyInfo->addStructureColumn("invoiceDuePassedDays", 	"IF((inv.cashedStatus = 't' OR inv.cashedStatus = 'o'), DATEDIFF(inv.lastCashingDate, inv.dueDate), DATEDIFF(CURDATE(), inv.dueDate))", "inv");
$reportCopyInfo->addStructureColumn("invoiceIssued", 			"IF(inv.issueTime IS NOT NULL, 1, 0)", "inv");
$reportCopyInfo->addStructureColumn("invoiceVoided", 			"IF(inv.voidTime IS NOT NULL, 1, 0)", "inv");

$reportCopyInfo->addStructureColumn("corporateId",				"IF(corp.idRealCorporate, corp.idRealCorporate, inv.idCorporate)", "corp");
$reportCopyInfo->addStructureColumn("customerId",				"IF(cust.idRealCustomer, cust.idRealCustomer, inv.idCustomer)", "cust");
$reportCopyInfo->addStructureColumn("corporateDuplicated", 		"IF(corp.idRealCorporate, 1, 0)", "corp");
$reportCopyInfo->addStructureColumn("customerDuplicated", 		"IF(cust.idRealCustomer, 1, 0)", "cust");

$reportCopyInfo->addStructureColumn("invoiceBaseDefCurr", 		"inv.invoiceValue*inv.currencyRate", "inv");
$reportCopyInfo->addStructureColumn("invoiceVatDefCurr", 		"inv.invoiceVat*inv.currencyRate", "inv");
$reportCopyInfo->addStructureColumn("invoiceTotalDefCurr", 		"inv.invoiceTotal*inv.currencyRate", "inv");
$reportCopyInfo->addStructureColumn("invoiceBaseWorkCurr", 		"inv.invoiceValue*inv.currencyRate/inv.workCurrencyRate", "inv");
$reportCopyInfo->addStructureColumn("invoiceVatWorkCurr", 		"inv.invoiceVat*inv.currencyRate/inv.workCurrencyRate", "inv");
$reportCopyInfo->addStructureColumn("invoiceTotalWorkCurr", 	"inv.invoiceTotal*inv.currencyRate/inv.workCurrencyRate", "inv");

$reportCopyInfo->addStructureColumn("cashedPercent", 			"inv.cashedTotal/inv.invoiceTotal", "inv");
$reportCopyInfo->addStructureColumn("notCashedValue", 			"inv.invoiceTotal-inv.cashedTotal", "inv");
$reportCopyInfo->addStructureColumn("cashedValueDefCurr", 		"inv.cashedTotal*inv.currencyRate", "inv");
$reportCopyInfo->addStructureColumn("notCashedValueDefCurr", 	"(inv.invoiceTotal-inv.cashedTotal)*inv.currencyRate", "inv");
$reportCopyInfo->addStructureColumn("cashedValueWorkCurr", 		"inv.cashedTotal*inv.currencyRate/inv.workCurrencyRate", "inv");
$reportCopyInfo->addStructureColumn("notCashedValueWorkCurr", 	"(inv.invoiceTotal-inv.cashedTotal)*inv.currencyRate/inv.workCurrencyRate", "inv");

$reportCopyInfo->addStructureColumn("userName", 			"CONCAT(respUser.firstName, ' ', respUser.lastName)", "respUser");
$reportCopyInfo->addStructureColumn("addUserName", 			"CONCAT(addUser.firstName, ' ', addUser.lastName)", "addUser");
$reportCopyInfo->addStructureColumn("issueUserName", 		"CONCAT(issueUser.firstName, ' ', issueUser.lastName)", "issueUser");
$reportCopyInfo->addStructureColumn("voidUserName", 		"CONCAT(voidUser.firstName, ' ', voidUser.lastName)", "voidUser");

$reportCopyInfo->setActiveColumns($availableTableColumns);

// ADD FILTERS ////////////////////////////////////////////////////////////////////////////////////
if ($reportFilters['corporateGroups']) {
	if ($reportExtraFilters['corporatesInGroups']) {
		$reportCopyInfo->addFilter('inv.idCorporate', 'IN', $reportExtraFilters['corporatesInGroups']);
	}
	else {
		$reportCopyInfo->addCustomFilter("0");
	}
}

if ($reportExtraFilters['invoiceCustomer']) {
	$reportCopyInfo->addFilter('inv.idCustomer', 'IN', $reportExtraFilters['invoiceCustomer']);
}
if ($reportExtraFilters['invoiceCorporate']) {
	$reportCopyInfo->addFilter('inv.idCorporate', 'IN', $reportExtraFilters['invoiceCorporate']);
}

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

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

if (count($reportFilters['clientType']) == 1) {
	// direct client
	if ($reportFilters['clientType']['DC']) {
		$reportCopyInfo->addFilter('inv.idCorporate', '=', 0);
	}
	else {
		$reportCopyInfo->addFilter('inv.idCustomer', '=', 0);
	}
}
// filter by two client types
if (count($reportFilters['clientType']) == 2 and $reportFilters['clientType']['CC'] and $reportFilters['clientType']['SA']) {
	$reportCopyInfo->addFilter('inv.idCustomer', '=', 0);
}

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

if ($reportFilters['cashedStatus']) {
	$reportCopyInfo->addFilter('inv.cashedStatus' , 'IN', $reportFilters['cashedStatus']);
}

if ($reportFilters['issued'] and count($reportFilters['issued']) == 1) {
	if ($reportFilters['issued'][1]) {
		$reportCopyInfo->addFilter('inv.issueTime' , 'IS NOT NULL');
	}
	elseif ($reportFilters['issued'][0]) {
		$reportCopyInfo->addFilter('inv.issueTime' , 'IS NULL');
	}
}

if ($reportFilters['temporary'] and count($reportFilters['temporary']) == 1) {
	if ($reportFilters['temporary'][1]) {
		$reportCopyInfo->addFilter('inv.temporary' , '=', 1);
	}
	elseif ($reportFilters['temporary'][0]) {
		$reportCopyInfo->addFilter('inv.temporary' , '=', 0);
	}
}

if ($reportFilters['voided'] and count($reportFilters['voided']) == 1) {
	if ($reportFilters['voided'][1]) {
		$reportCopyInfo->addFilter('inv.voidTime' , 'IS NOT NULL');
	}
	elseif ($reportFilters['voided'][0]) {
		$reportCopyInfo->addFilter('inv.voidTime' , 'IS NULL');
	}
}

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('respUser.idDepartment' , '=', $reportFilters['responsibleDepartment']['id']);

}
$reportCopyInfo->populate();