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

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


    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable Select(DateTime SelectDate)
    {
        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [AnnouncementID]" +
            " ,[Creator]" +
            " ,[PostFrom]" +
            " ,[CreateDate]" +
            " ,[StartDate]" +
            " ,[EndDate]" +
            " ,[Importance]" +
            " ,[Subject]" +
            " ,[Contents]" +
            " ,[AttachedFile]" +
            " FROM " + TableName + " Where StartDate<='" + SelectDate.ToString("yyyy/MM/dd") + 
                "' and EndDate>='" + SelectDate.ToString("yyyy/MM/dd") + "' ORDER BY CreateDate DESC", cn))
        {
            da.Fill(dt);
            return dt;
        }
    }
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public string[] SelectCreator()
    {
        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            //"SELECT * FROM " + TableName + " ORDER BY " + PrimaryKeyColumnNames[0], cn))
            "SELECT distinct [Creator]" +
            " FROM " + TableName + " ORDER BY Creator", cn)) {
            da.Fill(dt);
            string[] arRtn = null;
            if (dt.Rows.Count > 0) {
                arRtn = new string[dt.Rows.Count];
                for (int i = 0; i < dt.Rows.Count; i++) {
                    arRtn[i] = dt.Rows[i]["Creator"].ToString();
                }
            }
            return arRtn;
        }
    }
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable Select(string PostFrom, string Creator, string StartDate, string EndDate) {
        string sWhere = "";
        if (PostFrom.Trim() != "") 
            sWhere += " And PostFrom=@PostFrom";
        if (Creator.Trim() != "")
            sWhere += " And Creator=@Creator";
        if (StartDate.Trim() != "")
            sWhere += " And StartDate >= @StartDate";
        if (EndDate.Trim() != "")
            sWhere += " And EndDate <= @EndDate";
        if (sWhere != "")
            sWhere = " Where " + sWhere.Substring(4);

        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [AnnouncementID]" +
            " ,[Creator]" +
            " ,[PostFrom]" +
            " ,[CreateDate]" +
            " ,[StartDate]" +
            " ,[EndDate]" +
            " ,[Importance]" +
            " ,[Subject]" +
            " ,[Contents]" +
            " ,[AttachedFile]" +
            " FROM " + TableName + sWhere + " ORDER BY CreateDate DESC", cn)) {
            da.SelectCommand.Parameters.AddWithValue("@PostFrom", PostFrom.Trim());
            da.SelectCommand.Parameters.AddWithValue("@Creator", Creator.Trim());
            da.SelectCommand.Parameters.AddWithValue("@StartDate", StartDate == "" ? Convert.DBNull : StartDate.Trim());
            da.SelectCommand.Parameters.AddWithValue("@EndDate", EndDate == "" ? Convert.DBNull : EndDate.Trim());
            da.Fill(dt);
            return dt;
        }
    }
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable SelectByAnnouncementID(String AnnouncementID) {
        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            //"SELECT * FROM " + TableName + " ORDER BY " + PrimaryKeyColumnNames[0], cn))
            "SELECT [AnnouncementID]" +
            " ,[Creator]" +
            " ,[PostFrom]" +
            " ,[CreateDate]" +
            " ,[StartDate]" +
            " ,[EndDate]" +
            " ,[Importance]" +
            " ,[Subject]" +
            " ,[Contents]" +
            " ,[AttachedFile]" +
            " FROM " + TableName + " Where AnnouncementID='" + AnnouncementID.Replace("--","").Replace("''","").Trim() + "'", cn)) {
            da.Fill(dt);
            return dt;
        }
    }


    [DataObjectMethod(DataObjectMethodType.Insert, true)]
    public int Insert(string Creator, string PostFrom, string StartDate, string EndDate, 
        string Importance, string Subject, string Contents, string FilePath) {
        
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "INSERT INTO " + TableName +
            " ([Creator],[PostFrom],[StartDate],[EndDate],[Importance],[Subject],[Contents],[AttachedFile]) " +
            " VALUES " +
            " (@Creator, @PostFrom, @StartDate, @EndDate, @Importance, @Subject, @Contents, @FilePath)", cn)) {
            cn.Open();

            if (Subject == "")
                new Exception("Miss Subject Text");

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@Creator", Creator.Trim());
            cm.Parameters.AddWithValue("@PostFrom", PostFrom.Trim());
            cm.Parameters.AddWithValue("@StartDate", StartDate.Trim());
            cm.Parameters.AddWithValue("@EndDate", EndDate.Trim());
            cm.Parameters.AddWithValue("@Importance", Importance.Trim());
            cm.Parameters.AddWithValue("@Subject", Subject.Trim());
            cm.Parameters.AddWithValue("@Contents", Contents == null ? Convert.DBNull : Contents.Trim());
            cm.Parameters.AddWithValue("@FilePath", FilePath == null ? Convert.DBNull : FilePath.Trim());

            cm.ExecuteNonQuery();
            cm.CommandText = "select IDENT_CURRENT('" + TableName + "') as AnnouncementID "; //"Select max(WorkID) as WorkID from " + TableName;
            SqlDataReader sr = cm.ExecuteReader();
            int iRtn = -1;
            if (sr.Read())
                iRtn = Convert.ToInt32(sr["AnnouncementID"].ToString()); //¦^¶Ç WorkRecordID index

            cn.Close();
            return iRtn;

            cn.Close();
        }
    }



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

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

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
}
