-- 仮受注テーブル 追加

CREATE OR REPLACE FUNCTION f_pre_order_detail_update(
    kubun             integer,
    _preordermember   integer,
    _takkyubin            integer,
    _itemcd           integer,
    _structcd         integer,
    _stockcd          integer,
    _count            integer,
    upduser           character varying,
    pc                character varying)
RETURNS integer AS
$$
DECLARE
    rec      RECORD;
    cntcheck integer;
    --kubun    integer;
    title    integer;
    in_kubun integer;
BEGIN
    --
    -- パラメータチェック
    --

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

    IF kubun IN (2,3,4,5) THEN
        IF _preordermember IS NULL THEN
            RAISE EXCEPTION '引数 _preordermember は必須です';
        END IF;
    END IF;

    IF kubun IN (1,2,3) THEN
        IF _itemcd IS NULL THEN
            RAISE EXCEPTION '引数 _itemcd は必須です';
        END IF;
        IF _structcd IS NULL THEN
            RAISE EXCEPTION '引数 _structcd は必須です';
        END IF;
        IF _stockcd IS NULL THEN
            RAISE EXCEPTION '引数 _stockcd は必須です';
        END IF;
    END IF;

    IF kubun IN (1,2) THEN
        IF _count IS NULL THEN
            RAISE EXCEPTION '引数 _count は必須です';
        END IF;
        IF s_check_upd(upduser, pc) = FALSE THEN
            RAISE EXCEPTION '引数 upduser&pc が不正です';
        END IF;
    END IF;

    -- 存在チェック

    IF kubun IN (1,2,3) THEN
        SELECT stockcd INTO cntcheck FROM t_ex_stock_detail WHERE itemcd = _itemcd AND structcd = _structcd AND stockcd = _stockcd;
        IF NOT FOUND THEN
            RAISE EXCEPTION '引数 _itemcd,_structcd,_stockcd のレコードが存在しません';
        END IF;
    END IF;


    --前処理
    IF kubun = 1 THEN
        SELECT preorderno, takkyubin, upddate INTO rec FROM t_pre_order_title WHERE preordermember = _preordermember;
        IF FOUND THEN
            IF current_timestamp > rec.upddate + '1 days' THEN   -- 1日以上
                in_kubun := 2;
            ELSE -- 1日未満
                in_kubun := 3;
            END IF;
        ELSE
            in_kubun := 1;
        END IF;
    ELSE
        SELECT preorderno INTO cntcheck FROM t_pre_order_title WHERE preordermember = _preordermember;
    END IF;

 --- 更新処理 ---
    IF kubun = 1 THEN
        -- 追加
        IF in_kubun = 1 THEN
            INSERT INTO t_pre_order_title (preordermember, preorderdate, takkyubin, updusrnm, updpcnm) VALUES(_preordermember, current_date, _takkyubin, upduser, pc);
            INSERT INTO t_pre_order_detail(preorderno, itemcd, structcd, stockcd, count, updusrnm, updpcnm) VALUES(lastval(), _itemcd, _structcd, _stockcd, _count, upduser, pc);

        ELSIF in_kubun = 2 THEN
            UPDATE t_pre_order_title SET preorderdate = current_date, takkyubin = rec.takkyubin WHERE preorderno = rec.preorderno;
            DELETE FROM t_pre_order_detail WHERE preorderno = rec.preorderno;
            INSERT INTO t_pre_order_detail(preorderno, itemcd, structcd, stockcd, count, updusrnm, updpcnm) VALUES(rec.preorderno, _itemcd, _structcd, _stockcd, _count, upduser, pc);

        ELSIF in_kubun = 3 THEN
            UPDATE t_pre_order_title SET preorderdate = current_date, takkyubin = rec.takkyubin WHERE preorderno = rec.preorderno;
            SELECT preorderno INTO cntcheck FROM t_pre_order_detail
             WHERE preorderno = rec.preorderno AND itemcd = _itemcd AND structcd = _structcd AND stockcd = _stockcd;
            IF NOT FOUND THEN
                INSERT INTO t_pre_order_detail(preorderno, itemcd, structcd, stockcd, count, updusrnm, updpcnm) VALUES(rec.preorderno, _itemcd, _structcd, _stockcd, _count, upduser, pc);
            END IF;
        END IF;

    ELSIF kubun = 2 THEN
        UPDATE t_pre_order_title SET preorderdate = current_date, takkyubin = _takkyubin, updusrnm = upduser, updpcnm = pc WHERE preorderno = cntcheck;

        UPDATE t_pre_order_detail SET count = _count WHERE preorderno = cntcheck AND itemcd = _itemcd AND structcd = _structcd AND stockcd = _stockcd;

    ELSIF kubun = 3 THEN
        UPDATE t_pre_order_title SET preorderdate = current_date, updusrnm = upduser, updpcnm = pc WHERE preorderno = cntcheck;
        DELETE FROM t_pre_order_detail WHERE preorderno = cntcheck AND itemcd = _itemcd AND structcd = _structcd AND stockcd = _stockcd;

    ELSIF kubun = 4 THEN
        DELETE FROM t_pre_order_title WHERE preorderno = cntcheck;
        DELETE FROM t_pre_order_detail WHERE preorderno = cntcheck;

    ELSIF kubun = 5 THEN
        UPDATE t_pre_order_title SET preorderdate = current_date, updusrnm = upduser, updpcnm = pc WHERE preorderno = cntcheck;
        DELETE FROM t_pre_order_detail WHERE preorderno = cntcheck AND itemcd = _itemcd AND structcd = _structcd;

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