<?php

namespace App\Reports\Sources\CashingPaymentDocs;


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

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

		// INITS //////////////////////////////////////////////////////////////////////////////////

		global $DBTRd_clientInvoiceCashings, $DBTRd_clientInvoices, $DBTRd_importedDocs;
		global $DBTRd_supplierInvoicePayments, $DBTRd_supplierInvoices, $DBTRd_cashbookDocuments;
		global $DBTRd_corporates, $DBTRd_customers, $DBTRd_suppliers;

		$reportTable = $this->tableManager->getTableName();

		$activeColumns = array_keys( $this->columnsManager->getStdColumns() );

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

		$CFG = \Engine\Loader_Cfg::getInstance()->getGlobal();

		///////////////////////////////////////////////////////////////////////////////////////////
		// CREATE SQL FOR COPYING CASHING DOCS ////////////////////////////////////////////////////
		///////////////////////////////////////////////////////////////////////////////////////////

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

		// refresh report status
		$this->reportGenerationStatusUpdate('extractMainDataFromDb', 'runningCashingSQL', 0);
		$repCashingData = new \App\Reports\Db\ManageRecords\Insert($reportTable);
		$repCashingData->setMainTable($DBTRd_clientInvoiceCashings,	'doc');
		$repCashingData->addJoinTable($DBTRd_clientInvoices,		'inv',			'doc.idInvoice=inv.id',			'INNER');
		$repCashingData->addJoinTable($DBTRd_corporates,			'invCorp',		'inv.idCorporate=invCorp.id',	'LEFT');
		$repCashingData->addJoinTable($DBTRd_customers, 			'invCust',		'inv.idCustomer=invCust.id',	'LEFT');
		$repCashingData->addJoinTable($DBTRd_importedDocs,			'impDoc',		'doc.idImportedDoc=impDoc.id',	'LEFT');

		$cols = [
			'doc_id'					=> 'doc.id',
			'doc_sourceCode'			=> '"cashing"',
			'doc_typeCode'				=> 'doc.docType',
			'doc_seriesId'				=> 'doc.idCashingSeries',
			'doc_number'				=> 'doc.docNumber',
			'doc_date'					=> 'doc.docDate',
			'doc_bankAccountId'			=> 'doc.idBankAccount',
			'doc_explanations'			=> 'doc.explanations',
			'doc_comments'				=> 'doc.comments',

			'doc_value'					=> 'doc.value',
			'doc_currency'				=> 'doc.currency',
			'doc_currencyRate'			=> 'doc.currencyRate',
			'doc_workCurrencyRate'		=> 'doc.workCurrencyRate',

			'doc_respUserId'			=> 'doc.idUser',
			'doc_respOfficeId'			=> 'doc.idOffice',
			'doc_addUserId'				=> 'doc.idAddUser',
			'doc_issueUserId'			=> 'doc.idIssueUser',
			'doc_blockUserId'			=> 'doc.idBlockUser',

			'doc_addTime'				=> 'doc.addTime',
			'doc_issueTime'				=> 'doc.issueTime',
			'doc_blockTime'				=> 'doc.blockTime',

			'impDoc_id'					=> 'doc.idImportedDoc',
			'impDoc_value'				=> 'impDoc.docValue',
			'impDoc_currency'			=> 'impDoc.docCurrency',
			'impDoc_externalSystemId'	=> 'impDoc.idExternalSystem',
			'impDoc_systemStatusCode'	=> 'impDoc.systemStatus',
			'impDoc_processStatusCode'	=> 'impDoc.status',

			'inv_id'					=> 'inv.id',
			'inv_number'				=> 'inv.invoiceNumber',
			'inv_seriesId'				=> 'inv.idInvoiceSeries',
			'inv_date'					=> 'inv.invoiceDate',
			'inv_dueDate'				=> 'inv.dueDate',
			'inv_value'					=> 'inv.invoiceValue',
			'inv_vat' 					=> 'inv.invoiceVat',
			'inv_total' 				=> 'inv.invoiceTotal',
			'inv_currency'				=> 'inv.currency',
		];

		$repCashingData->setStructureColumns($cols);
		$repCashingData->addStructureColumn("doc_categoryCode",     	"IF(doc.value>0, 'cashing', 'payment')");
		$repCashingData->addStructureColumn("doc_dateYM", 			    "DATE_FORMAT(doc.docDate, '%Y-%m-01')");
		$repCashingData->addStructureColumn("doc_dateY", 	            "YEAR(doc.docDate)");
		$repCashingData->addStructureColumn("doc_valueDefCurr",     	"doc.value*doc.currencyRate");
		$repCashingData->addStructureColumn("doc_valueWorkCurr",     	"doc.value*doc.currencyRate/doc.workCurrencyRate");
		$repCashingData->addStructureColumn("doc_partnerTypeCode",		"IF(inv.idCorporate, 'corporate', 'customer')", "inv");

		$repCashingData->addStructureColumn("inv_idCorporate",			"IF(invCorp.idRealCorporate, invCorp.idRealCorporate, inv.idCorporate)", "invCorp");
		$repCashingData->addStructureColumn("inv_idCustomer",			"IF(invCust.idRealCustomer, invCust.idRealCustomer, inv.idCustomer)", "invCust");

		$cashIncludedTypesArr = [];
		foreach ($CFG['cashBook']['cashBookView']['clientAcceptedDocTypes'] as $type=>$settings) {
			if ($settings) {
				$cashIncludedTypesArr[] = "'".$type."'";
			}
		}
		$repCashingData->addStructureColumn("doc_isCash",				"IF(doc.docType IN (".implode(', ', $cashIncludedTypesArr)."), 1, 0)");

		$repCashingData->setActiveColumns($activeColumns);

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

		$repCashingData->addFilter('doc.voidTime', 'IS NULL');

		if ($stdFilters['docDate']['start']) {
			$repCashingData->addFilter('doc.docDate', '>=', $stdFilters['docDate']['start']);
		}
		if ($stdFilters['docDate']['end']) {
			$repCashingData->addFilter('doc.docDate', '<=', $stdFilters['docDate']['end']);
		}

		if ($stdFilters['idDocRespUsers']) {
			$repCashingData->addFilter('doc.idUser' , 'IN', $stdFilters['idDocRespUsers']);
		}
		if ($stdFilters['idDocRespOffices']) {
			$repCashingData->addFilter('doc.idOffice' , 'IN', $stdFilters['idDocRespOffices']);
		}

		$repCashingData->populate();

		///////////////////////////////////////////////////////////////////////////////////////////
		// CREATE SQL FOR COPYING PAYMENT DOCS ////////////////////////////////////////////////////
		///////////////////////////////////////////////////////////////////////////////////////////

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

		$repPaymentData = new \App\Reports\Db\ManageRecords\Insert($reportTable);
		$repPaymentData->setMainTable($DBTRd_supplierInvoicePayments,	'doc');
		$repPaymentData->addJoinTable($DBTRd_supplierInvoices,			'inv',		'doc.idInvoice=inv.id',			'INNER');
		$repPaymentData->addJoinTable($DBTRd_suppliers,					'supp',		'inv.idSupplier=supp.id',		'INNER');
		$repPaymentData->addJoinTable($DBTRd_importedDocs,				'impDoc',	'doc.idImportedDoc=impDoc.id',	'LEFT');

		$cols = [
			'doc_id'					=> 'doc.id',
			'doc_sourceCode'			=> '"payment"',
			'doc_typeCode'				=> 'doc.docType',
			'doc_seriesId'				=> 'doc.idPaymentSeries',
			'doc_number'				=> 'doc.docNumber',
			'doc_date'					=> 'doc.docDate',
			'doc_bankAccountId'			=> 'doc.idBankAccount',
			'doc_explanations'			=> 'doc.explanations',
			'doc_comments'				=> 'doc.comments',

			'doc_currency'				=> 'doc.currency',
			'doc_currencyRate'			=> 'doc.currencyRate',
			'doc_workCurrencyRate'		=> 'doc.workCurrencyRate',

			'doc_partnerTypeCode'		=> '"supplier"',

			'doc_respUserId'			=> 'doc.idUser',
			'doc_respOfficeId'			=> 'doc.idOffice',
			'doc_addUserId'				=> 'doc.idAddUser',
			'doc_issueUserId'			=> 'doc.idIssueUser',
			'doc_blockUserId'			=> 'doc.idBlockUser',

			'doc_addTime'				=> 'doc.addTime',
			'doc_issueTime'				=> 'doc.issueTime',
			'doc_blockTime'				=> 'doc.blockTime',

			'impDoc_id'					=> 'doc.idImportedDoc',
			'impDoc_value'				=> 'impDoc.docValue',
			'impDoc_currency'			=> 'impDoc.docCurrency',
			'impDoc_externalSystemId'	=> 'impDoc.idExternalSystem',
			'impDoc_systemStatusCode'	=> 'impDoc.systemStatus',
			'impDoc_processStatusCode'	=> 'impDoc.status',

			'inv_id'					=> 'inv.id',
			'inv_number'				=> 'inv.invoiceNumber',
			'inv_series'				=> 'inv.invoiceSeries',
			'inv_date'					=> 'inv.invoiceDate',
			'inv_dueDate'				=> 'inv.dueDate',
			'inv_value'					=> 'inv.invoiceValue',
			'inv_vat' 					=> 'inv.invoiceVat',
			'inv_total' 				=> 'inv.invoiceTotal',
			'inv_currency'				=> 'inv.currency',
		];

		$repPaymentData->setStructureColumns($cols);
		$repPaymentData->addStructureColumn("doc_value",     			"-1*doc.value");
		$repPaymentData->addStructureColumn("doc_categoryCode",     	"IF(doc.value<0, 'cashing', 'payment')");
		$repPaymentData->addStructureColumn("doc_dateYM", 			    "DATE_FORMAT(doc.docDate, '%Y-%m-01')");
		$repPaymentData->addStructureColumn("doc_dateY", 	            "YEAR(doc.docDate)");
		$repPaymentData->addStructureColumn("doc_valueDefCurr",     	"-1*doc.value*doc.currencyRate");
		$repPaymentData->addStructureColumn("doc_valueWorkCurr",     	"-1*doc.value*doc.currencyRate/doc.workCurrencyRate");

		$repPaymentData->addStructureColumn("inv_idSupplier",			"IF(supp.idRealSupplier, supp.idRealSupplier, inv.idSupplier)", "supp");

		$payIncludedTypesArr = [];
		foreach ($CFG['cashBook']['cashBookView']['supplierAcceptedDocTypes'] as $type=>$settings) {
			if ($settings) {
				$payIncludedTypesArr[] = "'".$type."'";
			}
		}
		$repCashingData->addStructureColumn("doc_isCash",				"IF(doc.docType IN (".implode(', ', $payIncludedTypesArr)."), 1, 0)");

		$repPaymentData->setActiveColumns($activeColumns);

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

		$repPaymentData->addFilter('doc.deleted', '=', 0);

		if ($stdFilters['docDate']['start']) {
			$repPaymentData->addFilter('doc.docDate', '>=', $stdFilters['docDate']['start']);
		}
		if ($stdFilters['docDate']['end']) {
			$repPaymentData->addFilter('doc.docDate', '<=',$stdFilters['docDate']['end']);
		}

		if ($stdFilters['idDocRespUsers']) {
			$repPaymentData->addFilter('doc.idUser' , 'IN', $stdFilters['idDocRespUsers']);
		}
		if ($stdFilters['idDocRespOffices']) {
			$repPaymentData->addFilter('doc.idOffice' , 'IN', $stdFilters['idDocRespOffices']);
		}

		$repPaymentData->populate();

		///////////////////////////////////////////////////////////////////////////////////////////
		// CREATE SQL FOR COPYING CASHBOOK DOCS ///////////////////////////////////////////////////
		///////////////////////////////////////////////////////////////////////////////////////////

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

		$repCashbookData = new \App\Reports\Db\ManageRecords\Insert($reportTable);
		$repCashbookData->setMainTable($DBTRd_cashbookDocuments,	'doc');

		$cols = [
			'doc_id'					=> 'doc.id',
			'doc_sourceCode'			=> '"cashbook"',
			'doc_categoryCode'			=> 'doc.category',
			'doc_typeCode'				=> 'doc.type',
			'doc_number'				=> 'doc.number',
			'doc_date'					=> 'doc.date',
			'doc_bankAccountId'			=> 'doc.idBankAccount',
			'doc_explanations'			=> 'doc.explanations',
			'doc_comments'				=> 'doc.comments',
			'doc_partnerName'			=> 'doc.partner',

			'doc_value'					=> 'doc.value',
			'doc_currency'				=> 'doc.currency',
			'doc_currencyRate'			=> 'doc.currencyRate',
			'doc_workCurrencyRate'		=> 'doc.workCurrencyRate',

			'doc_respUserId'			=> 'doc.idUser',
			'doc_respOfficeId'			=> 'doc.idOffice',
			'doc_addUserId'				=> 'doc.idAddUser',
			'doc_issueUserId'			=> 'doc.idIssueUser',
			'doc_blockUserId'			=> 'doc.idBlockUser',

			'doc_addTime'				=> 'doc.addTime',
			'doc_issueTime'				=> 'doc.issueTime',
			'doc_blockTime'				=> 'doc.blockTime',
		];

		$repCashbookData->setStructureColumns($cols);
		$repCashbookData->addStructureColumn("doc_dateYM", 			    "DATE_FORMAT(doc.date, '%Y-%m-01')");
		$repCashbookData->addStructureColumn("doc_dateY", 	            "YEAR(doc.date)");
		$repCashbookData->addStructureColumn("doc_valueDefCurr",     	"doc.value*doc.currencyRate");
		$repCashbookData->addStructureColumn("doc_valueWorkCurr",     	"doc.value*doc.currencyRate/doc.workCurrencyRate");


		$cashIncludedInCashbook = [];
		foreach ($CFG['cashBook']['documentTypes']['cashing'] as $type=>$settings) {
			if ($settings['includeInCashbook']) {
				$cashIncludedInCashbook[] = "'".$type."'";
			}
		}

		$payIncludedInCashbook = [];
		foreach ($CFG['cashBook']['documentTypes']['payment'] as $type=>$settings) {
			if ($settings['includeInCashbook']) {
				$payIncludedInCashbook[] = "'".$type."'";
			}
		}

		$repCashbookData->addStructureColumn("doc_isCash",				"IF(doc.category='cashing' AND doc.type IN (".implode(', ', $cashIncludedInCashbook)."), 1, IF(doc.category='payment' AND doc.type IN (".implode(', ', $payIncludedInCashbook)."), 1, 0))");

		$repCashbookData->setActiveColumns($activeColumns);

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

		$repCashbookData->addFilter('doc.deleted', '=', 0);

		if ($stdFilters['docDate']['start']) {
			$repCashbookData->addFilter('doc.date', '>=', $stdFilters['docDate']['start']);
		}
		if ($stdFilters['docDate']['end']) {
			$repCashbookData->addFilter('doc.date', '<=',$stdFilters['docDate']['end']);
		}

		if ($stdFilters['idDocRespUsers']) {
			$repCashbookData->addFilter('doc.idUser' , 'IN', $stdFilters['idDocRespUsers']);
		}
		if ($stdFilters['idDocRespOffices']) {
			$repCashbookData->addFilter('doc.idOffice' , 'IN', $stdFilters['idDocRespOffices']);
		}

		$repCashbookData->populate();

	}

}
