workHour/app/Service/AuxiliaryAccountService.php

<?php

namespace App\Service;

use App\Model\AuxiliaryAccount;
use App\Model\AuxiliaryAccountDetails;
use App\Model\Employee;
use App\Model\ExpenseClaims;
use App\Model\ExpenseClaimsDetails;
use App\Model\Fee;
use App\Model\MonthlyDdOrder;
use App\Model\MonthlyDdOrderDetails;
use App\Model\MonthlyPsOrder;
use App\Model\MonthlyPsOrderDetails;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class AuxiliaryAccountService extends Service
{
    public function auxiliaryAccountList($data,$user){
        $model = $this->setCommon($data, $user);
        $list = $this->limit($model,'',$data);
        $list = $this->fillData($list, $user);

        return [true, $list];
    }

    public function setCommon($data,$user, $field = []){
        if(empty($field)) $field = AuxiliaryAccount::$field;
        $model = AuxiliaryAccount::Clear($user,$data);
        $model = $model->where('del_time',0)
            ->select($field)
            ->orderby('month', 'desc');
        if(! empty($data['month'])) $model->where('month', $data['month']);
        if(! empty($data['code'])) $model->where('code', 'LIKE', '%'.$data['code'].'%');

        if(! empty($data['time'][0]) && ! empty($data['time'][1])) {
            $return = $this->changeDateToTimeStampAboutRange($data['time']);
            $model->where('month','>=',$return[0]);
            $model->where('month','<=',$return[1]);
        }

        return $model;
    }

    public function fillData($data, $user){
        if(empty($data['data'])) return $data;

        $emp = (new EmployeeService())->getEmployeeMap(array_unique(array_column($data['data'],'crt_id')));
        $map = ArchiveService::fillIsArchive(array_unique(array_column($data['data'],'month')), $user);
        foreach ($data['data'] as $key => $value){
            $data['data'][$key]['crt_time'] = $value['crt_time'] ? date('Y-m-d H:i:s',$value['crt_time']) : '';
            $data['data'][$key]['crt_name'] = $emp[$value['crt_id']] ?? '';
            $data['data'][$key]['month'] =  $value['month'] ? date('Y-m',$value['month']) : '';
            $data['data'][$key]['is_archive'] = $map[$value['month']] ?? false;
        }

        return $data;
    }

    public function auxiliaryAccountSummary($data,$user){
        //传参月份
        if(!isset($data['month'])) return [201,'月份必传'];
        $month = $data['month'];
        $monthStart = strtotime($month);
        $monthEnd = strtotime("$month +1 month") - 1;
        //获取员工汇总月份工资
        list($status,$employee_month_amount) = $this->employeeMonthAmount($data,$user,$monthStart,$monthEnd);
        if (!$status) return [$status,$employee_month_amount];
        //获取设备折旧费用
        list($status,$device_month_amount) = $this->deviceMonthAmount($data,$user,$monthStart,$monthEnd);
        if (!$status) return [$status,$device_month_amount];
        //获取费用汇总的费用数据
        list($status,$fee_month_amount) = $this->feeMonthAmount($data,$user,$monthStart,$monthEnd);
        if (!$status) return [$status,$fee_month_amount];

        return [true,[
            "employee_month_amount" => $employee_month_amount,
            "device_month_amount" => $device_month_amount,
            "fee_month_amount" => $fee_month_amount,
        ]];

    }

    private  function employeeMonthAmount($month,$user,$monthStart,$monthEnd){
        $data['top_depart_id'] = $user['top_depart_id'];
        $month_ps_order = MonthlyPsOrder::Clear($user,$data);
        $month_ps_order_id = $month_ps_order->where('del_time',0)->where("month",$monthStart)->first();
        if(empty($month_ps_order_id)){
            return [false,"请补充对应月份人员月度工资信息"];
        }else{
            $month_ps_order_id = $month_ps_order_id->id;
        }
        $model = MonthlyPsOrderDetails::Clear($user,$data);
        $totals = $model->where('main_id', $month_ps_order_id)
            ->where('del_time', 0)
            ->selectRaw('
        employee_id, 
        SUM(
            IFNULL(base_salary, 0) + 
            IFNULL(other, 0) + 
            IFNULL(performance_salary, 0) + 
            IFNULL(bonus, 0)
        ) as total
    ')
            ->groupBy('employee_id')
            ->pluck('total' , 'employee_id')
            ->toArray();

        $entrust_type_map = Employee::whereIn('id', array_keys($totals))
            ->pluck('entrust_type', 'id')
            ->toArray();
        $total_amount = $entrust_in_amount = $entrust_out_amount = 0;
        foreach ($totals as $employee_id => $amount){
            $total_amount = bcadd($amount, $total_amount, 2);
            if(! empty($entrust_type_map[$employee_id])){
                $tmp = $entrust_type_map[$employee_id];
                if($tmp == Employee::WT_TYPE_ONE){
                    $entrust_in_amount = bcadd($amount, $entrust_in_amount, 2);
                }elseif($tmp == Employee::WT_TYPE_TWO){
                    $entrust_out_amount = bcadd($amount, $entrust_out_amount, 2);
                }
            }
        }

        return [true,[
            "total_amount" => $total_amount,
            "entrust_in_amount" => $entrust_in_amount,
            "entrust_out_amount" => $entrust_out_amount,
        ]];
    }

    private  function employeeMonthAmount1($month,$user,$monthStart,$monthEnd){
        $data['top_depart_id'] = $user['top_depart_id'];
        $month_ps_order = MonthlyPsOrder::Clear($user,$data);
        $month_ps_order_id = $month_ps_order->where('del_time',0)->where("month",$monthStart)->first();
        if(empty($month_ps_order_id)){
            return [false,"请补充对应月份人员月度工资信息"];
        }else{
            $month_ps_order_id = $month_ps_order_id->id;
        }
        $model = MonthlyPsOrderDetails::Clear($user,$data);
        //总金额
        $total_amount = $model->where('main_id',$month_ps_order_id)->where('del_time',0)->sum("salary");
        //委内
        $entrust_in_amount = $model->where('main_id',$month_ps_order_id)->where("entrust_type",1)->sum("salary");
        //委外
        $entrust_out_amount = $model->where('main_id',$month_ps_order_id)->where("entrust_type",2)->sum("salary");

        return [true,[
            "total_amount" => $total_amount,
            "entrust_in_amount" => $entrust_in_amount,
            "entrust_out_amount" => $entrust_out_amount,
        ]];
    }

    private  function deviceMonthAmount($month,$user,$monthStart,$monthEnd){
        $data['top_depart_id'] = $user['top_depart_id'];
        $month_ps_order = MonthlyDdOrder::Clear($user,$data);
        $month_ps_order_id = $month_ps_order->where('del_time',0)->where("month",$monthStart)->first();
        if(empty($month_ps_order_id)){
            return [false,"请补充对应月份设备月度折旧信息"];
        }else{
            $month_ps_order_id = $month_ps_order_id->id;
        }
        $model = MonthlyDdOrderDetails::Clear($user,$data);
        //总金额
        $total_amount = $model->where('main_id',$month_ps_order_id)->sum("depreciation_amount");
        return [true,[
            "total_amount" => $total_amount,
        ]];
    }

    private  function feeMonthAmount($month,$user,$monthStart,$monthEnd){
        $data['top_depart_id'] = $user['top_depart_id'];
        $month_ps_order = ExpenseClaims::Clear($user,$data);
        $month_ps_order_id = $month_ps_order->where('del_time',0)->where("month",$monthStart)->first();
        if(empty($month_ps_order_id)){
            return [false,"请补充对应月份费用信息"];
        }else{
            $month_ps_order_id = $month_ps_order_id->id;
        }
        $model = new ExpenseClaimsDetails();
        //总金额
        $list = $model->where('expense_claims_id',$month_ps_order_id)->where('del_time',0)->select("fee_id","amount as total_amount","remark","entrust_type","item_id")->get()->toArray();
        foreach ($list as &$v){
            $v['entrust1_amount'] = $v['entrust_type'] == 1 ?  $v['total_amount'] : 0;
            $v['entrust2_amount'] = $v['entrust_type'] == 2 ?  $v['total_amount'] : 0;
        }
        ///分组
        $fee_type_list = Fee::TopClear($user,$data)->where('del_time',0)->select("title","id","parent_id")->get()->toArray();

        return [true,$this->groupListByRoot($list,$fee_type_list)];
    }

    /**
     * 将报销明细按照一级费用分类进行分组
     * * @param array $list          报销明细列表 (含 fee_id, amount 等)
     * @param array $fee_type_list 费用类型树 (含 id, parent_id, title)
     * @return array
     */
    public function groupListByRoot(array $list, array $fee_type_list)
    {
        // 1. 建立 ID 索引，方便快速查找
        $idMap = array_column($fee_type_list, null, 'id');

        // 2. 预处理映射表：让所有子 ID 直接指向它的最顶层“祖宗” ID
        $childToRoot = [];
        $grouped = [];
        foreach ($fee_type_list as $type) {
            $current = $type;
            // 向上追溯直到 parent_id 为 0，即找到一级分类
            while ($current['parent_id'] != 0) {
                $current = $idMap[$current['parent_id']];
            }
            $childToRoot[$type['id']] = [
                'id'    => $current['id'],
                'title' => $current['title']
            ];
//            if (!isset($grouped[$current['id']])) {
//                $grouped[$current['id']] = [
//                    'first_level_id'    => $current['id'],
//                    'first_level_title' => $current['title'],
//                    'details'           => []
//                ];
//            }
        }

        $map = Fee::whereIn('id', array_unique(array_column($list,'fee_id')))
            ->pluck('title','id')
            ->all();

        // 3. 遍历明细数据进行分组
        foreach ($list as $item) {
            $feeId = $item['fee_id'];
            $item['fee_title'] = $map[$item['fee_id']] ?? "";
            // 获取该费用对应的一级分类信息
            $rootInfo = $childToRoot[$feeId] ?? ['id' => 0, 'title' => '其他费用'];
            $rootId   = $rootInfo['id'];

            if (!isset($grouped[$rootId])) {
                $grouped[$rootId] = [
                    'first_level_id'    => $rootId,
                    'first_level_title' => $rootInfo['title'],
                    'details'           => []
                ];
            }

            $grouped[$rootId]['details'][]    = $item;
        }
        // 4. 重置数组索引并返回
        return array_values($grouped);
    }

    public function auxiliaryAccountAdd($data,$user){
        list($status,$msg) = $this->auxiliaryAccountRule($data, $user);
        if(!$status) return [$status,$msg];

        try {
            DB::beginTransaction();

            $model = new AuxiliaryAccount();
            $model->code = $this->generateBillNo([
                'top_depart_id' => $user['top_depart_id'],
                'type' => ExpenseClaims::Order_type,
                'period' => date("Ym", $data['month'])
            ]);
            $model->month = $data['month'];
            $model->crt_id = $user['id'];
            $model->top_depart_id = $data['top_depart_id'];
            $model->save();
            $this->saveDetail($model->id, time(), $data,$user['id'],$user);

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

        return [true, ''];
    }

    private function saveDetail($id, $time, $data,$crt_id,$user){
        if(! empty($data['details'])){
            $unit = [];
            foreach ($data['details'] as $value){
                $unit[] = [
                    'auxiliary_account_id' => $id,
                    'voucher_date' => strtotime($value['voucher_date']),
                    'voucher_type' => $value['voucher_type'],
                    'voucher_no' => $value['voucher_no'],
                    'voucher_remark' => $value['voucher_remark'],
                    'voucher_amount' => $value['voucher_amount'],
                    'entrust_type' => $value['entrust_type']??"",
                    'type' => $value['type'],
                    'fee_id' => $value['fee_id']??0,
                    'remark' => $value['remark']??"",
                    'entrust1_amount' => $value['entrust1_amount']??0,
                    'entrust2_amount' => $value['entrust2_amount']??0,
                    'aggregation_amount' => $value['aggregation_amount']??00,
                    'total_amount' => $value['total_amount'],
                    'top_depart_id' => $user['top_depart_id'],
                    'item_id' => $value['item_id'],
                    'crt_time' => $time,
                    'crt_id' => $crt_id,
                    'r_id' => $value['r_id']??0,
                ];
            }
            if(!empty($unit)) AuxiliaryAccountDetails::insert($unit);
        }
    }

    private function auxiliaryAccountRule(&$data, $user, $is_add = true){
        $data['top_depart_id'] = $user['top_depart_id'];
        if (empty($data['month'])) return [false, '月份不能为空'];
        $data['month'] = $this->changeDateToDate($data['month']);

        //归档
        list($status, $msg) = ArchiveService::isArchive($data['month'], $user);
        if(! $status) return [false, $msg];

        $monthStart = $data['month'];
        $monthEnd = strtotime('+1 month', $monthStart) - 1;
        if(empty($data['details'])) return [false, '研发支出辅助账单明细不能为空'];

        // 初始化各类型的计数器
        $typeCounters = [];

        $fee_map = Fee::where('top_depart_id', $data['top_depart_id'])
            ->whereIn('id', array_unique(array_column($data['details'], 'fee_id')))
            ->pluck('title', 'id')
            ->all();
        foreach ($data['details'] as $item) {
            if(empty($item['type']) || ! isset(AuxiliaryAccountDetails::Type[$item['type']])) return [false, 'type类型不能为空或错误'];
            $type = $item['type'];
            $tabName = AuxiliaryAccountDetails::Type[$item['type']];

            // 针对当前 type 的行数进行累加
            if($type == AuxiliaryAccountDetails::TYPE_ONE || $type == AuxiliaryAccountDetails::TYPE_TWO|| $type == AuxiliaryAccountDetails::TYPE_THREE){
                if (!isset($typeCounters[$type])) {
                    $typeCounters[$type] = 1;
                } else {
                    $typeCounters[$type]++;
                }
            }else{
                if(empty($item['fee_id'])) return [false, '费用类型id不能为空'];
                $fee_t = $fee_map[$item['fee_id']] ?? "";
                if(empty($fee_t)) return [false, '费用类型不存在'];
                $tabName = $fee_t;
                $new_key = $type . $item['fee_id'];
                if (!isset($typeCounters[$new_key])) {
                    $typeCounters[$new_key] = 1;
                } else {
                    $typeCounters[$new_key]++;
                }
            }
            $errorPrefix = "【{$tabName}】第 " . $typeCounters[$type] . " 行：";

            if (!isset($item['voucher_date'])) {
                return [false, $errorPrefix . "凭证日期缺失"];
            }
            // 将报销日期转换为时间戳
            $claimTime = strtotime($item['voucher_date']);
            // 判断：voucher_date 必须早于 month
            // 如果 voucher_date 是 2026-02-28，而 month 是 2026-03-01，则校验通过
            if ($claimTime < $monthStart || $claimTime > $monthEnd) return [false, $errorPrefix . "凭证日期必须早于当前结算月份（" . date("Y-m", $data['month']) . "）"];

            if (!isset($item['voucher_type'])) {
                return [false, $errorPrefix . "凭证种类缺失"];
            }
            if (!isset($item['voucher_no'])) {
                return [false, $errorPrefix . "凭证号数缺失"];
            }
            if (!isset($item['voucher_remark'])) {
                return [false, $errorPrefix . "凭证摘要缺失"];
            }
            if (!isset($item['voucher_amount'])) {
                return [false, $errorPrefix . "会计凭证记载金额缺失"];
            }
            $res = $this->checkNumber($item['voucher_amount'], 2, 'non-negative');
            if (! $res['valid']) return [false,  $errorPrefix . "会计凭证记载金额" . $res['error']];

            if (!isset($item['aggregation_amount'])) {
                return [false, $errorPrefix . "税法规定的归集金额缺失"];
            }
            $res = $this->checkNumber($item['aggregation_amount'], 2, 'non-negative');
            if (! $res['valid']) return [false,  $errorPrefix . "税法规定的归集金额" . $res['error']];
        }

        $query = AuxiliaryAccount::where('top_depart_id', $user['top_depart_id'])
            ->where('month', $monthStart)
            ->where('del_time', 0);

        if (!$is_add) {
            if (empty($data['id'])) return [false, 'ID不能为空'];
            $query->where('id', '<>', $data['id']);
        }

        if ($query->exists())  return [false, date("Y-m", $monthStart) . '已存在研发支出辅助账单'];

        return [true, ''];
    }

    public function auxiliaryAccountEdit($data,$user){
        list($status,$msg) = $this->auxiliaryAccountRule($data, $user, false);
        if(!$status) return [$status,$msg];

        try {
            DB::beginTransaction();

            $model = AuxiliaryAccount::where('id',$data['id'])->first();
//            $model->save();

            $time = time();
            AuxiliaryAccountDetails::where('del_time',0)
                ->where('auxiliary_account_id', $model->id)
                ->update(['del_time' => $time]);

            $this->saveDetail($model->id, $time, $data,$user['id']);

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

        return [true, ''];
    }

    public function auxiliaryAccountDel($data, $user){
        if($this->isEmpty($data,'id')) return [false,'请选择数据！'];

        try {
            DB::beginTransaction();
            $time = time();

            $month = AuxiliaryAccount::where('del_time',0)
                ->whereIn('id',$data['id'])
                ->pluck('month')
                ->toArray();

            //归档
            list($status, $msg) = ArchiveService::isArchive($month, $user);
            if(! $status) return [false, $msg];

            AuxiliaryAccount::where('del_time',0)
                ->whereIn('id',$data['id'])
                ->update(['del_time' => $time]);

            AuxiliaryAccountDetails::where('del_time',0)
                ->whereIn('auxiliary_account_id', $data['id'])
                ->update(['del_time' => $time]);

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

        return [true, ''];
    }

    public function auxiliaryAccountDetail($data, $user){
        if($this->isEmpty($data,'id')) return [false,'请选择数据！'];
        $customer = AuxiliaryAccount::where('del_time',0)
            ->where('id',$data['id'])
            ->first();
        if(empty($customer)) return [false,'单据不存在或已被删除'];
        $customer = $customer->toArray();
        $customer['crt_name'] = Employee::where('id',$customer['crt_id'])->value('title');
        $customer['crt_time'] =$this->utcTime($customer['crt_time']);
        $map = ArchiveService::fillIsArchive($customer['month'], $user);
        $customer['is_archive'] = $map[$customer['month']] ?? false;
        $customer['month'] =$this->utcTime($customer['month']);

        $details = $this->getDetail($data['id']);
        $customer["details"] = $details;

        return [true, $customer];
    }

    private function getDetail($id){
        $data = AuxiliaryAccountDetails::where('del_time',0)
            ->where('auxiliary_account_id', $id)
            ->get()->toArray();
        $map = Fee::whereIn('id', array_unique(array_column($data,'fee_id')))
            ->pluck('title','id')
            ->all();
        foreach ($data as &$v){
            $v['voucher_date'] = $this->utcTime($v['voucher_date']);
            $v['fee_title'] = $map[$v['fee_id']] ?? "";
        }
        return $data;
    }

    private function utcTime($time){
        return  Carbon::createFromTimestamp($time, 'UTC')
            ->toIso8601ZuluString();
    }

    public function fillDataForExport($data, $column, &$return)
    {
        if (empty($data)) return;

        $mainIds = array_column($data, 'id');
        // 获取详情映射 [expense_claims_id => [details...]]
        $detailsMap = $this->getDetailsMap($mainIds);

        // 默认空行模板
        $defaultRow = array_fill_keys($column, '');

        foreach ($data as $main) {
            $mainId = $main['id'];
            $details = $detailsMap[$mainId] ?? [];

            // 提取主表信息
            $mainInfo = [
                'code'  => $main['code'],
                'month' => $main['month'] ? date('Y-m', $main['month']) : '',
            ];

            if (empty($details)) {
                // 如果没有详情，至少导出一行主表信息
                $return[] = array_merge($defaultRow, $mainInfo);
            } else {
                // 遍历明细，每一行明细都带上主表的 code 和 month
                foreach ($details as $sub) {
                    // 合并主表字段 + 详情字段
                    $fullRow = array_merge($mainInfo, $sub);
                    // 仅保留 column 配置中要求的列
                    $return[] = array_merge($defaultRow, array_intersect_key($fullRow, $defaultRow));
                }
            }
        }
    }

    public function getDetailsMap($main_ids)
    {
        // 1. 获取详情
        $details = AuxiliaryAccountDetails::where('del_time', 0)
            ->whereIn('auxiliary_account_id', $main_ids)
            ->get();

        if ($details->isEmpty()) return [];

        // 2. 批量提取所有关联 ID 用于预加载
        $feeIds = $details->pluck('fee_id')->unique();

        // 3. 建立档案映射 Map
        $feeMap = Fee::whereIn('id', $feeIds)->get()->keyBy('id');

        $res = [];
        foreach ($details as $item) {
            $tmpFee = $feeMap[$item->fee_id] ?? null;
            $t = $tmpFee ? $tmpFee->title : '';

            if($item->type == AuxiliaryAccountDetails::TYPE_ONE || $item->type == AuxiliaryAccountDetails::TYPE_TWO){
                $type_title = AuxiliaryAccountDetails::Type[$item->type] ?? "";
            }else{
                $type_title = $t;
            }
            // 4. 组装明细行字段 (key 要与模板配置中的 export 对应)
            $res[$item->auxiliary_account_id][] = [
                'type_title' => $type_title,
                'voucher_date' => $item->voucher_date ? date('Y-m-d', $item->voucher_date) : '',
                'voucher_type' => $item->voucher_type,
                'voucher_no' => $item->voucher_no,
                'voucher_remark' => $item->voucher_remark,
                'voucher_amount' => $item->voucher_amount,
                'aggregation_amount' => $item->aggregation_amount,
                'total_amount' => $item->total_amount,
                'entrust1_amount' => $item->entrust1_amount,
                'entrust2_amount' => $item->entrust2_amount,
                'remark' => $item->remark,
                'fee_title' => $t,
            ];
        }
        return $res;
    }
}