crm线索列表接口及代码整理

fs-danaus

2024-08-09 7204e3dff0490732e861ccd1338e3e3c31d768c6

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

@@ -41,19 +41,35 @@
        //以9676为例
        //1--读取9771的配置信息，决定如何加载子功能号数据
        BaseService baseService = (BaseService) FactoryBean.getBean("BaseService");
        List<T9771Entity> list9771 = baseService.getJdbcTemplate().query("select mainformid,mainformname,sortBy,a.formid,a.formname,formtype,labelName,a.fT,a.fK,a.seekGroupID,tabID,\n" +
        List<T9771Entity> list9771 = baseService.getJdbcTemplate().query("set nocount on \n" +
                " declare @formid int=?,@myCount int\n" +
                " select @myCount=count(1) from  _sys_TabPageFormid where formid=@formid \n" +
                " if @myCount=0  \n" +
                "  begin \n" +
                "    raiserror('%d功能号在9771查找不到有对应主功能号，请到9771维护',16,1,@formid)\n" +
                "    return\n" +
                "   end \n" +
                " if @myCount>1  \n" +
                "  begin \n" +
                "    raiserror('%d功能号在9771存在多条对应主功能号记录',16,1,@formid)\n" +
                "    return\n" +
                "   end \n" +
                " select mainformid,mainformname,sortBy,a.formid,a.formname,formtype,labelName,a.fT,a.fK,a.seekGroupID,tabID,\n" +
                " case when a.ft=mainformid then 1   else 0 end as alone,b.hdtable\n" +
                " from _sys_TabPageFormid a join gform b on a.formid=b.formid \n" +
                " where mainformid=(select mainformid from  _sys_TabPageFormid where formid=?) and isnull(Actived,0)=1  order by SortBy asc\n", new BeanPropertyRowMapper<>(T9771Entity.class),page.getFormid());
                " where mainformid=(select top 1 mainformid from  _sys_TabPageFormid where formid=@formid) and isnull(Actived,0)=1  order by SortBy asc\n", new BeanPropertyRowMapper<>(T9771Entity.class),page.getFormid());
        if(list9771!=null&&list9771.size()>0){
            //2---取所有功能号id，取出对应9802数据
          final   StringJoiner formidsJoiner=new StringJoiner(",");//取得9676,9677,9678,9679
          final StringBuilder mainKey=new StringBuilder(";");//主功能号的主键
           list9771.stream()
                    .forEach(x->{
                        formidsJoiner.add(x.getFormid()+"");
                        if(x.formid.equals(x.mainformid)){
            final StringJoiner formidsJoiner = new StringJoiner(",");//取得9676,9677,9678,9679
            final StringBuilder mainKey = new StringBuilder(";");//主功能号的主键
            //处理关联功能号之间的字段和主功能号不同的情况，虽要取得主功能号里对应的数据传给子功能号进行where条件查询
            final StringBuilder mainPk = new StringBuilder();
            list9771.stream()
                    .forEach(x -> {
                        formidsJoiner.add(x.getFormid() + "");
                        if (x.formid.equals(x.mainformid)) {
                            mainKey.append(x.fK);
                            mainPk.append(x.fK);
                        }
                    });
            List<T9802Entity> list9802 = baseService.getJdbcTemplate().query(" select a.formid,a.fieldid,a.fieldname,gridcaption,displayformat,isnull(gridControlType,0) as gridControlType,isnull(controlType,0) as controlType,isnull(rowNo,0) as rowNo,isnull(colNo,0) as colNo,isnull(lengthNum,0) as lengthNum,isnull(heightNum,0) as heightNum,isnull(exportTitle,0) as exportTitle,showFieldValueExpression,isnull(sumField,0) as sumField,isnull(activefuns,0) as activefuns,funclinkname,b.index1 from gField a join gform b on a.formid=b.formid\n" +
@@ -63,29 +79,29 @@
          final List<Exprot496Entiry> exprot496list=new ArrayList<>();
            list9771.stream()
                    .filter(x->x.getAlone()==1)
                    .forEach(alone->{
                    .forEach(alone-> {

                        if(!alone.formid.equals(alone.mainformid)) {
                        //     if(!alone.formid.equals(alone.mainformid)) {
                        //不是第一个主功能号，取所有关联子功能数据，关联字段名称一起查询
                        StringJoiner MainfkKey = new StringJoiner(",");
                        list9771.stream().forEach(z -> {
                            if (alone.formid.equals(z.fT)) {
                                MainfkKey.add(z.fK);
                            }
                        });
                        alone.subFkKey = MainfkKey.toString();
                        //  }
                        Exprot496Entiry entiry = loadData(request, list9802, alone, page, mainPk, exprot496list,null);
                        //判断是否有关联的子功能，有则全都加载过来
                        if (!alone.formid.equals(alone.mainformid)) {
                            //不是第一个主功能号，取所有关联子功能数据
                            StringJoiner fkKey=new StringJoiner(",");
                            list9771.stream().forEach(z -> {
                                if (alone.formid.equals(z.fT)) {
                                   fkKey.add(z.fK);
                                }
                            });
                            alone.subFkKey =fkKey.toString();
                        }
                        Exprot496Entiry entiry = loadData(request,list9802, alone, page);
                        //判断是否有关联的子功能，有则全都加载过来
                        if(!alone.formid.equals(alone.mainformid)){
                            //不是第一个主功能号，取所有关联子功能数据
                            list9771.stream().forEach(z->{
                              if(alone.formid.equals(z.fT)) {
                                  //表示所属的子功能号，需要取数
                                  //判断关联的主外键情况:
                                  // 1,一种情况是主功能号用的主键在如果下面的子功能号都存在，取数可以直接用这个主键把所有相关的数据都取回来，再过滤。这样查数只需要一次性取回来。提高性能
                                  //2--子功能号的主外键关系没用到主功能号的主键，这种情况需要从已查出来的数据取得所有相关的主键数据，再一次性取数回来
                                   String finalWhere=page.getWhere();
                                    //表示所属的子功能号，需要取数
                                    //判断关联的主外键情况:
                                    // 1,一种情况是主功能号用的主键在如果下面的子功能号都存在，取数可以直接用这个主键把所有相关的数据都取回来，再过滤。这样查数只需要一次性取回来。提高性能
                                    //2--子功能号的主外键关系没用到主功能号的主键，这种情况需要从已查出来的数据取得所有相关的主键数据，再一次性取数回来
                                    String finalWhere = page.getWhere();
                                  if(!z.fK.contains(mainKey.toString())){
                                      //判断是否存在多个键值 acccode;rowid，因为rowid是限制匹配取数，需要去掉
                                      String[] fkKey = z.fK.toLowerCase().split(";");//主功能号字段
@@ -122,7 +138,7 @@
                                       finalWhere=subPkKey+" in("+ valuekeys.toString()+")";
                                    }
                                  page.setWhere(finalWhere);
                                  Exprot496Entiry subList = loadData(request,list9802, z, page);
                                    Exprot496Entiry subList = loadData(request, list9802, z, page, mainPk, exprot496list,entiry);
                                  entiry.subList.add(subList);
                              }
                            });
@@ -154,25 +170,27 @@
    }



    /**
     * 根据9771，9802定义，拼接得到sql,取数
     *
     * @param where
     * @param list9802
     * @param alone
     * @param where
     * @param mainPk
     * @param exprot496list
     * @return
     */
    private  Exprot496Entiry loadData(HttpServletRequest request,List<T9802Entity> list9802,T9771Entity alone,Page page){
    private Exprot496Entiry loadData(HttpServletRequest request, List<T9802Entity> list9802, T9771Entity alone, Page page, StringBuilder mainPk, List<Exprot496Entiry> exprot496list,Exprot496Entiry currentExprot496Entiry) {
        //取9676字段列表数据
        final   StringJoiner fieldsJoiner=new StringJoiner(",");//字段列表
        final   StringJoiner expressionJoiner=new StringJoiner(";");//权限表达式
        final   StringJoiner picFields=new StringJoiner(";");//图片字段
        final   StringJoiner titleToFile=new StringJoiner(";");//图片字段作为文件名称
        final   StringJoiner tbColsJoiner=new StringJoiner(",");//计算用-汇总列字段
        final   StringJoiner tbColsExcelJoiner=new StringJoiner(";");//excel导出用-汇总列字段
        final StringJoiner fieldsJoiner = new StringJoiner(",");//字段列表
        final StringJoiner expressionJoiner = new StringJoiner(";");//权限表达式
        final StringJoiner picFields = new StringJoiner(";");//图片字段
        final StringJoiner titleToFile = new StringJoiner(";");//图片字段作为文件名称
        final StringJoiner tbColsJoiner = new StringJoiner(",");//计算用-汇总列字段
        final StringJoiner tbColsExcelJoiner = new StringJoiner(";");//excel导出用-汇总列字段
        //---排序
        final   StringJoiner orderByJoiner=new StringJoiner(",");//排序字段
        final  StringJoiner fristField=new StringJoiner("");
        final StringJoiner orderByJoiner = new StringJoiner(",");//排序字段
        final StringJoiner fristField = new StringJoiner("");
        AtomicInteger fumIndex = new AtomicInteger();//标记导出时候字段位置
        AtomicInteger tabColIndex = new AtomicInteger();//标记导出字段在汇总列的位置
        fumIndex.set(0);
@@ -220,26 +238,57 @@
                fumIndex.getAndIncrement();
            }
            //处理权限表达式
            if(StringUtils.isNotBlank(k.showFieldValueExpression)){
                expressionJoiner.add(k.fieldid.toLowerCase()+"|"+k.showFieldValueExpression);
            if (StringUtils.isNotBlank(k.showFieldValueExpression)) {
                expressionJoiner.add(k.fieldid.toLowerCase() + "|" + k.showFieldValueExpression);
            }
                });
        });
        //1，当前功能号是子功能号：把关联的字段也放进去查询，避免这些字段没选上导出，到时处理不了关系
        //2--当前功能号是主功能号，需要提前把子功能号的fk字段取回来，让主功能号查询
        //需要去重处理
        if (fieldsJoiner.length() == 0) {
            //没设置导出字关段则取关联字段作为导出字段
            fieldsJoiner.add(alone.seekGroupID.replaceAll(";", ","));

        fieldsJoiner.add(alone.seekGroupID.replaceAll(";",","));
        if(StringUtils.isNotBlank(alone.subFkKey)){
            fieldsJoiner.add(alone.subFkKey.replaceAll(";",","));
        }
        String tempFields = "," + fieldsJoiner.toString().toLowerCase() + ",";
        if (!tempFields.contains("," + alone.seekGroupID.toLowerCase() + ",")) {
            fieldsJoiner.add(alone.seekGroupID.replaceAll(";", ","));
        }
        if (StringUtils.isNotBlank(alone.subFkKey)) {
            String[] subFkKeys = alone.subFkKey.replaceAll(";", ",").split(",");
            for (String subFkKey : subFkKeys) {
                tempFields = "," + fieldsJoiner.toString().toLowerCase() + ",";
                if (!tempFields.contains("," + subFkKey.toLowerCase() + ",")) {
                    fieldsJoiner.add(subFkKey);
                }
            }
        }
        if (mainPk != null && mainPk.length() > 0) {
            //判断当前功能号的where是否需要替换
            if (!alone.seekGroupID.equalsIgnoreCase(mainPk.toString())) {
                //不相同,需要从主功能号里取数拼接where, 第一个是主功能号数据
                if (exprot496list.size() > 0) {
                    String[] seekGroup= alone.seekGroupID.toLowerCase().replaceAll("rowid","").replaceAll("detailrowid","").split(";");//去掉rowid,detailrowid,因为这些只是限制条件，实际导出所有，所以取数可以过滤这些特定字段(rowid,detailrowid)
                    StringJoiner where=new StringJoiner(" and ");
                    Exprot496Entiry entiry496=exprot496list.get(0);
                    if(currentExprot496Entiry!=null){
                        entiry496=currentExprot496Entiry;
                    }
                    for(String str:seekGroup) {
                      where.add(str+ "=" + GridUtils.prossSqlParm(entiry496.data.get(0).get(str) + ""));
                    }
                    page.setWhere(where.toString());
                }
            }
        }
        String sqlList = Arrays.stream(fieldsJoiner.toString().split(",")).distinct().collect(Collectors.joining(","));
        //--处理权限表达式
        Page newpage=new Page();
        Page newpage = new Page();
        newpage.setExpr(expressionJoiner.toString());
        newpage.setTbCols(Base64.getEncoder().encodeToString(tbColsJoiner.toString().getBytes(StandardCharsets.UTF_8)));
        newpage.setTbCols(gridService.proccTbCols(newpage));
        String sql = "["+sqlList.replaceAll(",","],[")+"]";
             sql=   this.exprTOSql(newpage, sql);
        String sql = "[" + sqlList.replaceAll(",", "],[") + "]";
        sql = this.exprTOSql(newpage, sql);
        //取9676的数据
        try {
            SpObserver.setDBtoInstance("_" + env.get(SessionKey.DATA_BASE_ID));
@@ -254,27 +303,32 @@
            newpage.setAutopaging(2);//不分页
            newpage.setId(fristField.toString());
            newpage.setPageNum(1);
            newpage.setOrderBy(orderByJoiner.length()>0?orderByJoiner.toString().replaceAll(";",","):"");
            newpage.setOrderBy(orderByJoiner.length() > 0 ? orderByJoiner.toString().replaceAll(";", ",") : alone.seekGroupID.replaceAll(";",","));
            gridService.loadAll(newpage);// 第二步，调用相对应业务类取得数据,分页功能
        } finally {
        }catch (Exception ex) {
            throw ex;
        }finally {
            SpObserver.setDBtoInstance();
        }
    //    List<Map<String, Object>> list = baseService.getJdbcTemplate().queryForList(" select " + sql+ " from " + alone.getHdtable() + " " + where);

        entiry.data=newpage.getData();//自身数据
        entiry.page=newpage;
        entiry.metaData=collect;
        entiry.mainFormName=alone.mainformname;
        entiry.mainFormid=alone.mainformid;
        entiry.picFields=picFields.toString();
        entiry.formid=alone.formid;
        entiry.formName= StringUtils.isNotBlank(alone.labelName)?alone.labelName:alone.formname;
        entiry.titleToFileName=titleToFile.toString();
        entiry.fk=alone.fK;
        entiry.seekGroupID=alone.seekGroupID;
        entiry.sheetName=alone.labelName==null?alone.formname: alone.labelName;
        entiry.exprotType=alone.formtype.equals(16)?ExprotType.Panel:ExprotType.Grid;
        entiry.tbCols=tbColsExcelJoiner.toString();
        entiry.metaData = collect;
        entiry.mainFormName = alone.mainformname;
        entiry.mainFormid = alone.mainformid;
        entiry.picFields = picFields.toString();
        entiry.formid = alone.formid;
        entiry.formName = StringUtils.isNotBlank(alone.labelName) ? alone.labelName : alone.formname;
        entiry.titleToFileName = titleToFile.toString();
        entiry.fk = alone.fK;
        entiry.seekGroupID = alone.seekGroupID;
        entiry.sheetName = StringUtils.isBlank(alone.labelName) ? alone.formname : alone.labelName;
        if (entiry.sheetName.contains("/")) {
            entiry.sheetName = entiry.sheetName.replaceAll("/", "-");
        }
        entiry.exprotType = alone.formtype.equals(16) ? ExprotType.Panel : ExprotType.Grid;
        entiry.tbCols = tbColsExcelJoiner.toString();
        return entiry;
    }
}