create or alter procedure SP_RECALC_DIVIDENDS (
    IN_ID_KCARD DMN_FK,
    IN_ID_TRANCHE DMN_FK,
    IN_RECALC_DATE DMN_DATE,
    START_YEAR integer,
    END_YEAR integer,
    RECALC_TEXT DMN_NAME)
as
declare variable CUR_YEAR integer;
declare variable VAR_ID_KCARD DMN_FK;
declare variable VAR_STOCK_TYPE DMN_FK;
declare variable VAR_ID_TRANCHE DMN_FK;
declare variable VAR_CDATE DMN_DATE;
declare variable VAR_CVAL DMN_INT;
declare variable VAR_TAX DMN_SUMMA;
declare variable VAR_PRICE DMN_SUMMA;
declare variable VAR_SUM_VAL DMN_SUMMA;
declare variable ID_RECALC DMN_PK;
declare variable ID_CHARGE DMN_PK;
declare variable VAR_ID_FILIAL integer;
begin
  /*Пересчёт дивидендов*/

  --считаем на один транш?
  IF (IN_ID_TRANCHE != 0) THEN
  BEGIN

    FOR SELECT ID, ID_KCARD, ID_STOCK_TYPE, ID_TRANCHE, CDATE, CVAL, TAX, PRICE, SUM_VAL FROM DIVIDENDS
    WHERE ID_KCARD = :IN_ID_KCARD AND ID_TRANCHE = :IN_ID_TRANCHE
    INTO :id_charge,  :VAR_ID_KCARD, :VAR_STOCK_TYPE, :VAR_ID_TRANCHE, :VAR_CDATE, :VAR_CVAL, :VAR_TAX, :VAR_PRICE, :VAR_SUM_VAL DO
    BEGIN
        id_recalc = GEN_ID(GEN_RECALC_ID, 1);
        INSERT INTO RECALC (ID, ID_KCARD, ID_TRANCHE, MAIN_DATE, DATE_RECALC, OLD_SUM, COUNT_STOCK, SUM_TAX, RECALC_TEXT)
        VALUES ( :ID_RECALC,  :VAR_ID_KCARD, :VAR_ID_TRANCHE, :VAR_CDATE, :IN_RECALC_DATE, :VAR_SUM_VAL, :VAR_CVAL, :VAR_TAX, :RECALC_TEXT);


        delete from DIVIDENDS where id = :ID_CHARGE;
        EXECUTE PROCEDURE SP_CHARGE ( :VAR_ID_KCARD, :VAR_ID_TRANCHE, :IN_RECALC_DATE, :ID_RECALC);
    END
    exit;
  END



  CUR_YEAR = START_YEAR;
  WHILE (CUR_YEAR <= END_YEAR) DO
  BEGIN

    FOR SELECT ID, ID_KCARD, ID_STOCK_TYPE, ID_TRANCHE, CDATE, CVAL, TAX, PRICE, SUM_VAL FROM DIVIDENDS
    WHERE ID_KCARD in (select id from kcard where id_parent = :IN_ID_KCARD) AND ID_TRANCHE IN (SELECT ID FROM TRANCHE WHERE TRANCHE.TRANCHE_YEAR = :CUR_YEAR)
    INTO :id_charge, :VAR_ID_KCARD, :VAR_STOCK_TYPE, :VAR_ID_TRANCHE, :VAR_CDATE, :VAR_CVAL, :VAR_TAX, :VAR_PRICE, :VAR_SUM_VAL DO
    BEGIN
        Select id_filial from kcard where id = :VAR_ID_KCARD Into :VAR_ID_FILIAL;

        id_recalc = GEN_ID(GEN_RECALC_ID, 1);
        INSERT INTO RECALC (ID, ID_KCARD, ID_TRANCHE, MAIN_DATE, DATE_RECALC, OLD_SUM, COUNT_STOCK, SUM_TAX, RECALC_TEXT)
        VALUES (:ID_RECALC, :VAR_ID_KCARD, :VAR_ID_TRANCHE, :VAR_CDATE, :IN_RECALC_DATE, :VAR_SUM_VAL, :VAR_CVAL, :VAR_TAX, :RECALC_TEXT);


        EXECUTE PROCEDURE SP_CHARGE (:VAR_ID_KCARD, :VAR_ID_TRANCHE, :VAR_ID_FILIAL, :IN_RECALC_DATE, :ID_RECALC);
        delete from DIVIDENDS where id = :ID_CHARGE;
    END    

    CUR_YEAR = CUR_YEAR + 1;
  END

end

create or alter procedure SP_CHARGE (
    ID_KCARD DMN_INT,
    ID_TRANCHE DMN_INT,
    ID_FILIAL integer,
    CHARGE_DATE DMN_DATE,
    IN_RECALC DMN_FK_NULL = 0)
as
declare variable ID_OWNER integer = 0;
declare variable STOCK_CNT integer;
declare variable IDS integer;
declare variable STOCK_PRICE integer;
declare variable VAR_BENEFIT integer;
declare variable VAR_COUNTRY integer;
declare variable IVALUE integer;
declare variable RESULT_VALUE numeric(15,2) = 0;
declare variable TAX_VALUE numeric(15,2) = 0;
declare variable VAR_TAX_REZIDENT numeric(15,2) = 0;
declare variable VAR_TAX_NO_REZIDENT numeric(15,2) = 0;
declare variable VAR_MIN_ZP numeric(15,2) = 0;
declare variable VAR_CNT_OLD_RECORD integer;
begin
  IF ((ID_FILIAL IS NULL) OR (ID_FILIAL = 0)) THEN
     EXCEPTION ERR_NO_SET_FILIAL;

  if ((:ID_KCARD != 0) AND (IN_RECALC = 0)) THEN
  BEGIN
  --проверить был ли в этом транше уже расчёт?
    if (exists(select id from DIVIDENDS D where D.ID_KCARD = :ID_KCARD AND d.ID_TRANCHE = :ID_TRANCHE)) then
       EXCEPTION ERR_MANY_DIVIDEND_IN_TRANCHE;
  END

  IF (:ID_KCARD = 0 ) THEN
  BEGIN
      SELECT COUNT(D.ID) FROM DIVIDENDS D WHERE D.ID_KCARD IN (select id from kcard where id_filial = :ID_FILIAL AND d.ID_TRANCHE = :ID_TRANCHE) INTO :VAR_CNT_OLD_RECORD;
      if (VAR_CNT_OLD_RECORD > 0)  then
          EXCEPTION ERR_MANY_DIVIDEND;
  END
  ELSE
  BEGIN
      --SELECT COUNT(D.ID) FROM DIVIDENDS D WHERE D.ID_KCARD IN (select id from kcard where (ID = :ID_KCARD OR ID_PARENT = :ID_KCARD) AND d.ID_TRANCHE = :ID_TRANCHE) INTO :VAR_CNT_OLD_RECORD;
      --if (VAR_CNT_OLD_RECORD > 0)  then
      --   EXCEPTION ERR_MANY_DIVIDEND;
  END



  --ставка по резидентам
  SELECT A.VAL_FLOAT FROM ATTRVAL A WHERE A.ID_ATTRIBUT = 1 INTO :VAR_TAX_REZIDENT;
  SELECT A.VAL_FLOAT FROM ATTRVAL A WHERE A.ID_ATTRIBUT = 2 INTO :VAR_TAX_NO_REZIDENT;
  SELECT A.VAL_FLOAT FROM ATTRVAL A WHERE A.ID_ATTRIBUT = 4 INTO :VAR_MIN_ZP;

  /* Начисление дивидендов */
  FOR SELECT DISTINCT
      VK.ID, VK.ID_INVALID, VK.ID_COUNTRY
      FROM SALDO_STOCK SS
      INNER JOIN VIEW_KCARD VK ON (SS.ID_KCARD = VK.ID)
      WHERE
           (VK.ID_TRANCHE = :ID_TRANCHE)
           AND (SS.STOCK_COUNT > 0)
           AND ((:ID_KCARD = 0) OR (VK.ID = :ID_KCARD)OR (VK.ID_PARENT = :ID_KCARD))
           AND (VK.ID_FILIAL = :ID_FILIAL)
  INTO :ID_OWNER, :VAR_BENEFIT, :VAR_COUNTRY  DO
  BEGIN
    --выбрать льготы для владельца
    IVALUE = 0;
    --льготы выражаются в количестве МРЗП, которое не облагается налогом
    IF (VAR_BENEFIT != 0) THEN
       SELECT RATE FROM REF_INVALID WHERE ID = :VAR_BENEFIT INTO :IVALUE;

    --перечислить имеющиеся акции с ценами
    FOR SELECT ST.ID_STOCKTYPE, ST.STOCK_COUNT, TP.STOCK_PRICE FROM SALDO_STOCK ST
        INNER JOIN TRANCHE_PRICE TP ON (ST.ID_STOCKTYPE = TP.ID_STOCK_TYPE)
    WHERE
         ST.ID_KCARD = :ID_OWNER
         AND TP.ID_TRANCHE = :ID_TRANCHE
         AND ST.STOCK_COUNT > 0
    INTO :IDS, :STOCK_CNT, :STOCK_PRICE
    DO
    BEGIN
         TAX_VALUE = 0;

         --общая сумма по дивидендам
         RESULT_VALUE = STOCK_CNT * STOCK_PRICE;

         --резидент?
         IF (VAR_COUNTRY = 1) THEN
         BEGIN
             --есть ли льготы?
             IF (VAR_BENEFIT != 0) THEN
             BEGIN
                --для льготников налог взимается с суммы, оставшейся от (общая сумма - сумма необлагаемого)
                IF ( RESULT_VALUE > (IVALUE * VAR_MIN_ZP) ) THEN
                   TAX_VALUE =  (RESULT_VALUE - (IVALUE * VAR_MIN_ZP) ) * VAR_TAX_REZIDENT / 100;
             END
             ELSE
                  TAX_VALUE = RESULT_VALUE * VAR_TAX_REZIDENT / 100;
         END
         ELSE
         BEGIN
              --для нерезидента
              TAX_VALUE = RESULT_VALUE * VAR_TAX_NO_REZIDENT / 100;
         END

         --добавить начисление
         INSERT INTO DIVIDENDS (
         ID_KCARD,
         ID_STOCK_TYPE,
         ID_TRANCHE,
         CDATE,
         CVAL,
         TAX,
         PRICE,
         SUM_VAL,
         RECALC_NREC
         )
         VALUES(
         :ID_OWNER,
         :IDS,
         :ID_TRANCHE,
         :CHARGE_DATE,
         :STOCK_CNT,
         :TAX_VALUE,
         :STOCK_PRICE,
         :RESULT_VALUE,
         :IN_RECALC
         );

    END
  END
end