public String qrypanelPublishByEquipNo(String path, String equipNo) throws Exception {
Map paramMap = new HashMap();
StringBuilder jsonString = new StringBuilder();
try {
jsonString.append("[");
jsonString.append("{");
// 获取面板查询SQL
// panel_group
paramMap = new HashMap();
paramMap.put("1", equipNo);
Map<String, String> panelPublishInfoJsonTypeMap = new HashMap<String, String>();
panelPublishInfoJsonTypeMap.put("state", "NUMBER");
panelPublishInfoJsonTypeMap.put("cust_id", "NUMBER");
panelPublishInfoJsonTypeMap.put("panel_group_id", "NUMBER");
panelPublishInfoJsonTypeMap.put("equip_id", "NUMBER");
panelPublishInfoJsonTypeMap.put("region_id", "VARCHAR2");
panelPublishInfoJsonTypeMap.put("point_id", "NUMBER");
panelPublishInfoJsonTypeMap.put("point_group_id", "NUMBER");
panelPublishInfoJsonTypeMap.put("panel_publish_url", "VARCHAR2");
panelPublishInfoJsonTypeMap.put("panel_publish_id", "NUMBER");
jsonString.append(
JsonFactory.getSqlDdlDao()
.queryJson(getPanelPublishInfoSql(), paramMap, panelPublishInfoJsonTypeMap, false));
jsonString.append(",");
Map<String, String> panelUpdateInfoJsonTypeMap = new HashMap<String, String>();
panelUpdateInfoJsonTypeMap.put("version_id", "NUMBER");
panelUpdateInfoJsonTypeMap.put("version_url", "VARCHAR2");
jsonString.append(
JsonFactory.getSqlDdlDao()
.queryJson(getPanelUpdateInfoSql(), paramMap, panelUpdateInfoJsonTypeMap, false));
jsonString.append(",");
jsonString.append("\"column_publish_url\":");
jsonString.append("[");
Map<String, String> columnPublishInfoJsonTypeMap = new HashMap<String, String>();
columnPublishInfoJsonTypeMap.put("publish_id", "NUMBER");
columnPublishInfoJsonTypeMap.put("column_id", "NUMBER");
columnPublishInfoJsonTypeMap.put("file_url", "VARCHAR2");
String columnPublishInfoJosn =
JsonFactory.getSqlDdlDao()
.queryJson(getColumnPublishInfoSql(), paramMap, columnPublishInfoJsonTypeMap, true);
if (columnPublishInfoJosn.indexOf("\"publish_id\":\"\",") > -1) {
jsonString.append("");
} else {
jsonString.append(columnPublishInfoJosn);
}
jsonString.append("]");
jsonString.append("}");
jsonString.append("]");
} catch (Exception e) {
e.printStackTrace();
}
return jsonString.toString();
}
@Override
public void updatePanelPublishJsonFiels(String path) throws Exception {
// TODO Auto-generated method stub
String custId = UserUtil.getCurUser().getDepartId(); // "1";//
Map paramMap = new HashMap();
paramMap.put("1", custId); // UserUtil.getCurUser().getDepartId()
// --面板组定义表
String panelGroupSql =
"select * from panel_group where GROUP_ID in (select GROUP_ID from cust_group where cust_id = ?)";
List<PanelGroup> panelGroups =
DbFactory.getSqlDdlDao().query(panelGroupSql, paramMap, PanelGroup.class);
//
String date = SystemDateUtil.getSysDateTime();
String root = path + "data" + File.separator;
MD5Check md5 = new MD5Check();
paramMap.clear();
for (PanelGroup panelGroup : panelGroups) {
// 面板组ID
BigDecimal groupId = panelGroup.getGroupId();
paramMap.clear();
paramMap.put("1", groupId);
// 文件名
StringBuilder fileName = new StringBuilder();
String date_replace1 = date.replaceAll(" ", "_");
String date_replace2 = date_replace1.replaceAll(":", "_");
String date_replace3 = date_replace2.replaceAll("-", "_");
fileName.append("wifi.pub.").append(groupId);
fileName.append(".").append(date_replace3).append(".json");
PrintWriter print = null;
try {
print = org.frame.tool.FilesTool.getPrintWriter(root + fileName.toString());
print.println("[");
// 查找栏目子目录column_info
// 面板组明细sql
String panelGroupDtlSql =
"select * from panel_group_dtl where PANELS_ID in "
+ "(select PANELS_ID from panel_group where GROUP_ID = ?)";
JsonFactory.getSqlDdlDao()
.queryJson(panelGroupDtlSql, paramMap, PanelGroupDtl.class, print);
print.println(",");
// 面板定义
String panelDefSql =
"select * from panel_def where PANEL_ID in "
+ "(select PANEL_ID from panel_group_dtl where PANELS_ID in "
+ " (select PANELS_ID from panel_group where GROUP_ID = ?)"
+ ")";
JsonFactory.getSqlDdlDao().queryJson(panelDefSql, paramMap, PanelDef.class, print);
print.println(",");
// 面板项
String paneBoxDefSql =
"select * from panel_box_def where PANEL_ID in "
+ "(select PANEL_ID from panel_group_dtl where PANELS_ID in "
+ " (select PANELS_ID from panel_group where GROUP_ID = ?)"
+ ")";
JsonFactory.getSqlDdlDao().queryJson(paneBoxDefSql, paramMap, PanelBoxDef.class, print);
print.println(",");
// 面板是否有已发布
// updateStatus(col.getColumnId());
// 面板内容
String PanelBoxItemSql =
"select * from panel_box_item where box_id in "
+ "(select box_id from panel_box_def where PANEL_ID in "
+ " (select PANEL_ID from panel_group_dtl where PANELS_ID in "
+ " (select PANELS_ID from panel_group where GROUP_ID = ?)"
+ " )"
+ ")";
JsonFactory.getSqlDdlDao().queryJson(PanelBoxItemSql, paramMap, PanelBoxItem.class, print);
print.print("]");
} finally {
if (print != null) print.close();
}
//
PublishLog log = new PublishLog();
log.setColumnId(new BigDecimal(0));
log.setCustId(new BigDecimal(custId));
log.setFileUrl("data" + File.separator + fileName);
log.setMd5Num(md5.getFileMD5String(new File(root + fileName.toString())));
log.setState(BigDecimal.ZERO);
log.setPublishType(new BigDecimal(2));
log.setPublishId(PkHelper.getPk("PUBLISH_LOG"));
DbFactory.getSqlDdlDao().insert(log);
}
}
public String panelPublishByCustId(String path, String equipNo) throws Exception {
// TODO Auto-generated method stub
// String custId=UserUtil.getCurUser().getDepartId();//"1";//
Map paramMap = new HashMap();
// paramMap.put("1", custId);
String date = SystemDateUtil.getSysDate();
// 文件名
StringBuilder fileName = new StringBuilder(path);
fileName.append("wifi.panel.").append(equipNo).append(".json");
PrintWriter print = null;
BufferedReader reader = null;
try {
// File file=new File(fileName.toString());
//
StringBuilder sb = new StringBuilder();
// if(file.exists()){
// String lastdate=getModDate(file);
// if(lastdate.equals(SystemDateUtil.getSysDate())){
// reader=FilesTool.getFileReader(fileName.toString());
// String tempString=null;
// while ((tempString = reader.readLine()) != null) {
// sb.append(tempString);
// }
// return sb.toString();
// }
// }
// panel_group panel_group_dtl panel_def panel_box_def
// panel_box_item
print = org.frame.tool.FilesTool.getPrintWriter(fileName.toString());
print.println("[");
// panel_group
paramMap = new HashMap();
paramMap.put("1", equipNo);
JsonFactory.getSqlDdlDao()
.queryJson(
"select * from panel_group g where EXISTS(select 1 from cust_equipment a where a.EQUIP_NO=? and g.CREATE_MAN=a.cust_id and g.GROUP_ID=a.GROUP_ID and g.POINT_ID=a.POINT_ID ) ",
paramMap,
PanelGroup.class,
print);
print.println(",");
// panel_group_dtl
JsonFactory.getSqlDdlDao()
.queryJson(
"select * from panel_group_dtl dtl where EXISTS(select 1 from panel_group g where dtl.PANELS_ID=g.PANELS_ID and EXISTS(select 1 from cust_equipment a where a.EQUIP_NO=? and g.CREATE_MAN=a.cust_id and g.GROUP_ID=a.GROUP_ID and g.POINT_ID=a.POINT_ID ) ) ",
paramMap,
PanelGroupDtl.class,
print);
print.println(",");
// panel_def
JsonFactory.getSqlDdlDao()
.queryJson(
"select * from panel_def p where EXISTS (select 1 from panel_group_dtl dtl where p.PANEL_ID=dtl.PANEL_ID and EXISTS(select 1 from panel_group g where dtl.PANELS_ID=g.PANELS_ID and EXISTS(select 1 from cust_equipment a where a.EQUIP_NO=? and g.CREATE_MAN=a.cust_id and g.GROUP_ID=a.GROUP_ID and g.POINT_ID=a.POINT_ID ) ))",
paramMap,
PanelDef.class,
print);
print.println(",");
// panel_box_def panel_box_item
JsonFactory.getSqlDdlDao()
.queryJson(
"select * from panel_box_def box where EXISTS (select 1 from panel_def p where box.panel_id=p.panel_id and EXISTS (select 1 from panel_group_dtl dtl where p.PANEL_ID=dtl.PANEL_ID and EXISTS(select 1 from panel_group g where dtl.PANELS_ID=g.PANELS_ID and EXISTS(select 1 from cust_equipment a where a.EQUIP_NO=? and g.CREATE_MAN=a.cust_id and g.GROUP_ID=a.GROUP_ID and g.POINT_ID=a.POINT_ID ) )))",
paramMap,
PanelBoxDef.class,
print);
print.println(",");
// panel_box_item
JsonFactory.getSqlDdlDao()
.queryJson(
"select * from panel_box_item it where EXISTS (select 1 from panel_box_def box where it.box_id=box.box_id and EXISTS (select 1 from panel_def p where box.panel_id=p.panel_id and EXISTS (select 1 from panel_group_dtl dtl where p.PANEL_ID=dtl.PANEL_ID and EXISTS(select 1 from panel_group g where dtl.PANELS_ID=g.PANELS_ID and EXISTS(select 1 from cust_equipment a where a.EQUIP_NO=? and g.CREATE_MAN=a.cust_id and g.GROUP_ID=a.GROUP_ID and g.POINT_ID=a.POINT_ID ) ))))",
paramMap,
PanelBoxItem.class,
print);
print.print("]");
if (print != null) {
print.close();
print = null;
}
reader = FilesTool.getFileReader(fileName.toString());
String tempString = null;
while ((tempString = reader.readLine()) != null) {
sb.append(tempString);
}
return sb.toString();
} finally {
try {
if (print != null) print.close();
} catch (Exception e) {
}
if (reader != null) reader.close();
}
// 查找栏目column_info
// 查找栏目子目录column_info
// 栏目资源关系表 column_res_list
// 查找栏目下的资源 res_info res_attr res_file res_owner_tag sub_res
}
@Override
public void updatefbRes(String path, BigDecimal columnId) throws Exception {
if (columnId == null) {
return;
}
String custId = UserUtil.getCurUser().getDepartId(); // "1";//
Map paramMap = new HashMap();
paramMap.put("1", custId); // UserUtil.getCurUser().getDepartId()
//
String date = SystemDateUtil.getSysDateTime();
String date_replace1 = date.replaceAll(" ", "_");
String date_replace2 = date_replace1.replaceAll(":", "_");
String date_replace3 = date_replace2.replaceAll("-", "_");
String root = path + "data" + File.separator;
MD5Check md5 = new MD5Check();
// 文件名
StringBuilder fileName = new StringBuilder();
fileName.append("wifi.pub.").append(columnId);
fileName.append(".").append(date_replace3).append(".json");
PrintWriter print = null;
try {
paramMap.put("1", columnId);
Map childColInfo_map =
DbFactory.getSqlDdlDao()
.queryMap(
"SELECT * from (select qryChildColInfo(?) as custIds) a",
new String[] {"custIds"},
paramMap);
String cuistIds = (String) childColInfo_map.get("custIds");
if (cuistIds == null || cuistIds.length() == 0) {
cuistIds = "";
}
// String whereSql = SqlUtils
// .arrayToWhereSqlIn(cuistIds != null ? cuistIds.split(",")
// : null);
print = org.frame.tool.FilesTool.getPrintWriter(root + fileName.toString());
print.println("[");
paramMap.clear();
// 查找栏目子目录column_info
paramMap.clear();
paramMap.put("1", custId);
JsonFactory.getSqlDdlDao()
.queryJson(
" select * from column_info where cust_id=? and FIND_IN_SET(column_id, '"
+ cuistIds
+ "')",
paramMap,
ColumnInfo.class,
print);
print.println(",");
paramMap.clear();
// 栏目资源关系表 column_res_list
StringBuilder columnResList = new StringBuilder();
columnResList.append(
"SELECT * FROM column_res_list WHERE column_id IN ( "
+ "SELECT column_id FROM column_info WHERE FIND_IN_SET(column_id, '"
+ cuistIds
+ "') ) ");
// column_res_list
JsonFactory.getSqlDdlDao()
.queryJson(columnResList.toString(), paramMap, ColumnResList.class, print);
print.println(",");
// 资源公共的过滤条件：
String commonWhereSql =
"res_id in ( "
+ "SELECT res_id FROM column_res_list WHERE column_id in ( "
+ "SELECT column_id FROM column_info WHERE FIND_IN_SET(column_id, '"
+ cuistIds
+ "' ) ) )";
// 查找栏目下的资源 res_info res_attr res_owner_tag
// res_info
StringBuilder resInfoSql = new StringBuilder();
resInfoSql.append("SELECT * FROM res_info ");
resInfoSql.append("WHERE ");
resInfoSql.append(commonWhereSql);
JsonFactory.getSqlDdlDao().queryJson(resInfoSql.toString(), paramMap, ResInfo.class, print);
print.println(",");
updateStatus(columnId);
// res_attr
StringBuilder resAttrSql = new StringBuilder();
resAttrSql.append("SELECT * FROM res_attr ");
resAttrSql.append("WHERE ").append(commonWhereSql);
JsonFactory.getSqlDdlDao().queryJson(resAttrSql.toString(), paramMap, ResAttr.class, print);
print.println(",");
// res_owner_tag
StringBuilder resOwnerTagSql = new StringBuilder();
resOwnerTagSql.append("SELECT * FROM res_owner_tag ");
resOwnerTagSql.append("WHERE ").append(commonWhereSql);
JsonFactory.getSqlDdlDao()
.queryJson(resOwnerTagSql.toString(), paramMap, ResOwnerTag.class, print);
print.println(",");
// 查找栏目下的资源分集sub_res
StringBuilder subResSql = new StringBuilder();
subResSql.append("SELECT * FROM sub_res ");
subResSql.append("WHERE ").append(commonWhereSql);
JsonFactory.getSqlDdlDao().queryJson(subResSql.toString(), paramMap, ResSubInfo.class, print);
print.println(",");
// 资源文件及资源分集文件 res_file
StringBuilder resFileSql = new StringBuilder();
resFileSql.append("SELECT * FROM res_file ");
resFileSql.append("WHERE ").append(commonWhereSql);
// paramMap.put("1", columnId);
JsonFactory.getSqlDdlDao().queryJson(resFileSql.toString(), paramMap, ResFile.class, print);
print.print("]");
} finally {
if (print != null) print.close();
}
//
PublishLog log = new PublishLog();
log.setColumnId(columnId);
log.setCustId(new BigDecimal(custId));
log.setPublishType(new BigDecimal(1));
log.setFileUrl("data" + File.separator + fileName);
log.setMd5Num(md5.getFileMD5String(new File(root + fileName.toString())));
log.setState(BigDecimal.ZERO);
log.setPublishId(PkHelper.getPk("PUBLISH_LOG"));
DbFactory.getSqlDdlDao().insert(log);
}
// 暂时保存
public void updatefbRes_1(String path) throws Exception {
// TODO Auto-generated method stub
String custId = UserUtil.getCurUser().getDepartId(); // "1";//
Map paramMap = new HashMap();
paramMap.put("1", custId); // UserUtil.getCurUser().getDepartId()
List<ColumnInfo> rootLm =
DbFactory.getSqlDdlDao()
.query(
"select * from column_info where cust_id=? and parent_column_id=0 ",
paramMap,
ColumnInfo.class);
//
String date = SystemDateUtil.getSysDateTime();
String date_replace1 = date.replaceAll(" ", "_");
String date_replace2 = date_replace1.replaceAll(":", "_");
String date_replace3 = date_replace2.replaceAll("-", "_");
String root = path + "data" + File.separator;
MD5Check md5 = new MD5Check();
for (ColumnInfo col : rootLm) {
// 文件名
StringBuilder fileName = new StringBuilder();
fileName.append("wifi.pub.").append(col.getColumnId());
fileName.append(".").append(date_replace3).append(".json");
PrintWriter print = null;
try {
print = org.frame.tool.FilesTool.getPrintWriter(root + fileName.toString());
print.println("[");
// 查找栏目子目录column_info
paramMap = new HashMap();
paramMap.put("1", custId);
paramMap.put("2", col.getColumnId());
JsonFactory.getSqlDdlDao()
.queryJson(
" select * from column_info where cust_id=? and FIND_IN_SET(column_id, qryChildColInfo(?)) ",
paramMap,
ColumnInfo.class,
print);
print.println(",");
// 栏目资源关系表 column_res_list
paramMap = new HashMap();
paramMap.put("1", col.getColumnId());
// column_res_list
JsonFactory.getSqlDdlDao()
.queryJson(
"select a.* from column_res_list a where EXISTS(select 1 from column_info b where FIND_IN_SET(b.column_id, qryChildColInfo(?)) and a.Column_id=b.column_id)",
paramMap,
ColumnResList.class,
print);
print.println(",");
// 查找栏目下的资源 res_info res_attr res_owner_tag
// res_info
JsonFactory.getSqlDdlDao()
.queryJson(
"select c.* from res_info c where EXISTS(select 1 from column_res_list a where c.res_id=a.res_id and EXISTS(select 1 from column_info b where FIND_IN_SET(b.column_id, qryChildColInfo(?)) and a.Column_id=b.column_id))",
paramMap,
ResInfo.class,
print);
print.println(",");
updateStatus(col.getColumnId());
// res_attr
JsonFactory.getSqlDdlDao()
.queryJson(
"select d.* from res_attr d where EXISTS( select 1 from res_info c where d.res_id=c.res_id and EXISTS(select 1 from column_res_list a where c.res_id=a.res_id and EXISTS(select 1 from column_info b where FIND_IN_SET(b.column_id, qryChildColInfo(?)) and a.Column_id=b.column_id)))",
paramMap,
ResAttr.class,
print);
print.println(",");
// res_owner_tag
JsonFactory.getSqlDdlDao()
.queryJson(
"select d.* from res_owner_tag d where EXISTS( select 1 from res_info c where d.res_id=c.res_id and EXISTS(select 1 from column_res_list a where c.res_id=a.res_id and EXISTS(select 1 from column_info b where FIND_IN_SET(b.column_id, qryChildColInfo(?)) and a.Column_id=b.column_id)))",
paramMap,
ResOwnerTag.class,
print);
print.println(",");
// 查找栏目下的资源分集sub_res
JsonFactory.getSqlDdlDao()
.queryJson(
"select d.* from sub_res d where EXISTS( select 1 from res_info c where d.res_id=c.res_id and EXISTS(select 1 from column_res_list a where c.res_id=a.res_id and EXISTS(select 1 from column_info b where FIND_IN_SET(b.column_id, qryChildColInfo(?)) and a.Column_id=b.column_id)))",
paramMap,
ResSubInfo.class,
print);
print.println(",");
// 资源文件及资源分集文件 res_file
paramMap.put("1", col.getColumnId());
paramMap.put("2", col.getColumnId());
JsonFactory.getSqlDdlDao()
.queryJson(
"select d.* from res_file d where EXISTS( select 1 from res_info c where d.res_id=c.res_id and EXISTS(select 1 from column_res_list a where c.res_id=a.res_id and EXISTS(select 1 from column_info b where FIND_IN_SET(b.column_id, qryChildColInfo(?)) and a.Column_id=b.column_id))) union all select e.* from res_file e where EXISTS(select 1 from sub_res d where d.sub_res_id=e.sub_res_id and EXISTS( select 1 from res_info c where d.res_id=c.res_id and EXISTS(select 1 from column_res_list a where c.res_id=a.res_id and EXISTS(select 1 from column_info b where FIND_IN_SET(b.column_id, qryChildColInfo(?)) and a.Column_id=b.column_id))))",
paramMap,
ResFile.class,
print);
print.print("]");
} finally {
if (print != null) print.close();
}
//
PublishLog log = new PublishLog();
log.setColumnId(col.getColumnId());
log.setCustId(new BigDecimal(custId));
log.setPublishType(new BigDecimal(1));
log.setFileUrl("data" + File.separator + fileName);
log.setMd5Num(md5.getFileMD5String(new File(root + fileName.toString())));
log.setState(BigDecimal.ZERO);
log.setPublishId(PkHelper.getPk("PUBLISH_LOG"));
DbFactory.getSqlDdlDao().insert(log);
}
// 查找栏目column_info
// 查找栏目子目录column_info
// 栏目资源关系表 column_res_list
// 查找栏目下的资源 res_info res_attr res_file res_owner_tag sub_res
}