using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.ComponentModel;

[DataObject(true)]
public class HR_EmployeesData : SqlData
{
    public HR_EmployeesData()
    {
        DataConnection = ConfigurationManager.ConnectionStrings["ConnString_SEST"].ConnectionString;
        TableName = "HR_Employees";
        PrimaryKeyColumnNames = new string[] { "EmployeeID" };
    }

    [DataObjectMethod(DataObjectMethodType.Insert, true)]
    public void Insert(string EmployeeID, string Name, string Sex, string Blood, string IdentityNo, string PassportNo, string FirstName, string MiddleName,
        string LastName, string F_Address, string F_PhoneNo, string L_Address, string L_PhoneNo, string CellPhone, string CellPhone2, string EMail,
        string EMail2, string DeptID, string JobTitle, string Grade, string JobRole, string HireDate, string LeaveDate, string Comment)
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "INSERT INTO " + TableName +
            " ([EmployeeID],[Name],[Sex],[Blood],[IdentityNo],[PassportNo],[FirstName],[MiddleName],[LastName],[F_Address],[F_PhoneNo]," +
            " [L_Address],[L_PhoneNo],[CellPhone],[CellPhone2],[EMail],[EMail2],[DeptID],[JobTitle]," +
            " [Grade],[JobRole],[HireDate],[Comment]) " +
            " VALUES " +
            " (@EmployeeID, @Name, @Sex, @Blood, @IdentityNo, @PassportNo, @FirstName, @MiddleName, @LastName, @F_Address, @F_PhoneNo, " +
            " @L_Address, @L_PhoneNo,@CellPhone, @CellPhone2, @EMail, @EMail2, @DeptID, @JobTitle, " +
            " @Grade, @JobRole, @HireDate, @Comment)", cn))
        {
            cn.Open();

            //if (HireDate == DateTime.MinValue)
            //    HireDate = DateTime.Parse("1990/1/1");

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@EmployeeID", EmployeeID.Trim());
            cm.Parameters.AddWithValue("@Name", Name.Trim());
            cm.Parameters.AddWithValue("@Sex", Sex);
            cm.Parameters.AddWithValue("@Blood", Blood ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@IdentityNo", IdentityNo ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@PassportNo", PassportNo ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@FirstName", FirstName.Trim());
            cm.Parameters.AddWithValue("@MiddleName", MiddleName ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@LastName", LastName ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@F_Address", F_Address ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@F_PhoneNo", F_PhoneNo ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@L_Address", L_Address ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@L_PhoneNo", L_PhoneNo ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@CellPhone", CellPhone ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@CellPhone2", CellPhone2 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@EMail", EMail.Trim() );
            cm.Parameters.AddWithValue("@EMail2", EMail2 == null ? Convert.DBNull : EMail2.Trim() );
            cm.Parameters.AddWithValue("@DeptID", DeptID);
            cm.Parameters.AddWithValue("@JobTitle", JobTitle == null ? Convert.DBNull : JobTitle.Trim() );
            cm.Parameters.AddWithValue("@Grade", Grade ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@JobRole", JobRole ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@HireDate", (HireDate == "" || HireDate == null) ? Convert.DBNull : HireDate);
            cm.Parameters.AddWithValue("@Comment", Comment ?? Convert.DBNull);

            cm.ExecuteNonQuery();
                
            cn.Close();
        }
    }

    public int Count()
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "SELECT Count(*) FROM " + TableName , cn))
        {
            cn.Open();

            cm.CommandType = CommandType.Text;

            int i = (int)cm.ExecuteScalar();

            cn.Close();

            return i;
        }
    }
    public int Count(string Condition) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "SELECT Count(*) FROM " + TableName + " Where " + Condition, cn)) {
            cn.Open();

            cm.CommandType = CommandType.Text;

            int i = (int)cm.ExecuteScalar();

            cn.Close();

            return i;
        }
    }


    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable SelectExcludeLeave() {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
                "SELECT [EmployeeID]" +
                "," + TableName + ".[Name]" +
                ",[Sex]" +
                ",[Blood]" +
                ",[IdentityNo]" +
                ",[PassportNo]" +
                ",[FirstName]" +
                ",[MiddleName]" +
                ",[LastName]" +
                ",[Account]" +
                ",[F_Address]" +
                ",[F_PhoneNo]" +
                ",[L_Address]" +
                ",[L_PhoneNo]" +
                ",[CellPhone]" +
                ",[CellPhone2]" +
                ",[EMail]" +
                ",[EMail2]" +
                "," + TableName + ".[DeptID]" +
                ",[JobTitle]" +
                ",[Grade]" +
                ",[JobRole]" +
                ",CAST(YEAR([HireDate]) AS varchar(4)) + '/' + CAST(MONTH([HireDate]) AS varchar(2)) + '/' + CAST(DAY([HireDate]) AS varchar(2)) AS HireDate " +
                ",CAST(YEAR([LeaveDate]) AS varchar(4)) + '/' + CAST(MONTH([LeaveDate]) AS varchar(2)) + '/' + CAST(DAY([LeaveDate]) AS varchar(2)) AS LeaveDate " +
                ",[Grouping] " +
                ",[DutyFormEnabled] " +
                ",[DutyFormPriority] " +
                ",[Comment] " +
                " FROM " + TableName + 
                " Where (LeaveDate is null or LeaveDate='') ORDER BY " + PrimaryKeyColumnNames[0], cn)) {
            da.Fill(dt);
            return dt;
        }
    }
    public DataTable SelectExcludeLeave(string ColumnName, string Condition) {
        DataTable dt = new DataTable(TableName);

        string sOrderBy = "", sWhere = " Where (LeaveDate is null or LeaveDate='')";

        // 以預找尋的欄位做排序
        if ((ColumnName != null) && ColumnName != "" && ColumnName != "*")
            sOrderBy = " Order By " + ColumnName + " ";
        // 判斷有沒有給欄位
        else if (ColumnName != "*") 
            return null;

        // 預找尋的條件
        if ( Condition != null && Condition.ToUpper().IndexOf("ROY") > -1)
            sWhere = " Where " + Condition + " ";
        else if ((Condition != null) && Condition != "")
            sWhere += " and " + Condition + " ";

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT " + ColumnName +
            " FROM " + TableName + sWhere + sOrderBy, cn)) {
            da.Fill(dt);
            return dt;
        }
    }
    //public DataTable SelectExcludeLeaveJoinOrgaName() {
    //    DataTable dt = new DataTable(TableName);

    //    using (SqlConnection cn = new SqlConnection(DataConnection))
    //    using (SqlDataAdapter da = new SqlDataAdapter(
    //            "SELECT [EmployeeID]" +
    //            "," + TableName + ".[Name]" +
    //            ",[Sex]" +
    //            ",[Blood]" +
    //            ",[IdentityNo]" +
    //            ",[PassportNo]" +
    //            ",[FirstName]" +
    //            ",[MiddleName]" +
    //            ",[LastName]" +
    //            ",[Account]" +
    //            ",[F_Address]" +
    //            ",[F_PhoneNo]" +
    //            ",[L_Address]" +
    //            ",[L_PhoneNo]" +
    //            ",[CellPhone]" +
    //            ",[CellPhone2]" +
    //            ",[EMail]" +
    //            ",[EMail2]" +
    //            "," + TableName + ".[DeptID]" +
    //            ",HR_Organization.[CName] as DeptName" +
    //            ",[JobTitle]" +
    //            ",[Grade]" +
    //            ",[JobRole]" +
    //            ",CAST(YEAR([HireDate]) AS varchar(4)) + '/' + CAST(MONTH([HireDate]) AS varchar(2)) + '/' + CAST(DAY([HireDate]) AS varchar(2)) AS HireDate " +
    //            ",CAST(YEAR([LeaveDate]) AS varchar(4)) + '/' + CAST(MONTH([LeaveDate]) AS varchar(2)) + '/' + CAST(DAY([LeaveDate]) AS varchar(2)) AS LeaveDate " +
    //            ",[Grouping] " +
    //            ",[DutyFormEnabled] " +
    //            ",[DutyFormPriority] " +
    //            ",[Comment] " +
    //            " FROM " + TableName + ",HR_Organization " +
    //            " Where " + TableName + ".DeptID=HR_Organization.OrganizationID and (LeaveDate is null or LeaveDate='') ORDER BY " + PrimaryKeyColumnNames[0], cn)) {
    //        da.Fill(dt);
    //        return dt;
    //    }
    //}


    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable SelectIncludeLeave() {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
                "SELECT [EmployeeID]" +
                "," + TableName + ".[Name]" +
                ",[Sex]" +
                ",[Blood]" +
                ",[IdentityNo]" +
                ",[PassportNo]" +
                ",[FirstName]" +
                ",[MiddleName]" +
                ",[LastName]" +
                ",[Account]" +
                ",[F_Address]" +
                ",[F_PhoneNo]" +
                ",[L_Address]" +
                ",[L_PhoneNo]" +
                ",[CellPhone]" +
                ",[CellPhone2]" +
                ",[EMail]" +
                ",[EMail2]" +
                "," + TableName + ".[DeptID]" +
                ",HR_Organization.[CName] as DeptName" +
                ",[JobTitle]" +
                ",[Grade]" +
                ",[JobRole]" +
                ",CAST(YEAR([HireDate]) AS varchar(4)) + '/' + CAST(MONTH([HireDate]) AS varchar(2)) + '/' + CAST(DAY([HireDate]) AS varchar(2)) AS HireDate " +
                ",CAST(YEAR([LeaveDate]) AS varchar(4)) + '/' + CAST(MONTH([LeaveDate]) AS varchar(2)) + '/' + CAST(DAY([LeaveDate]) AS varchar(2)) AS LeaveDate " +
                ",[Grouping] " +
                ",[DutyFormEnabled] " +
                ",[DutyFormPriority] " +
                ",[Comment] " +
                //" FROM " + TableName +
                //" ORDER BY " + PrimaryKeyColumnNames[0], cn)) {
                " FROM " + TableName + ",HR_Organization " +
                " Where " + TableName + ".DeptID=HR_Organization.OrganizationID ORDER BY " + PrimaryKeyColumnNames[0], cn)) {
            da.Fill(dt);
            return dt;
        }
    }
    public DataTable SelectIncludeLeave(string Condition) {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
                "SELECT [EmployeeID]" +
                "," + TableName + ".[Name]" +
                ",[Sex]" +
                ",[Blood]" +
                ",[IdentityNo]" +
                ",[PassportNo]" +
                ",[FirstName]" +
                ",[MiddleName]" +
                ",[LastName]" +
                ",[Account]" +
                ",[F_Address]" +
                ",[F_PhoneNo]" +
                ",[L_Address]" +
                ",[L_PhoneNo]" +
                ",[CellPhone]" +
                ",[CellPhone2]" +
                ",[EMail]" +
                ",[EMail2]" +
                "," + TableName + ".[DeptID]" +
                ",HR_Organization.[CName] as DeptName" +
                ",[JobTitle]" +
                ",[Grade]" +
                ",[JobRole]" +
                ",CAST(YEAR([HireDate]) AS varchar(4)) + '/' + CAST(MONTH([HireDate]) AS varchar(2)) + '/' + CAST(DAY([HireDate]) AS varchar(2)) AS HireDate " +
                ",CAST(YEAR([LeaveDate]) AS varchar(4)) + '/' + CAST(MONTH([LeaveDate]) AS varchar(2)) + '/' + CAST(DAY([LeaveDate]) AS varchar(2)) AS LeaveDate " +
                ",[Grouping] " +
                ",[DutyFormEnabled] " +
                ",[DutyFormPriority] " +
                ",[Comment] " +
                //" FROM " + TableName +
                //" Where " + Condition + " ORDER BY " + PrimaryKeyColumnNames[0], cn)) {
                " FROM " + TableName + ",HR_Organization " +
                " Where " + TableName + ".DeptID=HR_Organization.OrganizationID ORDER BY " + PrimaryKeyColumnNames[0], cn)) {
            da.Fill(dt);
            return dt;
        }
    }
    public DataTable SelectIncludeLeave(string ColumnName, string Condition) {
        DataTable dt = new DataTable(TableName);

        string sOrderBy = "", sWhere = "";

        //以預找尋的欄位做排序
        if ((ColumnName != null) && ColumnName != "" && ColumnName != "*")
            sOrderBy = " Order By " + ColumnName + " ";
        // 判斷有沒有給欄位
        else if (ColumnName != "*")
            return null;

        // 預找尋的條件
        if ((Condition != null) && Condition != "")
            sWhere = " Where " + Condition + " ";

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT " + ColumnName +
            " FROM " + TableName + sWhere + sOrderBy, cn)) {
            da.Fill(dt);
            return dt;
        }
    }
    public DataTable SelectIncludeLeaveJoinOrgaName() {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
                "SELECT [EmployeeID]" +
                "," + TableName + ".[Name]" +
                ",[Sex]" +
                ",[Blood]" +
                ",[IdentityNo]" +
                ",[PassportNo]" +
                ",[FirstName]" +
                ",[MiddleName]" +
                ",[LastName]" +
                ",[Account]" +
                ",[F_Address]" +
                ",[F_PhoneNo]" +
                ",[L_Address]" +
                ",[L_PhoneNo]" +
                ",[CellPhone]" +
                ",[CellPhone2]" +
                ",[EMail]" +
                ",[EMail2]" +
                "," + TableName + ".[DeptID]" +
                ",HR_Organization.[CName] as DeptName" +
                ",[JobTitle]" +
                ",[Grade]" +
                ",[JobRole]" +
                ",CAST(YEAR([HireDate]) AS varchar(4)) + '/' + CAST(MONTH([HireDate]) AS varchar(2)) + '/' + CAST(DAY([HireDate]) AS varchar(2)) AS HireDate " +
                ",CAST(YEAR([LeaveDate]) AS varchar(4)) + '/' + CAST(MONTH([LeaveDate]) AS varchar(2)) + '/' + CAST(DAY([LeaveDate]) AS varchar(2)) AS LeaveDate " +
                ",[Grouping] " +
                ",[DutyFormEnabled] " +
                ",[DutyFormPriority] " +
                ",[Comment] " +
                " FROM " + TableName + ",HR_Organization " +
                " Where " + TableName + ".DeptID=HR_Organization.OrganizationID ORDER BY " + PrimaryKeyColumnNames[0], cn)) {
            da.Fill(dt);
            return dt;
        }
    }
    public DataTable SelectIncludeLeaveJoinOrgaName(string Condition) {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
                "SELECT [EmployeeID]" +
                "," + TableName + ".[Name]" +
                ",[Sex]" +
                ",[Blood]" +
                ",[IdentityNo]" +
                ",[PassportNo]" +
                ",[FirstName]" +
                ",[MiddleName]" +
                ",[LastName]" +
                ",[Account]" +
                ",[F_Address]" +
                ",[F_PhoneNo]" +
                ",[L_Address]" +
                ",[L_PhoneNo]" +
                ",[CellPhone]" +
                ",[CellPhone2]" +
                ",[EMail]" +
                ",[EMail2]" +
                "," + TableName + ".[DeptID]" +
                ",HR_Organization.[CName] as DeptName" +
                ",[JobTitle]" +
                ",[Grade]" +
                ",[JobRole]" +
                ",CAST(YEAR([HireDate]) AS varchar(4)) + '/' + CAST(MONTH([HireDate]) AS varchar(2)) + '/' + CAST(DAY([HireDate]) AS varchar(2)) AS HireDate " +
                ",CAST(YEAR([LeaveDate]) AS varchar(4)) + '/' + CAST(MONTH([LeaveDate]) AS varchar(2)) + '/' + CAST(DAY([LeaveDate]) AS varchar(2)) AS LeaveDate " +
                ",[Grouping] " +
                ",[DutyFormEnabled] " +
                ",[DutyFormPriority] " +
                ",[Comment] " +
                " FROM " + TableName + ",HR_Organization " +
                " Where " + Condition + " and " + TableName + ".DeptID=HR_Organization.OrganizationID ORDER BY " + PrimaryKeyColumnNames[0], cn)) {
            da.Fill(dt);
            return dt;
        }
    }


    public DataTable SelectDutyFormEmployeeExcludeLeave() {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [DeptID],[Name],[Grouping],DutyFormPriority" +
             " FROM " + TableName +
             " Where  DutyFormEnabled='1' and (LeaveDate is null or LeaveDate='') Order By [Grouping],DutyFormPriority ", cn)) {

            da.Fill(dt);
            return dt;
        }
    }
    public DataTable SelectDutyFormEmployeeExcludeLeave(string WorkID, DateTime selectDate) {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [DeptID],[Name],[Grouping],DutyFormPriority" +
             " FROM " + TableName + ",TEC_MainWorkSchedules " +
             " Where  (DutyFormEnabled='1' and LeaveDate is null or LeaveDate='') or " +
             "  (charindex(Name,Responsibility)>0 and WorkID=" + WorkID + " and YEAR(ExpectStartDate)=" + selectDate.Year + " and Month(ExpectStartDate)=" + selectDate.Month + ") " +
             " Group by [DeptID],[Name],[Grouping],DutyFormPriority " +
             " Order By [Grouping],[DeptID],[Name],DutyFormPriority ", cn)) {

            da.Fill(dt);
            return dt;
        }
    }


    public string getUserNameByAccount(string Account) {
        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [Name]" +
             " FROM " + TableName +
             " Where Account=@Account", cn)) {

            da.SelectCommand.CommandType = CommandType.Text;
            da.SelectCommand.Parameters.AddWithValue("@Account", Account.Trim());

            da.Fill(dt);
            string sRtn = "";
            if (dt.Rows.Count == 1)
                sRtn = dt.Rows[0]["Name"].ToString();
            else if (dt.Rows.Count > 1) {
                new Exception("Include mutilple rows (occurs from HR_EmployeesData - getUserNameByAccount)");
                return "Error";
            }
            return sRtn;
        }
    }

    public string[] getNoAccountUsersName() {
        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [Name]" +
             " FROM " + TableName +
             " Where (Account is null or Account='') and (LeaveDate is null or LeaveDate='') ORDER BY " + PrimaryKeyColumnNames[0], cn)) {
            da.Fill(dt);
            string[] ar = null;
            if (dt.Rows.Count > 0) {
                ar = new string[dt.Rows.Count + 1];
                ar[0] = "";
                for (int i = 0; i < dt.Rows.Count; i++) {
                    ar[i + 1] = dt.Rows[i]["Name"].ToString();
                }
            }
            return ar;
        }
    }

    public string[] getManagerUsersName() {
        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [Name]" +
             " FROM " + TableName + ",HR_JobTitle " +
             " Where " + TableName + ".JobTitle=HR_JobTitle.TitleName and IsLeader=1", cn)) {
            da.Fill(dt);
            string[] ar = null;
            if (dt.Rows.Count > 0) {
                ar = new string[dt.Rows.Count + 1];
                ar[0] = "";
                for (int i = 0; i < dt.Rows.Count; i++) {
                    ar[i + 1] = dt.Rows[i]["Name"].ToString();
                }
            }
            return ar;
        }
    }

    public string[] getUsersNameExcludeLeave() { 
        DataTable dt = SelectExcludeLeave();
        string[] ar = new string[dt.Rows.Count];

        for (int i = 0; i < dt.Rows.Count; i++) { 
            ar[i] = dt.Rows[i]["Name"].ToString();
        }

        return ar;
    }

    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void Update(string EmployeeID, string Name, string Sex, string Blood, string IdentityNo, string PassportNo, string FirstName, string MiddleName,
        string LastName, string F_Address, string F_PhoneNo, string L_Address, string L_PhoneNo, string CellPhone, string CellPhone2, string EMail,
        string EMail2, string DeptID, string JobTitle, string Grade, string JobRole, string HireDate, string LeaveDate,
        string Comment, string Original_EmployeeID)
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET [EmployeeID]=@EmployeeID, [Name]=@Name, [Sex]=@Sex, [Blood]=@Blood, [IdentityNo]=@IdentityNo, [PassportNo]=@PassportNo, [FirstName]=@FirstName, [MiddleName]=@MiddleName, " +
            " [LastName]=@LastName, [F_Address]=@F_Address, [F_PhoneNo]=@F_PhoneNo, [L_Address]=@L_Address, [L_PhoneNo]=@L_PhoneNo, " +
            " [CellPhone]=@CellPhone, [CellPhone2]=@CellPhone2, [EMail]=@EMail, [EMail2]=@EMail2, [DeptID]=@DeptID, " +
            " [JobTitle]=@JobTitle, [Grade]=@Grade, [JobRole]=@JobRole, [HireDate]=@HireDate, [LeaveDate]=@LeaveDate, [Comment]=@Comment " +
            " Where EmployeeID=@EmployeeID", cn))
        {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@EmployeeID", EmployeeID.Trim());
            cm.Parameters.AddWithValue("@Name", Name.Trim());
            cm.Parameters.AddWithValue("@Sex", Sex );
            cm.Parameters.AddWithValue("@Blood", Blood ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@IdentityNo", IdentityNo ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@PassportNo", PassportNo ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@FirstName", FirstName.Trim());
            cm.Parameters.AddWithValue("@MiddleName", MiddleName ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@LastName", LastName ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@F_Address", F_Address ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@F_PhoneNo", F_PhoneNo ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@L_Address", L_Address ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@L_PhoneNo", L_PhoneNo ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@CellPhone", CellPhone ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@CellPhone2", CellPhone2 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@EMail", EMail.Trim());
            cm.Parameters.AddWithValue("@EMail2", EMail2 == null ? Convert.DBNull : EMail2.Trim() );
            cm.Parameters.AddWithValue("@DeptID", DeptID);
            cm.Parameters.AddWithValue("@JobTitle", JobTitle == null ? Convert.DBNull : JobTitle.Trim() );
            cm.Parameters.AddWithValue("@Grade", Grade ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@JobRole", JobRole ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@HireDate", (HireDate == "" || HireDate == null) ? Convert.DBNull : HireDate);
            cm.Parameters.AddWithValue("@LeaveDate", (LeaveDate == "" || LeaveDate == null) ? Convert.DBNull : LeaveDate);
            cm.Parameters.AddWithValue("@Comment", Comment ?? Convert.DBNull);
            SqlParameter parameter = cm.Parameters.AddWithValue("@Original_EmployeeID", Original_EmployeeID);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void Update(string UpdateFieldValues, string Condition) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET " + UpdateFieldValues +
            " Where " + Condition, cn)) {
            cn.Open();

            cm.ExecuteNonQuery();
            cn.Close();
        }
    }


    [DataObjectMethod(DataObjectMethodType.Delete, true)]
    public void Delete(string Original_EmployeeID)
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand("DELETE " + TableName + " WHERE EmployeeID=@Original_EmployeeID", cn))
        {
            cn.Open();

            cm.CommandType = CommandType.Text;
            SqlParameter parameter = cm.Parameters.AddWithValue("@Original_EmployeeID", Original_EmployeeID);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }



    public void UpdateDutyFormGrouping(string[] EmployeeName) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand())
            {
            cm.Connection = cn;
            cn.Open();
            for (int i = 0; i < EmployeeName.Length; i++) {
                cm.CommandText = "UPDATE " + TableName +
                    " SET DutyFormGrouping='" + Convert.ToString(i + 1) + "' " +
                    " Where Name='" + EmployeeName[i];

                cm.ExecuteNonQuery();
                cn.Close();
            }
        }
    }


}
