You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
378 lines
15 KiB
378 lines
15 KiB
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
|
|
}
|
|
}
|