jili2/application/admin/controller/HousePay.php

<?php
namespace app\admin\controller;

use think\App;
use think\Db;

class HousePay extends Auth
{

    public function __construct(App $app = null) {
        parent::__construct($app);
        $this->table='house_pay';
        $this->model= new \app\common\model\HousePay();
    }
    public function index(){
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','id','trim');      //排序列
            $sort = input('sord','desc','trim');        //排序方式
            $order = $sortRow.' '.$sort;

            $sn = input('sn','','trim');
            if($sn){
                $map[] = ['sn','like','%'.$sn.'%'];
            }
            $name = input('name','','trim');//用户
            if($name){
                $map1[] = ['name','like','%'.$name.'%'];
                $user = Db::name('owner')
                    ->where($map1)
                    ->where('del',0)
                    ->where('enable',1)
                    ->where('org_id',$this->orgId)
                    ->column('id');
                if(!empty($user)){
                    $map[] = ['owner_id','in',$user];

                }else{
                    $map[] = ['owner_id','=',0];
                }
            }
            $status = input('status','','trim');
            if($status != ''){
                $map[] = ['status','=',$status];
            }

            $b = input('start','','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];
            }


            $map2 = [];
            $c = input('wstart','','trim');
            $d = input('wend','','trim');
            if($c && $d){
                if($c <= $d){
                    $c = date('Y-m-d',strtotime($c));
                    $d = date('Y-m-d',strtotime($d));
                    $map2[] = ['end','>=',$c];
                    $map2[] = ['start','<=',$d];
                }
            }
            if($c && $d==''){
                $c = date('Y-m-d',strtotime($c));
                $map2[] = ['end','>=',$c];
            }
            if($c=='' && $d){
                $d = date('Y-m-d',strtotime($d));
                $map2[] = ['start','<=',$d];
            }
            if(!empty($map2)){
                $logs = Db::name('house_pay_log')->where($map2)
                    ->where('org_id',$this->orgId)
                    ->distinct(true)->column('pay_id');
                if($logs){
                    $map[] = ['id','in',$logs];
                }else{
                    $map[] = ['id','=',-1];
                }
            }

            $map[] = ['del','=',0];
            $map[] = ['org_id','=',$this->orgId];
            $map= empty($map) ? true: $map;
            //数据查询
            $lists = db($this->table)->where($map)->limit($start,$length)->order($order)->select();
            foreach ($lists as $k=>$v){
                $lists[$k]['full_name'] = Db::name('owner')
                    ->where('id',$v['owner_id'])
                    ->value('name');
            }
            //数据返回
            $totalCount = db($this->table)->where($map)->count();
            $totalPage = ceil($totalCount/$length);

            $result['zys'] = db($this->table)->where($map)->sum('money');
            $result['zsh'] = db($this->table)->where($map)->sum('true_money');
            $result['zyfk'] = db($this->table)->where($map)->sum('pre_money');
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{
            return $this->fetch();
        }
    }

    public function detail($id){
        $info = $this->model->show_info($id);
        $this->assign('info',$info);
        $this->assign('userId',$this->userId);
        $this->assign('curDate',date('Y-m-d'));
        return $this->fetch();
    }
    public function pay(){
        $post = [
            'id' => input('id',0),
            'true_money' => input('true_money/f',0),
            'pay_time' => getTime(),
            'payee' => input('payee',''),
            'remark' => input('remark',''),
            'pay_type' => input('pay_type',0),
            'pay_data' => input('pay_data',[]),
        ];
        if($post['id'] <= 0){
            $this->error('参数错误');
        }
        if($post['true_money'] <= 0){
            $this->error('实收金额必须大于0');
        }
        if(!in_array($post['pay_type'],[1,2,3,4,5,6])){
            $this->error('请选择收款方式');
        }
        if($post['pay_type'] == 6 && empty($post['pay_data'])){
            $this->error('参数错误');
        }
        $paydata = [];
        if($post['pay_type'] == 6){
            foreach ($post['pay_data'] as $k=>$v){
                $v = floatval($v);
                if($v > 0){
                    $paydata[$k] = $v;
                }
            }
        }
        $post['pay_data'] = $paydata?json_encode($paydata):'';

        $user = Db::name('user')
              ->where('id',$this->userId)->find();
        $post['payee'] = $user?$user['real_name']:'';
        $ret = $this->model->paysave($post,$this->userId);
        if($ret){
            $this->success('操作成功');
        }else{
            $this->error($this->model->geterror());
        }
    }
    public function print($id){
        $info = $this->model->show_info($id);
        $this->assign('info',$info);
        $this->assign('curDate',getTime());
        return $this->fetch();
    }

    public function cancel($id) {
        if(request()->isGet()){

            $this->assign('id',$id);
            return $this->fetch();

        }else{
            $post = request()->post();
            if(!isset($post['cancel_reason'])|| empty($post['cancel_reason'])){
                $this->error('返销原因不能为空');
            }
            $ret = $this->model->cancel($post,$id,$this->userId);
            if($ret){
                $this->success('操作成功');
            }else{
                $this->error($this->model->getError());
            }
        }
    }

    public function export(){
        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        set_time_limit(0);
        ini_set("memory_limit", "1024M");

        //实例化PHPExcel类
        $excel = new \PHPExcel();
        $sheet = $excel->setActiveSheetIndex(0);
        $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
        foreach ($arr as $k=>$v) {
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(false);//换行
            $excel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
            $excel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $excel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
        }
        $sn = input('sn','','trim');
        if($sn){
            $map[] = ['sn','like','%'.$sn.'%'];
        }
        $name = input('name','','trim');//用户
        if($name){
            $map1[] = ['name','like','%'.$name.'%'];
            $user = Db::name('owner')
                ->where($map1)
                ->where('del',0)
                ->where('org_id',$this->orgId)
                ->where('enable',1)
                ->column('id');
            if(!empty($user)){
                $map[] = ['owner_id','in',$user];

            }else{
                $map[] = ['owner_id','=',0];
            }
        }
        $status = input('status','','trim');
        if($status != ''){
            $map[] = ['status','=',$status];
        }

        $b = input('start','','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];
        }
        $map2 = [];
        $c = input('wstart','','trim');
        $d = input('wend','','trim');
        if($c){
            $c = date('Y-m-d',strtotime($c));
            $map2[] = ['end','>=',$c];
        }
        if($d){
            $d = date('Y-m-d',strtotime($d));
            $map2[] = ['start','<=',$d];
        }
        if(!empty($map2)){
            $logs = Db::name('house_pay_log')->where($map2)
                ->where('org_id',$this->orgId)
                ->distinct(true)->column('pay_id');
            if($logs){
                $map[] = ['id','in',$logs];
            }else{
                $map[] = ['id','=',-1];
            }
        }

        $map[] = ['del','=',0];
        $map[] = ['org_id','=',$this->orgId];
        $map= empty($map) ? true: $map;
        //数据查询
        $lists = db($this->table)->where($map)->order('id','desc')->select();
        foreach ($lists as $k=>$v){
            $lists[$k]['full_name'] = Db::name('owner')
                ->where('id',$v['owner_id'])
                ->value('name');

            $lists[$k]['xq_title'] = $this->getTableField('house_areas',['id'=>$v['area_id']],'title');
            $lists[$k]['name'] = $this->getTableField('owner',['id'=>$v['owner_id']],'name');
            $logs = Db::name('house_pay_log')->where('pay_id',$v['id'])
                ->select();
            $lists[$k]['logs'] = $logs;
        }
        $input = input();
        $fees = $this->model->show_all_fee($this->orgId,$input);

        $sheet->setCellValueByColumnAndRow(0, 1, '小区');
        $sheet->setCellValueByColumnAndRow(1, 1, '客户');
        $sheet->setCellValueByColumnAndRow(2, 1, '票据号');
        $sheet->setCellValueByColumnAndRow(3, 1, '收费日期');
        $sheet->setCellValueByColumnAndRow(4, 1, '收款人');
        $sheet->setCellValueByColumnAndRow(5, 1, '收费方式');
        $sheet->setCellValueByColumnAndRow(6, 1, '预付款');
        $n = 6;
        foreach ($fees as $k=>$v){
            $n++;
            $sheet->setCellValueByColumnAndRow($n, 1, $v['title']);
        }
        $sheet->setCellValueByColumnAndRow($n+1, 1, '总计');

        foreach ($lists as $k=>$v) {
            $sheet->setCellValueByColumnAndRow(0, $k + 2, $v['xq_title']);
            $sheet->setCellValueByColumnAndRow(1, $k + 2, $v['name']);
            $sheet->setCellValueByColumnAndRow(2, $k + 2, $v['sn']);
            $sheet->setCellValueByColumnAndRow(3, $k + 2, $v['pay_time']);
            $sheet->setCellValueByColumnAndRow(4, $k + 2, $v['payee']);
            $payType = ['','现金','预付款','支付宝','银行托收','微信','组合付款'];
            if($v['pay_type'] != 6){ // 1=现金 2=预付款 3=支付宝 4=银行托收 5=微信 6=组合付款
                $sheet->setCellValueByColumnAndRow(5, $k + 2, $payType[$v['pay_type']]);
            }else{
                $str = '组合付款';
                $paydata = $v['pay_data']?json_decode($v['pay_data'],true):[];
                $arr = [];
                foreach ($paydata as $kk=>$vv){
                    $arr[] = $payType[$kk].':'.$vv;
                }
                if($arr){
                    $str .= '('.implode(';',$arr).')';
                }
                $sheet->setCellValueByColumnAndRow(5, $k + 2, $str);
            }
            $sheet->setCellValueByColumnAndRow(6, $k + 2, $v['pre_money']);
            $n = 6;
            foreach ($fees as $kk=>$vv){
                $logs = $v['logs'];
                $n++;
                foreach ($logs as $kkk=>$vvv){
                    if($vvv['fee_id'] == $vv['fee_id']){
                        $sheet->setCellValueByColumnAndRow($n, $k + 2, $vvv['money']);
                    }
                }
            }
            $sheet->setCellValueByColumnAndRow($n+1, $k + 2, $v['money']);
        }

        //设置保存的Excel表格名称
        $filename = '缴费单记录' . date('YmdHis', time()) . '.xls';
        //设置当前激活的sheet表格名称
        $excel->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($excel, 'Excel5');
        //下载文件在浏览器窗口
        return $objWriter->save('php://output');
    }

    public function flist(){
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','id','trim');      //排序列
            $sort = input('sord','desc','trim');        //排序方式
            $order = $sortRow.' '.$sort;
            $map = $this->model->getFeeSearch();
            //数据查询
            $lists = db('house_pay_log_view')->where($map)->limit($start,$length)->order($order)->select();
            foreach ($lists as $k=>$v) {
                $lists[$k]['cate_name'] = (new \app\common\model\House())->cate[$v['cate']];
                $refundMoney = Db::name('house_refund_log')
                   ->where('pay_id',$v['id'])
                   ->where('status','in',[1,2])
                    ->sum('money');
                $lists[$k]['refundMoney'] =  floatval($refundMoney);
                $t = Db::name('house_refund_log')
                    ->where('pay_id',$v['id'])
                    ->count();
                $lists[$k]['is_show_log'] = $t;
                $lists[$k]['cur_user_id'] = $this->userId;
            }
            //数据返回
            $totalCount = db('house_pay_log_view')->where($map)->count();
            $totalPage = ceil($totalCount/$length);
            $result['zje'] = db('house_pay_log_view')
                ->where($map)->sum('money');
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{
            $cType =Db::name('house_c_type')
                ->where('org_id',$this->orgId)
                ->where('del',0)
                ->where('enable',1)
                 ->select();
            $fees  =  (new \app\common\model\HouseFee())->getTree(cur_org_id());;
            $fees = $fees?json_decode(json_encode($fees),true):[];
            $arrs = [];
            foreach ($fees as $kk=>$vv){
                $children = $vv['child']?$vv['child']:[];
                foreach ($children as $kkk=>$vvv){
                    $vvv['parent_title'] = $vv['title'];
                    $arrs[] = $vvv;
                }
            }
            $this->assign('cType',$cType);
            $this->assign('fee',$arrs);
            return $this->fetch();
        }
    }

    public function refund($id) {
        if(request()->isGet()){
            $info = Db::name('house_pay_log_view')
                ->where('id',$id)
                ->find();
            $this->assign('name',$info['name']);
            $this->assign('id',$id);
            return $this->fetch();
        }else{
            $post = request()->post();
            $ret = $this->model->refund($post,$id,$this->userId,$this->orgId);
            if($ret){
                $this->success('操作成功');
            }else{
                $this->error($this->model->getError());
            }
        }
    }
    public function fexport()
    {
        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        set_time_limit(0);
        ini_set("memory_limit", "1024M");
        //排序
        $sortRow = input('sidx','id','trim');      //排序列
        $sort = input('sord','desc','trim');        //排序方式
        $order = $sortRow.' '.$sort;
        $map = $this->model->getFeeSearch();
        //数据查询
        $lists = db('house_pay_log_view')->where($map)->order($order)->select();
        foreach ($lists as $k=>$v) {
            $lists[$k]['cate_name'] = (new \app\common\model\House())->cate[$v['cate']];
            $refundMoney = Db::name('house_refund_log')
                ->where('pay_id',$v['id'])
                ->where('status','in',[1,2])
                ->sum('money');
            $lists[$k]['refundMoney'] =  floatval($refundMoney);
            $t = Db::name('house_refund_log')
                ->where('pay_id',$v['id'])
                ->count();
            $lists[$k]['is_show_log'] = $t;
            $lists[$k]['cur_user_id'] = $this->userId;
        }
        //实例化PHPExcel类
        $excel = new \PHPExcel();
        $sheet = $excel->setActiveSheetIndex(0);
        $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
        foreach ($arr as $k=>$v) {
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(false);//换行
            $excel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
            $excel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $excel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
        }

        $sheet->setCellValueByColumnAndRow(0, 1, '单据号');
        $sheet->setCellValueByColumnAndRow(1, 1, '业主');
        $sheet->setCellValueByColumnAndRow(2, 1, '小区');
        $sheet->setCellValueByColumnAndRow(3, 1, '资源名称');
        $sheet->setCellValueByColumnAndRow(4, 1, '资源属性');
        $sheet->setCellValueByColumnAndRow(5, 1, '类型');
        $sheet->setCellValueByColumnAndRow(6, 1, '收费科目');
        $sheet->setCellValueByColumnAndRow(7, 1, '金额');
        $sheet->setCellValueByColumnAndRow(8, 1, '起止日期');
        $sheet->setCellValueByColumnAndRow(9, 1, '备注');
        $sheet->setCellValueByColumnAndRow(10, 1, '创建日期');
        $sheet->setCellValueByColumnAndRow(11, 1, '退费金额');

        foreach ($lists as $k=>$v) {
            $sheet->setCellValueByColumnAndRow(0, $k + 2, $v['sn']);
            $sheet->setCellValueByColumnAndRow(1, $k + 2, $v['name']);
            $sheet->setCellValueByColumnAndRow(2, $k + 2, $v['area_title']);
            $sheet->setCellValueByColumnAndRow(3, $k + 2, $v['title']);
            $cates = ['','住房','商铺','营业房','储藏室','停车位'];
            $sheet->setCellValueByColumnAndRow(4, $k + 2, $cates[$v['cate']]);
            $sheet->setCellValueByColumnAndRow(5, $k + 2, $v['c_type_name']);
            $sheet->setCellValueByColumnAndRow(6, $k + 2, $v['fee_title']);
            $sheet->setCellValueByColumnAndRow(7, $k + 2, $v['money']);
            if($v['start']){
                $sheet->setCellValueByColumnAndRow(8, $k + 2, $v['start'].' ~ '.$v['end']);
            }else{
                $sheet->setCellValueByColumnAndRow(8, $k + 2, '');
            }

            $sheet->setCellValueByColumnAndRow(9, $k + 2, $v['remark']);
            $sheet->setCellValueByColumnAndRow(10, $k + 2, $v['create_time']);
            $sheet->setCellValueByColumnAndRow(11, $k + 2, $v['refundMoney']);
        }
        //设置保存的Excel表格名称
        $filename = '资源缴费明细_' . date('YmdHis', time()) . '.xls';
        //设置当前激活的sheet表格名称
        $excel->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($excel, 'Excel5');
        //下载文件在浏览器窗口
        return $objWriter->save('php://output');

    }

    public function selfFlist2($id=0){
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','id','trim');      //排序列
            $sort = input('sord','desc','trim');        //排序方式
            $order = $sortRow.' '.$sort;
            $map = $this->model->getFeeSearch();

            $map1[] = ['pay_id','=',$id];
            //数据查询
            $lists = db('house_refund_log_view')
                ->where($map)->where($map1)
                ->limit($start,$length)->order($order)->select();
            foreach ($lists as $k=>$v) {
                $lists[$k]['cate_name'] = (new \app\common\model\House())->cate[$v['cate']];
            }
            //数据返回
            $totalCount = db('house_refund_log_view')
                ->where($map)
                ->where($map1)
                ->count();
            $totalPage = ceil($totalCount/$length);
            $result['money'] = db('house_refund_log_view')
                ->where($map)
                ->where($map1)
                ->where('status','in',[1,2])
                ->sum('money');
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{
            $cType =Db::name('house_c_type')
                ->where('org_id',$this->orgId)
                ->where('del',0)
                ->where('enable',1)
                ->select();
            $fees  =  (new \app\common\model\HouseFee())->getTree(cur_org_id());;
            $fees = $fees?json_decode(json_encode($fees),true):[];
            $arrs = [];
            foreach ($fees as $kk=>$vv){
                $children = $vv['child']?$vv['child']:[];
                foreach ($children as $kkk=>$vvv){
                    $vvv['parent_title'] = $vv['title'];
                    $arrs[] = $vvv;
                }
            }
            $info  = Db::name('house_pay')
                ->where('id',$id)
                ->find();
            $this->assign('m_name','['.$info['sn'].']退款记录');
            $this->assign('cType',$cType);
            $this->assign('fee',$arrs);
            $this->assign('id',$id);
            return $this->fetch();
        }
    }
    public function selfFexport2($id)
    {
        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        set_time_limit(0);
        ini_set("memory_limit", "1024M");
        $map = $this->model->getFeeSearch();
        //排序
        $sortRow = input('sidx','id','trim');      //排序列
        $sort = input('sord','desc','trim');        //排序方式
        $order = $sortRow.' '.$sort;
        $map1[] = ['pay_id','=',$id];
        //数据查询
        $lists = db('house_refund_log_view')
            ->where($map)->where($map1)
            ->order($order)->select();
        foreach ($lists as $k=>$v) {
            $lists[$k]['cate_name'] = (new \app\common\model\House())->cate[$v['cate']];
        }
        //实例化PHPExcel类
        $excel = new \PHPExcel();
        $sheet = $excel->setActiveSheetIndex(0);
        $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
        foreach ($arr as $k=>$v) {
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(false);//换行
            $excel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
            $excel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $excel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
        }

        $sheet->setCellValueByColumnAndRow(0, 1, '单据号');
        $sheet->setCellValueByColumnAndRow(1, 1, '业主');
        $sheet->setCellValueByColumnAndRow(2, 1, '小区');
        $sheet->setCellValueByColumnAndRow(3, 1, '资源名称');
        $sheet->setCellValueByColumnAndRow(4, 1, '资源属性');
        $sheet->setCellValueByColumnAndRow(5, 1, '类型');
        $sheet->setCellValueByColumnAndRow(6, 1, '收费科目');
        $sheet->setCellValueByColumnAndRow(7, 1, '金额');
        $sheet->setCellValueByColumnAndRow(8, 1, '起止日期');
        $sheet->setCellValueByColumnAndRow(9, 1, '备注');
        $sheet->setCellValueByColumnAndRow(10, 1, '状态');
        $sheet->setCellValueByColumnAndRow(11, 1, '退款原因');
        $sheet->setCellValueByColumnAndRow(12, 1, '创建日期');
        $sheet->setCellValueByColumnAndRow(13, 1, '退款姓名');
        $sheet->setCellValueByColumnAndRow(14, 1, '退款账号');
        $sheet->setCellValueByColumnAndRow(15, 1, '退款卡号');

        foreach ($lists as $k=>$v) {
            $sheet->setCellValueByColumnAndRow(0, $k + 2, $v['sn']);
            $sheet->setCellValueByColumnAndRow(1, $k + 2, $v['name']);
            $sheet->setCellValueByColumnAndRow(2, $k + 2, $v['area_title']);
            $sheet->setCellValueByColumnAndRow(3, $k + 2, $v['title']);
            $cates = ['','住房','商铺','营业房','储藏室','停车位'];
            $sheet->setCellValueByColumnAndRow(4, $k + 2, $cates[$v['cate']]);
            $sheet->setCellValueByColumnAndRow(5, $k + 2, $v['c_type_name']);
            $sheet->setCellValueByColumnAndRow(6, $k + 2, $v['fee_title']);
            $sheet->setCellValueByColumnAndRow(7, $k + 2, $v['money']);
            if($v['start']){
                $sheet->setCellValueByColumnAndRow(8, $k + 2, $v['start'].' ~ '.$v['end']);
            }else{
                $sheet->setCellValueByColumnAndRow(8, $k + 2, '');
            }

            $sheet->setCellValueByColumnAndRow(9, $k + 2, $v['remark']);
            $sheet->setCellValueByColumnAndRow(10, $k + 2, $v['status']==0?'已拒绝':($v['status']==1?'退款中':'已退款'));
            $sheet->setCellValueByColumnAndRow(11, $k + 2, $v['reason']);
            $sheet->setCellValueByColumnAndRow(12, $k + 2, $v['refund_time']);
            $sheet->setCellValueByColumnAndRow(13, $k + 2, $v['refund_name']);
            $sheet->setCellValueByColumnAndRow(14, $k + 2, $v['refund_account']);
            $sheet->setCellValueByColumnAndRow(15, $k + 2, $v['refund_bank']);
        }
        //设置保存的Excel表格名称
        $filename = '退款记录_' . date('YmdHis', time()) . '.xls';
        //设置当前激活的sheet表格名称
        $excel->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($excel, 'Excel5');
        //下载文件在浏览器窗口
        return $objWriter->save('php://output');

    }
    public function flist2Print($id){
        $info = Db::name('house_refund_log_view')
            ->where('id',$id)
            ->find();
        $y = date('Y');
        $m = date('m');
        $d = date('d');

        $this->assign('value',$info);
        $this->assign('curDate',getTime());
        $this->assign('y',$y);
        $this->assign('m',$m);
        $this->assign('d',$d);
        return $this->fetch();
    }

    public function flist3() {
        if (request()->isAjax()) {
            //分页参数
            $length = input('rows', 10, 'intval');   //每页条数
            $page = input('page', 1, 'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx', 'id', 'trim');      //排序列
            $sort = input('sord', 'desc', 'trim');        //排序方式
            $order = $sortRow . ' ' . $sort;
            $map = $this->model->getFeeSearch();
            //数据查询
            $lists = db('house_pay_log_view')->where($map)->limit($start, $length)->order($order)->select();
            foreach ($lists as $k => $v) {
                $lists[$k]['cate_name'] = (new \app\common\model\House())->cate[$v['cate']];
            }
            //数据返回
            $totalCount = db('house_pay_log_view')->where($map)->count();
            $totalPage = ceil($totalCount / $length);
            $result['zje'] = db('house_pay_log_view')
                ->where($map)->sum('money');
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }
        else {
            $cType = Db::name('house_c_type')
                ->where('org_id', $this->orgId)
                ->where('del', 0)
                ->where('enable', 1)
                ->select();
            $fees = (new \app\common\model\HouseFee())->getTree(cur_org_id());;
            $fees = $fees ? json_decode(json_encode($fees), true) : [];
            $arrs = [];
            foreach ($fees as $kk => $vv) {
                $children = $vv['child'] ? $vv['child'] : [];
                foreach ($children as $kkk => $vvv) {
                    $vvv['parent_title'] = $vv['title'];
                    $arrs[] = $vvv;
                }
            }
            $this->assign('cType', $cType);
            $this->assign('fee', $arrs);
            return $this->fetch();
        }
    }

    public function fexport3()
    {
        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        set_time_limit(0);
        ini_set("memory_limit", "1024M");
        //排序
        $sortRow = input('sidx','id','trim');      //排序列
        $sort = input('sord','desc','trim');        //排序方式
        $order = $sortRow.' '.$sort;
        $map = $this->model->getFeeSearch();
        //数据查询
        $lists = db('house_pay_log_view')->where($map)->order($order)->select();
        foreach ($lists as $k=>$v) {
            $lists[$k]['cate_name'] = (new \app\common\model\House())->cate[$v['cate']];
        }
        //实例化PHPExcel类
        $excel = new \PHPExcel();
        $sheet = $excel->setActiveSheetIndex(0);
        $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
        foreach ($arr as $k=>$v) {
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(false);//换行
            $excel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
            $excel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $excel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
        }

        $sheet->setCellValueByColumnAndRow(0, 1, '单据号');
        $sheet->setCellValueByColumnAndRow(1, 1, '业主');
        $sheet->setCellValueByColumnAndRow(2, 1, '小区');
        $sheet->setCellValueByColumnAndRow(3, 1, '资源名称');
        $sheet->setCellValueByColumnAndRow(4, 1, '资源属性');
        $sheet->setCellValueByColumnAndRow(5, 1, '类型');
        $sheet->setCellValueByColumnAndRow(6, 1, '收费科目');
        $sheet->setCellValueByColumnAndRow(7, 1, '金额');
        $sheet->setCellValueByColumnAndRow(8, 1, '起止日期');
        $sheet->setCellValueByColumnAndRow(9, 1, '备注');
        $sheet->setCellValueByColumnAndRow(10, 1, '创建日期');

        foreach ($lists as $k=>$v) {
            $sheet->setCellValueByColumnAndRow(0, $k + 2, $v['sn']);
            $sheet->setCellValueByColumnAndRow(1, $k + 2, $v['name']);
            $sheet->setCellValueByColumnAndRow(2, $k + 2, $v['area_title']);
            $sheet->setCellValueByColumnAndRow(3, $k + 2, $v['title']);
            $cates = ['','住房','商铺','营业房','储藏室','停车位'];
            $sheet->setCellValueByColumnAndRow(4, $k + 2, $cates[$v['cate']]);
            $sheet->setCellValueByColumnAndRow(5, $k + 2, $v['c_type_name']);
            $sheet->setCellValueByColumnAndRow(6, $k + 2, $v['fee_title']);
            $sheet->setCellValueByColumnAndRow(7, $k + 2, $v['money']);
            if($v['start']){
                $sheet->setCellValueByColumnAndRow(8, $k + 2, $v['start'].' ~ '.$v['end']);
            }else{
                $sheet->setCellValueByColumnAndRow(8, $k + 2, '');
            }

            $sheet->setCellValueByColumnAndRow(9, $k + 2, $v['remark']);
            $sheet->setCellValueByColumnAndRow(10, $k + 2, $v['create_time']);
        }
        //设置保存的Excel表格名称
        $filename = '子级收费报表明细_' . date('YmdHis', time()) . '.xls';
        //设置当前激活的sheet表格名称
        $excel->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($excel, 'Excel5');
        //下载文件在浏览器窗口
        return $objWriter->save('php://output');

    }

    public function show2(){
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','id','trim');      //排序列
            $sort = input('sord','desc','trim');        //排序方式
            $order = $sortRow.' '.$sort;

            $sn = input('sn','','trim');
            if($sn){
                $map[] = ['sn','like','%'.$sn.'%'];
            }
            $name = input('name','','trim');//用户
            if($name){
                $map1[] = ['name','like','%'.$name.'%'];
                $user = Db::name('owner')
                    ->where($map1)
                    ->where('del',0)
                    ->where('enable',1)
                    ->where('org_id',$this->orgId)
                    ->column('id');
                if(!empty($user)){
                    $map[] = ['owner_id','in',$user];

                }else{
                    $map[] = ['owner_id','=',0];
                }
            }
            $status = input('status','','trim');
            if($status != ''){
                $map[] = ['status','=',$status];
            }
            $b = input('start','','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];
            }
            $map2 = [];
            $c = input('wstart','','trim');
            $d = input('wend','','trim');
            if($c){
                $c = date('Y-m-d',strtotime($c));
                $map2[] = ['end','>=',$c];
            }
            if($d){
                $d = date('Y-m-d',strtotime($d));
                $map2[] = ['start','<=',$d];
            }
            if(!empty($map2)){
                $logs = Db::name('house_pay_log')->where($map2)
                    ->distinct(true)->column('pay_id');
                if($logs){
                    $map[] = ['id','in',$logs];
                }else{
                    $map[] = ['id','=',-1];
                }
            }

            $map[] = ['del','=',0];
            $map[] = ['org_id','=',$this->orgId];
            $map[] = ['is_cancel','=',1];
            $map= empty($map) ? true: $map;
            //数据查询
            $lists = db($this->table)->where($map)->limit($start,$length)->order($order)->select();
            foreach ($lists as $k=>$v){
                $lists[$k]['full_name'] = Db::name('owner')
                    ->where('id',$v['owner_id'])
                    ->value('name');
            }
            //数据返回
            $totalCount = db($this->table)->where($map)->count();
            $totalPage = ceil($totalCount/$length);

            $result['dsh'] = db($this->table)
                ->where($map)
                ->where('status',2)
                ->count();
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{
            return $this->fetch();
        }
    }

    public function detail2($id){
        $info = $this->model->show_info($id);
        $this->assign('curDate',date('Y-m-d'));
        $this->assign('info',$info);

        return $this->fetch();
    }
    public function agree($id){
        $ret = $this->model->dealpay(3,$id,$this->userId);
        if($ret){
            $this->success('操作成功');
        }else{
            $this->error($this->model->getError());
        }
    }

    public function disagree($id){
        $ret = $this->model->dealpay(1,$id,$this->userId);
        if($ret){
            $this->success('操作成功');
        }else{
            $this->error($this->model->getError());
        }
    }

    public function flist2(){
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','id','trim');      //排序列
            $sort = input('sord','desc','trim');        //排序方式
            $order = $sortRow.' '.$sort;
            $map = $this->model->getFeeSearch();

            //数据查询
            $lists = db('house_refund_log_view')
                ->where($map)
                ->limit($start,$length)->order($order)->select();
            foreach ($lists as $k=>$v) {
                $lists[$k]['cate_name'] = (new \app\common\model\House())->cate[$v['cate']];
            }
            //数据返回
            $totalCount = db('house_refund_log_view')
                ->where($map)
                ->count();
            $totalPage = ceil($totalCount/$length);
            $result['money'] = db('house_refund_log_view')
                ->where($map)
                ->where('status','in',[1,2])
                ->sum('money');
            $result['dsh'] = db('house_refund_log_view')
                ->where($map)
                ->where('status',1)
                ->count();
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{
            $cType =Db::name('house_c_type')
                ->where('org_id',$this->orgId)
                ->where('del',0)
                ->where('enable',1)
                ->select();
            $fees  =  (new \app\common\model\HouseFee())->getTree(cur_org_id());;
            $fees = $fees?json_decode(json_encode($fees),true):[];
            $arrs = [];
            foreach ($fees as $kk=>$vv){
                $children = $vv['child']?$vv['child']:[];
                foreach ($children as $kkk=>$vvv){
                    $vvv['parent_title'] = $vv['title'];
                    $arrs[] = $vvv;
                }
            }
            $this->assign('cType',$cType);
            $this->assign('fee',$arrs);
            return $this->fetch();
        }
    }
    public function agreeRefund($id) {
        if(request()->isGet()){
            $this->assign('id',$id);
            return $this->fetch();
        }else{
            $post = request()->post();
            if(!isset($post['voucher'])||!$post['voucher']){
                $this->error('请上传凭证');

            }
            $ret = $this->model->agreeRefund($id,$this->userId,$post);
            if($ret){
                $this->success('操作成功');
            }else{
                $this->error($this->model->getError());
            }
        }

    }

    public function disagreeRefund($id) {
        $ret = $this->model->disagreeRefund($id,$this->userId);
        if($ret){
            $this->success('操作成功');
        }else{
            $this->error($this->model->getError());
        }
    }

    public function fexport2()
    {
        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        set_time_limit(0);
        ini_set("memory_limit", "1024M");
        $map = $this->model->getFeeSearch();
        //排序
        $sortRow = input('sidx','id','trim');      //排序列
        $sort = input('sord','desc','trim');        //排序方式
        $order = $sortRow.' '.$sort;
        //数据查询
        $lists = db('house_refund_log_view')
            ->where($map)
            ->order($order)->select();
        foreach ($lists as $k=>$v) {
            $lists[$k]['cate_name'] = (new \app\common\model\House())->cate[$v['cate']];
        }
        //实例化PHPExcel类
        $excel = new \PHPExcel();
        $sheet = $excel->setActiveSheetIndex(0);
        $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
        foreach ($arr as $k=>$v) {
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(false);//换行
            $excel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
            $excel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getStyle($v.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $excel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
        }

        $sheet->setCellValueByColumnAndRow(0, 1, '单据号');
        $sheet->setCellValueByColumnAndRow(1, 1, '业主');
        $sheet->setCellValueByColumnAndRow(2, 1, '小区');
        $sheet->setCellValueByColumnAndRow(3, 1, '资源名称');
        $sheet->setCellValueByColumnAndRow(4, 1, '资源属性');
        $sheet->setCellValueByColumnAndRow(5, 1, '类型');
        $sheet->setCellValueByColumnAndRow(6, 1, '收费科目');
        $sheet->setCellValueByColumnAndRow(7, 1, '金额');
        $sheet->setCellValueByColumnAndRow(8, 1, '起止日期');
        $sheet->setCellValueByColumnAndRow(9, 1, '备注');
        $sheet->setCellValueByColumnAndRow(10, 1, '状态');
        $sheet->setCellValueByColumnAndRow(11, 1, '退款原因');
        $sheet->setCellValueByColumnAndRow(12, 1, '创建日期');
        $sheet->setCellValueByColumnAndRow(13, 1, '退款姓名');
        $sheet->setCellValueByColumnAndRow(14, 1, '退款账号');
        $sheet->setCellValueByColumnAndRow(15, 1, '退款卡号');

        foreach ($lists as $k=>$v) {
            $sheet->setCellValueByColumnAndRow(0, $k + 2, $v['sn']);
            $sheet->setCellValueByColumnAndRow(1, $k + 2, $v['name']);
            $sheet->setCellValueByColumnAndRow(2, $k + 2, $v['area_title']);
            $sheet->setCellValueByColumnAndRow(3, $k + 2, $v['title']);
            $cates = ['','住房','商铺','营业房','储藏室','停车位'];
            $sheet->setCellValueByColumnAndRow(4, $k + 2, $cates[$v['cate']]);
            $sheet->setCellValueByColumnAndRow(5, $k + 2, $v['c_type_name']);
            $sheet->setCellValueByColumnAndRow(6, $k + 2, $v['fee_title']);
            $sheet->setCellValueByColumnAndRow(7, $k + 2, $v['money']);
            if($v['start']){
                $sheet->setCellValueByColumnAndRow(8, $k + 2, $v['start'].' ~ '.$v['end']);
            }else{
                $sheet->setCellValueByColumnAndRow(8, $k + 2, '');
            }

            $sheet->setCellValueByColumnAndRow(9, $k + 2, $v['remark']);
            $sheet->setCellValueByColumnAndRow(10, $k + 2, $v['status']==0?'已拒绝':($v['status']==1?'退款中':'已退款'));
            $sheet->setCellValueByColumnAndRow(11, $k + 2, $v['reason']);
            $sheet->setCellValueByColumnAndRow(12, $k + 2, $v['refund_time']);
            $sheet->setCellValueByColumnAndRow(13, $k + 2, $v['refund_name']);
            $sheet->setCellValueByColumnAndRow(14, $k + 2, $v['refund_account']);
            $sheet->setCellValueByColumnAndRow(15, $k + 2, $v['refund_bank']);
        }
        //设置保存的Excel表格名称
        $filename = '退款记录_' . date('YmdHis', time()) . '.xls';
        //设置当前激活的sheet表格名称
        $excel->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($excel, 'Excel5');
        //下载文件在浏览器窗口
        return $objWriter->save('php://output');

    }
}