workHour/app/Service/BIService.php

<?php

namespace App\Service;

use App\Model\DailyPwOrder;
use App\Model\DailyPwOrderDetails;
use App\Model\Depart;
use App\Model\Employee;
use App\Model\EmployeeDepartPermission;
use App\Model\ExpenseClaims;
use App\Model\ExpenseClaimsDetails;
use App\Model\Fee;
use App\Model\Item;
use App\Model\MonthlyDdOrder;
use App\Model\MonthlyDdOrderDetails;
use App\Model\MonthlyPsOrder;
use App\Model\MonthlyPsOrderDetails;
use App\Model\PLeaveOverOrder;
use App\Model\PLeaveOverOrderDetails;
use App\Model\WorkRangeDetails;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;

class BIService extends Service
{
    private function commonRule($data)
    {
        if (isset($data['time']) && !empty($data['time'])) {
            $start = $this->changeDateToDate($data['time'][0]);
            $end = $this->changeDateToDate($data['time'][1], true);
            $data['month_start'] = date("Y-m-d", $start);
            $data['month_end'] = date("Y-m-d", $end);
        }

        if (!isset($data['month_start'])) $month_start = date('Y-01-01', strtotime('-1 year'));
        else $month_start = date('Y-m-01', strtotime($data['month_start']));
        if (!isset($data['month_end'])) $month_end = date('Y-01-01', strtotime('+1 year', strtotime($month_start)));
        else {
            $start_year = date('Y', strtotime($month_start));
            $end_year = date('Y', strtotime($data['month_end']));
            if ($start_year != $end_year) return [false, "查询不得跨年!", ""];
            $month_end = date('Y-m-01', strtotime($data['month_end'] . ' +1 month'));
        }

        return [true, strtotime($month_start), strtotime($month_end)];
    }

    public function homePageData($data, $user)
    {
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];

        //人员费用
        list($total_man, $salary_map) = $this->getEmployeeSalary($month_start, $month_end, $data, $user);

        //折旧费用
        list($total_zj, $zj_map) = $this->getDeviceSalary($month_start, $month_end, $data, $user);

        //费用报销
        list($total_other, $other_map, $return_fee) = $this->getFeeItemSalary($month_start, $month_end, $data, $user);

        //研发费用总额
        $total = bcadd(bcadd($total_man, $total_zj,3), $total_other,3);

        //研发费用结构

        $rd = [
            0 => [
                'title' => '人工费用',
                'rate' => $total == 0 ? 0 : bcmul(bcdiv($total_man,$total,4),100,2),
                'rate_unit' => '%',
                'total' => $total_man,
                'total_unit' => '￥',
            ],
            1 => [
                'title' => '折旧费用',
                'rate' => $total == 0 ? 0 :bcmul(bcdiv($total_zj,$total,4),100,2),
                'rate_unit' => '%',
                'total' => $total_zj,
                'total_unit' => '￥',
            ],
            2 => [
                'title' => '费用报销',
                'rate' => $total == 0 ? 0 :bcmul(bcdiv($total_other,$total,4),100,2),
                'rate_unit' => '%',
                'total' => $total_other,
                'total_unit' => '￥',
            ],
        ];

        //研发费用趋势
        $rd_trend = $this->makeTrend($month_start, $salary_map, $zj_map, $other_map);

        //研发费用报销占比
        $rd_rate = $return_fee;

        //加班 请假 总时长
        list($over_time, $leave_time) = $this->overLeave($month_start, $month_end, $data, $user);

        // 项目人员工时汇总
        $man_work = $this->manWork($month_start, $month_end, $data, $user);

        //加计扣除金额
        $discount = 0; //todo

        return [true, [
            'rd_total' => $total,
            'rd_unit' => '￥',
            'rd_title' => '研发费用总额', //-----
            'discount_total' => $discount,
            'discount_unit' => '￥',
            'discount_title' => '加计扣除金额', //------
            'over_time' => $over_time, // 加班
            'leave_time' => $leave_time, // 请假
            'rd' => $rd, //研发费用结构
            'rd_trend' => $rd_trend, //研发费用趋势
            'rd_rate' => $rd_rate, // 研发费用报销占比
            'man_work' => $man_work, //项目人员工时汇总
        ]];
    }

    private function getEmployeeSalary($month_start, $month_end, $data, $user)
    {
        $monthly_ps_order = MonthlyPsOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("month", ">=", $month_start)
            ->where("month", "<", $month_end)
            ->pluck('month','id')
            ->toArray();
        $monthly_ps_order_ids = array_keys($monthly_ps_order);

        $month_employee_salary = MonthlyPsOrderDetails::whereIn('main_id', $monthly_ps_order_ids)
            ->select("base_salary","performance_salary","other","bonus", "main_id")
            ->get()
            ->toArray();
        $total = 0;
        $salary_map = [];
        foreach ($month_employee_salary as $value) {
            $currentAmount = bcadd($value['base_salary'], $value['performance_salary'],3);
            $currentAmount = bcadd($currentAmount, $value['other'],3);
            $currentAmount = bcadd($currentAmount, $value['bonus'],3);

            if(isset($monthly_ps_order[$value['main_id']])){
                $month = $monthly_ps_order[$value['main_id']];
                if(isset($salary_map[$month])){
                    $salary_map[$month] = bcadd($salary_map[$month], $currentAmount,3);
                }else{
                    $salary_map[$month] = $currentAmount;
                }
            }

            $total = bcadd($total, $currentAmount,3);
        }

        return [$total, $salary_map];
    }

    private function getDeviceSalary($month_start, $month_end, $data, $user)
    {
        $monthly_dd_order= MonthlyDdOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("month", ">=", $month_start)
            ->where("month", "<", $month_end)
            ->pluck('month','id')
            ->toArray();
        $monthly_dd_order_ids = array_keys($monthly_dd_order);

        $month_device_salary = MonthlyDdOrderDetails::whereIn('main_id', $monthly_dd_order_ids)
            ->select("depreciation_amount as amount", "main_id")
            ->get()
            ->toArray();

        $total = 0;
        $amount_map = [];
        foreach ($month_device_salary as $value) {
            $currentAmount = (string)$value['amount'];

            if(isset($monthly_dd_order[$value['main_id']])){
                $month = $monthly_dd_order[$value['main_id']];
                if(isset($amount_map[$month])){
                    $amount_map[$month] = bcadd($amount_map[$month], $currentAmount,3);
                }else{
                    $amount_map[$month] = $currentAmount;
                }
            }

            $total = bcadd($total, $currentAmount,3);
        }

        return [$total, $amount_map];
    }

    private function getFeeItemSalary($month_start, $month_end, $data, $user)
    {
        $e_order = ExpenseClaims::Clear($user, $data)
            ->where('del_time', 0)
            ->where("month", ">=", $month_start)
            ->where("month", "<", $month_end)
            ->pluck('month','id')
            ->toArray();
        $e_order_ids = array_keys($e_order);

        $e_order_detail = ExpenseClaimsDetails::whereIn('expense_claims_id', $e_order_ids)
            ->select("amount", "expense_claims_id as main_id", "fee_id")
            ->get()
            ->toArray();

        $fee = Fee::TopClear($user, $data);
        $fee_map = $fee->whereIn('id', array_unique(array_column($e_order_detail,'fee_id')))->pluck('title','id')->toArray();

        $total = 0;
        $amount_map = [];
        $fee_amount_map = [];
        foreach ($e_order_detail as $value) {
            $currentAmount = (string)$value['amount'];

            if(isset($e_order[$value['main_id']])){
                $month = $e_order[$value['main_id']];
                if(isset($amount_map[$month])){
                    $amount_map[$month] = bcadd($amount_map[$month], $currentAmount,3);
                }else{
                    $amount_map[$month] = $currentAmount;
                }
            }

            $fee_tmp = $fee_map[$value['fee_id']] ?? "";
            if(! empty($fee_tmp)){
                if(isset($fee_amount_map[$fee_tmp])){
                    $fee_amount_map[$fee_tmp] = bcadd($fee_amount_map[$fee_tmp], $currentAmount,3);
                }else{
                    $fee_amount_map[$fee_tmp] = $currentAmount;
                }
            }

            $total = bcadd($total, $currentAmount,3);
        }

        $return_fee = [];
        foreach ($fee_amount_map as $key => $value){
            $amount = (string)$value;
            $return_fee[] = [
                "title" => $key,
                "total" => $amount,
                "total_unit" => "元",
                "rate" => bcmul(bcdiv($amount,$total,4),100,2),
                "rate_unit" => "%"
            ];
        }unset($fee_amount_map);

        return [$total, $amount_map, $return_fee];
    }

    private function makeTrend($month_start, $salary_map, $zj_map, $other_map)
    {
        $trend = [];

        for ($i = 0; $i < 12; $i++) {
            // 计算每个月第一天的时间戳作为 Key
            $timestamp = strtotime("+$i month", $month_start);

            $monthName = ($i + 1) . '月';

            $trend[$timestamp] = [
                'month' => $monthName,
//                'salary' => "0.000",
//                'zj' => "0.000",
//                'other' => "0.000",
                'total' => "0.000",
            ];
        }

        // 2. 将传入的 Map 数据填充进去
        foreach ($trend as $timestamp => &$item) {
            // 使用 ?? "0" 容错，并强制转为 string 保证 bcadd 精度
            $s = $salary_map[$timestamp] ?? "0";
            $z = $zj_map[$timestamp] ?? "0";
            $o = $other_map[$timestamp] ?? "0";

//            $item['salary'] = bcadd($s, "0", 3);
//            $item['zj'] = bcadd($z, "0", 3);
//            $item['other'] = bcadd($o, "0", 3);

            // 计算该月总计
            $item['total'] = bcadd(bcadd($s, $z, 3), $o, 3);
        }

        return array_values($trend);
    }

    private function overLeave($month_start, $month_end, $data, $user)
    {
        $id = PLeaveOverOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("order_time", ">=", $month_start)
            ->where("order_time", "<", $month_end)
            ->pluck('id')
            ->toArray();

        $p = PLeaveOverOrderDetails::whereIn('main_id', $id)
            ->select("type", "total_min")
            ->get()
            ->toArray();
        $total = $total_1 = 0;
        foreach ($p as $value) {
            $total_min = (string)$value['total_min'];

            if($value['type'] == PLeaveOverOrderDetails::TYPE_ONE){
                $total = bcadd($total, $total_min,2);
            }else{
                $total_1 = bcadd($total_1, $total_min,2);
            }
        }

        // 最终返回或输出前转换
        $total_hours = bcdiv($total, 60, 2);
        $total_1_hours = bcdiv($total_1, 60, 2);

        return [$total_1_hours, $total_hours];
    }

    private function manWork($month_start, $month_end, $data, $user)
    {
        $daily_pw = DailyPwOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("order_time", ">=", $month_start)
            ->where("order_time", "<", $month_end)
            ->select('id','item_id')
            ->get()->toArray();

        $item_map = Item::whereIn('id',array_unique(array_column($daily_pw,'item_id')))
            ->pluck('title','id')
            ->toArray();
        $p = DailyPwOrderDetails::whereIn('main_id', array_column($daily_pw,'id'))
            ->select("item_id", "total_work_min")
            ->get()
            ->toArray();
        $total = 0;
        $map = [];
        foreach ($p as $value) {
            $total_min = (string)$value['total_work_min'];
            if(isset($map[$value['item_id']])){
                $map[$value['item_id']] = bcadd($map[$value['item_id']], $total_min,2);
            }else{
                $map[$value['item_id']] = $total_min;
            }
            $total = bcadd($total, $total_min,2);
        }
        $return = [];
        foreach ($map as $key => $value){
            $rate = bcmul(bcdiv($value, $total,4),100,2);
            $total_hours = bcdiv($value, 60, 2);

            $return[] = [
                'title' => $item_map[$key] ?? "",
                'total_work_hour' => $total_hours,
                'total_work_hour_unit' => "小时",
                "rate" => $rate,
                "rate_unit" => "%",
            ];
        }

        return $return;
    }

    public function initializationCompany($data, $user){
        if(empty($data['title'])) return [false, '公司名称不能为空'];
        if(empty($data['code'])) return [false, '公司代码不能为空'];

        // ^[A-Za-z]+$  表示从头到尾只能是英文字母（不区分大小写）
        if (!preg_match('/^[A-Za-z]+$/', $data['code']) || mb_strlen($data['code']) < 4) return [false, "公司代码必须全为英文且长度需大于等于 4 位"];

        $exists = Depart::where('parent_id', 0)
            ->where('del_time', 0)
            ->where(function($query) use ($data) {
                $query->where('title', $data['title'])
                    ->orWhere('code', $data['code']);
            })
            ->exists();
        if($exists) return [false, '公司名称或公司代码已存在，新增失败'];

        $account = $data['code'] . "_" . 'admin';
        $exists = Employee::where('del_time', 0)
            ->where('account', $account)
            ->exists();
        if($exists) return [false, '创建账号已存在，新增失败'];

        try {
            DB::beginTransaction();

            //创建公司
            $model = new Depart();
            $model->parent_id = 0;
            $model->title = $data['title'];
            $model->code = $data['code'];
            $model->top_depart_id = 0;
            $model->save();
            //公司ID
            $top_depart_id = $model->id;

            $password = $this->generateAt8CharPassword();
            //创建账号
            $model_2 = new Employee();
            $model_2->title = $data['title'];
            $model_2->code = $data['code'];
            $model_2->account = $account;
            $model_2->password = Hash::make($password);
            $model_2->is_admin = Employee::IS_ADMIN_TWO;
            $model_2->top_depart_id = $top_depart_id;
            $model_2->state = Employee::TYPE_ONE;
            $model_2->save();
            $employee_id = $model_2->id;

            //关联人员的部门初始信息
            $model_3 = new EmployeeDepartPermission();
            $model_3->employee_id = $employee_id;
            $model_3->depart_id = 0;
            $model_3->top_depart_id = $top_depart_id;
            $model_3->save();

            //公司上班时段
            $work_range[] = [
                'top_depart_id' => $top_depart_id,
                'start_time_hour' => 9,
                'start_time_min' => 0,
                'end_time_hour' => 12,
                'end_time_min' => 0,
                'total_work_min' => 180,
            ];
            $work_range[] = [
                'top_depart_id' => $top_depart_id,
                'start_time_hour' => 13,
                'start_time_min' => 0,
                'end_time_hour' => 18,
                'end_time_min' => 0,
                'total_work_min' => 300,
            ];
            WorkRangeDetails::insert($work_range);

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

        return [true, ['account' => $account, 'password' => $password]];
    }

    function generateAt8CharPassword() {
        $lettersNumbers = 'abcdefghijkmnpqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ23456789';

        $password = '@'; // 1. 先把固定的 @ 放进去

        // 2. 随机抽取 7 位英文或数字
        for ($i = 0; $i < 7; $i++) {
            $password .= $lettersNumbers[random_int(0, strlen($lettersNumbers) - 1)];
        }

        // 3. 打乱顺序，让 @ 的位置不固定
        return str_shuffle($password);
    }
}