all

gistfile1.txt

+        /** @var \Rgkh\Common\Base\Grid\BasePaginatorGrid $baseGrid */
+        $baseGrid = $this->getServiceLocator()->get(BasePaginatorGrid::class);
+        return new Page(1, 1, ['total' => 1], ['data' => 'GetCleanSewageListGrid']);

{
  "jsonrpc": "2.0",
  "method": "getPlumbingFacilitiesListIn",
  "params": {
    "reportingPeriodYear": "2019",
    "aoGuid": "e3a88932-c76e-4158-842c-d710a9856df1",
    "pageNumber": "1",
    "pageSize": 50
  },
  "id": "1"
}


{
  "jsonrpc": "2.0",
  "method": "getCleanSewageList",
  "params": {
    "reportingPeriodYear": "2019",
    "aoGuid": "e3a88932-c76e-4158-842c-d710a9856df1",
    "pageNumber": "1",
    "pageSize": 50
  },
  "id": "1"
}

module/Api/Rpc/config/module.config.php:54
Handler\VolgaRiverRecovery\GetCleanSewageList::class             => Handler\VolgaRiverRecovery\GetCleanSewageList::class,

module/Api/Rpc/src/Rpc/V1/Factory/JsonRpcServerFactory.php:42
$server->setClass(VolgaRiverRecoveryResource::class, '', $serviceLocator);

module/Api/Rpc/src/Rpc/V1/Resource/VolgaRiverRecoveryResource.php:55
    /**
     * Возвращает данные о параметрах очистки по выбранному субъекту за выбранный отчетный период
     *
     * @param string $reportingPeriodYear Год отчетного периода
     * @param string $aoGuid              Глобальный уникальный идентификатор адресного объекта
     * @param int $pageNumber             Номер страницы
     * @param int $pageSize               Количество записей на странице
     *
     * @Api\Filter({"param": "reportingPeriodYear", "name": "StringTrim"})
     * @Api\Filter({"param": "aoGuid", "name": "StringTrim"})
     * @Api\Filter({"param": "pageNumber", "name": "ToInt"})
     * @Api\Filter({"param": "pageSize", "name": "ToInt"})
     *
     * @Api\Validator({"param": "reportingPeriodYear", "name": "StringLength", "options": {"min": 4, "max": 4}})
     * @Api\Validator({"param": "aoGuid", "name": "guid"})
     * @Api\Validator({"param": "pageNumber", "name": "GreaterThan", "options": {"min": 0}})
     * @Api\Validator({"param": "pageSize", "name": "GreaterThan", "options": {"min": 0, "max": 300}})

     * @return Page of CleaningParameter objects
     */
    public function getCleanSewageList(?string $reportingPeriodYear = null, ?string $aoGuid = null, ?int $pageNumber = null, ?int $pageSize = 50)
    {
        /** @var Handler\GetCleanSewageList $handler */
        $handler = $this->serviceLocator->get(Handler\GetCleanSewageList::class);

        return $handler->getCleanSewageList($reportingPeriodYear, $aoGuid, $pageNumber, $pageSize);
    }

module/Api/Rpc/src/Rpc/V1/Service/Handler/VolgaRiverRecovery/GetCleanSewageList.php:1
<?php

namespace Rgkh\Api\Rpc\V1\Service\Handler\VolgaRiverRecovery;

use Rgkh\Api\Rpc\V1\Collection\Page;
use Rgkh\Api\Rpc\V1\Exception\CustomRpcException;
use Rgkh\Api\Rpc\V1\Model\Grid\GetCleanSewageListGrid;
use Rgkh\Api\Rpc\V1\Service\JsonRpcError;
use Rgkh\Common\AddressSystem\Entity\FIAS\Addrobj;
use Rgkh\Common\AddressSystem\Enum\FiasAddressObjectLevel;
use Rgkh\Common\Base\Grid\BasePaginatorGrid;
use Rgkh\Common\Base\Mapper\EntityManagerAwareInterface;
use Rgkh\Common\Base\Mapper\EntityManagerAwareTrait;
use Rgkh\Common\Main\Entity\ReportingPeriods;
use Rgkh\Common\Main\Entity\VolgaRiverRecovery\SewerageObjectState;
use Rgkh\Common\Main\Repository\ReportingPeriodsRepository;
use Zend\ServiceManager\ServiceLocatorAwareInterface;
use Zend\ServiceManager\ServiceLocatorAwareTrait;

class GetCleanSewageList implements ServiceLocatorAwareInterface, EntityManagerAwareInterface
{
    use ServiceLocatorAwareTrait;
    use EntityManagerAwareTrait;

    private function mandatoryError($fieldName, $fieldValue)
    {
        return new CustomRpcException(
            JsonRpcError::$specialMessages[JsonRpcError::SPECIAL_MANDATORY_FIELD_IS_NUL],
            JsonRpcError::SPECIAL_MANDATORY_FIELD_IS_NUL,
            [$fieldName],
            $fieldValue
        );
    }

    public function getCleanSewageList($reportingPeriodYear, $aoGuid, $pageNumber, $pageSize)
    {
        switch (true) {
            case !$reportingPeriodYear:
                throw $this->mandatoryError('reportingPeriodYear', $reportingPeriodYear);
            case !$aoGuid:
                throw $this->mandatoryError('aoGuid', $aoGuid);
            case !$pageNumber:
                throw $this->mandatoryError('pageNumber', $pageNumber);
        }

        /** @var \Rgkh\Common\AddressSystem\Repository\FiasAddrobjRepository $addrobjRepository */
        $addrobjRepository = $this->getEntityManager()->getRepository(Addrobj::class);
        $adrObj = $addrobjRepository->findCurrentByGuid($aoGuid);
        if (!$adrObj) {
            throw new CustomRpcException(
                JsonRpcError::$specialMessages[JsonRpcError::SPECIAL_AOGUID_CANT_BE_FOUND],
                JsonRpcError::SPECIAL_AOGUID_CANT_BE_FOUND,
                ['aoGuid'],
                $aoGuid
            );
        }

        $foundReportPeriod = null;

        /** @var ReportingPeriodsRepository $reportPeriodRepository */
        $reportPeriodRepository = $this->getEntityManager()->getRepository(ReportingPeriods::class);

        $reportPeriod = $reportPeriodRepository->findOneBy(['year' => (int)$reportingPeriodYear, 'type' => ReportingPeriods::TYPE_ASSESSMENT_STATE_CWSS]);
        if ($reportPeriod && $reportPeriod->getId()) {
            /** @var CwssObjectsStateRepository $reportPeriodRepo */
            $cwssObjectsStateRepository = $this->getEntityManager()->getRepository(SewerageObjectState::class);
            $foundReportPeriod = $cwssObjectsStateRepository->findOneBy(['reportingPeriod' => $reportPeriod->getId()]);
        }

        if (!$foundReportPeriod) {
            throw new CustomRpcException(
                JsonRpcError::$specialMessages[JsonRpcError::SPECIAL_REPORTING_PERIOD_CANT_BE_FOUND],
                JsonRpcError::SPECIAL_REPORTING_PERIOD_CANT_BE_FOUND,
                ['reportingPeriodYear'],
                $reportingPeriodYear
            );
        }

        /** @var \Rgkh\Common\Base\Grid\BasePaginatorGrid $baseGrid */
        $baseGrid = $this->getServiceLocator()->get(BasePaginatorGrid::class);

        if (!$baseGrid->prepare(GetCleanSewageListGrid::class, [
            'page' => $pageNumber,
            'limit' => $pageSize
        ])) {
            throw new \Exception();
        }

        $oktmoArr = ($adrObj->getAolevel() == FiasAddressObjectLevel::REGION) ? $addrobjRepository->getChildrenOktmo($adrObj) : [$adrObj->getOktmo()];

        $data = $baseGrid->getList([
            'oktmoArr' => $oktmoArr,
            'reportingPeriodId' => $reportPeriod->getId()
        ]);

        $error = $pageNumber > 1
            ? JsonRpcError::SPECIAL_DATA_IS_EMPTY_IN_THIS_AOGUID
            : JsonRpcError::SPECIAL_DATA_IS_EMPTY_IN_THIS_PAGE;

        if (empty($data['data']) && $data['total'] === 0) {
            throw new CustomRpcException(
                JsonRpcError::$specialMessages[$error],
                $error,
                ['aoGuid'],
                $aoGuid
            );
        }

        return new Page($pageSize, $pageNumber, $data['total'], $data['data']);
    }
}

module/Api/Rpc/src/Rpc/V1/Model/Grid/GetCleanSewageListGrid.php
<?php

namespace Rgkh\Api\Rpc\V1\Model\Grid;

use Rgkh\Common\Base\Grid\BaseCursorGrid;

class GetCleanSewageListGrid extends BaseCursorGrid
{

    public function __construct()
    {
        $sql = <<<SQL
WITH cte_geo_tags AS (
    SELECT
        DISTINCT ON (gc.id) gc.id,
        gc.name,
        gc.level,
        gc.oktmo
    FROM geo_tags gt
        JOIN geo_tags gc ON gc.lft BETWEEN gt.lft AND gt.rgt
    WHERE gc.is_archive = 0 AND gt.oktmo = :oktmoArr
), cte_cleaning_parameters AS (
    SELECT
        coalesce(to_char(cp.updated_at, 'DD-MM-YYYY'), 'Не заполнено')                          AS "updatedTime",
        cp.id                                                                                   AS "cleanSewageId",
        cp.clean_sewage_name                                                                    AS "cleanSewageName",
        gt.name                                                                                 AS "moAffiliation",
        cp.sewerage_object_state_id :: TEXT                                                     AS "sewerageObjectState",
        coalesce(cp.power_range_type_id, 0)                                                     AS "powerRangeTypeId",
        coalesce(cp.wastewater_inflow, 0.0)                                                     AS "wastewaterInflow",
        coalesce(cp.clean_sewage_wastewater_volume, 0.0)                                        AS "cleanSewageWastewaterVolume",
        coalesce(cp.water_pollution, 0.0)                                                       AS "waterPollution",
        coalesce(cp.water_pollution_excess, 0.0)                                                AS "waterPollutionExcess",
        coalesce(cp.water_clear_standart, 0.0)                                                  AS "waterClearStandart",
        CASE WHEN cp.has_eco_efficiency_program
            THEN 'Да'
            ELSE 'Нет'
        END                                                                                     AS "hasEcoEfficiencyProgram",
        coalesce(cp.temporary_resolution_doc_number, 'Не заполнено')                            AS "temporaryResolutionDocNumber",
        coalesce(to_char(cp.temporary_resolution_doc_date, 'DD-MM-YYYY'), 'Не заполнено')       AS "temporaryResolutionDocDate",
        coalesce(cp.not_comply_samples_share, 0.0)                                              AS "notComplySamplesShare",
        coalesce(cp.power_consumption, 0.0)                                                     AS "powerConsumption",
        coalesce(cpwd.id, 0)                                                                    AS "dischargeId",
        coalesce(cpwd.discharge_name, 'Не заполнено')                                           AS "dischargeName",
        coalesce(cpwd.type_id, 0)                                                               AS "type",
        coalesce(cpwd.coordinates, 'Не заполнено')                                              AS "coordinates",
        coalesce(cpwd.location_id :: TEXT, 'Не заполнено')                                      AS "location",
        coalesce(cpwd.waterbody_name, 'Не заполнено')                                           AS "waterbodyName",
        coalesce(cpwd.authority_name_issuing_order, 'Не заполнено')                             AS "authorityNameIssuingOrder",
        coalesce(cpwd.order_doc_number, 'Не заполнено')                                         AS "orderDocNumber",
        coalesce(to_char(cpwd.order_doc_date, 'DD-MM-YYYY'), 'Не заполнено')                    AS "orderDocDate",
        coalesce(cpwd.authority_name_issuing_clean_sewage, 'Не заполнено')                      AS "authorityNameIssuingCleanSewage",
        coalesce(cpwd.clean_sewage_doc_number, 'Не заполнено')                                  AS "cleanSewageDocNumber",
        coalesce(to_char(cpwd.clean_sewage_doc_date, 'DD-MM-YYYY'), 'Не заполнено')             AS "cleanSewageDocDate",
        coalesce(to_char(cpwd.clean_sewage_validity, 'DD-MM-YYYY'), 'Не заполнено')             AS "cleanSewageValidity",
        coalesce(cpwd.authority_name_issuing_limit, 'Не заполнено')                             AS "authorityNameIssuingLimit",
        coalesce(cpwd.limit_doc_number, 'Не заполнено')                                         AS "limitDocNumber",
        coalesce(to_char(cpwd.limit_doc_date, 'DD-MM-YYYY'), 'Не заполнено')                    AS "limitDocDate",
        coalesce(to_char(cpwd.limit_validity, 'DD-MM-YYYY'), 'Не заполнено')                    AS "limitValidity"
    FROM volga_river_recovery.cleaning_parameters cp
        JOIN geo_tags gt on cp.geo_tag_id = gt.id AND gt.id IN (SELECT id FROM cte_geo_tags)
        LEFT JOIN volga_river_recovery.cleaning_parameters_wastewater_discharge cpwd ON cpwd.cleaning_parameters_id = cp.id AND cpwd.deleted_at IS NULL
    WHERE cp.deleted_at IS NULL
), cte_cleaning_parameters_cleaning_stages AS (
    SELECT
        t."cleanSewageId",
        string_agg(cpcs.cleaning_stage_name_id :: TEXT, ', ')                                   AS "cleaningStageName"
    FROM volga_river_recovery.cleaning_parameters_cleaning_stages cpcs
        JOIN cte_cleaning_parameters t ON t."cleanSewageId" = cpcs.cleaning_parameters_id
    WHERE cpcs.deleted_at IS NULL
    GROUP BY t."cleanSewageId"
), cte_cleaning_parameters_sludge_cleaning_techs AS (
    SELECT
        t."cleanSewageId",
        string_agg(coalesce(cpsct.sludge_cleaning_tech_name_id :: TEXT, 'Не заполнено'), ', ')  AS "sludgeCleaningTechName"
    FROM volga_river_recovery.cleaning_parameters_sludge_cleaning_techs cpsct
        JOIN cte_cleaning_parameters t ON t."cleanSewageId" = cpsct.cleaning_parameters_id
    WHERE cpsct.deleted_at IS NULL
    GROUP BY t."cleanSewageId"
), cte_cleaning_parameters_wastewater_clean_techs AS (
    SELECT
        t."cleanSewageId",
        string_agg(coalesce(cpwct.tech_name_id :: TEXT, 'Не заполнено'), ', ')                  AS "techName"
    FROM volga_river_recovery.cleaning_parameters_wastewater_clean_techs cpwct
        JOIN cte_cleaning_parameters t ON t."cleanSewageId" = cpwct.cleaning_parameters_id
    WHERE cpwct.deleted_at IS NULL
    GROUP BY t."cleanSewageId"
), cte_cleaning_parameters_wastewater_discharge_pollutant_indicators AS (
    SELECT
        t."cleanSewageId",
        string_agg(cpwdpi.cleaning_parameters_wastewater_discharge_id :: TEXT, ', ')            AS "pollutantIndicatorDischargeId",
        string_agg(cpwdpi.id :: TEXT, ', ')                                                     AS "pollutantId",
        string_agg(r.name, ', ')                                                                AS "pollutantName",
        string_agg(coalesce(cpwdpi.actual_indicators, 0.0) :: TEXT, ', ')                       AS "actualIndicators",
        string_agg(coalesce(cpwdpi.project_indicators, 0.0) :: TEXT, ', ')                      AS "projectIndicators",
        string_agg(coalesce(cpwdpi.tech_indicator_id, 0) :: TEXT, ', ')                         AS "techIndicatorId",
        string_agg(coalesce(cpwdpi.set_rate, 0.0) :: TEXT, ', ')                                AS "setRate",
        string_agg(coalesce(cpwdpi.project_indicators_exceed, 0.0) :: TEXT, ', ')               AS "projectIndicatorsExceed",
        string_agg(coalesce(cpwdpi.tech_indicators_exceed, 0.0) :: TEXT, ', ')                  AS "techIndicatorsExceed",
        string_agg(coalesce(cpwdpi.indicator_exceed, 0.0) :: TEXT, ', ')                        AS "indicatorExceed",
        string_agg(CASE WHEN cpwdpi.project_indicators_complying
            THEN 'Да'
            ELSE 'Нет'
        END, ', ')                                                                              AS "projectIndicatorsComplying",
        string_agg(coalesce(cpwdpi.tech_indicators_complying_id, 0) :: TEXT, ', ')              AS "techIndicatorsComplyingId",
        string_agg(CASE WHEN cpwdpi.indicators_complying
            THEN 'Да'
            ELSE 'Нет'
        END, ', ')                                                                              AS "indicatorsComplying"
    FROM volga_river_recovery.cleaning_parameters_wastewater_discharge_pollutant_indicators cpwdpi
        JOIN cte_cleaning_parameters t ON t."dischargeId" = cpwdpi.cleaning_parameters_wastewater_discharge_id
        JOIN refs r ON r.id = cpwdpi.indicator_id
    GROUP BY t."cleanSewageId"
), cte_data AS (
    SELECT *
    FROM cte_cleaning_parameters
        LEFT JOIN cte_cleaning_parameters_cleaning_stages USING ("cleanSewageId")
        LEFT JOIN cte_cleaning_parameters_sludge_cleaning_techs USING ("cleanSewageId")
        LEFT JOIN cte_cleaning_parameters_wastewater_clean_techs USING ("cleanSewageId")
        LEFT JOIN cte_cleaning_parameters_wastewater_discharge_pollutant_indicators USING ("cleanSewageId")
    ORDER BY "cleanSewageName"
)
SELECT * FROM cte_data
SQL;

        $this->setQuery($sql);
    }
}