using System; using System.Collections.Generic; using System.Linq; using System.Text; using ICSSoft.Frame.Data.Entity; using System.Data; using ICSSoft.Base.Config.DBHelper; using ICSSoft.Base.Config.AppConfig; namespace ICSSoft.Frame.Data.DAL { public class ICSITEM2ROUTEDAL { #region 新增和修改List public static string AddList(List ITEM2ROUTEInfoList, string dsconn) { string str = ""; FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { foreach (FormICSITEM2ROUTEUIModel info in ITEM2ROUTEInfoList) { //校验产品是否存在 var item = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == info.ITEMCODE && a.WorkPoint == AppConfig.WorkPointCode); if (item == null) { str += "存货编码：" + info.ITEMCODE + " 不存在！\n"; continue; } else { info.item = new FormICSINVENTORYUIModel(); //info.item = new FormICSInventoryUIModelNew(); info.item.ID = item.ID; info.item.INVCODE = item.INVCODE; } //产品是否是默认途程 var ITEMISREF = db.ICSITEM2ROUTE.Where(a => a.ITEMCODE == info.ITEMCODE && a.ISREF == "是" && a.WorkPoint == AppConfig.WorkPointCode); if (ITEMISREF != null && ITEMISREF.Count() > 0 && info.ISREF == "是") { //str += "存货编码：" + info.ITEMCODE + " 已存在默认途程！\n"; //continue; foreach (var d in ITEMISREF) { d.ISREF = "否"; d.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"); } db.SubmitChanges(); } var user = db.Sys_User.Where(a => a.UserCode == info.EATTRIBUTE1 && a.WorkPointCode == AppConfig.WorkPointCode); if (user == null) { str += "负责人：" + info.EATTRIBUTE1 + " 不存在！\n"; continue; } //校验途程是否存在 var route = db.ICSROUTE.SingleOrDefault(a => a.ROUTECODE == info.ROUTECODE && a.WorkPoint == AppConfig.WorkPointCode); if (route == null) { str += "途程代码：" + info.ROUTECODE + " 不存在！\n"; continue; } else { info.route = new FormICSROUTEUIModel(); info.route.ID = route.ID; info.route.ROUTECODE = route.ROUTECODE; } bool isNew = false; var line = db.ICSITEM2ROUTE.SingleOrDefault(a => a.ITEMCODE == info.item.INVCODE && a.ROUTECODE == info.route.ROUTECODE && a.WorkPoint == AppConfig.WorkPointCode); if (line == null) { isNew = true; line = new ICSITEM2ROUTE(); line.ITEMID = info.item.ID; line.ITEMCODE = info.item.INVCODE; line.ROUTEID = info.route.ID; line.ROUTECODE = info.route.ROUTECODE; line.Status = "新增"; } line.ISREF = info.ISREF; line.MUSER = AppConfig.UserId; line.MUSERName = AppConfig.UserName; line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"); line.WorkPoint = AppConfig.WorkPointCode; if (isNew) { db.ICSITEM2ROUTE.InsertOnSubmit(line); //Addop(ITEM2ROUTEInfoList, dsconn); #region 产品途程工序 List listmodel = new List(); // string sql = @"select [OPID],[OPCODE],[OPSEQ],[OPCONTROL],[EATTRIBUTE1],[OPLock],ETime,Memo1 ,Memo2 // from [ICSROUTE2OP] // where ROUTECODE='{0}'"; string sql = @"select [OPID],[OPCODE],[OPSEQ],[OPCONTROL],[OPAttr],[CtrlType],[EATTRIBUTE1] as ItemRouteMGR from [ICSROUTE2OP] where ROUTECODE='{0}'"; sql = string.Format(sql, info.route.ROUTECODE); DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0]; foreach (DataRow dr in dt.Rows) { ICSITEMROUTE2OP model = new ICSITEMROUTE2OP(); model.ITEMID = info.item.ID; model.ITEMCODE = info.item.INVCODE; model.ROUTEID = info.route.ID; model.ROUTECODE = info.route.ROUTECODE; model.OPID = dr["OPID"].ToString(); model.OPCODE = dr["OPCODE"].ToString(); model.OPSEQ = Convert.ToInt32(dr["OPSEQ"].ToString()); model.OPCONTROL = dr["OPCONTROL"].ToString(); model.OPTIONALOP = ""; model.IDMERGETYPE = ""; model.IDMERGERULE = 0; model.RouteMGR = dr["ItemRouteMGR"].ToString(); model.CtrlType = dr["CtrlType"].ToString(); model.OPAttr = dr["OPAttr"].ToString(); model.MUSER = AppConfig.UserId; model.MUSERName = AppConfig.UserName; model.MTIME = DateTime.Now; model.WorkPoint = AppConfig.WorkPointCode; listmodel.Add(model); } foreach (ICSITEMROUTE2OP test in listmodel) { db.ICSITEMROUTE2OP.InsertOnSubmit(test); } #endregion } db.SubmitChanges(); } db.Transaction.Commit(); if (string.IsNullOrEmpty(str)) str = "数据导入成功！"; return str; } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion public static List SearchItemInfoList(string dsconn, string invcode) { try { List returntype = new List(); string sql = @"select [ID],[INVCODE],[INVNAME],[INVDESC] from [ICSINVENTORY] WHERE WorkPoint='" + AppConfig.WorkPointCode + "' "; sql = string.Format(sql, invcode); if (!string.IsNullOrWhiteSpace(invcode)) { sql += " AND INVCODE LIKE '%" + invcode + "%' "; } DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0]; foreach (DataRow dr in dt.Rows) { FormICSINVENTORYUIModel itemmodel = new FormICSINVENTORYUIModel(); itemmodel.ID = dr["ID"].ToString(); itemmodel.INVCODE = dr["INVCODE"].ToString(); itemmodel.INVNAME = dr["INVNAME"].ToString(); itemmodel.INVDESC = dr["INVDESC"].ToString(); if (!returntype.Contains(itemmodel)) returntype.Add(itemmodel); } return returntype; } catch (Exception ex) { throw ex; } } public static List SearchRouteInfoList(string dsconn) { try { List returntype = new List(); string sql = @"select [ID],[ROUTECODE],[ROUTEDESC] from [ICSROUTE] WHERE WorkPoint='" + AppConfig.WorkPointCode + "' "; sql = string.Format(sql); DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0]; foreach (DataRow dr in dt.Rows) { FormICSROUTEUIModel routemodel = new FormICSROUTEUIModel(); routemodel.ID = dr["ID"].ToString(); routemodel.ROUTECODE = dr["ROUTECODE"].ToString(); routemodel.ROUTEDESC = dr["ROUTEDESC"].ToString(); if (!returntype.Contains(routemodel)) returntype.Add(routemodel); } return returntype; } catch (Exception ex) { throw ex; } } #region 新增和修改 public static void Add(List ITEM2ROUTEInfoList,bool add, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { foreach (FormICSITEM2ROUTEUIModel info in ITEM2ROUTEInfoList) { bool isNew = false; string[] rv = info.route.ROUTECODE.Split('_'); string r = rv[0]; string v = ""; if (rv.Length == 2) { v = rv[1]; } if (info.ISREF == "1") { var lineISREF = db.ICSITEM2ROUTE.SingleOrDefault(a => a.ITEMCODE == info.item.INVCODE && a.ROUTECODE != info.route.ROUTECODE && a.ISREF == "1" && a.WorkPoint == AppConfig.WorkPointCode); if (lineISREF != null) { //throw new Exception("此产品" + info.item.INVCODE + "已有默认的工艺路线" + lineISREF.ROUTECODE); string[] orv = lineISREF.ROUTECODE.Split('_'); string or = orv[0]; string ov = ""; if (orv.Length == 2) { ov = orv[1]; } if (r == or && add) { //如果维护了标准工时,则把相同的工序对应的标准工时也copy string sql = @" INSERT INTO [dbo].[ICSEQPSTP] ([ID] ,[ITEMCODE] ,[OPCODE] ,[EQPCODE] ,[ETTRCODE] ,[STIME] ,[RTIME] ,[JiaGongTime] ,[JianYanTime] ,[TiaoJiTime] ,[JiaZhuangTime] ,[WorkPoint] ,[MUSER] ,[MUSERName] ,[MTIME] ,[EATTRIBUTE1] ,[EQPTypeCode] ,[ISREF] ,[Status] ,[OutsourcingProperties] ,[BladeModelAndBrand] ,[ToolingNo] ,[cClass] ,[cHandles] ,[cAxis] ,[cToolMagazine] ,[cMachinableProfiles] ,[cAccuracy] ,[OPLVL] ,[WTIME]) SELECT NEWID() ,A.[ITEMCODE] ,A.[OPCODE] ,A.[EQPCODE] ,A.[ETTRCODE] ,A.[STIME] ,A.[RTIME] ,A.[JiaGongTime] ,A.[JianYanTime] ,A.[TiaoJiTime] ,A.[JiaZhuangTime] ,A.[WorkPoint] ,A.[MUSER] ,A.[MUSERName] ,A.[MTIME] ,'{2}' ,A.[EQPTypeCode] ,'是' AS [ISREF] ,A.[Status] ,A.[OutsourcingProperties] ,A.[BladeModelAndBrand] ,A.[ToolingNo] ,A.[cClass] ,A.[cHandles] ,A.[cAxis] ,A.[cToolMagazine] ,A.[cMachinableProfiles] ,A.[cAccuracy] ,A.[OPLVL] ,A.[WTIME] FROM [dbo].[ICSEQPSTP] A INNER JOIN dbo.ICSROUTE2OP Old ON Old.ROUTECODE=A.EATTRIBUTE1 AND Old.OPCODE=A.OPCODE INNER JOIN dbo.ICSROUTE2OP New ON Old.OPCODE=New.OPCODE AND Old.WORKPOINT=New.WORKPOINT AND NEW.ROUTECODE='{2}' WHERE A.ITEMCODE='{0}' AND A.EATTRIBUTE1='{1}' "; sql = string.Format(sql, lineISREF.ITEMCODE, lineISREF.ROUTECODE, info.route.ROUTECODE); int res_i = db.ExecuteCommand(sql); db.SubmitChanges(); } lineISREF.ISREF = "0"; db.SubmitChanges(); } } var line = db.ICSITEM2ROUTE.SingleOrDefault(a => a.ITEMCODE == info.item.INVCODE && a.ROUTECODE == info.route.ROUTECODE && a.WorkPoint == AppConfig.WorkPointCode); if (line == null) { isNew = true; line = new ICSITEM2ROUTE(); line.ITEMID = info.item.ID; line.ITEMCODE = info.item.INVCODE; line.ROUTEID = info.route.ID; line.ROUTECODE = info.route.ROUTECODE; } line.ISREF = info.ISREF; line.EATTRIBUTE1 = info.EATTRIBUTE1; line.MUSER = AppConfig.UserId; line.MUSERName = AppConfig.UserName; line.MTIME = DateTime.Now; line.WorkPoint = AppConfig.WorkPointCode; if (isNew) { db.ICSITEM2ROUTE.InsertOnSubmit(line); db.SubmitChanges(); //复写至表ICSITEMROUTE2OP //Addop(ITEM2ROUTEInfoList, db); Addop(ITEM2ROUTEInfoList, info.EATTRIBUTE1, dsconn); } db.SubmitChanges(); } db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion public static void Addop(List ITEM2ROUTEInfoList, FramDataContext db) { try { string routecode; foreach (FormICSITEM2ROUTEUIModel info in ITEM2ROUTEInfoList) { List listmodel = new List(); routecode = info.route.ROUTECODE; string sql = @"select [OPID],[OPCODE],[OPSEQ],[OPCONTROL],[OPAttr],[CtrlType],[EATTRIBUTE1] from [ICSROUTE2OP] where ROUTECODE='{0}' AND WorkPoint='" + AppConfig.WorkPointCode + "' "; sql = string.Format(sql, routecode); var list = db.ICSROUTE2OP.Context.ExecuteQuery(sql); foreach (ICSROUTE2OP item in list) { ICSITEMROUTE2OP model = new ICSITEMROUTE2OP(); model.ITEMID = info.item.ID; model.ITEMCODE = info.item.INVCODE; model.ROUTEID = info.route.ID; model.ROUTECODE = info.route.ROUTECODE; model.OPID = item.OPID; model.OPCODE = item.OPCODE; model.OPSEQ = item.OPSEQ; model.OPCONTROL = item.OPCONTROL; model.OPTIONALOP = ""; model.IDMERGETYPE = ""; model.IDMERGERULE = 0; model.RouteMGR = item.EATTRIBUTE1;//负责人 model.CtrlType = item.CtrlType; model.OPAttr = item.OPAttr; model.MUSER = AppConfig.UserId; model.MUSERName = AppConfig.UserName; model.MTIME = DateTime.Now; model.WorkPoint = AppConfig.WorkPointCode; listmodel.Add(model); } foreach (ICSITEMROUTE2OP test in listmodel) { db.ICSITEMROUTE2OP.InsertOnSubmit(test); } db.SubmitChanges(); } } catch (Exception ex) { throw new Exception("复写至ItemRoute2OP出错:" + ex.Message); } } public static void Addop(List ITEM2ROUTEInfoList, string ItemRouteMGR, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { string routecode; foreach (FormICSITEM2ROUTEUIModel info in ITEM2ROUTEInfoList) { List listmodel = new List(); routecode = info.route.ROUTECODE; string sql = @"select [OPID],[OPCODE],[OPSEQ],[OPCONTROL],[OPAttr],[CtrlType] from [ICSROUTE2OP] where ROUTECODE='{0}' AND WorkPoint='" + AppConfig.WorkPointCode + "' "; sql = string.Format(sql, routecode); DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0]; foreach (DataRow dr in dt.Rows) { ICSITEMROUTE2OP model = new ICSITEMROUTE2OP(); model.ITEMID = info.item.ID; model.ITEMCODE = info.item.INVCODE; model.ROUTEID = info.route.ID; model.ROUTECODE = info.route.ROUTECODE; model.OPID = dr["OPID"].ToString(); model.OPCODE = dr["OPCODE"].ToString(); model.OPSEQ = Convert.ToInt32(dr["OPSEQ"].ToString()); model.OPCONTROL = dr["OPCONTROL"].ToString(); model.OPTIONALOP = ""; model.IDMERGETYPE = ""; model.IDMERGERULE = 0; model.RouteMGR = ItemRouteMGR; model.CtrlType = dr["CtrlType"].ToString(); model.OPAttr = dr["OPAttr"].ToString(); model.MUSER = AppConfig.UserId; model.MUSERName = AppConfig.UserName; model.MTIME = DateTime.Now; model.WorkPoint = AppConfig.WorkPointCode; listmodel.Add(model); } foreach (ICSITEMROUTE2OP test in listmodel) { db.ICSITEMROUTE2OP.InsertOnSubmit(test); } db.SubmitChanges(); db.Transaction.Commit(); } } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #region 代码是否存在 public static bool IsIncluding(string itemCODE, string routeCODE, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { var line = db.ICSITEM2ROUTE.SingleOrDefault(a => a.ITEMCODE == itemCODE && a.ROUTECODE == routeCODE && a.WorkPoint == AppConfig.WorkPointCode); if (line == null) return true; else return false; } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion #region 产品是否已有使用/默认的途程 public static bool IsIncludingISREF(string itemCODE, string routeCODE, string dsconn, out string defaultRouteCODE) { defaultRouteCODE = routeCODE; FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { var line = db.ICSITEM2ROUTE.SingleOrDefault(a => a.ITEMCODE == itemCODE && a.ROUTECODE != routeCODE && a.ISREF.Equals("1") && a.WorkPoint == AppConfig.WorkPointCode); if (line == null) { defaultRouteCODE = routeCODE; return false; } else { defaultRouteCODE = line.ROUTECODE; return true; } } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion #region 通过存货编码和途程代码查询 public static List SearchInfoByCode(string itemCode, string routeCode, string dsconn) { List returnir = new List(); string sql = @"select a.[ISREF],b.[ID] as bID,b.[INVCODE],b.[INVNAME],b.[INVDESC],c.[ID] as cID,c.[ROUTECODE],c.[ROUTEDESC],a.EATTRIBUTE1 from ICSITEM2ROUTE as a left join ICSINVENTORY as b on (a.ITEMID=b.ID) AND b.WorkPoint='{2}' left join ICSROUTE as c on (a.ROUTEID=c.ID) AND c.WorkPoint='{2}' where a.ITEMCODE='{0}' and a.ROUTECODE='{1}' AND a.WorkPoint='{2}' "; sql = string.Format(sql, itemCode, routeCode, AppConfig.WorkPointCode); DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0]; foreach (DataRow dr in dt.Rows) { FormICSITEM2ROUTEUIModel returnInfo = new FormICSITEM2ROUTEUIModel(); returnInfo.EATTRIBUTE1 = dr["EATTRIBUTE1"].ToString(); returnInfo.ISREF = dr["ISREF"].ToString(); returnInfo.item = new FormICSINVENTORYUIModel(); returnInfo.item.ID = dr["bID"].ToString(); returnInfo.item.INVCODE = dr["INVCODE"].ToString(); returnInfo.item.INVNAME = dr["INVNAME"].ToString(); returnInfo.item.INVDESC = dr["INVDESC"].ToString(); returnInfo.route = new FormICSROUTEUIModel(); returnInfo.route.ID = dr["cID"].ToString(); returnInfo.route.ROUTECODE = dr["ROUTECODE"].ToString(); returnInfo.route.ROUTEDESC = dr["ROUTEDESC"].ToString(); if (!returnir.Contains(returnInfo)) { returnir.Add(returnInfo); } } return returnir; } #endregion public static DataTable GetDetail(String itemcode, String routecode, String Appconstr) { try { string sql = @"SELECT a.ID, a.RouteCode, b.ITEMCODE FROM ICSMO2User a LEFT JOIN ICSMO b ON a.MOCODE=b.MOCODE AND a.MOSEQ=b.MOSEQ AND a.WorkPoint=b.WorkPoint where a.RouteCode='{0}' AND b.ITEMCODE='{1}' AND a.WorkPoint='" + AppConfig.WorkPointCode + "' order by a.RouteCode"; sql = string.Format(sql, routecode, itemcode); DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0]; return dt; } catch (Exception ex) { throw ex; } } #region 删除 public static void deleteInfo(string itemcode, string routecode, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { var lines = db.ICSITEM2ROUTE.Where(a => a.ITEMCODE == itemcode && a.ROUTECODE == routecode); db.ICSITEM2ROUTE.DeleteAllOnSubmit(lines); var lineone = db.ICSITEMROUTE2OP.Where(a => a.ITEMCODE == itemcode && a.ROUTECODE == routecode); db.ICSITEMROUTE2OP.DeleteAllOnSubmit(lineone); db.SubmitChanges(); db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion public static DataTable CheckOP(String ROUTECODE, String Appconstr) { try { string sql = @"SELECT * FROM ICSROUTE2OP WHERE 1=1 AND ROUTECODE = '{0}' AND WorkPoint='" + AppConfig.WorkPointCode + "' "; sql = string.Format(sql, ROUTECODE); DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0]; return dt; } catch (Exception ex) { throw ex; } } //public static bool IsIncludingISREF(string itemCODE, string routeCODE, string dsconn) //{ // FramDataContext db = new FramDataContext(dsconn); // db.Connection.Open(); // db.Transaction = db.Connection.BeginTransaction(); // try // { // var line = db.ICSITEM2ROUTE.SingleOrDefault(a => a.ITEMCODE == itemCODE && a.ROUTECODE != routeCODE && a.ISREF.Equals("1") && a.WorkPoint == AppConfig.WorkPointCode); // if (line == null) // return true; // else // return false; // } // catch (Exception ex) // { // db.Transaction.Rollback(); // throw ex; // } //} } }