Dumped on 2021-05-19

Index of database - cic-dev-db

• core
• ad_audits
• ad_mobile_devices
• ad_mobile_indicators
• ad_tracking
• cd_action_log
• cd_action_log_user
• cd_attachments
• cd_files
• cd_notifications
• cd_points
• cd_results
• cd_route_history
• cd_routes
• cd_settings
• cd_sys_log
• cd_user_points
• cd_userinroutes
• cs_attachment_types
• cs_point_types
• cs_result_types
• cs_route_statuses
• cs_route_types
• cs_setting_types
• pd_accesses
• pd_roles
• pd_userindivisions
• pd_userinroles
• pd_users
• pv_users
• sd_client_errors
• sd_digests
• sd_divisions
• sd_subdivisions
• sd_table_change
• sd_table_change_ref
• sd_table_states
• sv_objects
• sv_users
• cf_mui_cd_attachment(_id uuid)
• cf_mui_cd_attachments(_fn_user integer, _c_version text)
• cf_mui_cd_files(_fn_user integer, _c_version text)
• cf_mui_cd_notifications(_fn_user integer, _c_version text)
• cf_mui_cd_points(_fn_user integer, _c_version text)
• cf_mui_cd_results(_fn_user integer, _c_version text)
• cf_mui_cd_route_history(_fn_user integer, _c_version text)
• cf_mui_cd_routes(_fn_user integer, _c_version text)
• cf_mui_cd_user_points(_fn_user integer, _c_version text)
• cf_mui_cd_userinroutes(_fn_user integer, _c_version text)
• cf_mui_cs_attachment_types(_fn_user integer, _c_version text)
• cf_mui_cs_point_types(_fn_user integer, _c_version text)
• cf_mui_cs_result_types(_fn_user integer, _c_version text)
• cf_mui_cs_route_statuses(_fn_user integer, _c_version text)
• cf_mui_cs_route_types(_fn_user integer, _c_version text)
• cf_mui_pd_users(_fn_user integer, _c_version text)
• cf_mui_sd_digests(_fn_user integer, _c_version text)
• cf_mui_sd_table_change(_fn_user integer, _c_version text)
• cf_update_user_divisions(_user_id integer, _divisions json, _sub_divisions json)
• cft_cd_routes_update_history()
• cft_log_action()
• cft_pd_users_update_version()
• cft_sd_digest_update_version()
• cft_table_state_change_version()
• pf_accesses(n_user_id integer)
• pf_update_user_roles(_user_id integer, _claims json)
• sf_accesses(c_role_name text, n_currentuser integer, c_claims text, n_user_id integer)
• sf_build_version(status integer)
• sf_del_route(_route_id uuid)
• sf_finish_date(_d_date date)
• sf_get_version()
• sf_hard_reset_route(_route_id uuid)
• sf_is_mobile_route(_f_route uuid)
• sf_remove_outdated()
• sf_table_change_update(_c_table_name text, _f_user integer)
• sf_update_version()
• sf_version_to_number(c_version text)
• dbo
• cv_tracking
• ed_blanks
• ed_devices
• ed_input_meter_readings
• ed_output_conn_seals
• ed_output_meter_readings
• ed_output_transformers
• ed_registr_pts
• ed_registr_pts_history
• ed_seals
• ed_transformers
• ed_violation
• es_blank_status
• es_delivery_methods
• es_device_categories
• es_device_location
• es_device_types
• es_energy_types
• es_failure_reason
• es_no_plan_works
• es_notice_trash
• es_phase
• es_precission_classes
• es_reading_statuses
• es_registr_pts_status
• es_restrictions
• es_scales
• es_seal_places
• es_seal_status
• es_seal_types
• es_status_consume
• es_tariff_zones
• es_verification_reason
• es_violations
• es_work_links
• sd_employers
• sd_subscr
• ss_address
• sv_fias_address
• cf_arm_all_subdivisions(_f_user integer)
• cf_arm_assign_route(_f_current_user integer, _users json, _f_route uuid, _b_plan boolean)
• cf_arm_cd_point_update(_items json, _f_point_type integer)
• cf_arm_cd_points(_f_main_division integer, _f_division integer, _f_subdivision integer, _f_manager integer, _f_user integer, _d_date date)
• cf_arm_cd_points(_routes json, _d_date date)
• cf_arm_cd_results(_f_users json, _d_start_date date, _d_end_date date)
• cf_arm_cd_routes(_f_author integer)
• cf_arm_cd_routes(_f_main_division integer, _f_division integer, _f_subdivision integer, _f_manager integer, _f_user integer, _d_date_start date, _d_date_end date)
• cf_arm_cd_routes(_f_user integer, _d_date date)
• cf_arm_close_route(_f_current_user integer, _route_id uuid)
• cf_arm_close_routes(_f_current_user integer, _routes json)
• cf_arm_create_route(_f_current_user integer, _registr_pts json, _d_start date, _d_end date, _c_number text, _users json, _f_route_type integer, _b_draft boolean)
• cf_arm_ed_blank_create(_f_subdivision integer, _c_prefix text, _start bigint, _end bigint, _f_type integer, _f_user_from integer, _f_user_to integer, _f_user_mol integer, _c_notice text)
• cf_arm_ed_blank_update(_f_subdivision integer, _blanks json, _f_user_from integer, _f_user_to integer, _f_status integer, _c_document_trash text, _f_notice_trash integer, _f_user_mol integer, _c_notice text)
• cf_arm_ed_blanks(_id uuid, _f_subdivision integer, _f_user_from integer, _f_user_to integer, _c_prefix text, _n_start_number bigint, _n_end_number bigint, _d_start_send date, _d_end_send date, _d_start_setup date, _d_end_setup date, _d_start_trash date, _d_end_trash date, _f_type integer, _f_user_mol integer, _c_owner text, _f_status integer)
• cf_arm_ed_blanks_remove(_blanks json)
• cf_arm_ed_registr_pts()
• cf_arm_ed_seals(_id uuid, _f_subdivision integer, _f_user_from integer, _f_user_to integer, _c_prefix text, _n_start_number bigint, _n_end_number bigint, _d_start_send date, _d_end_send date, _d_start_setup date, _d_end_setup date, _d_start_trash date, _d_end_trash date, _f_type integer, _f_place integer, _c_device_number text, _f_registr_pts uuid, _f_status integer, _f_user_mol integer)
• cf_arm_ed_seals_create(_f_subdivision integer, _c_prefix text, _start bigint, _end bigint, _f_type integer, _f_user_from integer, _f_user_to integer, _f_user_mol integer, _c_notice text)
• cf_arm_ed_seals_remove(_seals json)
• cf_arm_ed_seals_update(_f_subdivision integer, _seals json, _f_user_from integer, _f_user_to integer, _f_status integer, _c_document_trash text, _f_notice_trash integer, _f_user_mol integer, _c_notice text)
• cf_arm_filtration(_c_ps text, _c_line_ps text, _c_rp text, _c_line_rp text, _c_tp text, _c_line_tp text, _c_line_segment_1 text, _c_raion text, _c_city text, _c_settle text, _c_street text, _c_house text, _c_house_build text, _c_device_location text, _b_askue boolean, _c_askue text, _n_mpi smallint, _f_div integer, _f_subdiv integer, _n_registr_pts_type integer, _c_registr_pts_name text, _f_consume_status integer, _b_problem boolean, _b_violation boolean, _n_check_date integer, _b_person boolean, _f_delivery integer, _d_date_begin date, _d_date_end date, _n_absent_period integer, _b_strong_check boolean, _f_event integer, _d_date_period date, _b_plan boolean, _f_no_plan_work smallint, _f_point_type integer, _b_no_plan boolean, _d_no_plan_date date, _f_registr_pts_status integer, _n_month_last_check_date integer, _f_fias uuid)
• cf_arm_get_statistics_by_day(_f_div integer, _f_subdiv integer, _d_date_begin date, _d_date_end date)
• cf_arm_get_statistics_by_month(_f_div integer, _f_subdiv integer, _d_date_begin date, _d_date_end date)
• cf_arm_get_users_statistics_by_hour(_f_div integer, _f_subdiv integer, _fn_user integer, _d_date_begin date, _d_date_end date)
• cf_arm_pd_user_managers(_f_current_user integer, _f_main_division integer, _f_division integer, _f_subdivision integer)
• cf_arm_pd_user_stat(_fn_user integer, _d_date date)
• cf_arm_pd_users(_f_main_division integer, _f_division integer, _f_subdivision integer, _f_manager integer, _f_user integer)
• cf_arm_pd_users_stat(_f_main_division integer, _f_division integer, _f_subdivision integer, _f_manager integer, _f_user integer, _d_date date)
• cf_arm_remove_route(_route_id uuid)
• cf_arm_remove_routes(_routes json)
• cf_arm_route_details(_f_route uuid)
• cf_arm_sd_divisions(_f_current_user integer, _f_main_division integer)
• cf_arm_sd_main_divisions(_f_current_user integer)
• cf_arm_sd_subdivisions(_f_current_user integer, _f_division integer)
• cf_arm_transfer_route(_f_current_user integer, _route_id uuid)
• cf_arm_transfer_routes(_f_current_user integer, _routes json)
• cf_arm_update_route(_f_route uuid, _registr_pts json, _b_delete boolean)
• cf_arm_update_route(_f_route uuid, _registr_pts_history json)
• cf_mim_create_user(_c_last_name text, _c_first_name text, _f_maindivision integer, _f_division integer, _f_subdivision integer, _c_login text, _c_tab_number text, _c_password text, _c_middle_name text, _c_post_code text, _c_post text, _c_phone text, _c_email text)
• cf_mim_get_user_info(_c_tab_number text)
• cf_mim_get_user_roles_list()
• cf_mim_get_users_list(_f_maindivision integer, _b_disabled boolean, _c_post_code text, _f_role integer)
• cf_mim_update_user(_c_tab_number text, _c_last_name text, _c_first_name text, _c_middle_name text, _c_login text, _c_post_code text, _c_post text, _f_maindivision integer, _f_division integer, _f_subdivision integer, _c_phone text, _c_email text)
• cf_mim_update_user_roles(_c_tab_number text, _f_role integer)
• cf_mim_user_disable(_c_tab_number text, _b_disabled boolean)
• cf_mim_user_pwd_change(_c_tab_number text, _c_new_pwd text)
• cf_mui_ed_blanks(_fn_user integer, _c_version text)
• cf_mui_ed_input_conn_seals(_fn_user integer, _c_version text, _f_point uuid)
• cf_mui_ed_input_meter_readings(_fn_user integer, _c_version text, _f_point uuid)
• cf_mui_ed_input_transformers(_fn_user integer, _c_version text, _f_point uuid)
• cf_mui_ed_output_conn_seals(_fn_user integer, _c_version text, _f_point uuid)
• cf_mui_ed_output_meter_readings(_fn_user integer, _c_version text, _f_result uuid)
• cf_mui_ed_output_transformers(_fn_user integer, _c_version text, _f_point uuid)
• cf_mui_ed_seals(_fn_user integer, _c_version text)
• cf_mui_es_device_location(_fn_user integer, _c_version text)
• cf_mui_es_device_types(_fn_user integer, _c_version text)
• cf_mui_es_energy_types(_fn_user integer, _c_version text)
• cf_mui_es_failure_reason(_fn_user integer, _c_version text)
• cf_mui_es_phase(_fn_user integer, _c_version text)
• cf_mui_es_restrictions(_fn_user integer, _c_version text)
• cf_mui_es_scales(_fn_user integer, _c_version text)
• cf_mui_es_seal_places(_fn_user integer, _c_version text)
• cf_mui_es_seal_types(_fn_user integer, _c_version text)
• cf_mui_es_tariff_zones(_fn_user integer, _c_version text)
• cf_mui_es_verification_reason(_fn_user integer, _c_version text)
• cf_mui_es_violations(_fn_user integer, _c_version text)
• cf_mui_es_work_links(_fn_user integer, _c_version text)
• cf_process_coord_update()
• cf_process_exp_buffers(_session_id uuid, _f_div text, _f_subdiv text, _d_date_begin date, _d_date_end date)
• cf_process_imp_buffers(_session_id uuid)
• cf_process_imp_insert()
• cft_ed_blank_update()
• cft_ed_seal_update()
• rpt_arm_cd_route_stat(_f_route uuid)
• sf_generate_meter_reading(_f_result uuid)
• sf_generate_point_info(_f_registr_pts uuid)
• sf_generate_point_jb_data(_f_registr_pts uuid)
• sf_transfer_output_data()
• sf_update_jb_history(items json, old_json json)
• sf_update_point_data(_f_point uuid)
• uuid_or_null(str text)
• rpt
• cf_doned_work(_d_date_start date, _d_date_end date, _f_main_division text, _f_division text, _f_subdivisions text, _c_substation text, _c_line_substation text, _c_tp text)
• cf_ed_blanks_act(_d_date_start date, _d_date_end date, _f_maindivisions text, _f_divisions text, _f_subdivisions text, _f_user_to text, _f_statuses text)
• cf_ed_blanks_unaccounted(_d_date_start date, _d_date_end date, _f_maindivisions text, _f_divisions text, _f_subdivisions text, _f_user_to text, _f_statuses text)
• cf_ed_meters_to_supplier(_d_date_start date, _d_date_end date, _f_maindivisions text, _f_divisions text, _f_subdivisions text, _subject text)
• cf_ed_seals(_d_date_start date, _d_date_end date, _f_maindivisions text, _f_divisions text, _f_subdivisions text, _f_user_to text, _f_statuses text)
• cf_meters(_d_date_start date, _d_date_end date, _f_main_division text, _f_division text, _f_subdivisions text)
• cf_meters_by_person(_d_date_start date, _d_date_end date, _f_main_division text, _f_division text, _f_subdivisions text)
• cf_route_statistics(_d_date_start date, _d_date_end date, _f_maindivisions text, _f_divisions text, _f_subdivisions text, _c_substation text, _c_line_substation text, _c_tp text, _f_user text, _f_route_types text)
• sf_rpt_csv_to_json(_csv text)

Schema core

Table: core.ad_audits

Действия пользователя

Index - Schema core

Table: core.ad_mobile_devices

Информация о мобильных устройствах

Index - Schema core

Table: core.ad_mobile_indicators

Показатели мобильного устройства

Index - Schema core

Table: core.ad_tracking

Перемещение исполнителя

• ad_tracking_d_date_idx d_date DESC NULLS LAST
• ad_tracking_fn_user_idx fn_user

Index - Schema core

Table: core.cd_action_log

Логирование действий пользователей с данными. Полная информация

Index - Schema core

Table: core.cd_action_log_user

Логирование запросов пользователя

Index - Schema core

Table: core.cd_attachments

Файлы

Index - Schema core

Table: core.cd_files

Файлы

Index - Schema core

Table: core.cd_notifications

Уведомления

Index - Schema core

Table: core.cd_points

Точки

• cd_points_f_route_idx f_route

Index - Schema core

Table: core.cd_results

Результат выполнения

• cd_results_fn_point_idx fn_point
• cd_results_fn_route_fn_user_idx fn_route, fn_user

Index - Schema core

Table: core.cd_route_history

История изменения статусов заданий

Index - Schema core

Table: core.cd_routes

Маршруты

Index - Schema core

Table: core.cd_settings

Настройки

Index - Schema core

Table: core.cd_sys_log

Логирование job

Index - Schema core

Table: core.cd_user_points

Точки

• cd_user_points_fn_route_fn_user_idx fn_route, fn_user

Index - Schema core

Table: core.cd_userinroutes

Исполнители задания

• cd_userinroutes_f_route_idx f_route
• cd_userinroutes_f_user_idx f_user

Index - Schema core

Table: core.cs_attachment_types

Тип изображений

Index - Schema core

Table: core.cs_point_types

Тип точки

Index - Schema core

Table: core.cs_result_types

Тип результат

Index - Schema core

Table: core.cs_route_statuses

Тип результат

Index - Schema core

Table: core.cs_route_types

Тип маршрута

Index - Schema core

Table: core.cs_setting_types

Тип настройки

Index - Schema core

Table: core.pd_accesses

Права доступа

Index - Schema core

Table: core.pd_roles

Роли

Index - Schema core

Table: core.pd_userindivisions

Привязка пользователей к отделению или участку

• pd_userindivisions_f_division_idx f_division
• pd_userindivisions_f_user_idx f_user

Index - Schema core

Table: core.pd_userinroles

Пользователи в ролях

• pd_userinroles_f_user_idx f_user

Index - Schema core

Table: core.pd_users

Пользователи

Index - Schema core

View: core.pv_users

Открытый список пользователей

SELECT t.id
,
    t.f_parent
,
    t.c_login
,
    t.c_claims
,
    t.fn_file
,
    t.c_description
,
    t.c_first_name
,
    t.c_last_name
,
    t.c_middle_name
,
    t.c_imei
,
    t.c_phone
,
    t.c_email
,
    t.c_version
,
    t.n_version
,
    t.b_disabled
,
        CASE
            WHEN 
(t.c_main_divisions = '..'::text) THEN ''::text
            ELSE t.c_main_divisions
        END AS c_main_divisions
,
        CASE
            WHEN 
(t.c_divisions = '..'::text) THEN ''::text
            ELSE t.c_divisions
        END AS c_divisions
,
        CASE
            WHEN 
(t.c_subdivisions = '..'::text) THEN ''::text
            ELSE t.c_subdivisions
        END AS c_subdivisions
   
FROM ( WITH items AS 
     (
                 
      SELECT uid.f_user
           ,
                    COALESCE
           (d.f_division
                 , uid.f_division
           ) AS f_main_division
           ,
                        CASE
                            WHEN 
           (d.f_division IS NOT NULL) THEN uid.f_division
                            ELSE NULL::integer
                        END AS f_division
           ,
                    uid.f_subdivision
                   
        FROM (core.pd_userindivisions uid
                     
              JOIN core.sd_divisions d 
                ON (
                       (uid.f_division = d.id)
                 )
           )
                  
    ORDER BY uid.f_user
                
     )
         
SELECT u.id
     ,
            u.f_parent
     ,
            u.c_login
     ,
            concat
     ('.'
           , (
            SELECT string_agg
                 (t_1.c_name
                       ,'.'::text
                 ) AS string_agg
                   
              FROM (
                  SELECT r.c_name
                           
                    FROM (core.pd_userinroles uir
                             
                          JOIN core.pd_roles r 
                            ON (
                                   (uir.f_role = r.id)
                             )
                       )
                          
                   WHERE (uir.f_user = u.id)
                          
                ORDER BY r.n_weight DESC
                 ) t_1
           )
           ,'.'
     ) AS c_claims
     ,
            u.fn_file
     ,
            u.c_description
     ,
            u.c_first_name
     ,
            u.c_last_name
     ,
            u.c_middle_name
     ,
            u.c_imei
     ,
            u.c_phone
     ,
            u.c_email
     ,
            u.c_version
     ,
            u.n_version
     ,
            u.b_disabled
     ,
            concat
     ('.'
           , (
            SELECT string_agg
                 (
                       (t_1.f_main_division)::text
                       ,'.'::text
                 ) AS string_agg
                   
              FROM (
                  SELECT DISTINCT i.f_main_division
                           
                    FROM items i
                          
                   WHERE (
                             (i.f_user = u.id)
                           AND (i.f_main_division IS NOT NULL)
                       )
                 ) t_1
           )
           ,'.'
     ) AS c_main_divisions
     ,
            concat
     ('.'
           , (
            SELECT string_agg
                 (
                       (t_1.f_division)::text
                       ,'.'::text
                 ) AS string_agg
                   
              FROM (
                  SELECT DISTINCT i.f_division
                           
                    FROM items i
                          
                   WHERE (
                             (i.f_user = u.id)
                           AND (i.f_division IS NOT NULL)
                       )
                 ) t_1
           )
           ,'.'
     ) AS c_divisions
     ,
            concat
     ('.'
           , (
            SELECT string_agg
                 (
                       (t_1.f_subdivision)::text
                       ,'.'::text
                 ) AS string_agg
                   
              FROM (
                  SELECT DISTINCT i.f_subdivision
                           
                    FROM items i
                          
                   WHERE (
                             (i.f_user = u.id)
                           AND (i.f_subdivision IS NOT NULL)
                       )
                 ) t_1
           )
           ,'.'
     ) AS c_subdivisions
           
  FROM core.pd_users u
          
 WHERE (u.sn_delete = false)
) t;

Index - Schema core

Table: core.sd_client_errors

Клиентские ошибки

Index - Schema core

Table: core.sd_digests

Журнал версий

Index - Schema core

Table: core.sd_divisions

Отделения

Index - Schema core

Table: core.sd_subdivisions

Участки

Index - Schema core

Table: core.sd_table_change

Изменение состояния таблицы

Index - Schema core

Table: core.sd_table_change_ref

Зависимость таблиц состояний

Index - Schema core

Table: core.sd_table_states

Состояние таблиц

Index - Schema core

View: core.sv_objects

SELECT table1.table_name
,
    table1.table_type
,
    table1.table_title
,
    table1.primary_key
,
    table1.table_comment
,
    table1.table_schema
   
FROM (
SELECT (t.table_name)::character varying AS table_name
     ,
            
     (t.table_type)::character varying AS table_type
     ,
            
     (pgd.description)::character varying AS table_title
     ,
            
     (cc.column_name)::character varying AS primary_key
     ,
            ''::character varying AS table_comment
     ,
            t.table_schema
           
  FROM (
           (
                 (
                       (information_schema.tables t
             
                     LEFT JOIN pg_statio_all_tables st 
                            ON (
                                   (st.relname = 
                                         (t.table_name)::name
                                   )
                             )
                       )
             
               LEFT JOIN pg_description pgd 
                      ON (
                             (
                                   (pgd.objoid = st.relid)
                                 AND (pgd.objsubid = 0)
                             )
                       )
                 )
             
         LEFT JOIN information_schema.table_constraints tc 
                ON (
                       (
                             (
                                   (t.table_name)::text = 
                                   (tc.table_name)::text
                             )
                           AND (
                                   (t.table_catalog)::text = 
                                   (tc.table_catalog)::text
                             )
                       )
                 )
           )
             
   LEFT JOIN information_schema.constraint_column_usage cc 
          ON (
                 (
                       (tc.constraint_name)::text = 
                       (cc.constraint_name)::text
                 )
           )
     )
          
 WHERE (
           (
                 (t.table_catalog)::text = 
                 (current_database
                       ()
                 )::text
           )
         AND (
                 (tc.constraint_type)::text = 'PRIMARY KEY'::text
           )
     )
        
 UNION
         
SELECT (t.table_name)::character varying AS table_name
     ,
            
     (t.table_type)::character varying AS table_type
     ,
            
     (pgd.description)::character varying AS table_title
     ,
            ''::character varying AS primary_key
     ,
            ''::character varying AS table_comment
     ,
            t.table_schema
           
  FROM (
           (information_schema.tables t
             
         LEFT JOIN pg_class pgc 
                ON (
                       (pgc.relname = 
                             (t.table_name)::name
                       )
                 )
           )
             
   LEFT JOIN pg_description pgd 
          ON (
                 (pgd.objoid = pgc.oid)
           )
     )
          
 WHERE (
           (
                 (t.table_type)::text = 'VIEW'::text
           )
         AND (
                 (t.table_catalog)::text = 
                 (current_database
                       ()
                 )::text
           )
     )
        
 UNION
         
SELECT (r.routine_name)::character varying AS table_name
     ,
            
     (r.routine_type)::character varying AS table_type
     ,
            
     (pgd.description)::character varying AS table_title
     ,
            ''::character varying AS primary_key
     ,
            ''::character varying AS table_comment
     ,
            r.routine_schema AS table_schema
           
  FROM (
           (information_schema.routines r
             
         LEFT JOIN pg_proc pgp 
                ON (
                       (pgp.proname = 
                             (r.routine_name)::name
                       )
                 )
           )
             
   LEFT JOIN pg_description pgd 
          ON (
                 (pgd.objoid = pgp.oid)
           )
     )
          
 WHERE (
           (r.routine_catalog)::text = 
           (current_database
                 ()
           )::text
     )
) table1
  
WHERE (
     (
           (table1.table_schema)::text <> 'pg_catalog'::text
     )
   AND (
           (table1.table_schema)::text <> 'information_schema'::text
     )
   AND (
           (table1.table_schema)::text <> 'public'::text
     )
);

Index - Schema core

View: core.sv_users

Системный список пользователей

SELECT u.id
,
    u.f_parent
,
    u.c_login
,
    u.c_first_name
,
    u.c_last_name
,
    u.c_middle_name
,
    concat
(u.c_first_name
     ,' '
     , u.c_last_name
     ,' '
     , u.c_middle_name
) AS c_user_name
,
    u.c_password
,
    u.s_salt
,
    u.s_hash
,
    u.b_pgcrypto
,
    concat
('.'
     , (
      SELECT string_agg
           (t.c_name
                 ,'.'::text
           ) AS string_agg
           
        FROM (
            SELECT r.c_name
                   
              FROM (core.pd_userinroles uir
                     
                    JOIN core.pd_roles r 
                      ON (
                             (uir.f_role = r.id)
                       )
                 )
                  
             WHERE (uir.f_user = u.id)
                  
          ORDER BY r.n_weight DESC
           ) t
     )
     ,'.'
) AS c_claims
,
    
( WITH RECURSIVE tab_rec AS 
     (
                 
      SELECT sd_divisions.id AS id_parent
           ,
                    sd_divisions.id
           ,
                    sd_divisions.f_division
           ,
                    1 AS num
                   
        FROM core.sd_divisions
                  
       WHERE (sd_divisions.id IN 
                 (
                  SELECT pud.f_division
                           
                    FROM core.pd_userindivisions pud
                          
                   WHERE (pud.f_user = u.id)
                 )
           )
                
   UNION ALL
                 
      SELECT r_2.id_parent
           ,
                    d.id
           ,
                    d.f_division
           ,
                    
           (r_2.num + 1) AS num
                   
        FROM core.sd_divisions d
           ,
                    tab_rec r_2
                  
       WHERE (r_2.id = d.f_division)
                
     )
         
SELECT string_agg
     (
           (r_1.id)::text
           ,','::text
     ) AS string_agg
           
  FROM tab_rec r_1
) AS c_all_divisions
,
    u.b_disabled
,
    u.c_version
,
    u.n_version
   
FROM core.pd_users u
  
WHERE (u.sn_delete = false);

Index - Schema core

Function: core.cf_mui_cd_attachment(_id uuid)

Returns: SET OF record

Language: PLPGSQL

Получение "Вложения" мобильным приложением

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_cd_attachments", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN
	RETURN QUERY select ad.c_name, f.c_mime, a.d_date, a.n_longitude, a.n_latitude, a.ba_data, f.ba_data as ba_file
	from core.cd_attachments as a
	INNER JOIN core.cd_files as f ON f.id = a.fn_file
	inner join core.cd_results as r ON a.fn_result = r.id
	inner join core.cd_points as p on r.fn_point = p.id
	inner join dbo.ed_registr_pts as rp ON rp.id = p.f_registr_pts
	inner join dbo.ss_address as ad ON ad.id = rp.f_address
	where a.id = _id;
END

Function: core.cf_mui_cd_attachments(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Получение списка "Вложений" мобильным приложением

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_cd_attachments", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN
	IF _fn_user IS NOT NULL THEN	
		RETURN QUERY select a.id, f.c_name, f.c_mime, a.fn_type, a.fn_result, a.fn_route, a.fn_point, a.d_date, a.n_longitude, a.n_latitude, a.fn_file, a.c_notice, a.jb_data::text, a.dx_created, a.ba_data, a.n_size, null::bytea as ba_file, f.n_size as n_file_size
		from core.cd_userinroutes as uir
		INNER JOIN core.cd_attachments as a ON a.fn_route = uir.f_route
		INNER JOIN core.cd_files as f ON f.id = a.fn_file
		inner join core.cd_results as r ON r.id = a.fn_result
		where uir.f_user = _fn_user and r.fn_user = _fn_user and core.sf_is_mobile_route(uir.f_route);	
	ELSE
		RETURN QUERY select a.id, f.c_name, f.c_mime, a.fn_type, a.fn_result, a.fn_route, a.fn_point, a.d_date, a.n_longitude, a.n_latitude, a.fn_file, a.c_notice, a.jb_data::text, a.dx_created, a.ba_data, a.n_size, null::bytea as ba_file, f.n_size as n_file_size
		from core.cd_attachments as a
		INNER JOIN core.cd_files as f ON f.id = a.fn_file;
	END IF;
END

Function: core.cf_mui_cd_files(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Получение списка "Файлов" мобильным приложением

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_cd_files", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN
	IF _fn_user IS NOT NULL THEN
		-- для файла специально указываем null, чтобы телефон не обрабатывал данные 
		RETURN QUERY select f.id, f.c_name, f.d_date, f.c_mime, f.c_extension, f.jb_data::text, null::bytea, f.n_size, f.dx_created 
		from core.cd_userinroutes as uir
		INNER JOIN core.cd_attachments as a ON a.fn_route = uir.f_route
		INNER JOIN core.cd_files as f ON f.id = a.fn_file
		inner join core.cd_results as r ON r.id = a.fn_result
		where uir.f_user = _fn_user and r.fn_user = _fn_user and core.sf_is_mobile_route(uir.f_route);
		
		--RETURN QUERY select f.id, f.c_name, f.d_date, f.c_mime, f.c_extension, f.jb_data::text, null::bytea, f.n_size, f.dx_created 
		--from core.cd_files as f
		--inner join core.cd_attachments as a ON a.fn_file = f.id
		--inner join core.cd_routes as r ON a.fn_route = r.id
		--inner join core.cd_results as rr ON rr.id = a.fn_result
		--where rr.fn_user = _fn_user or core.sf_is_mobile_route(r.id);
		
		--UNION
		
		--select f.id, f.c_name, f.d_date, f.c_mime, f.c_extension, f.jb_data::text, f.ba_data, f.n_size, f.dx_created 
		--from core.cd_files as f
		--where f.id in (select u.fn_file from core.pd_users as u where u.id = _fn_user) or 
			 --f.id in (select fb.fn_question_file from core.cd_feedbacks as fb where fb.fn_user = _fn_user and fb.fn_question_file is not null)
			--or f.id in (select fb.fn_answer_file from core.cd_feedbacks as fb where fb.fn_user = _fn_user and fb.fn_answer_file is not null);
	ELSE
		RETURN QUERY select f.id, f.c_name, f.d_date, f.c_mime, f.c_extension, f.jb_data::text, null::bytea, f.n_size, f.dx_created 
		from core.cd_files as f;
	END IF;
END

Function: core.cf_mui_cd_notifications(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Получение списка "Уведомлений" мобильным приложением

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_cd_notifications", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN
    RETURN QUERY 
	select n.id, n.c_title, n.c_message, n.d_date
	from core.cd_notifications as n
    where n.fn_user_to = _fn_user or n.fn_user_from = _fn_user
	order by n.d_date desc;
END

Function: core.cf_mui_cd_points(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Получение списка "Точек маршрута" мобильным приложением

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_cd_points", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN
    RETURN QUERY select p.id, p.f_registr_pts, p.f_route, p.f_type, p.c_notice, p.c_info, p.jb_data::text, p.dx_created, p.n_order, p.b_anomaly, p.sn_delete
    from core.cd_userinroutes as uir
    LEFT JOIN core.cd_points as p ON p.f_route = uir.f_route
    INNER JOIN core.cd_routes as r ON r.id = p.f_route
	left join core.cs_route_statuses as rs ON r.f_status = rs.id
	where uir.f_user = _fn_user and core.sf_is_mobile_route(r.id);
END

Function: core.cf_mui_cd_results(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Получение списка "Результаты обхода" мобильным приложением

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_cd_results", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN
	RETURN QUERY select r.id, r.fn_route, r.fn_point, r.fn_user_point, r.fn_type, r.fn_user, r.d_date, r.c_notice, r.b_warning, r.jb_data::text, r.dx_created, r.b_check, coalesce(r.n_manual_longitude, 0.0), coalesce(r.n_manual_latitude, 0.0)
    from core.cd_userinroutes as uir
    LEFT JOIN core.cd_routes as rt ON rt.id = uir.f_route
    INNER JOIN core.cd_results as r ON r.fn_route = rt.id
	left join core.cs_route_statuses as rs ON rt.f_status = rs.id
	where uir.f_user = _fn_user and r.fn_user = _fn_user and core.sf_is_mobile_route(rt.id);
END

Function: core.cf_mui_cd_route_history(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Получение списка "Истории маршрутов" мобильным приложением

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_cd_route_history", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN
    RETURN QUERY select rh.id, rh.fn_route, rh.fn_status, rh.fn_user, rh.d_date, rh.c_notice, rh.dx_created
    from core.cd_userinroutes as uir
	LEFT JOIN core.cd_route_history as rh ON rh.fn_route = uir.f_route
	where uir.f_user = _fn_user and core.sf_is_mobile_route(uir.f_route);
END

Function: core.cf_mui_cd_routes(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Получение списка "Маршрутов" мобильным приложением

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_cd_routes", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN
    RETURN QUERY select r.id, r.f_type, r.c_number, r.d_date, r.d_date_start, r.d_date_end, r.c_notice, r.b_extended, r.d_extended, r.jb_data::text, r.dx_created,
	(select count(*) from core.cd_points as p where p.f_route = r.id) as n_count, r.n_order, r.b_draft 
    from core.cd_userinroutes as uir
    INNER JOIN core.cd_routes as r ON r.id = uir.f_route
	left join core.cs_route_statuses as rs ON r.f_status = rs.id
	where uir.f_user = _fn_user and core.sf_is_mobile_route(r.id); -- не старше текущей даты
END

Function: core.cf_mui_cd_user_points(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Получение списка "Пользовательских точек" мобильным приложением

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_cd_user_points", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN
    RETURN QUERY select up.id, up.fn_point, up.fn_user, u.c_login as c_user, up.fn_route, up.fn_type, up.n_longitude, up.n_latitude, up.c_notice, up.jb_data::text, up.d_date_check, up.dx_created, up.d_date, up.c_telephone, false
    from core.cd_userinroutes as uir
    LEFT JOIN core.cd_routes as rt ON rt.id = uir.f_route
    INNER JOIN core.cd_user_points as up ON up.fn_route = rt.id
    INNER JOIN core.pd_users as u ON up.fn_user = u.id
	left join core.cs_route_statuses as rs ON rt.f_status = rs.id
	where uir.f_user = _fn_user and up.fn_user = _fn_user and core.sf_is_mobile_route(rt.id);
END

Function: core.cf_mui_cd_userinroutes(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Получение списка "пользователей в маршрутах" мобильным приложением

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_cd_userinroutes", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN
    RETURN QUERY select * from core.cd_userinroutes as cuir
    where cuir.f_route in (select DISTINCT r.id from core.cd_userinroutes as uir
    INNER JOIN core.cd_routes as r ON r.id = uir.f_route
	left join core.cs_route_statuses as rs ON r.f_status = rs.id
	where uir.f_user = _fn_user and core.sf_is_mobile_route(r.id));
END

Function: core.cf_mui_cs_attachment_types(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Типы вложений

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_cd_attachment_types", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN	
	RETURN QUERY select at.id, at.c_name, at.c_const, at.n_order, at.b_default
	from core.cs_attachment_types as at
	where at.b_disabled = false;
END

Function: core.cf_mui_cs_point_types(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Тип точки

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_cs_point_types", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN	
	RETURN QUERY select pt.id, pt.c_name, pt.c_const, pt.n_order
	from core.cs_point_types as pt
	where pt.b_disabled = false;
END

Function: core.cf_mui_cs_result_types(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Тип результат

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_cs_result_types", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN	
	RETURN QUERY select rt.id, rt.c_name, rt.c_short_name, rt.c_const, rt.n_order
	from core.cs_result_types as rt
	where rt.b_disabled = false;
END

Function: core.cf_mui_cs_route_statuses(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Тип результат

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_cs_route_statuses", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN	
	RETURN QUERY select rs.id, rs.c_name, rs.c_const, rs.n_order
	from core.cs_route_statuses as rs
	where rs.b_disabled = false;
END

Function: core.cf_mui_cs_route_types(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Тип маршрута

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_cs_route_types", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN	
	RETURN QUERY select rt.id, rt.c_name, rt.c_const, rt.n_order
	from core.cs_route_types as rt
	where rt.b_disabled = false;
END

Function: core.cf_mui_pd_users(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Получение списка пользователей мобильным приложением

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_pd_users", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN
    RETURN QUERY select u.id, u.f_parent, u.c_login, u.fn_file, concat(u.c_last_name, ' ', u.c_first_name, ' ', u.c_middle_name), u.c_description, u.b_disabled,
	(select string_agg(d.c_phone, ', ') from core.pd_userindivisions as uid
	inner join core.sd_divisions as d on uid.f_division = d.id
	where uid.f_user = _fn_user)
	from core.pd_users as u
    where u.id in (
    select distinct cuir.f_user from core.cd_userinroutes as cuir
    where cuir.f_route in (
    select r.id 
    from core.cd_userinroutes as uir
    LEFT JOIN core.cd_routes as r ON r.id = uir.f_route
    where uir.f_user = _fn_user and core.sf_is_mobile_route(r.id))) OR u.id = _fn_user;
END

Function: core.cf_mui_sd_digests(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Получение списка версий мобильным приложением

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_sd_digests", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
BEGIN
    RETURN QUERY select d.id, d.c_version, d.c_description, d.dx_created, d.n_version from core.sd_digests as d;
END

Function: core.cf_mui_sd_table_change(_fn_user integer, _c_version text)

Returns: SET OF record

Language: PLPGSQL

Получение списка изменений для пользователя

/**
* @params {integer} _fn_user - идентификатор пользователя
* @params {text} _c_version - текущая версия приложения
*
* @example
* [{ "action": "cf_mui_sd_table_change", "method": "Select", "data": [{ "params": [_fn_user, _c_version] }], "type": "rpc", "tid": 0 }]
*/
DECLARE
	_b_change boolean;
BEGIN
	select false into _b_change; -- значение по умолчанию

	select (case when c_value = 'true' then true else false end) into _b_change 
	from core.cd_settings
	where c_key = 'MBL_TABLE_CHANGE'
	limit 1;

	IF _b_change then 
		return query 
		select 
			tc.c_table_name,
			max(coalesce(tc.n_change, 0.0)) as n_change,
			max(tc.f_user) as f_user
		from core.sd_table_change as tc
		where tc.f_user is null or tc.f_user = _fn_user
		group by tc.c_table_name;
	else
		return query select null::text, 0.0::double precision, null::integer from core.sd_table_change as tc
		where tc.c_table_name = 'unknow';
	end if;
END

Function: core.cf_update_user_divisions(_user_id integer, _divisions json, _sub_divisions json)

Returns: integer

Language: PLPGSQL

Обновление Филиала/Отделения/Участка у пользователя

/**
* @params {integer} _user_id - идент. пользователя
* @params {json} _divisions - список отделений. Формат строки '[1,2]'
* @params {json} _sub_divisions - список участков. Формат строки '[1,2]'
*
* @returns {integer}
* 0 - ОК
*
* @example
* [{ "action": "cf_update_user_divisions", "method": "Query", "data": [{ "params": [_user_id, _divisions, _sub_divisions] }], "type": "rpc", "tid": 0 }]
*/
BEGIN
	delete from core.pd_userindivisions
	where f_user = _user_id;

	-- проверяем divisions
	IF (select count(*) from json_array_elements_text(_divisions)) > 0 THEN
		insert into core.pd_userindivisions(f_user, f_division, f_subdivision)
		SELECT _user_id, t.value::integer, null 
		FROM json_array_elements_text(_divisions) as t;
	END IF;
	
	-- проверяем sub_divisions
	IF (select count(*) from json_array_elements_text(_sub_divisions)) > 0 THEN
		insert into core.pd_userindivisions(f_user, f_division, f_subdivision)
		SELECT _user_id, (select f_division from core.sd_subdivisions where id = t.value::integer), t.value::integer 
		FROM json_array_elements_text(_sub_divisions) as t;
	END IF;
	
	RETURN 0;
END

Function: core.cft_cd_routes_update_history()

Returns: trigger

Language: PLPGSQL

Триггер. Обновление истории в маршрутах

DECLARE
	_f_route uuid;
	_f_status integer;
	_n_status_weight integer;
	_f_user integer;
BEGIN
	-- все вычисления производяться после выполнения операций
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
		select NEW.fn_route into _f_route;
		select NEW.fn_user into _f_user;
	ELSE	
		select OLD.fn_route into _f_route;
    END IF;
    
    select rs.id, rs.n_order into _f_status, _n_status_weight from core.cd_route_history as rh 
	inner join core.cs_route_statuses as rs ON rs.id = rh.fn_status
	where rh.fn_route = _f_route 
	order by rh.d_date desc, rs.n_order desc limit 1;
    
    IF ((_n_status_weight <= 1 OR _n_status_weight >= 1000) OR -- если статус меньше чем 1 или больше 1000, то это Диспетчер 
		(select count(*) from core.cd_userinroutes as uir
    	where uir.f_route = _f_route and uir.f_user = _f_user and uir.b_main) >= 1) THEN -- если это главный обходчик
    
		update core.cd_routes as r
		set f_status = _f_status
		where r.id = _f_route;
	
	END IF;

    RETURN NULL; -- result is ignored since this is an AFTER trigger
END

Function: core.cft_log_action()

Returns: trigger

Language: PLPGSQL

Триггер. Процедура логирования действия пользователя

BEGIN
	IF (TG_OP = 'UPDATE') THEN
		INSERT INTO core.cd_action_log(c_table_name, c_operation, jb_old_value, jb_new_value, c_user, d_date)
		VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), USER, clock_timestamp());
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
		INSERT INTO core.cd_action_log(c_table_name, c_operation, jb_old_value, c_user, d_date)
		VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), USER, clock_timestamp());
        RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
		INSERT INTO core.cd_action_log(c_table_name, c_operation, jb_new_value, c_user, d_date)
		VALUES (TG_TABLE_NAME, TG_OP, row_to_json(NEW), USER, clock_timestamp());
	    RETURN NEW;
    ELSE
        RETURN OLD;
	END IF;

EXCEPTION
	WHEN OTHERS
    THEN
		INSERT INTO core.cd_sys_log(d_timestamp, c_descr)
		VALUES(clock_timestamp(), 'Непредвиденная ошибка логирования');
    RETURN OLD;
END;

Function: core.cft_pd_users_update_version()

Returns: trigger

Language: PLPGSQL

Триггер. Обновление версии в пользователях

BEGIN
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
		UPDATE core.pd_users as u
		set n_version = core.sf_version_to_number(NEW.c_version),
		c_version = NEW.c_version
		where u.id = NEW.fn_user;
    END IF;

    RETURN NEW;
END

Function: core.cft_sd_digest_update_version()

Returns: trigger

Language: PLPGSQL

Триггер. Обновления версии приложения

BEGIN
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
		NEW.n_version = core.sf_version_to_number(NEW.c_version);
    END IF;

    RETURN NEW;
END

Function: core.cft_table_state_change_version()

Returns: trigger

Language: PLPGSQL

Триггер. Обновление справочной версии

DECLARE
	_users json;
BEGIN
	if TG_OP = 'DELETE' and to_jsonb(OLD) ? 'fn_user' then
		select concat('[{"f_user":', OLD.fn_user::text ,'}]')::json into _users;
	else
		if TG_OP != 'DELETE' and to_jsonb(NEW) ? 'fn_user' then
			select concat('[{"f_user":', NEW.fn_user::text ,'}]')::json into _users;
		else
			select '[{"f_user":null}]'::json into _users;
		end if;
	end if;
			
	perform core.sf_table_change_update(t.c_table_name_ref, (u.value#>>'{f_user}')::integer)
	from json_array_elements(_users) as u
	left join (select TG_TABLE_NAME as c_table_name_ref
				UNION
				select c_table_name_ref
				from core.sd_table_change_ref
				where c_table_name = TG_TABLE_NAME) as t on 1=1;

    RETURN null;
END

Function: core.pf_accesses(n_user_id integer)

Returns: SET OF record

Language: PLPGSQL

Системная функция. Получение прав доступа для пользователя. Используется NodeJS

/**
* @params {integer} n_user_id - иден. пользователя
*/
BEGIN
	RETURN QUERY select * from (select 
        a.c_name,
        a.c_criteria,
        a.c_path,
        a.c_function,
        a.c_columns,
        a.b_editable, 
        a.b_deletable, 
        a.b_creatable, 
        a.b_full_control, 
        core.sf_accesses(r.c_name, u.id, u.c_claims, a.f_user) as access 
    from core.pd_accesses as a
    left join core.sv_users as u on n_user_id = u.id
    left join core.pd_roles as r on a.f_role = r.id
    where a.sn_delete = false) as t 
	where t.access > 0;
END; 

Function: core.pf_update_user_roles(_user_id integer, _claims json)

Returns: integer

Language: PLPGSQL

Обновление ролей у пользователя

/**
* @params {integer} _user_id - идент. пользователя
* @params {json} _claims - роли в виде строки '["manager", "inspector"]'
*
* @returns {integer} идент. пользователя
*/
BEGIN
	delete from core.pd_userinroles
	where f_user = _user_id;

	insert into core.pd_userinroles(f_user, f_role, sn_delete)
	SELECT _user_id, (select id from core.pd_roles where t.value = c_name), false 
	FROM json_array_elements_text(_claims) as t;
	
	RETURN _user_id;
END

Function: core.sf_accesses(c_role_name text, n_currentuser integer, c_claims text, n_user_id integer)

Returns: integer

Language: PLPGSQL

Системная функция для обработки прав. Для внешнего использования не применять

/**
* @params {text} c_role_name - имя роли в безопасности
* @params {integer} n_currentuser - идент. пользователя в безопасности
* @params {text} c_claims - список ролей
* @params {integer} n_user_id - иден. пользователя
* 
* @returns
* 0 - доступ запрещен
*/
BEGIN
    IF c_role_name is null and n_user_id is null then
		RETURN 1;
	ELSEIF (c_role_name is not null and c_claims is not null and POSITION(CONCAT('.', c_role_name, '.') IN c_claims) > 0) then
		RETURN 2;
	ELSEIF (c_role_name is not null and c_claims is not null and POSITION(CONCAT('.', c_role_name, '.') IN c_claims) > 0) then  
		RETURN 3;
	ELSEIF (c_role_name is null and n_currentuser = n_user_id) then
        RETURN 4;
    ELSEIF (c_role_name = 'anonymous' or n_user_id = -1) then
		RETURN 5;
	else
		RETURN 0;
	end if;
 END

Function: core.sf_build_version(status integer)

Returns: text

Language: PLV8

Генерация версии БД

var birthday = '2020-11-09';
	var newVersion = '1.' + Math.floor(Math.abs(new Date().getTime() - new Date(birthday).getTime()) / (1000 * 3600 * 24)) + '.' + status + '.'
											  + ((new Date().getHours() * 60) + new Date().getMinutes());
	return newVersion;

Function: core.sf_del_route(_route_id uuid)

Returns: integer

Language: PLPGSQL

Окончательное удаление маршрута без сохранения истории

/**
* @params {uuid} _route_id - идентификатор маршрута
* 
* @returns {integer} 0 - маршрут удален, 1 - ошибка при удалении
*/
DECLARE
	_result integer;
BEGIN	
	delete from core.cd_files
	where id IN (select fn_file from core.cd_attachments
	where fn_route = _route_id);
	
	delete from core.cd_attachments
	where fn_route = _route_id;

	delete from core.cd_results
	where fn_route = _route_id;
	
	delete from core.cd_user_points
	where fn_route = _route_id;

	delete from core.cd_route_history
	where fn_route = _route_id;
	
	delete from core.cd_userinroutes
	where f_route = _route_id;
	
	delete from dbo.ed_output_meter_readings
	where fn_route = _route_id;	
	
	delete from core.cd_points
	where f_route = _route_id;

	delete from core.cd_routes
	where id = _route_id;
	
  	RETURN 0;
END

Function: core.sf_finish_date(_d_date date)

Returns: boolean

Language: PLPGSQL

Проверка даты завершения маршрута, true - маршрут активный

/**
* @params {date} _d_date - дата
* @returns {boolean} false - маршрут завершен
*/
BEGIN
	RETURN (_d_date + interval '1 day') >= now();
END

Function: core.sf_get_version()

Returns: text

Language: PLPGSQL

Версия БД

/**
* @returns {text} версия базы данных
*/
DECLARE
	_ver text;
BEGIN
	SELECT c_value INTO _ver FROM core.cd_settings WHERE lower(c_key) = lower('DB_VERSION');
	RETURN _ver;
END

Function: core.sf_hard_reset_route(_route_id uuid)

Returns: integer

Language: PLPGSQL

Сброс результатов прохождния маршрута. Системная функция, не применять на production

/**
* @params {uuid} _route_id - идентификатор маршрута
* 
* @returns {integer} 0 - маршрут очищен
*/
BEGIN
	delete from dbo.ed_output_meter_readings
	where fn_route = _route_id;
	
	delete from dbo.ed_output_conn_seals
	where fn_route = _route_id;
	
	delete from core.cd_files
	where id IN (select fn_file from core.cd_attachments
	where fn_route = _route_id);
	
	delete from core.cd_attachments
	where fn_route = _route_id;
	
	delete from core.cd_results
	where fn_route = _route_id;
	
	delete from core.cd_user_points
	where fn_route = _route_id;

	delete from core.cd_route_history
	where fn_route = _route_id and fn_status not IN (2,3);
	
	delete from core.cd_points
	where f_route = _route_id and b_anomaly = true;
	
	RETURN 0;
END

Function: core.sf_is_mobile_route(_f_route uuid)

Returns: boolean

Language: PLPGSQL

Является ли маршрут доступным для мобильного устройства

/**
* @params {uuid} _f_route - иден. маршрута
*
* @returns {boolean} true - маршрут доступен для мобильного устройства
*/
BEGIN
	IF (select count(*) from core.cd_routes as r
		inner join core.cs_route_statuses as rs ON r.f_status = rs.id
		where r.id = _f_route and rs.n_order > 0 and core.sf_finish_date(CASE WHEN r.b_extended = true THEN r.d_extended ELSE r.d_date_end END)) = 1 THEN
		RETURN true;
	ELSE
		RETURN false;	
	END IF;
END

Function: core.sf_remove_outdated()

Returns: void

Language: PLPGSQL

Процедура очистки устаревших данных

/**
* системная функция должна выполнять от postgres
*/
DECLARE
    _n_val      integer = 100;
    _d_data     timestamptz = date_trunc('day', now()) + '1 day'::interval; -- начало следующего дня
	_dg_cnt		integer; --diagnostic
	_dg_text	text = '';
BEGIN

    -- опрос настройки
    BEGIN
        select
            c_value::integer
        into _n_val
        from core.cd_settings
        where c_key = 'ALL_DEL_AFTER';

    --в любых непонятных ситуациях берем 100 дней
    EXCEPTION
        WHEN OTHERS
        THEN _n_val = 100;
    END;

    _d_data  = _d_data - _n_val * '1 day'::interval;

    delete from core.ad_audits where d_date < _d_data;
	get diagnostics _dg_cnt = row_count;
	_dg_text = _dg_text || ' core.ad_audits: удалено '|| _dg_cnt::text || E'\n';

    delete from core.ad_mobile_devices where d_date < _d_data;
	get diagnostics _dg_cnt = row_count;
	_dg_text = _dg_text || ' core.ad_mobile_devices: удалено '|| _dg_cnt::text || E'\n';

    delete from core.ad_mobile_indicators where d_date < _d_data;
	get diagnostics _dg_cnt = row_count;
	_dg_text = _dg_text || ' core.ad_mobile_indicators: удалено '|| _dg_cnt::text || E'\n';

    delete from core.cd_notifications where d_date < _d_data;
	get diagnostics _dg_cnt = row_count;
	_dg_text = _dg_text || ' core.cd_notifications: удалено '|| _dg_cnt::text || E'\n';

	delete from core.cd_action_log where d_date < _d_data;
	get diagnostics _dg_cnt = row_count;
	_dg_text = _dg_text || ' core.cd_action_log: удалено '|| _dg_cnt::text || E'\n';
	
	delete from core.cd_action_log_user where d_date < _d_data;
	get diagnostics _dg_cnt = row_count;
	_dg_text = _dg_text || ' core.cd_action_log_user: удалено '|| _dg_cnt::text || E'\n';
	
	delete from core.sd_client_errors where d_created < _d_data;
	get diagnostics _dg_cnt = row_count;
	_dg_text = _dg_text || ' core.sd_client_errors: удалено '|| _dg_cnt::text || E'\n';

	insert into core.cd_sys_log(d_timestamp, c_descr)
	values(clock_timestamp(), 'Очистка таблиц выполнена. ' || E'\n' || _dg_text);

	EXCEPTION
	WHEN OTHERS
    THEN
		insert into core.cd_sys_log(d_timestamp, c_descr)
		values(clock_timestamp(), 'Непредвиденная ошибка очистки таблиц');

END;

Function: core.sf_table_change_update(_c_table_name text, _f_user integer)

Returns: integer

Language: PLPGSQL

/**
* @params {text} _c_table_name - имя таблицы
* @params {integer} _f_user - иднтификатор пользователя
*
* @returns {integer} - 0 результат выполнения
*
* @example
* [{ "action": "sf_table_change_update", "method": "Query", "data": [{ "params": [_c_table_name, _f_user] }], "type": "rpc", "tid": 0 }]
*/
BEGIN
	IF (select count(*) from core.sd_table_change where c_table_name = _c_table_name
		and (case when _f_user is null then _f_user is null else f_user = _f_user end)) = 0 then
		INSERT INTO core.sd_table_change (c_table_name, n_change, f_user)
		VALUES (_c_table_name, (SELECT EXTRACT(EPOCH FROM now())), _f_user);
	else
		update core.sd_table_change
		set n_change = (SELECT EXTRACT(EPOCH FROM now()))
		where c_table_name = _c_table_name and (case when _f_user is null then _f_user is null else f_user = _f_user end);
	end if;

	RETURN 0;
END

Function: core.sf_update_version()

Returns: text

Language: PLPGSQL

Принудительное обновление версии базу данных

/**
* принудительное обновление
*
* @returns {text} новая версия базы данных
*/
DECLARE
	_ver text;
BEGIN
	UPDATE core.cd_settings
	SET c_value = core.sf_build_version(0)
	WHERE lower(c_key) = lower('DB_VERSION');
	
	SELECT c_value INTO _ver FROM core.cd_settings WHERE lower(c_key) = lower('DB_VERSION');
	RETURN _ver;
END;

Function: core.sf_version_to_number(c_version text)

Returns: bigint

Language: PLV8

Преобразование версии в число

/**
* @params {text} c_version - версия приложения
* @returns {bigint} числовой вариант версии
*/
try {
		var data = c_version.split('.');
		return (parseInt(data[1]) * 24 * 60) + parseInt(data[3]);
	} catch(e) { 
		return 0;
	}

Schema dbo

View: dbo.cv_tracking

SELECT t.f_user
,
    concat
(u.c_last_name
     ,' '
     , u.c_first_name
     ,' '
     , u.c_middle_name
) AS c_name
,
    t.d_date_str
,
    t.d_date
,
    t.n_longitude
,
    t.n_latitude
,
    t.c_network_status
   
FROM (
     (
      SELECT t_1.fn_user AS f_user
           ,
            to_char
           (t_1.d_date
                 ,'dd.MM.YYYY HH24:MI'::text
           ) AS d_date_str
           ,
            max
           (t_1.d_date) AS d_date
           ,
            avg
           (t_1.n_longitude) AS n_longitude
           ,
            avg
           (t_1.n_latitude) AS n_latitude
           ,
            max
           (t_1.c_network_status) AS c_network_status
           
        FROM core.ad_tracking t_1
          
    GROUP BY t_1.fn_user
           , (to_char
                 (t_1.d_date
                       ,'dd.MM.YYYY HH24:MI'::text
                 )
           )
        
   UNION ALL
         
      SELECT t_1.fn_user AS f_user
           ,
            to_char
           (t_1.d_date
                 ,'dd.MM.YYYY HH24:MI'::text
           ) AS d_date_str
           ,
            t_1.d_date
           ,
            t_1.n_longitude
           ,
            t_1.n_latitude
           ,
            'online'::text AS c_network_status
           
        FROM core.cd_user_points t_1
          
       WHERE (t_1.n_longitude > 
                 (1)::numeric
           )
     ) t
     
  JOIN core.pd_users u 
    ON (
           (t.f_user = u.id)
     )
)
  
ORDER BY t.d_date;

Index - Schema dbo

Table: dbo.ed_blanks

Бланки строгой отчетности

Index - Schema dbo

Table: dbo.ed_devices

Приборы учета

• ed_devices_f_device_types_idx f_device_type
• ed_devices_f_registr_pts_idx f_registr_pts
• ed_devices_link_idx link

Index - Schema dbo

Table: dbo.ed_input_meter_readings

Показания ПУ

• ed_input_meter_readings_d_replace_date_idx d_replace_date
• ed_input_meter_readings_f_device_idx f_device
• ed_input_meter_readings_f_registr_pts_f_scale_f_tariff_zone_idx f_registr_pts, f_scale, f_tariff_zone
• ed_input_meter_readings_f_registr_pts_idx f_registr_pts
• ed_input_meter_readings_jb_source_idx jb_sources

Index - Schema dbo

Table: dbo.ed_output_conn_seals

Привязка новых пломб к точке учета

Index - Schema dbo

Table: dbo.ed_output_meter_readings

Показания ПУ

• ed_output_meter_readings_d_date_idx d_date

Index - Schema dbo

Table: dbo.ed_output_transformers

Исходящая информация о трансформаторах

Index - Schema dbo

Table: dbo.ed_registr_pts

Учетный показатель

• ed_registr_pts_f_address_idx f_address
• ed_registr_pts_f_device_int_idx f_device_int
• ed_registr_pts_f_imp_idx f_division, f_subdivision, imp_text
• ed_registr_pts_f_subscr_idx f_subscr

Index - Schema dbo

Table: dbo.ed_registr_pts_history

История точек учета

Index - Schema dbo

Table: dbo.ed_seals

Плобмы

• c_type_idx c_type gin_trgm_ops
• ed_seals_f_registr_pts_idx f_registr_pts
• ed_seals_f_status_idx f_status

Index - Schema dbo

Table: dbo.ed_transformers

Трансформаторы

• ed_transformers_f_registr_pts_idx f_registr_pts

Index - Schema dbo

Table: dbo.ed_violation

Нарушения

• ed_violation_f_registr_pts_idx f_registr_pts

Index - Schema dbo

Table: dbo.es_blank_status

Статусы бланков

Index - Schema dbo

Table: dbo.es_delivery_methods

Источник показаний

Index - Schema dbo

Table: dbo.es_device_categories

Категории приборов

Index - Schema dbo

Table: dbo.es_device_location

Место установки

Index - Schema dbo

Table: dbo.es_device_types

Типы прибора

• es_device_types_f_device_types_idx f_device_types

Index - Schema dbo

Table: dbo.es_energy_types

Виды измерения

Index - Schema dbo

Table: dbo.es_failure_reason

Причины недопуска

Index - Schema dbo

Table: dbo.es_no_plan_works

Справочник внеплановых работ

Index - Schema dbo

Table: dbo.es_notice_trash

Причина списания пломбы/бланка

Index - Schema dbo

Table: dbo.es_phase

Фазы

Index - Schema dbo

Table: dbo.es_precission_classes

Классы точности прибора

Index - Schema dbo

Table: dbo.es_reading_statuses

Статусы показаний

Index - Schema dbo

Table: dbo.es_registr_pts_status

Статус активности ТУ

Index - Schema dbo

Table: dbo.es_restrictions

Типы ограничений

Index - Schema dbo

Table: dbo.es_scales

Шкалы прибора учета

Index - Schema dbo

Table: dbo.es_seal_places

Справочник мест установки пломб

Index - Schema dbo

Table: dbo.es_seal_status

Статусы пломб

Index - Schema dbo

Table: dbo.es_seal_types

тип пломбы

Index - Schema dbo

Table: dbo.es_status_consume

Статус потребления ТУ

Index - Schema dbo

Table: dbo.es_tariff_zones

Тарифные зоны

Index - Schema dbo

Table: dbo.es_verification_reason

Причина проверки

Index - Schema dbo

Table: dbo.es_violations

Типы нарушений

Index - Schema dbo

Table: dbo.es_work_links

Сопоставление видов работ

Index - Schema dbo

Table: dbo.sd_employers

Сотрудники

Index - Schema dbo

Table: dbo.sd_subscr

Абонент