<?php
/**
 * @copyright ONE/DEPO Project
 * @license   PHP License 3.01 <http://www.php.net/license/3_01.txt>
 */


/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */

/**
 * 在庫関連
 *
 * @package     Onedepo.Db.tock
 * @author      H.Watanabe <hiroshi.watanabe@netnee.jp>
 * @access      public
 */
class Onedepo_Db_Stock
{
    var $dbaccess;

     /**
     * 全取得
     *
     */
    function getStock($itemcd, $structcd)
    {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT * FROM v_stock_list WHERE itemcd = ? AND structcd = ?";
        $params = array($itemcd, $structcd);

        $log->debug($sql, "Onedepo_Db_Stock#getStock");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Stock#getStock");

        $data = $db->getAll($sql, $params);
        if (DB::isError($data)) {
            die($data->getMessage());
        }

        $this->dbaccess->doClose($db);

        return $data;
    }

     /**
     * stocktitlecdごとにlist取得
     *
     */
    function getStockTilteList($itemcd, $structcd)
    {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT * FROM v_stock_stocktitle_list WHERE itemcd = ? AND structcd = ?";
        $params = array($itemcd, $structcd);

        $log->debug($sql, "Onedepo_Db_Stock#getStockTilteList");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Stock#getStockTilteList");

        $data = $db->getAll($sql, $params);
        if (DB::isError($data)) {
            die($data->getMessage());
        }

        $this->dbaccess->doClose($db);

        return $data;
    }
     /**
     * stocktitlecdから、受注データ取得
     *
     */
    function getStockOrderCount($itemcd, $structcd, $stocktitlecd)
    {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT count(s.itemcd) as cnt
                   FROM t_ex_stock_detail s, t_order_detail o
                  WHERE 
                  s.itemcd=o.itemcd
                  AND s.structcd=o.structcd
                  AND s.stockcd=o.stockcd
                  AND s.itemcd = ? AND s.structcd = ? AND s.stocktitlecd = ?";
        $params = array($itemcd, $structcd, $stocktitlecd);

        $log->debug($sql, "Onedepo_Db_Stock#getStockOrderCount");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Stock#getStockOrderCount");

        $data = $db->getOne($sql, $params);
        if (DB::isError($data)) {
            $log->info($data->userinfo, "getStockOrderCount");
            die($data->getMessage());
        }

        $this->dbaccess->doClose($db);

        return $data;
    }

     /**
     * stocktitlecdごとに条件取得
     *
     */

    function getStockTilte($itemcd, $structcd, $stockcd)
    {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT * FROM t_ex_stock_detail WHERE itemcd = ? AND structcd = ? AND stockcd = ?";
        $params = array($itemcd, $structcd, $stockcd);

        $log->debug($sql, "Onedepo_Db_Stock#getStockTilte");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Stock#getStockTilte");

        $data = $db->getRow($sql, $params);
        if (DB::isError($data)) {
            die($data->getMessage());
        }

        $this->dbaccess->doClose($db);

        return $data;
    }

     /**
     * stocktitlecdごとに条件取得
     *
     */

    function getStockTilteSelect($itemcd, $structcd, $stockcd)
    {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT * FROM v_stock_stocktitle_list WHERE itemcd = ? AND structcd = ? AND stockcd = ?";
        $params = array($itemcd, $structcd, $stockcd);

        $log->debug($sql, "Onedepo_Db_Stock#getStockTilteSelect");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Stock#getStockTilteSelect");

        $data = $db->getRow($sql, $params);
        if (DB::isError($data)) {
            die($data->getMessage());
        }

        $this->dbaccess->doClose($db);

        return $data;
    }

    /**
     * 条件取得
     *
     */

    function getOneStock($itemcd, $structcd, $stocktitlecd)
    {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql = "SELECT * FROM t_ex_stock_detail detail, t_stock_title title WHERE detail.itemcd = ? AND detail.structcd = ? AND detail.stocktitlecd = ? AND detail.stocktitlecd = title.stocktitlecd";
        $params = array($itemcd, $structcd, $stocktitlecd);

        $log->debug($sql, "Onedepo_Db_Stock#getStockSelect");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Stock#getStockSelect");

        $data = $db->getRow($sql, $params);
        if (DB::isError($data)) {
            die($data->getMessage());
        }

        $this->dbaccess->doClose($db);

        return $data;
    }


     /**
     * 条件取得
     *
     */

    function getStockSelect($itemcd, $structcd, $stockcd)
    {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT * FROM v_stock_list WHERE itemcd = ? AND structcd = ? AND stockcd = ?";
        $params = array($itemcd, $structcd, $stockcd);

        $log->debug($sql, "Onedepo_Db_Stock#getStockSelect");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Stock#getStockSelect");

        $data = $db->getRow($sql, $params);
        if (DB::isError($data)) {
            die($data->getMessage());
        }

        $this->dbaccess->doClose($db);

        return $data;
    }

    /**
     * 条件取得
     *
     */

    function getLastUpdDate($itemcd)
    {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT max(s.upddate) as upddate
                        FROM v_stock_list s
                        WHERE (((sellercd IS NOT NULL
                                AND sellernm IS NOT NULL AND memberstate=1)
                                OR (sellercd IS NULL))
                               OR s.reserveflg = 1)
                              AND s.itemcd=?
                        GROUP BY s.itemcd";

        $data = $db->getOne($sql, array($itemcd));
        if (DB::isError($data)) {
            $log->debug($data->userinfo, $func_name);
            die($data->getMessage());
        }
        $this->dbaccess->doClose($db);
        return $data;
    }
    function getStockSelectForItemcd($itemcd, $where="")
    {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        // 在庫有
        $sql  = "(SELECT itemcd, structcd, min(stockcd) as stockcd, ";
        $sql .= "sellernm, takkyubinprc, news, ";
        $sql .= "sellingprice, souryouprc, conditionnm, sellercd, itemexp, ";
        $sql .= "reserveflg, sum(exhibitcount) as exhibitcount, ";
        $sql .= "salestartdate, orderclosedate ";
        $sql .= "FROM v_stock_list WHERE itemcd = ? AND stockstate=3";
        $sql .= " AND (sellercd IS NULL) ";
        //$sql .= " AND salestartdate<=CURRENT_DATE ";
        if (!empty($where)) $sql .= $where;
        $sql .= "GROUP BY itemcd, structcd, sellingprice, sellercd, ";
        $sql .= "sellernm, takkyubinprc, news, ";
        $sql .= "souryouprc, conditionnm, itemexp, reserveflg, ";
        $sql .= "salestartdate, orderclosedate )";
        // 予約中
        $sql .= " UNION ";
        $sql .= "(SELECT itemcd, structcd, min(stockcd) as stockcd, ";
        $sql .= "sellernm, takkyubinprc, news, ";
        $sql .= "sellingprice, souryouprc, conditionnm, sellercd, itemexp, ";
        $sql .= "reserveflg, sum(exhibitcount) as exhibitcount, ";
        $sql .= "salestartdate, orderclosedate ";
        $sql .= "FROM v_stock_list WHERE itemcd = ? AND stockstate=1";
        $sql .= " AND (sellercd IS NULL) ";
        $sql .= " AND salestartdate>CURRENT_DATE ";
        if (!empty($where)) $sql .= $where;
        $sql .= "GROUP BY itemcd, structcd, sellingprice, sellercd, ";
        $sql .= "sellernm, takkyubinprc, news, ";
        $sql .= "souryouprc, conditionnm, itemexp, reserveflg, ";
        $sql .= "salestartdate, orderclosedate )";
        // 出品
        $sql .= " UNION ";
        $sql .= "(SELECT itemcd, structcd, min(stockcd) as stockcd, ";
        $sql .= "sellernm, takkyubinprc, news, ";
        $sql .= "sellingprice, souryouprc, conditionnm, sellercd, itemexp, ";
        $sql .= "reserveflg, sum(exhibitcount) as exhibitcount, ";
        $sql .= "salestartdate, orderclosedate ";
        $sql .= "FROM v_stock_list WHERE itemcd = ? AND stockstate=3";
        $sql .= " AND (sellercd IS NOT NULL AND sellernm IS NOT NULL AND memberstate=1 AND exhibitcount>0) ";
        //$sql .= " AND salestartdate<=CURRENT_DATE ";
        if (!empty($where)) $sql .= $where;
        $sql .= "GROUP BY itemcd, structcd, sellingprice, sellercd, ";
        $sql .= "sellernm, takkyubinprc, news, stocktitlecd, ";
        $sql .= "souryouprc, conditionnm, itemexp, reserveflg, ";
        $sql .= "salestartdate, orderclosedate )";
        $sql .= " ORDER BY sellingprice, sellercd desc";

        $params = array($itemcd, $itemcd, $itemcd);

        $data = $db->getAll($sql, $params);
        //$log->debug($sql , $func_name);
        //$log->debug(print_r($params, true), $func_name);
        if (DB::isError($data)) {
            $log->debug($data->userinfo, $func_name);
            die($data->getMessage());
        }

        $this->dbaccess->doClose($db);

        return $data;
    }
    function getStockDetail($itemcd, $structcd, $where)
    {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT itemcd, structcd, min(stockcd) as stockcd, ";
        $sql .= "min(sellingprice) as sellingprice, souryouprc, reserveflg ";
        $sql .= "FROM v_stock_list WHERE itemcd = ? AND structcd = ? AND (stockstate=1 OR (stockstate=3 AND exhibitcount>0)) ";
        $sql .= $where;
        $sql .= "AND sellingprice=(SELECT min(sellingprice) ";
        $sql .= "FROM v_stock_list WHERE itemcd = ? AND structcd = ? AND (stockstate=1 OR (stockstate=3 AND exhibitcount>0)) ";
        $sql .= $where;
        $sql .= ")";
        $sql .= "GROUP BY itemcd, structcd, souryouprc, reserveflg ";

        $params = array($itemcd, $structcd, $itemcd, $structcd);

        $data = $db->getRow($sql, $params);
        if (DB::isError($data)) {
            $log->debug($data->userinfo, $func_name);
            die($data->getMessage());
        }

        $this->dbaccess->doClose($db);

        return $data;
    }

    function getStockSellData ($itemcd, $structcd) 
    {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql .= "SELECT itemcd, structcd, stockcd, ";
        $sql .= "sellernm, takkyubinprc, news, ";
        $sql .= "sellingprice, souryouprc, conditionnm, sellercd, itemexp, ";
        $sql .= "reserveflg, sum(exhibitcount) as exhibitcount, ";
        $sql .= "salestartdate, orderclosedate ";
        $sql .= "FROM v_stock_list WHERE itemcd = ? AND structcd = ? AND stockstate=3";
        $sql .= " AND (sellercd IS NOT NULL AND sellernm IS NOT NULL AND memberstate=1 AND exhibitcount>0) ";
        //$sql .= " AND salestartdate<=CURRENT_DATE ";
        $sql .= "GROUP BY itemcd, structcd, stockcd, sellingprice, sellercd, ";
        $sql .= "sellernm, takkyubinprc, news, stocktitlecd, ";
        $sql .= "souryouprc, conditionnm, itemexp, reserveflg, ";
        $sql .= "salestartdate, orderclosedate ";
        $sql .= "ORDER BY sellingprice, sellercd desc";

        $params = array($itemcd, $structcd);

        $data = $db->getAll($sql, $params);
        if (DB::isError($data)) {
            $log->error($data->userinfo, $func_name);
            die($data->getMessage());
        }

        $this->dbaccess->doClose($db);

        return $data;
    }

    /**
     * 条件取得
     *
     */
    function getStockData($itemcd, $structcd, $stockcd)
    {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT * FROM v_stock_list ";
        $sql .= "WHERE itemcd = ? AND structcd = ? AND stockcd = ? AND stockstate < 5";
        $sql .= " AND ((sellercd IS NOT NULL AND sellernm IS NOT NULL AND memberstate=1 AND exhibitcount>0)";
        $sql .= " OR (sellercd=9999) OR (sellercd IS NULL))";
        $sql .= " AND ((salestartdate>CURRENT_DATE AND (orderclosedate IS NULL OR orderclosedate>=CURRENT_DATE) AND reserveflg=1)";
        $sql .= " OR ((salestartdate IS NULL OR salestartdate<=CURRENT_DATE)";
        $sql .= "     AND (orderclosedate IS NULL OR orderclosedate<=CURRENT_DATE)) AND reserveflg=0)";
        $params = array($itemcd, $structcd, $stockcd);
        $data = $db->getRow($sql, $params);

        $sql  = "SELECT stockcd, sellercd, exhibitcount, reserveflg ";
        $sql .= "FROM v_stock_list WHERE ";
        $sql .= "itemcd=? AND structcd=? AND sellingprice=? ";
        $sql .= "AND takkyubinprc=? AND souryouprc=? ";
        $sql .= "AND condition=? AND reserveflg=? ";
        $params = array($data["itemcd"],
                        $data["structcd"],
                        $data["sellingprice"],
                        $data["takkyubinprc"],
                        $data["souryouprc"],
                        $data["condition"],
                        $data["reserveflg"]);
        if (!empty($data["itemexp"])) {
            $sql .= " AND REPLACE(BTRIM(itemexp), '\r\n', '')=? ";
            $params[] = str_replace("\r\n", "", trim($data["itemexp"]));
        } else {
            $sql .= " AND itemexp IS NULL ";
        }
        if (!empty($data["sellercd"])) {
            $sql .= " AND sellercd=? ";
            $params[] = $data["sellercd"];
            $sql .= " AND stocktitlecd=? ";
            $params[] = $data["stocktitlecd"];
        }
        $sql .= " AND stockstate < 5";
        $sql .= " AND ((sellercd IS NOT NULL AND sellernm IS NOT NULL AND memberstate=1 AND exhibitcount>0)";
        $sql .= " OR (sellercd=9999) OR (sellercd IS NULL))";
        $sql .= " AND ((salestartdate>CURRENT_DATE AND (orderclosedate IS NULL OR orderclosedate>=CURRENT_DATE) AND reserveflg=1)";
        $sql .= " OR ((salestartdate IS NULL OR salestartdate<=CURRENT_DATE)";
        $sql .= "     AND (orderclosedate IS NULL OR orderclosedate<=CURRENT_DATE)) AND reserveflg=0)";
        $sql .= " ORDER BY stockcd";
        $stock = $db->getAll($sql, $params);

        if (DB::isError($stock)) {
            $log->debug("rollback " . $stock->userinfo, $func_name);
            $this->dbaccess->doClose($db);
            return false;
        }
        $this->dbaccess->doClose($db);

        $data = array();
        foreach ($stock as $item) {
            $data["sellercd"] = $item["sellercd"];
            $data["reserveflg"] = $item["reserveflg"];
            $data["exhibitcount"] += $item["exhibitcount"];
            $data["stockcd"][] = $item["stockcd"];
        }
        return $data;
    }

    /**
     * 中古の商品を$limit件返却
     *
     */
    function getUsedItemList($limit, $shopdiv=null )
    {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT distinct on(itemcd) itemcd ,itemnm, structcd, stockcd, sellingprice ";
        $sql .= "FROM v_stock_list WHERE (itemcd,sellingprice) IN ";
        $sql .= "(select itemcd , min(sellingprice) AS sellingprice FROM v_stock_list ";
        $sql .= "WHERE condition>=2 AND releasedate <= CURRENT_DATE AND stockstate = 3 AND sellingprice > 0 ";
        $sql .= " AND ( (sellercd IS NULL) OR (sellercd IS NOT NULL AND sellernm IS NOT NULL AND memberstate=1 AND exhibitcount>0) ) ";
        if (!empty($shopdiv)) {
            $sql .=  " AND shopdiv = ? ";
            $params[] = $shopdiv;
        }
        $sql .= "GROUP BY itemcd ) ";
        $sql .= " LIMIT ? ";
        $params[] = $limit;

        $log->debug($sql, "Onedepo_Db_Stock#getUsedItemList");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Stock#getUsedItemList");

        $data = $db->getAll($sql, $params);
        if (DB::isError($data)) {
            die($data->getMessage());
        }

        $this->dbaccess->doClose($db);

        return $data;
    }

    /**
     * 更新
     *
     */
    function update($cd, $structcd, $stockcd, $supplidiv, $exhibitcount, $condition, $sellingprice, $souryouprc, $stockstate, $scdstartdate, $scdcompdate, $itemexp, $costprice, $upduser, $updcnt) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Stock#update";

        // 更新ユーザ
        $adminauth =& AdminAuthext::getInstance();
        $upduser = $adminauth->getAuthData("membernm");
        $log->debug("membernm", $upduser);
        //--

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $db->autoCommit();

        // ダウンロード商品は在庫変更不可
        $sql  = "SELECT * FROM t_dl_item ";
        $sql .= " WHERE itemcd=? AND structcd=? AND delflg=FALSE ";
        $params = array($cd, $structcd);
        $data = $db->getRow($sql, $params);
        if (!empty($data)) {
            return false;
        }
        //--

        $params = array($supplidiv,
                        $cd,
                        $structcd,
                        $stockcd,
                        $exhibitcount,
                        $condition,
                        $sellingprice,
                        $souryouprc,
                        $stockstate,
                        $scdstartdate,
                        $scdcompdate,
                        $itemexp,
                        $costprice,
                        $upduser,
                        $_SERVER['REMOTE_ADDR'],
                        $updcnt);

        $sql = "SELECT * FROM f_stock_update(2,NULL,NULL,NULL,?,?,?,?,NULL,?,?,?,?,?,?,?,?,NULL,NULL,?,NULL,NULL,NULL,?,?,?);";

        $log->debug($sql, $func_name);
        $log->debug(print_r($params, true), $func_name);
        $res = $db->getRow($sql, $params);
        if (DB::isError($res) || $res["errcd"] < 0) {
            $log->debug($res->userinfo, $func_name);
            $log->debug("rollback", $func_name);
            $db->rollback();

            return false;
        }
        $log->debug("commit", $func_name);

        $db->commit();
        $this->dbaccess->doClose($db);
        return true;
    }


    /**
     * 追加
     *
     */
    function insert($cd, $structcd, $supplidiv, $exhibitcount, $condition, $sellingprice, $souryouprc, $stockstate, $itemexp,  $costprice, $upduser) {

        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Stock#insert";

        // 更新ユーザ
        $adminauth =& AdminAuthext::getInstance();
        $upduser = $adminauth->getAuthData("membernm");
        $log->debug("membernm", $upduser);
        //--

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        // ダウンロード商品は在庫変更不可
        $sql  = "SELECT * FROM t_dl_item ";
        $sql .= " WHERE itemcd=? AND structcd=? AND delflg=FALSE ";
        $params = array($cd, $structcd);
        $data = $db->getRow($sql, $params);
        if (!empty($data)) {
            return false;
        }
        //--

        $db->autoCommit();
        $params = array($supplidiv,
                        $cd,
                        $structcd,
                        $exhibitcount,
                        $condition,
                        $sellingprice,
                        $souryouprc,
                        $stockstate,
                        $itemexp,
                        $costprice,
                        $upduser,
                        $_SERVER['REMOTE_ADDR']
        );

        $sql = "SELECT * FROM f_stock_update(1,NULL,NULL,NULL,?,?,?,NULL,NULL,?,?,?,?,?,NULL,NULL,?,NULL,NULL,?,NULL,NULL,NULL,?,?,NULL);";


        $log->debug($sql, $func_name);
        $log->debug(print_r($params, true), $func_name);

        $res = $db->getRow($sql, $params);
        if (DB::isError($res) || $res["errcd"] < 0) {
            $log->debug($res->userinfo, $func_name);
            $log->debug("rollback", $func_name);
            $db->rollback();

            return false;
        }
        $log->debug("commit", $func_name);

        $db->commit();
        $this->dbaccess->doClose($db);
        return true;
    }


    /**
     * 追加(会員出品用)
     *
     */
    function insert_ex($cd, $structcd, $sellercd, $supplidiv, $exhibitcount, $condition, $sellingprice, $souryouprc, $stockstate, $scdstartdate, $scdcompdate, $itemexp,  $costprice, $upduser) {

        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Stock#insert_ex";

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        // 自動コミット解除(トランザクション開始)
        $db->autoCommit(false);
        $params = array($supplidiv,
                        $cd,
                        $structcd,
                        $sellercd,
                        $exhibitcount,
                        $condition,
                        $sellingprice,
                        $souryouprc,
                        $stockstate,
                        $scdstartdate,
                        $scdcompdate,
                        $itemexp,
                        $costprice,
                        $upduser,
                        $_SERVER['REMOTE_ADDR']
        );

        $sql = "SELECT * FROM f_stock_update(1,NULL,NULL,NULL,?,?,?,NULL,?,?,?,?,?,?,?,?,?,NULL,NULL,?,NULL,NULL,NULL,?,?,NULL);";

        $log->debug($sql, $func_name);
        $log->debug(print_r($params, true), $func_name);

        $res = $db->getRow($sql, $params);
        if (DB::isError($res) || $res["errcd"] < 0) {
            $log->debug($res->userinfo, $func_name);
            $log->debug("rollback", $func_name);
            $db->rollback();

            return false;
        }
        $log->debug("commit", $func_name);

        $db->commit();
        $this->dbaccess->doClose($db);
        // 追加した stockcd 返却
        return $res["code"];
    }

    /**
     * 削除
     *
     */
    function delete($cd,
                    $structcd,
                    $stockcd,
                    $upduser,
                    $updcnt) {

        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Stock#update";

        // 更新ユーザ
        $adminauth =& AdminAuthext::getInstance();
        $upduser = $adminauth->getAuthData("membernm");
        $log->debug("membernm", $upduser);
        //--

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        // ダウンロード商品は在庫変更不可
        $sql  = "SELECT * FROM t_dl_item ";
        $sql .= " WHERE itemcd=? AND structcd=? AND delflg=FALSE ";
        $params = array($cd, $structcd);
        $data = $db->getRow($sql, $params);
        if (!empty($data)) {
            return false;
        }
        //--

        $db->autoCommit();
        $params = array($cd,
                        $structcd,
                        $stockcd,
                        $upduser,
                        $_SERVER['REMOTE_ADDR'],
                        $updcnt);

        $sql = "SELECT * FROM f_stock_update(3,NULL,NULL,NULL,NULL,?,?,?,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,?,?,?);";

        $log->debug($sql, $func_name);
        $log->debug(print_r($params, true), $func_name);

        $res = $db->getRow($sql, $params);
        if (DB::isError($res) || $res["errcd"] < 0) {
            $log->debug($res->userinfo, $func_name);
            $log->debug("rollback", $func_name);
            $db->rollback();

            return false;
        }
        $log->debug("commit", $func_name);

        $db->commit();
        $this->dbaccess->doClose($db);
        return true;
    }


    /*
    * 棚番 ID 最大値取得
    *
    */
    function getMaxRackid(){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Stock#getMaxRackid";

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = " SELECT MAX(to_number(es.rackid,'000000000000'))";
        $sql .= "   FROM t_ex_stock_detail es ";
        $sql .= "    LEFT JOIN t_order_detail od";
        $sql .= "        ON es.itemcd = od.itemcd AND es.structcd = od.structcd AND es.stockcd = od.stockcd";
        $sql .= "    LEFT JOIN t_forward_detail fd";
        $sql .= "        ON od.orderno = fd.orderno AND od.orderrowno = fd.orderrowno";
        $sql .= "  WHERE es.sellercd IS NULL ";
        $sql .= "    AND es.stockstate = 3 ";
        $sql .= "    AND es.delflg = FALSE ";
        $sql .= "    AND od.orderno IS NULL ";
        $sql .= "    AND od.orderrowno IS NULL ";
        $sql .= "    AND fd.orderno IS NULL ";
        $sql .= "    AND fd.orderrowno IS NULL ; ";

        $res = $db->getOne($sql);
        if (DB::isError($res)) {
            $log->debug($res->userinfo, $func_name);
            return "";
        }

        $this->dbaccess->doClose($db);

        return $res;

    }


    /*
    * JAN コードから棚番入力用データ取得
    *
    */
    function getRackiddataByJan($cd){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Stock#getRackiddataByJan";

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($cd);
        $sql  = " SELECT st_orderno, orderdate, suppliernm, makeritemcd, itemnm, variationnm, itemcd, structcd, stockcd, es_updcnt";
        $sql .= "   FROM v_stock_item ";
        $sql .= "  WHERE sellercd IS NULL ";
        $sql .= "    AND stockstate = 3 ";
        $sql .= "    AND rackid IS NULL ";
        $sql .= "    AND ((od_orderno IS NULL ";
        $sql .= "    AND   od_orderrowno IS NULL ";
        $sql .= "    AND   fd_orderno IS NULL ";
        $sql .= "    AND   fd_orderrowno IS NULL) ";
        $sql .= "     OR  (forwardstate = 9)) ";
        $sql .= "    AND jancd = ? ; ";


        $res = $db->getAll($sql, $params);
        if (DB::isError($res)) {
            $log->debug($res->userinfo, $func_name);
            return "";
        }

        $this->dbaccess->doClose($db);

        return $res;

    }

    /*
    * 品番から棚番入力用データ取得
    *
    */
    function getRackiddataByMIC($cd){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Stock#getRackiddataByJan";

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($cd);
        $sql  = " SELECT st_orderno, orderdate, suppliernm, makeritemcd, itemnm, variationnm, itemcd, structcd, stockcd, es_updcnt";
        $sql .= "   FROM v_stock_item ";
        $sql .= "  WHERE sellercd IS NULL ";
        $sql .= "    AND stockstate = 3 ";
        $sql .= "    AND rackid IS NULL ";
        $sql .= "    AND ((od_orderno IS NULL ";
        $sql .= "    AND   od_orderrowno IS NULL ";
        $sql .= "    AND   fd_orderno IS NULL ";
        $sql .= "    AND   fd_orderrowno IS NULL) ";
        $sql .= "     OR  (forwardstate = 9)) ";
        $sql .= "    AND makeritemcd = ? ; ";


        $res = $db->getAll($sql, $params);
        if (DB::isError($res)) {
            $log->debug($res->userinfo, $func_name);
            return "";
        }

        $this->dbaccess->doClose($db);

        return $res;

    }

    /*
    * 仕入見出しコードから棚番変更用データ取得
    *
    */
    function getRackiddataBySTC($cd){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Stock#getRackiddataBySTC";

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($cd);
        $sql  = " SELECT st_orderno, orderdate, suppliernm, makeritemcd, itemnm, variationnm, rackid, itemcd, structcd, stockcd, es_updcnt";
        $sql .= "   FROM v_stock_item ";
        $sql .= "  WHERE sellercd IS NULL ";
        $sql .= "    AND stockstate = 3 ";
        $sql .= "    AND ((od_orderno IS NULL ";
        $sql .= "    AND   od_orderrowno IS NULL ";
        $sql .= "    AND   fd_orderno IS NULL ";
        $sql .= "    AND   fd_orderrowno IS NULL) ";
        $sql .= "     OR  (forwardstate = 9)) ";
        $sql .= "    AND stocktitlecd = ? ; ";


        $res = $db->getAll($sql, $params);
        if (DB::isError($res)) {
            $log->debug($res->userinfo, $func_name);
            return "";
        }

        $this->dbaccess->doClose($db);

        return $res;

    }



    /*
    * 棚番 ID 更新
    *
    */
    function rackidUpdate($itemcd, $structcd, $stockcd, $rackid, $upduser, $updcnt){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Stock#rackidUpdate";

        // 更新ユーザ
        $adminauth =& AdminAuthext::getInstance();
        $upduser = $adminauth->getAuthData("membernm");
        $log->debug("membernm", $upduser);
        //--

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $db->autoCommit(false);
        $db->query("BEGIN;");

        $params = array($itemcd, $structcd, $stockcd, $rackid, $upduser, $_SERVER['REMOTE_ADDR'], $updcnt);
        $sql = " SELECT * FROM f_ex_stock_detail_update_rackid(?, ?, ?, ?, ?, ?, ?); ";

        $res = $db->getRow($sql, $params);
        if (DB::isError($res)) {
            $db->rollback();
            $db->query("ROLLBACK;");
            $log->error("rollback : ".$res->userinfo, $func_name);

            return array("errcd" => -10, "errmsg" => "DB エラー");
        } elseif ($res["errcd"] < 0){
            $db->rollback();
            $db->query("ROLLBACK;");
            $log->error("rollback : ".$res["errmsg"], $func_name);

            return $res;
        }

        $db->commit();
        $db->query("COMMIT;");
        $log->debug("commit", $func_name);
        $this->dbaccess->doClose($db);

        return $res;

    }

    // 在庫一覧
    function getStockList($to, $from, $date_from, $date_to, $price, $title)
    {
        $func_name = "Onedepo_Db_Stock#getStockList";
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        if(!empty($date_from)) {
            $params[] = $date_from;
            //$where .= ' AND v_stock.salestartdate >= ? ';
            $where .= ' AND struct.salestartdate >= ? ';
        }

        if(!empty($date_to)) {
            $params[] = $date_to;
            //$where .= ' AND v_stock.salestartdate <= ? ';
            $where .= ' AND struct.salestartdate <= ? ';
        }

        if($price == 0) {
            $where .= ' AND stock.sellingprice = 0 ';
        }

        //タイトル(スペース区切りでAND検索)
        if( !empty($title) ) {
            $title = mb_convert_kana($title, "s", "UTF-8");
            $title = explode(' ',$title);

            foreach ($title as $value) {
                $params[] = '%'. $value .'%';
                $where .= " AND basic.itemnm LIKE ?";
            }
        }
        
        $pager   .= "SELECT basic.itemcd, struct.structcd, stock.stockcd, basic.itemnm,";
        $pager   .= " stock.sellingprice, stock.costprice, struct.makerprice ";
        $nolimit .= "SELECT count(basic.itemcd) AS count ";

        $sql .= "FROM t_item_basic_attr basic, t_item_struct struct,t_ex_stock_detail stock ";
        $sql .= "WHERE basic.delflg = FALSE AND struct.delflg = FALSE AND stock.delflg = FALSE ";
        $sql .= " AND basic.itemcd = struct.itemcd AND basic.itemcd = stock.itemcd ";
        $sql .= " AND struct.itemcd = stock.itemcd AND struct.structcd = stock.structcd ";
        $sql .= $where;


        $pager   .= $sql . " LIMIT ". $to  ." OFFSET ". $from;
        $nolimit .= $sql;

        $log->debug($pager,"getStockList");
        $log->debug(print_r($params,true), "getStockList");
        $data = $db->getAll($pager, $params);

        $cnt = $db->getOne($nolimit, $params);
        //print_r($cnt);

        if (DB::isError($data)) {
            $log->info($data->userinfo, $func_name);
            die($data->getMessage());
        }
        $data += array("recordcount" => $cnt);
        $this->dbaccess->doClose($db);

        return $data;
    }

    function updatePrice($cd, $structcd, $stockcd, $price, $state, $upduser) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Stock#updatePrice";

        // 更新ユーザ
        $adminauth =& AdminAuthext::getInstance();
        $upduser = $adminauth->getAuthData("membernm");
        $log->debug("membernm", $upduser);
        //--

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $db->autoCommit();
        $params = array($cd,
                        $structcd,
                        $stockcd,
                        $price,
                        $state,
                        $upduser,
                        $_SERVER['REMOTE_ADDR']);

        $sql = "SELECT * FROM f_stock_update_price(?,?,?,?,?,?,?);";

        $log->debug($sql, $func_name);
        $log->debug(print_r($params, true), $func_name);
        $res = $db->getRow($sql, $params);
        if (DB::isError($res) || $res["errcd"] < 0) {
            $log->debug($res->userinfo, $func_name);
            $log->debug("rollback", $func_name);
            $db->rollback();

            return false;
        }
        $log->debug("commit", $func_name);

        $db->commit();
        $this->dbaccess->doClose($db);
        return true;
    }

}
?>
