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

<?php
namespace app\api\controller;

use think\Request;
use app\common\controller\Api;


/**
 * 设备电量管理
 */

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


    /**
     * 设备电量消耗管理左侧菜单
     * @return void|null
     */
    public function getTab()
    {
        // 检查请求方法
        if (!$this->request->isGet()) {
            return $this->error('请求错误');
        }

        // 计算一年前的月份
        $newMonth = date('Y-m', strtotime('-1 year'));

        // 获取月份数据
        $months = db('设备_产量计酬')
            ->where('开工时间', '>', $newMonth . '-01 00:00:00')
            ->group('mouth')
            ->order('mouth desc')
            ->column('DATE_FORMAT(开工时间, "%Y%m") AS mouth');

        // 获取所有车间名称
        $workShops = db('设备_基本资料')
            ->whereNotNull('sys_sbID')
            ->where('sys_sbID', '<>', '')
            ->where('使用部门','<>','检验车间')
            ->distinct('使用部门')
            ->column('使用部门');

        // 组织数据
        $data = array_fill_keys($months, $workShops);

        // 返回成功响应
        $this->success('成功', $data);
    }


    /**
     * 上方表格机台列表
     * @return void|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function MachineList()
    {
        // 判断请求方式是否为GET
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }

        // 获取请求参数
        $params = $this->request->param();

        // 检查必需参数
        if (empty($params) || !isset($params['mouth'])) {
            $this->error('参数错误');
        }

        // 转换为标准日期格式
        $month = substr($params['mouth'], 0, 4) . '-' . substr($params['mouth'], 4, 2);

        // 构建查询条件
        $conditions = !empty($params['workShop']) ? ['使用部门' => $params['workShop']] : [];

        // 获取机台列表
        $machineList = db('设备_基本资料')
            ->where($conditions)
            ->whereNotLike('设备编号', 'YQZ%')
            ->whereNotNull('sys_sbID')
            ->where('sys_sbID', '<>', '')
            ->order('设备编组, 设备编号')
            ->column('rtrim(设备名称)', '设备编号');

        // 准备数据容器
        $data = [];
        $monthLast = date('Y-m', strtotime("{$month} -1 month"));

        // 查询电表数据并优化为一次性查询
        $machineIds = array_keys($machineList);
        $meterData = $this->getBatchMeterData($machineIds, [$monthLast, $month]);
        // 遍历机台列表
        foreach ($machineList as $machineId => $machineName) {
            $lastData = $meterData[$machineId][$monthLast] ?? [];
            $newData = $meterData[$machineId][$month] ?? [];

            if (!empty($lastData) || !empty($newData)) {
                $res = [
                    'MachineCode' => $machineId,
                    'MachineName' => $machineName,
                    'lastMain' => $lastData['主电表'] ?? 0,
                    'lastAuxiliary' => $lastData['辅电表'] ?? 0,
                    'newMain' => $newData['主电表'] ?? 0,
                    'newAuxiliary' => $newData['辅电表'] ?? 0,
                    'mainNumber' => ($newData['主电表'] ?? 0) - ($lastData['主电表'] ?? 0),
                    'auxiliaryNumber' => ($newData['辅电表'] ?? 0) - ($lastData['辅电表'] ?? 0),
                ];
                $data[] = $res;
            }
        }

        $this->success('成功', $data);
    }

    // 批量获取电表数据的方法
    private function getBatchMeterData(array $machineIds, array $months)
    {
        $results = [];

        // 根据机台ID和月份批量查询电表数据 (假设可用的电表数据模型)
        foreach ($months as $month) {
            $data = db('设备_产量计酬')
                ->whereIn('sczl_jtbh', $machineIds)
                ->where(function($query) use ($month) {
                    $startDate = $month . '-01';
                    $endDate = date('Y-m-d', strtotime($startDate . ' +1 month'));
                    $query->where('sczl_rq', '>=', $startDate)
                        ->where('sczl_rq', '<', $endDate);
                })
                ->select();

            // 按设备编号和月份分组存储
            foreach ($data as $row) {
                $results[$row['sczl_jtbh']][$month] = [
                    '主电表' => $row['主电表'] ?? 0,
                    '辅电表' => $row['辅电表'] ?? 0,
                ];
            }
        }

        return $results;
    }
    /**
     * 获取电表数据的私有方法
     * @param $machineId
     * @param $date
     * @return array|bool|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    private function getMeterData($machineId, $date)
    {
        return db('设备_产量计酬')
            ->field('主电表, 辅电表')
            ->where('sczl_rq', 'like', "$date%")
            ->where('主电表', '<>', 0)
            ->where('辅电表', '<>', 0)
            ->where('sczl_jtbh', $machineId)
            ->order('UniqId desc')
            ->find();
    }


    /**
     * 机台电表数据详情
     * @return void
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function MachineDetail()
    {
        if ($this->request->isGet() === false){
            $this->error('请求错误');
        }
        $param = $this->request->param();
        if (!isset($param['mouth']) || !isset($param['machine'])){
            $this->error('参数错误');
        }
        // 转换为标准日期格式
        $month = substr($param['mouth'], 0, 4) . '-' . substr($param['mouth'], 4, 2);
        $where = [
            'sczl_rq' => ['like',$month.'%'],
            'sczl_jtbh' => $param['machine'],
            '主电表' => ['<>',0]
        ];
        $list = db('设备_产量计酬')
            ->field('sczl_jtbh as 机台编号,开工时间,主电表,辅电表')
            ->where($where)
            ->order('sczl_rq desc')
            ->select();
        if (empty($list)){
            $this->error('未找到数据');
        }else{
            $this->success('成功',$list);
        }
    }
}