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


/**
 * 受注テーブル関連
 *
 * @package     Onedepo.Db.Member.Customer
 * @author      netnee T.Otogawa<tomokazu.otogawa@netnee.jp>
 * @access      public
 */
class Onedepo_Db_Order
{
    var $dbaccess;
    
    var $mailtemplate;
    var $l_mail;
    var $c_member;
    
    //検索値からの取得
    function SearchResults($where = array(), $params = array(), $sortkey, $to, $from)
    {
        $log =& LogFactory::getLog();

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


        $sql  = "SELECT * FROM v_order_basic_list"; // WHERE (sellercd IS NULL or sellercd = 9999)";

		$flg = 0;
        foreach ($where as $key => $val) {
      //if ($key==0)    $sql .= " WHERE";
      //else            $sql .= " AND";
			if ($flg)
			{
	            $sql .= " AND $val";
	        }
	        else
	        {
				$flg = 1;
	            $sql .= " WHERE $val";
	        }
        }


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

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

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

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

        return $data;
    }

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

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

        $sql  = "SELECT * FROM v_order_basic_list WHERE (sellercd IS NULL or sellercd = 9999)";

        foreach ($where as $key => $val) {
            $sql .= " AND $val";
        }

        $log->debug($sql, "Onedepo_Db_Order#NowSaleList");
        $log->debug(print_r($params,true), "Onedepo_Db_Order#NowSaleList");

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

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

        return $data;
    }

    //該当コードの詳細取得
    function Orderdetail($no,$sortkey="orderrowno")
    {
        $log =& LogFactory::getLog();

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

        $params = array($no);

        $sql  = "SELECT * ";
        $sql .= "  FROM v_order_detail_list ";
        $sql .= " WHERE orderno = ?";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sortkey);

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

        return $data;
    }

    //該当コードの詳細取得(メール用
    function OrderdetailMail($no,$sortkey="typediv",$sellercd="")
    {
        $log =& LogFactory::getLog();

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

        if (!empty($sellercd)) {
            $s  = "SELECT count(orderno) as cnt FROM v_order_detail_list ";
            $s .= " WHERE orderno=? AND sellercd=?";
            $p = array($no,$sellercd);
            $t = $db->getOne($s,$p);
            if (!$t) return;
        }

        $params = array($no);

        $sql  = "SELECT orderno, sellercd, orderdate, lastnm, firstnm, ";
        $sql .= "makeritemcd, rackid, ";
        $sql .= "sendbuyer, sendmailaddr, ";
        $sql .= "sendpostalcd, prefnm, sendaddr1, sendaddr2, sendtelno1, ";
        $sql .= "addressee, note, depostate, mailaddr, depositdiv, depositdivname, ";
        $sql .= "cardcompany, cardnumber, cardname, cardexp, ";
        $sql .= "shitennumberkey, kouzanumberkey, cvsinfo, enquiryno, ";
        $sql .= "typediv, itemnm, variationnm, orderdetailtypename, sum(count) as count, ";
        $sql .= "sum(price) as price ";
        $sql .= "  FROM v_order_detail_list ";
        $sql .= " WHERE orderno = ?";
        if (!empty($sellercd)) {
            $sql .= " AND typediv IN (1,2,3,10,11) ";
        } else {
            $sql .= " and (forwardstate<>8 and forwardstate<>9 or forwardstate is null)";
        }
        $sql .= " GROUP BY orderno, sellercd, orderdate, lastnm, firstnm, ";
        $sql .= "makeritemcd, rackid, ";
        $sql .= "sendbuyer, sendmailaddr, ";
        $sql .= "sendpostalcd, prefnm, sendaddr1, sendaddr2, sendtelno1, ";
        $sql .= "addressee, note, depostate, mailaddr, depositdiv, depositdivname, ";
        $sql .= "cardcompany, cardnumber, cardname, cardexp, ";
        $sql .= "shitennumberkey, kouzanumberkey, cvsinfo, enquiryno, ";
        $sql .= "typediv, itemnm, variationnm, orderdetailtypename";
        $sql .= " ORDER BY %s";
        $sql = sprintf($sql, $sortkey);

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

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

        return $data;
    }

    //受注一覧詳細表示用 （進捗状況 typediv順）
    function OrderdetailSortTypeDiv($no,$sortkey="orderrowno")
    {
        $log =& LogFactory::getLog();

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

        $params = array($no);

        $sql  = "SELECT * ";
        $sql .= "  FROM v_order_detail_list ";
        $sql .= " WHERE orderno = ?";
        $sql = sprintf($sql, $sortkey);

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

        return $data;
    }


    /*
    * 予約状態のオーダをアイテム毎に取得
    *
    */
    function getReserveItemList($date){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Order#getReserveItemList";

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


        $sql  = " SELECT makeritemcd, itemnm, variationnm, count(*), salestartdate ";
        $sql .= "   FROM v_order_detail_list ";
        $sql .= "  WHERE forwardstate = 1 ";      // 予約：１
        if (!empty($date)){
            $params = array(date('Y-m-d', (time() + (60 * 60 * 24 * $date))));
            $sql .= "    AND salestartdate = ? ";
        } else {
            $params = array();
            $sql .= "    AND salestartdate <= CURRENT_DATE ";
        }
        $sql .= "  GROUP BY makeritemcd, itemnm, variationnm, salestartdate ";
        $sql .= "  ORDER BY salestartdate ";


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

        return $data;
    }


    /*
    * 任意のアイテムのオーダを取得
    *
    */
    function getReserveOrderList($itemcd){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Order#getReserveOrderList";

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


        $params = array($itemcd);

        $sql  = " SELECT orderno, orderrowno, TO_CHAR(orderdate, 'YYYY-MM-DD') AS orderdate, sendbuyer, lastnm, firstnm, ordermember ";
        $sql .= "   FROM v_order_detail_list ";
        $sql .= "  WHERE makeritemcd = ? ";
        $sql .= "    AND forwardstate = 1  ";     // 予約：１
        $sql .= "  ORDER BY orderno ";


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

        return $data;
    }


    /**
    * 出荷指示待ち（在庫有・入金済）の受注データを取得
    *
    */
    function getInstructionOrderList(){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Order#getInstructionOrderList";

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


        $params = array();

        $sql  = " SELECT orderno, orderrowno, makeritemcd, itemnm, variationnm, jancd, salestartdate, count, TO_CHAR(orderdate, 'YYYY-MM-DD') AS orderdate, depositdiv, ";
        $sql .= "        takkyubin, rackid, ordermember, sendbuyer, lastnm, firstnm, addressee, sendpostalcd, prefnm, sendaddr1, sendaddr2, sendtelno1, fwd_detail_updcnt";
        $sql .= "   FROM v_order_detail_list ";
        $sql .= "  WHERE stockstate = 3 ";      // 納品（在庫）：３
        $sql .= "    AND sellercd IS NULL ";    // ショップ販売分のみ
        $sql .= "    AND (depositstate = 2  ";   // 入金済：２
        $sql .= "    OR  (depositstate = 1 AND depositdiv in (3,5))) "; // 代引は未入金
        $sql .= "    AND (forwardstate IS NULL OR forwardstate < 3)  ";   // NULL または３（出荷指示済）以下
        $sql .= "  ORDER BY orderno, orderrowno ";


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

        return $data;
    }

    /**
    * 出荷指示済の受注データを取得
    *
    */
    function getInstructionOrderEndList(){
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Order#getInstructionOrderList";

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


        $params = array();

        $sql  = " SELECT o_d.orderno, o_d.orderrowno, o_d.makeritemcd, o_d.itemnm, o_d.variationnm, o_d.jancd, o_d.salestartdate, o_d.count, TO_CHAR(o_d.orderdate, 'YYYY-MM-DD') AS orderdate, o_d.depositdiv, o_d.takkyubin, ";
        $sql .= "        o_d.rackid, o_d.ordermember, o_d.sendbuyer, o_d.lastnm, o_d.firstnm, o_d.addressee, o_d.sendpostalcd, o_d.prefnm, o_d.sendaddr1, o_d.sendaddr2, o_d.sendtelno1, o_d.fwd_detail_updcnt";
        $sql .= "   FROM v_order_detail_list o_d, v_order_basic_list o_b ";
        $sql .= "  WHERE o_d.forwardstate = 3 ";      // ３（出荷指示済）
        $sql .= "  AND o_b.sellercd IS NULL ";         //
        $sql .= "  AND o_d.orderno = o_b.orderno ";         //
        $sql .= "  ORDER BY o_d.orderno, o_d.orderrowno ";


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

        return $data;
    }




// >>>>>>>>>>>>>>>>>>>>>>>>>> 以下 FUCO さん作成分？ >>>>>>>>>>>>>>>>>>


    //該当コードの詳細取得
    function getOrderDetailForSeller($no,$sortkey="orderrowno")
    {
        $log =& LogFactory::getLog();

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

        $params = array($no);

        $sql  = "SELECT od.orderrowno,
                        od.itemcd,
                        od.structcd,
                        od.stockcd,
                        ex.sellercd,
                        od.count,
                        od.price, 
                        ib.itemnm,
                        st.variationnm ";
        $sql .= "  FROM t_order_detail od, t_item_struct st ";
        $sql .= " , t_item_basic_attr ib , t_ex_stock_detail ex ";
        $sql .= " WHERE od.orderno = ?";
        $sql .= " AND   od.typediv = 1";
        $sql .= " AND  od.itemcd   = st.itemcd ";
        $sql .= " AND  od.structcd = st.structcd ";
        $sql .= " AND   od.itemcd = ib.itemcd ";
        $sql .= " AND  od.itemcd   = ex.itemcd ";
        $sql .= " AND od.structcd = ex.structcd ";
        $sql .= " AND od.stockcd  = ex.stockcd ";
//        $sql .= " ORDER BY %s";
//        $sql = sprintf($sql, $sortkey);

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

        return $data;
    }

    //07/03/18 tominaga add ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    function select_itemcd($OrderNo, $OrderRowNo)
    {
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($OrderNo, $OrderRowNo);

        $sql  = "SELECT ItemCd, structcd";
        $sql .= " FROM t_order_detail";
        $sql .= " WHERE OrderNo = ?";
        $sql .=    " AND OrderRowNo = ?";
        $sql .=    " AND delflg = FALSE ";
        $sql .= " ORDER BY orderno";
        $sql = sprintf($sql, $params);
        $log->debug($sql, "Onedepo_Db_Order#select_itemcd");
        $data = $db->getRow($sql, $params);
        $this->dbaccess->doClose($db);
        return $data;
    }
    //^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    function select_one($membercd,$orderno,$orderrowno,$itemcd,$structcd)
    {
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($membercd,$orderno,$orderrowno,$itemcd,$structcd);

        $data = $db->getRow("SELECT a.orderdate, a.orderno, c.itemnm, d.variationnm, b.orderrowno " .
                            "FROM t_order_title a, t_order_detail b, t_item_basic_attr c, t_item_struct d " .
                            "WHERE a.ordermember=? AND a.orderno=? AND b.orderrowno=? AND b.itemcd=? AND b.structcd=? ".
                            "AND a.delflg=FALSE " .
                            "AND a.orderno=b.orderno AND b.itemcd=c.itemcd ".
                            "AND b.itemcd=d.itemcd AND b.structcd=d.structcd;", $params);
        if (DB::isError($data)) {
            die($data->getMessage());
        }

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

        return $data;
    }

    //出荷明細にデータがあるかどうかをチェック
    function checkStock($orderno,$orderrowno)
    {
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($orderno,$orderrowno);

        $sql  = "SELECT forwardstate";
        $sql .= " FROM t_forward_detail";
        $sql .= " WHERE OrderNo = ?";
        $sql .=    " AND OrderRowNo = ?";
        $sql .=    " AND delflg = FALSE ";
        $sql = sprintf($sql, $params);

        $log->debug($sql, "Onedepo_Db_Order#select_itemcd");
        $data = $db->getOne($sql, $params);
        $this->dbaccess->doClose($db);
        return $data;
    }

    function return_select($membercd,$orderno,$orderrowno,$itemcd,$structcd)
    {
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($membercd,$orderno,$orderrowno,$itemcd,$structcd);
        $data = $db->getRow("SELECT a.orderdate, a.orderno, d.itemnm, e.variationnm, b.orderrowno, c.forwardstate, c.forwarddate " .
                            "FROM t_order_title a, t_order_detail b, t_forward_detail c, t_item_basic_attr d, t_item_struct e " .
                            "WHERE a.ordermember=? AND a.orderno=? AND b.orderrowno=? AND b.itemcd=? AND b.structcd=? ".
                            "AND c.forwardstate>2 AND c.forwardstate<5 " .
                            "AND a.delflg=FALSE AND a.orderno=b.orderno AND a.orderno=c.orderno AND b.orderrowno=c.orderrowno ".
                            "AND b.itemcd=d.itemcd AND b.itemcd=e.itemcd AND b.structcd=e.structcd;", $params);
        if (DB::isError($data)) {
            die($data->getMessage());
        }

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

        return $data;
    }

    //追加 07/07/06 冨永
    function getSellerInfo($OrderNo)
    {
        $log =& LogFactory::getLog();
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

        $params = array($OrderNo);

        $sql  = "SELECT ex.sellercd, mba.lastnm, mba.firstnm ";
        $sql .= " FROM t_order_detail ord ";

        $sql .= " INNER JOIN t_ex_stock_detail ex ";
        $sql .= " ON  ord.itemcd   = ex.itemcd ";
        $sql .= " AND ord.structcd = ex.structcd ";
        $sql .= " AND ord.stockcd  = ex.stockcd ";
        $sql .= " AND ex.delflg  = FALSE ";

        $sql .= " LEFT JOIN t_member_basic_attr mba ";
        $sql .= " ON  ex.sellercd = mba.membercd ";
        $sql .= " AND mba.delflg = FALSE ";

        $sql .= " WHERE ord.orderNo = ?";
        $sql .= " AND ord.delflg = FALSE ";
        $sql = sprintf($sql, $params);
        $log->debug($sql, "Onedepo_Db_List#select_One_OrderDetail");
        $data = $db->getRow($sql, $params);
        $this->dbaccess->doClose($db);
        return $data;
    }

/*    function insert($membercd,$zip1,$zip2,$pref,$addr1,$addr2,$phone1,$phone2,$sendzip1,$sendzip2,$sendpref,$sendaddr1,$sendaddr2,$sendphone,$addressee) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        $zip                = $zip1 . "-" . $zip2;
        $sendzip            = $sendzip1 . "-" . $sendzip2;
        $phone1_notarray    = $phone1[0] . "-" . $phone1[1] . "-" . $phone1[2];
        $sendphone_notarray = $sendphone[0] . "-" . $sendphone[1] . "-" . $sendphone[2];
        $adddate            = date("Y-m-d H:i:s");
        //MemberCd（本来は会員コードを貰ってくる）の暫定的な代入
        //$membercd = mt_rand();

        //必須項目以外のnullチェックここから
        if ($addr2              == "") $addr2              = null;
        if ($phone2             == "") $phone2             = null;
        if ($sendzip            == "") $sendzip            = null;
        if ($sendpref           == "") $sendpref           = null;
        if ($sendaddr1          == "") $sendaddr1          = null;
        if ($sendaddr2          == "") $sendaddr2          = null;
        if ($sendphone_notarray == "") $sendphone_notarray = null;
        if ($addressee          == "") $addressee          = null;
        //nullチェックここまで

        $params = array($membercd,
                        $zip,
                        $pref,
                        $addr1,
                        $addr2,
                        $phone1_notarray,
                        $phone2,
                        $sendzip,
                        $sendpref,
                        $sendaddr1,
                        $sendaddr2,
                        $sendphone_notarray,
                        $addressee,
                        $adddate,
                        $_SERVER['REMOTE_ADDR']);

        $res = $db->query("insert into t_consumer_attr(" .
                          "MemberCd, PostalCd, PrefCd, Addr1, Addr2, Telno1, Telno2, " .
                          "SendPostalCd, SendPrefCd, SendAddr1, SendAddr2, SendTelno1, Addressee, " .
                          "DelFlg, AddDate, UpdUsrNm, UpdPcNm, UpdCnt) " .
                          "values(" .
                          "?, ?, ?, ?, ?, ?, ?, " .
                          "?, ?, ?, ?, ?, ?, " .
                          "false, ?, '', ?, 1);" ,$params);

                          //エラーチェック用
                          //var_dump($db);
                          //exit;

        if (DB::isError($res) || !$res) {
            $log->trace("rollback", "Onedepo_Db_Member_Consumer#insert");

            //エラーチェック用
            //echo $res->toString();

            $db->rollback();
            $ret = false;
        }
        $log->trace("commit", "Onedepo_Db_Member_Consumer#insert");

        $this->dbaccess->doClose($db);
        return $ret;
    }
*/

    function update($membercd,$orderno,$orderrowno,$itemcd) {
        $ret = true;
        $log =& LogFactory::getLog();

        $db = $this->dbaccess->doConnect();
        $db->autoCommit(false);
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        
        // 返品、キャンセル可能な受注データ
        $sql = "select count(orderno) as count
                  from v_order_detail_list
                  where typediv=1 and orderno=?
                  and (forwardstate<>8 and forwardstate<>9 
                  or forwardstate is null)";
        $cnt = $db->getOne($sql, array($orderno));
        if ($cnt-1==0) {  // 返品できるデータが他にないとき、手数料も返金
            $sql = "select sum(price) as price from v_order_detail_list
                      where typediv in (1,2,3,10,11) and (forwardstate not in (8,9) OR forwardstate IS NULL)
                      and orderno=?
                     group by orderno";
            $params = array($orderno);
            $refundprice = $db->getOne($sql, $params);
        } else {
            $sql = "select price from v_order_detail_list
                      where typediv=1 and orderno=? and orderrowno=?";
            $params = array($orderno, $orderrowno);
            $refundprice = $db->getOne($sql, $params);
        }
        //--

        //-------↓ 2007-07-09 T.Shimano@fuco fixed -------//
        // 入金の確認
        $params = array($orderno);

        $sql = "SELECT depositstate "
              ."FROM t_deposit "
              ."WHERE orderno = ? "
              ."AND delflg = false ";

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

        if (DB::isError($res) || !$res) {
            $log->trace("update rollback", "Onedepo_Db_Cancel#update");
            $db->rollback();

            $this->dbaccess->doClose($db);
            $ret = false;
            return $ret;
        }

        // 返金処理
        if($res["depositstate"] == 2) {
            $params = array($orderno,$orderrowno,$refundprice);

            $sql = "INSERT INTO t_refund ("
                  ."orderno, "
                  ."orderrowno, "
                  ."refundreqdate, "
                  ."refundprice, "
                  ."refundreason, "
                  ."delflg, "
                  ."adddate, "
                  ."upddate) "
                  ."VALUES ( "
                  ."?, "
                  ."?, "
                  ."CURRENT_DATE, "
                  ."?, "
                  ."NULL, "
                  ."FALSE, "
                  ."'now', "
                  ."'now');";
            $res   = $db->query($sql ,$params);
            if (DB::isError($res) || !$res) {
                $log->trace("update rollback", "Onedepo_Db_Return#update");

                $db->rollback();
                $this->dbaccess->doClose($db);
                $ret = false;
                return $ret;
            }
            
        }
        //-------↑ 2007-07-09 T.Shimano@fuco fixed -------//

        $params = array(9, $orderno, $orderrowno);
        $check = $this->checkStock($orderno,$orderrowno);

        if (is_null($check)) {
            //在庫商品のキャンセルの場合
            $res   = $db->query("INSERT INTO t_forward_detail(forwardstate, orderno, orderrowno) " .
                                " VALUES(?, ?, ?)" ,$params);
            if (DB::isError($res) || !$res) {
                $log->trace("update rollback", "Onedepo_Db_Cancel#update");

                $db->rollback();
                $ret = false;
            }
            
            $param["type"] = 0;
        } else {
            //予約商品のキャンセルの場合
            $res   = $db->query("update t_forward_detail " .
                                "set forwardstate=? " .
                                "where orderno=? and orderrowno=?;" ,$params);
            if (DB::isError($res) || !$res) {
                $log->trace("update rollback", "Onedepo_Db_Cancel#update");

                $db->rollback();
                $ret = false;
            }
            $param["type"] = 1;
        }

        if ($cnt-1==0) {
            $sql = "update t_order_detail set price=0
                      where typediv in (2,3,4,5,10,11)
                      and orderno=?";
            $res = $db->query($sql, array($orderno));
            if (DB::isError($res) || !$res) {
                $log->trace("update rollback", "Onedepo_Db_Cancel#update");

                $db->rollback();
                $ret = false;
            }
        }
        
        //キャンセル処理が正しく行われていればメール送信
        if($ret) { 
            //会員のメールアドレス取得
            $m = $this -> c_member -> select_one($membercd);
            $mailaddress = $m['mailaddr'];
            $param["username"] = $m["lastnm"] ." ". $m["firstnm"];

            //キャンセル商品名取得
            $sql = "select itemnm, variationnm from v_order_detail_list " 
                  ."where typediv=1 and orderno=? and orderrowno=?";
            $params = array($orderno, $orderrowno);
            $nm = $db->getRow($sql, $params);
            $param["itemname"] = $nm["itemnm"] ." ". $nm["variationnm"];

            $param["cnt"] = $cnt;
            $param["price"] = $refundprice;
            
            $from        = MAIL_FROM;
            
            //キャンセルメール送信
            $this -> l_mail -> sendFromTemplate("buy_cancel",$mailaddress, $from, $param);
        }
        $log->trace("commit", "Onedepo_Db_Cancel#update");
        $db->commit();
        $this->dbaccess->doClose($db);

        return $ret;
    }

    function return_update($membercd,$orderno,$orderrowno,$itemcd,$mainreason,$otherreason) {
        $ret = true;
        $log =& LogFactory::getLog();

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

        //-----fixed 2007-07-08 by T.Shimano@fuco ↓ -----//
        $db->autoCommit(false);
        //-----fixed 2007-07-08 by T.Shimano@fuco ↑ -----//

        // 返品、キャンセル可能な受注データ
        $sql = "select count(orderno) as count
                  from v_order_detail_list
                  where typediv=1 and orderno=?
                  and (forwardstate<>8 and forwardstate<>9
                  or forwardstate is null)";
        $cnt = $db->getOne($sql, array($orderno));
        if ($cnt-1==0) {  // 返品できるデータが他にないとき、手数料も返金
            $sql = "select sum(price) as price from v_order_detail_list
                      where typediv in (1,2,3,10,11) and (forwardstate not in (8,9) OR forwardstate IS NULL)
                      and orderno=?
                     group by orderno";
            $params = array($orderno);
            $refundprice = $db->getOne($sql, $params);
        } else {
            $sql = "select price from v_order_detail_list
                      where typediv=1 and orderno=? and orderrowno=?";
            $params = array($orderno, $orderrowno);
            $refundprice = $db->getOne($sql, $params);
        }
        //--

        $reason = $mainreason . "," . $otherreason;

        $params = array(8,
                        $reason,
                        $orderno,
                        $orderrowno);

        $res   = $db->query("update t_forward_detail " .
                            "set forwardstate=?, returnreason=? " .
                            "where orderno=? and orderrowno=?;" ,$params);
        if (DB::isError($res) || !$res) {
            $log->trace("update rollback", "Onedepo_Db_Return#update");
            $db->rollback();
            $ret = false;
        }

        //-----fixed 2007-07-08 by T.Shimano@fuco ↓ -----//
        $params = array($orderno,
                        $orderrowno,
                        $refundprice,
                        $reason);

        $sql = "INSERT INTO t_refund ("
                  ."orderno, "
                  ."orderrowno, "
                  ."refundreqdate, "
                  ."refundprice, "
                  ."refundreason, "
                  ."delflg, "
                  ."adddate, "
                  ."upddate) "
                  ."VALUES ( "
                  ."?, "
                  ."?, "
                  ."CURRENT_DATE, "
                  ."?, "
                  ."?, "
                  ."FALSE, "
                  ."'now', "
                  ."'now');";

        $res   = $db->query($sql ,$params);
        if (DB::isError($res) || !$res) {
            $log->trace("update rollback", "Onedepo_Db_Return#update");
            $db->rollback();
            $ret = false;
        }

        //-----fixed 2007-07-08 by T.Shimano@fuco ↑ -----//

        if ($cnt-1==0) {
            $sql = "update t_order_detail set price=0
                      where typediv in (2,3,4,5,10,11)
                      and orderno=?";
            $res = $db->query($sql, array($orderno));
            if (DB::isError($res) || !$res) {
                $log->trace("update rollback", "Onedepo_Db_Cancel#update");

                $db->rollback();
                $ret = false;
            }
        }
        
        //返品処理が正しく行われていればメール送信
        if($ret) {
            //会員のメールアドレス取得
            $m = $this -> c_member -> select_one($membercd);
            $mailaddress = $m['mailaddr'];
            $param["username"] = $m["lastnm"] ." ". $m["firstnm"];
            
            //返品商品名取得
            $sql = "select itemnm, variationnm from v_order_detail_list " 
                  ."where typediv=1 and orderno=? and orderrowno=?";
            $params = array($orderno, $orderrowno);
            $nm = $db->getRow($sql, $params,DB_FETCHMODE_ASSOC);

$log->debug(print_r($nm,true),"");
            $param["itemname"] = $nm["itemnm"] ." ". $nm["variationnm"];

            $param["cnt"] = $cnt;
            $param["price"] = $refundprice;
            
            $from        = MAIL_FROM;
            
            
            //返品メール送信
            $this -> l_mail -> sendFromTemplate("buy_return",$mailaddress, $from, $param);
        }
        
        $log->trace("commit", "Onedepo_Db_Return#update");
        $db->commit();
        $this->dbaccess->doClose($db);

        return $ret;
    }

    //商品別の総(注文)個数の取得
    function getSumOrderCount($Itemcd, $Structcd, $Stockcd)
    {
        $params = array($Itemcd, $Structcd, $Stockcd);

        $log =& LogFactory::getLog();

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

        $sql  = "SELECT SUM(count) ";
        $sql .= "FROM t_order_detail ";
        $sql .= "WHERE itemcd = ? ";
        $sql .= "AND structcd = ? ";
        $sql .= "AND stockcd = ? ";
        $sql .= "AND delflg = FALSE ";

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

        return $data;
    }

    //返品依頼商品一覧取得
    function getPreReturnList($sort='o_t.orderdate')
    {
        $log =& LogFactory::getLog();

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

        $sql  = "SELECT ";
        $sql .= "o_d.ORDERNO, ";
        $sql .= "o_d.orderrowno, ";
        $sql .= "o_t.orderdate, ";
        $sql .= "o_t.OrderMember, ";
        $sql .= "o_t.sendbuyer,";
        $sql .= "member.lastnm, ";
        $sql .= "member.FirstNm, ";
        $sql .= "forward.ForwardState, ";
        $sql .= "o_d.itemcd, ";
        $sql .= "o_d.structcd, ";
        $sql .= "item.itemnm, ";
        $sql .= "forward.updcnt ";

        $sql .= "FROM T_ORDER_DETAIL o_d ";
        $sql .= "INNER JOIN t_ORDER_title  o_t ON o_d.orderno = o_t.orderno ";
        $sql .= "INNER JOIN t_forward_detail forward ON o_d.orderno = forward.orderno AND o_d.orderrowno = forward.orderrowno ";
        $sql .= "LEFT  JOIN t_member_basic_attr member ON o_t.ordermember = member.membercd ";
        $sql .= "INNER JOIN t_item_basic_attr item ON o_d.itemcd = item.itemcd ";

        $sql .= "WHERE ";
        //$sql .= "o_d.orderno = forward.orderno ";
        //$sql .= "AND o_d.orderrowno = forward.orderrowno ";
        //$sql .= "AND o_d.orderno = o_t.orderno ";
        //$sql .= "AND o_t.ordermember = member.membercd ";
        //$sql .= "AND o_d.itemcd = item.itemcd ";

        $sql .= "forward.ForwardState IN (8,9) ";
        $sql .= "AND forward.returndate IS NULL ";
        $sql .= "Order By %s ";

        $sql = sprintf($sql, $sort);
        $log->debug($sql,"");
        $data = $db->getAll($sql);
        $this->dbaccess->doClose($db);

        return $data;
    }

    //返品依頼商品取得
    function getPreReturn($orderno, $orderrowno)
    {
        $log =& LogFactory::getLog();

        $params = array($orderno, $orderrowno);

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

        $sql  = "SELECT ";
        $sql .= "o_d.ORDERNO, ";
        $sql .= "o_d.orderrowno, ";
        $sql .= "o_t.orderdate, ";
        $sql .= "o_t.OrderMember, ";
        $sql .= "member.lastnm, ";
        $sql .= "member.FirstNm, ";
        $sql .= "forward.ForwardState, ";
        $sql .= "o_d.itemcd, ";
        $sql .= "o_d.structcd, ";
        $sql .= "item.itemnm ";

        $sql .= "FROM T_ORDER_DETAIL o_d, ";
        $sql .= "t_ORDER_title  o_t, ";
        $sql .= "t_forward_detail forward, ";
        $sql .= "t_member_basic_attr member, ";
        $sql .= "t_item_basic_attr item ";

        $sql .= "WHERE o_d.orderno = forward.orderno ";
        $sql .= "AND o_d.orderrowno = forward.orderrowno ";
        $sql .= "AND o_d.orderno = o_t.orderno ";
        $sql .= "AND o_t.ordermember = member.membercd ";
        $sql .= "AND o_d.itemcd = item.itemcd ";

        $sql .= "AND o_d.orderno = ? ";
        $sql .= "AND o_d.orderrowno = ? ";

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

        return $data;
    }

    //返品依頼の商品を返品済みにする。
    function updateReturndate($orderno, $orderrowno, $upduser, $updcnt) {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Order#updateReturndate";

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

        $db->autoCommit();
        $params = array($orderno,
                        $orderrowno,
                        $upduser,
                        $_SERVER['REMOTE_ADDR'],
                        $updcnt);

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

        $log->debug($sql, $func_name);
        $log->debug(print_r($params, true), $func_name);
        $res = $db->getRow($sql, $params);
        if (DB::isError($res) || $res["errcd"] < 0) {
            $log->debug($res->userinfo, $func_name);
            $log->debug("rollback", $func_name);
            $db->rollback();

            return false;
        }
        $log->debug("commit", $func_name);

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

    // バリエーション名取得
    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;
    }
}
?>
