<?php
 $expandFilters=false; $invoicesTablesList = \App\Utils\Db\TablesUtilsCached::getInstance()->getExistingMonthTablesByInterval($DBTSupplierInvoices, $filters['invoiceStartDateMysql'], $filters['invoiceEndDateMysql']); $invoiceRowsListSQL=''; $iteration=0; foreach($invoicesTablesList as $thisTableTime=>$thisTableName) { $iteration++; if ($iteration>1) $invoiceRowsListSQL.="
		UNION ALL
	"; if (count($invoicesTablesList)>1) { $invoiceRowsListSQL.=" ( "; } $invoiceRowsListSQL.=" SELECT "; if ($iteration==1)$invoiceRowsListSQL.=" SQL_CALC_FOUND_ROWS "; $invoiceRowsListSQL.="
				'$thisTableTime' AS tableMonth, inv.id AS idInvoice, inv.invoiceNumber, inv.invoiceDate, inv.idSupplier, inv.currency, inv.statusOp, inv.blockTime,
				invRows.id AS idRow, invRows.serviceCode, invRows.idPassenger, invRows.description, (invRows.priceValue + invRows.vatValue) AS rowTotalValue, invRows.idBalance,
				invRows.reservationId, invRows.reservationCode, invRows.reservationNumber, invRows.externalReservationId, invRows.externalReservationDate,
				invRows.checkIn, invRows.contractNumber, invRows.ticketNo, invRows.idSupplierProduct, invRows.countryCode, invRows.cityId
			FROM `$DBTSupplierInvoices$thisTableTime` AS inv, `$DBTSupplierInvoiceRows$thisTableTime` AS invRows
			WHERE
				invRows.idInvoice=inv.id AND
				(inv.invoiceDate BETWEEN '".$filters['invoiceStartDateMysql']."' AND '".$filters['invoiceEndDateMysql']."') AND
				!inv.deleted
	"; if ($filters['invoiceNumber']) { $invoiceRowsListSQL.=" AND inv.invoiceNumber='".$filters['invoiceNumber']."'"; } if ($filters['invoiceIdOffice']) { $invoiceRowsListSQL.=" AND inv.idOffice='".$filters['invoiceIdOffice']."'"; } if ($filters['invoiceIdUser']) { $invoiceRowsListSQL.=" AND inv.idUser='".$filters['invoiceIdUser']."'"; } if ($filters['idReservation']) { $resList=explodeClean(",", $filters['idReservation']); $idReservationsList=array(); foreach($resList as $thisRes) { $thisRes=trim($thisRes); if ($thisRes) $idReservationsList[]=$thisRes; } if ($idReservationsList) { $invoiceRowsListSQL.=" AND (0"; foreach($idReservationsList as $resId) { if ($filters['idReservationType']=="id") { $orderId = \App\Utils\Db\TablesUtils::getInstance()->switchIdReservationConversion($resId, "normal"); $orderNumber = \Entity\Order::select($orderId)->reservationNumber; $invoiceRowsListSQL.=" OR invRows.reservationId='".$orderId."'"; $invoiceRowsListSQL.=" OR invRows.reservationNumber='".$orderNumber."'"; } if ($filters['idReservationType']=="code") { $invoiceRowsListSQL.=" OR invRows.reservationCode='".$resId."'"; } if ($filters['idReservationType']=="number") { $invoiceRowsListSQL.=" OR invRows.reservationNumber='".$resId."'"; } } $invoiceRowsListSQL.=")"; } } if ($filters['idSupplier']) { $invoiceRowsListSQL.=" AND (inv.idSupplier='".$filters['idSupplier']."'"; $suppList = \Entity\Supplier::select($filters['idSupplier'])->duplicatedSuppliersList; foreach($suppList as $thisSupp) $invoiceRowsListSQL.=" OR inv.idSupplier='".$thisSupp."'"; $invoiceRowsListSQL.=")"; } if ($filters['idSupplierService']) { $invoiceRowsListSQL.=" AND invRows.idSupplierProduct='".$filters['idSupplierService']."'"; } if ($filters['serviceCode']) { $invoiceRowsListSQL.=" AND invRows.serviceCode='".$filters['serviceCode']."'"; } if ($filters['balanceStatus']==0) { $invoiceRowsListSQL.=" AND invRows.idBalance=0"; } elseif ($filters['balanceStatus']==1) { $invoiceRowsListSQL.=" AND invRows.idBalance"; } if ($filters['text']) { $invoiceRowsListSQL.=" AND (invRows.description LIKE '%".$filters['text']."%' OR inv.processedRemarks LIKE '%=>\"".$filters['text']."\"%')"; } if ($filters['dueStartDateMysql']) { $invoiceRowsListSQL.=" AND inv.dueDate >= '".$filters['dueStartDateMysql']."'"; } if ($filters['dueEndDateMysql']) { $invoiceRowsListSQL.=" AND inv.dueDate <= '".$filters['dueEndDateMysql']."'"; } if ($filters['statusOp']) { $invoiceRowsListSQL.=" AND inv.statusOp='".$filters['statusOp']."'"; } if ($filters['statusFin']) { $invoiceRowsListSQL.=" AND inv.statusFin='".$filters['statusFin']."'"; } if ($filters['extResDateMysql']) { $invoiceRowsListSQL.=" AND invRows.externalReservationDate='".$filters['extResDateMysql']."'"; } if ($filters['extResId']) { $invoiceRowsListSQL.=" AND invRows.externalReservationId='".$filters['extResId']."'"; } if ($filters['idPassenger']) { $invoiceRowsListSQL.=" AND invRows.idPassenger='".$filters['idPassenger']."'"; } if ($filters['destinationCountryCode']) { $invoiceRowsListSQL.=" AND invRows.countryCode='".$filters['destinationCountryCode']."'"; } if ($filters['destinationCityId']) { $invoiceRowsListSQL.=" AND invRows.cityId='".$filters['destinationCityId']."'"; } if ($filters['serviceStartDateMysql']) { $invoiceRowsListSQL.=" AND invRows.checkIn >= '".$filters['serviceStartDateMysql']."'"; } if ($filters['serviceEndDateMysql']) { $invoiceRowsListSQL.=" AND invRows.checkIn <= '".$filters['serviceEndDateMysql']."'"; } if ($filters['ticketNo']) { $invoiceRowsListSQL.=" AND invRows.ticketNo='".$filters['ticketNo']."'"; } if ($filters['idMiceProject']) { $invoiceRowsListSQL.=" AND (0"; $idOrders = \Entity\MiceProject::select($filters['idMiceProject'])->idOrders; foreach($idOrders as $idOrder) { $invoiceRowsListSQL.=" OR invRows.reservationId='{$idOrder}'"; } $invoiceRowsListSQL.=")"; } if (count($invoicesTablesList)>1) { $invoiceRowsListSQL.=')'; } } if ($invoiceRowsListSQL) { $invoiceRowsListSQL.="
		ORDER BY invoiceDate DESC, idInvoice DESC, idRow ASC
		LIMIT ".($filters['currentPage']*$filters['resultsPerPage']).", ".$filters['resultsPerPage']."
	"; } if (!$invoiceRowsListSQL) { $showMessage['info'] = $_LANG['invoices']['balanceInvoices']['supplierInvoiceList']['infos']['noInvoiceFound']; $expandFilters=true; } else { $invoiceRowsList=$db->GetAll($invoiceRowsListSQL); if (!is_array($invoiceRowsList)) { $OK=0; $showMessage['error'] = $_LANG['invoices']['balanceInvoices']['supplierInvoiceList']['infos']['noInvoicesBadSql']; writeLog("Invoices list failed. SQL: ".$invoiceRowsListSQL, 'events', 10); } else { $invoicesCountSQL="SELECT FOUND_ROWS()"; $countResults=$db->GetOne($invoicesCountSQL); if (!$countResults) { $showMessage['info'] = $_LANG['invoices']['balanceInvoices']['supplierInvoiceList']['infos']['noInvoiceFound']; $expandFilters=true; } } } ?>