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

ACC_INCOME_PROD_BORIVAJ

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

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

Описание

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

Определение

CREATE PROCEDURE ACC_INCOME_PROD_BORIVAJ(
    BEGINDATE DATE,
    ENDDATE DATE,
    AOWNERID INTEGER,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    ISCURRENT INTEGER,
    PARTYDATE DATE)
RETURNS (
    REGDATE DATE,
    PROVIDERNAME VARCHAR(30),
    CARNUMBER VARCHAR(31),
    TRAILERNUMBER VARCHAR(31),
    INVOICENUMBER VARCHAR(31),
    CROPFULLNAME VARCHAR(30),
    CLASSNAME VARCHAR(30),
    GROSS INTEGER,
    TARE INTEGER,
    NET INTEGER,
    INVOICEGROSS INTEGER,
    INVOICETARE INTEGER,
    INVOICENET INTEGER,
    HUMIDITY NUMERIC(5,2),
    DIRT NUMERIC(5,2),
    WEIGHT BIGINT,
    REGISTERNUMBER INTEGER,
    OWNERNAME VARCHAR(30),
    REGISTERSHUMIDITY NUMERIC(5,2),
    REGISTERSDIRT NUMERIC(5,2),
    SCREENING NUMERIC(5,2),
    SMALLGRAIN NUMERIC(5,2),
    GRAINUNIT INTEGER,
    GLUTENQUALITY INTEGER,
    GLUTEN NUMERIC(5,2),
    PROTEIN NUMERIC(5,2),
    TURTLEBUG NUMERIC(5,2),
    INFECTION VARCHAR(30),
    INCOMESUM NUMERIC(15,2),
    HUMWEIGHT BIGINT,
    DRYSUM NUMERIC(15,2),
    DIRTWEIGHT BIGINT,
    UNDERWORKSUM NUMERIC(15,2),
    TOTALSUM NUMERIC(15,2),
    TOTALNDS NUMERIC(15,2),
    TOTALSUMNDS NUMERIC(15,2),
    STATIONNAME VARCHAR(30))
AS
declare variable REGISTERID varchar(30);
declare variable INCOMECOST numeric(6,2);
declare variable DRYCOST numeric(6,2);
declare variable UNDERWORKCOST numeric(6,2);
declare variable INVOICEID integer;
declare variable TRANSPORTTYPEID integer;
begin

  for

    select
      ITMAIN.REGDATE,
      PROVIDER.CLIENTNAME,
      ITTRANSPORT.TRANSPORTNUMBER,
      INVOICES.INVOICENUMBER,
      QUALITY_CROPFULLNAME_BYID.CROPFULLNAME,
      CLASS.CLASSNAME,
      ITMAIN.GROSS,
      ITMAIN.TARE,
      ITMAIN.NET,
      ITTRANSPORT.INVOICEGROSS,
      ITTRANSPORT.INVOICETARE,
      ITTRANSPORT.INVOICENET,
      QUALITY_SELECT.HUMIDITY,
      QUALITY_SELECT.DIRT,
      ITMAIN.REGISTERID,
      INVOICEREGISTERS.REGISTERNUMBER,
      OWNER.CLIENTNAME,
      case
        when (Q_SELECT.HUMIDITY < QUALITY_SELECT.HUMIDITY) then
          Q_SELECT.HUMIDITY
        else QUALITY_SELECT.HUMIDITY
      end,
      case
        when (Q_SELECT.DIRT < QUALITY_SELECT.DIRT) then
          Q_SELECT.DIRT
        else QUALITY_SELECT.DIRT
      end,
      case
        when (CROP.CROPTYPEID <> 5) then
          QUALITY_SELECT.SCREENING
        else QUALITY_SELECT.OILSEED
      end,
      QUALITY_SELECT.SMALLGRAIN,
      QUALITY_SELECT.GRAINUNIT,
      QUALITY_SELECT.GLUTENQUALITY,
      QUALITY_SELECT.GLUTEN,
      QUALITY_SELECT.PROTEIN,
      QUALITY_SELECT.TURTLEBUG,
      INFECTION.SELECTVALUE,
      ITMAIN.WEIGHT,
      INVOICEREGISTERS.INCOMECOST,
      INVOICEREGISTERS.DRYCOST,
      ITTRANSPORT.HUMWEIGHT,
      INVOICEREGISTERS.UNDERWORKCOST,
      ITTRANSPORT.DIRTWEIGHT,
      ITTRANSPORT.TRANSPORTTYPEID,
      ITMAIN.INVOICEID,
      STATIONS.STATIONNAME
    from IT ITMAIN
      join GETWORKPARTIES(:ISCURRENT, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = ITMAIN.PARTYID)
      left join CLIENTS OWNER on (OWNER.CLIENTID = ITMAIN.OWNERID)
      left join CLIENTS PROVIDER on (PROVIDER.CLIENTID = ITMAIN.PROVIDERID)
      left join QUALITY_SELECT(ITMAIN.BUHQUALITYID) on (0 = 0)
      left join CROP on (CROP.CROPID = QUALITY_SELECT.CROPID)
      left join CLASS on (CLASS.CLASSID = QUALITY_SELECT.CLASSID)
      left join QUALITY_CROPFULLNAME_BYID(ITMAIN.BUHQUALITYID, 0) on (0 = 0)
      left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = ITMAIN.REGISTERID)
      left join QUALITY_SELECT(INVOICEREGISTERS.QUALITYID) Q_SELECT on (0 = 0)
      left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = ITMAIN.INVENTORYID)
      left join INVOICES on (INVOICES.INVOICEID = ITMAIN.INVOICEID)
      left join STATIONS on (STATIONS.STATIONID = INVOICES.STATIONID)
      left join QUALITY_SELECT_VALUES(19, QUALITY_SELECT.INFECTIONID, 0) INFECTION on (0 = 0)
    where (ITMAIN.REGDATE between :BEGINDATE and :ENDDATE) and
      (ITMAIN.OPERATIONID = 1) and
      (ITMAIN.OWNERID = :AOWNERID or (coalesce(:AOWNERID, -1) = -1)) and
      (QUALITY_SELECT.CROPID = :ACROPID or (coalesce(:ACROPID, -1) = -1)) and
      (QUALITY_SELECT.CLASSID = :ACLASSID or (coalesce(:ACLASSID, -1) = -1)) and
      (ITMAIN.STATE >= 0) and
      (ITMAIN.PARENTID <> -1) and
      (ITMAIN.NET is not null) and
      (ITMAIN.NET <> 0)
    order by
      OWNER.CLIENTNAME,
      QUALITY_CROPFULLNAME_BYID.CROPFULLNAME,
      ITMAIN.REGDATE
    into
      :REGDATE,
      :PROVIDERNAME,
      :CARNUMBER,
      :INVOICENUMBER,
      :CROPFULLNAME,
      :CLASSNAME,
      :GROSS,
      :TARE,
      :NET,
      :INVOICEGROSS,
      :INVOICETARE,
      :INVOICENET,
      :HUMIDITY,
      :DIRT,
      :REGISTERID,
      :REGISTERNUMBER,
      :OWNERNAME,
      :REGISTERSHUMIDITY,
      :REGISTERSDIRT,
      :SCREENING,
      :SMALLGRAIN,
      :GRAINUNIT,
      :GLUTENQUALITY,
      :GLUTEN,
      :PROTEIN,
      :TURTLEBUG,
      :INFECTION,
      :WEIGHT,
      :INCOMECOST,
      :DRYCOST,
      :HUMWEIGHT,
      :UNDERWORKCOST,
      :DIRTWEIGHT,
      :TRANSPORTTYPEID,
      :INVOICEID,
      :STATIONNAME

  do begin
    TRAILERNUMBER = '';
    if (TRANSPORTTYPEID = 0) then begin
      select
        ITTRANSPORT.TRANSPORTNUMBER
      from IT
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
      where
        (IT.INVOICEID = :INVOICEID) and
        (((IT.STATE = 0) or (IT.STATE = -5))) and
        (ITTRANSPORT.TRANSPORTTYPEID = 1)
      into
        :TRAILERNUMBER;
    end
    else
      if (TRANSPORTTYPEID = 1) then begin
        TRAILERNUMBER = :CARNUMBER;
      end
    INCOMESUM = (NET * INCOMECOST) / 1000;
    if ((HUMIDITY - REGISTERSHUMIDITY) > 0) then
      DRYSUM = (((HUMIDITY - REGISTERSHUMIDITY) * NET) / 1000) * DRYCOST * 1.2;
    else DRYSUM = 0;
    if ((DIRT - REGISTERSDIRT) > 0) then
      UNDERWORKSUM = (((DIRT - REGISTERSDIRT) * (NET - HUMWEIGHT)) / 1000) * UNDERWORKCOST * 1.2;
    else UNDERWORKSUM = 0;
    TOTALSUM = coalesce(INCOMESUM, 0) + coalesce(DRYSUM, 0) + coalesce(UNDERWORKSUM, 0);
    TOTALNDS = TOTALSUM * 0.2;
    TOTALSUMNDS = TOTALSUM + TOTALNDS;

    suspend;

  end
end