<?php
// ADD CUSTOMER / CORPORATE DATA //////////////////////////////////////////////////////////////////
refreshReportStatus('extractMainDataFromDb', 'updateClientDetails', 0);
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addJoinTable($DBTRd_corporates,        'invCorp',        	'rep.inv_corporateId=invCorp.id', 				'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_customers,         'invCust',        	'rep.inv_customerId=invCust.id', 				'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_trusts,            'invCorpTrust',       'invCorp.idTrust=invCorpTrust.id', 				'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_corporateSegments, 'invCorpSeg',     	'invCorp.idSegment=invCorpSeg.id', 				'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_customerSegments,  'invCustSeg',     	'invCust.idSegment=invCustSeg.id', 				'LEFT');
$reportCopyInfo->addJoinTable($DBTRd_users, 			'invCorpKamUser',     'invCorp.idAccountManager=invCorpKamUser.id',	'LEFT');

$cols = [
	'inv_corporateAlias'					=> 'invCorp.alias',
	'inv_corporateTrustId'					=> 'invCorp.idTrust',
	'inv_corporateTrustName'				=> 'invCorpTrust.name',
	'inv_corporateSegmentId'				=> 'invCorp.idSegment',
	'inv_corporateSegmentName'				=> 'invCorpSeg.name',
	'inv_corporateKamId'					=> 'invCorp.idAccountManager',
	'inv_corporateProcessedRemarks'			=> 'invCorp.processedRemarks',

	'inv_customerSegmentId'					=> 'invCust.idSegment',
	'inv_customerSegmentName'				=> 'invCustSeg.name',
	'inv_customerCountryCode'				=> 'invCust.countryCode',
	'inv_customerCityId'					=> 'invCust.cityId',
];

$reportCopyInfo->setStructureColumns($cols);
$reportCopyInfo->addStructureColumn("inv_corporateCompanyName", "TRIM(CONCAT(invCorp.companyNameStd, ' ', invCorp.companySuffixStd))", "invCorp");
$reportCopyInfo->addStructureColumn("inv_corporateKamName", 	"CONCAT(invCorpKamUser.lastName, ' ', invCorpKamUser.firstName)", "invCorpKamUser");
$reportCopyInfo->addStructureColumn("inv_customerName", 		"CONCAT(invCust.lastNameStd, ' ', invCust.firstNameStd)", "invCust");
$reportCopyInfo->addStructureColumn("inv_clientId", 			"IF(rep.inv_corporateId, rep.inv_corporateId, rep.inv_customerId)", "rep");
$reportCopyInfo->addStructureColumn("inv_clientName", 			"IF(rep.inv_corporateId, invCorp.alias, CONCAT(invCust.lastNameStd, ' ', invCust.firstNameStd))", "rep");
$reportCopyInfo->addStructureColumn("inv_clientTypeCode", 		"IF(rep.inv_corporateId, invCorp.type, IF(rep.inv_customerId, 'DC', ''))", "invCorp");
$reportCopyInfo->addStructureColumn("inv_clientCountryCode", 	"IF(rep.inv_corporateId, rep.inv_corporateSelOfficeCountryCode, invCust.countryCode)", "invCust");
$reportCopyInfo->addStructureColumn("inv_clientCityId", 		"IF(rep.inv_corporateId, rep.inv_corporateSelOfficeCityId, invCust.cityId)", "invCust");

$reportCopyInfo->populate();

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

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

// update the invoice client type name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addCaseStructureColumn('inv_clientTypeName',  "inv_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 inv_clientTypeCode!='CC'";
	}
	elseif ($reportFilters['clientType']['SA']) {
		$deleteByClTypeSQL="DELETE FROM `$reportTable` WHERE inv_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 inv_clientTypeCode='CC'";
	}
	elseif (!$reportFilters['clientType']['SA']) {
		$deleteByClTypeSQL="DELETE FROM `$reportTable` WHERE inv_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, 'inv_corporateSelOfficeCountryName', 'inv_corporateSelOfficeCountryCode', 'nameStd', 'code');

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

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

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

// update invoice customer city
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_cities, 'inv_customerCityName', 'inv_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, 'inv_clientCountryName', 'inv_clientCountryCode', 'nameStd', 'code');

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

// INVOICE DATA ///////////////////////////////////////////////////////////////////////////////////
refreshReportStatus('extractMainDataFromDb', 'updateInvoiceDetails', 0);

// update the invoice series code
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_docSeries, 'inv_seriesCode', 'inv_seriesId', 'code');

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

// update the AP rule name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_autoProcessRules, 'inv_autoProcessRuleName', 'inv_autoProcessRuleId', "name");

// update the invoice reponsible office name/prRemarks
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addJoinTable($DBTRd_offices, 'off', 'rep.inv_respOfficeId=off.id', 'LEFT');
$cols = [
	'inv_respOfficeName' => 'off.name',
	'inv_respOfficeProcessedRemarks' => 'off.processedRemarks',
];
$reportCopyInfo->setStructureColumns($cols);
$reportCopyInfo->populate();

// update the invoice reponsible department name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_departments, 'inv_respDeptName', 'inv_respDeptId', 'name');

// update the invoice add user name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_users, 'inv_addUserName', 'inv_addUserId', "CONCAT(joinTable.lastName, ' ', joinTable.firstName)");

// update the invoice issue user name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_users, 'inv_issueUserName', 'inv_issueUserId', "CONCAT(joinTable.lastName, ' ', joinTable.firstName)");

// update the invoice void user name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_users, 'inv_voidUserName', 'inv_voidUserId', "CONCAT(joinTable.lastName, ' ', joinTable.firstName)");

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


// CASHING DATA ///////////////////////////////////////////////////////////////////////////////////
refreshReportStatus('extractMainDataFromDb', 'updateInvoiceCashDetails', 0);

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

// update the row cashed value original currency
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addStructureColumn("inv_rowCashedValue", "ROUND(rep.invRowsGrouped_total*rep.inv_rowCashedPercent, {$displayPriceDecimals})", "rep");
$reportCopyInfo->populate();

// update the row not cashed value original currency
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addStructureColumn("inv_rowNotCashedValue", "rep.invRowsGrouped_total-ROUND(rep.invRowsGrouped_total*rep.inv_rowCashedPercent, {$displayPriceDecimals})", "rep");
$reportCopyInfo->populate();

// update the row cashed value default currency
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addStructureColumn("inv_rowCashedValueDefCurr", "ROUND(rep.invRowsGrouped_totalDefCurr*rep.inv_rowCashedPercent, {$displayPriceDecimals})", "rep");
$reportCopyInfo->populate();

// update the row not cashed value default currency
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addStructureColumn("inv_rowNotCashedValueDefCurr", "rep.invRowsGrouped_totalDefCurr-ROUND(rep.invRowsGrouped_totalDefCurr*rep.inv_rowCashedPercent, {$displayPriceDecimals})", "rep");
$reportCopyInfo->populate();

// update the row cashed value wirking currency
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addStructureColumn("inv_rowCashedValueWorkCurr", "ROUND(rep.invRowsGrouped_totalWorkCurr*rep.inv_rowCashedPercent, {$displayPriceDecimals})", "rep");
$reportCopyInfo->populate();

// update the row not cashed value working currency
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addStructureColumn("inv_rowNotCashedValueWorkCurr", "rep.invRowsGrouped_totalWorkCurr-ROUND(rep.invRowsGrouped_totalWorkCurr*rep.inv_rowCashedPercent, {$displayPriceDecimals})", "rep");
$reportCopyInfo->populate();

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

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

// SERVICE DATA ///////////////////////////////////////////////////////////////////////////////////
refreshReportStatus('extractMainDataFromDb', 'updateServiceDetails', 0);

// update the AP rule name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_autoProcessRules, 'serv_autoProcessRuleName', 'serv_autoProcessRuleId', "name");

// update the first passenger name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_customers, 'serv_firstCustomerName', 'serv_firstCustomerId', "CONCAT(joinTable.lastNameStd, ' ', joinTable.firstNameStd)");

// update the first passenger processed remarks
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_customers, 'serv_firstCustomerProcessedRemarks', 'serv_firstCustomerId', "processedRemarks");

// update the service operational name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addCaseStructureColumn("serv_statusOpName", "serv_statusOpCode", (Array)$_LANG['reservationServices']['statusOpName']);
$reportCopyInfo->populate();

// update the service financial name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addCaseStructureColumn("serv_statusFinName", "serv_statusFinCode", (Array)$_LANG['reservationServices']['statusFinName']);
$reportCopyInfo->populate();

// update the service responsible user name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_users, 'serv_userName', 'serv_userId', "CONCAT(joinTable.lastName, ' ', joinTable.firstName)");

// update service departure country
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_countries, 'serv_deptCountryName', 'serv_deptCountryCode', 'nameStd', 'code');

// update service departure city
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_cities, 'serv_deptCityName', 'serv_deptCityId', "nameStd");

// update service destination country
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_countries, 'serv_destCountryName', 'serv_destCountryCode', 'nameStd', 'code');

// update service destination city
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_cities, 'serv_destCityName', 'serv_destCityId', "nameStd");

// IMPORTED SERVICE SYSTEM DATA ///////////////////////////////////////////////////////////////////

// update the imported system name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_importedOrderSystems, 'impServ_externalSystemName', 'impServ_externalSystemId', 'name');

// OFFER DATA /////////////////////////////////////////////////////////////////////////////////////
refreshReportStatus('extractMainDataFromDb', 'updateOfferDetails', 0);

// update the currency type name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addCaseStructureColumn('off_currencyTypeName',  "off_currencyTypeCode", \App\Config\CurrencyTypes::getInstance()->getSimple());
$reportCopyInfo->populate();

// update the offer service name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addCaseStructureColumn("off_serviceName", "off_serviceCode", \App\Config\Services_Basic::getInstance()->getListLinearSimple());
$reportCopyInfo->populate();

// update the invoice row location name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addCaseStructureColumn("off_locationName", "off_locationCode", (Array)$_LANG['locations']['countriesCriteria']['long']);
$reportCopyInfo->populate();

// update offer departure country
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_countries, 'off_deptCountryName', 'off_deptCountryCode', 'nameStd', 'code');

// update offer departure city
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_cities, 'off_deptCityName', 'off_deptCityId', "nameStd");

// update offer departure station
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_stations, 'off_deptStationName', 'off_deptStationCode', 'nameStd', 'code');

// update offer destination country
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_countries, 'off_destCountryName', 'off_destCountryCode', 'nameStd', 'code');

// update offer destination city
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_cities, 'off_destCityName', 'off_destCityId', "nameStd");

// update offer destination station
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_stations, 'off_destStationName', 'off_destStationCode', 'nameStd', 'code');

// ORDER DATA /////////////////////////////////////////////////////////////////////////////////////
refreshReportStatus('extractMainDataFromDb', 'updateOrderDetails', 0);

// update the order type
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addCaseStructureColumn('ord_typeName',  "ord_typeCode", \App\Config\OrderTypes::getInstance()->getAll());
$reportCopyInfo->populate();

// update the order services type
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addCaseStructureColumn('ord_servicesTypeName',  "ord_servicesTypeCode", \App\Config\OrderServicesTypes::getInstance()->getAll());
$reportCopyInfo->populate();

// update the order corporate owner name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_corporates, 'ord_corporateOwnerAlias', 'ord_corporateOwnerId', "alias");

// update the order customer owner name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_customers, 'ord_customerOwnerName', 'ord_customerOwnerId', "CONCAT(joinTable.lastNameStd, ' ', joinTable.firstNameStd)");

// update the order contact person name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_contactPersons, 'ord_contactPersonName', 'ord_contactPersonId', "CONCAT(joinTable.lastName, ' ', joinTable.firstName)");

// update the order mice project name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_miceProjects, 'ord_miceProjectName', 'ord_miceProjectId', 'name');

// update the order responsible user name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_users, 'ord_userName', 'ord_userId', "CONCAT(joinTable.lastName, ' ', joinTable.firstName)");

// update the order reponsible department name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_departments, 'ord_userDepartmentName', 'ord_userDepartmentId', 'name');

// update the order reponsible office name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_offices, 'ord_officeName', 'ord_officeId', 'name');

// BALANCE DATA ///////////////////////////////////////////////////////////////////////////////////
refreshReportStatus('extractMainDataFromDb', 'updateBalanceDetails', 0);

// update balance details
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addJoinTable($DBTRd_balances, 'bal', 'rep.bal_id=bal.id');
$reportCopyInfo->setStructureColumns([
	'bal_number' 						=> 'bal.balanceNumber',
	'bal_date' 							=> 'bal.balanceDate',
	'bal_code' 							=> 'bal.balanceCode',
	'bal_name' 							=> 'bal.balanceName',
	'bal_typeCode' 						=> 'bal.balanceType',
	'bal_statusCode' 					=> 'bal.balanceStatus',
	'bal_virtualDate' 					=> 'bal.virtualDate',
	'bal_comments' 						=> 'bal.comments',
	'bal_respUserId' 					=> 'bal.idUser',
	'bal_respOfficeId' 					=> 'bal.idOffice',
	'bal_addUserId' 					=> 'bal.idAddUser',
	'bal_confUserId' 					=> 'bal.idConfirmUser',
	'bal_addTime' 						=> 'bal.addTime'
]);

$reportCopyInfo->populate();

// update the balance responsible user name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_users, 'bal_respUserName', 'bal_respUserId', "CONCAT(joinTable.lastName, ' ', joinTable.firstName)");

// update the balance reponsible office name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_offices, 'bal_respOfficeName', 'bal_respOfficeId', 'name');

// update the balance add user name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_users, 'bal_addUserName', 'bal_addUserId', "CONCAT(joinTable.lastName, ' ', joinTable.firstName)");

// update the balance confirmation user name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\UpdateSimple($reportTable, $availableTableColumns);
$reportCopyInfo->populateSimple($DBTRd_users, 'bal_confUserName', 'bal_confUserId', "CONCAT(joinTable.lastName, ' ', joinTable.firstName)");

// update balance type name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addCaseStructureColumn("bal_typeName",   "bal_typeCode",   (Array)$_LANG['invoiceBalance']['type']);
$reportCopyInfo->populate();

// update balance status name
$reportCopyInfo = new \App\Reports\Db\ManageRecords\Update($reportTable, 'rep', $availableTableColumns);
$reportCopyInfo->addCaseStructureColumn("bal_statusName", "bal_statusCode", (Array)$_LANG['invoiceBalance']['status']);
$reportCopyInfo->populate();
