"localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры

DECLARATIONCROP

Входные параметры

Выходные параметры

Описание

Бухгалтерия - Декларация

Определение

CREATE PROCEDURE DECLARATIONCROP(
    STORINGID INTEGER,
    DATE1 DATE,
    DATE2 DATE,
    PARTYID INTEGER,
    PARTYDATE DATE)
RETURNS (
    CROPCLASS VARCHAR(150),
    STARTREMINDER NUMERIC(15,1),
    ENDREMINDER NUMERIC(15,1),
    INCOME NUMERIC(15,1),
    OUTCOME NUMERIC(15,1))
AS
declare variable CROPID integer;
declare variable CLASSID integer;
declare variable SORTID integer;
declare variable TOTALSTART numeric(15,3);
declare variable TOTALINCOME numeric(15,3);
declare variable TOTALOUTCOME numeric(15,3);
declare variable TOTALEND numeric(15,3);
begin
  TOTALSTART = 0;
  TOTALINCOME = 0;
  TOTALOUTCOME = 0;
  TOTALend = 0;

  CROPCLASS = '1. Пшеница м`яка, усього';
  CROPID = 1;
  CLASSID = -1;
  SORTID = -3;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  TOTALSTART = :TOTALSTART + :STARTREMINDER;
  TOTALINCOME = :TOTALINCOME + :INCOME;
  TOTALOUTCOME = :TOTALOUTCOME + :OUTCOME;
  TOTALend = :TOTALEND + :ENDREMINDER;

  CROPCLASS = '  у т.ч. група А 1-го класу';
  CROPID = 1;
  CLASSID = 1;
  SORTID = -3;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '                 2-го класу';
  CROPID = 1;
  CLASSID = 2;
  SORTID = -3;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;


  CROPCLASS = '                 3-го класу';
  CROPID = 1;
  CLASSID = 3;
  SORTID = -3;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '         група Б 4-го класу';
  CROPID = 1;
  CLASSID = 4;
  SORTID = -3;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '                 5-го класу';
  CROPID = 1;
  CLASSID = 5;
  SORTID = -3;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '                 6-го класу';
  CROPID = 1;
  CLASSID = 6;
  SORTID = -3;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '                 некласифікована';
  STARTREMINDER = 0;
  ENDREMINDER = 0;
  INCOME = 0;
  OUTCOME = 0;
  suspend;

  CROPCLASS = '2. Пшеница тверда';
  CROPID = 1;
  CLASSID = -1;
  SORTID = 3;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  TOTALSTART = :TOTALSTART + :STARTREMINDER;
  TOTALINCOME = :TOTALINCOME + :INCOME;
  TOTALOUTCOME = :TOTALOUTCOME + :OUTCOME;
  TOTALend = :TOTALEND + :ENDREMINDER;

  CROPCLASS = '3. Жито, усього';
  CROPID = 3;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  TOTALSTART = :TOTALSTART + :STARTREMINDER;
  TOTALINCOME = :TOTALINCOME + :INCOME;
  TOTALOUTCOME = :TOTALOUTCOME + :OUTCOME;
  TOTALend = :TOTALEND + :ENDREMINDER;

  CROPCLASS = '  у т.ч. 1-го класу';
  CROPID = 3;
  CLASSID = 10;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '         2-го класу';
  CROPID = 3;
  CLASSID = 11;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '         3-го класу';
  CROPID = -1;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '         4-го класу';
  CROPID = -1;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '4. Ячмінь, усього';
  CROPID = 2;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  TOTALSTART = :TOTALSTART + :STARTREMINDER;
  TOTALINCOME = :TOTALINCOME + :INCOME;
  TOTALOUTCOME = :TOTALOUTCOME + :OUTCOME;
  TOTALend = :TOTALEND + :ENDREMINDER;

  CROPCLASS = '  у т.ч. пивоварний';
  CROPID = 2;
  CLASSID = -1;
  SORTID = 23;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '5. Овес';
  CROPID = 48;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  TOTALSTART = :TOTALSTART + :STARTREMINDER;
  TOTALINCOME = :TOTALINCOME + :INCOME;
  TOTALOUTCOME = :TOTALOUTCOME + :OUTCOME;
  TOTALend = :TOTALEND + :ENDREMINDER;

  CROPCLASS = '6. Кукурудза';
  CROPID = 4;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  TOTALSTART = :TOTALSTART + :STARTREMINDER;
  TOTALINCOME = :TOTALINCOME + :INCOME;
  TOTALOUTCOME = :TOTALOUTCOME + :OUTCOME;
  TOTALend = :TOTALEND + :ENDREMINDER;

  CROPCLASS = '7. Зернобобові, усього';
  CROPID = -1;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '  у т.ч. горох';
  CROPID = 51;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  TOTALSTART = :TOTALSTART + :STARTREMINDER;
  TOTALINCOME = :TOTALINCOME + :INCOME;
  TOTALOUTCOME = :TOTALOUTCOME + :OUTCOME;
  TOTALend = :TOTALEND + :ENDREMINDER;

  CROPCLASS = '8. Просо';
  CROPID = 50;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  TOTALSTART = :TOTALSTART + :STARTREMINDER;
  TOTALINCOME = :TOTALINCOME + :INCOME;
  TOTALOUTCOME = :TOTALOUTCOME + :OUTCOME;
  TOTALend = :TOTALEND + :ENDREMINDER;

  CROPCLASS = '9. Гречка';
  CROPID = 73;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  TOTALSTART = :TOTALSTART + :STARTREMINDER;
  TOTALINCOME = :TOTALINCOME + :INCOME;
  TOTALOUTCOME = :TOTALOUTCOME + :OUTCOME;
  TOTALend = :TOTALEND + :ENDREMINDER;

  CROPCLASS = '10. Рис';
  CROPID = 74;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  TOTALSTART = :TOTALSTART + :STARTREMINDER;
  TOTALINCOME = :TOTALINCOME + :INCOME;
  TOTALOUTCOME = :TOTALOUTCOME + :OUTCOME;
  TOTALend = :TOTALEND + :ENDREMINDER;

  CROPCLASS = '11. Тритікале';
  CROPID = 71;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  TOTALSTART = :TOTALSTART + :STARTREMINDER;
  TOTALINCOME = :TOTALINCOME + :INCOME;
  TOTALOUTCOME = :TOTALOUTCOME + :OUTCOME;
  TOTALend = :TOTALEND + :ENDREMINDER;

  CROPCLASS = '12. Зерносуміш';
  CROPID = 49;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  TOTALSTART = :TOTALSTART + :STARTREMINDER;
  TOTALINCOME = :TOTALINCOME + :INCOME;
  TOTALOUTCOME = :TOTALOUTCOME + :OUTCOME;
  TOTALend = :TOTALEND + :ENDREMINDER;

  CROPCLASS = '13. Сортове та гібридне насіння, усього';
  CROPID = -1;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '  у т.ч. озимих зернових культур';
  CROPID = -1;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '       ярих зернових і зернобобових культур';
  CROPID = -1;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '       кукурудзи';
  CROPID = -1;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  TOTALSTART = :TOTALSTART + :STARTREMINDER;
  TOTALINCOME = :TOTALINCOME + :INCOME;
  TOTALOUTCOME = :TOTALOUTCOME + :OUTCOME;
  TOTALend = :TOTALEND + :ENDREMINDER;

/*  cropclass = ' ярих зернових і зернобобових культур кукурудзи';
  Cropid = -1;
  classid = -1;
  sortid = -1;

  select * from DECLARATIONSTORING(:STORINGID,:DATE1,:DATE2,:PARTYID,:CROPID,:CLASSID, :SORTID, :PARTYDATE) D
  into
  :STARTREMINDER,
  :ENDREMINDER,
  :INCOME,
  :OUTCOME;
  suspend;*/

  CROPCLASS = 'УСЬОГО ЗЕРНА';
  CROPID = -1;
  CLASSID = -1;
  SORTID = -1;

  STARTREMINDER = TOTALSTART;
  ENDREMINDER = TOTALEND;
  INCOME = TOTALINCOME;
  OUTCOME = TOTALOUTCOME;
  suspend;

  CROPCLASS = 'Насіння олійних культур, усього';
  CROPID = -1;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '14. Соняшник';
  CROPID = 37;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '15. Соя';
  CROPID = 82;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '16. Ріпак';
  CROPID = 81;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

  CROPCLASS = '17. Інше';
  CROPID = -1;
  CLASSID = -1;
  SORTID = -1;
  select *
    from DECLARATIONSTORING(:STORINGID, :DATE1, :DATE2, :PARTYID, :CROPID, :CLASSID, :SORTID, :PARTYDATE) D
  into :STARTREMINDER, :ENDREMINDER, :INCOME, :OUTCOME;
  suspend;

end