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

TRANSPORTPLAN_TRANSPORTJOURNAL

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

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

Описание

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

Определение

CREATE PROCEDURE TRANSPORTPLAN_TRANSPORTJOURNAL(
    BEGINDATE DATE,
    ENDDATE DATE,
    AOWNERID INTEGER,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    TRSUBTYPE INTEGER,
    AOPERATIONID INTEGER)
RETURNS (
    OWNERNAME VARCHAR(100),
    CROPFULLNAME VARCHAR(100),
    PLANTR VARCHAR(35),
    TRANSPORTPLANID INTEGER,
    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))
AS
DECLARE VARIABLE t_cropid INTEGER;
DECLARE VARIABLE t_classid INTEGER;
DECLARE VARIABLE t_ownerid INTEGER;
DECLARE VARIABLE t_counttr INTEGER;
DECLARE VARIABLE t_countnet INTEGER;
DECLARE VARIABLE tc_1 VARCHAR(35);
DECLARE VARIABLE tc_all VARCHAR(35);
DECLARE VARIABLE tm_counttr INTEGER;
DECLARE VARIABLE tm_countnet INTEGER;
DECLARE VARIABLE daynet VARCHAR(2);
BEGIN
  FOR
    select
      owner.clientfullname as ownername,
      quality_cropfullname.cropfullname,
      itmain.ownerid,
      qm.cropid,
      qm.classid,
      case
        when not trplan.transportplanid is null and :trsubtype <> -1
        then coalesce(trplan.planquantity,'')||' / '||coalesce(trplan.planweight,'')
      end plantr,
      trplan.transportplanid
    from it itmain
     left join clients owner on (itmain.ownerid = owner.clientid)
     left join quality qm on (itmain.samplerqualityid = qm.qualityid)
     left join parties  party1 on (itmain.partyid = party1.partyid)
     left join quality_cropfullname(qm.cropid, qm.classid, null,1) on (0 = 0)
     left join ittransport trt on (itmain.inventoryid = trt.inventoryid)
     left join transportplan_getplan(:begindate,itmain.ownerid,:trsubtype,:aoperationid,qm.cropid, qm.classid,null) trplan on (0=0)
     left join transporttype tt on (trt.transporttypeid = tt.transporttypeid)
    where itmain.regdate >= :begindate and itmain.regdate < :enddate
      and itmain.regdate >= party1.begindate
      and itmain.operationid = :aoperationid
      and (tt.transportsubtype = :trsubtype)
      and not itmain.ownerid is null --потом убрать
      and not qm.cropid is null --потом убрать
      and (itmain.ownerid = :aownerid or (:aownerid = -1))
      and (qm.cropid = :acropid or (:acropid = -1))
      and (qm.classid = :aclassid or (:aclassid = -1))
      and party1.state = 1
      and itmain.storageid = 1
      and not itmain.net is null
      and not itmain.samplerqualityid is null
      and itmain.state in (0,1)
    group by
      owner.clientfullname,
      quality_cropfullname.cropfullname,
      itmain.ownerid,
      qm.cropid,
      qm.classid,
      plantr,
      trplan.transportplanid
    INTO :ownername,
         :cropfullname,
         :t_ownerid,
         :t_cropid,
         :t_classid,
         :plantr,
         :transportplanid
  DO
  BEGIN
    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
      left 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 = :t_ownerid
      --ITm.RegDate between :BeginDate and :EndDate
      and q1.classid = :t_classid
      and q1.cropid = :t_cropid
      and not itm.net is null
      and party2.state = 1
      and itm.storageid = 1
      and itm.state in (0,1)
    group by
        daynet,
        itm.ownerid,
        q1.cropid,
        q1.classid
    INTO :daynet,
         :t_counttr,
         :t_countnet
    DO
    BEGIN
      tc_1 = :t_counttr||' / '||:t_countnet;
      tm_countnet = tm_countnet + :t_countnet;
      tm_counttr = tm_counttr + :t_counttr;
      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
    END
    SUSPEND;
  END
END