package manpowersystem;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2003</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */

import java.sql.*;
import java.util.*;

public class Database {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;

    public RecordItem[] AccessData() throws Exception {
        Class.forName( "com.microsoft.jdbc.sqlserver.SQLServerDriver" );
        String url = "jdbc:microsoft:sqlserver://localhost:1433;" +
            "DatabaseName=Manpower";
        conn = DriverManager.getConnection( url, "sa", "" );
        stmt = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE,
                                     ResultSet.CONCUR_UPDATABLE );

        String SQL = "select * from WorkTime";
        rs = stmt.executeQuery( SQL );

        RecordItem[] item = new RecordItem[ 100 ];
        for ( int j = 0; j < 100; j++ )
            item[ j ] = new RecordItem();
        int i = 0;

        while ( rs.next() && ( i < 100 ) ) {
            item[ i ].SetEmployeeID( rs.getString( "EmployeeID" ) );
            item[ i ].SetEmployeeName( rs.getString( "EmployeeName" ) );
            item[ i ].SetOnWorkTime(rs.getString( "OnworkTime" ) );
            item[ i ].SetOffWorkTime( rs.getString( "OffworkTime" ) );
            item[ i ].SetLeaveWorkTime( rs.getString( "LeaveworkTime" ) );
            item[ i ].SetDescribe( rs.getString( "Describe" ) );

            i++;
        }

        RecordItem[] result = new RecordItem[ i ];
        for ( int j = 0; j < i; j++ ) {
            result[ j ] = new RecordItem();
            result[ j ] = item[ j ];
        }

        rs.close();
        stmt.close();
        conn.close();

        return result;
    }

    public void StoreData( RecordItem item ) throws Exception {
        Class.forName( "com.microsoft.jdbc.sqlserver.SQLServerDriver" );
        String url = "jdbc:microsoft:sqlserver://localhost:1433;" +
            "DatabaseName=Manpower";
        conn = DriverManager.getConnection( url, "sa", "" );
        stmt = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE,
                                     ResultSet.CONCUR_UPDATABLE );

        String SQL = "select * from WorkTime where EmployeeID = '" +
            item.GetEmployeeID() + "'";
        rs = stmt.executeQuery( SQL ); //查找是否存在該員工的記錄

        //如果已經存在該員工的記錄則更新，如果不存在則插入新的記錄
        if ( rs.next() ) {
            SQL = "update WorkTime set EmployeeName='" + item.GetEmployeeName() +
                "',"
                + " OnworkTime='" + item.GetOnWorkTime() + "',"
                + " OffworkTime='" + item.GetOffWorkTime() + "',"
                + " LeaveworkTime='" + item.GetLeaveWorkTime() + "',"
                + " Describe='" + item.GetDescribe() + "'"
                + " where EmployeeID = '" + item.GetEmployeeID() + "'";
            stmt.execute( SQL );
        }
        else {
            SQL =
                "insert WorkTime(EmployeeID,EmployeeName,OnworkTime, OffworkTime,LeaveworkTime,Describe) "
                + " values('" + item.GetEmployeeID() + "', '"
                +item.GetEmployeeName() + "','"
                + item.GetOnWorkTime() + "','"
                + item.GetOffWorkTime() + "','"
                + item.GetLeaveWorkTime() + "','"
                + item.GetDescribe() + "')";
            stmt.execute( SQL );
        }

        rs.close();
        stmt.close();
        conn.close();
    }

    //刪除相應工號的員工記錄
    public void DeleteData( String strEmployeeID ) throws Exception {
        Class.forName( "com.microsoft.jdbc.sqlserver.SQLServerDriver" );
        String url = "jdbc:microsoft:sqlserver://localhost:1433;" +
            "DatabaseName=Manpower";
        conn = DriverManager.getConnection( url, "sa", "" );
        stmt = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE,
                                     ResultSet.CONCUR_UPDATABLE );

        String SQL = "delete from WorkTime where EmployeeID='" + strEmployeeID +
            "'";
        stmt.execute( SQL ); //查找是否存在該員工的記錄
    }
}