RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/ICSROUTEDAL.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;
using System.Data.SqlClient;

namespace ICSSoft.Frame.Data.DAL
{
    public class ICSROUTEDAL
    {
        #region 增加和修改List
        public static void AddList(List<ICSROUTE> InfoList, string Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (ICSROUTE info in InfoList)
                {
                    bool isNew = false;
                    var line = db.ICSROUTE.SingleOrDefault(a => a.ROUTECODE == info.ROUTECODE);

                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSROUTE();
                        line.ID = AppConfig.GetGuid();
                    }
                    line.ROUTECODE = info.ROUTECODE;
                    line.ROUTEDESC = info.ROUTEDESC;
                    line.ROUTETYPE = info.ROUTETYPE;
                    //line.EFFDATE = info.EFFDATE;
                    //line.IVLDATE = info.IVLDATE;
                    line.ENABLED = info.ENABLED;
                    line.MUSER = info.MUSER;
                    line.MUSERName = info.MUSERName;
                    line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
                    line.WorkPoint = AppConfig.WorkPointCode;
                    line.EATTRIBUTE1 = null;
                    if (isNew) db.ICSROUTE.InsertOnSubmit(line);

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

        public static string AddList(List<ICSROUTE> RouteLists, List<ICSROUTE2OP> OPLists, string Appconstr)
        {
            string isSuccess = "OK";
            SqlConnection conn = new System.Data.SqlClient.SqlConnection(Appconstr);
            conn.Open();
            SqlTransaction sqlTran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Transaction = sqlTran;
            cmd.Connection = conn;
            try
            {
                string sql = "";
                foreach (ICSROUTE RouteList in RouteLists)
                {
                    sql = @"
IF NOT EXISTS (SELECT * FROM ICSROUTE WHERE 1=1 AND ROUTECODE = '{1}')
BEGIN
DELETE FROM ICSROUTE WHERE 1=1 AND ROUTECODE = '{1}'

UPDATE ICSROUTE2OP SET ROUTEID = '{0}' WHERE ROUTECODE = '{1}' END

INSERT INTO ICSROUTE
(ID, ROUTECODE, ROUTEDESC, ROUTETYPE, ENABLED, MUSER, MUSERName, MTIME, WorkPoint)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}') ";
                    sql = string.Format(sql, RouteList.ID, RouteList.ROUTECODE, RouteList.ROUTEDESC, RouteList.ROUTETYPE, RouteList.ENABLED, RouteList.MUSER, RouteList.MUSERName, AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"), RouteList.WorkPoint);
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    DataTable dt = new DataTable();
                    foreach (ICSROUTE2OP OPList in OPLists)
                    {
                        if (RouteList.ROUTECODE == OPList.ROUTECODE)
                        {
                            sql = @"SELECT * FROM ICSROUTE2OP WHERE 1=1 AND ROUTECODE = '{0}' AND OPCODE = '{1}' AND WorkPoint = '{2}' ";
                            sql = string.Format(sql, OPList.ROUTECODE, OPList.OPCODE, OPList.WORKPOINT);
                            dt = SQlReturnData(sql, cmd);
                            if (dt.Rows.Count > 0 && dt != null)
                            {
                                cmd.Transaction.Rollback();
                                return "途程代码:" + OPList.ROUTECODE + "下,工序代码" + OPList.OPCODE + "已经存在,请确认";
                            }
                            else
                            {
                                sql = @"SELECT * FROM ICSROUTE2OP WHERE 1=1 AND ROUTECODE = '{0}' AND OPSEQ = '{1}' AND WorkPoint = '{2}' ";
                                sql = string.Format(sql, OPList.ROUTECODE, OPList.OPSEQ, OPList.WORKPOINT);
                                dt = SQlReturnData(sql, cmd);
                                if (dt.Rows.Count > 0 && dt != null)
                                {
                                    cmd.Transaction.Rollback();
                                    return "途程代码:" + OPList.ROUTECODE + "下,工序次序" + OPList.OPSEQ + "已经存在,请确认";
                                }
                                else
                                {
                                    sql = @"
IF EXISTS (SELECT * FROM ICSROUTE2OP WHERE 1=1 AND ROUTECODE = '{2}' AND OPCODE = '{3}')
BEGIN DELETE FROM ICSROUTE2OP WHERE 1=1 AND ROUTECODE = '{2}' AND OPCODE = '{3}' AND OPSEQ = '{4}' END

INSERT INTO ICSROUTE2OP
(ROUTEID, OPID, ROUTECODE, OPCODE, OPSEQ, OPCONTROL, MUSER, MUSERName, MTIME, WORKPOINT)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}') ";
                                    sql = string.Format(sql, OPList.ROUTEID, OPList.OPID, OPList.ROUTECODE, OPList.OPCODE, OPList.OPSEQ, OPList.OPCONTROL, OPList.MUSER, OPList.MUSERName, AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"), OPList.WORKPOINT);
                                    cmd.CommandText = sql;
                                    cmd.ExecuteNonQuery();
                                }
                            }
                        }
                    }
                }
                cmd.Transaction.Commit();
                return isSuccess;
            }
            catch (Exception ex)
            {
                cmd.Transaction.Rollback();
                return ex.Message;
            }
        }
        #endregion

        #region 增加和修改
        public static void AddandEdit(ICSROUTE routeLot, string Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                bool isNew = false;
                var line = db.ICSROUTE.SingleOrDefault(a => a.ID == routeLot.ID);

                if (line == null)
                {
                    isNew = true;
                    line = new ICSROUTE();
                    line.ID = AppConfig.GetGuid();
                }

                var codes = db.ICSROUTE.Where(a => a.ROUTECODE == routeLot.ROUTECODE && a.ID != line.ID);
                if (codes.ToList().Count > 0)
                {
                    throw new Exception("途程代码已存在");
                }

                line.ROUTECODE = routeLot.ROUTECODE;
                line.ROUTEDESC = routeLot.ROUTEDESC;
                line.ROUTETYPE = routeLot.ROUTETYPE;
                line.EFFDATE = routeLot.EFFDATE;
                line.IVLDATE = routeLot.IVLDATE;
                line.ENABLED = routeLot.ENABLED;
                line.MUSER = routeLot.MUSER;
                line.MUSERName = routeLot.MUSERName;
                line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd hh:mm:ss");
                line.WorkPoint = AppConfig.WorkPointCode;
                line.EATTRIBUTE1 = null;
                if (isNew) db.ICSROUTE.InsertOnSubmit(line);

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

        #region 根据id查询途程信息
        public static ICSROUTE select(String guid, String Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            ICSROUTE entity = new ICSROUTE();
            try
            {
                var line = db.ICSROUTE.SingleOrDefault(a => a.ID == guid);
                return (ICSROUTE)line;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static ICSROUTE search(string ROUTECODE, String Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            ICSROUTE entity = new ICSROUTE();
            try
            {
                var line = db.ICSROUTE.SingleOrDefault(a => a.ROUTECODE == ROUTECODE);
                return (ICSROUTE)line;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static ICSROUTE2OP select(string ROUTECODE, string OPCODE, string Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSROUTE2OP.SingleOrDefault(a => a.ROUTECODE == ROUTECODE && a.OPCODE == OPCODE);
                return (ICSROUTE2OP)line;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static ICSROUTE2OP search(string ROUTECODE, int OPSEQ, string Appconstr)
        {
            FramDataContext db = new FramDataContext(Appconstr);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSROUTE2OP.SingleOrDefault(a => a.ROUTECODE == ROUTECODE && a.OPSEQ == OPSEQ);
                return (ICSROUTE2OP)line;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 删除
        public static void delete(List<String> guidList, List<string> codeList)
        {
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSROUTE.Where(a => guidList.Contains(a.ID));
                //var line = db.ICSRES.Where(a => codeList.Contains(a.DCTCODE));
                //if(line.Count()!=0){
                //    throw new Exception("DCT指令在资源维护已经使用,无法删除!");
                //}
                db.ICSROUTE.DeleteAllOnSubmit(lines);

                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
        }
        #endregion

        #region 判断途程和工序的关系维护中是否已使用该途程
        public static bool isIncludingInICSROUTE2OP(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.ROUTEID));
                if (lines.Count() != 0)
                    flag = true;
                return flag;
            }
            catch (Exception ex)
            {
                //db.Transaction.Rollback();
                throw ex;
            }
        }

        public static List<ICSROUTE2OP> IncludingInICSROUTE2OP(List<string> idList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                List<ICSROUTE2OP> lines = db.ICSROUTE2OP.Where(a => idList.Contains(a.ROUTEID)).ToList();
                return lines;
            }
            catch (Exception ex)
            {
                //db.Transaction.Rollback();
                throw ex;
            }
        }
        #endregion

        #region 判断产品和途程的关系维护中是否已使用该途程
        public static bool isIncludingInICSITEM2ROUTE(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.ICSITEM2ROUTE.Where(a => idList.Contains(a.ROUTEID));
                if (lines.Count() != 0)
                    flag = true;
                return flag;
            }
            catch (Exception ex)
            {
                //db.Transaction.Rollback();
                throw ex;
            }
        }
        public static List<ICSITEM2ROUTE> IncludingInICSITEM2ROUTE(List<string> idList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            try
            {
                List<ICSITEM2ROUTE> lines = db.ICSITEM2ROUTE.Where(a => idList.Contains(a.ROUTEID)).ToList();
                return lines;
            }
            catch (Exception ex)
            {
                //db.Transaction.Rollback();
                throw ex;
            }
        }
        #endregion

        #region 判断产品、途程和工序的关系维护中是否已使用该途程
        public static bool isIncludingInICSITEMROUTE2OP(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.ICSITEMROUTE2OP.Where(a => idList.Contains(a.ROUTEID));
                if (lines.Count() != 0)
                    flag = true;
                return flag;
            }
            catch (Exception ex)
            {
                //db.Transaction.Rollback();
                throw ex;
            }
        }

        public static List<ICSITEMROUTE2OP> IncludingInICSITEMROUTE2OP(List<string> idList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            try
            {
                List<ICSITEMROUTE2OP> lines = db.ICSITEMROUTE2OP.Where(a => idList.Contains(a.ROUTEID)).ToList();
                return lines;
            }
            catch (Exception ex)
            {
                //db.Transaction.Rollback();
                throw ex;
            }
        }
        #endregion

        #region 获取工序信息
        public static DataTable GetOPCODE(string OPCODE, string WorkPoint, string dsconn)
        {
            string sql = @"SELECT ID, OPCODE, OPDESC, OPCONTROL FROM ICSOP WHERE 1=1 AND OPCODE = '{0}' AND WorkPoint = '{1}' ";
            sql = string.Format(sql, OPCODE, WorkPoint);
            DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
            return dt;
        }
        #endregion

        public static DataTable SQlReturnData(string SQL, SqlCommand cmd)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter dr = new System.Data.SqlClient.SqlDataAdapter();
            cmd.CommandText = SQL;
            dr.SelectCommand = cmd;
            dr.Fill(dt);
            return dt;
        }


    }
}