自定义报表查询
所谓的自定义报表，即提供与功能相关的所有字段全部列出，让用户根据以实际工作需求获取适当的字段信息，达到简化用户工作的效果。
经多家客户提供的报表需求来看，可分为“汇总统计”和“明细统计”两种，然后再由指定字段排序。
汇总统计：可按字段进行数量的汇总或金额的汇总。
明细统计：即将所有信息数据全部展现。
指定排序：用户可自定义指定字段，指定排序方式进行展现。
目前初步已完成的工作开发方案：
前端处理方式：
- **自定义查询条件：**将相关的查询字段列出，供用户选择查询。
- **自定义返回字段：**使用复选框供用户选择。
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();//显示字段Bean
var 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;//查询Bean
requestParam.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_pro d, ");
hql.append("t_linproduct c, ");
hql.append("t_category h, ");
hql.append("t_company b, ");
hql.append("t_promappingplatform ph, ");
hql.append("t_platform g");
hql.append("WHERE b.linkproductid = c.linkproductid ");
hql.append("AND c.productid = d.productid ");
hql.append("AND d.factorynumber = e.factorynumber ");
hql.append("AND b.companynamenumber = f.companynamenumber ");
hql.append("AND h.categoryid=b.categoryid ");
hql.append("AND b.companyproductid = ph.companyproductid ");
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数据类型
// String
scalarColumns_String.add("hospitalstatus");
scalarColumns_String.add("ismappinghis");
scalarColumns_String.add("ismappingcloud");
scalarColumns_String.add("ismappingthirdparty");
scalarColumns_String.add("ismappingcityplatform");
scalarColumns_String.add("isbilling");
// BigDecimal
scalarColumns_Decimal.add("supplyprice");
scalarColumns_Decimal.add("priceincreasel");
scalarColumns_Decimal.add("retailprice");
scalarColumns_Decimal.add("packingcount");
// Integer
scalarColumns_Integer.add("linkproductid");
// Long
scalarColumns_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 ();
t.commit ();
return list;
java后台SQL拼接CommonFun公共处理方法代码
/**
* 拼接自定义SQL返回字段
* @author zhuteng
* @time 2019-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 2019-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);
}
}
}
8371
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
