<?php

namespace App\Reports\Sources\ClientInvoices;


class PopulateInitialImport extends \App\Reports\Sources\PopulateAbstract
{

	/**
	 * {@inheritDoc}
	 * @see \App\Reports\Sources\PopulateAbstract::populate()
	 */
	public function populate() :void
	{

		global $DBTRd_clientInvoices, $DBTRd_docSeries, $DBTRd_corporates, $DBTRd_corporateOffices, $DBTRd_customers;
		global $DBTRd_users, $DBTRd_offices, $DBTRd_autoProcessRules, $DBTRd_departments;

		// refresh report status
		$this->reportGenerationStatusUpdate('extractMainDataFromDb', 'runningMainSQL', 0);

		$reportCopyInfo = new \App\Reports\Db\ManageRecords\Insert( $this->tableManager->getTableName() );
		$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", 			"YEAR(inv.invoiceDate)", "inv");
		$reportCopyInfo->addStructureColumn("invoiceDueDateYM", 		"DATE_FORMAT(inv.dueDate, '%Y-%m-01')", "inv");
		$reportCopyInfo->addStructureColumn("invoiceDueDateY", 			"YEAR(inv.dueDate)", "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( array_keys( $this->columnsManager->getStdColumns() ) );

		$stdFilters = $this->filtersManager->getStdFilters();

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

		// invoice date filter
		if ($stdFilters['invoiceDate']['start']) {
			$reportCopyInfo->addFilter('inv.invoiceDate', '>=', $stdFilters['invoiceDate']['start']);
		}
		if ($stdFilters['invoiceDate']['end']) {
			$reportCopyInfo->addFilter('inv.invoiceDate', '<=',$stdFilters['invoiceDate']['end']);
		}
		// invoice due date filter
		if ($stdFilters['invoiceDueDate']['start']) {
			$reportCopyInfo->addFilter('inv.dueDate', '>=', $stdFilters['invoiceDueDate']['start']);
		}
		if ($stdFilters['invoiceDueDate']['end']) {
			$reportCopyInfo->addFilter('inv.dueDate', '<=', $stdFilters['invoiceDueDate']['end']);
		}

		// customer(s) filter
		if ($stdFilters['idCustomers']) {
			$reportCopyInfo->addFilter('inv.idCustomer', 'IN', $this->filtersManager->getDuplicateEntitiesGroup('idCustomers'));
		}

		// corporate(s) filter
		if ($stdFilters['idCorporates']) {
			$reportCopyInfo->addFilter('inv.idCorporate', 'IN', $this->filtersManager->getDuplicateEntitiesGroup('idCorporates'));
		}

		// filter by corporate groups
		if ($stdFilters['corporateGroups']) {
			$corporateGroupsHelper = new \App\ReportsData\Helpers\PartnerGroups\Corporate();
			$corporateGroupsHelper->setGroupElementsLinear($stdFilters['corporateGroups']);
			$corpIds = $corporateGroupsHelper->getPartnerIdsList();
			if ($corpIds) {
				$reportCopyInfo->addFilter('inv.idCorporate', 'IN', $corpIds);
			} else {
				$reportCopyInfo->addCustomFilter("0");
			}
		}

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

		// last cash date filter
		if ($stdFilters['invoiceCashedDate']['start']) {
			$reportCopyInfo->addFilter('inv.lastCashingDate', '>=', $stdFilters['invoiceCashedDate']['start']);
		}
		if ($stdFilters['invoiceCashedDate']['end']) {
			$reportCopyInfo->addFilter('inv.lastCashingDate', '<=', $stdFilters['invoiceCashedDate']['end']);
		}

		// invoice cashed status
		if ($stdFilters['cashedStatus']) {
			$reportCopyInfo->addFilter('inv.cashedStatus' , 'IN', $stdFilters['cashedStatus']);
		}

		// issued invoice status
		if ($stdFilters['issued']!==null) {
			if ($stdFilters['issued']) {
				$reportCopyInfo->addFilter('inv.issueTime' , 'IS NOT NULL');
			} else {
				$reportCopyInfo->addFilter('inv.issueTime' , 'IS NULL');
			}
		}

		// temporary status
		if ($stdFilters['temporary']!==null) {
			if ($stdFilters['temporary']) {
				$reportCopyInfo->addFilter('inv.temporary' , '=', 1);
			} else {
				$reportCopyInfo->addFilter('inv.temporary' , '=', 0);
			}
		}

		// voided invoice status
		if ($stdFilters['voided']!==null) {
			if ($stdFilters['voided']) {
				$reportCopyInfo->addFilter('inv.voidTime' , 'IS NOT NULL');
			} else {
				$reportCopyInfo->addFilter('inv.voidTime' , 'IS NULL');
			}
		}

		// filter by resp users
		if ($stdFilters['idRespUsers']) {
			$reportCopyInfo->addFilter('inv.idUser', 'IN', $stdFilters['idRespUsers']);
		}
		// filter by resp offices
		if ($stdFilters['idRespOffices']) {
			$reportCopyInfo->addFilter('inv.idOffice', 'IN', $stdFilters['idRespOffices']);
		}
		// filter by resp depts
		if ($stdFilters['idRespDepartments']) {
			$reportCopyInfo->addFilter('respUser.idDepartment', 'IN', $stdFilters['idRespDepartments']);
		}

		$reportCopyInfo->populate();

	}


}