<?php

namespace App\QuickReports\PivotTables\Source;

class ClientInvoiceRows extends GeneralAbstract
{

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

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

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

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


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

	/**
	 * {@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 invoice rows
		$this->populateData_invoiceRows();

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

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

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

		// add client details
		$this->populateData_client();

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


	/**
	 * Add main data - info from invoice and invoice rows.
	 * @throws \DBException
	 */
	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).');
		}


		// UPDATE THE ROW VALUES //////////////////////////////////////////////////////////////////
		$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).');
		}
	}


	/**
	 * Add order/service/offer related info based on service id.
	 */
	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();

		// UPDATE CUSTOMERS ///////////////////////////////////////////////////////////////////////
		$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).');
		}

		// UPDATE CORPORATES //////////////////////////////////////////////////////////////////////
		$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).');
		}

		// UPDATE CORPORATE OFFICES ///////////////////////////////////////////////////////////////
		$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).');
		}
	}
}

