自定义报表查询
自定义报表查询前端处理方式：后台处理方式：自定义报表查询
所谓的自定义报表，即提供与功能相关的所有字段全部列出，让用户根据以实际工作需求获取适当的字段信息，达到简化用户工作的效果。
经多家客户提供的报表需求来看，可分为“汇总统计”和“明细统计”两种，然后再由指定字段排序。
汇总统计：可按字段进行数量的汇总或金额的汇总。
明细统计：即将所有信息数据全部展现。
指定排序：用户可自定义指定字段，指定排序方式进行展现。
目前初步已完成的工作开发方案：
前端处理方式：
**自定义查询条件：**将相关的查询字段列出，供用户选择查询。**自定义返回字段：**使用复选框供用户选择。html页面渲染处理方式
<div class="col-sm-12"><div class="checkbox checkbox-info checkbox-inline"><input id="custom-column-companyproductid" class="custom-column-checkbox" type="checkbox" value="companyproductid"><label for="custom-column-companyproductid">耗材唯一码</label></div></div><div class="col-sm-12"><div class="checkbox checkbox-info checkbox-inline"><input id="custom-column-uniquecode" class="custom-column-checkbox" type="checkbox" value="uniquecode"><label for="custom-column-uniquecode">产品唯一码</label></div></div><div class="col-sm-12"><div class="checkbox checkbox-info checkbox-inline"><input id="custom-column-productname" class="custom-column-checkbox" type="checkbox" value="productname"><label for="custom-column-productname">耗材名称</label></div></div>
JQUERY代码加载查询条件方式
var queryBean=new Object();//查询Bean//获取查询条件$("#custom-query-condition-accordion .custom-query-attribute").each(function(){var $Node=$(this);var attribute=$Node.attr("attribute");//属性名称【字段名称】var value=$Node.val();//属性值queryBean[attribute]=value;});//特殊字段需要另外编写queryBean.productname=$(".custom-productname").find("option:selected").attr("productname");//耗材名称queryBean.productnorms=$(".custom-productnorms").find("option:selected").attr("productnorms");//耗材规格queryBean.productstyles=$(".custom-productstyles").find("option:selected").attr("productstyles");//耗材型号
JQUERY代码加载用户所选的要显示的字段信息
//获取自定义显示字段var showColumnBean=new Object();//显示字段Beanvar columnTextBean=new Object();//字段中文注释Bean//查询条件数量var resultCheckCounts=$("#custom-result-condition-collapseOne").find(".custom-column-checkbox:checked").length;$("#custom-result-condition-collapseOne .custom-column-checkbox").each(function(){var $checkbox=$(this);var attribute=$checkbox.val();//属性名称【字段名称】var text=$.trim($checkbox.next().html());//表头if(resultCheckCounts<=0){showColumnBean[attribute]=true;//默认显示所有}else{var checked=$checkbox.is(":checked");//是否需要显示showColumnBean[attribute]=checked;//是否显示}columnTextBean[attribute]=text;//中文信息});
JQUERY动态表格初始化处理方式:
function initialMappingCityformProductTable(aoColumns){$('#city_platform_maping_product').DataTable({"oLanguage": {"sLengthMenu": "每页显示 _MENU_条","sZeroRecords": "没有找到符合条件的数据","sProcessing": "<img src=’./loading.gif’ />","sInfo": "当前第 _START_ - _END_ 条共计 _TOTAL_ 条","sInfoEmpty": "没有记录","sInfoFiltered": "(从 _MAX_ 条记录中过滤)","sSearch": "搜索：", "sCopy": "复制","oPaginate": {"sFirst": "首页","sPrevious": "前一页","sNext": "后一页","sLast": "尾页"}},dom: '<"html5buttons"B>lTfgitp',bRetrieve: true,"paging": false,"scrollX": true,"scrollY": "500px","aoColumnDefs": [ {"bSortable": false, "aTargets": [ 0 ] }],buttons:[{extend: 'copy',"text": "复制"},{extend: 'csv',"text": "导出CSV"},{extend: 'excel', title: '市平台材料对照信息',"text": "导出EXCEL"},],"scrollX": true,"aoColumns":aoColumns});}
JQUERY动态表格头部处理方式
function bindTableHead(paramBean){var showColumnBean=paramBean.showColumnBean;//需要显示的列var columnTextBean=paramBean.columnTextBean;//列名称注释if(showColumnBean==null||JSON.stringify(showColumnBean)=='{}'||columnTextBean==null||JSON.stringify(columnTextBean)=='{}'){return false;}var aoColumns=new Array();var tableHead='<tr>';for(var attribute in showColumnBean)//遍历需要显示的字段{if(showColumnBean[attribute]){var column=new Object();//表格列column.mData=attribute;aoColumns.push(column);tableHead+='<th>'+columnTextBean[attribute]+'</th>';}}tableHead+='</tr>';//表格var table='<table style="width:100%" class="table table-striped table-bordered table-hover dataTables-example" id="city_platform_maping_product">';table+='<thead>';table+=tableHead;table+='</thead>';table+='<tbody>';table+='</tbody>';table+='</table>';$("#city_platform_maping_product_div").html(table);return aoColumns;}
JQUERY异步查询结果处理
var requestParam=new Object();//请求参数requestParam.queryBean=queryBean;//查询BeanrequestParam.showColumnBean=showColumnBean;//显示字段Bean$.ajax({url:"../cloudProduct/get_MappingCityPlatformProductByInfo.do",data:JSON.stringify(requestParam),dataType:"json",type:"post",contentType:"application/json;charset=utf-8",success:function(data){var dataJson=eval(data);if(dataJson.success_Info=='success'){$("#customColumnModal").modal('hide');//关闭弹框var paramBean=new Object();paramBean.showColumnBean=showColumnBean;paramBean.columnTextBean=columnTextBean;//拼接表头,必须先拼接表格头部，再初始化表格var aoColumns=product_CityPlatform_Mapping_Initial.bindTableHead(paramBean);//初始化表格product_CityPlatform_Mapping_Initial.initialMappingCityformProductTable(aoColumns);var mappingCityPlatformList=dataJson.mappingCityPlatformList;//后台返回到的数据if(mappingCityPlatformList!=undefined&&mappingCityPlatformList.length>0){//添加列，必须先初始化表格，再添加数据$('#city_platform_maping_product').dataTable().fnAddData(mappingCityPlatformList);}swal.close();}else{swal({title: "查询失败！",text:dataJson.wrong_Info,timer:2000,type:"error",showConfirmButton: false});}},error:function(data){console.log('请求失败：'+data);}});
后台处理方式：
自定义查询条件：使用传统的sql拼接查询条件，入参方式使用阿里巴巴 fastJSON:com.alibaba.fastjson.JSONObject可灵活处理数据。封装了sql拼接代码，节省点dao层工作开发时间自定义返回字段：将返回字段以key：value键值对形式保存，便于动态拼接。</font> **<font color='balc'>java后台SQL拼接查询条件处理方式示例JSONObject columns = new JSONObject();// 字段容器columns.put("status","case b.status WHEN 1 THEN '停用' ELSE '正常' END status");columns.put("ismapping", "case b.ismapping WHEN 1 THEN '是' ELSE '否' END ismapping");columns.put("iscloud", "CASE b.iscloud WHEN 1 THEN '是' ELSE '否' END iscloud");columns.put("third", "CASE b.third WHEN 1 THEN '是' ELSE '否' END third");columns.put("isplatform","CASE b.isplatform WHEN 1 THEN '是' ELSE '否' END isplatform");Session session = this.getSessionFactory().getCurrentSession();Transaction t = session.beginTransaction();StringBuffer hql = new StringBuffer();hql.append("SELECT ");paramBean.put("columns", columns);JSONObject appendSql = CommonFun.appendCustomSqlResultColumnInfo(paramBean);// SQL拼接hql.append(appendSql.getString("appendSql"));// 执行拼接hql.append(" FROM ");hql.append("t_publica f, ");hql.append("t_publicb e, ");hql.append("t_prod, ");hql.append("t_linproduct c, ");hql.append("t_categoryh, ");hql.append("t_company b, ");hql.append("t_promappingplatform ph, ");hql.append("t_platformg");hql.append("WHERE b.linkproductid = c.linkproductid ");hql.append("AND c.productid = d.productid ");hql.append("AND d.factorynumber = e.factorynumber ");hql.append("AND panynamenumber = panynamenumber ");hql.append("AND h.categoryid=b.categoryid ");hql.append("AND panyproductid = panyproductid ");hql.append("AND ph.productcityplatformid=g.productcityplatformid ");JSONObject queryBean = new JSONObject();if (paramBean.containsKey("queryBean") && paramBean.get("queryBean") != null){queryBean = paramBean.getJSONObject("queryBean");// 查询条件Bean}hql.append(bindJsonQueryCondition(queryBean, "b", "isbilling", true, "number"));hql.append(bindJsonQueryCondition(queryBean, "b", "ismappinghis", true, "number"));hql.append(bindJsonQueryCondition(queryBean, "b", "islimit", true, "number"));hql.append(bindJsonQueryCondition(queryBean, "b", "isnumber", true, "number"));hql.append(bindJsonQueryCondition(queryBean, "b", "biddingmethod", "varchar"));hql.append(bindJsonQueryCondition(queryBean, "b", "isconsignment", true, "number"));hql.append(bindJsonQueryCondition(queryBean, "b", "isgaozhi", true, "number"));hql.append(bindJsonQueryCondition(queryBean, "b", "factorynumber", "varchar"));// 时间区间hql.append(bindQueryConditionBetween(queryBean,"ph","operationtime","operationtime_s","operationtime_e",true,"oracle"));hql.append("ORDER BY b.supplyprice,d.productname ");SQLQuery sqlQuery = session.createSQLQuery(hql.toString());// 字段拼接List<String> scalarColumns_String = new ArrayList<String>();// String数据类型List<String> scalarColumns_Decimal = new ArrayList<String>();// BigDecimal数据类型List<String> scalarColumns_Integer = new ArrayList<String>();// Integer数据类型List<String> scalarColumns_Long = new ArrayList<String>();// Long数据类型// StringscalarColumns_String.add("hospitalstatus");scalarColumns_String.add("ismappinghis");scalarColumns_String.add("ismappingcloud");scalarColumns_String.add("ismappingthirdparty");scalarColumns_String.add("ismappingcityplatform");scalarColumns_String.add("isbilling");// BigDecimalscalarColumns_Decimal.add("supplyprice");scalarColumns_Decimal.add("priceincreasel");scalarColumns_Decimal.add("retailprice");scalarColumns_Decimal.add("packingcount");// IntegerscalarColumns_Integer.add("linkproductid");// LongscalarColumns_Long.add("productmappingcityplatformid");scalarColumns_Long.add("productcityplatformid");paramBean.put("scalarColumns_String", scalarColumns_String);paramBean.put("scalarColumns_Decimal", scalarColumns_Decimal);CommonFun.bindCustomSqlResultColumnInfo(paramBean, sqlQuery);@ SuppressWarnings("unchecked")List <ProductMappingCityPlatformInfoBean> list = sqlQuery.setResultTransformer (Transformers.aliasToBean (ProductMappingCityPlatformInfoBean.class)).list ();mit ();return list;
java后台SQL拼接CommonFun公共处理方法代码
/*** 拼接自定义SQL返回字段* @author zhuteng* @time -09-28* @param jsonObject* @return* @throws Exception*/public static JSONObject appendCustomSqlResultColumnInfo (JSONObject paramBean) throws Exception{if ( ! paramBean.containsKey ("columns") || paramBean.get ("columns") == null){throw new Exception ("自定义Dao未定义指定的字段！");}JSONObject columns = paramBean.getJSONObject ("columns");Iterator <String> columnArray = columns.keys ();// SQL字段遍历if ( ! columnArray.hasNext ()) throw new Exception ("自定义Dao未定义指定的字段！");// 开始拼接StringBuffer hql = new StringBuffer ();boolean isFirstCondition = false;// 是否存在自定义字段if (paramBean.containsKey ("showColumnBean") && paramBean.get ("showColumnBean") != null){JSONObject showColumnBean = paramBean.getJSONObject ("showColumnBean");// 需要查询的字段if (showColumnBean.keys ().hasNext ()){isFirstCondition = true;}}//自定义字段和默认查询做了两部分处理，主要是为了节省处理性能if (isFirstCondition)// 自定义查询,字段拼接{isFirstCondition = false;// 标志位初始化JSONObject showColumnBean = paramBean.getJSONObject ("showColumnBean");// 需要查询的字段while (columnArray.hasNext ()){String column = columnArray.next ();// 字段名if ("countunit".equals (column)){System.out.println ();}// 是否获取字段,不匹配则不处理if(!showColumnBean.containsKey(column)||!showColumnBean.getBoolean(column))continue;String str = isFirstCondition ? "," : "";// 判断是否拼接段位符hql.append (str).append (columns.getString (column));isFirstCondition = true;// 更新标识位}}else// 默认查询{while (columnArray.hasNext ()){String column = columnArray.next ();// 字段名String str = (isFirstCondition) ? "," : "";// 判断是否拼接段位符hql.append (str).append (columns.getString (column));isFirstCondition = true;// 更新标识位}}JSONObject resultInfo = new JSONObject ();resultInfo.put ("appendSql" , hql.toString ());return resultInfo;}
/*** 处理自定义SQL返回字段* * @author zhuteng* @time -09-28* @param jsonObject* @return* @throws Exception*/public static void bindCustomSqlResultColumnInfo (JSONObject paramBean , SQLQuery sqlQuery) throws Exception{List <String> scalarColumns_String = new ArrayList <String> ();// String数据类型List <String> scalarColumns_Decimal = new ArrayList <String> ();// BigDecimal数据类型List <String> scalarColumns_Integer = new ArrayList <String> ();// Integer数据类型List <String> scalarColumns_Long = new ArrayList <String> ();// Long数据类型List <String> scalarColumns_Timestamp = new ArrayList <String> ();// TimeStamp数据类型boolean isExistRsultColumns=false;if(paramBean.containsKey("scalarColumns_String")&¶mBean.get("scalarColumns_String")!=null){JSONArray array = paramBean.getJSONArray ("scalarColumns_String");if (array.size () > 0) isExistRsultColumns = true;// 设置为已设置查询返回字段for (int i = 0 ; i < array.size () ; i ++ ){scalarColumns_String.add (array.getString (i));}}if (paramBean.containsKey ("scalarColumns_Decimal") && paramBean.get ("scalarColumns_Decimal") != null){JSONArray array = paramBean.getJSONArray ("scalarColumns_Decimal");if (array.size () > 0) isExistRsultColumns = true;// 设置为已设置查询返回字段for (int i = 0 ; i < array.size () ; i ++ ){scalarColumns_Decimal.add (array.getString (i));}}if (paramBean.containsKey ("scalarColumns_Integer") && paramBean.get ("scalarColumns_Integer") != null){JSONArray array = paramBean.getJSONArray ("scalarColumns_Integer");if (array.size () > 0) isExistRsultColumns = true;// 设置为已设置查询返回字段for (int i = 0 ; i < array.size () ; i ++ ){scalarColumns_Integer.add (array.getString (i));}}if (paramBean.containsKey ("scalarColumns_Long") && paramBean.get ("scalarColumns_Long") != null){JSONArray array = paramBean.getJSONArray ("scalarColumns_Long");if (array.size () > 0) isExistRsultColumns = true;// 设置为已设置查询返回字段for (int i = 0 ; i < array.size () ; i ++ ){scalarColumns_Long.add (array.getString (i));}}if (paramBean.containsKey ("scalarColumns_Timestamp") && paramBean.get ("scalarColumns_Timestamp") != null){JSONArray array = paramBean.getJSONArray ("scalarColumns_Timestamp");if (array.size () > 0) isExistRsultColumns = true;// 设置为已设置查询返回字段for (int i = 0 ; i < array.size () ; i ++ ){scalarColumns_Timestamp.add (array.getString (i));}}// 若未设置自定义返回字段，则不允许查询if ( ! isExistRsultColumns) throw new Exception ("未定义返回字段！");// 开始处理boolean isFirstCondition = false;if ( ! paramBean.containsKey ("columns") || paramBean.get ("columns") == null){throw new Exception ("自定义Dao未定义指定的字段！");}JSONObject columns = paramBean.getJSONObject ("columns");Iterator <String> columnArray = columns.keys ();// SQL字段遍历// 字段拼接if (paramBean.containsKey ("showColumnBean") && paramBean.get ("showColumnBean") != null){JSONObject showColumnBean = paramBean.getJSONObject ("showColumnBean");// 需要查询的字段if (showColumnBean.keys ().hasNext ()){isFirstCondition = true;}}// 自定义查询,字段拼接,做了两部分处理，主要是为了节省处理性能if (isFirstCondition){// 需要查询的字段JSONObject showColumnBean = paramBean.getJSONObject ("showColumnBean");while (columnArray.hasNext ()){String column = columnArray.next ();// 字段名// 是否获取字段,不匹配则不处理if (!showColumnBean.containsKey(column)||!showColumnBean.getBoolean(column))continue;if (scalarColumns_String.contains (column)){sqlQuery.addScalar (column , Hibernate.STRING);}else if (scalarColumns_Decimal.contains (column)){sqlQuery.addScalar (column , Hibernate.BIG_DECIMAL);}else if (scalarColumns_Integer.contains (column)){sqlQuery.addScalar (column , Hibernate.INTEGER);}else if (scalarColumns_Long.contains (column)){sqlQuery.addScalar (column , Hibernate.LONG);}else if (scalarColumns_Timestamp.contains (column)){sqlQuery.addScalar (column , Hibernate.TIMESTAMP);}}}else// 未设定自定义字段查询{for (String column : scalarColumns_String){sqlQuery.addScalar (column , Hibernate.STRING);}for (String column : scalarColumns_Decimal){sqlQuery.addScalar (column , Hibernate.BIG_DECIMAL);}for (String column : scalarColumns_Integer){sqlQuery.addScalar (column , Hibernate.INTEGER);}for (String column : scalarColumns_Long){sqlQuery.addScalar (column , Hibernate.LONG);}for (String column : scalarColumns_Timestamp){sqlQuery.addScalar (column , Hibernate.TIMESTAMP);}}}
