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

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

    [DataObjectMethod(DataObjectMethodType.Insert, true)]
    public void Insert(string SES_ToolNo, string Customer_ToolNo, string FABID,
        string ToolType, DateTime ReleasedDate, DateTime MovingDate, DateTime InstalledDate, string Guarantee, 
        string GuaranteeStartDate, string GuaranteeEndDate, string Module1,
        string Module2, string Module3, string Module4, string Module5, string Module6, 
        string Module7, string Module8, string Module9, string Module10, string Module11, 
        string Module12, string Module13, string Module14, string Module15, string Comment )
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "INSERT INTO " + TableName +
            " ([SES_ToolNo],[Customer_ToolNo],[FABID],[ToolType],[ReleasedDate],[MovingDate],[InstalledDate],[Guarantee],[GuaranteeStartDate],[GuaranteeEndDate], " +
            " [Module1],[Module2],[Module3],[Module4],[Module5],[Module6],[Module7],[Module8], " +
            " [Module9],[Module10],[Module11],[Module12],[Module13],[Module14],[Module15],[Comment]) " +
            " VALUES " +
            " (@SES_ToolNo,@Customer_ToolNo,@FABID,@ToolType,@ReleasedDate,@MovingDate,@InstalledDate,@Guarantee,@GuaranteeStartDate,@GuaranteeEndDate, " +
            " @Module1,@Module2,@Module3,@Module4,@Module5,@Module6,@Module7,@Module8, " +
            " @Module9,@Module10,@Module11,@Module12,@Module13,@Module14,@Module15,@Comment)", cn))
        {
            //if (MovingDate == DateTime.MinValue)
            //    MovingDate = DateTime.Parse("1990/1/1");

            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@SES_ToolNo", SES_ToolNo.Trim() );
            cm.Parameters.AddWithValue("@Customer_ToolNo", Customer_ToolNo.Trim() );
            cm.Parameters.AddWithValue("@FABID", FABID.Trim() );
            cm.Parameters.AddWithValue("@ToolType", ToolType ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@ReleasedDate", ReleasedDate == DateTime.MinValue ? Convert.DBNull : ReleasedDate);
            cm.Parameters.AddWithValue("@MovingDate", MovingDate == DateTime.MinValue ? Convert.DBNull : MovingDate);
            cm.Parameters.AddWithValue("@InstalledDate", InstalledDate == DateTime.MinValue ? Convert.DBNull : InstalledDate);
            cm.Parameters.AddWithValue("@Guarantee", Guarantee ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@GuaranteeStartDate", GuaranteeStartDate ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@GuaranteeEndDate", GuaranteeEndDate ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module1", Module1 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module2", Module2 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module3", Module3 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module4", Module4 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module5", Module5 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module6", Module6 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module7", Module7 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module8", Module8 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module9", Module9 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module10", Module10 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module11", Module11 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module12", Module12 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module13", Module13 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module14", Module14 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module15", Module15 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Comment", Comment ?? Convert.DBNull);

            cm.ExecuteNonQuery();
                
            cn.Close();
        }
    }

    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()
    {
        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 [SES_ToolNo]" +
              ",[Customer_ToolNo]" +
              ",[FABID]" +
              ",[ToolType]" +
              //",convert(varchar(50),[MovingDate],101) as MovingDate" +
              ",CAST(YEAR([ReleasedDate]) AS varchar(4)) + '/' + CAST(MONTH([ReleasedDate]) AS varchar(2)) + '/' + CAST(DAY([ReleasedDate]) AS varchar(2)) AS ReleasedDate " +
              ",CAST(YEAR([MovingDate]) AS varchar(4)) + '/' + CAST(MONTH([MovingDate]) AS varchar(2)) + '/' + CAST(DAY([MovingDate]) AS varchar(2)) AS MovingDate " +
              ",CAST(YEAR([InstalledDate]) AS varchar(4)) + '/' + CAST(MONTH([InstalledDate]) AS varchar(2)) + '/' + CAST(DAY([InstalledDate]) AS varchar(2)) AS InstalledDate " +
              ",[Guarantee]" +
              ",CAST(YEAR([GuaranteeStartDate]) AS varchar(4)) + '/' + CAST(MONTH([GuaranteeStartDate]) AS varchar(2)) + '/' + CAST(DAY([GuaranteeStartDate]) AS varchar(2)) AS GuaranteeStartDate " +
              ",CAST(YEAR([GuaranteeEndDate]) AS varchar(4)) + '/' + CAST(MONTH([GuaranteeEndDate]) AS varchar(2)) + '/' + CAST(DAY([GuaranteeEndDate]) AS varchar(2)) AS GuaranteeEndDate " +
              ",[Module1]" +
              ",[Module2]" +
              ",[Module3]" +
              ",[Module4]" +
              ",[Module5]" +
              ",[Module6]" +
              ",[Module7]" +
              ",[Module8]" +
              ",[Module9]" +
              ",[Module10]" +
              ",[Module11]" +
              ",[Module12]" +
              ",[Module13]" +
              ",[Module14]" +
              ",[Module15]" +
              ",[Comment] " + 
              " FROM " + TableName + " ORDER BY " + PrimaryKeyColumnNames[0], cn))
        {
            da.Fill(dt);
            return dt;
        }
    }
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable Select(string Condition) {
        DataTable dt = new DataTable(TableName);

        string sOrderBy = "", sWhere = "";
        sOrderBy = " Order By " + PrimaryKeyColumnNames[0] + " ";

        // 預找尋的條件
        if ((Condition != null) && Condition != "")
            sWhere = " Where " + Condition + " ";

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [SES_ToolNo]" +
            ",[Customer_ToolNo]" +
            ",[FABID]" +
            ",[ToolType]" +
            //",convert(varchar(50),[MovingDate],101) as MovingDate" +
              ",CAST(YEAR([ReleasedDate]) AS varchar(4)) + '/' + CAST(MONTH([ReleasedDate]) AS varchar(2)) + '/' + CAST(DAY([ReleasedDate]) AS varchar(2)) AS ReleasedDate " +
              ",CAST(YEAR([MovingDate]) AS varchar(4)) + '/' + CAST(MONTH([MovingDate]) AS varchar(2)) + '/' + CAST(DAY([MovingDate]) AS varchar(2)) AS MovingDate " +
              ",CAST(YEAR([InstalledDate]) AS varchar(4)) + '/' + CAST(MONTH([InstalledDate]) AS varchar(2)) + '/' + CAST(DAY([InstalledDate]) AS varchar(2)) AS InstalledDate " +
            ",[Guarantee]" +
              ",CAST(YEAR([GuaranteeStartDate]) AS varchar(4)) + '/' + CAST(MONTH([GuaranteeStartDate]) AS varchar(2)) + '/' + CAST(DAY([GuaranteeStartDate]) AS varchar(2)) AS GuaranteeStartDate " +
              ",CAST(YEAR([GuaranteeEndDate]) AS varchar(4)) + '/' + CAST(MONTH([GuaranteeEndDate]) AS varchar(2)) + '/' + CAST(DAY([GuaranteeEndDate]) AS varchar(2)) AS GuaranteeEndDate " +
            ",[Module1]" +
            ",[Module2]" +
            ",[Module3]" +
            ",[Module4]" +
            ",[Module5]" +
            ",[Module6]" +
            ",[Module7]" +
            ",[Module8]" +
            ",[Module9]" +
            ",[Module10]" +
            ",[Module11]" +
            ",[Module12]" +
            ",[Module13]" +
            ",[Module14]" +
            ",[Module15]" +
            ",[Comment] " +
            " FROM " + TableName + sWhere + sOrderBy, cn)) {
            da.Fill(dt);
            return dt;
        }
    }
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public string[] SelectDistinct(string ColumnName, string Condition) {
        DataTable dt = new DataTable(TableName);

        string sOrderBy = "", sWhere = "";
        // 預找尋的欄位名稱
        if ((ColumnName != null) && ColumnName != "")
            sOrderBy = " Order By " + ColumnName + " ";
        else
            return null;
        // 預找尋的條件
        if ((Condition != null) && Condition != "")
            sWhere = " Where " + Condition + " ";

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT distinct " + ColumnName +
            " FROM " + TableName + sWhere + sOrderBy, cn)) {
            da.Fill(dt);
            string[] ar = new string[dt.Rows.Count];
            int i = 0;
            foreach (DataRow dr in dt.Rows) {
                ar[i] = dr.ItemArray[0].ToString();
                i++;
            }

            return ar;
        }
    }

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

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT [SES_ToolNo]" +
              ",[Customer_ToolNo]" +
              ",[FABID]" +
              ",[ToolType]" +
              ",CAST(YEAR([ReleasedDate]) AS varchar(4)) + '/' + CAST(MONTH([ReleasedDate]) AS varchar(2)) + '/' + CAST(DAY([ReleasedDate]) AS varchar(2)) AS ReleasedDate " +
              ",CAST(YEAR([MovingDate]) AS varchar(4)) + '/' + CAST(MONTH([MovingDate]) AS varchar(2)) + '/' + CAST(DAY([MovingDate]) AS varchar(2)) AS MovingDate " +
              ",CAST(YEAR([InstalledDate]) AS varchar(4)) + '/' + CAST(MONTH([InstalledDate]) AS varchar(2)) + '/' + CAST(DAY([InstalledDate]) AS varchar(2)) AS InstalledDate " +
              ",[Guarantee]" +
              ",CAST(YEAR([GuaranteeStartDate]) AS varchar(4)) + '/' + CAST(MONTH([GuaranteeStartDate]) AS varchar(2)) + '/' + CAST(DAY([GuaranteeStartDate]) AS varchar(2)) AS GuaranteeStartDate " +
              ",CAST(YEAR([GuaranteeEndDate]) AS varchar(4)) + '/' + CAST(MONTH([GuaranteeEndDate]) AS varchar(2)) + '/' + CAST(DAY([GuaranteeEndDate]) AS varchar(2)) AS GuaranteeEndDate " +
              ",[Module1]" +
              ",[Module2]" +
              ",[Module3]" +
              ",[Module4]" +
              ",[Module5]" +
              ",[Module6]" +
              ",[Module7]" +
              ",[Module8]" +
              ",[Module9]" +
              ",[Module10]" +
              ",[Module11]" +
              ",[Module12]" +
              ",[Module13]" +
              ",[Module14]" +
              ",[Module15]" +
              ",[Comment] " +
              " FROM " + TableName + " Where SES_ToolNo='" + SES_ToolNo + "' ORDER BY SES_ToolNo", cn)) {
            da.Fill(dt);
            return dt;
        }
    }
    
    
    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void Update(string SES_ToolNo, string Customer_ToolNo, string FABID,
        string ToolType, DateTime ReleasedDate, DateTime MovingDate, DateTime InstalledDate, string Guarantee, string GuaranteeStartDate, string GuaranteeEndDate, string Module1,
        string Module2, string Module3, string Module4, string Module5, string Module6,
        string Module7, string Module8, string Module9, string Module10, string Module11,
        string Module12, string Module13, string Module14, string Module15, string Comment, string original_SES_ToolNo)
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET [SES_ToolNo]=@SES_ToolNo, " +
            " [Customer_ToolNo]=@Customer_ToolNo, " +
            " [FABID]=@FABID, [ToolType]=@ToolType, " +
            " [ReleasedDate]=@ReleasedDate, " +
            " [MovingDate]=@MovingDate, " +
            " [InstalledDate]=@InstalledDate, " +
            " [Guarantee]=@Guarantee, " +
            " [GuaranteeStartDate]=@GuaranteeStartDate, " +
            " [GuaranteeEndDate]=@GuaranteeEndDate, " +
            " [Module1]=@Module1, " +
            " [Module2]=@Module2, " +
            " [Module3]=@Module3, " +
            " [Module4]=@Module4, " +
            " [Module5]=@Module5, " +
            " [Module6]=@Module6, " +
            " [Module7]=@Module7, " +
            " [Module8]=@Module8, " +
            " [Module9]=@Module9, " +
            " [Module10]=@Module10, " +
            " [Module11]=@Module11, " +
            " [Module12]=@Module12, " +
            " [Module13]=@Module13, " +
            " [Module14]=@Module14, " +
            " [Module15]=@Module15, " +
            " [Comment]=@Comment " +
            " Where SES_ToolNo=@original_SES_ToolNo", cn))
        {
            //if (MovingDate == DateTime.MinValue)
            //    MovingDate = DateTime.Parse("1990/1/1");

            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@SES_ToolNo", SES_ToolNo.Trim() );
            cm.Parameters.AddWithValue("@Customer_ToolNo", Customer_ToolNo.Trim() );
            cm.Parameters.AddWithValue("@FABID", FABID.Trim() );
            cm.Parameters.AddWithValue("@ToolType", ToolType ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@ReleasedDate", ReleasedDate == DateTime.MinValue ? Convert.DBNull : ReleasedDate);
            cm.Parameters.AddWithValue("@MovingDate", MovingDate == DateTime.MinValue ? Convert.DBNull : MovingDate);
            cm.Parameters.AddWithValue("@InstalledDate", InstalledDate == DateTime.MinValue ? Convert.DBNull : InstalledDate);
            cm.Parameters.AddWithValue("@Guarantee", Guarantee ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@GuaranteeStartDate", GuaranteeStartDate ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@GuaranteeEndDate", GuaranteeEndDate ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module1", Module1 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module2", Module2 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module3", Module3 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module4", Module4 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module5", Module5 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module6", Module6 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module7", Module7 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module8", Module8 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module9", Module9 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module10", Module10 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module11", Module11 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module12", Module12 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module13", Module13 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module14", Module14 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Module15", Module15 ?? Convert.DBNull);
            cm.Parameters.AddWithValue("@Comment", Comment ?? Convert.DBNull);
            SqlParameter parameter = cm.Parameters.AddWithValue("@original_SES_ToolNo", original_SES_ToolNo);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }

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

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
}
