Page 2 sql

WITH "visits" AS (SELECT mv_stat_lvl_0_receptions.id, mv_stat_lvl_0_receptions.employee_id, mv_stat_lvl_0_receptions.asserted_date, mv_stat_lvl_0_receptions.patient_id FROM "mv_stat_lvl_0_receptions" WHERE "mv_stat_lvl_0_receptions"."legal_entity_id" = 867 AND "mv_stat_lvl_0_receptions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-29 23:59:59.999999'), "declarations_cnt" AS (SELECT mv_stat_lvl_1_declarations.employee_id, COUNT(DISTINCT visits.patient_id) as counter, COUNT(DISTINCT mv_stat_lvl_1_declarations.id) as total_counter FROM "mv_stat_lvl_1_declarations" JOIN visits ON mv_stat_lvl_1_declarations.employee_id = visits.employee_id WHERE "mv_stat_lvl_1_declarations"."legal_entity_id" = 867 GROUP BY "mv_stat_lvl_1_declarations"."employee_id") SELECT mv_stat_lvl_0_employees.*, COALESCE(declarations_cnt.counter, 0) as visits_counter, COALESCE(declarations_cnt.total_counter, 0)as total_counter FROM "mv_stat_lvl_0_employees" LEFT JOIN declarations_cnt ON mv_stat_lvl_0_employees.id = declarations_cnt.employee_id WHERE "mv_stat_lvl_0_employees"."legal_entity_id" = 867 AND "mv_stat_lvl_0_employees"."employee_type_ref_value" = 'DOCTOR' AND "mv_stat_lvl_0_employees"."employee_status_ref_value" = 'APPROVED' ORDER BY full_name ASC LIMIT 15 OFFSET 0



WITH "receptions" AS (SELECT "mv_stat_lvl_0_receptions".* FROM "mv_stat_lvl_0_receptions" WHERE "mv_stat_lvl_0_receptions"."legal_entity_id" = 867 AND "mv_stat_lvl_0_receptions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-29 23:59:59.999999') SELECT mv_stat_lvl_1_patients.id,
mv_stat_lvl_1_patients.employee_id,
mv_stat_lvl_1_patients.age,
mv_stat_lvl_1_patients.full_name,
mv_stat_lvl_1_patients.gender,
mv_stat_lvl_1_patients.personalities_id,
COUNT(receptions.id) as visits_counter,
MAX(receptions.asserted_date) as last_visit_date,
CASE
    WHEN COUNT(receptions.id) > 0 THEN
        CASE WHEN COUNT(receptions.id) filter ( where receptions.employee_id = mv_stat_lvl_1_patients.employee_id ) > 0
            THEN
              'own_doctor_visit'
            ELSE
              'general_visit'
        END
ELSE
    'without_visit'
END AS  visits_state FROM "mv_stat_lvl_1_patients" LEFT JOIN receptions ON mv_stat_lvl_1_patients.id = receptions.patient_id
LEFT JOIN mv_stat_lvl_0_patient_contacts ON mv_stat_lvl_1_patients.personalities_id = mv_stat_lvl_0_patient_contacts.id WHERE "mv_stat_lvl_1_patients"."legal_entity_id" = 867 AND (mv_stat_lvl_0_patient_contacts.full_street ILIKE '%но%') GROUP BY mv_stat_lvl_1_patients.id,
mv_stat_lvl_1_patients.employee_id,
mv_stat_lvl_1_patients.age,
mv_stat_lvl_1_patients.full_name,
mv_stat_lvl_1_patients.gender,
mv_stat_lvl_1_patients.personalities_id ORDER BY mv_stat_lvl_1_patients.full_name ASC LIMIT 15 OFFSET 0

SELECT "mv_stat_lvl_0_patient_contacts".* FROM "mv_stat_lvl_0_patient_contacts" WHERE "mv_stat_lvl_0_patient_contacts"."id" = 7698

SELECT "mv_stat_lvl_0_employees".* FROM "mv_stat_lvl_0_employees" WHERE "mv_stat_lvl_0_employees"."id" = 2639






      CREATE MATERIALIZED VIEW IF NOT EXISTS mv_stat_lvl_0_patient_contacts WITH (autovacuum_enabled = off) AS
       WITH phones AS (
          SELECT
              phones.dialable_id,
              COALESCE((MAX(phones.number) filter (where(phones.phone_type_id = 3))), (MAX(phones.number) filter (where(phones.phone_type_id = 11)))) AS number
          FROM "phones"
          WHERE "phones"."dialable_type" = 'Patient::Person' AND "phones"."phone_type_id" IN (3, 11)
          GROUP BY phones.dialable_id
      ), addresses AS (
        SELECT
          street_types.title_translations -> 'uk' as street_type,
          addresses.addressable_id as personality_id,
          addresses.street,
          addresses.building,
          addresses.apartment,
          addresses.zip,
          addresses.country_id,
          countries.title_translations -> 'uk' as country_name,
          addresses.district_id,
          districts.title_translations -> 'uk' as district_name,
          addresses.settlement_id,
          settlements.title_translations -> 'uk' as settlement_name,
          addresses.region_id,
          regions.title_translations -> 'uk' as region_name
        FROM
          "addresses"
            LEFT JOIN street_types ON addresses.street_type_id = street_types.id
            LEFT JOIN countries ON countries.id = addresses.country_id
            LEFT JOIN districts ON districts.id = addresses.district_id
            LEFT JOIN settlements ON settlements.id = addresses.settlement_id
            LEFT JOIN regions ON regions.id = addresses.region_id
        WHERE "addresses"."addressable_type" = 'Patient::Person' AND "addresses"."address_type_id" IN ((SELECT address_types.id FROM address_types WHERE address_types.code = 'RESIDENCE'))
      )

      SELECT DISTINCT ON (personalities.id)
          personalities.id,
          phones.number as phone_number,
          addresses.street_type,
          addresses.street,
          addresses.building,
          addresses.apartment,
          addresses.country_id,
          addresses.district_id,
          addresses.settlement_id,
          addresses.region_id,
          addresses.country_name,
          addresses.district_name,
          addresses.settlement_name,
          addresses.region_name,
          CONCAT_WS(', ', NULLIF(addresses.street_type, ''), NULLIF(addresses.street, ''), NULLIF(addresses.building, ''), NULLIF(addresses.apartment, '')) as full_street
      FROM "personalities"
          LEFT JOIN "addresses" ON "addresses"."personality_id" = "personalities"."id"
          LEFT JOIN "phones" ON "phones"."dialable_id" = "personalities"."id"
      WHERE (phones.number IS NOT NULL OR addresses.personality_id IS NOT NULL) AND "personalities"."type" IN ('Patient::Person')
            WITH NO DATA;