<?php
namespace App\Model;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Redis;
use Request;
use Excel;
use DB;

class OrderRefundModel extends Model
{
	protected $connection = 'order';
    protected $table = 'lie_order_refund';
    protected $primaryKey = 'refund_id';
    public $timestamps = false;

    // 获取联系人信息
    public function hasManyRefundItems()
    {
        return $this->hasMany('App\Model\OrderRefundItemsModel', 'refund_id', 'refund_id');
    }

	// 联营订单导出
	public function jointExport($request)
	{
        set_time_limit(0); 
        ini_set('memory_limit', '512M');
        
		$map = array();

        // 页面参数
        $map['order_sn']           = $request->input('order_sn', '');
        $map['sku_name']           = $request->input('sku_name', '');
        $map['time_start']         = $request->input('time_start', '');
        $map['time_end']           = $request->input('time_end', '');
        $map['apply_status']       = $request->input('apply_status', '');
        $map['order_payment_mode'] = $request->input('order_payment_mode', '');

        $list = $this->from('lie_order_refund_items as it')
                ->leftJoin('lie_order_refund as r', function($join) {
                    $join->on('it.refund_id', '=', 'r.refund_id')->where('r.refund_type', '=', 2)->where('r.order_goods_type', '=', 1);
                })
                ->leftJoin('lie_pay_log as p', 'r.order_id', '=', 'p.order_id')
                ->leftJoin('lie_user_main as u', 'r.user_id', '=', 'u.user_id')
                ->where(function ($query) use ($map) {
                    // 订单编号
                    if (!empty($map['order_sn'])) {
                        $query->where('r.order_sn', '=', $map['order_sn']);
                    }
                })
                ->where(function ($query) use ($map) {
                    // sku名称
                    if (!empty($map['sku_name'])) {
                        $query->where('it.sku_name', '=', $map['sku_name']);
                    }
                })
                ->where(function ($query) use ($map) {
                    // 创建时间
                    if (!empty($map['time_start']) && !empty($map['time_end'])) {
                        $query->whereBetween('r.create_time', [$map['time_start'], $map['time_end']]); 
                    } else if (!empty($map['time_start'])) {
                        $query->where('r.create_time', '>', $map['time_start']);
                    } else if (!empty($map['time_end'])) {
                        $query->where('r.create_time', '<', $map['time_end']); 
                    }
                })
                ->where(function ($query) use ($map) {
                    // 订单状态
                    if (!empty($map['apply_status'])) {
                        $query->whereIn('r.status', explode(',', $map['apply_status']));
                    }
                })
                ->where(function ($query) use ($map) {
                    // 支付方式
                    if (!empty($map['order_payment_mode'])) {
                        $pay_name = explode(',', $map['order_payment_mode']);
                        $order_payment_mode = [];

                        foreach ($pay_name as $v) {
                            switch ($v) {
                                case '1': $order_payment_mode[] = '微信支付';break;
                                case '2': $order_payment_mode[] = '支付宝';break;
                                case '3': $order_payment_mode[] = '银联支付(B2B)';break;
                                case '4': $order_payment_mode[] = '银联支付(B2C)';break;
                                case '5': $order_payment_mode[] = '账期支付';break;
                                case '6': $order_payment_mode[] = '京东支付';break;
                                case '7': $order_payment_mode[] = '交通银行';break;
                                case '8': $order_payment_mode[] = '恒生银行';break;
                                case '9': $order_payment_mode[] = '钱包支付';break;
                            }
                        }

                        $query->whereIn('p.pay_name', $order_payment_mode);
                    }
                })
                ->select('it.refund_rec_id', 'it.refund_id', 'it.rec_id', 'it.goods_id', 'it.goods_name', 'it.brand_id', 'it.brand_name', 'it.supplier_id', 'it.supplier_name', 'it.sku_name', 'it.goods_price', 'it.single_pre_price', 'it.refund_num', 'r.order_id', 'r.order_sn', 'r.order_goods_type', 'r.currency', 'r.pay_amount', 'r.price_fall', 'r.create_uid', 'r.status', 'r.refund_reason', 'r.create_time', 'r.refund_time', 'u.mobile', 'u.email')
                ->groupBy('it.refund_rec_id')
                ->orderBy('r.create_time', 'DESC')
                ->get()
                ->toArray();

        if (!empty($list)) {
            // 订单数据处理
            $cellData = $this->exportList($list);

            // 标题
            $headerCell = ['订单ID', '订单编号', '会员账号', '商品型号', '制造商', '供应商', '币种', '退货数量', '原始单价', '均摊后单价', '小计', '退货总额', '手动差价', '实际退货总额', '退货原因', '后台操作人', '创建时间', '退货处理状态', '退货处理时间'];  

            array_unshift($cellData, $headerCell);

            $fileName = '联营退货订单导出'.date('_YmdHis');

            Excel::create($fileName, function($excel) use ($cellData){
                $excel->sheet('退货订单导出', function($sheet) use ($cellData){
                    $sheet->rows($cellData);
                });
            })->export('xls');
        } else {
            return redirect('/prompt')->with(['message'=>"数据为空无法导出!",'url'=>$_SERVER['HTTP_REFERER'], 'jumpTime'=>3,'status'=>false]);
        }
	}

	/**
     * 导出数据处理
     * @param  [type] $order [查询出的订单数据]
     * @return [type]        [description]
     */
    public function exportList($order)
    {
        // 订单类型
        $apply_status = array(
            '-1' => '已拒绝',
            '1' => '待处理',
            '10' => '已处理',
        );

        $tmp = array();

        for ($i = 0; $i < count($order); $i++) {
            // 推送人
            if ($order[$i]['create_uid']) {
                $sales = DB::table('user_info')->where(['userId' => $order[$i]['create_uid']])->select('name')->first();
            }

            $tmp[$i]['order_id'] = $order[$i]['order_id'];
            $tmp[$i]['order_sn'] = "\t".$order[$i]['order_sn']."\t";
            $tmp[$i]['user_account'] = $order[$i]['mobile'] ? $order[$i]['mobile'] : $order[$i]['email'];

            // 自营商品名称换成商品型号
            $tmp[$i]['goods_name'] = $order[$i]['order_goods_type'] == 1 ? $order[$i]['goods_name'] : $this->getGoodsName($order[$i]['goods_id']); 
            $tmp[$i]['brand_name'] = $order[$i]['brand_name'];  
            $tmp[$i]['supplier_name'] = $order[$i]['supplier_name']; 
            $tmp[$i]['currency'] = $order[$i]['currency'] == 1 ? 'RMB' : 'USD';

            $tmp[$i]['refund_num'] = $order[$i]['refund_num']; 
            $tmp[$i]['goods_price'] = $order[$i]['goods_price'];  
            $tmp[$i]['single_pre_price'] = $order[$i]['single_pre_price'];  
            $tmp[$i]['goods_amount'] = $order[$i]['refund_num'] * $order[$i]['single_pre_price'];

            if ($i > 0 && $tmp[$i]['order_id'] == $tmp[$i-1]['order_id']) {
                $tmp[$i]['pay_amount'] = $order[$i]['pay_amount'];
                $tmp[$i]['price_fall'] = $order[$i]['price_fall'];
                $tmp[$i]['final_amount'] = $order[$i]['pay_amount'] - $order[$i]['price_fall'];
            } else {
                $tmp[$i]['pay_amount'] = '';
                $tmp[$i]['price_fall'] = '';
                $tmp[$i]['final_amount'] = '';
            }
         
            $tmp[$i]['refund_reason'] = $order[$i]['refund_reason'];
            $tmp[$i]['sale_name'] = isset($sales) ? $sales->name : '';  // 推送业务员
            $tmp[$i]['create_time'] = date('Y-m-d H:i:s', $order[$i]['create_time']); 
            $tmp[$i]['status'] = $apply_status[$order[$i]['status']];
            $tmp[$i]['refund_time'] = $order[$i]['refund_time'] ? date('Y-m-d H:i:s', $order[$i]['refund_time']) : ''; 
            
            unset($sales);     
        }

        return $tmp;
    }

    // 获取自营商品型号
    public function getGoodsName($goods_id)
    {
        $goods_info = json_decode(Redis::hget('Self_SelfGoods', $goods_id), true);
        
        return $goods_info['goods_name'];
    }

    // 自营订单导出
    public function selfExport($request)
    {
        set_time_limit(0); 
        ini_set('memory_limit', '512M');
        
        $map = array();

        // 页面参数
        $map['order_sn']           = $request->input('order_sn', '');
        $map['time_start']         = $request->input('time_start', '');
        $map['time_end']           = $request->input('time_end', '');
        $map['apply_status']       = $request->input('apply_status', '');
        $map['order_payment_mode'] = $request->input('order_payment_mode', '');

        $list = $this->from('lie_order_refund as r')
                ->leftJoin('lie_pay_log as p', 'r.order_id', '=', 'p.order_id')
                ->leftJoin('lie_user_main as u', 'r.user_id', '=', 'u.user_id')
                ->where('r.refund_type', '=', 1)
                ->where('r.order_goods_type', '=', 2)
                ->where(function ($query) use ($map) {
                    // 订单编号
                    if (!empty($map['order_sn'])) {
                        $query->where('r.order_sn', '=', $map['order_sn']);
                    }
                })
                ->where(function ($query) use ($map) {
                    // 创建时间
                    if (!empty($map['time_start']) && !empty($map['time_end'])) {
                        $query->whereBetween('r.create_time', [$map['time_start'], $map['time_end']]); 
                    } else if (!empty($map['time_start'])) {
                        $query->where('r.create_time', '>', $map['time_start']);
                    } else if (!empty($map['time_end'])) {
                        $query->where('r.create_time', '<', $map['time_end']); 
                    }
                })
                ->where(function ($query) use ($map) {
                    // 订单状态
                    if (!empty($map['apply_status'])) {
                        $query->whereIn('r.status', explode(',', $map['apply_status']));
                    }
                })
                ->where(function ($query) use ($map) {
                    // 支付方式
                    if (!empty($map['order_payment_mode'])) {
                        $pay_name = explode(',', $map['order_payment_mode']);
                        $order_payment_mode = [];

                        foreach ($pay_name as $v) {
                            switch ($v) {
                                case '1': $order_payment_mode[] = '微信支付';break;
                                case '2': $order_payment_mode[] = '支付宝';break;
                                case '3': $order_payment_mode[] = '银联支付(B2B)';break;
                                case '4': $order_payment_mode[] = '银联支付(B2C)';break;
                                case '5': $order_payment_mode[] = '账期支付';break;
                                case '6': $order_payment_mode[] = '京东支付';break;
                                case '7': $order_payment_mode[] = '交通银行';break;
                                case '8': $order_payment_mode[] = '恒生银行';break;
                                case '9': $order_payment_mode[] = '钱包支付';break;
                            }
                        }

                        $query->whereIn('p.pay_name', $order_payment_mode);
                    }
                })
                ->select('r.order_id', 'r.order_sn', 'r.order_goods_type', 'r.currency', 'r.pay_amount', 'r.price_fall', 'r.create_uid', 'r.status', 'r.refund_reason', 'r.create_time', 'r.refund_time', 'u.mobile', 'u.email')
                ->orderBy('r.create_time', 'DESC')
                ->get()
                ->toArray();

        if (!empty($list)) {
            // 订单数据处理
            $cellData = $this->selfExportList($list);

            // 标题
            $headerCell = ['订单ID', '订单编号', '会员账号', '退货原因', '后台操作人', '创建时间', '退货处理状态', '退货处理时间'];  

            array_unshift($cellData, $headerCell);

            $fileName = '自营退货订单导出'.date('_YmdHis');

            Excel::create($fileName, function($excel) use ($cellData){
                $excel->sheet('退货订单导出', function($sheet) use ($cellData){
                    $sheet->rows($cellData);
                });
            })->export('xls');
        } else {
            return redirect('/prompt')->with(['message'=>"数据为空无法导出!",'url'=>$_SERVER['HTTP_REFERER'], 'jumpTime'=>3,'status'=>false]);
        }
    }

    /**
     * 导出数据处理
     * @param  [type] $order [查询出的订单数据]
     * @return [type]        [description]
     */
    public function selfExportList($order)
    {
        // 订单类型
        $apply_status = array(
            '-1' => '已拒绝',
            '1' => '待处理',
            '10' => '已处理',
        );

        $tmp = array();

        for ($i = 0; $i < count($order); $i++) {
            // 推送人
            if ($order[$i]['create_uid']) {
                $sales = DB::table('user_info')->where(['userId' => $order[$i]['create_uid']])->select('name')->first();
            }

            $tmp[$i]['order_id'] = $order[$i]['order_id'];
            $tmp[$i]['order_sn'] = "\t".$order[$i]['order_sn']."\t";
            $tmp[$i]['user_account'] = $order[$i]['mobile'] ? $order[$i]['mobile'] : $order[$i]['email'];
            $tmp[$i]['refund_reason'] = $order[$i]['refund_reason'];
            $tmp[$i]['sale_name'] = isset($sales) ? $sales->name : '';  // 推送业务员
            $tmp[$i]['create_time'] = date('Y-m-d H:i:s', $order[$i]['create_time']); 
            $tmp[$i]['status'] = $apply_status[$order[$i]['status']];
            $tmp[$i]['refund_time'] = $order[$i]['refund_time'] ? date('Y-m-d H:i:s', $order[$i]['refund_time']) : ''; 
            
            unset($sales);     
        }

        return $tmp;
    }

    // 获取活动期间已完成退款的用户金额 (过滤掉联营账期)
    public function getJointReturnAmount($user_id, $start_time, $end_time)
    {
        return $this->from('lie_order_refund as r')
                ->leftJoin('lie_order as o', 'r.order_id', '=', 'o.order_id')
                ->where('r.user_id', $user_id)
                ->where('r.status', 10)
                ->whereBetween('r.refund_time', [$start_time, $end_time])
                ->where('o.order_pay_type', '<>', 3)
                ->select('r.user_id', 'r.currency', 'r.pay_amount', 'r.price_fall')
                ->get()
                ->toArray();
    }

}