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


/**
 * カート関連
 *
 * @package     Onedepo.Db.Cart
 * @author      FUCO H.Tominaga
 * @access      public
 */
class Onedepo_Db_Tag
{
    /**
     * DBアクセスオブジェクト
     *
     * @var     object
     * @access  private
     */
    var $dbaccess;

    /**
     * 全件取得
     * 
     * @param string $sort ソートキー
     * @return object 取得レコード
     */
    function selectAll($usercd)
    {
        $sort = "CreateDate";
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($usercd);

        $sql  = "SELECT *";
        $sql .= " FROM t_review t_r, t_item_basic_attr t_i";
        $sql .= " t_r.UserCd = %s";
        $sql .= " t_r.ItemCd = t_i.ItemCd";
        $sql .= " WHERE delflg = FALSE ";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);

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

        return $data;
    }

    function selectOne($usercd, $ReviewCd)
    {
        $sort = "CreateDate";
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $array = $this -> sellectAll($usercd);

        $params = array($ReviewCd);
        $sql  = "SELECT *";
        $sql .= " FROM t_review t_r, t_item_basic_attr t_i";
        $sql .= " t_r.ReviewCd = %s";
        $sql .= " t_r.ItemCd = t_i.ItemCd";
        $sql .= " WHERE delflg = FALSE ";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);

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

        return $data;
    }
    // 各商品の1ユーザータグ取得
    function getMyTag($usercd,$itemcd){

        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($itemcd,$usercd);
        $sql  = "SELECT *";
        $sql .= " FROM t_tag t";
        $sql .= "    INNER JOIN t_member_basic_attr m";
        $sql .= "     ON t.usercd = m.membercd AND m.memberstate = 1 ";
        $sql .= " WHERE t.itemcd = ?";
        $sql .= " AND t.usercd = ?";
        $sql .= " AND t.delflg = FALSE";
//        $sql = sprintf($sql,$itemcd,$usercd);

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

    // 商品の最近登録されたタグ取得
    function getItemTag($itemcd){

        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($itemcd);
        $sql  = "SELECT tagnm";
        $sql .= " FROM v_item_tag";
        $sql .= " WHERE itemcd = ?";
        $sql .= " AND createdate >= CURRENT_TIMESTAMP+'-1 month'";   // 1ヶ月前
        $sql .= " AND referdate >= CURRENT_TIMESTAMP+'-1 month'";          // 1ヶ月前
        $sql .= " ORDER BY tagcount desc";
        $sql .= " LIMIT " . ITEM_TAG_LIMIT;

        $data = $db->getAll($sql, $params);
        $this->dbaccess->doClose($db);
        
        return $data;
    }
    
    
    function getPopularTag($shopdiv=null, $itemcd=""){
        $params = array();

        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT tagnm, sum(tagcount) as tagcount";
        $sql .= " FROM v_item_tag";
        $sql .= " WHERE createdate >= CURRENT_TIMESTAMP+'-1 month'";   // 1ヶ月前
        $sql .= " AND referdate >= CURRENT_TIMESTAMP+'-1 month'";          // 1ヶ月前
        if (!empty($shopdiv)) {
            $sql .= " AND shopdiv = ?";
            $params[] = $shopdiv;
        }
        if ($itemcd != "") {
            $sql .= " AND itemcd = ?";
            $params[] = $itemcd;
        }
        $sql .= " GROUP BY tagnm";
        $sql .= " ORDER BY tagcount desc";
        $sql .= " LIMIT " . POPULAR_TAG_LIMIT;

        $data = $db->getAll($sql, $params);
        $this->dbaccess->doClose($db);
        
        return $data;
    }
    
    
    // ユーザが登録したタグすべて取得
    function getMyAllTag($usercd){

        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($usercd);
        $sql  = "SELECT distinct t.tagnm, i.itemnm, i.itemcd ";
        $sql .= " FROM t_tag t, t_item_basic_attr i, t_member_basic_attr m ";
        $sql .= " WHERE t.usercd = ?";
        $sql .= " AND   i.itemcd = t.itemcd";
        $sql .= " AND   t.delflg = FALSE";
        $sql .= " AND   i.delflg = FALSE";
        $sql .= " AND   t.usercd = m.membercd";
        $sql .= " AND   m.memberstate = 1 ";

//        $sql = sprintf($sql,$usercd);

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

        return $data;
    }
    
    // ユーザが登録したタグすべて取得(タグのみ)
    function getMyAllTagnm($usercd){

        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($usercd);
        $sql  = "SELECT distinct t.tagnm ";
        $sql .= " FROM t_tag t, t_member_basic_attr m ";
        $sql .= " WHERE t.usercd = ?";
        $sql .= " AND   t.delflg = FALSE";
        $sql .= " AND   t.usercd = m.membercd";
        $sql .= " AND   m.memberstate = 1 ";
        $sql .= " ORDER BY t.tagnm";

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

        return $data;
    }
    
    // 商品の他のユーザが登録したタグ取得
    function getAnyTag($usercd,$itemcd){
        
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($itemcd,$usercd);
        $sql  = "SELECT distinct t.tagnm";
        $sql .= " FROM t_tag t, t_member_basic_attr m ";
        $sql .= " WHERE t.itemcd = ?";
        $sql .= " AND t.usercd <> ?";
        $sql .= " AND t.delflg = FALSE";
        $sql .= " AND   t.usercd = m.membercd";
        $sql .= " AND   m.memberstate = 1 ";
        $sql .= " ORDER BY t.tagnm";

        $data = $db->getAll($sql, $params);
        $this->dbaccess->doClose($db);
        
        return $data;
    }
    
    // タグ再登録
    function renewMyTag($itemcd,$usercd,$tag) {
        
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        
        $db->autoCommit();
        
        //タグ一旦削除
        $params = array($itemcd,$usercd);
        
        $sql  = "DELETE FROM t_tag WHERE itemcd = ? AND usercd = ?";
        
        $res = $db->query($sql,$params);
        if (DB::isError($res) || $res < 0) {
            $log->error("rollback". $res->userinfo, "Onedepo_Db_Tag#renewMyTag");
            $db->rollback();
            $this->dbaccess->doClose($db);
            return false;
        }
        
        //新たにタグ挿入
        foreach($tag as $tagnm) {
            //空白なら追加しない
            $tagnm = trim($tagnm);
            if(empty($tagnm)) {
                continue;
            }
            $params = array($itemcd,$usercd,$tagnm,'',$_SERVER["REMOTE_ADDR"]);
            
            $sql = "SELECT * FROM f_tag_update(1,NULL,?,?,?,?,?,NULL)";
            
            /*
            $sql = "INSERT INTO t_tag(";
            $sql .= " itemcd,";
            $sql .= " usercd,";
            $sql .= " tagnm,";
            $sql .= " createdate,";
            $sql .= " referdate,";
            $sql .= " delflg";
            $sql .= ")";
            $sql .= " VALUES(";
            $sql .= " ?,";
            $sql .= " ?,";
            $sql .= " ?,";
            $sql .= " CURRENT_TIMESTAMP,";
            $sql .= " CURRENT_TIMESTAMP,";
            $sql .= " 'FALSE'";
            $sql .= ")";
            */
            
            $res = $db->getRow($sql,$params);
            if (DB::isError($res) || $res < 0) {
                $log->error("rollback". $res->userinfo, "Onedepo_Db_Tag#renewMyTag");
                $db->rollback();
                $this->dbaccess->doClose($db);
                return false;
            }
        }
        
        $db->commit();
        $this->dbaccess->doClose($db);
        
        return true;
    }
    
    function deleteItemTag($usercd,$itemcd)
    {
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();

        $db->autoCommit();$params = array($itemcd,$usercd);
        $sql  = "DELETE";
        $sql .= " FROM t_tag";
        $sql .= " WHERE itemcd = ?";
        $sql .= " AND usercd = ?";

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

        return true;
    }
    
    function getOneItem($cd, $where="")
    {
        $log =& LogFactory::getLog();

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


        $sql  = "SELECT * FROM t_item_basic_attr WHERE itemcd = ?";
        $sql .= $where;         // リプレースメントを使用する場合は変更すること。
        $params = array($cd);

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

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

        return $data;
    }

    function getItemcdForTagnm($tag=""){
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($tag);
        $sql  = "SELECT distinct t.itemcd";
        $sql .= " FROM t_tag t, t_member_basic_attr m ";
        $sql .= " WHERE t.tagnm = ?";
        $sql .= " AND   t.usercd = m.membercd";
        $sql .= " AND   m.memberstate = 1 ";

        $data = $db->getCol($sql, 0, $params);
        $this->dbaccess->doClose($db);
        
        return $data;
    }

    function getItemcdForTag($where, $params) {
      
        $ret = true;
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        
        $sql = "SELECT DISTINCT t.itemcd 
                FROM t_tag t, t_member_basic_attr m 
                WHERE t.delflg=FALSE
                  AND t.usercd = m.membercd
                  AND m.memberstate = 1 ";
        foreach ($where as $val) {
            $sql .= " AND $val";
        }

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

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


}
?>