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.
495 lines
21 KiB
495 lines
21 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using ICSSoft.Frame.Data.Entity;
|
|
using ICSSoft.Base.Config.AppConfig;
|
|
using System.Data;
|
|
using ICSSoft.Base.Config.DBHelper;
|
|
using System.Data.SqlClient;
|
|
|
|
namespace ICSSoft.Frame.Data.DAL
|
|
{
|
|
public class ICSITEMLOTDAL
|
|
{
|
|
|
|
#region 新增和修改
|
|
public static void Add(ICSITEMLot tbinfo, string dsconn)
|
|
{
|
|
FramDataContext db = new FramDataContext(dsconn);
|
|
db.Connection.Open();
|
|
db.Transaction = db.Connection.BeginTransaction();
|
|
try
|
|
{
|
|
bool isNew = false;
|
|
var line = db.ICSITEMLot.SingleOrDefault(a => a.ID == tbinfo.ID);
|
|
if (line == null)
|
|
{
|
|
isNew = true;
|
|
line = new ICSITEMLot();
|
|
line.ID = tbinfo.ID;
|
|
|
|
}
|
|
line.LotNO = tbinfo.LotNO;
|
|
line.ItemCode = tbinfo.ItemCode;
|
|
line.TransNO = tbinfo.TransNO;
|
|
line.TransLine = tbinfo.TransLine;
|
|
line.VENDORITEMCODE = tbinfo.VENDORITEMCODE;
|
|
line.VENDORCODE = tbinfo.VENDORCODE;
|
|
line.VenderLotNO = tbinfo.VenderLotNO;
|
|
line.PRODUCTDATE = tbinfo.PRODUCTDATE;
|
|
line.LOTQTY = tbinfo.LOTQTY;
|
|
line.ACTIVE = tbinfo.ACTIVE;
|
|
line.Exdate = tbinfo.Exdate;
|
|
line.WorkPoint = tbinfo.WorkPoint;
|
|
line.MUSER = tbinfo.MUSER;
|
|
line.MUSERName = tbinfo.MUSERName;
|
|
line.MTIME = tbinfo.MTIME;
|
|
if (isNew)
|
|
db.ICSITEMLot.InsertOnSubmit(line);
|
|
db.SubmitChanges();
|
|
|
|
|
|
|
|
db.Transaction.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
db.Transaction.Rollback();
|
|
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
|
|
|
|
//详细表 增加
|
|
public static void DetailADD(ICSITEMLotDetail tbinfo, string dsconn)
|
|
{
|
|
FramDataContext db = new FramDataContext(dsconn);
|
|
db.Connection.Open();
|
|
db.Transaction = db.Connection.BeginTransaction();
|
|
try
|
|
{
|
|
bool isNew = false;
|
|
var line = db.ICSITEMLotDetail.SingleOrDefault(a => a.ID == tbinfo.ID);
|
|
if (line == null)
|
|
{
|
|
isNew = true;
|
|
line = new ICSITEMLotDetail();
|
|
line.ID = tbinfo.ID;
|
|
|
|
}
|
|
line.SERIALNO = tbinfo.SERIALNO;
|
|
line.LotNO = tbinfo.LotNO;
|
|
line.ItemCode = tbinfo.ItemCode;
|
|
line.StorageCODE = tbinfo.StorageCODE;
|
|
line.StackCODE = tbinfo.StackCODE;
|
|
line.SerialStatus = tbinfo.SerialStatus;
|
|
line.WorkPoint = tbinfo.WorkPoint;
|
|
line.MUSER = tbinfo.MUSER;
|
|
line.MUSERName = tbinfo.MUSERName;
|
|
line.MTIME = tbinfo.MTIME;
|
|
|
|
|
|
if (isNew)
|
|
db.ICSITEMLotDetail.InsertOnSubmit(line);
|
|
db.SubmitChanges();
|
|
|
|
|
|
|
|
db.Transaction.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
db.Transaction.Rollback();
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
//详细表 删除
|
|
public static void DetaildeleteInfo(List<string> codeList, string dsconn)
|
|
{
|
|
FramDataContext db = new FramDataContext(dsconn);
|
|
db.Connection.Open();
|
|
db.Transaction = db.Connection.BeginTransaction();
|
|
try
|
|
{
|
|
var lines = db.ICSITEMLotDetail.Where(a => codeList.Contains(a.SERIALNO));
|
|
db.ICSITEMLotDetail.DeleteAllOnSubmit(lines);
|
|
db.SubmitChanges();
|
|
db.Transaction.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
db.Transaction.Rollback();
|
|
throw ex;
|
|
}
|
|
|
|
}
|
|
|
|
|
|
public static void insert(string NO, string code, int count)
|
|
{
|
|
string sql = @"update ICSINVReceiptDetail set ACTQTY+='" + count + "' where ITEMCODE='" + code + "' and ReceiptNO='" + NO + "' and WorkPoint='" + AppConfig.WorkPointCode + "'";
|
|
sql = string.Format(sql);
|
|
DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql);
|
|
}
|
|
|
|
public static void delete(string NO, string code, int count)
|
|
{
|
|
string sql = @"update ICSINVReceiptDetail set ACTQTY-='" + count + "' where ITEMCODE='" + code + "' and ReceiptNO='" + NO + "' and WorkPoint='" + AppConfig.WorkPointCode + "'";
|
|
sql = string.Format(sql);
|
|
DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql);
|
|
}
|
|
/// <summary>
|
|
/// 获取条码状态
|
|
/// </summary>
|
|
/// <param name="lotNo"></param>
|
|
/// <param name="workpointCode"></param>
|
|
/// <returns></returns>
|
|
public static DataTable CheckLotNO(string lotNo, string workpointCode, string dsconn)
|
|
{
|
|
string sql = @"select A.LotNO,TYPE,B.LotNO AS BarCode from ICSITEMLot A
|
|
LEFT JOIN ICSWareHouseLotInfo B
|
|
ON B.LotNO=A.LotNO
|
|
where A.LotNO='{0}' and A.WorkPoint='{1}'";
|
|
sql = string.Format(sql, lotNo, workpointCode);
|
|
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
|
|
return dt;
|
|
}
|
|
/// <summary>
|
|
/// 验证ASN单信息
|
|
/// </summary>
|
|
/// <param name="lotNo"></param>
|
|
/// <param name="workpointCode"></param>
|
|
/// <returns></returns>
|
|
public static DataTable CheckASNNO(string lotNo, string workpointCode, string dsconn)
|
|
{
|
|
string sql = @"select * from ICSPO_PoMain
|
|
where POCode='{0}' and WorkPoint='{1}'";
|
|
sql = string.Format(sql, lotNo, workpointCode);
|
|
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
|
|
return dt;
|
|
}
|
|
/// <summary>
|
|
/// 验证编码信息
|
|
/// </summary>
|
|
/// <param name="lotNo"></param>
|
|
/// <param name="workpointCode"></param>
|
|
/// <returns></returns>
|
|
public static DataTable CheckLOTNum(string INVCode, string workpointCode, string dsconn)
|
|
{
|
|
string sql = @"select A.INVCODE,B.WHCode,B.BinCode from ICSINVENTORY A
|
|
LEFT JOIN ICSWareHouseLotInfo B
|
|
ON B.INVCode=A.INVCODE
|
|
WHERE A.INVCODE='{0}' AND A.WorkPoint='{1}'";
|
|
sql = string.Format(sql, INVCode, workpointCode);
|
|
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
|
|
return dt;
|
|
}
|
|
/// <summary>
|
|
/// 获取条码信息
|
|
/// </summary>
|
|
/// <param name="barCode"></param>
|
|
/// <param name="workpointCode"></param>
|
|
/// <returns></returns>
|
|
public static DataTable GetLotData(string barCode, string workpointCode, string dsconn)
|
|
{
|
|
string sql = @"select A.TransNO,
|
|
A.TransLine,
|
|
A.LotNO,
|
|
A.ItemCode,
|
|
C.INVNAME,
|
|
C.INVSTD,
|
|
C.INVEXPORTIMPORT,
|
|
CONVERT(DECIMAL(18,2),A.LOTQTY) as LOTQTY,
|
|
C.INVUOM,
|
|
A.EATTRIBUTE2,
|
|
D.EATTRIBUTE6,
|
|
ISNULL(A.EATTRIBUTE4,0) AS EATTRIBUTE3
|
|
from ICSITEMLot A
|
|
LEFT JOIN ICSINVENTORY C
|
|
ON C.INVCODE=A.ItemCode
|
|
LEFT JOIN ICSINVInfo D
|
|
ON D.INVCODE=A.ItemCode
|
|
--WHERE A.LotNO='0000000001100002' AND A.WorkPoint='6000'
|
|
WHERE A.LotNO='{0}' and A.WorkPoint='{1}'";
|
|
sql = string.Format(sql, barCode, workpointCode);
|
|
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
|
|
return dt;
|
|
}
|
|
/// <summary>
|
|
/// 获取条码信息
|
|
/// </summary>
|
|
/// <param name="barCode"></param>
|
|
/// <param name="workpointCode"></param>
|
|
/// <returns></returns>
|
|
public static DataTable GetLotDataForPO(string barCode, string workpointCode, string dsconn)
|
|
{
|
|
string sql = @"SELECT A.TransNO, A.TransLine, A.LotNO, A.ItemCode, C.INVNAME, C.INVSTD, C.INVEXPORTIMPORT, CONVERT(DECIMAL(18,2),A.LOTQTY) AS LOTQTY,
|
|
C.INVUOM, A.EATTRIBUTE2, D.EATTRIBUTE6, ISNULL(A.EATTRIBUTE4, 0) AS EATTRIBUTE3, B.Batch, B.cWhCode FROM ICSITEMLot A
|
|
INNER JOIN ICSPOArrive B ON A.TransNO = B.cCode AND A.TransLine = B.irowno LEFT JOIN ICSINVENTORY C ON C.INVCODE = A.ItemCode
|
|
LEFT JOIN ICSINVInfo D ON D.INVCODE = A.ItemCode WHERE A.LotNO = '{0}' AND A.WorkPoint = '{1}' ";
|
|
sql = string.Format(sql, barCode, workpointCode);
|
|
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
|
|
return dt;
|
|
}
|
|
/// <summary>
|
|
/// 获取条码信息（半成品入库）
|
|
/// </summary>
|
|
/// <param name="barCode"></param>
|
|
/// <param name="workpointCode"></param>
|
|
/// <param name="dsconn"></param>
|
|
/// <returns></returns>
|
|
public static DataTable GetLotDataForMO(string barCode, string workpointCode, string dsconn)
|
|
{
|
|
string sql = @"select A.MOCODE
|
|
,A.MOSEQ
|
|
,A.ITEMCODE
|
|
,C.INVNAME
|
|
,C.INVSTD
|
|
,C.INVUOM
|
|
,B.LotQty
|
|
,B.LotNO
|
|
,B.VenderLotNO
|
|
from ICSMO A
|
|
LEFT JOIN ICSITEMLot B
|
|
ON B.TransNO=A.MOCODE AND B.TransLine=A.MOSEQ
|
|
LEFT JOIN ICSINVENTORY C
|
|
ON C.INVCODE=A.ITEMCODE
|
|
WHERE B.LotNO='{0}' AND A.WorkPoint='{1}'";
|
|
sql = string.Format(sql, barCode, workpointCode);
|
|
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
|
|
return dt;
|
|
}
|
|
/// <summary>
|
|
/// 获取编码信息
|
|
/// </summary>
|
|
/// <param name="INVCode"></param>
|
|
/// <param name="workpointCode"></param>
|
|
/// <returns></returns>
|
|
public static DataTable GetLotNumData(string INVCode, string workpointCode, string dsconn)
|
|
{
|
|
string sql = @"select A.TransNO,
|
|
A.TransLine,
|
|
A.LotNO,
|
|
A.ItemCode,
|
|
C.INVNAME,
|
|
C.INVSTD,
|
|
A.LOTQTY,
|
|
C.INVUOM
|
|
from ICSWareHouseLotInfo D
|
|
LEFT JOIN ICSITEMLot A
|
|
ON A.LotNO=D.LotNO
|
|
LEFT JOIN ICSPO_PoMain B
|
|
ON B.POCode=A.TransNO AND B.PORow=A.TransLine
|
|
LEFT JOIN ICSINVENTORY C
|
|
ON C.INVCODE=A.ItemCode
|
|
WHERE D.INVCode='{0}' AND A.WorkPoint='{1}'";
|
|
sql = string.Format(sql, INVCode, workpointCode);
|
|
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
|
|
return dt;
|
|
}
|
|
/// <summary>
|
|
/// 获取ASN单号信息（半成品入库）
|
|
/// </summary>
|
|
/// <param name="barCode"></param>
|
|
/// <param name="workpointCode"></param>
|
|
/// <param name="dsconn"></param>
|
|
/// <returns></returns>
|
|
public static DataTable GetASNDataForMO(string barCode, string workpointCode, string dsconn)
|
|
{
|
|
string sql = @"select A.MOCODE
|
|
,A.MOSEQ
|
|
,A.ITEMCODE
|
|
,C.INVNAME
|
|
,C.INVSTD
|
|
,A.MOPLANQTY
|
|
,A.MOINPUTQTY
|
|
,C.INVUOM
|
|
,B.LotQty
|
|
,B.LotNO
|
|
from ICSMO A
|
|
LEFT JOIN ICSITEMLot B
|
|
ON B.TransNO=A.MOCODE AND B.TransLine=A.MOSEQ
|
|
LEFT JOIN ICSINVENTORY C
|
|
ON C.INVCODE=A.ITEMCODE
|
|
LEFT JOIN ICSASNDetail E
|
|
ON E.LotNO=B.LotNO
|
|
WHERE E.STNO='{0}' AND A.WorkPoint='{1}'";
|
|
sql = string.Format(sql, barCode, workpointCode);
|
|
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
|
|
return dt;
|
|
}
|
|
|
|
#region 生成到货单
|
|
|
|
public static string CreateRCV(List<FormICSRSVDataModel.RCVData> rcv, string connection, out string docno, out string createUser, out string createTime)
|
|
{
|
|
SqlConnection conn = new System.Data.SqlClient.SqlConnection(connection);
|
|
conn.Open();
|
|
SqlTransaction sqlTran = conn.BeginTransaction();
|
|
SqlCommand cmd = new SqlCommand();
|
|
cmd.Transaction = sqlTran;
|
|
cmd.Connection = conn;
|
|
//DataContextDataContext data = new DataContextDataContext();
|
|
try
|
|
{
|
|
string rcvNo = "";
|
|
string user = "";
|
|
string time1 = "";
|
|
|
|
foreach (FormICSRSVDataModel.RCVData data in rcv)
|
|
{
|
|
string time = DateTime.Now.ToString("yyyyMMdd");
|
|
string sql = @"select max(ReceiptNO) from ICSINVReceipt where left(ReceiptNO,8)='{0}'";
|
|
sql = string.Format(sql, time);
|
|
DataTable dt = SQlReturnData(sql, cmd);
|
|
if (dt != null && dt.Rows.Count > 0 && !string.IsNullOrEmpty(dt.Rows[0][0].ToString()))
|
|
{
|
|
rcvNo = (decimal.Parse(dt.Rows[0][0].ToString()) + 1).ToString();
|
|
}
|
|
else
|
|
{
|
|
rcvNo = time + "001";
|
|
}
|
|
string rcvId = BitConverter.ToInt64(Guid.NewGuid().ToByteArray(), 0).ToString();
|
|
|
|
|
|
sql = @"INSERT INTO ICSINVReceipt(ID,ReceiptNO,StorageID,RECSTATUS,VENDORCODE,RECTYPE,MEMO,CreateTIME,CreateUSER,WorkPoint,MUSER,MUSERName,MTIME,EATTRIBUTE1,ISALLINSTORAGE)
|
|
Values(@ID,@ReceiptNO,'','待检','','','',@CreateTIME,@CreateUSER,'0001','',@MUSERName,@MTIME,@ASN,'N')";
|
|
cmd.Parameters.AddWithValue("@ID", rcvId);
|
|
cmd.Parameters.AddWithValue("@ReceiptNO", rcvNo);
|
|
cmd.Parameters.AddWithValue("@CreateTIME", data.createTime);
|
|
cmd.Parameters.AddWithValue("@CreateUSER", data.userName);
|
|
//cmd.Parameters.AddWithValue("@MUSER", rcvNo);
|
|
cmd.Parameters.AddWithValue("@MUSERName", data.userName);
|
|
cmd.Parameters.AddWithValue("@MTIME", DateTime.Now);
|
|
if (data.asnNo != null)
|
|
{
|
|
cmd.Parameters.AddWithValue("@ASN", data.asnNo);
|
|
}
|
|
else
|
|
{
|
|
cmd.Parameters.AddWithValue("@ASN", "");
|
|
}
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
user = data.userName;
|
|
time1 = data.createTime;
|
|
|
|
int row = 0;
|
|
foreach (FormICSRSVDataModel.RCVDatas datas in data.datas)
|
|
{
|
|
sql = @"select ReceiptNO from dbo.ICSINVReceiptDetail WHERE VenderLotNO='{0}'";
|
|
sql = string.Format(sql, datas.lotNo);
|
|
dt = SQlReturnData(sql, cmd);
|
|
if (dt != null && dt.Rows.Count > 0 && !string.IsNullOrEmpty(dt.Rows[0][0].ToString()))
|
|
{
|
|
throw new Exception("条码:" + datas.lotNo + " 已生成到货单!");
|
|
}
|
|
|
|
row++;
|
|
|
|
string checksql = @"select EATTRIBUTE6 from ICSINVInfo
|
|
where INVCODE='{0}'";
|
|
checksql = string.Format(checksql, datas.itemCode);
|
|
DataTable checkdt = SQlReturnData(sql, cmd);
|
|
string rcvsId = BitConverter.ToInt64(Guid.NewGuid().ToByteArray(), 0).ToString();
|
|
if (checkdt.Rows.Count == 0)
|
|
{
|
|
sql = @"INSERT INTO ICSINVReceiptDetail(ID,ReceiptID,ReceiptNO,ReceiptLine,RECSTATUS,IQCStatus,MEMO,ITEMCODE,PLANQTY,VenderLotNO,WorkPoint,MUSER,MUSERName,MTIME,ISINSTORAGE,TYPE)
|
|
Values('{0}','{1}','{2}','{3}','待检','待检','','{4}','{5}','{6}','0001','','{7}','{8}','N','原材料')";
|
|
}
|
|
else
|
|
{
|
|
if (checkdt.Rows[0]["EATTRIBUTE6"].ToString() == "免检")
|
|
{
|
|
sql = @"INSERT INTO ICSINVReceiptDetail(ID,ReceiptID,ReceiptNO,ReceiptLine,RECSTATUS,IQCStatus,MEMO,ITEMCODE,PLANQTY,VenderLotNO,WorkPoint,MUSER,MUSERName,MTIME,ISINSTORAGE,TYPE)
|
|
Values('{0}','{1}','{2}','{3}','免检','免检','','{4}','{5}','{6}','0001','','{7}','{8}','N','原材料')";
|
|
}
|
|
else
|
|
{
|
|
sql = @"INSERT INTO ICSINVReceiptDetail(ID,ReceiptID,ReceiptNO,ReceiptLine,RECSTATUS,IQCStatus,MEMO,ITEMCODE,PLANQTY,VenderLotNO,WorkPoint,MUSER,MUSERName,MTIME,ISINSTORAGE,TYPE)
|
|
Values('{0}','{1}','{2}','{3}','待检','待检','','{4}','{5}','{6}','0001','','{7}','{8}','N','原材料')";
|
|
}
|
|
}
|
|
sql = string.Format(sql, rcvsId, rcvId, rcvNo, row, datas.itemCode, datas.qty, datas.lotNo, data.userName, DateTime.Now);
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
#region 判断是否需要解除箱号关联
|
|
sql = @"select * from ICSPalletLotNO
|
|
where LotNO='{0}'";
|
|
string.Format(sql, datas.lotNo);
|
|
dt = SQlReturnData(sql, cmd);
|
|
if (dt.Rows.Count != 0)
|
|
{
|
|
sql = @"Delete ICSPalletLotNO where LotNO='{0}'";
|
|
string.Format(sql, datas.lotNo);
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
#endregion
|
|
|
|
}
|
|
}
|
|
sqlTran.Commit();
|
|
docno = rcvNo;
|
|
createUser = user;
|
|
createTime = time1;
|
|
|
|
return "OK";
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
sqlTran.Rollback();
|
|
docno = "";
|
|
createUser = "";
|
|
createTime = "";
|
|
return ex.Message;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
/// <summary>
|
|
/// SQL执行方法
|
|
/// </summary>
|
|
/// <param name="SQl"></param>
|
|
/// <param name="cmd"></param>
|
|
/// <returns></returns>
|
|
public static DataTable SQlReturnData(string SQl, SqlCommand cmd)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
cmd.CommandText = SQl;
|
|
SqlDataAdapter dr = new System.Data.SqlClient.SqlDataAdapter();
|
|
dr.SelectCommand = cmd;
|
|
dr.Fill(dt);
|
|
return dt;
|
|
}
|
|
}
|
|
|
|
|
|
}
|