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

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


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

        string sWhereCondi = "";
        if ((Condition != null) && Condition != "") {
            if (Condition.IndexOf("=") > 0)
                sWhereCondi = " Where " + Condition + " ";
            else
                sWhereCondi = " Where WorkID='" + Condition + "' ";
        }
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [WorkRecordID]" +
            ",[WorkID] " +
            ",[RecordDateTime] " +
            ",[Journalist] " +
            ",[Action] " +
            //",[AttachFile]" +
            " FROM " + TableName + sWhereCondi + " ORDER BY RecordDateTime Desc", cn)) {
            da.Fill(dt);
            return dt;
        }
    }


    public int Insert(string WorkID, string RecordDateTime, string Journalist, string Action){ //, string AttachFile) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "INSERT INTO " + TableName +
            " ([WorkID]" +
            " ,[RecordDateTime]" +
            " ,[Journalist]" +
            " ,[Action])" +
            " VALUES " +
            " (@WorkID,@RecordDateTime,@Journalist,@Action)", cn)) {

            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@WorkID", WorkID);
            cm.Parameters.AddWithValue("@RecordDateTime", RecordDateTime);
            cm.Parameters.AddWithValue("@Journalist", Journalist);
            cm.Parameters.AddWithValue("@Action", Action);
            //cm.Parameters.AddWithValue("@AttachFile", AttachFile);

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

            cn.Close();
            return iRtn;

            cn.Close();
        }
    }


    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void Update(string WorkID, string RecordDateTime, string Journalist, string Action, string original_WorkRecordID) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET [WorkID]=@WorkID, " +
            " [RecordDateTime]=@RecordDateTime, " +
            " [Journalist]=@Journalist, " +
            " [Action]=@Action " +
            " Where WorkRecordID=@original_WorkRecordID", cn)) {

            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@WorkID", WorkID.Trim());
            cm.Parameters.AddWithValue("@RecordDateTime", RecordDateTime ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Journalist", Journalist.Trim());
            cm.Parameters.AddWithValue("@Action", Action.Trim());
            SqlParameter parameter = cm.Parameters.AddWithValue("@original_WorkRecordID", original_WorkRecordID);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }



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

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

            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;
            SqlParameter parameter = cm.Parameters.AddWithValue("@WorkID", WorkID);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
}