qyadmin/app/Service/ExportFileService.php

<?php

namespace App\Service;


use App\Exports\ExportOrder;
use App\Exports\MyExport;
use App\Model\Area;
use App\Model\BasicMaterial;
use App\Model\BasicRollFilm;
use App\Model\BasicType;
use App\Model\CarDepart;
use App\Model\CarFiles;
use App\Model\CarType;
use App\Model\Company;
use App\Model\Construction;
use App\Model\ConstructionInfo;
use App\Model\ConstructionOrder;
use App\Model\ConstructionOrderImg;
use App\Model\ConstructionOrderSub;
use App\Model\ConstructionProductInfo;
use App\Model\Employee;
use App\Model\EmployeeDepartPermission;
use App\Model\FoursShop;
use App\Model\InOutRecord;
use App\Model\Inventory;
use App\Model\InventorySub;
use App\Model\KqList;
use App\Model\Material;
use App\Model\MaterialCharge;
use App\Model\MaterialChargeSub;
use App\Model\MaterialOrder;
use App\Model\MaterialOrderApply;
use App\Model\MaterialOrderIn;
use App\Model\MaterialOrderSend;
use App\Model\MaterialOrderSendSub;
use App\Model\MaterialReturn;
use App\Model\MaterialReturnSub;
use App\Model\PaymentReceipt;
use App\Model\PaymentReceiptInfo;
use App\Model\PurchaseOrder;
use App\Model\PurchaseOrderInfo;
use App\Model\RollFilm;
use App\Model\RollFilmCombine;
use App\Model\RollFilmCompany;
use App\Model\RollFilmInventory;
use App\Model\SalesOrder;
use App\Model\SalesOrderOtherFee;
use App\Model\SalesOrderProductInfo;
use App\Model\Storehouse;
use App\Model\Supplier;
use App\Model\Transfer;
use App\Model\TransferSub;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;

class ExportFileService extends Service
{
    //导出文件
    const type_one = 1;
    const type_two = 2;
    const type_three = 3;
    const type_four = 4;
    const type_five = 5;
    const type_six = 6;
    const type_seven = 7;

    //导出文件方法
    protected static $fuc = [
        self::type_one => 'kqCollect',
        self::type_two => 'kqSummary',
        self::type_three => 'kqMonthlyReport',
        self::type_four => '',
        self::type_five => '',
    ];

    protected static $special = [
        self::type_one,
        self::type_two,
        self::type_three,
    ];

    protected static $fuc_name = [
        self::type_one => '考勤统计',
        self::type_two => '每日考勤数据汇总',
        self::type_three => '月度考勤表',
        self::type_four => '',
        self::type_five => '',
    ];

    public static $filename = "";

    public function exportAll($data,$user){
        if(empty($data['type']) || ! isset(self::$fuc[$data['type']])) return [false,'导出文件类型错误或者不存在'];
        self::$filename = self::$fuc_name[$data['type']] ?? "";
        if(! in_array($data['type'], self::$special)) if(empty($data['id'])) return [false,'请选择导出数据'];

//        //不超时
//        ini_set('max_execution_time', 0);
//        //内存设置
//        ini_set('memory_limit', -1);

        $function = self::$fuc[$data['type']];
        list($status, $return) = $this->$function($data,$user);
        if(! $status) return [false, $return];

        return [true, $return];
    }

    public function kqCollect($ergs, $user){
        $service = new KqService();
        list($status, $msg) = $service->kqCollect($ergs, $user);
        if(! $status) return [false, $msg];

        // 导出数据
        $return = $msg;

        $header = ['工号','姓名','考勤天数','总工时','迟到次数','迟到总工时','早退次数','早退总工时','加班次数','加班总工时'];

        return $this->saveExportData($return,$header);
    }

    public function kqSummary($ergs, $user){
        // 导出数据
        $return = [];

        $employee_number = [];
        if(empty($ergs['export_type'])){
            //全部
        }elseif($ergs['export_type'] == 1){
            //部门
            $employee_id = EmployeeDepartPermission::whereIn('depart_id',$ergs['depart_id'])
                ->select('employee_id')
                ->get()->toArray();
            $employee_id = array_column($employee_id,'employee_id');
            $employee_number = Employee::where('del_time',0)
                ->whereIn('id',$employee_id)
                ->select('number')
                ->get()->toArray();
            $employee_number = array_column($employee_number,'number');
        }elseif($ergs['export_type'] == 2){
            //个人
            $employee_number = Employee::where('del_time',0)
                ->whereIn('id',$ergs['employee_id'])
                ->select('number')
                ->get()->toArray();
            $employee_number = array_column($employee_number,'number');
        }else{
            return [false, '导出类型不合法'];
        }
        if(empty($ergs['crt_time'][0]) || empty($ergs['crt_time'][1])) return [false, '请选择导出数据的日期'];
        list($start_time, $end_time) = $this->changeDateToTimeStampAboutRange($ergs['crt_time']);

        //数据
        $list = KqList::where('crt_time', '>=', $start_time)
            ->where('crt_time', '<=', $end_time)
            ->when(! empty($employee_number), function ($query) use ($employee_number) {
                return $query->where('number', $employee_number);
            })
            ->select('number',"emp_fname as name", DB::raw('FROM_UNIXTIME(crt_time, "%Y-%m-%d") as date'), DB::raw('MAX(crt_time) as max_crt_time'), DB::raw('MIN(crt_time) as min_crt_time'))
            ->groupBy('number', DB::raw('FROM_UNIXTIME(crt_time, "%Y-%m-%d")'))
            ->orderBy('number','asc')
            ->get()->toArray();

        foreach ($list as $value){
            $return[] = [
                0 => $value['number'],
                1 => $value['name'],
                2 => $value['date'],
                3 => date("Y-m-d H:i:s",$value['min_crt_time']),
                4 => date("Y-m-d H:i:s",$value['max_crt_time']),
            ];
        }unset($list);

        $header = ["工号", "姓名", "日期", "最早打卡时间", "最晚打卡时间"];

        return $this->saveExportData($return,$header,'kq');
    }

    // 新增方法
    public function kqMonthlyReport($ergs, $user){
        if(empty($ergs['month'])) return [false, '请选择导出月份']; // 格式: 2023-01

        $month = $ergs['month'];
        list($year, $mon) = explode('-', $month);

        $weekMap = [
            'Sun' => '周日',
            'Mon' => '周一',
            'Tue' => '周二',
            'Wed' => '周三',
            'Thu' => '周四',
            'Fri' => '周五',
            'Sat' => '周六'
        ];

        // 获取该月所有天数
        $daysInMonth = $this->get_days_in_month($mon, $year);
        $dates = [];
        $weekdays = [];
        for($d=1;$d<=$daysInMonth;$d++){
            $dates[] = $d;
            $dayOfWeek = date('D', strtotime("$year-$mon-" . str_pad($d, 2, '0', STR_PAD_LEFT)));
            $weekdays[] = $weekMap[$dayOfWeek];
        }
        $dates[] = "合计";

        $employee_id = $ergs['employee_id'] ?? [];
        // 获取员工列表
        $employees = Employee::where('del_time',0)
            ->when(! empty($employee_id) ,  fn($q) => $q->whereIn('id', $employee_id))
            ->select('number','emp_name')
            ->orderBy('number','asc')
            ->get()
            ->toArray();
        $employee_number = array_column($employees,'number');

        // 获取该月考勤数据
        $start_time = strtotime("$year-$mon-01 00:00:00");
        $end_time = strtotime("$year-$mon-$daysInMonth 23:59:59");

        //数据
        $kqList = KqList::where('crt_time', '>=', $start_time)
            ->where('crt_time', '<=', $end_time)
            ->when(! empty($employee_number), function ($query) use ($employee_number) {
                return $query->whereIn('number', $employee_number);
            })
            ->select('number', DB::raw('FROM_UNIXTIME(crt_time, "%d") as day'), DB::raw('ROUND((MAX(crt_time) - MIN(crt_time)) / 3600, 2) AS work_hour'))
            ->groupBy('number', DB::raw('FROM_UNIXTIME(crt_time, "%d")'))
            ->orderBy('number','asc')
            ->get()
            ->toArray();

        // 重组数据
        $kqData = [];
        $columnSums = array_fill(0, $daysInMonth + 3, 0); // 初始化列总和数组 (+3 是因为有编号、姓名和合计列)

        foreach($employees as $emp){
            $row = [$emp['number'], $emp['emp_name']];
            $sum = 0;
            for($d=1;$d<=$daysInMonth;$d++){
                $workHour = "";
                foreach($kqList as $k){
                    if($k['number']==$emp['number'] && intval($k['day'])==$d && $k['work_hour'] > 0){
                        $workHour = $k['work_hour'];
                        $sum += $workHour;

                        // 累加到列总和
                        $columnIndex = $d + 1; // +1 因为前两列是编号和姓名
                        $columnSums[$columnIndex] += $workHour;
                        break;
                    }
                }
                $row[] = $workHour;
            }
            $row[] = $sum;
            $kqData[] = $row;
        }

        // 添加合计行
        $totalRow = ["合计", ""]; // 前两列为"合计"和空字符串
        for($d=1;$d<=$daysInMonth;$d++){
            $columnIndex = $d + 1;
            $totalRow[] = $columnSums[$columnIndex];
        }
        $totalRow[] = array_sum(array_slice($columnSums, 2, $daysInMonth)); // 计算总工时列的总和

        // 将合计行添加到数据末尾
        $kqData[] = $totalRow;

        // 构建导出表头
        $header = [];
        // 第一行: 年月
        $header[] = ["$year-$mon"];
        // 第二行: 日期
        $header[] = array_merge(['日期',''],$dates);
        // 第三行: 星期
        $header[] = array_merge(['星期',''],$weekdays);
        $header[] = ['工号','姓名'];

        return $this->saveExportData($kqData, $header,'kq2');
    }

    function get_days_in_month($month, $year) {
        if (function_exists('cal_days_in_month')) {
            return cal_days_in_month(CAL_GREGORIAN, $month, $year);
        } else {
            // 回退方案
            return (int)date('t', mktime(0, 0, 0, $month, 1, $year));
        }
    }

    public function saveExportData($data, $headers, $type = 'default',$file_name = ''){
        if(empty($file_name)) $file_name = self::$filename . "_". date("Y-m-d") . "_". rand(1000,9999);
        $filename =  $file_name . '.' . 'xlsx';
        $bool = Excel::store(new ExportOrder($data,$type,$headers),"/public/export/{$filename}", null, 'Xlsx', []);
        return [true, $filename];
    }
}