|
|
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;
namespace ICSSoft.Frame.Data.DAL{ public class ICSDispatchReturnDAL {
#region 新增和修改
public static void Add(List<ICSITEMLot> InfoList, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { foreach (ICSITEMLot info in InfoList) { var inv = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == info.ItemCode && a.WorkPoint == AppConfig.WorkPointCode); if (inv == null) throw new Exception("物料不存在！");
var lines = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == info.LotNO && a.WorkPoint == AppConfig.WorkPointCode); if (lines != null) throw new Exception("条码已存在！");
bool isNew = false; var line = db.ICSITEMLot.SingleOrDefault(a => a.ID == info.ID && a.WorkPoint == AppConfig.WorkPointCode); if (line == null) { isNew = true; line = new ICSITEMLot(); line.ID = AppConfig.GetGuid(); } line.LotNO = info.LotNO; line.ItemCode = info.ItemCode; line.TransNO = info.TransNO; line.TransLine = info.TransLine; line.VENDORITEMCODE = info.VENDORITEMCODE; line.VENDORCODE = info.VENDORCODE; line.VenderLotNO = info.VenderLotNO; line.PRODUCTDATE = info.PRODUCTDATE; line.LOTQTY = info.LOTQTY; line.EATTRIBUTE3 = info.EATTRIBUTE3;//比率
line.EATTRIBUTE4 = 0; line.ACTIVE = info.ACTIVE; line.Exdate = info.Exdate; line.TYPE = "销售退货";
line.MUSER = AppConfig.UserCode; line.MUSERName = AppConfig.UserName; line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"); line.WorkPoint = AppConfig.WorkPointCode; if (isNew) { db.ICSITEMLot.InsertOnSubmit(line); }
db.SubmitChanges();
}
db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion
#region 退货条码新增和修改
public static void AddInv(List<ICSITEMLot> InfoList, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { foreach (ICSITEMLot info in InfoList) { var inv = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == info.ItemCode && a.WorkPoint == AppConfig.WorkPointCode); if (inv == null) throw new Exception("物料不存在！");
var lines = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == info.LotNO && a.WorkPoint == AppConfig.WorkPointCode); if (lines != null) throw new Exception("条码已存在！");
bool isNew = false; var line = db.ICSITEMLot.SingleOrDefault(a => a.ID == info.ID && a.WorkPoint == AppConfig.WorkPointCode); if (line == null) { isNew = true; line = new ICSITEMLot(); line.ID = AppConfig.GetGuid(); } line.LotNO = info.LotNO; line.ItemCode = info.ItemCode; line.TransNO = info.TransNO; line.TransLine = info.TransLine; line.VENDORITEMCODE = info.VENDORITEMCODE; line.VENDORCODE = info.VENDORCODE; line.VenderLotNO = info.VenderLotNO; line.PRODUCTDATE = info.PRODUCTDATE; line.LOTQTY = info.LOTQTY; line.EATTRIBUTE3 = info.LOTQTY; line.EATTRIBUTE4 = 0; line.ACTIVE = info.ACTIVE; line.Exdate = info.Exdate; line.TYPE = info.TYPE; line.MUSER = AppConfig.UserCode; line.MUSERName = AppConfig.UserName; line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"); line.WorkPoint = AppConfig.WorkPointCode;
if (isNew) { db.ICSITEMLot.InsertOnSubmit(line); }
db.SubmitChanges();
}
db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion
#region 更新打印信息
public static void updatePrint(List<ICSITEMLot> InfoList, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { foreach (ICSITEMLot info in InfoList) { var line = db.ICSITEMLot.SingleOrDefault(a => a.LotNO == info.LotNO && a.WorkPoint == AppConfig.WorkPointCode); if (line == null) { continue; } if (line.PrintTimes == null) { line.PrintTimes = 1; } else { line.PrintTimes++; } line.lastPrintUSERID = info.lastPrintUSERID; line.lastPrintTime = info.lastPrintTime;
db.SubmitChanges();
}
db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion
#region 通过代码、行号查询
public static FormICSDispatchReturnUIModel SearchInfo(string CODE,string RowNo, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); try { FormICSDispatchReturnUIModel info = new FormICSDispatchReturnUIModel();
info.reware = db.ICSSODispatch.SingleOrDefault(a => a.DispatchCode == CODE && a.DispatchRow == RowNo && a.WorkPoint == AppConfig.WorkPointCode); if (info.reware != null) info.inventory = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == info.reware.InvCode && a.WorkPoint == AppConfig.WorkPointCode);
if (info.inventory == null) throw new Exception("物料不存在！");
var infos = db.ICSITEMLot.Where(a => a.TransNO == CODE && a.TransLine == RowNo&& a.WorkPoint == AppConfig.WorkPointCode); info.SumQty = Convert.ToDecimal(infos.AsEnumerable().Sum(a => a.LOTQTY)); info.SumNo = infos.Count(); var infoMaxs = db.ICSITEMLot.Where(a => a.LotNO.Contains(info.reware.DispatchCode + info.reware.DispatchRow) && a.WorkPoint == AppConfig.WorkPointCode); if (infoMaxs.Count() == 0) info.MaxNo = 0; else { string lotno = infoMaxs.OrderByDescending(a => a.LotNO.Substring(a.LotNO.Length - 5)).FirstOrDefault().LotNO; info.MaxNo =Convert.ToInt32(lotno.Substring(lotno.Length - 5)); } if (infos != null && infos.Count() > 0) { var VenderLotNO = infos.Where(a => a.PRODUCTDATE == AppConfig.GetSeverDateTime("yyyy-MM-dd")).Select(a => a.VenderLotNO).Distinct(); if (VenderLotNO != null && VenderLotNO.Count()>0) { info.VenderLotNO = VenderLotNO.ToList()[0]; return info; } } var infoTodays = db.ICSITEMLot.Where(a => a.WorkPoint == AppConfig.WorkPointCode && a.PRODUCTDATE == AppConfig.GetSeverDateTime("yyyy-MM-dd")).Select(a => a.TransNO); info.TodayMOCount = infoTodays.Distinct().Count();
return info; } catch (Exception ex) { throw ex; }
} #endregion
#region 通过物料查询
public static FormICSDispatchReturnUIModel SearchInfoInv(string CODE, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); try { FormICSDispatchReturnUIModel info = new FormICSDispatchReturnUIModel();
info.inventory = db.ICSINVENTORY.SingleOrDefault(a => a.INVCODE == CODE && a.WorkPoint == AppConfig.WorkPointCode);
if (info.inventory == null) throw new Exception("物料不存在！");
var infos = db.ICSITEMLot.Where(a => a.TransNO == CODE && a.WorkPoint == AppConfig.WorkPointCode); string time = AppConfig.GetSeverDateTime("yyyy-MM-dd").ToString("yyyyMMdd"); time = "RP" + time.Substring(2); var infoMaxs = db.ICSITEMLot.Where(a => a.LotNO.Contains(time) && a.WorkPoint == AppConfig.WorkPointCode); if (infoMaxs.Count() == 0) info.MaxNo = 0; else { string lotno = infoMaxs.OrderByDescending(a => a.LotNO.Substring(a.LotNO.Length - 4)).FirstOrDefault().LotNO; info.MaxNo = Convert.ToInt32(lotno.Substring(lotno.Length - 4)); }
if (infos != null && infos.Count() > 0) { var VenderLotNO = infos.Where(a => a.PRODUCTDATE == AppConfig.GetSeverDateTime("yyyy-MM-dd")).Select(a => a.VenderLotNO).Distinct(); if (VenderLotNO != null && VenderLotNO.Count() > 0) { info.VenderLotNO = VenderLotNO.ToList()[0]; return info; } } var infoTodays = db.ICSITEMLot.Where(a => a.WorkPoint == AppConfig.WorkPointCode && a.PRODUCTDATE == AppConfig.GetSeverDateTime("yyyy-MM-dd")).Select(a => a.TransNO); info.TodayMOCount = infoTodays.Distinct().Count();
return info; } catch (Exception ex) { throw ex; }
} #endregion
#region 通过主表行和子表
public static DataSet SearchData(string CODE, string RowNo, string dsconn) { try { string sql = @"SELECT
'' as MoIsSelect, b.INVCODE as ITEMCODE, b.INVNAME, b.INVSTD, b.INVUOM as UNIT, a.DispatchCode as DispatchCode, a.DispatchRow as DispatchRow, a.MUSERName as Muser, a.MTIME as MTime, a.cBatch as MOVER --CAST(CASE WHEN d.INVCode IS NOT NULL THEN '1' ELSE '0' END AS bit) AS InPut FROM ICSSODispatch a LEFT JOIN ( SELECT i.INVCode, c.TransNO, c.TransLine FROM ICSITEMLot c inner JOIN ICSWareHouseLotInfo i ON c.LotNO=i.LotNO GROUP BY c.TransNO,c.TransLine,i.INVCode) d ON d.TransNO=a.DispatchCode and d.TransLine=a.DispatchRow INNER JOIN ICSINVENTORY b ON a.InvCode=b.INVCODE WHERE a.DispatchCode='{0}' AND a.DispatchRow='{1}' AND a.WorkPoint = '{2}' AND a.Type='退货'";
sql += @"SELECT
CASE WHEN ISNULL(a.PrintTimes,0) <=0 THEN 'Y' ELSE '' END AS isSelect, 'Y' AS BarCodeIdentify1 , a.ID, b.DispatchCode as MOCODE, b.DispatchRow as MOSEQ, a.TransNO, a.TransLine, a.LOTNO as LotNo, a.TYPE as Type, CAST(CASE WHEN c.LotNO IS NOT NULL THEN '1' ELSE '0' END AS BIT) AS isInput, a.LOTQTY, a.PrintTimes, a.VenderLotNO, a.PRODUCTDATE, a.MUSERName, d.INVUOM FROM ICSITEMLot a LEFT JOIN ICSSODispatch b ON a.TransNO=b.DispatchCode AND a.TransLine=b.DispatchRow AND a.WorkPoint=b.WorkPoint LEFT JOIN ICSWareHouseLotInfo c ON a.LOTNO=c.LotNO AND a.WorkPoint=c.WorkPoint INNER JOIN ICSINVENTORY d ON b.InvCode=d.INVCODE WHERE a.TransNO = '{0}' AND a.TransLine = '{1}' AND a.WorkPoint = '{2}' AND ISNULL(a.EATTRIBUTE2, '') <> 'FP' ORDER BY a.LOTNO ";
sql = string.Format(sql,CODE,RowNo,AppConfig.WorkPointCode); DataSet ds = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql); return ds; } catch (Exception ex) { 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.ICSITEMLot.Where(a => codeList.Contains(a.ID)); db.ICSITEMLot.DeleteAllOnSubmit(lines); db.SubmitChanges(); db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; }
} #endregion
}}
|
