Форум программистов [Powered by Invision Power Board]

Код

CREATE OR ALTER PROCEDURE IAMASSHOLE2 
RETURNS (
    CODE VARCHAR(30),
    CODE1 SMALLINT,
    NAME VARCHAR(100),
    REMAINDER_STORAGE VARCHAR(5),
    PRICE_ONE NUMERIC(15,2),
    PRICE_TWO NUMERIC(15,2),
    PRICE_THREE NUMERIC(15,2),
    PRICE_FOUR NUMERIC(15,2),
    PRICE_OLD NUMERIC(15,2),
    DESCRIPTION VARCHAR(1000),
    GOODS_SIZE NUMERIC(15,2),
    MODEL VARCHAR(3),
    FULL_DESCRIPTION VARCHAR(1000),
    BREND VARCHAR(100),
    NOVELTY VARCHAR(7),
    METAGE VARCHAR(10))
AS
DECLARE VARIABLE CURRENTGOODSTYPE VARCHAR(100) = '';
DECLARE VARIABLE CURRENTGOODSID BIGINT;
DECLARE VARIABLE CURRENTINSERTION VARCHAR(100) = '';
DECLARE VARIABLE CURRENTINSERTION2 VARCHAR(100) = '';
DECLARE VARIABLE CURRENTCOVERAGE VARCHAR(100) = '';
DECLARE VARIABLE GOODSTYPE VARCHAR(100) = '';
DECLARE VARIABLE INSERTIONTYPE VARCHAR(100) = '';
DECLARE VARIABLE INSERTIONTYPE2 VARCHAR(100) = '';
DECLARE VARIABLE COVERAGETYPE VARCHAR(100) = '';
DECLARE VARIABLE ID VARCHAR(100) = '';
begin
  for select tg.NAME, ti.NAME, ti2.NAME, tc.NAME, /*i.COLLECTION,*/ i.ID, tg.ID
    from T_GOOD tg, t_INSERTION ti, t_INSERTION2 ti2, t_coverage tc, IN1 i
    where (SUBSTRING (ARTICLE from 5 FOR 1) = tc.code)
      and (SUBSTRING (ARTICLE from 6 FOR 1) = tg.code)
      and (SUBSTRING (ARTICLE from 7 FOR 2) = ti.code)
      and (SUBSTRING (ARTICLE from 7 FOR 2) = ti2.code)

    order by tg.NAME, ti2.NAME, ti.NAME, tc.NAME, i.NAME/*, i.COLLECTION, i.NAME*/
    into :GoodsType, :InsertionType, :InsertionType2, :CoverageType, /*:BrandType,*/ :id, :CURRENTGOODSID
  do
  begin
    -- set all fields into null
    CODE              = null;
    CODE1             = null;
    NAME              = null;
    REMAINDER_STORAGE = null;
    PRICE_ONE         = null;
    PRICE_TWO         = null;
    PRICE_THREE       = null;
    PRICE_FOUR        = null;
    PRICE_OLD         = null;
    DESCRIPTION       = null;
    GOODS_SIZE        = null;
    MODEL             = null;
    FULL_DESCRIPTION  = null;
    NOVELTY           = null;
    METAGE            = null;

    if (CurrentGoodsType <> GoodsType) then
    begin
      CurrentGoodsType = GoodsType;
      CurrentInsertion = '';
      CurrentInsertion2 = '';
      CurrentCoverage = '';

      NAME = GoodsType;
      CODE1 = 1;
      code  = 'g' || CAST(CURRENTGOODSID as VARCHAR(15));
--      code  = 'g' || id;
      suspend;
    end
    if (CurrentInsertion2 <> InsertionType2) then
    begin
      CurrentInsertion2 = InsertionType2;
      CurrentInsertion = '';
      CurrentCoverage = '';

      NAME = InsertionType2;
      CODE1 = 2;
      code  = 'i' || id;
      suspend;
    end

    if (CurrentInsertion <> InsertionType) then
    begin
      CurrentInsertion = InsertionType;
      CurrentCoverage = '';


      NAME = InsertionType;
      CODE1 = 3;
      code  = 'i2' || id;
      suspend;
    end
    if (CurrentCoverage <> CoverageType) then
    begin
      CurrentCoverage = CoverageType;
      NAME = CoverageType;

      CODE1 = 4;
      code  = 'c' || id;
      suspend;
    end

    CODE1 = null;
    -- select all fields for full suspend

    select
      tg.NAME,
      ti.NAME,
      ti2.NAME,
      tc.NAME,
      i.ARTICLE,
      I.NAME || (SELECT RES from SP_GET_SIZE5(i.ARTICLE,I.NAME)),
      (select res from SP_PRESENSE_ON_STORAGE(i.ARTICLE)),
      (SELECT price1 from SP_GET_BASEPRICE(i.ARTICLE, i.PRICE_BASE)),
      (SELECT price2 from SP_GET_BASEPRICE(i.ARTICLE, i.PRICE_BASE)),
      (SELECT price3 from SP_GET_BASEPRICE(i.ARTICLE, i.PRICE_BASE)),
      (SELECT price4 from SP_GET_BASEPRICE(i.ARTICLE, i.PRICE_BASE)),
      (SELECT price_old from SP_GET_BASEPRICE(i.ARTICLE, i.PRICE_BASE)),
      (SUBSTRING('Покрытие: ' || tc.NAME || '. Вставка: ' || ti.NAME || '.' || (SELECT RES from SP_GET_SIZE4(i.ARTICLE,I.NAME)) || (SELECT RES from sp_get_novinka(i.ARTICLE)) || ' Артикул по каталогу: ' || i.ARTICLE from 1 FOR 1000))  || '. Коллекция: '|| (SELECT result from SP_GET_BREND_NAME(i.ARTICLE)) || '. Ювелирная бижутерия Эталон-Женави.',
      (select res FROM SP_GET_SIZE3(i.ARTICLE, i.name)),
      (SUBSTRING(i.ARTICLE from 2 for 3)),
      (SUBSTRING('Покрытие: ' || tc.NAME || '. Вставка: ' || ti.NAME || '.' || (SELECT RES from SP_GET_SIZE4(i.ARTICLE,I.NAME)) || (SELECT RES from sp_get_novinka(i.ARTICLE)) || ' Артикул по каталогу: ' || i.ARTICLE from 1 FOR 1000))  || '. Коллекция: '|| (SELECT result from SP_GET_BREND_NAME(i.ARTICLE)) || '. Ювелирная бижутерия Эталон-Женави.',
      i.COLLECTION,
      (case UPPER(i.novelty) when 'NEW' then 'Новинка' else null end),
      (select res FROM sp_get_metage(i.ARTICLE))

      -- insert all fields for output set
      from IN1 i, T_GOOD tg, t_INSERTION ti, t_INSERTION2 ti2, t_coverage tc
      where (i.ID = :Id)
        and (SUBSTRING (ARTICLE from 5 FOR 1) = tc.code)
        and (SUBSTRING (ARTICLE from 6 FOR 1) = tg.code)
        and (SUBSTRING (ARTICLE from 7 FOR 2) = ti.code)
        and (SUBSTRING (ARTICLE from 7 FOR 2) = ti2.code)    --  Select all data for current goods
      order by I.NAME
      into
        :GOODSTYPE,
        :InsertionType,
        :InsertionType2,
        :CoverageType,
        :CODE,
        :NAME,
        :REMAINDER_STORAGE,
        :pRICE_ONE,
        :pRICE_TWO,
        :pRICE_THREE,
        :pRICE_FOUR,
        :pRICE_OLD,
        :DESCRIPTION,
        :GOODS_SIZE,
        :MODEL,
        :FULL_DESCRIPTION,
        :BREND,
        :novelty,
        :metage
        ;

    suspend;

  end
end