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


/**
 * 商品基本テーブル関連
 *
 * @package     Onedepo.Db.Itembasic
 * @author      netnee T.Otogawa<tomokazu.otogawa@netnee.jp>
 * @access      public
 */
class Onedepo_Db_Itembasic
{

    var $dbaccess;


    function getSearchResultsCount($where = array(), $params = array())
    {
        $log =& LogFactory::getLog();

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

        $query = "SELECT count(basic.itemcd) as count  FROM
                  t_item_struct struct
                  INNER JOIN t_item_basic_attr basic
                        ON struct.itemcd=basic.itemcd
                        AND struct.delflg = FALSE
                        AND basic.delflg = FALSE
                  LEFT JOIN t_item_web_attr web
                        ON basic.itemcd=web.itemcd
                        AND web.delflg=FALSE
                      ";
        foreach ($where as $key => $val) {
            if ($key==0)    $query .= " WHERE";
            else            $query .= " AND";
            $query .= " $val";
        }
	//print $query ."<br>";
$cnt = $db->getOne($query, $params);
        if (DB::isError($cnt)) {
            $log->debug($cnt->userinfo, "search error");
            die($cnt->getMessage());
        }
        $this->dbaccess->doClose($db);

        return $cnt;
    }
    function getSearchResults($where = array(), $params = array(), $sortkey, $to, $from)
    {
        $log =& LogFactory::getLog();

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

        $sql = "SELECT
                  basic.itemcd,
                  basic.itemnm,
                  basic.itemnmkana,
                  basic.makercd,
                  basic.updcnt AS item_basic_updcnt,
                  struct.structcd,
                  struct.variationnm,
                  struct.makeritemcd,
                  struct.jancd,
                  struct.salestartdate,
                  struct.orderclosedate,
                  struct.updcnt AS item_struct_updcnt,
                  web.releasedate,
                  web.updcnt AS item_web_attr_updcnt,
                  seller.sellercd
               ";
        $t1 = " FROM
                  t_item_struct struct
                  INNER JOIN t_item_basic_attr basic
                        ON struct.itemcd=basic.itemcd
                        AND struct.delflg = FALSE
                        AND basic.delflg = FALSE
                  LEFT JOIN t_item_web_attr web
                        ON basic.itemcd=web.itemcd
                        AND web.delflg=FALSE
                  LEFT JOIN t_seller_item seller
                        ON basic.itemcd=seller.itemcd
                        AND seller.delflg=FALSE
                      ";
        foreach ($where as $key => $val) {
            if ($key==0)    $t1 .= " WHERE";
            else            $t1 .= " AND";
            $t1 .= " $val";
        }
        $sql .= $t1;
//echo "[" . $sql ."]<br>";
        $sql .= " ORDER BY ". $sortkey;
        if(!is_null($to)) {
            $sql .= " LIMIT ". $to  ." OFFSET ". $from;
        }

	//print $sql ."<br>";
	//print_r($params); print "<Br>";
        $data = $db->getAll($sql, $params);
        if (DB::isError($data)) {
            $log->debug($data->userinfo, "search error");
            die($data->getMessage());
        }

        $nolimit = "SELECT count(basic.itemcd) as count ". $t1;
        $cnt = $db->getOne($nolimit, $params);
        $data += array("recordcount" => $cnt);

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

        return $data;
    }
    function getSearchResultsExport($where = array(), $params = array(), $sortkey, $to, $from)
    {
        $log =& LogFactory::getLog();

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

        $sql = "SELECT
                  basic.itemcd,
                  basic.itemnm,
                  basic.itemnmkana,
                  basic.makercd,
                  maker.makernm,
                  basic.itemexp,
                  basic.updcnt AS item_basic_updcnt,
                  struct.structcd,
                  struct.makeritemcd,
                  struct.jancd,
                  struct.salestartdate,
                  struct.orderclosedate,
                  struct.makerprice,
                  struct.comment AS struct_comment,
                  struct.updcnt AS item_struct_updcnt,
                  web.releasedate,
                  web.rankingrate,
                  web.comment,
                  web.updcnt AS item_web_attr_updcnt 
               ";
        $t1 = " FROM
                  t_item_struct struct
                  INNER JOIN t_item_basic_attr basic
                        ON struct.itemcd=basic.itemcd
                        AND struct.delflg = FALSE
                        AND basic.delflg = FALSE
                  LEFT JOIN t_item_web_attr web
                        ON basic.itemcd=web.itemcd
                        AND web.delflg=FALSE
    LEFT JOIN t_maker maker
              ON basic.makercd = maker.makercd
                      ";
        foreach ($where as $key => $val) {
            if ($key==0)    $t1 .= " WHERE";
            else            $t1 .= " AND";
            $t1 .= " $val";
        }
        $sql .= $t1;

        $sql .= " ORDER BY ". $sortkey;
        if(!is_null($to)) {
            $sql .= " LIMIT ". $to  ." OFFSET ". $from;
        }

        //print $sql ."<br>";
	//print_r($params); print "<Br>";
        $data = $db->getAll($sql, $params);
        if (DB::isError($data)) {
            $log->debug($data->userinfo, "search error");
            die($data->getMessage());
        }
        $this->dbaccess->doClose($db);

        return $data;
    }
    function getImagecount($itemcd) {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $sql = "SELECT imgsizecd FROM t_image
                  WHERE typediv = 1
                  AND generalcd = ?
                  AND delflg=FALSE";
        $params = array($itemcd);
        $data = $db->getAll($sql, $params);
        if (DB::isError($data)) {
            die($data->getMessage());
        }
        $d = array('l' => '×', 'm' => '×', 's' => '×');
        foreach ($data as $val) {
            switch ($val['imgsizecd']) {
                case 'L':
                $d['l'] = '○';
                break;
                case 'M':
                $d['m'] = '○';
                break;
                case 'S':
                $d['s'] = '○';
                break;
            }
        }

        $this->dbaccess->doClose($db);
        return $d;
    }
    function getStockcount($itemcd, $structcd) {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $sql = "SELECT count(t_s.itemcd) as stocktitlecdcont
                       FROM t_ex_stock_detail t_s
                      left join 
                       (select * from t_order_detail t_o
                         left join t_forward_detail t_f
                         on t_f.orderno=t_o.orderno
                         and t_f.orderrowno=t_o.orderrowno
                        where t_f.forwardstate is null 
                        or not (t_f.forwardstate is not null 
                        and (t_f.forwardstate=9 
                        or (t_f.forwardstate=8 and t_f.returndate is not null)))
                       ) t_o 
                      on t_o.itemcd = t_s.itemcd 
                      AND t_o.structcd = t_s.structcd 
                      and t_s.stockcd = t_o.stockcd
                     WHERE t_o.itemcd IS NULL
                     AND   t_s.stockstate = 3
                     AND   t_s.exhibitcount > 0
                     AND   t_s.sellercd IS NULL
                     AND   t_s.delflg = false
                     AND   t_s.itemcd = ?
                     AND   t_s.structcd = ?
                    GROUP BY t_s.itemcd, t_s.structcd";
        $params = array($itemcd, $structcd);
        $data = $db->getOne($sql, $params);
        if (DB::isError($data)) {
            die($data->getMessage());
        }

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

        return $data;
    }

    function getStockcount2($itemcd, $structcd) {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $sql = "SELECT exhibitcount
                       FROM t_ex_stock_detail t_s
 
                     WHERE   t_s.stockstate = 3
                     AND   t_s.exhibitcount > 0

                     AND   t_s.delflg = false
                     AND   t_s.itemcd = ?
                     AND   t_s.structcd = ?";
        $params = array($itemcd, $structcd);
  
        $data = $db->getOne($sql, $params);
        if (DB::isError($data)) {
            die($data->getMessage());
        }

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

        return $data;
    }

    function getStockStyle($itemcd, $structcd) {
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
         $sql = "SELECT sellercd
                       FROM t_ex_stock_detail t_s
                     WHERE t_s.itemcd = ?
                     AND   t_s.structcd = ?";

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

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

        return $data;
    }

    function getOneItem($cd, $where="")
    {
        $log =& LogFactory::getLog();

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


        $sql  = "SELECT * FROM v_item_list WHERE itemcd = ?";
        $sql .= $where;         // リプレースメントを使用する場合は変更すること。
        $params = array($cd);

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

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

        return $data;
    }

    function getOneItemRow($cd, $where="")
    {
        $log =& LogFactory::getLog();

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


        $sql  = "SELECT * FROM v_item_list WHERE itemcd = ?";
        $sql .= $where;         // リプレースメントを使用する場合は変更すること。
        $params = array($cd);

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

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

        return $data;
    }
    function getMediaForItemcd($itemcd, $media) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $sql .= "SELECT itemcd, 
                        COALESCE(mediacd, 0) as dvd
                    FROM t_item_struct
                   WHERE mediacd = ?
                         AND delflg = FALSE
                        AND itemcd in (!)";
        $params = array($media, $itemcd);

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

        $this->dbaccess->doClose($db);
        return $data;
    }
    function getSellingpriceForItemcd($itemcd, $condition="") {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $sql  = "SELECT distinct on(itemcd) itemcd, 
                          structcd, stockcd, sellingprice 
                  FROM v_stock_list WHERE (itemcd,sellingprice) IN 
                   (SELECT s.itemcd, min(s.sellingprice) as sellingprice
                    FROM v_stock_list s
                   WHERE  ((s.exhibitcount > 0 AND s.stockstate = 3
                           AND ((sellercd IS NOT NULL AND sellernm IS NOT NULL 
                           AND memberstate=1) OR (sellercd IS NULL)))
                           OR s.reserveflg = 1)
                          AND itemcd in (!)
                          $condition
                   GROUP BY s.itemcd)";
        $params = array($itemcd);

        $data = $db->getAssoc($sql, false, $params, DB_FETCHMODE_ASSOC, true);
        if (DB::isError($data)) {
            $log->error("error ". $data->userinfo, "getSellingpriceForItemcd");
            die($data->getMessage());
        }

        $this->dbaccess->doClose($db);
        return $data;
    }
    function getLowprice($itemcd) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $sql  = "SELECT distinct on(itemcd) itemcd, 
                          structcd, stockcd, sellingprice 
                  FROM v_stock_list WHERE (itemcd,sellingprice) IN 
                   (SELECT basic.itemcd, basic.lowprice 
                      FROM t_item_basic_attr basic
                    WHERE basic.itemcd=? AND basic.delflg=FALSE)";
        $params = array($itemcd);

        $data = $db->getAssoc($sql, false, $params, DB_FETCHMODE_ASSOC, true);
        if (DB::isError($data)) {
            $log->error("error ". $data->userinfo, "getLowprice");
            die($data->getMessage());
        }

        $this->dbaccess->doClose($db);
        return $data;
    }
    function getCartCd($itemcd, $price=NULL) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $sql  = "SELECT distinct on(itemcd) structcd, stockcd
                  FROM v_stock_list 
                   WHERE stockstate in (1,3) AND itemcd=? AND sellingprice=?";
        $params = array($itemcd,$price);

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

        $this->dbaccess->doClose($db);
        return $data;
    }
    function getCartCdWithDateCheck($itemcd, $price=NULL) {
        $ret = true;
        $log =& LogFactory::getLog();
        
        $db = $this->dbaccess->doConnect();
        
        $sql  = "SELECT distinct on(itemcd) structcd, stockcd
                 FROM v_stock_list 
                 WHERE stockstate in (1,3) AND itemcd=? AND sellingprice=?
                    AND (
                        -- 予約中
                        ( salestartdate > current_date AND orderclosedate >= current_date )
                        OR
                        -- 発売中
                        (( salestartdate <= current_date AND orderclosedate <= current_date )
                            OR ( orderclosedate IS NULL AND salestartdate <= current_date )))";
        $params = array($itemcd,$price);
        
        $data = $db->getRow($sql, $params, DB_FETCHMODE_ASSOC);
        if (DB::isError($data)) {
            $log->error("error ". $data->userinfo, "getCartCdWithDateCheck");
            die($data->getMessage());
        }
        
        $this->dbaccess->doClose($db);
        return $data;
    }
    function getExhibitcountForItemcd($itemcd) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $sql .= "SELECT s.itemcd, count(distinct s.stocktitlecd) as exhibitcount
                    FROM v_stock_list s
                   WHERE s.exhibitcount > 0 AND s.stockstate = 3
                         AND ((sellercd IS NOT NULL AND sellernm IS NOT NULL
                              AND memberstate=1) OR (sellercd IS NULL))
                         AND itemcd in (!)
                    GROUP BY s.itemcd";
        $params = array($itemcd);

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

        $this->dbaccess->doClose($db);
        return $data;
    }
    function getReserveflgForItemcd($itemcd) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $sql .= "(SELECT s.itemcd, min(s.reserveflg) as reserveflg
                    FROM v_stock_list s 
                   WHERE s.reserveflg=1 
                         AND itemcd in (!)
                    GROUP BY s.itemcd)";
        $params = array($itemcd);

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

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


    function getItemcdForJancd($w, $p) {

        $ret = true;
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        
        $sql = "SELECT DISTINCT itemcd 
                FROM t_item_struct 
                WHERE delflg=FALSE ";
        foreach ($w as $val) {
            $sql .= " AND $val";
        }

        $data = $db->getCol($sql, 0, $p);
        if (DB::isError($data)) {
            $log->error($data->userinfo, "getItemcdForJancd");
            die($data->getMessage());
        }

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

    function getItemcdForMkrcd($w, $p) {

        $ret = true;
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        
        $sql = "SELECT DISTINCT itemcd 
                FROM t_item_struct 
                WHERE delflg=FALSE ";
        foreach ($w as $val) {
            $sql .= " AND $val";
        }

        $data = $db->getCol($sql, 0, $p);
        if (DB::isError($data)) {
            $log->error($data->userinfo, "getItemcdForMkrcd");
            die($data->getMessage());
        }

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

    function getDataCount($maxcount=1000, $g_join="", $dc_w="") {
        $log =& LogFactory::getLog();

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

        $sql = "SELECT f_data_count FROM f_data_count(?,?,?)";
        $c = $db->getOne($sql, array($maxcount,$g_join,$dc_w));
        $this->dbaccess->doClose($db);
        return $c;
    }
    function getTotalDataCount() {
        $log =& LogFactory::getLog();

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

        $sql = "SELECT alltitlenum FROM t_systeminfo WHERE systemcd=1 AND validflg=TRUE";

        $c = $db->getRow($sql);
        if (DB::isError($c)) {
            $log->debug("error c ".$c->userinfo, "Onedepo_Db_Itembasic#getTotalDataCount");
            die($c->getMessage());
        }

        $this->dbaccess->doClose($db);
        return $c;
    }
    function getNewitemList($to, $from, $where = array(), $params = array(), $sortkey, $join="", $g_join="")
    {
        $log =& LogFactory::getLog();

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

        $t1 = " FROM
    t_item_basic_attr basic
    ". $g_join ."
    INNER JOIN t_item_web_attr web
              ON basic.itemcd = web.itemcd AND web.delflg = FALSE AND web.releasedate <= CURRENT_DATE
";
        $limitquery = "SELECT
    basic.itemcd,
    basic.itemnm,
    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;

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

        $qry = " WHERE basic.delflg=FALSE ";
        foreach ($where as $key => $val) {
            if (is_array($val)) {
                $qry .= " AND";
                foreach($val as $k => $v) {
                    if ($k > 0) $qry .= " AND";
                    $qry .= " $v";
                }
            }
        }

        $limitquery .= $qry;
        $limitquery .= " ORDER BY ". $sortkey;
        $limitquery .=  " LIMIT ". $to ." OFFSET ". $from;

	//print $limitquery ."<br>";
	//print_r($params); print "<br>";
        $data = $db->getAll($limitquery, $params);
        if (DB::isError($data)) {
            $log->debug("error data ".$data->userinfo, "Onedepo_Db_Itembasic#getNewItemList");
            die($data->getMessage());
        }

        $countquery .= $qry;

	//print $countquery ."<br>";
	//print_r($params); print "<br>";
        $cnt = $db->getOne($countquery, $params);
        if (DB::isError($cnt)) {
            $log->debug("error cnt ".$cnt->userinfo, "Onedepo_Db_Itembasic#getNewItemList");
            die($cnt->getMessage());
        }
        $data += array("recordcount" => $cnt);

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

        return $data;
    }

    function getNewListPortal($to, $from, $where = array(), $params = array(), $sortkey)
    {
        $log =& LogFactory::getLog();

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

        $limitquery = "SELECT
    basic.itemcd,
    basic.itemnm,
    COALESCE(basic.lowprice, 0) as lowprice,
    COALESCE(basic.oldlowprice, 0) as oldlowprice,
    COALESCE(basic.newlowprice, 0) as newlowprice,
    min_salestartdate as salestartdate,
    web.releasedate,
    web.comment
    FROM
    t_item_basic_attr basic
    ". $g_join ."
    INNER JOIN t_item_web_attr web
              ON basic.itemcd = web.itemcd AND web.delflg = FALSE AND web.releasedate <= CURRENT_DATE
    ";

        $qry = " WHERE basic.delflg=FALSE ";
        foreach ($where as $key => $val) {
            if (is_array($val)) {
                $qry .= " AND";
                foreach($val as $k => $v) {
                    if ($k > 0) $qry .= " AND";
                    $qry .= " $v";
                }
            }
        }

        $limitquery .= $qry;
        $limitquery .= " ORDER BY ". $sortkey;
        $limitquery .=  " LIMIT ". $to ." OFFSET ". $from;

        $data = $db->getAll($limitquery, $params);
        if (DB::isError($data)) {
            $log->debug("error data ".$data->userinfo, "Onedepo_Db_Itembasic#getNewItemList");
            die($data->getMessage());
        }

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

        return $data;
    }

    function getOneItem4ExhibitRow($cd, $scd, $where="")
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itembasic#getOneItem4ExhibitRow";
        $log->debug("Onedepo_Db_Itembasicが実行されました", $func_name);

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

        $sql .= "select * from v_item_basic2_list where itemcd=? and structcd=?";
        $sql .= $where;         // リプレースメントを使用する場合は変更すること。
        $params = array($cd, $scd);

        //$log->debug("(sql, params( = ". print_r($sql, true).", ".print_r($params, true), $func_name);
        $data = $db->getRow($sql, $params);
        if (DB::isError($data)) {
            $log->debug($data->userinfo, $func_name);
            die($data->getMessage());
        }

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

        return $data;
    }

    function getNewitemList4Exhibit($to, $from, $where = array(), $params = array(), $sortkey)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itembasic#getNewitemList4Exhibit";
        $log->debug("Onedepo_Db_Itembasicが実行されました", $func_name);


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

        $sql = "SELECT
                  basic.itemcd,
                  basic.itemnm,
                  basic.makercd,
                  basic.itemexp,
                  struct.structcd,
                  struct.makeritemcd,
                  struct.jancd,
                  struct.makerprice,
                  struct.salestartdate,
                  web.releasedate
               ";
        $t1 = " FROM
                  t_item_struct struct
                  INNER JOIN t_item_basic_attr basic
                        ON struct.itemcd=basic.itemcd
                        AND struct.delflg = FALSE
                        AND basic.delflg = FALSE
                  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
                      ";

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


        $qry = " WHERE (struct.salestartdate <= CURRENT_DATE OR struct.salestartdate IS NULL)"; // NULLも許可
        foreach ($where as $key => $val) {
            if (is_array($val)) {
                foreach($val as $k => $v) {
                    $qry .= " AND";
                    $qry .= " $v";
                }
            }
        }

        $sql .= $qry;
        $sql .= " ORDER BY ". $sortkey;
        $sql .=  " LIMIT ". $to ." OFFSET ". $from;

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

        $countquery .= $qry;

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


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

        return $data;
    }

    // お勧め商品取得
    function getRecommendItem($media=NULL, $limit)
    {
        $params = array();
        $log =& LogFactory::getLog();

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


        $sql  = "SELECT item.itemcd, item.itemnm,".
                "COALESCE(item.lowprice, 0) as lowprice, COALESCE(item.oldlowprice, 0) as oldlowprice,".
                "COALESCE(item.newlowprice, 0) as newlowprice, item.min_salestartdate as salestartdate,".
                "web.releasedate, web.comment ".
                "FROM t_recommend rec, t_item_basic_attr item, " .
                    "t_item_web_attr web " .
                "WHERE rec.delflg = false AND item.delflg = false " .
                    "AND web.delflg = false AND item.itemcd = rec.itemcd " .
                    "AND item.itemcd = web.itemcd " .
                    
                    "AND rec.startdate <= CURRENT_DATE " .       // お勧め商品表示開始日
                    "AND rec.closedate >= CURRENT_DATE " .       //お勧め商品表示終了日
                    "AND web.releasedate <= CURRENT_DATE ";       // 公開している商品

        $sql .= " GROUP BY item.itemcd, item.itemnm,".
                "item.lowprice,item.oldlowprice,".
                "item.newlowprice, item.min_salestartdate,".
                "web.releasedate, web.comment";
        $sql .= " ORDER BY random()";                    // ランダム取得
        $sql .= " LIMIT !";
        $params[] = $limit;

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

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

        return $data;
    }

    // 渡されたジャンルコードの配列を含むお勧め商品取得
    function getRecommendItemForGenre($genre_list, $media=NULL, $limit)
    {
        $params = array();
        $log =& LogFactory::getLog();

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

        foreach ($genre_list as $value) {
            if ( !empty($where) ) {
                $where .= ',?';
            } else {
                $where .= '?';
            }
            $params[] = $value['genrecd'];
        }
        
        $sql  = "SELECT item.itemcd, item.itemnm,".
                "COALESCE(item.lowprice, 0) as lowprice, COALESCE(item.oldlowprice, 0) as oldlowprice,".
                "COALESCE(item.newlowprice, 0) as newlowprice, item.min_salestartdate as salestartdate,".
                "web.releasedate, web.comment ".
                "FROM t_recommend rec, t_item_basic_attr item, " .
                    "t_item_struct struct, t_item_web_attr web " .
                "WHERE rec.delflg = false " .
                    "AND item.delflg = false AND struct.delflg = false " .
                    "AND web.delflg = false AND item.itemcd = rec.itemcd " .
                    "AND item.itemcd = struct.itemcd AND item.itemcd = web.itemcd " .
                    
                    //"AND rec.priority >= 4 " .  
                    "AND rec.startdate <= CURRENT_DATE " .       // お勧め商品表示開始日
                    "AND rec.closedate >= CURRENT_DATE " .       //お勧め商品表示終了日
                    //"AND struct.salestartdate <= CURRENT_DATE ". // 発売している商品
                    "AND web.releasedate <= CURRENT_DATE ";       // 公開している商品
                        
        // ジャンル
        if(!empty($where)) {
            $sql .= "AND item.itemcd IN (SELECT gnr.itemcd FROM t_genre_item gnr WHERE gnr.delflg = false AND gnr.genrecd IN (". $where .")) ";
        }
        if(!is_null($media)) {
            $sql .= "AND struct.mediacd = ? ";
            $params[] = $media;
        }
        
        $sql .= " GROUP BY item.itemcd, item.itemnm,".
                "item.lowprice,item.oldlowprice,".
                "item.newlowprice, item.min_salestartdate,".
                "web.releasedate, web.comment";
        $sql .= " ORDER BY random()";                    // ランダム取得
        $sql .= " LIMIT !";
        $params[] = $limit;

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

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

        return $data;
    }
    
    // お勧め予約商品取得
    function getReserveRecommendItem()
    {
        $params = array();
        $log =& LogFactory::getLog();

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

        $sql  = "SELECT item.itemcd, item.itemnm " .
                "FROM t_recommend rec, t_item_basic_attr item, " .
                    "t_item_struct struct, t_item_web_attr web " .
                "WHERE rec.delflg = false " .
                    "AND item.delflg = false AND struct.delflg = false " .
                    "AND web.delflg = false AND item.itemcd = rec.itemcd " .
                    "AND item.itemcd = struct.itemcd AND item.itemcd = web.itemcd " .
                    
                    "AND rec.priority >= 4 " .  
                    "AND rec.startdate <= CURRENT_DATE " .      // お勧め商品表示開始日
                    "AND rec.closedate >= CURRENT_DATE " .      //お勧め商品表示終了日
                    "AND struct.salestartdate > CURRENT_DATE ". // 発売前の商品
                    "AND web.releasedate <= CURRENT_DATE ".     // 公開している商品
                    
                    "AND 0 < (SELECT count(*) FROM t_image img " .
                        "WHERE img.typediv = 1 AND img.generalcd = item.itemcd " .
                        "AND img.delflg=FALSE) ";
                        
        $sql .= " ORDER BY random()";                    // ランダム取得
        $sql .= " LIMIT 4";

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

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

        return $data;
    }

    // 渡されたジャンルコードの配列を含むお勧め予約商品取得
    function getReserveRecommendItemForGenre($genre_list)
    {
        $params = array();
        $log =& LogFactory::getLog();

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

        foreach ($genre_list as $value) {
            if ( !empty($where) ) {
                $where .= ',?';
            } else {
                $where .= '?';
            }
            $params[] = $value['genrecd'];
        }

        $sql  = "SELECT item.itemcd, item.itemnm, web.comment " .
                "FROM t_recommend rec, t_item_basic_attr item, " .
                    "t_item_struct struct, t_item_web_attr web " .
                "WHERE rec.delflg = false " .
                    "AND item.delflg = false AND struct.delflg = false " .
                    "AND web.delflg = false AND item.itemcd = rec.itemcd " .
                    "AND item.itemcd = struct.itemcd AND item.itemcd = web.itemcd " .
                    
                    "AND rec.priority >= 4 " .  
                    "AND rec.startdate <= CURRENT_DATE " .       // お勧め商品表示開始日
                    "AND rec.closedate >= CURRENT_DATE " .       //お勧め商品表示終了日
                    "AND struct.salestartdate > CURRENT_DATE " . // 発売前の商品
                    "AND web.releasedate <= CURRENT_DATE " .     // 公開している商品
                    
                    "AND 0 < (SELECT count(*) FROM t_image img " .
                        "WHERE img.typediv = 1 AND img.generalcd = item.itemcd " .
                        "AND img.delflg=FALSE) ";
        // ジャンル
        if(!empty($where)) {
            $sql .= "AND item.itemcd IN (SELECT gnr.itemcd FROM t_genre_item gnr WHERE gnr.delflg = false AND gnr.genrecd IN (". $where .")) ";
        }

        $sql .= " ORDER BY random()";                    // ランダム取得
        $sql .= " LIMIT 4";


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

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

        return $data;
    }

    /**
     * 更新取得
     *
     */
    function update($code, $itemnm, $itemnmkana, $makercode, $itemexp, 
                    $option01, $option02, $option03, $option04, $option05,
                    $option06, $option07, $option08, $option09, $option10,
                    $upduser, $updcnt, $releasedate, $rankingrate, $comment, $webupdcnt, $genre) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itembasic#update";

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

        $db = $this->dbaccess->doConnect();
        $db->autoCommit(false);
        $db->query("BEGIN;");


        // 商品基本属性 ==>>
        if (empty($code)){
            $code = NULL;
        }
        if (empty($makercode)){
            $makercode = NULL;
        }
        if (empty($itemexp)){
            $itemexp = NULL;
        }
        if (empty($option01)){
            $option01 = NULL;
        }
        if (empty($option02)){
            $option02 = NULL;
        }
        if (empty($option03)){
            $option03 = NULL;
        }
        if (empty($option04)){
            $option04 = NULL;
        }
        if (empty($option05)){
            $option05 = NULL;
        }
        if (empty($option06)){
            $option06 = NULL;
        }
        if (empty($option07)){
            $option07 = NULL;
        }
        if (empty($option08)){
            $option08 = NULL;
        }
        if (empty($option09)){
            $option09 = NULL;
        }
        if (empty($option10)){
            $option10 = NULL;
        }

        $params = array($code, $itemnm, $itemnmkana, $makercode, $itemexp, $option01, $option02,
                        $option03, $option04, $option05, $option06, $option07,
                        $option08, $option09, $option10, $upduser, $_SERVER['REMOTE_ADDR'], $updcnt);
        $sql  = "select * from f_item_basic_attr_update(2, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
        $res = $db->getRow($sql, $params);

        if (DB::isError($res) || $res[0] < 0) {
            $db->rollback();
            $db->query("ROLLBACK;");
            $log->error("f_item_basic_attr_update#rollback : ". $res->userinfo, $func_name);
            return $res;
        }


        // 商品ウェブ属性 ==>>
        if (empty($rankingrate)){
            $rankingrate = NULL;
        }
        if (empty($releasedate)){
            $releasedate = NULL;
        }

        $params = array($code, $releasedate, $rankingrate, $comment, $upduser, $_SERVER['REMOTE_ADDR'], $webupdcnt);
        $sql  = "select * from f_item_web_attf_update(2, ?, ?, ?, ?, ?, ?, ?);";
        $res = $db->getRow($sql, $params);

        if (DB::isError($res) || $res[0] < 0) {
            $db->rollback();
            $db->query("ROLLBACK;");
            $log->error("f_item_web_attf_update#rollback : ". $res->userinfo, $func_name);
            return $res;
        }


        // ジャンルアイテム ==>
        if (!empty($genre)){    // ジャンル未選択時は登録動作を行わない
            $genre = implode(",", $genre);
            $genre = "{" . $genre . "}";

            $params = array($code, $genre, $upduser, $_SERVER['REMOTE_ADDR']);
            $sql  = "select * from f_genre_item_update(?, ?, ?, ?);";
            $res = $db->getRow($sql, $params);

            if (DB::isError($res) || $res[0] < 0) {
                $db->rollback();
                $db->query("ROLLBACK;");
                $log->error("f_genre_item_update#rollback : ". $res->userinfo, $func_name);
                return $res;
            }
        }


        $db->query("COMMIT;");
        $db->commit();
        $log->debug("commit", $func_name);
        $this->dbaccess->doClose($db);

        return true;
    }


    /**
     * 追加登録
     *
     */
    function insert($itemnm, $itemnmkana, $makercode, $itemexp, 
                    $basic_option01, $basic_option02, $basic_option03, $basic_option04, $basic_option05,
                    $basic_option06, $basic_option07, $basic_option08, $basic_option09, $basic_option10,
                    $upduser, $releasedate, $rankingrate, $comment, $genre,
                    $variationnm, $makeritemcd, $jancd, $salestartdate, $orderclosedate,
                    $makerprice, $struct_comment, 
                    $struct_option01, $struct_option02, $struct_option03, $struct_option04, $struct_option05,
                    $struct_option06, $struct_option07, $struct_option08, $struct_option09, $struct_option10,
                    $download) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itembasic#insert";

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

        $db = $this->dbaccess->doConnect();
        $db->autoCommit(false);
        $db->query("BEGIN;");


        // 商品基本属性 ==>>
        if (empty($makercode)){
            $makercode = NULL;
        }
        if (empty($itemexp)){
            $itemexp = NULL;
        }
        if (empty($basic_option01)){
            $basic_option01 = NULL;
        }
        if (empty($basic_option02)){
            $basic_option02 = NULL;
        }
        if (empty($basic_option03)){
            $basic_option03 = NULL;
        }
        if (empty($basic_option04)){
            $basic_option04 = NULL;
        }
        if (empty($basic_option05)){
            $basic_option05 = NULL;
        }
        if (empty($basic_option06)){
            $basic_option06 = NULL;
        }
        if (empty($basic_option07)){
            $basic_option07 = NULL;
        }
        if (empty($basic_option08)){
            $basic_option08 = NULL;
        }
        if (empty($basic_option09)){
            $basic_option09 = NULL;
        }
        if (empty($basic_option10)){
            $basic_option10 = NULL;
        }


        $params = array($itemnm, $itemnmkana, $makercode, $itemexp, $basic_option01, $basic_option02,
                        $basic_option03, $basic_option04, $basic_option05, $basic_option06, $basic_option07,
                        $basic_option08, $basic_option09, $basic_option10, $upduser, $_SERVER['REMOTE_ADDR']);
        $sql  = "select * from f_item_basic_attr_update(1, NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL);";
        $res = $db->getRow($sql, $params);

        if (DB::isError($res) || $res[0] < 0) {
            $db->rollback();
            $db->query("ROLLBACK;");
            $log->error("f_item_basic_attr_update#rollback : ". $res->userinfo, $func_name);
            return false;
        }


        $fixcode = $res[2];


        // 商品ウェブ属性 ==>>
        if (empty($rankingrate)){
            $rankingrate = NULL;
        }
        if (empty($releasedate)){
            $releasedate = NULL;
        }

        $params = array($fixcode, $releasedate, $rankingrate, $comment, $upduser, $_SERVER['REMOTE_ADDR']);
        $sql  = "select * from f_item_web_attf_update(1, ?, ?, ?, ?, ?, ?, NULL);";
        $res = $db->getRow($sql, $params);

        if (DB::isError($res) || $res[0] < 0) {
            $db->rollback();
            $db->query("ROLLBACK;");
            $log->error("f_item_web_attf_update#rollback : ". $res->userinfo, $func_name);
            return false;
        }

        // ジャンル未選択時は登録動作を行わない
        if (!empty($genre)){
            $genre = implode(",", $genre);
            $genre = "{" . $genre . "}";

            $params = array($fixcode, $genre, $upduser, $_SERVER['REMOTE_ADDR']);
            $sql  = "select * from f_genre_item_update(?, ?, ?, ?);";
            $res = $db->getRow($sql, $params);

            if (DB::isError($res) || $res[0] < 0) {
                $db->rollback();
                $db->query("ROLLBACK;");
                $log->error("f_genre_item_update#rollback : ". $res->userinfo, $func_name);
                return false;
            }
        }


        // 構成情報 ==>>
        if (empty($struct_option01)){
            $struct_option01 = NULL;
        }
        if (empty($struct_option02)){
            $struct_option02 = NULL;
        }
        if (empty($struct_option03)){
            $struct_option03 = NULL;
        }
        if (empty($struct_option04)){
            $struct_option04 = NULL;
        }
        if (empty($struct_option05)){
            $struct_option05 = NULL;
        }
        if (empty($struct_option06)){
            $struct_option06 = NULL;
        }
        if (empty($struct_option07)){
            $struct_option07 = NULL;
        }
        if (empty($struct_option08)){
            $struct_option08 = NULL;
        }
        if (empty($struct_option09)){
            $struct_option09 = NULL;
        }
        if (empty($struct_option10)){
            $struct_option10 = NULL;
        }

        if (empty($makerprice)){
            $makerprice = NULL;
        }
        if (empty($salestartdate)){
            $salestartdate = NULL;
        }
        if (empty($orderclosedate)){
            $orderclosedate = NULL;
        }

        $params = array($fixcode, $variationnm, $makeritemcd, $jancd, $salestartdate, $orderclosedate,
                        $makerprice, $struct_comment, $struct_option01, $struct_option02,
                        $struct_option03, $struct_option04, $struct_option05, $struct_option06, $struct_option07,
                        $struct_option08, $struct_option09, $struct_option10, $upduser, $_SERVER['REMOTE_ADDR']);
        $sql  = "SELECT * FROM f_item_struct_update(1, ?, NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL);";
        $res = $db->getRow($sql, $params);

        if (DB::isError($res) || $res[0] < 0) {
            $db->rollback();
            $db->query("ROLLBACK;");
            $log->error("f_item_struct_update#rollback : ". $res->userinfo, $func_name);
            return false;
        }
        

        // ダウンロード販売
        if($download==1) {
            // 追加した構成コード取得
            $fixcode2 = $res[2];
            
            // ダウンロード商品テーブル
            $sql  = "SELECT * FROM f_dl_item_update(1, ?, ?, ?, ?, ?, ?, ?, ?, NULL);";
            $params = array($fixcode, $fixcode2, false, DL_COUNT, DL_REPIOD_DAYS, '', $upduser, $_SERVER['REMOTE_ADDR']);
            
            $res = $db->getRow($sql, $params);
            if (DB::isError($res) || $res[0] < 0) {
                $db->rollback();
                $db->query("ROLLBACK;");
                $log->error("f_dl_item_update#rollback : ". $res->userinfo, $func_name);
                return false;
            }
            
            // 仕入見出しテーブル
            $sql  = "SELECT * FROM f_stock_title_update(1, NULL, NULL, NULL, 1, ?, ?, NULL);";
            $params = array($upduser, $_SERVER['REMOTE_ADDR']);
            
            $res = $db->getRow($sql, $params);
            if (DB::isError($res) || $res[0] < 0) {
                $db->rollback();
                $db->query("ROLLBACK;");
                $log->error("f_stock_title_update#rollback : ". $res->userinfo, $func_name);
                return false;
            }
            
            // 追加した仕入れ見出しコード(stocktitlecd)取得
            $fixcode3 = $res[2];
            
            // 出品在庫明細
            $sql  = "SELECT * FROM f_ex_stock_detail_update(1, ?, ?, NULL, 9999,?, 10000, 1, 2000, 0, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,?,?, NULL);";
            $params = array($fixcode, $fixcode2, $fixcode3, $upduser, $_SERVER['REMOTE_ADDR']);
            
            $res = $db->getRow($sql, $params);
            if (DB::isError($res) || $res[0] < 0) {
                $db->rollback();
                $db->query("ROLLBACK;");
                $log->error("f_ex_stock_detail_update#rollback : ". $res->userinfo, $func_name);
                return false;
            }
        }
        
        $db->query("COMMIT;");
        $db->commit();
        $log->debug("commit", $func_name);
        $this->dbaccess->doClose($db);

        return true;
    }


    /**
     * 削除
     *
     */
    function delete($code, $shopdiv, $upduser, $updcnt) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itembasic#delete";

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


        $db = $this->dbaccess->doConnect();
        $db->autoCommit(false);
        $db->query("BEGIN;");

        if (empty($code)){
            $code = NULL;
        }

        $params = array($code, $upduser, $_SERVER['REMOTE_ADDR'], $updcnt);
        $sql  = "select * from f_item_basic_attr_update(3, ?, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ?, ?, ?);";
        $res = $db->getRow($sql, $params);

        if (DB::isError($res) || $res[0] < 0) {
            $db->rollback();
            $db->query("ROLLBACK;");
            $log->error("rollback : ". $res->userinfo, $func_name);

            return $res;
        }

        $db->commit();
        $db->query("COMMIT;");
        $log->debug("commit", $func_name);
        $this->dbaccess->doClose($db);

        return true;
    }


    /**
     * キャスト品目追加
     *
     * @param string  $itemcd アイテムコード
     * @param string  $castcd キャストコード
     * @param string  $upduser 更新ユーザ
     * @return boolean 結果ステータス
     */
    function insertCastItem($itemcd, $castcd, $disppriority, $upduser) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itembasic#insertCastItem";

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

        $db = $this->dbaccess->doConnect();
        $db->autoCommit();
        $params = array($itemcd, $castcd, $disppriority, $upduser, $_SERVER['REMOTE_ADDR']);

        $sql  = "select * from f_cast_item_update_one(1, ?, ?, ?, ?, ?, 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  $itemcd アイテムコード
     * @param string  $castcd キャストコード
     * @param string  $updcnt 更新カウンタ
     * @param string  $upduser 更新ユーザ
     * @return boolean 結果ステータス
     */
    function deleteCastItem($itemcd, $castcd, $updcnt, $upduser) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itembasic#insertCastItem";

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

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

        $sql  = "select * from f_cast_item_update_one(2, ?, ?, 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 getItemRanking($genrecd="", $recordlmt=RANKING_RECORD_LIMIT)
    {
        $params = array();
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itembasic#getItemRanking";

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        $sql  = "SELECT i.itemcd, i.itemnm, i.lowprice FROM t_ranking r";
        $sql .= " INNER JOIN t_item_basic_attr i ON i.itemcd=r.refercd AND i.delflg=FALSE";
        if (!empty($genrecd)) {
            $sql .= " INNER JOIN t_genre_item g ON i.itemcd=g.itemcd AND g.delflg=FALSE AND g.genrecd IN (!)";
            $params[] = implode(",", $genrecd);
        }
        $sql .= " WHERE r.rankingtype=2 AND r.date=CURRENT_DATE AND r.datadiv=1";
        $sql .= " GROUP BY i.itemcd, i.itemnm, i.lowprice,r.rankingrate 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 integer  $itemcd 商品コード
     * @param integer  $genrecd ジャンルコード(配列で)
     * @param string  $user 更新ユーザ
     * @return boolean 結果ステータス
    **/
    function updateGenreItem($itemcd, $genrecd, $upduser)
    {
        $func_name = "Onedepo_Db_Itembasic#updateGenreItem";
        $log =& LogFactory::getLog();

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

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

        //ジャンルコードをストアド用の配列に変更
        //  例）{1,2,3}
        $genre_str = '{';
        foreach ($genrecd as $genre) {
            $genre_str .= $genre . ',';
        }
        $genre_str = rtrim($genre_str, ',');

        $genre_str .= '}';


        $params = array($itemcd, $genre_str, $upduser, $_SERVER['REMOTE_ADDR']);

        $sql  = "SELECT * FROM f_genre_item_update(?,?,?,?)";

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

        if (DB::isError($data) || $data['errcd'] < 0) {
            $this->dbaccess->doClose($db);
            return false;
        }
        $log->debug("commit", $func_name);
        $this->dbaccess->doClose($db);
        return ture;
    }

    /**
     * web属性のランクを変更
     *
     * @param intger  $itemcd 商品コード
     * @param intger  $rank ランク
     * @param string  $user 更新ユーザ
     * @return boolean 結果ステータス
    **/
    function updateRankingRate($itemcd, $rank, $upduser)
    {
        $func_name = "Onedepo_Db_Itembasic#updateRankingRate";
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

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


        $params = array($itemcd, $rank, $upduser, $_SERVER['REMOTE_ADDR']);
        $sql  = "SELECT * FROM f_rankingrate_update(?,?,?,?)";
        $data = $db->getRow($sql, $params);
        if (DB::isError($data) || $data['errcd'] < 0) {
            $this->dbaccess->doClose($db);
            return false;
        }
        $log->debug("commit", $func_name);
        $this->dbaccess->doClose($db);
        return ture;
    }
    
    /**
     * web属性を変更
     *
     * @param intger  $itemcd 商品コード
     * @param intger  $date 公開日
     * @param intger  $rank ランク
     * @param string  $user 更新ユーザ
     * @param intger  $updcnt 更新カウンタ
     * @return boolean 結果ステータス
    **/
    function updateItemWeb($itemcd, $date, $rank, $comment, $upduser, $updcnt)
    {
        $func_name = "Onedepo_Db_Itembasic#updateItemWeb";
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

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


        $params = array($itemcd, $date, $rank, $comment, $upduser, $_SERVER['REMOTE_ADDR'], $updcnt);
        $sql  = "SELECT * FROM f_item_web_attf_update(2,?,?,?,?,?,?,?)";
        $data = $db->getRow($sql, $params);
        if (DB::isError($data) || $data['errcd'] < 0) {
            $this->dbaccess->doClose($db);
            return false;
        }
        $log->debug("commit", $func_name);
        $this->dbaccess->doClose($db);
        return ture;
    }

    /**
     * 基本属性の作品案内を変更
     *
     * @param intger  $itemcd 商品コード
     * @param string  $iteminfo 作品案内
     * @param string  $user 更新ユーザ
     * @return boolean 結果ステータス
    **/
    function updateItemInfo($itemcd, $iteminfo, $upduser)
    {
        $func_name = "Onedepo_Db_Itembasic#updateItemInfo";
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

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

        $params = array($itemcd, $iteminfo, $upduser, $_SERVER['REMOTE_ADDR']);
        $sql  = "SELECT * FROM f_item_basic_attr_update_iteminfo(?,?,?,?)";
        $data = $db->getRow($sql, $params);
        if (DB::isError($data) || $data['errcd'] < 0) {
            $this->dbaccess->doClose($db);
            return false;
        }
        $log->debug("commit", $func_name);
        $this->dbaccess->doClose($db);
        return ture;
    }
    
    function getItemWeb($itemcd)
    {
        $func_name = "Onedepo_Db_Itembasic#getItemWeb";
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $params[] = $itemcd;
        $sql  = "SELECT * FROM t_item_web_attr WHERE itemcd = ?";

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

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

        return $data;
    }
    
    function RankChangeList($to, $from, $makercd, $labelcd, $title, $stock, $image, $media, $release, $shopdiv=2)
    {
        $func_name = "Onedepo_Db_Itembasic#RankChangeList";
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        if($makercd != 0 && $labelcd != 0) {
            $params[] = $makercd;
            $params[] = $labelcd;
            $where   .= ' AND basic.makercd = ? AND basic.labelcd = ? ';
        } else if($makercd != 0) {
            $params[] = $makercd;
            $where   .= ' AND basic.makercd = ? ';
        }
        //タイトル(スペース区切りでAND検索)
        if( !empty($title) ) {
            $title = mb_convert_kana($title, "s", "UTF-8");
            $title = explode(' ',$title);
            
            foreach ($title as $value) {
                $params[] = '%'. $value .'%';
                $where   .= " AND basic.itemnm LIKE ? ";
            }
        }
        
        //商品画像登録状況
        if(!empty($image) ) {
            if($image==1)
                $where   .= " AND basic.itemcd IN (SELECT generalcd FROM t_image WHERE typediv = 1 AND delflg=FALSE GROUP BY generalcd) ";
            if($image==2)
                $where   .= " AND basic.itemcd NOT IN (SELECT generalcd FROM t_image WHERE typediv = 1 AND delflg=FALSE GROUP BY generalcd) ";
        }
        
        // 商品登録状況
        if(!empty($release) ) {
            if($release==1)
                $where   .= " AND web.releasedate <= 'now' AND web.releasedate IS NOT NULL ";
            if($release==2)
                $where   .= " AND (web.releasedate > 'now' OR web.releasedate IS NULL) ";
        }
        
        
        $sql  = "SELECT 
                   basic.itemcd, 
                   basic.itemnm, 
                   maker.makercd,
                   maker.makernm,
                   label.labelcd,
                   label.labelnm,
                   web.releasedate,
                   web.rankingrate
                 ";
        $t1   = "FROM 
                   t_item_basic_attr basic
                 INNER JOIN t_item_web_attr web
                   ON basic.itemcd=web.itemcd 
                 LEFT JOIN t_maker maker
                   ON basic.makercd = maker.makercd
                 LEFT JOIN t_label label
                   ON basic.makercd = label.makercd 
                      AND basic.labelcd = label.labelcd
                 WHERE basic.delflg=FALSE AND basic.shopdiv = " . $shopdiv
                 . $where;
        $sql .= $t1. " LIMIT ". $to  ." OFFSET ". $from;

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

        $nolimit = "SELECT count(basic.itemcd) as cnt ". $t1;
        $cnt = $db->getOne($nolimit, $params);
        $data += array("recordcount" => $cnt);

        if (DB::isError($cnt)) {
            die($cnt->getMessage());
        }

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

        return $data;
    }

    function GenreChangeList($to, $from, $makercd, $labelcd, $genrelist, $title, $shopdiv=2)
    {
        $func_name = "Onedepo_Db_Itembasic#GenreChangeList";
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        /*
        $params[] = $shopdiv;
        $where = " AND item.shopdiv = ? ";
        
        //メーカとレーベル
        if($makercd != 0 && $labelcd != 0) {
            $params[] = $makercd;
            $params[] = $labelcd;
            $where .= ' AND item.makercd = ? AND item.labelcd = ? ';
        } else if($makercd != 0) {
            $params[] = $makercd;
            $where .= ' AND item.makercd = ? ';
        }
        */
        
        //ジャンル
        foreach ($genrelist as $genrecd) {
            if(empty( $where2 )) {
                $where2 .= "AND item.itemcd IN ";
                $where2 .= '(SELECT itemcd FROM t_genre_item ';
                $where2 .= "WHERE delflg = FALSE AND genrecd IN (" .$genrecd;
            } else {
                $where2 .= ",". $genrecd;
            }
        }
        if( !empty($where2) ) {
            $where .= $where2 . ") GROUP BY itemcd) ";
        }
        //タイトル(スペース区切りでAND検索)
        if( !empty($title) ) {
            $title = mb_convert_kana($title, "s", "UTF-8");
            $title = explode(' ',$title);
            
            foreach ($title as $value) {
                $params[] = '%'. $value .'%';
                $where .= " AND item.itemnm LIKE ?";
            }
        }
        
        /*
        $sql = "SELECT DISTINCT item.itemcd,item.itemnm,maker.makernm,label.labelnm ";
        $nolimit = "SELECT count(DISTINCT item.itemcd) AS count ";
        
        $select = "FROM t_genre_item genre,t_item_basic_attr item "
               ."LEFT JOIN t_maker maker ON item.makercd = maker.makercd "
               ."LEFT JOIN t_label label ON item.makercd = label.makercd AND item.labelcd = label.labelcd "
               ."WHERE genre.delflg = FALSE AND item.delflg = FALSE AND item.itemcd = genre.itemcd "
               .$where ;
        */
        $sql = "SELECT item.itemcd,item.itemnm ";
        $nolimit = "SELECT count(item.itemcd) AS count ";
        
        $select = "FROM t_item_basic_attr item "
            ."WHERE item.delflg = FALSE "
            .$where ;
        
        $sql     .= $select . " LIMIT ". $to  ." OFFSET ". $from;
        $nolimit .= $select ;
        
        $data = $db->getAll($sql, $params);

        if (DB::isError($data)) {
            $log->error($data->userinfo, $func_name);
            die($data->getMessage());
        }

        $cnt = $db->getOne($nolimit, $params);
        $data += array("recordcount" => $cnt);

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

        return $data;
    }
    
    function SeriesChangeList($to, $from, $makercd, $labelcd, $title, $shopdiv=2)
    {
        $func_name = "Onedepo_Db_Itembasic#SeriesChangeList";
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        $params[] = $shopdiv;
        $where = " AND basic.shopdiv = ? ";
        
        //メーカとレーベル
        if($makercd != 0 && $labelcd != 0) {
            $params[] = $makercd;
            $params[] = $labelcd;
            $where .= ' AND basic.makercd = ? AND basic.labelcd = ? ';
        } else if($makercd != 0) {
            $params[] = $makercd;
            $where .= ' AND basic.makercd = ? ';
        }
        //タイトル(スペース区切りでAND検索)
        if( !empty($title) ) {
            $title = mb_convert_kana($title, "s", "UTF-8");
            $title = explode(' ',$title);
            
            foreach ($title as $value) {
                $params[] = '%'. $value .'%';
                if( !empty($where) ) $where .= " AND ";
                $where .= " basic.itemnm LIKE ?";
            }
        }
        
        $sql  = "SELECT 
                   basic.itemcd, 
                   basic.itemnm, 
                   maker.makercd,
                   maker.makernm,
                   label.labelcd,
                   label.labelnm,
                   series.seriescd,
                   series.seriesnm
                 ";
        $t1   = "FROM 
                   t_item_basic_attr basic
                 LEFT JOIN t_maker maker
                   ON basic.makercd = maker.makercd
                 LEFT JOIN t_label label
                   ON basic.makercd = label.makercd 
                      AND basic.labelcd = label.labelcd
                 LEFT JOIN t_series series
                   ON basic.makercd = series.makercd 
                      AND basic.seriescd = series.seriescd
                 WHERE basic.delflg=FALSE " . $where;
        $sql .= $t1. " LIMIT ". $to  ." OFFSET ". $from;

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

        $nolimit = "SELECT count(basic.itemcd) as cnt ". $t1;
        $cnt = $db->getOne($nolimit, $params);
        $data += array("recordcount" => $cnt);

        if (DB::isError($data)) {
            die($data->getMessage());
        }

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

        return $data;
    }
    
    
    // 作品案内一括変更での検索
    function itemInfoChangeList($to, $from, $makercd, $labelcd, $stock, $title, $shopdiv=2)
    {
        $func_name = "Onedepo_Db_Itembasic#itemInfoChangeList";
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        $params[] = $shopdiv;
        $where = ' AND basic.shopdiv = ? ';
        
        //メーカとレーベル
        if($makercd != 0 && $labelcd != 0) {
            $params[] = $makercd;
            $params[] = $labelcd;
            $where .= ' AND basic.makercd = ? AND basic.labelcd = ? ';
        } else if($makercd != 0) {
            $params[] = $makercd;
            $where .= ' AND basic.makercd = ? ';
        }
        
        // 在庫なしの商品
        if($stock==0) {
            $where .= ' AND basic.itemcd IN (SELECT stock.itemcd FROM t_ex_stock_detail stock WHERE stock.stockstate = 5) ';
        }
        
        //タイトル(スペース区切りでAND検索)
        if( !empty($title) ) {
            $title = mb_convert_kana($title, "s", "UTF-8");
            $title = explode(' ',$title);
            
            foreach ($title as $value) {
                $params[] = '%'. $value .'%';
                $where .= " AND basic.itemnm LIKE ?";
            }
        }
        
        $sql  = "SELECT 
                   basic.itemcd, 
                   basic.itemnm, 
                   maker.makercd,
                   maker.makernm,
                   label.labelcd,
                   label.labelnm,
                   basic.iteminfo
                 ";
        $t1   = "FROM 
                   t_item_basic_attr basic
                 LEFT JOIN t_maker maker
                   ON basic.makercd = maker.makercd
                 LEFT JOIN t_label label
                   ON basic.makercd = label.makercd 
                      AND basic.labelcd = label.labelcd
                 WHERE basic.delflg=FALSE ". $where;
        $sql .= $t1. " LIMIT ". $to  ." OFFSET ". $from;

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

        $nolimit = "SELECT count(basic.itemcd) as cnt ". $t1;
        $cnt = $db->getOne($nolimit, $params);
        $data += array("recordcount" => $cnt);

        if (DB::isError($data)) {
            die($data->getMessage());
        }

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

        return $data;
    }

}
?>