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


/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */

/**
 * レーベルマスタテーブル関連
 *
 * @package     Onedepo.Db.Label
 * @author      H.Watanabe<hiroshi.watanabe@netnee.jp>
 * @access      public
 */
class Onedepo_Db_Label
{
    var $dbaccess;

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

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

        $params = array($sort);

        $sql  = "SELECT label.makercd, maker.makernm, label.labelcd, ";
        $sql .= "       label.labelnm, label.labelnmkana, label.recommenddiv ";
        $sql .= "  FROM t_label label INNER JOIN t_maker maker ";
        $sql .= "                        ON label.makercd = maker.makercd ";
        $sql .= " WHERE label.delflg = FALSE ";
        $sql .= "   AND maker.delflg = FALSE ";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);

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

        return $data;
    }
    

    
    /**
     * 条件取得
     * 
     * @param string $mcode メーカーコード
     * @param string $lcode レーベルコード
     * @return object 取得レコード
     */
    function selectKey($mcode, $lcode)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Label#selectKey";

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

        $params = array($mcode, $lcode);
        
        $sql  = "SELECT label.makercd, maker.makernm, label.labelcd, ";
        $sql .= "       label.labelnm, label.labelnmkana, label.recommenddiv, ";
        $sql .= "       label.updcnt ";
        $sql .= "  FROM t_label label INNER JOIN t_maker maker ";
        $sql .= "                        ON label.makercd = maker.makercd ";
        $sql .= " WHERE label.delflg = FALSE ";
        $sql .= "   AND maker.delflg = FALSE ";
        $sql .= "   AND label.makercd = ?";
        $sql .= "   AND label.labelcd = ?;";

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

        return $data;
    }


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

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

        $params = NULL;

        $sql  = "SELECT label.makercd, maker.makernm, label.labelcd, ";
        $sql .= "       label.labelnm, label.labelnmkana, label.recommenddiv, ";
        $sql .= "       label.updcnt ";
        $sql .= "  FROM t_label label INNER JOIN t_maker maker ";
        $sql .= "                        ON label.makercd = maker.makercd ";
        $sql .= " WHERE label.delflg = FALSE ";
        $sql .= "   AND maker.delflg = FALSE ";

        // 検索ワード
        if (!empty($word)) {
            $sql .= "   AND (maker.makernm LIKE ? ";
            $sql .= "    OR  label.labelnm LIKE ?";
            $sql .= "    OR  label.labelnmkana LIKE ?)";
            $params[] = "%". trim($word) . "%";
            $params[] = "%". trim($word) . "%";
            $params[] = "%". trim($word) . "%";
        }

        // おすすめ区分
        if (!empty($recommenddiv)) {
            $sql .= "   AND label.recommenddiv = ? ";
            $params[] = $recommenddiv;
        }
        
        $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 $code コード
     * @return object 取得レコード
     */
    function selectMakercd($code)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Label#selectMakercd";

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

        $params = array($code);
        
        $sql  = "SELECT label.labelcd, label.labelnm FROM t_label label ";
        $sql .= " WHERE label.delflg = FALSE ";
        $sql .= "   AND label.makercd = ? ";

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

        return $data;
    }
    
    /**
     * メーカーから商品のあるレーベルのみ取得
     * 
     * @param string $code コード
     * @return object 取得レコード
     */
    function selectMakercdOfItem($code)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Label#selectMakercdOfItem";

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

        $params = array($code);
        
        $sql  = "SELECT label.labelcd, label.labelnm FROM t_label label ";
        $sql .= " WHERE label.delflg = FALSE ";
        $sql .= "   AND label.makercd = ? ";
        $sql .= "   AND label.labelcd IN (SELECT item.labelcd FROM t_item_basic_attr item WHERE item.makercd = label.makercd AND item.labelcd = label.labelcd) ";

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

        return $data;
    }

    
    /**
     * 追加
     * 
     */
    function insert($makercd, $labelnm, $labelnmkana, $recdiv, $upduser) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Label#insert";

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

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

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

        $res = $db->getOne($sql, $params);
        if (DB::isError($res) || $res < 0) {
	  $log->debug("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 update($makercd, $code, $name, $kana, $recdiv, $updcnt, $upduser) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Label#update";

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

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

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

        $res = $db->getOne($sql, $params);
        if (DB::isError($res) || $res < 0) {
            $log->debug("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 delete($makercd, $code, $updcnt, $upduser) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Label#delete";

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

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

        $sql  = "SELECT f_label_update(3, ?, ?, NULL, NULL, NULL, ?, ?, ?);";

        $res = $db->getOne($sql, $params);
        if (DB::isError($res) || $res < 0) {
            $log->debug("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  $sort   ソートキー
    */
    function viewSelectAllRecommend($sort="labelnmkana"){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Label#viewSelectAllRecommend";
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        $sql  = "   SELECT makercd, makernm, makernmkana, labelcd, labelnm, labelnmkana, updcnt ";
        $sql .= "     FROM v_label_list ";
        $sql .= "    WHERE recommenddiv = 1 ";
        $sql .= "      AND item_count > 0 ";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);
        
        $res = $db->getAll($sql);
        if (DB::isError($res)) {
            $log->debug($res->userinfo, $func_name);
            $this->dbaccess->doClose($db);
            return false;
        }
        
        $this->dbaccess->doClose($db);
        return $res;
    }


    /**
    * かなにマッチする画像S有りレーベルを取得（ビューを参照）
    *   ※おすすめを拾いません！
    *   ※商品が１件以上登録されているメーカーを拾います。
    *
    * @param    array   $kana   取得したい「かな」の配列
    * @param    string  $sort   ソートキー
    */
    function viewSelectImageByKana($kana, $sort="lbl.labelnmkana"){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Label#viewSelectImageByKana";
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT mkr.makercd, mkr.makernm, mkr.makernmkana, lbl.labelcd, lbl.labelnm, lbl.labelnmkana, lbl.recommenddiv, lbl.updcnt ";
        $sql .= "  FROM t_label lbl";
        $sql .= "    INNER JOIN t_maker mkr";
        $sql .= "      ON mkr.makercd=lbl.makercd AND mkr.delflg=FALSE";
        $sql .= "    INNER JOIN t_item_basic_attr b ";
        $sql .= "      ON b.makercd = lbl.makercd AND b.labelcd=lbl.labelcd AND b.delflg = FALSE";
        $sql .= " WHERE lbl.delflg=FALSE AND lbl.recommenddiv = 2 ";
        $sql .= "   AND (mkr.makercd, lbl.labelcd) IN (SELECT generalcd, generalsubcd FROM t_image WHERE typediv = 5 AND imgsizecd = 'S' AND delflg = FALSE) ";
        
        $params = array();
        foreach ($kana as $key => $val) {
            if ($key==0) $sql .= " AND (";
            else         $sql .= " OR";
            $sql .= " lbl.labelnmkana LIKE ?";
            $params[] = "$val%";
        }
        $sql .= ")";
        $sql .= " GROUP BY mkr.makercd, mkr.makernm, mkr.makernmkana, lbl.labelcd, lbl.labelnm, lbl.labelnmkana, lbl.recommenddiv, lbl.updcnt";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);


        $data = $db->getAll($sql, $params);
        if (DB::isError($data)) {
            $log->debug($data->userinfo, $func_name);
            $this->dbaccess->doClose($db);
            return false;
        }
        $this->dbaccess->doClose($db);

        return $data;
    }


    /**
    * かなにマッチする画像S無しレーベルを取得（ビューを参照）
    *   ※おすすめを拾いません！
    *   ※商品が１件以上登録されているメーカーを拾います。
    *
    * @param    array   $kana   取得したい「かな」の配列
    * @param    string  $sort   ソートキー
    */
    function viewSelectNoimageByKana($kana, $sort="lbl.labelnmkana"){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Label#viewSelectNoimageByKana";
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $sql  = "SELECT mkr.makercd, mkr.makernm, mkr.makernmkana, lbl.labelcd, lbl.labelnm, lbl.labelnmkana, lbl.recommenddiv, lbl.updcnt ";
        $sql .= "  FROM t_label lbl";
        $sql .= "    INNER JOIN t_maker mkr";
        $sql .= "      ON mkr.makercd=lbl.makercd AND mkr.delflg=FALSE";
        $sql .= "    INNER JOIN t_item_basic_attr b ";
        $sql .= "      ON b.makercd = lbl.makercd AND b.labelcd=lbl.labelcd AND b.delflg = FALSE";
        $sql .= " WHERE lbl.delflg=FALSE AND lbl.recommenddiv = 2 ";
        $sql .= "   AND (mkr.makercd, lbl.labelcd) NOT IN (SELECT generalcd, generalsubcd FROM t_image WHERE typediv = 5 AND imgsizecd = 'S' AND delflg = FALSE) ";
        
        $params = array();
        foreach ($kana as $key => $val) {
            if ($key==0) $sql .= " AND (";
            else         $sql .= " OR";
            $sql .= " lbl.labelnmkana LIKE ?";
            $params[] = "$val%";
        }
        $sql .= ")";
        $sql .= " GROUP BY mkr.makercd, mkr.makernm, mkr.makernmkana, lbl.labelcd, lbl.labelnm, lbl.labelnmkana, lbl.recommenddiv, lbl.updcnt";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);

        $data = $db->getAll($sql, $params);
        if (DB::isError($data)) {
            $log->debug($data->userinfo, $func_name);
            $this->dbaccess->doClose($db);
            return false;
        }
        $this->dbaccess->doClose($db);

        return $data;
    }


    /**
    * かなにマッチしない画像S有りレーベルを取得（ビューを参照）
    *   ※ただし「を」と「ん」は拾います！！
    *   ※おすすめを拾いません！
    *   ※商品が１件以上登録されているメーカーを拾います。
    *
    * @param    string  $sort   ソートキー
    */
    function viewSelectImageByNonKana($sort="lbl.labelnmkana"){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Label#viewSelectImageByNonKana";
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        $sql  = "SELECT mkr.makercd, mkr.makernm, mkr.makernmkana, lbl.labelcd, lbl.labelnm, lbl.labelnmkana, lbl.recommenddiv, lbl.updcnt ";
        $sql .= "  FROM t_label lbl";
        $sql .= "    INNER JOIN t_maker mkr";
        $sql .= "      ON mkr.makercd=lbl.makercd AND mkr.delflg=FALSE";
        $sql .= "    INNER JOIN t_item_basic_attr b ";
        $sql .= "      ON b.makercd = lbl.makercd AND b.labelcd=lbl.labelcd AND b.delflg = FALSE";
        $sql .= " WHERE lbl.delflg=FALSE AND lbl.recommenddiv = 2 ";
        $sql .= "   AND (mkr.makercd, lbl.labelcd) IN (SELECT generalcd, generalsubcd FROM t_image WHERE typediv = 5 AND imgsizecd = 'S' AND delflg = FALSE) ";
        $sql .= "   AND lbl.labelnmkana !~* '^[あ-わ]' ";
        $sql .= " GROUP BY mkr.makercd, mkr.makernm, mkr.makernmkana, lbl.labelcd, lbl.labelnm, lbl.labelnmkana, lbl.recommenddiv, lbl.updcnt";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);

        $data = $db->getAll($sql);
        if (DB::isError($data)) {
            $log->debug($data->userinfo, $func_name);
            $this->dbaccess->doClose($db);
            return false;
        }
        $this->dbaccess->doClose($db);

        return $data;
    }


    /**
    * かなにマッチしない画像S無しレーベルを取得（ビューを参照）
    *   ※ただし「を」と「ん」は拾います！！
    *   ※おすすめを拾いません！
    *   ※商品が１件以上登録されているメーカーを拾います。
    *
    * @param    string  $sort   ソートキー
    */
    function viewSelectNoimageByNonKana($sort="lbl.labelnmkana"){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Label#viewSelectNoimageByNonKana";
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        $sql  = "SELECT mkr.makercd, mkr.makernm, mkr.makernmkana, lbl.labelcd, lbl.labelnm, lbl.labelnmkana, lbl.recommenddiv, lbl.updcnt ";
        $sql .= "  FROM t_label lbl";
        $sql .= "    INNER JOIN t_maker mkr";
        $sql .= "      ON mkr.makercd=lbl.makercd AND mkr.delflg=FALSE";
        $sql .= "    INNER JOIN t_item_basic_attr b ";
        $sql .= "      ON b.makercd = lbl.makercd AND b.labelcd=lbl.labelcd AND b.delflg = FALSE";
        $sql .= " WHERE lbl.delflg=FALSE AND lbl.recommenddiv = 2 ";
        $sql .= "   AND (mkr.makercd, lbl.labelcd) NOT IN (SELECT generalcd, generalsubcd FROM t_image WHERE typediv = 5 AND imgsizecd = 'S' AND delflg = FALSE) ";
        $sql .= "   AND lbl.labelnmkana !~* '^[あ-わ]' ";
        $sql .= " GROUP BY mkr.makercd, mkr.makernm, mkr.makernmkana, lbl.labelcd, lbl.labelnm, lbl.labelnmkana, lbl.recommenddiv, lbl.updcnt";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);
        
        $data = $db->getAll($sql);
        if (DB::isError($data)) {
	  print $data->userinfo."<br>";
            $log->debug($data->userinfo, $func_name);
            $this->dbaccess->doClose($db);
            return false;
        }
        $this->dbaccess->doClose($db);

        return $data;
    }


    /**
     * メーカーcdとレーベルcdからレーベル名取得
     * 
     * @param int   $makercd メーカーコード
     * @param array $labelcd レーベルコードリスト
     * @return object 取得レコード
     */
    function getLabelnmList($makercd, $labelcd)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Label#getLabelnmList";
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        $sql  = "SELECT labelnm ";
        $sql .= "  FROM v_label_list ";
        $sql .= " WHERE makercd = ? ";
        $sql .= "   AND labelcd in ( ";
        $params = array($makercd);
        $cnt = 0;
        foreach($labelcd as $val){
            if ($ct++ == 0){
                $sql .= " ? ";
                $params[] = $val;
            } else {
                $sql .= " , ? ";
                $params[] = $val;
            }
        }
        $sql .= " ); ";
        
        $data = $db->getAll($sql, $params);
        $this->dbaccess->doClose($db);
        
        return $data;
    }


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

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

        $params = array($sort);

        $sql  = "SELECT * ";
        $sql .= "  FROM v_label_list ";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sort);

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

        return $data;
    }
    

    
    /**
     * 条件取得
     * 
     * @param string $mcode メーカーコード
     * @param string $lcode レーベルコード
     * @return object 取得レコード
     */
    function viewselectKey($mcode, $lcode)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Label#selectKey";

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

        $params = array($mcode, $lcode);
        
        $sql  = "SELECT * ";
        $sql .= "  FROM v_label_list ";
        $sql .= " WHERE makercd = ?";
        $sql .= "   AND labelcd = ?;";

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

        return $data;
    }


    /**
     * サーチ検索
     *
     * @param string  $word 検索ワード
     * @param integer $recommenddiv おすすめ区分
     * @param string  $sort ソートキー
     * @return object 取得レコード
     */
    function viewselectSearch($word, $recommenddiv, $sort="labelcd")
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Label#selectSearch";

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

        $params = NULL;

        $sql  = "SELECT * ";
        $sql .= "  FROM v_label_list ";
        $sql .= " WHERE ";

        // 検索ワード
        $w = array();
        if (!empty($word)) {
            $w1 = "   (makernm LIKE ? ";
            $w1 .= "    OR  labelnm LIKE ?";
            $w1 .= "    OR  labelnmkana LIKE ?)";
            $params[] = "%". trim($word) . "%";
            $params[] = "%". trim($word) . "%";
            $params[] = "%". trim($word) . "%";
        }
        if (!empty($w1)) $w[] = $w1;

        // おすすめ区分
        if (!empty($recommenddiv)) {
            $w[] = "   recommenddiv = ? ";
            $params[] = $recommenddiv;
        }
        $sql .= implode(" AND ", $w);
        
        $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;
    }
}
?>
