-- 出品者評価テーブル 変更
CREATE OR REPLACE FUNCTION f_seller_val_update(
    IN integer,

    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_orderno ALIAS FOR $2;              -- 受注番号
    i_membercd ALIAS FOR $3;             -- 出品者会員コード
    i_consumermembercd ALIAS FOR $4;     -- 購入者会員コード
    i_valuation ALIAS FOR $5;            -- 評価値
    i_comment ALIAS FOR $6;              -- コメント

    i_updusrnm ALIAS FOR $7;             -- 更新ユーザー名
    i_updpcnm ALIAS FOR $8;              -- 更新マシン名
    i_updcnt ALIAS FOR $9;               -- 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 i_orderno 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;

    IF kubun IN (1,2) THEN
        IF i_membercd IS NULL THEN
            RAISE EXCEPTION '出品者会員コードの引数は必須です';
        END IF;
        IF i_consumermembercd IS NULL THEN
            RAISE EXCEPTION '購入者会員コードの引数は必須です';
        END IF;
    END IF;

    IF kubun IN (2,3) THEN
        IF i_updcnt IS NULL THEN
            RAISE EXCEPTION 'UpdCntの引数は必須です';
        END IF;
    END IF;

	-- 存在チェック
    IF kubun IN (1,2) THEN
        SELECT membercd INTO cntcheck FROM t_member_basic_attr WHERE membercd = i_membercd;
	    IF NOT FOUND THEN
	        RAISE EXCEPTION '発注者は存在しません';
    	END IF;
        SELECT membercd INTO cntcheck FROM t_member_basic_attr WHERE membercd = i_consumermembercd;
	    IF NOT FOUND THEN
	        RAISE EXCEPTION '受注者は存在しません';
    	END IF;
    END IF;
    IF kubun IN (2,3) THEN
        SELECT orderno INTO cntcheck FROM t_seller_val WHERE orderno = i_orderno;
	    IF NOT FOUND THEN
	        RAISE EXCEPTION '受注番号は存在しません';
    	END IF;
    END IF;

    -- 更新カウンタチェック
    IF kubun IN (2,3) THEN
        SELECT updcnt INTO cntcheck FROM t_seller_val WHERE orderno = i_orderno;
          IF NOT FOUND OR cntcheck <> i_updcnt THEN
            errcd := -11;
            errmsg := '更新カウンタが不正';
            RETURN;
          END IF;
    END IF;

    ---　更新処理 ---


    nowdate := 'now';              -- 現在の日時を取得
    IF kubun IN (2,3) THEN
        newupdcnt := i_updcnt+1;   -- 更新カウントを進める
    END IF;

    IF kubun = 1 THEN
        INSERT INTO t_seller_val (
            orderno,
            membercd,
            consumermembercd,
            valuation,
            comment,
            delflg,
            adddate,
            upddate,
            updusrnm,
            updpcnm,
            updcnt)
        VALUES(
            i_orderno,
            i_membercd,
            i_consumermembercd,
            i_valuation,
            i_comment,
            FALSE,
            nowdate,
            nowdate,
            i_updusrnm,
            i_updpcnm,
            0);

    -- 更新
    ELSIF kubun = 2 THEN
        UPDATE t_seller_val SET
            membercd           = i_membercd,
            consumermembercd   = i_consumermembercd,
            valuation          = i_valuation,
            comment            = i_comment,
            upddate            = nowdate,
            updusrnm           = i_updusrnm,
            updpcnm            = i_updpcnm,
            updcnt             = newupdcnt
            WHERE orderno = i_orderno;

    -- 削除
    ELSIF kubun = 3 THEN
        UPDATE t_seller_val SET
            delflg = TRUE,
            upddate  = nowdate,
            updusrnm = i_updusrnm,
            updpcnm  = i_updpcnm,
            updcnt   = newupdcnt
        WHERE  orderno = i_orderno;
    END IF;

    code := 1;

END;
$$
LANGUAGE 'plpgsql' VOLATILE;
