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

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

namespace ICSSoft.Frame.Data.DAL
{
    public class ICSEQPSTPDAL
    {
        #region 新增和修改
        public static void Add(List<ICSEQPSTP> InfoList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (ICSEQPSTP info in InfoList)
                {
                    bool isNew = false;
                    var line = db.ICSEQPSTP.SingleOrDefault(a => a.ID == info.ID);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSEQPSTP();
                    }
                    if (info.EQPTypeCode == "固定设备")
                    {
                        var codes = db.ICSEQPSTP.Where(a => a.ITEMCODE == info.ITEMCODE && a.EATTRIBUTE1 == info.EATTRIBUTE1 && a.OPCODE == info.OPCODE && a.ID != info.ID
                            && a.EQPCODE == info.EQPCODE);
                        if (codes.ToList().Count > 0)
                        {
                            throw new Exception("已存在相同'产品-工艺-工序-固定设备'的标准工时");
                        }
                    }
                    else if (info.EQPTypeCode == "加工中心" || info.EQPTypeCode == "设备类型")
                    {
                        var codes = db.ICSEQPSTP.Where(a => a.ITEMCODE == info.ITEMCODE && a.EATTRIBUTE1 == info.EATTRIBUTE1 && a.OPCODE == info.OPCODE && a.ID != info.ID
                            && a.cClass == info.cClass && a.cAxis == info.cAxis && a.cHandles == info.cHandles && a.cAccuracy == info.cAccuracy && a.cMachinableProfiles == info.cMachinableProfiles && a.cToolMagazine == info.cToolMagazine);
                        if (codes.ToList().Count > 0)
                        {
                            throw new Exception("已存在相同'产品-工序-6大维度'的标准工时");
                        }
                    }
                    else if (info.EQPTypeCode == "车削中心") {
                        var codes = db.ICSEQPSTP.Where(a => a.ITEMCODE == info.ITEMCODE && a.EATTRIBUTE1 == info.EATTRIBUTE1 && a.OPCODE == info.OPCODE && a.ID != info.ID && a.CXJD == info.CXJD && a.CXType == info.CXType && a.CXBrand1 == info.CXBrand1 && a.CXBrand2 == a.CXBrand2 && a.CXBrand3 == a.CXBrand3);
                        if (codes.ToList().Count > 0)
                        {
                            throw new Exception("已存在相同'产品-工序-车削中心'的标准工时");
                        }
                    }

                    line.ID = info.ID;
                    line.ITEMCODE = info.ITEMCODE;
                    line.OPCODE = info.OPCODE;
                    line.EQPCODE = info.EQPCODE;
                    line.ETTRCODE = info.ETTRCODE;
                    line.ISREF = info.ISREF;
                    line.Status = "New";
                    var line1 = db.ICSEQPSTP.Where(a => a.ITEMCODE == info.ITEMCODE && a.EATTRIBUTE1 == info.EATTRIBUTE1 && a.OPCODE == info.OPCODE && a.ISREF == "是" && a.ID != info.ID);
                    if (line1.ToList().Count > 0 && info.ISREF == "是")
                    {
                        //已经存在则改为否
                        foreach (var stp in line1.ToList())
                        {
                            var lineOther = db.ICSEQPSTP.SingleOrDefault(a => a.ID == stp.ID);
                            lineOther.ISREF = "否";
                            db.SubmitChanges();
                        }
                    }
                    line.STIME = Convert.ToDecimal(info.STIME);
                    line.RTIME = Convert.ToDecimal(info.RTIME);
                    line.JiaGongTime = info.JiaGongTime;
                    line.JiaZhuangTime = info.JiaZhuangTime;
                    line.JianYanTime = info.JianYanTime;
                    line.TiaoJiTime = info.TiaoJiTime;
                    line.MUSER = AppConfig.UserCode;
                    line.MUSERName = AppConfig.UserName;
                    line.MTIME = DateTime.Now;
                    line.WorkPoint = AppConfig.WorkPointCode;
                    line.EQPTypeCode = info.EQPTypeCode;

                    line.cClass = info.cClass;
                    line.cHandles = info.cHandles;
                    line.cAxis = info.cAxis;
                    line.cToolMagazine = info.cToolMagazine;
                    line.cMachinableProfiles = info.cMachinableProfiles;
                    line.cAccuracy = info.cAccuracy;
                    line.OPLVL = info.OPLVL;
                    line.ToolingNo = info.ToolingNo;
                    line.EATTRIBUTE1 = info.EATTRIBUTE1;
                    line.WTIME = info.WTIME;
                    line.CXBrand1 = info.CXBrand1;
                    line.CXBrand2 = info.CXBrand2;
                    line.CXBrand3 = info.CXBrand3;
                    line.CXJD = info.CXJD;
                    line.CXType = info.CXType;


                    ////20180626新增
                    //line.BladeModelAndBrand = info.BladeModelAndBrand;
                    //line.OutsourcingProperties = info.OutsourcingProperties;
                    //line.ToolingNo = info.ToolingNo;

                    if (isNew)
                        db.ICSEQPSTP.InsertOnSubmit(line);
                    db.SubmitChanges();

                }

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

        #region 新增和修改List
        public static string AddList(List<FormICSEQPSTPUIModelNew> InfoList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                string str = "";
                foreach (FormICSEQPSTPUIModelNew info in InfoList)
                {
                    var codes = db.ICSEQPSTP.Where(a => a.ITEMCODE == info.ITEMCODE && a.OPCODE == info.OPCODE && a.EQPTypeCode == info.EQPTypeCode);
                    List<ICSEQPSTP> list = codes.ToList();

                    if (list.Count > 0)
                    {
                        //已审核不做处理
                        if (list[0].Status == "Check")
                        {
                            var line = list[0];
                            line.ITEMCODE = info.ITEMCODE;
                            line.OPCODE = info.OPCODE;
                            line.EQPCODE = info.EQPCODE;
                            line.EQPTypeCode = info.EQPTypeCode;
                            line.ETTRCODE = info.ETTRCODE;
                            line.STIME = Convert.ToDecimal(info.STIME);
                            line.ISREF = info.ISREF;
                            line.RTIME = info.RTIME;
                            line.Status = info.Status;
                            line.MUSER = AppConfig.UserId;
                            line.MUSERName = AppConfig.UserName;
                            line.MTIME = DateTime.Now;
                            line.WorkPoint = AppConfig.WorkPointCode;
                            line.EATTRIBUTE1 = info.EATTRIBUTE1;
                            line.OutsourcingProperties = info.OutsourcingProperties;
                            line.BladeModelAndBrand = info.BladeModelAndBrand;
                            line.ToolingNo = info.ToolingNo;
                            db.SubmitChanges();
                        }
                        else //未审核时需要更新
                        {

                            //str += "存货编码：" + info.ITEMCODE + "工序代码：" + info.OPCODE + "设备类型" + info.EQPTypeCode + "已维护标准工时" + "\r\n";
                            //continue;
                            var line = list[0];
                            line.ITEMCODE = info.ITEMCODE;
                            line.OPCODE = info.OPCODE;
                            line.EQPCODE = info.EQPCODE;
                            line.EQPTypeCode = info.EQPTypeCode;
                            line.ETTRCODE = info.ETTRCODE;
                            line.STIME = Convert.ToDecimal(info.STIME);
                            line.ISREF = info.ISREF;
                            line.RTIME = info.RTIME;
                            line.Status = info.Status;
                            var line1 = db.ICSEQPSTP.Where(a => a.ITEMCODE == info.ITEMCODE && a.OPCODE == info.OPCODE && a.ISREF == "是" && a.EQPTypeCode != info.EQPTypeCode);
                            if (line1.ToList().Count > 0 && info.ISREF == "是")
                            {
                                //str += "存货编码：" + info.ITEMCODE + "工序代码：" + info.OPCODE + "已存在默认标准工时" + "\r\n";
                                //continue;

                                foreach (var stp in line1.ToList())
                                {
                                    stp.ISREF = "否";
                                }
                            }

                            line.OutsourcingProperties = info.OutsourcingProperties;
                            line.BladeModelAndBrand = info.BladeModelAndBrand;
                            line.ToolingNo = info.ToolingNo;

                            line.MUSER = info.MUSER;
                            line.MUSERName = info.MUSERName;
                            line.MTIME = (DateTime)info.MTIME;
                            line.WorkPoint = info.WorkPoint;
                            line.EATTRIBUTE1 = info.EATTRIBUTE1;

                            db.SubmitChanges();
                        }
                    }
                    else
                    {

                        bool isNew = false;
                        var line = db.ICSEQPSTP.SingleOrDefault(a => a.ITEMCODE == info.ITEMCODE && a.OPCODE == info.OPCODE && a.EQPTypeCode == info.EQPTypeCode);

                        if (line == null)
                        {
                            isNew = true;
                            line = new ICSEQPSTP();
                            line.ID = AppConfig.GetGuid();
                        }
                        //line.ID = info.ID;
                        line.ITEMCODE = info.ITEMCODE;
                        line.OPCODE = info.OPCODE;
                        //line.EQPCODE = info.EQPCODE;
                        line.EQPTypeCode = info.EQPTypeCode;
                        line.ETTRCODE = info.ETTRCODE;
                        line.STIME = Convert.ToDecimal(info.STIME);
                        line.ISREF = info.ISREF;
                        line.RTIME = info.RTIME;
                        line.Status = info.Status;
                        var line1 = db.ICSEQPSTP.Where(a => a.ITEMCODE == info.ITEMCODE && a.OPCODE == info.OPCODE && a.EQPTypeCode != info.EQPTypeCode && a.ISREF == "是");
                        if (line1.ToList().Count > 0 && info.ISREF == "是")
                        {
                            //str += "存货编码：" + info.ITEMCODE + "工序代码：" + info.OPCODE + "已存在默认标准工时" + "\r\n";
                            foreach (var stp in line1.ToList())
                            {
                                stp.ISREF = "否";
                            }
                            //continue;
                        }
                        line.MUSER = AppConfig.UserId;
                        line.MUSERName = AppConfig.UserName;
                        line.MTIME = DateTime.Now;
                        line.WorkPoint = AppConfig.WorkPointCode;
                        line.EATTRIBUTE1 = info.EATTRIBUTE1;
                        line.OutsourcingProperties = info.OutsourcingProperties;
                        line.BladeModelAndBrand = info.BladeModelAndBrand;
                        line.ToolingNo = info.ToolingNo;

                        if (isNew)
                            db.ICSEQPSTP.InsertOnSubmit(line);
                        db.SubmitChanges();
                    }

                }

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

        public static void Import(List<ICSEQPSTP> InfoList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (ICSEQPSTP info in InfoList)
                {
                    bool isNew = false;
                    ICSEQPSTP line = null;
                    if (info.EQPTypeCode == "委外")
                    {
                        line = db.ICSEQPSTP.SingleOrDefault(a => a.ITEMCODE == info.ITEMCODE && a.OPCODE == info.OPCODE && a.WorkPoint == AppConfig.WorkPointCode);
                        if (line == null)
                        {
                            isNew = true;
                            line = new ICSEQPSTP();
                            line.ID = AppConfig.GetGuid();
                        }
                    }
                    else if (info.EQPTypeCode == "固定设备")
                    {
                        line = db.ICSEQPSTP.SingleOrDefault(a => a.ITEMCODE == info.ITEMCODE && a.OPCODE == info.OPCODE && a.WorkPoint == AppConfig.WorkPointCode
                            && a.EQPCODE == info.EQPCODE);
                        if (line == null)
                        {
                            isNew = true;
                            line = new ICSEQPSTP();
                            line.ID = AppConfig.GetGuid();
                        }
                    }
                    else
                    {
                        line = db.ICSEQPSTP.SingleOrDefault(a => a.ITEMCODE == info.ITEMCODE && a.OPCODE == info.OPCODE && a.WorkPoint == AppConfig.WorkPointCode
                            && a.cClass == info.cClass && a.cAxis == info.cAxis && a.cHandles == info.cHandles && a.cAccuracy == info.cAccuracy && a.cMachinableProfiles == info.cMachinableProfiles && a.cToolMagazine == info.cToolMagazine);
                        if (line == null)
                        {
                            isNew = true;
                            line = new ICSEQPSTP();
                            line.ID = AppConfig.GetGuid();
                        }
                    }
                    line.ITEMCODE = info.ITEMCODE;
                    line.OPCODE = info.OPCODE;
                    line.EQPCODE = info.EQPCODE;
                    line.ETTRCODE = info.ETTRCODE;
                    line.ISREF = info.ISREF;
                    line.Status = "New";
                    if (info.ISREF == "是")
                    {
                        //如果当前行是默认,则其他行改为非默认
                        var line1 = db.ICSEQPSTP.Where(a => a.ITEMCODE == info.ITEMCODE && a.OPCODE == info.OPCODE && a.ISREF == "是" && a.ID != info.ID);
                        List<ICSEQPSTP> list = line1.ToList();
                        foreach (var isRef in list)
                        {
                            var lineOther = db.ICSEQPSTP.SingleOrDefault(a => a.ID == isRef.ID);
                            lineOther.ISREF = "否";
                            db.SubmitChanges();
                        }
                    }

                    line.STIME = Convert.ToDecimal(info.STIME);
                    line.RTIME = Convert.ToDecimal(info.RTIME);
                    line.JiaGongTime = info.JiaGongTime;
                    line.JiaZhuangTime = info.JiaZhuangTime;
                    line.JianYanTime = info.JianYanTime;
                    line.TiaoJiTime = info.TiaoJiTime;
                    line.MUSER = AppConfig.UserId;
                    line.MUSERName = AppConfig.UserName;
                    line.MTIME = DateTime.Now;
                    line.WorkPoint = AppConfig.WorkPointCode;
                    line.EQPTypeCode = info.EQPTypeCode;

                    line.cClass = info.cClass;
                    line.cHandles = info.cHandles;
                    line.cAxis = info.cAxis;
                    line.cToolMagazine = info.cToolMagazine;
                    line.cMachinableProfiles = info.cMachinableProfiles;
                    line.cAccuracy = info.cAccuracy;
                    line.OPLVL = info.OPLVL;
                    line.WTIME = info.WTIME;
                    //line.EATTRIBUTE1 = info.EATTRIBUTE1;
                    ////20180626新增
                    //line.BladeModelAndBrand = info.BladeModelAndBrand;
                    //line.OutsourcingProperties = info.OutsourcingProperties;
                    //line.ToolingNo = info.ToolingNo;

                    if (isNew)
                        db.ICSEQPSTP.InsertOnSubmit(line);
                    db.SubmitChanges();

                }

                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                db.Connection.Close();
            }
        }

        #endregion

        #region 通过ID查询
        public static DataTable searchInfoByID(string ID, string dsconn)
        {

            //            string sql = @"select a.ID,
            //a.ITEMCODE,
            //a.OPCODE,
            //a.EQPCODE,
            //a.ETTRCOde,
            //a.STIME,
            //a.RTIME,
            //a.ISREF,
            //a.OutsourcingProperties ,
            //a.BladeModelAndBrand,
            //a.ToolingNo,
            //
            //a.WorkPoint,
            //a.MUSER,
            //a.MUSERName,
            //a.MTIME,
            //a.EATTRIBUTE1,
            //a.EQPTypeCode,
            //b.INVNAME ItemName,
            //c.OPDESC,
            //d.EnumText as EQPTypeName,
            //e.EQPName
            //
            //           FROM ICSEQPSTP a 
            //      left join ICSINVENTORY b on a.ITEMCODE=b.INVCODE
            //     left join ICSOP c on a.OPCODE=C.OPCODE
            //     left join ( select * from Sys_EnumValues where EnumKey='001') d on a.EQPTypeCode=d.EnumValue
            //     left join ICSEquipment e on a.EQPCODE=E.EQPCODE
            //            where a.ID='{0}'";

            string sql = @"
select 
	a.ID,
	a.ITEMCODE,
	a.OPCODE,
	a.EQPCODE,
	a.ETTRCOde,
	ISNULL(a.STIME,0)  STIME,
	ISNULL(a.RTIME,0)  RTIME,
	ISNULL(a.JianYanTime,0) JianYanTime,
	ISNULL(a.TiaoJiTime,0) TiaoJiTime,
	ISNULL(a.JiaZhuangTime,0) JiaZhuangTime,
	ISNULL(a.JiaGongTime,0) JiaGongTime,
	a.ISREF,
	a.OutsourcingProperties ,
	a.BladeModelAndBrand,
	a.ToolingNo AS ROUTEMEMO,
	a.WorkPoint,
	a.MUSER,
	a.MUSERName,
	a.MTIME,
	a.EATTRIBUTE1 AS ROUTECODE,
	a.EQPTypeCode,
	b.INVNAME ItemName,
	c.OPDESC,
	a.EQPTypeCode as EQPTypeCode,
a.cClass,
a.cHandles,
a.cAxis,
a.cToolMagazine,
a.cMachinableProfiles,
a.cAccuracy,
a.OPLVL,
a.CXType,
a.CXBrand1,
a.CXBrand2,
a.CXBrand3,
a.CXJD
	-- d.TypeCODE AS EQPCODE,
	--d.TypeDESC as EQPTypeName
FROM ICSEQPSTP a 
left join ICSINVENTORY b on a.ITEMCODE=b.INVCODE
left join ICSOP c on a.OPCODE=C.OPCODE
--LEFT JOIN ICSEquipmentType d ON a.EQPTypeCode=d.TypeCODE
--left join ( select * from Sys_EnumValues where EnumKey='001') d on a.EQPTypeCode=d.EnumValue
--left join ICSEquipment e on a.EQPCODE=E.EQPCODE
where a.ID='{0}'
";

            sql = string.Format(sql, ID);
            DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];

            return dt;
        }
        #endregion

        #region 是否存在
        public static bool IsIncluding(string ItemCode, string OPCode, string eqptype, string dsconn)
        {

            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSEQPSTP.SingleOrDefault(a => a.ITEMCODE == ItemCode && a.OPCODE == OPCode && a.EQPTypeCode == eqptype);
                if (line == null)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }

        }
        #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.ICSEQPSTP.Where(a => codeList.Contains(a.ID));
                db.ICSEQPSTP.DeleteAllOnSubmit(lines);
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }

        }
        #endregion


        #region 审核
        public static void checkInfo(List<string> codeList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSEQPSTP.Where(a => codeList.Contains(a.ID));
                foreach (var line in lines)
                {
                    line.Status = "Check";
                }
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }

        }
        #endregion



        public static DataTable GetEQ(string conn, string wp)
        {
            string sql = "SELECT EQPID,EQPCode AS EQPCODE,EQPName AS EQPNAME,EQPDESC AS EQPDESC FROM dbo.ICSEquipment WHERE WorkPoint='{0}'";
            sql = string.Format(sql, wp);
            return DBHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
        }
        public static DataTable GetFIXEQ(string conn, string wp)
        {
            string sql = @"
SELECT 
	A.EQPID,A.EQPCode AS EQPCODE,A.EQPName AS EQPNAME,A.EQPDESC AS EQPDESC,A.FIXOP,A.EType,B.TypeDESC 
FROM dbo.ICSEquipment a
INNER JOIN [dbo].[ICSEquipmentType] b ON a.EType=b.TypeCODE AND a.WorkPoint=b.WorkPoint
WHERE b.TypeDESC='固定设备' AND A.WorkPoint='{0}'";
            sql = string.Format(sql, wp);
            return DBHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
        }


        public static DataTable GetINV(string conn, string wp)
        {
            string sql = "SELECT ID AS ITEMID,INVCODE AS ITEMCODE,INVNAME AS ITEMNAME,INVDESC AS ITEMDESC FROM dbo.ICSINVENTORY WHERE WorkPoint='{0}'";
            sql = string.Format(sql, wp);
            return DBHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
        }

        public static DataTable GetBaseOP(string conn, string wp)
        {
            //阿威特 基础工序,状态失效,位数3,用于一键导入拼接工序名称
            string sql = @"SELECT distinct OPCODE AS OirOPCODE,OPNAME AS OriOPNAME FROM dbo.ICSOP WHERE EnableFlag=0 AND LEN(OPCODE)=3";
            sql = string.Format(sql, wp);
            return DBHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
        }

        public static void ImportFromErp(string conn, List<ITEM2ROUTE2OP2TIMEawt> list)
        {
            FramDataContext db = new FramDataContext(conn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                #region 需要导入以下表
                //1.OP
                //2.ROUTE
                //3.ROUTE2OP
                //4.ITEM2ROUTE
                //5.ITEMROUTE2OP
                //6.ICSEQPSTP
                #endregion
                foreach (ITEM2ROUTE2OP2TIMEawt info in list)
                {
                    //0.验证物料
                    var lineInv = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == info.ITEMCODE && a.WorkPoint == info.WorkPoint);
                    if (lineInv == null)
                    {
                        throw new Exception("物料不存在" + info.ITEMCODE);
                    }
                    info.ITEMID = lineInv.ID;
                    foreach (ROUTEawt route in info.listRoute)
                    {
                        string mgrUserCode = "";
                        int cr = db.ICSROUTE.Where(a => a.ROUTECODE == route.ROUTECODE && a.WorkPoint == info.WorkPoint).Count();
                        if (cr > 0)
                        {
                            throw new Exception("物料" + info.ITEMCODE + "工艺版本" + route.ROUTEVER + "已存在!");
                        }
                        List<Sys_User> cu = db.Sys_User.Where(a => a.UserName == route.MGR_USER && a.WorkPointCode == info.WorkPoint).ToList();
                        if (cu.Count() == 0)
                        {
                            throw new Exception("工艺负责人错误,未找到此人员名称" + route.MGR_USER);
                        }
                        mgrUserCode = cu[0].UserCode;
                        //工艺导入ICSROUTE
                        ICSROUTE lineR = new ICSROUTE();
                        lineR.ID = route.ROUTEID = AppConfig.GetGuid();
                        lineR.ROUTECODE = route.ROUTECODE;
                        lineR.ROUTEDESC = "物料" + info.ITEMCODE + "工艺版本" + route.ROUTEVER;
                        lineR.ROUTETYPE = route.ROUTETYPE;
                        lineR.ENABLED = route.ENABLED;
                        lineR.EFFDATE = route.EFFDATE;
                        lineR.IVLDATE = route.IVLDATE;
                        lineR.MTIME = info.MTIME;
                        lineR.MUSER = info.USERID;//id
                        lineR.MUSERName = info.USERNAME;
                        lineR.WorkPoint = info.WorkPoint;
                        db.ICSROUTE.InsertOnSubmit(lineR);

                        //物料工艺绑定ICSITEM2ROUTE
                        var linesI2R_Old = db.ICSITEM2ROUTE.Where(a => a.ITEMCODE == info.ITEMCODE && a.ISREF == "1" && a.WorkPoint == info.WorkPoint);
                        foreach (var I2R_Old in linesI2R_Old)
                        {
                            //取消旧绑定.默认工艺状态
                            I2R_Old.ISREF = "0";
                        }
                        db.SubmitChanges();
                        ICSITEM2ROUTE lineI2R = new ICSITEM2ROUTE();
                        lineI2R.ISREF = "1";
                        lineI2R.ITEMID = info.ITEMID;
                        lineI2R.ITEMCODE = info.ITEMCODE;
                        lineI2R.ROUTEID = route.ROUTEID;
                        lineI2R.ROUTECODE = route.ROUTECODE;
                        lineI2R.EATTRIBUTE1 = mgrUserCode;//工艺负责人
                        lineI2R.MTIME = info.MTIME;
                        lineI2R.MUSER = info.USERID;//id
                        lineI2R.MUSERName = info.USERNAME;
                        lineI2R.WorkPoint = info.WorkPoint;
                        db.ICSITEM2ROUTE.InsertOnSubmit(lineI2R);

                        foreach (OPTIMEawt op in route.listOP)
                        {
                            var lineop = db.ICSOP.SingleOrDefault(a => a.OPCODE == op.OPCODE && a.WorkPoint == info.WorkPoint);
                            if (lineop == null)
                            {
                                //工序导入ICSOP
                                lineop = new ICSOP();
                                lineop.ID = op.OPID = AppConfig.GetGuid();
                                lineop.OPCODE = op.OPCODE;
                                lineop.OPDESC = op.OPDESC;
                                lineop.OPNAME = op.OPNAME;
                                lineop.OpFrom = op.OpFrom;
                                lineop.OPCOLLECTION = op.OPCOLLECTION;
                                lineop.OPCONTROL = op.OPCONTROL;
                                lineop.CostCenter = op.CostCenter;
                                lineop.EATTRIBUTE1 = op.OPAttr == "自制" ? "0" : "1";
                                lineop.EnableFlag = op.EnableFlag;
                                lineop.MTIME = info.MTIME;
                                lineop.MUSER = info.USERID;//id
                                lineop.MUSERName = info.USERNAME;
                                lineop.WorkPoint = info.WorkPoint;
                                db.ICSOP.InsertOnSubmit(lineop);
                            }
                            else
                            {
                                op.OPID = lineop.ID;
                            }
                            //工艺工序绑定ICSROUTE2OP. (其他字段暂时没用)
                            ICSROUTE2OP lineR2O = new ICSROUTE2OP();
                            lineR2O.ID = AppConfig.GetGuid();
                            lineR2O.ROUTEID = route.ROUTEID;
                            lineR2O.OPID = op.OPID;
                            lineR2O.ROUTECODE = route.ROUTECODE;
                            lineR2O.OPCODE = op.OPCODE;
                            lineR2O.OPSEQ = op.OPSEQ;
                            lineR2O.OPCONTROL = op.OPCONTROL;
                            lineR2O.OPAttr = op.OPAttr;
                            lineR2O.CtrlType = op.CtrlType;
                            lineR2O.MTIME = info.MTIME;
                            lineR2O.MUSER = info.USERCODE;//code
                            lineR2O.MUSERName = info.USERNAME;
                            lineR2O.WORKPOINT = info.WorkPoint;
                            db.ICSROUTE2OP.InsertOnSubmit(lineR2O);
                            //物料工序下发ICSITEMROUTE2OP
                            ICSITEMROUTE2OP lineIR2O = new ICSITEMROUTE2OP();
                            lineIR2O.ITEMID = info.ITEMID;
                            lineIR2O.ROUTEID = route.ROUTEID;
                            lineIR2O.OPID = op.OPID;
                            lineIR2O.ITEMCODE = info.ITEMCODE;
                            lineIR2O.ROUTECODE = route.ROUTECODE;
                            lineIR2O.OPCODE = op.OPCODE;
                            lineIR2O.OPSEQ = op.OPSEQ;
                            lineIR2O.OPAttr = op.OPAttr;
                            lineIR2O.CtrlType = op.CtrlType;
                            lineIR2O.RouteMGR = route.MGR_USER;//工艺负责人
                            lineIR2O.OPCONTROL = op.OPCONTROL;
                            lineIR2O.OPTIONALOP = "";
                            lineIR2O.IDMERGETYPE = "";
                            lineIR2O.IDMERGERULE = 0;
                            lineIR2O.MTIME = info.MTIME;
                            lineIR2O.MUSER = info.USERID;//id
                            lineIR2O.MUSERName = info.USERNAME;
                            lineIR2O.WorkPoint = info.WorkPoint;
                            db.ICSITEMROUTE2OP.InsertOnSubmit(lineIR2O);
                            //旧的标准工时取消默认状态
                            var Old_lineEQSTP = db.ICSEQPSTP.Where(a => a.ITEMCODE == info.ITEMCODE && a.EATTRIBUTE1 == route.ROUTECODE && a.OPCODE == op.OPCODE && a.ISREF == "是" && a.WorkPoint == info.WorkPoint);
                            foreach (ICSEQPSTP item in Old_lineEQSTP)
                            {
                                item.ISREF = "否";
                            }
                            db.SubmitChanges();
                            //标准工时导入ICSEQPSTP
                            ICSEQPSTP lineEQSTP = new ICSEQPSTP();
                            lineEQSTP.ID = AppConfig.GetGuid();
                            lineEQSTP.ITEMCODE = info.ITEMCODE;
                            lineEQSTP.EATTRIBUTE1 = route.ROUTECODE;
                            lineEQSTP.OPCODE = op.OPCODE;
                            lineEQSTP.EQPCODE = op.EQPCODE;
                            lineEQSTP.ETTRCODE = "";
                            lineEQSTP.STIME = lineEQSTP.JiaGongTime = op.STIME;
                            lineEQSTP.RTIME = lineEQSTP.TiaoJiTime = op.RTIME;
                            lineEQSTP.JianYanTime = op.JYTime;
                            lineEQSTP.JiaZhuangTime = op.ZJTime;
                            lineEQSTP.WTIME = op.WTIME;
                            lineEQSTP.EQPTypeCode = op.Type;
                            lineEQSTP.ISREF = "是";
                            lineEQSTP.Status = "New";
                            lineEQSTP.ToolingNo = op.Memo;
                            lineEQSTP.cClass = op.cClass;
                            lineEQSTP.cHandles = op.cHandles.ToString();
                            lineEQSTP.cAxis = op.cAxis.ToString();
                            lineEQSTP.cToolMagazine = op.cToolMagazine.ToString();
                            lineEQSTP.cMachinableProfiles = op.cMachinableProfiles.ToString();
                            lineEQSTP.cAccuracy = op.cAccuracy;
                            lineEQSTP.OPLVL = op.OPLVL;
                            lineEQSTP.MTIME = info.MTIME;
                            lineEQSTP.MUSER = info.USERCODE;//CODE
                            lineEQSTP.MUSERName = info.USERNAME;
                            lineEQSTP.WorkPoint = info.WorkPoint;
                            lineEQSTP.CXBrand1 = op.CXBrand1;
                            lineEQSTP.CXBrand2 = op.CXBrand2;
                            lineEQSTP.CXBrand3 = op.CXBrand3;
                            lineEQSTP.CXJD = op.CXJD;
                            lineEQSTP.CXType = op.CXTYPE;
                            db.ICSEQPSTP.InsertOnSubmit(lineEQSTP);
                        }
                    }
                    //返工工艺
                    //1.对应工序标准工时
                    List<ICSEQPSTP_RW> listStp4rw = GetRW_EQPSTP(db, info.WorkPoint);
                    //2.绑定
                    Import4RW(db, info.ITEMCODE, info.USERCODE, info.MTIME, listStp4rw, info.WorkPoint);
                }
                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                db.Connection.Close();
            }
        }

        //public static Dictionary<string, string> GetRWTimesSetting(FramDataContext db, string WorkPoint)
        //{
        //    Dictionary<string, string> dic = new Dictionary<string, string>();
        //    Sys_EnumKey rwKey = db.Sys_EnumKey.SingleOrDefault(a => a.EnumKey == "00012" && a.WorkPointCode == WorkPoint);
        //    if (rwKey == null)
        //    {
        //        throw new Exception("站点" + WorkPoint + "自定义档案: 返工工单工时,未维护");
        //    }
        //    List<Sys_EnumValues> rwValues = db.Sys_EnumValues.Where(a => a.EnumKey == rwKey.EnumKey && a.WorkPointCode == WorkPoint).ToList();
        //    if (rwValues.Count == 0)
        //    {
        //        throw new Exception("站点" + WorkPoint + "自定义档案: 返工工单工时,未维护");
        //    }
        //    foreach (Sys_EnumValues item in rwValues)
        //    {
        //        string val = item.EnumValue;
        //        string text = item.EnumText;
        //        string define1 = item.Define1.Trim();
        //        if (string.IsNullOrEmpty(define1))
        //        {
        //            throw new Exception("自定义档案: 返工工单工时, " + text + "不可为空");
        //        }
        //        switch (val)
        //        {
        //            case "0001":
        //                dic.Add("调机工时", define1);
        //                break;
        //            case "0002":
        //                dic.Add("加工工时", define1);
        //                break;
        //            case "0003":
        //                dic.Add("装夹工时", define1);
        //                break;
        //            case "0004":
        //                dic.Add("检验工时", define1);
        //                break;
        //            case "0005":
        //                dic.Add("委外工时", define1);
        //                break;
        //            case "0006":
        //                dic.Add("工艺负责人", define1);
        //                break;
        //            case "0007":
        //                dic.Add("设备代码", define1);
        //                break;
        //            default:
        //                continue;
        //        }
        //    }
        //    if (!dic.Keys.Contains("调机工时"))
        //    {
        //        throw new Exception("自定义档案: 返工工单工时维护,  调机工时 未维护0001");
        //    }
        //    if (!dic.Keys.Contains("加工工时"))
        //    {
        //        throw new Exception("自定义档案: 返工工单工时维护,  加工工时 未维护0002");
        //    }
        //    if (!dic.Keys.Contains("装夹工时"))
        //    {
        //        throw new Exception("自定义档案: 返工工单工时维护,  装夹工时 未维护0003");
        //    }
        //    if (!dic.Keys.Contains("检验工时"))
        //    {
        //        throw new Exception("自定义档案: 返工工单工时维护,  检验工时 未维护0004");
        //    }
        //    if (!dic.Keys.Contains("委外工时"))
        //    {
        //        throw new Exception("自定义档案: 返工工单工时维护,  委外工时 未维护0005");
        //    }
        //    if (!dic.Keys.Contains("工艺负责人"))
        //    {
        //        throw new Exception("自定义档案: 返工工单工时维护,  工艺负责人 未维护0006");
        //    }
        //    if (!dic.Keys.Contains("设备代码"))
        //    {
        //        throw new Exception("自定义档案: 返工工单工时维护,  设备代码 未维护0007");
        //    }
        //    return dic;
        //}

        ////public static ICSEQPSTP4RW GetRWTimesSetting(FramDataContext db, string WorkPoint)
        ////{
        ////    ICSEQPSTP4RW info = new ICSEQPSTP4RW();
        ////    Sys_EnumKey rwKey = db.Sys_EnumKey.SingleOrDefault(a => a.EnumKey == "00012" && a.WorkPointCode == WorkPoint);
        ////    if (rwKey == null)
        ////    {
        ////        throw new Exception("站点" + WorkPoint + "自定义档案: 返工工单工时,未维护");
        ////    }
        ////    List<Sys_EnumValues> rwValues = db.Sys_EnumValues.Where(a => a.EnumKey == rwKey.EnumKey && a.WorkPointCode == WorkPoint).ToList();
        ////    if (rwValues.Count == 0)
        ////    {
        ////        throw new Exception("站点" + WorkPoint + "自定义档案: 返工工单工时,未维护");
        ////    }
        ////    foreach (Sys_EnumValues item in rwValues)
        ////    {
        ////        string val = item.EnumValue;
        ////        string text = item.EnumText;
        ////        string define1 = item.Define1.Trim();
        ////        if (string.IsNullOrEmpty(define1))
        ////        {
        ////            throw new Exception("自定义档案: 返工工单工时, " + text + "不可为空");
        ////        }
        ////        switch (val)
        ////        {
        ////            case "0001":
        ////                info.RTIME = Convert.ToDecimal(define1);
        ////                break;
        ////            case "0002":
        ////                info.STIME = Convert.ToDecimal(define1);
        ////                break;
        ////            case "0003":
        ////                info.ZJTIME = Convert.ToDecimal(define1);
        ////                break;
        ////            case "0004":
        ////                info.JYTIME = Convert.ToDecimal(define1);
        ////                break;
        ////            case "0005":
        ////                info.WTIME = Convert.ToDecimal(define1);
        ////                break;
        ////            case "0006":
        ////                info.routeMgr = define1;
        ////                break;
        ////            case "0007":
        ////                info.EQPCODE = define1;
        ////                break;
        ////            default:
        ////                continue;
        ////        }
        ////    }
        ////    if (info.RTIME == null)
        ////    {
        ////        throw new Exception("自定义档案: 返工工单工时维护,  调机工时 未维护0001");
        ////    }
        ////    if (info.STIME == null)
        ////    {
        ////        throw new Exception("自定义档案: 返工工单工时维护,  加工工时 未维护0002");
        ////    }
        ////    if (info.ZJTIME == null)
        ////    {
        ////        throw new Exception("自定义档案: 返工工单工时维护,  装夹工时 未维护0003");
        ////    }
        ////    if (info.JYTIME == null)
        ////    {
        ////        throw new Exception("自定义档案: 返工工单工时维护,  检验工时 未维护0004");
        ////    }
        ////    if (info.WTIME == null)
        ////    {
        ////        throw new Exception("自定义档案: 返工工单工时维护,  委外工时 未维护0005");
        ////    }
        ////    if (string.IsNullOrEmpty(info.routeMgr))
        ////    {
        ////        throw new Exception("自定义档案: 返工工单工时维护,  工艺负责人 未维护0006");
        ////    }
        ////    if (string.IsNullOrEmpty(info.EQPCODE))
        ////    {
        ////        throw new Exception("自定义档案: 返工工单工时维护,  设备代码 未维护0007");
        ////    }
        ////    return info;
        ////}

        public static List<ICSEQPSTP_RW> GetRW_EQPSTP(FramDataContext db, string WorkPoint)
        {
            List<ICSEQPSTP_RW> listStp4rw = new List<ICSEQPSTP_RW>();
            listStp4rw = db.ICSEQPSTP_RW.Where(a => a.WorkPoint == WorkPoint).ToList();
            return listStp4rw;
        }

        public static void Import4RW(FramDataContext db, string ITEMCODE, string USERCODE, DateTime dtNow, List<ICSEQPSTP_RW> listStp4rw, string WorkPoint)
        {
            //1.ITEM2ROUTE
            //2.ITEMROUTE2OP
            //3.ICSEQPSTP

            string ITEMID = "";
            string USERID = "";
            string USERNAME = "";

            Sys_User USR = db.Sys_User.SingleOrDefault(a => a.UserCode == USERCODE && a.WorkPointCode == WorkPoint);
            if (USR == null)
            {
                throw new Exception("用户不存在" + USERCODE);
            }
            USERID = USR.ID;
            USERNAME = USR.UserName;

            ICSINVENTORY inv = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == ITEMCODE && a.WorkPoint == WorkPoint);
            if (inv == null)
            {
                throw new Exception("物料不存在" + ITEMID);
            }
            ITEMID = inv.ID;
            var linesRWRoute = db.ICSROUTE.Where(a => a.ROUTETYPE.ToLower() == "rework" && a.WorkPoint == WorkPoint);
            foreach (ICSROUTE route in linesRWRoute)
            {

                ICSITEM2ROUTE i2r = db.ICSITEM2ROUTE.SingleOrDefault(a => a.ITEMCODE == ITEMCODE && a.ROUTECODE == route.ROUTECODE && a.WorkPoint == WorkPoint);
                if (i2r != null)
                {
                    continue;
                }
                i2r = new ICSITEM2ROUTE();
                i2r.ISREF = "0";
                i2r.ITEMID = ITEMID;
                i2r.ITEMCODE = ITEMCODE;
                i2r.ROUTEID = route.ID;
                i2r.ROUTECODE = route.ROUTECODE;
                i2r.EATTRIBUTE1 = "demo";//返工工艺负责人默认demo
                i2r.MTIME = dtNow;
                i2r.MUSER = USERID;
                i2r.MUSERName = USERNAME;
                i2r.WorkPoint = WorkPoint;
                db.ICSITEM2ROUTE.InsertOnSubmit(i2r);
                var linesR2O = db.ICSROUTE2OP.Where(a => a.ROUTECODE == route.ROUTECODE && a.WORKPOINT == WorkPoint).ToList();
                foreach (ICSROUTE2OP r2o in linesR2O)
                {
                    ICSEQPSTP_RW stp4rw = null;
                    foreach (ICSEQPSTP_RW item in listStp4rw)
                    {
                        if (item.OPCODE == r2o.OPCODE)
                        {
                            stp4rw = item;
                            break;
                        }
                    }
                    if (stp4rw == null)
                    {
                        throw new Exception("返工工艺" + r2o.ROUTECODE + "工序" + r2o.OPCODE + "没有预设的标准工时");
                    }
                    ICSITEMROUTE2OP lineir2o = new ICSITEMROUTE2OP();
                    lineir2o.ITEMID = ITEMID;
                    lineir2o.ROUTEID = r2o.ROUTEID;
                    lineir2o.OPID = r2o.OPID;
                    lineir2o.ITEMCODE = ITEMCODE;
                    lineir2o.ROUTECODE = r2o.ROUTECODE;
                    lineir2o.OPCODE = r2o.OPCODE;
                    lineir2o.OPSEQ = r2o.OPSEQ;
                    lineir2o.OPAttr = r2o.OPAttr;
                    lineir2o.CtrlType = r2o.CtrlType;
                    lineir2o.RouteMGR = "demo";//工艺负责人
                    lineir2o.OPCONTROL = r2o.OPCONTROL;
                    lineir2o.OPTIONALOP = "";
                    lineir2o.IDMERGETYPE = "";
                    lineir2o.IDMERGERULE = 0;
                    lineir2o.MTIME = dtNow;
                    lineir2o.MUSER = USERID;//id
                    lineir2o.MUSERName = USERNAME;
                    lineir2o.WorkPoint = WorkPoint;
                    db.ICSITEMROUTE2OP.InsertOnSubmit(lineir2o);
                    //旧的标准工时取消默认状态
                    var Old_lineEQSTP = db.ICSEQPSTP.Where(a => a.ITEMCODE == ITEMCODE && a.EATTRIBUTE1 == route.ROUTECODE && a.OPCODE == r2o.OPCODE && a.ISREF == "是" && a.WorkPoint == WorkPoint);
                    foreach (ICSEQPSTP item in Old_lineEQSTP)
                    {
                        item.ISREF = "否";
                    }
                    db.SubmitChanges();
                    //标准工时导入ICSEQPSTP
                    ICSEQPSTP lineEQSTP = new ICSEQPSTP();
                    lineEQSTP.ID = AppConfig.GetGuid();
                    lineEQSTP.ITEMCODE = ITEMCODE;
                    lineEQSTP.EATTRIBUTE1 = route.ROUTECODE;
                    lineEQSTP.OPCODE = r2o.OPCODE;
                    lineEQSTP.EQPCODE = stp4rw.EQPCODE;
                    lineEQSTP.ETTRCODE = "";
                    lineEQSTP.STIME = lineEQSTP.JiaGongTime = stp4rw.JiaGongTime;
                    lineEQSTP.RTIME = lineEQSTP.TiaoJiTime = stp4rw.TiaoJiTime;
                    lineEQSTP.JianYanTime = stp4rw.JianYanTime;
                    lineEQSTP.JiaZhuangTime = stp4rw.ZhuangJiaTime;
                    lineEQSTP.WTIME = stp4rw.WTIME;
                    lineEQSTP.EQPTypeCode = stp4rw.EQPTypeCode;
                    lineEQSTP.ISREF = "是";
                    lineEQSTP.Status = "New";
                    lineEQSTP.ToolingNo = "";
                    lineEQSTP.cClass = stp4rw.cClass;
                    lineEQSTP.cHandles = stp4rw.cHandles;
                    lineEQSTP.cAxis = stp4rw.cAxis;
                    lineEQSTP.cToolMagazine = stp4rw.cToolMagazine;
                    lineEQSTP.cMachinableProfiles = stp4rw.cMachinableProfiles;
                    lineEQSTP.cAccuracy = stp4rw.cAccuracy;
                    lineEQSTP.OPLVL = stp4rw.OPLVL;
                    lineEQSTP.MTIME = dtNow;
                    lineEQSTP.MUSER = USERCODE;//CODE
                    lineEQSTP.MUSERName = USERNAME;
                    lineEQSTP.WorkPoint = WorkPoint;
                    lineEQSTP.CXType = stp4rw.CXTYPE;
                    lineEQSTP.CXJD = stp4rw.CXJD;
                    lineEQSTP.CXBrand1 = stp4rw.CXBrand1;
                    lineEQSTP.CXBrand2 = stp4rw.CXBrand2;
                    db.ICSEQPSTP.InsertOnSubmit(lineEQSTP);
                    db.SubmitChanges();
                }
            }
        }
    }
}