<?php

namespace App\Reports\Sources\BalancedRows;


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

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

		// PREPARE THE TEMPORARY TABLE TO MAKE THE INITIAL BALANCES FILTER ////////////////////////

		// create temporary table with the balance ids
		$balancesTableName = 'balanceIds_' . $this->idReportGeneration;

		$balancesTableManager = new \App\Utils\Db\ManageTable($balancesTableName);
		$balancesTableManager->setTemporary(true);
		$balancesTableManager->addColumnByDef('id', 'BIGINT');
		$balancesTableManager->addPkColumns(['id']);
		$balancesTableManager->createTable();


		// MAIN EXEC - STEP 1 - populate the main balances ids in the temp table //////////////////
		$this->populate_initialFilter($balancesTableName);

		// MAIN EXEC - STEP 2 - populate the main balances details ////////////////////////////////
		$this->populate_mainImport($balancesTableName);

		// drop the temp table
		$balancesTableManager->dropTable();

	}

	private function populate_initialFilter(string $balancesTableName) :void
	{

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

		$db = \DB::getInstance();

		global $DBTRd_services, $DBTRd_balances, $DBTRd_orders, $DBTRd_offers;
		global $DBTRd_users, $DBTRd_corporates, $DBTRd_suppliers;
		global $DBTRd_clientInvoiceRows, $DBTRd_clientInvoices, $DBTRd_supplierInvoiceRows, $DBTRd_supplierInvoices;

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

		// compose the client type assoc filter
		$clientTypeAssocFilter = [];
		if ($stdFilters['clientInvoiceClientType']) {
			foreach($stdFilters['clientInvoiceClientType'] as $clientType) {
				$clientTypeAssocFilter[$clientType] = true;
			}
		}


		///////////////////////////////////////////////////////////////////////////////////////////
		// CREATE THE CLIENT INVOICE ROWS SQL TEMPLATE ////////////////////////////////////////////
		///////////////////////////////////////////////////////////////////////////////////////////
		$this->reportGenerationStatusUpdate('initialFilter', 'createClientFilterSQL', 0);

		$balancesSQL = "INSERT %s INTO `{$balancesTableName}`
							SELECT DISTINCT `%s`
								FROM `{$DBTRd_services}` AS `serv`
									INNER JOIN `{$DBTRd_balances}` AS `bal` ON serv.%s=bal.id
									INNER JOIN `{$DBTRd_clientInvoiceRows}` AS `row` ON row.idService=serv.id
									INNER JOIN `{$DBTRd_clientInvoices}` AS `inv` ON row.idInvoice=inv.id";

		if ($stdFilters['orderDate']['start'] or $stdFilters['orderDate']['end'] or
			$stdFilters['idOrderRespUsers'] or $stdFilters['idOrderRespOffices'] or $stdFilters['idOrderRespDepartments']) {
			$balancesSQL .= " INNER JOIN `{$DBTRd_orders}` AS `ord` ON serv.idOrder=ord.id";
		}

		if ($stdFilters['offerStartDate']['start'] or $stdFilters['offerStartDate']['end'] or
		    $stdFilters['offerEndDate']['start'] or $stdFilters['offerEndDate']['end'] or
		    $stdFilters['offerPeriod']['start'] or $stdFilters['offerPeriod']['end']) {
		    $balancesSQL .= " LEFT JOIN `{$DBTRd_offers}` AS `off` ON serv.idOffer=off.id";
		}

		if ($stdFilters['idBalanceRespDepartments']) {
			$balancesSQL .= " INNER JOIN `{$DBTRd_users}` AS `balRespUser` ON bal.idUser=balRespUser.id";
		}

		if ( $stdFilters['idInvoiceCorporateTrusts'] or
			(count($clientTypeAssocFilter) == 1 and !$clientTypeAssocFilter['DC']) or
			(count($clientTypeAssocFilter) == 2 and $clientTypeAssocFilter['DC'])
		) {
			$balancesSQL .= " LEFT JOIN `{$DBTRd_corporates}` AS `invCorp` ON inv.idCorporate=invCorp.id";
		}

		$balancesSQL .= " WHERE bal.deleted = 0 AND bal.orderServicesListAdv != ''";

		$paramBind = [];
		// ADD BALANCE FILTERS ////////////////////////////////////////////////////////////////////
		if ($stdFilters['balanceDate']['start']) {
			$balancesSQL .= " AND bal.balanceDate >= ?";
			$paramBind[] = $stdFilters['balanceDate']['start'];
		}

		if ($stdFilters['balanceDate']['end']) {
			$balancesSQL .= " AND bal.balanceDate <= ?";
			$paramBind[] = $stdFilters['balanceDate']['end'];
		}

		if ($stdFilters['idBalanceRespUsers']) {
			$balancesSQL .= " AND bal.idUser IN " . $this->getSqlInPlaceholdersByBindVars($stdFilters['idBalanceRespUsers']);
			$paramBind = array_merge($paramBind, $stdFilters['idBalanceRespUsers']);
		}
		if ($stdFilters['idBalanceRespOffices']) {
			$balancesSQL .= " AND bal.idOffice IN " . $this->getSqlInPlaceholdersByBindVars($stdFilters['idBalanceRespOffices']);
			$paramBind = array_merge($paramBind, $stdFilters['idBalanceRespOffices']);
		}

		if ($stdFilters['idBalanceRespDepartments']) {
			$balancesSQL .= " AND balRespUser.idDepartment IN " . $this->getSqlInPlaceholdersByBindVars($stdFilters['idBalanceRespDepartments']);
			$paramBind = array_merge($paramBind, $stdFilters['idBalanceRespDepartments']);
		}

		// ADD ORDER FILTERS //////////////////////////////////////////////////////////////////////
		if ($stdFilters['orderDate']['start']) {
			$balancesSQL .= " AND ord.date >= ?";
			$paramBind[] = $stdFilters['orderDate']['start'];
		}

		if ($stdFilters['orderDate']['end']) {
			$balancesSQL .= " AND ord.date <= ?";
			$paramBind[] = $stdFilters['orderDate']['end'];
		}

		if ($stdFilters['idOrderRespUsers']) {
			$balancesSQL .= " AND ord.idUser IN " . $this->getSqlInPlaceholdersByBindVars($stdFilters['idOrderRespUsers']);
			$paramBind = array_merge($paramBind, $stdFilters['idOrderRespUsers']);
		}
		if ($stdFilters['idOrderRespOffices']) {
			$balancesSQL .= " AND ord.idOffice IN " .  $this->getSqlInPlaceholdersByBindVars($stdFilters['idOrderRespOffices']);
			$paramBind = array_merge($paramBind, $stdFilters['idOrderRespOffices']);
		}

		if ($stdFilters['idOrderRespDepartments']) {
			$balancesSQL .= " AND ord.idDepartment IN " .  $this->getSqlInPlaceholdersByBindVars($stdFilters['idOrderRespDepartments']);
			$paramBind = array_merge($paramBind, $stdFilters['idOrderRespDepartments']);
		}

		// ADD OFFER FILTERS //////////////////////////////////////////////////////////////////////
		if ($stdFilters['offerStartDate']['start']) {
			$balancesSQL .= " AND off.startDatetime >= ?";
			$paramBind[] = $stdFilters['offerStartDate']['start'].' 00:00:00';
		}

		if ($stdFilters['offerStartDate']['end']) {
			$balancesSQL .= " AND off.startDatetime <= ?";
			$paramBind[] = $stdFilters['offerStartDate']['end'].' 23:59:59';
		}

		if ($stdFilters['offerEndDate']['start']) {
			$balancesSQL .= " AND off.endDatetime >= ?";
			$paramBind[] = $stdFilters['offerEndDate']['start'].' 00:00:00';
		}

		if ($stdFilters['offerEndDate']['end']) {
			$balancesSQL .= " AND off.endDatetime <= ?";
			$paramBind[] = $stdFilters['offerEndDate']['end'].' 23:59:59';
		}

		if ($stdFilters['offerPeriod']['start'] or $stdFilters['offerPeriod']['end']) {

			if ($stdFilters['offerPeriod']['start'] and $stdFilters['offerPeriod']['end']) {
				$balancesSQL .= " AND off.startDatetime >= ? AND (off.endDatetime <= ? OR off.startDatetime <= ?)";
				$paramBind[] = $stdFilters['offerPeriod']['start'].' 00:00:00';
				$paramBind[] = $stdFilters['offerPeriod']['end'].' 23:59:59';
				$paramBind[] = $stdFilters['offerPeriod']['end'].' 23:59:59';
			}
			else {
				if ($stdFilters['offerPeriod']['start']) {
					$balancesSQL .= " AND off.startDatetime >= ?";
					$paramBind[] = $stdFilters['offerPeriod']['start'].'';
				}
				if ($stdFilters['offerPeriod']['end']) {
					$balancesSQL .= " AND (off.startDatetime <= ? OR off.endDatetime <= ?)";
					$paramBind[] = $stdFilters['offerPeriod']['end'].' 23:59:59';
					$paramBind[] = $stdFilters['offerPeriod']['end'].' 23:59:59';
				}
			}
		}

		// ADD CLIENT FILTERS /////////////////////////////////////////////////////////////////////
		if ($stdFilters['clientInvoiceDate']['start']) {
			$balancesSQL .= " AND inv.invoiceDate >= ?";
			$paramBind[] = $stdFilters['clientInvoiceDate']['start'];
		}

		if ($stdFilters['clientInvoiceDate']['end']) {
			$balancesSQL .= " AND inv.invoiceDate <= ?";
			$paramBind[] = $stdFilters['clientInvoiceDate']['end'];
		}

		if ($stdFilters['idInvoiceCustomers']) {
			$idCustomersListFull = $this->filtersManager->getDuplicateEntitiesGroup('idInvoiceCustomers');
			$balancesSQL .= " AND inv.idCustomer IN " . $this->getSqlInPlaceholdersByBindVars($idCustomersListFull);
			$paramBind = array_merge($paramBind, $idCustomersListFull);
		}

		if ($stdFilters['idInvoiceCorporates']) {
			$idCorporatesListFull = $this->filtersManager->getDuplicateEntitiesGroup('idInvoiceCorporates');
			$balancesSQL .= " AND inv.idCorporate IN " . $this->getSqlInPlaceholdersByBindVars($idCorporatesListFull);
			$paramBind = array_merge($paramBind, $idCorporatesListFull);
		}

		if ($stdFilters['clientServiceType']) {
			$balancesSQL .= " AND row.serviceCode IN " . $this->getSqlInPlaceholdersByBindVars($stdFilters['clientServiceType']);
			$paramBind = array_merge($paramBind, $stdFilters['clientServiceType']);
		}

		if ($clientTypeAssocFilter) {
			if (count($clientTypeAssocFilter) == 1) {
				// direct client
				if ($clientTypeAssocFilter['DC']) {
					$balancesSQL .= " AND inv.idCorporate = 0";
				}
				else {
					$balancesSQL .= " AND inv.idCustomer = 0";
				}

				if ($clientTypeAssocFilter['CC']) {
					$balancesSQL .= " AND invCorp.type = 'CC'";
				}

				if ($clientTypeAssocFilter['SA']) {
					$balancesSQL .= " AND invCorp.type = 'SA'";
				}
			}

			if (count($clientTypeAssocFilter)==2) {
				if (!$clientTypeAssocFilter['CC']) {
					$balancesSQL .= " AND invCorp.type != 'CC'";
				}

				if (!$clientTypeAssocFilter['SA']) {
					$balancesSQL .= " AND invCorp.type != 'SA'";
				}

				if (!$clientTypeAssocFilter['DC']) {
				    $balancesSQL .= " AND inv.idCustomer = 0";
				}
			}
		}

		if ($stdFilters['idInvoiceCorporateTrusts']) {
			$balancesSQL .= " AND invCorp.idTrust IN " . $this->getSqlInPlaceholdersByBindVars($stdFilters['idInvoiceCorporateTrusts']);
			$paramBind = array_merge($paramBind, $stdFilters['idInvoiceCorporateTrusts']);
		}

		///////////////////////////////////////////////////////////////////////////////////////////
		// EXECUTE THE ISSUED CLIENT INVOICE ROWS SQL /////////////////////////////////////////////
		///////////////////////////////////////////////////////////////////////////////////////////
		/**
		 * Test case:
		 *  - Add a normal balance (type: client invoice or client invoice-supplier invoice)
		 *  - Use filters only client related filters for the "client invoice only balance" or client and
		 *  supplier related filters for the "client-supplier filters"
		 *  - The balance for which at least one client invoice row fits the filters will be displayed
		 */
		$this->reportGenerationStatusUpdate('initialFilter', 'filterCLientInvIssue', 0);

		$db->Execute(sprintf($balancesSQL, '', 'idBalance', 'idBalance'), $paramBind);
		if ($db->ErrorNo()) {
			throw new \DBException('The balance issue ids could not be inserted [client invoices step1]: '.$db->ErrorMsg());
		}

		///////////////////////////////////////////////////////////////////////////////////////////
		// EXECUTE THE REFUNDED CLIENT INVOICE ROWS SQL ///////////////////////////////////////////
		///////////////////////////////////////////////////////////////////////////////////////////
		/**
		 * Test case:
		 *  - Add a refund balance (type: client invoice or client invocie-supplier invoice)
		 *  - Use filters only client related filters for the "client invoice only balance" or client and
		 *  supplier related filters for the "client-supplier filters"
		 *  - The balance for which at least one client invoice row fits the filters will be displayed
		 */
		$this->reportGenerationStatusUpdate('initialFilter', 'filterClientInvRefund', 0);
		$db->Execute(sprintf($balancesSQL, 'IGNORE', 'idBalanceRefund', 'idBalanceRefund'), $paramBind);
		if ($db->ErrorNo()) {
			throw new \DBException('The balance refund ids could not be inserted [client invoices step2]: '.$db->ErrorMsg());
		}

		///////////////////////////////////////////////////////////////////////////////////////////
		// ADD SUPPLIER INVOICE ONLY BALANCES (IF NO CLIENT RELATED FILTER SELECTED) //////////////
		///////////////////////////////////////////////////////////////////////////////////////////
		$this->reportGenerationStatusUpdate('initialFilter', 'addSupplierInvBalances', 0);
		/**
		 * Test case:
		 *  - Add a supplier invoice only balance
		 *  - Use only supplier/balance filters (the balance should not be displayed if any client related
		 *  filter is set)
		 */
		if (!$stdFilters['orderDate']['start'] and !$stdFilters['orderDate']['end'] and !$stdFilters['idOrderRespUsers']
			and !$stdFilters['idOrderRespOffices'] and !$stdFilters['idOrderRespDepartments']
			and !$stdFilters['offerStartDate']['start'] and !$stdFilters['offerStartDate']['end']
			and !$stdFilters['offerEndDate']['start'] and !$stdFilters['offerEndDate']['end']
			and !$stdFilters['offerPeriod']['start'] and !$stdFilters['offerPeriod']['end'] and !$clientTypeAssocFilter
			and !$stdFilters['idInvoiceCorporateTrusts'] and !$stdFilters['idInvoiceCustomers'] and !$stdFilters['idInvoiceCorporates']
			and !$stdFilters['clientInvoiceDate']['start'] and !$stdFilters['clientInvoiceDate']['end'] and !$stdFilters['clientServiceType']) {

			$balancesSQL = "INSERT IGNORE INTO `{$balancesTableName}`
								SELECT DISTINCT `idBalance`
									FROM `{$DBTRd_balances}` AS `bal`
										JOIN `{$DBTRd_supplierInvoiceRows}` AS `row` ON bal.id=row.idBalance
										JOIN `{$DBTRd_supplierInvoices}`  AS `inv` ON row.idInvoice=inv.id";

			if ($stdFilters['idBalanceRespDepartments']) {
				$balancesSQL .= " INNER JOIN `{$DBTRd_users}` AS `balRespUser` ON bal.idUser=balRespUser.id";
			}

			$balancesSQL .= " WHERE bal.deleted = 0 AND bal.orderServicesListAdv = ''";

			$paramBind = [];

			// ADD BALANCE FILTERS ////////////////////////////////////////////////////////////////
			if ($stdFilters['balanceDate']['start']) {
				$balancesSQL .= " AND bal.balanceDate >= ?";
				$paramBind[] = $stdFilters['balanceDate']['start'];
			}

			if ($stdFilters['balanceDate']['end']) {
				$balancesSQL .= " AND bal.balanceDate <= ?";
				$paramBind[] = $stdFilters['balanceDate']['end'];
			}

			if ($stdFilters['idBalanceRespUsers']) {
				$balancesSQL .= " AND bal.idUser IN ". $this->getSqlInPlaceholdersByBindVars($stdFilters['idBalanceRespUsers']);
				$paramBind = array_merge($paramBind, $stdFilters['idBalanceRespUsers']);
			}
			elseif ($stdFilters['idBalanceRespOffices']) {
				$balancesSQL .= " AND bal.idOffice IN " . $this->getSqlInPlaceholdersByBindVars($stdFilters['idBalanceRespOffices']);
				$paramBind = array_merge($paramBind, $stdFilters['idBalanceRespOffices']);
			}

			if ($stdFilters['idBalanceRespDepartments']) {
				$balancesSQL .= " AND balRespUser.idDepartment IN " . $this->getSqlInPlaceholdersByBindVars($stdFilters['idBalanceRespDepartments']);
				$paramBind = array_merge($paramBind, $stdFilters['idBalanceRespDepartments']);
			}

			$db->Execute($balancesSQL, $paramBind);
			if ($db->ErrorNo()) {
				throw new \DBException('The balance ids could not be inserted [supplier invoices step1]: '.$db->ErrorMsg());
			}
		}

		///////////////////////////////////////////////////////////////////////////////////////////
		// DELETE THE CLIENT INVOICE ONLY BALANCES IDS (AT LEAST ONE SUPPLIER RELATED FILTER SET) /
		///////////////////////////////////////////////////////////////////////////////////////////
		$this->reportGenerationStatusUpdate('initialFilter', 'filterClientInvOnlyBalances', 0);
		/**
		 * Test case:
		 *  - Add a client invoice only balance
		 *  - Use only supplier related filters and balance filters (optional)
		 */
		if ($stdFilters['supplierInvoiceDate']['start'] or $stdFilters['supplierInvoiceDate']['end'] or $stdFilters['idInvoiceSuppliers'] or $stdFilters['idInvoiceSupplierTrusts']) {
			$balanceSuppInvSQL = "DELETE `filteredBal`
								FROM `{$balancesTableName}` AS `filteredBal`
									JOIN `{$DBTRd_balances}` AS `bal` ON filteredBal.id=bal.id";

			$balanceSuppInvSQL .= " WHERE bal.supplierInvoiceRowsList = ''";

			$db->Execute($balanceSuppInvSQL);
			if ($db->ErrorNo()) {
				throw new \DBException('The balances could not be filtered [supplier invoices step2]: '.$db->ErrorMsg());
			}
		}

		///////////////////////////////////////////////////////////////////////////////////////////
		// DELETE THE SUPPLIER INVOICE ROWS THAT DO NOT MATCH THE FILTERS /////////////////////////
		///////////////////////////////////////////////////////////////////////////////////////////
		$this->reportGenerationStatusUpdate('initialFilter', 'filterSupplierInv', 0);
		/**
		 * Test case:
		 *  - Add a supplier invoice only balance or a client-supplier balance (or multiple client-supplier
		 *  balance)
		 *  - Use supplier related filters - the balance for which at least one supplier row fits the conditions
		 *  will be displayed
		 *
		 *  Example for multiple client-supplier balance:
		 * 		balance content: client invoice with client A, client invoice with client B, supplier invoice with supplier X, and supplier invoice with supplier Y
		 * 		filter: supplier = Y
		 * 		expected result: the balance should be dispayed as it contains at least one supplier invoice that contains supplier Y
		 */
		// do not delete records if no supplier invoice related filter is set
		if ($stdFilters['supplierInvoiceDate']['start'] or $stdFilters['supplierInvoiceDate']['end'] or $stdFilters['idInvoiceSuppliers'] or $stdFilters['idInvoiceSupplierTrusts']) {

			// delete from balanceIds temporary table the filtered supplier invoices
			/** NOTE (06.08.2020) we didn't optimize the sql by joining with the temporary table in select because of mysql temp tables limitation
			 * @see https://dev.mysql.com/doc/refman/8.0/en/temporary-table-problems.html
			 * @see https://bugs.mysql.com/bug.php?id=10327
			 * @see https://dba.stackexchange.com/questions/45270/mysql-error-1137-hy000-at-line-9-cant-reopen-table-temp-table
			 */
			$balanceSuppInvSQL = "DELETE
									FROM `{$balancesTableName}`
										WHERE `id` NOT IN (
											SELECT DISTINCT row.idBalance FROM `{$DBTRd_supplierInvoiceRows}` AS `row`
											JOIN `{$DBTRd_supplierInvoices}`  AS `inv` ON row.idInvoice=inv.id";

			if ($stdFilters['idInvoiceSupplierTrusts']) {
				$balanceSuppInvSQL .= " JOIN `{$DBTRd_suppliers}` AS `invSupp` ON inv.idSupplier=invSupp.id";
			}

			$balanceSuppInvSQL .= " WHERE 1";

			$paramBind = [];
			// ADD SUPPLIER FILTERS
			if ($stdFilters['supplierInvoiceDate']['start']) {
				$balanceSuppInvSQL .= " AND inv.invoiceDate >= ?";
				$paramBind[] = $stdFilters['supplierInvoiceDate']['start'];
			}

			if ($stdFilters['supplierInvoiceDate']['end']) {
				$balanceSuppInvSQL .= " AND inv.invoiceDate <= ?";
				$paramBind[] = $stdFilters['supplierInvoiceDate']['end'];
			}

			if ($stdFilters['idInvoiceSuppliers']) {
				$idSuppliersListFull = $this->filtersManager->getDuplicateEntitiesGroup('idInvoiceSuppliers');
				$balanceSuppInvSQL .= " AND inv.idSupplier IN " . $this->getSqlInPlaceholdersByBindVars($idSuppliersListFull);
				$paramBind = array_merge($paramBind, $idSuppliersListFull);
			}

			if ($stdFilters['idInvoiceSupplierTrusts']) {
				$balanceSuppInvSQL .= " AND invSupp.idTrust IN " . $this->getSqlInPlaceholdersByBindVars($stdFilters['idInvoiceSupplierTrusts']);
				$paramBind = array_merge($paramBind, $stdFilters['idInvoiceSupplierTrusts']);
			}

			$balanceSuppInvSQL .= ')';

			$db->Execute($balanceSuppInvSQL, $paramBind);
			if ($db->ErrorNo()) {
				throw new \DBException('The balances could not be filtered [supplier invoices step3]: '.$db->ErrorMsg());
			}
		}

	}



	private function populate_mainImport(string $balancesTableName) :void
	{

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

		global $DBTRd_offers,  $DBTRd_users, $DBTRd_customers, $DBTRd_corporates, $DBTRd_suppliers;
		global $DBTRd_orders, $DBTRd_services, $DBTRd_clientInvoices, $DBTRd_clientInvoiceRows, $DBTRd_balances;
		global $DBTRd_supplierInvoices, $DBTRd_supplierInvoiceRows;

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

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


		// CREATE SQL FOR COPYING CLIENT INVOICE ROWS /////////////////////////////////////////////
		$clientInvGeneralCols = [
			'bal_id'					=> 'bal.id',
			'bal_number'				=> 'bal.balanceNumber',
			'bal_date'					=> 'bal.balanceDate',
			'bal_code'					=> 'bal.balanceCode',
			'bal_name'					=> 'bal.balanceName',
			'bal_typeCode'				=> 'bal.balanceType',
			'bal_statusCode'			=> 'bal.balanceStatus',
			'bal_virtualDate'			=> 'bal.virtualDate',
			'bal_generationType'		=> 'bal.generation',
			'bal_comments'				=> 'bal.comments',
			'bal_remarks'				=> 'bal.remarks',
			'bal_processedRemarks'		=> 'bal.processedRemarks',
			'bal_totalIncomeDefCurr'	=> 'bal.totalIncomeDefCurr',
			'bal_totalExpensesDefCurr'	=> 'bal.totalExpensesDefCurr',
			'bal_totalProfitDefCurr'	=> 'bal.totalProfitDefCurr',
			'bal_profitPercent'			=> 'bal.profitPercent',

			'bal_userId'				=> 'bal.idUser',
			'bal_officeId'				=> 'bal.idOffice',
			'bal_deptId'				=> 'balRespUser.idDepartment',
			'bal_confirmUserId'			=> 'bal.idConfirmUser',
			'bal_addUserId' 			=> 'bal.idAddUser',

			'bal_addTime' 				=> 'bal.addTime',
			'bal_confirmTime'			=> 'bal.confirmTime',

			'inv_id'					=> 'inv.id',
			'inv_number'				=> 'inv.invoiceNumber',
			'inv_date'					=> 'inv.invoiceDate',
			'inv_seriesId'				=> 'inv.idInvoiceSeries',
			'inv_statusCode'			=> 'inv.cashedStatus',
			'inv_dueDate'				=> 'inv.dueDate',
			'inv_fulfillmentDate'		=> 'inv.fulfillmentDate',
			'inv_comments'				=> 'inv.comments',
			'inv_remarks'				=> 'inv.remarks',
			'inv_processedRemarks'		=> 'inv.processedRemarks',
			'inv_formOfPaymentCode'		=> 'inv.formOfPayment',
			'inv_cumulative'			=> 'inv.cumulativeInvoice',
			'inv_addTime'				=> 'inv.addTime',

			'inv_userId'				=> 'inv.idUser',
			'inv_officeId'				=> 'inv.idOffice',
			'inv_departmentId'			=> 'invRespUser.idDepartment',
			'inv_addUserId'				=> 'inv.idAddUser',

			'serv_id'						=> 'serv.id',
			'serv_passengerId'				=> 'serv.idFirstCustomer',
			'serv_ticketNumber'				=> 'serv.firstTicketNumber',
		    'serv_ticketDate'				=> 'serv.passengersTicketsDate',
			'serv_respUserId'				=> 'serv.idUser',
			'serv_respOfficeId'				=> 'serv.idOffice',
			'serv_respDeptId'				=> 'servRespUser.idDepartment',

			'off_id'						=> 'off.id',
		    'off_startDatetime'				=> 'off.startDatetime',
		    'off_endDatetime'				=> 'off.endDatetime',
			'off_externalReservationId'		=> 'off.externalSystemReservationId',
			'off_externalReservationDate'	=> 'off.externalSystemReservationDate',
			'off_supplierId'				=> 'off.idSupplier',
		    'off_supplierAlias' 			=> 'offSupp.alias',
			'off_destinationCountryCode'	=> 'off.destinationCountryCode',
			'off_destinationCityId'			=> 'off.destinationCityId',

			'order_id'				=> 'ord.id',
			'order_idConverted'		=> 'ord.convertedId',
			'order_code'			=> 'ord.orderCode',
			'order_number'			=> 'ord.orderNumber',
			'order_date'			=> 'ord.date',
			'order_startDate'		=> 'ord.startDate',
			'order_endDate'			=> 'ord.endDate',
			'order_userId'			=> 'ord.idUser',
			'order_officeId'		=> 'ord.idOffice',
			'order_deptId'			=> 'ord.idDepartment',

			'row_id'					=> 'invRow.id',
			'row_serviceCode'			=> 'invRow.serviceCode',
			'row_locationCode'			=> 'invRow.location',
			'row_calculationCode'		=> 'invRow.calculationType',
			'row_rowTypeCode'			=> 'invRow.rowType',
			'row_description'			=> 'invRow.description',
		    'row_refund'				=> 'invRow.refund',
		    'row_cumulatesIdInvoiceRow'	=> 'invRow.cumulatesIdInvoiceRow',
			'row_categoryCode'			=> "'c'",
			'inv_currency'				=> 'inv.currency',
			'inv_defCurrencyExchRate'	=> 'inv.currencyRate',
			'inv_workCurrencyExchRate'	=> 'inv.workCurrencyRate',
			'row_measureUnit'			=> 'invRow.measureUnit',
			'row_quantity'				=> 'invRow.quantity',
			'row_unitValue'				=> 'invRow.rowUnitValue',
			'row_value'					=> 'invRow.rowValue',
			'row_vat'					=> 'invRow.rowVat',
			'row_vatPercent'			=> 'invRow.vatPercent',
			'row_vatText'				=> 'invRow.vatText',
		    'row_estOffProfit'			=> 'invRow.rowOfferEstProfit',

		];


		// CREATE SQL FOR COPYING CLIENT INVOICE ROWS ISSUES //////////////////////////////////////

		// refresh report status
		$this->reportGenerationStatusUpdate('extractMainDataFromDb', 'runningClientRowsIssueSQL', 0);
		$repClientRowsIssueData = new \App\Reports\Db\ManageRecords\Insert($reportTable);
		$repClientRowsIssueData->setMainTable($balancesTableName,		'filteredBal');
		$repClientRowsIssueData->addJoinTable($DBTRd_services,			'serv', 		'filteredBal.id=serv.idBalance');
		$repClientRowsIssueData->addJoinTable($DBTRd_users,				'servRespUser',	'serv.idUser=servRespUser.id');

		$repClientRowsIssueData->addJoinTable($DBTRd_orders,			'ord', 			'serv.idOrder=ord.id');
		$repClientRowsIssueData->addJoinTable($DBTRd_offers,			'off', 			'serv.idOffer=off.id',				'LEFT');
		$repClientRowsIssueData->addJoinTable($DBTRd_suppliers,         'offSupp',		'off.idSupplier=offSupp.id',		'LEFT');
		$repClientRowsIssueData->addJoinTable($DBTRd_clientInvoiceRows,	'invRow', 		'invRow.idService=serv.id');
		$repClientRowsIssueData->addJoinTable($DBTRd_clientInvoices,	'inv', 			'invRow.idInvoice=inv.id');
		$repClientRowsIssueData->addJoinTable($DBTRd_users,				'invRespUser',	'inv.idUser=invRespUser.id');
		$repClientRowsIssueData->addJoinTable($DBTRd_corporates,		'invCorp',		'inv.idCorporate=invCorp.id',		'LEFT');
		$repClientRowsIssueData->addJoinTable($DBTRd_customers, 		'invCust',		'inv.idCustomer=invCust.id',		'LEFT');

		$repClientRowsIssueData->addJoinTable($DBTRd_balances,			'bal', 			'filteredBal.id=bal.id');
		$repClientRowsIssueData->addJoinTable($DBTRd_users,				'balRespUser',	'bal.idUser=balRespUser.id');

		$repClientRowsIssueData->setStructureColumns($clientInvGeneralCols);
		$repClientRowsIssueData->addStructureColumn("row_internalId", 		"CONCAT('c', invRow.id)", 'invRow');

		$repClientRowsIssueData->addStructureColumn("bal_dateYM",			"DATE_FORMAT(bal.balanceDate, '%Y-%m-01')", "bal");
		$repClientRowsIssueData->addStructureColumn("bal_dateY",			"YEAR(bal.balanceDate)", "bal");
		$repClientRowsIssueData->addStructureColumn("bal_virtualDateYM",	"DATE_FORMAT(bal.virtualDate, '%Y-%m-01')", "bal");
		$repClientRowsIssueData->addStructureColumn("bal_virtualDateY",		"YEAR(bal.virtualDate)", "bal");
		$repClientRowsIssueData->addStructureColumn("bal_userName",			"CONCAT(balRespUser.lastName, ' ', balRespUser.firstName)", 'balRespUser');
		$repClientRowsIssueData->addStructureColumn("bal_confirmed",		"IF(bal.confirmTime IS NOT NULL, 1,0)",        "bal");

		$repClientRowsIssueData->addStructureColumn("inv_corporateId",		"IF(invCorp.idRealCorporate, invCorp.idRealCorporate, inv.idCorporate)", "invCorp");
		$repClientRowsIssueData->addStructureColumn("inv_customerId",		"IF(invCust.idRealCustomer, invCust.idRealCustomer, inv.idCustomer)", "invCust");

		$repClientRowsIssueData->addStructureColumn("row_calculationKey",	"CONCAT(invRow.serviceCode, '_', invRow.location, '_', invRow.calculationType)", "invRow");
		$repClientRowsIssueData->addStructureColumn("row_valueDefCurr",		"invRow.rowValue*inv.currencyRate", "inv");
		$repClientRowsIssueData->addStructureColumn("row_valueWorkCurr",	"invRow.rowValue*inv.currencyRate/inv.workCurrencyRate", "inv");
		$repClientRowsIssueData->addStructureColumn("row_vatDefCurr",		"invRow.rowVat*inv.currencyRate", "inv");
		$repClientRowsIssueData->addStructureColumn("row_vatWorkCurr",		"invRow.rowVat*inv.currencyRate/inv.workCurrencyRate", "inv");
		$repClientRowsIssueData->addStructureColumn("row_estOffProfitDefCurr", "invRow.rowOfferEstProfit*inv.currencyRate", "inv");
		$repClientRowsIssueData->addStructureColumn("row_estOffProfitWorkCurr", "invRow.rowOfferEstProfit*inv.currencyRate/inv.workCurrencyRate", "inv");

		$repClientRowsIssueData->addStructureColumn("row_total",			"invRow.rowValue+invRow.rowVat", "invRow");
		$repClientRowsIssueData->addStructureColumn("row_totalDefCurr",		"(invRow.rowValue+invRow.rowVat)*inv.currencyRate", "inv");
		$repClientRowsIssueData->addStructureColumn("row_totalWorkCurr",	"(invRow.rowValue+invRow.rowVat)*inv.currencyRate/inv.workCurrencyRate", "inv");
		$repClientRowsIssueData->addStructureColumn("row_vatPercent",		"invRow.vatPercent/100", "inv");

		$repClientRowsIssueData->addStructureColumn("row_profit",			"(invRow.rowValue+invRow.rowVat)*bal.profitPercent", "invRow");
		$repClientRowsIssueData->addStructureColumn("row_profitDefCurr",	"(invRow.rowValue+invRow.rowVat)*bal.profitPercent*inv.currencyRate", "invRow");
		$repClientRowsIssueData->addStructureColumn("row_profitWorkCurr",	"(invRow.rowValue+invRow.rowVat)*bal.profitPercent*inv.currencyRate/inv.workCurrencyRate", "invRow");

		$repClientRowsIssueData->addStructureColumn("off_startDate",		"DATE(off.startDatetime)",        "off");
		$repClientRowsIssueData->addStructureColumn("off_endDate",			"IF(off.endDatetime IS NOT NULL, DATE(off.endDatetime), NULL)",        "off");

		$repClientRowsIssueData->setActiveColumns($activeColumns);

		$repClientRowsIssueData->addFilter('invRow.refund' , '=', 0);
		$repClientRowsIssueData->addFilter('inv.voidTime' , 'IS NULL');

		$repClientRowsIssueData->populate();


		// CREATE SQL FOR COPYING CLIENT INVOICE ROWS REFUNDS /////////////////////////////////////

		// refresh report status
		$this->reportGenerationStatusUpdate('extractMainDataFromDb', 'runningClientRowsRefundSQL', 0);
		$repClientRowsRefundData = new \App\Reports\Db\ManageRecords\Insert($reportTable);
		$repClientRowsRefundData->setMainTable($balancesTableName,			'filteredBal');
		$repClientRowsRefundData->addJoinTable($DBTRd_services,				'serv', 		'filteredBal.id=serv.idBalanceRefund');
		$repClientRowsRefundData->addJoinTable($DBTRd_users,				'servRespUser',	'serv.idUser=servRespUser.id');
		$repClientRowsRefundData->addJoinTable($DBTRd_orders,				'ord', 			'serv.idOrder=ord.id');
		$repClientRowsRefundData->addJoinTable($DBTRd_offers,				'off', 			'serv.idOffer=off.id',		        'LEFT');
		$repClientRowsRefundData->addJoinTable($DBTRd_suppliers,        	'offSupp',		'off.idSupplier=offSupp.id',		'LEFT');

		$repClientRowsRefundData->addJoinTable($DBTRd_clientInvoiceRows,	'invRow', 		'invRow.idService=serv.id');
		$repClientRowsRefundData->addJoinTable($DBTRd_clientInvoices,		'inv', 			'invRow.idInvoice=inv.id');
		$repClientRowsRefundData->addJoinTable($DBTRd_users,				'invRespUser',	'inv.idUser=invRespUser.id');
		$repClientRowsRefundData->addJoinTable($DBTRd_corporates,			'invCorp',		'inv.idCorporate=invCorp.id',		'LEFT');
		$repClientRowsRefundData->addJoinTable($DBTRd_customers, 			'invCust',		'inv.idCustomer=invCust.id',		'LEFT');

		$repClientRowsRefundData->addJoinTable($DBTRd_balances,				'bal', 			'filteredBal.id=bal.id');
		$repClientRowsRefundData->addJoinTable($DBTRd_users,				'balRespUser',	'bal.idUser=balRespUser.id');

		$repClientRowsRefundData->setStructureColumns($clientInvGeneralCols);
		$repClientRowsRefundData->addStructureColumn("row_internalId", 		"CONCAT('c', invRow.id)", 'invRow');

		$repClientRowsRefundData->addStructureColumn("bal_dateYM",			"DATE_FORMAT(bal.balanceDate, '%Y-%m-01')",    "bal");
		$repClientRowsRefundData->addStructureColumn("bal_dateY",			"YEAR(bal.balanceDate)",    "bal");
		$repClientRowsRefundData->addStructureColumn("bal_virtualDateYM",	"DATE_FORMAT(bal.virtualDate, '%Y-%m-01')",    "bal");
		$repClientRowsRefundData->addStructureColumn("bal_virtualDateY",	"YEAR(bal.virtualDate)",    "bal");
		$repClientRowsRefundData->addStructureColumn("bal_userName",		"CONCAT(balRespUser.lastName, ' ', balRespUser.firstName)", 'balRespUser');
		$repClientRowsRefundData->addStructureColumn("bal_confirmed",		"IF(bal.confirmTime IS NOT NULL, 1,0)",        "bal");

		$repClientRowsRefundData->addStructureColumn("inv_corporateId",		"IF(invCorp.idRealCorporate, invCorp.idRealCorporate, inv.idCorporate)", "invCorp");
		$repClientRowsRefundData->addStructureColumn("inv_customerId",		"IF(invCust.idRealCustomer, invCust.idRealCustomer, inv.idCustomer)", "invCust");

		$repClientRowsRefundData->addStructureColumn("row_valueDefCurr",	"invRow.rowValue*inv.currencyRate", "inv");
		$repClientRowsRefundData->addStructureColumn("row_valueWorkCurr",	"invRow.rowValue*inv.currencyRate/inv.workCurrencyRate", "inv");
		$repClientRowsRefundData->addStructureColumn("row_vatDefCurr",		"invRow.rowVat*inv.currencyRate", "inv");
		$repClientRowsRefundData->addStructureColumn("row_vatWorkCurr",		"invRow.rowVat*inv.currencyRate/inv.workCurrencyRate", "inv");
		$repClientRowsRefundData->addStructureColumn("row_estOffProfitDefCurr", "invRow.rowOfferEstProfit*inv.currencyRate", "inv");
		$repClientRowsRefundData->addStructureColumn("row_estOffProfitWorkCurr", "invRow.rowOfferEstProfit*inv.currencyRate/inv.workCurrencyRate", "inv");

		$repClientRowsRefundData->addStructureColumn("row_total",			"invRow.rowValue+invRow.rowVat", "invRow");
		$repClientRowsRefundData->addStructureColumn("row_totalDefCurr",	"(invRow.rowValue+invRow.rowVat)*inv.currencyRate", "inv");
		$repClientRowsRefundData->addStructureColumn("row_totalWorkCurr",	"(invRow.rowValue+invRow.rowVat)*inv.currencyRate/inv.workCurrencyRate", "inv");
		$repClientRowsRefundData->addStructureColumn("row_vatPercent",		"invRow.vatPercent/100", "inv");

		$repClientRowsRefundData->addStructureColumn("row_profit",			"(invRow.rowValue+invRow.rowVat)*bal.profitPercent", "invRow");
		$repClientRowsRefundData->addStructureColumn("row_profitDefCurr",	"(invRow.rowValue+invRow.rowVat)*bal.profitPercent*inv.currencyRate", "invRow");
		$repClientRowsRefundData->addStructureColumn("row_profitWorkCurr",	"(invRow.rowValue+invRow.rowVat)*bal.profitPercent*inv.currencyRate/inv.workCurrencyRate", "invRow");

		$repClientRowsRefundData->addStructureColumn("off_startDate",		"DATE(off.startDatetime)",        "off");
		$repClientRowsRefundData->addStructureColumn("off_endDate",			"IF(off.endDatetime IS NOT NULL, DATE(off.endDatetime), NULL)",        "off");

		$repClientRowsRefundData->setActiveColumns($activeColumns);

		$repClientRowsRefundData->addFilter('invRow.refund' , '=', 1);
		$repClientRowsRefundData->addFilter('inv.voidTime' , 'IS NULL');

		$repClientRowsRefundData->populate();


		// CREATE SQL FOR COPYING SUPPLIER INVOICE ROWS ///////////////////////////////////////////

		// refresh report status
		$this->reportGenerationStatusUpdate('extractMainDataFromDb', 'runningSupplierRowsSQL', 0);
		$repSupplierRowsData = new \App\Reports\Db\ManageRecords\Insert($reportTable);
		$repSupplierRowsData->setMainTable($balancesTableName,			'filteredBal');
		$repSupplierRowsData->addJoinTable($DBTRd_supplierInvoiceRows,	'invRow', 		'filteredBal.id=invRow.idBalance');
		$repSupplierRowsData->addJoinTable($DBTRd_supplierInvoices,		'inv', 			'invRow.idInvoice=inv.id');
		$repSupplierRowsData->addJoinTable($DBTRd_users,				'invRespUser',	'inv.idUser=invRespUser.id');
		$repSupplierRowsData->addJoinTable($DBTRd_suppliers, 			'invSupp',		'inv.idSupplier=invSupp.id');

		$repSupplierRowsData->addJoinTable($DBTRd_balances,				'bal', 			'filteredBal.id=bal.id');
		$repSupplierRowsData->addJoinTable($DBTRd_users,				'balRespUser',	'bal.idUser=balRespUser.id');

		$supplierInvGeneralCols = [
			'bal_id'					=> 'bal.id',
			'bal_number'				=> 'bal.balanceNumber',
			'bal_date'					=> 'bal.balanceDate',
			'bal_code'					=> 'bal.balanceCode',
			'bal_name'					=> 'bal.balanceName',
			'bal_typeCode'				=> 'bal.balanceType',
			'bal_statusCode'			=> 'bal.balanceStatus',
			'bal_virtualDate'			=> 'bal.virtualDate',
			'bal_generationType'		=> 'bal.generation',
			'bal_comments'				=> 'bal.comments',
			'bal_remarks'				=> 'bal.remarks',
			'bal_processedRemarks'		=> 'bal.processedRemarks',
			'bal_totalIncomeDefCurr'	=> 'bal.totalIncomeDefCurr',
			'bal_totalExpensesDefCurr'	=> 'bal.totalExpensesDefCurr',
			'bal_totalProfitDefCurr'	=> 'bal.totalProfitDefCurr',
			'bal_profitPercent'			=> 'bal.profitPercent',

			'bal_userId'				=> 'bal.idUser',
			'bal_officeId'				=> 'bal.idOffice',
			'bal_deptId'				=> 'balRespUser.idDepartment',
			'bal_confirmUserId'			=> 'bal.idConfirmUser',
			'bal_addUserId' 			=> 'bal.idAddUser',

			'bal_addTime' 				=> 'bal.addTime',
			'bal_confirmTime'			=> 'bal.confirmTime',

			'inv_id'					=> 'inv.id',
			'inv_number'				=> 'inv.invoiceNumber',
			'inv_date'					=> 'inv.invoiceDate',
			'inv_seriesCode'			=> 'inv.invoiceSeries',
			'inv_statusCode'			=> 'inv.statusFin',
			'inv_dueDate'				=> 'inv.dueDate',
			'inv_fulfillmentDate'		=> 'inv.fulfillmentDate',
			'inv_comments'				=> 'inv.invoiceComments',
			'inv_remarks'				=> 'inv.remarks',
			'inv_processedRemarks'		=> 'inv.processedRemarks',
			'inv_formOfPaymentCode'		=> 'inv.formOfPayment',
			'inv_addTime'				=> 'inv.addTime',

			'inv_userId'				=> 'inv.idUser',
			'inv_officeId'				=> 'inv.idOffice',
			'inv_departmentId'			=> 'invRespUser.idDepartment',
			'inv_addUserId'				=> 'inv.idAddUser',

		    'serv_id'					=> 'invRow.idService',
		    'order_id'				    => 'invRow.idOrder',

			'row_id'					=> 'invRow.id',
			'row_serviceCode'			=> 'invRow.serviceCode',
			'row_rowTypeCode'			=> 'invRow.rowType',
			'row_description'			=> 'invRow.description',
			'row_categoryCode'			=> "'s'",
			'inv_currency'				=> 'inv.currency',
			'inv_defCurrencyExchRate'	=> 'inv.currencyRate',
			'inv_workCurrencyExchRate'	=> 'inv.workCurrencyRate',
			'row_measureUnit'			=> 'invRow.measureUnit',
			'row_quantity'				=> 'invRow.quantity',
			'row_vatText'				=> 'invRow.vatText',
		];

		$repSupplierRowsData->setStructureColumns($supplierInvGeneralCols);
		$repSupplierRowsData->addStructureColumn("row_internalId",		"CONCAT('s', invRow.id)", 'invRow');
		$repSupplierRowsData->addStructureColumn("bal_dateYM",			"DATE_FORMAT(bal.balanceDate, '%Y-%m-01')");
		$repSupplierRowsData->addStructureColumn("bal_dateY",			"YEAR(bal.balanceDate)");
		$repSupplierRowsData->addStructureColumn("bal_virtualDateYM",	"DATE_FORMAT(bal.virtualDate, '%Y-%m-01')");
		$repSupplierRowsData->addStructureColumn("bal_virtualDateY",	"YEAR(bal.virtualDate)");
		$repSupplierRowsData->addStructureColumn("bal_userName",		"CONCAT(balRespUser.lastName, ' ', balRespUser.firstName)", 'balRespUser');
		$repSupplierRowsData->addStructureColumn("bal_confirmed",		"IF(bal.confirmTime IS NOT NULL, 1,0)");

		$repSupplierRowsData->addStructureColumn("inv_supplierId",		"IF(invSupp.idRealSupplier, invSupp.idRealSupplier, inv.idSupplier)", "invSupp");
		$repSupplierRowsData->addStructureColumn("inv_partnerId", 		"IF(invSupp.idRealSupplier, invSupp.idRealSupplier, inv.idSupplier)", "invSupp");
		$repSupplierRowsData->addStructureColumn("inv_partnerTypeCode", "'SU'");

		$repSupplierRowsData->addStructureColumn("row_unitValue",		"-invRow.rowUnitValue");
		$repSupplierRowsData->addStructureColumn("row_value",			"-invRow.rowValue");
		$repSupplierRowsData->addStructureColumn("row_vat",				"-invRow.rowVat");
		$repSupplierRowsData->addStructureColumn("row_valueDefCurr",	"-invRow.rowValue*inv.currencyRate", "inv");
		$repSupplierRowsData->addStructureColumn("row_valueWorkCurr",	"-invRow.rowValue*inv.currencyRate/inv.workCurrencyRate", "inv");
		$repSupplierRowsData->addStructureColumn("row_vatDefCurr",		"-invRow.rowVat*inv.currencyRate", "inv");
		$repSupplierRowsData->addStructureColumn("row_vatWorkCurr",		"-invRow.rowVat*inv.currencyRate/inv.workCurrencyRate", "inv");
		$repSupplierRowsData->addStructureColumn("row_vatPercent",		"invRow.vatPercent/100", "inv");

		$repSupplierRowsData->addStructureColumn("row_total",			"-invRow.rowValue-invRow.rowVat", "invRow");
		$repSupplierRowsData->addStructureColumn("row_totalDefCurr",	"(-invRow.rowValue-invRow.rowVat)*inv.currencyRate", "inv");
		$repSupplierRowsData->addStructureColumn("row_totalWorkCurr",	"(-invRow.rowValue-invRow.rowVat)*inv.currencyRate/inv.workCurrencyRate", "inv");

		$repSupplierRowsData->setActiveColumns($activeColumns);

		$repSupplierRowsData->populate();


	}



	/**
	 * Returns a list of ?, ?, ?... based on the number of elements in bindVars.
	 * @param array $bindVars
	 * @return string
	 */
	private function getSqlInPlaceholdersByBindVars(array $bindVars) :string
	{
		return '(' . implode(", ", array_fill(0, count($bindVars), '?')) . ')';
	}

}