package com.product.server.report.service;
|
|
import java.text.SimpleDateFormat;
|
import java.util.Date;
|
import java.util.List;
|
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
|
import com.alibaba.fastjson.JSONArray;
|
import com.alibaba.fastjson.JSONObject;
|
import com.product.core.dao.BaseDao;
|
import com.product.core.dao.support.Dao;
|
import com.product.core.entity.DataTableEntity;
|
import com.product.core.entity.FieldSetEntity;
|
import com.product.core.exception.BaseException;
|
import com.product.core.service.support.AbstractBaseService;
|
import com.product.core.spring.context.SpringMVCContextHolder;
|
import com.product.core.transfer.Transactional;
|
import com.product.server.report.controller.TestDao;
|
import com.product.server.report.service.idel.EChartsReportConfigService;
|
@Service
|
public class EChartsReportConfigServiceimple extends AbstractBaseService implements EChartsReportConfigService {
|
@Autowired
|
public BaseDao baseDao;
|
public BaseDao getBaseDao() {
|
return baseDao;
|
}
|
public void setBaseDao(BaseDao baseDao) {
|
this.baseDao = baseDao;
|
}
|
@Override
|
@Transactional
|
public JSONObject getSqlTableInfo(String sql) throws BaseException{
|
DataTableEntity dataTableEntity = TestDao.getColumnNames(sql);
|
JSONArray jsonArray = new JSONArray();
|
for (int i = 0; i < dataTableEntity.getRows(); i++) {
|
JSONObject fObject = new JSONObject();
|
fObject.put("field_name",dataTableEntity.getFieldSetEntity(i).getString("field_name"));
|
fObject.put("field_type",dataTableEntity.getFieldSetEntity(i).getString("field_type"));
|
jsonArray.add(fObject);
|
}
|
JSONObject object = new JSONObject();
|
object.put("sqlfield", jsonArray);
|
return object;
|
}
|
|
@Override
|
public JSONObject getNameTableInfo(String name) throws BaseException {
|
//获取表信息
|
FieldSetEntity fSetEntity = baseDao.getFieldSetEntityByFilter("product_sys_datamodel_table", "table_name=?",new String[] {name}, false);
|
String tableuuid = fSetEntity.getString("uuid");
|
//获取字段信息
|
DataTableEntity dTableEntity = baseDao.listTable("product_sys_datamodel_field", "table_uuid = ?",new String[] {tableuuid});
|
//查询是否有子表
|
DataTableEntity subTableEntity = baseDao.listTable("product_sys_datamodel_field", "field_relation_table = ?",new String[] {fSetEntity.getString("uuid")});
|
JSONObject jsonObject = new JSONObject();
|
|
if (subTableEntity.getRows() > 0) {
|
JSONArray subJsonArray = new JSONArray();
|
for (int i = 0; i < subTableEntity.getRows(); i++) {
|
JSONObject subJsonObject = new JSONObject();
|
//子表uuid
|
String table_uuid = subTableEntity.getFieldSetEntity(i).getString("table_uuid");
|
//子表表名
|
FieldSetEntity sub_table = baseDao.getFieldSetEntityByFilter("product_sys_datamodel_table", "uuid = ?",new String[] {table_uuid}, false);
|
//存放子表信息
|
subJsonObject.put("sub_table_uuid", sub_table.getString("uuid"));
|
subJsonObject.put("sub_table_name", sub_table.getString("table_name"));
|
subJsonObject.put("sub_table_description", sub_table.getString("table_description"));
|
//子表字段信息
|
DataTableEntity subDataEntity = baseDao.listTable("product_sys_datamodel_field", "table_uuid = ?",new String[] {table_uuid});
|
//创建数组存字段名
|
JSONArray jsonArray = new JSONArray();
|
//存放子表字段
|
for (int j = 0; j < subDataEntity.getRows(); j++) {
|
FieldSetEntity subFieldEntity = subDataEntity.getFieldSetEntity(j);
|
String sub_field_uuid = subFieldEntity.getString("uuid");
|
String sub_field_name = subFieldEntity.getString("field_name");
|
String sub_field_description = subFieldEntity.getString("field_description");
|
String sub_field_type = subFieldEntity.getString("field_type");
|
JSONObject sub_field_json = new JSONObject();
|
sub_field_json.put("sub_field_uuid", sub_field_uuid);
|
sub_field_json.put("sub_field_name", sub_field_name);
|
sub_field_json.put("sub_field_description", sub_field_description);
|
sub_field_json.put("sub_field_type", sub_field_type);
|
jsonArray.add(sub_field_json);
|
}
|
//子表字段信息json
|
subJsonObject.put("sub_table_field", jsonArray);
|
subJsonArray.add(subJsonObject);
|
}
|
jsonObject.put("subTable", subJsonArray);
|
}
|
jsonObject.put("table_name", fSetEntity.getString("table_name"));//表名
|
jsonObject.put("table_description", fSetEntity.getString("table_description"));//描述
|
//创建数组存字段名
|
JSONArray jsonArray = new JSONArray();
|
for (int i = 0; i < dTableEntity.getRows(); i++) {
|
FieldSetEntity fEntity = dTableEntity.getFieldSetEntity(i);
|
JSONObject object = new JSONObject();
|
object.put("field_name", fEntity.getString("field_name"));
|
object.put("field_uuid", fEntity.getString("uuid"));
|
object.put("field_description", fEntity.getString("field_description"));
|
object.put("field_type", fEntity.getString("field_type"));
|
jsonArray.add(object);
|
}
|
jsonObject.put("table_field", jsonArray);
|
jsonObject.put("table_uuid",tableuuid);
|
System.out.println(jsonObject.toString());
|
return jsonObject;
|
}
|
@Override
|
@Transactional
|
public boolean addTableInfo(JSONObject reportInfo) throws BaseException {
|
FieldSetEntity fieldSetEntity = new FieldSetEntity();
|
fieldSetEntity.setTableName("product_sys_reoprt_config");
|
System.out.print(reportInfo);
|
String report_head_title_left = (String)reportInfo.get("report_head_title_left");//头部报表标题—左
|
String report_head_title_center = (String)reportInfo.get("report_head_title_center");//头部报表标题—中
|
String report_head_title_right = (String)reportInfo.get("report_head_title_right");//头部报表标题-右
|
|
String report_tail_title_left = (String)reportInfo.get("report_tail_title_left");//尾部报表标题—左
|
String report_tail_title_center = (String)reportInfo.get("report_tail_title_center");//尾部报表标题—中
|
String report_tail_title_right = (String)reportInfo.get("report_tail_title_right");//尾部报表标题-右
|
|
String report_name = (String)reportInfo.get("report_name");//报表名称
|
String report_type = (String)reportInfo.get("report_type");//报表类型
|
String report_table_name = (String)reportInfo.get("report_table_name"); //数据表名称
|
String report_data_filter = (String)reportInfo.get("report_data_filter");//查询条件
|
String report_data_sql = (String)reportInfo.get("report_data_sql");//报表数据源，直接写sql(不能有排序)
|
String fix_header = (String)reportInfo.get("fix_header");//是否固定表头
|
String last_total = (String)reportInfo.get("last_total");//是否最后总计
|
String is_page = (String)reportInfo.get("is_page");//是否分页
|
String page_size = (String)reportInfo.get("page_size");//每页条数
|
String title_back_css = (String)reportInfo.get("title_back_css");//标题样式*
|
String body_back_css = (String)reportInfo.get("body_back_css");//表格主体样式*
|
String total_row_css = (String)reportInfo.get("total_row_css");//汇总行样式
|
String orderby = (String)reportInfo.get("orderby");//排序*
|
String report_data_width = (String)reportInfo.get("report_data_width");//报表长度
|
JSONArray map = (JSONArray)((JSONObject)reportInfo.get("map")).get("elements");//选中字段map
|
JSONArray headerMap = (JSONArray)((JSONObject)reportInfo.get("headerMap")).get("elements");//表头map
|
String report_uuid = (String)reportInfo.get("report_uuid");//报表uuid
|
System.out.println(map);
|
System.out.println(headerMap);
|
fieldSetEntity.setValue("report_head_title_left", report_head_title_left);
|
fieldSetEntity.setValue("report_head_title_center", report_head_title_center);
|
fieldSetEntity.setValue("report_head_title_right", report_head_title_right);
|
|
fieldSetEntity.setValue("report_tail_title_left",report_tail_title_left);
|
fieldSetEntity.setValue("report_tail_title_center",report_tail_title_center);
|
fieldSetEntity.setValue("report_tail_title_right",report_tail_title_right);
|
|
fieldSetEntity.setValue("report_name", report_name);
|
fieldSetEntity.setValue("report_type", report_type);
|
fieldSetEntity.setValue("report_table_name", report_table_name);
|
fieldSetEntity.setValue("report_data_filter", report_data_filter);
|
fieldSetEntity.setValue("report_data_sql", report_data_sql);
|
fieldSetEntity.setValue("fix_header", fix_header);//是否固定表头
|
fieldSetEntity.setValue("last_total", last_total);//是否最后总计
|
fieldSetEntity.setValue("is_page", is_page);//是否分页
|
fieldSetEntity.setValue("page_size", page_size);//分页字段
|
fieldSetEntity.setValue("title_back_css", title_back_css);//标题样式
|
fieldSetEntity.setValue("body_back_css", body_back_css);//表格主体样式
|
fieldSetEntity.setValue("total_row_css", total_row_css);//会总行样式
|
fieldSetEntity.setValue("orderby", orderby);//排序
|
fieldSetEntity.setValue("report_data_width",report_data_width);
|
if(report_uuid.isEmpty()){
|
report_uuid = baseDao.add(fieldSetEntity);
|
}else {
|
fieldSetEntity.setValue("uuid",report_uuid);
|
baseDao.update(fieldSetEntity);
|
}
|
baseDao.delete("product_sys_report_header_properties", "report_uuid=?",new String[] {report_uuid});
|
baseDao.delete("product_sys_report_field_properties","report_uuid = ?",new String[] {report_uuid});
|
//表头配置表
|
//PRODUCT_SYS_reoprt_config_header
|
JSONObject header = (JSONObject)headerMap.get(0);
|
JSONArray headerArray = (JSONArray)header.get("value");
|
//DataTableEntity dataTableEntity = new DataTableEntity();
|
if(headerArray != null){
|
for(int i=0;i<headerArray.size();++i){
|
JSONArray array = (JSONArray) headerArray.get(i);
|
for(int k=0;k<array.size();++k){
|
JSONObject obj = (JSONObject) array.get(k);
|
FieldSetEntity fieldInfo = new FieldSetEntity();
|
fieldInfo.setTableName("product_sys_report_header_properties");
|
fieldInfo.setValue("header_index", obj.getString("header_index")+"-"+i);//下标
|
fieldInfo.setValue("header_colspan", obj.getString("header_colspan"));//跨列
|
fieldInfo.setValue("header_rowspan", obj.getString("header_rowspan"));//跨行
|
fieldInfo.setValue("header_name", obj.getString("header_value"));//表头里面的值
|
fieldInfo.setValue("report_uuid",report_uuid);//放uuid
|
baseDao.add(fieldInfo);
|
}
|
}
|
}
|
String crtime = transitionDate(new Date());//创建时间
|
//字段属性表
|
//PRODUCT_SYS_report_field_properties
|
if(map != null){
|
for(int i=0;i<map.size();++i){
|
JSONObject fieldArray = (JSONObject)map.get(i);
|
if(fieldArray.get("value") != "isnull"){
|
FieldSetEntity fieldInfo = new FieldSetEntity();
|
fieldInfo.setTableName("product_sys_report_field_properties");
|
fieldInfo.setValue("report_uuid", report_uuid);//配置表uuid
|
JSONObject value = (JSONObject)fieldArray.get("value");
|
fieldInfo.setValue("table_field_uuid", value.getString("fieldUuid"));//字段表uuid 可为空
|
fieldInfo.setValue("table_field_name", value.getString("fieldName"));//字段名
|
fieldInfo.setValue("table_field_description",value.getString("Description"));//字段中文名(可设置)
|
fieldInfo.setValue("table_field_type", value.getString("fieldType"));//字段类型
|
fieldInfo.setValue("group_total_type", value.getString("fieldGroupAll")); //分组统计:Sum/arg/count(字符)分组字段不能配
|
fieldInfo.setValue("order_number",value.getInteger("fieldOrder"));//分组统计排列序号
|
fieldInfo.setValue("table_field_description",value.getString("fieldDescription"));
|
fieldInfo.setValue("end_total_type",value.getString("endTotalType"));//最后合计:Sum/arg/count(字符),分组字段不能配
|
fieldInfo.setValue("is_group_field",value.getString("fieldGroup")); //0.表示否 1.表示是 (分组字段必须排最前面)
|
fieldInfo.setValue("data_format",value.getString("fieldFormat")); //数据格式:##.##、yyyy-mm-dd...
|
fieldInfo.setValue("show_width",value.getInteger("fieldRatio")); //宽度比例,所有字段，不能超100
|
fieldInfo.setValue("url",value.getString("fieldSite"));//穿透地址
|
fieldInfo.setValue("created_by",SpringMVCContextHolder.getCurrentUser().getUser_account());//创建人 获取当前用户名
|
fieldInfo.setValue("created_utc_datetime", crtime);//创建时间
|
baseDao.add(fieldInfo);
|
}
|
}
|
}
|
return true;
|
}
|
@Override
|
public JSONObject getReportInfo(String report_uuid) throws BaseException {
|
FieldSetEntity fieldSetEntity = baseDao.getFieldSetEntity("product_sys_reoprt_config", report_uuid, false);
|
JSONObject report = new JSONObject();
|
report.put("report_head_title_left", fieldSetEntity.getString("report_head_title_left"));//表头信息，显示左边
|
report.put("report_head_title_center", fieldSetEntity.getString("report_head_title_center"));//表头信息中间显示
|
report.put("report_head_title_right", fieldSetEntity.getString("report_head_title_right"));//表头信息右边显示
|
report.put("report_tail_title_left", fieldSetEntity.getString("report_tail_title_left"));//表尾信息左边显示
|
report.put("report_tail_title_center", fieldSetEntity.getString("report_tail_title_center"));//表尾信息中间显示
|
report.put("report_tail_title_right", fieldSetEntity.getString("report_tail_title_right"));//表尾信息右边显示
|
report.put("report_name", fieldSetEntity.getString("report_name"));//报表名称
|
report.put("report_type", fieldSetEntity.getString("report_type"));//报表类型:1分组报2交叉报表3饼状图4柱壮5曲线
|
report.put("report_table_name", fieldSetEntity.getString("report_table_name"));//数据源，对应表uuid
|
report.put("report_data_filter", fieldSetEntity.getString("report_data_filter"));//数据源过滤条件，支持动态参数
|
report.put("report_data_sql", fieldSetEntity.getString("report_data_sql"));//报表数据源，直接写sql(不能有排序)
|
report.put("fix_header", fieldSetEntity.getString("fix_header"));//是否固定表头
|
report.put("last_total", fieldSetEntity.getString("last_total"));//0.表示否 1.表示是 最后总计
|
report.put("is_page", fieldSetEntity.getString("is_page"));//是否要分页
|
report.put("page_size", fieldSetEntity.getString("page_size"));//每页条数
|
report.put("title_back_css", fieldSetEntity.getString("title_back_css"));//标题样式
|
report.put("body_back_css", fieldSetEntity.getString("body_back_css"));//表格主体样式
|
report.put("total_row_css", fieldSetEntity.getString("total_row_css"));//会总行样式格式
|
report.put("orderby", fieldSetEntity.getString("orderby"));//排序
|
report.put("report_data_width", fieldSetEntity.getString("report_data_width"));//报表宽度
|
//获取字段信息
|
DataTableEntity data = baseDao.listTable("product_sys_report_field_properties", "report_uuid='"+report_uuid+"'", "order_number");
|
JSONObject fieldInfo = new JSONObject();
|
// if(fieldSetEntity.getString("report_name") != null && !fieldSetEntity.getString("report_name").isEmpty()){
|
// fieldInfo = this.getNameTableInfo(fieldSetEntity.getString("report_table_name"));
|
// }else if(fieldSetEntity.getString("report_data_sql") != null && !fieldSetEntity.getString("report_data_sql").isEmpty()){
|
// fieldInfo = getSqlTableInfo(fieldSetEntity.getString("report_data_sql"));
|
// }
|
if(data != null){
|
JSONArray valueArray = new JSONArray();
|
for(int i=0;i<data.getRows();++i){
|
FieldSetEntity field = data.getFieldSetEntity(i);
|
JSONObject value = new JSONObject();
|
value.put("uuid", field.getString("uuid"));//字段uuid
|
value.put("report_uuid", field.getString("report_uuid"));//配置表uuid
|
value.put("fieldUuid",field.getString("table_field_uuid"));//字段表uuid 可为空
|
value.put("fieldName", field.getString("table_field_name"));//字段名
|
value.put("Description",field.getString("table_field_description"));//字段中文名(可设置)
|
value.put("fieldType", field.getString("table_field_type"));//字段类型
|
value.put("fieldGroupAll", field.getString("group_total_type")); //分组统计:Sum/arg/count(字符)分组字段不能配
|
value.put("fieldOrder",field.getString("order_number")); //分组统计排列序号
|
value.put("fieldDescription",field.getString("table_field_description"));
|
value.put("endTotalType",field.getString("end_total_type"));//最后合计:是或否,分组字段不能配
|
value.put("fieldGroup",field.getString("is_group_field")); //0.表示否 1.表示是 (分组字段必须排最前面)
|
value.put("fieldFormat",field.getString("data_format")); //数据格式:##.##、yyyy-mm-dd...
|
value.put("fieldRatio",field.getString("show_width")); //宽度比例,所有字段，不能超100
|
value.put("fieldSite",field.getString("url"));//穿透地址
|
valueArray.add(value);
|
}
|
report.put("table_field", valueArray);
|
}
|
//获取表头
|
DataTableEntity header = baseDao.listTable("product_sys_report_header_properties", "report_uuid='"+report_uuid+"'");
|
if(header != null){
|
JSONArray headerArray = new JSONArray();
|
for(int i=0;i<header.getRows();++i){
|
FieldSetEntity field = header.getFieldSetEntity(i);
|
JSONObject value = new JSONObject();
|
value.put("header_index", field.getString("header_index"));//下标
|
value.put("header_colspan", field.getString("header_colspan"));//跨列
|
value.put("header_rowspan", field.getString("header_rowspan"));//跨行
|
value.put("report_uuid", field.getString("report_uuid"));//报表uuid
|
value.put("header_name", field.getString("header_name"));//表头里面的值
|
headerArray.add(value);
|
}
|
report.put("headerArray", headerArray);
|
}
|
return report;
|
}
|
|
// 时间处理为字符串
|
public String transitionDate(Date date) {
|
// yyyy表示年数，MM表示月，dd表示日
|
// HH表示24小时制的小时(hh表示12小时的小时)，mm表示分钟，ss表示秒，SSS表示毫秒
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
return sdf.format(date);
|
}
|
}
|
