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

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

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

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@TitleName", TitleName.Trim());
            cm.Parameters.AddWithValue("@JobGrade", JobGrade);
            cm.Parameters.AddWithValue("@IsLeader", IsLeader);
            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;
        }
    }
    public int checkJobTitleUsing() {
        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 JobGrade DESC", cn))
        {
            da.Fill(dt);
            return dt;
        }
    }
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public string[] SelectTitleName() {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT distinct TitleName, MAX(JobGrade) as JobGrade FROM " + TableName + " Group By TitleName ORDER BY JobGrade,TitleName DESC", 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.Update, true)]
    public void Update(string TitleName, string JobGrade, bool IsLeader, string Description, string original_TitleName)
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET  TitleName=@TitleName, JobGrade=@JobGrade, IsLeader=@IsLeader, Description=@Description " +
            " WHERE TitleName=@original_TitleName", cn))
        {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@TitleName", TitleName.Trim());
            cm.Parameters.AddWithValue("@JobGrade", JobGrade);
            cm.Parameters.AddWithValue("@IsLeader", IsLeader);
            cm.Parameters.AddWithValue("@Description", (Description == null || Description == "") ? Convert.DBNull : Description);
            //p.SourceColumnNullMapping  = true; p.IsNullable=true; p.Value = null;
            SqlParameter parameter = cm.Parameters.AddWithValue("@original_TitleName", original_TitleName);
            parameter.SourceVersion = DataRowVersion.Original;

            try {
                cm.ExecuteNonQuery();
            }
            catch (SqlException sqle) { SES_Common.Show(sqle.Message); }
            finally { cn.Close(); }
        }
    }
    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void Update(string JobGrade, bool IsLeader, string Description, string original_TitleName) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET JobGrade=@JobGrade, IsLeader=@IsLeader, Description=@Description " +
            " WHERE TitleName=@original_TitleName", cn)) {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@JobGrade", JobGrade);
            cm.Parameters.AddWithValue("@IsLeader", IsLeader);
            cm.Parameters.AddWithValue("@Description", (Description == null || Description == "") ? Convert.DBNull : Description);
            //p.SourceColumnNullMapping  = true; p.IsNullable=true; p.Value = null;
            SqlParameter parameter = cm.Parameters.AddWithValue("@original_TitleName", original_TitleName);
            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_TitleName)
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand("DELETE " + TableName + " WHERE TitleName=@original_TitleName and ", cn))
        {
            cn.Open();

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

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
}
