-- タグテーブル 変更
CREATE OR REPLACE FUNCTION f_tag_update(
    IN integer,

    IN integer,
    IN integer,
    IN integer,
    IN character varying,

    IN character varying,
    IN character varying,
    IN integer,

    OUT errcd integer,
    OUT errmsg character varying,
    OUT code integer) AS
$$

DECLARE
    kubun ALIAS FOR $1;                  -- 区分

    i_tagcd ALIAS FOR $2;                -- タグコード
    i_itemcd ALIAS FOR $3;               -- 商品コード
    i_usercd ALIAS FOR $4;               -- ユーザーコード
    i_tagnm ALIAS FOR $5;                -- タグ名

    i_updusrnm ALIAS FOR $6;             -- 更新ユーザー名
    i_updpcnm ALIAS FOR $7;              -- 更新マシン名
    i_updcnt ALIAS FOR $8;               -- UpdCnt

    cntcheck integer;
    nowdate timestamp;
    newupdcnt integer;

BEGIN
-- 必須チェック --

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

    IF kubun IN (1) THEN
        IF i_itemcd IS NULL THEN
            RAISE EXCEPTION '商品コードの引数は必須です';
        END IF;
        IF i_usercd IS NULL THEN
            RAISE EXCEPTION 'ユーザーコードの引数は必須です';
        END IF;
        IF i_updusrnm IS NULL THEN
            RAISE EXCEPTION '更新ユーザー名の引数は必須です';
        END IF;
        IF i_updpcnm IS NULL THEN
            RAISE EXCEPTION '更新PC名の引数は必須です';
        END IF;
    END IF;

    IF kubun IN (1) THEN
        IF i_tagnm IS NULL THEN
            RAISE EXCEPTION 'タグ名の引数は必須です';
        END IF;
    END IF;

    IF kubun IN (3) THEN
        IF i_tagcd IS NOT NULL AND i_itemcd IS NOT NULL OR i_usercd IS NOT NULL THEN
            RAISE EXCEPTION 'タグCまたは商品CとユーザーCは必須です';
        END IF;
    END IF;

    -- 存在チェック
    IF kubun IN (1) THEN
        SELECT itemcd INTO cntcheck FROM t_item_basic_attr WHERE itemcd = i_itemcd;
        IF NOT FOUND THEN
            RAISE EXCEPTION '商品コードは存在しません';
        END IF;
        SELECT membercd INTO cntcheck FROM t_member_basic_attr WHERE membercd = i_usercd;
        IF NOT FOUND THEN
            RAISE EXCEPTION 'ユーザーコードは存在しません';
        END IF;
    END IF;



    -- 更新カウンタチェック
    IF kubun = 2 THEN
        SELECT updcnt INTO cntcheck FROM t_tag WHERE tagcd = i_tagcd;
          IF NOT FOUND OR cntcheck <> i_updcnt THEN
            errcd := -11;
            errmsg := '更新カウンタが不正';
            RETURN;
          END IF;
    END IF;

    -- すでに登録されていたら追加しない
    IF kubun = 1 THEN
        SELECT tagcd INTO cntcheck FROM t_tag WHERE itemcd = i_itemcd AND usercd = i_usercd AND tagnm LIKE i_tagnm AND delflg = FALSE;
        IF FOUND THEN
            code := 1;
            RETURN;
        END IF;
    END IF;

    IF kubun = 3 THEN
        SELECT tagcd INTO cntcheck FROM t_tag WHERE tagcd = i_tagcd OR itemcd = i_itemcd AND usercd = i_usercd;
        IF NOT FOUND THEN
            RAISE EXCEPTION 'タグCまたは商品CとユーザーCのレコードがありません';
        END IF;
    END IF;

    --- 更新処理 ---

    IF kubun = 1 THEN
        INSERT INTO t_tag (
            itemcd,
            usercd,
            tagnm,
            createdate,
            referdate,
            delflg,
            adddate,
            upddate,
            updusrnm,
            updpcnm,
            updcnt
        )VALUES(
            i_itemcd,
            i_usercd,
            i_tagnm,
            current_timestamp,
            current_timestamp,
            FALSE,
            nowdate,
            nowdate,
            i_updusrnm,
            i_updpcnm,
            0
        );

    -- 更新
    ELSIF kubun = 2 THEN
        UPDATE t_tag SET
            itemcd     = i_itemcd,
            usercd     = i_usercd,
            tagnm      = i_tagnm,
            upddate    = current_timestamp,
            updusrnm   = i_updusrnm,
            updpcnm    = i_updpcnm
            WHERE tagcd = i_tagcd;

    -- 削除
    ELSIF kubun = 3 THEN
        IF i_itemcd IS NOT NULL AND i_usercd IS NOT NULL THEN
            DELETE FROM t_tag WHERE itemcd = i_itemcd AND usercd = i_usercd;

        ELSIF i_tagcd IS NOT NULL THEN
            DELETE FROM t_tag WHERE tagcd = i_tagcd;

        END IF;
    END IF;

    code := 1;

END;
$$
LANGUAGE 'plpgsql' VOLATILE;
