package myProject.FrmBRJZ;

import java.sql.*;


public class FrmBRJZDb
{
	public ResultSet rs = null;
//	public boolean flg = false;
    String dbDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
    String dbConn = "jdbc:odbc:Dbserver";
    Connection con;
    Statement stmt;
	public ResultSet prs = null;
	public ResultSet rst = null;
	public ResultSet ps = null;	
	public ResultSet rt = null;	
	public ResultSet st = null;	
	public ResultSet ts = null;		
//連接數據庫
public void getConnection() {
    try {
        //加載驅動程序
        Class.forName(dbDriver);
        //建立連接
        con = DriverManager.getConnection(dbConn);
        //關閉自動提交
        con.setAutoCommit(false);
        //設定事務級別
        con.setTransactionIsolation(con.TRANSACTION_SERIALIZABLE);
        //創(chuàng)建一個JDBC聲明
        stmt = con.createStatement();
    }
    catch (Exception e) 
    {
        System.out.println(e.getMessage());
        e.printStackTrace();
    }
    
}

	public void getBrxx(String ZYH) {
		String strSQL="select BRXM,BCH,RYRQ,ZYKS from view_BRZYXX where ZYH='"+ZYH+"'";
//		System.out.println(strSQL);
		try{
        rs = stmt.executeQuery(strSQL);
        rs.next();
        //System.out.println(rs.getString("BRXM"));
        
        }
	
	
	catch (SQLException e){
	    System.out.println(e.getMessage());
		e.printStackTrace();
	
    }
}



	public void getBrfy(String ZYH) {
		String strSQL="select * from view_WJZFY where ZYH='"+ZYH+"'";
//		System.out.println(strSQL);
		try{
        prs = stmt.executeQuery(strSQL);
        prs.next();
        //System.out.println(rs.getString("BRXM"));
        
        }
	
	
	catch (SQLException e){
	    System.out.println(e.getMessage());
		e.printStackTrace();
	
    }
  }

	public void getBrjf(String ZYH) {
		String strSQL="select JE from JFJL where ZYH='"+ZYH+"'";
//		System.out.println(strSQL);
		try{
        rst = stmt.executeQuery(strSQL);
        
        //System.out.println(rs.getString("BRXM"));
        
        }
	
	
	catch (SQLException e){
	    System.out.println(e.getMessage());
		e.printStackTrace();
	
    }
  }

	public void gettblBrjf(String ZYH) {
		String strSQL="select * from JFJL where ZYH='"+ZYH+"'";
//		System.out.println(strSQL);
		try{
        rt = stmt.executeQuery(strSQL);
        
        }
	
	
	catch (SQLException e){
	    System.out.println(e.getMessage());
		e.printStackTrace();
	
    }
  }

    public String getNowDate()
    {
    	String getNowDate = null;
        try 
        {
            //sql字符串
            //String strSQL = "";
            String strSQL = "SELECT SUBSTRING(CONVERT(VARCHAR(16),GETDATE(),120),1,16) AS SYSDATE";
            //執(zhí)行sql結果保存在動態(tài)集里
             
            ps = stmt.executeQuery(strSQL);
             
            while(ps.next())
            {
            
                getNowDate = ps.getString("SYSDATE");
//                System.out.println(getNowDate);
            }
       } 
        catch (Exception se) 
        {
          System.out.println(se.getMessage());
          se.printStackTrace();
        }
        return getNowDate;
     }

	public void tblBrfy(String ZYH) {
		String strSQL="select * from view_WJZFY where ZYH='"+ZYH+"'";
//		System.out.println(strSQL);
		try{
        st = stmt.executeQuery(strSQL);
//        st.next();
        //System.out.println(rs.getString("BRXM"));
        
        }
	
	
	catch (SQLException e){
	    System.out.println(e.getMessage());
		e.printStackTrace();
	
    }
  }
  
	public String getUserID(){
		try{
			String strSQL="select UserID from SFUser where DQBJ='1' and UserLB='收費員'";
				ts = stmt.executeQuery(strSQL);	
				if(ts.next())
					return ts.getString("UserID");
				return new String("無登錄用戶");							
			}
		catch (SQLException e){
			System.out.println(e.getMessage());
			e.printStackTrace();
			return new String("出錯");
		}
		
	}
	

   public void update1(String ZYH, String time){
   	    try{
   	    	
   	    	String strSQL="update JFJL set JZRQ = '"+time+"' where ZYH='"+ZYH+"'";

			stmt.executeUpdate(strSQL);
			con.commit();   	    	
   	    	
   	    	}
   	    catch (SQLException e){
	    	System.out.println(e.getMessage());
			e.printStackTrace();
	
    		}
   	
   	}
   public void update2(String ZYH, String time){
   	    try{
   	    	
   	    	String strSQL="update CFD set JZRQ = '"+time+"' where ZYH='"+ZYH+"'";

			stmt.executeUpdate(strSQL);
			con.commit();   	    	
   	    	
   	    	}
   	    catch (SQLException e){
	    	System.out.println(e.getMessage());
			e.printStackTrace();
	
    		}
   	
   	}   	

   public void update3(String ZYH, String time){
   	    try{
   	    	
   	    	String strSQL="update CWF set JZRQ = '"+time+"' where ZYH='"+ZYH+"'";

			stmt.executeUpdate(strSQL);
			con.commit();   	    	
   	    	
   	    	}
   	    catch (SQLException e){
	    	System.out.println(e.getMessage());
			e.printStackTrace();
	
    		}
   	
   	}
   	
   	public void insert(String zyh,String time,String jfxs,String sfy,String je){
   		try{
   			
   			String strSQL = "insert into JFJL (ZYH,JFRQ,JFXS,SFY,JE) values('"+zyh+"',Convert(smalldatetime,'"+time+"'),'"+jfxs+"','"+sfy+"',Convert(money,'"+je+"'))";
   			stmt.executeUpdate(strSQL);
   			con.commit();
//   			stmt.execute(strSQL);
//   			System.out.println(strSQL);
   			}
   		
   	    catch (SQLException e){
	    	System.out.println(e.getMessage());
			e.printStackTrace();
	
    		}   		
   	
   	
   	
   	}
   	public void closeDB()
   	{
   		try
   		{
   			con.commit();
   			if (!con.isClosed())
   			con.close();
   			
   			}
   		catch(SQLException se)
   		{
   			System.out.print(se.getMessage());
   			se.printStackTrace();
   			}
   		
   		}
}