RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/ICSOPDAL.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 ICSSoft.Base.Config.DBHelper;

namespace ICSSoft.Frame.Data.DAL
{
    public class ICSOPDAL
    {
        #region 新增和修改
        public static void Add(List<FormICSOPUIModel> tbInfoList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (FormICSOPUIModel tbinfo in tbInfoList)
                {
                    bool isNew = false;
                    var line = db.ICSOP.SingleOrDefault(a => a.OPCODE == tbinfo.OPCODE && a.WorkPoint == AppConfig.WorkPointCode);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSOP();
                        line.ID = AppConfig.GetGuid();
                        line.OPCODE = tbinfo.OPCODE;
                    }
                    line.OPDESC = tbinfo.OPDESC;
                    line.OPCOLLECTION = tbinfo.OPCOLLECTION;
                    line.OPCONTROL = tbinfo.OPCONTROL;
                    line.OPNAME = tbinfo.OPNAME;
                    line.CostCenter = tbinfo.CostCenter;
                    //line.OpFrom = tbinfo.OpFrom;
                    line.EnableFlag = tbinfo.EnableFlag;
                    line.EATTRIBUTE1 = tbinfo.EATTRIBUTE1;
                    line.MUSER = AppConfig.UserId;
                    line.MUSERName = AppConfig.UserName;
                    line.MTIME = DateTime.Now;
                    line.WorkPoint = AppConfig.WorkPointCode;

                    if (isNew)
                        db.ICSOP.InsertOnSubmit(line);
                    db.SubmitChanges();
                }
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
        }
        #endregion
        #region 工序代码是否存在
        public static bool IsIncluding(string opCODE, string dsconn)
        {

            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSOP.SingleOrDefault(a => a.OPCODE == opCODE);
                if (line == null)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }

        }
        #endregion
        #region 查询工序信息
        public static List<FormICSOPUIModel> SearchOPInfo(string opcode, string dsconn)
        {
            List<FormICSOPUIModel> returnOP = new List<FormICSOPUIModel>();
            string sql = @"select OPCODE,OPDESC,OPNAME,OPCOLLECTION,OPCONTROL,CostCenter,EATTRIBUTE1,EnableFlag from ICSOP where OPCODE='{0}'";
            sql = string.Format(sql, opcode);
            DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                FormICSOPUIModel returnInfo = new FormICSOPUIModel();
                returnInfo.OPCODE = dr["OPCODE"].ToString();
                returnInfo.OPDESC = dr["OPDESC"].ToString();
                returnInfo.OPNAME = dr["OPNAME"].ToString();
                returnInfo.OPCOLLECTION = dr["OPCOLLECTION"].ToString();
                returnInfo.OPCONTROL = dr["OPCONTROL"].ToString();
                returnInfo.CostCenter = dr["CostCenter"].ToString();
                returnInfo.EATTRIBUTE1 = dr["EATTRIBUTE1"].ToString();
                returnInfo.EnableFlag = Convert.ToBoolean(dr["EnableFlag"]);
                returnOP.Add(returnInfo);
            }
            return returnOP;
        }
        #endregion
        #region 删除
        public static void deleteInfo(List<string> codeList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSOP.Where(a => codeList.Contains(a.OPCODE));
                db.ICSOP.DeleteAllOnSubmit(lines);
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
        }
        #endregion
        #region 判断是否在工序和资源的关系中
        public static bool Including2RES(List<string> idList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                bool flag = false;
                var lines = db.ICSOP2RES.Where(a => idList.Contains(a.OPID));
                if (lines.Count() != 0)
                    flag = true;

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

        }
        #endregion
        #region 判断是否在途程和工序的关系中
        public static bool IncludingROUTE2OP(List<string> idList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                bool flag = false;
                var lines = db.ICSROUTE2OP.Where(a => idList.Contains(a.OPID));
                if (lines.Count() != 0)
                    flag = true;

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

        }

        public static List<ICSROUTE2OP> GetROUTE2OP(List<string> idList, string wp, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            try
            {
                List<ICSROUTE2OP> lines = db.ICSROUTE2OP.Where(a => idList.Contains(a.OPID) && a.WORKPOINT == wp).ToList();
                db.Connection.Close();
                return lines;
            }
            catch (Exception ex)
            {
                //db.Transaction.Rollback();
                throw ex;
            }
        }

        public static DataTable GetITEMROUTE2OP(List<string> idList, string wp, string dsconn)
        {
            string sql = "SELECT DISTINCT OPCODE,ROUTECODE,ITEMCODE FROM dbo.ICSITEMROUTE2OP WHERE OPID in ('{0}') AND WorkPoint='{1}'";
            sql = string.Format(sql, string.Join("','", idList), wp);
            return DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
        }
        #endregion
        #region 获取成本中心

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

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

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