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

CLIENTOBOROT

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

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

Описание

Оборотная ведомость движения зерна по клиенту - культурам

Определение

CREATE PROCEDURE CLIENTOBOROT(
    DATE1 DATE,
    DATE2 DATE,
    OWNERID INTEGER,
    CONTRACTID INTEGER,
    STORAGEID INTEGER,
    CLASSID INTEGER,
    DOCROP INTEGER,
    CROPID INTEGER,
    PARTYID INTEGER,
    PARTYDATE DATE,
    STORINGID INTEGER)
RETURNS (
    REGDATE DATE,
    PROVIDER VARCHAR(30),
    OPERATIONSUBTYPE INTEGER,
    INVOICENUMBEROUT VARCHAR(31),
    INVOICENUMBERIN VARCHAR(31),
    OPERATIONID INTEGER,
    STATIONNAME VARCHAR(128),
    INCOMENET INTEGER,
    OUTCOME INTEGER,
    INCOMEWEIGHT INTEGER,
    CROPNAME VARCHAR(100),
    INITIAL_REMINDER INTEGER,
    DIRT NUMERIC(5,2),
    HUM NUMERIC(5,2),
    SCREEN NUMERIC(5,2),
    IDK INTEGER,
    KLEIK INTEGER,
    OUTCOMEWEIGHT INTEGER,
    INITIAL_REMINDERWEIGHT INTEGER)
AS
declare variable INVOICENUMBER varchar(31);
declare variable NET integer;
declare variable WEIGHT integer;
declare variable TRASH integer;
declare variable OUTWEIGHT integer;
begin
/*
  select coalesce(sum(IT.NET), 0), coalesce(sum(IT.WEIGHT), 0)
    from IT
      join GETWORKPARTIES(:PARTYID, :PARTYDATE, :CROPID, :CLASSID, :STORAGEID) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
      left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
      left join CLASS on (CLASS.CLASSID = QUALITY.CLASSID)
      left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
    where (IT.REGDATE < :DATE1)
      and (IT.OWNERID = :OWNERID)
      and ((IT.STATE >= 0) and (IT.PARENTID >= 0))
      and (IT.STORAGEID = :STORAGEID)
      and ((IT.STORINGID = :STORINGID) or (:STORINGID = 0))
      and ((IT.CONTRACTID = :CONTRACTID) or (:CONTRACTID = 0))
      and (((QUALITY.CLASSID = :CLASSID) and (:DOCROP = 0)) or ((QUALITY.CROPID = :CROPID) and (:DOCROP = 1)) or (:DOCROP = 2))
      and (OPERATION.OPERATIONSUBTYPE = 0)
  into :INITIAL_REMINDER, :INITIAL_REMINDERWEIGHT;

  select coalesce(sum(IT.NET),0), coalesce(sum(IT.WEIGHT),0)
    from IT
      join GetWorkParties(:PARTYID,:PARTYDATE, :CROPID, :CLASSID,:STORAGEID)
      on (IT.PARTYID = GetWorkParties.PARTYID)
    left join QUALITY on (IT.SAMPLERQUALITYID = QUALITY.QUALITYID)
    left join Class on (QUALITY.CLASSID = Class.CLASSID)
    left join OPERATION on (IT.OPERATIONID = OPERATION.OPERATIONID)
  where  IT.REGDATE < :DATE1
    and IT.OWNERID = :OWNERID
    and ((QUALITY.CLASSID = :CLASSID and :DOCROP = 0)
      or (QUALITY.CROPID = :CROPID and :DOCROP = 1) or (:DOCROP = 2))
    and IT.STATE >= 0
    and IT.PARENTID >= 0
    and (OPERATION.OPERATIONSUBTYPE = 1)
    and IT.STORAGEID = :STORAGEID
    and ((IT.STORINGID = :STORINGID) or (:STORINGID = 0))
    and (IT.CONTRACTID = :CONTRACTID or (:CONTRACTID = 0))
  into :NET,
     :OutWEIGHT;

  Initial_Reminder = :Initial_Reminder - :NET;
  Initial_ReminderWEIGHT = :Initial_ReminderWEIGHT - :OutWEIGHT;
*/

  select
      sum(case
            when (OPERATION.OPERATIONSUBTYPE = 0) then
              coalesce(IT.NET, 0)
            when (OPERATION.OPERATIONSUBTYPE = 1) then
              coalesce(-IT.NET, 0)
          end),
      sum(case
            when (OPERATION.OPERATIONSUBTYPE = 0) then
              coalesce(IT.WEIGHT, 0)
            when ((OPERATION.OPERATIONSUBTYPE = 1) and (IT.OPERATIONID <> 22)) then
              coalesce(-IT.WEIGHT, 0)
          end)
    from IT
      join GETWORKPARTIES(:PARTYID, :PARTYDATE, :CROPID, :CLASSID, :STORAGEID) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
      left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
      left join CLASS on (CLASS.CLASSID = QUALITY.CLASSID)
      left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
    where (IT.REGDATE < :DATE1)
      and (IT.OWNERID = :OWNERID)
      and ((IT.STATE >= 0) and (IT.PARENTID >= 0))
      and (IT.STORAGEID = :STORAGEID)
      and ((IT.STORINGID = :STORINGID) or (:STORINGID = 0))
      and ((IT.CONTRACTID = :CONTRACTID) or (:CONTRACTID = 0))
      and (((QUALITY.CLASSID = :CLASSID) and (:DOCROP = 0)) or ((QUALITY.CROPID = :CROPID) and (:DOCROP = 1)) or (:DOCROP = 2))
--      and (OPERATION.OPERATIONSUBTYPE = 0)
  into :INITIAL_REMINDER, :INITIAL_REMINDERWEIGHT;

  for  --avto
    select
        IT.REGDATE, CLIENTS.CLIENTNAME PROVIDER, OPERATION.OPERATIONSUBTYPE,
        cast(coalesce(sum(IT.NET), 0) as integer) NET,
        cast(coalesce(sum(IT.WEIGHT), 0) as integer) WEIGHT,
        cast(coalesce(INVOICEREGISTERS.REGISTERNUMBER, '') as varchar(31)) INVOICENUMBER,
        IT.OPERATIONID, cast(' ' as varchar(128)) STATIONNAME,
        QUALITY_CROPFULLNAME.CROPFULLNAME, OPERATION.OPERATIONSUBTYPE,
        QUALITY_SELECT.DIRT, QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.SCREENING,
        QUALITY_SELECT.GLUTENQUALITY, QUALITY_SELECT.GLUTEN
      from IT
        join GETWORKPARTIES(:PARTYID, :PARTYDATE, :CROPID, :CLASSID, :STORAGEID) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
        left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
        left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
        left join STATIONS on (STATIONS.STATIONID = INVOICES.STATIONID)
        left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
        left join QUALITY_SELECT(IT.BUHQUALITYID) on (0 = 0)
        left join CLIENTS on (CLIENTS.CLIENTID = IT.PROVIDERID)
        left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
        left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, null, 0) on (0 = 0)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
      where (IT.REGDATE between :DATE1 and :DATE2)
        and (IT.OWNERID = :OWNERID)
        and (ITTRANSPORT.TRANSPORTTYPEID in (0,1))
        and ((IT.STATE >= 0) and (IT.PARENTID >= 0))
        and (IT.STORAGEID = :STORAGEID)
        and ((IT.STORINGID = :STORINGID) or (:STORINGID = 0))
        and (((QUALITY.CLASSID = :CLASSID) and (:DOCROP = 0)) or ((QUALITY.CROPID = :CROPID) and (:DOCROP = 1)) or (:DOCROP = 2))
        and (OPERATION.OPERATIONSUBTYPE  in (0,1))
        and (OPERATION.OPERATIONTYPE not in (2,1,5))
        and ((IT.CONTRACTID = :CONTRACTID) or (:CONTRACTID = 0))
    group by IT.REGDATE, CLIENTS.CLIENTNAME, OPERATION.OPERATIONSUBTYPE,
      IT.OPERATIONID, INVOICEREGISTERS.REGISTERNUMBER, QUALITY_CROPFULLNAME.CROPFULLNAME,
      QUALITY_SELECT.DIRT, QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.SCREENING,
      QUALITY_SELECT.GLUTENQUALITY, QUALITY_SELECT.GLUTEN
    union all
--in, out -transport - null
      select
          IT.REGDATE, CLIENTS2.CLIENTNAME PROVIDER, OPERATION.OPERATIONSUBTYPE,
          coalesce(IT.NET, 0), coalesce(IT.WEIGHT, 0), INVOICES.INVOICENUMBER,
          IT.OPERATIONID,
          case
            when (IT.OPERATIONID = 4) then
              cast(OPERATION.OPERATIONNAME||' '||CLIENTS1.CLIENTNAME as varchar(128))
            else cast(' ' as varchar(128))
          end STATIONNAME,
          QUALITY_CROPFULLNAME.CROPFULLNAME, IT.INVENTORYID, QUALITY_SELECT.DIRT,
          QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.SCREENING,
          QUALITY_SELECT.GLUTENQUALITY, QUALITY_SELECT.GLUTEN
      from IT
        join GETWORKPARTIES(:PARTYID, :PARTYDATE, :CROPID, :CLASSID, :STORAGEID) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
        left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
        left join STATIONS on (STATIONS.STATIONID = INVOICES.STATIONID)
        left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
        left join QUALITY_SELECT(IT.BUHQUALITYID) on (0 = 0)
        left join CLIENTS CLIENTS1 ON (CLIENTS1.CLIENTID = IT.OWNERID)
        left join CLIENTS CLIENTS2 ON (CLIENTS2.CLIENTID = IT.PROVIDERID)
        left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
        left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, null, 0) on (0 = 0)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        left join OPERATIONS_OPERATIONNAME(IT.OPERATIONID, IT.OPERATIONParam) on (0 = 0)
        left join FORM36ADATATEXT(cast(null as varchar(30)), cast(null as varchar(30)),
            OPERATIONS_OPERATIONNAME.OPERATIONNAME, IT.OPERATIONID, CLIENTS2.CLIENTNAME,
            CLIENTS1.CLIENTNAME, null) on (0 = 0)
      where (IT.REGDATE between :DATE1 and :DATE2)
        and (IT.OWNERID = :OWNERID)
        and ((ITTRANSPORT.TRANSPORTTYPEID not in (0,1,2)) or (ITTRANSPORT.TRANSPORTTYPEID is null))
        and ((IT.STATE >= 0) and (IT.PARENTID >= 0))
        and (((QUALITY.CLASSID = :CLASSID) and (:DOCROP = 0)) or ((QUALITY.CROPID = :CROPID) and (:DOCROP = 1)) or (:DOCROP = 2))
        and (IT.STORAGEID = :STORAGEID)
        and (OPERATION.OPERATIONTYPE not in (2,1,5))
        and (OPERATION.OPERATIONSUBTYPE in (0,1))
        and ((IT.STORINGID = :STORINGID) or (:STORINGID = 0))
        and ((IT.CONTRACTID = :CONTRACTID) or (:CONTRACTID = 0))
    union all
--railroad
      select
          IT.REGDATE, CLIENTS2.CLIENTNAME PROVIDER, OPERATION.OPERATIONSUBTYPE,
          coalesce(IT.NET, 0), coalesce(IT.WEIGHT, 0),
          cast(INVOICEREGISTERS.REGISTERNUMBER as varchar(31)) INVOICENUMBER,
          IT.OPERATIONID, cast(STATIONS.STATIONNAME as varchar(128)),
          QUALITY_CROPFULLNAME.CROPFULLNAME, IT.INVENTORYID,
          QUALITY_SELECT.DIRT, QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.SCREENING,
          QUALITY_SELECT.GLUTENQUALITY, QUALITY_SELECT.GLUTEN
        from IT
          join GETWORKPARTIES(:PARTYID, :PARTYDATE, :CROPID, :CLASSID, :STORAGEID) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
          left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
          left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
          left join STATIONS on (STATIONS.STATIONID = INVOICES.STATIONID)
          left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
          left join QUALITY_SELECT(IT.BUHQUALITYID) on (0 = 0)
          left join CLIENTS CLIENTS1 ON (CLIENTS1.CLIENTID = IT.OWNERID)
          left join CLIENTS CLIENTS2 ON (CLIENTS2.CLIENTID = IT.PROVIDERID)
          left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
          left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, null, 0) on (0 = 0)
          left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        where (IT.REGDATE between :DATE1 and :DATE2)
          and (IT.OWNERID = :OWNERID)
          and ((IT.STATE >= 0) and (IT.PARENTID >= 0))
          and (ITTRANSPORT.TRANSPORTTYPEID = 2)
        and (((QUALITY.CLASSID = :CLASSID) and (:DOCROP = 0)) or ((QUALITY.CROPID = :CROPID) and (:DOCROP = 1)) or (:DOCROP = 2))
          and (IT.STORAGEID = :STORAGEID)
          and (OPERATION.OPERATIONSUBTYPE in (0,1))
          and (OPERATION.OPERATIONTYPE not in (2,1,5))
          and ((IT.STORINGID = :STORINGID) or (:STORINGID = 0))
          and ((IT.CONTRACTID = :CONTRACTID) or (:CONTRACTID = 0))
    union all
--dryer
      select
          IT.REGDATE, CLIENTS2.CLIENTNAME PROVIDER, OPERATION.OPERATIONSUBTYPE,
          coalesce(IT.NET, 0), cast(coalesce(-IT.WEIGHT, 0) as integer) WEIGHT,
          INVOICES.INVOICENUMBER, IT.OPERATIONID,
          cast(OPERATION.OPERATIONNAME as varchar(128)) STATIONNAME,
          QUALITY_CROPFULLNAME.CROPFULLNAME, IT.INVENTORYID,
          QUALITY_SELECT.DIRT, QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.SCREENING,
          QUALITY_SELECT.GLUTENQUALITY, QUALITY_SELECT.GLUTEN
        from IT
          join GETWORKPARTIES(:PARTYID, :PARTYDATE, :CROPID, :CLASSID, :STORAGEID) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
          left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
          left join STATIONS on (STATIONS.STATIONID = INVOICES.STATIONID)
          left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
          left join QUALITY_SELECT(IT.BUHQUALITYID) on (0 = 0)
          left join CLIENTS CLIENTS1 on (CLIENTS1.CLIENTID = IT.OWNERID)
          left join CLIENTS CLIENTS2 on (CLIENTS2.CLIENTID = IT.PROVIDERID)
          left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
          left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, null, 0) on (0 = 0)
          left join OPERATIONS_OPERATIONNAME(IT.OPERATIONID, IT.OPERATIONParam) on (0 = 0)
          left join FORM36ADATATEXT(cast(null as varchar(30)), cast(null as varchar(30)),
              OPERATIONS_OPERATIONNAME.OPERATIONNAME, IT.OPERATIONID, CLIENTS2.CLIENTNAME,
              CLIENTS1.CLIENTNAME, null) on (0 = 0)
        where (IT.REGDATE between :DATE1 and :DATE2)
          and (IT.OWNERID = :OWNERID)
          and ((IT.STATE >= 0) and (IT.PARENTID >= 0))
          and (((QUALITY.CLASSID = :CLASSID) and (:DOCROP = 0)) or ((QUALITY.CROPID = :CROPID) and (:DOCROP = 1)) or (:DOCROP = 2))
          and (IT.STORAGEID = :STORAGEID)
          and (OPERATION.OPERATIONTYPE = 2)
          and ((IT.STORINGID = :STORINGID) or (:STORINGID = 0))
          and ((IT.CONTRACTID = :CONTRACTID) or (:CONTRACTID = 0))
    union all
--wastes
      select
          IT.REGDATE, CLIENTS2.CLIENTNAME PROVIDER, OPERATION.OPERATIONSUBTYPE,
          coalesce(IT.NET, 0), coalesce(IT.WEIGHT,0),
          INVOICES.INVOICENUMBER, IT.OPERATIONID,
          cast(FORM36ADATATEXT.OWNERPROVIDERNAME as varchar(128)) STATIONNAME,
          QUALITY_CROPFULLNAME.CROPFULLNAME, IT.INVENTORYID,
          QUALITY_SELECT.DIRT, QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.SCREENING,
          QUALITY_SELECT.GLUTENQUALITY, QUALITY_SELECT.GLUTEN
        from IT
          join GETWORKPARTIES(:PARTYID, :PARTYDATE, :CROPID, :CLASSID, :STORAGEID) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
          left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
          left join STATIONS on (STATIONS.STATIONID = INVOICES.STATIONID)
          left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
          left join QUALITY_SELECT(IT.BUHQUALITYID) on (0 = 0)
          left join CLIENTS CLIENTS1 on (CLIENTS1.CLIENTID = IT.OWNERID)
          left join CLIENTS CLIENTS2 on (CLIENTS2.CLIENTID = IT.PROVIDERID)
          Left join OPERATION on (IT.OPERATIONID = OPERATION.OPERATIONID)
          left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, null, 0) on (0 = 0)
          left join OPERATIONS_OPERATIONNAME(IT.OPERATIONID, IT.OPERATIONParam) on (0 = 0)
          left join FORM36ADATATEXT(cast(null as varchar(30)), cast(null as varchar(30)),
              OPERATIONS_OPERATIONNAME.OPERATIONNAME, IT.OPERATIONID, CLIENTS2.CLIENTNAME,
              CLIENTS1.CLIENTNAME, null) on (0 = 0)
        where (IT.REGDATE between :DATE1 and :DATE2)
          and (IT.OWNERID = :OWNERID)
          and ((IT.STATE >= 0) and (IT.PARENTID >= 0))
          and (((QUALITY.CLASSID = :CLASSID) and (:DOCROP = 0)) or ((QUALITY.CROPID = :CROPID) and (:DOCROP = 1)) or (:DOCROP = 2))
          and (IT.STORAGEID = :STORAGEID)
          and ((IT.STORINGID = :STORINGID) or (:STORINGID = 0))
          and (OPERATION.OPERATIONTYPE = 5)
          and ((IT.CONTRACTID = :CONTRACTID) or (:CONTRACTID = 0))
    into :REGDATE, :PROVIDER, :OPERATIONSUBTYPE, :NET, :WEIGHT, :INVOICENUMBER,
         :OPERATIONID, :STATIONNAME, :CROPNAME, :TRASH, :DIRT, :HUM, :SCREEN,
         :IDK, :KLEIK

  do begin
    if ((:OPERATIONSUBTYPE = 1) or ((:OPERATIONID = 17) and (:NET < 0))) then begin
      if (:OPERATIONID = 17) then begin
        OUTCOME = -:NET;
        OPERATIONSUBTYPE = 1;
        OUTCOMEWEIGHT = -:WEIGHT;
      end
      else begin
        OUTCOME = :NET;
        if (:OPERATIONID <> 22) then
          OUTCOMEWEIGHT = :WEIGHT;
        else OUTCOMEWEIGHT = 0;
      end
      INVOICENUMBEROUT = INVOICENUMBER;
      INVOICENUMBERIN = '';
      INCOMENET = 0;
      INCOMEWEIGHT = 0;
    end
    else if (:OPERATIONSUBTYPE = 0) then begin
      INCOMENET = :NET;
      INCOMEWEIGHT = :WEIGHT;
      OUTCOME = 0;
      OUTCOMEWEIGHT = 0;
      INVOICENUMBERIN = :INVOICENUMBER;
      INVOICENUMBEROUT = '';
    end

    if ((:OUTCOME <> 0) or (:INVOICENUMBEROUT <> '') or (:OUTCOMEWEIGHT <> 0)
        or (:INCOMENET <> 0) or (:INVOICENUMBERIN <> '') or (:INCOMEWEIGHT <> 0)) then
      suspend;
  end
end