CREATE OR REPLACE PROCEDURE dev_NachislenieH(HsID       k_House.ID%TYPE,
                                             Data_Start DATE,
                                             Prm        NUMBER) AS
  finmonth      DATE;
  data_end_nach DATE;
  data_beg_nach DATE;

  previus_d     DATE;
  curr_d        DATE;
  next_d        DATE;

  data_accountchange  DATE;
  data_accountchange1 DATE;
  data_accountchange2 DATE;
  date_tmp            NUMBER;

  TYPE rec_HIST_RECORD IS RECORD(
    ACCID         NUMBER,
    DATA          DATE,
    NORMA         NUMBER(10, 5),
    STATE_SRV     NUMBER,
    SERVICE_ID    NUMBER,
    COUNT_INHABIT NUMBER,
    COUNT_WANTING NUMBER,
    AREA_POLEZN   NUMBER);
  TYPE T_hist_table IS TABLE OF RECORD_ACCOUNT_HIST INDEX BY BINARY_INTEGER;

  HIST TABLE_ACCOUNT_HIST;

  TYPE history_PART is table of RECORD_ACCOUNT_HIST INDEX BY BINARY_INTEGER;


  TYPE record_account is record(
     ACCID         VARCHAR2(12)
  );
  TYPE accounts_table is TABLE OF k_accounts%ROWTYPE;
  accounts_pool accounts_table;

  -- Тарифы на электричество.
  TYPE TTarifE IS RECORD(
    SrvNorm_ID NUMBER,
    Data       DATE,
    Data_End   DATE,
    Volume_Min NUMBER,
    Volume_Max NUMBER,
    Tarif      NUMBER);
  TYPE TTarifsE IS TABLE OF TTarifE INDEX BY BINARY_INTEGER;
  TarifsE TTarifsE;

  TYPE KoefsInTimeRange IS record(
    dayAnchor           DATE, -- Указатель на день месяца, показывает промежуток с этого дня, и до следуйщего указателя
    caculatedKoeficient NUMBER(10, 5) -- Коефициент работающий в этот промежуток времени
    );

  TYPE tMonthChanges IS TABLE OF KoefsInTimeRange INDEX BY binary_integer;
  monthchanges tmonthchanges;

  TYPE TAccountSpread is record(
    koeff number);

  TYPE tabAccountSpread is TABLE of TAccountSpread INDEX BY binary_integer;
  accountSpreadTable tabAccountSpread;

  TYPE TAcc IS RECORD(
    acc VARCHAR2(12),
    ID  VARCHAR2(12));
  TYPE TAccountsWithIndic is TABLE of TAcc INDEX BY binary_integer;
  AccountsWithIndic TAccountsWithIndic;

  FORMAT3 VARCHAR2(10) := '999D999';

  all_norm           NUMBER;
  avg_koeff          NUMBER;
  avg_norm           NUMBER;
  AvgPeoplsCount     NUMBER;
  Area_Izl           NUMBER;
  REALPeoplsCount    NUMBER;
  commonKoef         NUMBER := 0;
  curr_tarif         NUMBER;
  currentNorm        NUMBER;
  currentPeoplsCount NUMBER;
  housevolume        NUMBER(20);
  month_part         NUMBER;
  month_part_koef    NUMBER;
  summ_nach          NUMBER;
  volume_nach        NUMBER;
  tmp_numb           NUMBER;

  spread_amount      NUMBER;
  spread_tmp         NUMBER;
  volume_social_nach NUMBER;
  service_id         NUMBER;

  activeServiceFlag BOOLEAN;
  ActiveServiceDays NUMBER;
  normno            binary_integer;
  serviceno         binary_integer;
  dayinmonthnach    INTEGER;

  -- Услуги.
  TYPE TService IS RECORD(
    Formula   spr_Services.Formula%TYPE,
    Params    n_AccountService.Params%TYPE,
    Norm_ID   n_AccountService.Norm_ID%TYPE,
    State_Srv n_HouseService.State_Srv%TYPE);
  TYPE tservices IS TABLE OF tservice INDEX BY binary_integer;
  Services TServices;

  -- Нормы.
  TYPE TNorm IS record(
    norm               spr_norms.norm%TYPE,
    norm_social        spr_norms.norm_social%TYPE,
    Norm_Slave_Persons spr_Norms.Norm_Slave_Persons%TYPE,
    Period             spr_Norms.Period%TYPE);
  TYPE TNorms IS TABLE OF TNorm INDEX BY binary_integer;
  Norms TNorms;

  -- Тарифы общие и домовые.
  TYPE TTarif IS RECORD(
    Tarif   spr_Tarifs.Tarif%TYPE,
    Dotacya spr_Tarifs.Dotacya%TYPE);
  TYPE TTarifs IS TABLE OF TTarif INDEX BY BINARY_INTEGER;
  tarifs  ttarifs;
  TarifsH TTarifs;

  s_ VARCHAR2(10);

  FUNCTION CalcSummByTarifsE(Data       DATE,
                             NormNo     NUMBER,
                             Volume     NUMBER,
                             MonthsPart NUMBER) RETURN NUMBER AS
    Result NUMBER;
    No     NUMBER;
  BEGIN
    Result := 0;
    No     := TarifsE.FIRST;
    WHILE No IS NOT NULL LOOP
      IF TarifsE(No).SrvNorm_ID = NormNo AND TarifsE(No).Data <= Data AND
          Data <= TarifsE(No).Data_End AND
          Volume > TarifsE(No).Volume_Min * MonthsPart THEN
        IF Volume >= TarifsE(No).Volume_Max * MonthsPart THEN
          Result := Result +
                    (TarifsE(No).Volume_Max - TarifsE(No).Volume_Min) *
                    MonthsPart * TarifsE(No).Tarif;
        ELSE
          Result := Result + (Volume - TarifsE(No).Volume_Min * MonthsPart) * TarifsE(No)
                   .Tarif;
        END IF;
      END IF;
      No := TarifsE.NEXT(No);
    END LOOP;
    RETURN Result;
  END;

  FUNCTION Tarif(ServiceNo      BINARY_INTEGER,
                 SrvNorm_ID     BINARY_INTEGER,
                 OnlyHouseTarif BOOLEAN DEFAULT FALSE)
    RETURN spr_Tarifs.Tarif%TYPE -- Для услуги вернуть тариф (домовой или общий).
   AS
    Rslt NUMBER;
  BEGIN
    -- Взять тариф из домовых тарифов.
    BEGIN
      Rslt := TarifsH(ServiceNo * 1000000 + NVL(SrvNorm_ID, 0) * 100).Tarif;
    EXCEPTION
      WHEN no_data_found THEN
        BEGIN
          IF NOT onlyhousetarif THEN
            Rslt := Tarifs(ServiceNo * 1000000 + NVL(SrvNorm_ID, 0) * 100 /*+ Category*/
                    ).Tarif;
          ELSE
            Rslt := 0;
          END IF;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            -- Если тарифа для услуги среди общегородских нет.
            Rslt := 0;
        END;
    END;
    RETURN Rslt;
  EXCEPTION
    WHEN no_data_found THEN
      RETURN 0;
  END;

BEGIN
  s_             := '    ';
  housevolume    := 0;
  dayinmonthnach := EXTRACT(DAY FROM last_day(data_beg_nach));

  SELECT FinMonth INTO FinMonth FROM System;
  data_end_nach := last_day(finmonth) + 1 - 1 / (24 * 60 * 60);
  data_beg_nach := trunc(data_start, 'MM');

  dbms_output.put_line(data_beg_nach || '-' || data_end_nach);
  FOR counter IN (SELECT DISTINCT k.*, s.vid
                    FROM c_House      c1,
                         c_House      c2,
                         k_Counters   k,
                         spr_counters s
                   WHERE c1.Status = 1
                     AND c2.House_ID = c1.House_ID
                     AND c2.Status = 1
                     AND k.ID = c2.counter_id
                     AND s.ID = k.counter_id
                     AND k.Data BETWEEN Data_Beg_Nach AND Data_End_Nach
                     AND c1.House_ID = lpad(Hsid, 12, '0')) loop
    dbms_output.put_line('');
    dbms_output.put_line('домовой счётчик№ ' || counter.id || ' vid=' ||
                         counter.vid);
    FOR idict IN (SELECT A.indication,
                         a.indication_old,
                         a.volume,
                         a.data,
                         a.counter_id
                    FROM n_indications A
                    LEFT JOIN n_indications b
                      ON (A.counter_id = b.counter_id AND A.DATA < b.DATA)
                   WHERE b.counter_id IS NULL
                     AND A.counter_id = counter.ID
                     AND A.Data BETWEEN Data_Beg_Nach AND Data_End_Nach) loop
      dbms_output.put_line(s_ || 'квартирные счётчики: ');
      houseVolume := 0;
      commonKoef  := 0;
      FOR apartcounter IN (SELECT DISTINCT acc.id account_id,
                                           kc.apart_id,
                                           ni.indication,
                                           ni.data_ins,
                                           nvl(ni.indication_old, 0) indication_old,
                                           CASE
                                             WHEN ni.vid_old = 1 THEN
                                              (ni.data_old + 1)
                                             ELSE
                                              ni.data_old
                                           end data_old,
                                           CASE
                                             WHEN ni.vid = 1 then
                                              0
                                             else
                                              ni.indication -
                                              nvl(ni.indication_old, 0)
                                           end summa,
                                           ni.vid,
                                           ni.counter_id
                             FROM k_counters kc
                             JOIN K_Accounts acc
                               on Acc.Apart_Id = Kc.Apart_Id
                             JOIN spr_counters s
                               ON s.ID = kc.counter_id
                             JOIN a_counters ac
                               ON ac.house_counter_id = kc.house_counter_id
                             JOIN n_indications ni
                               on ni.counter_id = kc.id
                            WHERE ac.house_counter_id = counter.ID
                              AND ni.vid = 2
                              AND ni.data_ins BETWEEN data_beg_nach AND
                                  data_end_nach
                            ORDER BY ni.vid, ni.counter_id, ni.data_ins) loop
        houseVolume := houseVolume + apartcounter.summa;
        -- если есть показания - запоминаем что бы не начислять по норме
        AccountsWithIndic(apartcounter.account_id).id := apartcounter.account_id;
        dbms_output.put_line(s_ || s_ || 'аккаунт№ ' ||
                             apartcounter.account_id || ' счетчик№' ||
                             apartcounter.counter_id || '[' ||
                             apartcounter.data_old || '-' ||
                             apartcounter.data_ins || '][' ||
                             apartcounter.vid || ']' || ' показания ' ||
                             apartcounter.indication_old || '-' ||
                             apartcounter.indication || '=' ||
                             (apartcounter.summa));
      END loop;
      dbms_output.put_line(s_ || 'сумма V по квартирным счетчикам=' ||
                           housevolume);
      dbms_output.put_line(s_ || 'V по домовому счётчику         =' ||
                           idict.volume);
      dbms_output.put_line(s_ || 'Дельта для разброса            =' ||
                           (idict.volume - housevolume));
      dbms_output.put_line(s_ ||
                           'Список акаунтов для разброса: counter.id=' ||
                           counter.id|| ' counter.vid='||counter.vid);

        data_accountchange1 := data_end_nach;
        ActiveServiceDays   := 0;
        avg_norm            := 0;
        AvgPeoplsCount      := 0;
        currentPeoplsCount  := 0;

        select RECORD_ACCOUNT_HIST(account_id,
               TRUNC(Data),
               NORM,
               STATE_SRV,
               SERVICE_ID,
               count_inhabit,
               COUNT_WANTING,
               AREA_POLEZN )
          BULK COLLECT INTO HIST
          from (
                /*ИСТОРИЯ ЛЮДЕЙ*/
                SELECT
                  /*ACCOUNT*/       acc.account_id,
                  /*NORMA*/         null norm,
                  /*DATA*/          case when DATA < data_beg_nach then data_beg_nach else acc.DATA end data,
                  /*STATE_SRV*/     null state_srv,
                  /*SERVICE_ID*/    null service_id,
                  /*COUNT_INHABIT*/ nvl(acc.count_inhabit, 0) count_inhabit,
                  /*COUNT_WANTING*/ nvl(acc.COUNT_WANTING, 0) COUNT_WANTING,
                  /*AREA_POLEZN*/   null AREA_POLEZN
                 FROM a_accounts acc
                 WHERE account_id in (SELECT DISTINCT ah.Account_ID Account_ID
                                      FROM c_AccountHouse ah
                                      WHERE ah.Counter_ID = counter.id
                                      AND ah.Data =
                                      (SELECT MAX(Data)
                                       FROM c_AccountHouse
                                       WHERE Account_ID = ah.Account_ID
                                       AND Data <= data_end_nach
                                       AND Counter_ID = ah.Counter_ID)
                                       AND ah.status = 1)
                UNION
                /*ИСТОРИЯ ПЛОЩАДЕЙ*/
                SELECT
                  /*ACCOUNT*/       acc.account_id,
                  /*NORMA*/         null norm,
                  /*DATA*/          case when DATA < data_beg_nach then data_beg_nach else acc.DATA end data,
                  /*STATE_SRV*/     null state_srv,
                  /*SERVICE_ID*/    null service_id,
                  /*COUNT_INHABIT*/ null count_inhabit,
                  /*COUNT_WANTING*/ null COUNT_WANTING,
                  /*AREA_POLEZN*/   acc.AREA_POLEZN
                 FROM a_accounts acc
                 WHERE account_id in (SELECT DISTINCT ah.Account_ID Account_ID
                                      FROM c_AccountHouse ah
                                      WHERE ah.Counter_ID = counter.id
                                      AND ah.Data =
                                      (SELECT MAX(Data)
                                       FROM c_AccountHouse
                                       WHERE Account_ID = ah.Account_ID
                                       AND Data <= data_end_nach
                                       AND Counter_ID = ah.Counter_ID)
                                       AND ah.status = 1)
                UNION
                 /*ИСТОРИЯ ОТКЛЮЧЕНИЙ*/
                select
                /*ACCOUNT*/       N.Account_Id,
                /*NORMA*/         null norm,
                /*DATA*/          case when n.data < data_beg_nach then data_beg_nach else n.data end Data,
                /*STATE_SRV*/     N.State_Srv  State_Srv,
                /*SERVICE_ID*/    N.Service_Id service_id,
                /*COUNT_INHABIT*/ null count_inhabit,
                /*COUNT_WANTING*/ null COUNT_WANTING,
                /*AREA_POLEZN*/   null AREA_POLEZN
                from n_accountservice n
                where N.Account_Id in (
                           SELECT DISTINCT ah.Account_ID Account_ID
                           FROM c_AccountHouse ah
                           WHERE ah.Counter_ID = counter.id
                           AND ah.Data =
                           (
                            SELECT MAX(Data)
                            FROM c_AccountHouse
                            WHERE Account_ID = ah.Account_ID
                            AND Data <= data_end_nach
                            AND Counter_ID = ah.Counter_ID)
                            AND ah.status = 1
                           )
                AND decode(n.service_id,
                           28,
                           1,
                           5,
                           1,
                           6,
                           2,
                           35,
                           2,
                           27,
                           3,
                           44,
                           3,
                           25,
                           4,
                           10,
                           5) = counter.vid

                UNION
                /*ИСТОРИЯ НОРМ*/
                select
                /*ACCOUNT*/          ACCSERV.ACCOUNT_ID account_id,
                /*NORMA*/            INNER_.norm,
                /*DATA*/             INNER_.Data,
                /*STATE_SRV*/        null state_srv,
                /*SERVICE_ID*/       /*iservice.service_id*/999 service_id,
                /*COUNT_INHABIT*/    null count_inhabit,
                /*COUNT_WANTING*/    null COUNT_WANTING,
                /*AREA_POLEZN*/      null AREA_POLEZN
                from (
                      SELECT
                      Norms.norm,
                      Norms.Data,
                      Norms.srvnorm_id
                      FROM spr_norms norms
                      WHERE norms.DATA BETWEEN data_beg_nach AND data_end_nach
                      and norms.srvnorm_id IN
                          (SELECT distinct s.norm_id
                           FROM n_accountservice s
                           WHERE s.account_id in (SELECT DISTINCT ah.Account_ID Account_ID
                                                  FROM c_AccountHouse ah
                                                  WHERE ah.Counter_ID = counter.id
                                                  AND ah.Data IN
                                                      (SELECT MAX(Data)
                                                       FROM c_AccountHouse
                                                       WHERE Account_ID = ah.Account_ID
                                                       AND Data <= data_end_nach
                                                       AND Counter_ID = ah.Counter_ID)
                                                       AND ah.status = 1)
                          )
                          UNION
                          SELECT  norms.norm,
                                  case when norms.DATA < data_beg_nach then data_beg_nach else norms.DATA end data,
                                  Norms.srvnorm_id
                                  FROM spr_norms norms
                                  WHERE Norms.Srvnorm_Id in
                                      (SELECT distinct s.norm_id
                                       FROM n_accountservice s
                                       WHERE s.account_id in (SELECT DISTINCT ah.Account_ID Account_ID
                                                              FROM c_AccountHouse ah
                                                              WHERE ah.Counter_ID = counter.id
                                                              AND ah.Data =
                                                              (SELECT MAX(Data)
                                                              FROM c_AccountHouse
                                                              WHERE Account_ID = ah.Account_ID
                                                              AND Data <= data_end_nach
                                                              AND Counter_ID = ah.Counter_ID)
                                                              AND ah.status = 1
                                                              )
                                       )
                                  AND norms.DATA in
                                      (SELECT MAX(DATA)
                                       FROM spr_norms
                                       where srvnorm_id IN
                                           (SELECT distinct s.norm_id
                                            FROM n_accountservice s
                                            WHERE s.account_id in (SELECT DISTINCT ah.Account_ID Account_ID
                                                                   FROM c_AccountHouse ah
                                                                   WHERE ah.Counter_ID = counter.id
                                                                   AND ah.Data =
                                                                       (SELECT MAX(Data)
                                                                         FROM c_AccountHouse
                                                                         WHERE Account_ID = ah.Account_ID
                                                                         AND Data <= data_end_nach
                                                                         AND Counter_ID = ah.Counter_ID)
                                                                         AND ah.status = 1
                                                                   )
                                           )
                                      )
                ) INNER_ JOIN N_ACCOUNTSERVICE ACCSERV ON ACCSERV.NORM_ID = INNER_.srvnorm_id
                WHERE ACCSERV.account_id in (SELECT DISTINCT ah.Account_ID Account_ID
                                            FROM c_AccountHouse ah
                                            WHERE ah.Counter_ID = counter.id
                                            AND ah.Data =
                                                (SELECT MAX(Data)
                                                 FROM c_AccountHouse
                                                 WHERE Account_ID = ah.Account_ID
                                                 AND Data <= data_end_nach
                                                 AND Counter_ID = ah.Counter_ID)
                                                 AND ah.status = 1
                                           )

           )
        /* group by Data, account_id*/
         ORDER BY Data, account_id;

        previus_d := HIST(HIST.FIRST).data;
        curr_d    := HIST(HIST.FIRST).data;
        SELECT MIN(DATA) INTO next_d FROM TABLE(HIST) WHERE DATA>previus_d;
        WHILE next_d IS NOT NULL LOOP
            dbms_output.put_line(previus_d||'-'||next_d);

            FOR HIST_ROW IN (select
               T.ACCID,
               T.Data,
               T.NORMA,
               T.STATE_SRV,
               T.SERVICE_ID,
               T.count_inhabit,
               T.COUNT_WANTING,
               T.AREA_POLEZN
            FROM TABLE(HIST) T WHERE T.DATA BETWEEN previus_d AND next_d) LOOP
                 dbms_output.put_line(  ' ACCOUNT_ID=' || HIST_ROW.accid
                                 ||' DATE='       || HIST_ROW.data
                                 ||' HABITTS='    || to_char((HIST_ROW.COUNT_INHABIT - HIST_ROW.COUNT_WANTING), '99')
                                 ||' NORM='       || to_char(HIST_ROW.NORMA, '999D99')
                                 ||' STATE='      || to_char(HIST_ROW.state_srv, '99')
                                 ||' AREA_POLEZN='|| to_char(HIST_ROW.AREA_POLEZN, '99')
                              );
            END LOOP;

            previus_d := next_d;
            SELECT MIN(DATA) T INTO next_d FROM TABLE(HIST) WHERE DATA>next_d;

         /* dbms_output.put_line(  ' ACCOUNT_ID=' || HIST(indx).accid
                                 ||' DATE='       || HIST(indx).data
                                 ||' HABITTS='    || to_char((HIST(indx).COUNT_INHABIT - HIST(indx).COUNT_WANTING), '99')
                                 ||' NORM='       || to_char(HIST(indx).NORMA, '999D99')
                                 ||' STATE='      || to_char(HIST(indx).state_srv, '99')
                                 ||' AREA_POLEZN='|| to_char(HIST(indx).AREA_POLEZN, '99')
                              );*/

        end loop;
      end loop;
  END loop;
END;