|
|
using System;using System.Collections.Generic;using System.Linq;using System.Text;using ICSSoft.Base.Config.DBHelper;using System.Data;using ICSSoft.Frame.Data.Entity;using System.Data.Linq;using System.Data.SqlClient;using ICSSoft.Base.Config.AppConfig;
namespace ICSSoft.Frame.Data.DAL{
public class ICSMO2LotDAL {
#region 增加修改
public static void AddAndEdit(List<FormICSMO2LotUIModel> equipmentInfoList, string dsconn) {
FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { foreach (FormICSMO2LotUIModel equipmentInfo in equipmentInfoList) {
bool isNew = false; var line = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == equipmentInfo.LotNO); if (line == null) { isNew = true; line = new ICSITEMLot(); line.ID = AppConfig.GetGuid(); line.LotNO = equipmentInfo.LotNO;
}
line.ItemCode = equipmentInfo.ItemCode; line.TransNO = equipmentInfo.TransNO; line.TransLine = equipmentInfo.TransLine; line.VENDORITEMCODE = equipmentInfo.VENDORITEMCODE; line.VENDORCODE = equipmentInfo.VENDORCODE; line.VenderLotNO = equipmentInfo.VenderLotNO; line.PRODUCTDATE = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"); ; line.LOTQTY = equipmentInfo.LOTQTY; line.ACTIVE = "Y";//传入固定值
line.Exdate = Convert.ToDateTime("2999-12-31 00:00:00.000");//传入固定值
line.WorkPoint = AppConfig.WorkPointCode;//传入固定值
line.MUSER = equipmentInfo.MUSER; line.MUSERName = equipmentInfo.MUSERName; line.MTIME = Convert.ToDateTime(equipmentInfo.MTIME); line.TYPE = equipmentInfo.TYPE; line.EATTRIBUTE1 = equipmentInfo.EATTRIBUTE1; line.PrintTimes = equipmentInfo.PrintTimes; line.lastPrintUSERID = equipmentInfo.lastPrintUSERID; line.lastPrintTime = equipmentInfo.lastPrintTime; line.VoucherNO = equipmentInfo.VoucherNO; line.VoucherRow = equipmentInfo.VoucherRow;
line.EATTRIBUTE3 = equipmentInfo.EATTRIBUTE3; line.EATTRIBUTE6 = equipmentInfo.EATTRIBUTE6; line.EATTRIBUTE7 = equipmentInfo.EATTRIBUTE7; if (isNew) db.ICSITEMLot.InsertOnSubmit(line); db.SubmitChanges();
} //foreach (WM_BarCode barcode in barcodeList)
//{
// WM_BarCode line1 = new WM_BarCode();
// line1.guid = barcode.guid;
// line1.SrcType = barcode.SrcType;
// line1.Srcguid = barcode.Srcguid;
// line1.RCVShipguid = barcode.RCVShipguid;
// line1.BarCodeNo = barcode.BarCodeNo;
// line1.BarCodeQty = barcode.BarCodeQty;
// line1.ItemGuid = barcode.ItemGuid;
// line1.ItemCode = barcode.ItemCode;
// line1.ItemName = barcode.ItemName;
// line1.MUSERCode = barcode.MUSERCode;
// line1.MUSERName = barcode.MUSERName;
// line1.MTIME = Convert.ToDateTime(barcode.MTIME);
// line1.WorkPointCode = barcode.WorkPointCode;
// line1.BarCodeStatus = barcode.BarCodeStatus;
// //db.WM_BarCode.InsertOnSubmit(line1);
// db.SubmitChanges();
//}
db.SubmitChanges(); db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw new Exception(ex.Message); } }
public static void AddAndEditList(List<ICSITEMROUTE2OPLot> equipmentInfoList, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { int MaxSeqOnwip = 0; var lineOP = db.ICSLOTONWIP.Where(a => a.LOTNO == equipmentInfoList[0].LotNo); if (lineOP.Count() > 0) { MaxSeqOnwip = lineOP.Max(a => a.OPSEQ); } //阿威特生成请购只能在上工序完成并检验之后,不是派工完就可产生,因此工序变更时要管控次序>已请购次序
string sql = @"
SELECT ISNULL(MAX(B.OPSEQ),0) MAXOPSEQ FROM dbo.ICSMO2User A LEFT JOIN dbo.ICSITEMROUTE2OPLot B ON B.LotNo=A.LOTNO AND B.OPCODE=A.OPCODE WHERE A.LOTNO='{0}' AND A.PRLineID IS NOT NULL";
sql = string.Format(sql, equipmentInfoList[0].LotNo); int MaxSeqPR = db.ExecuteQuery<int>(sql).ToList()[0];
foreach (ICSITEMROUTE2OPLot equipmentInfo in equipmentInfoList) { var op = db.ICSITEMROUTE2OPLot.SingleOrDefault(a => a.LotNo == equipmentInfo.LotNo && a.OPCODE == equipmentInfo.OPCODE && a.ID != equipmentInfo.ID); if (op != null) { throw new Exception("工序代码：" + equipmentInfo.OPCODE + "已存在！"); } var seq = db.ICSITEMROUTE2OPLot.SingleOrDefault(a => a.LotNo == equipmentInfo.LotNo && a.OPSEQ == equipmentInfo.OPSEQ && a.ID != equipmentInfo.ID); if (seq != null) { throw new Exception("工序次序：" + equipmentInfo.OPSEQ + "已存在！"); }
if (MaxSeqOnwip >= equipmentInfo.OPSEQ) { throw new Exception("工序次序:" + MaxSeqOnwip.ToString() + ",已报工,只能在此之后添加工序"); }
if (MaxSeqPR >= equipmentInfo.OPSEQ) { throw new Exception("工序次序:" + MaxSeqPR.ToString() + ",已产生请购,只能在此之后添加工序"); }
bool isNew = false; //var line = db.ICSITEMROUTE2OPLot.SingleOrDefault(a => a.OPCODE == equipmentInfo.OPCODE && a.OPSEQ == equipmentInfo.OPSEQ && a.ROUTECODE == equipmentInfo.ROUTECODE && a.LotNo == equipmentInfo.LotNo);
var line = db.ICSITEMROUTE2OPLot.SingleOrDefault(a => a.ID == equipmentInfo.ID); if (line == null) { isNew = true; line = new ICSITEMROUTE2OPLot(); line.ID = AppConfig.GetGuid(); //line.LotNo = equipmentInfo.LotNo;
line.OPCONTROL = equipmentInfo.OPCONTROL; line.OPTIONALOP = equipmentInfo.OPTIONALOP; line.IDMERGETYPE = equipmentInfo.IDMERGETYPE; }
line.ROUTECODE = equipmentInfo.ROUTECODE; line.OPSEQ = equipmentInfo.OPSEQ; line.OPCODE = equipmentInfo.OPCODE; line.LotNo = equipmentInfo.LotNo; line.IDMERGERULE = equipmentInfo.IDMERGERULE; line.ITEMCODE = equipmentInfo.ITEMCODE; line.WorkPoint = equipmentInfo.WorkPoint;//传入固定值
line.MUSER = equipmentInfo.MUSER; line.MUSERName = equipmentInfo.MUSERName; line.MTIME = Convert.ToDateTime(equipmentInfo.MTIME); line.EATTRIBUTE1 = equipmentInfo.EATTRIBUTE1; line.CtrlType = equipmentInfo.CtrlType; line.RouteMGR = equipmentInfo.RouteMGR; line.OPAttr = equipmentInfo.OPAttr; line.EQPCODE = equipmentInfo.EQPCODE; line.OPLVL = equipmentInfo.OPLVL; line.STIME = equipmentInfo.STIME; line.RTIME = equipmentInfo.RTIME; line.WTIME = equipmentInfo.WTIME; line.ISWWRW = equipmentInfo.ISWWRW; if (isNew) db.ICSITEMROUTE2OPLot.InsertOnSubmit(line); db.SubmitChanges(); }
db.SubmitChanges(); db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw new Exception(ex.Message); } }
#endregion
#region delete
public static void delete(List<String> guidList) { FramDataContext db = new FramDataContext(AppConfig.AppConnectString); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try {
var tempLines = db.ICSMO2User.Where(a => guidList.Contains(a.LOTNO)); if (tempLines != null && tempLines.ToList().Count > 0) { throw new Exception("工单已有派工信息,无法删除!"); }
var lines = db.ICSITEMLot.Where(a => guidList.Contains(a.LotNO)); db.ICSITEMLot.DeleteAllOnSubmit(lines);
//删除 ICSITEMROUT2OPLot表中数据删除20190717ZM
var _lines = db.ICSITEMROUTE2OPLot.Where(a => guidList.Contains(a.LotNo)); db.ICSITEMROUTE2OPLot.DeleteAllOnSubmit(_lines);
db.SubmitChanges(); db.Transaction.Commit();
} catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } //删除ICSITEMROUTE2OPLot表中数据ZM
public static void deleteLot(List<String> guidList) { FramDataContext db = new FramDataContext(AppConfig.AppConnectString); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { //var tempLines = db.ICSITEMROUTE2OPLot.Where(a => guidList.Contains(a.LotNo));
//if (tempLines != null && tempLines.ToList().Count > 0)
//{
// throw new Exception("工单已有派工信息,无法删除!");
//}
var lines = db.ICSITEMROUTE2OPLot.Where(a => guidList.Contains(a.ID));
db.ICSITEMROUTE2OPLot.DeleteAllOnSubmit(lines);
db.SubmitChanges(); db.Transaction.Commit();
} catch (Exception ex) { db.Transaction.Rollback(); throw ex; } }
//删除ICSITEMROUTE2OPLot表中数据ZM
public static void deleteLot(List<ICSITEMROUTE2OPLot> guidList) { FramDataContext db = new FramDataContext(AppConfig.AppConnectString); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { foreach (var item in guidList) { var onwip = db.ICSLOTONWIP.SingleOrDefault(a => a.LOTNO == item.LotNo && a.ROUTECODE == item.ROUTECODE && a.OPSEQ == item.OPSEQ && a.OPCODE == item.OPCODE && a.WorkPoint == item.WorkPoint); if (onwip != null) { throw new Exception("已开工不能删除,工艺:" + item.ROUTECODE + ",次序:" + item.OPSEQ.ToString() + ",工序:" + item.OPCODE); } var users = db.ICSMO2User.Where(a => a.LOTNO == item.LotNo && a.RouteCode == item.ROUTECODE && a.OPCODE == item.OPCODE && a.WorkPoint == item.WorkPoint); db.ICSMO2User.DeleteAllOnSubmit(users);
var lines = db.ICSITEMROUTE2OPLot.Where(a => a.LotNo == item.LotNo && a.ROUTECODE == item.ROUTECODE && a.OPCODE == item.OPCODE && a.WorkPoint == item.WorkPoint); db.ICSITEMROUTE2OPLot.DeleteAllOnSubmit(lines); } db.SubmitChanges(); db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } finally { db.Connection.Close(); } }
#endregion
#region 工单是否已生成批次
public static bool IsIncludingInMO2Lot(string moid) {
FramDataContext db = new FramDataContext(AppConfig.AppConnectString); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { //var line = db.ICSITEMLot.Where(a => a.ID == moid);
var line = from a in db.ICSITEMLot join b in db.ICSMO on new { MOCODE = a.TransNO, MOSEQ = a.TransLine } equals new { b.MOCODE, b.MOSEQ } where b.ID == moid select a; if (line.ToList().Count > 0) return true; else return false; } catch (Exception ex) { db.Transaction.Rollback(); throw ex; }
}
public static DataTable GetMO2LotInfo(List<string> list, string wp, string conn) { string sql = @"
SELECT A.ID,A.MOCODE,A.MOSEQ,A.MOPLANQTY,A.ITEMCODE,A.MOVER,SUM(CASE WHEN ISNULL(B.TransNO,'')='' THEN 0 ELSE 1 END) AS FP FROM dbo.ICSMO A LEFT JOIN dbo.ICSITEMLot B ON A.MOCODE=B.TransNO AND A.MOSEQ=B.TransLine AND A.WorkPoint=B.WorkPointWHERE A.ID IN ('{0}') AND A.WorkPoint='{1}'GROUP BY A.ID,A.MOCODE,A.MOSEQ,A.MOPLANQTY,A.ITEMCODE,A.MOVER,CASE WHEN ISNULL(B.TransNO,'')='' THEN 0 ELSE 1 END";
string IDs = string.Join("','", list); sql = string.Format(sql, IDs, wp); DataTable dt = DBHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0]; return dt; } #endregion
public static DataTable searchForItem(string itemcode, string dsconn) {
string sql = @"select guid,ItemName
from Base_Inventory where ItemCode='{0}'";
sql = string.Format(sql, itemcode); DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
public static DataTable SelectShiftTypeCode() { string sql = @"select SHIFTTYPECODE as [班制代码]
from dbo.ICSSHIFTTYPE where 1=1";
sql = string.Format(sql); DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0]; return dt; }
public static DataTable SelectShiftTypeId(string str) { string sql = @"select ID
from dbo.ICSSHIFTTYPE where SHIFTTYPECODE='" + str + "'";
sql = string.Format(sql); DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0]; return dt; }
#region delete
public static void delete(string moid) { FramDataContext db = new FramDataContext(AppConfig.AppConnectString); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { var lines = db.ICSITEMLot.Where(a => a.ID == moid); db.ICSITEMLot.DeleteAllOnSubmit(lines);
db.SubmitChanges(); db.Transaction.Commit();
} catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion
public static DataTable GetShiftCode() { try { string sql = @"select TOP 1 [SHIFTCODE]
FROM [dbo].[ICSSHIFT] order by SHIFTCODE desc";
return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0]; } catch (Exception ex) { throw ex; }
}
public static DataTable GetShiftSeqCode() { try { string sql = @"select TOP 1 [SHIFTSEQ]
FROM [dbo].[ICSSHIFT] order by [SHIFTTYPEID] desc";
return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0]; } catch (Exception ex) { throw ex; }
}
#region 工单是否已生成批次
public static DataTable IsUsingMO(string MOCode, string MOSEQ) { FramDataContext db = new FramDataContext(AppConfig.AppConnectString); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { string sql = @"SELECT COUNT(*) FROM ICSMOPickLog
WHERE MOCODE='" + MOCode + "' AND MOSEQ='" + MOSEQ + "'";
return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0]; } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion
public static DataTable GetMO2Route(List<string> list, string wp, string conn) { // string _sql = @"
// SELECT DISTINCT
// a.ID,
// a.ITEMCODE,
// b.ROUTECODE,
// e.OPCODE,
// e.OPSEQ,
// a.MOCODE,
// a.MOSEQ,
// a.MOPLANQTY,
// e.OPAttr,
// e.CtrlType,
// e.RouteMGR
// FROM ICSMO a
// LEFT JOIN ICSMO2ROUTE b ON ( b.MOID = a.ID) AND b.WorkPoint=a.WorkPoint
// LEFT JOIN ICSITEMROUTE2OP e ON e.ROUTECODE = b.ROUTECODE AND a.ITEMCODE=e.ITEMCODE AND e.WorkPoint=a.WorkPoint
// WHERE
// a.ID in ('{0}') AND a.WorkPoint='{1}'";
// string IDs = string.Join("','", list);
// _sql = string.Format(_sql, IDs, wp);
// DataTable dt = DBHelper.ExecuteDataset(conn, CommandType.Text, _sql).Tables[0];
string sql1 = @"
SELECT DISTINCT mo.ID, mo.ITEMCODE, a.ROUTECODE, b.OPCODE, b.OPSEQ, mo.MOCODE, mo.MOSEQ, mo.MOPLANQTY, b.OPAttr, b.CtrlType, b.RouteMGR, ISNULL(c.OPCODE,'未维护') AS 工时FROM ICSMO moLEFT JOIN ICSMO2ROUTE a ON ( a.MOID = mo.ID) AND a.WorkPoint=mo.WorkPointLEFT JOIN ICSITEMROUTE2OP b ON b.ROUTECODE = a.ROUTECODE AND mo.ITEMCODE=b.ITEMCODE AND b.WorkPoint=mo.WorkPointLEFT JOIN ( SELECT a.ITEMCODE,a.OPCODE,a.EQPCODE,a.STIME,a.RTIME FROM ICSEQPSTP a WHERE ID=(SELECT MAX(ID) FROM ICSEQPSTP WHERE ITEMCODE=a.ITEMCODE AND OPCODE=a.OPCODE AND ISREF='是')) c ON b.ITEMCODE=c.ITEMCODE AND b.OPCODE=c.OPCODEWHEREmo.ID in ('{0}') AND mo.WorkPoint='{1}'";
string IDs = string.Join("','", list); sql1 = string.Format(sql1, IDs, wp); DataTable dt = DBHelper.ExecuteDataset(conn, CommandType.Text, sql1).Tables[0]; return dt; }
public static void GetMO2Route(List<ICSITEMLot> listItemLot, List<ICSITEMROUTE2OPLot> listItemRouteOpLot, string conn) { FramDataContext db = new FramDataContext(conn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { db.ICSITEMLot.InsertAllOnSubmit(listItemLot); db.SubmitChanges(); db.ICSITEMROUTE2OPLot.InsertAllOnSubmit(listItemRouteOpLot); db.SubmitChanges(); db.Transaction.Commit(); db.Connection.Close(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } }}
|
