package myprojects.DBcon;
import java.sql.*;

public class DBcon
{
	public ResultSet rstSelect=null;
	public ResultSet rstInsert=null;
	
	private Statement statSelect;
	private Statement statInsert;
	private Connection con;
	
	private String strUser=null;
	
	private String DbDriver="sun.jdbc.odbc.JdbcOdbcDriver";
	private String DbCon="jdbc:odbc:HomeManage";
	/**
	 * 函數名：getConnection
	 * 編寫者：郭軍
	 * 功  能：初始化與數據庫的連接
	 * 輸入參數：
	 * 輸出參數：
	 * 備  注：
	 */
	public boolean getConnection()
	{
		try
		{
			Class.forName(DbDriver);						//載入驅動程序
			con=DriverManager.getConnection(DbCon);			//建立連接
			con.setAutoCommit(false);
			con.setTransactionIsolation(con.TRANSACTION_SERIALIZABLE);
				//初始化狀態集
			statSelect=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			statInsert=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			System.out.println("**************************與數據庫HomeManage連接成功!**************************");
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
			e.printStackTrace();
			return false;
		}
		return true;
	}
	public String getUser()
	{
		return "1";
	}
	 /**
	* 函數名：getDH
	* 編寫者：郭軍
	* 功  能：
	* 輸入參數：
	* 輸出參數：
	* 備  注：
	*/ 
	public String getDH(String DH)
	{
		String strSQL=null;
		String strDH="none";
		long lmid=0;//設定一個中間處理變量
		strSQL="select JZDH from writeDetails where JZDH like '"+ DH +"%'";
		strSQL=strSQL+"order by JZDH DESC";
		try
		{
			
			rstSelect=statSelect.executeQuery(strSQL);
			if(rstSelect.next())
			{
				rstSelect.first();			//記錄集移到第一條
				strDH=rstSelect.getString("JZDH");//取出對應的處方單號
			}
		}
		catch(SQLException es)
		{
			System.out.println("錯誤是:  "+es.getMessage());
			es.getStackTrace();
			strDH="Error";					//置返回值為ERROR
		}
		return strDH;
    }
    
	public int saveTable(String[] lstSave)
	{
		int intSave=0;								//定義一個返回值
		String strSave="";
		strSave="insert into writeDetails(";
		strSave=strSave+"JZDH,amountID,userid,writeTime,usetime,details,place,price,amount,itemCharge,mark)";
		strSave=strSave+"values(";
		strSave=strSave+"'"+ lstSave[0]+"','"+ lstSave[1]+"','"+lstSave[2] +"',";
		strSave=strSave+"'"+ lstSave[3]+"','"+ lstSave[4]+"','"+lstSave[5] +"',";
		strSave=strSave+"'"+ lstSave[6]+"','"+ lstSave[7]+"','"+lstSave[8] +"',";
		strSave=strSave+"'"+ lstSave[9]+"','"+ lstSave[10]+"')";
		try
		{
			intSave=statInsert.executeUpdate(strSave);	//執行SQL更新命令
			con.commit();
			System.out.println("成功執行saveTable語句:"+strSave);
			System.out.println("*******************************************************************************");
		}
		catch(SQLException eSave)
		{
			System.out.println(eSave.getMessage());
			eSave.printStackTrace();
			try
			{
				con.rollback();					//如果更新失敗就回滾
			}
			catch(SQLException eSave2)
			{;}
			return -1;							//重置返回值
		}
		return intSave;
	}
	public int saveDetails(String strDetails)
	{
		String strSelect=null;
		String strInsert=null;
		String strTimes=null;
		int saveDetails=0;
		strInsert="insert into useItem(itemDetails,useTimes) values(";
		strInsert=strInsert+"'"+strDetails+"','1')";
		strSelect="select * from useItem where itemDetails='"+strDetails+"'";
		try
		{
			rstSelect=statSelect.executeQuery(strSelect);
			if(rstSelect.next())
			{
				rstSelect.first();
				strTimes=rstSelect.getString("useTimes");
				strTimes=Integer.toString(Integer.parseInt(strTimes)+1);
				try
				{
					String update="update useItem set useTimes='"+strTimes+"' where ";
					update=update+"itemDetails='"+strDetails+"'";
					int NO=statInsert.executeUpdate(update);
					con.commit();
			    	System.out.println(strDetails+" 項目使用次數更新成功!");
				}
				catch(SQLException eUp)
			    {
					eUp.printStackTrace();
					try
					{
						con.rollback();		//如果更新失敗就回滾
					}
					catch(Exception eUp2)
					{;}
					return -1;	
			    }
			}
			else
			{
				saveDetails=statInsert.executeUpdate(strInsert);
				con.commit();
				System.out.println("消費項目: "+strDetails+" "+"插入成功!");
			}
		}
		catch(SQLException e)
		{
			System.out.println("消費項目: "+strDetails+" "+"執行錯誤!");
		}
		return saveDetails;
	}
	public int savePlace(String strPlace)
	{
		String strSelect=null;
		String strInsert=null;
		String strTimes=null;
		int saveDetails=0;
		strInsert="insert into usePlace(place,useTimes) values(";
		strInsert=strInsert+"'"+strPlace+"','1')";
		strSelect="select * from usePlace where Place='"+strPlace+"'";
		try
		{
			rstSelect=statSelect.executeQuery(strSelect);
			if(rstSelect.next())
			{
				rstSelect.first();
				strTimes=rstSelect.getString("useTimes");
				strTimes=Integer.toString(Integer.parseInt(strTimes)+1);
				try
				{
					String update="update usePlace set useTimes='"+strTimes+"' where ";
					update=update+"place='"+strPlace+"'";
					int NO=statInsert.executeUpdate(update);
					con.commit();
			    	System.out.println(strPlace+" 地點使用次數更新成功!");
				}
				catch(SQLException eUp)
			    {
					eUp.printStackTrace();
					try
					{
						con.rollback();		//如果更新失敗就回滾
					}
					catch(Exception eUp2)
					{;}
					return -1;	
			    }
			}
			else
			{
				saveDetails=statInsert.executeUpdate(strInsert);
				con.commit();
				System.out.println("消費地點: "+strPlace+" "+"插入成功!");
			}
		}
		catch(SQLException e)
		{
			System.out.println("消費地點: "+strPlace+" "+"執行錯誤!");
		}
		return saveDetails;
	}
	
	public String getDetails()
	{
		String strDetails=null;
		String strGet=null;
		strGet="select * from useItem order by useTimes desc";
		try
		{
			rstSelect=statSelect.executeQuery(strGet);
			if(rstSelect.next())
			{
				rstSelect.first();
				strDetails=rstSelect.getString("itemDetails");
			}
		}
		catch(SQLException ex)
		{
			System.out.println("錯誤是:  "+ex.getMessage());
			ex.getStackTrace();
			strDetails="Error";		
		}
		return strDetails;
	}
	public int getJZMX(String strDH)
	{
		int i=0;									//設一個記數號!
		String strSQL="select JZDH,amountID,details,useTime,place,price,amount,itemCharge,mark from writeDetails ";
		strSQL=strSQL+"where JZDH like'%"+ strDH +"%' order by JZDH asc,amountID desc";
		try
		{
			rstSelect=statSelect.executeQuery(strSQL);	//執行SQL查詢語句
			rstSelect.last();							//將記錄集移到最后
			i=rstSelect.getRow();						//得到最后一行的列號作為記錄的總條數
			//rstSelect.first();							//將記錄集移到第一條記錄
		}
		catch(SQLException exDH)
		{
			i=-1;									//設定返回值
			System.out.println("處方信息錯誤是: "+exDH.getMessage());
			exDH.getStackTrace();
		}
		return i;	
	}
	
	public String getPlace()
	{
		String strPlace=null;
		String strGet=null;
		strGet="select * from usePlace order by useTimes desc";
		try
		{
			rstSelect=statSelect.executeQuery(strGet);
			if(rstSelect.next())
			{
				rstSelect.first();
				strPlace=rstSelect.getString("place");
			}
		}
		catch(SQLException ex)
		{
			System.out.println("錯誤是:  "+ex.getMessage());
			ex.getStackTrace();
			strPlace="Error";		
		}
		return strPlace;
	}
	public int checkUser(String userid,String pass)
	{
		int checkFlag=0;
		String strCheck=null;
		strCheck="select * from userInfo where userID=";
		strCheck=strCheck+"'"+userid+"' and pwd='"+pass+"'";
		System.out.println(strCheck);
		try
		{
			rstSelect=statSelect.executeQuery(strCheck);
			if(rstSelect.next())
			{
				rstSelect.first();				
				checkFlag=1;	
				System.out.println("***********************************登錄成功************************************");			
			}
			else
			{
				System.out.println("***********************************密碼錯誤************************************");
			}
		}
		catch(SQLException e)
		{
			System.out.println("調用DBcon中的checkUser時出錯!");
			System.out.println(e.getMessage());
			e.getStackTrace();
		}
		return checkFlag;		
	}
	public int updateUser(String userid,String newPass)
	{
		int upFlag=0;
		String strUpdate=null;
		strUpdate="update userInfo set pwd='"+newPass+"' where userID='"+userid+"'";
		System.out.println(strUpdate);
		try
		{
			statInsert.executeUpdate(strUpdate);
			con.commit();
			upFlag=1;
		}
		catch(SQLException e)
		{
			System.out.println("調用DBcon中的updateUser時出錯!");
			System.out.println(e.getMessage());
			e.getStackTrace();
		}
		return upFlag;
	}
	/**
	* 函數名：getNowDate
	* 編寫者：郭軍
	* 功  能：得到當前日期
	* 輸入參數：
	* 輸出參數：
	* 備  注：
	*/   
    public String getNowDate()
    {
    	String getNowDate = null;
        try 
        {
            String strSQL = "";					//定義SQL字符串
            strSQL = "SELECT SUBSTRING(CONVERT(VARCHAR(16),GETDATE(),120),1,16) AS SYSDATE";
            rstSelect = statSelect.executeQuery(strSQL);	//執行SQL結果保存在動態集里
            while(rstSelect.next())
            {
                getNowDate = rstSelect.getString("SYSDATE");
            }
        } 
        catch (Exception se)
        {
          	System.out.println(se.getMessage());
        	se.printStackTrace();
        }
        return getNowDate;						//返回當前時間
     }
}