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

--DROP FUNCTION f_pre_item_attr_update( IN kubun integer, IN _preitemcd integer, IN _itemnm character varying, IN _itemnmkana character varying, IN _itemexp character varying, IN _basic_option01 character varying, IN _basic_option02 character varying, IN _basic_option03 character varying, IN _basic_option04 character varying, IN _basic_option05 character varying, IN _basic_option06 character varying, IN _basic_option07 character varying, IN _basic_option08 character varying, IN _basic_option09 character varying, IN _basic_option10 character varying, IN _variationnm character varying, IN _makeritemcd character varying, IN _jancd character varying, IN _salestartdate date, IN _orderclosedate date, IN _comment character varying, IN _struct_option01 character varying, IN _struct_option02 character varying, IN _struct_option03 character varying, IN _struct_option04 character varying, IN _struct_option05 character varying, IN _struct_option06 character varying, IN _struct_option07 character varying, IN _struct_option08 character varying, IN _struct_option09 character varying, IN _struct_option10 character varying, IN _releasedate date, IN _web_comment character varying, IN _sellercd integer, IN _download integer, 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_pre_item_attr_update(
    IN kubun        integer,
    
-- basic
    IN _preitemcd   integer,
    IN _itemnm      character varying,
    IN _itemnmkana  character varying,
    IN _itemexp      character varying,
    IN _basic_option01     character varying,
    IN _basic_option02     character varying,
    IN _basic_option03     character varying,
    IN _basic_option04     character varying,
    IN _basic_option05     character varying,
    IN _basic_option06     character varying,
    IN _basic_option07     character varying,
    IN _basic_option08     character varying,
    IN _basic_option09     character varying,
    IN _basic_option10     character varying,
    
-- struct
    IN _variationnm     character varying,
    IN _makeritemcd     character varying,
    IN _jancd           character varying,
    IN _salestartdate   date,
    IN _orderclosedate  date,
    IN _comment         character varying,

    IN _struct_option01        character varying,
    IN _struct_option02        character varying,
    IN _struct_option03        character varying,
    IN _struct_option04        character varying,
    IN _struct_option05        character varying,
    IN _struct_option06        character varying,
    IN _struct_option07        character varying,
    IN _struct_option08        character varying,
    IN _struct_option09        character varying,
    IN _struct_option10        character varying,
    
-- web
    IN _releasedate  date,
    IN _web_comment character varying,
      
-- seller
    IN _sellercd        integer,                -- ForKey

-- download
    IN _download        integer,     -- 0: 通常商品, 1: ダウンロード商品

--
    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 _preitemcd IS NULL THEN
            RAISE EXCEPTION '引数 preitemcd は必須です';
        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_pre_item_attr WHERE preitemcd = _preitemcd;
          IF NOT FOUND OR cntcheck <> count THEN
            RAISE EXCEPTION '更新カウンタが不正です';
          END IF;
    END IF;


 --- 更新処理 ---

    -- 追加
    IF kubun = 1 THEN
        INSERT INTO t_pre_item_attr(
                itemnm,
                itemnmkana,
                itemexp,
                basic_option01,
                basic_option02,
                basic_option03,
                basic_option04,
                basic_option05,
                basic_option06,
                basic_option07,
                basic_option08,
                basic_option09,
                basic_option10,

                variationnm,
                makeritemcd,
                jancd,
                salestartdate,
                orderclosedate,
                comment,

                struct_option01,
                struct_option02,
                struct_option03,
                struct_option04,
                struct_option05,
                struct_option06,
                struct_option07,
                struct_option08,
                struct_option09,
                struct_option10,
                
                releasedate,
                web_comment,
                sellercd,
                download,
                
                updusrnm,
                updpcnm)
         VALUES(
                _itemnm,
                _itemnmkana,
                _itemexp,
                _basic_option01,
                _basic_option02,
                _basic_option03,
                _basic_option04,
                _basic_option05,
                _basic_option06,
                _basic_option07,
                _basic_option08,
                _basic_option09,
                _basic_option10,
    
                _variationnm,
                _makeritemcd,
                _jancd,
                _salestartdate,
                _orderclosedate,
                _comment,

                _struct_option01,
                _struct_option02,
                _struct_option03,
                _struct_option04,
                _struct_option05,
                _struct_option06,
                _struct_option07,
                _struct_option08,
                _struct_option09,
                _struct_option10,
                
                _releasedate,
                _web_comment,
                _sellercd,
                _download,
                
                upduser,
                pc);
        errcd := 1;
        code := lastval();
        RETURN;
    -- 更新
    ELSIF kubun = 2 THEN

        UPDATE t_pre_item_attr
           SET itemnm = _itemnm,
               itemnmkana = _itemnmkana,
               itemexp = _itemexp,
               basic_option01 = _basic_option01,
               basic_option02 = _basic_option02,
               basic_option03 = _basic_option03,
               basic_option04 = _basic_option04,
               basic_option05 = _basic_option05,
               basic_option06 = _basic_option06,
               basic_option07 = _basic_option07,
               basic_option08 = _basic_option08,
               basic_option09 = _basic_option09,
               basic_option10 = _basic_option10,
               
               variationnm = _variationnm,
               makeritemcd = _makeritemcd,
               jancd = _jancd,
               salestartdate = _salestartdate,
               orderclosedate = _orderclosedate,
               comment = _comment,
               
               struct_option01 = _struct_option01,
               struct_option02 = _struct_option02,
               struct_option03 = _struct_option03,
               struct_option04 = _struct_option04,
               struct_option05 = _struct_option05,
               struct_option06 = _struct_option06,
               struct_option07 = _struct_option07,
               struct_option08 = _struct_option08,
               struct_option09 = _struct_option09,
               struct_option10 = _struct_option10,
               
               releasedate = _releasedate,
               web_comment = _web_comment,
               sellercd = _sellercd,
               download = _download,
               
               updusrnm = upduser,
               updpcnm = pc
          WHERE preitemcd = _preitemcd;
        errcd := 1;
        code := _preitemcd;
        RETURN;

    --削除
    ELSIF kubun =3 THEN

        UPDATE t_pre_genre_item
           SET delflg   = TRUE,
               updusrnm = upduser,
               updpcnm  = pc
         WHERE preitemcd = _preitemcd;

        UPDATE t_pre_item_attr
           SET delflg   = TRUE,
               updusrnm = upduser,
               updpcnm  = pc
         WHERE preitemcd = _preitemcd;
        errcd := 1;
        code := _preitemcd;
        RETURN;

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