|
|
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<FormICSITEM2ROUTEUIModel> 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<ICSITEMROUTE2OP> listmodel = new List<ICSITEMROUTE2OP>(); // 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<FormICSINVENTORYUIModel> SearchItemInfoList(string dsconn, string invcode) { try { List<FormICSINVENTORYUIModel> returntype = new List<FormICSINVENTORYUIModel>(); 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<FormICSROUTEUIModel> SearchRouteInfoList(string dsconn) { try { List<FormICSROUTEUIModel> returntype = new List<FormICSROUTEUIModel>(); 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<FormICSITEM2ROUTEUIModel> 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] AINNER JOIN dbo.ICSROUTE2OP Old ON Old.ROUTECODE=A.EATTRIBUTE1 AND Old.OPCODE=A.OPCODEINNER 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<FormICSITEM2ROUTEUIModel> ITEM2ROUTEInfoList, FramDataContext db) { try { string routecode; foreach (FormICSITEM2ROUTEUIModel info in ITEM2ROUTEInfoList) { List<ICSITEMROUTE2OP> listmodel = new List<ICSITEMROUTE2OP>(); 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<ICSROUTE2OP>(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<FormICSITEM2ROUTEUIModel> 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<ICSITEMROUTE2OP> listmodel = new List<ICSITEMROUTE2OP>(); 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<FormICSITEM2ROUTEUIModel> SearchInfoByCode(string itemCode, string routeCode, string dsconn) { List<FormICSITEM2ROUTEUIModel> returnir = new List<FormICSITEM2ROUTEUIModel>(); 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;
// }
//}
}}
|
