紧接上节，继续贴相关的代码：
/**
* 通过配置将要展示的类型 来输出不同的格式
* @param sqlStr
* @param docType
* @param paramMap
* @param dataSetRepCnName
* @param fieldList
* @param conn
* @param type
* @return
*/
public String getPhotoUrl(String sqlStr,String docType,Map<String,String> paramMap,String dataSetRepCnName,List<FieldBean> fieldList,Connection conn,String type){
//System.out.println("sqlStr-------------:" + sqlStr);
String photoName = docType + "-";
String projectId ="";
String workgroupId ="";
String evtcaseInstId ="";
String userId ="";
String photoPath ="";
Set<Entry<String, String>> sets = paramMap.entrySet();
Iterator<Entry<String, String>> it = sets.iterator();
while (it.hasNext()) {
Map.Entry<String,String> entry = (Map.Entry<String,String>) it.next();
String key = entry.getKey();
if(key.equals("project_id")){
projectId = entry.getValue();
}else if(key.equals("workgroupId")){
workgroupId = entry.getValue();
}else if(key.equals("evtcase_inst_id")){
evtcaseInstId = entry.getValue();
}else if(key.equals("userId")){
userId = entry.getValue();
}
}
Set<Entry<String, String>> sets1 = paramMap.entrySet();
Iterator<Entry<String, String>> it1 = sets1.iterator();
//判断是否有参数存在
if(CommonUtils.isNotNull(sqlStr) && sqlStr.indexOf("$P{") != -1){//如果有参数
while (it1.hasNext()) {
Map.Entry<String,String> mapEntry = (Map.Entry<String,String>)it1.next();
String key = String.valueOf(mapEntry.getKey());
String value = String.valueOf(mapEntry.getValue());
if(CommonUtils.isNotNull(key) && sqlStr.contains("$P{"+ key +"}")){
sqlStr = sqlStr.replace("$P{"+ key +"}", "'" + value + "'");
}
}
}
//System.out.println("\n 创建图片时---最终要执行的SQL为：\n" + sqlStr);
if(CommonUtils.isNotNull(userId)){
photoName += userId + "-";
}
if(CommonUtils.isNotNull(workgroupId)){
photoName += workgroupId + "-";
}
if(CommonUtils.isNotNull(workgroupId)){
photoName += workgroupId + "-";
}
if(CommonUtils.isNotNull(evtcaseInstId)){
photoName += evtcaseInstId + "-";
}
if(CommonUtils.isNotNull(photoName)){
photoName = photoName + dataSetRepCnName + XmlPathDef.PHOTO_TYPE;
}else{
photoName = dataSetRepCnName + XmlPathDef.PHOTO_TYPE;//实际情况是不能有else的，否则的话 会出问题（多个人操作时图片会被替换）
}
//找到原来或者未生成的图片地址
photoPath = xmlPathDef.getBaseWordResFilePath(docType,projectId,workgroupId) + photoName;
String[] pplotArr = null;
if(docType.equals(XmlPathDef.ISO27001_DOC)){
pplotArr = new String[2];
pplotArr[0] = "合规性要求指标";
pplotArr[1] = "信息系统差距分析指标";
}
//判断图片是否存在，如果存在，则先删除
if(xmlPathDef.deleteFile(photoPath)){
creatPieForReport.createChartPic(sqlStr,dataSetRepCnName,fieldList,photoPath,conn,type,0, 0,pplotArr);
}
//System.out.println("image photoPath url:" + photoPath);
return photoPath;
}
/**
* 执行SQL语句,取得每个标签的值
* @param map
* @param sqlStr
* @param paramMap
* @param fieldList
* @param type
* @param conn
* @param sort
* @param docType
* @return
*/
public HashMap<String,Object> queryValue(HashMap<String,Object> map,String sqlStr,Map<String,String> paramMap,List<FieldBean> fieldList,String type,Connection conn,String sort,String docType){
//首先是将要查询的SQL语句 字段不能为空
if(CommonUtils.isNotNull(sqlStr) && fieldList != null && fieldList.size() > 0){
int num = 0;
boolean bo = false;
if(CommonUtils.isNotNull(sort)){
while(num < XmlPathDef.cellNameAndSort.length){
if(sort.equals(XmlPathDef.cellNameAndSort[num][1])){
bo = true;
break;
}
num++;
}
}
if(docType.equals(XmlPathDef.SOC_DOC) && bo){//如果为等保报告
//System.out.println("将要执行的SQL为：" + sqlStr);
Set<Entry<String, String>> sets = paramMap.entrySet();
Iterator<Entry<String, String>> it = sets.iterator();
//判断是否有参数存在
if(sqlStr.indexOf("$P{") != -1){//如果有参数
while (it.hasNext()) {
Map.Entry<String,String> mapEntry = (Map.Entry<String,String>)it.next();
String key = String.valueOf(mapEntry.getKey());
String value = String.valueOf(mapEntry.getValue());
if(CommonUtils.isNotNull(key) && sqlStr.contains("$P{"+ key +"}")){
sqlStr = sqlStr.replace("$P{"+ key +"}", "'" + value + "'");
}
}
}
List<String[]> listValue = new ArrayList<String[]>();
String[] headContent = new String[]{};
listValue.add(headContent);
String[] content = null;
//System.out.println("最终要执行的SQL为：" + sqlStr);
try {
//通过Query接口查询
stmt = conn.prepareStatement(sqlStr);
rs = stmt.executeQuery(sqlStr);
while (rs.next()) {
String layerId = rs.getString("layer_id");//取得子类id
String evtcaseInstId = rs.getString("evtcase_inst_id");//取得实例id
//String strutsName = rs.getString("name");//取得实例id
if(CommonUtils.isNotNull(layerId) && CommonUtils.isNotNull(evtcaseInstId)){
List<CellEvtContent> bean = evtcaseInstAssetContentService.count(layerId, evtcaseInstId);
List<CompontBean> compontbean = evtcaseInstService.getCompanetByInstId(layerId,evtcaseInstId);
if(compontbean != null && compontbean.size() > 0){
List<CellEvtContent> countbean = unitConformitService.getCountBean(compontbean, bean);
//Integer singleNum = evtcaseInstService.getSingleNum(layerId, evtcaseInstId);
int stnum = 1;
int sumaryvalue = 0;
int convalue = 0;
int inconvalue = 0;
if(countbean != null && countbean.size() > 0){
for (int i = 0; i < countbean.size(); i++) {
CellEvtContent cellEvtContent = (CellEvtContent)countbean.get(i);
if(cellEvtContent != null){
String assetName = cellEvtContent.getAssetName();
List<CountBean> beanList = cellEvtContent.getCountList();
content = new String[2+ compontbean.size()];//创建数组的长度
content[0] = String.valueOf(stnum);
content[1] = assetName;
cellEvtContent.getConform();
for(int j = 0; j < beanList.size(); j++) {
CountBean countBean =(CountBean)beanList.get(j);
if(countBean != null){
int summary = countBean.getSummary();
int inconformity = countBean.getInconformity();
int nacount = countBean.getNacount();
int conformity = countBean.getConformity();
if(summary == 0){
content[2+j] = "—";
}else if(inconformity == nacount){
content[2+j] = "不适用";
}else if(inconformity == 0 && summary != 0){
content[2+j] = inconformity + "/" + summary + " 符合";
}else if(inconformity == summary && summary != 0){
content[2+j] =inconformity + "/" + summary + " 不符合";
}else{
content[2+j] =inconformity+ "/" + summary + " 部分符合";
}
//如果是最后一行的统计数据
sumaryvalue += summary;
convalue += conformity;
inconvalue += inconformity;
}
}
stnum++;
//System.out.println("放入list中的数组为： "+ content);
//将表格对象放入list对象中
listValue.add(content);
}
}
}
}else{//如果没有数据
//将表格对象放入list对象中
listValue.add(content);
//System.out.println("content 无数据： " + content);
}
// String strutsName_tag = "从上表的统计结果来看," + strutsName +"单项测评共有测评指标 " + singleNum + " 个,检查对象 " + (countbean.size()-1) + " 个,实施测评项 " + sumaryvalue + " 个, 其中 " + convalue +" 个符合项, 部分符合和不符合项 "+inconvalue + " 个。";
// map.put("$" + strutsName + "$", strutsName_tag);//放入map中对用的标签值
//取得是那种报告类型,判断表格开始的序列
String docNum = xmlPathDef.getTypeNum(docType);//取得是那种报告序列
sort = Integer.parseInt(sort) + Integer.parseInt(docNum)+ "";//取得各种word模版对应的表格序列
map.put(docType + "*" + XmlPathDef.WORD_T + "@" + sort, listValue);//按照索引放入map对象中
//System.out.println("表格" + sort + "................." + docType + "_" + sort);
}
}
}catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}else{//其他报告
//System.out.println("将要执行的SQL为：" + sqlStr);
Set<Entry<String, String>> sets = paramMap.entrySet();
Iterator<Entry<String, String>> it = sets.iterator();
//判断是否有参数存在
if(sqlStr.indexOf("$P{") != -1){//如果有参数
while (it.hasNext()) {
Map.Entry<String,String> mapEntry = (Map.Entry<String,String>)it.next();
String key = String.valueOf(mapEntry.getKey());
String value = String.valueOf(mapEntry.getValue());
if(CommonUtils.isNotNull(key) && sqlStr.contains("$P{"+ key +"}")){
sqlStr = sqlStr.replace("$P{"+ key +"}", "'" + value + "'");
}
}
}
List<String[]> listValue = new ArrayList<String[]>();
String[] headContent = new String[]{};
listValue.add(headContent);
String[] content = null;
//System.out.println("最终要执行的SQL为：" + sqlStr);
try {
//通过Query接口查询
stmt = conn.prepareStatement(sqlStr);
rs = stmt.executeQuery(sqlStr);
rs.last(); //结果集指针知道最后一行数据
int count = rs.getRow();//最大行数
//System.out.println("最大行数为：" + count);
int rownum = 0;
if(count > 0){//如果最大行数大于 0 表示有结果集 进行循环操作
rs.beforeFirst();//将结果集指针指回到开始位置，这样才能通过while获取rs中的数据
while (rs.next()) {
++rownum;
content = new String[fieldList.size()];
for (int i = 0; i < fieldList.size(); i++) {
String filedKey = fieldList.get(i).getKey();
String filedColumn = fieldList.get(i).getColumn();
String filedValue = fieldList.get(i).getValue();
String ctype = fieldList.get(i).getCtype();
if(filedKey.equals("$rownum$")){//如果是带有序号的表格
filedValue = String.valueOf(rownum);
}else{
if(CommonUtils.isNotNull(filedColumn)){
filedValue = rs.getString(filedColumn);
}else{
continue;
}
}
//System.out.println(filedKey);
//System.out.println(filedValue);
if(type.equals("F")){//如果是单个字段
map.put(filedKey, filedValue==null?"":XmlPathDef.getWordFValue(ctype, filedValue));
//将创建的无用对象置为空
if(listValue != null && listValue.size() > 0){
listValue = null;
}
if(content != null && content.length > 0){
content = null;
}
}else if(type.equals(WORD_TYPE)) {//如果是表格
content[i]= (filedValue==null?" ":XmlPathDef.getWordFValue(ctype, filedValue));
}
}
if(type.equals(WORD_TYPE)){
//将表格对象放入list对象中
listValue.add(content);
}
}
if(type.equals(WORD_TYPE)){
//取得是那种报告类型,判断表格开始的序列
String docNum = xmlPathDef.getTypeNum(docType);//取得是那种报告序列
sort = Integer.parseInt(sort) + Integer.parseInt(docNum)+ "";//取得各种word模版对应的表格序列
map.put(docType + "*" + XmlPathDef.WORD_T + "@" + sort, listValue);//按照索引放入map对象中
//System.out.println("表格" + sort + "................." + docType + "_" + sort);
}
}else{
++rownum;
content = new String[fieldList.size()];
for (int i = 0; i < fieldList.size(); i++) {
String filedKey = fieldList.get(i).getKey();
if(type.equals("F")){//如果是单个字段
map.put(filedKey, "");
//将创建的无用对象置为空
if(listValue != null && listValue.size() > 0){
listValue = null;
}
if(content != null && content.length > 0){
content = null;
}
}else if(type.equals(WORD_TYPE)) {//如果是表格
content[i]= " ";
}
}
if(type.equals(WORD_TYPE)){
//将表格对象放入list对象中
listValue.add(content);
}
if(type.equals(WORD_TYPE)){
//取得是那种报告类型,判断表格开始的序列
String docNum = xmlPathDef.getTypeNum(docType);//取得是那种报告序列
sort = Integer.parseInt(sort) + Integer.parseInt(docNum)+ "";//取得各种word模版对应的表格序列
map.put(docType + "*" + XmlPathDef.WORD_T + "@" + sort, listValue);//按照索引放入map对象中
//System.out.println("表格" + sort + "................." + docType + "_" + sort);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return map;
}
（未完待续）