RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/ICSOQCCKLISTDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ICSSoft.Frame.Data.Entity;
using ICSSoft.Base.Config.AppConfig;
using System.Data;
using System.Data.Sql;
using System.Data.Linq;
using ICSSoft.Base.Config.DBHelper;

namespace ICSSoft.Frame.Data.DAL
{
    public class ICSOQCCKLISTDAL
    {
        public static void AddandEdit(ICSOQCCKLIST MODEL, string Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                bool isNew = false;
                string b = MODEL.ID;
                var line = db.ICSOQCCKLIST.SingleOrDefault(a => a.ID == MODEL.ID && a.WorkPoint==MODEL.WorkPoint);

                var codes = db.ICSOQCCKLIST.Where(a => a.INVCode == MODEL.INVCode && a.OPCode == MODEL.OPCode && a.CKGROUPCode == MODEL.CKGROUPCode &&
                     a.CKGROUPMETH == MODEL.CKGROUPMETH && a.CKGROUPATTR == MODEL.CKGROUPATTR && a.WorkPoint == MODEL.WorkPoint && a.ID != MODEL.ID);
                if (codes.ToList().Count > 0)
                {
                    throw new Exception("维护信息已存在！");
                }

                if (line == null)
                {
                    isNew = true;
                    line = new ICSOQCCKLIST();
                    line.ID = MODEL.ID;
                }
                line.ID = MODEL.ID;
                line.INVCode = MODEL.INVCode;
                line.OPCode = MODEL.OPCode;
                line.CKGROUPCode = MODEL.CKGROUPCode;
                line.CKGROUPMETH = MODEL.CKGROUPMETH;
                line.CKGROUPATTR = MODEL.CKGROUPATTR;
                line.UNIT = MODEL.UNIT;
                line.Tool = MODEL.Tool;
                line.SetValue = MODEL.SetValue;
                line.SetValueMax = MODEL.SetValueMax;
                line.SetValueMin = MODEL.SetValueMin;
                line.ISREF = MODEL.ISREF;
                line.MUSER = MODEL.MUSER;
                line.MUSERNAME = MODEL.MUSERNAME;
                line.MTIME = MODEL.MTIME;
                line.WorkPoint = AppConfig.WorkPointCode;
                line.EATTRIBUTE1 = MODEL.EATTRIBUTE1;
                if (isNew)
                    db.ICSOQCCKLIST.InsertOnSubmit(line);

                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }

        public static void AddandEditList(List<ICSOQCCKLIST> InfoList, string Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (ICSOQCCKLIST info in InfoList)
                {
                    bool isNew = false;
                    var line = db.ICSOQCCKLIST.SingleOrDefault(a => a.INVCode == info.INVCode && a.OPCode == info.OPCode && a.CKGROUPCode == info.CKGROUPCode &&
                         a.CKGROUPMETH == info.CKGROUPMETH && a.CKGROUPATTR == info.CKGROUPATTR && a.WorkPoint == info.WorkPoint);

                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSOQCCKLIST();
                        line.ID = info.ID;
                    }
                    line.INVCode = info.INVCode;
                    line.OPCode = info.OPCode;
                    line.CKGROUPCode = info.CKGROUPCode;
                    line.CKGROUPMETH = info.CKGROUPMETH;
                    line.CKGROUPATTR = info.CKGROUPATTR;
                    line.UNIT = info.UNIT;
                    line.Tool = info.Tool;
                    line.SetValue = info.SetValue;
                    line.SetValueMax = info.SetValueMax;
                    line.SetValueMin = info.SetValueMin;
                    line.ISREF = info.ISREF;
                    line.EATTRIBUTE1 = info.EATTRIBUTE1;
                    line.MUSER = info.MUSER;
                    line.MUSERNAME = info.MUSERNAME;
                    line.MTIME = info.MTIME;
                    line.WorkPoint = info.WorkPoint;

                    if (isNew)
                        db.ICSOQCCKLIST.InsertOnSubmit(line);
                }
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }

        public static void delete(List<String> guidList)
        {
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSOQCCKLIST.Where(a => guidList.Contains(a.ID) && a.WorkPoint == AppConfig.WorkPointCode);
                db.ICSOQCCKLIST.DeleteAllOnSubmit(lines);

                db.SubmitChanges();
                db.Transaction.Commit();

            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
        }

        #region 获取成检验方式
        public static DataTable SearchData(string dsconn)
        {
            try
            {
                string sql = @"SELECT EnumKey, EnumText FROM Sys_EnumValues WHERE EnumKey = '00015' AND WorkPointCode = '{0}' ";
                sql = string.Format(sql, AppConfig.WorkPointCode);
                DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable SearchData(string EnumText, string dsconn)
        {
            try
            {
                string sql = @"SELECT EnumKey, EnumText FROM Sys_EnumValues WHERE EnumKey = '00015' AND EnumText = '{0}' AND WorkPointCode = '{1}' ";
                sql = string.Format(sql, EnumText, AppConfig.WorkPointCode);
                DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获取成检验属性
        public static DataTable SearchDataa(string dsconn)
        {
            try
            {
                string sql = @"SELECT EnumText as 检验属性 FROM Sys_EnumValues WHERE EnumKey = '00016' AND WorkPointCode = '{0}' ";
                sql = string.Format(sql, AppConfig.WorkPointCode);
                DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable SearchDataa(string EnumText, string dsconn)
        {
            try
            {
                string sql = @"SELECT EnumText as 检验属性 FROM Sys_EnumValues WHERE EnumKey = '00016' AND EnumText = '{0}' AND WorkPointCode = '{1}' ";
                sql = string.Format(sql, EnumText, AppConfig.WorkPointCode);
                DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 检验工具
        public static DataTable SearchDataTool(string dsconn)
        {
            try
            {
                string sql = @"
SELECT EnumText AS 中文名,Define1 AS 英文名,Define2 AS 缩写,Define3 AS 检验属性 FROM Sys_EnumValues 
WHERE EnumKey = '00017' AND WorkPointCode = '{0}'  AND ISNULL(EnumText,'')<>'' AND ISNULL(Define1,'')<>''
ORDER BY EnumText asc ";
                sql = string.Format(sql, AppConfig.WorkPointCode);
                DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 单位
        public static DataTable SearchDataUNIT(string dsconn)
        {
            try
            {
                string sql = @"SELECT EnumKey, EnumText FROM Sys_EnumValues WHERE EnumKey = '00200' AND WorkPointCode = '{0}' order by EnumKey";
                sql = string.Format(sql, AppConfig.WorkPointCode);
                DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        public static DataSet SearchImportData(string dsconn)
        {
            try
            {
                string sql = @"SELECT INVCODE FROM ICSINVENTORY WHERE WorkPoint = '{0}'
                            SELECT OPCODE FROM ICSOP WHERE WorkPoint = '{0}'
                            SELECT CKGROUP FROM ICSOQCCKGROUP WHERE WorkPoint = '{0}'
                            SELECT EnumKey, EnumText FROM Sys_EnumValues WHERE EnumKey = '00015' AND WorkPointCode = '{0}'
                            SELECT EnumKey, EnumText FROM Sys_EnumValues WHERE EnumKey = '00016' AND WorkPointCode = '{0}'
                            SELECT EnumKey, EnumText FROM Sys_EnumValues WHERE EnumKey = '00017' AND WorkPointCode = '{0}'";
                sql = string.Format(sql, AppConfig.WorkPointCode);
                DataSet ds = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }
}