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


/**
 * メーカーマスタテーブル関連
 *
 * @package     Onedepo.Db.Maker
 * @author      fuco h.Tominaga<tomy@fuco.jp>
 * @access      public
 */
class Onedepo_Db_Money
{
    var $dbaccess;


    /**
     * 全件取得
     *
     */
//    function getPaymentList($where, $sortkey = 'itemcd')
    function getPaymentList($termStart, $termEnd)
    {

        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Money#getPaymentAll";
        
        $db = $this->dbaccess->doConnect();
        $db->setFetchMode(DB_FETCHMODE_ASSOC);

/*
        ////  期間内の入金データ deposit  ////
        $sql  = "SELECT att.membercd, att.lastnm, att.firstnm, ";
        $sql .= "det.typediv, sum(det.price * det.count) as price ";
        
        $sql .= "FROM t_order_title tit ";
        
        //商品の金額と個数を取得するための内部結合(商品売上)
        $sql .= " INNER JOIN t_order_detail det ";
        $sql .= " ON  det.orderno = tit.orderno ";
        $sql .= " AND det.typediv = 1 ";
        $sql .= " AND det.delflg  = FALSE ";
        
        //入金済みの受注Noだけを取得する内部結合
        $sql .= " INNER JOIN t_deposit dep ";
        $sql .= " ON  dep.orderno = tit.orderno ";
        $sql .= " AND dep.depositdate >= ? ";
        $sql .= " AND dep.depositdate <  ? ";
        $sql .= " AND dep.depositstate = 2 ";
        $sql .= " AND dep.delflg       = FALSE ";
        
        //出品者のMemberCd、LastNm、FirstNmを取得するための外部結合
        $sql .= " INNER JOIN t_ex_stock_detail exs";
        $sql .= " ON  exs.itemcd   = det.itemcd ";
        $sql .= " AND exs.structcd = det.structcd ";
        $sql .= " AND exs.stockcd  = det.stockcd ";
        $sql .= " AND exs.delflg   = FALSE ";

        //出品者の姓名を取得するための外部結合
        $sql .= " INNER JOIN t_member_basic_attr att";
        $sql .= " ON  att.membercd = exs.sellercd ";
        //$sql .= " AND att.delflg   = FALSE ";
        
        $sql .= " WHERE tit.delflg = FALSE ";
        
        $sql .= " GROUP BY att.membercd, att.lastnm, att.firstnm, det.typediv ";

        
        $sql .= " UNION ";
        
        
        ////  期間内の手数料データ commision  ////
        $sql .= "SELECT att.membercd, att.lastnm, att.firstnm, ";
        $sql .= "commision.typediv, sum(commision.price) as price ";

        $sql .= "FROM t_order_title tit ";

        //商品の金額と個数を取得するための内部結合(手数料)
        $sql .= " LEFT  JOIN t_order_detail commision ";
        $sql .= " ON  commision.orderno = tit.orderno ";
        $sql .= " AND commision.typediv IN (4, 5) ";
        $sql .= " AND commision.delflg  = FALSE ";

        //入金済みの受注Noだけを取得する内部結合
        $sql .= " INNER JOIN t_deposit dep ";
        $sql .= " ON  dep.orderno = tit.orderno ";
        $sql .= " AND dep.depositdate >= ? ";
        $sql .= " AND dep.depositdate <  ? ";
        $sql .= " AND dep.depositstate = 2 ";
        $sql .= " AND dep.delflg       = FALSE ";

        //出品者のMemberCd、LastNm、FirstNmを取得するための外部結合
        $sql .= " INNER JOIN t_ex_stock_detail exs";
        $sql .= " ON  exs.itemcd   = commision.itemcd ";
        $sql .= " AND exs.structcd = commision.structcd ";
        $sql .= " AND exs.stockcd  = commision.stockcd ";
        $sql .= " AND exs.delflg   = FALSE ";

        //出品者の姓名を取得するための外部結合
        $sql .= " INNER JOIN t_member_basic_attr att";
        $sql .= " ON  att.membercd = exs.sellercd ";
        //$sql .= " AND att.delflg   = FALSE ";

        $sql .= " WHERE tit.delflg = FALSE ";

        $sql .= " GROUP BY att.membercd, att.lastnm, att.firstnm, commision.typediv ";

        $sql .= " UNION ";
        
        
        ////  期間内の返金データ refund  ////
        $sql .= "SELECT att.membercd, att.lastnm, att.firstnm, ";
        $sql .= "0 AS price, sum(ref.refundprice) as price ";
        
        $sql .= " FROM t_order_title tit ";

        //商品の金額と個数を取得するための内部結合
        $sql .= " INNER JOIN t_order_detail det ";
        $sql .= " ON  det.orderno = tit.orderno ";
        $sql .= " AND det.delflg  = FALSE ";

        //RefundPriceあるいはNULLを取得するための外部結合
        $sql .= " INNER JOIN t_refund ref ";
        $sql .= " ON  ref.orderno    = det.orderno ";
        $sql .= " AND ref.delflg     = FALSE ";

        //出品者のMemberCd、LastNm、FirstNmを取得するための外部結合
        $sql .= " INNER JOIN t_ex_stock_detail exs";
        $sql .= " ON  exs.itemcd   = det.itemcd ";
        $sql .= " AND exs.structcd = det.structcd ";
        $sql .= " AND exs.stockcd  = det.stockcd ";
        $sql .= " AND exs.delflg   = FALSE ";

        //出品者の姓名を取得するための外部結合
        $sql .= " INNER JOIN t_member_basic_attr att";
        $sql .= " ON  att.membercd = exs.sellercd ";
        //$sql .= " AND att.delflg   = FALSE ";
        
        $sql .= " WHERE tit.delflg = FALSE ";
        $sql .= " AND   ref.refunddate >= ? ";
        $sql .= " AND   ref.refunddate <  ? ";
        
        $sql .= "GROUP BY att.membercd, att.lastnm, att.firstnm, ref.orderno, price ";
        
*/
        $sql = "SELECT 
        member.membercd,
        member.lastnm,
        member.firstnm,
        detail.typediv,
        detail.price,
        detail.count
        	FROM t_order_detail detail, v_order_basic_list list, t_member_basic_attr member, t_deposit dep
        	WHERE detail.orderno IN (select detail.orderno 
        		FROM t_ex_stock_detail stock2, t_order_detail detail2
        		WHERE stock2.itemcd = detail2.itemcd 
        		AND stock2.structcd = detail2.structcd
        		AND stock2.stockcd = detail2.stockcd
        		AND stock2.sellercd IS NOT NULL
        	)
        	AND detail.delflg = false
        	AND dep.orderno = detail.orderno
        	AND ? < dep.depositdate AND dep.depositdate < ?
        	AND list.orderno = detail.orderno AND member.membercd = list.sellercd
        ORDER BY member.membercd, detail.typediv";
        
        $params = array($termStart, $termEnd);
        //$params = array($termStart, $termEnd, $termStart, $termEnd, $termStart, $termEnd);
        $data = $db->getAll($sql, $params);

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

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

        return $data;
    }
    
    /**
     * 返品依頼リスト取得
     *
     */
    function getRepaymentList($orderno = array())
    {

        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Money#getPaymentAll";

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

        $sql =  "
                SELECT  
                ref.orderno,
                ot.orderdate,
                ot.sendbuyer,
                mba.membercd as ordermembercd,
                mba.lastnm as orderlastnm,
                mba.firstnm as orderfirstnm,
                mba2.membercd as sellermembercd,
                mba2.lastnm as sellerlastnm,
                mba2.firstnm as sellerfirstnm,
                ref.refundreqdate,
                ref.refundprice,
                od.orderprice,
                ref.refundreason
                
                FROM (
                    SELECT
                    orderno,
                    refunddate,
                    refundreqdate,
                    refundprice,
                    refundreason,
                    delflg

                    FROM t_refund ref1
                    WHERE refundprice IS NOT NULL 

                    UNION

                    SELECT
                    ref2.orderno as orderno,
                    ref2.refunddate,
                    ref2.refundreqdate,
                    case 
                    when od.allprice IS NULL then od2.allprice + od3.allprice
                    else od.allprice + od3.allprice
                    END as refundprice,
                    ref2.refundreason,
                    ref2.delflg as delflg
                    FROM t_refund ref2
                    LEFT JOIN (
                        SELECT 
                        orderno,
                        SUM(price*count) as allprice
                        FROM t_order_detail t_od
                            left join t_ex_stock_detail s
                            on t_od.itemcd=s.itemcd AND t_od.structcd=s.structcd AND t_od.stockcd=s.stockcd 
                        WHERE typediv=1 AND s.sellercd IS NULL
                        GROUP BY orderno 
                    ) AS od ON ref2.orderno = od.orderno

                    LEFT JOIN (
                        SELECT 
                        orderno,
                        SUM(price) as allprice
                        FROM t_order_detail t_od
                            left join t_ex_stock_detail s
                            on t_od.itemcd=s.itemcd AND t_od.structcd=s.structcd AND t_od.stockcd=s.stockcd 
                        WHERE typediv=1 AND s.sellercd IS NOT NULL
                        GROUP BY orderno 
                    ) AS od2 ON ref2.orderno = od2.orderno

                    LEFT JOIN (
                        SELECT 
                        orderno,
                        SUM(price) as allprice
                        FROM t_order_detail t_od
                        WHERE typediv in (2,3,10,11)
                        GROUP BY orderno 
                    ) AS od3 ON ref2.orderno = od3.orderno
                    WHERE refundprice IS NULL
                    
                )as ref
                
                INNER JOIN t_order_title ot 
                ON ot.orderno = ref.orderno   
                
                LEFT JOIN t_member_basic_attr mba 
                ON mba.membercd = ot.ordermember    
                
                LEFT JOIN (
                SELECT 
                orderno,
                SUM(t_od.price*t_od.count) as orderprice 
                FROM t_order_detail t_od 
                GROUP BY orderno
                )as od  ON ref.orderno = od.orderno
                
                LEFT JOIN (
                SELECT
                orderno,
                sellercd
                FROM t_order_detail od2
                JOIN t_ex_stock_detail sd2
                ON od2.itemcd = sd2.itemcd AND od2.structcd = sd2.structcd AND od2.stockcd = sd2.stockcd
                WHERE od2.typediv = 1 
                GROUP BY orderno, sellercd) as sel on sel.orderno = ref.orderno

                LEFT JOIN t_member_basic_attr mba2 
                ON mba2.membercd = sel.sellercd 
                
                WHERE ref.delflg = FALSE 
                AND refunddate is NULL
               ";
        
        if(count($orderno) > 0){
            
            $sql .= "AND ref.orderno IN (".implode(", ", $orderno).") ";
        }
        
        $sql .= "
                GROUP BY ref.orderno, ot.orderdate, ot.sendbuyer, mba.membercd, mba.lastnm, mba.firstnm, mba2.membercd,
                          mba2.lastnm, mba2.firstnm, ref.refundreqdate, ref.refundprice, ref.refundreason, od.orderprice
                ORDER BY ref.orderno";

        $data = $db->getAll($sql);
        $log->debug($sql, $func_name);

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

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

        return $data;
    }

    /**
     * 返品依頼リスト処理
     *
     */
    function updateRepaymentList($orderno)
    {
        $log =& LogFactory::getLog();
        $func_name = "Onedepo_Db_Money#updateRepaymentList";
        $log->debug("Onedepo_Db_Moneyが実行されました", $func_name);

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

        $today = date("Y-m-d");
        foreach ( $orderno as $value) {
        	
        	// 返金リストの更新
        	$params = array($today, $value);
        
            $sql = "UPDATE t_refund SET refunddate = ? WHERE orderno = ?;";

            $res = $db->getAll($sql, $params);
            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;
            }
            
            // 情報の収集 

            $sql =  "
                SELECT  
                ref.orderno,
                ref.refundprice,
                od.orderprice,
                od.orderrowno,
                od2.sellrefund,
                od3.commissionrefund,
                od4.sellercd
                
                FROM (
                    SELECT
                    orderno,
                    refunddate,
                    refundprice,
                    delflg

                    FROM t_refund ref1
                    WHERE refundprice IS NOT NULL AND orderno = $value

                    UNION

                    SELECT
                    ref2.orderno as orderno,
                    ref2.refunddate as refunddate,
                    case 
                    when od.allprice IS NULL then od2.allprice + od3.allprice
                    else od.allprice + od3.allprice
                    END as refundprice,
                    ref2.delflg as delflg
                    FROM t_refund ref2
                    LEFT JOIN (
                        SELECT 
                        orderno,
                        SUM(price*count) as allprice
                        FROM t_order_detail t_od
                            left join t_ex_stock_detail s
                            on t_od.itemcd=s.itemcd AND t_od.structcd=s.structcd AND t_od.stockcd=s.stockcd 
                        WHERE typediv=1 AND s.sellercd IS NULL
                        GROUP BY orderno 
                    ) AS od ON ref2.orderno = od.orderno

                    LEFT JOIN (
                        SELECT 
                        orderno,
                        SUM(price) as allprice
                        FROM t_order_detail t_od
                            left join t_ex_stock_detail s
                            on t_od.itemcd=s.itemcd AND t_od.structcd=s.structcd AND t_od.stockcd=s.stockcd 
                        WHERE typediv=1 AND s.sellercd IS NOT NULL
                        GROUP BY orderno 
                    ) AS od2 ON ref2.orderno = od2.orderno

                    LEFT JOIN (
                        SELECT 
                        orderno,
                        SUM(price) as allprice
                        FROM t_order_detail t_od
                        WHERE typediv in (2,3,10,11)
                        GROUP BY orderno 
                    ) AS od3 ON ref2.orderno = od3.orderno
                    WHERE refundprice IS NULL AND ref2.orderno = $value
                    
                )as ref  
                
                LEFT JOIN (
                SELECT 
                orderno,
                SUM(t_od.price*t_od.count) as orderprice, 
                MAX(orderrowno) as orderrowno
                FROM t_order_detail t_od  
                GROUP BY orderno
                )as od  ON ref.orderno = od.orderno
                
                LEFT JOIN (
                SELECT 
                orderno,
                SUM(t_od.price*t_od.count) as sellrefund
                FROM t_order_detail t_od  
                WHERE typediv = 1 OR typediv = 2 OR typediv = 3 OR typediv = 10 OR typediv = 11
                GROUP BY orderno
                )as od2  ON ref.orderno = od2.orderno
                
                LEFT JOIN (
                SELECT 
                orderno,
                SUM(t_od.price*t_od.count) as commissionrefund 
                FROM t_order_detail t_od 
                WHERE typediv = 4 OR typediv = 5
                GROUP BY orderno
                )as od3  ON ref.orderno = od3.orderno

                LEFT JOIN (
                SELECT 
                orderno,
                sellercd
                FROM t_order_detail 
                JOIN t_ex_stock_detail 
                USING (itemcd, structcd, stockcd) 
                WHERE typediv = 1 
                GROUP BY orderno, sellercd)as od4  ON ref.orderno = od4.orderno
                
                WHERE ref.delflg = FALSE;";

            $res = $db->getAll($sql);
            
            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;
            }
            $data = $res[0];
            
            // 7:売り上げ返金追加 

            if($data["refundprice"] >= $data["orderprice"]) {
            	$sql = "INSERT INTO t_order_detail "
                      ."( "
                      ."orderno, "
                      ."orderrowno, "
                      ."typediv, "
                      ."price, "
                      ."count, "
                      ."delflg) "
                      ."VALUES( "
                      .$data["orderno"].", "
                      .($data["orderrowno"]+1).", "
                      ."7, "
                      .$data["sellrefund"].", "
                      ."1, "
                      ."'false');";
            } else {
            	$sql = "INSERT INTO t_order_detail "
                      ."( "
                      ."orderno, "
                      ."orderrowno, "
                      ."typediv, "
                      ."price, "
                      ."count, "
                      ."delflg) "
                      ."VALUES( "
                      .$data["orderno"].", "
                      .($data["orderrowno"]+1).", "
                      ."7, "
                      .$data["refundprice"].", "
                      ."1, "
                      ."'false');";
            }

            $res = $db->getAll($sql);
            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;
            }
            
            // 8:手数料
	    /*
            if($data["refundprice"] >= $data["orderprice"] && $data["sellercd"] != NULL) {

            	$sql = "INSERT INTO t_order_detail "
                  ."( "
                  ."orderno, "
                  ."orderrowno, "
                  ."typediv, "
                  ."price, "
                  ."count, "
                  ."delflg) "
                  ."VALUES( "
                  .$data["orderno"].", "
                  .($data["orderrowno"]+1).", "
                  ."8, "
                  .$data["commissionrefund"].", "
                  ."1, "
                  ."'false');";

            	$res = $db->getAll($sql);
            	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;
            	}
            }
	    */
        }
        $db->commit();
        $this->dbaccess->doClose($db);
        return true;
    }


}
?>
