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

[DataObject(true)]
public class TEC_MainWorkSchedulesData : SqlData
{
    public TEC_MainWorkSchedulesData()
    {
        DataConnection = ConfigurationManager.ConnectionStrings["ConnString_SEST"].ConnectionString;
        TableName = "TEC_MainWorkSchedules";
        PrimaryKeyColumnNames = new string[] { "WorkID", "ExpectStartDate" };
    }

    [DataObjectMethod(DataObjectMethodType.Insert, true)]
    public int Insert(string WorkID, string ExpectStartDate, string ExpectEndDate, string Responsibility, string GroupName)
    {

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "INSERT INTO " + TableName +
            " ([WorkID]" +
            " ,[ExpectStartDate]" +
            " ,[ExpectEndDate]" +
            " ,[Responsibility]" +
            " ,[GroupName])" +
            " VALUES " +
            " (@WorkID,@ExpectStartDate,@ExpectEndDate,@Responsibility,@GroupName)", cn))
        {

            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@WorkID", WorkID.Trim());
            cm.Parameters.AddWithValue("@ExpectStartDate", ExpectStartDate == null ? Convert.DBNull : ExpectStartDate.Trim());
            cm.Parameters.AddWithValue("@ExpectEndDate", ExpectEndDate == null ? Convert.DBNull : ExpectEndDate.Trim());
            cm.Parameters.AddWithValue("@Responsibility", Responsibility == null ? Convert.DBNull : Responsibility.Trim());
            cm.Parameters.AddWithValue("@GroupName", GroupName == null ? Convert.DBNull : GroupName.Trim());

            cm.ExecuteNonQuery();

            cm.CommandText = "select IDENT_CURRENT('" + TableName + "') as ScheduleID "; //"Select max(WorkID) as WorkID from " + TableName;
            SqlDataReader sr = cm.ExecuteReader();
            int iRtn = -1;
            if (sr.Read())
                iRtn = Convert.ToInt32(sr["ScheduleID"].ToString()); //回傳 ScheduleID index

            cn.Close();
            return iRtn;
        }
    }
    public int Insert2(string WorkID, string ExpectStartDate, string ExpectEndDate, string Responsibility, string GroupName) {

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "if (Select count(*) from " + TableName + " Where WorkID=@WorkID and ExpectStartDate=@ExpectStartDate) = 1 " +
            " Begin " +
            " Update " + TableName +
            " set Responsibility=@Responsibility, GroupName=@GroupName " +
            " Where WorkID=@WorkID and  ExpectStartDate=@ExpectStartDate " +
            " End " +
            " Else" +
            " Begin " +
            " INSERT INTO " + TableName +
            " ([WorkID]" +
            " ,[ExpectStartDate]" +
            " ,[ExpectEndDate]" +
            " ,[Responsibility]" +
            " ,[GroupName])" +
            " VALUES " +
            " (@WorkID,@ExpectStartDate,@ExpectEndDate,@Responsibility,@GroupName)" +
            " End ", cn)) {

            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@WorkID", WorkID.Trim());
            cm.Parameters.AddWithValue("@ExpectStartDate", ExpectStartDate == null ? Convert.DBNull : ExpectStartDate.Trim());
            cm.Parameters.AddWithValue("@ExpectEndDate", ExpectEndDate == null ? Convert.DBNull : ExpectEndDate.Trim());
            cm.Parameters.AddWithValue("@Responsibility", Responsibility == null ? Convert.DBNull : Responsibility.Trim());
            cm.Parameters.AddWithValue("@GroupName", GroupName == null ? Convert.DBNull : GroupName.Trim());

            cm.ExecuteNonQuery();

            cm.CommandText = "select IDENT_CURRENT('" + TableName + "') as ScheduleID "; //"Select max(WorkID) as WorkID from " + TableName;
            SqlDataReader sr = cm.ExecuteReader();
            int iRtn = -1;
            if (sr.Read())
                iRtn = Convert.ToInt32(sr["ScheduleID"].ToString()); //回傳 ScheduleID index

            cn.Close();
            return iRtn;
        }
    }

    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable SelectByWorkID(string WorkID)
    {
        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [ScheduleID]" +
            " ,[WorkID]" +
            " ,CAST(YEAR([ExpectStartDate]) AS varchar(4)) + '/' + CAST(MONTH([ExpectStartDate]) AS varchar(2)) + '/' + CAST(DAY([ExpectStartDate]) AS varchar(2)) AS ExpectStartDate " +
            " ,CAST(YEAR([ExpectEndDate]) AS varchar(4)) + '/' + CAST(MONTH([ExpectEndDate]) AS varchar(2)) + '/' + CAST(DAY([ExpectEndDate]) AS varchar(2)) AS ExpectEndDate " +
            " ,[Responsibility]" +
            " ,[GroupName]" +
            " FROM " + TableName + " Where WorkID=@WorkID ORDER BY CAST(ExpectStartDate as datetime)", cn))
        {
            da.SelectCommand.CommandType = CommandType.Text;
            da.SelectCommand.Parameters.AddWithValue("@WorkID", WorkID);

            da.Fill(dt);
            return dt;
        }
    }
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable SelectByScheduleID(string ScheduleID) {
        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [ScheduleID]" +
            " ,[WorkID]" +
            " ,CAST(YEAR([ExpectStartDate]) AS varchar(4)) + '/' + CAST(MONTH([ExpectStartDate]) AS varchar(2)) + '/' + CAST(DAY([ExpectStartDate]) AS varchar(2)) AS ExpectStartDate " +
            " ,CAST(YEAR([ExpectEndDate]) AS varchar(4)) + '/' + CAST(MONTH([ExpectEndDate]) AS varchar(2)) + '/' + CAST(DAY([ExpectEndDate]) AS varchar(2)) AS ExpectEndDate " +
            " ,[Responsibility]" +
            " ,[GroupName]" +
            " FROM " + TableName + " Where ScheduleID=@ScheduleID ORDER BY " + PrimaryKeyColumnNames[1], cn)) {
            da.SelectCommand.CommandType = CommandType.Text;
            da.SelectCommand.Parameters.AddWithValue("@ScheduleID", ScheduleID);

            da.Fill(dt);
            return dt;
        }
    }
    // format --> 2002/4
    public string[] SelectHistoryDataByMonth(string WorkID) {

        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "Select cast(Y as varchar(4))+ '/' +cast(M as varchar(4)) as YearMonth " +
            "from " +
                "(select DATEPART(yyyy, ExpectStartDate) as Y,DATEPART(MM, ExpectStartDate) as M " +
                    "from " + TableName + " where WorkID=@WorkID " +
                    "Group By DATEPART(yyyy, ExpectStartDate),DATEPART(MM, ExpectStartDate)) T " +
            "Order by YearMonth", cn)) {

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

            da.Fill(dt);
            string[] ar = null;
            if (dt.Rows.Count > 0) {
                ar = new string[dt.Rows.Count];
                for (int i = 0; i < dt.Rows.Count; i++) {
                    ar[i] = dt.Rows[i]["YearMonth"].ToString();
                }
            }
            return ar;
        }
    }
    public bool CheckValidSchedule(string WorkID, string ExpectStartDate, string ExpectEndDate) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "SELECT Count(*) FROM " + TableName +
            " Where WorkID=@WorkID " +
            " and ((ExpectStartDate >= @ExpectStartDate and ExpectStartDate <= @ExpectEndDate) " +
            "       or (ExpectEndDate >= @ExpectStartDate and ExpectEndDate <= @ExpectEndDate))", cn)) {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@WorkID", WorkID);
            cm.Parameters.AddWithValue("@ExpectStartDate", ExpectStartDate);
            cm.Parameters.AddWithValue("@ExpectEndDate", ExpectEndDate);

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

            if (i > 0)
                return false;   // 已存在
            else
                return true;    // 不存在
        }
    }



    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void Update(string ExpectStartDate, string ExpectEndDate, string Responsibility, string GroupName, string original_ScheduleID) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET [ExpectStartDate]=@ExpectStartDate, " +
            " [ExpectEndDate]=@ExpectEndDate, " +
            " [Responsibility]=@Responsibility, " +
            " [GroupName]=@GroupName " +
            " Where ScheduleID=@original_ScheduleID", cn)) {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@ExpectStartDate", ExpectStartDate == null ? Convert.DBNull : ExpectStartDate.Trim());
            cm.Parameters.AddWithValue("@ExpectEndDate", ExpectEndDate == null ? Convert.DBNull : ExpectEndDate.Trim());
            cm.Parameters.AddWithValue("@Responsibility", Responsibility == null ? Convert.DBNull : Responsibility.Trim());
            cm.Parameters.AddWithValue("@GroupName", GroupName == null ? Convert.DBNull : GroupName.Trim());
            SqlParameter parameter = cm.Parameters.AddWithValue("@original_ScheduleID", original_ScheduleID);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }

    [DataObjectMethod(DataObjectMethodType.Delete, true)]
    public void Delete(string original_ScheduleID)
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand("DELETE " + TableName + " WHERE ScheduleID=@original_ScheduleID", cn))
        {
            cn.Open();
    
            cm.CommandType = CommandType.Text;
            SqlParameter parameter = cm.Parameters.AddWithValue("@original_ScheduleID", original_ScheduleID);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
    public void Delete(string WorkID, string Date) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand("DELETE " + TableName + 
            " WHERE WorkID='" + WorkID + "' and ExpectStartDate='" + Date + "'", cn)) {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
    public void DeleteByWorkID(string WorkID) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand("DELETE " + TableName +
            " WHERE WorkID='" + WorkID + "'", cn)) {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
}
