jkldhtjk/src/main/java/com/wxxcx/index/WxxcxIndexController.java

package com.wxxcx.index;

import java.io.*;
import java.math.BigDecimal;
import java.security.MessageDigest;
import java.sql.Timestamp;
import java.sql.Types;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.admin.alarm_event.AlarmEvent;
import com.admin.alarm_event.StayTime;
import com.alibaba.excel.EasyExcel;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.admin.dev.DevVO;
import com.admin.floorPlan.FloorPlanVO;
import com.admin.group.GroupProVO;
import com.admin.group.GroupVO;
import com.admin.groupShare.GroupShareVO;
import com.admin.lbt.LbtVO;
import com.admin.map.GroupDevMapVO;
import com.admin.sbgj.SbgjVO;
import com.admin.areaBase.Base;
import com.wxxcx.room.DevRoomVO;
import com.wxxcx.room.RoomVO;
import com.wxxcx.share.ShareVO;
import com.wxxcx.targets.TargetVO;
import com.wxxcx.user.MiniUserVO;
import com.wxxcx.user.WxRelatiion;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.pub.constant.Constants;
import com.pub.env.Environment;
import com.pub.jdbc.BaseDAO;
import com.pub.log.Log;
import com.pub.page.PageRecord;
import com.pub.util.HttpUtil;
import com.pub.util.JSONUtil;
import com.pub.util.MqttUtil;
import com.pub.util.MsgUtil;
import com.pub.util.PageUtil;
import com.pub.util.R;
import com.pub.upload.FileUploadController;
import com.pub.util.ImageUtil;

@RequestMapping("/s/wxxcx/index")
@Api(tags = "小程序接口")
@RestController
public class WxxcxIndexController<AesException extends Throwable> {

    @Resource
    private JdbcTemplate jdbcTemplate;


    @Autowired
    private BaseDAO baseDAO;

    @GetMapping("/login")
    @ApiOperation(value = "登录接口")
    public R login(String code) {
        R r = JSONUtil.getSuccessMsg(null);
        String res = HttpUtil.request(
                "https://api.weixin.qq.com/sns/jscode2session?appid=" + Constants.WXXCXAPPID + "&secret="
                        + Constants.WXXCXAPPSECRET + "&js_code=" + code + "&grant_type=authorization_code",
                null, false, null, null);
        JSONObject obj = JSONObject.parseObject(res);
        r.data.put("openid", obj.getString("openid"));
        r.data.put("unionid", obj.getString("unionid"));
        // 查询是否存在该 openid
        List<WxRelatiion> list = baseDAO.getJdbcTemplate().query(
                "SELECT * FROM wx_relation WHERE openid = ?",
                new Object[]{obj.getString("openid")},
                new BeanPropertyRowMapper<>(WxRelatiion.class)
        );
        if (list.size() <= 0) {
            String insertSql = "INSERT INTO wx_relation (openid, unionid,fwhopenid) VALUES (?, ?,?)";
            baseDAO.getJdbcTemplate().update(insertSql, obj.getString("openid"), obj.getString("unionid"), null);
        }
        return r;
    }

    /**
     * 小程序用户登录
     *
     * @param phonef
     * @param password
     * @return
     * @author wy
     */
    @GetMapping("/checkUser")
    @ApiOperation(value = "手机号登录")
    public R checkUser(String phone, String password, String openid) {
        R r = JSONUtil.getSuccessMsg(null);
        // 查询用户信息
        Object[] args = new Object[]{phone, password};
        List<MiniUserVO> users = baseDAO.getJdbcTemplate().query(
                "SELECT * FROM user_info WHERE IFNULL(delete_tag, 0) = 0 AND phone = ? AND password = ?", args,
                new BeanPropertyRowMapper<>(MiniUserVO.class));
        if (users.size() > 0) {
            MiniUserVO user = users.get(0);
            if (StringUtils.isEmpty(user.getOpenid())) {
                String updateSql = "UPDATE user_info SET openid = ? WHERE phone = ?";
                baseDAO.getJdbcTemplate().update(updateSql, openid, phone);
                user.setOpenid(openid);
            }
            r.data.put("user", user);
        } else {
            r = JSONUtil.getErrorMsg("手机号码或密码错误");
        }

        return r;
    }


    /**
     * 查找手机号
     *
     * @param phone
     * @return
     * @author wy
     */
    @GetMapping("/checkPhone")
    @ApiOperation(value = "查找手机号")
    public R checkPhone(String phone, String openid) {
        R r = JSONUtil.getSuccessMsg(null);
        Object[] args = new Object[]{phone};
        List<MiniUserVO> users = baseDAO.getJdbcTemplate().query(
                "SELECT * FROM user_info WHERE IFNULL(delete_tag, 0) = 0 AND phone = ?", args,
                new BeanPropertyRowMapper<>(MiniUserVO.class));
        if (users.size() > 0) {
            MiniUserVO user = users.get(0);
            if (StringUtils.isEmpty(user.getOpenid()) && StringUtils.isNotEmpty(openid)) {
                String updateSql = "UPDATE user_info SET openid = ? WHERE phone = ?";
                baseDAO.getJdbcTemplate().update(updateSql, openid, phone);
                user.setOpenid(openid);
            }
            r.data.put("user", user);
        } else {
            r = JSONUtil.getErrorMsg("该号码未注册");
        }
        return r;
    }


    /**
     * 记住用户
     *
     * @param openid
     * @return
     * @author wy
     */
    @GetMapping("/checkOpenid")
    @ApiOperation(value = "查询是否为新用户")
    public R checkOpenid(String openid) {
        if (StringUtils.isEmpty(openid)) {
            return JSONUtil.getErrorMsg("openid为空");
        }
        R r = JSONUtil.getSuccessMsg(null);
        Object[] args = new Object[]{openid};
        List<MiniUserVO> users = baseDAO.getJdbcTemplate().query(
                "select * from user_info where IFNULL(delete_tag,0)=0 and openid =?", args,
                new BeanPropertyRowMapper<>(MiniUserVO.class));
        if (!users.isEmpty()) {
            r.data.put("user", users.get(0));
        } else {
            r = JSONUtil.getErrorMsg("无信息");
        }
        return r;
    }

    /**
     * 发送登录验证码
     *
     * @param tel
     * @return
     * @throws IOException
     */
    @GetMapping("/sendLoginCaptcha")
    @ApiOperation(value = "发送登录验证码")
    public R sendLoginCaptcha(String tel) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        int captcha = (int) ((Math.random() * 9 + 1) * 1000);
        MsgUtil.sendLoginMsg(tel, String.valueOf(captcha));
        r.data.put("captcha", captcha);
        return r;
    }

    /**
     * 发送注册验证码
     *
     * @param tel
     * @return
     * @throws IOException
     */
    @GetMapping("/sendRegisterCaptcha")
    @ApiOperation(value = "发送注册验证码")
    public R sendRegisterCaptcha(String tel) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        int captcha = (int) ((Math.random() * 9 + 1) * 1000);
        MsgUtil.sendRegisterMsg(tel, String.valueOf(captcha));
        r.data.put("captcha", captcha);
        return r;
    }

    /**
     * 发送跌倒提示
     *
     * @param tel
     * @return
     * @throws IOException
     */
    @GetMapping("/sendFallMsg")
    @ApiOperation(value = "发送跌倒提示")
    public R sendFallMsg(String tel, String dev_name) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        MsgUtil.sendNotifyMsg(tel, dev_name);
        return r;
    }

    public String getAccessToken() {
        String url = "https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=" + Constants.WXXCXAPPID + "&secret=" + Constants.WXXCXAPPSECRET;
        String response = HttpUtil.request(url, null, false, null, null);
        JSONObject jsonResponse = JSONObject.parseObject(response);
        return jsonResponse.getString("access_token");
    }

    @GetMapping("/getPhoneNumber")
    @ApiOperation(value = "一键获取手机号")
    public R getPhoneNumber(String code) {
        R r = JSONUtil.getSuccessMsg(null);
        if (StringUtils.isNotEmpty(code)) {
            String phoneNumber = "";
            // 获取 access_token
            String accessToken = getAccessToken();
            // 检查 access_token 是否有效
            if (StringUtils.isEmpty(accessToken)) {
                return JSONUtil.getErrorMsg("获取 access_token 失败，请检查配置");
            }
            // 请求参数
            JSONObject param = new JSONObject();
            param.put("code", code);
            // 请求获取手机号的接口
            String res = HttpUtil.request("https://api.weixin.qq.com/wxa/business/getuserphonenumber?access_token=" + accessToken, param.toJSONString(), false, null, null);
            // 处理返回的结果
            if (StringUtils.isNotEmpty(res)) {
                try {
                    JSONObject resJson = JSONObject.parseObject(res);
                    if (resJson.containsKey("phone_info") && resJson.getJSONObject("phone_info").containsKey("phoneNumber")) {
                        phoneNumber = resJson.getJSONObject("phone_info").getString("phoneNumber");
                    } else {
                        phoneNumber = "未返回手机号";
                    }
                } catch (Exception e) {
                    phoneNumber = "解析失败，异常：" + e.getMessage();
                }
            } else {
                phoneNumber = "接口调用失败，未获取到手机号";
            }

            // 将获取到的手机号放入返回数据中
            r.data.put("phoneNumber", phoneNumber);
        } else {
            r.data.put("phoneNumber", "无效的 code 参数");
        }

        return r;
    }

    @GetMapping("/loginByPhone")
    @ApiOperation(value = "一键获取手机号登录")
    public R loginByPhone(String code, String openId, String unionId) {
        R r = JSONUtil.getSuccessMsg(null);
        if (StringUtils.isNotEmpty(code)) {
            String phoneNumber = "";
            // 获取 access_token
            String accessToken = getAccessToken();
            // 检查 access_token 是否有效
            if (StringUtils.isEmpty(accessToken)) {
                return JSONUtil.getErrorMsg("获取 access_token 失败，请检查配置");
            }
            // 请求参数
            JSONObject param = new JSONObject();
            param.put("code", code);
            // 请求获取手机号的接口
            String res = HttpUtil.request("https://api.weixin.qq.com/wxa/business/getuserphonenumber?access_token=" + accessToken, param.toJSONString(), false, null, null);
            // 处理返回的结果
            if (StringUtils.isNotEmpty(res)) {
                try {
                    JSONObject resJson = JSONObject.parseObject(res);
                    if (resJson.containsKey("phone_info") && resJson.getJSONObject("phone_info").containsKey("phoneNumber")) {
                        phoneNumber = resJson.getJSONObject("phone_info").getString("phoneNumber");

                        MiniUserVO user = new MiniUserVO();
                        List<MiniUserVO> users = baseDAO.queryAllByCondition(MiniUserVO.class, " and phone='" + phoneNumber + "' ", null);
                        if (users.size() == 0) {
                            user.setOpenid(openId);
                            user.setPhone(phoneNumber);
                            user.setDelete_tag(0);
                            baseDAO.insertAll(user);

                            // 查询是否存在该 openid
                            List<WxRelatiion> list = baseDAO.getJdbcTemplate().query(
                                    "SELECT * FROM wx_relation WHERE openid = ?",
                                    new Object[]{openId},
                                    new BeanPropertyRowMapper<>(WxRelatiion.class)
                            );
                            if (list.size() <= 0) {
                                String insertSql = "INSERT INTO wx_relation (openid, unionid,fwhopenid) VALUES (?, ?,?)";
                                baseDAO.getJdbcTemplate().update(insertSql, openId, unionId, null);
                            }
                        } else {
                            r = JSONUtil.getErrorMsg("该号码已注册");
                        }

                    } else {
                        phoneNumber = "未返回手机号";
                    }
                } catch (Exception e) {
                    phoneNumber = "解析失败，异常：" + e.getMessage();
                }
            } else {
                phoneNumber = "接口调用失败，未获取到手机号";
            }

            // 将获取到的手机号放入返回数据中
            r.data.put("phoneNumber", phoneNumber);
        } else {
            r.data.put("phoneNumber", "无效的 code 参数");
        }

        return r;
    }


    /**
     * 检测用户是否拥有设备
     *
     * @param openid
     * @param dev_id
     * @return
     */

    @GetMapping("/checkDev")
    @ApiOperation(value = "检测用户是否拥有设备")
    public R checkDev(String openid, String dev_id) {
        R r = JSONUtil.getSuccessMsg(null);
        List<DevVO> devs = baseDAO.queryAllByCondition(DevVO.class, " and ((user_openid ='" + openid + "' and dev_id='" + dev_id + "') or '"
                + dev_id + "' in (select dev_id from dev_share where delete_tag =0 and shared ='" + openid + "' and state =1))", null);
        if (devs != null && devs.size() > 0) {
            r = JSONUtil.getErrorMsg("");
        }
        return r;
    }


    /**
     * 小程序用户注册
     *
     * @param user
     * @return
     * @throws IOException
     * @author wy
     */
    @GetMapping("/userReg")
    @ApiOperation(value = "小程序用户注册")
    public R userReg(MiniUserVO user) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        List<MiniUserVO> users = baseDAO.queryAllByCondition(MiniUserVO.class, " and phone='" + user.getPhone() + "' ", null);
        if (users.size() == 0) {
            user.setDelete_tag(0);
            baseDAO.insertAll(user);
        } else {
            r = JSONUtil.getErrorMsg("该号码已注册");
        }
        return r;
    }

    /**
     * 首页加载
     *
     * @param user_openid
     * @return
     * @throws IOException
     * @author wy
     */
    @GetMapping("/home")
    @ApiOperation(value = "获取个人首页信息")
    public R home(String user_openid) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        List<LbtVO> lbts = baseDAO.queryByCondition(LbtVO.class, null, " create_time desc ");
        for (LbtVO v : lbts) {
            v.setLbt(Constants.DOMAIN + Environment.COMMON_FILE_UPLOAD_PATH + v.getLbt());
        }
        r.data.put("lbts", lbts);
        List<DevVO> devs = baseDAO.queryAllByCondition(DevVO.class, " and (user_openid='" + user_openid + "' or " +
                "dev_id in (select dev_id from dev_share where delete_tag =0 and shared ='" + user_openid + "' and state =1))", null);
        r.data.put("qb", devs.size());
        int bj = 0;
        for (DevVO v : devs) {
            if (v.getDev_warn() != null && v.getDev_warn() == 1) {
                bj++;
            }
        }
        r.data.put("bj", bj);

        // 群组数
        List<GroupVO> groups = baseDAO.queryAllByCondition(GroupVO.class, " and (user_openid='" + user_openid + "' or group_uuid in (select group_uuid"
                + " from group_share where delete_tag =0 and shared='" + user_openid + "' and state =1))", null);

        int qz = groups.size();

        r.data.put("qz", qz);

        // 待确认分享数
        Integer fxqr = baseDAO.getJdbcTemplate().queryForObject(
                "select count(dev_id) from dev_share where delete_tag=0 and shared='" + user_openid + "' and (state is null or state =0)", Integer.class);
        if (fxqr == null) {
            fxqr = 0;
        }
        r.data.put("fxqr", fxqr);
        return r;
    }

    /**
     * 修改密码
     *
     * @param user_openid
     * @param newPassword
     * @param oldPassword
     * @param phone
     * @return
     * @throws IOException
     */
    @GetMapping("/xgmm")
    @ApiOperation(value = "修改密码")
    public R xgmm(String user_openid, String newPassword, String oldPassword, String phone) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        try {
            if (StringUtils.isNotEmpty(phone)) {
                MiniUserVO user = baseDAO.queryByKey(MiniUserVO.class, "openid", user_openid);
                if (!user.getPhone().equals(phone)) {
                    throw new Exception("手机号码错误");
                }
                baseDAO.updateSQL("update user_info set password='" + newPassword + "',update_time ='"
                        + new Timestamp(System.currentTimeMillis()) + "' where openid='" + user_openid + "'");
            } else {
                MiniUserVO user = baseDAO.queryByKey(MiniUserVO.class, "openid", user_openid);
                if (!user.getPassword().equals(oldPassword)) {
                    throw new Exception("旧密码错误");
                }
                baseDAO.updateSQL("update user_info set password='" + newPassword + "',update_time ='"
                        + new Timestamp(System.currentTimeMillis()) + "' where openid='" + user_openid + "'");
            }
        } catch (Exception e) {
            r = JSONUtil.getErrorMsg(e);
        }

        return r;
    }

    /**
     * 设备列表
     *
     * @param user_openid
     * @param keyword
     * @param zt
     * @return
     * @throws IOException
     */
    @GetMapping("/devList")
    @ApiOperation(value = "查询设备列表")
    public R devList(String user_openid, String keyword, Integer zt) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        String con = "";
        if (StringUtils.isNotEmpty(keyword)) {
            con = con + " and (dev_name like '%" + keyword + "%' or dev_id like '%" + keyword + "%') ";
        }
        if (zt != null) {
            if (zt == 2) {
                con = con + " and online=0 ";
            }
            if (zt == 3) {
                con = con + " and online=1 ";
            }
            if (zt == 4) {
                con = con + " and dev_warn is not null and dev_warn =1";
            }
        }
        List<DevVO> devs = baseDAO.queryAllByCondition(DevVO.class, " and (user_openid='" + user_openid + "' or " +
                "dev_id in (select dev_id from dev_share where delete_tag =0 and shared ='" + user_openid + "' and state =1)) " + con, null);
        r.data.put("devs", devs);
        return r;
    }

    /**
     * 设备列表
     *
     * @param user_openid
     * @param keyword
     * @param zt
     * @return
     * @throws IOException
     */
    @GetMapping("/devListWithTimes")
    @ApiOperation(value = "查询设备列表")
    public R devListWithTimes(String user_openid, String keyword, Integer zt, String time) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        String con = "";
        if (StringUtils.isNotEmpty(keyword)) {
            con = con + " and (dev_name like '%" + keyword + "%' or dev_id like '%" + keyword + "%') ";
        }
        if (zt != null) {
            if (zt == 2) {
                con = con + " and online=0 ";
            }
            if (zt == 3) {
                con = con + " and online=1 ";
            }
            if (zt == 4) {
                con = con + " and dev_warn is not null and dev_warn =1";
            }
        }
        List<DevVO> devs = baseDAO.queryAllByCondition(DevVO.class, " and (user_openid='" + user_openid + "' or " +
                "dev_id in (select dev_id from dev_share where delete_tag =0 and shared ='" + user_openid + "' and state =1)) " + con, null);

        List<Map> res = new ArrayList<>();
        if (devs != null && devs.size() > 0) {
            ObjectMapper objectMapper = new ObjectMapper();
            for (DevVO dev : devs) {

                Map beanMap = objectMapper.convertValue(dev, Map.class);

                LocalDate inputDate = LocalDate.now();
                if (StringUtils.isNotEmpty(time)) {
                    inputDate = LocalDate.parse(time);
                }
                String inputDateStr = inputDate.toString();
                // 判断查询结果并返回
                List<StayTime> stayTimes = null;
                try {
                    stayTimes = jdbcTemplate.query(
                            "SELECT * FROM stay_time WHERE dev_id = ? AND DATE(leave_time) = ? and type = 0 order by leave_time",
                            new Object[]{dev.getDev_id(), inputDateStr},
                            new BeanPropertyRowMapper<>(StayTime.class)
                    );
                } catch (Exception e) {
                    e.printStackTrace();
                }
                if (Objects.nonNull(stayTimes) && stayTimes.size() > 0) {
                    beanMap.put("stayTimes", stayTimes);
                    beanMap.put("count", stayTimes.size());
                } else {
                    beanMap.put("stayTimes", null);
                    beanMap.put("count", 0);
                }
                res.add(beanMap);
            }
        }
        r.data.put("devs", res);
        return r;
    }

    /**
     * 设备解绑
     *
     * @param dev_id
     * @param user_openid
     * @return
     * @throws IOException
     */
    @GetMapping("/sbjb")
    @ApiOperation(value = "解绑设备")
    public R sbjb(String dev_id, String user_openid) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        List<DevVO> devs = baseDAO.queryAllByCondition(DevVO.class, " and dev_id='" + dev_id + "' and user_openid= '" + user_openid + "'", null);
        try {
            if (devs.size() > 0) {
                // 绑定人解绑
                baseDAO.updateSQL("update dev_info set user_openid=null where dev_id='" + dev_id + "'");
                baseDAO.updateSQL("update dev_share set state =0,delete_tag =1 where dev_id='" + dev_id + "'");
            } else {
                // 被分享者解绑
                baseDAO.updateSQL("update dev_share set state =0,delete_tag =1 where dev_id='" + dev_id + "' and shared ='" + user_openid + "' ");
            }
        } catch (DataAccessException e) {
            // 捕获数据库相关的异常，并记录日志
            Log.error("数据库操作失败: ", e);
            r = JSONUtil.getErrorMsg("数据库操作失败，请稍后再试");
        } catch (Exception e) {
            // 捕获其他未知的异常，并记录日志
            Log.error("解绑设备失败: ", e);
            r = JSONUtil.getErrorMsg("解绑设备失败，请稍后再试");
        }
        return r;
    }

    /**
     * 设备绑定
     *
     * @param dev
     * @return
     * @throws IOException
     */
    @GetMapping("/sbbd")
    @ApiOperation(value = "绑定设备")
    public R sbbd(DevVO dev) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        try {
            List<DevVO> devs = baseDAO.queryAllByCondition(DevVO.class, " and dev_id='" + dev.getDev_id() + "' ", null);
            if (devs.size() == 0) {
                throw new Exception("设备ID错误");
            }
            if (StringUtils.isNotEmpty(devs.get(0).getUser_openid())) {
                throw new Exception("设备已被绑定");
            }
            dev.setUpdate_time(new Timestamp(System.currentTimeMillis()));
            baseDAO.updateSQL("update dev_info set user_openid='" + dev.getUser_openid() + "',dev_name ='" + dev.getDev_name() + "',length ="
                    + dev.getLength() + ",width=" + dev.getWidth() + ",height=" + dev.getHeight() + ",update_time ='" + dev.getUpdate_time() + "',north_angle=" +
                    dev.getNorth_angle() + ",online =1 where dev_id='" + dev.getDev_id() + "'");
        } catch (Exception e) {
            r = JSONUtil.getErrorMsg(e);
        }
        return r;
    }

    /**
     * 设备详情
     *
     * @param dev_id
     * @return
     * @throws IOException
     */
    @GetMapping("/sbxq")
    @ApiOperation(value = "设备详情")
    public R sbxq(String dev_id) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        List<DevVO> devs = baseDAO.queryAllByCondition(DevVO.class, " and dev_id ='" + dev_id + "' ", null);
        if (devs.size() != 0) {
            r.data.put("dev", devs.get(0));
        }
        return r;
    }

    @GetMapping("/sbgj")
    @ApiOperation(value = "设备关系")
    public R sbgj(String sb_id, String s_date, String e_date) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        List<SbgjVO> sbgjs = baseDAO.queryByCondition(SbgjVO.class, " and sb_id='" + sb_id + "' and create_time>='" + s_date + "' and create_time<='" + e_date + "' ", " create_time desc ");
        r.data.put("sbgjs", sbgjs);
        return r;
    }

    /**
     * 设备事件
     *
     * @param dev_id
     * @param s_date
     * @param e_date
     * @param req
     * @return
     * @throws IOException
     */
    @GetMapping("/sbsj")
    @ApiOperation(value = "设备事件")
    public R sbsj(String dev_id, String s_date, String e_date, HttpServletRequest req) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        String con = "";
        if (StringUtils.isNotEmpty(s_date)) {
            con = con + " and create_time>='" + s_date + "' ";
        }
        if (StringUtils.isNotEmpty(e_date)) {
            con = con + " and create_time<='" + e_date + "' ";
        }
        PageRecord<TargetVO> targets = baseDAO.queryAllPaginateByCondition(TargetVO.class, " and dev_id='" + dev_id + "' and event=3 " + con, PageUtil.getPageSize(req), PageUtil.getPageNum(req), " create_time desc ");
        r.data.put("targets", targets);
        return r;
    }


    @GetMapping("/sbsjV2")
    @ApiOperation(value = "设备事件")
    public R sbsjV2(String dev_id, String s_date, String e_date, HttpServletRequest req) throws IOException {
        StringBuilder sql = new StringBuilder("SELECT * FROM event_list WHERE dev_id = ? AND event = 3");
        List<Object> params = new ArrayList<>();
        params.add(dev_id);

        // 根据传入的日期条件，动态拼接 SQL
        if (StringUtils.isNotEmpty(s_date)) {
            sql.append(" AND create_time >= ?");
            params.add(s_date);
        }
        if (StringUtils.isNotEmpty(e_date)) {
            sql.append(" AND create_time <= ?");
            params.add(e_date);
        }

        // 分页参数
        int pageSize = PageUtil.getPageSize(req);
        int pageNum = PageUtil.getPageNum(req);
        int offset = (pageNum - 1) * pageSize;

        sql.append(" ORDER BY create_time DESC LIMIT ? OFFSET ?");
        params.add(pageSize);
        params.add(offset);

        // 执行查询
        List<TargetVO> targets = jdbcTemplate.query(sql.toString(), params.toArray(), new BeanPropertyRowMapper<>(TargetVO.class));

        // 查询总数，重新初始化参数以避免分页参数干扰
        List<Object> countParams = new ArrayList<>();
        countParams.add(dev_id);
        if (StringUtils.isNotEmpty(s_date)) {
            countParams.add(s_date);
        }
        if (StringUtils.isNotEmpty(e_date)) {
            countParams.add(e_date);
        }

        String countSql = "SELECT COUNT(*) FROM event_list WHERE dev_id = ? AND event = 3";
        if (StringUtils.isNotEmpty(s_date)) {
            countSql += " AND create_time >= ?";
        }
        if (StringUtils.isNotEmpty(e_date)) {
            countSql += " AND create_time <= ?";
        }

        int total = jdbcTemplate.queryForObject(countSql, countParams.toArray(), Integer.class);

        // 封装分页数据
        PageRecord<TargetVO> pageRecord = new PageRecord<>();
        pageRecord.setRows(targets);
        pageRecord.setTotal((long) total);
        pageRecord.setPageSize(pageSize);
        pageRecord.setPageNum(pageNum);

        // 返回结果
        R r = new R();
        r.data.put("targets", pageRecord);
        return r;
    }


    /**
     * 处理设备事件
     *
     * @param id
     * @return
     * @throws IOException
     */
    @GetMapping("/handleMessage")
    @ApiOperation(value = "处理设备事件")
    public R handleMessage(int id) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        try {
            String sql = "UPDATE event_list SET is_handle = 1 WHERE id = " + id;
            baseDAO.updateSQL(sql);
        } catch (Exception e) {
            r = JSONUtil.getErrorMsg(e);
        }
        return r;
    }


    /**
     * 设备修改保存
     *
     * @param vo
     * @return
     */
    @GetMapping("/devSave")
    @ApiOperation(value = "设备信息修改保存")
    public R devSave(DevVO vo) {
        R r = JSONUtil.getSuccessMsg(null);
        // 数据库更新
        baseDAO.updateSQL("update dev_info set dev_name ='" + vo.getDev_name() + "',north_angle=" + vo.getNorth_angle() + ",start_x=" + vo.getStart_x() + ",stop_x=" + vo.getStop_x() + ",start_y=" + vo.getStart_y() + ",stop_y=" + vo.getStop_y() + ",height=" + vo.getHeight() + ",start_z=" + vo.getStart_z() + ",stop_z=" + vo.getStop_z() + ",status_light=" + vo.getStatus_light() + " where dev_id ='" + vo.getDev_id() + "' ");

        DevVO devInfoVO = baseDAO.queryByKey(DevVO.class, "dev_id", vo.getDev_id());
        String software = devInfoVO.getSoftware();
        String result = "";
        if (software != null) {
            String[] parts = software.split("\\.");
            result = parts[0] + "." + parts[1];
        } else {
            result = "2.0";
        }
        if (Double.parseDouble(result) >= 2.0) {
            String topic = "/dev/" + devInfoVO.getDev_id() + "/set_device_param";
            Map<String, BigDecimal> base = new HashMap<>();
            Map<String, Map<String, BigDecimal>> baseParent = new HashMap<>();
            base.put("x_cm_start", vo.getStart_x());
            base.put("x_cm_stop", vo.getStop_x());
            base.put("y_cm_start", vo.getStart_y());
            base.put("y_cm_stop", vo.getStop_y());
            base.put("z_cm_start", vo.getStart_z());
            base.put("z_cm_stop", vo.getStop_z());
            baseParent.put("base", base);
            Map<String, BigDecimal> z_cm = new HashMap<>();
            z_cm.put("z_cm", vo.getHeight());
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("sensor_location", z_cm);
            jsonObject.put("ext_region", baseParent);
            int indicator_led = 0;
            if (vo.getStatus_light() == 0) {
                indicator_led = 1;
            }
            if (vo.getStatus_light() == 1) {
                indicator_led = 0;
            }
            jsonObject.put("indicator_led", indicator_led);
            try {
                MqttUtil.pub(topic, jsonObject.toJSONString());
            } catch (Exception e) {
//             TODO: handle exception
//                r = JSONUtil.getErrorMsg(e);
            }
        } else {
            String topic = "/mps/set_device_param";
            JSONObject msg = new JSONObject();
            msg.put("dev_id", vo.getDev_id());
            msg.put("mounting_plain", vo.getMount_plain());
            msg.put("height", vo.getHeight());
            JSONObject area = new JSONObject();
            area.put("start_x", vo.getStart_x());
            area.put("stop_x", vo.getStop_x());
            area.put("start_y", vo.getStart_y());
            area.put("stop_y", vo.getStop_y());
            area.put("start_z", vo.getStart_z());
            area.put("stop_z", vo.getStop_z());
            msg.put("area", area);
            try {
                MqttUtil.pub(topic, msg.toJSONString());
            } catch (Exception e) {
//             TODO: handle exception
                r = JSONUtil.getErrorMsg(e);
            }
        }


        return r;
    }

    /**
     * 房间布局读取
     *
     * @param openid
     * @param dev_id
     * @return
     */
    @GetMapping("/readRoom")
    @ApiOperation(value = "读取房间信息")
    public R readRoom(String openid, String dev_id) {
        R r = JSONUtil.getSuccessMsg(null);

        DevRoomVO devRoomVO = baseDAO.queryByKey(DevRoomVO.class, "dev_id", dev_id);
        if (devRoomVO == null) {
            r = JSONUtil.getErrorMsg("房间信息不存在");
            return r;
        }

        JSONArray res = new JSONArray();
        if (devRoomVO.getFurnitures() != null && devRoomVO.getFurnitures().length() > 0) {
            res.addAll(JSON.parseArray(devRoomVO.getFurnitures()));
        }
        if (devRoomVO.getSub_regions() != null && devRoomVO.getSub_regions().length() > 0) {
            res.addAll(JSON.parseArray(devRoomVO.getSub_regions()));
        }
        // 向前端发送数据
        r.data.put("data", res);
        return r;
    }

    /**
     * 房间布局保存
     *
     * @param vo
     * @return
     */
    @PostMapping("/saveRoom")
    @ApiOperation(value = "房间布局保存")
    public R saveRoom(@RequestBody RoomVO vo) {
        R r = JSONUtil.getSuccessMsg(null);
        String dev_id = vo.getDev_id();

        try  {
            String jsonString = JSON.toJSONString(vo);

            // 写入数据库
            JSONObject obj = JSONObject.parseObject(jsonString);
            JSONArray roomParams = obj.getJSONArray("roomParams");
            DevRoomVO devRoomVO = baseDAO.queryByKey(DevRoomVO.class, "dev_id", dev_id);
            if (devRoomVO == null) {
                DevRoomVO room = new DevRoomVO();
                room.setDelete_tag(0);
                room.setDev_id(dev_id);
                baseDAO.insertAll(room);
            }

//            StringBuffer sql = new StringBuffer("update dev_room set room_params= JSON_OBJECT(");
            StringBuffer sql = new StringBuffer("update dev_room set ");

//            sql.append("'openid','" + obj.getString("openid") + "',");
//            sql.append("'dev_id','" + obj.getString("dev_id") + "',");
//            sql.append("'start_x','" + obj.getString("start_x") + "',");
//            sql.append("'stop_x','" + obj.getString("stop_x") + "',");
//            sql.append("'start_y','" + obj.getString("start_y") + "',");
//            sql.append("'stop_y','" + obj.getString("stop_y") + "',");
//            sql.append("'roomParams'," +  "JSON_ARRAY(");

            // 子区域插入sql
            StringBuffer subBuffer = new StringBuffer("sub_regions= JSON_ARRAY(");
            // 家具插入sql
            StringBuffer furBuffer = new StringBuffer("furnitures= JSON_ARRAY(");
            if (roomParams.size() > 0) {

                boolean hasSub = false;
                boolean hasFur = false;
                for (int i = 0; i < roomParams.size(); i++) {
                    JSONObject itemObj = roomParams.getJSONObject(i);
//                    sql.append("JSON_OBJECT('name','" + itemObj.getString("name") + "',");
//                    sql.append("'type','" + itemObj.getString("type") + "',");
//                    sql.append("'width'," + itemObj.getString("width") + ",");
//                    sql.append("'height'," + itemObj.getString("height") + ",");
//                    sql.append("'heightToM'," + itemObj.getString("heightToM") + ",");
//                    sql.append("'left'," + itemObj.getString("left") + ",");
//                    sql.append("'rotate'," + itemObj.getString("rotate") + ",");
//                    sql.append("'top'," + itemObj.getString("top") + ",");
//                    sql.append("'widthToM'," + itemObj.getString("widthToM") + ",");
//                    sql.append("'x'," + itemObj.getString("x") + ",");
//                    sql.append("'y'," + itemObj.getString("y") + ",");
//                    sql.append("'yuanX'," + itemObj.getString("yuanX") + ",");
//                    sql.append("'yuanY'," + itemObj.getString("yuanY") + "),");

                    String type = itemObj.getString("type");
                    if (type.contains("area") || type.contains("otherArea")) {
                        // 子区域
                        subBuffer.append("JSON_OBJECT('name','" + itemObj.getString("name") + "',");
                        subBuffer.append("'type','" + itemObj.getString("type") + "',");
                        subBuffer.append("'width'," + itemObj.getString("width") + ",");
                        subBuffer.append("'height'," + itemObj.getString("height") + ",");
//                        subBuffer.append("'heightToM'," + itemObj.getString("heightToM") + ",");
                        subBuffer.append("'left'," + itemObj.getString("left") + ",");
                        subBuffer.append("'rotate'," + itemObj.getString("rotate") + ",");
                        subBuffer.append("'top'," + itemObj.getString("top") + ",");
//                        subBuffer.append("'widthToM'," + itemObj.getString("widthToM") + ",");
                        subBuffer.append("'x'," + itemObj.getString("x") + ",");
                        subBuffer.append("'y'," + itemObj.getString("y") + ",");
                        subBuffer.append("'yuanX'," + itemObj.getString("yuanX") + ",");
                        subBuffer.append("'yuanY'," + itemObj.getString("yuanY") + "),");

                        hasSub = true;
                    } else {
                        // 家具
                        furBuffer.append("JSON_OBJECT('name','" + itemObj.getString("name") + "',");
                        furBuffer.append("'type','" + itemObj.getString("type") + "',");
                        furBuffer.append("'width'," + itemObj.getString("width") + ",");
                        furBuffer.append("'height'," + itemObj.getString("height") + ",");
//                        furBuffer.append("'heightToM'," + itemObj.getString("heightToM") + ",");
                        furBuffer.append("'left'," + itemObj.getString("left") + ",");
                        furBuffer.append("'rotate'," + itemObj.getString("rotate") + ",");
                        furBuffer.append("'top'," + itemObj.getString("top") + ",");
//                        furBuffer.append("'widthToM'," + itemObj.getString("widthToM") + ",");
                        furBuffer.append("'x'," + itemObj.getString("x") + ",");
                        furBuffer.append("'y'," + itemObj.getString("y") + ",");
                        furBuffer.append("'yuanX'," + itemObj.getString("yuanX") + ",");
                        furBuffer.append("'yuanY'," + itemObj.getString("yuanY") + "),");

                        hasFur = true;
                    }
                }

//                sql.setLength(sql.length() - 1);
//                sql.append("))" );


                if (hasSub) {
                    subBuffer.setLength(subBuffer.length() - 1);
                    sql.append(subBuffer).append(")");
                } else {
                    sql.append(" sub_regions=null ");
                }
                sql.append(",");
                if (hasFur) {
                    furBuffer.setLength(furBuffer.length() - 1);
                    sql.append(furBuffer).append(")");
                } else {
                    sql.append(" furnitures=null ");
                }

                sql.append(" where dev_id ='" + dev_id + "' ");

                System.out.println(sql.toString());
                // update dev_room set room_params= JSON_OBJECT('openid','oioMm7VtkWkvHFYBlzCR6couIENQ','dev_id','8CBFEA0B75D8','start_x','-200.0','stop_x','200.0','start_y','-200.0','stop_y','200.0',JSON_OBJECT('name','门','type','other','width',20,'height',80,'heightToM',80.0,'left',303,'rotate',false,'top',191,'widthToM',20.0,'x',-196.97,'y',198.09,'yuanX',0,'yuanY',0)) where dev_id ='8CBFEA0B75D8'

                baseDAO.updateSQL(sql.toString());
                //发送屏蔽区域的数据到服务器
                // 创建 ObjectMapper 实例
                ObjectMapper objectMapper = new ObjectMapper();
                // 将 JSON 字符串转换成 JsonNode 对象
                JsonNode rootNode = objectMapper.readTree(jsonString);
                // 获取 roomParams 数组
                JsonNode roomParamsNode = rootNode.path("roomParams");
                // 创建一个新的 List 用来存储 type 包含 "area" 的元素
                List<Base> ext_sub_regions = new ArrayList<>();
                for (JsonNode room : roomParamsNode) {
                    // 遍历 roomParamsNode 数组
                    String type = room.path("type").asText();
                    if (type.contains("area") || type.contains("otherArea")) {
                        Base base = new Base(0, 100, 0, 50, 1, 1);
                        int x = room.path("x").asInt();
                        int y = room.path("y").asInt();
                        int width = room.path("width").asInt();
                        int height = room.path("height").asInt();
                        // 设置 base 对象的字段值
                        base.setX_cm_start(x);
                        base.setX_cm_stop(x + width);
                        base.setY_cm_start(y);
                        base.setY_cm_stop(y - height);
                        // 设置固定值
                        base.setTrack_presence(1);
                        base.setExclude_falling(1);
                        ext_sub_regions.add(base);
                    }
                }
                ObjectMapper objectMapperOne = new ObjectMapper();
                List<ObjectNode> transformedList = new ArrayList<>();
                if (ext_sub_regions.size() > 0) {
                    for (Base baseOne : ext_sub_regions) {
                        ObjectNode newObject = objectMapperOne.createObjectNode();
                        ObjectNode baseNode = objectMapperOne.createObjectNode();
                        baseNode.put("x_cm_start", baseOne.getX_cm_start());
                        baseNode.put("x_cm_stop", baseOne.getX_cm_stop());
                        baseNode.put("y_cm_start", baseOne.getY_cm_start());
                        baseNode.put("y_cm_stop", baseOne.getY_cm_stop());
                        baseNode.put("presence_enter_duration", 0);
                        baseNode.put("presence_exit_duration", 0);
                        baseNode.put("track_presence", baseOne.getTrack_presence());
                        baseNode.put("exclude_falling", baseOne.getExclude_falling());
                        newObject.set("base", baseNode);
                        newObject.put("z_cm_start", 0);  // 固定为 0
                        newObject.put("z_cm_stop", 0);   // 固定为 0
                        newObject.put("is_low_snr", 0);  // 固定为 0
                        newObject.put("is_door", 0);     // 固定为 0
                        transformedList.add(newObject);

                    }
                    ObjectNode resultNode = objectMapper.createObjectNode();
                    resultNode.set("ext_sub_regions", objectMapper.valueToTree(transformedList));
                    String jsonStringTwo = objectMapper.writeValueAsString(resultNode);
                    try {
                        StringBuffer getTopic = new StringBuffer("/dev/" + dev_id + "/set_device_param");
                        MqttUtil.pub(getTopic.toString(), jsonStringTwo);
                    } catch (Exception e) {
                        r = JSONUtil.getErrorMsg(e);
                    }
                } else {
                    ObjectNode resultNode = objectMapper.createObjectNode();
                    resultNode.set("ext_sub_regions", objectMapper.valueToTree(transformedList));
                    String jsonStringTwo = objectMapper.writeValueAsString(resultNode);
                    try {
                        StringBuffer getTopic = new StringBuffer("/dev/" + dev_id + "/set_device_param");
                        MqttUtil.pub(getTopic.toString(), jsonStringTwo);
                    } catch (Exception e) {
                        r = JSONUtil.getErrorMsg(e);
                    }
                }
            } else {

                sql.append(" sub_regions=null , furnitures=null ");
                sql.append(" where dev_id ='" + dev_id + "' ");
                baseDAO.updateSQL(sql.toString());

                JSONObject jsonObject = new JSONObject();
                JSONArray extRegion = new JSONArray();
                jsonObject.put("ext_sub_regions", extRegion);
                try {
                    StringBuffer getTopic = new StringBuffer("/dev/" + dev_id + "/set_device_param");
                    MqttUtil.pub(getTopic.toString(), jsonObject.toJSONString());
                } catch (Exception e) {
                    r = JSONUtil.getErrorMsg(e);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
            r = JSONUtil.getErrorMsg(e);
        }
        return r;
    }

    /**
     * 设备（修改）保存
     *
     * @param dev
     * @return
     * @throws IOException
     */
    @GetMapping("/sbbc")
    @ApiOperation(value = "设备修改保存(第一次绑定)")
    public R sbbc(DevVO vo) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        double length = vo.getLength().doubleValue();
        double width = vo.getWidth().doubleValue();
        BigDecimal start_x = new BigDecimal(-length / 2);
        BigDecimal stop_x = new BigDecimal(length / 2);
        BigDecimal start_y = new BigDecimal(-width / 2);
        BigDecimal stop_y = new BigDecimal(width / 2);

        vo.setStart_x(start_x);
        vo.setStop_x(stop_x);
        vo.setStart_y(start_y);
        vo.setStop_y(stop_y);
        // 数据库更新
        baseDAO.updateSQL("update dev_info set dev_name ='" + vo.getDev_name() + "',mount_plain='" + vo.getMount_plain() + "',length=" + vo.getLength() + ",width="
                + vo.getWidth() + ",height=" + vo.getHeight() + ",start_x =" + vo.getStart_x() + ",stop_x =" + vo.getStop_x() + ",start_y =" + vo.getStart_y() + ",stop_y ="
                + vo.getStop_y() + ",start_z =" + vo.getStart_z() + ",stop_z =" + vo.getStop_z() + " where dev_id ='" + vo.getDev_id() + "' ");

        String topic = "/mps/set_device_param";
        JSONObject msg = new JSONObject();
        msg.put("dev_id", vo.getDev_id());
        msg.put("mounting_plain", vo.getMount_plain());
        msg.put("height", vo.getHeight());
        JSONObject area = new JSONObject();
        area.put("start_x", vo.getStart_x());
        area.put("stop_x", vo.getStop_x());
        area.put("start_y", vo.getStart_y());
        area.put("stop_y", vo.getStop_y());
        area.put("start_z", vo.getStart_z());
        area.put("stop_z", vo.getStop_z());
        msg.put("area", area);
        try {
            MqttUtil.pub(topic, msg.toJSONString());
        } catch (Exception e) {
            // TODO: handle exception
            r = JSONUtil.getErrorMsg(e);
        }
        return r;
    }

    // 雷达位置更新
    @GetMapping("/devPosUpdate")
    @ApiOperation(value = "更新雷达位置")
    public R devSave(String dev_id, float x, float y) {
        R r = JSONUtil.getSuccessMsg(null);
        DevVO vo = baseDAO.queryByKey(DevVO.class, "dev_id", dev_id);
        if (vo == null) {
            return JSONUtil.getErrorMsg("未查询到该设备，请退出重试");
        }
        baseDAO.updateSQL("update dev_info set x=" + x + ",y=" + y + " where dev_id ='" + dev_id + "' and delete_tag=0");
        return r;
    }

    /**
     * 设备分享
     *
     * @param share
     * @return
     * @throws IOException
     */
    @GetMapping("/sbfx")
    @ApiOperation(value = "个人分享设备")
    public R sbfx(ShareVO share) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        List<MiniUserVO> users = baseDAO.queryAllByCondition(MiniUserVO.class, " and phone='" + share.getShared_phone() + "' ", null);
        // 被分享者已注册
        if (users.size() > 0) {
            MiniUserVO user = users.get(0);
            share.setShared(user.getOpenid());
            share.setState(0);
            // 检查当前设备是否存在未处理邀请
            List<ShareVO> shares = baseDAO.queryAllByCondition(ShareVO.class, " and sharer='" + share.getSharer() + "' and shared='" + share.getShared() +
                    "' and dev_id='" + share.getDev_id() + "' and state=0", null);
            // 已发送邀请未处理
            if (shares.size() > 0) {
                r = JSONUtil.getErrorMsg("请勿重复发送邀请");
            }
            // 未发送邀请
            else {
                baseDAO.insertAll(share);
            }
        } else {
            r = JSONUtil.getErrorMsg("手机号码未注册");
        }
        return r;
    }

    /**
     * 通过链接分享设备
     *
     * @return
     */
    @GetMapping("/sbfxByUrl")
    @ApiOperation(value = "通过链接分享设备")
    public R sbfxByUrl(ShareVO share) {
        R r = JSONUtil.getSuccessMsg(null);
        // 分享人手机号为空（链接分享）
        if (share.getSharer_phone() == null || share.getSharer_phone().isEmpty()) {
            List<MiniUserVO> sharers = baseDAO.queryAllByCondition(MiniUserVO.class, " and openid='" + share.getSharer() + "' ", null);
            if (sharers.size() > 0) {
                MiniUserVO userVO = sharers.get(0);
                share.setSharer_phone(userVO.getPhone());
            }
        }
        share.setState(1);
        List<ShareVO> shares = baseDAO.queryAllByCondition(ShareVO.class, " and sharer='" + share.getSharer() + "' and shared='" + share.getShared() + "' "
                + "and dev_id='" + share.getDev_id() + "' and state=0", null);
        // 已发送邀请未处理
        if (shares != null && shares.size() > 0) {
            r = JSONUtil.getErrorMsg("存在未处理的邀请，请回主页查看");
        }
        // 未发送邀请
        else {
            baseDAO.insertAll(share);
        }
        Log.info("分享结果：" + r.getResult());
        return r;
    }

    /**
     * 设备分享记录
     *
     * @param user_openid
     * @return
     * @throws IOException
     */
    @GetMapping("/sbfxjl")
    @ApiOperation(value = "查看设备分享记录")
    public R sbfxjl(String user_openid) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        List<ShareVO> shares = baseDAO.queryAllByCondition(ShareVO.class, " and shared='" + user_openid + "' ", null);
//		ForeignKeyUtil.processForeignKey(fxjls, "tbl_sb", "sb_id", new String[] {"mc","bh"}, new String[] {"mc","bh"});
//		ForeignKeyUtil.processForeignKey(fxjls, "tbl_hy", "fxr", new String[] {"sjhm"}, new String[] {"sjhm"});
        r.data.put("shares", shares);
        return r;
    }

    /**
     * 设备分享确认
     *
     * @param dev_id
     * @param state
     * @return
     * @throws IOException
     */
    @GetMapping("/sbfxqr")
    @ApiOperation(value = "设备分享确认")
    public R sbfx(String dev_id, Integer state, String user_openid) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        baseDAO.updateSQL("update dev_share set state=" + state + " where dev_id='" + dev_id + "' and shared ='" +
                user_openid + "' ");
        return r;
    }

    /**
     * 检查更新/获取版本
     *
     * @return
     * @throws IOException
     */
    @GetMapping("/getVersion")
    @ApiOperation(value = "获取设备新版本")
    public R getVersion(String dev_id) throws IOException {
        R r = JSONUtil.getSuccessMsg(null);
        StringBuffer getTopic = new StringBuffer("/mps/" + dev_id + "/get_device_info ");
        try {
            JSONObject msg = new JSONObject();
            msg.put("dev_id", dev_id);
            MqttUtil.pub(getTopic.toString(), msg.toJSONString());
        } catch (Exception e) {
            // TODO: handle exception
            r = JSONUtil.getErrorMsg(e);
        }
        return r;
    }

    /**
     * 设备软重启
     *
     * @param dev_id
     * @return
     */
    @GetMapping("/reboot")
    @ApiOperation(value = "设备软重启")
    public R reboot(String dev_id) {
        R r = JSONUtil.getSuccessMsg(null);
        StringBuffer topic = new StringBuffer("/dev/" + dev_id + "/reboot");
        try {
            MqttUtil.pub(topic.toString(), "1");
        } catch (Exception e) {
            // TODO: handle exception
            r = JSONUtil.getErrorMsg(e);
        }
        return r;
    }

    /**
     * OTA升级
     *
     * @param dev_id
     * @return
     */
    @GetMapping("/updateOTA")
    @ApiOperation(value = "OTA升级")
    public R updateOTA(String dev_id) {
        R r = JSONUtil.getSuccessMsg(null);
        StringBuffer topic = new StringBuffer("/dev/" + dev_id + "/update_firmware");
        try {
            MqttUtil.pub(topic.toString(), "1");
        } catch (Exception e) {
            // TODO: handle exception
            r = JSONUtil.getErrorMsg(e);
        }
        return r;
    }

    // 0:站 1:坐 2:躺 3:跌倒
    @GetMapping("/startPoseFix")
    @ApiOperation(value = "姿态修正")
    public R startPoseFix(int poseIndex, String dev_id) {
        R r = JSONUtil.getSuccessMsg(null);
        Short pose;
        switch (poseIndex) {
            case 0:
                pose = 4;
                break;
            case 1:
                pose = 5;
                break;
            case 2:
                pose = 0;
                break;
            case 3:
                pose = 7;
                break;
            default:
                pose = -1;
                break;
        }
        MqttUtil.setMap(pose, dev_id);
        return r;
    }

    @GetMapping("/stopPoseFix")
    @ApiOperation(value = "停止姿态修正")
    public R stopPoseFix(String dev_id) {
        R r = JSONUtil.getSuccessMsg(null);
        MqttUtil.deleteMap(dev_id);
        return r;
    }

    @GetMapping("/getDeviceInfo")
    @ApiOperation(value = "获取设备信息")
    public R getDeviceInfo(String dev_id) {
        R r = JSONUtil.getSuccessMsg(null);
        String topic = "/mps/get_device_info";
        JSONObject msg = new JSONObject();
        msg.put("dev_id", dev_id);
        try {
            MqttUtil.pub(topic, msg.toJSONString());
        } catch (Exception e) {
            // TODO: handle exception
            r = JSONUtil.getErrorMsg(e);
        }
        return r;
    }

    @GetMapping("/getDeviceParam")
    @ApiOperation(value = "获取设备参数")
    public R getDeviceParam(String dev_id) {
        R r = JSONUtil.getSuccessMsg(null);
        String topic = "/mps/get_device_param";
        JSONObject msg = new JSONObject();
        msg.put("dev_id", dev_id);
        try {
            MqttUtil.pub(topic, msg.toJSONString());
        } catch (Exception e) {
            // TODO: handle exception
            r = JSONUtil.getErrorMsg(e);
        }
        return r;
    }

    // 添加群组
    @GetMapping("/addGroup")
    @ApiOperation(value = "添加群组")
    public R addGroup(GroupVO vo) {
        R r = JSONUtil.getSuccessMsg(null);
        vo.setGroup_uuid((UUID.randomUUID()).toString());
        vo.setDelete_tag(0);
        baseDAO.insertAll(vo);
        // MQTT
        String topic = "/mps/add_group";
        JSONObject msg = new JSONObject();
        msg.put("group_uuid", vo.getGroup_uuid());
        msg.put("north_angle", vo.getNorth_angle());
        try {
            MqttUtil.pub(topic, msg.toJSONString());
        } catch (Exception e) {
            Log.info("添加群组:MQTT消息发送失败");
        }
        return r;
    }

    // 删除群组
    @GetMapping("/deleteGroup")
    @ApiOperation(value = "删除群组")
    public R deleteGroup(String group_uuid) {
        R r = JSONUtil.getSuccessMsg(null);
        baseDAO.updateSQL("update dev_group set delete_tag =1 where group_uuid ='" + group_uuid + "' ");
        // MQTT
        String topic = "/mps/del_group";
        JSONObject msg = new JSONObject();
        msg.put("group_uuid", group_uuid);
        try {
            MqttUtil.pub(topic, msg.toJSONString());
        } catch (Exception e) {
            Log.info("删除群组:MQTT消息发送失败");
        }
        return r;
    }

    // 更新群组
    @GetMapping("/updateGroup")
    @ApiOperation(value = "更新群组")
    public R updateGroup(GroupVO vo) {
        R r = JSONUtil.getSuccessMsg(null);
        baseDAO.updateSQL("update dev_group set group_name ='" + vo.getGroup_name() + "',length=" + vo.getLength()
                + ",width=" + vo.getWidth() + ",north_angle=" + vo.getNorth_angle() + " where group_uuid ='" + vo.getGroup_uuid() + "' ");
        // MQTT
        String topic = "/mps/update_group";
        JSONObject msg = new JSONObject();
        msg.put("group_uuid", vo.getGroup_uuid());
        msg.put("north_angle", vo.getNorth_angle());
        try {
            MqttUtil.pub(topic, msg.toJSONString());
        } catch (Exception e) {
            Log.info("更新群组:MQTT消息发送失败");
        }
        return r;
    }


    // 群组列表
//	@GetMapping("/groupList")
//	public R groupList(String openid) {
//		R r =JSONUtil.getSuccessMsg(null);
//		List<GroupVO> groups = baseDAO.queryAllByCondition(GroupVO.class, " and (user_openid='" + openid + "' or group_uuid in (select group_uuid"
//				+ " from group_share where delete_tag =0 and shared='"+openid+"' and state =1))", null);
//		List<GroupProVO> groupsPro =new ArrayList<>();
//		for(int i=0;i<groups.size();i++) {
//			GroupVO vo = groups.get(i);
//			GroupProVO voPro =new GroupProVO();
//			voPro.setGroup(vo);
//			int total =0;
//			int online =0;
//			int warn =0;
//			JSONArray devListArray =JSONArray.parseArray(vo.getDev_list());
//			if(devListArray!=null && devListArray.size()>0) {
//				total =devListArray.size();
//				for(int j=0;j<devListArray.size();j++) {
//					DevVO devVO =baseDAO.queryByKey(DevVO.class, "dev_id", devListArray.getString(j));
//					if(devVO.getOnline() == 1) {
//						online++;
//					}
//					if(devVO.getDev_warn()!=null && devVO.getDev_warn() ==1) {
//						warn++;
//					}
//				}
//			}
//			voPro.setTotal(total);
//			voPro.setOnline(online);
//			voPro.setWarn(warn);
//			groupsPro.add(voPro);
//		}
//		r.data.put("groups", groupsPro);
//		return r;
//	}

    // 群组设备列表
//	@GetMapping("/groupDevList")
//	public R groupDevList(String group_uuid) {
//		R r =JSONUtil.getSuccessMsg(null);
//		List<GroupVO> groups = baseDAO.queryAllByCondition(GroupVO.class, " and group_uuid ='"+group_uuid+"' ", null);
//		if(groups.size()>0) {
//			GroupVO vo =groups.get(0);
//			JSONArray devListArray =JSONArray.parseArray(vo.getDev_list());
//			List<DevVO> dev_list =new ArrayList<>();
//			if(devListArray!=null && devListArray.size()>0) {
//				for(int i=0;i<devListArray.size();i++) {
//					DevVO devVO =baseDAO.queryByKey(DevVO.class, "dev_id", devListArray.getString(i));
//					dev_list.add(devVO);
//				}
//			}
//			r.data.put("dev_list", dev_list);
//		}
//		return r;
//	}

    // 添加群组设备
//	@GetMapping("/addGroupDev")
//	public R addGroupDev(String dev_id,String group_uuid) {
//		R r =JSONUtil.getSuccessMsg(null);
//		List<GroupVO> groups = baseDAO.queryAllByCondition(GroupVO.class, " and group_uuid ='"+group_uuid+"' ", null);
//		if(groups.size()>0) {
//			GroupVO vo =groups.get(0);
//			String jsonStr =vo.getDev_list();
//			if(StringUtils.isEmpty(jsonStr)) {
//				StringBuffer sql = new StringBuffer("update dev_group set dev_list =JSON_ARRAY('"+dev_id+"') where delete_tag =0 and group_uuid='"+group_uuid+"'");
//				baseDAO.updateSQL(sql.toString());
//			}
//			else {
//				JSONArray jsonArray =JSONArray.parseArray(jsonStr);
//				jsonArray.add(dev_id);
//				StringBuffer sql = new StringBuffer("update dev_group set dev_list =JSON_ARRAY(");
//				for(int i=0;i<jsonArray.size();i++) {
//					sql.append("'"+jsonArray.getString(i)+"',");
//				}
//				sql.setLength(sql.length() - 1);
//				sql.append(") where delete_tag =0 and group_uuid='"+group_uuid+"' ");
//				baseDAO.updateSQL(sql.toString());
//			}
//		}
//		return r;
//	}

    // 删除群组设备
//	@GetMapping("/removeGroupDev")
//	public R removeGroupDev(String dev_id,String group_uuid) {
//		R r =JSONUtil.getSuccessMsg(null);
//		List<GroupVO> groups = baseDAO.queryAllByCondition(GroupVO.class, " and group_uuid ='"+group_uuid+"' ", null);
//		if(groups.size()>0) {
//			GroupVO vo =groups.get(0);
//			String jsonStr =vo.getDev_list();
//			// 原数组
//			JSONArray jsonArray =JSONArray.parseArray(jsonStr);
//			// 删除指定元素后的列表
//			JSONArray updatedArray = new JSONArray();
//			for(int i=0;i<jsonArray.size();i++) {
//				if (!jsonArray.getString(i).equals(dev_id)) {
//	                updatedArray.add(jsonArray.getString(i));
//	            }
//			}
//			StringBuffer sql = new StringBuffer("update dev_group set dev_list =JSON_ARRAY(");
//
//			// 列表不为空
//			if(updatedArray.size()>0) {
//				for(int i=0;i<updatedArray.size();i++) {
//					sql.append("'"+updatedArray.getString(i)+"',");
//				}
//				sql.setLength(sql.length() - 1);
//			}
//			sql.append(") where delete_tag =0 and group_uuid='"+group_uuid+"' ");
//			baseDAO.updateSQL(sql.toString());
//		}
//		return r;
//	}

    // 群组列表
    @GetMapping("/groupList")
    @ApiOperation(value = "群组列表")
    public R groupList(String openid) {
        R r = JSONUtil.getSuccessMsg(null);
        List<GroupVO> groups = baseDAO.queryAllByCondition(GroupVO.class, " and (user_openid='" + openid + "' or group_uuid in (select group_uuid"
                + " from group_share where delete_tag =0 and shared='" + openid + "' and state =1))", null);
        List<GroupProVO> groupsPro = new ArrayList<>();
        for (int i = 0; i < groups.size(); i++) {
            GroupVO vo = groups.get(i);
            GroupProVO voPro = new GroupProVO();
            voPro.setGroup(vo);
            int total = 0;
            int online = 0;
            int warn = 0;
            List<GroupDevMapVO> maps = baseDAO.queryAllByCondition(GroupDevMapVO.class, " and group_uuid ='" + vo.getGroup_uuid() + "' and delete_tag =0 ", null);
            for (int j = 0; j < maps.size(); j++) {
                GroupDevMapVO mapVO = maps.get(j);
                DevVO devVO = baseDAO.queryByKey(DevVO.class, "dev_id", mapVO.getDev_id());
                // 设备存在且未删除
                if (devVO != null && devVO.getDelete_tag() == 0) {
                    total++;
                    if (devVO.getOnline() == 1) {
                        online++;
                    }
                    if (devVO.getDev_warn() != null && devVO.getDev_warn() == 1) {
                        warn++;
                    }
                }
            }
            voPro.setTotal(total);
            voPro.setOnline(online);
            voPro.setWarn(warn);
            groupsPro.add(voPro);
        }
        r.data.put("groups", groupsPro);
        return r;
    }

    // 群组设备列表
    @GetMapping("/groupDevList")
    @ApiOperation(value = "群组设备列表")
    public R groupDevList(String group_uuid) {
        R r = JSONUtil.getSuccessMsg(null);
        List<GroupDevMapVO> maps = baseDAO.queryAllByCondition(GroupDevMapVO.class, " and group_uuid ='" + group_uuid + "' and delete_tag =0 ", null);
        // 获取群组设备列表
        List<DevVO> dev_list = new ArrayList<>();
        for (int i = 0; i < maps.size(); i++) {
            GroupDevMapVO mapVO = maps.get(i);
            DevVO devVO = baseDAO.queryByKey(DevVO.class, "dev_id", mapVO.getDev_id());
            // 设备存在且未删除
            if (devVO != null && devVO.getDelete_tag() == 0) {
                dev_list.add(devVO);
            }
        }
        // 获取群组基本信息
        GroupVO group = baseDAO.queryByKey(GroupVO.class, "group_uuid", group_uuid);
        r.data.put("dev_list", dev_list);
        r.data.put("group", group);
        return r;
    }

    // 添加群组设备
    @GetMapping("/addGroupDev")
    @ApiOperation(value = "添加群组设备")
    public R addGroupDev(String dev_id, String group_uuid) {
        R r = JSONUtil.getSuccessMsg(null);
        GroupVO groupVO = baseDAO.queryByKey(GroupVO.class, "group_uuid", group_uuid);
        DevVO devVO = baseDAO.queryByKey(DevVO.class, "dev_id", dev_id);
        if (groupVO != null && devVO != null) {
            List<GroupDevMapVO> maps = baseDAO.queryAllByConditionNocare(GroupDevMapVO.class, " and dev_id='" + dev_id + "' and group_uuid ='" + group_uuid + "' ", null);
            Log.info(String.valueOf(maps.size()));
            if (maps.size() > 0) {
                GroupDevMapVO vo = maps.get(0);
                if (vo.getDelete_tag() == 1) {
                    baseDAO.updateSQL("update group_dev_map set delete_tag =0 where dev_id='" + dev_id + "' and group_uuid ='" + group_uuid + "' ");
                    return r;
                }
                r = JSONUtil.getErrorMsg("请勿重复添加同一设备");
                return r;
            }
            GroupDevMapVO mapVO = new GroupDevMapVO();
            mapVO.setDev_group_index(groupVO.getId());
            mapVO.setGroup_uuid(group_uuid);
            mapVO.setDev_info_index(devVO.getId());
            mapVO.setDev_id(dev_id);
            mapVO.setDelete_tag(0);
            baseDAO.insertAll(mapVO);
        } else {
            r = JSONUtil.getErrorMsg("数据出现错误，请重新进入小程序");
        }
        return r;
    }

    // 删除群组设备
    @GetMapping("/removeGroupDev")
    @ApiOperation(value = "删除群组设备")
    public R removeGroupDev(String dev_id, String group_uuid) {
        R r = JSONUtil.getSuccessMsg(null);
        List<GroupDevMapVO> maps = baseDAO.queryAllByCondition(GroupDevMapVO.class, " and group_uuid ='" + group_uuid + "' and dev_id ='" + dev_id + "' and "
                + "delete_tag =0", null);
        if (maps.size() > 0) {
            baseDAO.updateSQL("update group_dev_map set delete_tag =1 where group_uuid ='" + group_uuid + "' and dev_id ='" + dev_id + "' ");
        } else {
            r = JSONUtil.getErrorMsg("删除群组设备失败，请重新进入小程序");
        }
        return r;
    }

    /**
     * 通过链接分享群组
     *
     * @param vo
     * @return
     */
    @GetMapping("/groupShare")
    @ApiOperation(value = "通过链接分享群组")
    public R groupShare(GroupShareVO groupShare) {
        R r = JSONUtil.getSuccessMsg(null);
        baseDAO.insertAll(groupShare);
        return r;
    }

    /**
     * 群组分享信息确认
     *
     * @param uuid
     * @return
     */
    @GetMapping("/groupShareConfirm")
    @ApiOperation(value = "群组分享信息确认")
    public R groupShareConfirm(String uuid, String shared, String shared_phone) {
        R r = JSONUtil.getSuccessMsg(null);
        GroupShareVO vo = baseDAO.queryByKey(GroupShareVO.class, "uuid", uuid);
        if (vo != null) {
            if (vo.getState() == 0) {
                vo.setState(3);
                baseDAO.updateSQL("update group_share set state =3,shared='" + shared + "',shared_phone='" + shared_phone + "' where uuid='" + uuid + "' and delete_tag =0 ");
            }
            r.data.put("groupShare", vo);
        } else {
            r = JSONUtil.getErrorMsg("分享已过期");
        }
        return r;
    }

    /**
     * 群组分享事件处理
     *
     * @param uuid
     * @return
     */
    @GetMapping("/groupSharePro")
    @ApiOperation(value = "群组分享事件处理")
    public R groupSharePro(String uuid, int state) {
        R r = JSONUtil.getSuccessMsg(null);
        GroupShareVO vo = baseDAO.queryByKey(GroupShareVO.class, "uuid", uuid);
        if (vo != null) {
            vo.setState(state);
            baseDAO.updateSQL("update group_share set state =" + state + " where uuid='" + uuid + "' and delete_tag =0 ");
            r.data.put("groupShare", vo);
        }
        return r;
    }

    /**
     * 生成uuid
     *
     * @return
     */
    @GetMapping("/generateUUID")
    @ApiOperation(value = "生成uuid")
    public R generateUUID() {
        R r = JSONUtil.getSuccessMsg(null);
        String uuid = UUID.randomUUID().toString();
        r.data.put("uuid", uuid);
        return r;
    }

    /**
     * 用户注销
     *
     * @param openid
     * @return
     */
    @GetMapping("userLogout")
    @ApiOperation(value = "用户注销")
    public R userLogout(String openid) {
        R r = JSONUtil.getSuccessMsg(null);
        // user_info
        baseDAO.updateSQL("update user_info set delete_tag =1 where openid ='" + openid + "' ");
        // dev_share
        baseDAO.updateSQL("update dev_share set delete_tag =1 where sharer ='" + openid + "' or shared ='" + openid + "' ");
        // dev_info
        baseDAO.updateSQL("update dev_info set delete_tag =1 where user_openid ='" + openid + "' ");
        // dev_group
        baseDAO.updateSQL("update dev_group set delete_tag =1 where user_openid ='" + openid + "' ");
        // group_share
        baseDAO.updateSQL("update group_share set delete_tag =1 where sharer ='" + openid + "' or shared ='" + openid + "' ");
        return r;
    }

    // 获取设备所属群组
    @GetMapping("getDevGroup")
    @ApiOperation(value = "获取设备所属群组")
    public R getDevGroup(String dev_id) {
        R r = JSONUtil.getSuccessMsg(null);
        baseDAO.queryBySQL("");
        return r;
    }

    // 上传户型图
    @PostMapping("/uploadMap")
    @ApiOperation(value = "上传户型图")
    public R uploadMap(@RequestParam("file") MultipartFile file,
                       @RequestParam String group_uuid) throws IllegalStateException,
            IOException {
        // 校验文件非空
        if (file.isEmpty()) {
            return JSONUtil.getErrorMsg("文件为空");
        }

        // 校验文件类型
        String contentType = file.getContentType();
        if (contentType == null || (!contentType.equalsIgnoreCase("image/png") && !contentType.equalsIgnoreCase("image/jpeg"))) {
            return JSONUtil.getErrorMsg("仅支持PNG或JPG 格式");
        }

        // 校验文件头
        byte[] fileHeader = new byte[8];
        try (InputStream is = file.getInputStream()) {
            int readBytes = is.read(fileHeader);
            if (readBytes < 8) {
                return JSONUtil.getErrorMsg("文件格式不完整");
            }
        }
        boolean isPng = ImageUtil.checkPngMagicNumber(fileHeader);
        boolean isJpeg = ImageUtil.checkJpegMagicNumber(fileHeader);

        if (!isPng && !isJpeg) {
            return JSONUtil.getErrorMsg("文件实际格式与声明不符，仅支持 PNG/JPG");
        }

        // 生成安全路径
        String dirPath = FileUploadController.getFilePath() + "map/" + group_uuid + "/";
        File dir = new File(dirPath);
        if (!dir.exists() && !dir.mkdirs()) {
            Log.error("目录创建失败: " + dirPath);
            return JSONUtil.getErrorMsg("服务器错误");
        }

        // 删除旧文件
        File[] existingFiles = dir.listFiles();
        if (existingFiles != null) {
            for (File existingFile : existingFiles) {
                if (existingFile.isFile()) {
                    if (!existingFile.delete()) {
                        Log.error("旧文件无法删除，上传中断");
                        return JSONUtil.getErrorMsg("旧文件无法删除，上传中断");
                    }
                }
            }
        }

        // 根据文件类型生成正确扩展名
        String extension = isPng ? ".png" : ".jpg";
        String fileName = System.currentTimeMillis() + extension;

        // 上传服务器
        file.transferTo(new File(dirPath, fileName));
        Log.info("上传成功");

        // 查询
        FloorPlanVO vo = baseDAO.queryByKey(FloorPlanVO.class, "group_uuid", group_uuid);
        // 上传数据库
        String floorplan_addr = "map/" + group_uuid + "/" + fileName;
        if (vo != null) {
            StringBuffer buffer = new StringBuffer("update group_floorplan set ");
            if (vo.getDelete_tag() == 1) {
                buffer.append("delete_tag =0,");
            }
            buffer.append("floorplan_addr ='" + floorplan_addr + "',update_time ='" + new Timestamp(System.currentTimeMillis())
                    + "' where group_uuid ='" + group_uuid + "' ");
            baseDAO.updateSQL(buffer.toString());
        } else {
            vo = new FloorPlanVO();
            vo.setCreate_time(new Timestamp(System.currentTimeMillis()));
            vo.setDelete_tag(0);
            vo.setGroup_uuid(group_uuid);
            vo.setFloorplan_addr(floorplan_addr);
            baseDAO.insertAll(vo);
        }
        return JSONUtil.getSuccessMsg(null);
    }

    // 获取户型图（URL地址）
    @GetMapping("/getFloorPlan")
    @ApiOperation(value = "获取户型图")
    public R getFloorPlan(String group_uuid) {
        String dirPath = FileUploadController.getFilePath() + "map/" + group_uuid + "/";
        File dir = new File(dirPath);
        File[] existingFiles = dir.listFiles();
        if (existingFiles != null) {
            if (existingFiles.length > 1) {
                // 存在多张户型图
                return JSONUtil.getErrorMsg("存在多张户型图");
            } else if (existingFiles.length == 1) {
                // 存在且只有一张户型图
                File existingFile = existingFiles[0];

                // URL拼接
                FloorPlanVO vo = baseDAO.queryByKey(FloorPlanVO.class, "group_uuid", group_uuid);
                StringBuffer fileURL = new StringBuffer(Environment.COMMON_FILE_UPLOAD_PATH);
                if (vo != null && vo.getDelete_tag() == 0) {
                    fileURL.append(vo.getFloorplan_addr());
                } else {
                    fileURL.append("map/" + group_uuid + "/" + existingFile.getName());
                }
                // 返回成功信息，包含户型图的URL
                R r = JSONUtil.getSuccessMsg("户型图获取成功");
                r.customData.put("url", fileURL.toString());
                return r;
            }
        }
        // 无户型图
        return JSONUtil.getErrorMsg("户型图未配置");
    }

    private final static String TOKEN = "lnServer";

    /**
     * 查询前一天上厕所的次数
     *
     * @param time
     * @param dev_id
     * @return
     */
    @GetMapping("/getWcTimes")
    @ApiOperation(value = "查询前一天用卫生间的次数")
    public R getWcTimes(String dev_id, String time) {
        R r = JSONUtil.getSuccessMsg(null);
        LocalDate inputDate = LocalDate.parse(time);
        String previousDateStr = inputDate.toString();
        // 判断查询结果并返回
        List<StayTime> stayTimes = null;
        try {
            stayTimes = jdbcTemplate.query(
                    "SELECT * FROM stay_time WHERE dev_id = ? AND DATE(leave_time) = ? and type = 0 order by leave_time",
                    new Object[]{dev_id, previousDateStr},
                    new BeanPropertyRowMapper<>(StayTime.class)
            );
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (Objects.nonNull(stayTimes) && stayTimes.size() > 0) {
            r.data.put("stayTimes", stayTimes);
            r.data.put("count", stayTimes.size());
        } else {
            r.data.put("stayTimes", null);
            r.data.put("count", 0);
        }
        return r;
    }

    /**
     * 查询当天上卫生间的次数，以及用马桶的次数
     *
     * @param time
     * @param dev_id
     * @return
     */
    @GetMapping("/getWcInfo")
    @ApiOperation(value = "查询当天上卫生间的次数，以及用马桶的次数")
    public R getWcInfo(String dev_id, String time) {
        R r = JSONUtil.getSuccessMsg(null);
        LocalDate inputDate = LocalDate.parse(time);
        String inputDateStr = inputDate.toString();
        // 判断查询结果并返回
        List<StayTime> stayTimes = null;
        try {
            stayTimes = jdbcTemplate.query(
                    "SELECT * FROM stay_time WHERE dev_id = ? AND DATE(leave_time) = ?  order by leave_time ",
                    new Object[]{dev_id, inputDateStr},
                    new BeanPropertyRowMapper<>(StayTime.class)
            );
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (Objects.nonNull(stayTimes) && stayTimes.size() > 0) {

            List<StayTime> roomList = stayTimes.stream().filter(stayTime -> 0 == (stayTime.getType())).collect(Collectors.toList());
            List<StayTime> toiletList = stayTimes.stream().filter(stayTime -> 1 == stayTime.getType()).collect(Collectors.toList());

            if (!CollectionUtils.isEmpty(roomList)) {
                for (StayTime stayTime : roomList) {

                    if (!CollectionUtils.isEmpty(toiletList)) {
                        stayTime.setToiletList(toiletList.stream().filter(
                                toilet ->
                                        toilet.getEnter_time().compareTo(stayTime.getEnter_time())>=0 && toilet.getLeave_time().compareTo(stayTime.getLeave_time())<=0
                        ).collect(Collectors.toList()));
                    }
                }
                r.data.put("stayTimes", roomList);
                r.data.put("count", roomList.size());
                return r;
            }
        }

        r.data.put("stayTimes", null);
        r.data.put("count", 0);
        return r;
    }


    //公众号模块

    @GetMapping("/getAccessTokenTwo")
    @ApiOperation(value = "获取公众号token")
    public R getAccessTokenTwo() {
        R r = JSONUtil.getSuccessMsg(null);
        // 发送模板消息
        wxSendMessage.queryAccessToken("devId", "测试设备", "17356519496", "oWlo-6iXL0pQeYWZxEpwB8knv6D8", "test");
        return r;
    }


    @GetMapping("/serverCheck")
    @ApiOperation(value = "公众号发信息接口")
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String signature = request.getParameter("signature");
        String timestamp = request.getParameter("timestamp");
        String nonce = request.getParameter("nonce");
        String echostr = request.getParameter("echostr");

        String signatureCheck = getSHA1(TOKEN, timestamp, nonce);
        if (signatureCheck != null && signatureCheck.equals(signature)) {
            PrintWriter out = response.getWriter();
            out.print(echostr);
            out.flush();
            out.close();
        }
    }

    @PostMapping("/addOneDevice")
    @ApiOperation(value = "单个添加设备")
    public R addOneDevice(String dev_id, String dev_type, String software){
        //校验判断 1.存在且未软删除
        DevVO devVO = baseDAO.queryByKey(DevVO.class, "dev_id", dev_id);
        if (devVO != null && devVO.getDelete_tag() == 0) {
            return JSONUtil.getErrorMsg("设备已存在");
        }
        //检验判断 2.存在且已软删除 实现逻辑删除恢复
        if (devVO != null && devVO.getDelete_tag() == 1){
            String updateSql = "UPDATE dev_info SET dev_type = ?, software = ?, delete_tag = 0, update_time = ? WHERE dev_id = ?";
            baseDAO.getJdbcTemplate().update(updateSql, 
                dev_type,
                software,
                new Timestamp(System.currentTimeMillis()),
                dev_id
            );
            return JSONUtil.getSuccessMsg("设备恢复成功");
        }
        //3.新设备添加
        DevVO deviceVO = new DevVO();
        deviceVO.setDev_id(dev_id);
        deviceVO.setDev_type(dev_type);
        deviceVO.setSoftware(software);
        deviceVO.setStatus_light(1);
        deviceVO.setOnline(0);
        deviceVO.setDelete_tag(0);
        baseDAO.insertAll(deviceVO);
        return JSONUtil.getSuccessMsg("添加成功");
    }

    @PostMapping("/deleteDevice")
    @ApiOperation(value = "删除设备")
    public R deleteDevice(String dev_id) {
        //1.参数校验
        if (StringUtils.isBlank(dev_id)){
            return JSONUtil.getErrorMsg("设备ID不能为空");
        }

        //2.查询设备
        DevVO devVO = baseDAO.queryByKey(DevVO.class, "dev_id", dev_id);
        if (devVO != null) {
            String updateSql = "UPDATE dev_info SET delete_tag = 1 WHERE dev_id = ?";
            baseDAO.getJdbcTemplate().update(updateSql, dev_id);
            return JSONUtil.getSuccessMsg("设备删除成功");
        } else {
            return JSONUtil.getErrorMsg("设备不存在");
        }
    }

    public String getSHA1(String token, String timestamp, String nonce) {
        try {
            String[] array = new String[]{token, timestamp, nonce};
            Arrays.sort(array);
            StringBuilder sb = new StringBuilder();
            for (String s : array) {
                sb.append(s);
            }
            MessageDigest md = MessageDigest.getInstance("SHA-1");
            md.update(sb.toString().getBytes());
            byte[] digest = md.digest();
            StringBuilder hexstr = new StringBuilder();
            for (byte b : digest) {
                String shaHex = Integer.toHexString(b & 0xFF);
                if (shaHex.length() < 2) {
                    hexstr.append(0);
                }
                hexstr.append(shaHex);
            }
            return hexstr.toString();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }


    // 上传导入表
    @PostMapping("/uploadDevInfo")
    @ApiOperation(value = "上传导入表")
    public R uploadDevInfo(@RequestParam("file") MultipartFile file) throws IllegalStateException,
            IOException {
        // 校验文件非空
        if (file.isEmpty()) {
            return JSONUtil.getErrorMsg("文件为空");
        }
        System.out.println("start time :" + System.currentTimeMillis());
        List<DevInfoImportVo> devInfoImportVoList = EasyExcel.read(file.getInputStream(), DevInfoImportVo.class, null).sheet(0).doReadSync();
        System.out.println("parse time :" + System.currentTimeMillis());
        if (CollectionUtils.isEmpty(devInfoImportVoList)) {
            return JSONUtil.getErrorMsg("数据为空");
        }

        R r = JSONUtil.getSuccessMsg(null);

        String sql2 = "SELECT * FROM dev_info WHERE dev_id IN (" +
                devInfoImportVoList.stream()
                        .map(DevInfoImportVo::getDev_id)
                        .distinct()
                        .map(id -> "?")
                        .collect(Collectors.joining(", ")) +
                ")";
        List<String> params2 = devInfoImportVoList.stream()
                .map(DevInfoImportVo::getDev_id)
                .distinct()
                .collect(Collectors.toList());

        List<DevVO> newlist2 = baseDAO.getJdbcTemplate().query(sql2, params2.toArray(), new BeanPropertyRowMapper<>(DevVO.class));
        if (!CollectionUtils.isEmpty(newlist2)) {
            List<String> dupDevIds = newlist2.stream().map(DevVO::getDev_id).collect(Collectors.toList());
            r.data.put("dupIdList", dupDevIds);
            devInfoImportVoList = devInfoImportVoList.stream().filter(item -> !dupDevIds.contains(item.getDev_id())).collect(Collectors.toList());
        }

        // 将数据分批插入数据库，每批100条，使用jdbcTemplate
        // 将devInfoImportVoList按元素每一百个分成多个集合
        int batchSize = 100;
        int total = devInfoImportVoList.size();
        for (int i = 0; i < total; i += batchSize) {
            int end = Math.min(i + batchSize, total);
            List<DevInfoImportVo> subList = devInfoImportVoList.subList(i, end);
            // 使用jdbcTemplate批量插入数据
            baseDAO.getJdbcTemplate().batchUpdate(
                    "INSERT INTO dev_info (dev_id, dev_type, software, status_light, online, delete_tag, create_time, update_time, x, y, north_angle) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                    subList.stream().map(devVO -> new Object[]{
                        devVO.getDev_id(),
                        devVO.getDev_type(),
                        devVO.getSoftware(),
                        1,
                        0,
                        0,
                        new Timestamp(System.currentTimeMillis()),
                        new Timestamp(System.currentTimeMillis()), 0, 0, 0
                    }).collect(Collectors.toList()),
                    new int[]{Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.TIMESTAMP, Types.TIMESTAMP}
            );
        }


        System.out.println("end time :" + System.currentTimeMillis());
        return r;
    }

    // 查询列表
    @PostMapping("/queryList")
    @ApiOperation(value = "查询列表")
    public R queryList(@RequestBody QueryListParam queryListParam) throws IllegalStateException,
            IOException {

        // 根据queryListParam查询条件是否为空，拼接 jdbc sql
        StringBuilder sql = new StringBuilder("SELECT * FROM dev_info WHERE delete_tag = 0 ");
        StringBuilder countSql = new StringBuilder("SELECT count(*) FROM dev_info WHERE delete_tag = 0 ");
        if (!StringUtils.isEmpty(queryListParam.getDevId())) {
            sql.append(" AND dev_id like '%" + queryListParam.getDevId() + "%'");
            countSql.append(" AND dev_id like '%" + queryListParam.getDevId() + "%'");
        }
        if (!StringUtils.isEmpty(queryListParam.getDevType())) {
            sql.append(" AND dev_type = '" + queryListParam.getDevType() + "'");
            countSql.append(" AND dev_type = '" + queryListParam.getDevType() + "'");
        }
        if (!StringUtils.isEmpty(queryListParam.getSoftware())) {
            sql.append(" AND software = '" + queryListParam.getSoftware() + "'");
            countSql.append(" AND software = '" + queryListParam.getSoftware() + "'");
        }

        if (!StringUtils.isEmpty(queryListParam.getDevName())) {
            sql.append(" AND dev_name like '%" + queryListParam.getDevName() + "%'");
            countSql.append(" AND dev_name like '%" + queryListParam.getDevName() + "%'");
        }

        if (queryListParam.getOnline() != null) {
            sql.append(" AND online = '" + queryListParam.getOnline() + "'");
            countSql.append(" AND online = '" + queryListParam.getOnline() + "'");
        }

        // 添加创建时间的范围查询 数据库里的create_time字段为timestamp类型，查询条件里的createtimestart和createtimeend为string类型，需要转换成timestamp类型,yyyyMMdd
        String queryStartTime = queryListParam.getCreateTimeStart();
        if (!StringUtils.isEmpty(queryStartTime)) {
            sql.append(" AND create_time >= STR_TO_DATE('" + queryStartTime + "', '%Y%m%d') ");
            countSql.append(" AND create_time >= STR_TO_DATE('" + queryStartTime + "', '%Y%m%d') ");
        }
        String queryEndTime = queryListParam.getCreateTimeEnd();
        if (!StringUtils.isEmpty(queryEndTime)) {
            LocalDate endDate = LocalDate.parse(queryEndTime, DateTimeFormatter.ofPattern("yyyyMMdd"));
            endDate = endDate.plusDays(1);
            sql.append(" AND create_time < STR_TO_DATE('" + DateTimeFormatter.ofPattern("yyyyMMdd").format(endDate) + "', '%Y%m%d') ");
            countSql.append(" AND create_time < STR_TO_DATE('" + DateTimeFormatter.ofPattern("yyyyMMdd").format(endDate) + "', '%Y%m%d') ");
        }

        // count
        // 待确认分享数
        Integer count = baseDAO.getJdbcTemplate().queryForObject(
                countSql.toString(), Integer.class);
        R r = JSONUtil.getSuccessMsg(null);
        if (count == null || count == 0) {
            r.data.put("count", 0);
            return r;
        } else {
            r.data.put("count", count);
        }

        // 分页参数
        int pageSize = queryListParam.getPageSize();
        int pageNum = queryListParam.getPageNum();
        int offset = (pageNum - 1) * pageSize;

        sql.append(" ORDER BY create_time DESC LIMIT ").append("" + pageSize).append(" OFFSET ").append("" + offset);
        List<DevVO> newlist2 = baseDAO.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(DevVO.class));
        System.out.println("end time :" + System.currentTimeMillis());
        r.data.put("resList", newlist2);
        return r;
    }
}