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


/**
 * シリーズマスタテーブル関連
 *
 * @package     Onedepo.Db.Series
 * @author      netnee T.Otogawa<tomokazu.otogawa@netnee.jp>
 * @access      public
 */
class Onedepo_Db_Series
{
    /**
     * DBアクセスオブジェクト
     *
     * @var     object
     * @access  private
     */
    var $dbaccess;


    /**
     * 全件取得
     * 
     * @param string $sort ソートキー
     * @return object 取得レコード
     */
    function selectAll($sort="seriesnmkana")
    {

        $log =& LogFactory::getLog();

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

        $params = array($sort);

        $sql  = "SELECT makercd, seriescd, seriesnm, seriesnmkana, recommenddiv ";
        $sql .= "  FROM t_series ";
        $sql .= " WHERE delflg = FALSE ";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);

        $data = $db->getAll($sql);
        $this->dbaccess->doClose($db);

        return $data;
    }
    
    


    /**
     * 条件取得
     * 
     * @param string $code コード
     * @return object 取得レコード
     */
    function selectKey($makercd,$seriescd)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Series#selectKey";
       
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($makercd,$seriescd);

        $sql  = "SELECT makercd, seriescd, seriesnm, seriesnmkana, recommenddiv, updcnt ";
        $sql .= "  FROM t_series ";
        $sql .= " WHERE delflg = FALSE ";
        $sql .= "   AND makercd = ?";
        $sql .= "   AND seriescd = ?;";

        $data = $db->getRow($sql, $params);
        $this->dbaccess->doClose($db);

        return $data;

    }

    /**
     * サーチ検索
     *
     * @param string  $word 検索ワード
     * @param string  $sort ソートキー
     * @return object 取得レコード
     */
    function selectSearch($word, $sort="disppriority")
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Series#selectSearch";

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

        $params = NULL;

        $sql  = "SELECT makercd, seriescd, seriesnm, seriesno, disppriority, updcnt ";
        $sql .= "  FROM t_series ";
        $sql .= " WHERE delflg = FALSE ";

        // 検索ワード
        if (!empty($word)) {
            $sql .= "   AND (seriesnm LIKE ? ";
            $sql .= "    OR  seriesno LIKE ? )";
            $params[] = "%". trim($word) . "%";
            $params[] = "%". trim($word) . "%";
        }
        
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);
        
        $data = $db->getAll($sql, $params);
        if (DB::isError($data)) {
            $log->debug($data->userinfo, $func_name);
            die($data->getMessage());
        }

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

        return $data;
    }
    


    /**
     * 追加
     * 
     * @param string  $name 名称
     * @param string  $no 番号
     * @param integer $disppriority 表示順
     * @param string  $upduser 更新ユーザ
     * @return boolean 結果ステータス
     */
    function insert($makercd, $name, $kana, $recommend, $upduser) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Series#insert";

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

        $db = $this->dbaccess->doConnect();
        $db->autoCommit();
        $params = array($makercd, $name, $kana, $recommend, $upduser, $_SERVER['REMOTE_ADDR']);

        $sql  = "select f_series_update(1, ?, NULL, ?, ?, ?, ?, ?, NULL);";

        $res = $db->getOne($sql, $params);
        if (DB::isError($res) || $res < 0) {
            $log->error("rollback". $res->userinfo, $func_name);
            $db->rollback();
            $this->dbaccess->doClose($db);
            return false;
        }
        $log->debug("commit", $func_name);

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

    /**
     * 更新
     *
     * @param string  $code コード
     * @param string  $name 名称
     * @param string  $no 番号
     * @param integer $disppriority 表示順
     * @param integer $updcnt 更新カウンタ
     * @param string  $upduser 更新ユーザ
     * @return boolean 結果ステータス
     */
    function update($makercd, $seriescd, $name, $kana, $recommend, $upduser, $updcnt) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Series#update";

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

        $db = $this->dbaccess->doConnect();
        $db->autoCommit();
        $params = array($makercd, $seriescd, $name, $kana, $recommend, $upduser, $_SERVER['REMOTE_ADDR'], $updcnt);

        $sql  = "select f_series_update(2, ?, ?, ?, ?, ?, ?, ?, ?);";

        $res = $db->getOne($sql, $params);
        if (DB::isError($res) || $res < 0) {
            $log->error("rollback". $res->userinfo, $func_name);
            $db->rollback();
            $this->dbaccess->doClose($db);
            return false;
        }
        $log->debug("commit", $func_name);

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

    /**
     * 削除
     *
     * @param string  $code コード
     * @param integer $updcnt 更新カウンタ
     * @param string  $upduser 更新ユーザ
     * @return boolean 結果ステータス
     */
    function delete($makercd, $seriescd, $upduser, $updcnt) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Series#delete";

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

        $db = $this->dbaccess->doConnect();
        $db->autoCommit();
        $params = array($makercd, $seriescd, $upduser, $_SERVER['REMOTE_ADDR'], $updcnt);

        $sql  = "select f_series_update(3, ?, ?, NULL, NULL, NULL, ?, ?, ?);";

        $res = $db->getOne($sql, $params);
        if (DB::isError($res) || $res < 0) {
            $log->error("rollback" . $res->userinfo, $func_name);
            $db->rollback();
            $this->dbaccess->doClose($db);
            return false;
        }
        $log->debug("commit", $func_name);

        $db->commit();
        $this->dbaccess->doClose($db);
        return true;
    }
    
    
    /**
     *v_series_listからの全件取得
     * 
     * @param string $sort ソートキー
     * @return object 取得レコード
     */
    function selectV_series_list($shopdiv=null, $sort="seriesnmkana")
    {

        $log =& LogFactory::getLog();

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

        $params = array();

        $sql  = "SELECT makercd, makernm, seriescd, seriesnm, seriesnmkana, recommenddiv ";
        $sql .= "  FROM v_series_list ";
        if (!empty($shopdiv)) { 
            $sql .=  " WHERE shopdiv = ?"; 
            $params[] = $shopdiv; 
        } 
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);

        $data = $db->getAll($sql, $params);
        $this->dbaccess->doClose($db);

        return $data;
    }
    
    /**
     * viewサーチ検索
     *
     * @param string  $word 検索ワード
     * @param string  $sort ソートキー
     * @return object 取得レコード
     */
    function selectSearchView($shopdiv=null, $word, $sort="seriesnmkana")
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Series#selectSearch";

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

        $params = NULL;

        $sql  = "SELECT makercd, makernm, seriescd, seriesnm, seriesnmkana, recommenddiv, updcnt ";
        $sql .= "  FROM v_series_list ";
        $sql .= " WHERE ";

        // 検索ワード
        if (!empty($word)) {
            $sql .= "  ( seriesnm LIKE ? ";
            $sql .= "    OR  seriescd LIKE ? ";
            $sql .= "    OR  makercd LIKE ? ";
            $sql .= "    OR  makernm LIKE ? ) ";
            $params[] = "%". trim($word) . "%";
            $params[] = "%". trim($word) . "%";
            $params[] = "%". trim($word) . "%";
            $params[] = "%". trim($word) . "%";
        }
        if (!empty($shopdiv)) { 
            $sql .=  " AND shopdiv = ?"; 
            $params[] = $shopdiv; 
        } 
        
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);
        $log->debug($sql, $func_name);
        $log->debug(print_r($params, true), $func_name);

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

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

        return $data;
    }
    
    /**
     * view条件取得 
     * 
     * @param string $code コード
     * @return object 取得レコード
     */
    function selectKeyView($makercd,$seriescd)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Series#selectKey";
       
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($makercd,$seriescd);

        $sql  = "SELECT makercd, makernm, seriescd, seriesnm, seriesnmkana, recommenddiv, updcnt ";
        $sql .= "  FROM v_series_list ";
        $sql .= " WHERE ";
        $sql .= "   makercd = ?";
        $sql .= "   AND seriescd = ?;";

        $data = $db->getRow($sql, $params);
        $this->dbaccess->doClose($db);

        return $data;

    }
    
    /**
     * view条件取得 おすすめのみ全取得
     * 商品が登録されているもののみ
     * 
     * @param string $code コード
     * @return object 取得レコード
     */
    function ViewRecommend($rec, $shopdiv=null, $sort="seriesnmkana")
    {

        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Series#selectKey";
       
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        //$params = array($rec);

        $sql  = "SELECT makercd, makernm, seriescd, seriesnm, seriesnmkana, recommenddiv ";
        $sql .= "  FROM v_series_list ";
        $sql .= " WHERE ";
        $sql .= "   recommenddiv = ?";
        $params[] = $rec;
        $sql .= "   AND item_count > 0";
        if (!empty($shopdiv)) {
            $sql .=  " AND shopdiv = ?";
            $params[] = $shopdiv;
        }
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);
        
        $data = $db->getAll($sql, $params);
        $this->dbaccess->doClose($db);

        
        return $data;

    }
    
    
    /**
     * view メーカーcdを重複なしで取得
     * 商品が登録されているもののみ
     * 
     * @param string $code コード
     * @return object 取得レコード
     */
    function NonRecMakercd($nonrec, $shopdiv=null, $sort="makernmkana")
    {

        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Series#selectKey";
       
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($nonrec);

        $sql  = "SELECT DISTINCT makercd, makernm, makernmkana";
        $sql .= "  FROM v_series_list";
        $sql .= " WHERE ";
        $sql .= "   recommenddiv = ?";
        $sql .= "   AND item_count > 0";
        if (!empty($shopdiv)) {
            $sql .=  " AND shopdiv = ?";
            $params[] = $shopdiv;
        }
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);

        $data = $db->getAll($sql, $params);
        $this->dbaccess->doClose($db);

        return $data;

    }
    
    /**
     * view メーカーcdごとの シリーズ取得
     * 商品が登録されているもののみ
     * 
     * @param string $code コード
     * @return object 取得レコード
     */
    function NonRecSeries($mkcd, $nonrec, $sort="makernmkana")
    {

        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Series#NonRecSeries";
       
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($mkcd,$nonrec);
        

        $sql  = "SELECT makercd, seriescd, seriesnm";
        $sql .= "  FROM v_series_list";
        $sql .= " WHERE ";
        $sql .= "   makercd = ?";
        $sql .= "   AND recommenddiv = ?";
        $sql .= "   AND item_count > 0";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql,$sort);

        $data = $db->getAll($sql, $params);
        $this->dbaccess->doClose($db);

        return $data;

    }
    
    
    /**
     * メーカーcdからシリーズ取得（ビューを参照）
     * 
     * @param int $makercd メーカーコード
     * @return object 取得レコード
     */
    function viewSelectFromMakercd($makercd){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Series#viewSelectFromMakercd";
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        $params = array($makercd);
        
        $sql  = "   SELECT seriescd, seriesnm ";
        $sql .= "     FROM v_series_list ";
        $sql .= "    WHERE makercd = ?  ";
        $sql .= " ORDER BY seriescd ; ";
        
        $data = $db->getAll($sql, $params);
        if (DB::isError($data)) {
            $log->debug($data->userinfo, $func_name);
            die($data->getMessage());
        }
        $this->dbaccess->doClose($db);
        
        return $data;
    }
    
    function updateSeriesOfItem($itemcd, $seriescd, $upduser) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Series#updateSeriesOfItem";

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

        $db = $this->dbaccess->doConnect();
        $db->autoCommit();
        $params = array($seriescd, $upduser, $_SERVER['REMOTE_ADDR'], $itemcd);

        $sql  = "UPDATE t_item_basic_attr SET seriescd = ?, updusrnm = ?, updpcnm = ? WHERE itemcd = ? ";

        $res = $db->getAll($sql, $params);
        if (DB::isError($res)) {
            $log->error("rollback". $res->userinfo, $func_name);
            $db->rollback();
            $this->dbaccess->doClose($db);
            return false;
        }
        $log->debug("commit", $func_name);

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