private void updateStatus(BigDecimal colId) throws Exception { // 更新为已发布 Map paramMap = new HashMap(); paramMap.put("1", UserUtil.getCurUser().getLoginCode()); paramMap.put("2", colId); DbFactory.getSqlDdlDao() .delAddUptSql( "update res_info c set c.CHECK_STATUS=3,c.PUBLISH_MAN=?,c.PUBLISH_DATE=now() 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); }
public List qryGrpPg(Map condMap, PageUtil pageInfo) throws Exception { StringBuffer sql = new StringBuffer( "select a.*,b.GROUP_NAME,c.POINT_NAME from panel_group a left join cust_group b on a.GROUP_ID=b.GROUP_ID left join cust_point c on c.POINT_ID=a.POINT_ID where 1=1 "); Map param = new HashMap(); int index = 1; String panelId = (String) condMap.get("panelId"); if (!StringUtil.isEmpty(panelId)) { param.put(String.valueOf(index++), panelId); sql.append(" and a.PANEL_ID=? "); } String resType = (String) condMap.get("resType"); if (!StringUtil.isEmpty(resType)) { param.put(String.valueOf(index++), resType); sql.append(" and a.RES_TYPE=? "); } String title = (String) condMap.get("title"); if (!StringUtil.isEmpty(title)) { param.put(String.valueOf(index++), "%" + title + "%"); sql.append(" and a.PANELS_NAME like ? "); } String panelsName = (String) condMap.get("panelsName"); if (!StringUtil.isEmpty(panelsName)) { param.put(String.valueOf(index++), "%" + panelsName + "%"); sql.append(" and a.PANELS_NAME like ? "); } String screenId = (String) condMap.get("screenId"); if (!StringUtil.isEmpty(screenId)) { param.put(String.valueOf(index++), screenId); sql.append(" and a.SCREEN_ID=? "); } String groupId = (String) condMap.get("groupId"); if (!StringUtil.isEmpty(groupId)) { param.put(String.valueOf(index++), groupId); sql.append(" and a.GROUP_ID=? "); } String pointId = (String) condMap.get("pointId"); if (!StringUtil.isEmpty(pointId)) { param.put(String.valueOf(index++), pointId); sql.append(" and a.POINT_ID=? "); } sql.append(UserUtil.custFilter2("a")); if (pageInfo != null) { pageInfo.setRowTotal(DbFactory.getSqlDdlDao().queryCount(sql.toString(), param)); return DbFactory.getSqlDdlDao().query(sql.toString(), param, PanelGroup.class, pageInfo); } else { return DbFactory.getSqlDdlDao().query(sql.toString(), param, PanelGroup.class); } }
public void save(PanelGroup group, List<PanelGroupDtl> dtlList) throws Exception { if (group.getPanelsId() == null) { group.setPanelsId(getMax()); group.setVersionVal(BigDecimal.ONE); group.setCreateDate(SystemDateUtil.getSystemDate()); group.setCreateMan(String.valueOf(UserUtil.getCurUser().getDepartId())); DbFactory.getSqlDdlDao().insert(group); } else { if (group.getVersionVal() != null) group.setVersionVal(group.getVersionVal().add(BigDecimal.ONE)); // 更新版本号 else group.setVersionVal(BigDecimal.ONE); DbFactory.getSqlDdlDao().update(group); } DbFactory.getSqlDdlDao() .delete(PanelGroupDtl.class, "PANELS_ID=?", new Object[] {group.getPanelsId()}); for (PanelGroupDtl info : dtlList) { info.setPanelsId(group.getPanelsId()); DbFactory.getSqlDdlDao().insert(info); } }
@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); } }
@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 }