--
--  出品在庫明細
--      t_ex_stock_detail
--

-- DROP TABLE t_ex_stock_detail;

CREATE TABLE t_ex_stock_detail
(
    itemcd          integer NOT NULL,       -- PriKey
    structcd        integer NOT NULL,       -- PriKey
    stockcd         integer NOT NULL,       -- PriKey
    sellercd        integer,                -- ForKey
    stocktitlecd    integer,                -- ForKey
    exhibitcount    integer,
    condition       integer,
    sellingprice    integer,
    souryouprc      integer,
    stockstate      integer,
    scdstartdate    timestamp without time zone,
    scdcompdate     timestamp without time zone,
    itemexp         character varying(1024),
    rackid          character varying(256),
    managecd        character varying(256),
    costprice       integer,
    ntcostprice     integer,
    consignment     integer,
    arrivaldate     date,

    delflg      boolean,
    adddate     timestamp without time zone,
    upddate     timestamp without time zone,
    updusrnm    character varying(60),
    updpcnm     character varying(60),
    updcnt      integer,

    PRIMARY KEY(itemcd, structcd, stockcd)
);


-- トリガー
CREATE TRIGGER trigger_master_stamp
  BEFORE INSERT OR UPDATE
  ON t_ex_stock_detail
  FOR EACH ROW
  EXECUTE PROCEDURE g_master_stamp();


-- トリガー用関数: ex_stock_detail
CREATE OR REPLACE FUNCTION g_ex_stock_detail()
  RETURNS "trigger" AS
$BODY$
DECLARE
    itemstruct_rec RECORD;
    new_stockcd integer;
BEGIN
    -- structCDのチェック
    IF NEW.itemcd IS NULL THEN
        RAISE EXCEPTION '引数 itemcd は必須です';
    END IF;
    IF NEW.structcd IS NULL THEN
        RAISE EXCEPTION '引数 structcd は必須です';
    END IF;
    SELECT INTO itemstruct_rec * FROM t_item_struct WHERE itemcd = NEW.itemcd AND structcd = NEW.structcd ;
    IF NOT FOUND THEN
        RAISE EXCEPTION '引数 itemcd % と引数 structcd % のデータが見つかりません', NEW.itemcd, NEW.structcd;
    END IF;

    -- stockcdCDの取得
    SELECT INTO new_stockcd MAX(stockcd) +1 FROM t_ex_stock_detail WHERE itemcd = NEW.itemcd AND structcd = NEW.structcd ;

    IF new_stockcd IS NULL THEN
        new_stockcd := 1;
    END IF;

    -- 更新
    NEW.stockcd := new_stockcd;
    RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

-- トリガ
CREATE TRIGGER trigger_ex_stock_detail
  BEFORE INSERT
  ON t_ex_stock_detail
  FOR EACH ROW
  EXECUTE PROCEDURE g_ex_stock_detail();


-- トリガー用関数: ex_stock_detail
-- 品目基本情報を更新
CREATE OR REPLACE FUNCTION g_ex_stock_detail2()
  RETURNS "trigger" AS
$BODY$
BEGIN
    -- 更新
    IF NEW.delflg = FALSE THEN
        UPDATE t_item_basic_attr SET stock_upddate = NEW.upddate
         WHERE itemcd = NEW.itemcd;
    END IF;

    RETURN NULL;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


-- トリガ登録
CREATE TRIGGER trigger_ex_stock_detail2
  AFTER INSERT OR UPDATE
  ON t_ex_stock_detail
  FOR EACH ROW
  EXECUTE PROCEDURE g_ex_stock_detail2();



-- トリガー用関数: ex_stock_detail
-- 品目基本情報の最低価格を更新
-- DROP g_ex_stock_detail3();
CREATE OR REPLACE FUNCTION g_ex_stock_detail3()
    RETURNS "trigger" AS $BODY$
DECLARE
    _price  integer;
BEGIN
    -- 更新
    IF NEW.itemcd IS NOT NULL THEN

        -- 全て
        SELECT * INTO _price FROM s_get_lowprice(1, NEW.itemcd);
        IF NOT FOUND THEN
            _price := NULL;
        END IF;
        UPDATE t_item_basic_attr SET lowprice = _price
         WHERE itemcd = NEW.itemcd;

        -- 中古
        SELECT * INTO _price FROM s_get_lowprice(2, NEW.itemcd);
        IF NOT FOUND THEN
            _price := NULL;
        END IF;
        UPDATE t_item_basic_attr SET oldlowprice = _price
         WHERE itemcd = NEW.itemcd;

        -- 新品
        SELECT * INTO _price FROM s_get_lowprice(3, NEW.itemcd);
        IF NOT FOUND THEN
            _price := NULL;
        END IF;
        UPDATE t_item_basic_attr SET newlowprice = _price
         WHERE itemcd = NEW.itemcd;

    END IF;

    RETURN NULL;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;


-- トリガ登録
CREATE TRIGGER trigger_ex_stock_detail3
    AFTER INSERT OR UPDATE
    ON t_ex_stock_detail
    FOR EACH ROW
    EXECUTE PROCEDURE g_ex_stock_detail3();
