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

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

    public DataTable Select(int Year, int Month) {
        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT " +
            " [Year]" +
            " ,[Month]" +
            " ,[Day]" +
            " ,[Area]" +
            " ,[MM]" +
            " ,[MS]" +
            " ,[EM]" +
            " ,[ES]" +
            " FROM " + TableName +
            " Where [Year]=@Year and [Month]=@Month order by [Year],[Month],[Day]", cn)) {

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

            da.Fill(dt);
            // 沒有資料時會回傳一個空的 Row
            if (dt.Rows.Count == 0) {
                dt.Rows.Add(dt.NewRow());
            }
            return dt;
        }
    }
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable SelectByMonth(int Year, int Month, string Area) {
        DataTable dt = new DataTable(TableName);
        int iMaxDayOfMonth = new Root.pcCommon().getMaxDayOfMonth(
            DateTime.Parse(Year.ToString() + "/" + Month.ToString() + "/01")); // 取得月的最大天數

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "Select @Year as [Year],@Month as [Month],DaysOfMonth.[Day],MM,MS,EM,ES " +
            " from DaysOfMonth " +
            " left join TEC_OnCall " +
            " on TEC_OnCall.Area=@Area and TEC_OnCall.[Year]=@Year and TEC_OnCall.[Month]=@Month and DaysOfMonth.[Day]=TEC_OnCall.[Day] " +
            " Where DaysOfMonth.[Day] <=" + iMaxDayOfMonth.ToString() + 
            " order by [Year],[Month],DaysOfMonth.[Day]", cn)) {
            
            da.SelectCommand.CommandType = CommandType.Text;
            da.SelectCommand.Parameters.AddWithValue("@Year", Year);
            da.SelectCommand.Parameters.AddWithValue("@Month", Month);
            da.SelectCommand.Parameters.AddWithValue("@Area", Area);

            da.Fill(dt);

            return dt;
        }
    }


    public void UpdateOrInsert(string Year, string Month, string Day, string Area, string MM, string MS, string EM, string ES) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "if (select count(*) from TEC_OnCall Where [Year]=@Year and [Month]=@Month and [Day]=@Day and [Area]=@Area) <= 0 " +
	        "   begin " +
            "       insert into TEC_OnCall ([Year],[Month],[Day],[Area],[MM],[MS],[EM],[ES]) " +
		    "       values (@Year,@Month,@Day,@Area,@MM,@MS,@EM,@ES) " +
            "	end " +
            "else "+
            "	begin " +
            "		Update TEC_OnCall " +
            "		set MM=@MM,MS=@MS,EM=@EM,ES=@ES " +
            "		Where [Year]=@Year and [Month]=@Month and [Day]=@Day and [Area]=@Area " +
            "	end", cn)) {

            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@Year", Year);  
            cm.Parameters.AddWithValue("@Month", Month);
            cm.Parameters.AddWithValue("@Day", Day);
            cm.Parameters.AddWithValue("@Area", Area);
            cm.Parameters.AddWithValue("@MM", MM);
            cm.Parameters.AddWithValue("@MS", MS);
            cm.Parameters.AddWithValue("@EM", EM);
            cm.Parameters.AddWithValue("@ES", ES);

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
}
