<?php

// TODO: abstractul sa se ocupe de: sa se ocupe de managementul RD (status, etc)

namespace App\QuickReports\PivotTables\Source;

class OrderServices extends GeneralAbstract
{

	/**
	 * Invoices start date for the source.
	 * @var String - ISO date
	 */
	public $orderDateStart = null;

	/**
	 * Invoices end date for the source.
	 * @var String - ISO date
	 */
	public $orderDateEnd = null;

	/**
	 * {@inheritDoc}
	 * @see \App\QuickReports\PivotTables\Source\GeneralAbstract::getDbTable()
	 */
	protected function getDbTable()
	{
		global $DBTPivotTablesData_orderServices;
		return $DBTPivotTablesData_orderServices;
	}

	/**
	 * {@inheritDoc}
	 * @see \App\QuickReports\PivotTables\Source\GeneralAbstract::initializeFilters()
	 */
	public function initializeFilters()
	{
		$this->orderDateStart = (TODAY_YEAR-5).'-01-01';
		$this->orderDateEnd = TODAY_ISO;
	}

	/**
	 * {@inheritDoc}
	 * @see \App\QuickReports\PivotTables\Source\GeneralAbstract::populateData()
	 */
	protected function checkFilters()
	{
		// check invoice period
		if (!validDate($this->orderDateStart) or !validDate($this->orderDateEnd)) {
			throw new Exception( sprintf('Invalid order date interval [%s - %s].', $this->orderDateStart, $this->orderDateEnd) );
		}
	}


	/**
	 * {@inheritDoc}
	 * @see \App\QuickReports\PivotTables\Source\GeneralAbstract::populateData()
	 */
	protected function populateData()
	{

		$pivotCfg = $this->getPivotConfig();

		\DB::startTransaction();
		$pivotCfg->progress_step = 1;
		\DB::commitTransaction();

		// fill in the services
		$this->populateData_services();

		\DB::startTransaction();
		$pivotCfg->progress_step = 2;
		\DB::commitTransaction();

		// fill in the invoice details
		$this->populateData_invoices();

		\DB::startTransaction();
		$pivotCfg->progress_step = 3;
		\DB::commitTransaction();

 		// fill in the balance details
 		$this->populateData_balances();

 		\DB::startTransaction();
		$pivotCfg->progress_loopRecords = null;
		$pivotCfg->progress_memoryUsage = memory_get_peak_usage(true);
		\DB::commitTransaction();

	}

	/**
	 * Copies the services from working db to pivot source.
	 * @throws \DBException
	 */
	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).');
		}

	}


	/**
	 * Copies the invoice details to all services.
	 * @throws \DBException
	 */
	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).');
		}

	}


	/**
	 * Copies the balance details to all services.
	 * @throws \DBException
	 */
	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).');
		}

	}


}

