<?php
namespace App\Model;

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

class OrderModel extends Model
{
    protected $connection = 'order';
    protected $table      = 'lie_order';
    protected $primaryKey = 'order_id';
    public $timestamps    = false;

	// 订单导出
	public function orderExport($request, $info)
	{
        set_time_limit(0); 
        ini_set('memory_limit', '2048M');
        
		$map = array();

        $userMainModel = new UserMainModel();
        $map['testMobile'] = $userMainModel->testMobile();

        // 页面参数
        if ($request->isMethod('get')) {
            $map['order_type']           = $request->input('order_type', '');
            $map['order_contain']        = $request->input('order_contain', '');
            $map['time_start']           = $request->input('time_start', '');
            $map['time_end']             = $request->input('time_end', '');
            $map['order_status']         = $request->input('order_status', '');
            $map['shipping_name']        = $request->input('shipping_name', '');
            $map['order_send']           = $request->input('order_send', '');
            $map['test_order']           = $request->input('test_order', '');
            $map['order_pay_type']       = $request->input('order_pay_type', '');
            $map['order_type_a']         = $request->input('order_type_a', '');
            $map['order_source_pf']      = $request->input('order_source_pf', '');
            $map['order_source_adtag']   = $request->input('order_source_adtag', '');
            $map['order_source_ptag']    = $request->input('order_source_ptag', '');
            $map['erp_order_id']         = $request->input('erp_order_id', '');
            $map['order_payment_mode']   = $request->input('order_payment_mode', '');
            $map['order_invoice_status'] = $request->input('order_invoice_status', '');
            $map['order_goods_type']     = $request->input('order_goods_type', '');
            $map['is_new']               = $request->input('is_new', '');
            $map['is_new_order']         = $request->input('is_new_order', '');
            $map['sale_type']            = $request->input('sale_type', '');
            $map['business_type']        = $request->input('business_type', '');
        }

        /* 临时添加 所有人只能查看线上订单 */
        // $map['online_order'] = 1;

        // 非竞调账号，则查看真实数据
        if ($info['role'] != 13) {
            $map['is_fake'] = 0;
        }

        if ($map['order_goods_type'] == 1) {
            $map['order_type_filter'] = in_array($info['role'], [1, 13]) ? [1, 2, 3] : [1]; // 管理员和尽调账号可以查看平台、ERP、京东订单
        } else {
            $map['order_type_filter'] = [1, 3, 4]; // 1.网站 3.京东 4.象牙宝
        } 

        $list = $this->from('lie_order_items as it')
                ->leftJoin('lie_order as o', 'it.order_id', '=', 'o.order_id')
                ->leftJoin('lie_pay_log as p', 'it.order_id', '=', 'p.order_id')
                ->leftJoin('lie_order_invoice as i', 'it.order_id', '=', 'i.order_id')
                ->leftJoin('lie_order_extend as oe', 'oe.order_id', '=', 'i.order_id')
                ->leftJoin('lie_order_address as a', function($join) {
                    $join->on('it.order_id', '=', 'a.order_id')->where('a.address_type', '=', 1);
                })
                ->leftJoin('lie_order_shipping as s', function($join) {
                    $join->on('it.order_id', '=', 's.order_id')->where('s.shipping_type', '=', 1);
                })
                ->leftJoin('lie_user_main as u', 'it.user_id', '=', 'u.user_id')
                ->leftJoin('lie_user_company as c', 'it.user_id', '=', 'c.user_id')
                ->where(function ($query) use ($map) {
                    // 查询类型
                    if (!empty($map['order_contain'])) {
                        if ($map['order_type'] == 1) { // 会员账号 
                            if (preg_match('/@/', $map['order_contain'])) {
                                $query->where('u.email', '=', $map['order_contain']);
                            } else {
                                $query->where('u.mobile', '=', $map['order_contain']);
                            }             
                        } else if ($map['order_type'] == 2) { // 型号名称
                            $query->where('it.goods_name', '=', $map['order_contain']);
                        } else if ($map['order_type'] == 3) { // 发票抬头
                            $query->where('i.tax_title', '=', $map['order_contain']);
                        } else if($map['order_type'] == 4) {
                            $query->where('o.user_id', '=', $map['order_contain']);
                        } else { // 订单编号
                            $query->where('o.order_sn', '=', $map['order_contain']);
                        }
                    }
                })
                ->where(function ($query) use ($map) {
                    // 创建时间
                    if (!empty($map['time_start']) && !empty($map['time_end'])) {
                        $query->whereBetween('o.create_time', [$map['time_start'], $map['time_end']]); 
                    } else if (!empty($map['time_start'])) {
                        $query->where('o.create_time', '>', $map['time_start']);
                    } else if (!empty($map['time_end'])) {
                        $query->where('o.create_time', '<', $map['time_end']); 
                    }
                })
                ->where(function ($query) use ($map) {
                    // 订单状态
                    if (!empty($map['order_status'])) {
                        $query->whereIn('o.status', explode(',', $map['order_status']));
                    }
                })
                ->where(function ($query) use ($map) {
                    // 配送方式
                    if (!empty($map['shipping_name'])) {
                        $query->where('o.order_shipping_type', '=', $map['shipping_name']);
                    }
                })
                ->where(function ($query) use ($map) {
                    // 订单推送业务员
                    if (!empty($map['order_send'])) {
                        $query->whereIn('o.sale_id',  explode(',', $map['order_send']));
                    }
                })
                ->where(function ($query) use ($map) {
                    // 订单类型
                    if (!empty($map['order_pay_type'])) {
                        $query->where('o.order_pay_type', '=', $map['order_pay_type']);
                    }
                })
                ->where(function ($query) use ($map) {
                    // 测试订单
                    if (empty($map['test_order'])) {            
                        $query->whereNotIn('o.user_id', $map['testMobile']);
                    } 
                })
                ->where(function ($query) use ($map) {
                    // 订单来源
                    if (!empty($map['order_source_pf'])) {
                        $order_source_pf = explode(',', $map['order_source_pf']);
                        $string = '';

                        foreach ($order_source_pf as $v) {
                            switch ($v) {
                                case '1': 
                                case '2': 
                                case '6': 
                                    $string .= "FIND_IN_SET('pf=".$v."', o.order_source) OR "; 
                                    break;
                                case '3': 
                                    $string .= "oe.order_type != 0 OR "; 
                                    break;
                                case '4': 
                                    $string .= "o.order_type = 3 OR "; 
                                    break;
                                case '5': 
                                    $string .= "o.order_type = 2 OR ";
                                    break;
                                case '7': 
                                    $string .= "o.order_type = 4 OR "; 
                                    break;
                            }
                        }

                        $query->whereRaw(rtrim($string, ' OR '));

                        // if ($map['order_source_pf'] == 1) {
                        //     $query->whereRaw("FIND_IN_SET('pf=1', o.order_source)");
                        // } else {
                        //     $query->whereRaw("FIND_IN_SET('pf=2', o.order_source)");

                        // }
                    }
                })
                ->where(function ($query) use ($map) {
                    // adtags来源
                    if (!empty($map['order_source_adtag'])) {
                        $query->where('o.order_source', 'like', '%adtag='.$map['order_source_adtag'].'%');
                    }
                })
                ->where(function ($query) use ($map) {
                    // ptag来源
                    if (!empty($map['order_source_ptag'])) {
                        $query->where('o.order_source', 'like', '%ptag='.$map['order_source_ptag'].'%');
                    }
                })
                ->where(function ($query) use ($map) {
                    if ($map['order_type_filter']) { // 自营获取线上、京东、象牙宝订单
                        $query->whereIn('o.order_type', $map['order_type_filter']); 
                    }
                })
                ->where(function ($query) use ($map) {
                    // 同步状态
                    if (!empty($map['erp_order_id'])) {
                        $query->where('o.erp_order_id', '<>', '');    
                    }
                })
                ->where(function ($query) use ($map) {
                    // 竞调数据
                    if (isset($map['is_fake'])) {
                        $query->where('o.is_type', '=', $map['is_fake']);
                    }
                })
                ->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);
                    }
                })
                ->where(function ($query) use ($map) {
                    // 发票类型
                    if (!empty($map['order_invoice_status'])) {
                        $query->where('i.inv_type', '=', $map['order_invoice_status']);
                    }
                })
                ->where(function ($query) use ($map) {
                    // 是否为新用户
                    if ($map['is_new'] != '') {
                        $query->where('u.is_new', '=', $map['is_new']);
                    }
                })
                ->where(function ($query) use ($map) {
                    // 是否新订单
                    if ($map['is_new_order'] != '') {
                        $query->where('oe.is_new', '=', $map['is_new_order']);
                    }
                })
                ->where(function ($query) use ($map) {
                    // 自营其他业务类型
                    if ($map['business_type'] != '') {
                        $query->where('oe.order_type', '=', 3)->whereIn('oe.business_type', explode(',', $map['business_type']));
                    }
                })
                ->where(function ($query) use ($map) {
                    // 销售类型
                    if ($map['sale_type'] != '') {
                        $query->where('o.sale_type', '=', $map['sale_type']);
                    }
                })
                ->where('it.status', '<>', -1)
                ->where('o.order_goods_type', '=', $map['order_goods_type'])
                ->select('it.goods_id', 'it.goods_name', 'it.goods_number', 'it.goods_price', 'it.single_pre_price', 'it.brand_name', 'it.supplier_name', 'o.order_id', 'o.order_sn', 'o.order_type', 'o.order_pay_type', 'o.order_goods_type', 'o.order_source', 'o.create_time', 'o.status', 'o.order_amount', 'o.sale_type', 'o.currency', 'o.sale_id', 'o.cancel_reason', 'i.tax_title', 'i.inv_type', 'i.invoice_status', 'i.tax_title', 'i.company_address', 'i.company_phone', 'i.tax_no', 'i.bank_name', 'i.bank_account', 'a.consignee', 'a.address', 's.status as shipping_status', 'u.user_id', 'u.mobile', 'u.email', 'u.client_source', 'u.is_new', 'u.is_test', 'c.com_name', 'oe.send_remark', 'oe.is_new as is_new_order', 'oe.business_type', 'oe.jd_order_id', 'oe.exchange_rate')
                ->groupBy('it.rec_id')
                ->orderBy('o.create_time', 'DESC')
                ->get()
                ->toArray();

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

            // 标题
            if ($map['order_goods_type'] == 1) {
                $headerCell = ['订单ID', '订单编号', '京东订单编号', '会员账号', '收货人', '下单日期', '下单时间', '客户名称', '平台来源', 'SKUID', '商品型号', '制造商', '供应商', '数量', '单价', '均摊后单价', '商品小计', '币种', '客服', '商品总额', '运费', '附加费', '优惠券', '订单总额', '人民币总额', '付款类型', '订单状态', '发货状态', '收货地址', '发票类型', '发票状态', '发票抬头', '公司注册地址', '公司电话', 'adtags来源', '新用户来源', '取消原因', '推送备注', '是否为测试订单', '是否为新订单'];
            } else {
                $headerCell = ['订单ID', '订单编号', '京东订单编号', '会员账号', '收货人', '下单日期', '下单时间', '客户名称', '平台来源', 'SKUID', '商品型号', '制造商', '供应商', '数量', '单价', '均摊后单价', '商品小计', '币种', '客服', '商品总额', '运费', '附加费', '优惠券', '订单总额', '付款类型', '订单状态', '发货状态', '收货地址', '发票类型', '发票状态', '发票抬头', '公司注册地址', '公司电话', 'adtags来源', '新用户来源', '取消原因', '推送备注', '是否为测试订单', '销售类型', '业务类型'];
            }  

            array_unshift($cellData, $headerCell);

            $fileName = $map['order_goods_type'] == 1 ? '联营订单导出'.date('_YmdHis') : '自营订单导出'.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)
    {
        // 订单发货状态
        $shipping_status = array(
            '-1' => '未配送',
            '1'  => '配送中',
            '2'  => '已签',
        );

        // 发票类型
        $inv_type = array(
            '1' => '不开发票',
            '2' => '普通发票',
            '3' => '增值税专用发票',
            '4' => '增值税普通发票',
        ); 

        // 发票状态
        $invoice_status = array(
            '-1' => '待确认',
            '1'  => '已开票',
            '2'  => '已发货', 
            '3'  => '已签收',
        );

        // 用户标签
        $user_tags = array(
            '0' => '用户',
            '1' => '新用户',
            '2' => '老用户',
        );

        $tmp = array();

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

            $tmp[$i]['order_id']     = $order[$i]['order_id'];
            $tmp[$i]['order_sn']     = "\t".$order[$i]['order_sn']."\t";
            $tmp[$i]['jd_order_id']  = "\t".$order[$i]['jd_order_id']."\t";
            
            $tmp[$i]['user_account'] = $order[$i]['mobile'] ? $order[$i]['mobile'] : $order[$i]['email'];
            // $tmp[$i]['is_new']     = $order[$i]['is_new'] == 1 ? '是' : '否'; // 是否为新用户
            
            $tmp[$i]['consignee']        = isset($order[$i]['consignee']) ? $order[$i]['consignee'] : '';
            $tmp[$i]['create_time_date'] = date('Y-m-d', $order[$i]['create_time']); 
            $tmp[$i]['create_time_sec']  = date('H:i:s', $order[$i]['create_time']); 
            $tmp[$i]['com_name']         = !empty($order[$i]['tax_title']) ? $order[$i]['tax_title'] : $order[$i]['com_name']; 
            $tmp[$i]['items_source']     = $this->getOrderSource($order[$i]['order_id'], $order[$i]['order_type']);

            $tmp[$i]['goods_id']         = $order[$i]['goods_id'];

            // 自营商品名称换成商品型号
            $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]['goods_number']     = $order[$i]['goods_number']; 
            $tmp[$i]['goods_price']      = $order[$i]['goods_price'];  
            $tmp[$i]['single_pre_price'] = $order[$i]['single_pre_price'];  
            $tmp[$i]['goods_amount']     = $order[$i]['goods_number'] * $order[$i]['goods_price'];
            $tmp[$i]['currency']         = $order[$i]['currency'] == 1 ? 'RMB' : 'USD';
            $tmp[$i]['sale_name']        = isset($sales) ? $sales->name : '';  // 推送业务员
             
            if ($i > 0 && $order[$i]['order_id'] == $order[$i-1]['order_id']) {
                $tmp[$i]['goods_sum']    = '';
                $tmp[$i]['shipping_fee'] = '';
                $tmp[$i]['extra_fee']    = '';
                $tmp[$i]['coupon']       = '';
                $tmp[$i]['order_amount'] = '';   

                if ($order[$i]['order_goods_type'] == 1) {
                    $tmp[$i]['rmb_amount'] = ''; // 人民币总额  
                }
            } else {
                $tmp[$i]['goods_sum']    = $this->getOrderPrice($order[$i]['order_id'], 1);
                $tmp[$i]['shipping_fee'] = $this->getOrderPrice($order[$i]['order_id'], 3);
                $tmp[$i]['extra_fee']    = $this->getOrderPrice($order[$i]['order_id'], 2);
                $tmp[$i]['coupon']       = $this->getOrderPrice($order[$i]['order_id'], -4);
                $tmp[$i]['order_amount'] = $order[$i]['order_amount'];  

                if ($order[$i]['order_goods_type'] == 1) {
                    $tmp[$i]['rmb_amount']   = $order[$i]['currency'] == 2 ? $order[$i]['order_amount'] * $order[$i]['exchange_rate'] : $order[$i]['order_amount']; // 人民币总额  
                }
            }

            $tmp[$i]['order_type']      = !empty($order[$i]['order_pay_type']) ? Config('params.order_pay_type')[$order[$i]['order_pay_type']] : '未知';
            $tmp[$i]['order_status']    = !empty($order[$i]['status']) ? Config('params.order_status')[$order[$i]['status']] : '未知';
            $tmp[$i]['shipping_status'] = !empty($order[$i]['shipping_status']) ? $shipping_status[$order[$i]['shipping_status']] : '无发货信息'; // 发货状态
            $tmp[$i]['address']         = $order[$i]['address'];
            $tmp[$i]['inv_type']        =  $order[$i]['inv_type'] ? $inv_type[$order[$i]['inv_type']] : '未知'; // 发票类型
            $tmp[$i]['invoice_status']  = !empty($order[$i]['invoice_status']) ? $invoice_status[$order[$i]['invoice_status']] : '无发票信息'; // 发票状态
            $tmp[$i]['tax_title']       = $order[$i]['tax_title'];
            $tmp[$i]['company_address'] = $order[$i]['company_address'];
            $tmp[$i]['company_phone']   = $order[$i]['company_phone'];

            if ($i > 0 && $order[$i]['order_id'] == $order[$i-1]['order_id']) { 
                $tmp[$i]['adtags']        = '';
                $tmp[$i]['client_source'] = '';
                $tmp[$i]['cancel_reason'] = 
                $tmp[$i]['send_remark']   = '';
            } else {
                $tmp[$i]['adtags']          = $order[$i]['order_source'];
                $tmp[$i]['client_source']   = $order[$i]['client_source'];
                $tmp[$i]['cancel_reason']   = $order[$i]['cancel_reason'];
                $tmp[$i]['send_remark']     = $order[$i]['send_remark'];
            }

            $tmp[$i]['is_test'] = $order[$i]['is_test'] == 1 ? '是' : '否';

            if ($order[$i]['order_goods_type'] == 2) {
                $tmp[$i]['sale_type']     = $order[$i]['sale_type'] == 1 ? '现卖' : '预售';
                $tmp[$i]['business_type'] = $order[$i]['business_type'] ? Config('params.business_type')[$order[$i]['business_type']] : '正常订单'; // 自营其他业务类型
            } else {
                $tmp[$i]['is_new_order'] = $order[$i]['is_new_order'] == 1 ? '是' : '否'; // 是否为新订单
            }

            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 getOrderPrice($order_id, $type)
    {
        $price = DB::connection('order')->table('lie_order_price')->where('order_id', '=', $order_id)->where('price_type', '=', $type)->select('price')->first();

        return $price ? $price->price : 0;
    }

    // 获取订单来源
    public function getOrderSource($order_id, $order_type=1)
    {
        if ($order_type == 1) {
            $order = DB::connection('order')->table('lie_order_extend')->where('order_id', $order_id)->select('order_type')->first();

            if ($order) {
                switch ($order->order_type) {
                    case 1: 
                    case 2: 
                    case 3: 
                        return '后台';               
                }
            }

            $order_source = DB::connection('order')->table('lie_order')->where('order_id', $order_id)->select('order_source')->first();

            if (preg_match('/pf=1/', $order_source->order_source)) {
                $source = 'PC端';
            } else if (preg_match('/pf=2/', $order_source->order_source)) {
                $source = '移动端';
            } else if (preg_match('/pf=6/', $order_source->order_source)) {
                $source = '小程序';
            } else {
                $source = '未知';
            }

            return $source;
        } else if ($order_type == 2) {
            return 'ERP';
        } else if ($order_type == 3) {
            return '京东';
        } else if ($order_type == 4) {
            return '象牙宝';
        }

        return false;
    }
}