-- 商品基本属性テーブル 更新

DROP FUNCTION f_item_basic_attr_update(IN kubun integer,IN _itemcd integer, IN _itemnm character varying,IN _itemnmkana character varying, IN _makercd integer,IN _itemexp character varying,IN _option01 character varying,IN _option02 character varying,IN _option03 character varying,IN _option04 character varying,IN _option05 character varying,IN _option06 character varying,IN _option07 character varying,IN _option08 character varying,IN _option09 character varying,IN _option10 character varying,IN upduser character varying,IN pc character varying,IN count integer,OUT errcd integer,OUT errmsg varchar,OUT code integer);


CREATE OR REPLACE FUNCTION f_item_basic_attr_update(
    IN kubun        integer,
    IN _itemcd         integer,
    IN _itemnm      character varying,
    IN _itemnmkana  character varying,
    IN _makercd     integer,
    IN _itemexp      character varying,
    IN _option01     character varying,
    IN _option02     character varying,
    IN _option03     character varying,
    IN _option04     character varying,
    IN _option05     character varying,
    IN _option06     character varying,
    IN _option07     character varying,
    IN _option08     character varying,
    IN _option09     character varying,
    IN _option10     character varying,
    IN upduser     character varying,
    IN pc          character varying,
    IN count       integer,

    OUT errcd      integer,
    OUT errmsg     varchar,
    OUT code       integer
    ) AS
$$
DECLARE
    cntcheck integer;
BEGIN
    --
    -- パラメータチェック
    --

    -- 処理区分
    IF kubun IS NULL OR kubun NOT IN (1,2,3) THEN
        RAISE EXCEPTION '処理区分が不正です';
    END IF;

    -- 必須チェック
    IF kubun IN (2,3) THEN
        IF _itemcd IS NULL THEN
            RAISE EXCEPTION '引数 itemcd は必須です';
        END IF;
        IF count IS NULL THEN
            RAISE EXCEPTION '引数 count は必須です';
        END IF;
    END IF;
    IF kubun IN (1,2,3) THEN
        IF upduser IS NULL THEN
            RAISE EXCEPTION '更新ユーザが指定されていません';
        END IF;
    END IF;

    -- 桁数チェック
    IF kubun IN (1,2) THEN
        IF _itemnm IS NOT NULL THEN
            IF s_check_length(_itemnm, 256) = FALSE THEN
                RAISE EXCEPTION '引数 _itemnm が不正です';
            END IF;
        END IF;
        IF _itemnmkana IS NOT NULL THEN
            IF s_check_length(_itemnmkana, 256) = FALSE THEN
                RAISE EXCEPTION '引数 _itemnmkana が不正です';
            END IF;
        END IF;
    END IF;
    IF kubun IN (1,2,3) THEN
        IF s_check_upd(upduser, pc) = FALSE THEN
            RAISE EXCEPTION '引数 upduser&pc が不正です';
        END IF;
    END IF;

    -- 更新カウンタチェック
    IF kubun IN (2,3) THEN
        SELECT updcnt INTO cntcheck FROM t_item_basic_attr WHERE itemcd = _itemcd;
          IF NOT FOUND OR cntcheck <> count THEN
            RAISE EXCEPTION '更新カウンタが不正です';
          END IF;
    END IF;


 --- 更新処理 ---

    -- 追加
    IF kubun = 1 THEN
        INSERT INTO t_item_basic_attr(
                itemnm,
                itemnmkana,
                makercd,
                itemexp,
                option01,
                option02,
                option03,
                option04,
                option05,
                option06,
                option07,
                option08,
                option09,
                option10,
                updusrnm,
                updpcnm)
         VALUES(
                _itemnm,
                _itemnmkana,
                _makercd,
                _itemexp,
                _option01,
                _option02,
                _option03,
                _option04,
                _option05,
                _option06,
                _option07,
                _option08,
                _option09,
                _option10,
                upduser,
                pc);
        errcd := 1;
        code := lastval();
        RETURN;
    -- 更新
    ELSIF kubun = 2 THEN

        UPDATE t_item_basic_attr
           SET itemnm      = _itemnm,
               itemnmkana  = _itemnmkana,
               makercd     = _makercd,
               itemexp      = _itemexp,
               option01     = _option01,
               option02     = _option02,
               option03     = _option03,
               option04     = _option04,
               option05     = _option05,
               option06     = _option06,
               option07     = _option07,
               option08     = _option08,
               option09     = _option09,
               option10     = _option10,
               updusrnm    = upduser,
               updpcnm     = pc
          WHERE itemcd      = _itemcd;
        errcd := 1;
        code := _itemcd;
        RETURN;

    --削除
    ELSIF kubun =3 THEN
        SELECT itemcd INTO cntcheck FROM v_order_detail_list WHERE itemcd = _itemcd;
        IF FOUND THEN
            errcd := -11;
            errmsg := '受注データが存在するので削除できません';
            RETURN;
        END IF;

        SELECT itemcd INTO cntcheck FROM t_ex_stock_detail WHERE itemcd = _itemcd AND stockstate <> 5 AND delflg = FALSE;
        IF FOUND THEN
            errcd := -11;
            errmsg := '在庫データが存在するので削除できません';
            RETURN;
        END IF;

        UPDATE t_item_web_attr
           SET delflg   = TRUE,
               updusrnm = upduser,
               updpcnm  = pc
         WHERE itemcd = _itemcd;

        UPDATE t_item_struct
           SET delflg   = TRUE,
               updusrnm = upduser,
               updpcnm  = pc
         WHERE itemcd = _itemcd;

        UPDATE t_genre_item
           SET delflg   = TRUE,
               updusrnm = upduser,
               updpcnm  = pc
         WHERE itemcd = _itemcd;



        UPDATE t_item_basic_attr
           SET delflg   = TRUE,
               updusrnm = upduser,
               updpcnm  = pc
         WHERE itemcd = _itemcd;
        errcd := 1;
        code := _itemcd;
        RETURN;

    END IF;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
