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

/**
 * 返金関連
 *
 * @package     Onedepo.Db.Refund
 */
class Onedepo_Db_Refund
{
	var $dbaccess;
	
	function getRefundList($to, $from, $where = array(), $params = array(), $sortkey = "orderno")
	{
		$log =& LogFactory::getLog();

		$db = $this->dbaccess->doConnect();
		$db->setFetchMode(DB_FETCHMODE_ASSOC);
		
		$sql = "FROM ("
              ."    SELECT "
              ."    orderno, "
              ."    orderrowno, "
              ."    itemcd, "
              ."    structcd, "
              ."    stockcd, "
              ."    count, "
//                    ."(count*price) AS price " 変更 070705 tominaga
              ."    price "
              ."    FROM t_order_detail "
              ."    WHERE typediv = 1 "
              ."    AND delflg = false "
              .")AS od1 "
              ."LEFT JOIN ( "
              ."    SELECT  "
              ."    orderno, "
              ."    NULL as itemcd, "
              ."    NULL as structcd, "
              ."    NULL as stockcd, "
              ."    SUM(price*count) as price "
              ."    FROM t_order_detail "
              ."    WHERE typediv = 4 OR typediv = 5 "
              ."    AND delflg = false "
              ."    GROUP BY orderno "
              ." )AS od2 "
              ."ON od1.orderno = od2.orderno " 
//                 ."AND od1.itemcd = od2.itemcd "
//                 ."AND od1.structcd = od2.structcd "
              ."JOIN t_order_title ot "
              ."ON od1.orderno = ot.orderno "

              ."LEFT JOIN t_ex_stock_detail sd "
              ."ON od1.itemcd = sd.itemcd "
              ."AND od1.structcd = sd.structcd "
              ."AND od1.stockcd = sd.stockcd "

              ."LEFT JOIN t_item_basic_attr ba "
              ."ON od1.itemcd = ba.itemcd "
              ."LEFT JOIN t_item_struct st "
              ."ON od1.itemcd = st.itemcd "
              ."AND od1.structcd = st.structcd "

              ."LEFT JOIN t_member_basic_attr mb1 "
              ."ON ot.ordermember = mb1.membercd "
              ."LEFT JOIN t_refund rf "
              ."ON od1.orderno = rf.orderno "
              ."WHERE ba.delflg = false "
              ."AND rf.orderno IS NULL ";
		
		$limitquery = "SELECT od1.orderno, "                     //受注番号 
                      		."od1.orderrowno, "
                            ."od1.itemcd, "
                            ."od1.structcd, "
                            ."od1.stockcd, "
                            ."ot.orderdate, "                   //受注日 
                            ."ot.ordermember,"
                            ."(mb1.lastnm || mb1.firstnm) AS ordernm, "                     // 購入者 
                            ."ot.sendbuyer,"
                            ."(od1.itemcd || '-' || od1.structcd || '-' || od1.stockcd || '-' || sd.sellercd ) AS exno, " // 出品番号 
                            ."ba.itemnm, "                      // タイトル
                            ."st.variationnm, "                 // バリエーション名
                            ."od1.count, "                      // 数量 
                            ."od1.price, "                      // 金額 
                            ."od2.price AS comission, "         // 手数料 
                            ."sd.rackid "                       // 棚番ID 
                            .$sql;
                             
        $countquery = "SELECT COUNT(*) as cnt ".$sql;            //総数 
        $qry = "";
        foreach ($where as $key => $val) {
        	if (is_array($val)) {
        		if ($key==0)    $qry .= " AND ( ";
        		else            $qry .= " OR";

        		foreach($val as $k => $v) {
        			if ($k > 0) $qry .= " AND";
        			$qry .= " $v";
        		}
        	}
        }
        if( $qry != "")
        $qry .= ")";

        $limitquery .= $qry;
        $limitquery .= " ORDER BY ". $sortkey;
        
        if ($to > 0) {
            $limitquery .=  " LIMIT ". $to ." OFFSET ". $from;
        }

        $data = $db->getAll($limitquery, $params);
        if (DB::isError($data)) {
        	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 getOrderInfo($orderno, $sellercd)
	{
		$log =& LogFactory::getLog();

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

		$sql = "SELECT "
              ."ob.orderdate, "
              ."ob.orderno, "
              ."(mb.lastnm||mb.firstnm) as ordernm, "
              ."ob.ordermember, "
              ."ob.sendbuyer, "
              ."od.allprice, "
              ."sv.valuation, "
              ."sv.comment, "
              ."ob.sendpostalcd, "
              ."pr.prefnm, "
              ."ob.sendaddr1, "
              ."ob.sendaddr2, "
              ."ob.sendtelno1, "
              ."ob.addressee "
              ."FROM "
              ."t_order_title ob "
              ."LEFT JOIN ( "
              ."    SELECT  "
              ."    orderno, "
              ."    SUM(price) as allprice "
              ."    FROM t_order_detail t_od "
              ."    WHERE t_od.typediv IN (1, 2, 3) "//追加　07/07/10 tominaga
              ."    GROUP BY orderno "
              .") AS od "
              ."ON ob.orderno = od.orderno "
              ."LEFT JOIN ( "
              ."SELECT "
              ."orderno, "
              ."sellercd "
              ."FROM t_order_detail sod "
              ."JOIN t_ex_stock_detail ste "
              ."ON sod.itemcd = ste.itemcd AND sod.structcd = ste.structcd AND sod.stockcd = ste.stockcd "
              ."GROUP BY orderno, sellercd"
              .")as od2 ON od2.orderno = ob.orderno "
              ."LEFT JOIN t_member_basic_attr mb "
              ."ON ob.ordermember = mb.membercd "
              ."LEFT JOIN t_seller_val sv "
              ."ON ob.orderno = sv.orderno "
              ."LEFT JOIN t_pref pr "
              ."ON ob.sendprefcd = pr.prefcd "
              ."WHERE ob.delflg = false AND ob.orderno = ? AND od2.sellercd = ?";
		$params = array($orderno, $sellercd);
		$data = $db->getRow($sql, $params);
		if (DB::isError($data)) {
			die($data->getMessage());
		}


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

		return $data;
	}
	
	function insert($orderno, $sellercd, $comment,  $refundprice, $updusr)
	{
		$log =& LogFactory::getLog();
		$func_name = "Onedepo_Db_Refund#insert";
		$db = $this->dbaccess->doConnect();
		$db->autoCommit(false);
                $db->query("BEGIN;");
		$db->setFetchMode(DB_FETCHMODE_ASSOC);
		$updpc = $_SERVER['REMOTE_ADDR'];
		

                $params = array($orderno, 
                                $refundprice,
                                $comment,
                                $updusr,
                                $updpc);

   		$sql = "INSERT INTO t_refund "
                 ."(orderno,"
                 ." orderrowno, "
                 ." refundreqdate, "
                 ." refundprice, "
                 ." refundreason, "
                 ." delflg, "
                 ." adddate, "
                 ." upddate, "
                 ." updusrnm, "
                 ." updpcnm, "
                 ." updcnt) "
                 ." VALUES( "
                 ." ?, "
                 ." 0, "
                 ." 'now', "
                 ." ?, "
                 ." ?, "
                 ." false, "
                 ." 'now', "
                 ." 'now', "
                 ." ?, "
                 ." ?, "
                 ." 0) ";
           
                $res = $db->getAll($sql, $params);

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

                if (empty($refundprice)) {
                    $sql = "update t_order_detail set price=0
                              where typediv in (4,5)
                              and orderno=?";
                    $res = $db->query($sql, array($orderno));

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

	
 	
	$db->query("COMMIT;");
    	$db->commit();
    	$this->dbaccess->doClose($db);
    	return true;
    }
}
?>