/*2024.18*/
create or alter procedure SP_GET_REESTR_TYPE (
    HDR_ID DM_INT,
    OKATO DM_OKATO,
    IS_IDENT DM_INT = null)
returns (
    IS_EXPORT DM_BOOL,
    REESTR_NAME DM_CHAR_100,
    ZAP_CNT DM_INT,
    NOTE DM_CHAR_100,
    EXPORT_ID DM_INT,
    TARGET DM_INT,
    RTYPE DM_CHAR_10,
    SUMV DM_MONEY_15_2,
    FORMAT_TYPE DM_INT)
as
begin
  note = null;
  is_export = 0;
  reestr_name = 'Основной файл (без ЗНО, ВМП, профосмотров) (HM)';

  if (:okato = '70000') then
    reestr_name = 'Общий реестр (по всем типам помощи)';

  target = 1;
  rtype = 'HM';

  if ((:okato in ('03000','54000','46000','37000')) and (:is_ident = 1)) then
  begin
    reestr_name = 'Файл на идентификацию';
    select sum(iif(target not in (9,10,13,14,15,16,17,18), 1, 0)) AllCnt,
         sum(iif(target not in (9,10,13,14,15,16,17,18), sum_uslf, 0))
    from zs z
    join zs_hdr h on h.id = z.hdr_id
    where (h.id = :hdr_id) or (h.parent_id = :hdr_id)
    into :zap_cnt, :sumv;

    select first 1 s.name, s.id, s.format_type
     from xml_structure s
     where (s.okato_reg = :okato) and s.is_show = 1 and s.struct_type = 2
     order by s.okato_reg desc
     into :note, :export_id, :format_type;

    is_export = 1;
    if (:note is null) then  note = 'Отсутствует выгрузка';
    suspend;
    exit;
  end

  if (:okato in ('46000','03000')) then
  begin
    reestr_name = 'Основной архив файлов';
    select sum(iif(target not in (9,10,13,14,15,16,17,18), 1, 0)) AllCnt,
         sum(iif(target not in (9,10,13,14,15,16,17,18), sum_uslf, 0))
    from zs z
    join zs_hdr h on h.id = z.hdr_id
    where (h.id = :hdr_id) or (h.parent_id = :hdr_id)
    into :zap_cnt, :sumv;

    select first 1 s.name, s.id, s.format_type
     from xml_structure s
     where (s.okato_reg = :okato) and s.is_show = 1 and s.struct_type = 11
     order by s.okato_reg desc
     into :note, :export_id, :format_type;

    is_export = 1;
    if (:note is null) then  note = 'Отсутствует выгрузка';
    suspend;
    exit;
  end

  select sum(iif(target not in (9,10,13,14,15,16,17,18) and coalesce(vid_hmp, '''') = '''' and (((select IS_ONK from SP_CHECK_REESTR_AS_ONK(DS1, ID)) <> 1) AND (coalesce(ds_onk,0) <> 1)), 1, 0)) AllCnt,
         sum(iif(target not in (9,10,13,14,15,16,17,18) and coalesce(vid_hmp, '''') = '''' and (((select IS_ONK from SP_CHECK_REESTR_AS_ONK(DS1, ID)) <> 1) AND (coalesce(ds_onk,0) <> 1)), sum_uslf, 0))
  from zs
  where hdr_id = :hdr_id
  into :zap_cnt, :sumv;

  if (:okato = '70000') then
    select count(id) AllCnt, sum(sum_uslf)
      from zs
      where hdr_id = :hdr_id
      into :zap_cnt, :sumv;

  if (:okato in ('71140','75000')) then
  begin
    note = 'Выгрузка в формате ' || (select first 1 k.name from h_okato k where k.code = :okato);
    export_id = 1;
  end
  else
  begin
    select first 1 s.name, s.id, s.format_type
     from xml_structure s
     where (s.okato_reg = :okato or (s.okato_reg is null)) and s.is_show = 1 and s.struct_type = 11
     order by s.okato_reg desc
     into :note, :export_id, :format_type;
  end

  if (:note is null) then  note = 'Отсутствует выгрузка';

  if ((zap_cnt > 0) and note <> 'Отсутствует выгрузка') then is_export = 1;

  if ((:okato = '70000') and note <> 'Отсутствует выгрузка') then is_export = 1;

  if ((zap_cnt > 0) or (:okato = '70000')) then suspend;

  if (:okato = '70000') then exit;
  /***************************************************************/
  note = null;
  is_export = 0;
  reestr_name = 'Файл с ВМП (TM)';
  target = 1;
  rtype = 'TM';

  select sum(iif(target not in (9,10,13,14,15,16,17,18) and coalesce(vid_hmp, '''') <> '''', 1, 0)) VmpCnt,
         sum(iif(target not in (9,10,13,14,15,16,17,18) and coalesce(vid_hmp, '''') <> '''', sum_uslf, 0)) VmpCnt
  from zs
  where hdr_id = :hdr_id
  into :zap_cnt, :sumv;

  if (:okato in ('71140','75000')) then
  begin
    note = 'Выгрузка в формате ' || (select first 1 k.name from h_okato k where k.code = :okato);
    export_id = 1;
  end
  else
  begin
    select first 1 s.name, s.id, s.format_type
     from xml_structure s
     where (s.okato_reg = :okato or (s.okato_reg is null)) and s.is_show = 1 and s.struct_type = 12
     order by s.okato_reg desc
     into :note, :export_id, :format_type;
  end

  if (:note is null) then  note = 'Отсутствует выгрузка';
  if ((zap_cnt > 0) and note <> 'Отсутствует выгрузка') then is_export = 1;
  if (zap_cnt > 0) then suspend;
  /***************************************************************/
  note = null;
  is_export = 0;
  reestr_name = 'Файл с ЗНО (CM)';
  target = 1;
  rtype = 'CM';

  select sum(iif(target not in (9,10,13,14,15,16,17,18) and coalesce(vid_hmp, '''') = '''' and (((select IS_ONK from SP_CHECK_REESTR_AS_ONK(DS1, ID)) = 1) or (coalesce(ds_onk,0) = 1)), 1, 0)),
         sum(iif(target not in (9,10,13,14,15,16,17,18) and coalesce(vid_hmp, '''') = '''' and (((select IS_ONK from SP_CHECK_REESTR_AS_ONK(DS1, ID)) = 1) or (coalesce(ds_onk,0) = 1)), sum_uslf, 0))
  from zs
  where hdr_id = :hdr_id
  into :zap_cnt, :sumv;

  if (:okato in ('71140','75000')) then
  begin
    note = 'Выгрузка в формате ' || (select first 1 k.name from h_okato k where k.code = :okato);
    export_id = 1;
  end
  else
  begin
    select first 1 s.name, s.id, s.format_type
     from xml_structure s
     where (s.okato_reg = :okato or (s.okato_reg is null)) and s.is_show = 1 and s.struct_type = 13
     order by s.okato_reg desc
     into :note, :export_id, :format_type;
  end

  if (:note is null) then  note = 'Отсутствует выгрузка';
  if ((zap_cnt > 0) and note <> 'Отсутствует выгрузка') then is_export = 1;
  if (zap_cnt > 0) then suspend;
  /***************************************************************/
  note = null;
  is_export = 0;
  reestr_name = 'Первый этап диспансеризации взрослых (DP)';
  target = 91;

  select sum(iif(target = 9 and disp_stage = 1, 1, 0))
  from zs
  where hdr_id = :hdr_id
  into :zap_cnt;

  if (:okato in ('71140','75000')) then
  begin
    note = 'Выгрузка в формате ' || (select first 1 k.name from h_okato k where k.code = :okato);
    export_id = 1;
  end
  else
  begin
    select first 1 s.name, s.id, s.format_type
     from xml_structure s
     where (s.okato_reg = :okato or (s.okato_reg is null)) and s.is_show = 1 and s.struct_type = 14
     order by s.okato_reg desc
     into :note, :export_id, :format_type;
  end

  if (:note is null) then  note = 'Отсутствует выгрузка';
  if ((zap_cnt > 0) and note <> 'Отсутствует выгрузка') then is_export = 1;
  if (zap_cnt > 0) then suspend;
  /***************************************************************/
  is_export = 0;
  reestr_name = 'Второй этап диспансеризации взрослых (DV)';
  target = 92;

  select sum(iif(target = 9 and disp_stage = 2, 1, 0))
  from zs
  where hdr_id = :hdr_id
  into :zap_cnt;

  if ((zap_cnt > 0) and note <> 'Отсутствует выгрузка') then is_export = 1;
  if (zap_cnt > 0) then suspend;
  /***************************************************************/
  target = 16;
  is_export = 0;
  reestr_name = 'Профосмотры взрослых (DO)';

  select sum(iif(target = 16, 1, 0))
  from zs
  where hdr_id = :hdr_id
  into :zap_cnt;

  if ((zap_cnt > 0) and note <> 'Отсутствует выгрузка') then is_export = 1;
  if (zap_cnt > 0) then suspend;
  /***************************************************************/
  is_export = 0;
  reestr_name = 'Диспансеризация сирот (DS)';
  target = 10;

  select sum(iif(target = 10, 1, 0))
  from zs
  where hdr_id = :hdr_id
  into :zap_cnt;

  if ((zap_cnt > 0) and note <> 'Отсутствует выгрузка') then is_export = 1;
  if (zap_cnt > 0) then suspend;
  /***************************************************************/
  is_export = 0;
  reestr_name = 'Диспансеризация усыновлённых (DU)';
  target = 17;

  select sum(iif(target = 17, 1, 0))
  from zs
  where hdr_id = :hdr_id
  into :zap_cnt;

  if ((zap_cnt > 0) and note <> 'Отсутствует выгрузка') then is_export = 1;
  if (zap_cnt > 0) then suspend;
  /***************************************************************/
  is_export = 0;
  reestr_name = 'Профосмотры несовершеннолетних (DF)';
  target = 13;

  select sum(iif(target = 13, 1, 0))
  from zs
  where hdr_id = :hdr_id
  into :zap_cnt;

  if ((zap_cnt > 0) and note <> 'Отсутствует выгрузка') then is_export = 1;
  if (zap_cnt > 0) then suspend;
  /***************************************************************/
  note = null;
  is_export = 0;
  reestr_name = 'Первый этап углублённой диспансеризации (DA)';
  target = 181;

  select sum(iif(target = 18 and disp_stage = 1, 1, 0))
  from zs
  where hdr_id = :hdr_id
  into :zap_cnt;

  if ((zap_cnt > 0) and note <> 'Отсутствует выгрузка') then is_export = 1;
  if (zap_cnt > 0) then suspend;
  /***************************************************************/
  note = null;
  is_export = 0;
  reestr_name = 'Второй этап углублённой диспансеризации (DB)';
  target = 182;

  select sum(iif(target = 18 and disp_stage = 2, 1, 0))
  from zs
  where hdr_id = :hdr_id
  into :zap_cnt;

  if ((zap_cnt > 0) and note <> 'Отсутствует выгрузка') then is_export = 1;
  if (zap_cnt > 0) then suspend;
end;