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

/**
 * 商品検索
 *
 * @package     Onedepo.Logic.Itemsearch
 * @author      netnee H.Watanabe <hiroshi.watanabe@netnee.jp>
 * @access      public
 */
class Onedepo_Logic_Itemsearch {

    var $searchkey;
    var $dbaccess;
    var $genre;


    function SearchKeyInsert($itemcd, $usercd, $shopdiv, $q, $upduser)
    {
        $log =& LogFactory::getLog();
        $log->debug("debug", "Onedepo_Logic_Itemsearch#insert");

        // AND検索の為に半角空白で分割
        $q = mb_convert_kana($q, "s", "UTF-8");
        $q_arr = explode(" ", trim($q));
        //--

        if (is_array($q_arr)) {
            foreach ($q_arr as $v) {
                if (!empty($v)) {
                    $this->searchkey->insert($itemcd, $usercd, $v, $upduser);
                }
            }
        } else {
            $this->searchkey->insert($itemcd, $usercddiv, $q, $upduser);
        }
        RETURN 1;
    }



    // 詳細検索
    function SearchDetailItem( $parameters=array(), $sort, $from, $to)
    {
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        /// SQL ロジック
        $t1 = " FROM
    t_item_basic_attr basic
    INNER JOIN t_item_web_attr web
              ON basic.itemcd = web.itemcd AND web.delflg = FALSE AND web.releasedate <= CURRENT_DATE
    LEFT JOIN t_maker maker
              ON basic.makercd = maker.makercd
";

        $where  = ' WHERE basic.delflg=FALSE ';

        // メーカ
        if ( $parameters['makercd'] != 0 ) {
            $where .= ' AND basic.makercd = ? ';
            $params[] = $parameters['makercd'];
        }

        // タイトル
        if ( !empty($parameters['itemnm']) ) {
            $where .= ' AND basic.itemnm LIKE ? ';
            $params[] = "%". $parameters['itemnm'] ."%";
        }

        // メーカー品番
        if ( !empty($parameters['makeritemcd']) ) {
            // 全角半角大文字小文字を"全角大文字"で統一
            $where .= ' AND basic.itemcd IN ';
            $where .= ' (SELECT DISTINCT itemcd FROM t_item_struct WHERE ';
            $where .= 'makeritemcd LIKE ?) ';
            $s = mb_convert_kana($parameters["makeritemcd"], "as", "UTF-8");
            $rep = array("‐","ー","―","－");
            $s = strtoupper(str_replace($rep, "-", $s));
            $params[] = '%'. $s .'%';
        }

        // JANコード
        if ( !empty($parameters['jancd']) ) {
            $where .= ' AND basic.itemcd IN (SELECT DISTINCT itemcd FROM t_item_struct WHERE jancd=?) ';
            $params[] = $parameters['jancd'];
        }

    // ジャンル
        if ( !empty($parameters['genre']) ) {
            // 単一選択
            if (is_numeric($parameters["genre"][0])) {
                $g = $this->genre->selectGenreRelationship();
                $tree = array();
                $this->genre->makeTree($g, "", $tree, false);
                $this->getGenreUnderGenre($tree, $parameters["genre"][0], $ret, false);

                $imp = implode(",", $ret);
                $t1 .= " INNER JOIN (SELECT itemcd FROM t_genre_item WHERE genrecd IN (".$imp.") GROUP BY itemcd ) gi ON gi.itemcd=basic.itemcd ";
            }
        }

    // 発売年代
      // 発売年代start
        if ( $parameters['releasedate']['start'] != 'all' ) {
            $where .= ' AND min_salestartdate >= ? ';
            $params[] = $parameters['releasedate']['start'] ."-01-01";
        }

      // 発売年代end
        if ( $parameters['releasedate']['end'] != 'all' ) {
            $where .= ' AND min_salestartdate <= ? ';
            $params[] = $parameters['releasedate']['end'] + 9 ."-12-31";
        }

    // オプション
        $qy1 = $qy2 = $qy3 = $qy4 = "";
        if ($parameters["etc"]["saledate"]=="after") {        // 発売後
            $where .= " AND min_salestartdate <= CURRENT_DATE";
        } elseif ($parameters["etc"]["saledate"]=="before") { // 発売前
            $where .= " AND min_salestartdate > CURRENT_DATE";
        }
        //--

        if ($parameters["etc"]["type"]=="new") {        // 新品
            $where .= " AND basic.newlowprice > 0";
        } elseif ($parameters["etc"]["type"]=="used") { // 中古
            $where .= " AND basic.oldlowprice > 0";
        }
        //--
        if ($parameters["etc"]["stock"]=="stk") {          // 在庫有
            if ($parameters["etc"]["type"]=="new") {        // 新品
                $where .= " AND basic.newlowprice > 0";
            } elseif ($parameters["etc"]["type"]=="used") { // 中古
                $where .= " AND basic.oldlowprice > 0";
            } else {
                $where .= " AND basic.lowprice > 0";
            }
	  //$qy3 = " (select itemcd from v_item_basic_term where stockstate=3 group by itemcd having sum(exhibitcount)>0)";
        } elseif ($parameters["etc"]["stock"]== "nstk") {  // 在庫無
            if ($parameters["etc"]["type"]=="new") {        // 新品
                $where .= " AND basic.newlowprice IS NULL";
            } elseif ($parameters["etc"]["type"]=="used") { // 中古
                $where .= " AND basic.oldlowprice IS NULL";
            } else {
                $where .= " AND basic.lowprice IS NULL";
            }
	  //$qy3 = " (select itemcd from v_item_basic_term group by itemcd having sum(exhibitcount)<=0)";
        } elseif ($parameters["etc"]["stock"]=="rsv") {    // 予約中
            $qy3 = " (SELECT itemcd FROM v_stock_list WHERE reserveflg=1 GROUP BY itemcd)";
        }
        //--
        $re = array();
        $w = "";
        if (!empty($qy4)) $w .= " and ". $qy4;

        if (!empty($qy1)) $re[] = $qy1;
        if (!empty($qy2)) $re[] = $qy2;
        if (!empty($qy3)) $re[] = $qy3;
        if (!empty($re)) {
            $r = implode(" intersect ", $re);
            $w .= " AND basic.itemcd in (". $r .")";
        }
        //$log->debug("where ".$r, "v_item_basic_list");

        // ソート
        $sortkey = "COALESCE(basic.stock_upddate, '1900-01-01') desc, 
                    basic.itemnm";
        if ($sort=="-udate") {
            $sortkey = "COALESCE(basic.stock_upddate, '1900-01-01') desc, 
                        basic.itemnm";
        } else if ($sort=="udate") {
            $sortkey = "COALESCE(basic.stock_upddate, '1900-01-01'), 
                        basic.itemnm";
        } else if ($sort=="-title") {
            $sortkey = "basic.itemnm desc";
        } else if ($sort=="title") {
            $sortkey = "basic.itemnm";
        } else if ($sort=="-saledate") {
            $sortkey = "COALESCE(basic.min_salestartdate, '1900-01-01') desc, 
                        basic.itemnm";
        } else if ($sort=="saledate") {
            $sortkey = "COALESCE(basic.min_salestartdate, '1900-01-01'), 
                        basic.itemnm";
        } else if ($sort=="-price") {
            if ($parameters["etc"]["type"]=="new") {        // 新品
                $sortkey = "COALESCE(basic.newlowprice, 0) desc, 
                            basic.itemnm";
            } else if ($parameters["etc"]["type"]=="used") {// 中古
                $sortkey = "COALESCE(basic.oldlowprice, 0) desc, 
                            basic.itemnm";
            } else {                                        // 全て
                $sortkey = "COALESCE(basic.lowprice, 0) desc, 
                            basic.itemnm";
            }
        } else if ($sort=="price") {
            if ($parameters["etc"]["type"]=="new") {        // 新品
                $sortkey = "COALESCE(basic.newlowprice, 0), 
                            basic.itemnm";
            } else if ($parameters["etc"]["type"]=="used") {// 中古
                $sortkey = "COALESCE(basic.oldlowprice, 0), 
                            basic.itemnm";
            } else {                                        // 全て
                $sortkey = "COALESCE(basic.lowprice, 0), 
                            basic.itemnm";
            }
/*
        } else if ($sort == "sell") {
            $sortkey = "COALESCE(s2.exhibitcount, 0), 
                        basic.itemnm";
            $join .= " LEFT JOIN (SELECT s.itemcd,
                                 count(distinct s.stocktitlecd) as exhibitcount
                        FROM v_stock_list s
                        WHERE s.salestartdate <= CURRENT_DATE
                              AND s.exhibitcount > 0 AND s.stockstate = 3
                              AND ((sellercd IS NOT NULL
                              AND sellernm IS NOT NULL AND memberstate=1)
                              OR (sellercd IS NULL))
                        GROUP BY s.itemcd) s2
                       ON s2.itemcd=basic.itemcd ";
        } else if ($sort == "-sell") {
            $sortkey = "COALESCE(s2.exhibitcount, 0) desc, 
                        basic.itemnm";
            $join .= " LEFT JOIN (SELECT s.itemcd,
                                 count(distinct s.stocktitlecd) as exhibitcount
                        FROM v_stock_list s
                        WHERE s.salestartdate <= CURRENT_DATE
                              AND s.exhibitcount > 0 AND s.stockstate = 3
                              AND ((sellercd IS NOT NULL
                              AND sellernm IS NOT NULL AND memberstate=1)
                              OR (sellercd IS NULL))
                        GROUP BY s.itemcd) s2
                       ON s2.itemcd=basic.itemcd ";
*/
        } 


        //-- SQL ロジック
        $sql = "SELECT
    basic.itemcd,
    basic.itemnm,
    basic.makercd,
    maker.makernm,
    COALESCE(basic.lowprice, 0) as lowprice,
    COALESCE(basic.oldlowprice, 0) as oldlowprice,
    COALESCE(basic.newlowprice, 0) as newlowprice,
    basic.updcnt AS item_basic_updcnt,
    min_salestartdate as salestartdate,
    web.releasedate,
    web.comment,
    web.updcnt AS item_web_attr_updcnt
" . $t1. $join . $where . $w . " ORDER BY " . $sortkey ." LIMIT ". $to ." OFFSET ". $from;

        $data = $db->getAll($sql, $params);
        if (DB::isError($data)) {
            $log->info("error ". $data->userinfo, "SearchDetailItem");
            die($data->getMessage());
        }

        $countquery = "SELECT count(basic.itemcd) as cnt ". $t1;
        $countquery .= $where . $w;

        $cnt = $db->getOne($countquery, $params);
        if (DB::isError($cnt)) {
            $log->info("error ". $cnt->userinfo, "SearchDetailItem");
            die($cnt->getMessage());
        }
        $data += array("recordcount" => $cnt);

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

        return $data;
    }

    /* 指定したジャンルより下の階層のジャンルを取得 */
    function getGenreUnderGenre ($genre, $genrecd, &$ret = NULL, $f=false) {
        $place = false;
        foreach ($genre as $key => $val) {
            if ($place) $f = false;
            if ($val["genrecd"]==$genrecd) $f = $place = true;
            if ($f) {
                $ret[] = $val["genrecd"];
            }
            // 下の階層をチェック
            $r = $this->getGenreUnderGenre ($val["child"], $genrecd, $ret, $f);
        }
    }
}
?>