#feat 修改大屏统计自愿者和普通用户sql语法错误问题

yanghui

2022-10-28 102b2f83a9010ec4aca2f75f6dfcb67b89f0413f

 springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComSwRotaServiceImpl.java

@@ -1,43 +1,38 @@
package com.panzhihua.service_community.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.panzhihua.common.model.dtos.community.ComSwRotaPageDTO;
import com.panzhihua.common.model.dtos.community.ComSwRotaSaveDTO;
import com.panzhihua.common.model.vos.LoginUserInfoVO;
import com.panzhihua.common.model.vos.R;
import com.panzhihua.common.model.vos.community.ComSwPatrolRecordVO;
import com.panzhihua.common.model.vos.community.ComSwRotaExcelVO;
import com.panzhihua.common.model.vos.community.ComSwRotaPersonVO;
import com.panzhihua.common.model.vos.community.ComSwRotaVO;
import com.panzhihua.common.model.vos.partybuilding.PartyBuildingComPbDynVO;
import com.panzhihua.common.service.user.UserService;
import com.panzhihua.common.utlis.DateUtils;
import com.panzhihua.common.utlis.Snowflake;
import com.panzhihua.common.utlis.StringUtils;
import com.panzhihua.service_community.dao.ComOpsAccUserDAO;
import com.panzhihua.service_community.dao.ComSwPatrolRecordDAO;
import com.panzhihua.service_community.dao.ComSwRotaDAO;
import com.panzhihua.service_community.model.dos.ComMngUserTagDO;
import com.panzhihua.service_community.model.dos.ComSwRotaDO;
import com.panzhihua.service_community.service.ComSwRotaService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.context.annotation.Bean;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.annotation.Resource;

import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.panzhihua.common.model.dtos.community.ComSwRotaPageDTO;
import com.panzhihua.common.model.dtos.community.ComSwRotaSaveDTO;
import com.panzhihua.common.model.vos.R;
import com.panzhihua.common.model.vos.community.ComSwRotaExcelVO;
import com.panzhihua.common.model.vos.community.ComSwRotaPersonVO;
import com.panzhihua.common.model.vos.community.ComSwRotaVO;
import com.panzhihua.common.service.user.UserService;
import com.panzhihua.common.utlis.DateUtils;
import com.panzhihua.common.utlis.Snowflake;
import com.panzhihua.common.utlis.StringUtils;
import com.panzhihua.service_community.dao.ComSwPatrolRecordDAO;
import com.panzhihua.service_community.dao.ComSwRotaDAO;
import com.panzhihua.service_community.model.dos.ComSwRotaDO;
import com.panzhihua.service_community.service.ComSwRotaService;

import cn.hutool.core.date.DateUtil;
import lombok.extern.slf4j.Slf4j;

/**
 * @auther txb
@@ -76,13 +71,13 @@
                comSwRotaVO.setWeekDay(DateUtils.dateToWeek(comSwRotaVO.getRotaDate()));
                StringBuilder leaderNameString = new StringBuilder();
                StringBuilder leaderPhoneString = new StringBuilder();
                //值班领导id查询值班领导的名字和电话
                if(StringUtils.isNotEmpty(comSwRotaVO.getRotaLeader())){
                // 值班领导id查询值班领导的名字和电话
                if (StringUtils.isNotEmpty(comSwRotaVO.getRotaLeader())) {
                    String[] leaderIds = comSwRotaVO.getRotaLeader().split(",");
                    for (int i = 0; i < leaderIds.length ; i++) {
                        Map<String,String> leader = comSwPatrolRecordDAO.getPbServiceTeamById(leaderIds[i]);
                        if(leader != null){
                            if(i != 0){
                    for (int i = 0; i < leaderIds.length; i++) {
                        Map<String, String> leader = comSwPatrolRecordDAO.getPbServiceTeamById(leaderIds[i]);
                        if (leader != null) {
                            if (i != 0) {
                                leaderNameString.append(",");
                                leaderPhoneString.append(",");
                            }
@@ -93,15 +88,15 @@
                    comSwRotaVO.setLeaderName(leaderNameString.toString());
                    comSwRotaVO.setLeaderPhone(leaderPhoneString.toString());
                }
                //值班人员id查询值班人员的名字和电话
                // 值班人员id查询值班人员的名字和电话
                StringBuilder personNameString = new StringBuilder();
                StringBuilder personPhoneString = new StringBuilder();
                if(StringUtils.isNotEmpty(comSwRotaVO.getRotaPerson())){
                if (StringUtils.isNotEmpty(comSwRotaVO.getRotaPerson())) {
                    String[] personIds = comSwRotaVO.getRotaPerson().split(",");
                    for (int i = 0; i < personIds.length ; i++) {
                        Map<String,String> person = comSwPatrolRecordDAO.getPbServiceTeamById(personIds[i]);
                        if(person != null){
                            if(i != 0){
                    for (int i = 0; i < personIds.length; i++) {
                        Map<String, String> person = comSwPatrolRecordDAO.getPbServiceTeamById(personIds[i]);
                        if (person != null) {
                            if (i != 0) {
                                personNameString.append(",");
                                personPhoneString.append(",");
                            }
@@ -124,20 +119,20 @@
        if (null == comSwRotaDO) {
            return R.fail("该值班表不存在");
        }
        BeanUtils.copyProperties(comSwRotaDO,comSwRotaVO);
        BeanUtils.copyProperties(comSwRotaDO, comSwRotaVO);
        if (null != comSwRotaDO.getRotaDate()) {
            comSwRotaVO.setRotaDate(new SimpleDateFormat("yyyy-MM-dd").format(comSwRotaDO.getRotaDate()));
        }
        comSwRotaVO.setWeekDay(DateUtils.dateToWeek(comSwRotaVO.getRotaDate()));
        StringBuilder leaderNameString = new StringBuilder();
        StringBuilder leaderPhoneString = new StringBuilder();
        //值班领导id查询值班领导的名字和电话
        if(StringUtils.isNotEmpty(comSwRotaDO.getRotaLeader())){
        // 值班领导id查询值班领导的名字和电话
        if (StringUtils.isNotEmpty(comSwRotaDO.getRotaLeader())) {
            String[] leaderIds = comSwRotaDO.getRotaLeader().split(",");
            for (int i = 0; i < leaderIds.length ; i++) {
                Map<String,String> leader = comSwPatrolRecordDAO.getPbServiceTeamById(leaderIds[i]);
                if(leader != null){
                    if(i != 0){
            for (int i = 0; i < leaderIds.length; i++) {
                Map<String, String> leader = comSwPatrolRecordDAO.getPbServiceTeamById(leaderIds[i]);
                if (leader != null) {
                    if (i != 0) {
                        leaderNameString.append(",");
                        leaderPhoneString.append(",");
                    }
@@ -148,15 +143,15 @@
            comSwRotaVO.setLeaderName(leaderNameString.toString());
            comSwRotaVO.setLeaderPhone(leaderPhoneString.toString());
        }
        //值班人员id查询值班人员的名字和电话
        // 值班人员id查询值班人员的名字和电话
        StringBuilder personNameString = new StringBuilder();
        StringBuilder personPhoneString = new StringBuilder();
        if(StringUtils.isNotEmpty(comSwRotaDO.getRotaPerson())){
        if (StringUtils.isNotEmpty(comSwRotaDO.getRotaPerson())) {
            String[] personIds = comSwRotaDO.getRotaPerson().split(",");
            for (int i = 0; i < personIds.length ; i++) {
                Map<String,String> person = comSwPatrolRecordDAO.getPbServiceTeamById(personIds[i]);
                if(person != null){
                    if(i != 0){
            for (int i = 0; i < personIds.length; i++) {
                Map<String, String> person = comSwPatrolRecordDAO.getPbServiceTeamById(personIds[i]);
                if (person != null) {
                    if (i != 0) {
                        personNameString.append(",");
                        personPhoneString.append(",");
                    }
@@ -172,14 +167,15 @@

    @Override
    public R addRota(List<ComSwRotaSaveDTO> comSwRotaSaveDTOs) {
        for (ComSwRotaSaveDTO comSwRotaSaveDTO : comSwRotaSaveDTOs){
        for (ComSwRotaSaveDTO comSwRotaSaveDTO : comSwRotaSaveDTOs) {
            if (comSwRotaSaveDTO.getId() != null && comSwRotaSaveDTO.getId() != 0) {
                ComSwRotaDO comSwRotaDO = comSwRotaDAO.selectById(comSwRotaSaveDTO.getId());
                if (null == comSwRotaDO) {
                    return R.fail("该值班表不存在");
                }
                ComSwRotaDO checkCreditCode = comSwRotaDAO.selectOne(new QueryWrapper<ComSwRotaDO>().lambda().eq(ComSwRotaDO::getRotaDate, comSwRotaSaveDTO.getRotaDate()));
                if (null != checkCreditCode && comSwRotaDO.getId() != checkCreditCode.getId()) {
                ComSwRotaDO checkCreditCode = comSwRotaDAO.selectOne(new QueryWrapper<ComSwRotaDO>().lambda()
                    .eq(ComSwRotaDO::getRotaDate, comSwRotaSaveDTO.getRotaDate()).eq(ComSwRotaDO::getCommunityId,comSwRotaSaveDTO.getCommunityId()));
                if (null != checkCreditCode && !comSwRotaDO.getId().equals(checkCreditCode.getId())) {
                    return R.fail("该值班表已存在，值班日期重复");
                }
                BeanUtils.copyProperties(comSwRotaSaveDTO, comSwRotaDO);
@@ -190,7 +186,8 @@
                }
                comSwRotaDAO.updateById(comSwRotaDO);
            } else {
                Integer count = comSwRotaDAO.selectCount(new QueryWrapper<ComSwRotaDO>().lambda().eq(ComSwRotaDO::getRotaDate, comSwRotaSaveDTO.getRotaDate()));
                Integer count = comSwRotaDAO.selectCount(new QueryWrapper<ComSwRotaDO>().lambda()
                    .eq(ComSwRotaDO::getRotaDate, comSwRotaSaveDTO.getRotaDate()).eq(ComSwRotaDO::getCommunityId,comSwRotaSaveDTO.getCommunityId()));
                if (count > 0) {
                    return R.fail("该值班表已存在，值班日期重复");
                }
@@ -218,22 +215,26 @@
    }

    @Override
    public R getRotaPersonByDate(String rotaDate) {
    public R getRotaPersonByDate(String rotaDate,Long communityId) {
        ComSwRotaDO comSwRotaDO = new ComSwRotaDO();
        try {
            comSwRotaDO = comSwRotaDAO.selectOne(new QueryWrapper<ComSwRotaDO>().lambda().eq(ComSwRotaDO::getRotaDate, new SimpleDateFormat("yyyy-MM-dd").parse(rotaDate)));
        }catch (ParseException e) {
                e.printStackTrace();
            comSwRotaDO = comSwRotaDAO.selectOne(new QueryWrapper<ComSwRotaDO>().lambda().eq(ComSwRotaDO::getRotaDate,
                new SimpleDateFormat("yyyy-MM-dd").parse(rotaDate)).eq(ComSwRotaDO::getCommunityId,communityId));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        if (comSwRotaDO == null) {
            return R.fail(rotaDate + "值班表不存在");
        }
        List<ComSwRotaPersonVO> rotaPersons = new ArrayList<>();
        if (null != comSwRotaDO){
        if (null != comSwRotaDO) {
            String rotaPerson = comSwRotaDO.getRotaPerson();
            String[] rotaPersonArray = rotaPerson.split(",");
            for (int i = 0; i <= rotaPersonArray.length - 1; i++){
            for (int i = 0; i <= rotaPersonArray.length - 1; i++) {
                ComSwRotaPersonVO comSwRotaPersonVO = new ComSwRotaPersonVO();
                comSwRotaPersonVO.setId(Long.valueOf(rotaPersonArray[i]));
                Map<String,String> userMap = this.comSwRotaDAO.getImageUrl(Long.valueOf(rotaPersonArray[i]));
                if(userMap != null){
                Map<String, String> userMap = this.comSwRotaDAO.getImageUrl(Long.valueOf(rotaPersonArray[i]));
                if (userMap != null) {
                    comSwRotaPersonVO.setPhone(userMap.get("phone"));
                    comSwRotaPersonVO.setImageUrl(userMap.get("url"));
                    comSwRotaPersonVO.setName(userMap.get("name"));
@@ -245,22 +246,26 @@
    }

    @Override
    public R getRotaLeaderByDate(String rotaDate) {
    public R getRotaLeaderByDate(String rotaDate,Long communityId) {
        ComSwRotaDO comSwRotaDO = new ComSwRotaDO();
        try {
            comSwRotaDO = comSwRotaDAO.selectOne(new QueryWrapper<ComSwRotaDO>().lambda().eq(ComSwRotaDO::getRotaDate, new SimpleDateFormat("yyyy-MM-dd").parse(rotaDate)));
            comSwRotaDO = comSwRotaDAO.selectOne(new QueryWrapper<ComSwRotaDO>().lambda().eq(ComSwRotaDO::getRotaDate,
                new SimpleDateFormat("yyyy-MM-dd").parse(rotaDate)).eq(ComSwRotaDO::getCommunityId,communityId));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        if (comSwRotaDO == null) {
            return R.fail(rotaDate + "值班表不存在");
        }
        List<ComSwRotaPersonVO> rotaLeaders = new ArrayList<>();
        if (null != comSwRotaDO){
        if (null != comSwRotaDO) {
            String rotaPerson = comSwRotaDO.getRotaLeader();
            String[] rotaPersonArray = rotaPerson.split(",");
            for (int i = 0; i <= rotaPersonArray.length - 1; i++){
            for (int i = 0; i <= rotaPersonArray.length - 1; i++) {
                ComSwRotaPersonVO comSwRotaPersonVO = new ComSwRotaPersonVO();
                comSwRotaPersonVO.setId(Long.valueOf(rotaPersonArray[i]));
                Map<String,String> userMap = this.comSwRotaDAO.getImageUrl(Long.valueOf(rotaPersonArray[i]));
                if(userMap != null){
                Map<String, String> userMap = this.comSwRotaDAO.getImageUrl(Long.valueOf(rotaPersonArray[i]));
                if (userMap != null) {
                    comSwRotaPersonVO.setPhone(userMap.get("phone"));
                    comSwRotaPersonVO.setImageUrl(userMap.get("url"));
                    comSwRotaPersonVO.setName(userMap.get("name"));
@@ -273,63 +278,72 @@

    @Override
    public R getTotlePerson(String param, Long communityId) {
        return userService.listActivityManager(param,communityId);
        return userService.listActivityManager(param, communityId);
    }

    @Override
    public R listSaveSwRotaExcelVO(List<ComSwRotaExcelVO> list, Long communityId) {
        String[] parsePatterns = new String[] {"yyyy-MM-dd", "yyyy-M-dd", "yyyy-MM-d", "yyyy-M-d", "yyyy/MM/dd",
            "yyyy/M/dd", "yyyy/MM/d", "yyyy/M/d"};
        List<ComSwRotaDO> comSwRotaDOS = new ArrayList<>();
            for (ComSwRotaExcelVO comSwRotaExcelVO : list){
                Integer count = comSwRotaDAO.selectCount(new QueryWrapper<ComSwRotaDO>().lambda().eq(ComSwRotaDO::getRotaDate, comSwRotaExcelVO.getRotaDate()));
                if (count > 0) {
                    return R.fail("值班日期已存在，" + comSwRotaExcelVO.getRotaDate() + "重复");
                }
                ComSwRotaDO comSwRotaDO = new ComSwRotaDO();
                String[] leaderPhones = comSwRotaExcelVO.getLeaderPhone().split("、");
                String[] leaderNames = comSwRotaExcelVO.getLeaderName().split("、");
                List<String> leaderIds = new ArrayList<>();
                for (int i = 0; i < leaderPhones.length; i++) {
                    String id = comSwRotaDAO.getTeamIdsByTeamPhone(leaderPhones[i]);
                    if (StringUtils.isEmpty(id)) {
                        return R.fail(leaderNames[i] + "为非社区团队人员或该人员电话未维护！");
                    }
                    leaderIds.add(id);
                }

                String[] personPhones = comSwRotaExcelVO.getPersonPhone().split("、");
                String[] personNames = comSwRotaExcelVO.getPersonName().split("、");
                List<String> personIds = new ArrayList<>();
                for (int i = 0; i < leaderPhones.length; i++) {
                    String id = comSwRotaDAO.getTeamIdsByTeamPhone(personPhones[i]);
                    if (StringUtils.isEmpty(id)) {
                        return R.fail(personNames[i] + "为非社区团队人员或该人员电话未维护！");
                    }
                    personIds.add(id);
                }
                BeanUtils.copyProperties(comSwRotaExcelVO, comSwRotaDO);
                comSwRotaDO.setId(Snowflake.getId());
                comSwRotaDO.setCommunityId(communityId);
                if (StringUtils.isNotEmpty(comSwRotaExcelVO.getRotaDate())) {
                    String el = "(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|((0[48]|[2468][048]|[3579][26])00))-02-29)   \n" +
                            "(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|((0[48]|[2468][048]|[3579][26])00))-02-29) ";
                    Pattern p = Pattern.compile(el);
                    Matcher m = p.matcher(comSwRotaExcelVO.getRotaDate());
                    if (!m.matches()) {
                        return R.fail(comSwRotaExcelVO.getRotaDate() + "日期格式不正确");
                    }
                }
                try {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    comSwRotaDO.setRotaDate(sdf.parse(comSwRotaExcelVO.getRotaDate()));
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                comSwRotaDO.setRotaLeader(StringUtils.join(leaderIds, ","));
                comSwRotaDO.setRotaPerson(StringUtils.join(personIds, ","));
                comSwRotaDO.setLeaderName(comSwRotaExcelVO.getLeaderName().replace("、",","));
                comSwRotaDO.setPersonName(comSwRotaExcelVO.getPersonName().replace("、",","));
                comSwRotaDOS.add(comSwRotaDO);
        for (ComSwRotaExcelVO comSwRotaExcelVO : list) {
            Date date = DateUtil.parse(comSwRotaExcelVO.getRotaDate(), parsePatterns);
            Integer count =
                comSwRotaDAO.selectCount(new QueryWrapper<ComSwRotaDO>().lambda().eq(ComSwRotaDO::getRotaDate, date)
                        .eq(ComSwRotaDO::getCommunityId,communityId));
            if (count > 0) {
                return R.fail("值班日期已存在，" + comSwRotaExcelVO.getRotaDate() + "重复");
            }
            ComSwRotaDO comSwRotaDO = new ComSwRotaDO();
            String[] leaderPhones = comSwRotaExcelVO.getLeaderPhone().split("、");
            String[] leaderNames = comSwRotaExcelVO.getLeaderName().split("、");
            List<String> leaderIds = new ArrayList<>();
            for (int i = 0; i < leaderPhones.length; i++) {
                String id = comSwRotaDAO.getTeamIdsByTeamPhone(leaderPhones[i]);
                if (StringUtils.isEmpty(id)) {
                    return R.fail(leaderNames[i] + "为非社区团队人员或该人员电话未维护！");
                }
                leaderIds.add(id);
            }

            String[] personPhones = comSwRotaExcelVO.getPersonPhone().split("、");
            String[] personNames = comSwRotaExcelVO.getPersonName().split("、");
            List<String> personIds = new ArrayList<>();
            for (int i = 0; i < personPhones.length; i++) {
                String id = comSwRotaDAO.getTeamIdsByTeamPhone(personPhones[i]);
                if (StringUtils.isEmpty(id)) {
                    return R.fail(personNames[i] + "为非社区团队人员或该人员电话未维护！");
                }
                personIds.add(id);
            }
            BeanUtils.copyProperties(comSwRotaExcelVO, comSwRotaDO);
            comSwRotaDO.setId(Snowflake.getId());
            comSwRotaDO.setCommunityId(communityId);
            // if (StringUtils.isNotEmpty(comSwRotaExcelVO.getRotaDate())) {
            // String el =
            // "(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|((0[48]|[2468][048]|[3579][26])00))-02-29)
            // \n" +
            // "(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|((0[48]|[2468][048]|[3579][26])00))-02-29)
            // ";
            // Pattern p = Pattern.compile(el);
            // Matcher m = p.matcher(comSwRotaExcelVO.getRotaDate());
            // if (!m.matches()) {
            // return R.fail(comSwRotaExcelVO.getRotaDate() + "日期格式不正确");
            // }
            // }
            // try {
            // SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            // comSwRotaDO.setRotaDate(sdf.parse(comSwRotaExcelVO.getRotaDate()));
            // } catch (ParseException e) {
            // e.printStackTrace();
            // }
            comSwRotaDO.setRotaDate(date);
            comSwRotaDO.setRotaLeader(StringUtils.join(leaderIds, ","));
            comSwRotaDO.setRotaPerson(StringUtils.join(personIds, ","));
            comSwRotaDO.setLeaderName(comSwRotaExcelVO.getLeaderName().replace("、", ","));
            comSwRotaDO.setPersonName(comSwRotaExcelVO.getPersonName().replace("、", ","));
            comSwRotaDOS.add(comSwRotaDO);
        }
        this.saveBatch(comSwRotaDOS);
        return R.ok();
    }