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

REGISTER_CALCSERVICES

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

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

Описание

Расчет услуг по реестру ТТН

Определение

CREATE PROCEDURE REGISTER_CALCSERVICES(
    INVOICEREGISTERID INTEGER)
RETURNS (
    NAME VARCHAR(100),
    QUANTITY NUMERIC(12,3),
    COST NUMERIC(12,4),
    AMOUNT NUMERIC(15,2),
    NDS NUMERIC(15,2),
    AMOUNT_NDS NUMERIC(15,2))
AS
declare variable DISPLAYREGISTER integer;
declare variable INCOMECOST numeric(12,4);
declare variable DRYCOST numeric(12,4);
declare variable UNDERWORKCOST numeric(12,4);
declare variable DRYQUANTITY numeric(12,3);
declare variable CLEARQUANTITY numeric(12,3);
declare variable STORAGETICKETID integer;
declare variable REGDATE date;
declare variable INCOMENET numeric(15,3);
declare variable CONTRACTID integer;
declare variable CROPID integer;
declare variable CLASSID integer;
begin
  select INVOICEREGISTERS.INCOMECOST, INVOICEREGISTERS.DRYCOST, INVOICEREGISTERS.UNDERWORKCOST,
      INVOICEREGISTERS.DRYQUANTITY, INVOICEREGISTERS.CLEARQUANTITY, INVOICEREGISTERS.STORAGETICKETID,
      max(IT.REGDATE), sum(IT.NET), max(IT.CONTRACTID), max(QUALITY.CROPID), max(QUALITY.CLASSID)
    from INVOICEREGISTERS
      left join IT on ((IT.REGISTERID = INVOICEREGISTERID) and (IT.STATE = 0))
      left join STORAGETICKETS on (STORAGETICKETS.STORAGETICKETID = INVOICEREGISTERS.STORAGETICKETID)
      left join QUALITY on (QUALITY.QUALITYID = STORAGETICKETS.QUALITYID)
      left join CONTRACTS on (CONTRACTS.CONTRACTID = IT.CONTRACTID)
    where (INVOICEREGISTERS.INVOICEREGISTERID = :INVOICEREGISTERID)
  group by INVOICEREGISTERS.INCOMECOST, INVOICEREGISTERS.DRYCOST, INVOICEREGISTERS.UNDERWORKCOST,
      INVOICEREGISTERS.DRYQUANTITY, INVOICEREGISTERS.CLEARQUANTITY, INVOICEREGISTERS.STORAGETICKETID
  into :INCOMECOST, :DRYCOST, :UNDERWORKCOST, :DRYQUANTITY, :CLEARQUANTITY,
       :STORAGETICKETID, :REGDATE, :INCOMENET, :CONTRACTID, :CROPID, :CLASSID;


  if ((coalesce(INCOMENET,0) > 0) and (INCOMECOST > 0)) then begin
    select SERVICES.DISPLAYREGISTER, SERVICES.SERVICEFULLNAME
      from SERVICES
      where (SERVICES.SERVICEID = 5)
    into :DISPLAYREGISTER, :NAME;

    if (coalesce(DISPLAYREGISTER,0) = 1) then begin
      QUANTITY = INCOMENET / 1000;
      COST = INCOMECOST;
      AMOUNT = QUANTITY * COST;
      NDS = AMOUNT * 0.2;
      AMOUNT_NDS = AMOUNT * 1.2;
      suspend;
    end
  end

  if ((coalesce(DRYQUANTITY,0) > 0) and (DRYCOST > 0)) then begin
    select SERVICES.DISPLAYREGISTER, SERVICES.SERVICEFULLNAME
      from SERVICES
      where (SERVICES.SERVICEID = 3)
    into :DISPLAYREGISTER, :NAME;

    if (coalesce(DISPLAYREGISTER,0) = 1) then begin
      QUANTITY = DRYQUANTITY;
      COST = DRYCOST;
      AMOUNT = QUANTITY * COST;
      NDS = AMOUNT * 0.2;
      AMOUNT_NDS = AMOUNT * 1.2;
      suspend;
    end
  end

  if ((coalesce(CLEARQUANTITY,0) > 0) and (UNDERWORKCOST > 0)) then begin
    select SERVICES.DISPLAYREGISTER, SERVICES.SERVICEFULLNAME
    from SERVICES
      where (SERVICES.SERVICEID = 4)
    into :DISPLAYREGISTER, :NAME;

    if (coalesce(DISPLAYREGISTER,0) = 1) then begin
    QUANTITY = CLEARQUANTITY;
    COST = UNDERWORKCOST;
    AMOUNT = QUANTITY * COST;
    NDS = AMOUNT * 0.2;
    AMOUNT_NDS = AMOUNT * 1.2;
    suspend;
  end

  select SERVICES.DISPLAYREGISTER, SERVICES.SERVICEFULLNAME
  from SERVICES
    where (SERVICES.SERVICEID = 35)
  into :DISPLAYREGISTER, :NAME;

  if (coalesce(DISPLAYREGISTER,0) = 1) then begin
      select coalesce(sum(cast(IT.NET as numeric(12,3))) / 1000,0), max(GET_CONTRACT_COSTS.COST)
        from IT
          left join QUALITY_SELECT(IT.BUHQUALITYID) on (0=0)
          left join GET_CONTRACT_COSTS(IT.REGDATE, IT.CONTRACTID, 35, QUALITY_SELECT.CROPID, QUALITY_SELECT.CLASSID) on (0=0)
        where (IT.REGISTERID = :INVOICEREGISTERID) and (coalesce(QUALITY_SELECT.AMBROZIA,0) > 0)
      into :QUANTITY, :COST;

      if (QUANTITY > 0) then begin
        AMOUNT = QUANTITY * COST;
        NDS = AMOUNT * 0.2;
        AMOUNT_NDS = AMOUNT * 1.2;
        suspend;
      end
    end
  end

  if (STORAGETICKETID is not null) then begin
    select SERVICES.DISPLAYREGISTER, SERVICES.SERVICEFULLNAME
      from SERVICES
      where (SERVICES.SERVICEID = 10)
    into :DISPLAYREGISTER, :NAME;

    if (coalesce(DISPLAYREGISTER,0) = 1) then begin
      QUANTITY = 1;

      select GET_CONTRACT_COSTS.COST
        from GET_CONTRACT_COSTS(:REGDATE, :CONTRACTID, 10, :CROPID, :CLASSID)
      into :AMOUNT;

      COST = AMOUNT;
      NDS = AMOUNT * 0.2;
      AMOUNT_NDS = AMOUNT * 1.2;
      suspend;
    end
  end
end