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

STORAGETICKET_FORM

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

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

(Нет выходных параметров)

Описание

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

Определение

CREATE PROCEDURE STORAGETICKET_FORM(
    REGISTERID INTEGER,
    TOIT INTEGER,
    REGISTERORREBIL SMALLINT,
    DEFAULTSERIE VARCHAR(5),
    DEFAULTNUMBER VARCHAR(6),
    STORAGEENDDATECHECKED SMALLINT)
AS
declare variable REGDATE date;
declare variable OWNERID integer;
declare variable BUHQUALITYID integer;
declare variable NET integer;
declare variable WEIGHT integer;
declare variable NEWQUALITYID integer;
declare variable NUMBER integer;
declare variable BLANKNUMBER varchar(6);
declare variable STORAGETICKETID integer;
declare variable CONTRACTID integer;
declare variable TEMP integer;
declare variable INVOICEID integer;
declare variable TRANSPORTSUBTYPE integer;
declare variable ONENET integer;
declare variable TEMPTYPE integer;
declare variable TVALUE numeric(7,2);
declare variable STORAGEID integer;
declare variable REGISTERNUMBER integer;
declare variable ZHS integer;
declare variable CONTRACTENDDATE date;
declare variable FINALDATE date;
declare variable CROPID integer;
declare variable CLASSID integer;
declare variable SORTID integer;
declare variable ANALYSISDATE date;
declare variable PRECISENESS smallint;
begin
  select ENVIRONMENTOPTIONS.PRECISENESS
    from ENVIRONMENTOPTIONS
  into :PRECISENESS;

  for
    select
        max(IT.REGDATE), IT.OWNERID, max(IT.BUHQUALITYID), sum(IT.NET),
        sum(IT.WEIGHT), IT.CONTRACTID, IT.STORAGEID, INVOICEREGISTERS.STORAGETICKETID,
        TRANSPORTTYPE.TRANSPORTSUBTYPE, INVOICEREGISTERS.REGISTERNUMBER,
        INVOICEREGISTERS.ZHS, CONTRACTS.CONTRACTENDDATE
      from IT
        left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
        left join CONTRACTS on (CONTRACTS.CONTRACTID = IT.CONTRACTID)
      where ((IT.REGISTERID = :REGISTERID) or (:REGISTERORREBIL = 1))
        and ((IT.INVENTORYID = :TOIT) or (:REGISTERORREBIL = 0))
        and (IT.STATE >= 0) and (IT.PARENTID >= 0)
    group by IT.OWNERID, IT.CONTRACTID, IT.STORAGEID, INVOICEREGISTERS.STORAGETICKETID,
      TRANSPORTTYPE.TRANSPORTSUBTYPE, INVOICEREGISTERS.REGISTERNUMBER, INVOICEREGISTERS.ZHS,
      CONTRACTS.CONTRACTENDDATE
    into :REGDATE, :OWNERID, :BUHQUALITYID, :NET, :WEIGHT, :CONTRACTID,
         :STORAGEID, :STORAGETICKETID, :TRANSPORTSUBTYPE, :REGISTERNUMBER,
         :ZHS, :CONTRACTENDDATE

  do begin
    if (:STORAGETICKETID is not null) then begin
     -- if (:TRANSPORTSUBTYPE = 1) then    /* исправить */
     --   NEWQUALITYID = null;
     -- else
     -- begin
      select QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, QUALITY.ANALYSISDATE
        from QUALITY
        where (QUALITY.QUALITYID = :BUHQUALITYID)
      into :CROPID, :CLASSID, :SORTID, :ANALYSISDATE;

      select STORAGETICKETS.QUALITYID, STORAGETICKETS.FINALDATE
        from STORAGETICKETS
        where (STORAGETICKETS.STORAGETICKETID = :STORAGETICKETID)
      into :NEWQUALITYID, :FINALDATE;

      update QUALITY
      set CROPID = :CROPID,
          CLASSID = :CLASSID,
          SORTID = :SORTID,
          ANALYSISDATE = :ANALYSISDATE
      where (QUALITYID = :NEWQUALITYID);

      if ((:TRANSPORTSUBTYPE = 1) or (:ZHS = 4)) then begin/* а тут надо вычислять средневзвешенное*/
          -- здесь выбираю параметры качества, кот. есть
         --
        for
          select
              QUALITYDATA.QUALITYTYPEID,
              case
                when (QUALITYDATA.QUALITYTYPEID = 1) then
                  :REGISTERNUMBER
                when (QUALITYDATA.QUALITYTYPEID in (2,13)) then
                  case
                    when (:PRECISENESS = 1) then
                      cast(cast(sum(cast(cast(QUALITYDATA.QUALITYVALUE as numeric(14,1)) * IT.NET / 100 as integer)) * 100 as numeric(18,4)) / sum(IT.NET) as numeric(4,2))
                    else cast(cast(sum(cast(cast(QUALITYDATA.QUALITYVALUE as numeric(14,2)) * IT.NET / 100 as integer)) * 100 as numeric(18,4)) / sum(IT.NET) as numeric(4,2))
                  end
                when (QUALITYDATA.QUALITYTYPEID in (4,7)) then
                  cast(sum(QUALITYDATA.QUALITYVALUE * IT.NET) / sum(nullif(IT.NET, 0) + QUALITYDATA.QUALITYVALUE - QUALITYDATA.QUALITYVALUE) as integer)
                else cast(sum(QUALITYDATA.QUALITYVALUE * IT.NET) / sum(nullif(IT.NET, 0) + QUALITYDATA.QUALITYVALUE - QUALITYDATA.QUALITYVALUE) as numeric(7,2))
              end
            from IT
              left join QUALITYDATA on (QUALITYDATA.QUALITYID = IT.BUHQUALITYID)
            where (IT.REGISTERID = :REGISTERID) and (IT.STATE >= 0) and (IT.PARENTID >= 0)
          group by QUALITYDATA.QUALITYTYPEID
          into :TEMPTYPE, :TVALUE

        do begin
          update QUALITYDATA
            set QUALITYVALUE = :TVALUE
            where ((QUALITYID = :NEWQUALITYID) and (QUALITYTYPEID = :TEMPTYPE));
        end
       -- NEWQUALITYID = null;
      end
      else
        select Q.RESULTQUALITYID
          from QUALITY_COPY_ONLYDATA(:BUHQUALITYID, :NEWQUALITYID) Q
        into :TEMP;
      --end

      update STORAGETICKETS
        set OWNERID = :OWNERID,
            NET = :NET,
            WEIGHT = :WEIGHT,
            CONTRACTID = :CONTRACTID,
            STORAGEID = :STORAGEID,
            FINALDATE = :FINALDATE
        where (STORAGETICKETS.STORAGETICKETID = :STORAGETICKETID);

    end
    else begin
      if ((:TRANSPORTSUBTYPE = 1) or (:ZHS = 4)) then begin/* а тут надо вычислять средневзвешенное*/
          -- здесь выбираю параметры качества, кот. есть
        NEWQUALITYID = gen_id(QUALITYIDGEN, 1);

        insert into QUALITY(QUALITYID, CROPID, CLASSID, SORTID, ANALYSISDATE, SAMPLINGDATE)
          select :NEWQUALITYID, CROPID, CLASSID, SORTID, ANALYSISDATE, SAMPLINGDATE
            from QUALITY
            where (QUALITY.QUALITYID = :BUHQUALITYID);
         --
        for

          select
            QUALITYDATA.QUALITYTYPEID,
            case
              when (QUALITYDATA.QUALITYTYPEID = 1) then
                :REGISTERNUMBER
              when (QUALITYDATA.QUALITYTYPEID in (2,13)) then
                cast(cast(sum(cast(cast(QUALITYDATA.QUALITYVALUE as numeric(14,1)) * IT.NET / 100 as integer)) * 100 as numeric(18,4)) / sum(IT.NET) as numeric(4,2))
              when (QUALITYDATA.QUALITYTYPEID in (4,7)) then
                cast(sum(QUALITYDATA.QUALITYVALUE * IT.NET) / sum(nullif(IT.NET, 0) + QUALITYDATA.QUALITYVALUE - QUALITYDATA.QUALITYVALUE) as integer)
              else cast(sum(QUALITYDATA.QUALITYVALUE * IT.NET) / sum(nullif(IT.NET, 0) + QUALITYDATA.QUALITYVALUE - QUALITYDATA.QUALITYVALUE) as numeric(7,2))
            end
          from IT
            left join QUALITYDATA on (IT.BUHQUALITYID = QUALITYDATA.QUALITYID)
          where (IT.REGISTERID = :REGISTERID)
            and (IT.STATE >=0) and (IT.PARENTID >= 0)
          group by QUALITYDATA.QUALITYTYPEID
          into :TEMPTYPE, :TVALUE

        do begin
           insert into QUALITYDATA(QUALITYID, QUALITYTYPEID, QUALITYVALUE)
           values (:NEWQUALITYID, :TEMPTYPE, :TVALUE);
        end
       -- NEWQUALITYID = null;
      end
      else
        select COPYQUALITYID
          from QUALITY_COPY(:BUHQUALITYID)
        into :NEWQUALITYID;

      select first 1 STORAGETICKETS.NUMBER, STORAGETICKETS.STORAGETICKETID
        from STORAGETICKETS
      order by STORAGETICKETS.STORAGETICKETID desc
      into :NUMBER, :TEMP;

      if (:NUMBER is null) then
        NUMBER = 0;
      NUMBER = :NUMBER + 1;
      --  blankNUMBER = :BLANKNUMBER + 1;
      STORAGETICKETID = gen_id(STORAGETICKETSIDGEN, 1);

      if (:DEFAULTSERIE = '') then
        DEFAULTSERIE = null;
      if (:DEFAULTNUMBER = '') then
        DEFAULTNUMBER = null;
      if (coalesce(:STORAGEENDDATECHECKED,0) = 1) then
        FINALDATE = :CONTRACTENDDATE;
      else FINALDATE = null;

      insert into STORAGETICKETS(STORAGETICKETID, REGDATE, OWNERID, QUALITYID, NET, WEIGHT, NUMBER, BLANKSERIES, CONTRACTID, BLANKNUMBER, STORAGEID, FINALDATE)
        values (:STORAGETICKETID, :REGDATE, :OWNERID, :NEWQUALITYID, :NET, :WEIGHT, :NUMBER, :DEFAULTSERIE, :CONTRACTID, :DEFAULTNUMBER, :STORAGEID, :FINALDATE);

      if (:REGISTERORREBIL = 0) then
        update INVOICEREGISTERS
          set INVOICEREGISTERS.STORAGETICKETID = :STORAGETICKETID
          where (INVOICEREGISTERS.INVOICEREGISTERID = :REGISTERID);
      else begin
         TEMP = GEN_ID(INVOICEREGISTERS_IDGEN, 1);

         insert into INVOICEREGISTERS(INVOICEREGISTERS.INVOICEREGISTERID, INVOICEREGISTERS.STORAGETICKETID)
           values (:TEMP, :STORAGETICKETID);
      
     /*   select distinct invoices.invoiceid from invoices
        left join IT on (invoices.invoiceid = IT.invoiceid)
        where IT.INVENTORYID = :TOIT
        into
        :INVOICEID; */

         update IT
           set IT.REGISTERID = :TEMP
           where (IT.INVENTORYID = :TOIT);
      end
    end
  end
end