<?php

// TODO: [notImportant] filtru serviciu (sa se faca din interfata, dar sa am default bifate default-urile aplicatiei: k/ k+x, etc) ; momentan se iau aceleasi tipuri de servicii de la sursa veche

namespace App\Reports\Sources\Ticketing;


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

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

		// INITS ----------------------------------------------------------------------------------

		global $DBTRd_orders, $DBTRd_services, $DBTRd_offers, $DBTRd_clientInvoices, $DBTRd_balances;
		global $DBTRd_importedOrderSystems, $DBTRd_importedOrderServices, $DBTRd_importedOrders;
		global $DBTRd_customers, $DBTRd_corporates, $DBTRd_corporateOffices, $DBTRd_suppliers;

		$CFG = \Engine\Loader_Cfg::getInstance()->getGlobal();

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

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

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

		$operationTypes = [];
		foreach ((array)$stdFilters['operationType'] as $opType) {
			$operationTypes[$opType] = true;
		}


		// PREPARE COMMON COLUMNS FOR ISSUES/REFUNDS //////////////////////////////////////////////
		// common basic columns
		$commonColumnsBasic = [

			'impServ_id'					=> 'impServ.id',
			'impServ_idServiceExtSys'		=> 'impServ.idServiceExtSys',
			'impServ_systemId'				=> 'impServ.idSystem',
			'impServ_systemCode'			=> 'extSys.code',
			'impServ_systemName'			=> 'extSys.name',
			'impServ_serviceCode'			=> 'impServ.serviceCode',
			'impServ_systemServiceCode'		=> 'impServ.systemServiceCode',
			'impServ_systemStatusCode'		=> 'impServ.systemStatus',
			'impServ_ticketNumber'			=> 'impServ.firstTicketNumber',
			'impServ_ticketNumberConj'		=> 'impServ.ticketsConj',
			'impServ_ticketNumberList'		=> 'impServ.ticketsList',
			'impServ_ticketDate'			=> 'impServ.ticketsDate',
			'impServ_idImpServReissued'		=> 'impServ.idImportedServiceReissued',
			'impServ_startDate'				=> 'impServ.startDate',
			'impServ_endDate'				=> 'impServ.endDate',

			'impServ_pax'					=> 'impServ.pax',
			'impServ_passengerLastName'		=> 'impServ.passengerLastName',
			'impServ_passengerFirstName'	=> 'impServ.passengerFirstName',
			'impServ_destCountryCode'		=> 'impServ.destinationCountryCode',
			'impServ_destCityId'			=> 'impServ.destinationCityId',
			'impServ_locationsDetails'		=> 'impServ.locationsDetails',
			'impServ_offerProductType'		=> 'impServ.offerProductType',
			'impServ_supplierId'			=> 'impServ.idOfferSupplier',
			'impServ_offerExtSysResId'		=> 'impServ.offerExternalSystemReservationId',
			'impServ_offerExtSysResDate'	=> 'impServ.offerExternalSystemReservationDate',
			'impServ_offerConfirmationNo'	=> 'impServ.offerConfirmationNumber',

			'impServ_offerTourCode'			=> 'impServ.offerTourCode',
			'impServ_reasonName'			=> 'impServ.reasonName',
			'impServ_subReasonName'			=> 'impServ.subReasonName',
			'impServ_formOfPayment'			=> 'impServ.offerFormOfPayment',
			'impServ_optionDate'			=> 'impServ.offerOptionDate',
			'impServ_isNdc'					=> 'impServ.isNdc',
			'impServ_itinerary'				=> 'impServ.matchedItinerary',
			'impServ_addTime'				=> 'impServ.addTime',
			'impServ_blockTime'				=> 'impServ.blockTime',
			'impServ_blockUserId'			=> 'impServ.idBlockUser',
			'impServ_blockComments'			=> 'impServ.blockedComments',
			'impServ_processedRemarks'		=> 'impServ.processedRemarks',

			'impServ_officeId'				=> 'impServ.idOffice',
			'impServ_userId'				=> 'impServ.idUser',
			'impServ_bookOfficeSystem'		=> 'impServ.idBookOfficeSystem',
			'impServ_bookOfficeId'			=> 'impServ.idBookOffice',
			'impServ_bookUserSystem'		=> 'impServ.idBookUserSystem',
			'impServ_bookUserId'			=> 'impServ.idBookUser',
			'impServ_issueOfficeSystem'		=> 'impServ.idIssueOfficeSystem',
			'impServ_issueOfficeId'			=> 'impServ.idIssueOffice',
			'impServ_issueUserSystem'		=> 'impServ.idIssueUserSystem',
			'impServ_issueUserId'			=> 'impServ.idIssueUser',
			'impServ_usersDetails'			=> 'impServ.usersDetails',

			'impServ_total'					=> 'impServ.offerTotalValue',
			'impServ_currency'				=> 'impServ.offerTotalCurrency',
			'impServ_priceComponents'		=> 'impServ.offerPricesDetails',
			'impServ_refundDetails'			=> 'impServ.refundDetails',
			'impServ_taxDetails'			=> 'impServ.taxDetails',

			'impOrder_id'					=> 'impOrd.id',
			'impOrder_idOrderExtSys'		=> 'impOrd.idOrderExtSys',
			'impOrder_date'					=> 'impOrd.date',
			'impOrder_originalCorporateName' => 'impOrd.ownerCorporate',
			'impOrder_matchedCorporateId'	=> 'impOrd.idCorporateOwner',
			'impOrder_miceProjectId'		=> 'impOrd.idMiceProject',
			'impOrder_userId'				=> 'impOrd.idUser',
			'impOrder_officeId'				=> 'impOrd.idOffice',
			'impOrder_addTime'				=> 'impOrd.addTime',

			'order_id'						=> 'orders.id',
			'order_convertedId'				=> 'orders.convertedId',
			'order_code'					=> 'orders.orderCode',
			'order_number'					=> 'orders.orderNumber',
			'order_date'					=> 'orders.date',
			'order_autoProcessRuleId'		=> 'orders.idAutoProcessRule',
			'order_typeCode'				=> 'orders.orderType',
			'order_flagCode'				=> 'orders.flag',
			'order_servicesTypeCode'		=> 'orders.servicesType',
			'order_ownerCustId'				=> 'orders.idCustomerOwner',
			'order_ownerCustProcessedRemarks' => 'ordersOwnCust.processedRemarks',
			'order_ownerCorpId'				=> 'orders.idCorporateOwner',
			'order_ownerCorpAlias'			=> 'ordersOwnCorp.alias',
			'order_ownerCorpProcessedRemarks' => 'ordersOwnCorp.processedRemarks',
			'order_ownerCorpSegmentId'		=> 'ordersOwnCorp.idSegment',
			'order_ownerCorpOfficeId'		=> 'orders.idCorporateOwnerOffice',
			'order_contactPersonId'			=> 'orders.idContactPerson',
			'order_miceProjectId'			=> 'orders.idMiceProject',
			'order_description'				=> 'orders.description',
			'order_comments'				=> 'orders.comments',
			'order_notes'					=> 'orders.notes',
			'order_statusOpCode'			=> 'orders.statusOp',
			'order_statusFinCode'			=> 'orders.statusFin',
			'order_genDocsAssoc'			=> 'orders.genDocsAssoc',
			'order_addTime'					=> 'orders.addTime',

			'order_userId'					=> 'orders.idUser',
			'order_officeId'				=> 'orders.idOffice',
			'order_departmentId'			=> 'orders.idDepartment',
			'order_addUserId'				=> 'orders.idAddUser',

			'service_id'					=> 'serv.id',
			'service_idPackageService'		=> 'serv.idPackageService',
			'service_linkedToIdService'		=> 'serv.linkedToIdService',
			'service_idAutoProcessRule'		=> 'serv.idAutoProcessRule',
			'service_serviceCode'			=> 'serv.serviceCode',
			'service_serviceName'			=> 'serv.serviceName',
			'service_statusOpCode'			=> 'serv.statusOp',
			'service_statusFinCode'			=> 'serv.statusFin',
			'service_pax'					=> 'serv.pax',
			'service_firstPsgId'			=> 'serv.idFirstCustomer',
			'service_firstPsgTypeCode'		=> 'servPsg.type',
			'service_firstPsgSegmentId'		=> 'servPsg.idSegment',
			'service_firstPsgProcessedRemarks' => 'servPsg.processedRemarks',

			'service_ticketNumber' 			=> 'serv.firstTicketNumber',
			'service_ticketNumberList' 		=> 'serv.passengersTicketsList',
			'service_ticketsDate'			=> 'serv.passengersTicketsDate',
			'service_startDate'				=> 'serv.startDate',
			'service_endDate'				=> 'serv.endDate',
			'service_duration'				=> 'serv.duration',
			'service_estInvoiceDate'		=> 'serv.estInvoiceDate',
			'service_estCashDate'			=> 'serv.estCashDate',
			'service_agencyCostCenterCode'	=> 'serv.agencyCostCenter',
			'service_description'			=> 'serv.description',
			'service_comments'				=> 'serv.comments',
			'service_preferences'			=> 'serv.preferences',
			'service_remarks'				=> 'serv.remarks',
			'service_processedRemarks'		=> 'serv.processedRemarks',
			'service_genDocsAssoc' 			=> 'serv.genDocsAssoc',
			'service_userId'				=> 'serv.idUser',
			'service_officeId'				=> 'serv.idOffice',
			'service_addTime'				=> 'serv.addTime',
			'service_addUserId'				=> 'serv.idAddUser',
			'service_updateUserId'			=> 'serv.idUpdateUser',
			'service_lastUpdateTime'		=> 'serv.lastUpdateTime',

			'service_departureCountryCode' 	=> 'serv.departureCountryCode',
			'service_departureCityId' 		=> 'serv.departureCityId',
			'service_departureStationCode' 	=> 'serv.departureStationCode',
			'service_destinationCountryCode'=> 'serv.destinationCountryCode',
			'service_destinationCityId' 	=> 'serv.destinationCityId',
			'service_destinationStationCode'=> 'serv.destinationStationCode',

			'offer_id'						=> 'offers.id',
			'offer_supplierId'				=> 'offers.idSupplier',
			'offer_supplierAlias'			=> 'offerSupp.alias',
			'offer_supplierIataCode'		=> 'offerSupp.iataCode',
			'offer_supplierTktCode'			=> 'offerSupp.tktCode',
			'offer_offerType'				=> 'offers.offerType',
			'offer_serviceCode'				=> 'offers.serviceCode',
			'offer_locationCode'			=> 'offers.location',
			'offer_calculationCode'			=> 'offers.calculationType',
			'offer_startDate'				=> 'offers.startDatetime',
			'offer_endDate'					=> 'offers.endDatetime',
			'offer_duration'				=> 'offers.duration',
			'offer_currencyTypeCode'		=> 'offers.currencyType',
			'offer_currencyDate'			=> 'offers.currencyDate',
			'offer_expirationDate'			=> 'offers.expirationDate',
			'offer_cancellationPolicy'		=> 'offers.cancellationPolicy',
			'offer_optionDate'				=> 'offers.optionDate',
			'offer_extSysResId'				=> 'offers.externalSystemReservationId',
			'offer_extSysResDate'			=> 'offers.externalSystemReservationDate',
			'offer_confirmationNumber'		=> 'offers.confirmationNumber',
			'offer_flightType'				=> 'offers.flightType',
			'offer_serviceClassCodeFirst'	=> 'offers.serviceClassCodeFirst',
			'offer_serviceClassNameFirst'	=> 'offers.serviceClassNameFirst',
			'offer_serviceClassCodes'		=> 'offers.serviceClassCodes',
			'offer_serviceClassNames'		=> 'offers.serviceClassNames',
			'offer_fareBasis'				=> 'offers.fareBasis',
			'offer_flightNumbers'			=> 'offers.flightNumbers',
			'offer_flightDurations'			=> 'offers.flightDurations',
			'offer_flightDistances'			=> 'offers.flightDistances',
			'offer_flightDistanceTotal'		=> 'offers.flightDistanceTotal',
			'offer_comments'				=> 'offers.comments',
			'offer_addTime'					=> 'offers.addTime',
			'offer_addUserId'				=> 'offers.idAddUser',

			'offer_departureCountryCode' 	=> 'offers.departureCountryCode',
			'offer_departureCityId' 		=> 'offers.departureCityId',
			'offer_departureStationCode' 	=> 'offers.departureStationCode',
			'offer_destinationCountryCode' 	=> 'offers.destinationCountryCode',
			'offer_destinationCityId' 		=> 'offers.destinationCityId',
			'offer_destinationStationCode' 	=> 'offers.destinationStationCode',
			'offer_itinerary'				=> 'offers.itinerary',
			'offer_itineraryReadable'		=> 'offers.itineraryReadable',

			'offer_ignoreSupplierOnDocs'	=> 'offers.ignoreSupplierOnDocuments',
			'offer_commissionable'			=> 'offers.commissionable',
			'offer_supplierPriceValue'		=> 'offers.supplierValue',
			'offer_supplierPriceVat'		=> 'offers.supplierVat',
			'offer_commissionValue'			=> 'offers.commissionValue',
			'offer_commissionVat' 			=> 'offers.commissionVat',
			'offer_discountValue' 			=> 'offers.discountValue',
			'offer_discountVat' 			=> 'offers.discountVat',
			'offer_serviceFeeValue' 		=> 'offers.serviceFeeValue',
			'offer_serviceFeeVat' 			=> 'offers.serviceFeeVat',
			'offer_taxValue' 				=> 'offers.taxValue',
			'offer_taxVat'					=> 'offers.taxVat',
			'offer_netCompany'				=> 'offers.netCompanyTotal',
			'offer_total' 					=> 'offers.offerTotal',
			'offer_currency' 				=> 'offers.currency',
			'offer_currencyRate' 			=> 'offers.currencyRate',
			'offer_workCurrencyRate' 		=> 'offers.workCurrencyRate',

			'offer_formOfPayment'           => 'offers.formOfPayment',
			'offer_paymentCcMaskedNo'       => 'offers.paymentCcMaskedNo',
			'offer_paymentCcExpireDate'     => 'offers.paymentCcExpireDate',
			'offer_paymentCcCompany'        => 'offers.paymentCcCompany',
			'offer_paymentCcApprovalCode'   => 'offers.paymentCcApprovalCode',
			'offer_paymentCcValue'          => 'offers.paymentCcValue',
			'offer_paymentCcCurrency'       => 'offers.paymentCcCurrency',
			'offer_formOfCashing'           => 'offers.formOfCashing',
			'offer_cashingCcMaskedNo'       => 'offers.cashingCcMaskedNo',
			'offer_cashingCcExpireDate'     => 'offers.cashingCcExpireDate',
			'offer_cashingCcCompany'        => 'offers.cashingCcCompany',
			'offer_cashingCcApprovalCode'   => 'offers.cashingCcApprovalCode',

			'inv_id'						=> 'inv.id',
			'inv_invoiceNumber'				=> 'inv.invoiceNumber',
			'inv_seriesId'					=> 'inv.idInvoiceSeries',
			'inv_formOfPaymentCode'			=> 'inv.formOfPayment',
			'inv_invoiceDate'				=> 'inv.invoiceDate',
			'inv_dueDate'					=> 'inv.dueDate',
			'inv_fulfillmentDate'			=> 'inv.fulfillmentDate',
			'inv_idAutoProcessRule'			=> 'inv.idAutoProcessRule',
			'inv_temporary'					=> 'inv.temporary',
			'inv_cumulative'				=> 'inv.cumulativeInvoice',
			'inv_roundedTotal'				=> 'inv.roundTotal',
			'inv_bankAccountId'				=> 'inv.idBankAccount',
			'inv_remarks'					=> 'inv.remarks',
			'inv_processedRemarks'			=> 'inv.processedRemarks',
			'inv_genDocsAssoc' 				=> 'inv.genDocsAssoc',
			'inv_userId'					=> 'inv.idUser',
			'inv_officeId'					=> 'inv.idOffice',
			'inv_issueTime'					=> 'inv.issueTime',
			'inv_issueUserId'				=> 'inv.idIssueUser',
			'inv_addTime'					=> 'inv.addTime',
			'inv_addUserId'					=> 'inv.idAddUser',

			'inv_currency'					=> 'serv.invoiceCurrency',
			'inv_currencyRate'				=> 'serv.invoiceCurrencyRate',
			'inv_workCurrencyRate'			=> 'serv.invoiceWorkCurrencyRate',

			'inv_corporateSelOfficeId'		=> 'inv.idCorporateOffice',
			'inv_corporateSelOfficeName'	=> 'invCorpOff.name',
			'inv_corporateSelOfficeCountryCode' => 'invCorpOff.countryCode',
			'inv_corporateSelOfficeCityId'	=> 'invCorpOff.cityId',
			'inv_corporateOriginalId'		=> 'inv.idCorporate',
			'inv_customerOriginalId'		=> 'inv.idCustomer',

			'cash_lastCashingDate'			=> 'inv.lastCashingDate',

			'balance_id'					=> 'bal.id',
			'balance_number'				=> 'bal.balanceNumber',
			'balance_code'					=> 'bal.balanceCode',
			'balance_name'					=> 'bal.balanceName',
			'balance_date'					=> 'bal.balanceDate',
			'balance_virtualDate'			=> 'bal.virtualDate',
			'balance_profitPercent'			=> 'bal.profitPercent',
			'balance_userId'				=> 'bal.idUser',
			'balance_officeId'				=> 'bal.idOffice',
			'balance_confirmUserId'			=> 'bal.idConfirmUser',
			'balance_confirmTime'			=> 'bal.confirmTime',
			'balance_addUserId'				=> 'bal.idAddUser',
			'balance_addTime'				=> 'bal.addTime',
		];


		$commonColumnsFunction = [

			[ 'column' => 'impServ_ticketDateYM',			'value' => "DATE_FORMAT(impServ.ticketsDate, '%Y-%m-01')",									'table' => 'impServ' ],
			[ 'column' => 'impServ_ticketDateY',			'value' => "YEAR(impServ.ticketsDate)",														'table' => 'impServ' ],
			[ 'column' => 'impServ_reissue',				'value' => 'IF(impServ.idImportedServiceReissued, 1, 0)',									'table' => 'impServ' ],
			[ 'column' => 'impServ_offerExtSysResDateYM',	'value' => "DATE_FORMAT(impServ.offerExternalSystemReservationDate, '%Y-%m-01')",			'table' => 'impServ' ],
			[ 'column' => 'impServ_offerExtSysResDateY',	'value' => "YEAR(impServ.offerExternalSystemReservationDate)",								'table' => 'impServ' ],
			[ 'column' => 'impServ_passengerFullName',		'value' => "TRIM(CONCAT(impServ.passengerLastName, ' ', impServ.passengerFirstName))",		'table' => 'impServ' ],

			[ 'column' => 'service_internationalIndicator',	'value' => "IF((offers.location=''), (IF((impServ.offerCriterion IS NULL OR impServ.offerCriterion=''), '', (IF(impServ.offerCriterion='int', 'D', 'I')))), (IF(offers.location='int', 'D', 'I')))",	'table' => 'offers' ],

			[ 'column' => 'order_ownerCustName',			'value' => "CONCAT(ordersOwnCust.lastNameStd, ' ', ordersOwnCust.firstNameStd)", 'table' => 'ordersOwnCust' ],
			[ 'column' => 'order_dateYM',					'value' => "DATE_FORMAT(orders.date, '%Y-%m-01')",					'table' => 'orders' ],
			[ 'column' => 'order_dateY',					'value' => "YEAR(orders.date)",										'table' => 'orders' ],

			[ 'column' => 'service_isPackageSubservice',	'value' => 'IF(serv.idPackageService, 1, 0)',						'table' => 'serv' ],
			[ 'column' => 'service_firstPsgName',			'value' => "CONCAT(servPsg.lastNameStd, ' ', servPsg.firstNameStd)",'table' => 'servPsg' ],

			[ 'column' => 'offer_supplierPriceTotal',		'value' => 'offers.supplierValue+offers.supplierVat',				'table' => 'offers' ],
			[ 'column' => 'offer_commissionTotal',			'value' => 'offers.commissionValue+offers.commissionVat',			'table' => 'offers' ],
			[ 'column' => 'offer_discountTotal',			'value' => 'offers.discountValue+offers.discountVat',				'table' => 'offers' ],
			[ 'column' => 'offer_serviceFeeTotal',			'value' => 'offers.serviceFeeValue+offers.serviceFeeVat',			'table' => 'offers' ],
			[ 'column' => 'offer_taxTotal',					'value' => 'offers.taxValue+offers.taxVat',							'table' => 'offers' ],
			[ 'column' => 'offer_estProfit',				'value' => 'IF(offers.ignoreSupplierOnDocuments, (offers.serviceFeeValue+offers.serviceFeeVat-offers.discountValue-offers.discountVat), (offers.offerTotal-offers.netCompanyTotal))', 'table' => 'offers' ],
			[ 'column' => 'offer_estProfitDefCurr',			'value' => 'IF(offers.ignoreSupplierOnDocuments, ((offers.serviceFeeValue+offers.serviceFeeVat-offers.discountValue-offers.discountVat)*offers.currencyRate), ((offers.offerTotal-offers.netCompanyTotal)*offers.currencyRate))', 'table' => 'offers' ],
			[ 'column' => 'offer_netCompanyDefCurr',		'value' => 'offers.netCompanyTotal*offers.currencyRate',			'table' => 'offers' ],
			[ 'column' => 'offer_totalDefCurr',				'value' => 'offers.offerTotal*offers.currencyRate',					'table' => 'offers' ],

			[ 'column' => 'offer_estProfitWorkCurr',		'value' => 'IF(offers.ignoreSupplierOnDocuments, ((offers.serviceFeeValue+offers.serviceFeeVat-offers.discountValue-offers.discountVat)*offers.currencyRate/offers.workCurrencyRate), ((offers.offerTotal-offers.netCompanyTotal)*offers.currencyRate/offers.workCurrencyRate))', 'table' => 'offers' ],
			[ 'column' => 'offer_netCompanyWorkCurr',		'value' => 'offers.netCompanyTotal*offers.currencyRate/offers.workCurrencyRate', 'table' => 'offers' ],
			[ 'column' => 'offer_totalWorkCurr',			'value' => 'offers.offerTotal*offers.currencyRate/offers.workCurrencyRate', 'table' => 'offers' ],

			[ 'column' => 'inv_issued',						'value' => 'IF(inv.issueTime IS NULL, 0, 1)',						'table' => 'inv' ],
			[ 'column' => 'inv_invoiceDateYM',				'value' => "DATE_FORMAT(inv.invoiceDate, '%Y-%m-01')",				'table' => 'inv' ],
			[ 'column' => 'inv_invoiceDateY',				'value' => "YEAR(inv.invoiceDate)",									'table' => 'inv' ],

			[ 'column' => 'inv_corporateId',				'value' => "IF(invCorp.idRealCorporate, invCorp.idRealCorporate, inv.idCorporate)", 'table' => 'invCorp'],
			[ 'column' => 'inv_customerId',					'value' => "IF(invCust.idRealCustomer, invCust.idRealCustomer, inv.idCustomer)", 'table' => 'invCust'],
			[ 'column' => 'inv_corporateDuplicated', 		'value' => "IF(invCorp.idRealCorporate, 1, 0)",						'table' => 'invCorp'],
			[ 'column' => 'inv_customerDuplicated', 		'value' => "IF(invCust.idRealCustomer, 1, 0)",						'table' => 'invCust'],

			[ 'column' => 'balance_balanced', 				'value' => "IF(bal.id, 1, 0)",										'table' => 'bal'],
			[ 'column' => 'balance_confirmed', 				'value' => "IF(bal.confirmTime IS NOT NULL, 1, 0)",					'table' => 'bal'],


		];


		///////////////////////////////////////////////////////////////////////////////////////////
		// 1. ADD ISSUES FROM TINA SERVICES ///////////////////////////////////////////////////////
		///////////////////////////////////////////////////////////////////////////////////////////

		// refresh report status
		$this->reportGenerationStatusUpdate('importServices', 'import_tinaServIssue', 0);

		$reportImportTinaIss = new \App\Reports\Db\ManageRecords\Insert($reportTable);
		$reportImportTinaIss->setMainTable($DBTRd_services, 'serv') ;
		$reportImportTinaIss->addJoinTable($DBTRd_importedOrderServices, 'impServ', 'serv.idImportedService = impServ.id', 'LEFT') ;
		$reportImportTinaIss->addJoinTable($DBTRd_importedOrderSystems, 'extSys', 'extSys.id = impServ.idSystem', 'LEFT') ;
		$reportImportTinaIss->addJoinTable($DBTRd_importedOrders, 'impOrd', 'impServ.idImportedOrder = impOrd.id', 'LEFT') ;
		$reportImportTinaIss->addJoinTable($DBTRd_customers, 'servPsg', 'servPsg.id = serv.idFirstCustomer', 'LEFT');
		$reportImportTinaIss->addJoinTable($DBTRd_orders, 'orders', 'serv.idOrder = orders.id', 'INNER');
		$reportImportTinaIss->addJoinTable($DBTRd_customers, 'ordersOwnCust', 'orders.idCustomerOwner = ordersOwnCust.id', 'LEFT');
		$reportImportTinaIss->addJoinTable($DBTRd_corporates, 'ordersOwnCorp', 'orders.idCorporateOwner = ordersOwnCorp.id', 'LEFT');
		$reportImportTinaIss->addJoinTable($DBTRd_offers, 'offers', 'offers.idService=serv.id', 'LEFT');
		$reportImportTinaIss->addJoinTable($DBTRd_suppliers, 'offerSupp', 'offerSupp.id=offers.idSupplier', 'LEFT');
		$reportImportTinaIss->addJoinTable($DBTRd_clientInvoices, 'inv', 'serv.idRelevantInvoice = inv.id', 'LEFT');
		$reportImportTinaIss->addJoinTable($DBTRd_corporates, 'invCorp', 'inv.idCorporate = invCorp.id', 'LEFT');
		$reportImportTinaIss->addJoinTable($DBTRd_corporateOffices, 'invCorpOff', 'inv.idCorporateOffice = invCorpOff.id', 'LEFT');
		$reportImportTinaIss->addJoinTable($DBTRd_customers, 'invCust', 'inv.idCustomer = invCust.id', 'LEFT');
		$reportImportTinaIss->addJoinTable($DBTRd_balances, 'bal', 'serv.idBalance = bal.id', 'LEFT');

		// ADD FILTERS ------------------------------------------------------------------------------------
		$reportImportTinaIss->addFilter('serv.statusOp', '!=', 'XX');
		$reportImportTinaIss->addFilter('serv.serviceCode', 'IN', $CFG['reportsGeneration']['generation']['details']['ticketing']['includedServiceCodes']);
		$reportImportTinaIss->addFilter('serv.passengersTicketsDate', 'BETWEEN', [$stdFilters['operationDate']['start'], $stdFilters['operationDate']['end']]);
		// ticket operation type
		if ($operationTypes) {
			if (!$operationTypes['ISSUE'] and !$operationTypes['REFUNDED']) { // no issue and no refund
				$reportImportTinaIss->addCustomFilter('0');
			}
			if ($operationTypes['ISSUE'] and !$operationTypes['REFUNDED']) { // only refunded but not issued
				$reportImportTinaIss->addFilter('serv.refunded', '=', '0');
			}
			if (!$operationTypes['ISSUE'] and $operationTypes['REFUNDED']) { // only issued  but not refunded
				$reportImportTinaIss->addFilter('serv.refunded', '=', '1');
			}
		}
		// ext systems
		if ($stdFilters['idImpOrdersSystems']) {
			if (!in_array(0, $stdFilters['idImpOrdersSystems'])) {
				$reportImportTinaIss->addFilter('impServ.idSystem', 'IN', $stdFilters['idImpOrdersSystems']);
			} else {
				$custFilters = [];$bindVars = [];
				foreach($stdFilters['idImpOrdersSystems'] as $idSystem) {
					if ($idSystem) {
						$custFilters[] = "impServ.idSystem = ?";
						$bindVars[] = $idSystem;
					} else {
						$custFilters[] = "impServ.idSystem IS NULL";
					}
				}
				$reportImportTinaIss->addCustomFilter(implode(' OR ', $custFilters), $bindVars);
			}
		}
		// order period
		if ($stdFilters['orderDate']['start']) {
			$reportImportTinaIss->addFilter('orders.date', '>=', $stdFilters['orderDate']['start']);
		}
		if ($stdFilters['orderDate']['end']) {
			$reportImportTinaIss->addFilter('orders.date', '<=', $stdFilters['orderDate']['end']);
		}
		// service status fin
		if ($stdFilters['serviceStatusFin']) {
			if (!array_intersect($stdFilters['serviceStatusFin'], ['NI', 'PI', 'TI'])) {
				$reportImportTinaIss->addCustomFilter('0');
			} else {
				$reportImportTinaIss->addFilter('serv.statusFin', 'IN', array_filter($stdFilters['serviceStatusFin']));
			}
		}
		// service startDate period
		if ($stdFilters['offerStartDate']['start']) {
			$reportImportTinaIss->addFilter('offers.startDatetime', '>=', $stdFilters['offerStartDate']['start'].' 00:00:00');
		}
		if ($stdFilters['offerStartDate']['end']) {
			$reportImportTinaIss->addFilter('offers.startDatetime', '<=', $stdFilters['offerStartDate']['end'].' 23:59:59');
		}
		// service psg
		if ($stdFilters['idServicePassengers']) {
			$reportImportTinaIss->addFilter('serv.idFirstCustomer', 'IN', $stdFilters['idServicePassengers']);
		}
		// imp serv book user/office
		if ($stdFilters['idImpServBookUsers']) {
			$reportImportTinaIss->addFilter('impServ.idBookUser', 'IN', $stdFilters['idImpServBookUsers']);
		}
		if ($stdFilters['idImpServBookOffices']) {
			$reportImportTinaIss->addFilter('impServ.idBookOffice', 'IN', $stdFilters['idImpServBookOffices']);
		}
		// imp serv issue user/office
		if ($stdFilters['idImpServIssueUsers']) {
			$reportImportTinaIss->addFilter('impServ.idIssueUser', 'IN', $stdFilters['idImpServIssueUsers']);
		}
		if ($stdFilters['idImpServIssueOffices']) {
			$reportImportTinaIss->addFilter('impServ.idIssueOffice', 'IN', $stdFilters['idImpServIssueOffices']);
		}
		// serv resp user/office
		if ($stdFilters['idServiceRespUsers']) {
			$reportImportTinaIss->addFilter('serv.idUser', 'IN', $stdFilters['idServiceRespUsers']);
		}
		if ($stdFilters['idServiceRespOffices']) {
			$reportImportTinaIss->addFilter('serv.idOffice', 'IN', $stdFilters['idServiceRespOffices']);
		}
		// offer supplier
		if ($stdFilters['idOfferSuppliers']) {
			$reportImportTinaIss->addFilter('offers.idSupplier', 'IN', $stdFilters['idOfferSuppliers']);
		}
		// offer destination
		if ($stdFilters['offerDestinationCityIds']) {
			$reportImportTinaIss->addFilter('offers.destinationCityId', 'IN', $stdFilters['offerDestinationCityIds']);
		}
		if ($stdFilters['offerDestinationCountryCodes']) {
			$reportImportTinaIss->addFilter('offers.destinationCountryCode', 'IN', $stdFilters['offerDestinationCountryCodes']);
		}
		// offer FOP
		if ($stdFilters['offerFormOfPayment']) {
			// "no FOP" included
			if (in_array('0', $stdFilters['offerFormOfPayment'])) {
				// add no FOP in the custom filter
				$customFilter = "offers.formOfPayment='' OR offers.formOfPayment IS NULL";
				$bindVars = [];
				// if there are other (regular) FOPS add them as well
				if (count($stdFilters['offerFormOfPayment'])>1) {
					$bindVars = array_diff($stdFilters['offerFormOfPayment'], ['0']);
					$customFilter .= " OR offers.formOfPayment IN (". implode( ', ', array_fill(0, count($bindVars), '?') ) .")";
				}
				// apply filter
				$reportImportTinaIss->addCustomFilter($customFilter, $bindVars);
				$reportImportTinaIss->addFilter('offers.formOfPayment', '!=', 'FAKE_VALUE_FOP'); // this is a workaround to help the SQL builder to add the `offers` table in the SQL (in case it's not already included)
			}
			// only regular FOPs included
			else {
				$reportImportTinaIss->addFilter('offers.formOfPayment', 'IN', $stdFilters['offerFormOfPayment']);
			}
		}

		// invoice period
		if ($stdFilters['invoiceDate']['start']) {
			$reportImportTinaIss->addFilter('inv.invoiceDate', '>=', $stdFilters['invoiceDate']['start']);
		}
		if ($stdFilters['invoiceDate']['end']) {
			$reportImportTinaIss->addFilter('inv.invoiceDate', '<=', $stdFilters['invoiceDate']['end']);
		}
		if ($stdFilters['invoiceDate']['start'] or $stdFilters['invoiceDate']['end']) {
			$reportImportTinaIss->addFilter('inv.invoiceDate', 'IS NOT NULL');
		}
		// invoice clients
		if ($stdFilters['idInvoiceCorporates']) {
			$reportImportTinaIss->addFilter('inv.idCorporate', 'IN', $this->filtersManager->getDuplicateEntitiesGroup('idInvoiceCorporates'));
		}
		if ($stdFilters['idInvoiceCustomers']) {
			$reportImportTinaIss->addFilter('inv.idCustomer', 'IN', $this->filtersManager->getDuplicateEntitiesGroup('idInvoiceCustomers'));
		}

		// filter by corporates with groups
		if ($stdFilters['invoiceCorporateGroups']) {
			$corporateGroupsHelper = new \App\ReportsData\Helpers\PartnerGroups\Corporate();
			$corporateGroupsHelper->setGroupElementsLinear($stdFilters['invoiceCorporateGroups']);
			$corpIds = $corporateGroupsHelper->getPartnerIdsList();
			if ($corpIds) {
				$reportImportTinaIss->addFilter('inv.idCorporate', 'IN', $corpIds);
			} else {
				$reportImportTinaIss->addCustomFilter("0");
			}
		}

		// filter by suppliers with groups
		if ($stdFilters['offerSupplierGroups']) {
			$supplierGroupsHelper = new \App\ReportsData\Helpers\PartnerGroups\Supplier();
			$supplierGroupsHelper->setGroupElementsLinear($stdFilters['offerSupplierGroups']);
			$suppIds = $supplierGroupsHelper->getPartnerIdsList();
			if ($suppIds) {
				$reportImportTinaIss->addFilter('offers.idSupplier', 'IN', $suppIds);
			} else {
				$reportImportTinaIss->addCustomFilter("0");
			}
		}



		// ADD COLUMNS ----------------------------------------------------------------------------

		$reportImportTinaIss->setActiveColumns($activeColumns);

		// ADD BASIC COLUMNS ......................................................................
		$cols = $commonColumnsBasic;
		$cols['tkt_date'] 							= 'serv.passengersTicketsDate';
		$cols['tkt_tktNumber'] 						= 'serv.firstTicketNumber';
		$cols['impServ_processStatusCode']			= 'impServ.processStatus';
		$cols['inv_total']							= 'serv.invoiceIssueTotal';
		$cols['inv_components']						= 'serv.invoiceIssueValues';
		$cols['cash_cashedValue']					= 'serv.invoiceIssueCashedValue';
		$cols['cash_notCashedValue']				= 'serv.invoiceIssueNotCashedValue';
		$cols['balance_profitDefCurr']				= 'serv.balanceIssueProfitDefCurr';

		$reportImportTinaIss->setStructureColumns($cols);

		// ADD FUNCTION COLUMNS ...................................................................

		// add common function columns
		foreach($commonColumnsFunction as $structCol) {
			$reportImportTinaIss->addStructureColumn($structCol['column'], $structCol['value'], $structCol['table']);
		}

		$reportImportTinaIss->addStructureColumn("tkt_key", "CONCAT('T', serv.id, 'I')", 'serv');
		$reportImportTinaIss->addStructureColumn("tkt_operation", "IF(serv.refunded, 'REFUNDED', 'ISSUE')", 'serv');
		$reportImportTinaIss->addStructureColumn("tkt_imported", "IF(serv.idImportedService , 1, 0)", 'serv');
		$reportImportTinaIss->addStructureColumn("tkt_voided", "IF((impServ.systemStatus='cancelled' OR impServ.systemStatus='deleted'), 1, 0)", 'impServ');
		$reportImportTinaIss->addStructureColumn("tkt_dateYM", "DATE_FORMAT(serv.passengersTicketsDate, '%Y-%m-01')", 'serv');
		$reportImportTinaIss->addStructureColumn("tkt_dateY", "YEAR(serv.passengersTicketsDate)", 'serv');
		$reportImportTinaIss->addStructureColumn("inv_totalDefCurr", "serv.invoiceIssueTotal*serv.invoiceCurrencyRate", 'serv');
		$reportImportTinaIss->addStructureColumn("inv_totalWorkCurr", "serv.invoiceIssueTotal*serv.invoiceCurrencyRate/serv.invoiceWorkCurrencyRate", 'serv');


		// POPULATE REPORT ------------------------------------------------------------------------
		$reportImportTinaIss->populate();



		///////////////////////////////////////////////////////////////////////////////////////////
		// 2. ADD REFUNDS FROM TINA SERVICES //////////////////////////////////////////////////////
		///////////////////////////////////////////////////////////////////////////////////////////

		// refresh report status
		$this->reportGenerationStatusUpdate('importServices', 'import_tinaServRefund', 0);

		$reportImportTinaRfnd = new \App\Reports\Db\ManageRecords\Insert($reportTable);
		$reportImportTinaRfnd->setMainTable($DBTRd_services, 'serv') ;
		$reportImportTinaRfnd->addJoinTable($DBTRd_importedOrderServices, 'impServ', 'serv.idImportedService = impServ.id', 'LEFT') ;
		$reportImportTinaRfnd->addJoinTable($DBTRd_importedOrderSystems, 'extSys', 'extSys.id = impServ.idSystem', 'LEFT') ;
		$reportImportTinaRfnd->addJoinTable($DBTRd_importedOrders, 'impOrd', 'impServ.idImportedOrder = impOrd.id', 'LEFT') ;
		$reportImportTinaRfnd->addJoinTable($DBTRd_customers, 'servPsg', 'servPsg.id = serv.idFirstCustomer', 'LEFT');
		$reportImportTinaRfnd->addJoinTable($DBTRd_orders, 'orders', 'serv.idOrder = orders.id', 'INNER');
		$reportImportTinaRfnd->addJoinTable($DBTRd_customers, 'ordersOwnCust', 'orders.idCustomerOwner = ordersOwnCust.id', 'LEFT');
		$reportImportTinaRfnd->addJoinTable($DBTRd_corporates, 'ordersOwnCorp', 'orders.idCorporateOwner = ordersOwnCorp.id', 'LEFT');
		$reportImportTinaRfnd->addJoinTable($DBTRd_offers, 'offers', 'offers.idService=serv.id', 'LEFT');
		$reportImportTinaRfnd->addJoinTable($DBTRd_suppliers, 'offerSupp', 'offerSupp.id=offers.idSupplier', 'LEFT');
		$reportImportTinaRfnd->addJoinTable($DBTRd_clientInvoices, 'inv', 'serv.idRelevantInvoiceRefund = inv.id', 'LEFT');
		$reportImportTinaRfnd->addJoinTable($DBTRd_corporates, 'invCorp', 'inv.idCorporate = invCorp.id', 'LEFT');
		$reportImportTinaRfnd->addJoinTable($DBTRd_corporateOffices, 'invCorpOff', 'inv.idCorporateOffice = invCorpOff.id', 'LEFT');
		$reportImportTinaRfnd->addJoinTable($DBTRd_customers, 'invCust', 'inv.idCustomer = invCust.id', 'LEFT');
		$reportImportTinaRfnd->addJoinTable($DBTRd_balances, 'bal', 'serv.idBalanceRefund = bal.id', 'LEFT');

		// ADD FILTERS ----------------------------------------------------------------------------
		$reportImportTinaRfnd->addFilter('serv.statusOp', '!=', 'XX');
		$reportImportTinaRfnd->addFilter('serv.serviceCode', 'IN', $CFG['reportsGeneration']['generation']['details']['ticketing']['includedServiceCodes']);
		$reportImportTinaRfnd->addFilter('serv.refundDate', 'BETWEEN', [$stdFilters['operationDate']['start'], $stdFilters['operationDate']['end']]);
		// operation types (if REFUND ops not checked - simply ignore them)
		if ($operationTypes and !$operationTypes['REFUND']) {
			$reportImportTinaRfnd->addCustomFilter('0');
		}
		// ext systems
		if ($stdFilters['idImpOrdersSystems']) {
			if (!in_array(0, $stdFilters['idImpOrdersSystems'])) {
				$reportImportTinaRfnd->addFilter('impServ.idSystem', 'IN', $stdFilters['idImpOrdersSystems']);
			} else {
				$custFilters = [];$bindVars = [];
				foreach($stdFilters['idImpOrdersSystems'] as $idSystem) {
					if ($idSystem) {
						$custFilters[] = "impServ.idSystem = ?";
						$bindVars[] = $idSystem;
					} else {
						$custFilters[] = "impServ.idSystem IS NULL";
					}
				}
				$reportImportTinaRfnd->addCustomFilter(implode(' OR ', $custFilters), $bindVars);
			}
		}
		// order period
		if ($stdFilters['orderDate']['start']) {
			$reportImportTinaRfnd->addFilter('orders.date', '>=', $stdFilters['orderDate']['start']);
		}
		if ($stdFilters['orderDate']['end']) {
			$reportImportTinaRfnd->addFilter('orders.date', '<=', $stdFilters['orderDate']['end']);
		}
		// offer startDate period
		if ($stdFilters['offerStartDate']['start']) {
			$reportImportTinaRfnd->addFilter('offers.startDatetime', '>=', $stdFilters['offerStartDate']['start'].' 00:00:00');
		}
		if ($stdFilters['offerStartDate']['end']) {
			$reportImportTinaRfnd->addFilter('offers.startDatetime', '<=', $stdFilters['offerStartDate']['end'].' 23:59:59');
		}
		// service status fin
		if ($stdFilters['serviceStatusFin']) {
			if (!array_intersect($stdFilters['serviceStatusFin'], ['NI', 'PI', 'TI'])) {
				$reportImportTinaRfnd->addCustomFilter('0');
			} else {
				$reportImportTinaRfnd->addFilter('serv.statusFin', 'IN', array_filter($stdFilters['serviceStatusFin']));
			}
		}
		// service psg
		if ($stdFilters['idServicePassengers']) {
			$reportImportTinaRfnd->addFilter('serv.idFirstCustomer', 'IN', $stdFilters['idServicePassengers']);
		}
		// imp serv book user/office
		if ($stdFilters['idImpServBookUsers']) {
			$reportImportTinaRfnd->addFilter('impServ.idBookUser', 'IN', $stdFilters['idImpServBookUsers']);
		}
		if ($stdFilters['idImpServBookOffices']) {
			$reportImportTinaRfnd->addFilter('impServ.idBookOffice', 'IN', $stdFilters['idImpServBookOffices']);
		}
		// imp serv issue user/office
		if ($stdFilters['idImpServIssueUsers']) {
			$reportImportTinaRfnd->addFilter('impServ.idIssueUser', 'IN', $stdFilters['idImpServIssueUsers']);
		}
		if ($stdFilters['idImpServIssueOffices']) {
			$reportImportTinaRfnd->addFilter('impServ.idIssueOffice', 'IN', $stdFilters['idImpServIssueOffices']);
		}
		// serv user/office
		if ($stdFilters['idServiceRespUsers']) {
			$reportImportTinaRfnd->addFilter('serv.idUser', 'IN', $stdFilters['idServiceRespUsers']);
		}
		if ($stdFilters['idServiceRespOffices']) {
			$reportImportTinaRfnd->addFilter('serv.idOffice', 'IN', $stdFilters['idServiceRespOffices']);
		}
		// offer supplier
		if ($stdFilters['idOfferSuppliers']) {
			$reportImportTinaRfnd->addFilter('offers.idSupplier', 'IN', $stdFilters['idOfferSuppliers']);
		}
		// offer destination
		if ($stdFilters['offerDestinationCityIds']) {
			$reportImportTinaRfnd->addFilter('offers.destinationCityId', 'IN', $stdFilters['offerDestinationCityIds']);
		}
		if ($stdFilters['offerDestinationCountryCodes']) {
			$reportImportTinaRfnd->addFilter('offers.destinationCountryCode', 'IN', $stdFilters['offerDestinationCountryCodes']);
		}
		// offer FOP
		if ($stdFilters['offerFormOfPayment']) {
			// "no FOP" included
			if (in_array('0', $stdFilters['offerFormOfPayment'])) {
				// add no FOP in the custom filter
				$customFilter = "offers.formOfPayment='' OR offers.formOfPayment IS NULL";
				$bindVars = [];
				// if there are other (regular) FOPS add them as well
				if (count($stdFilters['offerFormOfPayment'])>1) {
					$bindVars = array_diff($stdFilters['offerFormOfPayment'], ['0']);
					$customFilter .= " OR offers.formOfPayment IN (". implode( ', ', array_fill(0, count($bindVars), '?') ) .")";
				}
				// apply filter
				$reportImportTinaRfnd->addCustomFilter($customFilter, $bindVars);
				$reportImportTinaRfnd->addFilter('offers.formOfPayment', '!=', 'FAKE_VALUE_FOP'); // this is a workaround to help the SQL builder to add the `offers` table in the SQL (in case it's not already included)
			}
			// only regular FOPs included
			else {
				$reportImportTinaRfnd->addFilter('offers.formOfPayment', 'IN', $stdFilters['offerFormOfPayment']);
			}
		}

		// invoice date
		if ($stdFilters['invoiceDate']['start']) {
			$reportImportTinaRfnd->addFilter('inv.invoiceDate', '>=', $stdFilters['invoiceDate']['start']);
		}
		if ($stdFilters['invoiceDate']['end']) {
			$reportImportTinaRfnd->addFilter('inv.invoiceDate', '<=', $stdFilters['invoiceDate']['end']);
		}
		if ($stdFilters['invoiceDate']['start'] or $stdFilters['invoiceDate']['end']) {
			$reportImportTinaRfnd->addFilter('inv.invoiceDate', 'IS NOT NULL');
		}
		// invoice clients
		if ($stdFilters['idInvoiceCorporates']) {
			$reportImportTinaRfnd->addFilter('inv.idCorporate', 'IN', $this->filtersManager->getDuplicateEntitiesGroup('idInvoiceCorporates'));
		}
		if ($stdFilters['idInvoiceCustomers']) {
			$reportImportTinaRfnd->addFilter('inv.idCustomer', 'IN', $this->filtersManager->getDuplicateEntitiesGroup('idInvoiceCustomers'));
		}

		// filter by corporates with groups
		if ($stdFilters['invoiceCorporateGroups']) {
			$corporateGroupsHelper = new \App\ReportsData\Helpers\PartnerGroups\Corporate();
			$corporateGroupsHelper->setGroupElementsLinear($stdFilters['invoiceCorporateGroups']);
			$corpIds = $corporateGroupsHelper->getPartnerIdsList();
			if ($corpIds) {
				$reportImportTinaRfnd->addFilter('inv.idCorporate', 'IN', $corpIds);
			} else {
				$reportImportTinaRfnd->addCustomFilter("0");
			}
		}

		// filter by suppliers with groups
		if ($stdFilters['offerSupplierGroups']) {
			$supplierGroupsHelper = new \App\ReportsData\Helpers\PartnerGroups\Supplier();
			$supplierGroupsHelper->setGroupElementsLinear($stdFilters['offerSupplierGroups']);
			$suppIds = $supplierGroupsHelper->getPartnerIdsList();
			if ($suppIds) {
				$reportImportTinaRfnd->addFilter('offers.idSupplier', 'IN', $suppIds);
			} else {
				$reportImportTinaRfnd->addCustomFilter("0");
			}
		}

		// ADD COLUMNS ----------------------------------------------------------------------------

		$reportImportTinaRfnd->setActiveColumns($activeColumns);

		// ADD BASIC COLUMNS ......................................................................
		$cols = $commonColumnsBasic;
		$cols['tkt_date']							= 'serv.refundDate';
		$cols['tkt_operation']						= "'REFUND'";
		$cols['tkt_voided']							= "'0'";
		$cols['inv_total']							= 'serv.invoiceRefundTotal';
		$cols['inv_components']						= 'serv.invoiceRefundValues';
		$cols['cash_cashedValue']					= 'serv.invoiceRefundCashedValue';
		$cols['cash_notCashedValue']				= 'serv.invoiceRefundNotCashedValue';
		$cols['balance_profitDefCurr']				= 'serv.balanceRefundProfitDefCurr';

		$reportImportTinaRfnd->setStructureColumns($cols);

		// ADD CUSTOM (FUNCTION) COLUMNS ..........................................................

		// add common structure columns
		foreach($commonColumnsFunction as $structCol) {
			$reportImportTinaRfnd->addStructureColumn($structCol['column'], $structCol['value'], $structCol['table']);
		}

		// add custom function columns
		$reportImportTinaRfnd->addStructureColumn("tkt_key", "CONCAT('T', serv.id, 'R')", 'serv');
		$reportImportTinaRfnd->addStructureColumn("tkt_tktNumber", "IF(serv.firstTicketNumber, CONCAT(serv.firstTicketNumber, 'R'), '')", 'serv');
		$reportImportTinaRfnd->addStructureColumn("tkt_imported", "IF((serv.idImportedService AND impServ.systemStatus='refunded'), 1, 0)", 'impServ');
		$reportImportTinaRfnd->addStructureColumn("tkt_dateYM", "DATE_FORMAT(serv.refundDate, '%Y-%m-01')", 'serv');
		$reportImportTinaRfnd->addStructureColumn("tkt_dateY", "YEAR(serv.refundDate)", 'serv');

		$reportImportTinaRfnd->addStructureColumn("impServ_processStatusCode", "IF(serv.idRelevantInvoiceRefund, 'processed', 'new')", 'impServ');

		// POPULATE REPORT ------------------------------------------------------------------------
		$reportImportTinaRfnd->populate();



		///////////////////////////////////////////////////////////////////////////////////////////
		// 3. ADD ISSUES FROM IMPORTED SERVICES (NOT PROCESSED) ///////////////////////////////////
		///////////////////////////////////////////////////////////////////////////////////////////

		// refresh report status
		$this->reportGenerationStatusUpdate('importServices', 'import_impServIssue', 0);

		if (
			(!$operationTypes or $operationTypes['REFUNDED'] or $operationTypes['ISSUE']) and
			!$stdFilters['orderDate']['start'] and !$stdFilters['orderDate']['end'] and !$stdFilters['idServicePassengers'] and
			!$stdFilters['idServiceRespUsers'] and !$stdFilters['idServiceRespOffices'] and !$stdFilters['idOfferSuppliers'] and
			!$stdFilters['offerStartDate']['start'] and !$stdFilters['offerStartDate']['end'] and
			!$stdFilters['offerDestinationCityIds'] and !$stdFilters['offerDestinationCountryCodes'] and
			!$stdFilters['invoiceDate']['start'] and !$stdFilters['invoiceDate']['end'] and
			!$stdFilters['idInvoiceCorporates'] and !$stdFilters['idInvoiceCustomers'] and
			(!$stdFilters['serviceStatusFin'] or in_array('0', $stdFilters['serviceStatusFin'])) and
			(!$stdFilters['offerFormOfPayment'] or in_array('0', $stdFilters['offerFormOfPayment'])) and
			!$stdFilters['offerSupplierGroups'] and !$stdFilters['invoiceCorporateGroups']
		) { // if any of these filters are set - no need to load records from imported services

			$reportImportImpIss = new \App\Reports\Db\ManageRecords\Insert($reportTable);
			$reportImportImpIss->setMainTable($DBTRd_importedOrderServices, 'impServ') ;
			$reportImportImpIss->addJoinTable($DBTRd_importedOrderSystems, 'extSys', 'extSys.id = impServ.idSystem', 'LEFT') ;
			$reportImportImpIss->addJoinTable($DBTRd_importedOrders, 'impOrd', 'impServ.idImportedOrder = impOrd.id', 'LEFT') ;

			// ADD FILTERS ------------------------------------------------------------------------
			$reportImportImpIss->addFilter('impServ.systemStatus', '!=', 'deleted');
			$reportImportImpIss->addFilter('impServ.processStatus', '!=', 'processed');
			$reportImportImpIss->addFilter('impServ.serviceCode', 'IN', $CFG['reportsGeneration']['generation']['details']['ticketing']['includedServiceCodes']);
			// ticket date filter falls back to `offerExternalSystemReservationDate`
			$reportImportImpIss->addCustomFilter( '(impServ.ticketsDate BETWEEN ? AND ?) OR (impServ.ticketsDate IS NULL AND impServ.offerExternalSystemReservationDate BETWEEN ? AND ?)', [ $stdFilters['operationDate']['start'], $stdFilters['operationDate']['end'], $stdFilters['operationDate']['start'], $stdFilters['operationDate']['end'] ] );

			// ticket operation type
			if ($operationTypes) {
				if (!$operationTypes['ISSUE'] and !$operationTypes['REFUNDED']) { // no issue and no refund
					$reportImportImpIss->addCustomFilter('0');
				}
				if ($operationTypes['ISSUE'] and !$operationTypes['REFUNDED']) { // only issue but not refunded
					$reportImportImpIss->addFilter('impServ.systemStatus', '!=', 'refunded');
				}
				if (!$operationTypes['ISSUE'] and $operationTypes['REFUNDED']) { // only refunded but not issued
					$reportImportImpIss->addFilter('impServ.systemStatus', '=', 'refunded');
				}
			}
			// ext systems
			if (array_filter((array)$stdFilters['idImpOrdersSystems'])) { // no NULLs treating as all imported services have a system attached
				$reportImportImpIss->addFilter('impServ.idSystem', 'IN', array_filter($stdFilters['idImpOrdersSystems']));
			} elseif ($stdFilters['idImpOrdersSystems']==[0]) { // if only "MANUAL" was selected - skip this SQL
				$reportImportImpIss->addCustomFilter('0');
			}
			// imp serv users
			if ($stdFilters['idImpServBookUsers']) {
				$reportImportImpIss->addFilter('impServ.idBookUser', 'IN', $stdFilters['idImpServBookUsers']);
			}
			if ($stdFilters['idImpServBookOffices']) {
				$reportImportImpIss->addFilter('impServ.idBookOffice', 'IN', $stdFilters['idImpServBookOffices']);
			}
			if ($stdFilters['idImpServIssueUsers']) {
				$reportImportImpIss->addFilter('impServ.idIssueUser', 'IN', $stdFilters['idImpServIssueUsers']);
			}
			if ($stdFilters['idImpServIssueOffices']) {
				$reportImportImpIss->addFilter('impServ.idIssueOffice', 'IN', $stdFilters['idImpServIssueOffices']);
			}

			// ADD COLUMNS ------------------------------------------------------------------------
			// copy all default columns that start with impServ_
			$impServCols = [];
			foreach($commonColumnsBasic as $col=>$val) {
				if (strpos($col, 'impServ_')===0 || strpos($col, 'impOrder_')===0 ) { // report col name starts with impServ_ or impOrder_
					$impServCols[$col] = $val;
				}
			}
			$cols = $impServCols;

			// add other columns
			$cols['tkt_date']						= 'IF(impServ.ticketsDate IS NOT NULL, impServ.ticketsDate, impServ.offerExternalSystemReservationDate)';
			$cols['tkt_tktNumber']					= 'impServ.firstTicketNumber';
			$cols['tkt_imported']					= "'1'";
			$cols['impServ_processStatusCode']		= 'impServ.processStatus';
			$reportImportImpIss->setStructureColumns($cols);

			// add common function columns
			foreach($commonColumnsFunction as $structCol) {
				if (strpos($structCol['column'], 'impServ_')===0 || strpos($structCol['column'], 'impOrder_')===0 ) { // report col name starts with impServ_ or impOrder_
					$reportImportImpIss->addStructureColumn($structCol['column'], $structCol['value'], $structCol['table']);
				}
			}

			$reportImportImpIss->addStructureColumn("tkt_key", "CONCAT('I', impServ.id, 'I')", 'impServ');
			$reportImportImpIss->addStructureColumn("tkt_operation", "IF(impServ.systemStatus='refunded', 'REFUNDED', 'ISSUE')", 'impServ');
			$reportImportImpIss->addStructureColumn("tkt_voided", "IF((impServ.systemStatus='cancelled' OR impServ.systemStatus='deleted'), 1, 0)", 'impServ');
			$reportImportImpIss->addStructureColumn("tkt_dateYM", "IF(impServ.ticketsDate IS NOT NULL, DATE_FORMAT(impServ.ticketsDate, '%Y-%m-01'), DATE_FORMAT(impServ.offerExternalSystemReservationDate, '%Y-%m-01'))", 'impServ');
			$reportImportImpIss->addStructureColumn("tkt_dateY", "IF(impServ.ticketsDate IS NOT NULL, YEAR(impServ.ticketsDate), YEAR(impServ.offerExternalSystemReservationDate))", 'impServ');

			$reportImportImpIss->addStructureColumn("impServ_reissue", "IF(impServ.idImportedServiceReissued, 1, 0)", 'impServ');

			$reportImportImpIss->setActiveColumns($activeColumns);

			// POPULATE REPORT --------------------------------------------------------------------
			$reportImportImpIss->populate();

		}

		///////////////////////////////////////////////////////////////////////////////////////////
		// 4. ADD REFUNDS FROM IMPORTED SERVICES (NOT PROCESSED) //////////////////////////////////
		///////////////////////////////////////////////////////////////////////////////////////////

		// refresh report status
		$this->reportGenerationStatusUpdate('importServices', 'import_impServRefund', 0);

		if (
			(!$operationTypes or $operationTypes['REFUND']) and
			!$stdFilters['orderDate']['start'] and !$stdFilters['orderDate']['end'] and !$stdFilters['idServicePassengers'] and
			!$stdFilters['idServiceRespUsers'] and !$stdFilters['idServiceRespOffices'] and !$stdFilters['idOfferSuppliers'] and
			!$stdFilters['offerStartDate']['start'] and !$stdFilters['offerStartDate']['end'] and
			!$stdFilters['offerDestinationCityIds'] and !$stdFilters['offerDestinationCountryCodes'] and
			!$stdFilters['invoiceDate']['start'] and !$stdFilters['invoiceDate']['end'] and
			!$stdFilters['idInvoiceCorporates'] and !$stdFilters['idInvoiceCustomers'] and
			(!$stdFilters['serviceStatusFin'] or in_array('0', $stdFilters['serviceStatusFin'])) and
			(!$stdFilters['offerFormOfPayment'] or in_array('0', $stdFilters['offerFormOfPayment'])) and
			!$stdFilters['offerSupplierGroups'] and !$stdFilters['invoiceCorporateGroups']
		) { // if any of these filters are set - no need to load records from imported services

			$reportImportImpRfnd = new \App\Reports\Db\ManageRecords\Insert($reportTable);
			$reportImportImpRfnd->setMainTable($DBTRd_importedOrderServices, 'impServ') ;
			$reportImportImpRfnd->addJoinTable($DBTRd_importedOrderSystems, 'extSys', 'extSys.id = impServ.idSystem', 'LEFT') ;
			$reportImportImpRfnd->addJoinTable($DBTRd_importedOrders, 'impOrd', 'impServ.idImportedOrder = impOrd.id', 'LEFT') ;

			// ADD FILTERS ------------------------------------------------------------------------
			$reportImportImpRfnd->addFilter('impServ.systemStatus', '=', 'refunded');
			$reportImportImpRfnd->addFilter('impServ.processStatus', '!=', 'processed');
			$reportImportImpRfnd->addFilter('impServ.cancelDate', 'BETWEEN', [$stdFilters['operationDate']['start'], $stdFilters['operationDate']['end']]);
			$reportImportImpRfnd->addFilter('impServ.serviceCode', 'IN', $CFG['reportsGeneration']['generation']['details']['ticketing']['includedServiceCodes']);
			// ext systems
			if (array_filter((array)$stdFilters['idImpOrdersSystems'])) { // no NULLs treating as all imported services have a system attached
				$reportImportImpRfnd->addFilter('impServ.idSystem', 'IN', array_filter($stdFilters['idImpOrdersSystems']));
			} elseif ($stdFilters['idImpOrdersSystems']==[0]) { // if only "MANUAL" was selected - skip this SQL
				$reportImportImpRfnd->addCustomFilter('0');
			}
			// imp serv users
			if ($stdFilters['idImpServBookUsers']) {
				$reportImportImpRfnd->addFilter('impServ.idBookUser', 'IN', $stdFilters['idImpServBookUsers']);
			}
			if ($stdFilters['idImpServBookOffices']) {
				$reportImportImpRfnd->addFilter('impServ.idBookOffice', 'IN', $stdFilters['idImpServBookOffices']);
			}
			if ($stdFilters['idImpServIssueUsers']) {
				$reportImportImpRfnd->addFilter('impServ.idIssueUser', 'IN', $stdFilters['idImpServIssueUsers']);
			}
			if ($stdFilters['idImpServIssueOffices']) {
				$reportImportImpRfnd->addFilter('impServ.idIssueOffice', 'IN', $stdFilters['idImpServIssueOffices']);
			}

			// ADD COLUMNS ------------------------------------------------------------------------
			// copy all default columns that start with impServ_
			$impServCols = [];
			foreach($commonColumnsBasic as $col=>$val) {
				if (strpos($col, 'impServ_')===0 || strpos($col, 'impOrder_')===0 ) { // report col name starts with impServ_ or impOrder_
					$impServCols[$col] = $val;
				}
			}
			$cols = $impServCols;

			// add other columns
			$cols['tkt_date']						= 'impServ.cancelDate';
			$cols['tkt_operation']					= "'REFUND'";
			$cols['tkt_imported']					= "'1'";
			$cols['tkt_voided']						= "'0'";
			$cols['impServ_processStatusCode']		= 'impServ.processStatus';
			$reportImportImpRfnd->setStructureColumns($cols);

			// add common function columns
			foreach($commonColumnsFunction as $structCol) {
				if (strpos($structCol['column'], 'impServ_')===0 || strpos($structCol['column'], 'impOrder_')===0 ) { // report col name starts with impServ_ or impOrder_
					$reportImportImpRfnd->addStructureColumn($structCol['column'], $structCol['value'], $structCol['table']);
				}
			}

			$reportImportImpRfnd->addStructureColumn("tkt_tktNumber", "IF(impServ.firstTicketNumber, CONCAT(impServ.firstTicketNumber, 'R'), '')", 'impServ');
			$reportImportImpRfnd->addStructureColumn("tkt_key", "CONCAT('I', impServ.id, 'R')", 'impServ');
			$reportImportImpRfnd->addStructureColumn("tkt_dateYM", "DATE_FORMAT(impServ.cancelDate, '%Y-%m-01')", 'impServ');
			$reportImportImpRfnd->addStructureColumn("tkt_dateY", "YEAR(impServ.cancelDate)", 'impServ');

			$reportImportImpRfnd->addStructureColumn("impServ_reissue", "IF(impServ.idImportedServiceReissued, 1, 0)", 'impServ');

			$reportImportImpRfnd->setActiveColumns($activeColumns);

			// POPULATE REPORT --------------------------------------------------------------------
			$reportImportImpRfnd->populate();

		}

	}

}
