-- 商品在庫 変更

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

    IN i_stocktitlecd  integer,    -- 仕入見出しC
    IN i_orderno       varchar,    -- 発注番号
    IN i_suppliercd    integer,    -- 仕入先C
    IN i_supplidiv     integer,    -- 仕入区分

    IN i_itemcd        integer,    -- 商品C
    IN i_structcd      integer,    -- 構成C
    IN i_stockcd       integer,    -- 在庫C
    IN i_sellercd      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
    _kubun integer;
    stocktitle integer;
    _ntcostprice integer;
    _orderno varchar;
    _suppliercd integer;
    rec RECORD;
    cntcheck integer;
    i integer;
    _stock 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_stocktitlecd IS NULL THEN
            RAISE EXCEPTION '仕入見出しコードの引数は必須です';
        END IF;
        IF i_updcnt IS NULL THEN
            RAISE EXCEPTION '更新カウンタの引数は必須です';
        END IF;
        */

        --IF i_exhibitcount < 1 THEN
        --    RAISE EXCEPTION '出品数が1未満です';
        --END IF;
    END IF;

    IF kubun IN (1,2) THEN
        IF i_updusrnm IS NULL THEN
            RAISE EXCEPTION '更新ユーザ名が指定されていません';
        END IF;
        IF i_updpcnm IS NULL THEN
            RAISE EXCEPTION '更新マシン名が指定されていません';
        END IF;
    END IF;

 -- 更新カウンタチェック
    /*とりあえず無効
    IF kubun IN (2,3) THEN
        SELECT updcnt INTO cntcheck FROM t_stock_title WHERE stocktitlecd = i_stocktitlecd;
          IF NOT FOUND OR cntcheck <> i_updcnt THEN
            RAISE EXCEPTION '更新カウンタが不正';
          END IF;
    END IF;
    */
 ---  更新処理 ---

    -- 追加
    IF kubun = 1 THEN
        stocktitle := NULL;

        SELECT stocktitlecd INTO stocktitle FROM t_stock_title WHERE stocktitlecd = i_stocktitlecd;

        -- レコードがないとき

        IF stocktitle IS NULL THEN
            -- 追加

            SELECT * FROM f_stock_title_update(
                1,    -- 区分

                NULL,    -- 仕入見出しC
                i_orderno,    -- 発注番号
                i_suppliercd,    -- 仕入先C
                i_supplidiv,    -- 仕入区分

                i_updusrnm,    -- 更新ユーザ
                i_updpcnm,    -- 更新マシン
                NULL
            ) INTO rec;
            stocktitle := rec.code;

        ELSE
            -- 変更
            SELECT * FROM f_stock_title_update(
                2,    -- 区分
                stocktitle,    -- 仕入見出しC
                i_orderno,         -- 発注番号
                i_suppliercd,      -- 仕入先C
                i_supplidiv,       -- 仕入区分

                i_updusrnm,    -- 更新ユーザ
                i_updpcnm,    -- 更新マシン
                i_updcnt    -- 更新カウンタ
            ) INTO rec;

        END IF;

        -- 追加

        SELECT * FROM f_ex_stock_detail_update(
            1,              -- 区分

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

            i_updusrnm,
            i_updpcnm,
            NULL
        ) INTO rec;

        errcd := rec.errcd;
        code := rec.code;
        RETURN;

    ELSIF kubun = 2 THEN
        -- 変更

        -- レコードの不変情報取得
        SELECT sellercd,
               stocktitlecd,
               Condition,
               SellingPrice,
               SouryouPrc,
               StockState,
               ScdStartDate,
               ScdCompDate,
               ItemExp,
               ManageCd,
               CostPrice,
               NTCostPrice,
               Consignment,
               ArrivalDate
            INTO rec FROM t_ex_stock_detail WHERE itemcd = i_itemcd AND structcd = i_structcd AND stockcd = i_stockcd;
        stocktitle := rec.stocktitlecd;

        IF i_suppliercd IS NULL THEN
            _ntcostprice := rec.ntcostprice;
        ELSE
            _ntcostprice := i_ntcostprice;
        END IF;

        IF rec.stocktitlecd IS NOT NULL THEN

            -- DELETE FROM t_ex_stock_detail WHERE itemcd = i_itemcd AND structcd = i_structcd AND stocktitlecd = stocktitle;

            -- 現在のレコード数取得
            SELECT stocktitlecdcont INTO cntcheck FROM v_stock_stocktitle_list WHERE stocktitlecd = rec.stocktitlecd AND itemcd = i_itemcd;
            cntcheck := i_exhibitcount - cntcheck;

            IF cntcheck > 0 THEN
                -- 在庫増
                -- NULL なら更新しない

                IF i_suppliercd IS NULL THEN
                    _ntcostprice := rec.ntcostprice;
                ELSE
                    _ntcostprice := i_ntcostprice;
                END IF;

                i := 1;

                LOOP
                    INSERT INTO t_ex_stock_detail(
                        itemcd,
                        structcd,

                        stocktitlecd,
                        exhibitcount,

                        condition,
                        sellingprice,

                        souryouprc,
                        stockstate,

                        scdstartdate,
                        scdcompdate,

                        itemexp,
                        managecd,

                        costprice,
                        ntcostprice,

                        consignment,
                        arrivaldate,

                        updusrnm,
                        updpcnm)
                    VALUES(
                        i_itemcd,
                        i_structcd,

                        stocktitle,
                        1,

                        i_condition,
                        i_sellingprice,

                        i_souryouprc,
                        i_stockstate,

                        i_scdstartdate,
                        i_scdcompdate,

                        i_itemexp,
                        i_managecd,

                        i_costprice,
                        _ntcostprice,

                        i_consignment,
                        i_arrivaldate,

                        i_updusrnm,
                        i_updpcnm);

                    IF i >= cntcheck THEN
                        EXIT;
                    END IF;

                    i := i + 1;
                END LOOP;
            ELSIF cntcheck < 0 THEN
                -- 在庫減
                -- 削除できる在庫数
                --SELECT stocktitlecdcont INTO _stock FROM v_stock_stocktitle_list WHERE stocktitlecd = stocktitle AND itemcd = i_itemcd;

                SELECT COUNT(*) INTO _stock FROM t_ex_stock_detail
                    WHERE itemcd =       i_itemcd
                    AND   structcd =     i_structcd
                    AND   stocktitlecd = stocktitle
                    AND   stockcd NOT IN ( 
                      SELECT stockcd FROM t_order_detail t_o 
                        LEFT JOIN t_forward_detail t_f ON t_f.orderno = t_o.orderno AND t_f.orderrowno = t_o.orderrowno 
                       WHERE t_o.itemcd = i_itemcd AND t_o.structcd = i_structcd 
                         AND (t_f.forwardstate IS NULL OR NOT (t_f.forwardstate = 9 OR (t_f.forwardstate = 8 AND t_f.returndate IS NOT NULL))) 
                      )
                    AND   rackid IS NULL
                    AND   stockstate = 3
                    AND   delflg = false;

                _stock := _stock * -1;

                IF cntcheck < _stock THEN
                    errcd := -21;
                    RETURN;
                END IF;

                LOOP
                    SELECT max(stockcd) INTO _stock FROM t_ex_stock_detail
                        WHERE itemcd =       i_itemcd
                        AND   structcd =     i_structcd
                        AND   stocktitlecd = stocktitle
                        AND   stockcd NOT IN  ( 
                          SELECT stockcd FROM t_order_detail t_o 
                            LEFT JOIN t_forward_detail t_f ON t_f.orderno = t_o.orderno AND t_f.orderrowno = t_o.orderrowno 
                           WHERE t_o.itemcd = i_itemcd AND t_o.structcd = i_structcd 
                             AND (t_f.forwardstate IS NULL OR NOT (t_f.forwardstate = 9 OR (t_f.forwardstate = 8 AND t_f.returndate IS NOT NULL))) 
                          ) 
                        AND   stockstate = 3
                        AND   rackid IS NULL
                        AND   delflg = false
                    GROUP BY stocktitlecd, itemcd, structcd;

                    IF NOT FOUND THEN
                        errcd := -22;
                        RETURN;
                    END IF;

                    UPDATE t_ex_stock_detail SET delflg = TRUE
                                            WHERE itemcd = i_itemcd
                                            AND structcd = i_structcd
                                            AND stockcd  = _stock;

                    cntcheck := cntcheck + 1;
                    IF 0 <= cntcheck THEN
                        EXIT;
                    END IF;
                END LOOP;

            END IF;

            UPDATE t_ex_stock_detail SET condition = i_condition,
                                            sellingprice = i_sellingprice,
                                            souryouprc = i_souryouprc,
                                            stockstate = i_stockstate,
                                            scdstartdate = i_scdstartdate,
                                            scdcompdate = i_scdcompdate,
                                            itemexp = i_itemexp,
                                            managecd = i_managecd,
                                            costprice = i_costprice,
                                            ntcostprice = _ntcostprice,
                                            consignment = i_consignment,
                                            arrivaldate = i_arrivaldate
                                            WHERE stocktitlecd = stocktitle
                                            AND itemcd = i_itemcd
                                            AND structcd = i_structcd
                                            AND delflg = false
                                            AND stockcd NOT IN( 
                                              SELECT stockcd FROM t_order_detail t_o 
                                                LEFT JOIN t_forward_detail t_f ON t_f.orderno = t_o.orderno AND t_f.orderrowno = t_o.orderrowno 
                                               WHERE t_o.itemcd = i_itemcd AND t_o.structcd = i_structcd 
                                                 AND (t_f.forwardstate IS NULL OR NOT (t_f.forwardstate = 9 OR (t_f.forwardstate = 8 AND t_f.returndate IS NOT NULL))) 
                                              );

            SELECT orderno, suppliercd INTO rec FROM t_stock_title WHERE stocktitlecd = stocktitle;

            _orderno := i_orderno;
            IF i_orderno IS NULL THEN
                _orderno := rec.orderno;
            END IF;

            _suppliercd := i_suppliercd;
            IF i_suppliercd IS NULL THEN
                _suppliercd := rec.suppliercd;
            END IF;

            --見出しのみ変更
            SELECT * FROM f_stock_title_update(
                2,    -- 区分
                stocktitle,    -- 仕入見出しC
                _orderno,         -- 発注番号
                _suppliercd,      -- 仕入先C
                i_supplidiv,       -- 仕入区分

                i_updusrnm,    -- 更新ユーザ
                i_updpcnm,    -- 更新マシン
                i_updcnt    -- 更新カウンタ
            ) INTO rec;

        ELSE  -- 存在しない時、新たに追加
            --見出しを追加
            DELETE FROM t_ex_stock_detail WHERE itemcd = i_itemcd AND structcd = i_structcd AND stockcd = i_stockcd;
            SELECT * FROM f_stock_title_update(
                1,    -- 区分
                NULL,    -- 仕入見出しC
                i_orderno,         -- 発注番号
                i_suppliercd,      -- 仕入先C
                i_supplidiv,       -- 仕入区分

                i_updusrnm,    -- 更新ユーザ
                i_updpcnm,    -- 更新マシン
                i_updcnt    -- 更新カウンタ
            ) INTO rec;

            SELECT * FROM f_ex_stock_detail_update(
                1,              -- 区分

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

                i_updusrnm,
                i_updpcnm,
                i_updcnt
            ) INTO rec;

        END IF;

        errcd := rec.errcd;
        RETURN;

    -- 削除
    ELSIF kubun =3 THEN

        -- 詳細削除
        SELECT stocktitlecd INTO stocktitle FROM t_ex_stock_detail WHERE itemcd = i_itemcd AND structcd = i_structcd AND stockcd = i_stockcd;
/*
        SELECT stockcd INTO _stock FROM t_ex_stock_detail
            WHERE stocktitlecd = stocktitle
            AND itemcd = i_itemcd
            AND structcd = i_structcd
            AND stockcd IN
            (SELECT stockcd FROM t_order_detail t_o WHERE t_o.itemcd = i_itemcd AND t_o.structcd = i_structcd);
*/
        IF stocktitle IS NULL THEN
            --在庫なし削除
            UPDATE t_ex_stock_detail SET delflg = TRUE, updusrnm = i_updusrnm, updpcnm = i_updpcnm WHERE itemcd = i_itemcd AND structcd = i_structcd AND stockcd = i_stockcd;
        ELSE

            UPDATE t_ex_stock_detail SET delflg = TRUE, updusrnm = i_updusrnm, updpcnm = i_updpcnm
                WHERE stocktitlecd = stocktitle
                AND itemcd = i_itemcd
                AND structcd = i_structcd
                AND stockcd NOT IN ( 
                      SELECT stockcd FROM t_order_detail t_o 
                        LEFT JOIN t_forward_detail t_f ON t_f.orderno = t_o.orderno AND t_f.orderrowno = t_o.orderrowno 
                       WHERE t_o.itemcd = i_itemcd AND t_o.structcd = i_structcd 
                         AND (t_f.forwardstate IS NULL OR NOT (t_f.forwardstate = 9 OR (t_f.forwardstate = 8 AND t_f.returndate IS NOT NULL))) 
                      );


        END IF;

        errcd := 1;
        RETURN;

    END IF;
END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE;