-- Function: f_ex_stock_detail_update(IN kubun integer, IN i_itemcd integer, IN i_structcd integer, IN i_stockcd integer, IN i_sellercd integer, IN i_stocktitlecd integer, IN i_exhibitcount integer, IN i_condition integer, IN i_sellingprice integer, IN i_souryouprc integer, IN i_stockstate integer, IN i_scdstartdate date, IN i_scdcompdate date, IN i_itemexp character varying, IN i_rackid character varying, IN i_managecd character varying, IN i_costprice integer, IN i_ntcostprice integer, IN i_consignment integer, IN i_arrivaldate date, IN i_updusrnm character varying, IN i_updpcnm character varying, IN i_updcnt integer, OUT errcd integer, OUT errmsg character varying)

-- DROP FUNCTION f_ex_stock_detail_update(IN kubun integer, IN i_itemcd integer, IN i_structcd integer, IN i_stockcd integer, IN i_sellercd integer, IN i_stocktitlecd integer, IN i_exhibitcount integer, IN i_condition integer, IN i_sellingprice integer, IN i_souryouprc integer, IN i_stockstate integer, IN i_scdstartdate date, IN i_scdcompdate date, IN i_itemexp character varying, IN i_rackid character varying, IN i_managecd character varying, IN i_costprice integer, IN i_ntcostprice integer, IN i_consignment integer, IN i_arrivaldate date, IN i_updusrnm character varying, IN i_updpcnm character varying, IN i_updcnt integer, OUT errcd integer, OUT errmsg character varying);

CREATE OR REPLACE FUNCTION f_ex_stock_detail_update(
 IN kubun integer,          -- 区分

 IN i_itemcd integer,       -- 商品C
 IN i_structcd integer,     -- 構成C
 IN i_stockcd integer,      -- 在庫C
 IN i_sellercd integer,     -- 出品者C
 IN i_stocktitlecd integer, -- 仕入見出しC
 IN i_exhibitcount integer, -- 出品数
 IN i_condition integer,    -- コンディション
 IN i_sellingprice integer, -- 販売価格
 IN i_souryouprc integer,   -- 送料
 IN i_stockstate integer,   -- 在庫状態
 IN i_scdstartdate date,    -- 開始予定日時
 IN i_scdcompdate date,     -- 完了予定日時
 IN i_itemexp varchar,      -- 商品説明
 IN i_rackid varchar,       -- 棚番ID
 IN i_managecd varchar,     -- 管理C
 IN i_costprice integer,            -- 仕入価格
 IN i_ntcostprice integer,          --仕入価格税抜き
 IN i_consignment integer,          --委託
 IN i_arrivaldate date,             --入荷日

 IN i_updusrnm varchar,
 IN i_updpcnm varchar,
 IN i_updcnt integer,


 OUT errcd integer,
 OUT errmsg varchar,
 OUT code  integer) AS
$BODY$

DECLARE
    i integer;
    cntcheck integer;
    exhibitcnt 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 i_stockcd IS NULL THEN
            RAISE EXCEPTION '在庫コードの引数は必須です';
        END IF;
        IF i_updcnt IS NULL THEN
            RAISE EXCEPTION '更新カウンタの引数は必須です';
        END IF;
    END IF;

    IF kubun IN (1,2,3) THEN
        IF i_itemcd IS NULL THEN
            RAISE EXCEPTION '品目コードが指定されていません';
        END IF;
        IF i_structcd 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 '更新マシン名が指定されていません';
        END IF;
    END IF;

    IF kubun NOT IN (1,2) THEN
        IF i_sellingprice < 1 THEN
            RAISE EXCEPTION '販売価格の引数が1円未満です';
        END IF;
    END IF;

    -- 存在チェック
    SELECT COUNT(*) INTO cntcheck FROM t_ex_stock_detail WHERE itemcd = i_itemcd AND structcd = i_structcd AND stockcd = i_stockcd;
      --IF kubun IN (1) AND cntcheck >= 1 THEN
      --    RAISE EXCEPTION 'レコード重複してしまいます';
      --END IF;

      IF kubun IN (2,3) AND cntcheck = 0 THEN
          RAISE EXCEPTION 'レコードが存在しません';
      END IF;

    -- 更新カウンタチェック
    /* とりあえず無効
    IF kubun IN (2,3) THEN
        SELECT updcnt INTO cntcheck FROM t_ex_stock_detail WHERE itemcd = i_itemcd AND structcd = i_structcd AND stockcd = i_stockcd;
          IF NOT FOUND OR cntcheck <> i_updcnt THEN
            RAISE EXCEPTION '更新カウンタが不正';
          END IF;
    END IF;
    */

 ---  更新処理 ---

    -- 追加
    IF kubun = 1 THEN
        i := 1;

        IF i_stockstate = 1 THEN -- 予約のとき
            exhibitcnt := 0;

        ELSIF i_sellercd IS NOT NULL THEN -- 出品者Cが存在するとき
            exhibitcnt := i_exhibitcount;

        ELSE
            exhibitcnt := 1;

        END IF;

        LOOP

            INSERT INTO t_ex_stock_detail(
                    itemcd,
                    structcd,
                    sellercd,
                    stocktitlecd,
                    exhibitcount,
                    condition,
                    sellingprice,
                    souryouprc,
                    stockstate,
                    scdstartdate,
                    scdcompdate,
                    itemexp,
                    rackid,
                    managecd,
                    costprice,
                    ntcostprice,
                    consignment,
                    arrivaldate,
                    updusrnm,
                    updpcnm)
            VALUES(
                    i_itemcd,
                    i_structcd,
                    i_sellercd,
                    i_stocktitlecd,
                    exhibitcnt,
                    i_condition,
                    i_sellingprice,
                    i_souryouprc,
                    i_stockstate,
                    i_scdstartdate,
                    i_scdcompdate,
                    i_itemexp,
                    i_rackid,
                    i_managecd,
                    i_costprice,
                    i_ntcostprice,
                    i_consignment,
                    i_arrivaldate,
                    i_updusrnm,
                    i_updpcnm);

            IF i >= i_exhibitcount OR i_stockstate = 1 OR i_sellercd IS NOT NULL THEN
                EXIT;
            END IF;

            i := i + 1;
        END LOOP;

        SELECT stockcd INTO cntcheck FROM t_ex_stock_detail WHERE upddate = current_timestamp;
        code := cntcheck;
        errcd := 1;

        RETURN;

    -- 変更
    ELSIF kubun = 2 THEN
        UPDATE t_ex_stock_detail SET
            itemcd = i_itemcd,
            structcd = i_structcd,
            sellercd = i_sellercd,
            stocktitlecd = i_stocktitlecd,
            exhibitcount = i_exhibitcount,
            condition = i_condition,
            sellingprice = i_sellingprice,
            souryouprc = i_souryouprc,
            stockstate = i_stockstate,
            scdstartdate = i_scdstartdate,
            scdcompdate = i_scdcompdate,
            itemexp = i_itemexp,
            rackid = i_rackid,
            managecd = i_managecd,
            costprice = i_costprice,
            ntcostprice = i_ntcostprice,
            consignment = i_consignment,
            arrivaldate = i_arrivaldate,

            updusrnm = i_updusrnm,
            updpcnm = i_updpcnm
             WHERE itemcd = i_itemcd AND structcd = i_structcd AND stockcd = i_stockcd;

        SELECT stocktitlecd INTO cntcheck FROM t_ex_stock_detail WHERE itemcd = i_itemcd AND structcd = i_structcd AND stockcd = i_stockcd;
        errcd := 1;
        code := cntcheck;
        RETURN;

    -- 削除
    ELSIF kubun = 3 THEN
        SELECT stocktitlecd INTO cntcheck FROM t_ex_stock_detail WHERE itemcd = i_itemcd AND structcd = i_structcd AND stockcd = i_stockcd;
        UPDATE t_ex_stock_detail SET delflg = TRUE, updusrnm = i_updusrnm, updpcnm = i_updpcnm WHERE itemcd = i_itemcd AND structcd = i_structcd AND stocktitlecd = cntcheck;

        errcd := 1;
        code := cntcheck;
        RETURN;
    END IF;
END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
