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

[DataObject(true)]
public class Personal_CalendarData : SqlData
{
    public Personal_CalendarData()
    {
        DataConnection = ConfigurationManager.ConnectionStrings["ConnString_SEST"].ConnectionString;
        TableName = "Personal_Calendar";
        PrimaryKeyColumnNames = new string[] { "UserEName", "Year", "Month", "Day" };
    }



    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public string[] Select(string Account, DateTime SelectDay) {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [Contents]" +
            " FROM " + TableName +
            " Where Account=@Account " +
            " and Year=@Year and Month=@Month and Day=@Day ", cn)) {

            da.SelectCommand.CommandType = CommandType.Text;
            da.SelectCommand.Parameters.AddWithValue("@Account", Account);
            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 = "";
            if (dt.Rows.Count > 0) {
                string sCnt = dt.Rows[0]["Contents"].ToString();
                string[] ar = sCnt.Replace("\r", "").Split("\n".ToCharArray());
                for (int i = 0; i < ar.Length; i++) {
                    sRtnTmp += "#" + ar[i];
                }
                if (sRtnTmp != "")
                    sRtnTmp = sRtnTmp.Substring(1);
            }

            // return 
            if (sRtnTmp == "")
                return null;
            else
                return sRtnTmp.Split("#".ToCharArray());
        }
    }
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable Select(string Account, int Year, int Month) {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [Account]" +
            " ,[Year]" +
            " ,[Month]" +
            " ,[Day]" +
            " ,[Significance]" +
            " ,[Contents]" +
            " FROM " + TableName +
            //" Where Account in (Select Account from HR_Employees Where FirstName=@Account) " +
            " Where Account=@Account " +
            " and Year=@Year and Month=@Month ", cn)) {

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

            da.Fill(dt);
            return dt;
        }
    }


    public void Save(string Account, int Year, int Month, int Day, string Contents) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "SELECT Count(*) FROM " + TableName +
            " Where Account=@Account and Year=@Year and Month=@Month and Day=@Day", cn)) {

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

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

            if (i == 0) { // not exist
                Insert(Account, Year, Month, Day, 1, Contents);
            }
            else { // exist
                if (Contents.Trim() != "")
                    Update(Account, Year, Month, Day, 1, Contents);
                else
                    Delete(Account, Year, Month, Day);
            }
        }
    }

    [DataObjectMethod(DataObjectMethodType.Insert, true)]
    public void Insert(string Account, int Year, int Month, int Day, int Significance, string Contents) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "INSERT INTO " + TableName +
            " ([Account]" +
            " ,[Year]" +
            " ,[Month]" +
            " ,[Day]" +
            " ,[Significance]" +
            " ,[Contents])" +
            " Values (@Account, @Year, @Month, @Day, @Significance, @Contents)", cn)) {

            cn.Open();

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

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void Update(string Account, int Year, int Month, int Day, int Significance, string Contents) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET [Significance]=@Significance, [Contents]=@Contents " +
            " Where Account=@Account and Year=@Year and Month=@Month and Day=@Day", cn)) {


            cn.Open();

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

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void Delete(string Account, int Year, int Month, int Day) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "Delete " + TableName +
            " Where Account=@Account and Year=@Year and Month=@Month and Day=@Day", cn)) {


            cn.Open();

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

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }

}
