重大改版
 鱼翔空 2012-09-22 多表组合查询|   0 1244 
文章评分 90 次,平均分 5.0

综合查询(四)后台功能模板

/**
 * 综合查询action 
 */
public class ZhSearchAction extends StrutsEntityAction<HtInfo, HtbaManager> {
    @Override
    public HtbaManager getEntityManager() {
        return SpringContextHolder.getBean("htbaManager");
    }
    @Override
    protected Class<HtInfo> getEntityClass() {
        return HtInfo.class;
    }
    public WyQyInfoManager getWyQyInfoManager() {
        return SpringContextHolder.getBean("wyQyInfoManager");
    }
    public RegionManager getRegionManager() {
        return SpringContextHolder.getBean("regionManager");
    }
    public WyUserInfoManager getWyUserInfoManager() {
        return SpringContextHolder.getBean("wyUserInfoManager");
    }
    public JdbcSearchManager getJdbcSearchManager(){
        return SpringContextHolder.getBean("jdbcSearchManager");
    }
    /**
     * 保存查询条件到模板
     * @return
     */
    public ActionForward saveSearchCondition(ActionMapping actionMapping, ActionForm form,
            HttpServletRequest request, HttpServletResponse response) {
        //封装以search开头的条件到map
        Map<String, Object> params = WebUtils.getParametersStartingWith(request, "search_");
        try {
            //获取登录用户所在区代码
            Map infoMap = new GetUserInfo().getUserInfoMap(request);
            String loginName = (String) infoMap.get("loginName");
            if(StringUtils.isNotBlank(loginName)){
                //获取项目的根路径
                String rootPath=request.getSession().getServletContext().getRealPath("/");
                //获取项目的classes的真实路径
                //当前用户对应的查询模板的真实路径
                String realPath=rootPath+"WEB-INF/userSearchTemplate/"+loginName+".xml";
                File file=new  File(realPath);
                if(!file.exists()){//如果不存在创建
                   if(!file.createNewFile())
                       throw new Exception("文件不存在,创建失败!");
                    //创建一篇文档
                    Document docSource = DocumentHelper.createDocument();
                    //添加根元素
                    Element root = docSource.addElement("searchCondition");
                    //将创建带根元素的xml的document写入到文件中
                    OutputFormat  format=new OutputFormat();
                    format.setEncoding("UTF-8");
                    XMLWriter output=new XMLWriter(new FileWriter(file),format);
                    output.write(docSource);
                    output.close();
                }
                String sqlWhere=(String) params.get("sqlWhere");
                String sqlfromTables=(String) params.get("sqlfromTables");
                String tableHead=(String) params.get("tableHead");
                tableHead.replace("\"", "\'");
                String mbName=(String)params.get("mbName");//设置的模板名称
                int max=SearchUtil.findMaxId(realPath)+1;//最大id
                if("未命名".equals(mbName)){
                    mbName=mbName+max;
                }
                //获取document对象
                Document doc=Dom4jUtil.getDocument(realPath);
                Element root=doc.getRootElement();
                                      
                Element searchTemplate=root.addElement("searchTemplate")
                .addAttribute("id", max+"")
                .addAttribute("name", mbName)
                .addAttribute("fromTable", sqlfromTables)
                .addAttribute("tableHead", tableHead);
                                      
                //将json串转成map集合
                JSONObject jb=JSONObject.fromObject(sqlWhere);
                Map<Integer,Object> mapWhere=(Map<Integer, Object>)jb;
                for(int i=0;i<mapWhere.size();i++){
                    Object obj=mapWhere.get(i+"");
                    JSONObject objTrMap=JSONObject.fromObject(obj);
                    Map<String,String> trMap=objTrMap;
                    searchTemplate.addElement("tr")
                    .addAttribute("ljys",trMap.get("ljys"))
                    .addAttribute("zkh",trMap.get("zkh"))
                    .addAttribute("tableName", trMap.get("tableName"))
                    .addAttribute("columnName", trMap.get("columnName"))
                    .addAttribute("gx", trMap.get("gx"))
                    .addAttribute("cxtjz", trMap.get("cxtjz"))
                    .addAttribute("ykh", trMap.get("ykh"));
                }
                                      
                Dom4jUtil.write2xml(doc, realPath);
            }
        }catch(Exception e){
            e.printStackTrace();
            return toSearchPage(actionMapping, form, request, response);
        }
        return toSearchPage(actionMapping, form, request, response);
    }
    /**
     *导出所有的数据到xml中
     * @param actionMapping
     * @return
     */
    public ActionForward import2Excel(ActionMapping actionMapping, ActionForm form,
            HttpServletRequest request, HttpServletResponse response) {
        //封装以search开头的条件到map
        Map<String, Object> params = WebUtils.getParametersStartingWith(request, "search_");
        //生成的excel文件存放在服务器的相对路径
        //String outputFile=request.getRealPath("/tmp/result.xls");
        try {
            //获取登录用户所在区代码
            Map infoMap = new GetUserInfo().getUserInfoMap(request);        
                                  
            //返回结果
            List<Map<String, Object>> resultList = getJdbcSearchManager().resultList(params);
            //将null数据转成"",将不规范的数据转换
            resultList=changeData(resultList);
            String tableHead=(String) params.get("tableHead");
            //将json串转成map集合
            JSONObject jb=JSONObject.fromObject(tableHead);
            Map<String,String> map=(Map)jb;
                                  
            String[] headers=new String[map.size()];
            String[] columns=new String[map.size()];
            initHeaderAndColumns(map,headers,columns);
                                  
            response.setContentType("octets/stream");  
            response.addHeader("Content-Disposition","attachment;filename=result.xls");  
            OutputStream out = response.getOutputStream(); 
                                  
            Export2ExcelUtils.exportExcel("result", headers, columns, resultList, out, "");  
            out.flush();
            out.close();  
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
        return null;
    }
    private List<Map<String, Object>> changeData(List<Map<String, Object>> resultList) {
        //查询到所有的区县,用于显示企业所在区县和项目所在区县
        List<Region> regions = getRegionManager().findNodeList();
        Map<String,String> maps=new HashMap<String, String>();
        for(int j=0;j<regions.size();j++){
            //{code=10, name=东城区}, {code=11, name=西城区}, {code=14, name=朝阳区}, {code=15, name=海淀区}, {code=16, name=丰台区}, {code=17, name=石景山区}, {code=18, name=房山区}, {code=19, name=通州区}, {code=20, name=顺义区}, {code=21, name=昌平区}, {code=22, name=大兴区}, {code=23, name=门头沟区}, {code=24, name=怀柔区}, {code=25, name=平谷区}, {code=26, name=密云县}, {code=27, name=延庆县}, {code=28, name=北京经济技术开发区}]
            Map<String,String> m=(Map<String, String>) regions.get(j);
            maps.put(m.get("code"),m.get("name"));
        }
        List<Map<String, Object>> result=new ArrayList<Map<String,Object>>();
        //在迭代的时候不能有插入操作
        for(Map<String,Object> m:resultList){
            Map<String, Object> mresult=new HashMap<String, Object>();
            for(String str:m.keySet()){
                Object obj=m.get(str);
                obj=changeDefaultData(str,obj);
                if("xmzlssq".equalsIgnoreCase(str)||"zcdzQx".equalsIgnoreCase(str)
                        ||"bgdz".equalsIgnoreCase(str)){
                    //{19=通州区, 17=石景山区, 22=大兴区, 18=房山区, 23=门头沟区, 15=海淀区, 24=怀柔区, 25=平谷区, 16=丰台区, 26=密云县, 27=延庆县, 14=朝阳区, 28=北京经济技术开发区, 11=西城区, 21=昌平区, 20=顺义区, 10=东城区}
                    String strkey=(String) m.get(str);
                    if(StringUtils.isNotBlank(strkey)){
                        if(maps.containsKey(strkey)){//先判断该key在不在里面
                           if("12".equals(strkey)){
                               obj=maps.get("10");
                           }else if("13".equals(strkey)){
                               obj=maps.get("11");
                           }else{
                               obj=maps.get(strkey);
                           }
                        }else{
                            obj="无";
                        }
                    }
                }
                //将修改过的值重新放入到map中
                mresult.put(str, obj);
            }
            result.add(mresult);
        }
        return result;
    }
   private Object changeDefaultData(String column,Object obj){
       if(obj==null){
            obj="无";
        }
        obj=obj.toString();
        if("wylx".equalsIgnoreCase(column)){
            if("-1".equals(obj)){
                obj="无";
            }
        }
        if("WYFWBZ".equalsIgnoreCase(column)){
            if("-1".equals(obj)){
                obj="无";
            }
        }
        if("zxzt".equalsIgnoreCase(column)){
            if("0".equals(obj)){
                obj="未注销";
            }else if("1".equals(obj)){
                obj="已注销";
            }
        }
        if("wtqxlb".equalsIgnoreCase(column)){
            if("1".equals(obj)){
                obj="固定期限合同管理";
            }else if("2".equals(obj)){
                obj="无合同提供事实服务";
            }else if("3".equals(obj)){
                obj="无固定期限合同";
            }else if("4".equals(obj)){
                obj="业主共同决定自管合同";
            }
        }
        if("qylx".equalsIgnoreCase(column)){
            if("1".equals(obj)){
                obj="物业企业";
            }else if("0".equals(obj)){
                obj="建设单位";
            }
        }
        if("sfwp".equalsIgnoreCase(column)){
            if("1".equals(obj)){
                obj="是";
            }else if("0".equals(obj)){
                obj="否";
            }
        }
        if("jdbsc".equalsIgnoreCase(column)||"sqjwh".equalsIgnoreCase(column)){
            if(obj!=null&&StringUtils.isNotBlank(obj.toString())&&obj!="无"){
                if(StringUtils.isNumeric(obj.toString())){
                    obj=getRegionManager().getReginNameByCode(obj.toString());
                    if(obj==null){
                        obj="无";
                    }
                }
            }
        }
        if("isMember".equalsIgnoreCase(column)){
            if("1".equals(obj)){
                obj="是";
            }else if("0".equals(obj)){
                obj="否";
            }
        }
        if("sfhy".equalsIgnoreCase(column)){
            if("0".equals(obj)){
                obj="是";
            }else if("1".equals(obj)){
                obj="否";
            }
        }
        if("sfzj".equalsIgnoreCase(column)){
            if("0".equals(obj)){
                obj="是";
            }else if("1".equals(obj)){
                obj="否";
            }
        }
        return obj;
   }
    /**
     * 将数据组装到headers和columns中
     */
    private void initHeaderAndColumns(Map<String, String> map, String[] headers, String[] columns) {
        //定义表头顺序的map
        Map<Integer,String> glmap=new HashMap<Integer,String>();
        int i=0;
        for(String str:map.keySet()){
            String column=str.substring(str.indexOf(".")+1);
            if("备案结果".equals(column)){
                headers[i]="备案结果";
                column="htjg";
            }
            headers[i]=map.get(str);
            columns[i]=column;
            i++;
        }
    }
                      
    /**
     * 跳转到查询条件配置页面
     * @return
     */
    public ActionForward toSearchPage(ActionMapping actionMapping, ActionForm form,
            HttpServletRequest request, HttpServletResponse response) {
                      
        Map<String, Object> params = WebUtils.getParametersStartingWith(request, "search_");
                      
        try {
            Map infoMap = new GetUserInfo().getUserInfoMap(request);
            //获取模板
            String loginName = (String) infoMap.get("loginName");
            if(StringUtils.isNotBlank(loginName)){
                //获取项目的根路径
                String rootPath=request.getSession().getServletContext().getRealPath("/");
                //当前用户对应的查询模板的真实路径
                String realPath=rootPath+"WEB-INF/userSearchTemplate/"+loginName+".xml";
                SearchUtil.setMyTPath(realPath);
                File file=new  File(realPath);
                if(!file.exists()){//如果不存在创建
                   if(!file.createNewFile())
                       throw new Exception("文件不存在,创建失败!");
                    //创建一篇文档
                    Document docSource = DocumentHelper.createDocument();
                    //添加根元素
                    Element root = docSource.addElement("searchCondition");
                    //将创建带根元素的xml的document写入到文件中
                    OutputFormat  format=new OutputFormat();
                    format.setEncoding("UTF-8");
                    XMLWriter output=new XMLWriter(new FileWriter(file),format);
                    output.write(docSource);
                    output.close();
                }
                List<Map<String,String>> allMb = SearchUtil.findAllSearchCondition(realPath);
                request.setAttribute("allMb", allMb);
            }
                                 
                      
        } catch (Exception e) {
                      
            e.printStackTrace();
        }
        //查询到所有的区县,用于显示企业所在区县和项目所在区县
        List<Region> regionsList = getRegionManager().findNodeList();
        request.setAttribute("regions", regionsList);
        return actionMapping.findForward("searchPage");
    }
    /**
     * 获取查询结果,并将结果拼接成页面代码
     * @param actionMapping
     * @return
     */
    public ActionForward searchResultList(ActionMapping actionMapping, ActionForm form,
            HttpServletRequest request, HttpServletResponse response) {
        Map<String, Object> params = WebUtils.getParametersStartingWith(request, "search_");
        try {
            //获取登录用户所在区代码
            Map infoMap = new GetUserInfo().getUserInfoMap(request);
            //获取总数
            long count = getJdbcSearchManager().countByConntion(params);
            if(count!=-1){
                int currentPage=1;
                String strCurrentPage = request.getParameter("currentPage");
                if(StringUtils.isNotBlank(strCurrentPage)){
                    currentPage=Integer.parseInt(strCurrentPage);
                }
                System.out.println("当前的页面编号是:"+currentPage);
                Page page=new Page();
                //初始化page
                page.init(currentPage, (int)count);
                String sqlWhere=(String) params.get("sqlWhere");
                String sqlfromTables=(String) params.get("sqlfromTables");
                String tableHead=(String) params.get("tableHead");
                tableHead.replace("\"", "\'");
                System.out.println("tablehead是:"+tableHead);
                      
                //将查询的条件存入到page中
                page.setConditionObj(sqlWhere);
                page.setSqlfromTables(sqlfromTables);
                page.setTableHead(tableHead);
                                      
                //返回结果
                List<Map<String, Object>> resultList = getJdbcSearchManager().resultListByContion(params, page.beginNum(), page.getRecordNum());
                              
                StringBuffer resultSb=new StringBuffer();
                if(resultList!=null){
                   initResult(resultList, tableHead, resultSb);
                   String path=request.getContextPath();
                   initPageBar(page, resultSb,path);
                }else{
                    resultSb.append("<p style='color:red'>没有您要查询的数据哦</p>");
                }
                System.out.println(resultSb);
                request.setAttribute("resultText", resultSb.toString());
            }   
        } catch (Exception e) {
            e.printStackTrace();
        }
        return actionMapping.findForward("searchlist");
    }
    /**
     * 初始化分页导航
     * @param page
     */
    private void initPageBar(Page page,StringBuffer resultSb,String path){
        if(page.getPageTotalNum()>0){
            //将条件隐藏到页面中去
            resultSb.append("<form name='searchForm' id='searchForm' method='post' action='"+path+"/zhSearchAction.do?method=searchResultList&timestamp="+new Date()+"'>");
            resultSb.append("<input type='hidden'id='search_xmszqQuery' name='search_xmszqQuery' value=\""+page.getXmszqQuery()+"\" />");
            resultSb.append("<input type='hidden' id='search_qyszqQuery' name='search_qyszqQuery' value='"+page.getQyszqQuery()+"' />");
            resultSb.append("<input type='hidden' id='search_tableHead' name='search_tableHead' value=\""+page.getTableHead()+"\" />");
            resultSb.append("<input type='hidden' id='search_sqlfromTables' name='search_sqlfromTables' value='"+page.getSqlfromTables()+"' />");
            resultSb.append("<input type='hidden' id='search_sqlWhere' name='search_sqlWhere' value=\""+page.getConditionObj()+"\" />");
            //拼接分页导航
            resultSb.append("<div align='right' style='padding-top: 5px;padding-right: 10px;'>共找到");
            resultSb.append(page.getTotalNum());
            resultSb.append("条记录 当前是第<font color='red'>");
            resultSb.append(page.getThisPageNum()+"</font>页/"+page.getPageTotalNum()+"页");
            if(page.getThisPageNum()!=1){
                resultSb.append(" <a href='#' onclick='setpagenow(1)'>第一页</a>");
            }
            if(page.getThisPageNum()-1>0){
                resultSb.append(" |&nbsp;<a href='#' onclick='setpagenow("+page.getPrePage()+")'>上一页</a>");
            }
            if(page.getThisPageNum()+1<=page.getPageTotalNum()){
                resultSb.append(" |&nbsp;<a href='#' onclick='setpagenow("+page.getNextPage()+")'>下一页</a>");
            }
            if(page.getThisPageNum()!=page.getPageTotalNum()){
                resultSb.append(" |&nbsp;<a href='#' onclick='setpagenow("+page.getPageTotalNum()+")'>最后一页</a>");
            }
            resultSb.append("  &nbsp;&nbsp;转到第<select onchange='setpagenow(this.value);' style='font-size: 12px!important;margin:0px;padding:0px;'>");
            if(page.getPageTotalNum()>50){
                if(page.getThisPageNum()>25){
                    int max=page.getPageTotalNum();
                    if(page.getThisPageNum()+25<page.getPageTotalNum()){
                        max=page.getThisPageNum()+25;
                    }
                    for(int i=page.getThisPageNum()-25;i<=max;i++){
                        if(i==page.getThisPageNum()){
                            resultSb.append(" <option selected='selected' value='"+i+"'><span>"+i+"</span></option>");
                        }else{
                            resultSb.append("<option value="+i+">"+i+"</option>");
                        }
                    }
                }
                if(page.getThisPageNum()<=25){
                    for(int i=1;i<50;i++){
                        if(i==page.getThisPageNum()){
                            resultSb.append(" <option selected='selected' value='"+i+"'><span>"+i+"</span></option>");
                        }else{
                            resultSb.append("<option value="+i+">"+i+"</option>");
                        }
                    }
                }
                                      
            }else{
                if(page.getPageTotalNum()<50){//页面总数小于50的时候
                    for(int i=1;i<=page.getPageTotalNum();i++){
                        if(i==page.getThisPageNum()){
                            resultSb.append(" <option selected='selected' value='"+i+"'><span>"+i+"</span></option>");
                        }else{
                            resultSb.append("<option value="+i+">"+i+"</option>");
                        }
                    }
                }
            }
            resultSb.append(" </select>页</div></form>");
        }
                              
    }
    /**
     * 拼接表
     * @param resultList
     * @param jsonTableHeader 
     * @param resultSb
     */
    private void initResult(List<Map<String,Object>> resultList,String jsonTableHeader,StringBuffer resultSb){
        //拼接thead
        resultSb.append("<table id='resultTable' class='tableRegion' style='table-layout:fixed;' width='100%'>");
        resultSb.append("<thead class='tableHeader'><tr>");
        //将json串转成map集合
        JSONObject jb=JSONObject.fromObject(jsonTableHeader);
        Map<String,String> map=(Map)jb;
        //定义表头顺序的map
        Map<Integer,String> glmap=new HashMap<Integer,String>();
        int i=0;
        for(String str:map.keySet()){
           resultSb.append("<td>");
           resultSb.append(map.get(str));
           glmap.put(i,str.substring(str.indexOf(".")+1));
          // tbodyTdSb.append("<td>"++"</td>");
           resultSb.append("</td>");  
           i++;
        }
        resultSb.append("</tr></thead>");
        //拼接tbody
        resultSb.append("<tbody>");
        //查询到所有的区县,用于显示企业所在区县和项目所在区县
        List regions = getRegionManager().findNodeList();
        Map<String,String> maps=new HashMap<String, String>();
        for(int j=0;j<regions.size();j++){
            //{code=10, name=东城区}, {code=11, name=西城区}, {code=14, name=朝阳区}, {code=15, name=海淀区}, {code=16, name=丰台区}, {code=17, name=石景山区}, {code=18, name=房山区}, {code=19, name=通州区}, {code=20, name=顺义区}, {code=21, name=昌平区}, {code=22, name=大兴区}, {code=23, name=门头沟区}, {code=24, name=怀柔区}, {code=25, name=平谷区}, {code=26, name=密云县}, {code=27, name=延庆县}, {code=28, name=北京经济技术开发区}]
            Map<String,String> m=(Map<String, String>) regions.get(j);
            maps.put(m.get("code"),m.get("name"));
        }
        for(Map<String,Object> m:resultList){
            resultSb.append("<tr>");
            for(int n=0;n<glmap.keySet().size();n++){
                String column=glmap.get(n);
                Object obj=m.get(column);
                obj=changeDefaultData(column,obj);
                if("xmzlssq".equalsIgnoreCase(column)||"zcdzQx".equalsIgnoreCase(column)
                        ||"bgdz".equalsIgnoreCase(column)){
                    //{19=通州区, 17=石景山区, 22=大兴区, 18=房山区, 23=门头沟区, 15=海淀区, 24=怀柔区, 25=平谷区, 16=丰台区, 26=密云县, 27=延庆县, 14=朝阳区, 28=北京经济技术开发区, 11=西城区, 21=昌平区, 20=顺义区, 10=东城区}
                    String strkey=(String) m.get(column);
                    if(StringUtils.isNotBlank(strkey)){
                        if(maps.containsKey(strkey)){//先判断该key在不在里面
                           if("12".equals(strkey)){
                               obj=maps.get("10");
                           }else if("13".equals(strkey)){
                               obj=maps.get("11");
                           }else{
                               obj=maps.get(strkey);
                           }
                        }else{
                            obj="无";
                        }
                    }
                }
                resultSb.append("<td>"+obj+"</td>");
            }
            resultSb.append("</tr>");
        }
        resultSb.append("</tbody>");
        resultSb.append("</table>");  
        //拼接分页
    }
}


 

除特别注明外,本站所有文章均为我要编程原创,转载请注明出处来自http://5ycode.com/article/85.html

关于
该用户很懒!
切换注册

登录

忘记密码 ?

切换登录

注册

扫一扫二维码分享