tbcauselogimpl.java

电信的网厅的整站代码

package com.doone.fj1w.fjmgr.rpt;

import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.doone.data.DacClient;
import com.doone.data.DataRow;
import com.doone.data.DataTable;
import com.doone.fj1w.fjmgr.order.CallTuxdoInter;
import com.doone.iossp.EMessage;
import com.doone.util.ExtString;
import com.doone.util.FileLogger;

public class TBCauselogImpl extends ReportImpl {
	
	
	protected DataTable getRptList(Map _map) {
		DacClient db = new DacClient();
		try {
			StringBuffer sql = new StringBuffer();
			StringBuffer selectSql = new StringBuffer();
			StringBuffer fromSql = new StringBuffer();
			StringBuffer whereSql = new StringBuffer();
			StringBuffer groupBySql = new StringBuffer();
			StringBuffer orderBySql = new StringBuffer();
			List oParam = new ArrayList();
			
			int pageSize = Integer.parseInt((String) _map.get("pageSize"));
			int currentPage = Integer.parseInt((String) _map.get("currentPage"));
			
			int startrecord = currentPage * pageSize;
			int endrecord = pageSize;
			if (startrecord < 0)
				startrecord = 0;
			if (endrecord > 0 && startrecord > 0)
				endrecord = startrecord + pageSize;
			
			buildSelectSQL(_map, selectSql,oParam);
			sql.append(selectSql);
			buildFromSQL(_map, fromSql,oParam);
			sql.append(fromSql);
			buildWhereSQL(_map, whereSql, oParam);
			sql.append(whereSql);
			buildGroupBySQL(_map, groupBySql);
			sql.append(groupBySql);
			buildOrderBySQL(_map, orderBySql);
			sql.append(orderBySql);
			
			StringBuffer _sb = new StringBuffer();
			_sb.append("SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM(");
			_sb.append(sql);
			_sb.append(")row_ WHERE rownum <= ?");
			_sb.append(") WHERE rownum_ > ?");
			
			oParam.add(new Integer(endrecord));
			oParam.add(new Integer(startrecord));
			
			Object ap[] = new Object[oParam.size()];
			
			for (int i = 0; i < ap.length; i++) {
				ap[i] = oParam.get(i);
			}
			
			return db.executeQuery(_sb.toString(), ap);
		} catch (Exception e) {
			FileLogger.getLogger().warn(e.getMessage(), e);
		}
		
		return null;
	}
	
	/**用于导出Excel页面获取报表
	 * 
	 */
	public DataTable getAllList(Map _map) {
		DacClient db = new DacClient();
		try {
			
			StringBuffer sql = new StringBuffer();
			StringBuffer selectSql = new StringBuffer();
			StringBuffer fromSql = new StringBuffer();
			StringBuffer whereSql = new StringBuffer();
			StringBuffer groupBySql = new StringBuffer();
			StringBuffer orderBySql = new StringBuffer();
			List oParam = new ArrayList();
			
			buildSelectSQL(_map, selectSql,oParam);
			sql.append(selectSql);
			buildFromSQL(_map, fromSql,oParam);
			sql.append(fromSql);
			buildWhereSQL(_map, whereSql, oParam);
			sql.append(whereSql);
			buildGroupBySQL(_map, groupBySql);
			sql.append(groupBySql);
			buildOrderBySQL(_map, orderBySql);
			sql.append(orderBySql);
			
			Object ap[] = new Object[oParam.size()];
			
			for (int i = 0; i < ap.length; i++) {
				ap[i] = oParam.get(i);
			}
			
			return db.executeQuery(sql.toString(), ap);
		} catch (Exception e) {
			FileLogger.getLogger().warn(e.getMessage(), e);
		}
		
		return null;
	}
	
	/**获取按条件总计的条数
	 * 
	 * @param _map
	 * @return
	 */
	private int getTBCauseLogSum(Map _map) {
		DacClient db = new DacClient();
		try {
			
			StringBuffer sql = new StringBuffer();
			StringBuffer fromSql = new StringBuffer();
			StringBuffer whereSql = new StringBuffer();
			StringBuffer groupBySql = new StringBuffer();
			StringBuffer orderBySql = new StringBuffer();
			List oParam = new ArrayList();
			StringBuffer sql1 = new StringBuffer();
			
			sql.append("select count(1) \"数量\" ");
			buildFromSQL(_map, fromSql,oParam);
			sql.append(fromSql);
			buildWhereSQL(_map, whereSql, oParam);
			sql.append(whereSql);
			buildGroupBySQL(_map, groupBySql);
			sql.append(groupBySql);
			buildOrderBySQL(_map, orderBySql);
			sql.append(orderBySql);
			
			sql1.append("select sum(\"数量\") from(");
			sql1.append(sql).append(")");
			
			Object ap[] = new Object[oParam.size()];
			for (int i = 0; i < ap.length; i++) {
				ap[i] = oParam.get(i);
			}
			
			String sum = db.getStringFromSqlQuery(sql1.toString(), ap);
			//System.out.println("总记数：" + sum);
			if(sum == null || sum.equals("") || sum.equals("")) sum = "0";
			return Integer.parseInt(sum);
		} catch (Exception e) {
			FileLogger.getLogger().warn(e.getMessage(), e);
		}
		
		return 0;

	}
	
	public String getSum(ServletRequest request, ServletResponse response, Map _map) {
		String sum = String.valueOf(getTBCauseLogSum(_map));
		return sum;

	}
	
	/**用于分页
	 * 
	 */
	protected int getRptListCount(Map _map) {
		DacClient db = new DacClient();
		try {
			StringBuffer sql = new StringBuffer();
			StringBuffer fromSql = new StringBuffer();
			StringBuffer whereSql = new StringBuffer();
			StringBuffer groupBySql = new StringBuffer();
			StringBuffer orderBySql = new StringBuffer();
			List oParam = new ArrayList();
			StringBuffer sql1 = new StringBuffer();
			
			sql.append("select count(1) \"数量\" ");
			buildFromSQL(_map, fromSql,oParam);
			sql.append(fromSql);
			buildWhereSQL(_map, whereSql, oParam);
			sql.append(whereSql);
			buildGroupBySQL(_map, groupBySql);
			sql.append(groupBySql);
			buildOrderBySQL(_map, orderBySql);
			sql.append(orderBySql);
			
			sql1.append("select count(1) from(");
			sql1.append(sql).append(")");
			
			Object ap[] = new Object[oParam.size()];
			for (int i = 0; i < ap.length; i++) {
				ap[i] = oParam.get(i);
			}

			String count = db.getStringFromSqlQuery(sql1.toString(), ap);
			//System.out.println("记录数：" + count);
			return Integer.parseInt(count);
		} catch (Exception e) {
			FileLogger.getLogger().warn(e.getMessage(), e);
		}
		
		return 0;
	}
	
	private void buildSelectSQL(Map _map, StringBuffer selectSql, List oParam) {
		String sProductType = (String) _map.get("PRODUCTTYPE");
		
		selectSql.append("select ");
		if(ExtString.isEmpty(sProductType) || sProductType.equals("A")) {
			selectSql.append("t2.productid,t2.productname \"产品名称\",");
		}
		selectSql.append("t1.otherdesc \"停机保号原因\",");
		selectSql.append("count(1) \"数量\",");
		selectSql.append("round(count(1) / ? * 100, 2) || '%' \"占比情况\" ");
		
		int sum = getTBCauseLogSum(_map);
		oParam.add(new Integer(sum));

	}
	
	private void buildFromSQL(Map _map, StringBuffer fromSql, List oParam) {
		fromSql.append("from tf_prodorder t1 ");
		fromSql.append(",td_product t2 ");
	}
	
	private void buildWhereSQL(Map _map, StringBuffer whereSql, List oParam) {
		String sCityCode = (String) _map.get("CITYCODE");
		String sStartTime = (String) _map.get("STARTTIME");
		String sEndTime = (String) _map.get("ENDTIME");
		String sProductType = (String) _map.get("PRODUCTTYPE");
		
		whereSql.append("where t1.operationtype = 'TB' ");
		whereSql.append("and t1.productid = t2.productid ");
		
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			whereSql.append("and Substr(t1.weborder, 5, 4) = ? ");
			oParam.add(sCityCode);
		}
		
		if (!ExtString.isEmpty(sStartTime)) {
			whereSql.append("and ");
			whereSql.append("t1.accepttime >= to_date(? || ' 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ");
			oParam.add(sStartTime);
		}

		if (!ExtString.isEmpty(sEndTime)) {
			whereSql.append("and ");
			whereSql.append("t1.accepttime <= to_date(? || ' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') ");
			oParam.add(sEndTime);
		}
		
		if (!ExtString.isEmpty(sProductType) && !sProductType.equals("A")) {
			whereSql.append("and ");
			whereSql.append("t1.productid = ? ");
			oParam.add(sProductType);
		}
	}
	
	private void buildGroupBySQL(Map _map, StringBuffer groupBySql) {
		groupBySql.append("group by t2.productid,t2.productname ,t1.otherdesc ");
	}
	
	private void buildOrderBySQL(Map _map, StringBuffer orderBySql) {
		orderBySql.append("order by t1.otherdesc ");
	}
	
	public String genHtml(Map _map) {
		DataTable dt = getRptList(_map);
		StringBuffer _sb = new StringBuffer();
		String sProductType = (String) _map.get("PRODUCTTYPE");
		DataTable causeDt = null;
		if ( dt != null && dt.getRows().getCount() > 0 ) {
			_sb.append("<table>");
			try {
				com.doone.data.DataColumnCollection dcs = dt.getColumns();
				int colcount = dcs.getCount();
				_sb.append("<tr>");
				if (ExtString.isEmpty(sProductType) || sProductType.equals("A")) {
					for (int icol = 1;icol<colcount-1;icol++){
						com.doone.data.DataColumn dc = dcs.getDataColumn(icol);
						_sb.append("<td class=\"body_td\" align=\"center\" nowrap><b>");
						_sb.append(dc.getColumnName());
						_sb.append("</b></TD>");
					}
				}
				else {
					for (int icol = 0;icol<colcount-1;icol++){
						com.doone.data.DataColumn dc = dcs.getDataColumn(icol);
						_sb.append("<td class=\"body_td\" align=\"center\" nowrap><b>");
						_sb.append(dc.getColumnName());
						_sb.append("</b></TD>");
					}
				}
				_sb.append("</tr>");
				
				boolean isNull = true;
				for(int iresult=0;iresult<dt.getRows().getCount(); iresult++){	
					_sb.append("<tr height=\"20\">");			
					DataRow drresult = dt.getRow(iresult);
					if (ExtString.isEmpty(sProductType) || sProductType.equals("A")) {
						String productId = drresult.getString("productid");
						causeDt = qryStopCause(productId,"5","0595");
						for (int icol=1; icol<colcount-1;icol++){
							if(icol == 2 && causeDt != null ) {
								for(int i=0;i<causeDt.getRows().getCount();i++){
									DataRow causeDr = causeDt.getRow(i);
									if(causeDr.getString("ORDER_DT_HISITOUT_REASON_ID").equals(drresult.getString(icol).trim())) {
										_sb.append("<td class=\"body_td\" align=\"left\" nowrap>");
										String sReasonName = causeDr.getString("ORDER_DT_HISITOUT_REASON_NAME");
										if(sReasonName == null || sReasonName.equals(""))
											_sb.append("&nbsp;");
										else
											_sb.append(sReasonName);
										_sb.append("</TD>");
										isNull = false;
										break;
									}
								}
								if(isNull) {
									_sb.append("<td class=\"body_td\" align=\"left\" nowrap>");
									_sb.append("&nbsp;");
									_sb.append("</TD>");
								}
								isNull = true;
							}
							else {
								_sb.append("<td class=\"body_td\" align=\"left\" nowrap>");
								_sb.append(drresult.getString(icol).trim().equals("")?"&nbsp;":drresult.getString(icol).trim());
								_sb.append("</TD>");
							}
	
						}
					}
					else {
						causeDt = qryStopCause(sProductType,"5","0595");
						for (int icol=0; icol<colcount-1;icol++){
							if(icol == 0 && causeDt != null ) {
								for(int i=0;i<causeDt.getRows().getCount();i++){
									DataRow causeDr = causeDt.getRow(i);
									if(causeDr.getString("ORDER_DT_HISITOUT_REASON_ID").equals(drresult.getString(icol).trim())) {
										_sb.append("<td class=\"body_td\" align=\"left\" nowrap>");
										String sReasonName = causeDr.getString("ORDER_DT_HISITOUT_REASON_NAME");
										if(sReasonName == null || sReasonName.equals(""))
											_sb.append("&nbsp;");
										else
											_sb.append(sReasonName);
										_sb.append("</TD>");
										isNull = false;
										break;
									}
								}
								if(isNull) {
									_sb.append("<td class=\"body_td\" align=\"left\" nowrap>");
									_sb.append("&nbsp;");
									_sb.append("</TD>");
								}
								isNull = true;
							}
							else {
								_sb.append("<td class=\"body_td\" align=\"left\" nowrap>");
								_sb.append(drresult.getString(icol).trim().equals("")?"&nbsp;":drresult.getString(icol).trim());
								_sb.append("</TD>");
							}
	
						}
					}
					_sb.append("</tr>");
				}
			} catch (Exception e) {
				FileLogger.getLogger().info(e.getMessage(),e);
			}
			_sb.append("</table>");
		}
		else if(dt == null || dt.getRows().getCount() == 0 ) {
			_sb.append("<table>");
			_sb.append("<tr key=\"-1\" type=\"\">");
			_sb.append("<td class=\"InnerHead\" style=\"cursor: default;\" onclick=\"\" colspan=\"6\">没有找到任何记录信息</td>");
			_sb.append("</tr>");
			_sb.append("</table>");
		}
		return _sb.toString();
	}
	
	/**停机原因调接口
	 * 
	 * @return
	 */
	public DataTable qryStopCause(String SERV_TYPEiSERV_TYPE_ID,
			                      String SO_TYPEiSO_TYPE_ID,
			                      String cityCode){
		DataTable dt = null;
		try {
			Hashtable ht = new Hashtable();

			ht.put("OPER_LOG_ID","QZ00015822");
			ht.put("STAFF_ID","QZ02900");
			ht.put("SERIAL1","20030318200330342682032");
			ht.put("SERIAL2","1");
			ht.put("SERIAL3","1");
			ht.put("MODULE_ID","1");
			ht.put("MENU_ID","1");
			ht.put("OPER_CODE","A");
			ht.put("SERV_TYPEiSERV_TYPE_ID",SERV_TYPEiSERV_TYPE_ID);//小灵通服务类型编码
			ht.put("SO_TYPEiSO_TYPE_ID",SO_TYPEiSO_TYPE_ID);////申请类型编码(停机保号)
			
			String methodname = "WebQryReason";
			
			String result = CallTuxdoInter.callTuxdo(ht,
					CallTuxdoInter.BSS_SERVICENAME,
					cityCode,
					methodname);
			
			EMessage msg = new EMessage(result);
			if("".equals(msg.getBody())) return null;
			
			dt = msg.getDataTableBody();

		} catch (Exception ex) {
			FileLogger.getLogger().debug(
					"\n 停机原因调接口(PhoneFunListThird)出错(WEB)" +
					ex);
		}
		return dt;
	}
	
	public static String getHistoryProductType(HttpServletRequest request,HttpServletResponse response, Map _map) {
		String productType = "";
		try {
			productType = AccesslogImpl.getHistoryByName(request,response,_map,"productType");
			if (productType == null) productType = "";
		}catch(Exception e) {
			FileLogger.getLogger().info(e.getMessage(),e);
		}
		return productType;
	}

}