<?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_Usermanage_Itemattr
{
    /**
     * DBアクセスオブジェクト
     *
     * @var     object
     * @access  private
     */
    var $dbaccess;

    /**
     * 全件取得
     * 
     * @param string $sort ソートキー
     * @return object 取得レコード
     */

    function getRegistTagNumber($keyword)
    {
        $sqlcondition = "";
        if(!empty($keyword)){
            $sqlcondition .= "AND tag.tagnm LIKE '%". $keyword ."%' ";
        }

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

        $sql  = "SELECT count(tag.tagcd) ";
        $sql .= "FROM t_tag tag, t_member_basic_attr member ";
        $sql .= "WHERE tag.delflg = FALSE ";
        $sql .= "AND   member.membercd = tag.usercd ";
        $sql .= "AND   member.delflg = FALSE ";
        $sql .= "AND   member.memberstate = 1 ";
        $sql .= $sqlcondition;
        $log->debug($sql, "Onedepo_Db_Usermanage_Itemattr#getRegistTagNumber");

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

        return $data;
    }

    function getRegistReviewNumber($keyword)
    {
        $sqlcondition = "";
        if(!empty($keyword)){
            $sqlcondition .= "AND review.comment LIKE '%". $keyword ."%' ";
        }

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

        $sql  = "SELECT count(review.reviewcd) ";
        $sql .= "FROM t_review review, t_member_basic_attr member ";
        $sql .= "WHERE review.delflg = FALSE ";
        $sql .= "AND   member.delflg = FALSE ";
        $sql .= "AND   member.membercd = review.usercd ";
        $sql .= "AND   member.memberstate = 1 ";
        $sql .= $sqlcondition;
        $log->debug($sql, "Onedepo_Db_Usermanage_Itemattr#getRegistReviewNumber");

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

        return $data;
    }

    function selectAllTag($key, $keyword, $to , $from)
    {
        switch($key) {
            case "tag":
                $sort = "tagcd";
                break;
            case "date":
            	$sort = "adddate";
            	break;
            case "name":
            	$sort = "tagnm";
            	break;
            case "item":
            	$sort = "itemcd";
            	break;
            case "user":
            	$sort = "usercd";
            	break;
            default:
            	$sort = "tagcd";
        }

        $sqlcondition = "";
        if(!empty($keyword)){
            $sqlcondition .= "AND tag.tagnm LIKE '%". $keyword ."%' ";
        }
        
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT tag.tagcd, tag.adddate, tag.tagnm, tag.itemcd, tag.usercd ";
        $sql .= "FROM t_tag tag, t_member_basic_attr member ";
        $sql .= "WHERE tag.delflg = FALSE ";
        $sql .= "AND   member.membercd = tag.usercd ";
        $sql .= "AND   member.delflg = FALSE ";
        $sql .= "AND   member.memberstate = 1 ";
        $sql .= $sqlcondition;
        $sql .= "ORDER BY " . $sort . " ";
        $sql .= "LIMIT ". $to ." OFFSET ". $from . ";";
        $log->debug($sql, "Onedepo_Db_Usermanage_Itemattr#selectAllTag");

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

        return $data;
    }
    
    function selectAllReview($key, $keyword, $to , $from)
    {
        switch($key) {
            case "review":
                $sort = "reviewcd";
                break;
            case "date":
            	$sort = "adddate";
            	break;
            case "rateup":
            	$sort = "rankingrate";
            	break;
            case "ratedown":
            	$sort = "rankingrate DESC";
            	break;
            case "item":
            	$sort = "itemcd";
            	break;
            case "user":
            	$sort = "usercd";
            	break;
            default:
            	$sort = "reviewcd";
        }
        
        $sqlcondition = "";
        if(!empty($keyword)){
            $sqlcondition .= "AND review.comment LIKE '%". $keyword ."%' ";
        }
        
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT review.reviewcd, review.adddate, review.comment, ";
        $sql .= "review.rankingrate, review.itemcd, review.usercd ";
        $sql .= "FROM t_review review, t_member_basic_attr member ";
        $sql .= "WHERE review.delflg = FALSE ";
        $sql .= "AND   member.delflg = FALSE ";
        $sql .= "AND   member.membercd = review.usercd ";
        $sql .= "AND   member.memberstate = 1 ";

        $sql .= $sqlcondition;
        $sql .= "ORDER BY " . $sort . " ";
        $sql .= "LIMIT ". $to ." OFFSET ". $from . ";";
        $log->debug($sql, "Onedepo_Db_Usermanage_Itemattr#selectAllReview");

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

        return $data;
    }

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

        $key  = $itemcd;
        $sql  = "SELECT itemnm ";
        $sql .= "FROM t_item_basic_attr ";
        $sql .= "WHERE delflg = FALSE ";
        $sql .=	"AND itemcd = %s;";
        $sql = sprintf($sql, $key);
        $log->debug($sql, "Onedepo_Db_Usermanage_Itemattr#getItemName");

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

        return $data;
    }
    
    function getUserName($usercd)
    {
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $key  = $usercd;
        $sql  = "SELECT lastnm, firstnm ";
        $sql .= "FROM t_member_basic_attr ";
        $sql .= "WHERE delflg = FALSE ";
        $sql .=	"AND membercd = %s;";
        $sql = sprintf($sql, $key);
        $log->debug($sql, "Onedepo_Db_Usermanage_Itemattr#getUserName");

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

        return $data;
    }
    
    function updateItemAttr($code, $list)
    {
        $ret = true;
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        $params = array($code);
        
        if($list == "t_tag") {
            $sql = "SELECT * FROM f_tag_update(3,?,NULL,NULL,NULL,NULL,NULL,NULL)";
            $res = $db->getRow($sql ,$params);
        } else {
            $res = $db->query("update t_review " .
        		          	  "set delflg=TRUE " .
        		          	  "where reviewcd=?;" ,$params);
        }
        
        if (DB::isError($res) || !$res) {
            $log->trace("update rollback", "Onedepo_Db_Usermanage_Itemattr#update");

            $db->rollback();
            $ret = false;
        }
        $log->trace("commit", "Onedepo_Db_Usermanage_Itemattr#update");
		$db->commit();
        $this->dbaccess->doClose($db);
        return $ret;
    }
    
    function getTagInfo($tagcd)
    {
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $param= array($tagcd);
        $sql  = "SELECT tag.tagcd, tag.tagnm, tag.createdate, ";
        $sql .= "item.itemnm, ";
        $sql .= "member.firstnm, member.lastnm ";
        $sql .= "FROM  t_tag tag, t_item_basic_attr item, t_member_basic_attr member ";
        $sql .= "WHERE tag.delflg = FALSE ";
        $sql .= "AND   item.delflg = FALSE ";
        $sql .= "AND   member.delflg = FALSE ";
        $sql .= "AND   tag.itemcd = item.itemcd ";
        $sql .= "AND   tag.usercd = member.membercd ";
        $sql .=	"AND   tag.tagcd = ?;";

        $log->debug($sql, "Onedepo_Db_Usermanage_Itemattr#getTagInfo");

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

        return $data;
    }
    
    function getReviewInfo($reviewcd)
    {
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $param= array($reviewcd);
        $sql  = "SELECT review.reviewcd, review.rankingrate, review.createdate, ";
        $sql .= "review.comment, ";
        $sql .= "item.itemnm, ";
        $sql .= "member.firstnm, member.lastnm ";
        $sql .= "FROM  t_review review, t_item_basic_attr item, t_member_basic_attr member ";
        $sql .= "WHERE review.delflg = FALSE ";
        $sql .= "AND   item.delflg = FALSE ";
        $sql .= "AND   member.delflg = FALSE ";
        $sql .= "AND   review.itemcd = item.itemcd ";
        $sql .= "AND   review.usercd = member.membercd ";
        $sql .=	"AND   review.reviewcd = ?;";

        $log->debug($sql, "Onedepo_Db_Usermanage_Itemattr#getReviewInfo");

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

        return $data;
    }

}
?>
