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

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

    [DataObjectMethod(DataObjectMethodType.Insert, true)]
    public void Insert(string FABID, string Abbreviation, string CompanyID, string CompanyName, string Address, string Tel)
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "INSERT INTO " + TableName +
            " ([FABID],[Abbreviation],[CompanyID],[CompanyName],[Address],[Tel]) " +
            " VALUES " +
            " (@FABID, @Abbreviation, @CompanyID, @CompanyName, @Address, @Tel)", cn))
        {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@FABID", FABID.Trim() );
            cm.Parameters.AddWithValue("@Abbreviation", Abbreviation.Trim());
            cm.Parameters.AddWithValue("@CompanyID", CompanyID.Trim());
            cm.Parameters.AddWithValue("@CompanyName", CompanyName == null ? Convert.DBNull : CompanyName.Trim() );
            cm.Parameters.AddWithValue("@Address", Address == null ? Convert.DBNull : Address.Trim());
            cm.Parameters.AddWithValue("@Tel", Tel ?? 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))
        {
            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;
        }
    }

    public string[] SelectFABs(string JobNo) {
        DataTable dt = new DataTable(TableName);

        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlDataAdapter da = new SqlDataAdapter(
            "SELECT FABID FROM TEC_JobNo Where JobNo='" + JobNo + "' order by FABID", cn)) {
            da.Fill(dt);
            string[] arRtn = null;
            if (dt.Rows.Count == 1){
                arRtn = dt.Rows[0]["FABID"].ToString().Replace("\r", "").Split('\n');
            }

            return arRtn;
        }
    }

    
    [DataObjectMethod(DataObjectMethodType.Update, true)]
    public void Update(string FABID, string Abbreviation, string CompanyID, string CompanyName, string Address, string Tel, string original_FABID)
    {
        using (SqlConnection cn = new SqlConnection(DataConnection))
        using (SqlCommand cm = new SqlCommand(
            "UPDATE " + TableName +
            " SET [FABID]=@FABID, [Abbreviation]=@Abbreviation, [CompanyID]=@CompanyID, [CompanyName]=@CompanyName, [Address]=@Address, [Tel]=@Tel " +
            " Where FABID=@original_FABID", cn))
        {
            cn.Open();

            cm.CommandType = CommandType.Text;
            cm.Parameters.AddWithValue("@FABID", FABID.Trim());
            cm.Parameters.AddWithValue("@Abbreviation", Abbreviation.Trim());
            cm.Parameters.AddWithValue("@CompanyID", CompanyID.Trim());
            cm.Parameters.AddWithValue("@CompanyName", CompanyName == null ? Convert.DBNull : CompanyName.Trim());
            cm.Parameters.AddWithValue("@Address", Address == null ? Convert.DBNull : Address.Trim());
            cm.Parameters.AddWithValue("@Tel", Tel ?? Convert.DBNull);
            SqlParameter parameter = cm.Parameters.AddWithValue("@original_FABID", original_FABID);
            parameter.SourceVersion = DataRowVersion.Original;

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }

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

            cm.ExecuteNonQuery();

            cn.Close();
        }
    }
}
