<?php
refreshReportStatus('extractMainDataFromDb', 'updateInvoiceDetails', 0);

// update the invoice form of payment name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addCaseStructureColumn('invoiceFormOfPaymentName',  "invoiceFormOfPayment", \App\Config\FormsOfPayment::getInstance()->getAll());
$reportCopyInfo->populate();

// update the invoice void reason name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addCaseStructureColumn('invoiceVoidReasonName',  "invoiceVoidReasonCode", (array)$_LANG['invoiceVoidReason']);
$reportCopyInfo->populate();

// ADD LAST CASHING DOC TYPE //////////////////////////////////////////////////////////////////////
refreshReportStatus('extractMainDataFromDb', 'updateLastCashingDocType', 0);

$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addStructureColumn('lastCashingDocType', '(SELECT docType FROM `'.$DBTRd_clientInvoiceCashings.'` WHERE rep.invoiceId = idInvoice ORDER BY id DESC LIMIT 1)', 'rep');
$reportCopyInfo->populate();

// update the last cashing doc type name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addCaseStructureColumn("lastCashingDocTypeName", "lastCashingDocType", \App\Config\CashingDocuments::getInstance()->getList());
$reportCopyInfo->populate();

// update the last cashing doc id
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addStructureColumn("lastCashingDocId", "(SELECT id FROM `".$DBTRd_clientInvoiceCashings."` WHERE rep.invoiceId = idInvoice ORDER BY id DESC LIMIT 1)", "rep");
$reportCopyInfo->populate();

// update the invoice cashed status name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addCaseStructureColumn("cashedStatusName", "cashedStatus", (Array)$_LANG['invoices']['status']);
$reportCopyInfo->populate();

// ADD CUSTOMER / CORPORATE DATA //////////////////////////////////////////////////////////////////
refreshReportStatus('extractMainDataFromDb', 'updateClientDetails', 0);

$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addJoinTable($DBTRd_corporates,        'corp',        'rep.corporateId=corp.id', 'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_trusts,            'trust',       'corp.idTrust=trust.id', 'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_corporateSegments, 'corpSeg',     'corp.idSegment=corpSeg.id', 'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_users, 			'kamUser',     'corp.idAccountManager=kamUser.id', 'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_customers,         'cust',        'rep.customerId=cust.id', 'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_customerSegments,  'custSeg',     'cust.idSegment=custSeg.id', 'LEFT');

$cols = [
	'corporateAlias' => 				'corp.alias',
	'corporateTrustId' => 				'corp.idTrust',
	'corporateTrustName' => 			'trust.name',
	'corporateSegmentId' => 			'corp.idSegment',
	'corporateSegmentName' => 			'corpSeg.name',
	'corporateKamId' => 				'corp.idAccountManager',
	'corporateRemarks' => 				'corp.remarks',
	'corporateProcessedRemarks' => 		'corp.processedRemarks',

	'customerSegmentId' => 			'cust.idSegment',
	'customerSegmentName' => 		'custSeg.name',
	'customerRemarks' => 			'cust.remarks',
	'customerProcessedRemarks' =>	'cust.processedRemarks',
	'customerCountryCode' =>		'cust.countryCode',
	'customerCityId' =>				'cust.cityId',
];

$reportCopyInfo->setStructureColumns($cols);
$reportCopyInfo->addStructureColumn("corporateKamName", 	"CONCAT(kamUser.lastName, ' ', kamUser.firstName)", "kamUser");
$reportCopyInfo->addStructureColumn("corporateCompanyName", "TRIM(CONCAT(corp.companyNameStd, ' ', corp.companySuffixStd))", "corp");
$reportCopyInfo->addStructureColumn("customerName", 		"CONCAT(cust.lastNameStd, ' ', cust.firstNameStd)", "cust");
$reportCopyInfo->addStructureColumn("clientId", 			"IF(rep.corporateId, rep.corporateId, rep.customerId)", "rep");
$reportCopyInfo->addStructureColumn("clientName", 			"IF(rep.corporateId, corp.alias, CONCAT(cust.lastNameStd, ' ', cust.firstNameStd))", "rep");
$reportCopyInfo->addStructureColumn("clientTypeCode", 		"IF(rep.corporateId, corp.type, IF(rep.customerId, 'DC', ''))", "corp");
$reportCopyInfo->addStructureColumn("clientCountryCode", 	"IF(rep.corporateId, rep.corporateCountryCode, cust.countryCode)", "cust");
$reportCopyInfo->addStructureColumn("clientCityId", 		"IF(rep.corporateId, rep.corporateCityId, cust.cityId)", "cust");

$reportCopyInfo->populate();

// update corporate activity domain and category names
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_activityDomains, 'corporateDomainName', 'corporateDomainId', 'name');

$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_activityDomainCategories, 'corporateDomainCategoryName', 'corporateDomainCategoryId', 'name');

// update the invoice client type name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addCaseStructureColumn('clientTypeName',  "clientTypeCode", (Array)$_LANG['generalData']['clientType']);
$reportCopyInfo->populate();

// REFILTER BY CLIENT TYPE ////////////////////////////////////////////////////////////////////////
if (count($reportFilters['clientType'])==1) {
	if ($reportFilters['clientType']['CC']) {
		$deleteByClTypeSQL="DELETE FROM `$reportTable` WHERE clientTypeCode!='CC'";
	}
	elseif ($reportFilters['clientType']['SA']) {
		$deleteByClTypeSQL="DELETE FROM `$reportTable` WHERE clientTypeCode!='SA'";
	}

	if ($deleteByClTypeSQL) {
		$deleteByClTypeEx=$db->Execute($deleteByClTypeSQL);
		if ($db->ErrorNo()) killReport("Could not run SQL in report. SQL: ".$deleteByClTypeEx);
	}
}

if (count($reportFilters['clientType'])==2) {
	if (!$reportFilters['clientType']['CC']) {
		$deleteByClTypeSQL="DELETE FROM `$reportTable` WHERE clientTypeCode='CC'";
	}
	elseif (!$reportFilters['clientType']['SA']) {
		$deleteByClTypeSQL="DELETE FROM `$reportTable` WHERE clientTypeCode='SA'";
	}

	if ($deleteByClTypeSQL) {
		$deleteByClTypeEx=$db->Execute($deleteByClTypeSQL);
		if ($db->ErrorNo()) killReport("Could not run SQL in report. SQL: ".$deleteByClTypeSQL);
	}
}


// ADD LEGAL ENTITY COUNTRY AND CITY NAMES ////////////////////////////////////////////////////////
refreshReportStatus('extractMainDataFromDb', 'countryAndCityCorporate', 0);

// update invoice corporate country
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_countries, 'corporateCountryName', 'corporateCountryCode', 'nameStd', 'code');

// update invoice corporate city
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_cities, 'corporateCityName', 'corporateCityId', "nameStd");


// ADD INDIVIDUAL COUNTRY AND CITY NAMES /////////////////////////////////////////////////////////
refreshReportStatus('extractMainDataFromDb', 'countryAndCityCustomer', 0);

// update invoice corporate country
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_countries, 'customerCountryName', 'customerCountryCode', 'nameStd', 'code');

// update invoice corporate city
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_cities, 'customerCityName', 'customerCityId', "nameStd");

// ADD UNIFIED CLIENT COUNTRY AND CITY NAMES //////////////////////////////////////////////////////
refreshReportStatus('extractMainDataFromDb', 'countryAndCityClient', 0);

// update invoice client country
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_countries, 'clientCountryName', 'clientCountryCode', 'nameStd', 'code');

// update invoice client city
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_cities, 'clientCityName', 'clientCityId', "nameStd");

