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

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

    [DataObjectMethod(DataObjectMethodType.Insert, true)]
    public int Insert(string JobNo, string CreateDate, string CreateFrom, string Proposer, string FABID,
        string SES_ToolNo, string Description, string RootCause, //string GroupID, string Responsibility, string ExpectStartDate, string ExpectEndDate, 
        string Status, string FinishDate, string DeliveryReportDate, string TroubleNo, //string AttachFile, 
        string Comment){
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "INSERT INTO " + TableName +
            " ([JobNo] " +
            ",[CreateDate] " +
            ",[CreateFrom] " +
            ",[Proposer] " +
            ",[FABID] " +
            ",[SES_ToolNo] " +
            ",[Description] " +
            ",[RootCause] " +
            //",[GroupID] " +
            //",[Responsibility] " +
            //",[ExpectStartDate] " +
            //",[ExpectEndDate] " +
            ",[Status] " +
            ",[FinishDate] " +
            //",[LastAction] " +
            //",[LastActionDate] " +
            ",[DeliveryReportDate] " +
            ",[TroubleNo] " +
            //",[AttachFile] " +
            ",[Comment]) " +
            " VALUES " +
            " (@JobNo " +
            ",@CreateDate " +
            ",@CreateFrom " +
            ",@Proposer " +
            ",@FABID " +
            ",@SES_ToolNo " +
            ",@Description " +
            ",@RootCause " +
            //",@GroupID " +
            //",@Responsibility " +
            //",@ExpectStartDate " +
            //",@ExpectEndDate " +
            ",@Status " +
            ",@FinishDate " +
            //",@LastAction " +
            //",@LastActionDate " +
            ",@DeliveryReportDate " +
            ",@TroubleNo " +
            //",@AttachFile " +
            ",@Comment)", cn))
        {
            // check date format, if have input data of date
            if (CreateDate != null ) {
                try { DateTime.Parse(CreateDate); }
                catch { throw new Exception("DateTime format incorrect [CreateDate]-" + CreateDate); }
            }
            //if (ExpectStartDate != null) {
            //    try { DateTime.Parse(ExpectStartDate); }
            //    catch { ExpectStartDate = null;}//throw new Exception("DateTime format incorrect [ExpectStartDate]-" + ExpectStartDate); }
            //}
            //if (ExpectEndDate != null) {
            //    try { DateTime.Parse(ExpectEndDate); }
            //    catch { ExpectEndDate = null; }//throw new Exception("DateTime format incorrect [ExpectEndDate]-" + ExpectEndDate); }
            //}
            if (FinishDate != null) {
                try { DateTime.Parse(FinishDate); }
                catch { throw new Exception("DateTime format incorrect [CloseDate]-" + FinishDate); }
            }
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@JobNo", JobNo ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@CreateDate", CreateDate ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@CreateFrom", CreateFrom ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Proposer", Proposer ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@FABID", FABID);
            cm.Parameters.AddWithValue("@SES_ToolNo", SES_ToolNo);
            cm.Parameters.AddWithValue("@Description", Description ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@RootCause", RootCause ?? Convert.DBNull);
            //cm.Parameters.AddWithValue("@GroupID", GroupID ?? Convert.DBNull);
            //cm.Parameters.AddWithValue("@Responsibility", Responsibility ?? Convert.DBNull);
            //cm.Parameters.AddWithValue("@ExpectStartDate", ExpectStartDate ?? Convert.DBNull);
            //cm.Parameters.AddWithValue("@ExpectEndDate", ExpectEndDate ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Status", Status ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@FinishDate", FinishDate ?? Convert.DBNull);
            //cm.Parameters.AddWithValue("@LastAction", LastAction ?? Convert.DBNull);
            //cm.Parameters.AddWithValue("@LastActionDate", LastActionDate ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@DeliveryReportDate", DeliveryReportDate ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@TroubleNo", TroubleNo ?? Convert.DBNull);
            //cm.Parameters.AddWithValue("@AttachFile", AttachFile ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Comment", Comment ?? Convert.DBNull);

            cm.ExecuteNonQuery();

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

            cn.Close();
            return iRtn;
        }
    }

    public int Count()
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "SELECT Count(*) FROM " + TableName + " ORDER BY " + PrimaryKeyColumnNames[0] , cn))
        {
            cn.Open();

            cm.CommandType = CommandType.Text;

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

            cn.Close();

            return i;
        }
    }

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

        string sWhereCondi = "";
        if ((Condition != null) && Condition != "") {
            //if (Condition.IndexOf("=") > 0 || Condition.IndexOf("in") > 0)
                sWhereCondi = " Where " + Condition + " ";
            //else
            //    sWhereCondi = " Where WorkID='" + Condition + "' ";
        }
        if ( OrderBy != null && OrderBy != "")
            OrderBy = " Order By " + OrderBy;
        else
            OrderBy = "";


        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            //"SELECT [WorkID],[JobNo],convert( varchar(50), [CreateDate], 101) as CreateDate,[CompleteDate],[FABID],[SES_ToolNo],[Description]" +
            //",[GroupID],[Responsibility],[LastAction],convert( varchar(50), [LastRecord], 101) as [LastRecord],[RootCause] FROM " + TableName + " ORDER BY " + PrimaryKeyColumnNames[0], cn))
            "Select [WorkID] ," + 
            "[JobNo] " + 
            ",CreateDate " + 
            //",CAST(YEAR([CreateDate]) AS varchar(4)) + '/' + CAST(MONTH([CreateDate]) AS varchar(2)) + '/' + CAST(DAY([CreateDate]) AS varchar(2)) AS CreateDate " +
            ",[CreateFrom] " + 
            ",[Proposer] " + 
            ",[FABID] " + 
            ",[SES_ToolNo] " + 
            ",[Description] " + 
            ",[RootCause] " + 
            //",[GroupID] " + 
            //",[Responsibility] " + 
            ////",[ExpectStartDate] " +
            //",CAST(YEAR([ExpectStartDate]) AS varchar(4)) + '/' + CAST(MONTH([ExpectStartDate]) AS varchar(2)) + '/' + CAST(DAY([ExpectStartDate]) AS varchar(2)) AS ExpectStartDate " +
            ////",[ExpectEndDate] " +
            //",CAST(YEAR([ExpectEndDate]) AS varchar(4)) + '/' + CAST(MONTH([ExpectEndDate]) AS varchar(2)) + '/' + CAST(DAY([ExpectEndDate]) AS varchar(2)) AS ExpectEndDate " +
            ",[Status] " + 
            //",[FinishDate] " + 
            ",CAST(YEAR([FinishDate]) AS varchar(4)) + '/' + CAST(MONTH([FinishDate]) AS varchar(2)) + '/' + CAST(DAY([FinishDate]) AS varchar(2)) AS FinishDate " +
            ",[LastAction] " + 
            ",[LastActionDate] " +
            ",CAST(YEAR([DeliveryReportDate]) AS varchar(4)) + '/' + CAST(MONTH([DeliveryReportDate]) AS varchar(2)) + '/' + CAST(DAY([DeliveryReportDate]) AS varchar(2)) AS DeliveryReportDate " +
            ",[TroubleNo] " + 
            //",[AttachFile] " + 
            ",[Comment]  FROM " + TableName + sWhereCondi + OrderBy, cn)){
            da.Fill(dt);
            return dt;
        }
    }
    //public DataTable SelectByManualSearch(string Condition, string OrderBy) {
    //    DataTable dt = new DataTable(TableName);
    //    using (SqlConnection cn = new SqlConnection(DataConnection))
    //    using (SqlDataAdapter da = new SqlDataAdapter(
    //        "Select T1.[WorkID] ," +
    //        "[JobNo] " +
    //        ",CreateDate " +
    //        ",[CreateFrom] " +
    //        ",[Proposer] " +
    //        ",[FABID] " +
    //        ",[SES_ToolNo] " +
    //        ",[Description] " +
    //        ",[RootCause] " +
    //        //",[GroupID] " + 
    //        ",T2.[Responsibility] " + 
    //        ////",[ExpectStartDate] " +
    //        ",CAST(YEAR(T2.[ExpectStartDate]) AS varchar(4)) + '/' + CAST(MONTH(T2.[ExpectStartDate]) AS varchar(2)) + '/' + CAST(DAY(T2.[ExpectStartDate]) AS varchar(2)) AS ExpectStartDate " +
    //        ////",[ExpectEndDate] " +
    //        //",CAST(YEAR([ExpectEndDate]) AS varchar(4)) + '/' + CAST(MONTH([ExpectEndDate]) AS varchar(2)) + '/' + CAST(DAY([ExpectEndDate]) AS varchar(2)) AS ExpectEndDate " +
    //        ",[Status] " +
    //        //",[FinishDate] " + 
    //        ",CAST(YEAR([FinishDate]) AS varchar(4)) + '/' + CAST(MONTH([FinishDate]) AS varchar(2)) + '/' + CAST(DAY([FinishDate]) AS varchar(2)) AS FinishDate " +
    //        ",[LastAction] " +
    //        ",[LastActionDate] " +
    //        ",[TroubleNo] " +
    //        //",[AttachFile] " + 
    //        ",[Comment] " +
    //        " FROM " + TableName + " T1 " +
    //        " left join " +
    //        "   (Select WorkID,ExpectStartDate,ExpectEndDate,GroupName,Responsibility from TEC_MainWorkSchedules " +
    //        "   Where ScheduleID in (Select max(ScheduleID) from TEC_MainWorkSchedules Group By WorkID)) T2" +
    //        " on T1.WorkID=T2.WorkID " +
    //        " Where " + Condition + " Order By " + OrderBy, cn)) {
            
    //        if ( Condition == "")
    //            da.SelectCommand.CommandText = da.SelectCommand.CommandText.Replace("Select T1.[WorkID]", "Select Top 100 T1.[WorkID]");

    //        da.Fill(dt);
    //        return dt;
    //    }
    //}
    public DataTable SelectByManualSearch(string Condition, string OrderBy) {
        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "Select * from " +
                "(Select T1.[WorkID] ," +
                "[JobNo] " +
                ",CreateDate " +
                ",[CreateFrom] " +
                ",[Proposer] " +
                ",[FABID] " +
                ",[SES_ToolNo] " +
                ",[Description] " +
                ",[RootCause] " +
                //",[GroupID] " + 
                ",T2.[Responsibility] " +
                ////",[ExpectStartDate] " +
                ",CAST(YEAR(T2.[ExpectStartDate]) AS varchar(4)) + '/' + CAST(MONTH(T2.[ExpectStartDate]) AS varchar(2)) + '/' + CAST(DAY(T2.[ExpectStartDate]) AS varchar(2)) AS ExpectStartDate " +
                ////",[ExpectEndDate] " +
                //",CAST(YEAR([ExpectEndDate]) AS varchar(4)) + '/' + CAST(MONTH([ExpectEndDate]) AS varchar(2)) + '/' + CAST(DAY([ExpectEndDate]) AS varchar(2)) AS ExpectEndDate " +
                ",[Status] " +
                //",[FinishDate] " + 
                ",CAST(YEAR([FinishDate]) AS varchar(4)) + '/' + CAST(MONTH([FinishDate]) AS varchar(2)) + '/' + CAST(DAY([FinishDate]) AS varchar(2)) AS FinishDate " +
                //",[LastAction] " +
                //",[LastActionDate] " +
                ",CAST(YEAR([DeliveryReportDate]) AS varchar(4)) + '/' + CAST(MONTH([DeliveryReportDate]) AS varchar(2)) + '/' + CAST(DAY([DeliveryReportDate]) AS varchar(2)) AS DeliveryReportDate " +
                ",[TroubleNo] " +
                //",[AttachFile] " + 
                ",[Comment] " +
                " FROM " + TableName + " T1 " +
                " left join " +
                "   (Select WorkID,ExpectStartDate,ExpectEndDate,GroupName,Responsibility from TEC_MainWorkSchedules " +
                "   Where cast(WorkID as varchar(20)) + cast(ExpectStartDate as varchar(20)) " +
                "   in (Select cast(WorkID as varchar(20)) + cast(max(ExpectStartDate) as varchar(20)) from TEC_MainWorkSchedules Group By WorkID)) T2" +
                " on T1.WorkID=T2.WorkID " +
                " Where " + Condition + ") TT1 " +
            " left join " +
            "   (Select WorkID,RecordDateTime as [LastActionDate],Journalist as Recorder,Action as[LastAction],AttachFile as LastRecordFile " +
            "   from TEC_WorkRecord " +
            "   Where WorkRecordID in (Select max(WorkRecordID) from TEC_WorkRecord Group By WorkID)) TT2" +
            " on TT1.WorkID=TT2.WorkID " +
            " Order By " + OrderBy, cn)) {

            if (Condition == "")
                da.SelectCommand.CommandText = da.SelectCommand.CommandText.Replace("Select T1.[WorkID]", "Select Top 100 T1.[WorkID]");

            da.Fill(dt);
            return dt;
        }
    }
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable SelectFromWorkRecord(string JobNo, string FABID, string ToolID) {
        DataTable dt = new DataTable(TableName);
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "Select [WorkID]" +
            ",[JobNo] " +
            //",CreateDate " +
            ",CAST(YEAR([CreateDate]) AS varchar(4)) + '/' + CAST(MONTH([CreateDate]) AS varchar(2)) + '/' + CAST(DAY([CreateDate]) AS varchar(2)) AS CreateDate " +
            ",[FABID] " +
            ",[SES_ToolNo] " +
            ",[Description]   FROM " + TableName +
            " Where (CHARINDEX ( @JobNo, JobNo) > 0 or  CHARINDEX ( JobNo, @JobNo) > 0) and FABID=@FABID and SES_ToolNo=@ToolID order by CreateDate DESC", cn)) {
            da.SelectCommand.CommandType = CommandType.Text;
            da.SelectCommand.Parameters.AddWithValue("@JobNo", JobNo ?? "");
            da.SelectCommand.Parameters.AddWithValue("@FABID", FABID ?? "");
            da.SelectCommand.Parameters.AddWithValue("@ToolID", ToolID ?? "");

            da.Fill(dt);
            return dt;
        }
    }
    //[DataObjectMethod(DataObjectMethodType.Select, true)]
    //public DataTable GetWorkNoRecord(string Account) { //個人目錄首頁
    //    DataTable dt = new DataTable(TableName);

    //    string sUserName = new HR_EmployeesData().Select("Name", "Account='" + Account + "'").Rows[0]["Name"].ToString();
    //    string sWhereCondi = "";

    //    using (SqlConnection cn = new SqlConnection(DataConnection))
    //    using (SqlDataAdapter da = new SqlDataAdapter(
    //        " Select T1.*,T2.Journalist " +
    //        " From " +
    //        " (Select " + 
    //        TableName + ".[WorkID] ," +
    //        "[JobNo] " +
    //        ",CAST(YEAR([CreateDate]) AS varchar(4)) + '/' + CAST(MONTH([CreateDate]) AS varchar(2)) + '/' + CAST(DAY([CreateDate]) AS varchar(2)) AS CreateDate " +
    //        ",[CreateFrom] " +
    //        ",[Proposer] " +
    //        ",[FABID] " +
    //        ",[SES_ToolNo] " +
    //        ",[Description] " +
    //        ",[RootCause] " +
    //        //",[GroupID] " +
    //        //",[Responsibility] " +
    //        ",TEC_MainWorkSchedules.[Responsibility] " +
    //        //",CAST(YEAR([ExpectStartDate]) AS varchar(4)) + '/' + CAST(MONTH([ExpectStartDate]) AS varchar(2)) + '/' + CAST(DAY([ExpectStartDate]) AS varchar(2)) AS ExpectStartDate " +
    //        //",CAST(YEAR([ExpectEndDate]) AS varchar(4)) + '/' + CAST(MONTH([ExpectEndDate]) AS varchar(2)) + '/' + CAST(DAY([ExpectEndDate]) AS varchar(2)) AS ExpectEndDate " +
    //        ",[Status] " +
    //        ",CAST(YEAR([FinishDate]) AS varchar(4)) + '/' + CAST(MONTH([FinishDate]) AS varchar(2)) + '/' + CAST(DAY([FinishDate]) AS varchar(2)) AS FinishDate " +
    //        ",[LastAction] " +
    //        ",[LastActionDate] " +
    //        ",[TroubleNo] " +
    //        //",[AttachFile] " +
    //        ",[Comment]  " +
    //        ",CAST(YEAR(TEC_MainWorkSchedules.[ExpectEndDate]) AS varchar(4)) + '/' + CAST(MONTH(TEC_MainWorkSchedules.[ExpectEndDate]) AS varchar(2)) + '/' + CAST(DAY(TEC_MainWorkSchedules.[ExpectEndDate]) AS varchar(2)) AS ExpectEndDate " +
    //        " FROM " + TableName + " ,TEC_MainWorkSchedules " +
    //        " WHERE TEC_MainWorkSchedules.WorkID=" + TableName + ".WorkID " +
    //        "   and (TEC_MainWorkSchedules.Responsibility IS Not Null " +
    //        "   and LEN(TEC_MainWorkSchedules.Responsibility) > 0 " +
    //        //"   and CHARINDEX( @UserName,Responsibility) = 1 ) " +
    //        //"   and SUBSTRING(Responsibility, 1, CHARINDEX( CHAR(13),Responsibility)-1) = @UserName) " +
    //        "   and CHARINDEX(@UserName, TEC_MainWorkSchedules.Responsibility, 1 ) > 0) " +
    //        "   and ((TEC_MainWorkSchedules.ExpectStartDate <= getdate() and LastAction IS NULL) " +
    //        "      or (getdate() between TEC_MainWorkSchedules.ExpectStartDate and DATEADD(day, 7, TEC_MainWorkSchedules.ExpectEndDate) )) ) as T1" +
    //        " left join TEC_WorkRecord as T2 " +
    //        " on T1.WorkID=T2.WorkID " +
    //        " Where CHARINDEX(@UserName, IsNull(Journalist,''), 1 ) <= 0 " +
    //        " Order by T1.ExpectEndDate desc", cn)) {

    //        cn.Open();

    //        da.SelectCommand.CommandType = CommandType.Text;
    //        da.SelectCommand.Parameters.AddWithValue("@UserName", sUserName);

    //        da.Fill(dt);
    //        return dt;
    //    }
    //}
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable GetWorkNoRecord(string Account) { //個人目錄首頁
        DataTable dt = new DataTable(TableName);

        string sUserName = new HR_EmployeesData().getUserNameByAccount(Account);
        string sWhereCondi = "";

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            " Select " +
            " T1.[WorkID] " +
            ",[JobNo] " +
            ",CAST(YEAR([CreateDate]) AS varchar(4)) + '/' + CAST(MONTH([CreateDate]) AS varchar(2)) + '/' + CAST(DAY([CreateDate]) AS varchar(2)) AS CreateDate " +
            ",[CreateFrom] " +
            ",[Proposer] " +
            ",[FABID] " +
            ",[SES_ToolNo] " +
            ",[Description] " +
            ",[RootCause] " +
            //",[GroupID] " +
            //",[Responsibility] " +
            ",T2.[ScheduleID]  " +
            ",T2.[Responsibility] " +
            //",CAST(YEAR([ExpectStartDate]) AS varchar(4)) + '/' + CAST(MONTH([ExpectStartDate]) AS varchar(2)) + '/' + CAST(DAY([ExpectStartDate]) AS varchar(2)) AS ExpectStartDate " +
            //",CAST(YEAR([ExpectEndDate]) AS varchar(4)) + '/' + CAST(MONTH([ExpectEndDate]) AS varchar(2)) + '/' + CAST(DAY([ExpectEndDate]) AS varchar(2)) AS ExpectEndDate " +
            ",[Status] " +
            ",CAST(YEAR([FinishDate]) AS varchar(4)) + '/' + CAST(MONTH([FinishDate]) AS varchar(2)) + '/' + CAST(DAY([FinishDate]) AS varchar(2)) AS FinishDate " +
            ",[LastAction] " +
            ",[LastActionDate] " +
            ",CAST(YEAR([DeliveryReportDate]) AS varchar(4)) + '/' + CAST(MONTH([DeliveryReportDate]) AS varchar(2)) + '/' + CAST(DAY([DeliveryReportDate]) AS varchar(2)) AS DeliveryReportDate " +
            ",[TroubleNo] " +
            //",[AttachFile] " +
            ",[Comment]  " +
            ",CAST(YEAR(T2.[ExpectStartDate]) AS varchar(4)) + '/' + CAST(MONTH(T2.[ExpectStartDate]) AS varchar(2)) + '/' + CAST(DAY(T2.[ExpectStartDate]) AS varchar(2)) AS ExpectStartDate " +
            ",CAST(YEAR(T2.[ExpectEndDate]) AS varchar(4)) + '/' + CAST(MONTH(T2.[ExpectEndDate]) AS varchar(2)) + '/' + CAST(DAY(T2.[ExpectEndDate]) AS varchar(2)) AS ExpectEndDate " +
            " FROM " + TableName + " T1 ,TEC_MainWorkSchedules T2 " + 
            " Where T1.WorkID=T2.WorkID " +
            "   and CHARINDEX(@UserName, T2.Responsibility, 1 ) > 0 " +
            "   and (getdate() between T2.ExpectStartDate and DATEADD(day, 7, T2.ExpectEndDate)) " +
            "   and T1.WorkID not in (Select Distinct WorkID from TEC_WorkRecord Where CHARINDEX(@UserName, IsNull(Journalist,''), 1 ) >0) " +

            //"(Select [ScheduleID],TEC_MainWorkSchedules.[WorkID],[ExpectStartDate],[ExpectEndDate],[Responsibility],[GroupName],[Journalist] " +
            //    "From TEC_MainWorkSchedules " +
            //        "left join TEC_WorkRecord " +
            //        "on TEC_MainWorkSchedules.WorkID=TEC_WorkRecord.WorkID " +
            //    "Where CHARINDEX(@UserName, TEC_MainWorkSchedules.Responsibility, 1 ) > 0 " +
            //        "and (getdate() between TEC_MainWorkSchedules.ExpectStartDate and DATEADD(day, 7, TEC_MainWorkSchedules.ExpectEndDate)) " +
            //        "and CHARINDEX(@UserName, IsNull(Journalist,''), 1 ) <=0 " +
            //    ") as T2 " +
            //"Where T1.WorkID=T2.WorkID " +
            "Order By T1.WorkID ASC, T2.ExpectEndDate ASC ", cn)) {

            cn.Open();

            da.SelectCommand.CommandType = CommandType.Text;
            da.SelectCommand.Parameters.AddWithValue("@UserName", sUserName);

            da.Fill(dt);
            string sTmp = "";
            for (int i = dt.Rows.Count -1; i >= 0; i--) {
                if (dt.Rows[i]["WorkID"].ToString() == sTmp) { 
                    dt.Rows.RemoveAt(i);
                }
                else
                    sTmp = dt.Rows[i]["WorkID"].ToString();
            }
            return dt;
        }
    }

    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void Update(string JobNo, string CreateDate, string CreateFrom, string Proposer, string FABID,
        string SES_ToolNo, string Description, string RootCause, //string GroupID, string Responsibility, string ExpectStartDate, string ExpectEndDate, 
        string Status, string FinishDate, //string LastAction, string LastActionDate, 
        string DeliveryReportDate, string TroubleNo, //string AttachFile, 
        string Comment, string original_WorkID)
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET [JobNo] = @JobNo " +
              ",[CreateDate] = @CreateDate" +
              ",[CreateFrom] = @CreateFrom " +
              ",[Proposer] = @Proposer " +
              ",[FABID] = @FABID " +
              ",[SES_ToolNo] = @SES_ToolNo " +
              ",[Description] = @Description " +
              ",[RootCause] = @RootCause " +
              //",[GroupID] = @GroupID " +
              //",[Responsibility] = @Responsibility " +
              //",[ExpectStartDate] = @ExpectStartDate " +
              //",[ExpectEndDate] = @ExpectEndDate " +
              ",[Status] = @Status " +
              ",[FinishDate] = @FinishDate " +
              //",[LastAction] = @LastAction " +
              //",[LastActionDate] = @LastActionDate " +
              ",[DeliveryReportDate] = @DeliveryReportDate " +
              ",[TroubleNo] = @TroubleNo " +
              //",[AttachFile] = @AttachFile " +
              ",[Comment] = @Comment " +
              " Where WorkID=@original_WorkID", cn))
        {
            try {DateTime.Parse(CreateDate);} catch{ throw new Exception("DateTime format incorrect");}
            //try { if (ExpectStartDate != null && ExpectStartDate != "") DateTime.Parse(ExpectStartDate); }
            //catch { throw new Exception("DateTime format incorrect"); }
            //try { if (ExpectEndDate != null && ExpectEndDate != "") DateTime.Parse(ExpectEndDate); }
            //catch { throw new Exception("DateTime format incorrect"); }
            try { if (FinishDate != null && FinishDate != "") DateTime.Parse(FinishDate); }
            catch { throw new Exception("DateTime format incorrect"); }

            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@JobNo", JobNo ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@CreateDate", (CreateDate == null || CreateDate == "") ? Convert.DBNull : CreateDate);
            cm.Parameters.AddWithValue("@CreateFrom", CreateFrom);
            cm.Parameters.AddWithValue("@Proposer", Proposer);
            cm.Parameters.AddWithValue("@FABID", FABID );
            cm.Parameters.AddWithValue("@SES_ToolNo", SES_ToolNo);
            cm.Parameters.AddWithValue("@Description", Description ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@RootCause", RootCause ?? Convert.DBNull);
            //cm.Parameters.AddWithValue("@GroupID", GroupID ?? Convert.DBNull);
            //cm.Parameters.AddWithValue("@Responsibility", Responsibility ?? Convert.DBNull);
            //cm.Parameters.AddWithValue("@ExpectStartDate", (ExpectStartDate == null || ExpectStartDate == "") ? Convert.DBNull : ExpectStartDate);
            //cm.Parameters.AddWithValue("@ExpectEndDate", (ExpectEndDate == null || ExpectEndDate == "") ? Convert.DBNull : ExpectEndDate);
            cm.Parameters.AddWithValue("@Status", (Status == null || Status == "") ? Convert.DBNull : Status);
            //cm.Parameters.AddWithValue("@LastAction", LastAction ?? Convert.DBNull);
            //cm.Parameters.AddWithValue("@LastActionDate", LastActionDate ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@FinishDate", (FinishDate == null || FinishDate == "") ? Convert.DBNull : FinishDate);
            cm.Parameters.AddWithValue("@DeliveryReportDate", (DeliveryReportDate == null || DeliveryReportDate == "") ? Convert.DBNull : DeliveryReportDate);
            cm.Parameters.AddWithValue("@TroubleNo", (TroubleNo == null || TroubleNo == "") ? Convert.DBNull : TroubleNo);
            //cm.Parameters.AddWithValue("@AttachFile", AttachFile ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Comment", (Comment == null || Comment == "") ? Convert.DBNull : Comment);
            SqlParameter parameter = cm.Parameters.AddWithValue("@original_WorkID", original_WorkID);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void UpdateCommentOnly(string Comment, string original_WorkID) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET [Comment] = @Comment " +
              " Where WorkID=@original_WorkID", cn)) {

            cn.Open();

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

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void UpdateComplete(DateTime FinishDate, string original_WorkID) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET [Status] = @Status " +
              ",[FinishDate] = @FinishDate " +
              " Where WorkID=@original_WorkID", cn)) {

            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@Status", "Finished");
            cm.Parameters.AddWithValue("@FinishDate", FinishDate);
            SqlParameter parameter = cm.Parameters.AddWithValue("@original_WorkID", original_WorkID);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void UpdateWorkRecord(string LastAction, DateTime LastActionDate, string original_WorkID) {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET [LastAction] = @LastAction " +
              ",[LastActionDate] = @LastActionDate " +
              " Where WorkID=@original_WorkID", cn)) {

            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@LastAction", LastAction);
            cm.Parameters.AddWithValue("@LastActionDate", LastActionDate.ToString("yyyy/MM/dd HH:mm:ss")) ;
            SqlParameter parameter = cm.Parameters.AddWithValue("@original_WorkID", original_WorkID);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }


    [DataObjectMethod(DataObjectMethodType.Delete, true)]
    public void Delete(string original_WorkID)
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand("DELETE " + TableName + " WHERE WorkID=@original_WorkID", cn))
        {
            cn.Open();
    
            cm.CommandType = CommandType.Text;
            SqlParameter parameter = cm.Parameters.AddWithValue("@original_WorkID", original_WorkID);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

            cm.CommandText = "Delete TEC_MainWorkSchedules Where WorkID=@original_WorkID";
            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
}
