seafood/app/Http/Controllers/Admin/TemplateCont...

<?php
/**
 * Created by Sublime.
 * User: Williamslife Wang
 * Date: 17/10/18
 * Time: 上午11:20
 */
namespace  App\Http\Controllers\Admin;
use App\Http\Controllers\Controller;
use App\Admin;
use Illuminate\Http\Request;
use App\Templates;
use App\TemplatesSalers;
use App\TemplatesSource;
use App\TemplatesSourceSalers;
use App\AdminRole;
use App\TemplatesLog;
use App\TemplatesBackimg;
use App\CustDetail;
use App\Order;
use App\Services\OssServices as oss;
use Illuminate\Support\Facades\DB;

class TemplateController extends Controller
{
    /**
     * @return \Illuminate\View\View
     */
    public function index(Request $request)
    {
        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }

        $offset = ($page-1) * $pageSize;

        $count = Templates::where('is_del',0)->count();
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = Templates::where('is_del',0)->orderBy('id', 'desc')->offset($offset)->limit($pageSize)->get();
        $result = json_decode(json_encode($result),true);

        foreach($result as $k=>&$v){
            # 获取销售
            $salers = TemplatesSalers::select('templates_salers.*', 'admin.realname as admin_name')->leftJoin('admin', 'admin.id', '=', 'templates_salers.admin_id')->where('templates_salers.t_id', $v['id'])->where('templates_salers.is_del', 0)->where('admin.is_use', 1)->get();
            $v['salers'] = $salers;            
            $v['salers_k'] = count($salers); 
        }

        return view('template/index', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,                   
            ]);
    }

    /**
     * @return \Illuminate\View\View
     */
    public function create()
    {
        return view('template/create');
    }

    /**
     * @param Request $request
     * @return \Illuminate\Http\RedirectResponse
     */
    public function store(Request $request)
    {
        $this->validate($request, [
            'url' => 'required|unique:templates',           
        ], [
            'url.required' => '模板链接不能为空',
            'url.unique' => '该模板链接已存在',
            
        ]);
        $template = new Templates();
        $template->url = trim($request->input('url'));
        $template->note = trim($request->input('note'));
    
        //图片上传 阿里云oss       
        if ($request->hasFile('img') && $request->file('img')->isValid()) {
            $file = $request->file('img');
            $ossClient=new oss();
            // 上传阿里云
            $file = $ossClient->upload($file->getClientOriginalExtension(), $file->getRealPath(), 'upload/seafoodPic'.date("Y-m-d",time()).'/'.date('His'));
            $img=$file['oss-request-url'];
            $template->img=str_replace("kx-youhuiquan.oss-cn-beijing.aliyuncs.com","imgs.726p.com",$img);
        }

        $template->save();
        return redirect('/admin/template/index')->with('info', '添加模板成功');
    }

    /**
     * @param $id
     * @return \Illuminate\View\View
     */
    public function edit($id)
    {
        $template = Templates::findOrFail($id);
        return view('template/edit', ['template' => $template, 'id'=>$id]);
    }

    /**
     * @param Request $request
     * @return \Illuminate\Http\RedirectResponse
     */
    public function update(Request $request)
    {
        $id = (int)$request->input('id');
        $this->validate($request, [
            'url' => 'required|unique:templates,url,'.$id.',id',           
        ], [
            'url.required' => '模板链接不能为空',
            'url.unique' => '该模板链接已存在',
            
        ]);

        $template = Templates::findOrFail($id);
        $template->url = trim($request->input('url'));
        $template->note = trim($request->input('note'));
    
        //图片上传 阿里云oss       
        if ($request->hasFile('img') && $request->file('img')->isValid()) {
            $file = $request->file('img');
            $ossClient=new oss();
            // 上传阿里云
            $file = $ossClient->upload($file->getClientOriginalExtension(), $file->getRealPath(), 'upload/seafoodPic'.date("Y-m-d",time()).'/'.date('His'));
            $img=$file['oss-request-url'];
            $template->img=str_replace("kx-youhuiquan.oss-cn-beijing.aliyuncs.com","imgs.726p.com",$img);
        }

        $template->save();
        return redirect('/admin/template/index')->with('info', '修改模板成功');
    }

    /**
     * @param $id
     * @return \Illuminate\Http\RedirectResponse
     */
    public function assignSalers(Request $request)
    {
        $s_id = (int)$request->input('id');
        $salers = $request->input('salers');
        $assign_type = (int)$request->input('assign_type');


        if( empty($salers) ){
            exit('<script>parent.layer.msg("分配失败，未选择销售!",{icon:2,time:2000}); var index=parent.layer.getFrameIndex(window.name); parent.layer.close(index);</script>');
        }


        if($assign_type == 1){
            //平均分配
            $count = count($salers);
            $weight_avg = round(1000 / $count) / 10;
        }else{
            $weights = array();
            $total = 0;
            foreach( $salers as $k=>$id ){
                $weight_id = 'weight'.$id;
                $weights[$k] = trim( $request->input($weight_id) );
                if(!$weights[$k]){
                    $weights[$k] = 0;
                }
                $total += $weights[$k];
            }
            if($total>100){
                exit('<script>parent.layer.msg("分配权重之和不能超过100!",{icon:2,time:2000}); var index=parent.layer.getFrameIndex(window.name); parent.layer.close(index);</script>');
            }
        }

        //如果已经有 ，删除未选中
        TemplatesSourceSalers::where('s_id', $s_id)->whereNotIn('admin_id',$salers)->update(['is_del'=>1]);
        foreach( $salers as $k=>$admin_id ){
            if($assign_type == 1){
                $weight = $weight_avg;
            }else{
                $weight = $weights[$k];
            }

            TemplatesSourceSalers::updateOrCreate( ['s_id'=>$s_id, 'admin_id'=>$admin_id], ['is_del'=>0, 'weight'=>$weight] );
        }

        exit('<script>parent.layer.msg("操作成功!",{icon:1,time:1000}); var index=parent.layer.getFrameIndex(window.name); parent.location.href="/admin/template/sourceindex";parent.layer.close(index);</script>');
    }

    /**
     * 获取分配销售
     * @return \Illuminate\View\View
     */
    public function getAssigns(Request $request){
        $id = $request->input('id');
        #销售筛选
        //$saler_ids = DB::table('admin_role')->where('role_name', '销售')->lists('user_id');
        $adminList = Admin::select('id', 'realname', 'username', 'team_id')->where('id','>', 1)->where('is_use', 1)->where(function ($query) {
            $query->whereNotNull('qrcode')->where('qrcode', '<>', '');
        })->get();
        $adminList = json_decode(json_encode($adminList), true);
        $assign_admin = TemplatesSourceSalers::where('s_id', $id)->where('is_del', 0)->lists('weight', 'admin_id');

        $team_info = DB::table('teams')->lists('name', 'id');
        foreach($adminList as &$admin){
            if( isset($assign_admin[$admin['id']]) ){
                $admin['weight'] = $assign_admin[$admin['id']];
            }else{
                $admin['weight'] = '';
            }
            if(isset($team_info[$admin['team_id']])){
                $admin['team_name'] = $team_info[$admin['team_id']];
            }else{
                $admin['team_name'] = '';
            }
        }
        return view('template/assign', [
            'adminlist'       =>$adminList,   
            'id'              =>$id     
            ]);
    }

    /**
     * @return \Illuminate\View\View
     */
    public function logindex(Request $request)
    {
        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }

        $offset = ($page-1) * $pageSize;

        $count = TemplatesLog::count();
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = TemplatesLog::orderBy('id', 'desc')->offset($offset)->limit($pageSize)->get();
        $result = json_decode(json_encode($result),true);

        foreach($result as $k=>&$v){
            # 获取销售名字
            $v['admin_name'] = Admin::where('id', $v['admin_id'])->pluck('realname');
        }

        return view('template/logindex', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,          
            ]);
    }

    /**
     * 销售引流模板分配日志报表
     */
    public function templateLogReport(Request $request){
        $admin_id = (int)$request->input('admin_id');
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');

        //假如有团队筛选，检索销售队员
        $sale_ids = null;
        if($team_id>0){
            $sale_ids = DB::table('admin')->where('team_id', $team_id)->lists('id');
        }

        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }

        $offset = ($page-1) * $pageSize;

        $count = TemplatesLog::select(DB::raw('left(create_time,10) as day,admin_id'))->where(function($query) use($admin_id, $stime, $etime, $sale_ids){
            if($admin_id>0) $query->where('admin_id', $admin_id);
            if($stime) $query->where('create_time', '>=', $stime);
            if($etime) $query->where('create_time', '<=', $etime. ' 23:59:59');
            if($sale_ids !== null) $query->whereIn('admin_id', $sale_ids);
        })->groupBy('day')->groupBy('admin_id')->get();
        $count = count($count);
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = TemplatesLog::select(DB::raw('left(create_time,10) as day,admin_id,count(1) as tcount, count(if(type=1,true,null)) as pv_count'))->where(function($query) use($admin_id, $stime, $etime, $sale_ids){
            if($admin_id>0) $query->where('admin_id', $admin_id);
            if($stime) $query->where('create_time', '>=', $stime);
            if($etime) $query->where('create_time', '<=', $etime. ' 23:59:59');
            if($sale_ids !== null) $query->whereIn('admin_id', $sale_ids);
        })->groupBy('day')->groupBy('admin_id')->orderBy('day', 'desc')->offset($offset)->limit($pageSize)->get();
        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
            //获取销售当天加粉情况
            $custDetail = CustDetail::select('fan_add', 'new_reply', 'new_consult', 'old_consult', 'admin_name')->where('dtime', $v['day'])->where('admin_id', $v['admin_id'])->where('is_del', 0)->first();
            if(!empty($custDetail)){
                $v['fan_add'] = $custDetail->fan_add;
                $v['new_reply'] = $custDetail->new_reply;
                $v['old_consult'] = $custDetail->old_consult;
                $v['new_consult'] = $custDetail->new_consult;
                $v['admin_name'] = $custDetail->admin_name;
            }else{
                $v['fan_add'] = '';
                $v['new_reply'] = '';
                $v['old_consult'] = '';
                $v['new_consult'] = '';
                $v['admin_name'] = DB::table('admin')->where('id', $v['admin_id'])->pluck('realname');
            }
            $v['long_count'] = $v['tcount'] - $v['pv_count'];

            //点击率
            $v['click_rate'] = $v['pv_count']>0 && $v['long_count']>0 ? round( $v['long_count'] / $v['pv_count'], 4 ) * 100 . '%' : '';
            $v['change_rate'] = $v['pv_count']>0 && $v['fan_add']>0 ? round( $v['fan_add'] / $v['pv_count'], 4 ) * 100 . '%' : '';
        }

        $teamList = DB::table('teams')->select('id', 'name')->get();
        $teamList = json_decode(json_encode($teamList), true);
        $adminList = DB::table('admin')->select('id', 'realname', 'username')->where('id','>', 1)->get();
        $adminList = json_decode(json_encode($adminList), true);

        return view('template/templateLogReport', ['result'=>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,  
            'teamlist'          =>$teamList,  
            'adminlist'         =>$adminList,  
            'team_id'           =>$team_id,  
            'admin_id'          =>$admin_id,  
            'stime'             =>$stime,  
            'etime'             =>$etime,  
        ]);
    }

    /**
     * 销售引流模板分配日志报表
     */
    public function templateLogReport_export(Request $request){
        $admin_id = (int)$request->input('admin_id');
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');

        //假如有团队筛选，检索销售队员
        $sale_ids = null;
        if($team_id>0){
            $sale_ids = DB::table('admin')->where('team_id', $team_id)->lists('id');
        }

        $result = TemplatesLog::select(DB::raw('left(create_time,10) as day,admin_id,count(1) as tcount, count(if(type=1,true,null)) as pv_count'))->where(function($query) use($admin_id, $stime, $etime, $sale_ids){
            if($admin_id>0) $query->where('admin_id', $admin_id);
            if($stime) $query->where('create_time', '>=', $stime);
            if($etime) $query->where('create_time', '<=', $etime. ' 23:59:59');
            if($sale_ids !== null) $query->whereIn('admin_id', $sale_ids);
        })->groupBy('day')->groupBy('admin_id')->orderBy('day', 'desc')->get();
        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
            //获取销售当天加粉情况
            $custDetail = CustDetail::select('fan_add', 'new_reply', 'new_consult', 'old_consult', 'admin_name')->where('dtime', $v['day'])->where('admin_id', $v['admin_id'])->where('is_del', 0)->first();
            if(!empty($custDetail)){
                $v['fan_add'] = $custDetail->fan_add;
                $v['new_reply'] = $custDetail->new_reply;
                $v['old_consult'] = $custDetail->old_consult;
                $v['new_consult'] = $custDetail->new_consult;
                $v['admin_name'] = $custDetail->admin_name;
            }else{
                $v['fan_add'] = '';
                $v['new_reply'] = '';
                $v['old_consult'] = '';
                $v['new_consult'] = '';
                $v['admin_name'] = DB::table('admin')->where('id', $v['admin_id'])->pluck('realname');
            }
            $v['long_count'] = $v['tcount'] - $v['pv_count'];

            //点击率
            $v['click_rate'] = $v['pv_count']>0 && $v['long_count']>0 ? round( $v['long_count'] / $v['pv_count'], 4 ) * 100 . '%' : '';
            $v['change_rate'] = $v['pv_count']>0 && $v['fan_add']>0 ? round( $v['fan_add'] / $v['pv_count'], 4 ) * 100 . '%' : '';
        }

        $indexKey = ['day','admin_name','pv_count','long_count','fan_add','click_rate','change_rate','new_reply','new_consult','old_consult'];
        $title = ['日期', '销售名', 'PV量', '长按次数', '加粉数', '点击率', '转化率', '新粉回复数', '新粉询价数', '老粉询价数'];
        $filename = 'xiaoshoudaofen_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);  
    }

    /*
     * @return \Illuminate\View\View
     */
    public function sourceindex(Request $request)
    {
        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }

        $offset = ($page-1) * $pageSize;

        $count = TemplatesSource::where('is_del',0)->count();
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = TemplatesSource::where('is_del',0)->orderBy('id', 'desc')->offset($offset)->limit($pageSize)->get();
        $result = json_decode(json_encode($result),true);

        foreach($result as $k=>&$v){
            # 获取销售
            $salers = TemplatesSourceSalers::select('templates_source_salers.*', 'admin.realname as admin_name')->leftJoin('admin', 'admin.id', '=', 'templates_source_salers.admin_id')->where('templates_source_salers.s_id', $v['id'])->where('templates_source_salers.is_del', 0)->where('admin.is_use', 1)->get();
            $v['salers'] = $salers;            
            $v['salers_k'] = count($salers); 
            # 获取模板图
            $v['img'] = Templates::where('id', $v['t_id'])->pluck('img');
        }

        return view('template/sourceindex', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,                   
            ]);
    }

    /**
     * @return \Illuminate\View\View
     */
    public function sourcecreate()
    {
        $templates = Templates::where('is_del', 0)->whereNotNull('url')->get();
        $back_imgs = TemplatesBackimg::where('is_del', 0)->whereNotNull('back_img')->where('t_id', 1)->lists('back_img');
        return view('template/sourcecreate', ['templates'=>$templates, 'back_imgs'=>$back_imgs]);
    }

    /**
     * @param Request $request
     * @return \Illuminate\Http\RedirectResponse
     */
    public function sourcestore(Request $request)
    {
        $t_id = intval($request->input('t_id'));
        $back_vali_str = '';
        if($t_id != 4){
            $back_vali_str = 'required';
        }
        $this->validate($request, [
            't_id' => 'required',           
            'back_img' => $back_vali_str,           
        ], [
            't_id.required' => '模板不能为空',
            'back_img.required' => '背景图不能为空',
            
        ]);
        $template = new TemplatesSource();
        $template->t_id = $t_id;
        $template->note = trim($request->input('note'));
    
        $template->back_img = trim($request->input('back_img'));
        //图片上传 阿里云oss    
        /*   
        if ($request->hasFile('img') && $request->file('img')->isValid()) {
            $file = $request->file('img');
            $ossClient=new oss();
            // 上传阿里云
            $file = $ossClient->upload($file->getClientOriginalExtension(), $file->getRealPath(), 'upload/seafoodPic'.date("Y-m-d",time()).'/'.date('His'));
            $img=$file['oss-request-url'];
            $template->back_img=str_replace("kx-youhuiquan.oss-cn-beijing.aliyuncs.com","imgs.726p.com",$img);
            #保存背景图
            TemplatesBackimg::insert(['back_img'=>$template->back_img]);
        }
        */

        //新模板数据
        if($template->t_id == 4){
            $arr = array();
            $arr['title'] = trim($request->input('title'));
            $arr['text1'] = trim($request->input('text1'));
            $arr['text2'] = trim($request->input('text2'));
            $arr['text3'] = trim($request->input('text3'));
            $arr['img1'] = '';
            $arr['img2_1'] = '';
            $arr['img2_2'] = '';
            $arr['img3'] = '';
            if ($request->hasFile('img1') && $request->file('img1')->isValid()) {
                $file = $request->file('img1');
                $ossClient=new oss();
                // 上传阿里云
                $file = $ossClient->upload($file->getClientOriginalExtension(), $file->getRealPath(), 'upload/seafoodPic'.date("Y-m-d",time()).'/'.date('His'));
                $img=$file['oss-request-url'];
                $arr['img1']=str_replace("kx-youhuiquan.oss-cn-beijing.aliyuncs.com","imgs.726p.com",$img);
            }
            if ($request->hasFile('img2_1') && $request->file('img2_1')->isValid()) {
                $file = $request->file('img2_1');
                $ossClient=new oss();
                // 上传阿里云
                $file = $ossClient->upload($file->getClientOriginalExtension(), $file->getRealPath(), 'upload/seafoodPic'.date("Y-m-d",time()).'/'.date('His'));
                $img=$file['oss-request-url'];
                $arr['img2_1']=str_replace("kx-youhuiquan.oss-cn-beijing.aliyuncs.com","imgs.726p.com",$img);
            }
            if ($request->hasFile('img2_2') && $request->file('img2_2')->isValid()) {
                $file = $request->file('img2_2');
                $ossClient=new oss();
                // 上传阿里云
                $file = $ossClient->upload($file->getClientOriginalExtension(), $file->getRealPath(), 'upload/seafoodPic'.date("Y-m-d",time()).'/'.date('His'));
                $img=$file['oss-request-url'];
                $arr['img2_2']=str_replace("kx-youhuiquan.oss-cn-beijing.aliyuncs.com","imgs.726p.com",$img);
            }
            if ($request->hasFile('img3') && $request->file('img3')->isValid()) {
                $file = $request->file('img3');
                $ossClient=new oss();
                // 上传阿里云
                $file = $ossClient->upload($file->getClientOriginalExtension(), $file->getRealPath(), 'upload/seafoodPic'.date("Y-m-d",time()).'/'.date('His'));
                $img=$file['oss-request-url'];
                $arr['img3']=str_replace("kx-youhuiquan.oss-cn-beijing.aliyuncs.com","imgs.726p.com",$img);
            }

            $template->new_context = json_encode($arr);
        }
        if($template->save()){
            //生成连接
            $template->url = $this->createSourceUrl($template->t_id, $template->id);
            $template->save();
        }

        return redirect('/admin/template/sourceindex')->with('info', '添加模板成功');
    }

    /**
     * @param $id
     * @return \Illuminate\View\View
     */
    public function sourceedit($id)
    {
        $templates = Templates::where('is_del', 0)->whereNotNull('url')->get();      
        $template = TemplatesSource::findOrFail($id);
        $back_imgs = TemplatesBackimg::where('is_del', 0)->whereNotNull('back_img')->where('t_id', $template->t_id)->lists('back_img');
        $new_context = array();
        if($template->t_id==4){
            $new_context = json_decode($template->new_context,true);
        }
        return view('template/sourceedit', ['template' => $template, 'id'=>$id, 'templates' => $templates, 'back_imgs'=>$back_imgs, 'new_context'=>$new_context]);
    }

    /**
     * @param Request $request
     * @return \Illuminate\Http\RedirectResponse
     */
    public function sourceupdate(Request $request)
    {
        $id = (int)$request->input('id');
       
        $template = TemplatesSource::findOrFail($id);
        $template->note = trim($request->input('note'));
        $template->back_img = trim($request->input('back_img'));

        //是否是新图文模板
        //新模板数据
        if($template->t_id == 4){
            $new_context = json_decode($template->new_context, true);
            $arr = $new_context;
            $arr['title'] = trim($request->input('title'));
            $arr['text1'] = trim($request->input('text1'));
            $arr['text2'] = trim($request->input('text2'));
            $arr['text3'] = trim($request->input('text3'));

            if ($request->hasFile('img1') && $request->file('img1')->isValid()) {
                $file = $request->file('img1');
                $ossClient=new oss();
                // 上传阿里云
                $file = $ossClient->upload($file->getClientOriginalExtension(), $file->getRealPath(), 'upload/seafoodPic'.date("Y-m-d",time()).'/'.date('His'));
                $img=$file['oss-request-url'];
                $arr['img1']=str_replace("kx-youhuiquan.oss-cn-beijing.aliyuncs.com","imgs.726p.com",$img);
            }
            if ($request->hasFile('img2_1') && $request->file('img2_1')->isValid()) {
                $file = $request->file('img2_1');
                $ossClient=new oss();
                // 上传阿里云
                $file = $ossClient->upload($file->getClientOriginalExtension(), $file->getRealPath(), 'upload/seafoodPic'.date("Y-m-d",time()).'/'.date('His'));
                $img=$file['oss-request-url'];
                $arr['img2_1']=str_replace("kx-youhuiquan.oss-cn-beijing.aliyuncs.com","imgs.726p.com",$img);
            }
            if ($request->hasFile('img2_2') && $request->file('img2_2')->isValid()) {
                $file = $request->file('img2_2');
                $ossClient=new oss();
                // 上传阿里云
                $file = $ossClient->upload($file->getClientOriginalExtension(), $file->getRealPath(), 'upload/seafoodPic'.date("Y-m-d",time()).'/'.date('His'));
                $img=$file['oss-request-url'];
                $arr['img2_2']=str_replace("kx-youhuiquan.oss-cn-beijing.aliyuncs.com","imgs.726p.com",$img);
            }
            if ($request->hasFile('img3') && $request->file('img3')->isValid()) {
                $file = $request->file('img3');
                $ossClient=new oss();
                // 上传阿里云
                $file = $ossClient->upload($file->getClientOriginalExtension(), $file->getRealPath(), 'upload/seafoodPic'.date("Y-m-d",time()).'/'.date('His'));
                $img=$file['oss-request-url'];
                $arr['img3']=str_replace("kx-youhuiquan.oss-cn-beijing.aliyuncs.com","imgs.726p.com",$img);
            }

            $template->new_context = json_encode($arr);
        }

        $template->save();
        return redirect('/admin/template/sourceindex')->with('info', '修改成功');
    }

    public function createSourceUrl($t_id, $id){
        $url = Templates::where('id', $t_id)->pluck('url');
        if($url){
            return $url . '?id='.$id;
        }
        return false;
    }

    public function uploadBack(Request $request){
        $img = $request->input('img');
        if (preg_match('/^(data:\s*image\/(\w+);base64,)/', $img, $res)) {
            //获取图片类型
            $type = $res[2];
            // 临时文件
            $tmpfname = tempnam("/image/", "FOO");
            //保存图片
            $handle = fopen($tmpfname, "w");
            //阿里云oss上传的文件目录
            if (fwrite($handle, base64_decode(str_replace($res[1], '', $img)))) 
            {
                $ossClient=new oss();
                // 上传阿里云
                $file = $ossClient->upload($type, $tmpfname, 'upload/seafoodPic'.date("Y-m-d",time()).'/'.date('His'));
                fclose($handle);
                unlink($tmpfname);
                $img=$file['oss-request-url'];
                $back_img=str_replace("kx-youhuiquan.oss-cn-beijing.aliyuncs.com","imgs.726p.com",$img);
                #保存背景图
                $t_id = (int)$request->input('t_id');
                $res = TemplatesBackimg::insert(['back_img'=>$back_img, 't_id'=>$t_id]);
                exit($back_img);
            }

            exit('0');
        }
    }

    public function getTempback($t_id){
        $result = TemplatesBackimg::where('t_id', $t_id)->lists('back_img');
        exit(json_encode($result));
    }

    /**
     * 销售引流模板分配月报
     */
    public function templateLogMonthReport(Request $request){
        $admin_id = (int)$request->input('admin_id');
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');

        //假如有团队筛选，检索销售队员
        $sale_ids = null;
        if($team_id>0){
            $sale_ids = DB::table('admin')->where('team_id', $team_id)->lists('id');
        }

        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }

        $offset = ($page-1) * $pageSize;

        $mstime = '';
        $metime = '';
        if($stime){
            $mstime = date('Y-m-1', strtotime($stime));
        }
        if($etime){
            $metime = date('Y-m-1', strtotime($etime.' +1 month'));
        }
        $_start = '2019-11-01';
        $count = TemplatesLog::select(DB::raw('left(create_time,7) as month,admin_id'))->where('create_time', '>', $_start)->where(function($query) use($admin_id, $mstime, $metime, $sale_ids){
            if($admin_id>0) $query->where('admin_id', $admin_id);
            if($mstime) $query->where('create_time', '>=', $mstime);
            if($metime) $query->where('create_time', '<', $metime);
            if($sale_ids !== null) $query->whereIn('admin_id', $sale_ids);
        })->groupBy('month')->groupBy('admin_id')->get();
        $count = count($count);
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = TemplatesLog::select(DB::raw('left(create_time,7) as month,admin_id,count(1) as tcount, count(if(type=1,true,null)) as pv_count'))->where('create_time', '>', $_start)->where(function($query) use($admin_id, $mstime, $metime, $sale_ids){
            if($admin_id>0) $query->where('admin_id', $admin_id);
            if($mstime) $query->where('create_time', '>=', $mstime);
            if($metime) $query->where('create_time', '<', $metime);
            if($sale_ids !== null) $query->whereIn('admin_id', $sale_ids);
        })->groupBy('month')->groupBy('admin_id')->orderBy('month', 'desc')->get();
        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
            //起止时间
            $s_time = date('Y-m-1', strtotime($v['month']));
            $e_time = date('Y-m-1', strtotime($v['month'].' +1 month'));
            //获取销售当月加粉情况
            $custDetail = CustDetail::select(DB::raw('sum(fan_add) as fan_add'))->whereRaw('left(dtime,7)="'.$v['month'].'"')->where('admin_id', $v['admin_id'])->where('is_del', 0)->first();
            if(!empty($custDetail)){
                $v['fan_add'] = $custDetail->fan_add;
            }else{
                $v['fan_add'] = '';                
            }
            $v['admin_name'] = DB::table('admin')->where('id', $v['admin_id'])->pluck('realname');
            $v['long_count'] = $v['tcount'] - $v['pv_count'];

            //点击率
            //$v['click_rate'] = $v['pv_count']>0 && $v['long_count']>0 ? round( $v['long_count'] / $v['pv_count'], 4 ) * 100 . '%' : '';
            $v['change_rate'] = $v['pv_count']>0 && $v['fan_add']>0 ? round( $v['fan_add'] / $v['pv_count'], 4 ) * 100 . '%' : '';

            //1.获取当月新粉
            $phones = DB::table('customers')->whereRaw('left(fanTime,7)="'.$v['month'].'"')->lists('phone');
            $order = Order::select(DB::raw('count(1) as order_count, count(distinct(receiverMobile)) as cust_count, sum(receivedAmount) as order_amount'))->where('admin_id', $v['admin_id'])->whereIn('receiverMobile', $phones)->whereRaw('left(createTime,7)="'.$v['month'].'"')->where('is_del', 0)->first();
            //当月新粉下单单数
            $v['order_count'] = $order->order_count;
            //当月新粉下单人数
            $v['cust_count'] = $order->cust_count;
            //当月粉丝复购单数
            $v['fugou_order_count'] = $order->order_count - $order->cust_count;
            //当月粉丝总销售额
            $v['order_amount'] = $order->order_amount;
            //新粉下单率
            $v['new_order_rate'] = $v['fan_add']>0 ? round($v['cust_count'] / $v['fan_add'], 4) * 100 . '%' : '';
            //新粉复购率
            //复购用户数 = 下单超过一单的用户数
            $order_cust_fugou = Order::select(DB::raw('count(1) as counts,receiverMobile'))->where('admin_id', $v['admin_id'])->whereIn('receiverMobile', $phones)->where('is_del', 0)->whereRaw('left(createTime,7)="'.$v['month'].'"')->groupBy('receiverMobile')->having('counts', '>', 1)->get();
            $customer_fugou_count = count($order_cust_fugou);
            $v['cust_fugou_rate'] = $v['cust_count']>0 ? round($customer_fugou_count / $v['cust_count'], 4) * 100 . '%' : '';
            //新粉订单复购率
            $v['order_fugou_rate'] = $order->cust_count>0 ? round($v['fugou_order_count'] / $order->cust_count, 4) * 100 . '%' : '';
            //当月粉丝人均销售额
            $v['avg_amount'] = $v['fan_add']>0 ? round($order->order_amount / $v['fan_add'], 2) : '';

        }

        $sort_c = array_column($result, 'avg_amount');
        array_multisort($sort_c,SORT_DESC,$result);
        $result = array_slice($result, $offset, $pageSize);

        $teamList = DB::table('teams')->select('id', 'name')->get();
        $teamList = json_decode(json_encode($teamList), true);
        $adminList = DB::table('admin')->select('id', 'realname', 'username')->where('id','>', 1)->get();
        $adminList = json_decode(json_encode($adminList), true);

        return view('template/templateLogMonthReport', ['result'=>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,  
            'teamlist'          =>$teamList,  
            'adminlist'         =>$adminList,  
            'team_id'           =>$team_id,  
            'admin_id'          =>$admin_id,  
            'stime'             =>$stime,  
            'etime'             =>$etime,  
        ]);
    }

    /**
     * 销售引流模板分配月报
     */
    public function templateLogMonthReport_export(Request $request){
        $admin_id = (int)$request->input('admin_id');
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');

        //假如有团队筛选，检索销售队员
        $sale_ids = null;
        if($team_id>0){
            $sale_ids = DB::table('admin')->where('team_id', $team_id)->lists('id');
        }

        $mstime = '';
        $metime = '';
        if($stime){
            $mstime = date('Y-m-1', strtotime($stime));
        }
        if($etime){
            $metime = date('Y-m-1', strtotime($etime.' +1 month'));
        }
        $_start = '2019-11-01';
        $result = TemplatesLog::select(DB::raw('left(create_time,7) as month,admin_id,count(1) as tcount, count(if(type=1,true,null)) as pv_count'))->where('create_time', '>', $_start)->where(function($query) use($admin_id, $mstime, $metime, $sale_ids){
            if($admin_id>0) $query->where('admin_id', $admin_id);
            if($mstime) $query->where('create_time', '>=', $mstime);
            if($metime) $query->where('create_time', '<', $metime);
            if($sale_ids !== null) $query->whereIn('admin_id', $sale_ids);
        })->groupBy('month')->groupBy('admin_id')->orderBy('month', 'desc')->get();
        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
            //获取销售当月加粉情况
            $custDetail = CustDetail::select(DB::raw('sum(fan_add) as fan_add'))->whereRaw('left(dtime,7)="'.$v['month'].'"')->where('admin_id', $v['admin_id'])->where('is_del', 0)->first();
            if(!empty($custDetail)){
                $v['fan_add'] = $custDetail->fan_add;
            }else{
                $v['fan_add'] = '';                
            }
            $v['admin_name'] = DB::table('admin')->where('id', $v['admin_id'])->pluck('realname');
            $v['long_count'] = $v['tcount'] - $v['pv_count'];

            //点击率
            //$v['click_rate'] = $v['pv_count']>0 && $v['long_count']>0 ? round( $v['long_count'] / $v['pv_count'], 4 ) * 100 . '%' : '';
            $v['change_rate'] = $v['pv_count']>0 && $v['fan_add']>0 ? round( $v['fan_add'] / $v['pv_count'], 4 ) * 100 . '%' : '';

            //1.获取当月新粉
            $phones = DB::table('customers')->whereRaw('left(fanTime,7)="'.$v['month'].'"')->lists('phone');
            $order = Order::select(DB::raw('count(1) as order_count, count(distinct(receiverMobile)) as cust_count, sum(receivedAmount) as order_amount'))->where('admin_id', $v['admin_id'])->whereIn('receiverMobile', $phones)->whereRaw('left(createTime,7)="'.$v['month'].'"')->where('is_del', 0)->first();
            //当月新粉下单单数
            $v['order_count'] = $order->order_count;
            //当月新粉下单人数
            $v['cust_count'] = $order->cust_count;
            //当月粉丝复购单数
            $v['fugou_order_count'] = $order->order_count - $order->cust_count;
            //当月粉丝总销售额
            $v['order_amount'] = $order->order_amount;
            //新粉下单率
            $v['new_order_rate'] = $v['fan_add']>0 ? round($v['cust_count'] / $v['fan_add'], 4) * 100 . '%' : '';
            //新粉复购率
            //复购用户数 = 下单超过一单的用户数
            $order_cust_fugou = Order::select(DB::raw('count(1) as counts,receiverMobile'))->where('admin_id', $v['admin_id'])->whereIn('receiverMobile', $phones)->where('is_del', 0)->whereRaw('left(createTime,7)="'.$v['month'].'"')->groupBy('receiverMobile')->having('counts', '>', 1)->get();
            $customer_fugou_count = count($order_cust_fugou);
            $v['cust_fugou_rate'] = $v['cust_count']>0 ? round($customer_fugou_count / $v['cust_count'], 4) * 100 . '%' : '';
            //新粉订单复购率
            $v['order_fugou_rate'] = $order->cust_count>0 ? round($v['fugou_order_count'] / $order->cust_count, 4) * 100 . '%' : '';
            //当月粉丝人均销售额
            $v['avg_amount'] = $v['fan_add']>0 ? round($order->order_amount / $v['fan_add'], 2) : '';

        }

        $sort_c = array_column($result, 'avg_amount');
        array_multisort($sort_c,SORT_DESC,$result);

        $indexKey = ['month','admin_name','pv_count','long_count','fan_add','cust_count','order_count','fugou_order_count','order_amount','change_rate','new_order_rate','cust_fugou_rate','order_fugou_rate','avg_amount'];
        $title = ['月份', '销售名', '当月PV量', '当月长按次数', '当月上报加粉数', '当月新粉下单人数', '新粉下单单数', '当月粉丝复购单数', '当月粉丝总销售额', '粉丝转化率', '新粉下单率', '新粉复购率', '新粉订单复购率', '当月粉丝人均销售额'];
        $filename = 'daofenyuebao_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);  
    }


}