<?php

namespace App\Reports\Sources\OrderServices;


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;
		global $DBTRd_users, $DBTRd_offices, $DBTRd_customers, $DBTRd_corporates, $DBTRd_corporateOffices, $DBTRd_suppliers, $DBTRd_supplierProducts;

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

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

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


		// CREATE SQL FOR COPYING INVOICES ////////////////////////////////////////////////////////

		// refresh report status
		$this->reportGenerationStatusUpdate('extractMainDataFromDb', 'runningMainSQL', 0);

		$reportCopyInfo = new \App\Reports\Db\ManageRecords\Insert($reportTable);
		$reportCopyInfo->setMainTable($DBTRd_services,           'serv');
		$reportCopyInfo->addJoinTable($DBTRd_supplierProducts,   'servSuppProd',	'serv.idSupplierProduct=servSuppProd.id',		'LEFT');
		$reportCopyInfo->addJoinTable($DBTRd_customers,          'servFirstCust',	'serv.idFirstCustomer=servFirstCust.id',		'LEFT');
		$reportCopyInfo->addJoinTable($DBTRd_services,           'package',			'serv.idPackageService=package.id',				'LEFT');
		$reportCopyInfo->addJoinTable($DBTRd_importedOrderSystems,'impOrdSyst',		'serv.idExternalSystem=impOrdSyst.id',			'LEFT');
		$reportCopyInfo->addJoinTable($DBTRd_users,              'servRespUser', 	'serv.idUser=servRespUser.id');
		$reportCopyInfo->addJoinTable($DBTRd_offices,            'servRespOffice', 'serv.idOffice=servRespOffice.id');

		$reportCopyInfo->addJoinTable($DBTRd_orders,             'ord',				'serv.idOrder=ord.id', 							'LEFT');
		$reportCopyInfo->addJoinTable($DBTRd_customers,          'ownCust',			'ord.idCustomerOwner=ownCust.id',				'LEFT');
		$reportCopyInfo->addJoinTable($DBTRd_corporates,         'ownCorp',			'ord.idCorporateOwner=ownCorp.id',				'LEFT');
		$reportCopyInfo->addJoinTable($DBTRd_corporateOffices,   'ownCorpOffice',	'ord.idCorporateOwnerOffice=ownCorpOffice.id',	'LEFT');

		$reportCopyInfo->addJoinTable($DBTRd_offers,             'off',				'serv.id=off.idService',						'LEFT');
		$reportCopyInfo->addJoinTable($DBTRd_suppliers,          'offSupp',			'off.idSupplier=offSupp.id',					'LEFT');
		$reportCopyInfo->addJoinTable($DBTRd_supplierProducts,   'offSuppProd',		'off.idSupplierProduct=offSuppProd.id',			'LEFT');

		$reportCopyInfo->addJoinTable($DBTRd_clientInvoices,     'inv',				'serv.idRelevantInvoice=inv.id',				'LEFT');
		$reportCopyInfo->addJoinTable($DBTRd_corporates,         'invCorp',			'inv.idCorporate=invCorp.id',					'LEFT');
		$reportCopyInfo->addJoinTable($DBTRd_corporateOffices,   'invCorpOffices',	'inv.idCorporateOffice=invCorpOffices.id',		'LEFT');
		$reportCopyInfo->addJoinTable($DBTRd_customers,          'invCust',			'inv.idCustomer=invCust.id',					'LEFT');

		$reportCopyInfo->addJoinTable($DBTRd_balances,           'bal',				'serv.idBalance=bal.id',						'LEFT');

		$cols = [
			'serv_id' => 					   		'serv.id',
			'serv_linkedToIdService' =>        		'serv.linkedToIdService',
			'serv_idPackageService' =>         		'serv.idPackageService',
			'serv_packageOfferMode' =>          	'serv.packageOfferMode',
			'serv_packageServFinStatusCode' => 		'package.statusFin',
			'serv_serviceCode' =>             		'serv.serviceCode',
			'serv_serviceName' =>              		'serv.serviceName',
			'serv_category' =>						'serv.category',
			'serv_productType' =>					'serv.productType',
			'serv_multiplier' =>					'serv.multiplier',
			'serv_multiplierTotal' =>				'serv.multiplierTotal',
			'serv_source' =>						'serv.serviceSource',
			'serv_productName' =>        	   		'serv.productName',
			'serv_suppProductId' =>            		'serv.idSupplierProduct',
			'serv_suppProductName' =>       		'servSuppProd.productName',
			'serv_suppProductChain' =>      		'servSuppProd.chainCode',
			'serv_description' =>           		'serv.description',
			'serv_preferences' =>           		'serv.preferences',
			'serv_comments' =>              		'serv.comments',
			'serv_remarks' =>               		'serv.remarks',
			'serv_processedRemarks' =>      		'serv.processedRemarks',
			'serv_firstTicketNo' =>       			'serv.firstTicketNumber',
			'serv_ticketNumberList' =>       		'serv.passengersTicketsList',
			'serv_ticketDate' =>       				'serv.passengersTicketsDate',
			'serv_statusOpCode' =>             	 	'serv.statusOp',
			'serv_statusFinCode' =>            		'serv.statusFin',
			'serv_estInvoiceDate' =>        		'serv.estInvoiceDate',
			'serv_estCashDate' =>           		'serv.estCashDate',
			'serv_cancelReasonCode' =>          	'serv.cancelReason',
			'serv_cancelReasonDetails' =>   		'serv.cancelReasonDetails',
			'serv_refunded' =>             			'serv.refunded',
			'serv_startDate' =>             		'serv.startDate',
			'serv_endDate' =>               		'serv.endDate',
			'serv_duration' =>						'serv.duration',
			'serv_firstCustomerId' =>       		'serv.idFirstCustomer',
			'serv_firstCustomerProcessedRemarks' => 'servFirstCust.processedRemarks',

			'serv_pax' =>                   		'serv.pax',
			'serv_paxDetails' => 					'serv.paxDetails',
			'serv_agencyCostCenterCode' => 			'serv.agencyCostCenter',
			'serv_autoProcessRuleId' =>				'serv.idAutoProcessRule',
			'serv_genDocsAssoc' =>					'serv.genDocsAssoc',
			'serv_addTime' =>               		'serv.addTime',
			'serv_lastUpdateTime' =>      	 	 	'serv.lastUpdateTime',
			'serv_updatesCount' =>         		 	'serv.updatesCount',

			'serv_userId' =>                		'serv.idUser',
			'serv_userOfficeId' =>         			'serv.idOffice',
			'serv_officeProcessedRemarks' =>        'servRespOffice.processedRemarks',
			'serv_userDeptId' => 	        		'servRespUser.idDepartment',
			'serv_userProcessedRemarks' =>  		'servRespUser.processedRemarks',
			'serv_addUserId' =>             		'serv.idAddUser',
			'serv_updateUserId' =>          		'serv.idUpdateUser',

			'impServ_id' =>							'serv.idImportedService',
			'impServ_externalSystemId' =>			'serv.idExternalSystem',
			'impServ_externalSystemName' =>			'impOrdSyst.name',

			'ord_id' =>                            	'serv.idOrder',
			'ord_convertedId' =>                   	'ord.convertedId',
			'ord_code' =>                          	'ord.orderCode',
			'ord_number' =>                        	'ord.orderNumber',
			'ord_typeCode' =>                      	'ord.orderType',
			'ord_flagCode' =>                      	'ord.flag',
			'ord_servicesTypeCode' =>              	'ord.servicesType',
			'ord_date' =>                          	'ord.date',
			'ord_startDate' =>                     	'ord.startDate',
			'ord_endDate' =>                       	'ord.endDate',
			'ord_customerOwnerId' =>               	'ord.idCustomerOwner',
			'ord_customerOwnerProcessedRemarks' => 	'ownCust.processedRemarks',
			'ord_pax' =>                           	'ord.pax',
			'ord_corporateOwnerId' =>              	'ord.idCorporateOwner',
			'ord_corporateOwnerName' =>            	'ownCorp.alias',
			'ord_corporateRegNo' =>                	'ownCorp.regNo',
			'ord_corporateFiscalCode' =>           	'ownCorp.fiscalCode',
			'ord_corporateVatCertificateNumber' => 	'ownCorp.vatCertificateNumber',
			'ord_corporateProcessedRemarks' =>     	'ownCorp.processedRemarks',
			'ord_corporateOwnerOfficeId' =>        	'ord.idCorporateOwnerOffice',
			'ord_corporateOwnerOfficeName' =>      	'ownCorpOffice.name',
			'ord_contactPersonId' =>               	'ord.idContactPerson',
			'ord_description' =>                   	'ord.description',
			'ord_comments' =>                      	'ord.comments',
			'ord_notes' =>							'ord.notes',
			'ord_statusOpCode' =>                  	'ord.statusOp',
			'ord_statusFinCode' =>                 	'ord.statusFin',
			'ord_miceProjectId' =>                 	'ord.idMiceProject',
			'ord_genDocsAssoc' =>					'ord.genDocsAssoc',
			'ord_addTime' =>                       	'ord.addTime',
			'ord_lastUpdateTime' =>                	'ord.lastUpdateTime',
			'ord_updatesCount' =>                  	'ord.updatesCount',

			'ord_userId' =>                        	'ord.idUser',
			'ord_deptId' =>                        	'ord.idDepartment',
			'ord_officeId' =>                      	'ord.idOffice',
			'ord_addUserId' =>                     	'ord.idAddUser',
			'ord_updateUserId' =>                  	'ord.idUpdateUser',

			'off_id' =>                  			'off.id',
			'off_typeCode' =>                		'off.offerType',
			'off_serviceCode' =>         			'off.serviceCode',
			'off_locationCode' =>        			'off.location',
			'off_calculationTypeCode' => 			'off.calculationType',
			'off_suppId' =>         	 			'off.idSupplier',
			'off_suppAlias' =>       	 			'offSupp.alias',
			'off_suppIataCode' =>   	 			'offSupp.iataCode',
			'off_suppTktCode' =>    	 			'offSupp.tktCode',
			'off_supplierProcessedRemarks' => 		'offSupp.processedRemarks',
			'off_suppProductId' =>       			'off.idSupplierProduct',
			'off_suppProductName' =>     			'offSuppProd.productName',
			'off_productName' =>     	 			'off.productName',
			'off_duration' =>            			'off.duration',
			'off_externalOrderId' =>     			'off.externalSystemReservationId',
			'off_externalOrderDate' =>   			'off.externalSystemReservationDate',
			'off_confirmationNo' =>      			'off.confirmationNumber',
			'off_expirationDate' =>      			'off.expirationDate',
			'off_optionDate' =>          			'off.optionDate',
			'off_comments' =>            			'off.comments',
			'off_cancellationPolicy' =>				'off.cancellationPolicy',
			'off_addTime' =>             			'off.addTime',
			'off_lastUpdateTime' =>      			'off.lastUpdateTime',
			'off_addUserId' =>          			'off.idAddUser',

			'off_deptCountryCode' =>    	 		'off.departureCountryCode',
			'off_deptCityId' =>    		     		'off.departureCityId',
			'off_deptStationCode' =>         		'off.departureStationCode',
			'off_destCountryCode' =>  	     		'off.destinationCountryCode',
			'off_destCityId' =>    			 		'off.destinationCityId',
			'off_destStationCode' =>    	 		'off.destinationStationCode',
			'off_itinerary' =>						'off.itinerary',
			'off_itineraryReadable' =>				'off.itineraryReadable',
			'off_itineraryFirstClassCode' => 		'off.serviceClassCodeFirst',
			'off_itineraryFirstClassName' => 		'off.serviceClassNameFirst',

			'off_commissionable' =>     			'off.commissionable',
			'off_currency' =>						'off.currency',
			'off_currencyTypeCode' =>   			'off.currencyType',
			'off_currencyDate' =>       			'off.currencyDate',
			'off_currencyRate' =>    				'off.currencyRate',
			'off_workCurrencyRate' =>   			'off.workCurrencyRate',
			'off_ignSuppOnDocs' =>      			'off.ignoreSupplierOnDocuments',
			'off_supplierValue' =>  				'off.supplierValue',
			'off_supplierVat' =>     				'off.supplierVat',
			'off_commissionValue' => 				'off.commissionValue',
			'off_commissionVat' =>   				'off.commissionVat',
			'off_discountValue' =>   				'off.discountValue',
			'off_discountVat' =>     				'off.discountVat',
			'off_serviceFeeValue' => 				'off.serviceFeeValue',
			'off_serviceFeeVat' =>   				'off.serviceFeeVat',
			'off_taxValue' =>        				'off.taxValue',
			'off_taxVat' =>          				'off.taxVat',
			'off_total' => 							'off.offerTotal',
			'off_netCompanyTotal' => 				'off.netCompanyTotal',

		    'off_formOfPayment' =>                  'off.formOfPayment',
		    'off_paymentCcMaskedNo' =>              'off.paymentCcMaskedNo',
		    'off_paymentCcExpireDate' =>            'off.paymentCcExpireDate',
		    'off_paymentCcCompany' =>               'off.paymentCcCompany',
		    'off_paymentCcApprovalCode' =>          'off.paymentCcApprovalCode',
		    'off_paymentCcValue' =>                 'off.paymentCcValue',
		    'off_paymentCcCurrency' =>              'off.paymentCcCurrency',
		    'off_formOfCashing' =>                  'off.formOfCashing',
		    'off_cashingCcMaskedNo' =>              'off.cashingCcMaskedNo',
		    'off_cashingCcExpireDate' =>            'off.cashingCcExpireDate',
		    'off_cashingCcCompany' =>               'off.cashingCcCompany',
		    'off_cashingCcApprovalCode' =>          'off.cashingCcApprovalCode',

			'inv_id' =>                   			'inv.id',
			'inv_number' =>             			'inv.invoiceNumber',
			'inv_date' =>          				    'inv.invoiceDate',
			'inv_seriesId' =>      				    'inv.idInvoiceSeries',
			'inv_dueDate' => 						'inv.dueDate',
			'inv_formOfPayment' => 					'inv.formOfPayment',
			'inv_temporary' => 						'inv.temporary',
			'inv_cumulative' => 	    			'inv.cumulativeInvoice',
			'inv_noOfCreditNoteInv' =>    			'serv.reversedService',
			'inv_processedRemarks' => 				'inv.processedRemarks',
			'inv_genDocsAssoc' =>					'inv.genDocsAssoc',
			'inv_autoProcessRuleId' =>				'inv.idAutoProcessRule',
			'inv_addTime' =>            			'inv.addTime',
			'inv_issueTime' =>          			'inv.issueTime',

			'inv_currency' =>             			'serv.invoiceCurrency',
			'inv_currencyExchRate' =>  	  			'serv.invoiceCurrencyRate',
			'inv_workCurrencyExchRate' => 			'serv.invoiceWorkCurrencyRate',
			'inv_base' =>				  			'serv.invoiceValue',
			'inv_vat' =>				  			'serv.invoiceVat',
			'inv_total' =>				  			'serv.invoiceTotal',
			'inv_servicePriceValue' =>	  			'serv.invoiceServicePriceValue',
			'inv_servicePriceVat' =>	  			'serv.invoiceServicePriceVat',
			'inv_commissionValue' =>	  			'serv.invoiceCommissionValue',
			'inv_commissionVat' =>		  			'serv.invoiceCommissionVat',
			'inv_discountValue' =>		  			'serv.invoiceDiscountValue',
			'inv_discountVat' =>		  			'serv.invoiceDiscountVat',
			'inv_serviceFeeValue' =>	  			'serv.invoiceServiceFeeValue',
			'inv_serviceFeeVat' =>		  			'serv.invoiceServiceFeeVat',
			'inv_taxValue' =>			  			'serv.invoiceTaxValue',
			'inv_taxVat' =>				  			'serv.invoiceTaxVat',
			'inv_advanceValue' =>		  			'serv.invoiceAdvanceValue',
			'inv_advanceVat' =>			  			'serv.invoiceAdvanceVat',
			'inv_penaltyValue' =>		  			'serv.invoicePenaltyValue',
			'inv_penaltyVat' =>			  			'serv.invoicePenaltyVat',
			'inv_invoiceIssueTotal' =>	  			'serv.invoiceIssueTotal',
			'inv_invoiceRefundTotal' =>	  			'serv.invoiceRefundTotal',

			'inv_corporateSelOfficeId' =>			'inv.idCorporateOffice',
			'inv_corporateSelOfficeName' => 		'invCorpOffices.name',
			'inv_corporateSelOfficeCountryCode' =>	'invCorpOffices.countryCode',
			'inv_corporateSelOfficeCityId' => 		'invCorpOffices.cityId',
			'inv_corporateDomainId' =>				'invCorp.idDomain',
			'inv_corporateDomainCategoryId' =>		'invCorp.idDomainCategory',
			'inv_corporateOriginalId' => 			'inv.idCorporate',
			'inv_customerOriginalId' => 			'inv.idCustomer',

			'inv_cashedPercent' =>	  	  			'serv.invoiceCashedPercent',
			'inv_cashedStatus' =>	      			'serv.invoiceCashedStatus',
			'inv_cashedValue' =>	      			'serv.invoiceCashedValue',
			'inv_notCashedValue' =>	      			'serv.invoiceNotCashedValue',
			'inv_issueCashedValue' =>	  			'serv.invoiceIssueCashedValue',
			'inv_issueNotCashedValue' =>  			'serv.invoiceIssueNotCashedValue',
			'inv_refundCashedValue' =>	  			'serv.invoiceRefundCashedValue',
			'inv_refundNotCashedValue' => 			'serv.invoiceRefundNotCashedValue',
			'inv_lastCashingDate' =>				'inv.lastCashingDate',

			'inv_respUserId' =>         			'inv.idUser',
			'inv_respOfficeId' =>					'inv.idOffice',
			'inv_addUserId' =>          			'inv.idAddUser',
			'inv_issueUserId' =>        			'inv.idIssueUser',

			'bal_id' => 							'serv.idBalance',
			'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_profitDefCurr' =>					'serv.balanceProfitDefCurr',
			'bal_issueProfitDefCurr' =>				'serv.balanceIssueProfitDefCurr',
			'bal_refundProfitDefCurr' =>			'serv.balanceRefundProfitDefCurr',

			'bal_respUserId' =>						'bal.idUser',
			'bal_respOfficeId' =>					'bal.idOffice',
			'bal_addUserId' =>						'bal.idAddUser',
			'bal_confUserId' =>						'bal.idConfirmUser',
			'bal_confirmTime' =>					'bal.confirmTime',
			'bal_addTime' =>						'bal.addTime',
			'balRfnd_Id' => 						'serv.idBalanceRefund',
		];

		$reportCopyInfo->setStructureColumns($cols);
		$reportCopyInfo->addStructureColumn("serv_isPackageSubservice",     "IF(serv.idPackageService, 1,0)", "serv");
		$reportCopyInfo->addStructureColumn("serv_balanced", 	            "IF(serv.idBalance, 1,0)", "serv");
		$reportCopyInfo->addStructureColumn("serv_startDateYM", 			"DATE_FORMAT(serv.startDate, '%Y-%m-01')", "serv");
		$reportCopyInfo->addStructureColumn("serv_startDateY", 			    "YEAR(serv.startDate)", "serv");
		$reportCopyInfo->addStructureColumn("serv_endDateYM", 			    "DATE_FORMAT(serv.endDate, '%Y-%m-01')", "serv");
		$reportCopyInfo->addStructureColumn("serv_endDateY", 			    "YEAR(serv.endDate)", "serv");
		$reportCopyInfo->addStructureColumn("serv_firstCustomerName", 		"CONCAT(servFirstCust.lastNameStd, ' ', servFirstCust.firstNameStd)", "servFirstCust");
		$reportCopyInfo->addStructureColumn("serv_packageServBalStatus",    "IF(package.idBalance, 1,0)", "package");
		$reportCopyInfo->addStructureColumn("serv_addDate", 				"DATE(serv.addTime)", "serv");

		$reportCopyInfo->addStructureColumn("ord_dateYM", 					"DATE_FORMAT(ord.date, '%Y-%m-01')", "ord");
		$reportCopyInfo->addStructureColumn("ord_dateY", 			   		"YEAR(ord.date)", "ord");
		$reportCopyInfo->addStructureColumn("ord_startDateYM", 				"DATE_FORMAT(ord.startDate, '%Y-%m-01')", "ord");
		$reportCopyInfo->addStructureColumn("ord_startDateY", 			   	"YEAR(ord.startDate)", "ord");
		$reportCopyInfo->addStructureColumn("ord_endDateYM", 				"DATE_FORMAT(ord.endDate, '%Y-%m-01')", "ord");
		$reportCopyInfo->addStructureColumn("ord_endDateY", 			   	"YEAR(ord.endDate)", "ord");
		$reportCopyInfo->addStructureColumn("ord_customerOwnerName", 		"CONCAT(ownCust.lastNameStd, ' ', ownCust.firstNameStd)", "ownCust");

		$reportCopyInfo->addStructureColumn("off_startDate", 				"DATE_FORMAT(off.startDatetime, '%Y-%m-%d')", "off");
		$reportCopyInfo->addStructureColumn("off_startDateYM", 				"DATE_FORMAT(off.startDatetime, '%Y-%m-01')", "off");
		$reportCopyInfo->addStructureColumn("off_startDateY", 			    "YEAR(off.startDatetime)", "off");
		$reportCopyInfo->addStructureColumn("off_endDate", 				    "DATE_FORMAT(off.endDatetime, '%Y-%m-%d')", "off");
		$reportCopyInfo->addStructureColumn("off_endDateYM", 				"DATE_FORMAT(off.endDatetime, '%Y-%m-01')", "off");
		$reportCopyInfo->addStructureColumn("off_endDateY", 			   	"YEAR(off.endDatetime)", "off");
		$reportCopyInfo->addStructureColumn("off_addDate", 					"DATE(off.addTime)", "off");

		$reportCopyInfo->addStructureColumn("off_supplierTotal", 	    	"off.supplierValue+off.supplierVat", "off");
		$reportCopyInfo->addStructureColumn("off_commissionTotal", 	  	  	"off.commissionValue+off.commissionVat", "off");
		$reportCopyInfo->addStructureColumn("off_discountTotal", 	    	"off.discountValue+off.discountVat", "off");
		$reportCopyInfo->addStructureColumn("off_serviceFeeTotal", 	    	"off.serviceFeeValue+off.serviceFeeVat", "off");
		$reportCopyInfo->addStructureColumn("off_taxTotal", 	    		"off.taxValue+off.taxVat", "off");

		$reportCopyInfo->addStructureColumn("off_totalDefCurr", 			"off.offerTotal*off.currencyRate", "off");
		$reportCopyInfo->addStructureColumn("off_totalWorkCurr", 			"off.offerTotal*off.currencyRate/off.workCurrencyRate", "off");
		$reportCopyInfo->addStructureColumn("off_netCompanyTotalDefCurr", 	"off.netCompanyTotal*off.currencyRate", "off");
		$reportCopyInfo->addStructureColumn("off_netCompanyTotalWorkCurr", 	"off.netCompanyTotal*off.currencyRate/off.workCurrencyRate", "off");
		$reportCopyInfo->addStructureColumn("off_estProfitDefCurr", 	    "IF(off.ignoreSupplierOnDocuments, ((off.serviceFeeValue+off.serviceFeeVat-off.discountValue-off.discountVat)*off.currencyRate), ((off.offerTotal-off.netCompanyTotal)*off.currencyRate))", "off");
		$reportCopyInfo->addStructureColumn("off_estProfitWorkCurr", 	    "IF(off.ignoreSupplierOnDocuments, ((off.serviceFeeValue+off.serviceFeeVat-off.discountValue-off.discountVat)*off.currencyRate/off.workCurrencyRate), ((off.offerTotal-off.netCompanyTotal)*off.currencyRate/off.workCurrencyRate))", "off");
		$reportCopyInfo->addStructureColumn("off_advanceBookingDays", 		"IF(off.externalSystemReservationDate, DATEDIFF(off.startDatetime, off.externalSystemReservationDate), DATEDIFF(off.startDatetime, FROM_UNIXTIME(serv.addTime)))", "off");

		$reportCopyInfo->addStructureColumn("inv_servicePriceTotal", 	    "serv.invoiceServicePriceValue+serv.invoiceServicePriceVat", "serv");
		$reportCopyInfo->addStructureColumn("inv_commissionTotal", 	  	  	"serv.invoiceCommissionValue+serv.invoiceCommissionVat", "serv");
		$reportCopyInfo->addStructureColumn("inv_discountTotal", 	    	"serv.invoiceDiscountValue+serv.invoiceDiscountVat", "serv");
		$reportCopyInfo->addStructureColumn("inv_serviceFeeTotal", 	    	"serv.invoiceServiceFeeValue+serv.invoiceServiceFeeVat", "serv");
		$reportCopyInfo->addStructureColumn("inv_taxTotal", 	    		"serv.invoiceTaxValue+serv.invoiceTaxVat", "serv");
		$reportCopyInfo->addStructureColumn("inv_advanceTotal", 	    	"serv.invoiceAdvanceValue+serv.invoiceAdvanceVat", "serv");
		$reportCopyInfo->addStructureColumn("inv_penaltyTotal", 	    	"serv.invoicePenaltyValue+serv.invoicePenaltyVat", "serv");
		$reportCopyInfo->addStructureColumn("inv_totalDefCurr", 	    	"serv.invoiceTotal*serv.invoiceCurrencyRate", "serv");
		$reportCopyInfo->addStructureColumn("inv_totalWorkCurr", 	    	"serv.invoiceTotal*serv.invoiceCurrencyRate/serv.invoiceWorkCurrencyRate", "serv");
		$reportCopyInfo->addStructureColumn("inv_netCompanyTotalDefCurr", 	"(serv.invoiceServicePriceValue+serv.invoiceServicePriceVat-serv.invoiceCommissionValue-serv.invoiceCommissionVat+serv.invoiceTaxValue+serv.invoiceTaxVat+serv.invoiceAdvanceValue+serv.invoiceAdvanceVat+serv.invoicePenaltyValue+serv.invoicePenaltyVat)*serv.invoiceCurrencyRate", "serv");
		$reportCopyInfo->addStructureColumn("inv_netCompanyTotalWorkCurr", 	"(serv.invoiceServicePriceValue+serv.invoiceServicePriceVat-serv.invoiceCommissionValue-serv.invoiceCommissionVat+serv.invoiceTaxValue+serv.invoiceTaxVat+serv.invoiceAdvanceValue+serv.invoiceAdvanceVat+serv.invoicePenaltyValue+serv.invoicePenaltyVat)*serv.invoiceCurrencyRate/serv.invoiceWorkCurrencyRate", "serv");

		$reportCopyInfo->addStructureColumn("inv_dateYM", 			        "DATE_FORMAT(inv.invoiceDate, '%Y-%m-01')", "inv");
		$reportCopyInfo->addStructureColumn("inv_dateY", 	                "YEAR(inv.invoiceDate)", "inv");
		$reportCopyInfo->addStructureColumn("inv_addDate", 					"DATE(inv.addTime)", "inv");

		$reportCopyInfo->addStructureColumn("inv_corporateId",				"IF(invCorp.idRealCorporate, invCorp.idRealCorporate, inv.idCorporate)", "invCorp");
		$reportCopyInfo->addStructureColumn("inv_customerId",				"IF(invCust.idRealCustomer, invCust.idRealCustomer, inv.idCustomer)", "invCust");
		$reportCopyInfo->addStructureColumn("inv_corporateDuplicated", 		"IF(invCorp.idRealCorporate, 1, 0)", "invCorp");
		$reportCopyInfo->addStructureColumn("inv_customerDuplicated", 		"IF(invCust.idRealCustomer, 1, 0)", "invCust");

		$reportCopyInfo->addStructureColumn("inv_exceededDueDays", 			"IF((inv.cashedStatus = 't' OR inv.cashedStatus = 'o'), DATEDIFF(inv.lastCashingDate, IF(inv.dueDate, inv.dueDate, inv.invoiceDate)), DATEDIFF(NOW(), IF(inv.dueDate, inv.dueDate, inv.invoiceDate)))", "inv");

		$reportCopyInfo->addStructureColumn("bal_addDate", 					"DATE(bal.addTime)", "bal");
		$reportCopyInfo->addStructureColumn("bal_confirmDate",				"DATE(bal.confirmTime)", "bal");
		$reportCopyInfo->addStructureColumn("bal_confirmed", 				"IF(bal.confirmTime IS NULL, 0, 1)", "bal");

		$reportCopyInfo->setActiveColumns($activeColumns);

		// ADD FILTERS ////////////////////////////////////////////////////////////////////////////

		// ORDER FILTERS --------------------------------------------------------------------------
		if ($stdFilters['orderDate']['start']) {
			$reportCopyInfo->addFilter('ord.date', '>=', $stdFilters['orderDate']['start']);
		}
		if ($stdFilters['orderDate']['end']) {
			$reportCopyInfo->addFilter('ord.date', '<=',$stdFilters['orderDate']['end']);
		}

		if ($stdFilters['idOrderRespUsers']) {
			$reportCopyInfo->addFilter('ord.idUser' , 'IN', $stdFilters['idOrderRespUsers']);
		}
		if ($stdFilters['idOrderRespOffices']) {
			$reportCopyInfo->addFilter('ord.idOffice' , 'IN', $stdFilters['idOrderRespOffices']);
		}
		if ($stdFilters['idOrderRespDepartments']) {
			$reportCopyInfo->addFilter('ord.idDepartment' , 'IN', $stdFilters['idOrderRespDepartments']);
		}

		// SERVICE FILTERS ------------------------------------------------------------------------
		if ($stdFilters['addTime']['start']) {
			$reportCopyInfo->addFilter('serv.addTime', '>=', $stdFilters['addTime']['start'].' 00:00:00');
		}
		if ($stdFilters['addTime']['end']) {
			$reportCopyInfo->addFilter('serv.addTime', '<=', $stdFilters['addTime']['end'].' 23:59:59');
		}

		if ($stdFilters['idServiceRespUsers']) {
			$reportCopyInfo->addFilter('serv.idUser' , 'IN', $stdFilters['idServiceRespUsers']);
		}
		if ($stdFilters['idServiceRespOffices']) {
			$reportCopyInfo->addFilter('serv.idOffice' , 'IN', $stdFilters['idServiceRespOffices']);
		}
		if ($stdFilters['idServiceRespDepartments']) {
			$reportCopyInfo->addFilter('servRespUser.idDepartment' , 'IN', $stdFilters['idServiceRespDepartments']);
		}

		if ($stdFilters['serviceType']) {
			$reportCopyInfo->addFilter('serv.serviceCode' , 'IN', $stdFilters['serviceType']);
		}

		if ($stdFilters['serviceStatusOp'] and count($stdFilters['serviceStatusOp']) < 5) {
			$reportCopyInfo->addFilter('serv.statusOp' , 'IN', $stdFilters['serviceStatusOp']);
		}
		if ($stdFilters['serviceStatusFin'] and count($stdFilters['serviceStatusFin']) < 3) {
			$reportCopyInfo->addFilter('serv.statusFin' , 'IN', $stdFilters['serviceStatusFin']);
		}

		// OFFERS FILTERS -------------------------------------------------------------------------
		if ($stdFilters['offerServiceType']) {
			$reportCopyInfo->addFilter('off.serviceCode' , 'IN', $stdFilters['offerServiceType']);
		}

		if ($stdFilters['offerStartDate']['start']) {
			$reportCopyInfo->addFilter('off.startDatetime', '>=', $stdFilters['offerStartDate']['start'].' 00:00:00');
		}
		if ($stdFilters['offerStartDate']['end']) {
			$reportCopyInfo->addFilter('off.startDatetime', '<=', $stdFilters['offerStartDate']['end'].' 23:59:59');
		}

		if ($stdFilters['offerEndDate']['start']) {
			$reportCopyInfo->addFilter('off.endDatetime', '>=', $stdFilters['offerEndDate']['start']);
		}
		if ($stdFilters['offerEndDate']['end']) {
			$reportCopyInfo->addFilter('off.endDatetime', '<=',$stdFilters['offerEndDate']['end']);
		}

		if ($stdFilters['idOfferSuppliers']) {
			$reportCopyInfo->addFilter('off.idSupplier' , 'IN', $stdFilters['idOfferSuppliers']);
		}

		if ($stdFilters['offerSupplierGroups']) {
			$supplierGroupsHelper = new \App\ReportsData\Helpers\PartnerGroups\Supplier();
			$supplierGroupsHelper->setGroupElementsLinear($stdFilters['offerSupplierGroups']);
			$suppIds = $supplierGroupsHelper->getPartnerIdsList();
			if ($suppIds) {
				$reportCopyInfo->addFilter('off.idSupplier', 'IN', $suppIds);
			} else {
				$reportCopyInfo->addCustomFilter("0");
			}
		}

		// INVOICE FILTERS ------------------------------------------------------------------------
		if ($stdFilters['invoiceDate']['start']) {
			$reportCopyInfo->addFilter('inv.invoiceDate', '>=', $stdFilters['invoiceDate']['start']);
		}
		if ($stdFilters['invoiceDate']['end']) {
			$reportCopyInfo->addFilter('inv.invoiceDate', '<=', $stdFilters['invoiceDate']['end']);
		}

		if ($stdFilters['idInvoiceCustomers']) {
			$reportCopyInfo->addFilter('inv.idCustomer', 'IN', $this->filtersManager->getDuplicateEntitiesGroup('idInvoiceCustomers'));
		}
		if ($stdFilters['idInvoiceCorporates']) {
			$reportCopyInfo->addFilter('inv.idCorporate', 'IN', $this->filtersManager->getDuplicateEntitiesGroup('idInvoiceCorporates'));
		}

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

		if ($stdFilters['serviceBalanced']===true) {
			$reportCopyInfo->addFilter('serv.idBalance' , '!=', 0);
		} elseif ($stdFilters['serviceBalanced']===false) {
			$reportCopyInfo->addFilter('serv.idBalance' , '=', 0);
		}

		$reportCopyInfo->populate();

	}

}
