---

create or replace package body umdap_cdu_coal_ton_mining_cost_pkg as
  -- ----------------------------------------------------------------------
  SHEET_ID       pls_integer   := 1;
  BULK_LIMIT     pls_integer   := 1000;
  DFMT           varchar2(32)  := 'dd.mm.yyyy hh24:mi:ss';
  DECIMAL_CHAR   CHAR          :='.';
  -- ----------------------------------------------------------------------
  function fn(p in varchar2) return varchar2 is begin return sys_context('USERENV', 'CURRENT_SCHEMA') || '.' || $$plsql_unit || '.' || p; end;
  -- ----------------------------------------------------------------------
  function split(p_params in varchar2) return sys.odciVarchar2List is
    l_res   sys.odciVarchar2List := sys.odciVarchar2List();
    l_delim varchar2(1) := ' ';
  begin
      if p_params is null then return l_res; end if;
      for curs in (
        select distinct t.param, rownum rn, count(1)over() cnt
        from(select trim(regexp_substr(tbl.param, '[^'||l_delim||']+', 1, level)) param
             from (select p_params param from dual) tbl
             connect by level <= length(tbl.param) - length(replace(tbl.param, l_delim)) + 1
        ) t where t.param is not null
        order by rn
      ) loop
        if curs.rn = 1 then l_res.extend(curs.cnt); end if;
        l_res(curs.rn) := curs.param;
      end loop;

      return l_res;
  end;
  -- ----------------------------------------------------------------------
  function consistentCheck (p_event_id in number) return varchar2 is
    invalid_date_format exception;
    pragma exception_init(invalid_date_format, -01843);
    --
    type parseTable is table of xxapps.xxapps_xlsx_parsed_content_t%rowtype;
    type loadTable  is table of umdap_cdu_coal_ton_mining_cost_xls%rowtype;
    l_load_table  loadTable := loadTable();
    l_parse_table parseTable;
    l_cr          pls_integer;
    l_cv          varchar2(4000);
    l_filename    varchar2(150 char);
    l_period      varchar2(50 char);
    c             integer;
    qry           varchar2(200 char);
    l_on_date_str varchar2(1000) := null;
    l_on_date     date := null;
    l_year        varchar2(10)   := null;
    l_month       varchar2(100)  := null;
    l_publication_date date      := null;
    l_ussi        varchar2(1000) := null;
    l_region      varchar2(1000) := null;
    l_company     varchar2(1000) := null;
    l_coal        varchar2(1000) := null;
    l_cnt         integer;
    l_region_cnt  integer;
    l_company_cnt integer;
    l_unit        varchar2(1000) := 'руб.';
    l_company_id  varchar2(128)  := null;
    l_cost_col    varchar2(10)   := null;
    --
    cursor parseCursor(p_sheet_id pls_integer) is
      select pc.*
        from xxapps.xxapps_xlsx_parsed_content_t pc
       where pc.sheet_id = p_sheet_id
         and pc.row_id  >= LOAD_START_ROW
      order by pc.row_id;
    --
    function filename(p_event_id in number) return varchar2 is
      l_filename varchar2(100 char);
    begin
     select file_name
      into l_filename
      from xxwh_etl_loader_events_t
      where id = p_event_id;
      return l_filename;
    end;
    --
    function getMonthNum(p_month in varchar2) return varchar2 is
      l_filename varchar2(100 char);
    begin
      return
        case
          when p_month = 'январь'   then '01'
          when p_month = 'февраль'  then '02'
          when p_month = 'март'     then '03'
          when p_month = 'апрель'   then '04'
          when p_month = 'май'      then '05'
          when p_month = 'июнь'     then '06'
          when p_month = 'июль'     then '07'
          when p_month = 'август'   then '08'
          when p_month = 'сентябрь' then '09'
          when p_month = 'октябрь'  then '10'
          when p_month = 'ноябрь'   then '11'
          when p_month = 'декабрь'  then '12'
        end;
    end;
    --
    procedure appendToLoad(pr xxapps.xxapps_xlsx_parsed_content_t%rowtype) is
      l_idx      pls_integer;
      l_type     number;
      l_cost_val varchar2(4000);
      l_sql      varchar2(4000);
      --
      function toStr (s varchar2) return varchar2 is begin l_cv := s; return s; end;
      function toDate(s varchar2) return date     is begin l_cv := s; return to_date(s, DFMT); end;
      function toNum (s varchar2) return number   is
      begin
        l_cv := s;
        return to_number(
                 case DECIMAL_CHAR
                   when '.' then replace(s, ',', '.')
                   when ',' then replace(s, '.', ',')
                   else s
                 end);
      end;
    --
    begin
      l_cr := pr.row_id;
   
      l_sql := 'select ' || l_cost_col || ' from xxapps.xxapps_xlsx_parsed_content_t where sheet_id = :1 and row_id = :2';
   
      execute immediate l_sql into l_cost_val using SHEET_ID, l_cr;
   
      l_load_table.extend();
      l_idx := l_load_table.last;

      l_load_table(l_idx)."companyId"           := toStr (l_company_id);   
      l_load_table(l_idx)."companyName"         := toStr (l_company);
      l_load_table(l_idx)."costPerTonCdu"       := toNum (l_cost_val);
      l_load_table(l_idx)."unit"                := toStr (l_unit);
      l_load_table(l_idx)."onDate"              := l_on_date;
      l_load_table(l_idx)."publicationDate"     := l_publication_date;
      l_load_table(l_idx)."loadEventID"         := p_event_id;
    end;
  begin
    select substr(value,1,1) into DECIMAL_CHAR from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS';
    l_filename := filename(p_event_id);

    open parseCursor(SHEET_ID);
    loop
      fetch parseCursor bulk collect into l_parse_table limit BULK_LIMIT;
      exit when l_parse_table.count = 0;

      l_ussi := l_parse_table(l_parse_table.count).col_a;

      for i in 1..l_parse_table.count loop

        if i = 1 and l_parse_table(i).col_a not like '%одной тонны добычи%' then
          return 'Файл должен содержать фразу "одной тонны добычи"';
        end if;

      -- "publicationDate" --
        if i = 1 then
          l_publication_date := to_date(replace(substr(replace(l_ussi, 'УСОИ: '), 1, 10), '/', '.'), DFMT);
          if l_publication_date is null or length(replace(l_ussi, 'УСОИ: ')) <> 19 then
            return 'Поле "publicationDate" должно быть заполнено';
          end if;
        end if;

      -- "onDate" --
        if i = 2 then
          l_on_date_str := replace(replace(l_parse_table(i).col_a, 'за '), ' года');
          l_year := regexp_replace(l_on_date_str, '[^[:digit:]]', '');
          l_month := lower(trim(replace(l_on_date_str, l_year)));
          l_month := getMonthNum(l_month);
          l_on_date := to_date('01.'||l_month||'.'||l_year , DFMT);
        end if;
 
      -- "costPerTonCdu" --
        if i = 4 and l_cost_col is null then
          if l_parse_table(i).col_a != 'УЭП-3.07.99'
            then return 'В шапке таблицы отсутствует заголовок "УЭП-3.07.99"';
          end if;
          l_cost_col :=
            case
              when l_parse_table(i).col_b  like '%Полная себестоимость%' then 'COL_B' 
              when l_parse_table(i).col_c  like '%Полная себестоимость%' then 'COL_C'
              when l_parse_table(i).col_d  like '%Полная себестоимость%' then 'COL_D'
              when l_parse_table(i).col_e  like '%Полная себестоимость%' then 'COL_E'
              when l_parse_table(i).col_f  like '%Полная себестоимость%' then 'COL_F'
              when l_parse_table(i).col_g  like '%Полная себестоимость%' then 'COL_G'
              when l_parse_table(i).col_h  like '%Полная себестоимость%' then 'COL_H'
              when l_parse_table(i).col_i  like '%Полная себестоимость%' then 'COL_I'
              when l_parse_table(i).col_j  like '%Полная себестоимость%' then 'COL_J'
              when l_parse_table(i).col_k  like '%Полная себестоимость%' then 'COL_K'
              when l_parse_table(i).col_l  like '%Полная себестоимость%' then 'COL_L'
              when l_parse_table(i).col_m  like '%Полная себестоимость%' then 'COL_M'
              when l_parse_table(i).col_n  like '%Полная себестоимость%' then 'COL_N'
              when l_parse_table(i).col_o  like '%Полная себестоимость%' then 'COL_O'
              when l_parse_table(i).col_p  like '%Полная себестоимость%' then 'COL_P'
              when l_parse_table(i).col_q  like '%Полная себестоимость%' then 'COL_Q'
              when l_parse_table(i).col_r  like '%Полная себестоимость%' then 'COL_R'
              when l_parse_table(i).col_s  like '%Полная себестоимость%' then 'COL_S'
              when l_parse_table(i).col_t  like '%Полная себестоимость%' then 'COL_T'
              when l_parse_table(i).col_u  like '%Полная себестоимость%' then 'COL_U'
              when l_parse_table(i).col_v  like '%Полная себестоимость%' then 'COL_V'
              when l_parse_table(i).col_w  like '%Полная себестоимость%' then 'COL_W'
              when l_parse_table(i).col_x  like '%Полная себестоимость%' then 'COL_X'
              when l_parse_table(i).col_y  like '%Полная себестоимость%' then 'COL_Y'
              when l_parse_table(i).col_z  like '%Полная себестоимость%' then 'COL_Z'
              when l_parse_table(i).col_aa like '%Полная себестоимость%' then 'COL_AA'
              when l_parse_table(i).col_ab like '%Полная себестоимость%' then 'COL_AB'
              when l_parse_table(i).col_ac like '%Полная себестоимость%' then 'COL_AC'
              when l_parse_table(i).col_ad like '%Полная себестоимость%' then 'COL_AD'
              when l_parse_table(i).col_ae like '%Полная себестоимость%' then 'COL_AE'
              when l_parse_table(i).col_af like '%Полная себестоимость%' then 'COL_AF'
              when l_parse_table(i).col_ag like '%Полная себестоимость%' then 'COL_AG'
              when l_parse_table(i).col_ah like '%Полная себестоимость%' then 'COL_AH'
              when l_parse_table(i).col_ai like '%Полная себестоимость%' then 'COL_AI'
              when l_parse_table(i).col_aj like '%Полная себестоимость%' then 'COL_AJ'
              when l_parse_table(i).col_ak like '%Полная себестоимость%' then 'COL_AK'
              when l_parse_table(i).col_al like '%Полная себестоимость%' then 'COL_AL'
              when l_parse_table(i).col_am like '%Полная себестоимость%' then 'COL_AM'
              when l_parse_table(i).col_an like '%Полная себестоимость%' then 'COL_AN'
              when l_parse_table(i).col_ao like '%Полная себестоимость%' then 'COL_AO'
              when l_parse_table(i).col_ap like '%Полная себестоимость%' then 'COL_AP'
              when l_parse_table(i).col_aq like '%Полная себестоимость%' then 'COL_AQ'
              when l_parse_table(i).col_ar like '%Полная себестоимость%' then 'COL_AR'
              when l_parse_table(i).col_as like '%Полная себестоимость%' then 'COL_AS'
              when l_parse_table(i).col_at like '%Полная себестоимость%' then 'COL_AT'
              when l_parse_table(i).col_au like '%Полная себестоимость%' then 'COL_AU'
              when l_parse_table(i).col_av like '%Полная себестоимость%' then 'COL_AV'
              when l_parse_table(i).col_aw like '%Полная себестоимость%' then 'COL_AW'
              when l_parse_table(i).col_ax like '%Полная себестоимость%' then 'COL_AX'
              when l_parse_table(i).col_ay like '%Полная себестоимость%' then 'COL_AY'
              when l_parse_table(i).col_az like '%Полная себестоимость%' then 'COL_AZ'
              else null
            end;
          if l_cost_col is null then return 'В шапке таблицы отсутствует заголовок "Полная себестоимость"'; end if; 
        end if;

        if i >= 8 and i < l_parse_table.count and l_parse_table(i).col_a not like '% ФО' then
          l_company := l_parse_table(i).col_a;
          if l_company is not null then 
            select max("id") into l_company_id from xxwh."coalaCompanyNames" where "name" = l_company and "sourceType" = 'CDU' and "deletedDate" is null;
            appendToLoad(l_parse_table(i));  
          end if;
        end if;
      end loop;
      forall j in 1..l_load_table.count
        insert into umdap_cdu_coal_ton_mining_cost_xls values l_load_table(j);
      l_load_table.delete;
    end loop;
    close parseCursor;
    return null;
  exception
    when invalid_date_format then
      rollback;
      if parseCursor%isopen then close parseCursor; end if;
      return 'Некорректная дата публикации';
    when others then
      rollback;
      if parseCursor%isopen then close parseCursor; end if;
      return 'Error on row '||l_cr||' value '||l_cv||' '||sqlerrm;
  end;
  -- ----------------------------------------------------------------------
end;
/