/**
 * 定點醫院
 */
package com.NCL;

import java.util.*;
import com.sinosoft.common.*;
import java.io.File;
import jxl.*;

public class ExcelHospital {

	/**
	 * @param args
	 * @author vincent
	 * @date 2007-7-9
	 */	
	protected IndexMap propList;
	protected HashSet Property;
	private boolean EOF = false;
	private int COUNT;
	private String Type = "";
	private String area = "";
	/**
	 * 構造函數
	 *
	 */
	public ExcelHospital(){
		propList=new IndexMap();
		Property=new HashSet();
		Property.add("HospitalID");			//醫院ID
		Property.add("HospitalName");		//醫院名稱
		Property.add("HospitalTel");		//醫院電話
		Property.add("HospitalAddress");	//醫院地址
		Property.add("Authority");			//特色權威
		Property.add("County");				//區縣
		Property.add("HospitalLevel");		//醫院級別			
		Property.add("Province");			//省	
		Property.add("City");				//市
		Property.add("Note");				//備注
		
	}
	/**
	 * 初始化
	 * @param HospitalID 醫院ID
	 */
	public void init(String HospitalID){
		DBAccess d  = new DBAccess();
		String sql = "SELECT * from ExcelHospital where HospitalID=?";
		this.propList = d.init(sql,HospitalID,this.Property);		
	}
	/**
	 * 根據條件查詢
	 * @param eh		對象
	 * @param pageSize	分頁大小
	 * @param pageIndex	分頁頁碼
	 * @return String
	 */
	public String findHospital(ExcelHospital eh,int pageSize,int pageIndex){
		String result="";		
		String HospitalName = eh.get("HospitalName");
		String ProvinceName = eh.get("Province");
		String CityName = eh.get("City");
		String CountyName = eh.get("County");		
		//	得到所省，市，縣;sql所需的
		try{
			StringBuffer Condition = new StringBuffer("select HospitalID from ExcelHospital where 1=1");
			if(Data.hasValue(HospitalName))
				Condition.append(" and HospitalName like ?");			
			if(Data.hasValue(ProvinceName)){
				Condition.append(" and Province like ? ");
			}
			if(Data.hasValue(CityName)){
				Condition.append(" and City like ? ");
			}
			if(Data.hasValue(CountyName)){
				Condition.append(" and County like ? ");
			}
			DBAccess q = new DBAccess();
			result = q.executeQuery(Condition.toString(),eh.propList,pageSize,pageIndex);
			setCOUNT(q.COUNT);
			setEOF(q.EOF);
			return result;
		}catch(Exception e){
			System.out.println("ExcelHospital.findHospital() ERROR:" + e.getMessage());
			return "";
		}
	}
	/**
	 * 刪除
	 * @return boolean
	 */
	public boolean delete(){			
		StringBuffer SQL = new StringBuffer("delete excelhospital");			
		DBAccess DBA = new DBAccess();
		return DBA.execute(SQL.toString());
	}
	
	/**
	 * 分析Excel文件并入庫
	 * @param excelUrl Excel路徑
	 * @return boolean
	 */
	public boolean FromExcelToOracle(String excelUrl){
		try{
			Workbook workbook = Workbook.getWorkbook(new File(excelUrl));
		    Sheet sheet = workbook.getSheet(0);
		    int rows=sheet.getRows();//得到總行數
		      //刪除ExcelHospital表
		    if(!delete()) //20070831
		    	return false;
		    String sql = "insert into excelhospital values (HOSPITALID_SQE.nextval,?,?,?,?,?,?,?,?,?)";
		    List l = new ArrayList();
		    for(int i=1;i<rows;i++){
		    	String[] colValue = new String[9];	    	
		    	colValue[0] = sheet.getCell(0,i).getContents().trim().replaceAll("？", "");//醫院名稱
		    	colValue[1] = sheet.getCell(5,i).getContents().trim().replaceAll("？", "");//聯系電話
		    	colValue[2] = sheet.getCell(6,i).getContents().trim().replaceAll("？", "");//聯系地址
		    	colValue[3] = sheet.getCell(7,i).getContents().trim().replaceAll("？", "");//特色權威（類別）
		    	colValue[4] = sheet.getCell(3,i).getContents().trim().replaceAll("？", "");//區縣
		    	colValue[5] = sheet.getCell(4,i).getContents().trim().replaceAll("？", "");//醫院等級
		    	colValue[6] = sheet.getCell(1,i).getContents().trim().replaceAll("？", "");//省
		    	colValue[7] = sheet.getCell(2,i).getContents().trim().replaceAll("？", "");//市
		    	colValue[8] = sheet.getCell(8,i).getContents().trim().replaceAll("？", "");//備注
		        l.add(colValue);
		    }
		    DBAccess d = new DBAccess();
		    return d.mulExecute(sql,l);	
		}catch (Exception ex){
	    	ex.printStackTrace();
	    	return false;
	    }	
	}
	/**
	 * 在此映射中關聯指定值與指定鍵
	 * @param name 指定鍵
	 * @param value 指定值
	 */
	public boolean set(String name, Object value){
		if(Property.contains(name)){
			propList.put(name,value);
			return true;
		}else
			return false;		
	}
	/**
	 * 返回指定鍵在此映射中所映射的值
	 * @param name 指定鍵
	 * @return
	 */
	public String get(String name){
		if (Property.contains(name)){
			String value = (String)propList.get(name);
			if(value != null && !value.equals(""))
				return value;
			else
				return "";
		}else 
			return "";
	}
	/**
	 * 返回COUNT
	 * @return int
	 */
	public int getCOUNT() {
		return COUNT;
	}
	/**
	 * 設置COUNT
	 * @param count
	 */
	public void setCOUNT(int count) {
		COUNT = count;
	}
	/**
	 * 返回EOF
	 * @return boolean
	 */
	public boolean isEOF() {
		return EOF;
	}
	/**
	 * 設置EOF
	 * @param eof
	 */
	public void setEOF(boolean eof) {
		EOF = eof;
	}
	/**
	 * 清空propList
	 *
	 */
	public void clear(){
		propList.clear();
	}
	/**
	 * 獲取area
	 * @return String
	 */
	public String getArea() {
		return area;
	}
	/**
	 * 設置area
	 * @param area 地區
	 */
	public void setArea(String area) {
		this.area = area;
	}
	/**
	 * 獲取Type
	 * @return String
	 */
	public String getType() {
		return Type;
	}
	/**
	 * 設置Type
	 * @param type 類型
	 */
	public void setType(String type) {
		Type = type;
	}
}