--// 最低価格を取得する
--   該当データ無しは NULL
--   引数の「区分」無しは全て

DROP FUNCTION s_get_lowprice (integer, integer);

CREATE OR REPLACE FUNCTION s_get_lowprice (
    IN kubun    integer,    -- 区分（1:全て 2:中古 3:新品）
    IN _itemcd  integer
) RETURNS integer AS
$BODY$
DECLARE
    _kubun integer;
    _price integer;
    _sql1  varchar(4000);
    _sql2  varchar(4000);
BEGIN
    -- 引数チェック
    IF kubun IN (1,2,3) THEN
        _kubun := kubun;
    ELSE
        _kubun := 1;
    END IF;

    --// 価格取得
    _price := 0;    -- 初期化

    -- 新品中古の条件追加
    IF _kubun = 2 THEN
        _sql2 = ' AND e.condition IN (2,3,4,5) ';
    ELSIF _kubun = 3 THEN
        _sql2 = ' AND e.condition = 1 ';
    ELSE
        _sql2 = '';
    END IF;

    _sql1 := '
    SELECT sellingprice FROM t_ex_stock_detail e
       INNER JOIN t_item_basic_attr ii
         ON ii.itemcd=e.itemcd AND ii.delflg=FALSE
       INNER JOIN t_item_struct ss
         ON ss.itemcd=e.itemcd AND ss.structcd=e.structcd AND ss.delflg=FALSE
       LEFT JOIN t_seller_attr se
         ON e.sellercd=se.membercd AND se.delflg=FALSE
       LEFT JOIN t_member_basic_attr me
         ON me.membercd=se.membercd AND me.delflg=FALSE
     WHERE e.itemcd = ' || _itemcd || '
       AND COALESCE(se.itemexhibitstop, FALSE) = FALSE
       AND COALESCE(me.memberstate, 1) = 1
       AND e.delflg = FALSE ' || _sql2 ||
     ' AND 
       ((e.exhibitcount - 
        (SELECT COALESCE(sum(count), 0) FROM t_order_detail o
                        WHERE e.itemcd = o.itemcd
                          AND e.structcd = o.structcd
                          AND e.stockcd = o.stockcd
                          AND o.delflg = FALSE AND o.preorderflg = FALSE 
                          AND NOT EXISTS (SELECT * FROM t_forward_detail f
                                       WHERE f.orderno=o.orderno
                                       AND f.orderrowno=o.orderrowno
                                       AND (f.forwardstate=9 or (f.forwardstate=8 and f.returndate is not null)))
        ) > 0 AND e.stockstate = 3) 
       or (e.stockstate = 1 AND e.exhibitcount = 0))

     ORDER BY e.sellingprice
     LIMIT 1';

    EXECUTE _sql1 INTO _price;
    -- 該当データ無しは NULLが返る

    RETURN _price;
END;

$BODY$ LANGUAGE 'plpgsql' VOLATILE;