jili2/application/admin/controller/PhStatistics.php

<?php
namespace app\admin\controller;

use app\common\util\ExcelUtil;
use think\App;
use think\Db;
use think\db\Where;
use think\Exception;
use function GuzzleHttp\Promise\all;

class PhStatistics extends Auth
{
    /**
     * 流水统计
     */
    public function ls()
    {
        $begin = input('begin',date('Y-m-d',strtotime('-7 days')),'trim');
        $end = input('end',date('Y-m-d'),'trim');
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            $worker = input('worker','','trim');
            if($worker){
                $ids = Db::name('worker')
                    ->alias('a')
                    ->join('user b','a.user_id = b.id')
                    ->join('user_org c','c.user_id = a.user_id')
                    ->where('b.real_name','like','%'.$worker.'%')
                    ->where('c.org_id',$this->orgId)
                    ->column('a.id');
                if (!empty($ids)){
                    $map[] = ['d.worker_id','in',$ids];
                }
            }
            $name= input('name','','trim');
            if ($name){
                $map[] = ['b.name','like','%'.$name.'%'];
            }

            $cateId = input('cateId','','trim');
            if($cateId){
                $map[] = ['b.cate_id','=',$cateId];
            }

            $depId = input('depId','','trim');
            if($depId){
                $map[] = ['b.dep_id','=',$depId];
            }

            $payType = input('payType','','trim');
            if ($payType){
                $map[] = ['a.type','=',$payType];
            }

            $delType = input('dealType','','trim');
            if ($delType != ''){
                $map[] = ['a.mode','=',$delType];
            }
            $cateId = input('cateId','','trim');
            if($cateId){
                $map[] = ['b.cate_id','=',$cateId];
            }
            $map[] = ['a.pay_time','>=',$begin.' 00:00:00'];
            $map[] = ['a.pay_time','<=',$end.' 23:59:59'];
            $map[] = ['b.org_id','=',$this->orgId];
            $lists = Db::name('ph_order_water')
                ->alias('a')
                ->join('ph_orders b','a.order_id = b.id')
                ->join('dep c','b.dep_id = c.id','left')
                ->join('ph_todo d',' a.order_id = d.order_id','left')
                ->field('a.*,c.title as depTitle,b.cate_id')
                ->group('a.sn')
                ->order('a.pay_time','desc')
                ->where($map)
                ->limit($start,$length)
                ->select();

            foreach ($lists as $k=>$v){
                $order = Db::name('ph_orders')
                    ->where('id',$v['order_id'])
                    ->field('sn,name,phone,user_id,form')
                    ->find();
                $lists[$k]['order_sn'] = $order?$order['sn']:'';
                $lists[$k]['contact'] = $order?$order['name']:'';
                $lists[$k]['phone'] = $order?$order['phone']:'';
                $lists[$k]['pay_text'] = $v['type'] == 1 ?  "线下" : '线上';
                $lists[$k]['name'] = Db::name('user')
                    ->where('id',$v['user_id'])
                    ->value('real_name');

                if ($v['mode'] == 0) {
                    $lists[$k]['modeName'] ='预收金';
                }elseif ($v['mode'] == 1){
                    $lists[$k]['modeName'] ='服务费';
                } else{
                    $lists[$k]['modeName'] ='退款';
                }
                $workerIds = Db::name('ph_todo')->where('order_id',$v['order_id'])->column('worker_id');
                $workerIds = array_unique($workerIds);
                $users = Db::name('user')
                    ->alias('a')
                    ->join('worker b','a.id = b.user_id')
                    ->where('b.id','in',$workerIds)
                    ->column('a.real_name','b.id');
                foreach ($workerIds as $k2=>$v2){
                    $workerIds[$k2] = $users[$v2];
                }
                $lists[$k]['worker'] = implode(',',$workerIds);
                $lists[$k]['cateTitle'] = Db::name('cate')->where('id',$v['cate_id'])->value('title');

            }
            //数据返回
            $totalCount = Db::name('ph_order_water')
                ->alias('a')
                ->join('ph_orders b','a.order_id = b.id')
                ->join('dep c','b.dep_id = c.id','left')
                ->join('ph_todo d',' a.order_id = d.order_id','left')
                ->order('a.pay_time asc,a.id asc')
                ->field('a.*,c.title as depTitle')
                ->group('a.sn')
                ->where($map)
                ->count();
            $lists2 = Db::name('ph_order_water')
                ->alias('a')
                ->join('ph_orders b','a.order_id = b.id')
                ->join('dep c','b.dep_id = c.id','left')
                ->join('ph_todo d',' a.order_id = d.order_id','left')
                ->order('a.pay_time asc,a.id asc')
                ->field('a.mode,a.money')
                ->group('a.sn')
                ->where($map)
                ->select();

            $money1 = $money2 = $money3 = 0;
            $totalMoney = 0;
            foreach ($lists2 as $k=>$v){
                if ($v['mode'] == 0) {
                    $money1+=$v['money'];
                }elseif ($v['mode'] == 1) {
                    $money2+=$v['money'];
                }
                else{
                    $money3+=$v['money'];
                }
                $totalMoney = $money1 + $money2 - $money3;
            }
            $totalPage = ceil($totalCount/$length);
            $result['money1'] = round($money1,2);
            $result['money2'] = round($money2,2);
            $result['money3'] = round($money3,2);
            $result['totalMoney'] = round($totalMoney,2);
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{

            $dep =(new \app\common\model\Dep())->getList($this->orgId);
            $this->assign('dep',$dep);
            $this->assign('begin',$begin);
            $this->assign('end',$end);
            $cate =(new \app\common\model\Cate())->getAllByOrg($this->orgId);
            $this->assign('cate',$cate);
            return $this->fetch();
        }
    }

    //流水列表导出
    public function lsExport()
    {
        if(request()->isGet()){
            $begin = input('begin',date('Y-m-d',strtotime('-7 days')),'trim');//用户
            $end = input('end',date('Y-m-d'),'trim');//用户
            $worker = input('worker','','trim');
            if($worker){
                $ids = Db::name('worker')
                    ->alias('a')
                    ->join('user b','a.user_id = b.id')
                    ->join('user_org c','c.user_id = a.user_id')
                    ->where('b.real_name','like','%'.$worker.'%')
                    ->where('c.org_id',$this->orgId)
                    ->column('a.id');
                if (!empty($ids)){
                    $map[] = ['d.worker_id','in',$ids];
                }
            }
            $name= input('name','','trim');
            if ($name){
                $map[] = ['b.name','like','%'.$name.'%'];
            }
            $cateId = input('cateId','','trim');
            if($cateId){
                $map[] = ['b.cate_id','=',$cateId];
            }

            $depId = input('depId','','trim');
            if($depId){
                $map[] = ['b.dep_id','=',$depId];
            }

            $payType = input('payType','','trim');
            if ($payType){
                $map[] = ['a.type','=',$payType];
            }

            $delType = input('dealType','','trim');
            if ($delType){
                $map[] = ['a.mode','=',$delType];
            }
            $cateId = input('cateId','','trim');
            if($cateId){
                $map[] = ['b.cate_id','=',$cateId];
            }
            $map[] = ['a.pay_time','>=',$begin.' 00:00:00'];
            $map[] = ['a.pay_time','<=',$end.' 23:59:59'];
            $map[] = ['b.org_id','=',$this->orgId];
            $lists = Db::name('ph_order_water')
                ->alias('a')
                ->join('ph_orders b','a.order_id = b.id')
                ->join('dep c','b.dep_id = c.id','left')
                ->join('ph_todo d',' a.order_id = d.order_id','left')
                ->join('cate e',' b.cate_id = e.id','left')
                ->order('a.pay_time','desc')
                ->field('a.*,c.title as depTitle,e.title as cateTitle')
                ->group('a.sn')
                ->where($map)
                ->select();
            $money1 = $money2 = $money3 = $totalMoney = 0;
            foreach ($lists as $k=>$v){
                $order = Db::name('ph_orders')
                    ->where('id',$v['order_id'])
                    ->field('sn,name,phone,user_id,form')
                    ->find();
                $lists[$k]['order_sn'] = $order?$order['sn']:'';
                $lists[$k]['contact'] = $order?$order['name']:'';
                $lists[$k]['phone'] = $order?$order['phone']:'';
                $lists[$k]['pay_text'] = $v['type'] == 1 ?  "线下" : '线上';
                $lists[$k]['name'] = Db::name('user')
                    ->where('id',$v['user_id'])
                    ->value('real_name');
                if ($v['mode'] == 0) {
                    $lists[$k]['modeName'] ='预收金';
                }elseif ($v['mode'] == 1){
                    $lists[$k]['modeName'] ='服务费';
                } else{
                    $lists[$k]['modeName'] ='退款';
                }
                $workerIds = Db::name('ph_todo')->where('order_id',$v['order_id'])->column('worker_id');
                $workerIds = array_unique($workerIds);
                $users = Db::name('user')
                    ->alias('a')
                    ->join('worker b','a.id = b.user_id')
                    ->where('b.id','in',$workerIds)
                    ->column('a.real_name','b.id');
                foreach ($workerIds as $k2=>$v2){
                    $workerIds[$k2] = $users[$v2];
                }
                $lists[$k]['worker'] = implode(',',$workerIds);
                if ($v['mode'] == 0) {
                    $money1+=$v['money'];
                }elseif ($v['mode'] == 1) {
                    $money2+=$v['money'];
                }
                else{
                    $money3+=$v['money'];
                }
                $totalMoney = $money1 + $money2 - $money3;
            }

            //实例化PHPExcel类
            include_once env('root_path') . '/extend/phpexcel/Classes/PHPExcel.php';
            $objPHPExcel = new \PHPExcel();
            //激活当前的sheet表
            $objPHPExcel->setActiveSheetIndex(0);
            //设置表格头（即excel表格的第一行）
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', '订单编号')
                ->setCellValue('B1', '下单科室')
                ->setCellValue('C1', '护工')
                ->setCellValue('D1', '陪护服务')
                ->setCellValue('E1', '患者姓名')
                ->setCellValue('F1', '联系方式')
                ->setCellValue('G1', '流水号')
                ->setCellValue('H1', '交易时间')
                ->setCellValue('I1', '金额')
                ->setCellValue('J1', '交易类型')
                ->setCellValue('K1', '支付方式')
                ->setCellValue('L1', '操作人')
                ->setCellValue('M1', '备注');

            $arrs = ['A','B','C','D','E','F','G','H','I','J','H','I','J','K','L','M','N'];
            foreach ($arrs as $arr){
                // 设置表格头水平居中
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($arr.'1')->getAlignment()
                    ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                //设置列水平居中
                $objPHPExcel->setActiveSheetIndex(0)->getStyle($arr)->getAlignment()
                    ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                //设置单元格宽度
                $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($arr)->setWidth(20);
            }

            //循环刚取出来的数组，将数据逐一添加到excel表格。
            for ($i = 0; $i < count($lists); $i++) {
                $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $lists[$i]['order_sn']);
                $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $lists[$i]['depTitle']);
                $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $lists[$i]['worker']);
                $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $lists[$i]['cateTitle']);
                $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i + 2), $lists[$i]['contact']);
                $objPHPExcel->getActiveSheet()->setCellValue('F' . ($i + 2), $lists[$i]['phone']);
                $objPHPExcel->getActiveSheet()->setCellValue('G' . ($i + 2), $lists[$i]['sn']);
                $objPHPExcel->getActiveSheet()->setCellValue('H' . ($i + 2), $lists[$i]['pay_time']);
                $objPHPExcel->getActiveSheet()->setCellValue('I' . ($i + 2), $lists[$i]['money']);
                $objPHPExcel->getActiveSheet()->setCellValue('J' . ($i + 2), $lists[$i]['modeName']);
                $objPHPExcel->getActiveSheet()->setCellValue('K' . ($i + 2), $lists[$i]['pay_text']);
                $objPHPExcel->getActiveSheet()->setCellValue('L' . ($i + 2), $lists[$i]['name']);
                $objPHPExcel->getActiveSheet()->setCellValue('M' . ($i + 2), $lists[$i]['remark']);
            }
            $objPHPExcel->getActiveSheet()->setCellValue('A' . (count($lists) + 3), '预收金:');
            $objPHPExcel->getActiveSheet()->setCellValue('B' . (count($lists) + 3), $money1.'元');
            $objPHPExcel->getActiveSheet()->setCellValue('C' . (count($lists) + 3), '服务费:');
            $objPHPExcel->getActiveSheet()->setCellValue('D' . (count($lists) + 3), $money2.'元');
            $objPHPExcel->getActiveSheet()->setCellValue('E' . (count($lists) + 3), '退款:');
            $objPHPExcel->getActiveSheet()->setCellValue('F' . (count($lists) + 3), $money3.'元');
            $objPHPExcel->getActiveSheet()->setCellValue('G' . (count($lists) + 3), '总金额:');
            $objPHPExcel->getActiveSheet()->setCellValue('H' . (count($lists) + 3), $totalMoney.'元');
            //设置保存的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');
        }

    }

}