<?php
 namespace App\QuickReports\PivotTables\Source; class OrderServices extends GeneralAbstract { public $orderDateStart = null; public $orderDateEnd = null; protected function getDbTable() { global $DBTPivotTablesData_orderServices; return $DBTPivotTablesData_orderServices; } public function initializeFilters() { $this->orderDateStart = (TODAY_YEAR-5).'-01-01'; $this->orderDateEnd = TODAY_ISO; } protected function checkFilters() { if (!validDate($this->orderDateStart) or !validDate($this->orderDateEnd)) { throw new Exception( sprintf('Invalid order date interval [%s - %s].', $this->orderDateStart, $this->orderDateEnd) ); } } protected function populateData() { $pivotCfg = $this->getPivotConfig(); \DB::startTransaction(); $pivotCfg->progress_step = 1; \DB::commitTransaction(); $this->populateData_services(); \DB::startTransaction(); $pivotCfg->progress_step = 2; \DB::commitTransaction(); $this->populateData_invoices(); \DB::startTransaction(); $pivotCfg->progress_step = 3; \DB::commitTransaction(); $this->populateData_balances(); \DB::startTransaction(); $pivotCfg->progress_loopRecords = null; $pivotCfg->progress_memoryUsage = memory_get_peak_usage(true); \DB::commitTransaction(); } private function populateData_services() { global $DBTRd_offers, $DBTRd_orders, $DBTRd_services; $db = \DB::getInstance(); $copyDataSql = "
			INSERT INTO `{$this->dbTable}` (
				id, idOrder, orderDate, orderDateY, orderDateM, orderDateYM,
				orderType, orderServicesType, idMiceProject,
				orderStartDate, orderStartDateY, orderStartDateM, orderStartDateYM,
				orderEndDate, orderEndDateY, orderEndDateM, orderEndDateYM,
				orderIdUser, orderIdOffice, orderIdDepartment,
				idExternalSystem, pax, serviceCode, serviceName, serviceCategory,
				statusOp, statusFin, agencyCostCenter, idUser, idOffice,
				ticketsDate, ticketsDateY, ticketsDateM, ticketsDateYM,
				addDate, cancelDate, refundDate,
				idBalance, balanced, idInvoice, invoicesCashedStatus,
				offerStartDate, offerStartDateY, offerStartDateM, offerStartDateYM, offerDuration,
				offerEndDate, offerEndDateY, offerEndDateM, offerEndDateYM,
				offerIdSupplier, offerIdSupplierProduct, offerDeptCountryCode, offerDeptCityId, offerDestCountryCode, offerDestCityId,
				offerTotalValueOrg, offerCurrency, offerCurrencyType, offerCurrencyDate,
				offerTotalValueDef, offerTotalValueWrk,
				offerProfitValueOrg, 
			    offerCostValueOrg,
				offerProfitValueDef, 
			    offerCostValueDef,
				offerProfitValueWrk, 
			    offerCostValueWrk,
				invoiceValOrg, invoiceVatOrg, invoiceTtlOrg, invoiceCurrency,
				invoiceValDef, invoiceVatDef, invoiceTtlDef,
				invoiceValWrk, invoiceVatWrk, invoiceTtlWrk,
				balanceProfitDef
			)
			SELECT
				serv.id, ord.id, ord.date, YEAR(ord.date), MONTH(ord.date), DATE_FORMAT(ord.date, '%Y%m'),
				ord.orderType, ord.servicesType, ord.idMiceProject,
				ord.startDate, YEAR(ord.startDate), MONTH(ord.startDate), DATE_FORMAT(ord.startDate, '%Y%m'),
				ord.endDate, YEAR(ord.endDate), MONTH(ord.endDate), DATE_FORMAT(ord.endDate, '%Y%m'),
				ord.idUser, ord.idOffice, ord.idDepartment,
				serv.idExternalSystem, serv.pax, serv.serviceCode, serv.serviceName, serv.serviceCfgCat,
				serv.statusOp, serv.statusFin, serv.agencyCostCenter, serv.idUser, serv.idOffice,
				serv.passengersTicketsDate, YEAR(serv.passengersTicketsDate), MONTH(serv.passengersTicketsDate), DATE_FORMAT(serv.passengersTicketsDate, '%Y%m'),
				CAST(serv.addTime AS DATE), IF(serv.statusOp='XX', CAST(serv.lastUpdateTime AS DATE), NULL), serv.refundDate,
				serv.idBalance, !(!serv.idBalance), serv.idRelevantInvoice, serv.invoiceCashedStatus,
				off.startDatetime, YEAR(off.startDatetime), MONTH(off.startDatetime), DATE_FORMAT(off.startDatetime, '%Y%m'), off.duration,
				off.endDatetime, YEAR(off.endDatetime), MONTH(off.endDatetime), DATE_FORMAT(off.endDatetime, '%Y%m'),
				off.idSupplier, off.idSupplierProduct, off.departureCountryCode, off.departureCityId, off.destinationCountryCode, off.destinationCityId,
				off.offerTotal, off.currency, off.currencyType, off.currencyDate,
				off.offerTotal*off.currencyRate, off.offerTotal*off.currencyRate/off.workCurrencyRate ,
				(off.commissionValue+off.commissionVat+off.serviceFeeValue+off.serviceFeeVat-off.discountValue-off.discountVat), 
			    netCompanyTotal,
				(off.commissionValue + off.commissionVat + off.serviceFeeValue + off.serviceFeeVat - off.discountValue - off.discountVat) * off.currencyRate, 
			    netCompanyTotal * off.currencyRate,
				(off.commissionValue + off.commissionVat + off.serviceFeeValue + off.serviceFeeVat - off.discountValue - off.discountVat) * off.currencyRate / off.workCurrencyRate, 
			    netCompanyTotal * off.currencyRate / off.workCurrencyRate,
				serv.invoiceValue, serv.invoiceVat, serv.invoiceTotal, serv.invoiceCurrency,
				serv.invoiceValue*serv.invoiceCurrencyRate, serv.invoiceVat*serv.invoiceCurrencyRate, serv.invoiceTotal*serv.invoiceCurrencyRate,
				serv.invoiceValue*serv.invoiceCurrencyRate/serv.invoiceWorkCurrencyRate, serv.invoiceVat*serv.invoiceCurrencyRate/serv.invoiceWorkCurrencyRate, serv.invoiceTotal*serv.invoiceCurrencyRate/serv.invoiceWorkCurrencyRate,
				serv.balanceProfitDefCurr
			FROM `{$DBTRd_services}` AS serv
				INNER JOIN `{$DBTRd_orders}` AS ord ON (ord.id=serv.idOrder)
				LEFT JOIN `{$DBTRd_offers}` AS off ON (off.idService=serv.id)
			WHERE (ord.date BETWEEN ? AND ?) AND serv.idPackageService = 0
		"; $copyDataEx = $db->Execute($copyDataSql, [$this->orderDateStart, $this->orderDateEnd]); if (!$copyDataEx) { throw new \DBException('Could not populate pivot table info (step1 - services).'); } } private function populateData_invoices() { global $DBTRd_clientInvoices; $db = \DB::getInstance(); $copyInvoiceDataSql = "
			UPDATE `{$this->dbTable}` AS dest, `{$DBTRd_clientInvoices}` AS inv
			SET
				dest.invoiceDate			= inv.invoiceDate,
				dest.invoiceDateY			= YEAR(inv.invoiceDate),
				dest.invoiceDateM			= MONTH(inv.invoiceDate),
				dest.invoiceDateYM			= DATE_FORMAT(inv.invoiceDate, '%Y%m'),
				dest.invoiceIdCorporate		= inv.idCorporate,
				dest.invoiceIdCustomer		= inv.idCustomer,
				dest.invoiceClientType		= IF (inv.idCorporate, 'CC', 'DC'),
				dest.invoiceIdUser			= inv.idUser,
				dest.invoiceIdOffice		= inv.idOffice,
				dest.invoiceDueDate			= inv.dueDate,
				dest.invoiceCashDate		= inv.lastCashingDate
			WHERE dest.idInvoice = inv.id
		"; $copyInvoiceDataEx = $db->Execute($copyInvoiceDataSql); if (!$copyInvoiceDataEx) { throw new \DBException('Could not populate pivot table info (step2 - invoices).'); } } private function populateData_balances() { global $DBTRd_balances; $db = \DB::getInstance(); $copyInvoiceDataSql = "
			UPDATE `{$this->dbTable}` AS dest, `{$DBTRd_balances}` AS bal
			SET
				dest.balanceDate			= bal.balanceDate,
				dest.balanceDateY			= YEAR(bal.balanceDate),
				dest.balanceDateM			= MONTH(bal.balanceDate),
				dest.balanceDateYM			= DATE_FORMAT(bal.balanceDate, '%Y%m'),
				dest.balanceVirtualDate		= bal.virtualDate,
				dest.balanceVirtualDateY	= YEAR(bal.virtualDate),
				dest.balanceVirtualDateM	= MONTH(bal.virtualDate),
				dest.balanceVirtualDateYM	= DATE_FORMAT(bal.virtualDate, '%Y%m')
			WHERE dest.idBalance = bal.id
		"; $copyInvoiceDataEx = $db->Execute($copyInvoiceDataSql); if (!$copyInvoiceDataEx) { throw new \DBException('Could not populate pivot table info (step3 - balances).'); } } } 