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

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


    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable Select(string Condition) {
        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [OrganizationID]" +
            " ,[EName]" +
            " ,[CName]" +
            " ,[Parent]" +
            " ,[Manager]" +
            " ,[ManagerTitle]" +
            " ,[Deputy]" +
            " ,[DeputyTitle]" +
            " ,[Description]" +
            " FROM " + TableName +
            " Where " + Condition, cn)) {
            da.Fill(dt);
            return dt;
        }
    }
    public string[] SelectOrganizationIDAndName() {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT distinct OrganizationID + ' - ' + CName FROM " + TableName + " ", 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;
        }
    }
    public string[] SelectOrganizationName() {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT distinct CName FROM " + TableName + " ", cn)) { // + PrimaryKeyColumnNames[0], cn)) {

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

    [DataObjectMethod(DataObjectMethodType.Insert, true)]
    public void Insert(string OrganizationID, string CName, string EName, string Manager, string ManagerTitle, string Deputy, 
        string DeputyTitle, string Parent, string Description) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "INSERT INTO " + TableName +
            " ([OrganizationID],[CName],[EName],[Manager],[ManagerTitle],[Deputy],[DeputyTitle],[Parent],[Description]) " +
            " VALUES " +
            " (@OrganizationID, @CName, @EName, @Manager, @ManagerTitle, @Deputy, @DeputyTitle, @Parent, @Description)", cn)) {
            cn.Open();

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

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@OrganizationID", OrganizationID.Trim() == "" ? Convert.DBNull : OrganizationID.Trim());
            cm.Parameters.AddWithValue("@CName", CName.Trim() == "" ? Convert.DBNull : CName.Trim());
            cm.Parameters.AddWithValue("@EName", EName.Trim() == "" ? Convert.DBNull : EName.Trim());
            cm.Parameters.AddWithValue("@Manager", Manager.Trim() == "" ? Convert.DBNull : Manager.Trim());
            cm.Parameters.AddWithValue("@ManagerTitle", ManagerTitle.Trim() == "" ? Convert.DBNull : ManagerTitle.Trim());
            cm.Parameters.AddWithValue("@Deputy", Deputy.Trim() == "" ? Convert.DBNull : Deputy.Trim());
            cm.Parameters.AddWithValue("@DeputyTitle", DeputyTitle.Trim() == "" ? Convert.DBNull : DeputyTitle.Trim());
            cm.Parameters.AddWithValue("@Parent", Parent.Trim() == "" ? Convert.DBNull : Parent.Trim());
            cm.Parameters.AddWithValue("@Description", Description.Trim() == "" ? Convert.DBNull : Description.Trim());

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void Update(string OrganizationID, string CName, string EName, string Manager, string ManagerTitle, string Deputy,
        string DeputyTitle, string Parent, string Description, string Original_OrganizationID) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET [OrganizationID]=@OrganizationID, [CName]=@CName, [EName]=@EName, [Manager]=@Manager, [ManagerTitle]=@ManagerTitle, " +
            " [Deputy]=@Deputy, [DeputyTitle]=@DeputyTitle, [Parent]=@Parent, [Description]=@Description " +
            " Where OrganizationID=@Original_OrganizationID", cn)) {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@OrganizationID", OrganizationID.Trim() == "" ? Convert.DBNull : OrganizationID.Trim());
            cm.Parameters.AddWithValue("@CName", CName.Trim() == "" ? Convert.DBNull : CName.Trim());
            cm.Parameters.AddWithValue("@EName", EName.Trim() == "" ? Convert.DBNull : EName.Trim());
            cm.Parameters.AddWithValue("@Manager", Manager.Trim() == "" ? Convert.DBNull : Manager.Trim());
            cm.Parameters.AddWithValue("@ManagerTitle", ManagerTitle.Trim() == "" ? Convert.DBNull : ManagerTitle.Trim());
            cm.Parameters.AddWithValue("@Deputy", Deputy.Trim() == "" ? Convert.DBNull : Deputy.Trim());
            cm.Parameters.AddWithValue("@DeputyTitle", DeputyTitle.Trim() == "" ? Convert.DBNull : DeputyTitle.Trim());
            cm.Parameters.AddWithValue("@Parent", Parent.Trim() == "" ? Convert.DBNull : Parent.Trim());
            cm.Parameters.AddWithValue("@Description", Description.Trim() == "" ? Convert.DBNull : Description.Trim());
            SqlParameter parameter = cm.Parameters.AddWithValue("@Original_OrganizationID", Original_OrganizationID);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

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

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

            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;
        }
    }




}