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


/**
 * ジャンルマスタテーブル関連
 *
 * @package     Onedepo.Db.Genre
 * @author      netnee T.Otogawa<tomokazu.otogawa@netnee.jp>
 * @access      public
 */
class Onedepo_Db_Genre
{
    /**
     * DBアクセスオブジェクト
     *
     * @var     object
     * @access  private
     */
    var $dbaccess = null;

    /**
     * 親ジャンルになるジャンルのみ取得
     * @return object 取得レコード
     */
    function selectParentAll()
    {
        $log =& LogFactory::getLog();

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

        $params = array();

        $sql  = "SELECT parentcd, genrecd, genrenm, disppriority FROM t_genre ";
        $sql .= " WHERE delflg = FALSE ";
        if (GENRE_MAX_NEST <= 2) $sql .= " AND parentcd IS NULL";
        $sql .= " ORDER BY COALESCE(parentcd,0), disppriority";

        $data = $db->getAssoc($sql, FALSE, $params, DB_FETCHMODE_ASSOC, true);
        $this->dbaccess->doClose($db);

        $c = "";
        $g = array();
        $this->getGenreNest($data, $c, 2, $g);

        return $g;
    }
    function getGenreNest($data, $c, $nest, &$g) {
        $con = str_repeat("　", $nest-2);
        if ($nest-2 > 0) $con .= "|_";

        $d = $data[$c];
        for ($i = 0; $i < count($d); $i++) {
            $g[] = array("genrecd"=>$d[$i]["genrecd"],
                         "genrenm"=>$con . $d[$i]["genrenm"]);

            if ($nest < GENRE_MAX_NEST) {
                $c = $d[$i]["genrecd"];
                $this->getGenreNest($data, $c, $nest+1, $g);
            }
        }
    }

    /**
     * 親子関係を維持
     * @return object 取得レコード
     */
    function selectGenreRelationship()
    {
        $log =& LogFactory::getLog();

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

        $params = array();

        $sql  = "SELECT g1.parentcd, g1.genrecd, g1.genrenm, g1.disppriority, count(g2.itemcd) as count ";
        $sql .= "  FROM t_genre g1 ";
        $sql .= "  LEFT JOIN t_genre_item g2 
                     ON g1.genrecd=g2.genrecd AND g2.delflg = FALSE ";

/**
        $sql .=  " AND (g1.disppriority BETWEEN 1 AND ?) ";
        $params[] = GENRE_DISP_MAX -1;
***/

        $sql .= " WHERE g1.delflg=FALSE ";
        $sql .= " GROUP BY g1.parentcd, g1.genrecd, g1.genrenm, g1.disppriority ";
        $sql .= " ORDER BY COALESCE(g1.parentcd,0), g1.disppriority";




        $log->debug($sql,"");
        $data = $db->getAssoc($sql, FALSE, $params, DB_FETCHMODE_ASSOC, true);
        $this->dbaccess->doClose($db);

        return $data;
    }
    /**
     * ジャンルをツリー構造の配列に（再帰処理）
     * @params  $data  元となる配列 各配列の添字には、親ジャンルのコード
     *          $c     コード
     *          $tree  ツリー構造の配列
     *          $del   true の時、関連するジャンルに商品が登録されていなければ、今いる場所より下の構造は破棄
     *          $cnt   商品のカウント（$del に関連）
     **/
    function makeTree($data, $c, &$tree, $del, &$cnt=0) {
        $d = (isset($data[$c])) ? $data[$c] : NULL;
        $top = false;
        if (empty($c)) $top = true;
        for ($i = 0; $i < count($d); $i++) {
            if ($top) $cnt = 0;

            $tree[$i] = array("genrecd"=>$d[$i]["genrecd"],
                              "genrenm"=>$d[$i]["genrenm"],
                              "child"=>"");

            $c = $d[$i]["genrecd"];
            $cnt += $d[$i]["count"];
            $this->makeTree($data, $c, $tree[$i]["child"], $del, $cnt);

            // $del === true の時
            // 商品が１件もないジャンルを階層ごと削除
            if ($del===true && intval($cnt) <= 0) {
                unset($tree[$i]);
            }
        }
    }
    /**
     * 指定したジャンルのみ
     * @params  $data    元となる配列 各配列の添字には、親ジャンルのコード
     *          $cd      指定したジャンル
     *          $newdata 変更後の配列
     *          $del     初期値true
     *          $top     true:親ジャンル false:子ジャンル
     **/
    function selectGenreOnly($olddata, $cd, &$newdata, &$del=true, $top) {
        $log =& LogFactory::getLog();

        if (!empty($olddata)) {

            foreach ($olddata as $key => $val) {
                if (in_array($val["genrecd"], $cd)) $del = false;

                $this->selectGenreOnly($val["child"], $cd, $newdata, $del, false);

                if ($top===true && $del===true) {
                    //$log->error("del cd=" . $newdata[$key]["genrecd"], "");
                    unset($newdata[$key]);
                }
                if ($top===true) $del = true;
            }

        }
    }

    /**
     * 全件取得
     * 
     * @param string $sort ソートキー
     * @return object 取得レコード
     */
    function selectAll($shopdiv=null, $sort="disppriority")
    {
        $log =& LogFactory::getLog();

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

        $params = array();

        $sql  = "SELECT g1.parentcd, g2.genrenm as parentnm, g1.genrecd, g1.genrenm, g1.disppriority ";
        $sql .= "  FROM t_genre g1 LEFT JOIN t_genre g2 ON g1.parentcd=g2.genrecd AND g2.delflg=FALSE ";
        $sql .= " WHERE g1.delflg = FALSE";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);

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

        return $data;
    }

    /**
     * 全件取得
     * 
     * @param string $sort ソートキー
     * @return object 取得レコード
     */
    function selectType($shopdiv=null, $type="", $sort="disppriority")
    {
        $log =& LogFactory::getLog();

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

        $params = array();

        $sql  = "SELECT g1.genrecd, g1.genrenm, g1.disppriority ";
        $sql .= "  FROM t_genre_item g2 ";
        $sql .= "  INNER JOIN t_genre g1
                     ON g1.genrecd=g2.genrecd AND g1.delflg = FALSE";
        if (!empty($shopdiv)) {
            $sql .=  " AND g1.shopdiv = ?";
            $params[] = $shopdiv;
        }
        switch ($type) {
        case 'used':
        $sql .= " INNER JOIN t_item_basic_attr basic
                    ON basic.itemcd=g2.itemcd
                    AND basic.oldlowprice > 0 AND basic.delflg=FALSE
                ";
        break;
        case 'new':
        $sql .= " INNER JOIN t_item_basic_attr basic
                    ON basic.itemcd=g2.itemcd
                    AND basic.newlowprice > 0 AND basic.delflg=FALSE
                ";
        break;
        case 'all':
        $sql .= " INNER JOIN t_item_basic_attr basic
                    ON basic.itemcd=g2.itemcd
                    AND basic.lowprice > 0 AND basic.delflg=FALSE
                ";
        break;
        }
        $sql .= " GROUP BY g1.genrecd, g1.genrenm, g1.disppriority ORDER BY %s";
        $sql = sprintf($sql, $sort);

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

        return $data;
    }
    
    /**
     * 表示が1からGENRE_MAX_DISPのジャンルを返却
     * 
     * @param string $shopdiv 区分
     * @param string $type 新中古
     * @param string $sort ソートキー
     * @param integer $limit 取得件数
     * @return object 取得レコード
     */
    function selectTypeMaxDisp($shopdiv=null, $type="", $sort="disppriority", $limit=0)
    {
        $log =& LogFactory::getLog();

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

        $params = array();

        $sql  = "SELECT g1.genrecd, g1.genrenm, g1.disppriority ";
        $sql .= "  FROM t_genre_item g2 ";
        $sql .= "  INNER JOIN t_genre g1
                     ON g1.genrecd=g2.genrecd AND g1.delflg = FALSE";
        if (!empty($shopdiv)) {
            $sql .=  " AND g1.shopdiv = ? ";
            $params[] = $shopdiv;
        }
        $sql .=  " AND (g1.disppriority BETWEEN 1 AND ?) ";
        $params[] = GENRE_DISP_MAX -1;
        
        switch ($type) {
        case 'used':
        $sql .= " INNER JOIN t_item_basic_attr basic
                    ON basic.itemcd=g2.itemcd
                    AND basic.oldlowprice > 0 AND basic.delflg=FALSE
                ";
        break;
        case 'new':
        $sql .= " INNER JOIN t_item_basic_attr basic
                    ON basic.itemcd=g2.itemcd
                    AND basic.newlowprice > 0 AND basic.delflg=FALSE
                ";
        break;
        case 'all':
        $sql .= " INNER JOIN t_item_basic_attr basic
                    ON basic.itemcd=g2.itemcd
                    AND basic.lowprice > 0 AND basic.delflg=FALSE
                ";
        break;
        }
        $sql .= " GROUP BY g1.genrecd, g1.genrenm, g1.disppriority ORDER BY %s";
        if ($limit>0) $sql .= " LIMIT ". $limit;
        $sql = sprintf($sql, $sort);

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

        return $data;
    }
    
    /**
     * メディアで判別
     * 
     * @param string $shopdiv 区分
     * @param string $media DVD,VHS
     * @param integer $limit 取得件数
     * @return object 取得レコード
     */
    function selectMediaGenre($shopdiv=null, $media="", $limit=0)
    {
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array();

        $sql  = "SELECT g1.genrecd, g1.genrenm, g1.disppriority ";
        $sql .= "  FROM t_genre_item g2 ";
        $sql .= "  INNER JOIN t_genre g1
                     ON g1.genrecd=g2.genrecd AND g1.delflg = FALSE";
        if (!empty($shopdiv)) {
            $sql .=  " AND g1.shopdiv = ? ";
            $params[] = $shopdiv;
        }
        $sql .= " AND (g1.disppriority BETWEEN 1 AND ?) ";
        $params[] = GENRE_DISP_MAX -1;
        $sql .= "
                  INNER JOIN t_item_basic_attr basic
                    ON basic.itemcd=g2.itemcd
                    AND basic.lowprice > 0 AND basic.delflg=FALSE";
        
        /*
        switch (strtolower($media)) {
        case 'dvd':
        $sql .= "
                  INNER JOIN t_item_struct struct
                    ON struct.itemcd=basic.itemcd AND struct.mediacd=1
                    AND struct.delflg=FALSE
                ";
        break;
        case 'vhs':
        $sql .= "
                  INNER JOIN t_item_struct struct
                    ON struct.itemcd=basic.itemcd AND struct.mediacd=2
                    AND struct.delflg=FALSE
                ";
        break;
        }
        */
        $sql .= " GROUP BY g1.genrecd, g1.genrenm, g1.disppriority ORDER BY g1.disppriority";
        if ($limit>0) $sql .= " LIMIT ". $limit;

        $log =& LogFactory::getLog();
        $log->debug($sql, "selectPopularGenre");
        $log->debug(print_r($params,true), "selectPopularGenre");

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

        return $data;
    }
    
    /**
     * 条件取得
     * 
     * @param string $code コード
     * @return object 取得レコード
     */
    function selectKey($shopdiv=null, $code)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Genre#selectKey";

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

        $params = array($code);
        
        $sql  = "SELECT genrecd, genrenm, parentcd, disppriority, updcnt ";
        $sql .= "  FROM t_genre ";
        $sql .= " WHERE delflg = FALSE ";
        $sql .= "   AND genrecd = ?";
        if (!empty($shopdiv)) {
            $sql .=  " AND shopdiv = ?";
            $params[] = $shopdiv;
        }

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

        return $data;
    }

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

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

        $params = NULL;

        $sql  = "SELECT g1.parentcd, g2.genrenm as parentnm, g1.genrecd, g1.genrenm, g1.disppriority ";
        $sql .= "  FROM t_genre g1 LEFT JOIN t_genre g2 ON g1.parentcd=g2.genrecd AND g2.delflg=FALSE ";
        $sql .= " WHERE g1.delflg = FALSE";

        if (!empty($word)) {
            $sql .= "   AND (g1.genrenm LIKE ? OR g2.genrenm LIKE ?)";
            $params[] = "%". trim($word) . "%";
            $params[] = "%". trim($word) . "%";
        }

        switch ($v) {
            case 'parent':
            $sql .= ' AND g1.parentcd IS NULL ';
            $sort = 'g1.parentcd, g1.disppriority';
            break;

            case 'child':
            $sql .= ' AND g1.parentcd IS NOT NULL ';
            $sort = 'g1.parentcd, g1.disppriority';
            break;
        }

        $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 integer $dispsort 表示順
     * @param string  $upduser 更新ユーザ
     * @return boolean 結果ステータス
     */
    function insert($name, $dispsort, $parentcd=NULL, $upduser) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Genre#insert";

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

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

        $sql  = "select f_genre_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 integer $dispsort 表示順
     * @param integer $updcnt 更新カウンタ
     * @param string  $upduser 更新ユーザ
     * @return boolean 結果ステータス
     */
    function update($code, $name, $dispsort, $parentcd=NULL, $updcnt, $upduser) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Genre#update";

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

        $db = $this->dbaccess->doConnect();
        $db->autoCommit();
        $params = array($code, $parentcd, $name, $dispsort, $upduser, $_SERVER['REMOTE_ADDR'], $updcnt);

        $sql  = "select f_genre_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($code, $parentcd=NULL, $updcnt, $upduser) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Genre#delete";

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

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

        $sql  = "SELECT f_genre_update(3, ?, ?, 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;
    }

    function getGenrenmForItemcd($itemcd) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $sql = "SELECT itemcd, genrenm 
                FROM t_genre tgenre, t_genre_item genreitem 
                WHERE tgenre.genrecd = genreitem.genrecd AND genreitem.itemcd in (!) AND tgenre.delflg=FALSE 
                ORDER BY disppriority";
        $params = array($itemcd);

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

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

    function getGenreForItemcd($itemcd) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $sql = "SELECT itemcd, genreitem.genrecd, genrenm 
                FROM t_genre tgenre, t_genre_item genreitem 
                WHERE tgenre.genrecd = genreitem.genrecd AND genreitem.itemcd in (!) AND tgenre.delflg=FALSE 
                ORDER BY disppriority";
        $params = array($itemcd);

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

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

    function getGenrecdForItemcd($itemcd) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $sql = "SELECT genrecd 
                FROM t_genre_item genreitem 
                WHERE genreitem.itemcd in (?)";
        $params = array($itemcd);

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

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

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

        $db = $this->dbaccess->doConnect();
        
        $sql = "SELECT DISTINCT itemcd 
                FROM t_genre_item 
                WHERE ";
        foreach ($where as $val) {
            $sql .= $val;
        }

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

        $this->dbaccess->doClose($db);
        return $data;
    }
    
    function getItemcdForGenrenm($where, $params) {
      
        $ret = true;
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        
        $sql = "SELECT itemcd 
                FROM t_genre_item genreitem, t_genre tgenre
                WHERE tgenre.delflg=FALSE AND tgenre.genrecd=genreitem.genrecd ";
        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;
    }
        
    function getGenrenmList($genrecd) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $sql = "SELECT genrenm 
                FROM t_genre
                WHERE genrecd in (!)";
        $params = array(implode(",", $genrecd));

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

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