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

TRANSPORTPLAN_JOURNALSUM

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

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

Описание

(Нет описания для процедуры TRANSPORTPLAN_JOURNALSUM)

Определение

CREATE PROCEDURE TRANSPORTPLAN_JOURNALSUM(
    BEGINDATE DATE,
    ENDDATE DATE,
    AOWNERID INTEGER,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    TRSUBTYPE INTEGER,
    AOPERATIONID INTEGER)
RETURNS (
    D01 VARCHAR(35),
    D02 VARCHAR(35),
    D03 VARCHAR(35),
    D04 VARCHAR(35),
    D05 VARCHAR(35),
    D06 VARCHAR(35),
    D07 VARCHAR(35),
    D08 VARCHAR(35),
    D09 VARCHAR(35),
    D10 VARCHAR(35),
    D11 VARCHAR(35),
    D12 VARCHAR(35),
    D13 VARCHAR(35),
    D14 VARCHAR(35),
    D15 VARCHAR(35),
    D16 VARCHAR(35),
    D17 VARCHAR(35),
    D18 VARCHAR(35),
    D19 VARCHAR(35),
    D20 VARCHAR(35),
    D21 VARCHAR(35),
    D22 VARCHAR(35),
    D23 VARCHAR(35),
    D24 VARCHAR(35),
    D25 VARCHAR(35),
    D26 VARCHAR(35),
    D27 VARCHAR(35),
    D28 VARCHAR(35),
    D29 VARCHAR(35),
    D30 VARCHAR(35),
    D31 VARCHAR(35),
    DS01 VARCHAR(35),
    DS02 VARCHAR(35),
    DS03 VARCHAR(35),
    DS04 VARCHAR(35),
    DS05 VARCHAR(35),
    DS06 VARCHAR(35),
    DS07 VARCHAR(35),
    DS08 VARCHAR(35),
    DS09 VARCHAR(35),
    DS10 VARCHAR(35),
    DS11 VARCHAR(35),
    DS12 VARCHAR(35),
    DS13 VARCHAR(35),
    DS14 VARCHAR(35),
    DS15 VARCHAR(35),
    DS16 VARCHAR(35),
    DS17 VARCHAR(35),
    DS18 VARCHAR(35),
    DS19 VARCHAR(35),
    DS20 VARCHAR(35),
    DS21 VARCHAR(35),
    DS22 VARCHAR(35),
    DS23 VARCHAR(35),
    DS24 VARCHAR(35),
    DS25 VARCHAR(35),
    DS26 VARCHAR(35),
    DS27 VARCHAR(35),
    DS28 VARCHAR(35),
    DS29 VARCHAR(35),
    DS30 VARCHAR(35),
    DS31 VARCHAR(35),
    PLANTR VARCHAR(35))
AS
declare variable daynet integer;
declare variable t_counttr integer;
declare variable t_countnet integer;
declare variable tc_1 varchar(35);
declare variable tm_countnet integer;
declare variable tm_counttr integer;
declare variable tn_q integer;
declare variable tn_w integer;
declare variable tc_all varchar(35);
begin
    select
      sum(trplan.planquantity),
      sum(trplan.planweight)
    from transportplan_getplan(:begindate,:aownerid,:trsubtype,:aoperationid,:acropid,:aclassid,1) trplan
    into :tn_q,
         :tn_w;
    plantr = coalesce(:tn_q,'')||' / '||coalesce(:tn_w,'');


    d01 = null;d02 = null;d03 = null;d04 = null;d05 = null;d06 = null;d07 = null;d08 = null;d09 = null;d10 = null;
    d11 = null;d12 = null;d13 = null;d14 = null;d15 = null;d16 = null;d17 = null;d18 = null;d19 = null;d20 = null;
    d21 = null;d22 = null;d23 = null;d24 = null;d25 = null;d26 = null;d27 = null;d28 = null;d29 = null;d30 = null;d31 = null;
    ds01 = null;ds02 = null;ds03 = null;ds04 = null;ds05 = null;ds06 = null;ds07 = null;ds08 = null;ds09 = null;ds10 = null;
    ds11 = null;ds12 = null;ds13 = null;ds14 = null;ds15 = null;ds16 = null;ds17 = null;ds18 = null;ds19 = null;ds20 = null;
    ds21 = null;ds22 = null;ds23 = null;ds24 = null;ds25 = null;ds26 = null;ds27 = null;ds28 = null;ds29 = null;ds30 = null;ds31 = null;

    tm_countnet = 0; tm_counttr = 0;
  for
    select
      extract(day from itm.regdate) daynet,
      count(case
        when tr.transporttypeid = 3 then itm.inventoryid
        when tr.transporttypeid = 2 then itm.invoiceid
        when tr.transporttypeid = 1 then itm.invoiceid
        when tr.transporttypeid = 0
          and not exists (select it1.inventoryid from it it1
            left join ittransport tr2 on (it1.inventoryid = tr2.inventoryid)
            where  it1.invoiceid = itm.invoiceid and tr2.transporttypeid = 1 and not it1.net is null ) then itm.invoiceid
        when tr.transporttypeid = 0
          and not exists (select it1.inventoryid from it it1
            left join ittransport tr2 on (it1.inventoryid = tr2.inventoryid)
            left join quality q2 on (it1.samplerqualityid = q2.qualityid)
            where  it1.invoiceid = itm.invoiceid and tr2.transporttypeid = 1
              and q2.classid = q1.classid) then itm.invoiceid
        end),
          sum(itm.net) sumnetr
        from it itm
          join quality q1 on (itm.samplerqualityid = q1.qualityid)
          left join ittransport tr on (itm.inventoryid = tr.inventoryid)
          left join parties party2 on (itm.partyid = party2.partyid)
          left join transporttype tt2 on (tr.transporttypeid = tt2.transporttypeid)

        where  itm.regdate >= :begindate and itm.regdate < :enddate
          and itm.operationid = :aoperationid
          and (tt2.transportsubtype = :trsubtype)
          and (itm.ownerid = :aownerid or (:aownerid = -1))
          and (q1.classid = :aclassid or (:aclassid = -1))
          and (q1.cropid = :acropid or (:acropid = -1))
          and not itm.net is null
          and party2.state = 1
          and itm.storageid = 1
          and itm.state in (0,1)
        group by
          daynet

    into :daynet,
         :t_counttr,
         :t_countnet
  do
  begin
      tc_1 = :t_counttr||' / '||:t_countnet;
      tm_countnet = tm_countnet + coalesce(:t_countnet,0);
      tm_counttr = tm_counttr + coalesce(:t_counttr,0);
      tc_all = :tm_counttr||' / '||:tm_countnet;
      if (:daynet = '1') then begin  d01 = :tc_1; ds01 = :tc_all; end
      else if (:daynet = '2') then begin  d02 = tc_1; ds02 = tc_all; end
       else if (:daynet = '3') then begin  d03 = tc_1; ds03 = tc_all; end
        else if (:daynet = '4') then begin  d04 = tc_1; ds04 = tc_all; end
         else if (:daynet = '5') then begin  d05 = tc_1; ds05 = tc_all; end
          else if (:daynet = '6') then begin  d06 = tc_1; ds06 = tc_all; end
           else if (:daynet = '7') then begin  d07 = tc_1; ds07 = tc_all; end
            else if (:daynet = '8') then begin  d08 = tc_1; ds08 = tc_all; end
             else if (:daynet = '9') then begin  d09 = tc_1; ds09 = tc_all; end
              else if (:daynet = '10') then begin  d10 = tc_1; ds10 = tc_all; end
               else if (:daynet = '11') then begin  d11 = tc_1; ds11 = tc_all; end
                else if (:daynet = '12') then begin  d12 = tc_1; ds12 = tc_all; end
                 else if (:daynet = '13') then begin  d13 = tc_1; ds13 = tc_all; end
                  else if (:daynet = '14') then begin  d14 = tc_1; ds14 = tc_all; end
                   else if (:daynet = '15') then begin  d15 = tc_1; ds15 = tc_all; end
                    else if (:daynet = '16') then begin  d16 = tc_1; ds16 = tc_all; end
                     else if (:daynet = '17') then begin  d17 = tc_1; ds17 = tc_all; end
                      else if (:daynet = '18') then begin  d18 = tc_1; ds18 = tc_all; end
                       else if (:daynet = '19') then begin  d19 = tc_1; ds19 = tc_all; end
                        else if (:daynet = '20') then begin  d20 = tc_1; ds20 = tc_all; end
                         else if (:daynet = '21') then begin  d21 = tc_1; ds21 = tc_all; end
                          else if (:daynet = '22') then begin  d22 = tc_1; ds22 = tc_all; end
                           else if (:daynet = '23') then begin  d23 = tc_1; ds23 = tc_all; end
                            else if (:daynet = '24') then begin  d24 = tc_1; ds24 = tc_all; end
                             else if (:daynet = '25') then begin  d25 = tc_1; ds25 = tc_all; end
                              else if (:daynet = '26') then begin  d26 = tc_1; ds26 = tc_all; end
                               else if (:daynet = '27') then begin  d27 = tc_1; ds27 = tc_all; end
                                else if (:daynet = '28') then begin  d28 = tc_1; ds28 = tc_all; end
                                 else if (:daynet = '29') then begin  d29 = tc_1; ds29 = tc_all; end
                                  else if (:daynet = '30') then begin  d30 = tc_1; ds30 = tc_all; end
                                   else if (:daynet = '31') then begin  d31 = tc_1; ds31 = tc_all; end
      tc_1 = null;
 --  suspend;
    end
    suspend;
end