drb/app/Service/ImportService.php

<?php

namespace App\Service;

use App\Exports\TableHeadExport;
use App\Import\ImportAll;
use App\Model\Employee;
use App\Model\Freight;
use App\Model\GiveOut;
use App\Model\Product;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;

class ImportService extends Service
{
    public static $type = [
        'product', //存货
        'freight', //运费设置
        'giveOut', //发放统计
    ];

    public function getTableTitleXls($data,$user){
        if(empty($data['type'])) return [false,'缺少类型'];
        if(! in_array($data['type'],self::$type)) return [false,'类型不存在'];

        //获取配置文件
        $fuc = $data['type'];
        list($status,$return) = $this->$fuc($data,$user);
        list($msg,$filename) = $return;
        if(! $status) return [false, $msg];

        $headers = array_column($msg,'value');
        $comments = [];
        foreach ($msg as $value){
            if(! empty($value['comments'])) $comments[$value['value']] = $value['comments'];
        }
        Excel::store(new TableHeadExport([], $headers, $comments),"/public/export/{$filename}", null, 'Xlsx', []);
        return [true, ['file' => $filename]];
    }

    private function getTableConfig($type = ""){
        if(empty($type)) return [];

        //获取配置文件
        $config = "excel." . $type . "Table";
        return config($config) ?? [];
    }

    private function product($data,$user){
        $config_array = $this->getTableConfig($data['type']);
        if(empty($config_array)) return [false, ['导入配置表头文件不存在','']];

        //生成下载文件
        $filename =  "存货导入模板_" . time() . '.' . 'xlsx';

        return [true, [$config_array, $filename]];
    }

    private function freight($data,$user){
        $config_array = $this->getTableConfig($data['type']);
        if(empty($config_array)) return [false, ['导入配置表头文件不存在','']];

        //生成下载文件
        $filename =  "运费设置导入模板_" . time() . '.' . 'xlsx';

        return [true, [$config_array, $filename]];
    }

    private function giveOut($data,$user){
        $config_array = $this->getTableConfig($data['type']);
        if(empty($config_array)) return [false, ['导入配置表头文件不存在','']];

        //生成下载文件
        $filename =  "发放统计导入模板_" . time() . '.' . 'xlsx';

        return [true, [$config_array, $filename]];
    }

    //导入入口
    public function importAll($data,$user){
//        //不超时
//        ini_set('max_execution_time', 0);
//        //内存设置
//        ini_set('memory_limit', -1);
//        $reader = IOFactory::createReader('Xlsx');
//        $reader->setReadDataOnly(true); // 只读取有数据的单元格
//        $spreadsheet = $reader->load($data['file']);
//        dd($spreadsheet);
//        // 创建一个Reader对象
//        $reader = IOFactory::createReader('Xlsx'); // 根据你的文件格式选择合适的reader
//
//// 加载Excel文件
//        $spreadsheet = $reader->load($data['file']);
//
//// 获取第一个工作表
//        $worksheet = $spreadsheet->getActiveSheet();
//
//// 获取总行数
//        $totalRows = $worksheet->getHighestRow();dd($totalRows);
        if(empty($data['type'])) return [false,'缺少导入类型，导入失败'];
        if(! in_array($data['type'],self::$type)) return [false,'导入类型不存在，导入失败'];
        if(empty($data['file'])) return [false,'导入文件不能为空'];

        try {
            $import = new ImportAll();
            //设置导入人id
            $import->setCrt($user['id']);
            $import->setUser($user);
            $import->setType($data['type']);
            $other = $data;
            unset($other['file']);
            $import->setOtherParam($other);

            //导入
            \Maatwebsite\Excel\Facades\Excel::import($import,$data['file']);
            if($import->getMsg()) {
                $bool = $import->getIsLongText();
                if($bool) {
                    return [0, $import->getMsg()];
                }else{
                    return [false, $import->getMsg()];
                }
            }
        }catch (\Throwable $exception) {
            return [false, $exception->getMessage() . ' (Code: ' . $exception->getCode() . ', Line: ' . $exception->getLine() . ')'];
        }

        return [true, ''];
    }

    public function productImport($array, $user, $other_param){
        $upload = $array[0];
        list($status, $msg) = $this->compareTableAndReturn($upload, $other_param);
        if(! $status) return [false, $msg];
        $table_config = $msg;

        // 去除表头
        unset($array[0]);
        if(empty($array)) return [false, '导入数据不能为空'];

        list($array, $error) = $this->checkCommon($array, $table_config);
        if(! empty($error)) return [0, $error];
        if(empty($array)) return [false, '导入数据不能为空'];

        //查找产品
        $product_map = Product::whereIn("code", array_column($array,0))
            ->where('del_time',0)
            ->pluck('id','code')
            ->toArray();

        $time = time();
        $insert = $update = [];
        foreach ($array as $value){
            $tmp = [];
            foreach ($value as $k => $val){
                $field = $table_config[$k]['key'];
                $tmp[$field] = $val;
            }

            if(isset($product_map[$tmp['code']])){
                $product_id = $product_map[$tmp['code']];
                //产品主表
                $update[$product_id] = $tmp;
            }else{
                $tmp['crt_id'] = $user['id'];
                $tmp['crt_time'] = $time;

                //产品主表
                $insert[$tmp['code']] = $tmp;
            }
        }

        try{
            //新增
            if(! empty($insert)){
                Product::insert($insert);
            }

            //编辑
            if(! empty($update)){
                foreach ($update as $p_id => $value){
                    Product::where('id',$p_id)
                        ->update($value);
                }
            }

            DB::commit();
        }catch (\Exception $e){
            DB::rollBack();
            return [false, $e->getMessage() . $e->getLine() . $e->getCode()];
        }

        return [true, ''];
    }

    public function freightImport($array, $user, $other_param){
        $upload = $array[0];
        list($status, $msg) = $this->compareTableAndReturn($upload, $other_param);
        if(! $status) return [false, $msg];
        $table_config = $msg;

        // 去除表头
        unset($array[0]);
        if(empty($array)) return [false, '导入数据不能为空'];

        list($array, $error) = $this->checkCommon($array, $table_config);
        if(! empty($error)) return [0, $error];
        if(empty($array)) return [false, '导入数据不能为空'];

        $time = time();
        $insert = [];
        foreach ($array as $value){
            $tmp = [];
            foreach ($value as $k => $val){
                $field = $table_config[$k]['key'];
                $tmp[$field] = $val;
            }

            $tmp['crt_id'] = $user['id'];
            $tmp['crt_time'] = $time;

            $insert[] = $tmp;
        }

        try{
            Freight::where('del_time',0)
                ->update(['del_time' => $time]);
            //新增
            if(! empty($insert)) Freight::insert($insert);

            DB::commit();
        }catch (\Exception $e){
            DB::rollBack();
            return [false, $e->getMessage() . $e->getLine() . $e->getCode()];
        }

        return [true, ''];
    }

    public function giveOutImport($array, $user, $other_param){
        $upload = $array[0];
        list($status, $msg) = $this->compareTableAndReturn($upload, $other_param);
        if(! $status) return [false, $msg];
        $table_config = $msg;

        // 去除表头
        unset($array[0]);
        if(empty($array)) return [false, '导入数据不能为空'];

        list($array, $error) = $this->checkCommon($array, $table_config);
        if(! empty($error)) return [0, $error];
        if(empty($array)) return [false, '导入数据不能为空'];

        $time = time();
        $new_array = [];
        foreach ($array as $value){
            $tmp = [];
            foreach ($value as $k => $val){
                $field = $table_config[$k]['key'];
                $tmp[$field] = $val;
            }
            $tmp['crt_id'] = $user['id'];
            $tmp['crt_time'] = $time;

            $new_array[] = $tmp;
        }
        $emp_map = Employee::where('del_time',0)
            ->whereIn('emp_name', array_column($new_array,'employee_id_1_title'))
            ->pluck('id','emp_name')
            ->toArray();

        $error_2 = [];
        foreach ($new_array as $key => $value){
            $line_number = $key + 2;
            $line = '第' . $line_number . '行';
            if(! isset($emp_map[$value['employee_id_1_title']])) {
                $error_2[] = $line . "业务员不存在或已被删除";
            }else{
                $new_array[$key]['employee_id_1'] = $emp_map[$value['employee_id_1_title']];
            }
            list($status, $msg) = $this->convertExcelCellToDate($value['send_time']);
            if(! $status){
                $error_2[] = $line . "发放日期错误";
            }else{
                $new_array[$key]['send_time'] = $msg;
            }
            if (strpos($value['belong_time'], '|') !== false) {
                list($start,$end) = explode('|', $value['belong_time']);
                $startStamp = strtotime($start);
                $endStamp = strtotime($end);
                if(! $startStamp || ! $endStamp || $startStamp > $endStamp) {
                    $error_2[] = $line . "归属日期错误";
                }else{
                    $new_array[$key]['start_time'] = $startStamp;
                    $new_array[$key]['end_time'] = $endStamp;
                    unset($new_array[$key]['belong_time']);
                }
            }else{
                list($status, $msg) = $this->convertExcelCellToDate($value['belong_time']);
                if(! $status){
                    $error_2[] = $line . "归属日期错误";
                }else{
                    $new_array[$key]['start_time'] = $msg;
                    $new_array[$key]['end_time'] = $msg;
                    unset($new_array[$key]['belong_time']);
                }
            }
            $res = $this->checkNumber($value['give_out_amount'],2,'positive');
            if(! $res['valid']) $error_2[] = $line . "分红已发放金额：" . $res['error'];
        }
        if(! empty($error_2)) {
            $error_string = implode('|', $error_2);
            return [0, $error_string];
        }

        try{
            //新增
            if(! empty($new_array)) GiveOut::insert($new_array);

            DB::commit();
        }catch (\Exception $e){
            DB::rollBack();
            return [false, $e->getMessage() . $e->getLine() . $e->getCode()];
        }

        return [true, ''];
    }

    //公共校验
    private function checkCommon($array, $table_config){
        $error = [];

        $uniqueValues = []; // 用于存储需要唯一性检查的值
        // 第一次遍历：收集所有唯一字段的值
        foreach ($array as $key => $value) {
            foreach ($value as $k => $v) {
                if (isset($table_config[$k]) && ! empty($table_config[$k]['unique'])) {
                    $uniqueValues[$k][] = [
                        'value' => trim($v),
                        'line' => $key
                    ];
                }
            }
        }
        // 检查唯一性
        foreach ($uniqueValues as $column => $values) {
            $valueCount = [];
            foreach ($values as $item) {
                $valueCount[$item['value']][] = $item['line'];
            }
            foreach ($valueCount as $value => $lines) {
                if (count($lines) > 1) {
                    $lineNumbers = implode(',', $lines);
                    $error[] = $table_config[$column]['value'] . "重复，在第{$lineNumbers}行出现重复值：{$value}";
                }
            }
        }
        
        // 第二次遍历：进行其他验证
        foreach ($array as $key => $value){
            $line = '第' . $key . '行';
            $rowData = array_filter($value);
            if (empty($rowData)) {
                unset($array[$key]);
            } else{
                foreach ($value as $k => $v){
                    $row = '第' . $k . '列';
                    $tmp_v = trim($v);
                    if(! isset($table_config[$k])){
                        $error[] = $line . $row . "数据不存在";
                    }else{
                        $table_tmp = $table_config[$k] ?? [];
                        if(! empty($table_tmp['required'])){
                            if ($tmp_v === '' || ! isset($tmp_v)) {
                                $error[] = $line . $table_tmp['value'] . "必填";
                            }
                        }
                    }

                    if(empty($tmp_v) && isset($table_tmp['default'])) $tmp_v = $table_tmp['default'];
                    $value[$k] = $tmp_v;
                }

                $array[$key] = $value;
            }
        }

        $error_string = "";
        if(! empty($error)) $error_string = implode('|', $error);

        return [$array, $error_string];
    }

    //模板校验
    private function compareTableAndReturn($upload, $param){
        if(empty($upload)) return [false, '表头不能为空'];
        $config_array = $this->getTableConfig($param['type']);
        if(empty($config_array)) return [false, '导入配置表头文件不存在'];

        foreach ($config_array as $key => $value){
            $key_position = $key + 1;
            if(! isset($upload[$key])) return [false, "第" . $key_position . "列表头缺失"];
            $tmp_v = trim($upload[$key]);
            if($tmp_v != $value['value']) return [false, "第" . $key_position . "列表头与模板不符合,请重新下载模板"];
        }

        return [true, $config_array];
    }

    //转换日期
    function convertExcelCellToDate($cellValue) {
        // 尝试将单元格值转换为浮点数（Excel 日期序列号）
        $excelTimestamp = filter_var($cellValue, FILTER_VALIDATE_FLOAT);

        if ($excelTimestamp !== false && $excelTimestamp > 0) {
            // 如果成功转换并且值大于0，则认为是Excel日期序列号
            try {
                $dateTimeObject = Date::excelToDateTimeObject($cellValue);

//                if ($dateTimeObject->format('H:i:s') === '00:00:00') {
//                    // 如果是，则将时间设置为 '23:59:00'
//                    $dateTimeObject->setTime(23, 59);
//                }

                // 现在你可以格式化这个日期了
                $formattedDate = $dateTimeObject->format('Y-m-d');
                if(! strtotime($formattedDate)) return [false, ''];

                return [true,  strtotime($formattedDate)];
            } catch (\Exception $e) {
                // 处理转换失败的情况
                return [false, '单元格日期格式转换时间戳失败'];
            }
        }

        // 如果不是有效的浮点数，则尝试按照多种日期格式解析
        if(! strtotime($cellValue)) return [false, '单元格文本格式转换时间戳失败'];

        return [true, strtotime($cellValue)];
    }
}