guoyao-jiankang/application/admin/controller/SettlementLog.php

<?php
namespace app\admin\controller;

use think\App;
use think\Db;
use think\Exception;

class SettlementLog extends Auth
{

    public function index(){
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','u.id','trim');      //排序列
            $sort = input('sord','desc','trim');        //排序方式
            $order = 'id desc';
            $title = input('title','','trim');
            if($title){
                $map[] = ['name','like','%'.$title.'%'];
            }
            $phone = input('phone','','trim');
            if($phone){
                $map[] = ['phone','like','%'.$phone.'%'];
            }
            $recommend = input('recommend','','trim');
            if($recommend != ''){
                $map[] = ['recommend','=',$recommend];
            }
            $enable = input('enable','','trim');
            if($enable != ''){
                $map[] = ['enable','=',$enable];
            }
            $gender = input('gender','','trim');
            if($gender != ''){
                $map[] = ['gender','=',$gender];
            }
            $map[] = ['org_id','=',$this->orgId];

            $b = input('begin','','trim');
            $e = input('end','','trim');
            if($b!=''){
                $b = date('Y-m-d 00:00:00',strtotime($b));
                $map[] = ['create_time','>=',$b];
            }

            if($e!=''){
                $e = date('Y-m-d 23:59:59',strtotime($e));
                $map[] = ['create_time','<=',$e];
            }

            $map= empty($map) ? true: $map;
            //数据查询
            $lists = Db::name('settlement_log')
                ->where($map)
                ->limit($start,$length)
                ->order($order)
                ->select();
            foreach ($lists as $k=>$v){
                $lists[$k]['uName'] = Db::name('user')
                    ->where('id',$v['user_id'])
                    ->value('real_name');
            }
            //数据返回
            $totalCount = Db::name('settlement_log')
                ->where($map)->count();
            $totalPage = ceil($totalCount/$length);
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{
            return $this->fetch();
        }
    }


    public function detail($id){
        $list = Db::name('worker_balance')
            ->where('org_id',$this->orgId)
            ->where('settlement_log_id',$id)
            ->order('id','desc')
            ->paginate(10,false,[
                'query'=>input()
            ]);

        $render = $list->render();
        $data = $list->toArray();
        foreach ($data['data'] as $k=>$v){
            $data['data'][$k]['uName'] = Db::name('user')
                ->where('id',$v['user_id'])
                ->value('real_name');
            $work_user_id = Db::name('worker')
                ->where('id',$v['worker_id'])
                ->value('user_id');
            $data['data'][$k]['workName'] = Db::name('user')
                ->where('id',$work_user_id)
                ->value('real_name');
        }

        $this->assign('list',$data['data']);
        $this->assign('page',$render);

        $this->assign('id',$id);
        $this->assign('meta_title','结算记录详情');
        return $this->fetch();
    }

    //导出
    public function export(){

        $id = input('id','');
        //数据查询
        $lists = Db::name('settlement_log')
            ->where('id',$id)
            ->find();
        $ret = [];
        $log =  Db::name('worker_balance')
            ->where('settlement_log_id',$id)
            ->select();
        foreach ($log as $k1=>&$v1){
            $v1['uName'] = Db::name('user')
                ->where('id',$v1['user_id'])
                ->value('real_name');
            $work_user_id = Db::name('worker')
                ->where('id',$v1['worker_id'])
                ->value('user_id');
            $v1['workName'] = Db::name('user')
                ->where('id',$work_user_id)
                ->value('real_name');
            $ret[] = $v1;
        }
        include_once env('root_path') . '/extend/phpexcel/Classes/PHPExcel.php';
        //实例化PHPExcel类
        $objPHPExcel = new \PHPExcel();
        //激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '护工')
            ->setCellValue('B1', '金额')
            ->setCellValue('C1', '结算时间');
        // 设置表格头水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('H1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置列水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置单元格宽度
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(50);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(20);
        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($ret); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $ret[$i]['workName']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $ret[$i]['money']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $ret[$i]['create_time']);
        }
        //设置保存的Excel表格名称
        $filename = '结算记录_' . date('YmdHis', time()) . '.xls';
        //设置当前激活的sheet表格名称
        $objPHPExcel->getActiveSheet()->setTitle('结算记录');
        //设置浏览器窗口下载表格
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $filename);
        //生成excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //下载文件在浏览器窗口
        return $objWriter->save('php://output');
    }
}