package db.FrmSFXMDb;

import java.sql.*;

public class FrmSFXMDb {
	private	String dbDriver = "sun.jdbc.odbc.JdbcOdbcDriver";//驅動字符串
	private	String dbConnStr = "jdbc:odbc:Dbserver";//連接字符串
	private	Connection conn;	//連接對象
    private Statement stmt;   		//JDBC聲明 
    private String psSQL = null;
    private String[] preSQL;
    
    public ResultSet prs;
 
    public boolean getConnection()
	{
		try
		{
				Class.forName(dbDriver);
				conn = DriverManager.getConnection(dbConnStr);
				//設置不自動提交
				conn.setAutoCommit(false);
				stmt = conn.createStatement();
		}
		catch(Exception e)
		{
			System.out.print(e.getMessage());
			e.printStackTrace();
			return false;
		}
		return true;
	}	
	
	/**
	* 函數名：getTreeItemData
	* 編寫者：YJ
	* 功  能：返回醫療服務信息，為創建樹做好基礎數據
	* 輸入參數：醫療服務分類標志
	* 輸出參數：醫療信息服務
	* 備  注：
	*/
	
	public void getTreeItemData(String ssFlg)
	{
		psSQL = "SELECT * FROM SFFL ";
		psSQL = psSQL + "WHERE FLDM LIKE '";
		psSQL = psSQL + ssFlg;
		psSQL = psSQL + "[A-Z]%' ";//匹配A%或者B%，但不包括A和B
		psSQL = psSQL + "ORDER BY FLDM ";
		
		try
		{
			prs = stmt.executeQuery(psSQL);
		}
		catch(SQLException se)
		{
			System.out.print(se.getMessage());
			se.printStackTrace();
		}
	}
	
	/**
	* 函數名：getSfxmData()
	* 編寫者：XY
	* 功  能：返回收費標準信息
	* 輸入參數：無
	* 輸出參數：無
	* 備  注：
	*/	
	
	public void getSfxmData()
	{	
		psSQL = "SELECT * FROM SFBZ ";
		try
		{
		    
			 
				 
			prs = stmt.executeQuery(psSQL);
				
				
			}
		catch(SQLException se)
		{
			System.out.print(se.getMessage());
			se.printStackTrace();
		}
		}
	
	/**
	* 函數名：getSfnm()
	* 編寫者：XY
	* 功  能：返回最大收費標準內部代碼
	* 輸入參數：無
	* 輸出參數：最大收費標準內部代碼
	* 備  注：
	*/	
	
	public String getSfnm()
	{	int maxSfnm=0;
		String maxSfnmData;
		char s[];
		
		psSQL = " SELECT MAX(SFNM) as SFNM FROM SFBZ ";
		try
		{	
			
			prs = stmt.executeQuery(psSQL);
			prs.next();
			
			maxSfnm= prs.getInt("SFNM");
			
				
			}
			
		catch(SQLException se)
		{
			System.out.print(se.getMessage());
			se.printStackTrace();
		}
			maxSfnm=maxSfnm+1000001;
		
		    maxSfnmData = String.valueOf(maxSfnm);
		    
		    
		    s=new char[6];
		    maxSfnmData.getChars(1,7,s,0);
		    
		    maxSfnmData= String.valueOf(s);
		    
			return maxSfnmData;
	
		}
	
	/**
	* 函數名：Insert()
	* 編寫者：XY
	* 功  能：添加收費標準
	* 輸入參數：收費標準內部代碼、收費項目名、輸入代碼、單價、單位、自費比例、分類代碼、外部連接代碼、備注
	* 輸出參數：是否插入成功 
	* 備  注：
	*/	
	
	public boolean Insert(String SfnmNa, 
						  String SfxmName, 
						  String SfsrName,
						  float SfdjName, 
						  String JjdwName, 
						  String ZfblName, 
						  String SfflName, 
						  String SfwmName, 
						  String BzName )
		{ 
	
		psSQL = "INSERT INTO [SFBZ]([SFNM],[SFXM],[SFSR],[SFDJ],[JJDW],[ZFBL],[SFFL],[SFWM],[BZ])VALUES(";
		psSQL = psSQL +"'"+ SfnmNa+"'"+",";
		psSQL = psSQL +"'"+ SfxmName+"'"+",";
		psSQL = psSQL +"'"+ SfsrName+"'"+",";
		psSQL = psSQL + SfdjName+",";
		psSQL = psSQL +"'"+JjdwName+"'"+",";
		psSQL = psSQL + "'"+ZfblName+"'"+",";
		psSQL = psSQL + "'"+SfflName+"'"+",";
		psSQL = psSQL + "'"+SfwmName+"'"+",";
		psSQL = psSQL +"'"+ BzName+"'";
		psSQL = psSQL + ")";
		
		System.out.println(psSQL);	
			try{
			   stmt.executeUpdate(psSQL);
			   conn.commit();
			   return true;
			}
			catch (SQLException e){
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
			return false;
	}
	
	/**
	* 函數名：Update()
	* 編寫者：XY
	* 功  能：修改收費標準
	* 輸入參數：收費標準內部代碼、收費項目名、輸入代碼、單價、單位、自費比例、分類代碼、外部連接代碼、備注
	* 輸出參數：是否修改成功 
	* 備  注：
	*/	
	
	public boolean Update(String SfnmNam,
						  String SfxmNam, 
						  String SfsrNam,
						  float SfdjNam, 
						  String JjdwNam, 
						  String ZfblNam, 
						  String SfflNam, 
						  String SfwmNam, 
						  String BzNam)
		{
			psSQL = "UPDATE [SFBZ] ";
			psSQL = psSQL +"SET [SFXM]="+"'"+SfxmNam+"'"+",";
			psSQL = psSQL +"[SFSR]="+"'"+SfsrNam+"'"+",";
			psSQL = psSQL +"[SFDJ]="+SfdjNam+",";
			psSQL = psSQL +"[JJDW]="+"'"+JjdwNam+"'"+",";
			psSQL = psSQL +"[ZFBL]="+"'"+ZfblNam+"'"+",";
			psSQL = psSQL +"[SFFL]="+"'"+SfflNam+"'"+",";
			psSQL = psSQL +"[SFWM]="+"'"+SfwmNam+"'"+",";
			psSQL = psSQL +"[BZ]="+"'"+BzNam+"'";
			
			psSQL = psSQL +"WHERE [SFNM] LIKE '";
			psSQL = psSQL + SfnmNam + "'";
			
			System.out.println(psSQL);
			
			
			
			
			try{
			   stmt.executeUpdate(psSQL);
			   conn.commit();
			   return true;
			}
			catch (SQLException e){
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
			
			
			return false;
			}
			
			
				 			
	
	/*
	*關閉數據庫
	*/	
	public void closeDB()
	{
		try
		{
			conn.commit();
			if (!conn.isClosed())
				conn.close();
				System.out.println("數據庫關閉");
		}
		catch(SQLException se)
		{
			System.out.print(se.getMessage());
			se.printStackTrace();
		}
	}
}