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

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

    [DataObjectMethod(DataObjectMethodType.Insert, true)]
    public void Insert(string JobNo, string FABID, string Tools, string CreateDate, string Charge, Boolean IsClosed, string Description)
    {
        if (Tools != null) {
            Tools = Tools.Replace("\r\n", "@"); //直接使用在 split 中會有問題 (多出空白陣列)
            string[] arTemp = Tools.Split("@".ToCharArray());
            Root.pcCommon.ArraySort (ref arTemp);
            Tools = "";
            foreach (string s in arTemp) {
                Tools += "\r\n" + s;
            }
            if (Tools != "") Tools = Tools.Substring(2);
        }

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "INSERT INTO " + TableName +
            " ([JobNo]" +
            " ,[FABID]" +
            " ,[Tools]" +
            " ,[CreateDate]" +
            " ,[Charge]" +
            " ,[IsClosed]" +
            " ,[Description])" +
            " VALUES " +
            " (@JobNo,@FABID,@Tools,@CreateDate,@Charge,@IsClosed,@Description)", cn))
        {

            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@JobNo", JobNo.Trim() );
            cm.Parameters.AddWithValue("@FABID", FABID == null ? Convert.DBNull : FABID.Trim());
            cm.Parameters.AddWithValue("@Tools", Tools == null ? Convert.DBNull : Tools.Trim());
            cm.Parameters.AddWithValue("@CreateDate", CreateDate);
            cm.Parameters.AddWithValue("@Charge", Charge == null ? Convert.DBNull : Charge.Trim() );
            cm.Parameters.AddWithValue("@IsClosed", IsClosed);
            cm.Parameters.AddWithValue("@Description", Description ?? 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(string Condition)
    {
        DataTable dt = new DataTable(TableName);

        string sWhere = "";
        if ((Condition != null) && Condition != "") {
            //if (Condition.IndexOf("=") > 0)
            sWhere = " Where " + Condition + " ";
        }

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            //"SELECT * FROM " + TableName + " ORDER BY " + PrimaryKeyColumnNames[0], cn))
            "SELECT [JobNo]" +
            " ,[FABID]" +
            " ,[Tools]" +
            " ,CAST(YEAR([CreateDate]) AS varchar(4)) + '/' + CAST(MONTH([CreateDate]) AS varchar(2)) + '/' + CAST(DAY([CreateDate]) AS varchar(2)) AS CreateDate " +
            " ,[Charge]" +
            " ,[IsClosed]" +
            " ,[Description]" +
            " FROM " + TableName + sWhere + " ORDER BY " + PrimaryKeyColumnNames[0], cn))
        {
            da.Fill(dt);
            return dt;
        }
    }
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable Select(Boolean IncludeClosed) {
        DataTable dt = new DataTable(TableName);

        string sWhere = "";
        if (!IncludeClosed)
            sWhere = " Where IsClosed<>1 ";

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            //"SELECT * FROM " + TableName + " ORDER BY " + PrimaryKeyColumnNames[0], cn))
            "SELECT [JobNo]" +
            " ,[FABID]" +
            " ,[Tools]" +
            " ,CAST(YEAR([CreateDate]) AS varchar(4)) + '/' + CAST(MONTH([CreateDate]) AS varchar(2)) + '/' + CAST(DAY([CreateDate]) AS varchar(2)) AS CreateDate " +
            " ,[Charge]" +
            " ,[IsClosed]" +
            " ,[Description]" +
            " FROM " + TableName + sWhere + " ORDER BY " + PrimaryKeyColumnNames[0], cn)) {
            da.Fill(dt);
            return dt;
        }
    }
    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable SelectUnassigned(string Condition) {
        DataTable dt = new DataTable(TableName);

        string sWhere = "";
        if (Condition != "")
            sWhere = " Where " + Condition;

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT JobNo,Tools FROM " + TableName + sWhere + " ORDER BY " + PrimaryKeyColumnNames[0], cn)) {
            da.Fill(dt);

            string[] arTools;           
            string sWhereTools = "";    //要到 MainWork 比對是否該工番的所有機台都已加入至work時的條件字串

            if (dt.Rows.Count > 0) {
                sWhere = "";            //重新使用該變數. 會記錄全部要找的工番
                for (int i = 0; i < dt.Rows.Count; i++) {
                    sWhereTools = "";
                    arTools = dt.Rows[i]["Tools"].ToString().Replace("\r", "").Split("\n".ToCharArray());
                    for (int j = 0; j < arTools.Length; j++) {
                        sWhereTools += ",'" + arTools[j] + "'";
                    }
                    if (sWhereTools != "") {
                        sWhereTools = sWhereTools.Substring(1);

                        DataTable dtMainWork = new TEC_MainWorksData().Select(
                            "JobNo='" + dt.Rows[i]["JobNo"].ToString() + "' and SES_ToolNo in (" + sWhereTools + ")", "");
                        if (dtMainWork.Rows.Count < arTools.Length)
                            sWhere += ",'" + dt.Rows[i]["JobNo"].ToString() + "'";
                    }
                }

                if (sWhere != "") {
                    // 利用 select function 取得真正所要的資料
                    return Select("JobNo in (" + sWhere.Substring(1) + ")");
                }
                else
                    return null;
            }
            else
                return null;
        }
    }
    [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.Update, true)]
    public void Update(string JobNo, string FABID, string Tools, string CreateDate, string Charge, Boolean IsClosed, string Description, string original_JobNo) {
        if (Tools != null) {
            Tools = Tools.Replace("\r\n", "@"); //直接使用在 split 中會有問題 (多出空白陣列)
            string[] arTemp = Tools.Split("@".ToCharArray());
            Root.pcCommon.ArraySort(ref arTemp);
            Tools = "";
            foreach (string s in arTemp) {
                Tools += "\r\n" + s;
            }
            if (Tools != "") Tools = Tools.Substring(2);
        }

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET [JobNo]=@JobNo, " + 
            " [FABID]=@FABID, " +
            " [Tools]=@Tools, " +
            " [CreateDate]=@CreateDate, " +
            " [Charge]=@Charge, " +
            " [IsClosed]=@IsClosed, " +
            " [Description]=@Description " +
            " Where JobNo=@original_JobNo", cn)) {


            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@JobNo", JobNo.Trim());
            cm.Parameters.AddWithValue("@FABID", FABID == null ? Convert.DBNull : FABID.Trim());
            cm.Parameters.AddWithValue("@Tools", Tools == null ? Convert.DBNull : Tools.Trim());
            cm.Parameters.AddWithValue("@CreateDate", CreateDate);
            cm.Parameters.AddWithValue("@Charge", Charge == null ? Convert.DBNull : Charge.Trim());
            cm.Parameters.AddWithValue("@IsClosed", IsClosed);
            cm.Parameters.AddWithValue("@Description", Description ?? Convert.DBNull);
            SqlParameter parameter = cm.Parameters.AddWithValue("@original_JobNo", original_JobNo);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }

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

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
}
