<?php
 namespace App\QuickReports\PivotTables\Source; class ClientInvoiceRows extends GeneralAbstract { public $invoiceDateStart = null; public $invoiceDateEnd = null; protected function getDbTable() { global $DBTPivotTablesData_clientInvoiceRows; return $DBTPivotTablesData_clientInvoiceRows; } public function initializeFilters() { $this->invoiceDateStart = (TODAY_YEAR-5).'-01-01'; $this->invoiceDateEnd = TODAY_ISO; } protected function checkFilters() { if (!validDate($this->invoiceDateStart) or !validDate($this->invoiceDateEnd)) { throw new Exception( sprintf('Invalid invoice date interval [%s - %s].', $this->invoiceDateStart, $this->invoiceDateEnd) ); } } protected function populateData() { $pivotCfg = $this->getPivotConfig(); \DB::startTransaction(); $pivotCfg->progress_step = 1; \DB::commitTransaction(); $this->populateData_invoiceRows(); \DB::startTransaction(); $pivotCfg->progress_step = 2; \DB::commitTransaction(); $this->populateData_services(); \DB::startTransaction(); $pivotCfg->progress_step = 3; \DB::commitTransaction(); $this->populateData_client(); \DB::startTransaction(); $pivotCfg->progress_loopRecords = null; $pivotCfg->progress_memoryUsage = memory_get_peak_usage(true); \DB::commitTransaction(); } protected function populateData_invoiceRows() { global $DBTRd_clientInvoices, $DBTRd_clientInvoiceRows; $db = \DB::getInstance(); $copyDataSql = "
			INSERT INTO `{$this->dbTable}` (
				id, invoiceDate, invoiceDateY, invoiceDateM, invoiceDateYM,
				dueDate, dueDays,
				dueDateY, dueDateM, dueDateYM,
				idService, serviceCode, rowType, rowValOrg, rowVatOrg, rowTtlOrg, 
				rowProfitOrg, rowCostOrg,
				rowVatPercent, rowVatText,
				idCorporate, idCorporateOffice, idCustomer, currency, invoiceIdUser, invoiceIdOffice,
				cashedStatus, cashedOrg,
				cashDate, cashDays,
				rowValDef, rowVatDef, rowTtlDef, 
				rowProfitDef, rowCostDef,
				rowValWrk, rowVatWrk, rowTtlWrk, 
				rowProfitWrk, rowCostWrk,
				duePayOrg,
				invoiceAddDate, invoiceIssueDate, invoiceVoidDate
			)
			SELECT
				invRow.id, inv.invoiceDate, YEAR(inv.invoiceDate), MONTH(inv.invoiceDate), DATE_FORMAT(inv.invoiceDate, '%Y%m'),
				inv.dueDate, IF (inv.dueDate IS NULL, NULL, DATEDIFF(inv.dueDate, inv.invoiceDate)),
				IF (inv.dueDate IS NULL, NULL, YEAR(inv.dueDate)), IF (inv.dueDate IS NULL, NULL, MONTH(inv.dueDate)), IF (inv.dueDate IS NULL, NULL, DATE_FORMAT(inv.dueDate, '%Y%m')),
				invRow.idService, invRow.serviceCode, invRow.rowType, invRow.rowValue, invRow.rowVat, invRow.rowValue + invRow.rowVat, 
			    invRow.rowOfferEstProfit, invRow.rowValue + invRow.rowVat - invRow.rowOfferEstProfit,
				invRow.vatPercent, invRow.vatText,
				inv.idCorporate, inv.idCorporateOffice, inv.idCustomer, inv.currency, inv.idUser, inv.idOffice,
				IF (inv.cashedStatus IN ('t', 'o'), 't', inv.cashedStatus), IF (inv.cashedStatus IN ('t', 'o'), (invRow.rowValue+invRow.rowVat), (invRow.rowValue+invRow.rowVat)*inv.cashedTotal/inv.invoiceTotal),
				inv.lastCashingDate, DATEDIFF(inv.lastCashingDate, inv.invoiceDate),
				invRow.rowValue * inv.currencyRate, invRow.rowVat * inv.currencyRate, (invRow.rowValue + invRow.rowVat) * inv.currencyRate, 
			    invRow.rowOfferEstProfit * inv.currencyRate, (invRow.rowValue + invRow.rowVat - invRow.rowOfferEstProfit) * inv.currencyRate,
				invRow.rowValue * inv.currencyRate / inv.workCurrencyRate, invRow.rowVat * inv.currencyRate / inv.workCurrencyRate, (invRow.rowValue + invRow.rowVat) * inv.currencyRate / inv.workCurrencyRate, 
			    invRow.rowOfferEstProfit * inv.currencyRate / inv.workCurrencyRate, (invRow.rowValue + invRow.rowVat - invRow.rowOfferEstProfit) * inv.currencyRate / inv.workCurrencyRate,
				IF (inv.cashedStatus IN ('t', 'o'), 0, (invRow.rowValue + invRow.rowVat) - ( (invRow.rowValue + invRow.rowVat) * inv.cashedTotal / inv.invoiceTotal) ),
				CAST(inv.addTime AS DATE), CAST(inv.issueTime AS DATE), CAST(inv.voidTime AS DATE)
			FROM `{$DBTRd_clientInvoices}` AS inv
				INNER JOIN `{$DBTRd_clientInvoiceRows}` AS invRow ON (inv.id=invRow.idInvoice)
			WHERE (inv.invoiceDate BETWEEN ? AND ?)
		"; $copyDataEx = $db->Execute($copyDataSql, [$this->invoiceDateStart, $this->invoiceDateEnd]); if (!$copyDataEx) { throw new \DBException('Could not populate pivot table info (step1 - invoice rows).'); } $updateSuppSQL = "
			UPDATE `{$this->dbTable}` AS src
				INNER JOIN `{$DBTRd_clientInvoiceRows}` AS invRow ON (src.id=invRow.id)
				INNER JOIN `{$DBTRd_clientInvoices}` inv ON (invRow.idInvoice=inv.id)
			SET
				src.cashedDef = src.cashedOrg*inv.currencyRate,
				src.cashedWrk = src.cashedOrg*inv.currencyRate/inv.workCurrencyRate,
				src.duePayDef = src.duePayOrg*inv.currencyRate,
				src.duePayWrk = src.duePayOrg*inv.currencyRate/inv.workCurrencyRate
		"; $updateSuppEx = $db->Execute($updateSuppSQL); if (!$updateSuppEx) { throw new \DBException('Could not populate pivot table info (step1 - row values).'); } } protected function populateData_services() { global $DBTRd_offers, $DBTRd_orders, $DBTRd_services; $db = \DB::getInstance(); $updateDataOrdersSql = "
			UPDATE
				`{$this->dbTable}` AS src INNER JOIN `{$DBTRd_services}` AS serv ON (src.idService=serv.id)
				INNER JOIN `{$DBTRd_orders}` AS ord ON (ord.id=serv.idOrder)
			SET
				src.orderDate = ord.date,
				src.orderDateY = YEAR(ord.date),
				src.orderDateM = MONTH(ord.date),
				src.orderDateYM = DATE_FORMAT(ord.date, '%Y%m')
		"; $updateDataOrdersEx = $db->Execute($updateDataOrdersSql); if (!$updateDataOrdersEx) { throw new \DBException('Could not populate pivot table info (step2 - services - order).'); } $updateDataOfferSql = "
			UPDATE
				`{$this->dbTable}` AS src
					INNER JOIN `{$DBTRd_services}` AS serv ON (src.idService=serv.id)
					LEFT JOIN `{$DBTRd_offers}` AS off ON (serv.id=off.idService)
			SET
				src.offerStartDate = DATE(off.startDatetime),
				src.offerStartDateY = YEAR(off.startDatetime),
				src.offerStartDateM = MONTH(off.startDatetime),
				src.offerStartDateYM = DATE_FORMAT(off.startDatetime, '%Y%m'),
				src.offerDestCountryCode = off.destinationCountryCode,
				src.offerDestCityId = off.destinationCityId,
				src.idSupplier = off.idSupplier,
				src.serviceIdUser = serv.idUser,
				src.serviceIdOffice = serv.idOffice
		"; $updateDataOfferEx = $db->Execute($updateDataOfferSql); if (!$updateDataOfferEx) { throw new \DBException('Could not populate pivot table info (step2 - services - offer).'); } } protected function populateData_client() { global $DBTRd_customers, $DBTRd_corporates, $DBTRd_corporateOffices; $db = \DB::getInstance(); $updateCustSQL = "
			UPDATE
				`{$this->dbTable}` AS src INNER JOIN `{$DBTRd_customers}` AS cust ON (src.idCustomer=cust.id)
			SET
				src.clientType='DC',
				src.idCustomerSegment = cust.idSegment,
				src.clientCountryCode = cust.countryCode
		"; $updateCustEx = $db->Execute($updateCustSQL); if (!$updateCustEx) { throw new \DBException('Could not populate pivot table customer info (step3 - customers).'); } $updateCorpSQL = "
			UPDATE
				`{$this->dbTable}` AS src INNER JOIN `{$DBTRd_corporates}` AS corp ON (src.idCorporate=corp.id)
			SET
				src.clientType = corp.type,
				src.idCorporateSegment = corp.idSegment
		"; $updateCorpEx = $db->Execute($updateCorpSQL); if (!$updateCorpEx) { throw new \DBException('Could not populate pivot table corporate info (step3 - corporates).'); } $updateCorpOffSQL = "
			UPDATE
				`{$this->dbTable}` AS src INNER JOIN `{$DBTRd_corporateOffices}` AS corpOff ON (src.idCorporateOffice=corpOff.id)
			SET
				src.clientCountryCode = corpOff.countryCode
		"; $updateCorpOffEx = $db->Execute($updateCorpOffSQL); if (!$updateCorpOffEx) { throw new \DBException('Could not populate pivot table corporate office info (step3 - corporate offices).'); } } } 