WebRoot/WEB-INF/lib/kaptcha-2.3.jar

Binary files differ

 WebRoot/js/loginValidate.js

@@ -7,11 +7,14 @@
 var rsakey={
     pubkey:"MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAkRupiYcKVGGUtDBDoR1t/1zm3ZtZgnte39iTJW6hlqjdY0UagKjpNiIv7J6XjtgfX7SgsR4AWnivqQHAICIvdPKfGZZzIs62OQ19MqrDTMoB/LvK5teNWhClv23WMUfRbP+EHgprT6hTw8U5apw1IB6i/y57NkLav792wiYBYRU4X45NoTaT+aiTSLFEflbfm94EXnhSS3vFkBmrZGy5BRNI8gmzafroslGx2Hk90CqlNdeKYxgZQ6xtvj+u33yrszWvPT6F9fsJT8aMjtvH050iYKRVct+x6Q7VRJgCI4MgvAexnTKdxW54YzvXCuO5bDiy5la7CgerWkTAq9dzXwIDAQAB"
 };
 let telCoeKey;
(function($) {    
    $.Read=function(){        
        var _this=this;
        var select;
        var browserName = myexplorer();
        $(".get_phoneCode").addClass('is-disabled');
        $('.get_phoneCode').attr('disabled', true);
        if (browserName && browserName != "" && browserName != "谷歌") {
            $(".tips").show();  
            alert("您正在使用 " + browserName +" 浏览器，强烈推荐您使用【谷歌】浏览器登录系统!");
@@ -54,7 +57,19 @@
                     event.src="/imageCode?rand="+i; 
                 }                     
        }
		
        this.reloadTelCode=function (){
            $.get('/ws/captcha/get.do',function(r){
                if(r!=null){
                    if(r.state==0){
                        $("#telimage").attr("src",r.data.imag);
                        telCoeKey=r.data.key;
                    }else {
                        layer.alert(r.msg);
                    }

                }
            });
        }
        this.initValue=function(){
            if(select.selectedIndex!=-1){
                var text=select.options[select.selectedIndex].text;
@@ -67,7 +82,7 @@
             $("#sub").attr("disabled",true);
             $("#sub").css("background-color","#bec4c8");
         }
		
        _this.reloadTelCode();
        $("#sub").click(function(){
            _this.disableButton();
            var flg=false;
@@ -180,8 +195,31 @@

        $("#randomming").click(function(){
            _this.reloadCode(Math.random(),this);      
        });   	
    	
        });
        $("#telimage").click(function(){
            // 获取验证码
            _this.reloadTelCode();
        });

        $("#telCode").blur(function(){
            if($("#telCode").val()=='') return;
            // 获取验证码
            $.post('/ws/captcha/verification.do?key='+telCoeKey+'&verification='+$("#telCode").val(),function(r){
                if(r!=null){
                    if(r.state==0){
                        $(".get_phoneCode").removeClass('is-disabled');
                        $('.get_phoneCode').attr('disabled', false);
                    }else {
                        layer.alert(r.msg);
                        $("#telCode").val("");
                        $(".get_phoneCode").addClass('is-disabled');
                        $('.get_phoneCode').attr('disabled', true);
                        _this.reloadTelCode();
                    }

                }
            });
        });
        $(".pop-sns-weixin-m").on("touchstart",function () {
            $(".icon-weixin-m").addClass("weixin-hover");
        });
@@ -296,38 +334,46 @@
        var isCount = false; // 是否倒计时中
        // 获取验证码按钮点击事件
        $(".get_phoneCode").click(function () {
            if($("#telCode").val()==""){
                layer.alert("图片验证码不能为空");
                return;
            }
            var phone=$("#telephone").val();
            if(!(/^1[3456789]\d{9}$/.test(phone))){ 
                $(".telephone").find("span").html("请输入正确的手机号码");
                $("#telephone").addClass("errorinput");
                return; 
            } 
          if (isCount) {
            return;
          }
          $(".get_phoneCode").addClass('is-disabled');          
            }
          $(".get_phoneCode").addClass('is-disabled');
            $('.get_phoneCode').attr('disabled', true);
         // 获取验证码
          $.post('/ws/phoneCode.do',{'dbId':select.value,'telephone':phone,'type':1},function(r){
          $.post('/ws/phoneCode.do',{'dbId':select.value,'telephone':phone,'type':1,'key':telCoeKey,'captcha': $("#telCode").val()},function(r){
              if(r!=null){
                  if(r.code && r.code==-1){
                      layer.msg(r.info);
                      return;
                      layer.alert(r.info);
                      $("#telCode").val("");
                  }else {
                      layer.alert("验证码已发送到"+phone+"，请留意手机短信通知");

                  }
                  isCount = true;          
                  countDown();
                  function countDown() {
                    setTimeout(function () {
                      countTime--;
                      $(".get_phoneCode").text(countTime+'秒后再获取')
                      if (countTime>0){
                        countDown()
                      } else {
                        isCount = false;
                        countTime = 60;
                        $(".get_phoneCode").text('获取验证码').removeClass('is-disabled')
                      }
                    }, 1000)
                  }
                  isCount = true;
                  countDown();
                  function countDown() {
                      setTimeout(function () {
                          countTime--;
                          $(".get_phoneCode").text(countTime+'秒后再获取')
                          if (countTime>0){
                              countDown()
                          } else {
                              isCount = false;
                              countTime = 60;
                              $(".get_phoneCode").text('获取验证码').removeClass('is-disabled')
                              $('.get_phoneCode').attr('disabled', false);
                              $("#telCode").val("");
                              _this.reloadTelCode();
                          }
                      }, 1000)
                  }
              }
          });
        });

 WebRoot/login.jsp

@@ -309,6 +309,15 @@
                            </label>
                            <div class="error"><span class="text"></span></div>
                        </div>
                        <div class="code">
                            <label>
                                <input type="text" id="telCode" name="telCode" placeholder="图片验证码"
                                       class="txt_input txt_input_yzm" maxlength="10"/>
                                <i class="yzm_i"></i>
                            </label>
                            <img id="telimage" src="" title="点击更换验证码" alt="点击更换验证码"/>
                            <div class="error"><span class="text"></span></div>
                        </div>
                        <div class="phoneCode">
                            <label>
                                <input type="text" id="phoneCode" name="phoneCode" placeholder="验证码"

 WebRoot/public/template/0/gridDef.xml

@@ -11,7 +11,7 @@
   <Cfg SaveSession='1'/> 
   <Cfg ShowDeleted='1'/>
   <Cfg MinTagHeight='@minHeight' ConstHeight='1' ConstWidth='1' CacheTimeout='10' Cache='3'  FilterLap='1' ExactSize='0'/>
   <Cfg DateStrings='2'  Alternate='0' AlternateCount='0' AlternateStart='100000'/>
   <Cfg DateStrings='2'  Alternate='0' AlternateCount='0' AlternateStart='100000' AcceptEnters='3'/>
   <Cfg IdChars='0123456789' NumberId='1' IdPrefix='__YC'/>
   <Cfg Sorting='@Sorting'/>
   <Cfg Sorted='1' @Sort />

 src/com/yc/action/grid/GTGrid.java

@@ -5711,13 +5711,17 @@
                //复单不需要分页 by danaus 2023-08-17 09:46
                page.setPageSize(MAX_RECORD);
            }
            if (-1 == page.getFlag() && (type.equalsIgnoreCase("2") || type.equalsIgnoreCase("17")|| type.equalsIgnoreCase("497")))//2,16类型的导出执行这个
            if (-1 == page.getFlag() && (type.equalsIgnoreCase("2") ||type.equalsIgnoreCase("20") || type.equalsIgnoreCase("17")|| type.equalsIgnoreCase("497")))//2,16类型的导出执行这个
            {
                if (page.getWhere() != null && page.getWhere().trim().indexOf("and") == 0)
                    page.setWhere(" 1=1 " + page.getWhere());
                try {
                    SpObserver.setDBtoInstance("_" + dbid);
                    String fields = gridService.getSimpleJdbcTemplate().queryForObject("select fields = stuff((select ','+cast(fieldid as varchar) from gField where formid=? and isnull(headflag,0)=0 and isnull(isExport,0)=1 for xml path('')),1,1,'') ", String.class, page.getFormid());
                    int headflag=0;
                    if(type.equalsIgnoreCase("20")){
                        headflag=1;
                    }
                    String fields = gridService.getSimpleJdbcTemplate().queryForObject("select fields = stuff((select ','+cast(fieldid as varchar) from gField where formid=? and isnull(headflag,0)="+headflag+" and isnull(isExport,0)=1 for xml path('')),1,1,'') ", String.class, page.getFormid());
                    if (org.apache.commons.lang3.StringUtils.isBlank(fields)) {
                        throw new ApplicationException(page.getFormid() + "-没有需要导出的字段，请在9802设置勾选需要导出的字段");
                    }

 src/com/yc/action/grid/PoiExcelWriter.java

@@ -523,7 +523,7 @@
                baseService.getSimpleJdbcTemplate().query(" declare @table table(unid varchar(150),count int,FileType varchar(10),pic image)\n declare  @maxCount int="+((count-2)*2)+
                                " insert into @table select cast(unid as varchar(100))+';'+cast(seq as varchar(10)) as unid,@maxCount as count" + fileds + " from _sys_Attachment9\n" +
                                " where unid in(" + joiner.toString() + ")\n" +
                                " if @@ROWCOUNT=0 \n" +
                                //" if @@ROWCOUNT=0 \n" +//去掉限制，因为同时存在19，9的情况
                                " insert into @table select cast(unid as varchar(100))+';'+cast(seq as varchar(10)) as unid,@maxCount as count" + fileds + " from _sys_Attachment\n" +
                                " where unid in(" + joiner.toString() + ")\n" +
                                " select * from @table",

 src/com/yc/action/grid/TreeGrid.java

@@ -1636,7 +1636,7 @@

            }
            //while(dto.gfields.next());
            if ("".equalsIgnoreCase(dto.orderFiled)) dto.orderFiled = firstID + " desc";
            if ("".equalsIgnoreCase(dto.primeKey)) dto.orderFiled = firstID + " desc";
            dto.expr = valExp.length() > 0 ? valExp.substring(0, valExp.length() - 1) : "";
            dto.cspr = cssExp.length() > 0 ? cssExp.substring(0, cssExp.length() - 1) : "";
            dto.tipspr = tipsExp.length() > 0 ? tipsExp.substring(0, tipsExp.length() - 1) : "";

 src/com/yc/action/upload/AttachmentAction.java

@@ -1339,6 +1339,7 @@
        }
        String doccode = request.getParameter("doccode");
        String fieldid = (String) request.getParameter("fieldid");
        if(fieldid==null) fieldid="";//避免app上传头像时报错
        String rowid = (String) request.getParameter("rowid");
        String uuid = (String) request.getParameter("uuid");

@@ -1492,7 +1493,8 @@
            str_formid = postBeanInfo.getFormid() + "";
        }
        String doccode = request.getParameter("doccode");
        String fieldid = request.getParameter("fieldid");
        String fieldid =request.getParameter("fieldid");
        if(fieldid==null) fieldid="";//避免app上传头像时报错
        String rowid = request.getParameter("rowid");
        String uuid = request.getParameter("uuid");
        String matCode=request.getParameter("matCode");//个别数据没有doccode值需要到这个属性 xin 2023-2-10 17:29:11
@@ -1659,10 +1661,9 @@
                    //其他情况不更新
                } else {
                    //从表更新需要有rowid, 针对5类型,8类型从表
                    if (StringUtils.isBlank(postBeanInfo.getRowid())) {
                        throw new ApplicationException(postBeanInfo.getFormid() + "更新单号【" + postBeanInfo.getDoccode() + "】附件信息，rowid不能为空");
                    if (StringUtils.isNotBlank(postBeanInfo.getRowid())) {
                        baseService.getSimpleJdbcTemplate().update(" update a set a." + postBeanInfo.getFieldid() + "=? from " + tableName + " a where a.doccode=? and a.rowid=?", seqString, postBeanInfo.getDoccode(), postBeanInfo.getRowid());
                    }
                    baseService.getSimpleJdbcTemplate().update(" update a set a." + postBeanInfo.getFieldid() + "=? from " + tableName + " a where a.doccode=? and a.rowid=?", seqString, postBeanInfo.getDoccode(), postBeanInfo.getRowid());
                }
            }
            Map<String, String> m = new HashMap<String, String>();

 src/com/yc/api/bean/CartEntity.java

@@ -38,4 +38,5 @@
    private String skuName9;
    private Integer skuId10;
    private String skuName10;
    private String cartIdList;
}

 src/com/yc/api/controller/ApiController.java

@@ -1595,7 +1595,26 @@
        }
        return rightJson;
    }

    /**
     * 检查是否已注册APP
     * @param telephone
     * @return
     * @throws Exception
     */
    public static boolean checkUserRegisteredByAPP(String telephone)throws Exception {
        try {
            SpObserver.setDBtoDemo();
            BaseService baseService = (BaseService) FactoryBean.getBean("BaseService");
            Integer result = baseService.getJdbcTemplate().queryForObject("declare @num int\n" +
                    "select @num=count(1) from gProfile where  Telephone='" + telephone + "'\n" +
                    "select @num", Integer.class);
            return result != null && result > 0;
        }catch (Exception ex){
            throw new ApplicationException(ex.getMessage());
        }finally {
            SpObserver.setDBtoInstance();
        }
    }
    /**
     * 发手机验证码
     */
@@ -1604,11 +1623,71 @@
        //发送验证码到对应手机号
        //TODO 暂定用标准版的短信账号来发送，以后再转第三方做验证调用时就可以用第三方的短信账号
        request.setAttribute(SessionKey.SHOPPING_DBID, "82");
        //已注册app才能发送
        JsonObject json = new JsonObject();
        JsonObject errJson = new JsonObject();
        try {
            if (checkImageVerifyCode(request, response, errJson, json)) return;
            if (!checkUserRegisteredByAPP(tel)) {
                errJson.addProperty("warning", "您的手机号[" + tel + "]还未注册，不能发送请求!");
                json.add("error", errJson);
                this.printJson(response, json.toString());
                return;
            }
        }catch (Exception ex){
            errJson.addProperty("warning", ex.getMessage());
            json.add("error", errJson);
            this.printJson(response, json.toString());
            return;
        }
        VerificationCodes verificationCodes = (VerificationCodes) FactoryBean.getBean("verificationCodes");
        verificationCodes.GenRandomVcode(tel, request, response);
    }

    /**
     * 检查图片验证码是否正确
     * @param request
     * @param response
     * @param errJson
     * @param json
     * @return
     */
    private boolean checkImageVerifyCode(HttpServletRequest request, HttpServletResponse response, JsonObject errJson, JsonObject json) {
        String key= request.getParameter("key");
        String verifyCode= request.getParameter("verifyCode");
        if(StringUtils.isNotBlank(key)) {//TODO 为了兼容，如果没传key则暂不处理，等版本更新后再重新放出来
            if (StringUtils.isBlank(key)) {
                errJson.addProperty("warning", "图片验证码key不能为空");
                json.add("error", errJson);
                this.printJson(response, json.toString());
                return true;
            }
            if (StringUtils.isBlank(verifyCode)) {
                errJson.addProperty("warning", "图片验证码不能为空");
                json.add("error", errJson);
                this.printJson(response, json.toString());
                return true;
            }
            Object o = redisTemplate.opsForValue().get(key);
            if (o == null) {
                errJson.addProperty("warning", "图片验证码已失效，请重新刷新");
                json.add("error", errJson);
                this.printJson(response, json.toString());
                return true;
            } else {
                String txt = o.toString();
                if (!verifyCode.equalsIgnoreCase(txt)) {
                    errJson.addProperty("warning", "图片验证码不正确");
                    json.add("error", errJson);
                    this.printJson(response, json.toString());
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 取功能号对应的主键，多个是以;号分隔
     */
    @RequestMapping(value = "/forminfo.do", method = RequestMethod.POST)

 src/com/yc/api/controller/QrCodeController.java

@@ -140,12 +140,6 @@
            SpObserver.setDBtoInstance("_" +dbid );
            String hostUrl = SettingKey.getHostUrlForImage(request);
            final List<com.yc.api.bean.CartEntity> cartList = qrServiceIfc.getCartList(request.getSession().getAttribute(SessionKey.USERCODE) + "", docCode);
//            cartList.stream().map(x -> {
//                x.setPhotoPath(imgData.getImageUrl(x.getPhotoPath(), 120,
//                        120, false,
//                        false, request));
//                return x;
//            }).collect(Collectors.toList());
            cartList.stream().forEach(x -> {
                x.setPhotoPath(SettingKey.getUrl(hostUrl, x.getPhotoPathUrl(), dbid+"",null));
                x.setPhotoPathUrl(SettingKey.getUrl(hostUrl, x.getPhotoPathUrl(), dbid+"",null));
@@ -160,31 +154,31 @@
                BeanUtils.copyProperties(cartEntity,cart);
                double totalAmount=StringUtils.isNotBlank(cart.getAmount())?Double.parseDouble(cart.getAmount()):0;//合并总价
                double totalQuantity=cart.getQuantity();//合并数量
                StringJoiner cartIdjoiner=new StringJoiner(";");
                iterator.remove();
                if(!matCode.equalsIgnoreCase(cart.getMatCode())) {
                    matCode=cart.getMatCode();
                    cartIdjoiner.add(cart.getCartId()+"");
                    Iterator<CartEntity> iterator2 = cartList.iterator();
                    while (iterator2.hasNext()) {
                        CartEntity cartEntity2 = iterator2.next();
                        if (cart.getMatCode().equalsIgnoreCase(cartEntity2.getMatCode())) {
                            totalAmount += Double.parseDouble(cartEntity2.getAmount());
                            totalQuantity += cartEntity2.getQuantity();
                            cartIdjoiner.add(cartEntity2.getCartId()+"");
                        }
                    }
                    cart.setCartIdList(cartIdjoiner.toString());
                    cart.setAmount(totalAmount + "");
                    cart.setQuantity(totalQuantity);
                    newCartList.add(cart);
                }
            }
            //List<Gfrom> refFormIdList = getRefFormIdList(request, refFormid);
            Map map = new HashMap();
            map.put("carts", newCartList);
            //map.put("refFormids", refFormIdList);

            callBackMessage.setInfo(map);
            callBackMessage.sendSuccessMessage("成功");
            return callBackMessage.toJSONObject();

        } catch (Exception ex) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(ex));
            return callBackMessage.toJSONObject();
@@ -375,6 +369,9 @@
                        cartids = postCartEntity.getCartids();
                    }
                    map = qrServiceIfc.savePost(postCartEntity, cartids, userCode, cartSql);
                    if (map == null || StringUtils.isBlank(GridUtils.prossRowSetDataType_String(map, "docCode"))) {
                        throw new ApplicationException(postCartEntity.getFormId() + "生成单据出错:单号为空");
                    }
                    if (map != null) {
                        postCartEntity.setCartids(map.get("cartIds") + "");
                        final Integer result = qrServiceIfc.delCart(postCartEntity);
@@ -385,6 +382,7 @@
                if (map == null || StringUtils.isBlank(GridUtils.prossRowSetDataType_String(map, "docCode"))) {
                    throw new ApplicationException(postCartEntity.getFormId() + "生成单据出错:单号为空");
                }

                log.info("cltCode:" + userCode + "-客户：" + userCode + "下单花费:" + (System.currentTimeMillis() - startTime));
                callBackMessage.setInfo(map);
                callBackMessage.sendSuccessMessageByDefault();

 src/com/yc/api/schedule/AttendanceSchedule.java

@@ -33,7 +33,7 @@
 * spring加载完成后启动
 *
 * 定时更新前一天的打卡考勤记录，每天凌晨4点后执行
 *
 *已废弃，用api接口调用
 */
        @Service
        public class AttendanceSchedule implements ApplicationListener<ContextRefreshedEvent> {
@@ -125,7 +125,7 @@
                        }
                        if(seqList.size()>0){
                            //更新可发送次数
                            attendanceServiceIfc.updateT180252(seqList);
                            attendanceServiceIfc.updateT180252(seqList,null);
                        }
                    }catch (RuntimeException r){
                        throw r;
@@ -202,7 +202,7 @@
            }
                if(seqList.size()>0){
                    //更新可发送次数
                    attendanceServiceIfc.updateT180252(seqList);
                    attendanceServiceIfc.updateT180252(seqList,null);
                }
            }catch (RuntimeException r){
             throw r;

 src/com/yc/api/service/AttendanceService.java

@@ -314,31 +314,36 @@

    @Override
    public List<T180252> getT180252List(String workTime,int perMinutes) {
        //避免触发时当前时间已过了设置的打卡提醒时间，所以需要用between and 且增加10分钟作为宽限时间
        //上班打卡提醒触发时间期间：在AttentionTimeForCheck 和CheckInDateTime之间
        //下班打卡提醒触发时间期间：因为AttentionTimeForCheck 和CheckInDateTime是相同时间，所以CheckInDateTime+10分钟，作为触发时间期间
        log.info("workTime:" + workTime);
        if(StringUtils.isNotBlank(workTime)) {
            LocalTime parse = LocalTime.parse(workTime, DateTimeFormatter.ofPattern("HH:mm"));
            parse = parse.minusMinutes(perMinutes);//减去指定分钟，因为上班卡可以提前提醒
            LocalDate localDate = LocalDate.now();
            String dataTime = localDate.atTime(parse.getHour(), parse.getMinute()).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
            //System.out.println("dataTime:" + dataTime);
            log.info("dataTime:" + dataTime);
            String sql = "set nocount on \n select Seq,RulesDocCode,RulesName,UserCode,UserName,CheckInDateTime,Title,Description,CreateDate,LastSendTime,RemainTimes,TryTimes,ErrorCode,ErrorMsg,AttentionTimeForCheck,AttentionTypeForCheck,telephone from t180252 " +
                    "  where '" + dataTime + "' between AttentionTimeForCheck and  DATEADD(mi,10,CheckInDateTime) \n" +
                    "  and isnull(RemainTimes,0)>0 ";
            // log.info("执行打卡提醒sql:" + sql);
             log.info("执行打卡提醒sql-1:" + sql);
            //System.out.println("执行打卡提醒sql:" + sql);
            return this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T180252.class));
        }else{
            String sql = "set nocount on \n  select Seq,RulesDocCode,RulesName,UserCode,UserName,CheckInDateTime,Title,Description,CreateDate,LastSendTime,RemainTimes,TryTimes,ErrorCode,ErrorMsg,AttentionTimeForCheck,AttentionTypeForCheck,telephone from t180252 " +
                    "  where getdate() between AttentionTimeForCheck and  DATEADD(mi,10,CheckInDateTime) \n" +
                    "  and isnull(RemainTimes,0)>0 ";
            log.info("执行打卡提醒sql-2:" + sql);
            return this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T180252.class));
        }
    }

    @Override

    public Integer updateT180252(List<String> seqList) {
        return this.jdbcTemplate.queryForObject("set nocount on \n update a set a.LastSendTime=getdate(),a.RemainTimes=isnull(a.RemainTimes,1)-1,a.TryTimes=isnull(a.TryTimes,0)+1 from t180252 a where a.seq in (" + String.join(",", seqList) + ") \n select @@rowcount;", Integer.class);
    public Integer updateT180252(List<String> seqList,String dbid) {
        String sql="set nocount on \n update a set a.LastSendTime=getdate(),a.RemainTimes=isnull(a.RemainTimes,1)-1,a.TryTimes=isnull(a.TryTimes,0)+1 from t180252 a where a.seq in (" + String.join(",", seqList) + ") \n select @@rowcount;";
        log.info("updateT180252:"+sql+"["+dbid+"]");
        return this.jdbcTemplate.queryForObject(sql, Integer.class);
    }
}

 src/com/yc/api/service/AttendanceServiceIfc.java

@@ -111,5 +111,5 @@
    List<T180232> get180232List(String docCode);

    List<T180252> getT180252List(String dateTime,int perMinutes);
    Integer updateT180252(List<String> seqList);
    Integer updateT180252(List<String> seqList,String dbid);
}

 src/com/yc/captcha/CaptchaController.java

New file
@@ -0,0 +1,104 @@
package com.yc.captcha;

import com.google.code.kaptcha.Producer;
import com.yc.exception.ApplicationException;
import com.yc.exception.CallBackMessage;
import org.apache.commons.lang3.RandomStringUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.util.Base64;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.TimeUnit;

@RequestMapping("/ws/captcha")
@Controller
public class CaptchaController {
    @Autowired
    private Producer defaultProducer;
    @Qualifier("redisTemplate")
    @Autowired
    private RedisTemplate redisTemplate;

    /**
     * 生成图片验证码
     * @param request
     * @param response
     * @throws Exception
     */
    @GetMapping("/get.do")
    public @ResponseBody Object getCaptcha(HttpServletRequest request, HttpServletResponse response) throws Exception {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
           // 生成验证码文本
           String captchaText = defaultProducer.createText();
           String key= RandomStringUtils.randomAlphanumeric(18);
           // 生成验证码图片
           BufferedImage captchaImage = defaultProducer.createImage(captchaText);
           String base64 = convertImageToBase64(captchaImage);
            Map<String,String> map=new HashMap<String,String>();
            map.put("key", key);
            map.put("imag", "data:image/png;base64,"+base64);
           callBackMessage.setInfo(map);
           callBackMessage.setSuccess("成功");
           redisTemplate.opsForValue().set(key,captchaText,5, TimeUnit.MINUTES);
           return callBackMessage.toJSONObject();
       }catch (Exception e){
        callBackMessage.sendErrorMessage(e.getMessage());
        return callBackMessage.toJSONObject();
       }

    }
    public static String convertImageToBase64(BufferedImage image) throws Exception {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        ImageIO.write(image, "png", baos); // 可以改为需要的图片格式，如"jpg"
        byte[] imageBytes = baos.toByteArray();
        return Base64.getEncoder().encodeToString(imageBytes);
    }
    /**
     * 验证图片验证码对不对
     * @param verification
     * @param session
     * @return
     */
    @RequestMapping("/verification.do")
    @ResponseBody
    public Object verification(String key,String verification){
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            if(StringUtils.isBlank(key)){
                throw new ApplicationException("key不能为空");
            }
            if(StringUtils.isBlank(verification)){
                throw new ApplicationException("验证码不能为空");
            }
            Object o = redisTemplate.opsForValue().get(key);
            if(o==null){
                throw new ApplicationException("图片验证码已失效，请重新刷新");
            }else {
                String txt=o.toString();
                if(verification.equalsIgnoreCase(txt)){
                    callBackMessage.sendSuccessMessageByDefault();
                    return callBackMessage.toJSONObject();
                }else {
                    throw new ApplicationException("图片验证码不正确");
                }
            }
        }catch (Exception e){
            callBackMessage.sendErrorMessage(e.getMessage());
            return callBackMessage.toJSONObject();
        }
    }
}

 src/com/yc/crm/base/action/CrmController.java

@@ -1,7 +1,7 @@
package com.yc.crm.base.action;

import com.yc.action.BaseAction;
import com.yc.crm.base.entity.Page;
import com.yc.crm.base.entity.*;
import com.yc.crm.base.service.CrmServiceIfc;
import com.yc.exception.CallBackMessage;
import com.yc.multiData.SpObserver;
@@ -14,11 +14,15 @@
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.List;

@RequestMapping("/crm/base")
@RestController
public class CrmController extends BaseAction {
    @Autowired
    CrmServiceIfc crmServiceIfc;

    /**
     * 输出下拉相关数据
     * ，-48013000：公司类型
@@ -31,42 +35,383 @@
     * 7，-48013007：社交平台
     * 8，-48013008：电话区号
     * 9，-48013009：职级
     *
     * @param request
     * @return
     */
    @RequestMapping("/selectInfo.do")
    public @ResponseBody Object selectInfo(HttpServletRequest request){
    public @ResponseBody Object selectInfo(HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            callBackMessage.setInfo(crmServiceIfc.getBaseInfo(request.getSession().getAttribute(SessionKey.USERCODE)+""));
            callBackMessage.setInfo(crmServiceIfc.getBaseInfo(request.getSession().getAttribute(SessionKey.USERCODE) + ""));
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        }catch (Exception e){
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        }finally {
        } finally {
            SpObserver.setDBtoInstance();
        }
    }

    /**
     * 获取用户，以部门分组树形结构显示
     *
     * @param request
     * @return
     */
    @RequestMapping("/userInfo.do")
    public @ResponseBody Object userInfo(@RequestBody @Nullable Page page, HttpServletRequest request){
    public @ResponseBody Object userInfo(@RequestBody @Nullable Page page, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            callBackMessage.setInfo(crmServiceIfc.getUserInfo(request.getSession(),page));
            callBackMessage.setInfo(crmServiceIfc.getUserInfo(request.getSession(), page));
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        }catch (Exception e){
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        }finally {
        } finally {
            SpObserver.setDBtoInstance();
        }
    }

    /**
     * 保存跟进信息
     *
     * @param request
     * @return
     */
    @RequestMapping("/followUp/save.do")
    public @ResponseBody Object followUpSave(@RequestBody T482105Entity entity, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            entity.setCompanyId(request.getSession().getAttribute(SessionKey.COMPANY_ID) + "");
            entity.setCompanyName(request.getSession().getAttribute(SessionKey.COMPANY_NAME) + "");
            entity.setUserCode(request.getSession().getAttribute(SessionKey.USERCODE) + "");
            entity.setUserName(request.getSession().getAttribute(SessionKey.USERNAME) + "");
            crmServiceIfc.followUpSave(entity);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        } finally {
            SpObserver.setDBtoInstance();
        }
    }

    /**
     * 跟进列表
     *
     * @param request
     * @return
     */
    @RequestMapping("/followUp/list.do")
    public @ResponseBody Object followUpList(@RequestBody FollowUpRequestEntity entity, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            entity.setDbid(request.getSession().getAttribute(SessionKey.DATA_BASE_ID) + "");
            entity.setFormid("482105");
            Response response = crmServiceIfc.followUpList(entity);
            callBackMessage.setInfo(response);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        } finally {
            SpObserver.setDBtoInstance();
        }
    }

    /**
     * 删除跟进
     *
     * @param request
     * @return
     */
    @RequestMapping("/followUp/del.do")
    public @ResponseBody Object followUpDel(Integer id, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            crmServiceIfc.followUpDel(id);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        } finally {
            SpObserver.setDBtoInstance();
        }
    }

    /**
     * 审计内容列表
     *
     * @param request
     * @return
     */
    @RequestMapping("/audit/list.do")
    public @ResponseBody Object personalList(@RequestBody AuditRequestEntity entity, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            String userCode = request.getSession().getAttribute(SessionKey.USERCODE) + "";
            Response response = crmServiceIfc.auditList(userCode, entity);
            callBackMessage.setInfo(response);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        } finally {
            SpObserver.setDBtoInstance();
        }
    }

    /**
     * 个人分组列表
     *
     * @param request
     * @return
     */
    @RequestMapping("/personal/list.do")
    public @ResponseBody Object personalList(@RequestBody T482117Entity entity, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            String userCode = request.getSession().getAttribute(SessionKey.USERCODE) + "";
            List<T482117Entity> list = crmServiceIfc.personalList(userCode, entity.getGroupid());
            callBackMessage.setInfo(list);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        } finally {
            SpObserver.setDBtoInstance();
        }
    }

    /**
     * 保存个人分组
     *
     * @param request
     * @return
     */
    @RequestMapping("/personal/save.do")
    public @ResponseBody Object personalSave(@RequestBody @Nullable List<T482117Entity> list, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            if (list == null) {
                list = new ArrayList<T482117Entity>();
            }
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            String userCode = request.getSession().getAttribute(SessionKey.USERCODE) + "";
            list.stream().forEach(x -> {
                x.setCompanyId(request.getSession().getAttribute(SessionKey.COMPANY_ID) + "");
                x.setCompanyName(request.getSession().getAttribute(SessionKey.COMPANY_NAME) + "");
                x.setUserCode(request.getSession().getAttribute(SessionKey.USERCODE) + "");
                x.setUserName(request.getSession().getAttribute(SessionKey.USERNAME) + "");
            });
            crmServiceIfc.personalSave(list, userCode);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        } finally {
            SpObserver.setDBtoInstance();
        }
    }
    /**
     * 日程列表
     *
     * @param request
     * @return
     */
    @RequestMapping("/schedule/list.do")
    public @ResponseBody Object scheduleList(@RequestBody ScheduleRequestEntity entity, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            String userCode = request.getSession().getAttribute(SessionKey.USERCODE) + "";
            entity.setDbid(request.getSession().getAttribute(SessionKey.DATA_BASE_ID)+"");
            entity.setFormId("482112");
            Response response = crmServiceIfc.scheduleList(entity);
            callBackMessage.setInfo(response);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        } finally {
            SpObserver.setDBtoInstance();
        }
    }

    /**
     * 保存日程
     *
     * @param request
     * @return
     */
    @RequestMapping("/schedule/save.do")
    public @ResponseBody Object scheduleSave(@RequestBody T482112Entity entity, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            //  String userCode = request.getSession().getAttribute(SessionKey.USERCODE) + "";
            entity.setCompanyId(request.getSession().getAttribute(SessionKey.COMPANY_ID) + "");
            entity.setCompanyName(request.getSession().getAttribute(SessionKey.COMPANY_NAME) + "");
            entity.setUserCode(request.getSession().getAttribute(SessionKey.USERCODE) + "");
            entity.setUserName(request.getSession().getAttribute(SessionKey.USERNAME) + "");
            crmServiceIfc.scheduleSave(entity);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        } finally {
            SpObserver.setDBtoInstance();
        }
    }
    /**
     * 删除日程
     *
     * @param request
     * @return
     */
    @RequestMapping("/schedule/del.do")
    public @ResponseBody Object scheduleDel(Integer id, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            crmServiceIfc.scheduleDel(id);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        } finally {
            SpObserver.setDBtoInstance();
        }
    }
    /**
     * 关注日程
     *
     * @param request
     * @return
     */
    @RequestMapping("/schedule/attention.do")
    public @ResponseBody Object scheduleAttention(@RequestBody  T482112Entity entity, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            crmServiceIfc.scheduleAttention(entity);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        } finally {
            SpObserver.setDBtoInstance();
        }
    }
    /**
     * 关注日程
     *
     * @param request
     * @return
     */
    @RequestMapping("/schedule/completed.do")
    public @ResponseBody Object scheduleCompleted(Integer id, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            crmServiceIfc.scheduleCompleted(id);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        } finally {
            SpObserver.setDBtoInstance();
        }
    }
    /**
     * 评论列表
     *
     * @param request
     * @return
     */
    @RequestMapping("/comment/list.do")
    public @ResponseBody Object commentList(@RequestBody CommentRequestEntity entity, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            String userCode = request.getSession().getAttribute(SessionKey.USERCODE) + "";
            entity.setDbid(request.getSession().getAttribute(SessionKey.DATA_BASE_ID)+"");
            entity.setUserCode(userCode);
            entity.setFormId("482104");
            Response response = crmServiceIfc.commentList(entity);
            callBackMessage.setInfo(response);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        } finally {
            SpObserver.setDBtoInstance();
        }
    }
    /**
     * 删除评论
     *
     * @param request
     * @return
     */
    @RequestMapping("/comment/del.do")
    public @ResponseBody Object commentDel(Integer id, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            crmServiceIfc.commentDel(id);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        } finally {
            SpObserver.setDBtoInstance();
        }
    }
    /**
     * 保存评论
     *
     * @param request
     * @return
     */
    @RequestMapping("/comment/save.do")
    public @ResponseBody Object commentSave(@RequestBody T482104Entity entity, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            //  String userCode = request.getSession().getAttribute(SessionKey.USERCODE) + "";
            entity.setUserCode(request.getSession().getAttribute(SessionKey.USERCODE) + "");
            entity.setUserName(request.getSession().getAttribute(SessionKey.USERNAME) + "");
            crmServiceIfc.commentSave(entity);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        } finally {
            SpObserver.setDBtoInstance();
        }
    }

 src/com/yc/crm/base/entity/AuditEntity.java

New file
@@ -0,0 +1,36 @@
package com.yc.crm.base.entity;

import lombok.Data;

import java.util.Objects;

/**
 * 审计实体
 */
@Data
public class AuditEntity {
    private  String oldValue;//旧值
    private  String newValue;//新值
    private  String fieldName;//字段名称
    private  String fieldId;//字段id
    private  String auditType;
    private  String auditDateTime;
    private  String userCode;
    private  String userName;
    private  Integer totalRowCount;
    private  Integer pageCount;

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        AuditEntity audit = (AuditEntity) o;
        return Objects.equals(auditDateTime, audit.auditDateTime);
    }

    @Override
    public int hashCode() {
        return Objects.hashCode(auditDateTime);
    }

}

 src/com/yc/crm/base/entity/AuditRequestEntity.java

New file
@@ -0,0 +1,8 @@
package com.yc.crm.base.entity;

import lombok.Data;

@Data
public class AuditRequestEntity extends Page{
    private String formid,docCode;
}

 src/com/yc/crm/base/entity/CommentRequestEntity.java

New file
@@ -0,0 +1,8 @@
package com.yc.crm.base.entity;

import lombok.Data;

@Data
public class CommentRequestEntity extends Page{
   private String refId,dbid,formId,userCode;
}

 src/com/yc/crm/base/entity/FileEntity.java

New file
@@ -0,0 +1,15 @@
package com.yc.crm.base.entity;

import lombok.Data;

/**
 * 图片，附件实体
 */
@Data
public class FileEntity {
    private String fileName;
    private String downloadUrl;//下载或打开url
    private String thumbnailFileUrl;//缩略图url
    private String fileType;
    private String fileSize;
}

 src/com/yc/crm/base/entity/FileStructEntity.java

New file
@@ -0,0 +1,9 @@
package com.yc.crm.base.entity;

import lombok.Data;

@Data
public class FileStructEntity {
    private String fileStr, dbid, domain,formid;
    private Integer isShowOrgPic=0;
}

 src/com/yc/crm/base/entity/FollowUpRequestEntity.java

New file
@@ -0,0 +1,8 @@
package com.yc.crm.base.entity;

import lombok.Data;

@Data
public class FollowUpRequestEntity extends Page{
    private String followUpType,formid,refCode,dbid;
}

 src/com/yc/crm/base/entity/Response.java

@@ -2,8 +2,6 @@

import lombok.Data;

import java.util.List;

/**
 * 分页实体
 */
@@ -11,5 +9,5 @@
public class Response{
   private  Integer pageCount;//总页数
   private  Integer totalCount;//总条数
   private List data;
   private Object data;
}

 src/com/yc/crm/base/entity/ScheduleRequestEntity.java

New file
@@ -0,0 +1,9 @@
package com.yc.crm.base.entity;

import lombok.Data;

@Data
public class ScheduleRequestEntity extends Page{
   private String refCode,dbid,formId;//取docCode，因为这些内容作为线索的公共资源。转给其他人时候可以看得到这些历史信息
    private Integer completeFlag;//是否完成
}

 src/com/yc/crm/base/entity/T110202Entity.java

New file
@@ -0,0 +1,15 @@
package com.yc.crm.base.entity;

import lombok.Data;

/**
 * 省市地区
 */
@Data
public class T110202Entity extends TreeEntiry {

  private String areaid;
  private String areaname;
  private String memo;
  private Integer docVersion;
}

 src/com/yc/crm/base/entity/T480116Entity.java

New file
@@ -0,0 +1,15 @@
package com.yc.crm.base.entity;

import lombok.Data;

/**
 * 主营产品
 */
@Data
public class T480116Entity extends TreeEntiry {

  private String categoryId;
  private String categoryName;
  private String memo;
  private Integer docVersion;
}

 src/com/yc/crm/base/entity/T482104Entity.java

New file
@@ -0,0 +1,22 @@
package com.yc.crm.base.entity;

import lombok.Data;

import java.util.List;

@Data
public class T482104Entity {
    private String userCode;
    private String userName;
    private Integer refId;
    private Integer id;
    private String comment;
    private String attachList;
    private String createTime;
    private String senders;//通知人员
    private String updateTime;
    private Integer totalRowCount;
    private Integer pageCount;
    private Integer enableFlag;//是否可编辑和删除
    private List<FileEntity> files;
}

 src/com/yc/crm/base/entity/T482105Entity.java

New file
@@ -0,0 +1,38 @@
package com.yc.crm.base.entity;

import lombok.Data;

import java.util.List;

/**
 * 跟进
 */
@Data
public class T482105Entity {
    private String companyId;
    private String companyName;
    private String userCode;
    private String userName;
    private Integer id;
    private String refCode;
    private Integer enableFlag;
    private Integer deleteFlag;
    private Integer inquiryId;
    private Integer commentCount;
    private String content;
    private String plainContent;
    private String latitude;
    private String longitude;
    private String address;
    private String attachList;
    private String followUpType;
    private String followUpNextTime;
    private String createTime;
    private String updateTime;
    private String followUser;
    private List<FileEntity> files;
    private String formid;//关联的功能号(线索，客户，商机)
    private String dbid;
    private  Integer totalRowCount;
    private  Integer pageCount;
}

 src/com/yc/crm/base/entity/T482112Entity.java

New file
@@ -0,0 +1,44 @@
package com.yc.crm.base.entity;

import lombok.Data;

import java.util.List;

/**
 * 日程实体
 */
@Data
public class T482112Entity {
    private String companyId;
    private String companyName;
    private String userCode;
    private String userName;
    private Integer id;
    private String refType;
    private String refCode;
    private String title;
    private String color;
    private String participantId;//跟进人
    private String startTime;
    private String endTime;
    private String remark;
    private String attachList;
    private String imageList;
    private Integer fullDayFlag;
    private Integer repeatFlag=0;//当前日程有设置重复，则在修改时需要弹出选项，是只修改当前日程还是按新的日程重新生成新的重复日程，而旧的也保留
    private String createTime;
    private String updateTime;
    private List<T482113Entity> remindTimes;//提醒时间设置
    private  List<FileEntity> files;
    private String unit;
    private String type;//周期性类型不设置:not，每天：everyday，每周：everyweek，每月：everymonth
    private String repeatStart;
    private String repeatEnd;//周期性结束时间
    private Integer num;
    private Integer pinFlag;//关注
    private Integer totalRowCount;
    private Integer pageCount;
    private Integer completeFlag;//是否完成


}

 src/com/yc/crm/base/entity/T482113Entity.java

New file
@@ -0,0 +1,13 @@
package com.yc.crm.base.entity;

import lombok.Data;

/**
 * 提醒时间设置
 */
@Data
public class T482113Entity {
    private Integer refId;
    private String type;
    private String time;
}

 src/com/yc/crm/base/entity/T482117Entity.java

New file
@@ -0,0 +1,18 @@
package com.yc.crm.base.entity;

import lombok.Data;

/**
 * 个人分组
 */
@Data
public class T482117Entity {
    private String companyId;
    private String companyName;
    private String userCode;
    private String userName;
    private String content;
    private String groupid;
    private Integer quickid;

}

 src/com/yc/crm/base/service/CrmServiceIfc.java

@@ -1,8 +1,9 @@
package com.yc.crm.base.service;

import com.yc.crm.base.entity.Page;
import com.yc.crm.base.entity.*;

import javax.servlet.http.HttpSession;
import java.util.List;
import java.util.Map;

public interface CrmServiceIfc {
@@ -10,4 +11,32 @@
    Map getBaseInfo(String userCode);

    Object getUserInfo(HttpSession session, Page page);

    void followUpSave(T482105Entity entity);

    Response followUpList(FollowUpRequestEntity entity);

    void followUpDel(Integer id);

    List<T482117Entity> personalList(String userCode, String groupid);

    void personalSave(List<T482117Entity> list, String userCode);

    Response auditList(String userCode, AuditRequestEntity entity);

    void scheduleSave(T482112Entity entity);

    Response scheduleList(ScheduleRequestEntity entity);

    void scheduleDel(Integer id);

    void scheduleAttention(T482112Entity pinFlag);

    void scheduleCompleted(Integer id);

    void commentSave(T482104Entity entity);

    Response commentList(CommentRequestEntity entity);

    void commentDel(Integer id);
}

 src/com/yc/crm/base/service/CrmServiceImpl.java

@@ -5,15 +5,22 @@
import com.yc.crm.base.util.Utils;
import com.yc.crm.clues.entity.T480107Entity;
import com.yc.crm.clues.entity.T480114Entity;
import com.yc.exception.ApplicationException;
import com.yc.service.BaseService;
import com.yc.utils.DateUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpSession;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.*;
import java.util.stream.Collectors;

@Service
@@ -22,20 +29,24 @@
    public Map getBaseInfo(String userCode) {
        HashMap map = new HashMap();
        //----系统参数
        List<SysdictEntity> list = this.jdbcTemplate.query("select dictid,interValue as id,dictvalue as name from _sysdict where dictid in(-48013000,-48013001,-48013002,-48013003,-48013004,-48013005,-48013006,-48013007,-48013008,-48013009,-48013010,-48013011)\n" +
        List<SysdictEntity> list = this.jdbcTemplate.query("select dictid,interValue as id,dictvalue as name from _sysdict where dictid in(-48013000,-48013001,-48013002,-48013003,-48013004,-48013005,-48013006,-48013007,-48013008,-48013009,-48013010,-48013011,-48013012,-48013013,-48013014,-48013015)\n" +
                "order by dictid desc,sequence\n ", new BeanPropertyRowMapper<>(SysdictEntity.class));
        map.put("公司类型",list.stream().filter(x->x.getDictid()==-48013000).collect(Collectors.toList()));
        map.put("采购意向",list.stream().filter(x->x.getDictid()==-48013001).collect(Collectors.toList()));
        map.put("年采购额",list.stream().filter(x->x.getDictid()==-48013002).collect(Collectors.toList()));
        map.put("时区",list.stream().filter(x->x.getDictid()==-48013003).collect(Collectors.toList()));
        map.put("规模",list.stream().filter(x->x.getDictid()==-48013004).collect(Collectors.toList()));
        map.put("访问来源",list.stream().filter(x->x.getDictid()==-48013005).collect(Collectors.toList()));
        map.put("访问IP所在地",list.stream().filter(x->x.getDictid()==-48013006).collect(Collectors.toList()));
        map.put("社交平台",list.stream().filter(x->x.getDictid()==-48013007).collect(Collectors.toList()));
        map.put("电话区号",list.stream().filter(x->x.getDictid()==-48013008).collect(Collectors.toList()));
        map.put("职级",list.stream().filter(x->x.getDictid()==-48013009).collect(Collectors.toList()));
        map.put("系统标签",list.stream().filter(x->x.getDictid()==-48013010).collect(Collectors.toList()));
        map.put("跟进类型",list.stream().filter(x->x.getDictid()==-48013011).collect(Collectors.toList()));
        map.put("bizType", list.stream().filter(x -> x.getDictid() == -48013000).collect(Collectors.toList()));
        map.put("intentionLevel", list.stream().filter(x -> x.getDictid() == -48013001).collect(Collectors.toList()));
        map.put("annualProcurement", list.stream().filter(x -> x.getDictid() == -48013002).collect(Collectors.toList()));
        map.put("timezone", list.stream().filter(x -> x.getDictid() == -48013003).collect(Collectors.toList()));
        map.put("scaleId", list.stream().filter(x -> x.getDictid() == -48013004).collect(Collectors.toList()));
        map.put("inquiryOrigin", list.stream().filter(x -> x.getDictid() == -48013005).collect(Collectors.toList()));
        map.put("inquiryCountry", list.stream().filter(x -> x.getDictid() == -48013006).collect(Collectors.toList()));
        map.put("mediaList", list.stream().filter(x -> x.getDictid() == -48013007).collect(Collectors.toList()));
        map.put("telAreaCode", list.stream().filter(x -> x.getDictid() == -48013008).collect(Collectors.toList()));
        map.put("postGrade", list.stream().filter(x -> x.getDictid() == -48013009).collect(Collectors.toList()));
        map.put("systemFlag", list.stream().filter(x -> x.getDictid() == -48013010).collect(Collectors.toList()));
        map.put("followUpType", list.stream().filter(x -> x.getDictid() == -48013011).collect(Collectors.toList()));
        map.put("cluesStatusFlag", list.stream().filter(x -> x.getDictid() == -48013012).collect(Collectors.toList()));
        map.put("failStatus", list.stream().filter(x -> x.getDictid() == -48013013).collect(Collectors.toList()));
        map.put("quickTxtList", list.stream().filter(x -> x.getDictid() == -48013014).collect(Collectors.toList()));
        map.put("remindTimes", list.stream().filter(x -> x.getDictid() == -48013015).collect(Collectors.toList()));
        //---线索来源
        List<T480107Entity> t480107Entities = this.jdbcTemplate.query("select leadSourceCode\n" +
                ",leadSourceName\n" +
@@ -43,54 +54,876 @@
                ",rowid        \n" +
                ",treecontrol  \n" +
                ",isnull(parentrowid,'') as parentrowid  from t480107", new BeanPropertyRowMapper<>(T480107Entity.class));
        List<T480107Entity> treeEntiries = Utils.convertToTree(t480107Entities,T480107Entity::getParentrowid,T480107Entity::getRowid);
        map.put("线索来源",treeEntiries);
        //---国家地区
        List<T480114Entity> t480114Entities = this.jdbcTemplate.query("select regionsCode\n" +
        List<T480107Entity> treeEntiries = Utils.convertToTree(t480107Entities, T480107Entity::getParentrowid, T480107Entity::getRowid);
        map.put("originList", treeEntiries);
        //---国家
        List<T480114Entity> t480114Entities = this.jdbcTemplate.query("select " +
                // "areaid as regionsCode\n" +
                "regionsCode\n" +
                //",areaname as regionsName\n" +
                ",regionsName\n" +
                "--,memo         \n" +
                ",rowid        \n" +
                ",treecontrol  \n" +//t480114,t110202
                ",isnull(parentrowid,'') as parentrowid  from t480114", new BeanPropertyRowMapper<>(T480114Entity.class));
        List<T480114Entity> treeEntiries114 = Utils.convertToTree(t480114Entities, T480114Entity::getParentrowid, T480114Entity::getRowid);
        map.put("country", treeEntiries114);
        //---省市
        List<T110202Entity> t110202Entities = this.jdbcTemplate.query("select " +
                "areaid\n" +
                ",areaname\n" +
                "--,memo         \n" +
                ",rowid        \n" +
                ",treecontrol  \n" +//t480114,t110202
                ",isnull(parentrowid,'') as parentrowid  from t110202", new BeanPropertyRowMapper<>(T110202Entity.class));
        List<T110202Entity> treeEntiries110202 = Utils.convertToTree(t110202Entities, T110202Entity::getParentrowid, T110202Entity::getRowid);
        map.put("province", treeEntiries110202);
        //---主营产品
        List<T480116Entity> t480116Entities = this.jdbcTemplate.query("select categoryId\n" +
                ",categoryName\n" +
                ",memo         \n" +
                ",rowid        \n" +
                ",treecontrol  \n" +
                ",isnull(parentrowid,'') as parentrowid  from t480114", new BeanPropertyRowMapper<>(T480114Entity.class));
        List<T480114Entity> treeEntiries114 = Utils.convertToTree(t480114Entities,T480114Entity::getParentrowid,T480114Entity::getRowid);
        map.put("国家地区",treeEntiries114);
                ",treecontrol  \n" +//t480114
                ",isnull(parentrowid,'') as parentrowid  from t480116", new BeanPropertyRowMapper<>(T480116Entity.class));
        List<T480116Entity> treeEntiries116 = Utils.convertToTree(t480116Entities, T480116Entity::getParentrowid, T480116Entity::getRowid);
        map.put("categoryIds", treeEntiries116);

        //---标签
        List<T482115Entity> t482115Entities = this.jdbcTemplate.query("select  tag_Id,    \n" +
                "tag_Name,   \n" +
                " sort_Id,   \n" +
                " system_Flag,\n" +
                "tag_Color,  \n" +
                " tag_Type,create_time,update_time from t482115 where  isnull(system_Flag,0)=1 or userCode= " + GridUtils.prossSqlParm(userCode)+" order by sort_Id asc", new BeanPropertyRowMapper<>(T482115Entity.class));
        map.put("标签",t482115Entities);
                " tag_Type,create_time,update_time from t482115 where  isnull(system_Flag,0)=1 or userCode= " + GridUtils.prossSqlParm(userCode) + " order by sort_Id asc", new BeanPropertyRowMapper<>(T482115Entity.class));
        map.put("tags", t482115Entities);
        return map;
    }

    @Override
    public Object getUserInfo(HttpSession session, Page page) {
       // String ccCode = (String) session.getAttribute(SessionKey.CCCODE);
        String where="";
        if(page!=null&&StringUtils.isNotBlank(page.getKey())){
        // String ccCode = (String) session.getAttribute(SessionKey.CCCODE);
        String where = "";
        if (page != null && StringUtils.isNotBlank(page.getKey())) {

            where=" where email like '%"+page.getKey()+"%' or userName like '%"+page.getKey()+"%'";
            where = " where email like '%" + page.getKey() + "%' or userName like '%" + page.getKey() + "%'";
        }
        String sql=" select ccCode,ccName,userCode,userName,email from _sysuser "+where+"order by ccCode asc";
        String sql = " select ccCode,ccName,userCode,userName,email from _sysuser " + where + "order by ccCode asc";
        List<UserEntity> query = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UserEntity.class));
        if(page!=null&&StringUtils.isNotBlank(page.getKey())){
        //直接输出符合条件的用户列表，没树形结构
           return query;
       }else {
           // 没查询email条件则输出树形结构
           List<DepartmentsEntity> departments = query.stream().distinct().map(x -> {
               DepartmentsEntity departmentsEntity = new DepartmentsEntity();
               departmentsEntity.setCcCode(x.getCcCode());
               departmentsEntity.setCcName(x.getCcName());
               return departmentsEntity;
           }).collect(Collectors.toList());
           departments.stream().forEach(x -> {
               List<UserEntity> collect = query.stream().filter(x1 -> x1.getCcCode().equalsIgnoreCase(x.getCcCode())).collect(Collectors.toList());
               x.setSubList(collect);
           });
           return departments;
       }
        if (page != null && StringUtils.isNotBlank(page.getKey())) {
            //直接输出符合条件的用户列表，没树形结构
            return query;
        } else {
            // 没查询email条件则输出树形结构
            List<DepartmentsEntity> departments = query.stream().distinct().map(x -> {
                DepartmentsEntity departmentsEntity = new DepartmentsEntity();
                departmentsEntity.setCcCode(x.getCcCode());
                departmentsEntity.setCcName(x.getCcName());
                return departmentsEntity;
            }).collect(Collectors.toList());
            departments.stream().forEach(x -> {
                List<UserEntity> collect = query.stream().filter(x1 -> x1.getCcCode().equalsIgnoreCase(x.getCcCode())).collect(Collectors.toList());
                x.setSubList(collect);
            });
            return departments;
        }
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void followUpSave(T482105Entity entity) {
        if (StringUtils.isBlank(entity.getContent())) {
            throw new ApplicationException("跟进内容不能为空");
        }
        String sql = "declare     " +
                "    @companyId           varchar(50), \n" +
                "    @companyName        nvarchar(100),\n" +
                "    @userCode           varchar(30), \n" +
                "    @userName           nvarchar(30),\n" +
                "    @id                 bigint, \n" +
                "    @refCode            varchar(50),\n" +
                "    @enable_flag        bit,         \n" +
                "    @delete_flag        bit,         \n" +
                "    @inquiry_id         int,         \n" +
                "    @comment_count      int,         \n" +
                "    @content            nvarchar(4000),\n" +
                "    @plain_content      nvarchar(4000),\n" +
                "    @latitude           varchar(50), \n" +
                "    @longitude          varchar(50), \n" +
                "    @address            nvarchar(200),\n" +
                "    @attach_list        varchar(150),\n" +
                "    @followUp_type      nvarchar(20),\n" +
                "    @followUser         varchar(20),\n" +
                "    @followUp_next_time datetime,    \n" +
                "    @create_time        datetime,    \n" +
                "    @update_time        datetime\n" +
                " select @companyId=" + GridUtils.prossSqlParm(entity.getCompanyId()) + "\n" +
                ",@companyName=" + GridUtils.prossSqlParm(entity.getCompanyName()) + "\n" +
                ",@userCode=" + GridUtils.prossSqlParm(entity.getUserCode()) + "\n" +
                ",@userName=" + GridUtils.prossSqlParm(entity.getUserName()) + "\n" +
                ",@id=" + entity.getId() + "\n" +
                ",@refCode=" + GridUtils.prossSqlParm(entity.getRefCode()) + "\n" +
                ",@inquiry_id=" + entity.getInquiryId() + "\n" +
                ",@content=" + GridUtils.prossSqlParm(entity.getContent()) + "\n" +
                ",@plain_content=" + GridUtils.prossSqlParm(entity.getPlainContent()) + "\n" +
                ",@latitude=" + GridUtils.prossSqlParm(entity.getLatitude()) + "\n" +
                ",@longitude=" + GridUtils.prossSqlParm(entity.getLongitude()) + "\n" +
                ",@address=" + GridUtils.prossSqlParm(entity.getAddress()) + "\n" +
                ",@attach_list=" + GridUtils.prossSqlParm(entity.getAttachList()) + "\n" +
                ",@followUp_type=" + GridUtils.prossSqlParm(entity.getFollowUpType()) + "\n" +
                ",@followUser=" + GridUtils.prossSqlParm(entity.getFollowUser()) + "\n" +
                ",@followUp_next_time=" + GridUtils.prossSqlParm(entity.getFollowUpNextTime()) + "\n" +
                (StringUtils.isNotBlank(entity.getCreateTime()) ? ",@create_time=" + GridUtils.prossSqlParm(entity.getCreateTime()) + "\n" : ",@create_time=getdate()       \n") +
                ",@update_time=getdate()       \n" +
                " if isnull(@id,'')='' \n" +//新增
                " begin \n" +
                "     insert into t482105(" +
                "companyId          \n" +
                ",companyName        \n" +
                ",userCode           \n" +
                ",userName           \n" +
                ",refCode            \n" +
                ",inquiry_id         \n" +
                ",content            \n" +
                ",plain_content      \n" +
                ",latitude           \n" +
                ",longitude          \n" +
                ",address            \n" +
                ",attach_list        \n" +
                ",followUp_type      \n" +
                ",followUser      \n" +
                ",followUp_next_time \n" +
                ",create_time        \n" +
                ",update_time        " +
                ")values(" +
                "@companyId          \n" +
                ",@companyName        \n" +
                ",@userCode           \n" +
                ",@userName           \n" +
                ",@refCode            \n" +
                ",@inquiry_id         \n" +
                ",@content            \n" +
                ",@plain_content      \n" +
                ",@latitude           \n" +
                ",@longitude          \n" +
                ",@address            \n" +
                ",@attach_list        \n" +
                ",@followUp_type      \n" +
                ",@followUser     \n" +
                ",@followUp_next_time \n" +
                ",@create_time        \n" +
                ",@update_time        )\n" +
                "end \n" +
                " else \n" +//修改
                "begin\n" +
                " update a set \n" +
                " content=" + GridUtils.prossSqlParm(entity.getContent()) + "\n" +
                ",plain_content=" + GridUtils.prossSqlParm(entity.getPlainContent()) + "\n" +
                ",latitude =" + GridUtils.prossSqlParm(entity.getLatitude()) + "\n" +
                ",longitude=" + GridUtils.prossSqlParm(entity.getLongitude()) + "\n" +
                ",address=" + GridUtils.prossSqlParm(entity.getAddress()) + "\n" +
                ",attach_list=" + GridUtils.prossSqlParm(entity.getAttachList()) + "\n" +
                ",followUp_type=" + GridUtils.prossSqlParm(entity.getFollowUpType()) + "\n" +
                ",followUser=" + GridUtils.prossSqlParm(entity.getFollowUser()) + "\n" +
                ",followUp_next_time=" + GridUtils.prossSqlParm(entity.getFollowUpNextTime()) + "\n" +
                ",update_time=@update_time from t482105 a where id=@id \n " +
                "end\n";
        this.doBaseExecute(sql);
    }

    @Override
    public Response followUpList(FollowUpRequestEntity request) {
        String where = "";
        if (request.getSearchTxt() != null && request.getSearchTxt().size() > 0) {
            //拼接查询条件
            where = " where ( 1=1 ";
            Set<Map.Entry<String, String>> entries = request.getSearchTxt().entrySet();
            for (Map.Entry<String, String> entry : entries) {
                if (entry.getKey().equalsIgnoreCase("refCode")) {
                    where += "  and refCode=" + GridUtils.prossSqlParm(entry.getValue());
                }
                if (entry.getKey().equalsIgnoreCase("followUpType")) {
                    where += "  and followUp_type=" + GridUtils.prossSqlParm(entry.getValue());
                }
                if (entry.getKey().equalsIgnoreCase("formid")) {
                    request.setFormid(entry.getValue());
                }
            }
            where += ")";
        }
        String fileds =
                " companyId          \n" +
                        ",companyName        \n" +
                        ",userCode           \n" +
                        ",userName           \n" +
                        ",id,refCode            \n" +
                        ",inquiry_id         \n" +
                        ",comment_count      \n" +
                        ",content            \n" +
                        ",plain_content      \n" +
                        ",latitude           \n" +
                        ",longitude          \n" +
                        ",address            \n" +
                        ",attach_list        \n" +
                        ",followUp_type      \n" +
                        ",followUser      \n" +
                        ",followUp_next_time \n" +
                        ",create_time        \n" +
                        ",case when convert(varchar(19),dateadd(hh,1,update_time),120)>convert(varchar(19),GETDATE(),120) then 1 else 0 end as deleteFlag" +//1小时内可以删除跟进
                        ",update_time ,@TotalRowCount as totalRowCount ,@pageCount as pageCount       ";

        String sql = "set nocount on ; \n" +
                "                 declare @Limit int , @Page int ,@StartRowNo int ,@EndRowNo int ; \n" +
                "                 select @Limit  = " + request.getPageSize() + " , @Page  = " + request.getPageNo() + "  ; \n" +
                "                 declare @TotalRowCount int ,@pageCount int; \n" +
                " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" +
                " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" +
                " select @TotalRowCount =count(1),@pageCount=CEILING((COUNT(1)+0.0)/" + request.getPageSize() + ")  from t482105 a   " + where + " \n" +
                " SELECT * FROM ( \n" +
                " select top 100 percent ROW_NUMBER() OVER (ORDER BY  a.create_time desc) AS NO,\n" +
                fileds + "  from t482105 a " + where +
                " order by  a.create_time desc \n" +
                " ) t WHERE t.NO  BETWEEN @StartRowNo AND @EndRowNo";
        List<T482105Entity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482105Entity.class));
        list.stream().forEach(x -> {
            //处理图片，附件，生成调用url及相关信息
            FileStructEntity fileStructEntity = new FileStructEntity();
            fileStructEntity.setDbid(request.getDbid());
            fileStructEntity.setFormid(request.getFormid());
            fileStructEntity.setFileStr(x.getAttachList());
            x.setFiles(Utils.getFileEntities(fileStructEntity));
        });
        Response response = new Response();
        response.setTotalCount((list != null && list.size() > 0) ? list.get(0).getTotalRowCount() : 0);
        response.setPageCount((list != null && list.size() > 0) ? list.get(0).getPageCount() : 0);
        response.setData(list);
        return response;
    }

    @Override
    public void followUpDel(Integer id) {
        this.doBaseExecute("delete from t482105 where id=" + id);
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void scheduleDel(Integer id) {
        this.doBaseExecute("delete from t482113 where refid=" + id + " \n delete from t482112 where id=" + id);
    }

    @Override
    public void scheduleAttention(T482112Entity entity) {
        this.doBaseExecute(" update  t482112 set pinFlag=" + entity.getPinFlag() + " where id=" + entity.getId());
    }

    @Override
    public void scheduleCompleted(Integer id) {
        this.doBaseExecute(" update  t482112 set completeFlag=1 where id=" + id);
    }

    @Override
    public List<T482117Entity> personalList(String userCode, String groupid) {
        String where = " isnull(groupId,'')=" + GridUtils.prossSqlParm(groupid);
        if (StringUtils.equalsIgnoreCase(groupid, "个人分组")) {
            where += " and userCode=" + GridUtils.prossSqlParm(userCode);
        }
        return this.jdbcTemplate.query("select quickid,content,companyid ,companyName ,userCode,userName,groupid from t482117 where " + where, new BeanPropertyRowMapper<>(T482117Entity.class));
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void personalSave(List<T482117Entity> list, String userCode) {
        String sql = "declare @content nvarchar(150),@companyid varchar(50),@companyName nvarchar(150),@userCode varchar(50),@userName nvarchar(50),@groupid varchar(50)='个人分组'\n" +
                "delete from t482117 where userCode=" + GridUtils.prossSqlParm(userCode) + "\n";
        for (T482117Entity entity : list) {
            sql += " select @content=" + GridUtils.prossSqlParm(entity.getContent()) + ",@companyid=" + GridUtils.prossSqlParm(entity.getCompanyId()) + ",@companyName =" + GridUtils.prossSqlParm(entity.getCompanyName()) + ",@userCode=" + GridUtils.prossSqlParm(entity.getUserCode()) + ",@userName=" + GridUtils.prossSqlParm(entity.getUserName()) + "\n" +
                    "insert into t482117(content,companyid,companyName,userCode,userName,groupid) values(@content,@companyid,@companyName,@userCode,@userName,@groupid)\n";
        }
        this.doBaseExecute(sql);
    }

    @Override
    public Response auditList(String userCode, AuditRequestEntity request) {
        String where = " where  formid=" + request.getFormid() + " and docCode=" + GridUtils.prossSqlParm(request.getDocCode());
        String fileds = " userCode,userName,oldValue,newValue,fieldName,auditDateTime,@TotalRowCount as totalRowCount ,@pageCount as pageCount       ";

        String sql = "set nocount on ; \n" +
                "                 declare @Limit int , @Page int ,@StartRowNo int ,@EndRowNo int ; \n" +
                "                 select @Limit  = " + request.getPageSize() + " , @Page  = " + request.getPageNo() + "  ; \n" +
                "                 declare @TotalRowCount int ,@pageCount int; \n" +
                " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" +
                " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" +
                " select @TotalRowCount =count(1),@pageCount=CEILING((COUNT(1)+0.0)/" + request.getPageSize() + ")  from _sysAudit a   " + where + " \n" +
                " SELECT * FROM ( \n" +
                " select top 100 percent ROW_NUMBER() OVER (ORDER BY  a.auditDateTime desc) AS NO,\n" +
                fileds + "  from _sysAudit a " + where +
                " order by  a.auditDateTime desc \n" +
                " ) t WHERE t.NO  BETWEEN @StartRowNo AND @EndRowNo";
        List<AuditEntity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AuditEntity.class));
        Map<String, List<Map>> maps = new LinkedHashMap<>();
        List<AuditEntity> collect = list.stream().sorted(Comparator.comparing(AuditEntity::getAuditDateTime).reversed()).distinct().collect(Collectors.toList());
        collect.stream().forEach(x -> {
            //把相同时间的放在同一组输出
            List<Map> auditEntities = new ArrayList<>();
            list.stream().forEach(y -> {
                if (x.getAuditDateTime().equals(y.getAuditDateTime())) {
                    Map<String, String> map = new HashMap<>();
                    map.put("oldValue", y.getOldValue());
                    map.put("newValue", y.getNewValue());
                    map.put("fieldName", y.getFieldName());
                    map.put("userCode", y.getUserCode());
                    map.put("userName", y.getUserName());
                    auditEntities.add(map);
                }
            });
            maps.put(DateUtil.formatDate(x.getAuditDateTime(), "yyyy-MM-dd HH:mm:ss"), auditEntities);
        });
        Response response = new Response();
        response.setTotalCount((list != null && list.size() > 0) ? list.get(0).getTotalRowCount() : 0);
        response.setPageCount((list != null && list.size() > 0) ? list.get(0).getPageCount() : 0);
        response.setData(maps);
        return response;
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void commentSave(T482104Entity entity) {
        if (StringUtils.isBlank(entity.getComment())) {
            throw new ApplicationException("评论内容不能为空");
        }
        String sql = "declare " +
                "@userCode varchar(50)\n" +
                ",@userName varchar(50)\n" +
                ",@refId int   \n" +
                ",@id int     \n" +
                ",@comment varchar(3000)  \n" +
                ",@attachList varchar(300)  \n" +
                ",@senders varchar(300)  \n" +
                ",@createTime datetime\n" +
                ",@updateTime datetime\n" +
                " select " +
                "@userCode=" + GridUtils.prossSqlParm(entity.getUserCode()) + "      ,\n" +
                "@userName=" + GridUtils.prossSqlParm(entity.getUserName()) + "      ,\n" +
                "@refId=" + entity.getRefId() + ",   \n" +
                "@id=" + entity.getId() + ",   \n" +
                "@comment=" + GridUtils.prossSqlParm(entity.getComment()) + "      ,\n" +
                "@attachList=" + GridUtils.prossSqlParm(entity.getAttachList()) + "      ,\n" +
                "@senders=" + GridUtils.prossSqlParm(entity.getSenders()) + "      ,\n" +
                "@createTime=getdate(),\n" +
                "@updateTime=getdate()\n" +
                " if isnull(@id,'')=''\n" +
                " begin\n" +
                " insert into t482104(" +
                " userCode" +
                ",userName \n" +
                ",refId  \n" +
                ",comment \n" +
                ",attachList\n" +
                ",senders\n" +
                ",createTime\n" +
                ",updateTime)values(" +
                " @userCode" +
                ",@userName \n" +
                ",@refId  \n" +
                ",@comment\n" +
                ",@attachList\n" +
                ",@senders\n" +
                ",@createTime\n" +
                ",@updateTime)" +
                "end\n" +
                "else\n" +
                "begin\n" +
                " update t482104 set " +
                " userCode=@userCode" +
                ",userName=@userName \n" +
                ",refId=@refId  \n" +
                ",comment=@comment \n" +
                ",attachList=@attachList\n" +
                ",senders=@senders\n" +
                ",updateTime=@updateTime where id=@id\n" +
                "end\n";
        this.doBaseExecute(sql);

    }

    /**
     * 日程周期的生成
     * 有设置周期性，则需要根据周期性设置重复生成相关的日程
     * 每天例如：start:2024-8-1,end:2024-8-4
     * 指定结束时间(2024-8-3)，则生成从开始时间到结束时间的相同日程
     * 2024-8-1,2024-8-2,2024-8-3
     * 每周例如：start:2024-8-1,end:2024-8-24
     * 指定结束时间(2024-8-20)，则会按周一次生成从开始时间到结束时间的相同日程
     * 2024-8-1,2024-8-7,2024-8-14
     * 每月例如：start:2024-8-1,end:2024-10-24
     * 指定结束时间(2024-9-20)，则会按周一次生成从开始时间到结束时间的相同日程
     * 2024-8-1,2024-9-1
     *
     * @param entity
     * @return
     */
    private List<T482112Entity> prossSchedule(T482112Entity entity) {
        List<T482112Entity> list = new ArrayList<>();
        if (entity.getType().equalsIgnoreCase("not")) {
            //没设置则直接返回
            list.add(entity);
        } else {
            if (StringUtils.isBlank(entity.getStartTime())) {
                throw new ApplicationException("开始日期不能为空");
            }
            if (StringUtils.isBlank(entity.getEndTime())) {
                throw new ApplicationException("结束日期不能为空");
            }
            if (StringUtils.isBlank(entity.getRepeatEnd())) {
                throw new ApplicationException("周期性结束日期不能为空");
            }
            if (entity.getId() == null || entity.getId() == 0) {//新增
                calRepeatDate(entity, list);
            } else if (entity.getId() != null && entity.getId() != 0 && entity.getRepeatFlag() == 1) {//修改且选择了生成后续重复日程
                //修改才执行
                //表示需要以当前重复日程重复生成新的重复日程
                calRepeatDate(entity, list);
            } else {
                list.add(entity);
            }
        }
        return list;
    }

    private void calRepeatDate(T482112Entity entity, List<T482112Entity> list) {

        //开始日期
        String pattern = "yyyy-MM-dd HH:mm:ss";
        Calendar calStart = Calendar.getInstance();
        LocalDateTime start = null;
        LocalDateTime end = null;
        LocalDateTime repeatEnd = null;
        try {
            start = LocalDateTime.parse(entity.getStartTime(), DateTimeFormatter.ofPattern(pattern));

        } catch (Exception e) {
            //表示没有时间，需要增加00:00:00，以便不出错
            start = LocalDateTime.parse(entity.getStartTime() + " 00:00:00", DateTimeFormatter.ofPattern(pattern));
        }
        try {
            end = LocalDateTime.parse(entity.getEndTime(), DateTimeFormatter.ofPattern(pattern));
        } catch (Exception e) {
            end = LocalDateTime.parse(entity.getEndTime() + " 00:00:00", DateTimeFormatter.ofPattern(pattern));
        }
        try {
            repeatEnd = LocalDateTime.parse(entity.getRepeatEnd(), DateTimeFormatter.ofPattern(pattern));
        } catch (Exception e) {
            repeatEnd = LocalDateTime.parse(entity.getRepeatEnd() + " 00:00:00", DateTimeFormatter.ofPattern(pattern));
        }
        calStart.setTime(Date.from(start.atZone(ZoneId.systemDefault()).toInstant()));
        //结束日期取周期性设置
        Calendar calEnd = Calendar.getInstance();
        calEnd.setTime(Date.from(repeatEnd.atZone(ZoneId.systemDefault()).toInstant()));
        if (entity.getType().equalsIgnoreCase("everyday")) {
            //1，----每天
            repeatByDay(entity, list, start, end, pattern, calStart,1);
        }
        if (entity.getType().equalsIgnoreCase("everyweek")) {
            //2，----每周
            repeatByWeek(entity, list, calStart, calEnd, start, end, pattern,1);
        }
        if (entity.getType().equalsIgnoreCase("everymonth")) {
            //3，----每月
            repeatByMonth(entity, list, calStart, calEnd, start, end, pattern,1);
        }
        if (entity.getType().equalsIgnoreCase("ext")) {
            if(entity.getNum()==null||entity.getNum()==0){
                throw  new ApplicationException("自定义重复周期[num]不能为空");
            }
            //4，----自定义
            if (entity.getType().equalsIgnoreCase("day")) {
                //1，----每天
                repeatByDay(entity, list, start, end, pattern, calStart,entity.getNum());
            }
            if (entity.getType().equalsIgnoreCase("week")) {
                //2，----每周
                repeatByWeek(entity, list, calStart, calEnd, start, end, pattern,entity.getNum());
            }
            if (entity.getType().equalsIgnoreCase("month")) {
                //3，----每月
                repeatByMonth(entity, list, calStart, calEnd, start, end, pattern,entity.getNum());
            }
        }
       // String s=null;
        //        s.toLowerCase();
    }

    private static void repeatByDay(T482112Entity entity, List<T482112Entity> list, LocalDateTime start, LocalDateTime end, String pattern, Calendar calStart,int interval) {
        long dayNumber = (ChronoUnit.DAYS.between(start, end)-1)/interval;
        list.add(entity);
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        while (dayNumber > 0) {
            calStart.add(Calendar.DAY_OF_MONTH, 1);   //加一天
            T482112Entity temp = new T482112Entity();
            BeanUtils.copyProperties(entity, temp);
            temp.setId(null);
            String dateStr = sdf.format(calStart.getTime());
            temp.setStartTime(dateStr);
            //新结束日期=新的日期+相差天数
            calStart.add(Calendar.DAY_OF_MONTH, (int) dayNumber);
            dateStr = sdf.format(calStart.getTime());
            temp.setEndTime(dateStr);
            calStart.add(Calendar.DAY_OF_MONTH, -(int) dayNumber);//还原回来
            list.add(temp);
            dayNumber--;
        }
    }

    private void repeatByWeek(T482112Entity entity, List<T482112Entity> list, Calendar calStart, Calendar calEnd, LocalDateTime start, LocalDateTime end, String pattern,int interval) {
        int start_weekInYear = this.getWeekInYear(calStart.getTime());
        int end_weekInYear = this.getWeekInYear(calEnd.getTime());
        long weekNumber = (end_weekInYear-start_weekInYear)/interval;
        long dayNumber = ChronoUnit.DAYS.between(start, end);
        list.add(entity);
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        while (weekNumber > 0) {
            calStart.add(Calendar.WEEK_OF_YEAR, 1);   //加一周
            T482112Entity temp = new T482112Entity();
            BeanUtils.copyProperties(entity, temp);
            temp.setId(null);
            String dateStr = sdf.format(calStart.getTime());
            temp.setStartTime(dateStr);
            //新结束日期=新的日期+相差天数
            calStart.add(Calendar.DAY_OF_MONTH, (int) dayNumber);
            dateStr = sdf.format(calStart.getTime());
            temp.setEndTime(dateStr);
            calStart.add(Calendar.DAY_OF_MONTH, -(int) dayNumber);//还原回来
            list.add(temp);
            weekNumber--;
        }
    }

    private static void repeatByMonth(T482112Entity entity, List<T482112Entity> list, Calendar calStart, Calendar calEnd, LocalDateTime start, LocalDateTime end, String pattern,int interval) {
        int start_monthInYear = calStart.get(Calendar.MONTH);
        int end_monthInYear = calEnd.get(Calendar.MONTH);
        long monthNumber = (end_monthInYear-start_monthInYear)/interval;
        long dayNumber = ChronoUnit.DAYS.between(start, end);
        list.add(entity);
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        while (monthNumber > 0) {
            calStart.add(Calendar.MONTH, 1);   //加一个月
            T482112Entity temp = new T482112Entity();
            BeanUtils.copyProperties(entity, temp);
            temp.setId(null);
            String dateStr = sdf.format(calStart.getTime());
            temp.setStartTime(dateStr);
            //新结束日期=新的日期+相差天数
            calStart.add(Calendar.DAY_OF_MONTH, (int) dayNumber);
            dateStr = sdf.format(calStart.getTime());
            temp.setEndTime(dateStr);
            calStart.add(Calendar.DAY_OF_MONTH, -(int) dayNumber);//还原回来
            list.add(temp);
            monthNumber--;
        }
    }

    //获取到所传日期在当年第几周
    public  int getWeekInYear(Date date){
        Calendar calendar = Calendar.getInstance();
        calendar.setFirstDayOfWeek(Calendar.MONDAY);
        calendar.setTime(date);
        return calendar.get(Calendar.WEEK_OF_YEAR);
    }
    /**
     * 因为日历比较用的是time属性，这个值是创建时候生成，就算二个日期一样，也会不一样。所以这里判断年月日，时分秒
     *
     * @param start
     * @param end
     * @return
     */
    private boolean checkCalendar(Calendar start, Calendar end) {
        int dayNumber = DateUtil.daysBetween(end.getTime(), start.getTime());
        if (dayNumber > 0) {
            return true;
        } else {
            return false;
        }
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void scheduleSave(T482112Entity t482112Entity) {
        if (StringUtils.isBlank(t482112Entity.getTitle())) {
            throw new ApplicationException("日程内容不能为空");
        }
        String sql = " declare @companyId      varchar(50),\n" +
                "    @companyName    nvarchar(100),\n" +
                "    @userCode       varchar(30),\n" +
                "    @userName       nvarchar(30),\n" +
                "    @id             bigint,\n" +
                "    @refType        varchar(20),\n" +
                "    @refCode        varchar(50) ,\n" +
                "    @title          nvarchar(100),\n" +
                "    @color          varchar(10),\n" +
                "    @participant_id varchar(100),\n" +
                "    @start_time     datetime,\n" +
                "    @end_time       datetime,\n" +
                "    @remark         nvarchar(230),\n" +
                "    @attach_list    varchar(230),\n" +
                "    @image_list     varchar(230),\n" +
                "    @full_day_flag  bit,\n" +
                "    @create_time    datetime,\n" +
                "    @update_time    datetime,@last_inserted_id bigint\n" +
                ",@rule_type varchar(50)" +
                ",@rule_unit varchar(50)\n" +
                ",@rule_repeatStart varchar(50)\n" +
                ",@rule_repeatEnd varchar(50)\n" +
                ",@rule_num int \n" +
                ",@remind_refid int \n" +
                ",@remind_type varchar(50)\n" +
                ",@remind_time varchar(50)\n";
        List<T482112Entity> list = this.prossSchedule(t482112Entity);
        for (T482112Entity entity : list) {
            sql += " select @companyId=" + GridUtils.prossSqlParm(entity.getCompanyId()) + "      ,\n" +
                    "@companyName=" + GridUtils.prossSqlParm(entity.getCompanyName()) + "      ,\n" +
                    "@userCode=" + GridUtils.prossSqlParm(entity.getUserCode()) + "      ,\n" +
                    "@userName=" + GridUtils.prossSqlParm(entity.getUserName()) + "      ,\n" +
                    "@id=" + entity.getId() + "      ,\n" +
                    "@refType=0        ,\n" +
                    "@refCode=" + GridUtils.prossSqlParm(entity.getRefCode()) + "      ,\n" +
                    "@title=" + GridUtils.prossSqlParm(entity.getTitle()) + "      ,\n" +
                    "@color=" + GridUtils.prossSqlParm(entity.getColor()) + "      ,\n" +
                    "@participant_id =" + GridUtils.prossSqlParm(entity.getParticipantId()) + "      ,\n" +
                    "@start_time=" + GridUtils.prossSqlParm(entity.getStartTime()) + "      ,\n" +
                    "@end_time=" + GridUtils.prossSqlParm(entity.getEndTime()) + "      ,\n" +
                    "@remark=" + GridUtils.prossSqlParm(entity.getRemark()) + "      ,\n" +
                    "@attach_list=" + GridUtils.prossSqlParm(entity.getAttachList()) + "      ,\n" +
                    "@image_list=" + GridUtils.prossSqlParm(entity.getImageList()) + "      ,\n" +
                    "@full_day_flag=" + entity.getFullDayFlag() + "      ,\n" +
                    "@rule_unit=" + GridUtils.prossSqlParm(entity.getUnit()) + "      ,\n" +
                    "@rule_type=" + GridUtils.prossSqlParm(entity.getType()) + "      ,\n" +
                    "@rule_repeatStart=" + GridUtils.prossSqlParm(entity.getRepeatStart()) + "      ,\n" +
                    "@rule_repeatEnd=" + GridUtils.prossSqlParm(entity.getRepeatEnd()) + "      ,\n" +
                    "@rule_num =" + entity.getNum() + "      ,\n" +
                    "@create_time=getdate()    ,\n" +
                    "@update_time=getdate()    \n" +
                    "if isnull(@id,'')='' \n" +
                    " begin\n" +
                    " insert into t482112(" +
                    " companyId     \n" +
                    ",companyName   \n" +
                    ",userCode      \n" +
                    ",userName      \n" +
                    ",refType       \n" +
                    ",refCode       \n" +
                    ",title         \n" +
                    ",color         \n" +
                    ",participant_id\n" +
                    ",start_time    \n" +
                    ",end_time      \n" +
                    ",remark        \n" +
                    ",attach_list   \n" +
                    ",image_list    \n" +
                    ",full_day_flag \n" +
                    ",unit      \n" +
                    ",type        \n" +
                    ",repeat_Start \n" +
                    ",repeat_End   \n" +
                    ",num        " +
                    ",create_time   \n" +
                    ",update_time   \n)values(" +
                    " @companyId     \n" +
                    ",@companyName   \n" +
                    ",@userCode      \n" +
                    ",@userName      \n" +
                    ",@refType       \n" +
                    ",@refCode       \n" +
                    ",@title         \n" +
                    ",@color         \n" +
                    ",@participant_id\n" +
                    ",@start_time    \n" +
                    ",@end_time      \n" +
                    ",@remark        \n" +
                    ",@attach_list   \n" +
                    ",@image_list    \n" +
                    ",@full_day_flag \n" +
                    ",@rule_unit      \n" +
                    ",@rule_type        \n" +
                    ",@rule_repeatStart \n" +
                    ",@rule_repeatEnd   \n" +
                    ",@rule_num        " +
                    ",@create_time   \n" +
                    ",@update_time   )\n" +
                    " SELECT @last_inserted_id=IDENT_CURRENT('t482112')\n" +
                    "end\n" +
                    " else\n" +//修改
                    "begin\n" +
                    " update t482112 set \n" +
                    "title=@title        \n" +
                    ",color =@color        \n" +
                    ",participant_id=@participant_id\n" +
                    ",start_time=@start_time    \n" +
                    ",end_time=@end_time      \n" +
                    ",remark=@remark        \n" +
                    ",attach_list=@attach_list   \n" +
                    ",image_list =@image_list   \n" +
                    ",unit=@rule_unit      \n" +
                    ",type=@rule_type        \n" +
                    ",num=@rule_num        \n" +
                    ",repeat_Start=@rule_repeatStart \n" +
                    ",repeat_End=@rule_repeatEnd   \n" +
                    ",update_time=@update_time\n" +
                    ",full_day_flag=@full_day_flag \n" +
                    " SELECT @last_inserted_id=@id\n" +
                    "end\n" +
                    AddRemindTimesSql(entity.getRemindTimes());
        }
        this.doBaseExecute(sql);
    }

    /**
     * 生成周期性日程设置sql
     *
     * @param entity
     * @return
     */
    private String AddRemindTimesSql(List<T482113Entity> entity) {
        //只有新增，所以要先全部删除
        String sql = " delete from t482113 where refId=@last_inserted_id \n";
        for (T482113Entity t482113 : entity) {
            sql += "\nselect\n" +
                    " @remind_type=" + GridUtils.prossSqlParm(t482113.getType()) + "\n" +
                    ",@remind_time=" + GridUtils.prossSqlParm(t482113.getTime()) + "\n" +
                    ",@remind_refid=@last_inserted_id\n" +
                    " insert into t482113(" +
                    " refId,      \n" +
                    "time,      \n" +
                    "type        \n" +
                    ")values(" +
                    "@remind_refid,      \n" +
                    "@remind_time,      \n" +
                    "@remind_type       \n" +
                    ")\n";
        }
        return sql;
    }

    @Override
    public Response scheduleList(ScheduleRequestEntity request) {
        String where = "";
        if (request.getSearchTxt() == null) {
            throw new ApplicationException("查询参数不能都为空");
        } else {
            String refCode = request.getSearchTxt().get("refCode");
            if (refCode == null) {
                throw new ApplicationException("refCode参数不能都为空");
            }
            where = " where  refCode=" + GridUtils.prossSqlParm(refCode);
            String completeFlag = request.getSearchTxt().get("completeFlag");
            if (completeFlag == null) {
                where += " and isnull(completeFlag,0)=0";
            } else {
                where += " and isnull(completeFlag,0)=" + completeFlag;
            }
        }
        String fileds = "refType       \n" +
                ",id,refCode       \n" +
                ",title         \n" +
                ",color         \n" +
                ",participant_id\n" +
                ",start_time    \n" +
                ",end_time      \n" +
                ",remark        \n" +
                ",attach_list   \n" +
                ",image_list    \n" +
                ",full_day_flag \n" +
                ",unit      \n" +
                ",type        \n" +
                ",repeat_Start \n" +
                ",repeat_End   \n" +
                ",num        " +
                ",create_time   \n" +
                ",update_time   \n" +
                ",@TotalRowCount as totalRowCount ,@pageCount as pageCount       ";

        String sql = "set nocount on ; \n" +
                "                 declare @Limit int , @Page int ,@StartRowNo int ,@EndRowNo int ; \n" +
                "                 select @Limit  = " + request.getPageSize() + " , @Page  = " + request.getPageNo() + "  ; \n" +
                "                 declare @TotalRowCount int ,@pageCount int; \n" +
                " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" +
                " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" +
                " select @TotalRowCount =count(1),@pageCount=CEILING((COUNT(1)+0.0)/" + request.getPageSize() + ")  from t482112 a   " + where + " \n" +
                " SELECT * FROM ( \n" +
                " select top 100 percent ROW_NUMBER() OVER (ORDER BY  a.update_time desc) AS NO,\n" +
                fileds + "  from t482112 a " + where +
                " order by  a.update_time desc \n" +
                " ) t WHERE t.NO  BETWEEN @StartRowNo AND @EndRowNo";
        List<T482112Entity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482112Entity.class));

        list.stream().forEach(x -> {
            if (StringUtils.isNotBlank(x.getAttachList())) {
                FileStructEntity fileStructEntity = new FileStructEntity();
                fileStructEntity.setDbid(request.getDbid());
                fileStructEntity.setFormid(request.getFormId() + "");
                fileStructEntity.setFileStr(x.getAttachList());
                x.setFiles(Utils.getFileEntities(fileStructEntity));
            }
        });
        Response response = new Response();
        response.setTotalCount((list != null && list.size() > 0) ? list.get(0).getTotalRowCount() : 0);
        response.setPageCount((list != null && list.size() > 0) ? list.get(0).getPageCount() : 0);
        response.setData(list);
        return response;
    }

    @Override
    public Response commentList(CommentRequestEntity request) {
        String where = "";
        if (request.getSearchTxt() == null) {
            throw new ApplicationException("查询参数不能都为空");
        } else {
            String refId = request.getSearchTxt().get("refId");
            if (refId == null) {
                throw new ApplicationException("refId参数不能都为空");
            }
            where = " where  refId=" + GridUtils.prossSqlParm(refId);
        }
        String fileds = "userCode,  \n" +
                "userName,  \n" +
                " refId,    \n" +
                " id,       \n" +
                "comment,   \n" +
                "attachList,\n" +
                "convert(varchar(19), createTime,120) as createTime,senders,\n" +
                "convert(varchar(19), updateTime,120)  as updateTime" +
                ",case when usercode=@usercode then 1 else 0 end as enableFlag,@TotalRowCount as totalRowCount ,@pageCount as pageCount       ";

        String sql = "set nocount on ; \n" +
                "                 declare @Limit int , @Page int ,@StartRowNo int ,@EndRowNo int ,@usercode varchar(50)=" + GridUtils.prossSqlParm(request.getUserCode()) + " \n" +
                "                 select @Limit  = " + request.getPageSize() + " , @Page  = " + request.getPageNo() + "  ; \n" +
                "                 declare @TotalRowCount int ,@pageCount int; \n" +
                " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" +
                " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" +
                " select @TotalRowCount =count(1),@pageCount=CEILING((COUNT(1)+0.0)/" + request.getPageSize() + ")  from t482104 a   " + where + " \n" +
                " SELECT * FROM ( \n" +
                " select top 100 percent ROW_NUMBER() OVER (ORDER BY  a.updateTime desc) AS NO,\n" +
                fileds + "  from t482104 a " + where +
                " order by  a.updateTime desc \n" +
                " ) t WHERE t.NO  BETWEEN @StartRowNo AND @EndRowNo";
        List<T482104Entity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482104Entity.class));

        list.stream().forEach(x -> {
            if (StringUtils.isNotBlank(x.getAttachList())) {
                FileStructEntity fileStructEntity = new FileStructEntity();
                fileStructEntity.setDbid(request.getDbid());
                fileStructEntity.setFormid(request.getFormId() + "");
                fileStructEntity.setFileStr(x.getAttachList());
                x.setFiles(Utils.getFileEntities(fileStructEntity));
            }
        });
        Response response = new Response();
        response.setTotalCount((list != null && list.size() > 0) ? list.get(0).getTotalRowCount() : 0);
        response.setPageCount((list != null && list.size() > 0) ? list.get(0).getPageCount() : 0);
        response.setData(list);
        return response;
    }

    @Override
    public void commentDel(Integer id) {
        this.doBaseExecute("delete from t482104 where id=" + id);
    }
}

 src/com/yc/crm/base/util/Utils.java

@@ -1,6 +1,14 @@
package com.yc.crm.base.util;

import com.yc.crm.base.entity.FileEntity;
import com.yc.crm.base.entity.FileStructEntity;
import com.yc.crm.base.entity.TreeEntiry;
import com.yc.entity.AttachmentConfig;
import com.yc.entity.attachment.AttachmentEntity;
import com.yc.exception.ApplicationException;
import com.yc.factory.FactoryBean;
import com.yc.multiData.SpObserver;
import com.yc.service.upload.AttachmentIfc;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
@@ -55,4 +63,164 @@
        ((TreeEntiry) node).setSubList(children); // 假设YourNodeType是节点对象的类型，‌且有setChildren方法
    }

    /**
     * 通过unid;seq查找对应附件内容
     * @return
     */
    public static List<FileEntity> getFileEntities(FileStructEntity fileStructEntity) {
        try {
            if(StringUtils.isBlank(fileStructEntity.getFileStr())) {
                throw new ApplicationException("文件url不能为空");
            }
            List<FileEntity> fileEntities=new ArrayList<>();
            SpObserver.setDBtoInstance("_" + fileStructEntity.getDbid());   //切换数据源
            AttachmentIfc attachmentIfc= (AttachmentIfc) FactoryBean.getBean("AttachmentImpl");
            List<AttachmentEntity> attachmentEntityList = attachmentIfc.getAttachmentEntityList(fileStructEntity.getFileStr().split(";")[0], "3");//3表示多附件
            for (AttachmentEntity attachmentEntity : attachmentEntityList) {
                fileEntities.add(getFileInfo(attachmentEntity,fileStructEntity));
            }
            return fileEntities;
        }catch (Exception e){
            throw e;
        }finally {
            SpObserver.setDBtoInstance();
        }
    }
    private static FileEntity  getFileInfo(AttachmentEntity attachmentEntity,FileStructEntity fileStructEntity) {
        String fileSizeStr = "";
        Long fileSize = attachmentEntity.getFileSize();
        if (fileSize.longValue() < 1024L) {
            fileSizeStr = fileSize.longValue() + "B";
        } else if (fileSize.longValue() >= 1024L && fileSize.longValue() < 1024L * 1024L) {
            fileSizeStr = (fileSize.longValue() / 1024L) + "KB";
        } else if (fileSize.longValue() >= 1024L * 1024L && fileSize.longValue() < 1024L * 1024L * 1024L) {
            fileSizeStr = (fileSize.longValue() / (1024L * 1024L)) + "MB";
        } else if (fileSize.longValue() >= 1024L * 1024L * 1024L && fileSize.longValue() < 1024L * 1024L * 1024L * 1024L) {
            fileSizeStr = (fileSize.longValue() / (1024L * 1024L * 1024L)) + "GB";
        } else {
            Long fileSizeByMB = (fileSize.longValue() / (1024L * 1024L));  //分开2次计算，因为 1024*1024*1024*1024=？？ 会导致内存溢出         2019-12-07
            fileSizeStr = (fileSizeByMB.longValue() / (1024L * 1024L)) + "TB";
        }
        String fileType="";
        String src="";
        String staticUrl="";
        if ("jpg".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "jpeg".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "png".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "gif".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "pic".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "ico".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "bmp".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "tif".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "image";
            src = "/getImage.do?type=3&uuid=" + attachmentEntity.getUnid() + ";" + attachmentEntity.getSeq() + "&dbid=" + fileStructEntity.getDbid();
        } else if ("mp4".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "avi".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "rm".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "asf".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "divx".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "rmvb".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "video";
            src = "/smallpic/specialpic/avi.png";
            staticUrl = "/smallpic/specialpic/avi.png";
        } else if ("mp3".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "aif".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "wav".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "au".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "ram".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "audio";
            src = "/smallpic/specialpic/avi.png";
            staticUrl = "/smallpic/specialpic/avi.png";
        } else if ("html".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "htm".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "html";
            src = "/smallpic/specialpic/html.png";
            staticUrl = "/smallpic/specialpic/html.png";
        } else if ("txt".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "hlp".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "wps".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "text";
            src = "/smallpic/specialpic/txt.png";
            staticUrl = "/smallpic/specialpic/txt.png";
        } else if ("pdf".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "pdf";
            src = "/smallpic/specialpic/pdf.png";
            staticUrl = "/smallpic/specialpic/pdf.png";
        } else if ("docx".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "docm".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "dotx".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "dotm".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "doc".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "office";
            src = "/smallpic/specialpic/doc.png";
            staticUrl = "/smallpic/specialpic/doc.png";
        } else if ("pptx".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "pptm".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "ppsx".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "potx".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "potm".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "ppam".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "ppt".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "office";
            src = "/smallpic/specialpic/ppt.png";
            staticUrl = "/smallpic/specialpic/ppt.png";
        } else if ("xlsx".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "xltx".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "xltm".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "xlsm".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "xlsb".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "xlam".equalsIgnoreCase((String) attachmentEntity.getFileType()) ||
                "xls".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "office";
            staticUrl = "/smallpic/specialpic/excel.png";
        } else if ("zip".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "zip";
            src = "/smallpic/specialpic/zip.png";
            staticUrl = "/smallpic/specialpic/zip.png";
        } else if ("rar".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "rar";
            src = "/smallpic/specialpic/rar.png";
            staticUrl = "/smallpic/specialpic/rar.png";
        } else if ("key".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "key";
            src = "/smallpic/specialpic/key.png";
            staticUrl = "/smallpic/specialpic/key.png";
        }else if ("mov".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "video";
            src = "/smallpic/specialpic/mov.png";
            staticUrl = "/smallpic/specialpic/mov.png";
        }else if ("numbers".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "numbers";
            src = "/smallpic/specialpic/numbers.png";
            staticUrl = "/smallpic/specialpic/numbers.png";
        }else if ("pages".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "pages";
            src = "/smallpic/specialpic/pages.png";
            staticUrl = "/smallpic/specialpic/pages.png";
        }else if ("png".equalsIgnoreCase((String) attachmentEntity.getFileType())) {
            fileType = "image";
            src = "/smallpic/specialpic/png.png";
            staticUrl = "/smallpic/specialpic/png.png";
        }else {
            src = "/smallpic/specialpic/unknown.png";
            staticUrl = "/smallpic/specialpic/unknown.png";
        }
        FileEntity fileEntity = new FileEntity();
        fileEntity.setFileName(attachmentEntity.getOriginalFileName());

        //生成附件url调用
        String url= AttachmentConfig.get("attachment.server") + "/uploads/attachment/" + fileStructEntity.getDbid() + "/" + fileStructEntity.getFormid() + "/" + attachmentEntity.getUnid()+"@p@"+attachmentEntity.getSeq();
        String imgUrl="";
         if(fileType.equalsIgnoreCase("image")){
             imgUrl=url+"."+ attachmentEntity.getFileType();
            url+="_80x80."+ attachmentEntity.getFileType();
        }else{
            url+="."+ attachmentEntity.getFileType();
        }
        fileEntity.setDownloadUrl(fileType.equalsIgnoreCase("image")?imgUrl:url);
        fileEntity.setThumbnailFileUrl(fileType.equalsIgnoreCase("image")?url:src);
        fileEntity.setFileSize(fileSizeStr);
        fileEntity.setFileType(fileType);
        return fileEntity;
    }
}

 src/com/yc/crm/clues/action/CluesController.java

@@ -4,6 +4,7 @@
import com.yc.crm.clues.entity.AssignCluesEntity;
import com.yc.crm.clues.entity.CluesRequestEntity;
import com.yc.crm.clues.entity.T481101Entity;
import com.yc.crm.clues.entity.T481104Entity;
import com.yc.crm.clues.service.CluesServiceIfc;
import com.yc.exception.CallBackMessage;
import com.yc.multiData.SpObserver;
@@ -42,6 +43,69 @@
        }
    }
    /**
     * 改变线索状态
     * @param request
     * @return
     */
    @RequestMapping("/changeStatus.do")
    public @ResponseBody Object changeStatus(@RequestBody T481104Entity entity, HttpServletRequest request){
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            entity.setOwnerCode(request.getSession().getAttribute(SessionKey.USERCODE)+"");
            cluesServiceIfc.changeStatus(entity);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        }catch (Exception e){
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        }finally {
            SpObserver.setDBtoInstance();
        }
    }
    /**
     * 关注线索
     * @param request
     * @return
     */
    @RequestMapping("/attention.do")
    public @ResponseBody Object attention(@RequestBody CluesRequestEntity cluesRequest, HttpServletRequest request){
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            cluesRequest.setCreateUsercode(request.getSession().getAttribute(SessionKey.USERCODE)+"");//指定更新当前用户
            cluesServiceIfc.attention(cluesRequest);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        }catch (Exception e){
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        }finally {
            SpObserver.setDBtoInstance();
        }
    }
    /**
     * 删除联系人
     *
     * @param request
     * @return
     */
    @RequestMapping("/contact/del.do")
    public @ResponseBody Object scheduleDel(Integer id, HttpServletRequest request) {
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            cluesServiceIfc.contactDel(id);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
        } catch (Exception e) {
            callBackMessage.sendErrorMessage(this.getErrorMsg(e));
            return callBackMessage.toJSONObject();
        } finally {
            SpObserver.setDBtoInstance();
        }
    }
    /**
     * 线索列表
     * @param request
     * @return
@@ -51,6 +115,8 @@
        CallBackMessage callBackMessage = new CallBackMessage();
        try {
            SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
            cluesRequest.setDbid(request.getSession().getAttribute(SessionKey.DATA_BASE_ID)+"");
            cluesRequest.setFormId(481101);
            callBackMessage.setInfo(cluesServiceIfc.getAllClues(cluesRequest));
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();
@@ -77,6 +143,7 @@
            clues.setCompanyname(request.getSession().getAttribute(SessionKey.COMPANY_NAME)+"");
            clues.setCreateUsercode(request.getSession().getAttribute(SessionKey.USERCODE)+"");
            clues.setCreateUserName(request.getSession().getAttribute(SessionKey.USERNAME)+"");
            clues.setFormId(481101);
                cluesServiceIfc.save(clues);
            callBackMessage.sendSuccessMessageByDefault();
            return callBackMessage.toJSONObject();

 src/com/yc/crm/clues/entity/CluesRequestEntity.java

@@ -11,7 +11,7 @@
@Data
public class CluesRequestEntity extends Page {


    private Integer id;
    private String docCode;
    private Integer formId;
    private String refcode;
@@ -130,4 +130,5 @@
    private String inquiryCountry;//访客IP所在地
    private Integer pinFlag=0;//关注
    private List<T482103Entity> contactsList;//联系人
    private  String dbid;
}

 src/com/yc/crm/clues/entity/T481101Entity.java

@@ -1,5 +1,7 @@
package com.yc.crm.clues.entity;

import com.yc.crm.base.entity.AuditEntity;
import com.yc.crm.base.entity.FileEntity;
import lombok.Data;

import java.util.List;
@@ -130,4 +132,8 @@
    private Integer pageCount;
    private String ccCode;
    private String ccName;
    private  List<FileEntity> files;
    private List<AuditEntity> auditRecords;
    private Integer systemFlag;//系统标签
    private Integer cluesStatusFlag;//线索状态
}

 src/com/yc/crm/clues/entity/T481104Entity.java

@@ -4,18 +4,20 @@

@Data
public class T481104Entity extends T481101Entity {
private Integer id;
private String refCode;
private String ownerCode;
private String ownerName;
private String enterTime;
private String exitTime;
private Integer hasRead;
private String reasonForRollback;
private Double costs;
private Integer hasPinFlag;
private String firstFllowUpTime;
private String lastModifyTime;
private Integer daysNotContacted;
    private Integer id;
    private String refCode;
    private String ownerCode;
    private String ownerName;
    private String enterTime;
    private String exitTime;
    private Integer hasRead;
    private String reasonForRollback;
    private Double costs;
    private Integer hasPinFlag;
    private String firstFllowUpTime;
    private String lastModifyTime;
    private Integer daysNotContacted;



}

 src/com/yc/crm/clues/service/CluesServiceIfc.java

@@ -4,6 +4,7 @@
import com.yc.crm.clues.entity.AssignCluesEntity;
import com.yc.crm.clues.entity.CluesRequestEntity;
import com.yc.crm.clues.entity.T481101Entity;
import com.yc.crm.clues.entity.T481104Entity;

public interface CluesServiceIfc {
    public void save(T481101Entity t481101Entity);
@@ -11,4 +12,10 @@
    Response getAllClues(CluesRequestEntity request);

    void assign(AssignCluesEntity assignClues);

    void attention(CluesRequestEntity cluesRequest);

    void changeStatus(T481104Entity entity);

    void contactDel(Integer id);
}

 src/com/yc/crm/clues/service/CluesServiceImpl.java

@@ -1,8 +1,12 @@
package com.yc.crm.clues.service;

import com.yc.action.grid.GridUtils;
import com.yc.crm.base.entity.AuditEntity;
import com.yc.crm.base.entity.FileStructEntity;
import com.yc.crm.base.entity.Response;
import com.yc.crm.base.util.Utils;
import com.yc.crm.clues.entity.*;
import com.yc.exception.ApplicationException;
import com.yc.service.BaseService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
@@ -13,12 +17,16 @@
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

@Service
public class CluesServiceImpl extends BaseService implements CluesServiceIfc{
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void save(T481101Entity t481101Entity) {
        if(StringUtils.isBlank(t481101Entity.getCluesName())){
            throw  new ApplicationException("线索名称不能为空");
        }
        String sql=" declare " +
                "    @DocCode                  nvarchar(16),\n" +
                "    @FormID                   int,\n" +
@@ -95,10 +103,12 @@
                " \ndeclare " +
                "    @media_type varchar(50),\n" +
                "    @media_refid                 bigint, \n" +
                "    @media_id                 bigint, \n" +
                "    @media_value            varchar(150)\n"+
                " \ndeclare " +
                "    @areaCode varchar(50),\n" +
                "    @telList_refid                 bigint, \n" +
                "    @telList_id                 bigint, \n" +
                "    @telList_tel            varchar(150)\n"+
        //---赋值
                " select " +
@@ -120,6 +130,8 @@
                "@isRead ="+t481101Entity.getIsRead()+",           \n" +//TODO 自己新建，isRead=1,其他人
                "@Email=null,             \n" +
                "@Country ="+ GridUtils.prossSqlParm(t481101Entity.getCountry()) +",         \n" +
                "@province ="+ GridUtils.prossSqlParm(t481101Entity.getProvince()) +",         \n" +
                "@city ="+ GridUtils.prossSqlParm(t481101Entity.getCity()) +",         \n" +
                "@Address ="+ GridUtils.prossSqlParm(t481101Entity.getAddress()) +",         \n" +
                "@PostCode ="+ GridUtils.prossSqlParm(t481101Entity.getCreateUsercode()) +",         \n" +
                "@HDMemo ="+ GridUtils.prossSqlParm(t481101Entity.getHdMemo()) +",         \n" +
@@ -169,6 +181,8 @@
                ",isRead" +
                ",Email" +
                ",Country " +
                ",province " +
                ",city " +
                ",Address" +
                ",PostCode" +
                ",HDMemo" +
@@ -214,6 +228,8 @@
                ",@isRead" +
                ",@Email" +
                ",@Country " +
                ",@province " +
                ",@city " +
                ",@Address" +
                ",@PostCode" +
                ",@HDMemo" +
@@ -250,6 +266,8 @@
                "ModifyName=@ModifyName" +
                ",ModifyDate=@ModifyDate" +
                ",Country=@Country " +
                ",province=@province " +
                ",city=@city " +
                ",Address=@Address" +
                ",HDMemo=@HDMemo" +
                ",clues_name=@clues_name" +
@@ -276,11 +294,21 @@
                //----处理联系人信息
                contactUpdateSql(t481101Entity.getContactsList())   +
                "end\n" +
                "";
                //--处理审计功能
                auditAddSql(t481101Entity);
        //System.out.println("clues:"+sql);
        this.doBaseExecute(sql);
    }

    private String auditAddSql(T481101Entity t481101Entity) {
        List<AuditEntity> list=t481101Entity.getAuditRecords();
        if(list==null||list.size()==0){ return "";}
        String sql=" declare @now datetime=getdate()\n" ;//方便同一时间提交到时输出成一条
        for(AuditEntity audit:list){
            sql+=" insert into _sysAudit([formid],[newvalue],[headflag],[auditType],[fieldname],[mainFormid],[usercode],[oldvalue],[fieldid],[username],[doccode],[auditIndex],[auditDateTime],[auditDate])values("+t481101Entity.getFormId()+","+GridUtils.prossSqlParm(audit.getNewValue())+",0,"+GridUtils.prossSqlParm(audit.getAuditType())+","+GridUtils.prossSqlParm(audit.getFieldName())+","+t481101Entity.getFormId()+","+GridUtils.prossSqlParm(t481101Entity.getCreateUsercode())+","+GridUtils.prossSqlParm(audit.getOldValue())+","+GridUtils.prossSqlParm(audit.getFieldId())+","+GridUtils.prossSqlParm(t481101Entity.getCreateUserName())+",@docCode,null,@now,convert(varchar(10),@now,120))\n";
        }
        return sql;
    }


    @Override
@@ -331,6 +359,7 @@
                ",a.duplicate_flag         \n" +
                ",a.tagList                \n" +
                ",a.homepage               \n" +
                ",a.address               \n" +
                ",a.origin_list            \n" +
                ",a.short_name             \n" +
                ",a.corporate_name         \n" +
@@ -360,6 +389,8 @@
                ",b.firstFllowUpTime  \n" +
                ",b.lastModifyTime   \n" +
                ",b.daysNotContacted " +
                ",b.systemFlag " +
                ",b.cluesStatusFlag " +
                ",@TotalRowCount as totalRowCount ,@pageCount as pageCount              \n";
        String where=" 1=1 ";
        if(request.getSearchTxt()!=null&&request.getSearchTxt().size()>0) {
@@ -414,6 +445,15 @@
                    });
                }
                x.setContactsList(query);
                //附件的输出
                //处理图片，附件，生成调用url及相关信息
                if(StringUtils.isNotBlank(x.getImageList())) {
                    FileStructEntity fileStructEntity = new FileStructEntity();
                    fileStructEntity.setDbid(request.getDbid());
                    fileStructEntity.setFormid(request.getFormId() + "");
                    fileStructEntity.setFileStr(x.getImageList());
                    x.setFiles(Utils.getFileEntities(fileStructEntity));
                }
            });
        }
        Response response=new Response();
@@ -422,7 +462,14 @@
        response.setData(list);
        return response;
    }
   @Transactional(rollbackFor = Exception.class)

    @Override
    public void attention(CluesRequestEntity cluesRequest) {
        String sql="update a set a.pinFlag="+cluesRequest.getPinFlag()+" from t481104h a where id="+cluesRequest.getId();
        this.doBaseExecute(sql);
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void assign(AssignCluesEntity assignClues) {
        String sql=" declare @refCode varchar(20),@ownerCode varchar(20),@ownerName varchar(20)\n" +
@@ -433,6 +480,13 @@
                ",enterTime       \n)values(@refCode,@ownerCode,@ownerName,getdate())";
        this.doBaseExecute(sql);
    }

    @Override
    public void changeStatus(T481104Entity entity) {
        String sql=" update a set a.cluesStatusFlag="+entity.getCluesStatusFlag()+" from t481104h a where id= "+entity.getId();
        this.doBaseExecute(sql);
    }

    /**
     * 拼接查询条件
     * @param request
@@ -452,7 +506,31 @@
                where+=" and isnull(b.pinFlag,0)="+entry.getValue();
            }
            if(entry.getKey().equalsIgnoreCase("ownerCode")){
                where+=" and b.ownerCode = '%"+entry.getValue()+"%'";
                where+=" and b.ownerCode  like '%"+entry.getValue()+"%'";
            }
            if(entry.getKey().equalsIgnoreCase("systemFlag")){
                where+=" and isnull(b.systemFlag,0) = "+entry.getValue()+"";
            }
            if(entry.getKey().equalsIgnoreCase("cluesStatusFlag")){
                where+=" and isnull(b.cluesStatusFlag,0) = "+entry.getValue()+"";
            }
            if(entry.getKey().equalsIgnoreCase("tagList")){
                where+=" and a.tag_List like '%"+entry.getValue()+"%'";
            }
            if(entry.getKey().equalsIgnoreCase("failStatus")){
                where+=" and isnull(a.failStatus,0) = "+entry.getValue()+"";
            }
            if(entry.getKey().equalsIgnoreCase("cluesStatusFlag")){
                where+=" and isnull(b.cluesStatusFlag,0) = "+entry.getValue()+"";
            }
            if(entry.getKey().equalsIgnoreCase("originList")){
                where+=" and a.originList like '%"+entry.getValue()+"%'";
            }
            if(entry.getKey().equalsIgnoreCase("createUsercode")){
                where+=" and a.createUsercode like '%"+entry.getValue()+"%'";
            }
            if(entry.getKey().equalsIgnoreCase("country")){
                where+=" and a.country = '"+entry.getValue()+"'";
            }
            if(entry.getKey().equalsIgnoreCase("lastModifyTime")){
                String[] value=entry.getValue().split(";");
@@ -502,6 +580,21 @@

        }
        return sql;
    }
@Transactional(rollbackFor = Exception.class)
    @Override
    public void contactDel(Integer id) {
        String sql="declare @id int="+id+",@mainCustomerFlag int \n" +
                " select @mainCustomerFlag=isnull(main_Customer_Flag,0) from t482103 where id=@id\n" +
                " if isnull(@mainCustomerFlag,0)=1 \n" +
                " begin \n" +
                "  raiserror('联系人已设置为主要联系人，不能删除',16,1)" +
                "  return" +
                " end\n" +
                " delete from t482116 where refid=@id\n" +
                " delete from t482111 where refid=@id\n" +
                "  delete from t482103 where id=@id";
        this.doBaseExecute(sql);
    }

    /**
@@ -555,8 +648,7 @@
                            ")\n" +
                            " SELECT @last_inserted_id=IDENT_CURRENT('t482103')\n"+
                            mediaAddSql(contact.getMediaList())+
                            telListAddSql(contact.getTelList())
                    ;
                            telListAddSql(contact.getTelList());
        }
        return sql;
    }
@@ -572,6 +664,7 @@
            sql+="\nselect\n" +
                    " @media_type="+GridUtils.prossSqlParm(contact.getType())+"\n" +
                    ",@media_value="+GridUtils.prossSqlParm(contact.getValue())+"\n" +
                    ",@media_id="+contact.getId()+"\n" +
                    ",@media_refid=@last_inserted_id\n" +
                    " insert into t482111(" +
                    "type \n" +
@@ -592,8 +685,16 @@
    private String mediaUpdateSql(List<T482111Entity> mediaEntityList) {
        if(mediaEntityList==null||mediaEntityList.size()==0){ return "";}
        String sql="";
        //增加删除，保证最新
        String collectIds = mediaEntityList.stream().filter(x->x.getId()!=null&&x.getId()!=0).map(x -> x.getId()+"").collect(Collectors.joining(","));
        if(collectIds.equalsIgnoreCase("")){
            //没有修改，都是新增情况
            sql=" delete from t482111 \n";
        }else {
            sql=" delete from t482111 where id not in("+collectIds+")\n";
        }
        for(T482111Entity mediaEntity:mediaEntityList){
            if(mediaEntity.getRefId()==null){
            if(mediaEntity.getId()==null){
                List<T482111Entity> temp=new ArrayList<>();
                temp.add(mediaEntity);
                sql+=mediaAddSql(temp);
@@ -602,10 +703,13 @@
            sql+="\nselect\n" +
                    " @media_type="+GridUtils.prossSqlParm(mediaEntity.getType())+"\n" +
                    ",@media_value="+GridUtils.prossSqlParm(mediaEntity.getValue())+"\n" +
                    ",@media_id="+mediaEntity.getId()+"\n" +
                    "  update t482111 set " +
                    "type=@media_type \n" +
                    ",value=@media_value from t482111 where   refid=@last_inserted_id       \n" ;
                    ",value=@media_value from t482111 where   id=@media_id       \n" ;
        }


        return sql;
    }
    /**
@@ -620,6 +724,7 @@
            sql+="\nselect\n" +
                    " @areaCode="+GridUtils.prossSqlParm(contact.getAreaCode())+"\n" +
                    ",@telList_tel="+GridUtils.prossSqlParm(contact.getTel())+"\n" +
                    ",@telList_id="+contact.getId()+"\n" +
                    ",@telList_refid=@last_inserted_id\n" +
                    " insert into t482116(" +
                    "areaCode \n" +
@@ -640,8 +745,16 @@
    private String telListUpdateSql(List<T482116Entity> telListEntities) {
        if(telListEntities==null||telListEntities.size()==0){ return "";}
        String sql="";
        //增加删除，保证最新
        String collectIds = telListEntities.stream().filter(x->x.getId()!=null&&x.getId()!=0).map(x -> x.getId()+"").collect(Collectors.joining(","));
            if(collectIds.equalsIgnoreCase("")){
                //没有修改，都是新增情况
                sql=" delete from t482116 \n";
            }else {
                sql=" delete from t482116 where id not in("+collectIds+")\n";
            }
        for(T482116Entity telListEntity:telListEntities){
            if(telListEntity.getRefId()==null){
            if(telListEntity.getId()==null){
                List<T482116Entity> temp=new ArrayList<>();
                temp.add(telListEntity);
                sql+=telListAddSql(temp);
@@ -651,10 +764,11 @@
                    " @areaCode="+GridUtils.prossSqlParm(telListEntity.getAreaCode())+"\n" +
                    ",@telList_tel="+GridUtils.prossSqlParm(telListEntity.getTel())+"\n" +
                    ",@telList_refid=@last_inserted_id\n" +
                    ",@telList_id="+telListEntity.getId()+"\n" +
                    " update  t482116 set " +
                    "areaCode=@areaCode \n" +
                    ",tel=@telList_tel            \n" +
                    " from t482116 where refid=@telList_refid\n";
                    " from t482116 where id=@telList_id\n";
        }
        return sql;
    }

 src/com/yc/crm/mail/action/MailController.java

@@ -58,9 +58,15 @@
                    return msg;
                }
            }
            HttpSession session = request.getSession();
            String userCode = (String) session.getAttribute(SessionKey.USERCODE);//当前登录用户
            if (StringUtils.isBlank(userCode)) {//获取不到当前用户直接结束
                msg.setFail("获取不到用户信息");
                return msg;
            }
            DataSourceEntity dataSourceEntity = MultiDataSource.getDataSourceMap(request);//获取数据源信息
            SpObserver.setDBtoInstance("_" + dataSourceEntity.getDbId());//切换数据源
            List<t482101HList> t482101HEntityList = emailIfc.getReceivingMailList(mail, mailType, isNoRead);
            List<t482101HList> t482101HEntityList = emailIfc.getReceivingMailList(mail, mailType, isNoRead, userCode);
            if (t482101HEntityList.size() > 0) {
                msg.setSuccess("执行完成", t482101HEntityList);
            }
@@ -104,8 +110,8 @@
     *
     * @return
     */
    @PostMapping("/deleteReceivingMail.do")
    public AllBackMsg deleteReceivingMail(@RequestBody List<String> docCode, HttpServletRequest request, HttpServletResponse response) throws Exception {
    @PostMapping("/deleteEmail.do")
    public AllBackMsg deleteEmail(@RequestBody List<String> docCode, HttpServletRequest request, HttpServletResponse response) throws Exception {
        AllBackMsg msg = new AllBackMsg();
        try {
            if (docCode == null && docCode.size() == 0) {//获取不到当前用户直接结束
@@ -121,9 +127,9 @@
            }
            DataSourceEntity dataSourceEntity = MultiDataSource.getDataSourceMap(request);//获取数据源信息
            SpObserver.setDBtoInstance("_" + dataSourceEntity.getDbId());//切换数据源
            Integer cont = emailIfc.deleteReceivingMail(userCode, arrayCode);
            Integer cont = emailIfc.deleteEmail(userCode, arrayCode);
            if (cont > 0) {
                msg.setOk("邮件已删除");
                msg.setOk("已删除");
            }
        } catch (Exception e) {
            msg.setFail(e.getCause() != null ? e.getCause().getMessage() : e.getMessage());
@@ -154,7 +160,7 @@
                    return msg;
                }
            }
            if (t482101H.getReceiver()!=null && t482101H.getReceiver().size()>0) {
            if (t482101H.getReceiver() != null && t482101H.getReceiver().size() > 0) {
                for (String mail : t482101H.getReceiver()) {
                    if (!EMAIL_PATTERN.matcher(mail).matches()) {
                        msg.setFail("收件人邮箱：" + mail + "的格式不正确");
@@ -171,6 +177,7 @@
            t482101H.setUserCode(userCode);
            t482101H.setUserName(userName);
            t482101H.setMailType(0);//草稿
            t482101H.setReadFlag(1);//已读
            DataSourceEntity dataSourceEntity = MultiDataSource.getDataSourceMap(request);//获取数据源信息
            SpObserver.setDBtoInstance("_" + dataSourceEntity.getDbId());//切换数据源
            t482101H = emailIfc.saveReceivingMail(t482101H);
@@ -190,34 +197,34 @@
     *
     * @return
     */
    @PostMapping("/deleteMailDrafts.do")
    public AllBackMsg deleteMailDrafts(@RequestBody List<String> docCode, HttpServletRequest request, HttpServletResponse response) throws Exception {
        AllBackMsg msg = new AllBackMsg();
        try {
            if (docCode == null && docCode.size() == 0) {//获取不到当前用户直接结束
                msg.setFail("请选中删除的邮件");
                return msg;
            }
            String arrayCode=StringUtils.join(docCode,",");
            HttpSession session = request.getSession();
            String userCode = (String) session.getAttribute(SessionKey.USERCODE);//当前登录用户
            if (StringUtils.isBlank(userCode)) {//获取不到当前用户直接结束
                msg.setFail("获取不到用户信息");
                return msg;
            }
            DataSourceEntity dataSourceEntity = MultiDataSource.getDataSourceMap(request);//获取数据源信息
            SpObserver.setDBtoInstance("_" + dataSourceEntity.getDbId());//切换数据源
            int cont = emailIfc.deleteMailDrafts(userCode,arrayCode);
            if (cont > 0) {
                msg.setOk("删除成功");
            }
        } catch (Exception e) {
            msg.setFail(e.getCause() != null ? e.getCause().getMessage() : e.getMessage());
        } finally {
            SpObserver.setDBtoInstance();
        }
        return msg;
    }
//    @PostMapping("/deleteMailDrafts.do")
//    public AllBackMsg deleteMailDrafts(@RequestBody List<String> docCode, HttpServletRequest request, HttpServletResponse response) throws Exception {
//        AllBackMsg msg = new AllBackMsg();
//        try {
//            if (docCode == null && docCode.size() == 0) {//获取不到当前用户直接结束
//                msg.setFail("请选中删除的邮件");
//                return msg;
//            }
//            String arrayCode = StringUtils.join(docCode, ",");
//            HttpSession session = request.getSession();
//            String userCode = (String) session.getAttribute(SessionKey.USERCODE);//当前登录用户
//            if (StringUtils.isBlank(userCode)) {//获取不到当前用户直接结束
//                msg.setFail("获取不到用户信息");
//                return msg;
//            }
//            DataSourceEntity dataSourceEntity = MultiDataSource.getDataSourceMap(request);//获取数据源信息
//            SpObserver.setDBtoInstance("_" + dataSourceEntity.getDbId());//切换数据源
//            int cont = emailIfc.deleteEmail(userCode, arrayCode);
//            if (cont > 0) {
//                msg.setOk("删除成功");
//            }
//        } catch (Exception e) {
//            msg.setFail(e.getCause() != null ? e.getCause().getMessage() : e.getMessage());
//        } finally {
//            SpObserver.setDBtoInstance();
//        }
//        return msg;
//    }

    /**
     * 发送邮件
@@ -242,7 +249,7 @@
                    return msg;
                }
            }
            if (t482101H.getReceiver()!=null && t482101H.getReceiver().size()>0) {
            if (t482101H.getReceiver() != null && t482101H.getReceiver().size() > 0) {
                for (String mail : t482101H.getReceiver()) {
                    if (!EMAIL_PATTERN.matcher(mail).matches()) {
                        msg.setFail("收件人邮箱：" + mail + "的格式不正确");

 src/com/yc/crm/mail/action/MailTag.java

@@ -78,7 +78,7 @@
        AllBackMsg msg = new AllBackMsg();
        try {
            if (tag.getTagId() == null) {
                msg.setFail("缺少SignId值，请检查");
                msg.setFail("缺少tagId值，请检查");
                return msg;
            }
            HttpSession session = request.getSession();
@@ -113,7 +113,7 @@
        AllBackMsg msg = new AllBackMsg();
        try {
            if (tagId == 0) {
                msg.setFail("签名ID获取不到");
                msg.setFail("标签ID获取不到");
                return msg;
            }
            HttpSession session = request.getSession();

 src/com/yc/crm/mail/service/MailIfc.java

@@ -54,14 +54,14 @@
     * @param docCode
     * @return
     */
    Integer deleteReceivingMail(String userCode,String docCode);
    Integer deleteEmail(String userCode,String docCode);

    /**
     * 删除邮件(根据messageId删除)
     * 删除邮件(根据messageId)
     * @param messagesId
     * @return
     */
    Integer deleteReceivingMail(String messageId);
    Integer deleteEmail(String messageId);

    /**
     * 获取邮件的详情内容
@@ -78,7 +78,7 @@
     * @param isRead 是否已读
     * @return
     */
    List<t482101HList> getReceivingMailList(String email, Integer mailType, boolean isNoRead);
    List<t482101HList> getReceivingMailList(String email, Integer mailType, boolean isNoRead,String userCode);


    /**
@@ -110,7 +110,7 @@
     * @param docCode
     * @return
     */
    Integer deleteMailDrafts(String userCode,String docCode);
//    Integer deleteMailDrafts(String userCode,String docCode);

    /**
     * 邮件附件的调用处理

 src/com/yc/crm/mail/service/MailImpl.java

@@ -121,7 +121,7 @@
                    "cc=" + GridUtils.prossSqlParm(StringUtils.join(mail.getCc(), ",")) + ",bcc=" + GridUtils.prossSqlParm(StringUtils.join(mail.getBcc(), ",")) + ",subject=" + GridUtils.prossSqlParm(mail.getSubject()) + "," +
                    "content=" + GridUtils.prossSqlParm(mail.getContent()) + ",file_unid=" + GridUtils.prossSqlParm(mail.getFileUNID()) + ",mailType=" + mail.getMailType() + "," +
                    "update_time=getdate() \n";
            sql += " where docCode=" + GridUtils.prossSqlParm(mail.getDocCode())+"\n";
            sql += " where docCode=" + GridUtils.prossSqlParm(mail.getDocCode()) + "\n";
            sql += "select @@ROWCOUNT";
            return jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {
@@ -136,7 +136,7 @@
            sql += "declare @docCode varchar(200) ='" + docCode + "' \n";
            sql += "update t482101H deleteFlag=1 where userCode =" + GridUtils.prossSqlParm(userCode) +
                    " and docCode in (select list from GetInStr(@docCode))\n";//标记删除
            sql += "select select @@ROWCOUNT";
            sql += " select @@ROWCOUNT";
            return jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {
            throw e;
@@ -144,13 +144,13 @@
    }

    @Override
    public Integer deleteReceivingMail(String userCode, String docCode) {
    public Integer deleteEmail(String userCode, String docCode) {
        String sql = "set nocount on\n";
        try {
            sql += "declare @docCode varchar(200) ='" + docCode + "' \n";
            sql += "delete t482101H where userCode =" + GridUtils.prossSqlParm(userCode) +
                    " and docCode in (select list from GetInStr(@docCode))\n";//标记删除
            sql += "select select @@ROWCOUNT";
            sql += " select @@ROWCOUNT";
            return jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {
            throw e;
@@ -158,12 +158,12 @@
    }

    @Override
    public Integer deleteReceivingMail(String messageId) {
    public Integer deleteEmail(String messageId) {
        String sql = "set nocount on\n";
        try {
            sql += "declare @messageId varchar(200) ='" + messageId + "' \n";
            sql += "delete t482101H where messageId in (select list from GetInStr(@messageId))\n";//删除
            sql += "select select @@ROWCOUNT";
            sql += " select @@ROWCOUNT";
            return jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {
            throw e;
@@ -174,8 +174,9 @@
    public t482101HEntity getReceivingMailInfo(String docCode) {
        String sql = "set nocount on\n";
        try {
            sql += "select top 1 DocCode,sender,receiver,subject,content,file_unid as fileUNID,messageId," +
                    "read_flag,delete_flag,create_time,update_time,receive_time from t482101H " +
            sql += "select top 1 DocCode,companyId,companyName,sender,receiver,mailType,subject,content,plain_text,file_unid as fileUNID,messageId," +
                    "read_flag,delete_flag,attach_flag,urgent_flag,receipt_flag,track_flag,userCode,userName,create_time,update_time," +
                    "send_status,receive_time,sign_id,attachment_list,remark,remark,DocVersion from t482101H " +
                    "where docCode=" + GridUtils.prossSqlParm(docCode);
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(t482101HEntity.class)).get(0);
        } catch (Exception e) {
@@ -184,16 +185,21 @@
    }

    @Override
    public List<t482101HList> getReceivingMailList(String email, Integer mailType, boolean isNoRead) {
    public List<t482101HList> getReceivingMailList(String email, Integer mailType, boolean isNoRead, String userCode) {
        String sql = "set nocount on\n";
        try {
            sql += "select companyId,companyName,mailType,DocCode,sender,receiver,subject,plain_text," +
                    "receive_time,update_time,create_time,sender,userCode,userName,folder_id,delete_Flag, " +
                    "read_Flag from t482101H ";
            if (isNoRead) {//未读执行
                sql += "where receiver ='" + email + "' and  isnull(readFlag,0)= 0";
            } else {//已读
                sql += "where receiver ='" + email + "' and  mailType=" + mailType;
            sql += " where userCode=" + GridUtils.prossSqlParm(userCode);
            sql += " and isnull(read_flag,0) = " + (isNoRead ? 0 : 1);//0表示未读，1表示已读
            sql += " and mailType=" + mailType;//0：草稿箱 1：收件箱 2：发件箱
            if (StringUtils.isNotBlank(email)) {
                if (mailType == 1) {
                    sql += " and receiver like '%" + email + "%'";
                } else if (mailType == 2) {
                    sql += " and sender =" + GridUtils.prossSqlParm(email);
                }
            }
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(t482101HList.class));
        } catch (Exception e) {
@@ -360,26 +366,26 @@
                    "content=" + GridUtils.prossSqlParm(mail.getContent()) + ",file_unid=" + GridUtils.prossSqlParm(mail.getFileUNID()) + ",mailType=" + mail.getMailType() + "," +
                    "updateTime=getdate() \n";
            sql += " where docCode=" + GridUtils.prossSqlParm(mail.getDocCode());
            sql += "select select @@ROWCOUNT";
            sql += " select @@ROWCOUNT";
            return jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public Integer deleteMailDrafts(String userCode, String docCode) {
        String sql = "set nocount on\n";
        try {
            sql += "declare @docCode varchar(200) ='" + docCode + "' \n";
            sql += "delete t482101H where userCode =" + GridUtils.prossSqlParm(userCode) +
                    " and docCode in (select list from GetInStr(@docCode)) \n";
            sql += "select select @@ROWCOUNT";
            return jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {
            throw e;
        }
    }
//    @Override
//    public Integer deleteMailDrafts(String userCode, String docCode) {
//        String sql = "set nocount on\n";
//        try {
//            sql += "declare @docCode varchar(200) ='" + docCode + "' \n";
//            sql += "delete t482101H where userCode =" + GridUtils.prossSqlParm(userCode) +
//                    " and docCode in (select list from GetInStr(@docCode)) \n";
//            sql += " select @@ROWCOUNT";
//            return jdbcTemplate.queryForObject(sql, Integer.class);
//        } catch (Exception e) {
//            throw e;
//        }
//    }

    /**
     * 收件箱返回的信息进行封装处理
@@ -486,7 +492,7 @@
            }
            if (deleteMsgId.size() > 0) {
                //删除
                deleteReceivingMail(StringUtils.join(deleteMsgId, ","));
                deleteEmail(StringUtils.join(deleteMsgId, ","));
            }
            return t482101HEntityList;
        } catch (Exception e) {
@@ -640,7 +646,8 @@
    public void updateRead(String docCode) {
        String sql = " set nocount on \n";
        try {
            sql += "update t482101H set read_flag=1 where docCode in (" + GridUtils.prossSqlParm(docCode) + ")\n";
            sql += "declare @docCode varchar(200) ='" + docCode + "' \n";
            sql += "update t482101H set read_flag=1 where docCode in (select list from GetInStr(@docCode))\n";
            sql += "select @@ROWCOUNT";
            jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {

 src/com/yc/open/config/APIBeanConfig.java

@@ -1,14 +1,15 @@
package com.yc.open.config;

import com.google.code.kaptcha.impl.DefaultKaptcha;
import com.google.code.kaptcha.util.Config;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.scheduling.concurrent.ThreadPoolTaskScheduler;

import java.util.Properties;
import java.util.concurrent.Executor;
import java.util.concurrent.RejectedExecutionHandler;
import java.util.concurrent.ThreadPoolExecutor;
@@ -52,5 +53,22 @@
        executor.initialize();
        return executor;
    }

    @Bean
    public DefaultKaptcha defaultKaptcha() {//图片验证码
        DefaultKaptcha defaultKaptcha = new DefaultKaptcha();
        Properties properties = new Properties();
        properties.put("kaptcha.border", "no");
        properties.put("kaptcha.border.color", "black");
        properties.put("kaptcha.textproducer.font.color", "black");
        properties.put("kaptcha.textproducer.font.size", "32");
        properties.put("kaptcha.textproducer.char.length", "4");
        properties.put("kaptcha.textproducer.char.space", "5");
        properties.put("kaptcha.textproducer.char.length", "4");
        properties.put("kaptcha.image.width", "110");
        properties.put("kaptcha.image.height", "40");
        properties.put("kaptcha.textproducer.char.string","ABCDEFGHJKLMNPQRSTUVWXYZ1234567890");
        Config config=new Config(properties);
        defaultKaptcha.setConfig(config);
        return defaultKaptcha;
    }
}

 src/com/yc/open/init/shcedule/AttendanceReMindSchedule.java

@@ -82,7 +82,7 @@
            }
            if (seqList.size() > 0) {
                //更新可发送次数
                attendanceServiceIfc.updateT180252(seqList);
                attendanceServiceIfc.updateT180252(seqList,dataSourceEntity.getDbId()+"");
            }
        } catch (InterruptedException inter) {
            log.info(dataSourceEntity.getSystemID() + "考勤定时任务已被终止");

 src/com/yc/phoneQRLogin/action/verification.java

@@ -1,34 +1,30 @@
package com.yc.phoneQRLogin.action;

import java.io.IOException;
import java.util.Properties;
import java.util.concurrent.TimeUnit;
import javax.mail.BodyPart;
import javax.mail.Message;
import javax.mail.Multipart;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.yc.service.panel.DiBangIfc;
import com.yc.action.BaseAction;
import com.yc.duanxin.Sms;
import com.yc.duanxin.SmsParm;
import com.yc.exception.ApplicationException;
import com.yc.factory.FactoryBean;
import com.yc.multiData.SpObserver;
import com.yc.sdk.shopping.service.imagedata.ShoppingImageDataIfc;
import com.yc.service.BaseService;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.yc.action.BaseAction;
import com.yc.duanxin.Sms;
import com.yc.duanxin.SmsParm;
import com.yc.multiData.SpObserver;
import com.yc.sdk.shopping.action.api.GenerationQrCodeForApp;
import com.yc.sdk.shopping.entity.QrCodeForAppEntity;
import com.yc.sdk.shopping.service.imagedata.ShoppingImageDataIfc;

import javax.mail.*;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Properties;
import java.util.concurrent.TimeUnit;

/**
 * 用来执行手机、Email验证码
@@ -44,7 +40,26 @@
    RedisTemplate redisTemplate;
    @Autowired
    ShoppingImageDataIfc imgData;

    /**
     * 检查是否存在ERP系统
     * @param telephone
     * @return
     * @throws Exception
     */
    public static boolean checkUserRegisteredByERP(String telephone,String dbid)throws Exception {
        try {
            SpObserver.setDBtoInstance("_"+dbid);
            BaseService baseService = (BaseService) FactoryBean.getBean("BaseService");
            Integer result = baseService.getJdbcTemplate().queryForObject("declare @num int\n" +
                    "select @num=count(1) from _sys_LoginUser where tel='" + telephone + "'\n" +
                    "select @num", Integer.class);
            return result != null && result > 0;
        }catch (Exception ex){
            throw new ApplicationException(ex.getMessage());
        }finally {
            SpObserver.setDBtoInstance();
        }
    }
    /**
     * 手机发送6位随机验证码 xin 2020-5-19 16:17:17
     * 
@@ -62,6 +77,34 @@
            if(StringUtils.isBlank(telephone)){
                throw new Exception("手机号不能为空");
            }
            String captcha = request.getParameter("captcha");
            String key = request.getParameter("key");
            if(StringUtils.isBlank(captcha)){
                throw new Exception("图片验证码不能为空");
            }
            if(StringUtils.isBlank(key)){
                throw new Exception("图片验证码key不能为空");
            }
            Object o = redisTemplate.opsForValue().get(key);
            if(o == null){
                throw new ApplicationException("图片验证码已失效，请重新刷新");
            }else {
                String txt=o.toString();
                if(!captcha.equalsIgnoreCase(txt)){
                    throw new ApplicationException("图片验证码不正确");
                }
            }
            //验证是否已在系统存在这个手机号
            //"您的手机号[" + telephone + "】还没注册，不能发送请求!"
            try{
            if(!checkUserRegisteredByERP(telephone,dbId)){
                this.printJson(response, "{\"code\":-1,\"info\":\"您的手机号[" + telephone + "]还没注册，不能发送请求!\"}");
                return;
            }
            }catch (Exception ex){
                this.printJson(response, "{\"code\":-1,\"info\":\"" + ex.getMessage() + "\"}");
              return;
            }
            String isRepeat= (String) redisTemplate.opsForValue().get("repeat:" + telephone + ":" + dbId);
            if(StringUtils.isNotBlank(isRepeat)){//存在表示在一分钟内还没有过期，不能再次发送短信 xin 2023-4-17 09:57:02
                throw new Exception("在一分钟内，请不要重复发送");

 src/com/yc/service/datacheck/DataCheckImpl.java

@@ -417,8 +417,9 @@

    private String getPanDuan(String chaochu, String diyu, Map<String, Object> map) {
        String str = "";
        String chaochuPan = (GridUtils.prossRowSetDataType_String(map, chaochu).equals("0")) ? "0=0" : "1=0";
        String diyuPan = (GridUtils.prossRowSetDataType_String(map, diyu).equals("0")) ? "0=0" : "1=0";
        //equals("1")=true表示需要弹出提示窗口，所以0=0组合条件才能返回1 by danaus 2024-08-23 10:01
        String chaochuPan = (GridUtils.prossRowSetDataType_String(map, chaochu).equals("1")) ? "0=0" : "1=0";
        String diyuPan = (GridUtils.prossRowSetDataType_String(map, diyu).equals("1")) ? "0=0" : "1=0";
        str = "( " + chaochuPan + " and @rtnvalue > 0 ) or (" + diyuPan + " and @rtnvalue < 0 ) or (1=0 and @rtnvalue = 0 )";// 两个等于的情况总是1=0为假
        return str;
    }

 src/com/yc/service/panel/v2/PanelServiceImpl.java

@@ -190,13 +190,13 @@

            // 把需要导出的写到excel
            String filename = "/panel_data";
            if (formType == 2) {
            if (formType == 2||formType==20) {
                filename = "/data";// 是为了作为格线数据来处理
            }
            if (pHelper.excelContext.length() > 0) {
                try {

                    FileUtil.writeFile((formType == 2 ? gformInfo.get("formname") + "-" : "") + pHelper.excelContext + "-"
                    FileUtil.writeFile(((formType == 2||formType == 20) ? gformInfo.get("formname") + "-" : "") + pHelper.excelContext + "-"
                            + pHelper.tableIs + "-" + pHelper.excelField + "-" + pHelper.excelLoca + "-"
                            + pHelper.excelTitles, pHelper.URI + pHelper.realPath + "/" + formType + filename);// 写到文件里面
                    pHelper.isto = true;