<?php
 refreshReportStatus('extractMainDataFromDb', 'runningMainSQL', 0); $reportCopyInfo = new \App\Reports\Db\ManageRecords\Insert($reportTable); $reportCopyInfo->setMainTable($DBTRd_orders, 'ord'); $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'); $cols = [ 'ord_id' => 'ord.id', 'ord_convertedId' => 'ord.convertedId', 'ord_number' => 'ord.orderNumber', 'ord_code' => 'ord.orderCode', 'ord_idAutoProcessRule' => 'ord.idAutoProcessRule', '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_ownerCustomerId' => 'ord.idCustomerOwner', 'ord_ownerCustomerProcessedRemarks' => 'ownCust.processedRemarks', 'ord_pax' => 'ord.pax', 'ord_ownerCorporateId' => 'ord.idCorporateOwner', 'ord_ownerCorporateAlias' => 'ownCorp.alias', 'ord_ownerCorporateProcessedRemarks' => 'ownCorp.processedRemarks', 'ord_ownerCorporateOfficeId' => 'ord.idCorporateOwnerOffice', 'ord_ownerCorporateOfficeName' => 'ownCorpOffice.name', 'ord_ownerCorporateOfficeCostCenter' => 'ord.corporateOwnerOfficeCostCenter', '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', ]; $reportCopyInfo->setStructureColumns($cols); $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_ownerCustomerName", "CONCAT(ownCust.lastNameStd, ' ', ownCust.firstNameStd)", "ownCust"); $reportCopyInfo->setActiveColumns($availableTableColumns); if ($reportFilters['orderDate']['startDate']) { $reportCopyInfo->addFilter('ord.date', '>=', $reportFilters['orderDate']['startDate']); } if ($reportFilters['orderDate']['endDate']) { $reportCopyInfo->addFilter('ord.date', '<=', $reportFilters['orderDate']['endDate']); } if ($reportFilters['orderStartDate']['startDate']) { $reportCopyInfo->addFilter('ord.startDate', '>=', $reportFilters['orderStartDate']['startDate']); } if ($reportFilters['orderStartDate']['endDate']) { $reportCopyInfo->addFilter('ord.startDate', '<=', $reportFilters['orderStartDate']['endDate']); } if ($reportFilters['orderEndDate']['startDate']) { $reportCopyInfo->addFilter('ord.endDate', '>=', $reportFilters['orderEndDate']['startDate']); } if ($reportFilters['orderEndDate']['endDate']) { $reportCopyInfo->addFilter('ord.endDate', '<=', $reportFilters['orderEndDate']['endDate']); } if ($reportFilters['idMiceProject']) { $reportCopyInfo->addFilter('ord.idMiceProject', '=', $reportFilters['idMiceProject']); } if ($reportFilters['orderStatusOp'] and count($reportFilters['orderStatusOp']) < 4) { $reportCopyInfo->addFilter('ord.statusOp', 'IN', array_keys($reportFilters['orderStatusOp'])); } if ($reportFilters['orderStatusFin'] and count($reportFilters['orderStatusFin']) < 3) { $reportCopyInfo->addFilter('ord.statusFin', 'IN', array_keys($reportFilters['orderStatusFin'])); } $reportCopyInfo->populate();