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

ACC_SUMMARY_REGISTERS

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

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

Описание

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

Определение

CREATE PROCEDURE ACC_SUMMARY_REGISTERS(
    BEGINDATE DATE,
    ENDDATE DATE,
    AOWNERID INTEGER,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    ISCURRENT INTEGER,
    PARTYDATE DATE,
    PLUGUNDERWORK INTEGER,
    NOTCOSTSTORAGETICKETS INTEGER)
RETURNS (
    REGDATE DATE,
    REGISTERNUMBER INTEGER,
    PROVIDERNAME VARCHAR(63),
    OWNERNAME VARCHAR(30),
    STORAGEID INTEGER,
    CROPFULLNAME VARCHAR(30),
    NET DOUBLE PRECISION,
    HUMIDITY NUMERIC(5,2),
    HUMIDITY_C INTEGER,
    DIRT NUMERIC(5,2),
    DIRT_C INTEGER,
    SCREENING NUMERIC(5,2),
    REGISTERSHUMIDITY NUMERIC(5,2),
    REGISTERSHUMIDITY_C INTEGER,
    REGISTERSDIRT NUMERIC(5,2),
    REGISTERSDIRT_C INTEGER,
    REGISTERSSCREENING NUMERIC(5,2),
    SMALLGRAIN NUMERIC(5,2),
    GRAINUNIT INTEGER,
    GLUTEN NUMERIC(5,2),
    PROTEIN NUMERIC(5,2),
    WEIGHT BIGINT,
    INCOMESUM NUMERIC(15,2),
    HUMWEIGHT BIGINT,
    DRYSUM NUMERIC(15,2),
    DIRTWEIGHT BIGINT,
    SCREENWEIGHT BIGINT,
    UNDERWORKSUM NUMERIC(15,2),
    STORAGETICKETSSUM NUMERIC(5,2),
    TOTALSUM NUMERIC(15,2),
    TOTALNDS NUMERIC(15,2),
    TOTALSUMNDS NUMERIC(15,2))
AS
declare variable REGISTERID varchar(30);
declare variable INCOMECOST numeric(6,2);
declare variable DRYCOST numeric(6,2);
declare variable DRYQUANTITY numeric(15,4);
declare variable UNDERWORKCOST numeric(6,2);
declare variable CLEARQUANTITY numeric(15,4);
declare variable DEFAULTVALUE numeric(4,2);
declare variable CROPID integer;
declare variable CLASSID integer;
declare variable CONTRACTID integer;
begin
  for

    select
        min(ITMAIN.REGDATE) REGDATE, ITMAIN.REGISTERID, INVOICEREGISTERS.REGISTERNUMBER,
        PROVIDER.CLIENTNAME || coalesce(ascii_char(13) || '(' || MANUFACTURER.CLIENTNAME || ')','') PROVIDERNAME,
        OWNER.CLIENTNAME, QUALITY_CROPFULLNAME_BYID.CROPFULLNAME,
        sum(ITMAIN.NET), min(QUALITY_SELECT.HUMIDITY), min(QUALITY_SELECT.DIRT),
        min(case
          when (CROP.CROPTYPEID <> 5) then
            QUALITY_SELECT.SCREENING
          else QUALITY_SELECT.OILSEED
        end),
        min(case
          when (Q_SELECT.HUMIDITY < QUALITY_SELECT.HUMIDITY) then
            Q_SELECT.HUMIDITY
          else QUALITY_SELECT.HUMIDITY
        end),
        min(case
          when (Q_SELECT.DIRT < QUALITY_SELECT.DIRT) then
            Q_SELECT.DIRT
          else QUALITY_SELECT.DIRT
        end),
        min(case
          when (CROP.CROPTYPEID <> 5) then
            case
              when (Q_SELECT.SCREENING < QUALITY_SELECT.SCREENING) then
                Q_SELECT.SCREENING
              else QUALITY_SELECT.SCREENING
            end
          else
            case
              when (Q_SELECT.OILSEED < QUALITY_SELECT.OILSEED) then
                Q_SELECT.OILSEED
              else QUALITY_SELECT.OILSEED
            end
        end),
        min(QUALITY_SELECT.SMALLGRAIN), min(QUALITY_SELECT.GRAINUNIT), min(QUALITY_SELECT.GLUTEN),
        min(QUALITY_SELECT.PROTEIN), sum(ITMAIN.WEIGHT) as WEIGHT, min(INVOICEREGISTERS.INCOMECOST),
        min(INVOICEREGISTERS.DRYCOST), sum(ITTRANSPORT.HUMWEIGHT), coalesce(min(INVOICEREGISTERS.DRYQUANTITY), 0),
        min(INVOICEREGISTERS.UNDERWORKCOST), sum(ITTRANSPORT.DIRTWEIGHT), sum(ITTRANSPORT.SCREENWEIGHT),
        coalesce(min(INVOICEREGISTERS.CLEARQUANTITY), 0),
/*
        coalesce(min((select CROPPARAMS.DEFAULTVALUE
                        from CROPPARAMS
                        where (CROPPARAMS.CROPID = QUALITY_SELECT.CROPID)
                          and (CROPPARAMS.CROPPARAMTYPEID = 28)
                          and (CROPPARAMS.QUALITYTYPEID = 2))), 0),
*/
        coalesce(min(GET_CONTRACT_COSTS.HUMB), 0), ITMAIN.STORAGEID,
        min(QUALITY_SELECT.CROPID), min(QUALITY_SELECT.CLASSID),
        min(ITMAIN.CONTRACTID)
      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 INVOICES on (INVOICES.INVOICEID = ITMAIN.INVOICEID)
        left join CLIENTS MANUFACTURER on (MANUFACTURER.CLIENTID = INVOICES.MANUFACTURERID)
        left join QUALITY_SELECT(ITMAIN.BUHQUALITYID) on (0 = 0)
        left join CROP on (CROP.CROPID = QUALITY_SELECT.CROPID)
        left join QUALITY_CROPFULLNAME_BYID(ITMAIN.BUHQUALITYID, 1) 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 GET_CONTRACT_COSTS(ITMAIN.REGDATE, ITMAIN.CONTRACTID, 3, QUALITY_SELECT.CROPID, QUALITY_SELECT.CLASSID) 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))
    group by OWNER.CLIENTNAME, QUALITY_CROPFULLNAME_BYID.CROPFULLNAME,
      PROVIDERNAME, ITMAIN.REGISTERID, INVOICEREGISTERS.REGISTERNUMBER,
      ITMAIN.STORAGEID
    order by OWNER.CLIENTNAME, QUALITY_CROPFULLNAME_BYID.CROPFULLNAME, REGDATE
    into :REGDATE, :REGISTERID, :REGISTERNUMBER, :PROVIDERNAME, :OWNERNAME,
      :CROPFULLNAME, :NET, :HUMIDITY, :DIRT, :SCREENING,
      :REGISTERSHUMIDITY, :REGISTERSDIRT, :REGISTERSSCREENING,
      :SMALLGRAIN, :GRAINUNIT, :GLUTEN, :PROTEIN, :WEIGHT,
      :INCOMECOST, :DRYCOST, :HUMWEIGHT, :DRYQUANTITY, :UNDERWORKCOST,
      :DIRTWEIGHT, :SCREENWEIGHT, :CLEARQUANTITY, :DEFAULTVALUE, :STORAGEID,
      :CROPID, :CLASSID, :CONTRACTID
  do begin
    INCOMESUM = 0;
    DRYSUM = 0;
    UNDERWORKSUM = 0;

    HUMIDITY_C = cast(NET * cast(HUMIDITY as numeric (3, 1)) / 100 as integer);
    DIRT_C = cast(NET * cast(DIRT as numeric (3, 1)) / 100 as integer);
    REGISTERSHUMIDITY_C = cast(WEIGHT * cast(REGISTERSHUMIDITY as numeric (3, 1)) / 100 as integer);
    REGISTERSDIRT_C = cast(WEIGHT * cast(REGISTERSDIRT as numeric (3, 1)) / 100 as integer);

    INCOMESUM = (NET * INCOMECOST) / 1000;

    if (DRYQUANTITY = 0) then begin
      if (HUMIDITY >= DEFAULTVALUE) then
        DRYSUM = ((HUMIDITY - REGISTERSHUMIDITY) * NET * DRYCOST) / 1000;
    end
    else DRYSUM = DRYQUANTITY * DRYCOST;

    if (CLEARQUANTITY = 0) then
      if (PLUGUNDERWORK = 2) then
        UNDERWORKSUM = ((((DIRT - REGISTERSDIRT) + (SCREENING - REGISTERSSCREENING)) * NET) / 1000) * UNDERWORKCOST;
      else
        if (PLUGUNDERWORK = 6) then
          UNDERWORKSUM = (((DIRT - REGISTERSDIRT) * NET) / 1000) * UNDERWORKCOST;
        else UNDERWORKSUM = (((DIRT - REGISTERSDIRT) * (NET - HUMWEIGHT)) / 1000) * UNDERWORKCOST;
    else UNDERWORKSUM = CLEARQUANTITY * UNDERWORKCOST;

    if (:NOTCOSTSTORAGETICKETS = 0) then
      select GET_CONTRACT_COSTS.COST BASECOST
        from GET_CONTRACT_COSTS(:REGDATE, :CONTRACTID, 10, :CROPID, :CLASSID)
      into :STORAGETICKETSSUM;

    TOTALSUM = coalesce(INCOMESUM, 0) + coalesce(DRYSUM, 0) + coalesce(UNDERWORKSUM, 0) + coalesce(STORAGETICKETSSUM, 0);
    TOTALNDS = TOTALSUM * 0.2;
    TOTALSUMNDS = TOTALSUM + TOTALNDS;

    suspend;

  end
end