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

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

    [DataObjectMethod(DataObjectMethodType.Insert, true)]
    public void Insert(string DeptID, string Name, string Organization, string Description)
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "INSERT INTO " + TableName + " (DeptID, Name, Organization, Description) VALUES (@DeptID, @Name, @Organization, @Description)", cn))
        {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@DeptID", DeptID.Trim());
            cm.Parameters.AddWithValue("@Name", Name.Trim() );
            cm.Parameters.AddWithValue("@Organization", Organization.Trim());
            cm.Parameters.AddWithValue("@Description", (Description == null || Description == "") ? Convert.DBNull : Description);
            //p.SourceColumnNullMapping = true; p.IsNullable = true; p.Value = DBNull null;

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

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

            cm.CommandType = CommandType.Text;

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

            cn.Close();

            return i;
        }
    }

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

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT * FROM " + TableName + " ORDER BY " + PrimaryKeyColumnNames[0], cn))
        {
            da.Fill(dt);
            return dt;
        }
    }
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public string[] SelectDistinctID() {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT distinct DeptID + ' - ' + Name FROM " + TableName + " ORDER BY DeptID + ' - ' + Name", cn)){ // + PrimaryKeyColumnNames[0], cn)) {
            
            da.Fill(dt);
            string[] ar= new string[dt.Rows.Count];
            int i = 0;
            foreach (DataRow dr in dt.Rows){
                ar[i] = dr.ItemArray[0].ToString();
                i++;
            }
            return ar;
        }
    }
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public string[] SelectGroupIDByEmployeeID(string Condition) {
        DataTable dt = new DataTable(TableName);

        Condition = " Where " + Condition;

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT distinct Name " +
            " FROM " + TableName + " Where DeptID in (Select distinct DeptID FROM HR_Employees " + Condition + ")", cn)) {

            da.Fill(dt);
            string[] ar = new string[dt.Rows.Count];
            int i = 0;
            foreach (DataRow dr in dt.Rows) {
                ar[i] = dr.ItemArray[0].ToString();
                i++;
            }
            return ar;

            //string[] arTemp = null;
            //string sGroups = "";
            //da.Fill(dt);
            //foreach (DataRow dr in dt.Rows) {
            //    arTemp = dr.ItemArray[0].ToString().Split("/".ToCharArray());
            //    sGroups += ",'" + arTemp[4] + "'"; // index 4 --> Group ID
            //}
            //if (sGroups != "") {
            //    sGroups = sGroups.Substring(1);

            //    SqlDataAdapter da2 = new SqlDataAdapter("Select distinct Name Where DeptID in (" + sGroups + ")", cn);
            //    da2.Fill(dt);
            //    string[] ar = new string[dt.Rows.Count];
            //    int i = 0;
            //    foreach (DataRow dr in dt.Rows) {
            //        ar[i] = dr.ItemArray[0].ToString();
            //        i++;
            //    }
            //    return ar;
            //}

            //return null;
        }
    }

    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void Update(string DeptID, string Name, string Organization, string Description, string original_DeptID)
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET  DeptID=@DeptID, Name=@Name, Organization=@Organization, Description=@Description WHERE DeptID=@original_DeptID", cn))
        {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@DeptID", DeptID.Trim());
            cm.Parameters.AddWithValue("@Name", Name.Trim());
            cm.Parameters.AddWithValue("@Organization", Organization.Trim());
            cm.Parameters.AddWithValue("@Description", Description ?? Convert.DBNull);
            //p.SourceColumnNullMapping  = true; p.IsNullable=true; p.Value = null;
            SqlParameter parameter = cm.Parameters.AddWithValue("@original_DeptID",original_DeptID);
            parameter.SourceVersion = DataRowVersion.Original;

            try {
                cm.ExecuteNonQuery();
            }
            catch (SqlException sqle) { SES_Common.Show(sqle.Message); }
            finally { cn.Close(); }
        }
    }

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

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

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
}
