baokan/app/Http/Controllers/Admin/CustomClockinRecord.php

<?php namespace App\Http\Controllers\Admin;

use App\Models\City;
use App\Models\Custom as Custom;
use Illuminate\Support\Carbon;
use App\Models\WeiBan\Follow as WeiBanFollow;
use App\Models\CustomClockinRecord as Model;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;

/**
 * 客户签到记录
 *
 * @author    jun
 *
 */
class CustomClockinRecord extends Auth{

	protected function _initialize(){
		parent::_initialize();
		$this->assign('breadcrumb1','积分管理');
		$this->assign('breadcrumb2','打卡记录');
	}

	/**
	 * 列表页
	 * 
	 * */
    public function index(Model $Model,Custom $Custom,City $City){
 		// 接受参数
		$code					= request('custom_code','');
		$phone					= request('phone','');
		$username				= request('username','');
		$activeName				= request('active_name','');
		$cityId					= request('city_id',0);
		$status					= request('status');
		$startTime				= request('start_time','');
		$endTime				= request('end_time','');
		// 编码转ID
		$uid					= $Custom->codeToId($code);
		// 查询条件
		$map 					= [];
		// 编码ID
		if( $uid )				$map[] = ['custom.uid','=',$uid];
		if( $phone )			$map[] = ['custom.phone','=',$phone];
		if( $username )			$map[] = ['custom.username','=',$username];
		if( $cityId )			$map[] = ['custom.city_id','=',$cityId];
		if( $activeName )		$map[] = ['score_clockin_active.name','=',$activeName];
		if( $startTime )		$map[] = ['custom_clockin_record.insert_time','>=',Carbon::createFromFormat('Y-m-d',$startTime)->startOfDay()->getTimestamp()];
		if( $endTime )		    $map[] = ['custom_clockin_record.insert_time','<=',Carbon::createFromFormat('Y-m-d',$endTime)->endOfDay()->getTimestamp()];
		if( !is_null($status) )	$map[] = ['custom_clockin_record.status','=',$status];
		// 查询数据
		$list					= $Model->query()
            ->leftJoin('custom','custom.uid','=','custom_clockin_record.custom_uid')
            ->leftJoin('score_clockin_active','score_clockin_active.id','=','custom_clockin_record.active_id')
            ->where($map)
            ->select(['custom.*','score_clockin_active.name as active_name','custom_clockin_record.*'])
            ->orderByDesc('custom_clockin_record.id')
            ->paginate(config('page_num',10))
            ->appends(request()->all());
		// 循环处理数据
		foreach ($list as $key => $value) {
			// 城市名
			$value['city_name'] 	= $value['city_id'] ? $City->getOne($value['city_id'],'name') : '';
			// id转编号
			$value['custom_code'] 	= $Custom->idToCode($value['uid']);
			// 重组
			$list[$key]				= $value;
		}
		// 获取列表
		$cityList					= $City->getCityList();
		// 分配数据
		$this->assign('cityList',$cityList);
		// 分配数据
		$this->assign('empty', '<tr><td colspan="20">~~暂无数据</td></tr>');
		$this->assign('list',$list);
		// 加载模板
		return 						$this->fetch();
    }

    /**
     * 导出表格
     *
     * */
    public function down_excel(Model $Model,Custom $Custom,City $City){
        // 接受参数
        $code					= request('custom_code','');
        $phone					= request('phone','');
        $username				= request('username','');
        $activeName				= request('active_name','');
        $cityId					= request('city_id',0);
        $status					= request('status');
        $startTime				= request('start_time','');
        // 编码转ID
        $uid					= $Custom->codeToId($code);
        // 查询条件
        $map 					= [];
        // 编码ID
        if( $uid )				$map[] = ['custom.uid','=',$uid];
        if( $phone )			$map[] = ['custom.phone','=',$phone];
        if( $username )			$map[] = ['custom.username','=',$username];
        if( $cityId )			$map[] = ['custom.city_id','=',$cityId];
        if( $activeName )		$map[] = ['score_clockin_active.name','=',$activeName];
        if( $startTime )		$map[] = ['custom_clockin_record.insert_time','>=',Carbon::createFromFormat('Y-m-d',$startTime)->startOfDay()->getTimestamp()];
        if( $startTime )		$map[] = ['custom_clockin_record.nsert_time','<=',Carbon::createFromFormat('Y-m-d',$startTime)->endOfDay()->getTimestamp()];
        if( !is_null($status) )	$map[] = ['custom_clockin_record.status','=',$status];
        // 查询数据
        $list					= $Model->query()
            ->leftJoin('custom','custom.uid','=','custom_clockin_record.custom_uid')
            ->leftJoin('score_clockin_active','score_clockin_active.id','=','custom_clockin_record.active_id')
            ->where($map)
            ->select(['custom.*','score_clockin_active.name as active_name','custom_clockin_record.*'])
            ->orderByDesc('custom_clockin_record.id')
            ->get()
            ->toArray();
        // 循环处理数据
        foreach ($list as $key => $value) {
            // 城市名
            $value['city_name'] 	= $value['city_id'] ? $City->getOne($value['city_id'],'name') : '';
            // id转编号
            $value['custom_code'] 	= $Custom->idToCode($value['uid']);
            // 重组
            $list[$key]				= $value;
        }

        try {
            // 去下载
            $this->toDown($list);
        } catch (\Throwable $th) {
            echo $th->getMessage();
        }

    }
    /**
     * 去下载
     */
    private function  toDown($data){
        // 创建新的电子表格对象
        $spreadsheet			= new Spreadsheet();
        // 设置合并单元格的行和列，例如合并A1到B2的单元格
        $sheet					= $this->setStyle($spreadsheet);
        // 从第二行写入
        $row					= 2;
        // 循环写入
        foreach ($data as $key => $value) {

            // 单元格内容写入
            $sheet->setCellValue('A'.$row, $value['id']);
            $sheet->setCellValue('B'.$row, $value['custom_code']);
            $sheet->setCellValue('C'.$row, $value['username']);
            $sheet->setCellValue('D'.$row, $value['active_name']);
            $sheet->setCellValue('E'.$row, $value['phone']);
            $sheet->setCellValue('F'.$row, $value['external_userid']);
            $sheet->setCellValue('G'.$row, $value['city_name']);
            $sheet->setCellValue('H'.$row, date('Y-m-d H:i:s',$value['clockin_time']));
            $sheet->setCellValue('I'.$row, $value['clockin_day']);
            $row++;
        }
        //
        // 创建内容
        $writer 				= IOFactory::createWriter($spreadsheet, 'Xlsx');
        header('Pragma: public');
        header('Content-type:application/vnd.ms-excel');
        header('Content-Disposition: inline;filename=下载打卡记录.xlsx');
        // 输出数据流
        return $writer->save('php://output');
    }
    /**
     * 设置表格样式
     *
     */
    private function setStyle(Spreadsheet $spreadsheet){
        // 选择当前活动的工作表
        $sheet					= $spreadsheet->getActiveSheet();
        // 宽
        $sheet->getColumnDimension('A')->setWidth(15);
        $sheet->getColumnDimension('B')->setWidth(15);
        $sheet->getColumnDimension('C')->setWidth(15);
        $sheet->getColumnDimension('D')->setWidth(15);
        $sheet->getColumnDimension('E')->setWidth(15);
        $sheet->getColumnDimension('F')->setWidth(15);
        $sheet->getColumnDimension('G')->setWidth(15);
        $sheet->getColumnDimension('H')->setWidth(15);
        $sheet->getColumnDimension('I')->setWidth(15);
        $sheet->getColumnDimension('J')->setWidth(15);
        // 默认高度
        $sheet->getDefaultRowDimension()->setRowHeight(18);
        // 加粗第一行
        $sheet->getStyle('A:S')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->getStyle('A1:S1')->getFont()->setBold(true);
        $sheet->getStyle('A1:S1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FF00FF00'); // ARGB颜色代码，例如绿色
        // 设置表格标题
        $sheet
            ->setCellValue('A1', '记录ID')
            ->setCellValue('B1', '客户编码')
            ->setCellValue('C1', '客户昵称')
            ->setCellValue('D1', '活动名称')
            ->setCellValue('E1', '联系方式')
            ->setCellValue('F1', '企微ID')
            ->setCellValue('G1', '客户城市')
            ->setCellValue('H1', '打卡时间')
            ->setCellValue('I1', '连续打卡天数');
        // 返回结果
        return 					$sheet;
    }
}