综合查询(四)后台功能模板
/**
* 综合查询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×tamp="+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(" | <a href='#' onclick='setpagenow("+page.getPrePage()+")'>上一页</a>");
}
if(page.getThisPageNum()+1<=page.getPageTotalNum()){
resultSb.append(" | <a href='#' onclick='setpagenow("+page.getNextPage()+")'>下一页</a>");
}
if(page.getThisPageNum()!=page.getPageTotalNum()){
resultSb.append(" | <a href='#' onclick='setpagenow("+page.getPageTotalNum()+")'>最后一页</a>");
}
resultSb.append(" 转到第<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>");
//拼接分页
}
}