<?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.itemexhibit
 * @author      T.Shimano <shimano@fuco.jp>
 * @access      public
 */
class Onedepo_Db_Itemexhibit
{
    var $dbaccess;
    
    // 一覧取得
    function getItemexhibitList($to = 10, $from = 0, $where = array(), $codelist = array(), $params = array(), $sortkey = 'itemcd')
    {
        $log =& LogFactory::getLog();

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

        $limitquery = "SELECT DISTINCT s.itemcd,
                       i.itemnm       ,
                       s.stockcd      ,
                       s.structcd     ,
                       (i.itemcd || s.structcd || s.stockcd) AS exno, 
                       s.sellercd     ,
                       s.stocktitlecd ,
                       s.scdcompdate  ,
                       s.scdcompdate > 'now' AS status  ,
                       s.itemexp      ,
                       m.lastnm       ,
                       m.firstnm      ,
                       (m.lastnm || m.firstnm) AS membernm ,
                       (m.lastnmkana || m.firstnmkana) AS membernmkana, 
                       m.mailaddr     ,
                       m.mobmailaddr  ,
                       m.memberstate  ,
                       n1.name AS supplidivnm,
                       s.exhibitcount ,
                       s.condition    ,
                       n2.name AS conditionnm,
                       s.sellingprice ,
                       s.stockstate   ,
                       n3.name AS stockstatenm,
                       img.generalcd,
                       s.updcnt,
	                   CASE WHEN sum(od.count) IS NULL THEN 0 ELSE sum(od.count) END AS ordercnt
                 FROM t_ex_stock_detail s
                      LEFT JOIN t_stock_title t
                         ON t.stocktitlecd = s.stocktitlecd
                        AND t.delflg = FALSE
                      LEFT JOIN t_name n1
                         ON n1.namecd = s.stockcd
                        AND n1.namediv = 8
                      LEFT JOIN t_name n2
                         ON n2.namecd = s.condition
                        AND n2.namediv = 9 
                      LEFT JOIN t_name n3
                         ON n3.namecd = s.stockstate
                        AND n3.namediv = 10
                      LEFT JOIN t_image img
                         ON img.generalcd = s.itemcd
                        AND img.typediv = 2
                        AND img.generalsubcd = s.structcd
                        AND img.imgsizecd = 'o'
                        AND img.sellercd = s.sellercd
                        AND img.stockcd = s.stockcd
                      LEFT JOIN t_member_basic_attr m
                         ON m.membercd = s.sellercd
                      INNER JOIN t_item_basic_attr i
                         ON i.itemcd = s.itemcd
                        AND i.delflg = FALSE 
                      LEFT  JOIN t_order_detail od
                        ON s.itemcd = od.itemcd
                       AND s.structcd = od.structcd
                       AND s.stockcd = od.stockcd
                       AND od.delflg = false ";

        $limitquery .= "WHERE s.delflg = false ";
        $limitquery .= "AND   s.sellercd is not null ";
        
        $limitquery .= "  GROUP BY s.itemcd, i.itemcd
                        , i.itemnm
                        , s.stockcd
                        , s.structcd
                        , s.sellercd
                        , s.stocktitlecd
                        , s.scdcompdate
                        , s.itemexp
                        , m.lastnm
                        , m.firstnm
                        , m.lastnmkana
                        , m.firstnmkana
                        , m.mailaddr
                        , m.mobmailaddr
                        , m.memberstate
                        , n1.name
                        , s.exhibitcount
                        , s.condition
                        , n2.name
                        , s.sellingprice
                        , s.stockstate
                        , n3.name
                        , img.generalcd
                        , s.updcnt ";
        
        $qry = "";
        $whereFlg = true;

        foreach($where as $one){
            if($whereFlg){
                $qry .= " WHERE ";
                $whereFlg = false;
            } else {
                $qry .= " AND ";
            }

            $qry .= $one;

        }

        if(count($codelist) > 0){
            if($whereFlg){
                $qry .= " WHERE ";
                $whereFlg = false;
            } else {
                $qry .= " AND ";
            }

            $qry .= "( ";
            $orFlg = false;

            foreach($codelist as $codearray){
                if($orFlg){
                    $qry .= " OR ";
                }
                $qry .= "( ";
                $andFlg = false;
                foreach($codearray as $code){
                    if($andFlg){
                        $qry .= " AND ";
                    }
                    $qry .= $code;
                    $andFlg = true;
                }
                $qry .= " ) ";
                $orFlg = true;

            }
            $qry .= " ) ";
        }

        $limitquery  = "SELECT * FROM (".$limitquery.") main ";
        $limitquery .= $qry;
        if($qry==""){
            $limitquery .= " WHERE ";
        } else {
            $limitquery .= " AND ";
        }
        $limitquery .= "exhibitcount > ordercnt ";
        
        $countquery  = $limitquery;
        $limitquery .= " ORDER BY ". $sortkey;
        $limitquery .=  " LIMIT ". $to ." OFFSET ". $from;

        $log->debug($limitquery, "Onedepo_Db_Itemexhibit#getItemexhibitList");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Itemexhibit#getItemexhibitList");

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

        $countquery = "SELECT count(*) FROM ( " . $countquery . ") temp;";

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

        $data += array("recordcount" => $cnt);
        $this->dbaccess->doClose($db);

        return $data;
    }

    
    // 一覧取得(最低価格を含む) 
    function getItemexhibitList2($to = 10, $from = 0, $where = array(), $params = array(), $sortkey = 'itemcd') 
    {      
        $log =& LogFactory::getLog(); 
        $func_name = "Onedepo_Db_Itemexhibit#getItemexhibitList2";
        $log->debug("Onedepo_Db_Itemexhibitが実行されました", $func_name);

        $db = $this->dbaccess->doConnect(); 
        $db->setFetchMode(DB_FETCHMODE_ASSOC); 
     
        $limitquery = "SELECT DISTINCT s.itemcd, i.itemnm, s.stockcd, s.structcd, ". 
          "s.sellercd, s.stocktitlecd, s.scdcompdate, s.scdcompdate > 'now' AS status, ". 
          "s.itemexp, ".
          "s.exhibitcount - COALESCE(o.ocount, 0) AS exhibitcount, ". 
          "s.condition, s.sellingprice, s.souryouprc, ". 
          "s.stockstate, s.delflg, s.adddate, s.updcnt, i.lowprice, o.ocount, s_a.membercd ". 
          "FROM t_ex_stock_detail s ". 
          "LEFT JOIN t_stock_title t ON t.stocktitlecd = s.stocktitlecd ".
          "AND t.delflg = FALSE ". 
          "LEFT JOIN (SELECT o.itemcd, o.structcd, o.stockcd, SUM(o.count) AS ocount ".
          "FROM t_order_detail o WHERE o.delflg = FALSE ".
          "GROUP BY o.itemcd, o.structcd, o.stockcd) o ".
          "ON o.itemcd = s.itemcd AND o.structcd = s.structcd AND o.stockcd = s.stockcd ".
          "INNER JOIN t_item_basic_attr i ON i.itemcd = s.itemcd AND i.delflg = FALSE ".  
          "LEFT JOIN t_seller_item s_i ON i.itemcd = s_i.itemcd AND s_i.delflg = FALSE ". 
          "LEFT JOIN t_seller_attr s_a ON s_i.sellercd = s_a.membercd AND s_a.delflg = FALSE "; 
        $qry = ""; 
        foreach ($where as $key => $val) { 
            if (is_array($val)) { 
                if ($key==0)    $qry .= " WHERE"; 
                else            $qry .= " OR"; 
 
                foreach($val as $k => $v) { 
                    if ($k > 0) $qry .= " AND"; 
                    $qry .= " $v"; 
                } 
            } 
        } 
 
        $limitquery .= $qry." AND s.exhibitcount - COALESCE(o.ocount, 0) > 0"; 
        $countquery  = $limitquery;
        $limitquery .= " ORDER BY ". $sortkey; 
        $limitquery .=  " LIMIT ". $to ." OFFSET ". $from; 
         
        $log->debug($limitquery, $func_name); 
        $p = print_r($params, true); 
        $log->debug($p, $func_name); 
         
        $data = $db->getAll($limitquery, $params); 
        if (DB::isError($data)) { 
            die($data->getMessage()); 
        } 

        $countquery = "SELECT count(*) FROM ( " . $countquery . ") temp;";

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

        $data += array("recordcount" => $cnt);
         
        $this->dbaccess->doClose($db); 
     
        return $data; 
    } 

    // 一件取得
    function getOneItemexhibit($itemcd, $structcd, $stockcd, $sellercd)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itemexhibit#getOneItemexhibit";
        $log->debug("Onedepo_Db_Itemexhibitが実行されました", $func_name);

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

        $sql  = "SELECT DISTINCT s.itemcd ,
                       i.itemnm       ,
                       s.stockcd      ,
                       s.structcd     ,
                       s.sellercd     ,
                       s.stocktitlecd ,
                       s.scdcompdate  ,
                       s.sellercd     ,
                       s.itemexp      ,
                       m.lastnm       ,
                       m.firstnm      ,
                       n1.name AS supplidivnm,
                       s.exhibitcount ,
                       s.condition    ,
                       n2.name AS conditionnm,
                       s.sellingprice ,
                       s.souryouprc ,
                       s.stockstate   ,
                       s.itemexp      ,
                       st.makerprice  ,
                       n3.name AS stockstatenm,
                       st.variationnm,
                       st.makeritemcd,
                       st.salestartdate,
                       img.generalcd,
                       s.updcnt
                 FROM t_ex_stock_detail s
                      LEFT JOIN t_stock_title t
                         ON t.stocktitlecd = s.stocktitlecd
                        AND t.delflg = FALSE
                      LEFT JOIN t_name n1
                         ON n1.namecd = s.stockcd
                        AND n1.namediv = 8
                      LEFT JOIN t_name n2
                         ON n2.namecd = s.condition
                        AND n2.namediv = 9 
                      LEFT JOIN t_name n3
                         ON n3.namecd = s.stockstate
                        AND n3.namediv = 10
                      LEFT JOIN t_member_basic_attr m
                         ON m.membercd = s.sellercd
                      LEFT JOIN t_image img
                         ON img.generalcd = s.itemcd
                        AND img.typediv = 2
                        AND img.generalsubcd = s.structcd
                        AND img.imgsizecd = 'o'
                        AND img.sellercd = s.sellercd
                        AND img.stockcd = s.stockcd
                 INNER JOIN t_item_basic_attr i
                         ON i.itemcd = s.itemcd
                        AND i.delflg = FALSE
                 INNER JOIN t_item_struct st
                         ON st.itemcd   = s.itemcd
                        AND st.structcd = s.structcd
                        AND st.delflg = FALSE
                WHERE s.delflg = FALSE
                        AND s.itemcd = ?
                        AND s.structcd = ?
                        AND s.stockcd = ?
                        AND s.sellercd = ?;";

        $sqlmin = "SELECT MIN(sellingprice) AS minprice
                 FROM t_ex_stock_detail
                 WHERE     delflg = FALSE AND stockstate=3
                 AND itemcd = ?
                 AND structcd = ?;";
                     
        $params = array($itemcd, $structcd, $stockcd, $sellercd);

        //$log->debug($sql, "Onedepo_Db_Itemexhibit#getOneItemexhibit");
        //$p = print_r($params, true);
        //$log->debug($p, "Onedepo_Db_Itemexhibit#getOneItemexhibit");

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

        if (empty($data)) {
            return false;
        }

        $params = array($itemcd, $structcd);
        $minprice = $db->getAll($sqlmin, $params);
        if (DB::isError($minprice)) {
            die($minprice->getMessage());
        }

        $data[0]["minprice"] = $minprice[0]["minprice"];

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

        return $data;
    }
    

    // 最低価格取得
    function getMinPrice($itemcd, $structcd, $stockcd)
    {
                      
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itemexhibit#getMinPrice";
        $log->debug("Onedepo_Db_Itemexhibitが実行されました", $func_name);

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

                        
        $params = array($itemcd, $structcd, $stockcd);

        $log->debug($sql, "Onedepo_Db_Itemexhibit#getMinPrice");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Itemexhibit#getMinPrice");

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

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

        return $data;
    }

    /**
     * 販売価格変更
     *
     */
    function updatePrice($sellingprice, $souryouprc, $itemcd, $structcd,
                         $stockcd, $upduser, $updcnt)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itemexhibit#updatePrice";
        $log->debug("Onedepo_Db_Itemexhibitが実行されました", $func_name);

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

        $sql  = "UPDATE t_ex_stock_detail SET sellingprice = ?, souryouprc = ?, ".
                "updusrnm = ?, updpcnm = ? ".
                "WHERE itemcd = ? AND structcd = ? AND stockcd = ? AND updcnt = ?;";

        //$log->debug($sql, $func_name);
        //$log->debug(print_r($params, true), $func_name);
        $res = $db->getAll($sql, $params);
        //$log->debug(print_r($res->useinfo, true), $func_name);
        if (DB::isError($res) || $res < 0) {
                $log->debug("rollback userinfo=".print_r($res->userinfo, true), $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($condition, $itemexp, $exhibitcount, $sellingprice,
                    $souryouprc, $itemcd, $structcd, $stockcd, $upduser, $updcnt)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itemexhibit#update";
        $log->debug("Onedepo_Db_Itemexhibitが実行されました", $func_name);

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

        $params = array($condition, $itemexp, $exhibitcount, $sellingprice,
                        $souryouprc, $upduser, $_SERVER['REMOTE_ADDR'], 
                        $itemcd, $structcd, $stockcd, $updcnt);

        $sql  = "UPDATE t_ex_stock_detail SET condition = ?, itemexp = ?, ".
                "exhibitcount = exhibitcount + ?, sellingprice = ?, souryouprc = ?, ".
                "updusrnm = ?, updpcnm = ? ".
                "WHERE itemcd = ? AND structcd = ? AND stockcd = ? AND updcnt = ?;";

        //$log->debug($sql, $func_name);
        //$log->debug(print_r($params, true), $func_name);
        $res = $db->getAll($sql, $params);
        //$log->debug(print_r($res->useinfo, true), $func_name);
        if (DB::isError($res) || $res < 0) {
                        $log->debug("rollback userinfo=".print_r($res->userinfo, true), $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($itemcd, $structcd, $stockcd, $sellercd, $upduser, $updcnt)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itemexhibit#delete";
        $log->debug("Onedepo_Db_Itemexhibitが実行されました", $func_name);

        $w = array();  //　問い合わせに使用 
        $w[] = "s.delflg=false"; 
        $w[] = "s.itemcd=?"; 
        $w[] = "s.structcd=?"; 
        $w[] = "s.stockcd=?"; 
        $w[] = "s.sellercd=?"; 
        $p = array($itemcd, $structcd, $stockcd, $sellercd); 
        $data = $this->getItemexhibitList2(10,0, array($w), $p); 

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

        if (!empty($data[0]["ocount"])) { 
            // 削除フラグを立てるのではなく、購入可能な在庫数を0にする 
            $params = array($data[0]["exhibitcount"], $upduser, $_SERVER['REMOTE_ADDR'],  
                            $itemcd, $structcd, $stockcd, $updcnt); 

            $sql  = "UPDATE t_ex_stock_detail SET exhibitcount = exhibitcount - ?, ". 
                    "updusrnm = ?, updpcnm = ? ". 
                    "WHERE itemcd = ? AND structcd = ? AND stockcd = ? AND updcnt = ?;"; 
            $res = $db->query($sql, $params); 
        } else { 
            $params = array($itemcd, $structcd, $stockcd, $upduser, $_SERVER['REMOTE_ADDR'], $updcnt); 

            $sql  = "SELECT f_ex_stock_detail_update(3, ?, ?, ?, NULL, NULL, NULL, NULL, NULL,". 
            "NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ?, ?, ?);"; 
            $res = $db->getOne($sql, $params); 
        } 
        //$log->debug($sql, $func_name); 
        //$log->debug(print_r($params, true), $func_name); 
        $res = $db->query($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 getSelledNotSendList2($memberCd)
    {
        $log =& LogFactory::getLog();

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

        $params = array($memberCd);


        $sql  = "SELECT ot.orderno, ot.orderdate, ot.sendbuyer, mem.lastnm, mem.firstnm, ";
        $sql .= "od.orderrowno, od.itemcd, od.structcd, od.stockcd, ";
        $sql .= "ex.sellercd, item.itemnm, od.count, ex.rackid ";
        $sql .= "FROM t_order_detail od ";
        $sql .= "    LEFT JOIN t_forward_detail fd "; 
        $sql .= "        ON  od.orderno  = fd.orderno "; 
        $sql .= "        AND od.orderrowno = fd.orderrowno ";
        $sql .= "        AND fd.delflg = false ";
        $sql .= "    INNER JOIN t_order_title ot ";
        $sql .= "        ON  od.orderno = ot.orderno ";
        $sql .= "        AND ot.delflg = false ";
        $sql .= "    INNER JOIN t_ex_stock_detail ex ";
        $sql .= "        ON  od.itemcd = ex.itemcd ";
        $sql .= "        AND od.structcd = ex.structcd ";
        $sql .= "        AND od.stockcd = ex.stockcd ";
        $sql .= "        AND ex.delflg = false ";
        $sql .= "    LEFT  JOIN t_member_basic_attr mem ";
        $sql .= "        ON  ot.ordermember = mem.membercd ";
        $sql .= "        AND mem.delflg = false ";
        $sql .= "    INNER JOIN t_item_basic_attr item "; 
        $sql .= "        ON  od.itemcd = item.itemcd ";
        $sql .= "        AND item.delflg = false ";
        $sql .= "WHERE (fd.forwardstate < 4 "; 
        $sql .= "    OR  fd.orderno IS NULL) ";
        $sql .= "    AND od.delflg = false ";
        $sql .= "    AND ex.sellercd = ? ";

        $log->debug($sql, "Onedepo_Db_Itemexhibit#getSelledNotSendList");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Itemexhibit#getSelledNotSendList");

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

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

        return $data;
    }

    function getSelledNotSendList($memberCd)
    {
        $log =& LogFactory::getLog();

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

        $params = array($memberCd);


        $sql  = "SELECT ot.orderno, ot.orderdate, ot.sendbuyer, mem.lastnm, mem.firstnm, ";
        $sql .= "od.orderrowno, od.itemcd, od.structcd, od.stockcd, ";
        $sql .= "ex.sellercd, item.itemnm, od.count, ex.rackid, depo.depositstate ";
        $sql .= "FROM t_order_detail od ";
        $sql .= "    LEFT JOIN t_forward_detail fd "; 
        $sql .= "        ON  od.orderno  = fd.orderno "; 
        $sql .= "        AND od.orderrowno = fd.orderrowno ";
        $sql .= "        AND fd.delflg = false ";
        $sql .= "    INNER JOIN t_order_title ot ";
        $sql .= "        ON  od.orderno = ot.orderno ";
        $sql .= "        AND ot.delflg = false ";
        $sql .= "    INNER JOIN t_ex_stock_detail ex ";
        $sql .= "        ON  od.itemcd = ex.itemcd ";
        $sql .= "        AND od.structcd = ex.structcd ";
        $sql .= "        AND od.stockcd = ex.stockcd ";
        $sql .= "        AND ex.delflg = false ";
        $sql .= "    LEFT  JOIN t_member_basic_attr mem ";
        $sql .= "        ON  ot.ordermember = mem.membercd ";
        $sql .= "        AND mem.delflg = false ";
        $sql .= "    INNER JOIN t_item_basic_attr item "; 
        $sql .= "        ON  od.itemcd = item.itemcd ";
        $sql .= "        AND item.delflg = false ";
        $sql .= "    LEFT JOIN t_deposit depo ";
        $sql .= "        ON od.orderno = depo.orderno ";
        $sql .= "        AND depo.delflg = FALSE ";
        $sql .= "WHERE (fd.forwardstate < 4 "; 
        $sql .= "    OR  fd.orderno IS NULL) ";
        $sql .= "    AND od.delflg = false ";
        $sql .= "    AND depo.depositstate = 2 ";
        $sql .= "    AND ex.sellercd = ? ";

        $log->debug($sql, "Onedepo_Db_Itemexhibit#getSelledNotSendList2");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Itemexhibit#getSelledNotSendList2");

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

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


        return $data;
    }
    
    //発送済みにする
    function updateSendItem($checkNo,$orderRowArray)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Itemexhibit#updateSendItem";

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

        $sql  = "SELECT * FROM f_forward_detail_update (2,?,?,?,?,?,?,?);";
        $log->debug($sql, "Onedepo_Db_Itemexhibit#updateSendItem");
        
        /*foreach($orderNoArray as $orderNo => $orderRowNoArray){
            foreach($orderRowNoArray as $orderRowNo){
                
                $params = array($orderNo, $orderRowNo, date("Y-m-d"), 4, NULL, '', $_SERVER['REMOTE_ADDR']);
                $p = print_r($params, true);
                $log->debug($p, "Onedepo_Db_Itemexhibit#updateSendItem");

                $res = $db->getOne($sql, $params);
              
                if (DB::isError($res) || $res < 0) {
                    $log->error("rollback". $res->userinfo, $func_name);
                    var_dump($res);
                    $db->rollback();
                    $this->dbaccess->doClose($db);
                    return false;
                }                
            }
        }*/

        foreach($orderRowArray as $orderRowNo){
               $params = array($checkNo, $orderRowNo, date("Y-m-d"), 4, NULL, '', $_SERVER['REMOTE_ADDR']);
            $p = print_r($params, true);
            $log->debug($p, "Onedepo_Db_Itemexhibit#updateSendItem");
            
            $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 getMemberInfo($memberCd)
    {
        $log =& LogFactory::getLog();

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

        $sql  = "SELECT mailaddr,firstnm,lastnm,nicknm
                 FROM t_member_basic_attr
                 WHERE membercd = ?";

        $params = array($memberCd);

        $log->debug($sql, "Onedepo_Db_Itemexhibit#getMemberInfo");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Itemexhibit#getMemberInfo");

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

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

        return $data;
    }
    
    //注文番号から購入者の会員番号を取得
    function getMemberCd($orderno)
    {
        $log =& LogFactory::getLog();

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

        $sql  = "SELECT ordermember
                 FROM t_order_title
                 WHERE orderno = ?";

        $params = array($orderno);

        $log->debug($sql, "Onedepo_Db_Itemexhibit#getMemberCd");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Itemexhibit#getMemberCd");

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

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

        return $data;
    }
    
    //メール送信用購入者の住所取得
    function getMemberConsumer($orderNo)
    {      
        $log =& LogFactory::getLog();

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

        $sql  = "SELECT orderdate, sendbuyer, sendmailaddr,
                        sendpostalcd, sendprefcd, sendaddr1, sendaddr2,
                        sendtelno1, addressee, takkyubin 
                 FROM t_order_title 
                 WHERE orderno = ?";

        $params = array($orderNo);

        $log->debug($sql, "Onedepo_Db_Itemexhibit#getMemberCd");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Itemexhibit#getMemberCd");

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

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

        return $data;
    }
    
    //メール送信用購入者の金額
    function getPrice($orderNo)
    {      
        $log =& LogFactory::getLog();

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

        $sql  = "SELECT typediv, price
                 FROM t_order_detail 
                 WHERE orderno = ? AND typediv in (1,2,3,10,11)";

        $params = array($orderNo);

        $log->debug($sql, "Onedepo_Db_Itemexhibit#getMemberCd");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Itemexhibit#getMemberCd");

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

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

        return $data;
    }
    
    //メール送信用 県名取得
    function getPref($prefcd)
    {      
        $log =& LogFactory::getLog();

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

        $sql  = "SELECT prefnm
                 FROM t_pref 
                 WHERE prefcd = ?";

        $params = array($prefcd);

        $log->debug($sql, "Onedepo_Db_Itemexhibit#getMemberCd");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Itemexhibit#getMemberCd");

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

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

        return $data;
    }
    
    //メール送信用 し払い区分取得
    function getDepositdiv($orderNo)
    {      
        $log =& LogFactory::getLog();

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

        $sql  = "SELECT depositdiv
                 FROM t_deposit 
                 WHERE orderno= ?";

        $params = array($orderNo);

        $log->debug($sql, "Onedepo_Db_Itemexhibit#getMemberCd");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Itemexhibit#getMemberCd");

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

        $sql  = "SELECT name
                 FROM t_name 
                 WHERE namediv= 13
                 AND namecd = ?";
        
        $params = array($data);
        
        $log->debug($sql, "Onedepo_Db_Itemexhibit#getMemberCd");
        $p = print_r($params, true);
        $log->debug($p, "Onedepo_Db_Itemexhibit#getMemberCd");

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

        return $data;
    }
    
    //  ここからfuco sakai追加分    
    function getHistory($authcd,$membercd,$name,$mailaddr,$exno,$itemnm,$limit,$offset)
    {
        //過去の出品情報リストのうち、
        //受注番号、受注日、購入者、評価を取得

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();

        $sqlParams = $this->createSqlForHistory($authcd,$membercd,$name,$mailaddr,$exno,$itemnm,$limit,$offset);

        $sql = $sqlParams["sql"];
        $params = $sqlParams["param"];

        //$sql .= "ORDER BY orderdate DESC ";

        $log->debug($sql, "Onedepo_Db_Itemexhibit#getHistory");
        $log->debug(print_r($params,true), "Onedepo_Db_Itemexhibit#getHistory");
        $data = $db->getAll($sql, $params);

        if (DB::isError($data)) {
            $log->error($data->userinfo, "Onedepo_Db_Itemexhibit#getHistory");
        }

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

        return $data;
    }

    function getHistoryCount($authcd,$membercd,$name,$mailaddr,$exno,$itemnm)
    {
        //過去の出品情報リストのうち、
        //受注番号、受注日、購入者、評価を取得

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();

        $sqlParams = $this->createSqlForHistory($authcd,$membercd,$name,$mailaddr,$exno,$itemnm);

        $sql = "SELECT count(*) FROM ( ".$sqlParams["cntsql"]." ) tmp";
        $params = $sqlParams["param"];

        $log->debug($sql, "Onedepo_Db_Itemexhibit#getHistoryCount");
        $log->debug(print_r($params,true), "Onedepo_Db_Itemexhibit#getHistory");

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

        if (DB::isError($data)) {
            $log->error($data->userinfo, "Onedepo_Db_Itemexhibit#getHistoryCount");
        }

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

        return $data;
    }

    function createSqlForHistory($authcd,$membercd,$name,$mailaddr,$exno,$itemnm,$limit=0,$offset=0)
    {
        $params = array();

        // 検索条件をSQL化
        $queryParam = array();
        $sqlWhere = "";
        if (!empty($membercd) && is_numeric($membercd)){
            $sqlWhere .= "AND ordermember = ? ";
            array_push($queryParam,$membercd);
        }

        if (!empty($name)){
            $sqlWhere .= "AND (customernm LIKE ? OR customernmkana LIKE ? OR customernm2 LIKE ?) ";
            array_push($queryParam,"%$name%");
            array_push($queryParam,"%$name%");
            array_push($queryParam,"%$name%");
        }

        if (!empty($mailaddr)){
            $sqlWhere .= "AND (mailaddr = ? OR mailaddr2 = ?) ";
            array_push($queryParam,$mailaddr);
            array_push($queryParam,$mailaddr);
        }

        if (!empty($exno)){
            $sqlWhere .= "AND exno = ? ";
            array_push($queryParam, $exno);
        }

        if (!empty($itemnm)){
            $sqlWhere .= "AND (itemnm LIKE ? OR itemnmkana LIKE ?) ";
            array_push($queryParam,"%$itemnm%");
            array_push($queryParam,"%$itemnm%");
        }

        // SELECT
        $sqlSelect = "SELECT ot.orderno, ot.orderdate, ot.ordermember, ";
        $sqlSelect .= "(mb.lastnm || mb.firstnm) as customernm, ";
        $sqlSelect .= "(mb.lastnmkana || mb.firstnmkana) as customernmkana, ";
        $sqlSelect .= "(ot.sendbuyer) as customernm2, ";
        $sqlSelect .= "(ot.sendmailaddr) as mailaddr2, ";
        $sqlSelect .= "mb.mailaddr, sv.valuation, ";
        $sqlSelect .= "od.orderrowno, ";
        $sqlSelect .= "(od.itemcd || '-' || od.structcd || '-' || od.stockcd || '-' || ex.sellercd) as exno, ";
        $sqlSelect .= "od.count, ib.itemnm, st.variationnm, ib.itemnmkana ";

        // FROM句
        $sqlFrom  = "FROM t_order_detail od ";
        $sqlFrom .= "INNER JOIN t_order_title ot ";
        $sqlFrom .= "        ON ot.orderno = od.orderno ";
        $sqlFrom .= "       AND ot.delflg = false ";
        $sqlFrom .= "INNER JOIN t_ex_stock_detail ex ";
        $sqlFrom .= "        ON ex.itemcd = od.itemcd ";
        $sqlFrom .= "       AND ex.structcd = od.structcd ";
        $sqlFrom .= "       AND ex.stockcd = od.stockcd ";
        $sqlFrom .= "       AND ex.delflg = false ";
        $sqlFrom .= "INNER JOIN t_item_struct st ";
        $sqlFrom .= "        ON st.itemcd = od.itemcd ";
        $sqlFrom .= "       AND st.structcd = od.structcd ";
        $sqlFrom .= "       AND st.delflg = false ";
        $sqlFrom .= "INNER JOIN t_item_basic_attr ib ";
        $sqlFrom .= "        ON ib.itemcd = od.itemcd ";
        $sqlFrom .= "       AND ib.delflg = false ";
        $sqlFrom .= "LEFT  JOIN t_member_basic_attr mb ";
        $sqlFrom .= "        ON mb.membercd = ot.ordermember ";
        $sqlFrom .= "       AND mb.delflg = false ";
        $sqlFrom .= "LEFT  JOIN t_seller_val sv ";
        $sqlFrom .= "        ON sv.orderno = ot.orderno ";
        $sqlFrom .= "       AND sv.delflg = false ";

        // WHERE
        $sqlWhere2  = "WHERE ex.sellercd = ? ";
        $sqlWhere2 .= "AND   od.delflg = false ";

        // ORDER,LIMIT
        $sqlLimit = " ORDER BY orderdate DESC LIMIT $limit OFFSET $offset ";


        $sql  = "SELECT * FROM ( ";
        $sql .= $sqlSelect;
        $sql .= $sqlFrom;
        $sql .= $sqlWhere2;
        $sql .= ") as tmp2 ";
        $sql .= "WHERE orderno IS NOT NULL ";
        $sql .= $sqlWhere;

        $cntsql = $sql;
        // limit指定
        $sql   .= $sqlLimit;

        $params = array_merge($params, array($authcd));
        $params = array_merge($params, $queryParam);


        $ret["sql"] = $sql;
        $ret["cntsql"] = $cntsql;
        $ret["param"] = $params;

        return $ret;

    }


    function getHistoryOrderRow($orderno,$exno,$itemnm)
    {
        //過去の出品情報リストのうち、
        //出品番号、タイトル、数量を取得

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();

        $subsql  = "SELECT itm.itemnm, itm.itemnmkana, odd.orderrowno, odd.count, (odd.itemcd || odd.structcd || odd.stockcd) as excode ";
        $subsql .= "FROM t_order_detail odd, t_item_basic_attr itm ";
        $subsql .= "WHERE odd.orderno = ? ";
        $subsql .= "AND   odd.typediv = 1 ";
        $subsql .= "AND   odd.itemcd = itm.itemcd ";

        $sql  =  "SELECT * FROM ( " . $subsql . ") as tmp ";


        /*
         $sql  = "SELECT itm.itemnm, odd.orderrowno, odd.count, (exs.itemcd || exs.structcd || exs.stockcd) as excode ";
         $sql .= "FROM t_order_detail odd, t_item_basic_attr itm, t_ex_stock_detail exs ";
         $sql .= "WHERE odd.orderno = ? ";
         $sql .= "AND odd.typediv = 1 ";
         $sql .= "AND itm.itemcd = odd.itemcd ";
         $sql .= "AND itm.itemcd = exs.itemcd ";
         */
        $params = array($orderno);
        $isWhere = true;

        if (!empty($exno)){
            /*
             $item   = substr(strval($exno),0,6);
             $struct = substr(strval($exno),6,1);
             $stock  = substr(strval($exno),7,1);
             $sql .= "AND exs.itemcd = ? ";
             $sql .= "AND exs.structcd = ? ";
             $sql .= "AND exs.stockcd = ? ";
             array_push($params,$item);
             array_push($params,$struct);
             array_push($params,$stock);
             */
            $sql .= "WHERE excode = ? ";
            array_push($params, $exno);
            $isWhere = false;
        }

        if (!empty($itemnm)){
            if($isWhere){
                $sql .= "WHERE ";
            } else {
                $sql .= "AND ";
            }
            $sql .= "(itemnm LIKE ? OR itemnmkana LIKE ?) ";
            array_push($params,$itemnm);
            array_push($params,$itemnm);
        }

        $sql .= "ORDER BY orderrowno;";

        $log->debug($sql, "Onedepo_Db_Untansdetail#selectItemlist");
        $data = $db->getAll($sql,$params);

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

        return $data;
    }
    //  ここまでfuco sakai追加分

    /**
     * 入金管理
     **/
    function getReceiveManage($sellercd, $sdate, $edate, $deposit=false) {
        $data = array();
        $params = array($sdate, $edate, $sellercd);

        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $log =& LogFactory::getLog();

        $sql  = "select sum(price) as total";
        $sql .= " from v_order";
        $sql .= "  where typediv in (1,2,3,10,11) and ";
        // 未入金
        if (!$deposit) $sql .= " sellerpaydate IS NULL and orderdate >= ? and orderdate < ? and ";
        // 入金済み
        else           $sql .= " sellerpaydate >= ? and sellerpaydate < ? and ";
        $sql .= "  orderno in ";
        $sql .= "(select orderno from v_order where sellercd=?)";
        $data["total"] = $db->getOne($sql,$params);

        $sql  = "select sum(price) as tesuryou";
        $sql .= " from v_order";
        $sql .= "  where typediv in (4,5) and ";
        $sql .= " ((allrefund=1 and refunddate IS NULL and refundreqdate IS NULL) or allrefund=0) and ";
        // 未入金
        if (!$deposit) $sql .= " sellerpaydate IS NULL and orderdate >= ? and orderdate < ? and ";
        // 入金済み
        else           $sql .= " sellerpaydate >= ? and sellerpaydate < ? and ";
        $sql .= "  orderno in ";
        $sql .= "(select orderno from v_order where sellercd=?)";
        if (!$deposit) $sql .= " and sellerpaydate IS NULL";
        $prms = array($sdate, $edate, $sellercd);
        $data["tesuryou"] = $db->getOne($sql,$prms);

        // 未入金
        if (!$deposit) {
            $sql  = "select coalesce(sum(refundprice), 0) as henkin";
            $sql .= " from v_order ";
            $sql .= "  where itemcd IS NOT NULL and ";
            $sql .= "  refundreqdate >= ? and refundreqdate < ? and ";
            $sql .= "  sellerpaydate IS NULL ";
            $sql .= "  and  orderno in ";
            $sql .= "(select orderno from v_order where sellercd=?)";
            $sql .= " group by orderno";
            $h = $db->getCol($sql, 0, $params);
            $henkin = array_sum($h);
        }
        // 入金済み
        else {
            $sql  = "select sum(price) as total";
            $sql .= " from v_order";
            $sql .= "  where typediv in (7,8) and ";
            $sql .= " sellerpaydate >= ? and sellerpaydate < ? and ";
            $sql .= "  orderno in ";
            $sql .= "(select orderno from v_order where sellercd=?)";

	      /*
            $sql .= " refunddate >= ? and refunddate <= ? and ";
            $sql .= "  orderno in ";
            $sql .= "(select orderno from v_order where sellercd=?)";
            if (!$deposit) $sql .= " and refunddate IS NULL";
            $sql .= " GROUP BY itemcd";
	      */
            $henkin = $db->getOne($sql,$params);
        }
        $data["henkin"] = $henkin;

        $data["nyuukin"] = $data["total"]-$data["tesuryou"]-$data["henkin"];

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

        return $data;
    }
    /**
     * 入金管理(詳細)
     **/
    function getReceiveManageList($sellercd, $sdate, $edate, $deposit=false) {
        $data = array();
        $params = array($sdate, $edate, $sellercd, 
                        $sdate, $edate, $sellercd,
                        $sdate, $edate, $sellercd);

        $db = $this->dbaccess->doConnect();
        $log =& LogFactory::getLog();

        // 売上
        $sql  = "(select orderno, orderdate, ordermembernm, ";
        $sql .= "itemcd, structcd, stockcd, sellercd, ";
        $sql .= "case when typediv=1 then itemnm ";
        $sql .= "  when typediv=2 or typediv=3 then typedivnm end as itemnm, ";
        $sql .= "count, price, sellerpaydate";
        $sql .= " from v_order";
        $sql .= "  where typediv in (1,2,3,10,11) and";
        // 未入金
        if (!$deposit) $sql .= " sellerpaydate IS NULL and orderdate >= ? and orderdate < ? and ";
        // 入金済み
        else           $sql .= " sellerpaydate >= ? and sellerpaydate < ? and ";
        $sql .= "  orderno in ";
        $sql .= "(select orderno from v_order where sellercd=?))";
        //--
        $sql .= " union ";
        // 出品成約料、利用料
        $sql .= "(select orderno, orderdate, ordermembernm, ";
        $sql .= "itemcd, structcd, stockcd, sellercd, ";
        $sql .= "typedivnm, 0, -price, sellerpaydate";
        $sql .= " from v_order";
        $sql .= "  where typediv in (4,5) and ";
        $sql .= " ((allrefund=1 and refunddate IS NULL and refundreqdate IS NULL) or allrefund=0) and ";
        // 未入金
        if (!$deposit) $sql .= " sellerpaydate IS NULL and orderdate >= ? and orderdate < ? and ";
        // 入金済み
        else           $sql .= " sellerpaydate >= ? and sellerpaydate < ? and ";
        $sql .= "  orderno in ";
        $sql .= "(select orderno from v_order where sellercd=?))";
        //--
        $sql .= " union ";
        // 売上返金、手数料返金
        $sql .= "(select orderno, orderdate, ordermembernm, ";
        $sql .= "itemcd, structcd, stockcd, sellercd, ";
        $sql .= "typedivnm, 0, -price, sellerpaydate";
        $sql .= " from v_order";
        $sql .= "  where typediv in (7,8) and ";
        // 未入金
        if (!$deposit) $sql .= " sellerpaydate IS NULL and orderdate >= ? and orderdate < ? and ";
        // 入金済み
        else           $sql .= " sellerpaydate >= ? and sellerpaydate < ? and ";
        $sql .= "  orderno in ";
        $sql .= "(select orderno from v_order where sellercd=?))";
        //--
        // 返金
        // 未入金
        if (!$deposit) {
            $sql .= " union ";
            $sql .= "(select orderno, refundreqdate, ordermembernm, ";
            $sql .= "null, null, null, null, ";
            $sql .= "'返金', 0, -refundprice, refunddate";
            $sql .= " from v_order";
            $sql .= "  where ";
            $sql .= " refunddate IS NULL and refundreqdate >= ? and refundreqdate < ? and ";
            $sql .= "  orderno in ";
            $sql .= "(select orderno from v_order where sellercd=?)";
            $sql .= " group by orderno, refundreqdate, ordermembernm, ";
            $sql .= "refundprice, refunddate)";

            $params[] = $sdate;
            $params[] = $edate;
            $params[] = $sellercd;
        }
        //--
        $sql .= " order by orderno";
        $data = $db->getAssoc($sql,false,$params,DB_FETCHMODE_ASSOC,true);

        $log->debug ($data->userinfo , "receivelist");
        $log->debug ($sql , "receivelist");
        $log->debug (print_r($params,true), "receivelist");
        $this->dbaccess->doClose($db);

        return $data;
    }

    // バリエーション名取得
    function getVariationNm($itemcd, $structcd)
    {
        $log =& LogFactory::getLog();

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

        $sql  = "SELECT variationnm FROM t_item_struct WHERE itemcd=? AND structcd=?";
        $params = array($itemcd,$structcd);

        $data = $db->getOne($sql, $params);
        if (DB::isError($data)) {
            $log->error($data->userinfo, "Onedepo_Db_Itemexhibit#getVariationnm");
            die($data->getMessage());
        }

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

        return $data;
    }

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

        $params = array($itemcd);
        $sql = "update t_item_basic_attr SET delflg=true where itemcd=?;";

        $db = $this->dbaccess->doConnect();
        $res = $db->query($sql, $params);
        if (DB::isError($res) || !$res) {
            $log->trace("update rollback", "Onedepo_Db_Itemxhbit#delete");

            $db->rollback();
            $ret = false;
        }
        $log->trace("commit", "Onedepo_Db_Itemxhbit#delete");
        $db->commit();
        $this->dbaccess->doClose($db);
        return $ret;
    }

}
?>
