API tools faq
paste
Advertisement
temaon_lieto

ALL VIEWS SQL WITH INDEXES

temaon_lieto
Feb 2nd, 2024
81
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 24.92 KB | None | 0 0
raw download clone embed print report
  1. --
  2. -- Name: mv_connected_assocs_disease_progresses; Type: MATERIALIZED VIEW; Schema: public; Owner: -
  3. --
  4. CREATE MATERIALIZED VIEW public.mv_connected_assocs_disease_progresses
  5. WITH (autovacuum_enabled=off) AS
  6.  SELECT combined_data.id,
  7.     combined_data.patient_id,
  8.     combined_data.service_code,
  9.     combined_data.performed_date,
  10.     combined_data.assoc_type
  11.    FROM (((( SELECT ehr_direction_service_requests.id,
  12.             ehr_direction_service_requests.patient_id,
  13.             ehr_ehealth_services.code AS service_code,
  14.             ehr_direction_service_requests.ehealth_inserted_at AS performed_date,
  15.             0 AS assoc_type
  16.            FROM (public.ehr_direction_service_requests
  17.              JOIN public.ehr_ehealth_services ON (((ehr_ehealth_services.id = ehr_direction_service_requests.service_requestable_id) AND ((ehr_direction_service_requests.service_requestable_type)::text = 'Ehr::Ehealth::Service'::text) AND ((ehr_direction_service_requests.status)::text = ANY ((ARRAY['active'::character varying, 'completed'::character varying])::text[])))))
  18.         UNION
  19.          SELECT ehr_diagnostic_reports.id,
  20.             ehr_diagnostic_reports.patient_id,
  21.             ehr_ehealth_services.code AS service_code,
  22.             ehr_diagnostic_reports.issued AS perfomed_date,
  23.             1 AS assoc_type
  24.            FROM (public.ehr_diagnostic_reports
  25.              JOIN public.ehr_ehealth_services ON (((ehr_diagnostic_reports.service_id = ehr_ehealth_services.id) AND (ehr_diagnostic_reports.ehealth_status = 1))))
  26.         UNION
  27.          SELECT ehr_observations.id,
  28.             ehr_observations.patient_id,
  29.             NULL::character varying AS service_code,
  30.             ehr_observations.issued AS performed_date,
  31.             2 AS assoc_type
  32.            FROM (public.ehr_observations
  33.              JOIN public.classification_fhir_observation_codes ON (((classification_fhir_observation_codes.id = ehr_observations.codeable_id) AND ((classification_fhir_observation_codes.code)::text = '14743-9'::text))))) combined_data
  34.      JOIN public.user_patients ON ((user_patients.id = combined_data.patient_id)))
  35.      JOIN public.personalities ON (((personalities.id = user_patients.personality_id) AND ((personalities.type)::text = 'Patient::Person'::text))))
  36.      JOIN public.declarations ON (((declarations.personality_id = personalities.id) AND (declarations.declaration_status_id = ( SELECT declaration_statuses.id
  37.            FROM public.declaration_statuses
  38.           WHERE ((declaration_statuses.code)::text = 'ACTIVE'::text))))))
  39.   WITH NO DATA;
  40. --
  41. -- Name: idx_k4_mv_connected_assocs_disease_progresses_patient_id; Type: INDEX; Schema: public; Owner: -
  42. --
  43. CREATE INDEX idx_k4_mv_connected_assocs_disease_progresses_patient_id ON public.mv_connected_assocs_disease_progresses USING btree (patient_id);
  44.  
  45.  
  46. --
  47. -- Name: mv_encounter_disease_progresses; Type: MATERIALIZED VIEW; Schema: public; Owner: -
  48. --
  49. CREATE MATERIALIZED VIEW public.mv_encounter_disease_progresses
  50. WITH (autovacuum_enabled=off) AS
  51.  SELECT receptions.id,
  52.     receptions.patient_id,
  53.     receptions.onset_date,
  54.         CASE
  55.             WHEN ((EXISTS ( SELECT 1
  56.                FROM (public.ehr_reasons
  57.                  JOIN public.classification_icpc2_items ON (((ehr_reasons.encounter_id = receptions.id) AND (ehr_reasons.icpc2_reason_id = classification_icpc2_items.id) AND ((classification_icpc2_items.code)::text = 'A98'::text)))))) AND (EXISTS ( SELECT 1
  58.                FROM (public.ehr_observations
  59.                  JOIN public.classification_fhir_observation_codes ON (((ehr_observations.encounter_id = receptions.id) AND (classification_fhir_observation_codes.id = ehr_observations.codeable_id) AND ((classification_fhir_observation_codes.code)::text = ANY ((ARRAY['39156-5'::character varying, '14743-9'::character varying, '8462-4'::character varying, '8480-6'::character varying])::text[])))))))) THEN true
  60.             ELSE false
  61.         END AS prof_check
  62.    FROM (((public.receptions
  63.      JOIN public.user_patients ON ((user_patients.id = receptions.patient_id)))
  64.      JOIN public.personalities ON (((personalities.id = user_patients.personality_id) AND ((personalities.type)::text = 'Patient::Person'::text))))
  65.      JOIN public.declarations ON (((declarations.personality_id = personalities.id) AND (declarations.declaration_status_id = ( SELECT declaration_statuses.id
  66.            FROM public.declaration_statuses
  67.           WHERE ((declaration_statuses.code)::text = 'ACTIVE'::text))))))
  68.   WHERE (receptions.ehealth_status = 1)
  69.   WITH NO DATA;
  70. --
  71. -- Name: idx_k4_mv_encounter_disease_progresses_patient_id; Type: INDEX; Schema: public; Owner: -
  72. --
  73. CREATE INDEX idx_k4_mv_encounter_disease_progresses_patient_id ON public.mv_encounter_disease_progresses USING btree (patient_id);
  74.  
  75.  
  76. --
  77. -- Name: mv_stat_calendar_events_service_requests; Type: MATERIALIZED VIEW; Schema: public; Owner: -
  78. --
  79. CREATE MATERIALIZED VIEW public.mv_stat_calendar_events_service_requests
  80. WITH (autovacuum_enabled=off) AS
  81.  SELECT calendar_events.id,
  82.     calendar_events.legal_entity_id,
  83.     calendar_events.service_request_id,
  84.     calendar_events.created_at,
  85.     ehr_direction_service_requests.service_requestable_id,
  86.     ehr_direction_service_requests.program_processing_status
  87.    FROM ((public.calendar_events
  88.      JOIN public.ehr_direction_service_requests ON ((ehr_direction_service_requests.id = calendar_events.service_request_id)))
  89.      JOIN public.ehr_ehealth_services ON ((ehr_ehealth_services.id = ehr_direction_service_requests.service_requestable_id)))
  90.   WHERE (((calendar_events.state)::text = 'approved'::text) AND ((ehr_direction_service_requests.status)::text = ANY (ARRAY[('completed'::character varying)::text, ('active'::character varying)::text])))
  91.   WITH NO DATA;
  92. --
  93. -- Name: idx_k4_mv_stat_calendar_events_service_requests_legal_service; Type: INDEX; Schema: public; Owner: -
  94. --
  95. CREATE INDEX idx_k4_mv_stat_calendar_events_service_requests_legal_service ON public.mv_stat_calendar_events_service_requests USING btree (legal_entity_id, service_requestable_id);
  96.  
  97.  
  98. --
  99. -- Name: mv_stat_conditions; Type: MATERIALIZED VIEW; Schema: public; Owner: -
  100. --
  101. CREATE MATERIALIZED VIEW public.mv_stat_conditions
  102. WITH (autovacuum_enabled=off) AS
  103.  SELECT ehr_conditions.encounter_id,
  104.     ehr_conditions.patient_id,
  105.     ehr_conditions.employee_id,
  106.     classification_icpc2_items.code,
  107.     (classification_icpc2_items.title_translations OPERATOR(public.->) 'uk'::text) AS label,
  108.     receptions.asserted_date
  109.    FROM ((public.ehr_conditions
  110.      JOIN public.receptions ON (((receptions.id = ehr_conditions.encounter_id) AND (receptions.ehealth_status = 1))))
  111.      JOIN public.classification_icpc2_items ON ((((classification_icpc2_items.type)::text = 'Classification::Icpc2::Condition'::text) AND (classification_icpc2_items.id = ehr_conditions.icpc2_code_id))))
  112.   WHERE ((ehr_conditions.employee_id IS NOT NULL) AND (((classification_icpc2_items.code)::text = ANY (ARRAY[('T90'::character varying)::text, ('K85'::character varying)::text, ('K86'::character varying)::text, ('K87'::character varying)::text, ('R96'::character varying)::text])) OR ((classification_icpc2_items.code)::text ~~ 'P%'::text)))
  113.   WITH NO DATA;
  114. --
  115. -- Name: idx_k4_mv_stat_condition_code; Type: INDEX; Schema: public; Owner: -
  116. --
  117. CREATE INDEX idx_k4_mv_stat_condition_code ON public.mv_stat_conditions USING btree (code);
  118. --
  119. -- Name: idx_k4_mv_stat_condition_code_asserted_date; Type: INDEX; Schema: public; Owner: -
  120. --
  121. CREATE INDEX idx_k4_mv_stat_condition_code_asserted_date ON public.mv_stat_conditions USING btree (code, asserted_date);
  122. --
  123. -- Name: idx_k4_mv_stat_condition_employee_patient; Type: INDEX; Schema: public; Owner: -
  124. --
  125. CREATE INDEX idx_k4_mv_stat_condition_employee_patient ON public.mv_stat_conditions USING btree (employee_id, patient_id);
  126.  
  127.  
  128. --
  129. -- Name: mv_stat_declarations; Type: MATERIALIZED VIEW; Schema: public; Owner: -
  130. --
  131. CREATE MATERIALIZED VIEW public.mv_stat_declarations
  132. WITH (autovacuum_enabled=off) AS
  133.  SELECT DISTINCT ON (declarations.id) declarations.id,
  134.     declarations.employee_id,
  135.     declarations.personality_id,
  136.     declarations.division_id,
  137.     declarations.start_date,
  138.     declarations.end_date,
  139.     "Parties"."LegalEntityId" AS legal_entity_id,
  140.     user_patients.id AS patient_id,
  141.     personalities.birth_date,
  142.     declaration_statuses.code AS declaration_status_code,
  143.     receptions.asserted_date
  144.    FROM ((((((public.declarations
  145.      JOIN dbo."Employees" ON (("Employees"."Id" = declarations.employee_id)))
  146.      JOIN public.declaration_statuses ON ((declaration_statuses.id = declarations.declaration_status_id)))
  147.      JOIN dbo."Parties" ON (("Parties"."Id" = "Employees"."PartyId")))
  148.      JOIN public.personalities ON (((personalities.id = declarations.personality_id) AND ((personalities.type)::text = 'Patient::Person'::text))))
  149.      JOIN public.user_patients ON ((user_patients.personality_id = personalities.id)))
  150.      LEFT JOIN public.receptions ON (((receptions.patient_id = user_patients.id) AND (receptions.ehealth_status = 1))))
  151.   WHERE ((declaration_statuses.code)::text = ANY (ARRAY[('ACTIVE'::character varying)::text, ('COMPLETED'::character varying)::text]))
  152.   ORDER BY declarations.id, receptions.asserted_date DESC
  153.   WITH NO DATA;
  154. --
  155. -- Name: idx_k4_mv_stat_declarations_by_date; Type: INDEX; Schema: public; Owner: -
  156. --
  157. CREATE INDEX idx_k4_mv_stat_declarations_by_date ON public.mv_stat_declarations USING btree (legal_entity_id, start_date);
  158. --
  159. -- Name: idx_k4_mv_stat_declarations_by_division; Type: INDEX; Schema: public; Owner: -
  160. --
  161. CREATE INDEX idx_k4_mv_stat_declarations_by_division ON public.mv_stat_declarations USING btree (legal_entity_id, division_id);
  162. --
  163. -- Name: idx_k4_mv_stat_declarations_by_employee; Type: INDEX; Schema: public; Owner: -
  164. --
  165. CREATE INDEX idx_k4_mv_stat_declarations_by_employee ON public.mv_stat_declarations USING btree (legal_entity_id, employee_id);
  166. --
  167. -- Name: idx_k4_mv_stat_declarations_emp_pers; Type: INDEX; Schema: public; Owner: -
  168. --
  169. CREATE INDEX idx_k4_mv_stat_declarations_emp_pers ON public.mv_stat_declarations USING btree (legal_entity_id, personality_id);
  170. --
  171. -- Name: idx_k4_mv_stat_declarations_legal; Type: INDEX; Schema: public; Owner: -
  172. --
  173. CREATE INDEX idx_k4_mv_stat_declarations_legal ON public.mv_stat_declarations USING btree (legal_entity_id, id);
  174. --
  175. -- Name: idx_k4_mv_stat_declarations_status; Type: INDEX; Schema: public; Owner: -
  176. --
  177. CREATE INDEX idx_k4_mv_stat_declarations_status ON public.mv_stat_declarations USING btree (legal_entity_id, declaration_status_code);
  178.  
  179.  
  180. --
  181. -- Name: mv_stat_ehr_ehealth_service_groups; Type: MATERIALIZED VIEW; Schema: public; Owner: -
  182. --
  183. CREATE MATERIALIZED VIEW public.mv_stat_ehr_ehealth_service_groups
  184. WITH (autovacuum_enabled=off) AS
  185.  SELECT ehr_ehealth_services.id,
  186.     ehr_ehealth_service_groups.ehr_ehealth_group_id
  187.    FROM (public.ehr_ehealth_services
  188.      JOIN public.ehr_ehealth_service_groups ON ((ehr_ehealth_services.id = ehr_ehealth_service_groups.ehr_ehealth_service_id)))
  189.   WITH NO DATA;
  190. --
  191. -- Name: idx_k4_mv_stat_ehr_ehealth_service_groups; Type: INDEX; Schema: public; Owner: -
  192. --
  193. CREATE INDEX idx_k4_mv_stat_ehr_ehealth_service_groups ON public.mv_stat_ehr_ehealth_service_groups USING btree (ehr_ehealth_group_id);
  194.  
  195.  
  196. --
  197. -- Name: mv_stat_employees_parties; Type: MATERIALIZED VIEW; Schema: public; Owner: -
  198. --
  199. CREATE MATERIALIZED VIEW public.mv_stat_employees_parties
  200. WITH (autovacuum_enabled=off) AS
  201.  SELECT "Employees"."Id",
  202.     "Employees"."PartyId",
  203.     "Parties"."LegalEntityId",
  204.     "Employees"."EmployeeTypeRefValue",
  205.     "Employees"."EmployeeStatusRefValue",
  206.     "Employees"."PositionRefId",
  207.     "PositionRefs"."Value" AS "PositionRefValue",
  208.     "PositionRefs"."_Name" AS "PositionName",
  209.     "Parties"."FirstName",
  210.     "Parties"."LastName",
  211.     "Parties"."SecondName",
  212.     "Employees"."DivisionId",
  213.     concat_ws(' '::text, "Parties"."LastName", "Parties"."FirstName") AS "FullName"
  214.    FROM ((dbo."Employees"
  215.      JOIN dbo."Parties" ON (("Employees"."PartyId" = "Parties"."Id")))
  216.      JOIN dbo."PositionRefs" ON (("PositionRefs"."Id" = "Employees"."PositionRefId")))
  217.   WITH NO DATA;
  218. --
  219. -- Name: idx_k4_mv_stat_employees_parties; Type: INDEX; Schema: public; Owner: -
  220. --
  221. CREATE INDEX idx_k4_mv_stat_employees_parties ON public.mv_stat_employees_parties USING btree ("LegalEntityId", "EmployeeTypeRefValue", "EmployeeStatusRefValue") INCLUDE ("Id");
  222. --
  223. -- Name: idx_uq_k4_mv_stat_employees_le_id_division; Type: INDEX; Schema: public; Owner: -
  224. --
  225. CREATE INDEX idx_uq_k4_mv_stat_employees_le_id_division ON public.mv_stat_employees_parties USING btree ("LegalEntityId", "Id", "DivisionId");
  226. --
  227. -- Name: idx_uq_k4_mv_stat_employees_parties_id_legal; Type: INDEX; Schema: public; Owner: -
  228. --
  229. CREATE INDEX idx_uq_k4_mv_stat_employees_parties_id_legal ON public.mv_stat_employees_parties USING btree ("LegalEntityId", "Id");
  230. --
  231. -- Name: idx_uq_k4_mv_stat_employess_parties_id_full_position; Type: INDEX; Schema: public; Owner: -
  232. --
  233. CREATE INDEX idx_uq_k4_mv_stat_employess_parties_id_full_position ON public.mv_stat_employees_parties USING btree ("LegalEntityId", "Id", "PositionRefId", "FullName") INCLUDE ("PositionRefValue") WHERE ((("EmployeeTypeRefValue")::text = 'DOCTOR'::text) AND (("EmployeeStatusRefValue")::text = 'APPROVED'::text));
  234.  
  235.  
  236. --
  237. -- Name: mv_stat_imt_diseases; Type: MATERIALIZED VIEW; Schema: public; Owner: -
  238. --
  239. CREATE MATERIALIZED VIEW public.mv_stat_imt_diseases
  240. WITH (autovacuum_enabled=off) AS
  241.  SELECT DISTINCT ON (ehr_observations.id, ehr_observations.patient_id, classification_fhir_observation_codes.code) 'ІМТ'::text AS label,
  242.     ehr_observations.encounter_id,
  243.     ehr_observations.employee_id,
  244.     ehr_observations.patient_id,
  245.     classification_fhir_observation_codes.code,
  246.     receptions.asserted_date,
  247.         CASE
  248.             WHEN ((classification_fhir_observation_codes.code)::text = '39156-5'::text) THEN (NULLIF(regexp_replace((ehr_observations.surveillance_value ->> 'value'::text), '[^0-9.]+'::text, ''::text, 'g'::text), ''::text))::double precision
  249.             ELSE (COALESCE((NULLIF(regexp_replace((ehr_observations.surveillance_value ->> 'value'::text), '[^0-9.]+'::text, ''::text, 'g'::text), ''::text))::double precision, (1)::double precision) / power(((COALESCE((NULLIF(regexp_replace(imt_table.observation_value, '[^0-9.]+'::text, ''::text, 'g'::text), ''::text))::numeric, (1)::numeric))::double precision * (0.01)::double precision), (2)::double precision))
  250.         END AS imt_index
  251.    FROM ((((public.ehr_observations
  252.      JOIN public.classification_fhir_observation_codes ON (((classification_fhir_observation_codes.id = ehr_observations.codeable_id) AND ((classification_fhir_observation_codes.code)::text = ANY (ARRAY[('39156-5'::character varying)::text, ('29463-7'::character varying)::text])))))
  253.      JOIN public.classification_fhir_items ON (((classification_fhir_items.id = classification_fhir_observation_codes.quantity_unit_id) AND ((classification_fhir_items.type)::text = 'Classification::Fhir::ValueSet::UcumUnit'::text))))
  254.      JOIN public.receptions ON (((receptions.id = ehr_observations.encounter_id) AND (receptions.ehealth_status = 1))))
  255.      LEFT JOIN ( SELECT ehr_observations_1.id,
  256.             ehr_observations_1.patient_id,
  257.             (classification_fhir_observation_codes_1.title_translations OPERATOR(public.->) 'uk'::text) AS observation_title,
  258.             (ehr_observations_1.surveillance_value ->> 'value'::text) AS observation_value,
  259.             classification_fhir_items_1.code AS unit_code
  260.            FROM ((public.ehr_observations ehr_observations_1
  261.              JOIN public.classification_fhir_observation_codes classification_fhir_observation_codes_1 ON (((classification_fhir_observation_codes_1.id = ehr_observations_1.codeable_id) AND ((classification_fhir_observation_codes_1.code)::text = '8302-2'::text))))
  262.              JOIN public.classification_fhir_items classification_fhir_items_1 ON (((classification_fhir_items_1.id = classification_fhir_observation_codes_1.quantity_unit_id) AND ((classification_fhir_items_1.type)::text = 'Classification::Fhir::ValueSet::UcumUnit'::text))))
  263.           ORDER BY ehr_observations_1.id DESC) imt_table ON (((imt_table.patient_id = ehr_observations.patient_id) AND ((classification_fhir_observation_codes.code)::text = '29463-7'::text))))
  264.   WHERE ((ehr_observations.id IS NOT NULL) AND (ehr_observations.employee_id IS NOT NULL) AND (NULLIF(TRIM(BOTH FROM (ehr_observations.surveillance_value ->> 'value'::text)), ''::text) IS NOT NULL) AND ((((classification_fhir_observation_codes.code)::text = '39156-5'::text) AND ((COALESCE((NULLIF(regexp_replace((ehr_observations.surveillance_value ->> 'value'::text), '[^0-9.]+'::text, ''::text, 'g'::text), ''::text))::numeric, (1)::numeric))::double precision > (25)::double precision)) OR (((classification_fhir_observation_codes.code)::text = '29463-7'::text) AND (imt_table.patient_id IS NOT NULL) AND (COALESCE((NULLIF(regexp_replace(imt_table.observation_value, '[^0-9.]+'::text, ''::text, 'g'::text), ''::text))::numeric, (0)::numeric) > (0)::numeric) AND ((COALESCE((NULLIF(regexp_replace((ehr_observations.surveillance_value ->> 'value'::text), '[^0-9.]+'::text, ''::text, 'g'::text), ''::text))::double precision, (1)::double precision) / power(((COALESCE((NULLIF(regexp_replace(imt_table.observation_value, '[^0-9.]+'::text, ''::text, 'g'::text), ''::text))::numeric, (1)::numeric))::double precision * (0.01)::double precision), (2)::double precision)) > (25)::double precision))))
  265.   WITH NO DATA;
  266. --
  267. -- Name: idx_k4_mv_stat_imt_diseases_asserted_date; Type: INDEX; Schema: public; Owner: -
  268. --
  269. CREATE INDEX idx_k4_mv_stat_imt_diseases_asserted_date ON public.mv_stat_imt_diseases USING btree (asserted_date);
  270. --
  271. -- Name: idx_k4_mv_stat_imt_diseases_employee; Type: INDEX; Schema: public; Owner: -
  272. --
  273. CREATE INDEX idx_k4_mv_stat_imt_diseases_employee ON public.mv_stat_imt_diseases USING btree (employee_id);
  274. --
  275. -- Name: idx_k4_mv_stat_imt_diseases_patient; Type: INDEX; Schema: public; Owner: -
  276. --
  277. CREATE INDEX idx_k4_mv_stat_imt_diseases_patient ON public.mv_stat_imt_diseases USING btree (patient_id);
  278.  
  279.  
  280. --
  281. -- Name: mv_stat_legal_entity_total_completed_service_requests; Type: MATERIALIZED VIEW; Schema: public; Owner: -
  282. --
  283. CREATE MATERIALIZED VIEW public.mv_stat_legal_entity_total_completed_service_requests
  284. WITH (autovacuum_enabled=off) AS
  285.  SELECT ehr_direction_service_requests.legal_entity_id,
  286.     count(ehr_direction_service_requests.id) AS counter
  287.    FROM public.ehr_direction_service_requests
  288.   WHERE (((ehr_direction_service_requests.status)::text = 'completed'::text) AND ((ehr_direction_service_requests.category_code)::text = 'counselling'::text))
  289.   GROUP BY ehr_direction_service_requests.legal_entity_id
  290.   WITH NO DATA;
  291. --
  292. -- Name: idx_uq_k4_mv_stat_mv_stat_legal_entity_completed_requests; Type: INDEX; Schema: public; Owner: -
  293. --
  294. CREATE INDEX idx_uq_k4_mv_stat_mv_stat_legal_entity_completed_requests ON public.mv_stat_legal_entity_total_completed_service_requests USING btree (legal_entity_id);
  295.  
  296.  
  297.  
  298. --
  299. -- Name: mv_stat_legal_entity_total_reception_conditions; Type: MATERIALIZED VIEW; Schema: public; Owner: -
  300. --
  301. CREATE MATERIALIZED VIEW public.mv_stat_legal_entity_total_reception_conditions
  302. WITH (autovacuum_enabled=off) AS
  303.  SELECT receptions.legal_entity_id,
  304.     count(DISTINCT receptions.patient_id) AS counter
  305.    FROM ((public.receptions
  306.      JOIN public.ehr_conditions ON ((ehr_conditions.encounter_id = receptions.id)))
  307.      JOIN public.classification_icpc2_items ON (((classification_icpc2_items.id = ehr_conditions.icpc2_code_id) AND ((classification_icpc2_items.type)::text = 'Classification::Icpc2::Condition'::text) AND (((classification_icpc2_items.code)::text >= 'K29'::text) AND ((classification_icpc2_items.code)::text <= 'K99'::text)))))
  308.   WHERE (receptions.ehealth_status = 1)
  309.   GROUP BY receptions.legal_entity_id
  310.   WITH NO DATA;
  311. --
  312. -- Name: idx_uq_k4_mv_stat_mv_stat_legal_entity_reception_conditions; Type: INDEX; Schema: public; Owner: -
  313. --
  314. CREATE INDEX idx_uq_k4_mv_stat_mv_stat_legal_entity_reception_conditions ON public.mv_stat_legal_entity_total_reception_conditions USING btree (legal_entity_id);
  315.  
  316.  
  317.  
  318. --
  319. -- Name: mv_stat_legal_entity_total_receptions; Type: MATERIALIZED VIEW; Schema: public; Owner: -
  320. --
  321. CREATE MATERIALIZED VIEW public.mv_stat_legal_entity_total_receptions
  322. WITH (autovacuum_enabled=off) AS
  323.  SELECT receptions.legal_entity_id,
  324.     count(receptions.id) AS counter
  325.    FROM public.receptions
  326.   WHERE (receptions.ehealth_status = 1)
  327.   GROUP BY receptions.legal_entity_id
  328.   WITH NO DATA;
  329. --
  330. -- Name: idx_uq_k4_mv_stat_mv_stat_legal_entity_id_total_receptions; Type: INDEX; Schema: public; Owner: -
  331. --
  332. CREATE INDEX idx_uq_k4_mv_stat_mv_stat_legal_entity_id_total_receptions ON public.mv_stat_legal_entity_total_receptions USING btree (legal_entity_id);
  333.  
  334.  
  335. --
  336. -- Name: mv_stat_legal_entity_total_service_requests; Type: MATERIALIZED VIEW; Schema: public; Owner: -
  337. --
  338. CREATE MATERIALIZED VIEW public.mv_stat_legal_entity_total_service_requests
  339. WITH (autovacuum_enabled=off) AS
  340.  SELECT ehr_direction_service_requests.legal_entity_id,
  341.     count(ehr_direction_service_requests.id) AS counter
  342.    FROM public.ehr_direction_service_requests
  343.   WHERE (((ehr_direction_service_requests.status)::text = ANY (ARRAY[('completed'::character varying)::text, ('active'::character varying)::text])) AND ((ehr_direction_service_requests.category_code)::text = 'counselling'::text))
  344.   GROUP BY ehr_direction_service_requests.legal_entity_id
  345.   WITH NO DATA;
  346. --
  347. -- Name: idx_uq_k4_mv_stat_mv_stat_legal_entity_id_service_requests; Type: INDEX; Schema: public; Owner: -
  348. --
  349. CREATE INDEX idx_uq_k4_mv_stat_mv_stat_legal_entity_id_service_requests ON public.mv_stat_legal_entity_total_service_requests USING btree (legal_entity_id);
  350.  
  351.  
  352. --
  353. -- Name: mv_stat_patient_personalities; Type: MATERIALIZED VIEW; Schema: public; Owner: -
  354. --
  355.  
  356. CREATE MATERIALIZED VIEW public.mv_stat_patient_personalities
  357. WITH (autovacuum_enabled=off) AS
  358.  SELECT user_patients.id,
  359.     personalities.id AS personalities_id,
  360.     declarations.id AS declaration_id,
  361.     declarations.employee_id,
  362.     personalities.gender,
  363.     user_patients.is_smoking,
  364.     personalities.birth_date,
  365.     EXTRACT(year FROM age((personalities.birth_date)::timestamp with time zone)) AS age,
  366.     concat_ws(' '::text, personalities.last_name, personalities.first_name) AS original_name,
  367.     concat_ws(' '::text, personalities.last_name, personalities.first_name, personalities.second_name) AS full_name
  368.    FROM (((public.user_patients
  369.      JOIN public.personalities ON ((user_patients.personality_id = personalities.id)))
  370.      JOIN public.declarations ON ((declarations.personality_id = personalities.id)))
  371.      JOIN public.declaration_statuses ON ((declaration_statuses.id = declarations.declaration_status_id)))
  372.   WHERE (((declaration_statuses.code)::text = 'ACTIVE'::text) AND ((personalities.type)::text = 'Patient::Person'::text))
  373.   WITH NO DATA;
  374. --
  375. -- Name: idx_k4_mv_stat_patient_personalities_age; Type: INDEX; Schema: public; Owner: -
  376. --
  377. CREATE INDEX idx_k4_mv_stat_patient_personalities_age ON public.mv_stat_patient_personalities USING btree (age);
  378. --
  379. -- Name: idx_k4_mv_stat_patient_personalities_age_is_smoking; Type: INDEX; Schema: public; Owner: -
  380. --
  381. CREATE INDEX idx_k4_mv_stat_patient_personalities_age_is_smoking ON public.mv_stat_patient_personalities USING btree (age, is_smoking);
  382. --
  383. -- Name: idx_k4_mv_stat_patient_personalities_employee; Type: INDEX; Schema: public; Owner: -
  384. --
  385. CREATE INDEX idx_k4_mv_stat_patient_personalities_employee ON public.mv_stat_patient_personalities USING btree (id, employee_id);
  386.  
  387.  
  388.  
  389. --
  390. -- Name: mv_stat_receptions_conditions; Type: MATERIALIZED VIEW; Schema: public; Owner: -
  391. --
  392. CREATE MATERIALIZED VIEW public.mv_stat_receptions_conditions
  393. WITH (autovacuum_enabled=off) AS
  394.  SELECT receptions.id,
  395.     receptions.legal_entity_id,
  396.     receptions.patient_id,
  397.     medication_prescriptions.ehealth_status AS medication_prescriptions_ehealth_status,
  398.     medication_prescriptions.program_id AS prescription_program_id,
  399.     classification_icpc2_items.id AS icpc2_code_id
  400.    FROM (((public.receptions
  401.      JOIN public.ehr_conditions ON ((ehr_conditions.encounter_id = receptions.id)))
  402.      LEFT JOIN public.medication_prescriptions ON (((medication_prescriptions.encounter_id = receptions.id) AND (medication_prescriptions.ehealth_status = ANY (ARRAY[0, 1])))))
  403.      JOIN public.classification_icpc2_items ON (((classification_icpc2_items.id = ehr_conditions.icpc2_code_id) AND ((classification_icpc2_items.type)::text = 'Classification::Icpc2::Condition'::text) AND (((classification_icpc2_items.code)::text >= 'K29'::text) AND ((classification_icpc2_items.code)::text <= 'K99'::text)))))
  404.   WHERE (receptions.ehealth_status = 1)
  405.   WITH NO DATA;
  406. --
  407. -- Name: idx_k4_mv_stat_receptions_conditions_legal_patient_id; Type: INDEX; Schema: public; Owner: -
  408. --
  409. CREATE INDEX idx_k4_mv_stat_receptions_conditions_legal_patient_id ON public.mv_stat_receptions_conditions USING btree (legal_entity_id, medication_prescriptions_ehealth_status, patient_id, prescription_program_id, icpc2_code_id, id);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!