specailqueryimpl.java

电信的网厅的整站代码

package com.doone.fj1w.fjmgr.order.list;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import com.doone.data.DacClient;
import java.util.ArrayList;
import java.util.List;
import com.doone.data.DataTable;
import com.doone.data.DataRow;
import com.doone.fj1w.fjmgr.order.DAO;
import com.doone.util.ExtString;
import com.doone.util.FileLogger;

import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

/**
 * <strong>
 *  地市查询
 * </strong>
 * @author Administrator
 *
 */
public class SpecailQueryImpl extends DAO {

	static FileLogger _logger = new FileLogger();
	
    private static final long serialVersionUID = -4324325454341L;
    
    /**SQL Statement*/
    /*private final String SQL_QUE_PRODUCT 
       = "select t.PRODUCTID as OPTIONVALUE,t.PRODUCTNAME as OPTIONTEXT " +
         "from Td_Product t where t.upproductid=0 and t.state='E'";*/
    
    public SpecailQueryImpl() {
    }
    
    /*
     *  (non-Javadoc)
     * @param Map map
     * @see com.doone.fj1w.common.taglib.list.QueryListInterface#getList()
     */
    /*public DataTable getList(Map map) throws RuntimeException { 
    	try{
    		DataTable dt = _dac.executeQuery(SQL_QUE_PRODUCT);
    		if(dt != null) {
    			DataRow newRow = dt.newRow();
    			newRow.setValue("OPTIONVALUE", "-1");
    			newRow.setValue("OPTIONTEXT", "全部");
    			dt.getRows().add(newRow);
    			dt.acceptChanges();
    		}
    		return dt;
    		
    	}catch(SQLException sqlx){
    		_logger.warn(sqlx.getMessage(),sqlx);
    		throw new RuntimeException(sqlx);
    	}catch(RuntimeException rux){
    		_logger.warn(rux.getMessage(),rux);
    		throw new RuntimeException(rux);
    	}
    }*/
    
    /*public static Map getConfigType(Map param) {
		Map map = new HashMap();
		map.put("1", "未配置产品");
		map.put("2", "已配置产品");
		map.put("0", "全部");
		return map;
	}*/
    
    private DataTable getProductList(Map _map) {
    	DacClient db = new DacClient();
		try {
			StringBuffer sql = 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;

			buildSQL(_map,sql,oParam);	
			
			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_ > ?");
			
			System.out.println("***********:"+sql);
			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);
				System.out.println("***********:"+ap[i]);
			}
			
			return db.executeQuery(_sb.toString(), ap);
		} catch (Exception e) {
			FileLogger.getLogger().warn(e.getMessage(), e);
		}
		
		return null;
	}
    
    public int getProductListCount(ServletRequest request,
			ServletResponse response, Map _map) {
    	DacClient db = new DacClient();
		try {
			
			StringBuffer sql = new StringBuffer();
			StringBuffer sql1 = new StringBuffer();
			List oParam = new ArrayList();
			
			buildSQL(_map,sql,oParam);	
			
			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 sCityCode = (String) _map.get("CITYCODE");
    	if (ExtString.isEmpty(sCityCode) || sCityCode.equals("0590")) {
    		selectSql.append("select c.SPECACCNBRID \"码号编码\",e.cityname \"所属地市\",c.SPECACCNBR \"码号\",");
		}
    	else {
    		selectSql.append("select c.SPECACCNBRID \"码号编码\",c.SPECACCNBR \"码号\",");
    	}
    	selectSql.append("c.SPECACCNBRREMARK \"码号说明\",to_char(c.EFF_DATE,'yyyy-mm-dd') \"生效时间\",to_char(c.EXP_DATE,'yyyy-mm-dd') \"失效时间\",to_char(c.CREATE_DATE,'yyyy-mm-dd') \"创建时间\",");
    	selectSql.append("decode(c.SPECACCNBR_FLAG, '1', '允许', '0', '禁止') \"是否发布\",c.DISPLAYNAME \"显示名称\" ");
    	selectSql.append("from td_specaccnbr c, td_city e ");
	}
    
    private void buildWhereSQL(Map _map, StringBuffer whereSql, List oParam) {
		String sCityCode = (String) _map.get("CITYCODE");
		//String sProductName = (String) _map.get("PRODUCTNAME");
		//String sProductType = (String) _map.get("PRODUCTTYPE");
		//String sConfigType = (String) _map.get("CONFIGTYPE");
		
		
		whereSql.append("where c.citycode = e.citycode ");
		if (!ExtString.isEmpty(sCityCode) && !sCityCode.equals("0590")) {
			whereSql.append("and c.citycode = ? ");
			oParam.add(sCityCode);
		}
		
		/*if (!ExtString.isEmpty(sProductName)) {
			whereSql.append("and c.productname like '%' || ? || '%' ");
			oParam.add(sProductName);
		}
		
		if (!ExtString.isEmpty(sProductType) && !sProductType.equals("-1")) {
			whereSql.append("and d.productid = ? ");
			oParam.add(sProductType);
		}
		
		if (!ExtString.isEmpty(sConfigType) && !sConfigType.equals("0")) {
			if(sConfigType.equals("2")){
				whereSql.append("and c.upproductid != '99' ");
	        }
	        else if(sConfigType.equals("1")){
	        	whereSql.append("and c.upproductid = '99' ");
	        }	
		}*/

	}
    
    private void buildSQL(Map _map, StringBuffer sql, List oParam) {
    	StringBuffer selectSql = new StringBuffer();
    	StringBuffer whereSql = new StringBuffer();
    	//StringBuffer groupBySql = new StringBuffer();
    	//StringBuffer orderBySql = new StringBuffer();
		
		buildSelectSQL(_map,selectSql,oParam);
		sql.append(selectSql);
		buildWhereSQL(_map,whereSql,oParam);
		sql.append(whereSql);
	}
    
    public String genHtml(ServletRequest request,
			ServletResponse response, Map _map) {
		DataTable dt = getProductList(_map);
		StringBuffer _sb = new StringBuffer();
		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>");
				_sb.append("<td class=\"body_td\" align=\"center\" width=\"4%\" nowrap>&nbsp;<!-- 存放标识组织类别的小图 --></td>");
				for (int icol = 0;icol<colcount-1;icol++){
					com.doone.data.DataColumn dc = dcs.getDataColumn(icol);
					String columnName = dc.getColumnName();
					if(columnName.equals("码号编码"))
						_sb.append("<td class=\"body_td\" align=\"center\" style=\"display:none\" nowrap><b>");
					else
						_sb.append("<td class=\"body_td\" align=\"center\" nowrap><b>");
					_sb.append(columnName);
					_sb.append("</b></td>");
				}
				_sb.append("</tr>");
				
				for(int iresult=0;iresult<dt.getRows().getCount(); iresult++){	
					_sb.append("<tr height=\"20\">");
					DataRow drresult = dt.getRow(iresult);
					_sb.append("<td class=\"body_td\" align=\"center\" width=\"4%\" nowrap>");
					_sb.append("<input type=\"checkbox\" name=\"SPECACCNBRID\" value=\""+drresult.getString(0).trim()+"\" onclick=\"return fSelectOne(this);\" />");
					_sb.append("</td>");
					for (int icol=0; icol<colcount-1;icol++){
						com.doone.data.DataColumn dc = dcs.getDataColumn(icol);
						String columnName = dc.getColumnName();
						if(columnName.equals("码号编码"))
							_sb.append("<td class=\"body_td\" align=\"left\" style=\"display:none\" nowrap>");
						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();
	}

}