everywhereorderrptimpl.java

电信的网厅的整站代码

			idName = "appealid";
			upidName = "upappealid";
			modeNameA = "b1.appealmode,";
			modeNameB = "a1.appealmode,";
		}
		
		StringBuffer firstOrderSql = new StringBuffer();
		StringBuffer firstChildSql = new StringBuffer();
		StringBuffer upOderSql = new StringBuffer();
		firstOrderSql.append("select ").append(modeNameA).append(" b1.weborder,b1.outdate,b1.progressstate,b1.").append(timeName);
		firstOrderSql.append(",a1.").append(timeName);
		firstOrderSql.append(" finishtime ");
		firstOrderSql.append("from ").append(tableName).append(" a1, ").append(tableName).append(" b1 ");
		firstOrderSql.append("where a1.").append(idName).append(" = (");
		
		firstChildSql.append("select ").append(idName).append(" from (");
		firstChildSql.append("select * from ").append(tableName).append(" where ").append(upidName).append(" > 0 order by ").append(upidName).append(", ").append(timeName).append(" asc");
		firstChildSql.append(") c1 ");
		firstChildSql.append("where c1.").append(upidName).append(" = b1.").append(idName).append(" and rownum = 1");
		
		firstOrderSql.append(firstChildSql);
		firstOrderSql.append(") ");
		firstOrderSql.append("and b1.").append(upidName).append(" = 0 ");
		
		upOderSql.append("select ").append(modeNameB).append(" a1.weborder,a1.outdate,a1.progressstate,a1.").append(timeName).append(",a1.finishtime finishtime ");
		upOderSql.append("from ").append(tableName).append(" a1 ");
		upOderSql.append("where not exists (select 1 from tf_prodorder where ").append(upidName).append(" = a1.").append(idName).append(") ");
		upOderSql.append("and a1.").append(upidName).append(" = 0 ");
		
		if(sRptType == null || sRptType.equals("") || sRptType.equals("1")) {
			orderSql.append(" (");
			orderSql.append(firstOrderSql);
			orderSql.append(" union all ");
			orderSql.append(upOderSql);
			orderSql.append(") ");
		}
		else if(sRptType.equals("2")) {
			orderSql.append(" ").append(tableName).append(" ");
		}
		
	}
	
	private void buildAppealSQL(Map _map, StringBuffer appealSql, List oParam,boolean isOverTime,String appealmode) {
		int orderType = 5;
		if (appealmode.equals("1") && isOverTime)
			orderType = 5;
		else if (appealmode.equals("1") && !isOverTime)
			orderType = 6;
		if (appealmode.equals("2") && isOverTime)
			orderType = 7;
		else if (appealmode.equals("2") && !isOverTime)
			orderType = 8;
		if (appealmode.equals("3") && isOverTime)
			orderType = 9;
		else if (appealmode.equals("3") && !isOverTime)
			orderType = 10;
		StringBuffer selectSql = new StringBuffer();
		StringBuffer whereSql = new StringBuffer();
		StringBuffer groupBySql = new StringBuffer();
		StringBuffer orderSql = new StringBuffer();
		
		buildSelectSQL(_map,selectSql,oParam,orderType);
		appealSql.append(selectSql);
		buildOrderSQL(_map,orderSql,oParam,"appeal");
		appealSql.append("from ").append(orderSql).append(" c ");
		buildWhereSQL(_map,whereSql,oParam,orderType);
		appealSql.append(whereSql);
		buildGroupBySQL(_map,groupBySql,oParam,orderType);
		appealSql.append(groupBySql);
	}
	
	
	private void buildSQL(Map _map, StringBuffer sql, List oParam) {
		StringBuffer prodorderSql = new StringBuffer();
		StringBuffer funorderSql = new StringBuffer();
		StringBuffer appealSql = new StringBuffer();
		StringBuffer adviceSql = new StringBuffer();
		StringBuffer faultSql = new StringBuffer();
		
		int orderType = 0;
		StringBuffer selectSql = new StringBuffer();
		buildSelectSQL(_map,selectSql,oParam,orderType);
		sql.append(selectSql);
		sql.append("from (");
		boolean isOverTime = true;
		buildProdorderSQL(_map,prodorderSql,oParam,isOverTime);
		sql.append(prodorderSql);
		sql.append(") t1,(");
		isOverTime = false;
		prodorderSql = new StringBuffer();
		buildProdorderSQL(_map,prodorderSql,oParam,isOverTime);
		sql.append(prodorderSql);
		sql.append(") t2,(");
		isOverTime = true;
		buildFunorderSQL(_map,funorderSql,oParam,isOverTime);
		sql.append(funorderSql);
		sql.append(") t3,(");
		isOverTime = false;
		funorderSql = new StringBuffer();
		buildFunorderSQL(_map,funorderSql,oParam,isOverTime);
		sql.append(funorderSql);
		sql.append(") t4,(");
		isOverTime = true;
		buildAppealSQL(_map,appealSql,oParam,isOverTime,"1");
		sql.append(appealSql);
		sql.append(") t5,(");
		isOverTime = false;
		appealSql = new StringBuffer();
		buildAppealSQL(_map,appealSql,oParam,isOverTime,"1");
		sql.append(appealSql);
		sql.append(") t6,(");
		isOverTime = true;
		buildAppealSQL(_map,adviceSql,oParam,isOverTime,"2");
		sql.append(adviceSql);
		sql.append(") t7,(");
		isOverTime = false;
		adviceSql = new StringBuffer();
		buildAppealSQL(_map,adviceSql,oParam,isOverTime,"2");
		sql.append(adviceSql);
		sql.append(") t8,(");
		isOverTime = true;
		buildAppealSQL(_map,faultSql,oParam,isOverTime,"3");
		sql.append(faultSql);
		sql.append(") t9,(");
		isOverTime = false;
		faultSql = new StringBuffer();
		buildAppealSQL(_map,faultSql,oParam,isOverTime,"3");
		sql.append(faultSql);
		sql.append(") t10,(");
		sql.append("select cityname,citycode from td_city where citycode in ('0590','0591','0592','0595')) t11 ");
		StringBuffer whereSql = new StringBuffer();
		buildWhereSQL(_map,whereSql,oParam,orderType);
		sql.append(whereSql);
	}
	
	public String genHtml(Map _map) {
		DataTable dt = getRptList(_map);
		StringBuffer _sb = new StringBuffer();
		if ( dt != null ) {
			_sb.append("<table>");
			try {
				com.doone.data.DataColumnCollection dcs = dt.getColumns();
				int colcount = dcs.getCount();
				_sb.append("<tr>");
				for (int icol = 0;icol<colcount-1;icol++){
					com.doone.data.DataColumn dc = dcs.getDataColumn(icol);
					String columnName = dc.getColumnName();
					if(!columnName.equals("CITYCODE")) {
						if(columnName.equals("地区")) {
							_sb.append("<td class=\"body_td\" align=\"center\" rowspan=\"2\" nowrap><b>");
							_sb.append(columnName);
							_sb.append("</b></td>");
						}
						else if(columnName.equals("业务受理单超时") ||
								columnName.equals("程控受理单超时") ||
								columnName.equals("故障申告单超时") ||
								columnName.equals("服务投诉单超时") ||
								columnName.equals("建议单超时")) {
							_sb.append("<td class=\"body_td\" align=\"center\" colspan=\"2\" nowrap><b>");
							_sb.append(columnName.substring(0,columnName.indexOf("单")+1));
							_sb.append("</b></td>");
						}
						else if(columnName.equals("超时")){
							_sb.append("<td class=\"body_td\" align=\"center\" colspan=\"3\" nowrap><b>");
							_sb.append("总数");
							_sb.append("</b></td>");
						}	
					}
				}
				_sb.append("</tr>");
				
				_sb.append("<tr>");
				for (int icol = 0;icol<colcount-1;icol++){
					com.doone.data.DataColumn dc = dcs.getDataColumn(icol);
					String columnName = dc.getColumnName();
					if(!columnName.equals("CITYCODE")) {
						if(columnName.equals("业务受理单超时") ||
								columnName.equals("业务受理单未超时") ||
								columnName.equals("程控受理单超时") ||
								columnName.equals("程控受理单未超时") ||
								columnName.equals("故障申告单超时") ||
								columnName.equals("故障申告单未超时") ||
								columnName.equals("服务投诉单超时") ||
								columnName.equals("服务投诉单未超时") ||
								columnName.equals("建议单超时") ||
								columnName.equals("建议单未超时")) {
							_sb.append("<td class=\"body_td\" align=\"center\" nowrap>");
							_sb.append(columnName.substring(columnName.indexOf("单")+1,columnName.length()));
							_sb.append("</TD>");
						}
						else if(columnName.equals("超时") ||
								columnName.equals("未超时") ||
								columnName.equals("总计")) {
							_sb.append("<td class=\"body_td\" align=\"center\" nowrap>");
							_sb.append(columnName);
							_sb.append("</TD>");
						}	
					}
				}
				_sb.append("</tr>");
				
				List prodoverTime = new ArrayList();
				List prodnoOverTime = new ArrayList();
				List funoverTime = new ArrayList();
				List funnoOverTime = new ArrayList();
				List faultoverTime = new ArrayList();
				List faultnoOverTime = new ArrayList();
				List appealOverTime = new ArrayList();
				List appealnoOverTime = new ArrayList();
				List adviceOverTime = new ArrayList();
				List advicenoOverTime = new ArrayList();
				List overTime = new ArrayList();
				List noOverTime = new ArrayList(); 
				List lTotal = new ArrayList(); 
				for(int iresult=0;iresult<dt.getRows().getCount(); iresult++){	
					_sb.append("<tr height=\"20\">");			
					DataRow drresult = dt.getRow(iresult);
					for (int icol=0; icol<colcount-1;icol++){
						com.doone.data.DataColumn dc = dcs.getDataColumn(icol);
						String columnName = dc.getColumnName();
						if(!columnName.equals("CITYCODE")) {
							if(columnName.equals("地区") ||
									columnName.equals("超时") ||
									columnName.equals("未超时") ||
									columnName.equals("总计")) {
								_sb.append("<td class=\"body_td\" align=\"left\" nowrap>");
								_sb.append(drresult.getString(icol).trim().equals("")?"&nbsp;":drresult.getString(icol).trim());
								_sb.append("</td>");
								setTotal(columnName,"超时",drresult.getString("超时").trim(),overTime);
								setTotal(columnName,"未超时",drresult.getString("未超时").trim(),noOverTime);
								setTotal(columnName,"总计",drresult.getString("总计").trim(),lTotal);
							}
							else if(columnName.equals("业务受理单超时") ||
									columnName.equals("业务受理单未超时") ||
									columnName.equals("程控受理单超时") ||
									columnName.equals("程控受理单未超时") ||
									columnName.equals("故障申告单超时") ||
									columnName.equals("故障申告单未超时") ||
									columnName.equals("服务投诉单超时") ||
									columnName.equals("服务投诉单未超时") ||
									columnName.equals("建议单超时") ||
									columnName.equals("建议单未超时")) {
								_sb.append("<td class=\"body_td\" align=\"left\" nowrap>");
								String sValue = drresult.getString(icol).trim();
								_sb.append(sValue.equals("")?"&nbsp;":sValue);
								String sPercent = drresult.getString(columnName + "百分比");
								if(sPercent != null &&
										!sPercent.equals("")) {
									_sb.append("<br>");
									if(sPercent.trim().startsWith(".")) {
										_sb.append("0"+sPercent.trim());
									}
									else {
										_sb.append(sPercent.trim());
									}
								}
								_sb.append("</td>");

								setTotal(columnName,"业务受理单超时",drresult.getString("业务受理单超时").trim(),prodoverTime);
								setTotal(columnName,"业务受理单未超时",drresult.getString("业务受理单未超时").trim(),prodnoOverTime);
								setTotal(columnName,"程控受理单超时",drresult.getString("程控受理单超时").trim(),funoverTime);
								setTotal(columnName,"程控受理单未超时",drresult.getString("程控受理单未超时").trim(),funnoOverTime);
								setTotal(columnName,"故障申告单超时",drresult.getString("故障申告单超时").trim(),faultoverTime);
								setTotal(columnName,"故障申告单未超时",drresult.getString("故障申告单未超时").trim(),faultnoOverTime);
								setTotal(columnName,"服务投诉单超时",drresult.getString("服务投诉单超时").trim(),appealOverTime);
								setTotal(columnName,"服务投诉单未超时",drresult.getString("服务投诉单未超时").trim(),appealnoOverTime);
								setTotal(columnName,"建议单超时",drresult.getString("建议单超时").trim(),adviceOverTime);
								setTotal(columnName,"建议单未超时",drresult.getString("建议单未超时").trim(),advicenoOverTime);
								
							}
						}
					}
					_sb.append("</tr>");
				}
				_sb.append("<tr>");
				_sb.append("<td class=\"body_td\" align=\"left\" nowrap>");
				_sb.append("合计");
				_sb.append("</td>");
				buildTotalHtml(_sb,prodoverTime);
				buildTotalHtml(_sb,prodnoOverTime);
				buildTotalHtml(_sb,funoverTime);
				buildTotalHtml(_sb,funnoOverTime);
				buildTotalHtml(_sb,faultoverTime);
				buildTotalHtml(_sb,faultnoOverTime);
				buildTotalHtml(_sb,appealOverTime);
				buildTotalHtml(_sb,appealnoOverTime);
				buildTotalHtml(_sb,adviceOverTime);
				buildTotalHtml(_sb,advicenoOverTime);
				buildTotalHtml(_sb,overTime);
				buildTotalHtml(_sb,noOverTime);
				buildTotalHtml(_sb,lTotal);
				_sb.append("<tr>");
			} catch (Exception e) {
				FileLogger.getLogger().info(e.getMessage(),e);
			}
			_sb.append("</table>");
		}
		
		return _sb.toString();
	}
	
	/**计算合计数
	 * 
	 * @param columnName
	 * @param reColumnName
	 * @param sValue
	 * @param lList
	 */
	public void setTotal(String columnName,String reColumnName,String sValue,List lList) {
		if(columnName.equals(reColumnName)) {
			if(sValue == null ||sValue.equals("")) sValue = "0";
			lList.add(sValue);
		}
	}
	
	public String getTotal(List lList) {
		int iValue = 0;
		for (int i = 0; i < lList.size(); i++) {
			iValue += Integer.parseInt((String)lList.get(i));
		}
		return String.valueOf(iValue);
	}
	
	public void buildTotalHtml(StringBuffer sql,List lList) {
		sql.append("<td class=\"body_td\" align=\"left\" nowrap>");
		sql.append(getTotal(lList));
		sql.append("</td>");
	}
	
	public void buildTotalExecl(StringBuffer content,List lList) {
		content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
		content.append(getTotal(lList));
		content.append("</ss:Data></ss:Cell>\n");
	}
	
}