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


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


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

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

        $params = array($sort);

        $sql  = "SELECT castcd, castnm, castnmkana, castdiv, recommenddiv, comment, debutdate, birthplacecd, birthdate ";
        $sql .= "  FROM t_cast ";
        $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($code)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#selectKey";

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

        $params = array($code);

        $sql  = "SELECT castcd, castnm, castnmkana, castdiv, recommenddiv, comment, debutdate, birthplacecd, birthdate, updcnt ";
        $sql .= "  FROM t_cast ";
        $sql .= " WHERE delflg = FALSE ";
        $sql .= "   AND castcd = ?;";

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

        return $data;
    }

    /**
     * サーチ検索
     *
     * @param string  $word 検索ワード
     * @param integer $castdiv キャスト種別
     * @param integer $recommenddiv おすすめ区分
     * @param string  $sort ソートキー
     * @return object 取得レコード
     */
    function selectSearch($word, $castdiv, $recommenddiv, $shopdiv=null, $sort="castnmkana")
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#selectSearch";

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

        $params = NULL;

        $sql  = "SELECT castcd, castnm, castnmkana, castdiv, recommenddiv, comment, debutdate, birthplacecd, birthdate, updcnt ";
        $sql .= "  FROM t_cast ";
        $sql .= " WHERE delflg = FALSE ";

        // 検索ワード
        if (!empty($word)) {
            $sql .= "   AND (castnm LIKE ? ";
            $sql .= "    OR  castnmkana LIKE ?";
            $sql .= "    OR  comment LIKE ?)";
            $params[] = "%". trim($word) . "%";
            $params[] = "%". trim($word) . "%";
            $params[] = "%". trim($word) . "%";
        }

        // キャスト種別
        if (!empty($castdiv)) {
            $sql .= "   AND castdiv = ? ";
            $params[] = $castdiv;
        }

        // おすすめ区分
        if (!empty($recommenddiv)) {
            $sql .= "   AND recommenddiv = ? ";
            $params[] = $recommenddiv;
        }
        
        // 区分
        if (!empty($shopdiv)) {
            $sql .=  " AND shopdiv = ?";
            $params[] = $shopdiv;
        }
        $sql .= " AND (SELECT count(item.castcd) FROM t_cast_item item WHERE item.castcd = t_cast.castcd AND item.delflg = FALSE) > 0 ";
        $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 integer $shopdiv 区分
     * @param string  $name 名称
     * @param string  $kana 名称かな
     * @param integer $castdiv キャスト種別
     * @param integer $recommenddiv おすすめ区分
     * @param string  $comment コメント
     * @param string  $debutdate デビュー年月
     * @param integer $birthplace 出身
     * @param string  $birthdate 生年月日
     * @param string  $upduser 更新ユーザ
     * @return boolean 結果ステータス
     */
    function insert($shopdiv=null, $name, $kana, $castdiv, $recommenddiv, $comment, $debutdate, $birthplace, $birthdate, $upduser) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#insert";

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

        $db = $this->dbaccess->doConnect();
        $db->autoCommit();
        $params = array($shopdiv, $name, $kana, $castdiv, $recommenddiv, $comment, $debutdate, $birthplace, $birthdate, $upduser, $_SERVER['REMOTE_ADDR']);

        $sql  = "select f_cast_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 integer $shopdiv 区分
     * @param string  $code コード
     * @param string  $name 名称
     * @param string  $kana 名称かな
     * @param integer $castdiv キャスト種別
     * @param integer $recommenddiv おすすめ区分
     * @param string  $comment コメント
     * @param string  $debutdate デビュー年月
     * @param integer $birthplace 出身
     * @param string  $birthdate 生年月日
     * @param integer $updcnt 更新カウンタ
     * @param string  $upduser 更新ユーザ
     * @return boolean 結果ステータス
     */
    function update($shopdiv=null, $code, $name, $kana, $castdiv, $recommenddiv, $comment, $debutdate, $birthplace, $birthdate, $updcnt, $upduser) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#update";

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

        $db = $this->dbaccess->doConnect();
        $db->autoCommit();
        $params = array($code, $shopdiv, $name, $kana, $castdiv, $recommenddiv, $comment, $debutdate, $birthplace, $birthdate, $upduser, $_SERVER['REMOTE_ADDR'], $updcnt);

        $sql  = "select f_cast_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 integer $shopdiv 区分
     * @param string  $code コード
     * @param integer $updcnt 更新カウンタ
     * @param string  $upduser 更新ユーザ
     * @return boolean 結果ステータス
     */
    function delete($shopdiv=null, $code, $updcnt, $upduser) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#delete";

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

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

        $sql  = "select f_cast_update(3, ?, ?, NULL, NULL, NULL, NULL, NULL, 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;
    }

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

        $db = $this->dbaccess->doConnect();
        
        $sql = "SELECT itemcd 
                FROM t_cast tcast, t_cast_item castitem 
                WHERE tcast.delflg = FALSE AND tcast.castcd = castitem.castcd AND castitem.delflg = FALSE ";
        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 getItemcdForCastWomen($where, $params) {
        $ret = true;
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        
        $sql = "SELECT itemcd 
                FROM t_cast tcast, t_cast_item castitem 
                WHERE tcast.delflg = FALSE AND castitem.delflg = FALSE AND
                      tcast.castcd = castitem.castcd AND
                      tcast.castdiv=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;
    }

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

        $db = $this->dbaccess->doConnect();
        
        $sql = "SELECT itemcd 
                FROM t_cast tcast, t_cast_item castitem 
                WHERE tcast.delflg = FALSE AND castitem.delflg = FALSE AND
                      tcast.castcd = castitem.castcd AND
                      tcast.castdiv<>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;
    }

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

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

        $sql  = " SELECT itemcd, castnm ";
        $sql .= "   FROM t_cast tcast, t_cast_item castitem ";
        $sql .= "  WHERE tcast.castcd = castitem.castcd ";
        $sql .= "    AND castitem.delflg = false ";
        $sql .= "    AND castitem.itemcd in (!)";
        $sql .= "  ORDER BY castitem.disppriority, tcast.castnm";
        $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 getCastListForItemcd($itemcd) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $sql  = " SELECT itemcd, castitem.castcd, castnm ";
        $sql .= "   FROM t_cast tcast, t_cast_item castitem ";
        $sql .= "  WHERE tcast.castcd = castitem.castcd ";
        $sql .= "    AND castitem.delflg = false ";
        $sql .= "    AND castitem.itemcd in (!)";
        $sql .= "  ORDER BY castdiv";
        $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 getCastListWomen($itemcd) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $sql  = " SELECT itemcd, castitem.castcd, castnm ";
        $sql .= "   FROM t_cast tcast, t_cast_item castitem ";
        $sql .= "  WHERE tcast.castcd = castitem.castcd ";
        $sql .= "    AND castitem.delflg = false  and tcast.castdiv=1 and tcast.delflg=false";
        $sql .= "    AND castitem.itemcd in (!)";
        $sql .= "  ORDER BY castdiv";
        $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 getCastWomen($itemcd) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $sql  = " SELECT itemcd, castitem.castcd, castnm ";
        $sql .= "   FROM t_cast tcast, t_cast_item castitem ";
        $sql .= "  WHERE tcast.castcd = castitem.castcd ";
        $sql .= "    AND castitem.delflg = false ";
        $sql .= "    AND castitem.itemcd=? and tcast.castdiv=1 and tcast.delflg=false";
        $sql .= "  ORDER BY castitem.disppriority, tcast.castnm";
        $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 getCastDirec($itemcd) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $sql  = " SELECT itemcd, castitem.castcd, castnm ";
        $sql .= "   FROM t_cast tcast, t_cast_item castitem ";
        $sql .= "  WHERE tcast.castcd = castitem.castcd ";
        $sql .= "    AND castitem.delflg = false ";
        $sql .= "    AND castitem.itemcd=? and tcast.castdiv=2 and tcast.delflg=false";
        $sql .= "  ORDER BY castdiv";
        $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 getCastForItemcd($itemcd, $shopdiv=null) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $params = array($itemcd);
        $sql  =  "SELECT tcast.castcd, tcast.castnm, tname.name, castitem.disppriority, castitem.updcnt ";
        $sql .=  "  FROM t_cast tcast, t_cast_item castitem, t_name tname ";
        $sql .=  " WHERE tcast.castcd = castitem.castcd ";
        $sql .=  "   AND tcast.castdiv = tname.namecd ";
        $sql .=  "   AND tname.namediv = 6 ";
        $sql .=  "   AND castitem.delflg = false ";
        $sql .=  "   AND castitem.itemcd = ? ";
        if (!empty($shopdiv)) {
            $sql .=  "   AND tcast.shopdiv = ? ";
            $params[] = $shopdiv;
        }
        $sql .= "  ORDER BY castitem.disppriority, tcast.castnm";

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

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


    /*
    * 「かな」にマッチするレコード取得
    *
    * @param  int    $castdiv キャスト種別
    * @param  string $kana    かな
    * @param  string $sort    ソートキー
    * @return object 取得レコード
    */
    function selectKana($castdiv, $kana, $shopdiv=null, $sort="castnmkana")
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#selectKana";
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT castcd, castnm ";
        $sql .= "  FROM t_cast ";
        $sql .= " WHERE delflg = FALSE";
        $sql .= "   AND castdiv = ? ";
        $params = array();
        $params[] = $castdiv;

        if (!empty($shopdiv)) {
            $sql .= "   AND shopdiv = ? ";
            $params[] = $shopdiv;
        }
        $sql .= " AND (SELECT count(item.castcd) FROM t_cast_item item WHERE item.castcd = t_cast.castcd AND item.delflg = FALSE) > 0 ";
        foreach ($kana as $key => $val) {
            if ($key==0) $sql .= " AND (";
            else         $sql .= " OR";
            
            if ($val=='わ') $sql .= " castnmkana \!~* '^[あ-わ]' OR ";
            $sql .= " castnmkana LIKE ?";
            $params[] = "$val%";
        }
        
        $sql .= sprintf(") ORDER BY %s", $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  int    $castdiv キャスト種別
    * @param  string $sort    ソートキー
    * @return object 取得レコード
    */
    function viewSelectNonKana($castdiv, $shopdiv=null, $sort="castnmkana")
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#viewSelectNonKana";
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        $sql  = "SELECT castcd, castnm , castnmkana ";
        $sql .= "  FROM v_cast_list ";
        $sql .= " WHERE castdiv = %d ";
        if (!empty($shopdiv)) {
            $sql .=  " AND shopdiv = ?";
            $params[] = $shopdiv;
        }
        $sql .= "   AND castnmkana \!~* '^[あ-わ]' ";
        $sql .= " AND (SELECT count(item.castcd) FROM t_cast_item item WHERE item.castcd = v_cast_list.castcd AND item.delflg = FALSE) > 0 ";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $castdiv, $sort);
        
//        $params = array($castdiv);
        
        $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  int    $castdiv キャスト種別
    * @param  string $kana    かな
    * @param  string $sort    ソートキー
    * @return object 取得レコード
    */
    function selectKanaAll($castdiv, $kana, $shopdiv=null, $sort="castnmkana")
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#selectKanaAll";
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT castcd, castnm ";
        $sql .= "  FROM t_cast ";
        $sql .= " WHERE delflg = FALSE";
        $sql .= "   AND castdiv = ? ";
        $params = array();
        $params[] = $castdiv;

        if (!empty($shopdiv)) {
            $sql .= "   AND shopdiv = ? ";
            $params[] = $shopdiv;
        }
        foreach ($kana as $key => $val) {
            if ($key==0) $sql .= " AND (";
            else         $sql .= " OR";
            
            if ($val=='わ') $sql .= " castnmkana \!~* '^[あ-わ]' OR ";
            $sql .= " castnmkana LIKE ?";
            $params[] = "$val%";
        }
        
        $sql .= sprintf(") ORDER BY %s", $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  int    $castdiv キャスト種別
    * @param  string $kana かな
    * @param  string $sort ソートキー
    * @return object 取得レコード
    */
    function selectKeyToKana($castdiv, $key, $initial, $shopdiv=null, $sort="castnmkana")
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#selectKeyToKana";
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT castcd, castnm, castnmkana ";
        $sql .= "  FROM t_cast ";
        $sql .= " WHERE delflg = FALSE";
        $sql .= "   AND castcd = ?";
        $params = array ($key);
        $data = $db->getRow($sql, $params);

        $k = '';
        foreach ($initial as $key => $val) {
            if (in_array(mb_substr($data["castnmkana"],0,1,"UTF-8"), $val)) {
                $k = $key;
                break;
            }
        }


        $sql  = "SELECT castcd, castnm ";
        $sql .= "  FROM t_cast ";
        $sql .= " WHERE delflg = FALSE";
        $sql .= "   AND castdiv = ? ";

        $params = array();
        $params[] = $castdiv;

        if (empty($shopdiv)) {
            $sql .= "   AND shopdiv = ? ";
            $params[] = $shopdiv;
        }
        
        foreach ($initial[$k] as $key => $val) {
            if ($key==0) $sql .= " AND (";
            else         $sql .= " OR";
            $sql .= " castnmkana LIKE ?";
            $params[] = "$val%";
        }
        $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;
    }
    


    function getCastRanking($castdiv, $recordlmt=RANKING_RECORD_LIMIT, $shopdiv=null)
    {
        $params = array();
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#getCastRanking";

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        $sql  = "SELECT c.castcd, c.castnm FROM t_ranking r";
        $sql .= " INNER JOIN t_cast c ON c.castcd=r.refercd AND c.delflg=FALSE";
        $sql .= " WHERE r.rankingtype=5 AND r.date=CURRENT_DATE AND r.datadiv=1";
        $sql .= " ORDER BY r.rankingrate";
        $sql .= " LIMIT ? ; ";
        $params[] = $recordlmt;
        
        $data = $db->getAll($sql, $params);
        if (DB::isError($data)) {
            $log->debug("error ".$data->userinfo, $func_name);
            die($data->getMessage());
        }

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

        return $data;
    }


    /**
     * おすすめ全件取得
     * 
     * @param int    $div   キャスト区分
     * @param int    $shopdiv   区分
     * @param string $sort  ソートキー
     * @return object 取得レコード
     */
    function selectAllRecommend($div=1, $shopdiv=null, $sort="castnmkana")
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#selectAllRecommend";

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

        $params = array($div);

        $sql  = "SELECT castcd, castnm, castnmkana, castdiv, recommenddiv, comment, debutdate, birthplacecd, birthdate ";
        $sql .= "  FROM t_cast ";
        $sql .= " WHERE castdiv = ? ";
        if (!empty($shopdiv)) {
            $sql .=  " AND shopdiv = ?";
            $params[] = $shopdiv;
        }
        $sql .= "   AND recommenddiv = 1 ";
        $sql .= "   AND delflg = FALSE ";
        $sql .= " AND (SELECT count(item.castcd) FROM t_cast_item item WHERE item.castcd = t_cast.castcd AND item.delflg = FALSE) > 0 ";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);
        
        $data = $db->getAll($sql, $params);
        $this->dbaccess->doClose($db);

        return $data;
    }


    /**
    * キャストコードからSサイズの画像登録のあるキャストを取得（ビューを参照）
    * 
    * @param string $castcd キャストコード
    */
    function viewSelectImageSHasCast($castcd, $sort="castnmkana"){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#viewSelectImageSHasCast";

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

        $params = array($castcd);

        $sql  = "SELECT castcd, castnm, castnmkana ";
        $sql .= "  FROM v_cast_list ";
        $sql .= " WHERE img_count_s > 0 ";
        $sql .= "   AND item_count > 0 ";
        $sql .= "   AND castcd in (!) ";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);
        
        $data = $db->getAll($sql, $params);
        $this->dbaccess->doClose($db);

        return $data;
    }


    /**
    * キャストコードからSサイズの画像登録のないキャストを取得（ビューを参照）
    * 
    * @param string $castcd キャストコード
    */
    function viewSelectNonImageSCast($castcd, $sort="item_count desc"){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#viewSelectNonImageSCast";

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

        $params = array($castcd);

        $sql  = "SELECT castcd, castnm, castnmkana ";
        $sql .= "  FROM v_cast_list ";
        $sql .= " WHERE img_count_s = 0 ";
        $sql .= "   AND item_count > 0 ";
        $sql .= "   AND castcd in (!) ";
        $sql .= " ORDER BY %s";
        $sql .= " LIMIT 100 ";
        $sql = sprintf($sql, $sort);
        
        $data = $db->getAll($sql, $params);
        $this->dbaccess->doClose($db);

        return $data;
    }

    /**
     * v_cast_listからの全件取得
     * 
     * @param string $sort ソートキー
     * @return object 取得レコード
     */
    function viewselectAll($Pager_perPage , $from, $shopdiv=null , $sort="castnmkana")
    {
        $log =& LogFactory::getLog();

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

        $sql  = "SELECT castcd, castnm, castnmkana, castdiv, castdivnm, recommenddiv, comment, debutdate, birthplacenm, birthdate, shopdiv, img_count_s, img_count_m, img_count_l ";
        $sql .= "  FROM v_cast_list ";
        if (!empty($shopdiv)) {
            $sql .=  " WHERE shopdiv = ?";
            $params[] = $shopdiv;
        }
        $sql .= " ORDER BY %s";
        $sql .=  " LIMIT ". $Pager_perPage  ." OFFSET ". $from;
        $sql = sprintf($sql, $sort);

        $data = $db->getAll($sql, $params);
        
        $countquery = "SELECT count(castcd) as cnt FROM v_cast_list";
        if (!empty($shopdiv)) {
            $countquery .=  " WHERE shopdiv = ?";
        }
        $cnt = $db->getOne($countquery, $params);
        $data += array("recordcount" => $cnt);
        
        $this->dbaccess->doClose($db);

        return $data;
        
    }
    
    
    
    /**
     *  v_cast_listからサーチ検索
     *
     * @param string  $word 検索ワード
     * @param integer $castdiv キャスト種別
     * @param integer $recommenddiv おすすめ区分
     * @param string  $sort ソートキー
     * @return object 取得レコード
     */
    function viewselectSearch($Pager_perPage , $from, $shopdiv=null , $word, $castdiv, $recommenddiv, $sort="castnmkana")
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#selectSearch";

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

        $params = NULL;

        $sql  = "SELECT castcd, castnm, castnmkana, castdiv, castdivnm, recommenddiv, comment, debutdate, birthplacenm, birthdate, img_count_s, img_count_m, img_count_l ";
        $sql .= "  FROM v_cast_list ";
        $sql .= " WHERE ";  
        // 検索ワード
        if (!empty($word)) {
            $sql .= "   (castnm LIKE ? ";
            $sql .= "    OR  castnmkana LIKE ?";
            $sql .= "    OR  comment LIKE ?)";
            $params[] = "%". trim($word) . "%";
            $params[] = "%". trim($word) . "%";
            $params[] = "%". trim($word) . "%";
        }

        // キャスト種別
        if (!empty($word)) {
          if (!empty($castdiv)) {
              $sql .= "   AND castdiv = ? ";
              $params[] = $castdiv;
              }
        }
        else{
              if (!empty($castdiv)) {
              $sql .= "   castdiv = ? ";
              $params[] = $castdiv;
              }
        }
        
        
        // おすすめ区分
        if (!empty($word) or !empty($castdiv)) {
          if (!empty($recommenddiv)) {
              $sql .= "   AND recommenddiv = ? ";
              $params[] = $recommenddiv;
              }
        }
        else{
           if (!empty($recommenddiv)) {
              $sql .= "   recommenddiv = ? ";
              $params[] = $recommenddiv;
           }
        }
        if (!empty($word) or !empty($castdiv) or !empty($recommenddiv)) {
            if (!empty($shopdiv)) {
                $sql .=  " AND shopdiv = ?";
                $params[] = $shopdiv;
            }
        } else {
            if (!empty($shopdiv)) {
                $sql .=  " shopdiv = ?";
                $params[] = $shopdiv;
            }
        }
        
        $sql .= " ORDER BY %s";
        
        $nolimit = $sql;
        
        $sql .= " LIMIT ". $Pager_perPage  ." OFFSET ". $from;
        $sql = sprintf($sql, $sort);
        
        $data = $db->getAll($sql, $params);
        if (DB::isError($data)) {
            $log->debug($data->userinfo, $func_name);
            die($data->getMessage());
        }

        $nolimit = sprintf($nolimit, $sort);
        $nolimitdata = $db->getAll($nolimit, $params);
        $cnt = count($nolimitdata);
        $data += array("recordcount" => $cnt);
        
        
        $this->dbaccess->doClose($db);

        $log->debug("return data : " . print_r($data, true), $func_name);
        return $data;
    }
    
    /**
     * v_cast_listからの条件取得
     * 
     * @param string $code コード
     * @return object 取得レコード
     */
    function viewselectKey($shopdiv=null, $code)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#viewselectKey";

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

        $params = array($code);

        $sql  = "SELECT castcd, castnm, castnmkana, castdiv, castdivnm, recommenddiv, comment, debutdate, birthplacecd, birthplacenm, birthdate, shopdiv, img_count_s, img_count_m, img_count_l, updcnt ";
        $sql .= "  FROM v_cast_list ";
        $sql .= " WHERE castcd = ?";
        if (!empty($shopdiv)) {
            $sql .=  " AND shopdiv = ?";
            $params[] = $shopdiv;
        }

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

        return $data;
    }


    /**
     * キャスト名のユニークチェック
     * 
     * @param string $name キャスト名
     * @param string $code コード
     * @return boolean 
     */
    function uniqueCastnm($shopdiv=null, $name, $castdiv, $code="")
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Cast#uniqueCastnm";

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

        $params = array($name, $castdiv);

        $sql  = "SELECT count(*) as cnt ";
        $sql .= "  FROM t_cast ";
        $sql .= " WHERE delflg = FALSE ";
        $sql .= "   AND castnm = ?";
        $sql .= "   AND castdiv = ?";
        if (!empty($code)) {
            // コードがあったら、そのレコード以外でユニークチェック
            $sql .= "   AND castcd <> ?";
            $params[] = $code;
        }
        if (!empty($shopdiv)) {
            $sql .=  " AND shopdiv = ?";
            $params[] = $shopdiv;
        }

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

        return ($data > 0) ? true : false;
    }
    
}
?>
