--
--  出品者属性
--      t_seller_attr
--

CREATE TABLE t_seller_attr
(
    membercd            integer                 NOT NULL,   -- PriKey
    depbanknm           character varying(256),
    depbranchnm         character varying(256),
    depaccount          integer,
    depaccountno        character varying(256),
    depaccountnm        character varying(256),
    depinfo             integer,
    deppostalcd         character varying(10),
    depprefcd           integer,                  -- ForKey
    depaddr1            character varying(256),
    depaddr2            character varying(256),
    deptelno1           character varying(20),
    depaddressee        character varying(256),
    payinfo             integer,
    paybanknm           character varying(256),
    paybranchnm         character varying(256),
    payaccount          integer,
    payaccountno        character varying(256),
    payaccountnm        character varying(256),
    identtelno          character varying(20),
    creditregist        integer,
    credittype          integer,
    creditaccountnm     character varying(256),
    creditaccountno     character varying(256),
    creditexpireyear    integer,
    creditexpiremonth   integer,
    securitycd          character varying(256),
    sellerdiv           integer,
    basicfee            integer,
    exsystemfee         integer,
    stsystemfee         integer,
    sellerstate         integer,
    itemexhibitstop     boolean,
    news                character varying(256),
    profile             character varying(1024),
    takkyubinprc        integer,
    registdate          date,
    infomation          text,
    sellersouryou       integer,
    sellersouryoumax    integer,

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

    PRIMARY KEY(MemberCd)
);


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

-- トリガー用関数: t_seller_attr
-- 品目基本情報の最低価格を更新
-- DROP FUNCTION g_seller_attr();
CREATE OR REPLACE FUNCTION g_seller_attr()
    RETURNS "trigger" AS $BODY$
DECLARE
    _item RECORD;
    _price  integer;
BEGIN
    -- 更新
    IF NEW.membercd IS NOT NULL AND
        ((OLD.itemexhibitstop = FALSE AND NEW.itemexhibitstop = TRUE) OR
        (OLD.itemexhibitstop = TRUE AND NEW.itemexhibitstop = FALSE)) THEN

        FOR _item IN SELECT itemcd FROM t_ex_stock_detail WHERE sellercd = NEW.membercd GROUP BY itemcd ORDER BY itemcd LOOP

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

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

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

        RAISE NOTICE 'selleritem itemcd % updated...', _item.itemcd;

        END LOOP;
    END IF;

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


-- DROP TRIGGER trigger_seller_attr ON t_seller_attr;
-- トリガ登録
CREATE TRIGGER trigger_seller_attr
    AFTER UPDATE
    ON t_seller_attr
    FOR EACH ROW
    EXECUTE PROCEDURE g_seller_attr();
