mes-server-api/application/api/controller/MachineList.php

<?php

namespace app\api\controller;

use think\Request;
use app\common\controller\Api;
use think\response\Json;
use function fast\e;

/**
 * 设备维修保养记录
 */

class MachineList extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];


    //设备维修保养记录左侧菜单
    public function getTab()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $year = date('Y',time());
        $lastYear = date('Y', strtotime('-1 year'));
        //年度维修统计
        $yearResult1 = $this->getYearSite($year);
        $yearResult2 = $this->getYearSite($lastYear);
        $data['1、年度维修统计'] = [
            $year => $yearResult1,
            $lastYear => $yearResult2
        ];
        //月度维修记录
        $lastMouth = date('Y-m', strtotime('-1 year'));
        $mouthReault = $this->getMouthSite($lastMouth);
        $data['2、月度维修记录'] = $mouthReault;
        //年度保养记录
        $YearMaintenanceSist1 = $this->getYearMaintenanceSist($year);
        $YearMaintenanceSist2 = $this->getYearMaintenanceSist($lastYear);
        if (!empty($YearMaintenanceSist1)){
            $data['3、年度保养统计'][$year] = $YearMaintenanceSist1;
        }
        if (!empty($YearMaintenanceSist2)){
            $data['3、年度保养统计'][$lastYear] = $YearMaintenanceSist2;
        }
        //月度保养记录
        $mouthMaintenanceSist = $this->getMouthMaintenanceSist($lastMouth);
        $data['4、月度保养记录'] = $mouthMaintenanceSist;
        $this->success('成功',$data);
    }

    private function getYearSite($year)
    {
        $sist = db('设备_维修记录')
            ->where('报障时间','like',$year.'%')
            ->group('使用部门')
            ->column('rtrim(使用部门)');
        $data = [];
        if (!empty($sist)){
            foreach ($sist as $item){
                $count = db('设备_维修记录')
                    ->where('使用部门',$item)
                    ->where('报障时间','like',$year.'%')
                    ->count();
                if ($count !== 0){
                    $data[] = $item.'('.$count.'台次)';
                }
            }
        }
        return $data;
    }

    private function getMouthSite($mouth)
    {
        $mouth = db('设备_维修记录')
            ->where('报障时间','>',$mouth.'-01 00:00:00')
            ->where('报障时间','<',date('Y-m-d H:i:s',time()))
            ->group('date')
            ->order('date desc')
            ->column('DATE_FORMAT(报障时间, "%Y-%m") AS date');
        $data = [];
        foreach ($mouth as $item){
            $sist = db('设备_维修记录')
                ->where('报障时间','like',$item.'%')
                ->group('使用部门')
                ->column('rtrim(使用部门)');
            foreach ($sist as $value){
                $count = db('设备_维修记录')
                    ->where('使用部门',$value)
                    ->where('报障时间','like',$item.'%')
                    ->count();
                $data[$item][] = $value.'('.$count.'台次)';
            }
        }
        return $data;
    }

    private function getYearMaintenanceSist($year)
    {
        $sist = db('设备_保养记录')
            ->where('保养开始时间','like',$year.'%')
            ->group('使用部门')
            ->column('rtrim(使用部门)');
        $data = [];
        if (!empty($sist)){
            foreach ($sist as $item){
                $count = db('设备_保养记录')
                    ->where('使用部门',$item)
                    ->where('保养开始时间','like',$year.'%')
                    ->count();
                if ($count !== 0){
                    $data[] = $item.'('.$count.'台次)';
                }
            }
        }
        return $data;
    }

    private function getMouthMaintenanceSist($mouth)
    {
        $mouth = db('设备_保养记录')
            ->where('保养开始时间','>',$mouth.'-01 00:00:00')
            ->where('保养开始时间','<',date('Y-m-d H:i:s',time()))
            ->group('date')
            ->order('date desc')
            ->column('DATE_FORMAT(保养开始时间, "%Y-%m") AS date');
        $data = [];
        foreach ($mouth as $item){
            $sist = db('设备_保养记录')
                ->where('保养开始时间','like',$item.'%')
                ->group('使用部门')
                ->column('rtrim(使用部门)');
            foreach ($sist as $value){
                $count = db('设备_保养记录')
                    ->where('使用部门',$value)
                    ->where('保养开始时间','like',$item.'%')
                    ->count();
                $data[$item][] = $value.'('.$count.'台次)';
            }
        }
        return $data;
    }

    /**
     * 年度维修记录上方列表
     * @return Json|void
     */
    public function YearMaintenanceList()
    {
        // 验证请求方法
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }

        // 获取并验证参数
        $param = $this->request->param();
        if (empty($param) || !isset($param['sist']) || !isset($param['year'])) {
            $this->error('参数错误');
        }

        $department = $param['sist'];
        $year = $param['year'];

        try {
            // 获取该部门该年份的所有维修设备编号
            $machineQuery = db('设备_维修记录')
                ->where('使用部门', $department)
                ->where('报障时间', 'like', $year . '%')
                ->order('设备编号');

            $machines = $machineQuery->column('distinct(设备编号) as 设备编号');
            if (empty($machines)) {
                $this->success('成功', []);
            }

            // 预加载设备名称
            $machineNames = db('设备_基本资料')
                ->whereIn('设备编号', $machines)
                ->column('rtrim(设备名称) as 设备名称', '设备编号');

            $result = [];

            // 一次性获取所有维修记录
            $allRecords = db('设备_维修记录')
                ->where('使用部门', $department)
                ->where('报障时间', 'like', $year . '%')
                ->field([
                    '设备编号',
                    'DATE_FORMAT(报障时间, "%m") AS month', // 直接提取月份数字
                    'COUNT(*) AS count'
                ])
                ->group('设备编号, DATE_FORMAT(报障时间, "%m")')
                ->select();

            // 按设备编号组织数据
            $recordsByMachine = [];
            foreach ($allRecords as $record) {
                $monthKey = $record['month']; // 直接使用月份数字作为键
                $recordsByMachine[$record['设备编号']][$monthKey] = $record['count'];
            }

            // 构建结果数组
            foreach ($machines as $machineId) {
                $monthData = [];
                $total = 0;

                if (isset($recordsByMachine[$machineId])) {
                    foreach ($recordsByMachine[$machineId] as $month => $count) {
                        $monthData[$month] = $count; // 保持月份键为字符串
                        $total += $count;
                    }
                }

                // 确保月份键是两位数格式
                $formattedMonthData = [];
                foreach ($monthData as $month => $count) {
                    $formattedMonth = str_pad($month, 2, '0', STR_PAD_LEFT);
                    $formattedMonthData[$formattedMonth] = $count;
                }

                $result[] = [
                        '使用部门' => $department,
                        '设备编号' => $machineId,
                        '设备名称' => $machineNames[$machineId] ?? '未知设备',
                        'total' => $total
                    ] + $formattedMonthData;
            }
            return json([
                'code' => 0,
                'msg' => '成功',
                'time' =>time(),
                'data' => $result
            ]);
        } catch (\Exception $e) {
            $this->error('查询失败: ' . $e->getMessage());
        }
    }


    /**
     * 设备维修记录配件详情
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function YearAccessoriesList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param) || !isset($param['machine']) || !isset($param['year']) || !isset($param['sist'])) {
            $this->error('参数错误');
        }
        $where = [];
        if (isset($param['type']) && $param['type'] == '修理') {
            $where['st_jylb'] =['like','修理领用%'];
        }
        $list = db('物料_收发记录')
            ->alias('a')
            ->join('物料_仓库信息 b', 'a.仓库编号 = b.编号','left')
            ->join('设备_基本资料 c', 'a.st_jtbh = c.设备编号','left')
            ->join('物料_存货编码 d', 'a.st_wlbh = d.物料代码')
            ->where('a.st_jtbh',$param['machine'])
            ->where('a.st_rq','like',$param['year'].'%')
            ->where(function ($query) {
                $query->where('a.仓库编号', '107')
                    ->whereOr('a.仓库编号', 'Y107');
            })
            ->where($where)
            ->field(['DATE_FORMAT(a.st_rq, "%y-%m-%d") AS 日期','rtrim(a.st_jylb) as 交易类别','a.st_jtbh AS 机台编号','rtrim(a.仓库编号) as 仓库编号',
                'rtrim(b.名称) as 仓库名称','rtrim(c.使用部门) as 领用部门','a.st_wlbh as 物料编号','rtrim(d.物料名称) as 物料名称','a.st_sl as 领用数量',
                'rtrim(a.st_dw) as 领用单位','a.领用单价','a.st_sl*a.领用单价 as 金额','a.st_desc as 备注'])
            ->order('日期 DESC')
            ->select();
        if (empty($list)) {
            $this->error('未找到数据');
        }else{
            $this->success('成功', $list);
        }
    }

    /**
     * 年度保养统计上方几台列表
     * @return Json|void
     */
    public function YearMaintainList()
    {
        // 验证请求方法
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }

        // 获取并验证参数
        $param = $this->request->param();
        if (empty($param) || !isset($param['sist']) || !isset($param['year'])) {
            $this->error('参数错误');
        }

        $department = $param['sist'];
        $year = $param['year'];

        try {
            // 获取该部门该年份的所有维修设备编号
            $machineQuery = db('设备_保养记录')
                ->where('使用部门', $department)
                ->where('保养开始时间', 'like', $year . '%')
                ->order('设备编号');

            $machines = $machineQuery->column('distinct(设备编号) as 设备编号');

            if (empty($machines)) {
                $this->success('成功', []);
            }

            // 预加载设备名称
            $machineNames = db('设备_基本资料')
                ->whereIn('设备编号', $machines)
                ->column('rtrim(设备名称) as 设备名称', '设备编号');

            $result = [];

            // 一次性获取所有维修记录
            $allRecords = db('设备_保养记录')
                ->where('使用部门', $department)
                ->where('保养开始时间', 'like', $year . '%')
                ->field([
                    '设备编号',
                    'DATE_FORMAT(保养开始时间, "%m") AS month', // 直接提取月份数字
                    'COUNT(*) AS count'
                ])
                ->group('设备编号, DATE_FORMAT(保养开始时间, "%m")')
                ->select();

            // 按设备编号组织数据
            $recordsByMachine = [];
            foreach ($allRecords as $record) {
                $monthKey = $record['month']; // 直接使用月份数字作为键
                $recordsByMachine[$record['设备编号']][$monthKey] = $record['count'];
            }

            // 构建结果数组
            foreach ($machines as $machineId) {
                $monthData = [];
                $total = 0;

                if (isset($recordsByMachine[$machineId])) {
                    foreach ($recordsByMachine[$machineId] as $month => $count) {
                        $monthData[$month] = $count; // 保持月份键为字符串
                        $total += $count;
                    }
                }

                // 确保月份键是两位数格式
                $formattedMonthData = [];
                foreach ($monthData as $month => $count) {
                    $formattedMonth = str_pad($month, 2, '0', STR_PAD_LEFT);
                    $formattedMonthData[$formattedMonth] = $count;
                }

                $result[] = [
                        '使用部门' => $department,
                        '设备编号' => $machineId,
                        '设备名称' => $machineNames[$machineId] ?? '未知设备',
                        'total' => $total
                    ] + $formattedMonthData;
            }
            return json([
                'code' => 0,
                'msg' => '成功',
                'time' =>time(),
                'data' => $result
            ]);
        } catch (\Exception $e) {
            $this->error('查询失败: ' . $e->getMessage());
        }
    }


    /**
     * 月度设备维修记录上方机台列表
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function MonthAccessoriesList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param) || !isset($param['sist']) || !isset($param['mouth'])) {
            $this->error('参数错误');
        }
        $query = db('设备_维修记录')
            ->alias('a')
            ->join('人事_基本资料 b','a.报障人 = b.员工编号','left')
            ->where('a.使用部门', $param['sist'])
            ->where('a.报障时间', 'like', $param['mouth'] . '%')
            ->field('rtrim(a.设备编号) as 设备编号,rtrim(a.设备名称) as 设备名称,a.报障时间,a.维修受理时间,a.修复时间,a.实际维修工时 as 故障维修工时,rtrim(a.验收情况) as 验收情况,
            rtrim(b.员工姓名) as 机长,rtrim(c1.员工姓名) as 维修姓名1,rtrim(c2.员工姓名) as 维修姓名2,rtrim(c3.员工姓名) as 维修姓名3,rtrim(c4.员工姓名) as 维修姓名4,
            rtrim(a.故障现象) as 故障现象,rtrim(a.处理方法) as 处理方法,rtrim(a.使用部门) as 使用部门,a.sys_id as 创建用户,a.sys_rq as 创建时间,a.mod_rq as 修改时间,a.UniqId');
        for($i=1;$i<=4;$i++){
            $tableNumber = 'c'.$i;
            $field = 'a.维修人员'.$i;
            $query->join("人事_基本资料 $tableNumber", "$field = {$tableNumber}.员工编号 AND {$field} IS NOT NULL", 'LEFT');
        }
        $list = $query->order('设备编号')->select();
        if (empty($list)) {
            $this->error('未找到数据');
        }else{
            $this->success('成功', $list);
        }
    }


    /**
     * 月度设备保养记录上方机台列表
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function MouthMaintainList()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param) || !isset($param['sist']) || !isset($param['mouth'])) {
            $this->error('参数错误');
        }
        $fields = 'rtrim(a.设备编号) as 设备编号,rtrim(a.设备名称) as 设备名称,rtrim(a.使用部门) as 使用部门,rtrim(a.保养类型) as 保养类型,a.保养开始时间,a.保养结束时间,
        a.保养工时,rtrim(b.员工姓名) as 执行机修,rtrim(c.员工姓名) as 执行机长,rtrim(a.保养备注) as 保养备注,d.员工姓名 as 验收人员,rtrim(a.验收情况) as 验收情况,
        rtrim(a.sys_id) as 创建用户,a.sys_rq as 创建时间,a.UniqId';
        $query = db('设备_保养记录')
            ->alias('a')
            ->join('人事_基本资料 b','a.执行机修 = b.员工编号','left')
            ->join('人事_基本资料 c','a.执行机长 = c.员工姓名','left')
            ->join('人事_基本资料 d','a.验收人员 = d.员工姓名','left')
            ->field($fields)
            ->where('a.使用部门', $param['sist'])
            ->where('a.保养开始时间', 'like', $param['mouth'] . '%')
            ->order('a.设备编号');
        $list = $query->select();
        if (empty($list)) {
            $this->error('未找到数据');
        }else{
            $this->success('成功', $list);
        }
    }


    /**
     * 设备维修记录修改-》维修信息获取
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function MachineMaintenanceDetail()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param) || !isset($param['UniqId'])) {
            $this->error('参数错误');
        }
        $field = 'rtrim(a.设备编号) as 设备编号,rtrim(a.设备名称) as 设备名称,rtrim(a.使用部门) as 使用部门,a.报障时间,a.等配件时间,a.修复时间,rtrim(a.故障现象) as 故障现象,
        rtrim(a.报障人) as 机长,b.员工编号 as 机长姓名,rtrim(a.维修性质) as 维修性质,a.维修受理时间,a.修复时间,a.等配件时间,
        a.修复时间,a.维修人员1,a.维修人员2,a.维修人员3,a.维修人员4,c1.员工姓名 as 维修人员姓名1,c2.员工姓名 as 维修人员姓名2,c3.员工姓名 as 维修人员姓名3,c4.员工姓名 as 维修人员姓名4,
        rtrim(a.处理方法) as 原因分析及措施,a.实际维修工时,a.故障工时,rtrim(a.验收人) as 验收人,rtrim(a.验收情况) as 验收情况,a.UniqId';
        $query = db('设备_维修记录')
            ->alias('a')
            ->join('人事_基本资料 b','a.报障人 = b.员工编号','left')
            ->field($field)
            ->where('a.UniqId', $param['UniqId']);
        for($i=1;$i<=4;$i++){
            $tableNumber = 'c'.$i;
            $field = 'a.维修人员'.$i;
            $query->join("人事_基本资料 $tableNumber", "$field = {$tableNumber}.员工编号 AND {$field} IS NOT NULL", 'LEFT');
        }
        $list = $query->find();
        if (empty($list)) {
            $this->error('未找到数据');
        }else{
            $this->success('成功', $list);
        }
    }


    /**
     * 设备维修修改
     * @return void
     * @throws \think\Exception
     * @throws \think\db\exception\BindParamException
     * @throws \think\exception\PDOException
     */
    public function MachineMaintenanceUpdate()
    {
        if ($this->request->isPost() === false) {
            $this->error('请求错误');
        }
        $param = Request::instance()->post();
        if (empty($param) || !isset($param['UniqId'])) {
            $this->error('参数错误');
        }
        $data = $param;
        unset($data['UniqId']);
        $data['mod_rq'] = date('Y-m-d H:i:s');
        $sql = db('设备_维修记录')
            ->where('UniqId', $param['UniqId'])
            ->fetchSql(true)
            ->update($data);
        $res = db()->query($sql);
        if ($res !== false) {
            $this->success('修改成功');
        }else{
            $this->error('修改失败');
        }
    }

    /**
     * 设备维修记录添加
     * @return void
     * @throws \think\db\exception\BindParamException
     * @throws \think\exception\PDOException
     */
    public function MachineMaintenanceAdd()
    {
        if ($this->request->isPost() === false) {
            $this->error('请求错误');
        }
        $param = Request::instance()->post();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $param['sys_rq'] = date('Y-m-d H:i:s');
        $param['mod_rq'] = '1900-01-01 00:00:00';
        $sql = db('设备_维修记录')
            ->fetchSql(true)
            ->insert($param);
        $res = db()->query($sql);
        if ($res !== false) {
            $this->success('添加成功');
        }else{
            $this->error('添加失败');
        }
    }


    /**
     * 设备保养记录修改信息获取
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function MachineMaintainDetail()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param) || !isset($param['UniqId'])) {
            $this->error('参数错误');
        }
        $field = 'rtrim(a.设备编号) as 设备编号,rtrim(a.设备名称) as 设备名称,rtrim(a.使用部门) as 使用部门,rtrim(a.保养类型) as 保养类型,a.保养开始时间,a.保养结束时间,
        a.保养工时,rtrim(a.执行机修) as 执行机修,b.员工编号 as 执行机修姓名,a.执行机长,c.员工姓名 as 执行机长姓名,rtrim(a.保养备注) as 保养备注,rtrim(a.验收人员) as 验收人员,
        d.员工姓名 as 验收人员姓名,rtrim(a.验收备注) as 验收备注,rtrim(a.验收情况) as 验收情况,a.UniqId';
        $query = db('设备_保养记录')
            ->alias('a')
            ->join('人事_基本资料 b','a.执行机修 = b.员工编号','left')
            ->join('人事_基本资料 c','a.执行机长 = c.员工姓名','left')
            ->join('人事_基本资料 d','a.验收人员 = d.员工姓名','left')
            ->field($field)
            ->where('a.UniqId', $param['UniqId']);
        $list = $query->find();
        if (empty($list)) {
            $this->error('未找到数据');
        }else{
            $this->success('成功', $list);
        }
    }

    /**
     * 设备保养记录修改
     * @return void
     * @throws \think\Exception
     * @throws \think\db\exception\BindParamException
     * @throws \think\exception\PDOException
     */
    public function MachineMaintainUpdate()
    {
        if ($this->request->isPost() === false) {
            $this->error('请求错误');
        }
        $param = Request::instance()->post();
        if (empty($param) || !isset($param['UniqId'])) {
            $this->error('参数错误');
        }
        $data = $param;
        unset($data['UniqId']);
        $data['mod_rq'] = date('Y-m-d H:i:s');
        $sql = db('设备_保养记录')
            ->where('UniqId', $param['UniqId'])
            ->fetchSql(true)
            ->update($data);
        $res = db()->query($sql);
        if ($res !== false) {
            $this->success('修改成功');
        }else{
            $this->error('修改失败');
        }
    }

    /**
     * 设备保养记录新增
     * @return void
     * @throws \think\db\exception\BindParamException
     * @throws \think\exception\PDOException
     */
    public function MachineMaintainAdd()
    {
        if ($this->request->isPost() === false) {
            $this->error('请求错误');
        }
        $param = Request::instance()->post();
        if (empty($param)) {
            $this->error('参数错误');
        }
        $param['sys_rq'] = date('Y-m-d H:i:s');
        $param['mod_rq'] = '1900-01-01 00:00:00';
        $sql = db('设备_保养记录')
            ->fetchSql(true)
            ->insert($param);
        $res = db()->query($sql);
        if ($res !== false) {
            $this->success('添加成功');
        }else{
            $this->error('添加失败');
        }
    }

    /**
     * 设备保养维修记录删除
     * @return void
     * @throws \think\Exception
     * @throws \think\exception\PDOException
     */
    public function MachineMaintainDelete()
    {
        if ($this->request->isGet() === false) {
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (empty($param) || !isset($param['UniqId']) || !isset($param['type'])) {
            $this->error('参数错误');
        }
        if ($param['type'] == 0) {
            $table = '设备_维修记录';
        }else{
            $table = '设备_保养记录';
        }
        $res = db($table)->where('UniqId', $param['UniqId'])->delete();
        if ($res !== false) {
            $this->success('删除成功');
        }else{
            $this->error('删除失败');
        }
    }
}