Moka_CShare_FactoryTool/FactoryTool_CShare/DAL/DAL_AMResult.cs

using MOKA_Factory_Tools.Database;
using MOKA_Factory_Tools.Models;
using SXLibrary;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MOKA_Factory_Tools.DAL
{
    public class DAL_AMResult : BaseDAL
    {
        public DAL_AMResult(int NTFposion)
        {
            //Provider=SqlOLEDB;Data Source=10.146.0.52;initial catalog=AMAZON;user ID=amazon;password=Volvo120;connect timeout=30;
            this.TableFieldNameString = "";
            if (NTFposion == 1)
                this.cps.SetConnectParameters(databaseServer:"10.138.96.109", databaseName: "IDOtherData", databaseAccount: "usr_rd", databasePassword: "Pa55W0rd4TMSA@202207#");
            else if(NTFposion == 2)
                this.cps.SetConnectParameters(databaseServer: "10.146.0.52", databaseName: "AMAZON", databaseAccount: "amazon", databasePassword: "Volvo120");

        }


        #region 属性和字段

        string _tableName = "AMResult";

        /// <summary>
        /// 获取数据表名
        /// </summary>
        public override string TableName
        {
            get { return _tableName; }
            set { this._tableName = value; }
        }

        /// <summary>
        /// 获取当前新的数据表模型对象
        /// </summary>
        public override object ObjModel
        {
            get
            {
                return this.CurrentModel;
            }
        }

        /// <summary>
        /// 获取当前新的MOdel
        /// </summary>
        public AMResult CurrentModel
        {
            get { return new AMResult(); }
        }


        string _tableFieldNameString = "";
        /// <summary>
        /// 数据表字段名数组
        /// </summary>
        public override string TableFieldNameString
        {
            get { return this._tableFieldNameString; }

            set { this._tableFieldNameString = value; }
        }

        #endregion

        #region 检查记录
        //基类已经实现
        #endregion

        #region 增加数据
        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <returns></returns>
        public bool Add(AMResult model)
        {
            return base.Add(model);
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <param name="overlookFieldList">忽略字段名列表 字段名之间用“,”号分隔</param>
        /// <returns></returns>
        public bool Add(AMResult model, string overlookFieldList = "ID")
        {
            return base.Add(model, overlookFieldList);
        }

        #endregion

        #region 删除数据
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Delete(AMResult model)
        {
            return base.Delete(string.Format("Station = '{0}' and SN = '{1}' and ReDo = '{2}'", model.Station, model.SN, model.ReDo));
        }
        #endregion

        #region 更新数据
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(AMResult model)
        {
            return base.Update(model);
        }

        /// <summary>
        /// 根据筛选字段和SQL筛选运算符号更新数据
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <param name="filterFieldName">筛选字段名称</param>
        /// <param name="operators">SQL筛选运算符号</param>
        /// <param name="overlookFieldList">忽略字段名列表 字段名之间用“,”号分隔</param>
        /// <returns></returns>
        public bool Update(AMResult model, string strWhere, string IgnoreUpdateFields = "Station,SN,ODF,DSN,Line,Model,Dimension")
        {
            return base.UpdateAMResult(model, strWhere, IgnoreUpdateFields);
        }
        #endregion

        #region 查询数据
        /// <summary>
        /// 
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public AMResult GetModel(string strWhere)
        {
            return DataRowToModel(GetDataRow(strWhere));
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public AMResult GetModel(string strStation, string strSN)
        {
            return DataRowToModel(GetDataRow(string.Format("Station = '{0}' and SN = '{1}'", strStation, strSN)));
        }

        public HourlyResultData GetHourlyResultData(string Line, string Station, string ODF, DateTime TestDate, int TestHour)
        {
            DataRow row = GetHourlyDataRow(string.Format("Line='{0}' and Station='{1}' and ODF='{2}' and TestDate='{3}' and TestHour='{4}'", Line, Station, ODF, TestDate, TestHour));
            if ( row != null )
            {
                HourlyResultData model = new HourlyResultData();
                model.TotalAllCount = row["TotalAllCount"].ToString();
                model.TotalZeroCount = row["TotalZeroCount"].ToString();
                model.OncePassAllCount = row["OncePassAllCount"].ToString();
                model.OncePassZeroCount = row["OncePassZeroCount"].ToString();
                model.NTFAllCount = row["NTFAllCount"].ToString();
                model.NTFZeroCount = row["NTFZeroCount"].ToString();
                model.RealFailCount = row["RealFailCount"].ToString();
                model.NTFSN = row["NTFSN"].ToString();
                model.FailDSN = row["FailDSN"].ToString();

                return model;
            }

            return null;
        }

        public DataRow GetHourlyDataRow(string whereString)
        {
            if (whereString.Trim().Length > 0)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select ");
                // TotalAllCount
                strSql.Append("count(1) as 'TotalAllCount',");
                // TotalZeroCount
                strSql.Append("count(case when ReDo = '0' then 1 else null end) as 'TotalZeroCount',");
                // OncePassAllCount
                strSql.Append("count(case when ResultType = 'OncePass' then 1 else null end) as 'OncePassAllCount',");
                // OncePassZeroCount
                strSql.Append("count(case when ResultType = 'OncePass' and ReDo = '0' then 1 else null end) as 'OncePassZeroCount',");
                // NTFAllCount
                strSql.Append("count(case when ResultType = 'NTF' then 1 else null end) as 'NTFAllCount',");
                // NTFZeroCount
                strSql.Append("count(case when ResultType = 'NTF' and ReDo = '0' then 1 else null end) as 'NTFZeroCount',");
                // RealFailCount
                strSql.Append("count(case when ResultType = 'RealFail' then 1 else null end) as 'RealFailCount',");
                // NTF的SN集合;
                strSql.Append(string.Format("stuff((select ',' + SN from {0} where {1} and ResultType='NTF' for XML PATH('')),1,1,'') as 'NTFSN',", this.TableName, whereString));
                // FinalTest=Fail的DSN集合;
                strSql.Append(string.Format("stuff((select ',' + DSN from {0} where {1} and FinalTest='FAIL' for XML PATH('')),1,1,'') as 'FailDSN' ", this.TableName, whereString));
                strSql.Append("from " + this.TableName);
                strSql.Append(" where  " + whereString);

                Log.WriteInfoLog(strSql.ToString());
                DataSet ds = DbHelper.Query(cps.ConnectionString, strSql.ToString());
                if (ds.Tables[0].Rows.Count > 0)
                {
                    return ds.Tables[0].Rows[0];
                }
            }

            return null;            
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public AMResult DataRowToModel(DataRow row)
        {
            return DataRowToModelObject(row) as AMResult;
        }

        /// <summary>
        /// 获得数据Model列表
        /// </summary>
        /// <param name="strWhere">条件 不包含 where 关键字</param>
        public List<AMResult> GetModelList(string strWhere)
        {
            DataSet ds = base.GetList(strWhere);
            return DataTableToList(ds.Tables[0]);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strLine"></param>
        /// <param name="strStation"></param>
        /// <param name="ODF"></param>
        /// <param name="TestDate"></param>
        /// <param name="TestHour"></param>
        /// <returns></returns>
        public List<AMResult> GetModelList(string strLine, string strStation, string ODF, DateTime TestDate, int TestHour)
        {
            DataSet ds = base.GetList(string.Format("Line = '{0}' and Station = '{1}' and ODF='{2}' and TestDate='{3}' and TestHour='{4}'", strLine, strStation, ODF, TestDate, TestHour));
            return DataTableToList(ds.Tables[0]);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="dt">DataTable</param>
        public List<AMResult> DataTableToList(DataTable dt)
        {
            List<AMResult> modelList = new List<AMResult>();
            List<object> ObjList = base.GetDataTableToOblList(dt);
            foreach (object obj in ObjList)
            {
                modelList.Add((AMResult)obj);
            }
            return modelList;
        }
        #endregion
    }

    public class DAL_AMResult_TR : BaseDAL
    {
        public DAL_AMResult_TR(int NTFposion)
        {
            this.TableFieldNameString = "";           
            if (NTFposion == 1)
                this.cps.SetConnectParameters(databaseServer: "10.138.96.109", databaseName: "IDOtherData", databaseAccount: "usr_rd", databasePassword: "Pa55W0rd4TMSA@202207#");
            else if (NTFposion == 2)
                this.cps.SetConnectParameters(databaseServer: "10.146.0.52", databaseName: "AMAZON", databaseAccount: "amazon", databasePassword: "Volvo120");
        }


        #region 属性和字段

        string _tableName = "AMResult_TR";

        /// <summary>
        /// 获取数据表名
        /// </summary>
        public override string TableName
        {
            get { return _tableName; }
            set { this._tableName = value; }
        }

        /// <summary>
        /// 获取当前新的数据表模型对象
        /// </summary>
        public override object ObjModel
        {
            get
            {
                return this.CurrentModel;
            }
        }

        /// <summary>
        /// 获取当前新的MOdel
        /// </summary>
        public AMResult_TR CurrentModel
        {
            get { return new AMResult_TR(); }
        }


        string _tableFieldNameString = "";
        /// <summary>
        /// 数据表字段名数组
        /// </summary>
        public override string TableFieldNameString
        {
            get { return this._tableFieldNameString; }

            set { this._tableFieldNameString = value; }
        }

        #endregion

        #region 检查记录
        //基类已经实现
        #endregion

        #region 增加数据
        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <returns></returns>
        public bool Add(AMResult_TR model)
        {
            return base.Add(model);
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <param name="overlookFieldList">忽略字段名列表 字段名之间用“,”号分隔</param>
        /// <returns></returns>
        public bool Add(AMResult_TR model, string overlookFieldList = "ID")
        {
            return base.Add(model, overlookFieldList);
        }

        #endregion

        #region 删除数据
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Delete(AMResult_TR model)
        {
            return base.Delete(string.Format("Station = '{0}' and SN = '{1}' and ReDo = '{2}'", model.Station, model.SN, model.ReDo));
        }
        #endregion

        #region 更新数据
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(AMResult_TR model)
        {
            return base.Update(model);
        }

        /// <summary>
        /// 根据筛选字段和SQL筛选运算符号更新数据
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <param name="filterFieldName">筛选字段名称</param>
        /// <param name="operators">SQL筛选运算符号</param>
        /// <param name="overlookFieldList">忽略字段名列表 字段名之间用“,”号分隔</param>
        /// <returns></returns>
        public bool Update(AMResult_TR model, string strWhere, string IgnoreUpdateFields = "Station,SN,ODF,DSN,Line,Model,Dimension")
        {
            return base.UpdateAMResult(model, strWhere, IgnoreUpdateFields);
        }
        #endregion

        #region 查询数据
        /// <summary>
        /// 
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public AMResult_TR GetModel(string strWhere)
        {
            return DataRowToModel(GetDataRow(strWhere));
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public AMResult_TR GetModel(string strStation, string strSN)
        {
            return DataRowToModel(GetDataRow(string.Format("Station = '{0}' and SN = '{1}'", strStation, strSN)));
        }

        public HourlyResultTRData GetHourlyResultData(string Line, string Station, string ODF, DateTime TestDate, int TestHour)
        {
            DataRow row = GetHourlyDataRow(string.Format("Line='{0}' and Station='{1}' and ODF='{2}' and TestDate='{3}' and TestHour='{4}'", Line, Station, ODF, TestDate, TestHour));
            if (row != null)
            {
                HourlyResultTRData model = new HourlyResultTRData();
                model.TotalAllCount = row["TotalAllCount"].ToString();
                model.TotalZeroCount = row["TotalZeroCount"].ToString();
                model.OncePassAllCount = row["OncePassAllCount"].ToString();
                model.OncePassZeroCount = row["OncePassZeroCount"].ToString();
                model.NTFAllCount = row["NTFAllCount"].ToString();
                model.NTFZeroCount = row["NTFZeroCount"].ToString();
                model.RealFailCount = row["RealFailCount"].ToString();
                model.NTFSN = row["NTFSN"].ToString();
                model.FailDSN = row["FailDSN"].ToString();

                return model;
            }

            return null;
        }

        public DataRow GetHourlyDataRow(string whereString)
        {
            if (whereString.Trim().Length > 0)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select ");
                // TotalAllCount
                strSql.Append("count(1) as 'TotalAllCount',");
                // TotalZeroCount
                strSql.Append("count(case when ReDo = '0' then 1 else null end) as 'TotalZeroCount',");
                // OncePassAllCount
                strSql.Append("count(case when ResultType = 'OncePass' then 1 else null end) as 'OncePassAllCount',");
                // OncePassZeroCount
                strSql.Append("count(case when ResultType = 'OncePass' and ReDo = '0' then 1 else null end) as 'OncePassZeroCount',");
                // NTFAllCount
                strSql.Append("count(case when ResultType = 'NTF' then 1 else null end) as 'NTFAllCount',");
                // NTFZeroCount
                strSql.Append("count(case when ResultType = 'NTF' and ReDo = '0' then 1 else null end) as 'NTFZeroCount',");
                // RealFailCount
                strSql.Append("count(case when ResultType = 'RealFail' then 1 else null end) as 'RealFailCount',");
                // NTF的SN集合;
                strSql.Append(string.Format("stuff((select ',' + SN from {0} where {1} and ResultType='NTF' for XML PATH('')),1,1,'') as 'NTFSN',", this.TableName, whereString));
                // FinalTest=Fail的DSN集合;
                strSql.Append(string.Format("stuff((select ',' + DSN from {0} where {1} and FinalTest='FAIL' for XML PATH('')),1,1,'') as 'FailDSN' ", this.TableName, whereString));
                strSql.Append("from " + this.TableName);
                strSql.Append(" where  " + whereString);

                Log.WriteInfoLog(strSql.ToString());
                DataSet ds = DbHelper.Query(cps.ConnectionString, strSql.ToString());
                if (ds.Tables[0].Rows.Count > 0)
                {
                    return ds.Tables[0].Rows[0];
                }
            }

            return null;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public AMResult_TR DataRowToModel(DataRow row)
        {
            return DataRowToModelObject(row) as AMResult_TR;
        }

        /// <summary>
        /// 获得数据Model列表
        /// </summary>
        /// <param name="strWhere">条件 不包含 where 关键字</param>
        public List<AMResult_TR> GetModelList(string strWhere)
        {
            DataSet ds = base.GetList(strWhere);
            return DataTableToList(ds.Tables[0]);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strLine"></param>
        /// <param name="strStation"></param>
        /// <param name="ODF"></param>
        /// <param name="TestDate"></param>
        /// <param name="TestHour"></param>
        /// <returns></returns>
        public List<AMResult_TR> GetModelList(string strLine, string strStation, string ODF, DateTime TestDate, int TestHour)
        {
            DataSet ds = base.GetList(string.Format("Line = '{0}' and Station = '{1}' and ODF='{2}' and TestDate='{3}' and TestHour='{4}'", strLine, strStation, ODF, TestDate, TestHour));
            return DataTableToList(ds.Tables[0]);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="dt">DataTable</param>
        public List<AMResult_TR> DataTableToList(DataTable dt)
        {
            List<AMResult_TR> modelList = new List<AMResult_TR>();
            List<object> ObjList = base.GetDataTableToOblList(dt);
            foreach (object obj in ObjList)
            {
                modelList.Add((AMResult_TR)obj);
            }
            return modelList;
        }
        #endregion
    }
}