package myprojects.FrmBRCXDb;

import java.sql.*;

public class FrmBRCXDb {	
	
	public ResultSet rs = null;
	public ResultSet rsFY = null;
	public ResultSet rsJF = null;
	public int count = 0;
	//public boolean flg = false;
	String dbDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
	String dbConn = "jdbc:odbc:DbServer";    
	Connection con;	
	Statement stmt;
	
	
	public boolean getConnection(){
		
		 try {
		 
			 //加載驅(qū)動(dòng)程序
		 	Class.forName(dbDriver);
		 
		 	//建立連接
		 	con = DriverManager.getConnection(dbConn);
		 
		 	//關(guān)閉自動(dòng)提交
		 	con.setAutoCommit(false);
		 
		 	//設(shè)定事務(wù)級(jí)別
		 	con.setTransactionIsolation(con.TRANSACTION_SERIALIZABLE);
			 
			//創(chuàng)建一個(gè)JDBC聲明
			stmt = con.createStatement(
										ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_UPDATABLE);		 
			return true;		 		 		 
		 }
		 
		 catch (Exception e) {
		 	System.out.println(e.getMessage());
        	e.printStackTrace();	 		 	
		 	return false;	
		 }
	}
	
	public String getDate()
    {
    	String Date = null;
        try 
        {
            //sql字符串
            String strSQL = "";
            strSQL = "select substring(convert(varchar(16),getdate(),120),1,16) as sysdate";
            //執(zhí)行sql結(jié)果保存在動(dòng)態(tài)集里
            ResultSet rsDate = stmt.executeQuery(strSQL);
            while(rsDate.next())
            {
                Date = rsDate.getString("sysdate");
            }
        } 
        catch (Exception e) 
        {
          System.out.println(e.getMessage());
          e.printStackTrace();
        }
        return Date;
     }
	  
	
	//執(zhí)行查詢	
	public int getDataMD(String[] str) {    
    	
    	try {  
    		  		
    		//sql字符串
            String strSQL = "";            
            strSQL = "select ZYH,BLH,BRXM,BRXB,CSNY,SFZH,ZYKS,BCH,BRDH,BRDZ,LXRXM,LXRDH,LXRDZ,RYRQ,CYRQ from View_ZYBR";            
            strSQL = strSQL + " where ZYH like '%" + str[0] + "%'";
            strSQL = strSQL + " and BLH like '%" + str[1] + "%'";
            strSQL = strSQL + " and BRXM like '%" + str[2] + "%'";
            strSQL = strSQL + " and isnull(BRXB,'') like '%" + str[3] + "%'";
            strSQL = strSQL + " and isnull(CSNY,'') like '%" + str[4] + "%'";
            strSQL = strSQL + " and isnull(SFZH,'') like '%" + str[5] + "%'";
            strSQL = strSQL + " and ZYKS like '%" + str[6] + "%'";
            strSQL = strSQL + " and isnull(BCH,'') like '%" + str[7] + "%'";
            strSQL = strSQL + " and isnull(BRDH,'') like '%" + str[8] + "%'";
            strSQL = strSQL + " and isnull(BRDZ,'') like '%" + str[9] + "%'";
            strSQL = strSQL + " and isnull(LXRXM,'') like '%" + str[10] + "%'";
            strSQL = strSQL + " and isnull(LXRDH,'') like '%" + str[11] + "%'";
            strSQL = strSQL + " and isnull(LXRDZ,'') like '%" + str[12] + "%'";
            strSQL = strSQL + " and RYRQ like '%" + str[13] + "%'";
            strSQL = strSQL + " and isnull(CYRQ,'') like '%" + str[14] + "%'"; 	   	    		 		
    		System.out.println(strSQL);    		 	   	
    		rs = stmt.executeQuery(strSQL);
    		count = 0;
    		while(rs.next()){
            	count++;           	           	            	         	
        	}        	
        	rs.beforeFirst();   
	        if (count > 0) {
	        	//查詢成功
	        	System.out.print("查詢病人數(shù)：");
	        	System.out.println(count); 
            	return count;
        	} else {        		
            	//查詢失敗
            	return -1;
        	}
        	 
    	}
    	catch (SQLException e)     	
    	{
        	System.out.println(e.getMessage());
        	e.printStackTrace();
        	return 0;         	
    	}    	  	 	
   }
	
	public int getDataFY(String str) {
		
		try {
			
			String strSQL = "select CFRQ,KM,DJ*SL AS SUM,DJ,SL,ZYKS,JJDW,GG,KMLB,XMFL,ZFBL,ZYH,CD,CJ from View_WJZFY ";
			strSQL = strSQL + " where ZYH = '"+ str +"'";
			System.out.println(strSQL);    		 	   	
    		rsFY = stmt.executeQuery(strSQL);
    		count = 0;
           	while(rsFY.next()){           		
            	count++;        	            	            	         	
        	}        	
        	rsFY.beforeFirst();   
	        if (count > 0) {
	        	//查詢成功
	        	System.out.print("查詢病人費(fèi)用項(xiàng)數(shù)：");
	        	System.out.println(count);
            	return count;
        	} else {        		
            	//查詢失敗
            	return -1;
        	}
    	}
    	catch (SQLException e)    	
    	{
        	System.out.println(e.getMessage());
        	e.printStackTrace();
        	return 0;         	
    	}
	
	}
	
	public int getDataJF(String str) {		
		
		try {			
			String strSQL = "select JFRQ,SFY,JE,ZYH,JFXS from JFJL ";
			strSQL = strSQL + " where ZYH = '"+ str +"'";
			System.out.println(strSQL);    		 	   	
    		rsJF = stmt.executeQuery(strSQL);
    		count = 0;    		
           	while(rsJF.next()){
            	count++;           	           	            	         	
        	}        	
        	rsJF.beforeFirst();   
	        if (count > 0) {
	        	//查詢成功
	        	System.out.print("查詢病人交費(fèi)次數(shù)：");
	        	System.out.println(count); 
            	return count;
        	} else {        		
            	//查詢失敗
            	return -1;
        	}
    	}
    	catch (SQLException e)     	
    	{
        	System.out.println(e.getMessage());
        	e.printStackTrace();
        	return 0;         	
    	}
		
	}	

	/*
	*關(guān)閉數(shù)據(jù)庫連接
	*/  
	public void closeDB(){
    	try	{
        	//在系統(tǒng)連接關(guān)閉之前，將所有未能及時(shí)提交的事務(wù)釋放
        	con.commit();
        	//關(guān)閉連接
        	if (!con.isClosed())
    		con.close();
    	}
    	catch(SQLException se)
    	{
        	System.out.print(se.getMessage());
        	se.printStackTrace();
    	}
	}	 


}