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

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



    // WhiteBoardDetail.aspx use
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable Select(int Year, int Month, int Day) {
        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT " +
            " WhiteBoardID " + 
            " ,[Year]" +
            " ,[Month]" +
            " ,[Day]" +
            " ,[Contents]" +
            " FROM " + TableName +
            " Where [Year]=@Year and [Month]=@Month and [Day]=@Day order by [Year],[Month],[Day]", cn)) {

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

            da.Fill(dt);
            // 沒有資料時會回傳一個空的 Row
            if (dt.Rows.Count == 0) {
                dt.Rows.Add(dt.NewRow());
            }
            return dt;
        }
    }
    public DataTable Select(int Year, int Month) {
        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT " +
            " WhiteBoardID " +
            " ,[Year]" +
            " ,[Month]" +
            " ,[Day]" +
            " ,[Contents]" +
            " 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;
        }
    }
    // WhiteBoard.aspx use
    public DataTable SelectByMonth(int Year, int Month) {
        DataTable dt = new DataTable(TableName);
        int iMaxDayOfMonth = new Root.pcCommon().getMaxDayOfMonth(
            DateTime.Parse(Year.ToString() + "/" + Month.ToString() + "/01")); // 取得月的最大天數
        // 建立月資料表(實際回傳 DataTable)
        DataTable dtRealReturn = new DataTable("SelectByMonth");
        dtRealReturn.Columns.Add("Year");
        dtRealReturn.Columns.Add("Month");
        dtRealReturn.Columns.Add("Day");
        dtRealReturn.Columns.Add("Contents");
        DataRow dataRow;
        for (int i = 0; i < iMaxDayOfMonth; i++) { 
            dataRow = dtRealReturn.NewRow();
            dataRow["Year"] = Year.ToString();
            dataRow["Month"] = Month.ToString();
            dataRow["Day"] = (i+1).ToString();
            dataRow["Contents"] = "";
            dtRealReturn.Rows.Add(dataRow);
        }

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT " +
            " [Year]" +
            " ,[Month]" +
            " ,[Day]" +
            " ,[Contents]" +
            " 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);
            for (int i=0; i< dt.Rows.Count; i++){
                int iDay = int.Parse(dt.Rows[i]["Day"].ToString());
                if (dtRealReturn.Rows[iDay - 1]["Contents"].ToString() == "") {
                    dtRealReturn.Rows[iDay - 1]["Contents"] = dt.Rows[i]["Contents"].ToString();
                }
                else {
                    dtRealReturn.Rows[iDay - 1]["Contents"] =
                        dtRealReturn.Rows[iDay - 1]["Contents"].ToString() + "\r\n" + dt.Rows[i]["Contents"].ToString();
                }
            }

            return dtRealReturn;
        }
    }

    // PersonalHome.aspx use
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public string[] Select(DateTime SelectDay, string UserName, string UserEName) {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [Contents]" +
            " FROM " + TableName +
            " Where Year=@Year and Month=@Month and Day=@Day and " +
            " (Contents like '%" + UserName + "%' or Contents like '%" + UserEName + "%') ", cn)) {

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

            da.Fill(dt);
            string sRtnTmp = "";
            foreach (DataRow dr in dt.Rows) {
                sRtnTmp += "\n" + dr["Contents"].ToString();
            }
            if (sRtnTmp == "")
                return null;
            else
                return sRtnTmp.Substring(1).Split("\n".ToCharArray());

        }
    }
    

    [DataObjectMethod(DataObjectMethodType.Insert, true)]
    public void Insert(int Year, int Month, int Day, string Contents) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "INSERT INTO " + TableName +
            " ([Year]" +
            " ,[Month]" +
            " ,[Day]" +
            " ,[Contents])" +
            " Values (@Year, @Month, @Day, @Contents)", 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("@Contents", Contents ?? Convert.DBNull);

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }


    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void Update(string Contents, int original_WhiteBoardID) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET [Contents]=@Contents " +
            " Where WhiteBoardID=@original_WhiteBoardID", cn)) {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@Contents", Contents ?? Convert.DBNull);
            SqlParameter parameter = cm.Parameters.AddWithValue("@original_WhiteBoardID", original_WhiteBoardID);
            parameter.SourceVersion = DataRowVersion.Original;

            int iCount = cm.ExecuteNonQuery();

            cn.Close();
        }
    }

    
    // 刪除 Schedule 時, 同時使用下面函數刪除白板的資料
    public void Delete(int Year, int Month, int Day, string WorkID) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "Delete " + TableName +
            " Where Year=@Year and Month=@Month and Day=@Day and CHARINDEX('[" + WorkID + "]',Contents) > 0  ", cn)) {


            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@Year", Year);
            cm.Parameters.AddWithValue("@Month", Month);
            cm.Parameters.AddWithValue("@Day", Day);

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
    public void Delete(int Year, int Month, string WorkID) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "Delete " + TableName +
            " Where Year=@Year and Month=@Month and CHARINDEX('[" + WorkID + "]',Contents) > 0  ", cn)) {


            cn.Open();

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

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
    public void DeleteByWorkID(string WorkID) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "Delete " + TableName +
            " Where CHARINDEX('[" + WorkID + "]',Contents) > 0  ", cn)) {

            cn.Open();

            cm.CommandType = CommandType.Text;

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
    public void Delete(int original_WhiteBoardID) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "Delete " + TableName +
            " Where WhiteBoardID=@original_WhiteBoardID ", cn)) {


            cn.Open();

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

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
}
