hanfu
/
RuiTengJb
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
锐腾搅拌上料功能
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.
3 Commits
1 Branch
0 Tags
3.3 MiB
 
Branch: master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/ICSWareHouseLotInfoDAL.cs

4176 lines
203 KiB
Raw Permalink Blame History

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;
using Newtonsoft.Json;
using System.Net;
using System.IO;
using ICSSoft.Entity;
using ICSSoft.Entity.mom_order;
using ICSSoft.Entity.Dispatchlist;
using ICSSoft.Entity.PU_ArrivalVouch;
using ICSSoft.Entity.TransVouch;
using ICSSoft.Entity.mom_orderdetail;
namespace ICSSoft.Frame.Data.DAL
{
public class ICSWareHouseLotInfoDAL
{
static string message = System.Configuration.ConfigurationSettings.AppSettings["SysIsOpen"].ToString();
static string APIURL = System.Configuration.ConfigurationSettings.AppSettings["APIURL"].ToString();
static string IStockIN = APIURL + "APICreateRdrecord01";
static string IInvOut = APIURL + "APICreateRdrecord11";
//static string IInvOutPick = APIURL + "APICreateRdrecord11ByMaterial";
static string IInvTrans = APIURL + "APIConfimTranVouch";
//static string IInvTransMO = APIURL + "APICreateTransVouch";
static string IDispatch = APIURL + "APICreateRdrecord32";
static string IStockINForMO = APIURL + "APICreateRdrecord10";
static string IOtherOut = ""; //System.Configuration.ConfigurationSettings.AppSettings["IOtherOut"].ToString();
/// <summary>
/// 获取货位信息
/// </summary>
/// <param name="StackCode"></param>
/// <param name="workpointCode"></param>
/// <returns></returns>
public static DataTable GetBinCode(string LotNO, string WorkPoint, string dsconn)
{
string sql = @"SELECT B.BinCode FROM ICSWareHouseInfo B LEFT JOIN ICSITEMLot A ON A.ItemCode = B.INVCode
WHERE A.LotNO = '{0}' AND A.WorkPoint = '{1}' ORDER BY B.QTY DESC ";
sql = string.Format(sql, LotNO, WorkPoint);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 验证库位信息
/// </summary>
/// <param name="StackCode"></param>
/// <param name="workpointCode"></param>
/// <returns></returns>
public static DataTable CheckStackCode(string StackCode, string workpointCode, string dsconn)
{
string sql = @"select A.StackCode,B.StorageCode from ICSStack A
LEFT JOIN ICSStorage B
ON B.Serial=A.Storage_Serial
WHERE A.StackCode='{0}' AND A.WorkPoint='{1}'";
sql = string.Format(sql, StackCode, 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 CheckLotNoForStockIn(string lotNo, string workpointCode, string dsconn)
{
string sql = @"
select A.LotNO,A.TYPE,
'' AS LogLot,
D.LotNO AS BarCode,
A.EATTRIBUTE2,
C.cInvCode from ICSITEMLot A
LEFT JOIN ICSPOArrive C
ON A.TransNO=C.cCode AND A.TransLine=C.irowno
LEFT JOIN ICSWareHouseLotInfo D
ON D.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>
/// 验证条码信息（半成品/成品上架）
/// </summary>
/// <param name="lotNo"></param>
/// <param name="workpointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckLotNoForMOIn(string lotNo, string workpointCode, string dsconn)
{
string sql = @"select A.LotNO,A.TYPE,D.LotNO AS BarCode,A.TransNO,B.MOCODE,E.ISCOM,G.Result,G.TResult from ICSITEMLot A
LEFT JOIN ICSMO B
ON B.MOCODE=A.TransNO AND B.MOSEQ=A.TransLine
LEFT JOIN ICSWareHouseLotInfo D
ON D.LotNO=A.LotNO
LEFT JOIN ICSLOTSIMULATION E
ON E.LOTNO=A.LotNO
LEFT JOIN ICSLOTONWIP F
ON F.LOTNO=E.LOTNO AND F.OPCODE=E.OPCODE
LEFT JOIN ICSLOTONWIPCheck G
ON G.ONWIPID=F.ID
where A.LotNO='{0}' and A.WorkPoint='{1}' AND A.TYPE='工单'";
sql = string.Format(sql, lotNo, workpointCode);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 采购入库
/// </summary>
/// <param name="barcodeList"></param>
/// <param name="bincodeList"></param>
/// <param name="wrokpointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static bool StockIn(SqlConnection sqlConnection, SqlTransaction trans, string[] barcodeList, string[] bincodeList, string userCode, string userName, string wrokpointCode, string Type, string ScanType, string dsconn)
{
string sql = "";
string BarCode = "";
string BinCode = "";
string cBarCode = "";
int result = 0;
string ConnectString = dsconn;
bool isSuccess = true;
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Transaction = trans;
for (int i = 0; i < barcodeList.Length; i++)
{
try
{
BarCode = barcodeList[i];
BinCode = bincodeList[i];
if (cBarCode == "")
{
cBarCode += "'" + BarCode + "'";
}
else
{
cBarCode += ",'" + BarCode + "'";
}
#region 获取仓库库位信息
string WHGuid = "";
string BinGuid = "";
string WHCode = "";
sql = @"select A.Serial,B.Serial AS WHGUID,B.StorageCode from ICSStack A
INNER JOIN ICSStorage B
on B.Serial=A.Storage_Serial
where StackCode='{0}'";
sql = string.Format(sql, BinCode);
DataTable dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取仓库信息失败！");
}
else
{
WHGuid = dt.Rows[0]["WHGUID"].ToString();
BinGuid = dt.Rows[0]["Serial"].ToString();
WHCode = dt.Rows[0]["StorageCode"].ToString();
}
#endregion
#region 获取物料信息
string INVCode = "";
string INVGuid = "";
decimal LotQTY = 0;
string TransNo = "";
string TransLine = "";
string cVenCode = "";
DateTime ReceDate = DateTime.Now;
sql = @"SELECT DISTINCT A.TransNO, A.TransLine, B.INVCODE, B.ID, A.LOTQTY, A.PRODUCTDATE, A.VENDORCODE, A.EATTRIBUTE2,
C.EATTRIBUTE6, ISNULL(A.EATTRIBUTE3,0) AS EATTRIBUTE3, ISNULL(A.EATTRIBUTE4,0) AS EATTRIBUTE4
FROM ICSITEMLot A LEFT JOIN ICSINVENTORY B ON A.ItemCode = B.INVCODE LEFT JOIN ICSINVInfo C ON C.INVCODE = A.ItemCode
WHERE LotNO = '{0}' ";
sql = string.Format(sql, BarCode);
dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取物料信息失败");
}
else
{
INVCode = dt.Rows[0]["INVCODE"].ToString();
INVGuid = dt.Rows[0]["ID"].ToString();
if (dt.Rows[0]["EATTRIBUTE6"].ToString() == "免检" || dt.Rows[0]["EATTRIBUTE2"].ToString() == "合格" || dt.Rows[0]["EATTRIBUTE2"].ToString() == "让步")
{
LotQTY = Convert.ToDecimal(dt.Rows[0]["LOTQTY"].ToString());
}
else
{
throw new Exception("条码" + BarCode + "尚未检验或者为不合格，无法入库！");
}
#region
//else
//{
// if (Convert.ToDecimal(dt.Rows[0]["EATTRIBUTE3"].ToString()) == 0)
// {
// throw new Exception("条码" + BarCode + "尚未进行检验数据录入或合格数为0，无法入库！");
// }
// else
// {
// LotQTY = Convert.ToDecimal(dt.Rows[0]["EATTRIBUTE3"].ToString());
// }
//}
#endregion
ReceDate = Convert.ToDateTime(dt.Rows[0]["PRODUCTDATE"].ToString());
TransNo = dt.Rows[0]["TransNO"].ToString();
TransLine = dt.Rows[0]["TransLine"].ToString();
cVenCode = dt.Rows[0]["VENDORCODE"].ToString();
}
#endregion
#region 存入ICSWareHouseInfo表
sql = @"select * from ICSWareHouseInfo
where INVCode='{0}' AND WHCode='{1}' AND BinCode='{2}'";
sql = string.Format(sql, INVCode, WHCode, BinCode);
dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
sql = @"insert into ICSWareHouseInfo
Values
('{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}','{9}','{10}','')";
sql = string.Format(sql, WHGuid, WHCode, BinGuid, BinCode, INVGuid, INVCode, LotQTY, wrokpointCode, userCode, userName, DateTime.Now);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表存入失败！");
}
}
else
{
sql = @"update ICSWareHouseInfo
set QTY=QTY+{0},MTIME='{1}'
where INVCode='{2}' AND WHCode='{3}' AND BinCode='{4}'";
sql = string.Format(sql, LotQTY, DateTime.Now, INVCode, WHCode, BinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表更新失败");
}
}
#endregion
#region 存入ICSWareHouseLotInfo表
sql = @"insert into ICSWareHouseLotInfo
Values
(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},GETDATE(),'{8}','{9}','{10}',GETDATE(),'','','')";
sql = string.Format(sql, BarCode, WHGuid, WHCode, BinGuid, BinCode, INVGuid, INVCode, LotQTY, wrokpointCode, userCode, userName);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存子表存入失败");
}
#endregion
#region 存入ICSWareHouseLotInfoLog表（日志）
sql = @"insert into ICSWareHouseLotInfoLog
Values
(NEWID(),'{0}','{1}','{2}','{3}',null,null,'{4}','{5}'
,{6},null,'{7}','采购入库','{8}','{9}',GETDATE(),'{10}','','','','','')";
sql = string.Format(sql, TransNo, TransLine, INVCode, BarCode, WHCode, BinCode, LotQTY, Type, userCode, userName, wrokpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志存入失败");
}
#endregion
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
if (message == "Open")
{
#region 接口调用
try
{
sql = @"SELECT A.VENDORCODE, A.TransNO, B.WHCode, C.ErpID AS WBS, A.ItemCode, B.LotQty, A.VenderLotNO, C.ERPAutoid AS Free1, A.EATTRIBUTE3 * A.LOTQTY iNum
FROM ICSITEMLot A INNER JOIN ICSWareHouseLotInfo B ON B.LotNO = A.LotNO
INNER JOIN ICSPOArrive C ON C.cCode = A.TransNO AND C.irowno = A.TransLine AND Free3 = '到货'
WHERE A.LotNO IN ({0}) ORDER BY A.TransNO ";
sql = string.Format(sql, cBarCode);
DataTable dt = SQlReturnData(sql, cmd);
List<PU_ArrivalVouch> contextlist = new List<PU_ArrivalVouch>();
PU_ArrivalVouch context = new PU_ArrivalVouch();
List<PU_ArrivalVouchs> contextlists = new List<PU_ArrivalVouchs>();
PU_ArrivalVouchs contexts = new PU_ArrivalVouchs();
string POCode = "";
string WareHouse = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
if (POCode != dt.Rows[i]["TransNO"].ToString() || WareHouse != dt.Rows[i]["WHCode"].ToString())
{
if (i > 0)
{
context.list = contextlists;
contextlist.Add(context);
}
context = new PU_ArrivalVouch();
POCode = dt.Rows[i]["TransNO"].ToString();
WareHouse = dt.Rows[i]["WHCode"].ToString();
context.cWhCode = dt.Rows[i]["WHCode"].ToString();
context.ID = dt.Rows[i]["WBS"].ToString(); ;
context.UserCode = userCode;
context.IsReturn = "0";
contextlists = new List<PU_ArrivalVouchs>();
}
contexts = new PU_ArrivalVouchs();
contexts.cInvCode = dt.Rows[i]["ItemCode"].ToString();
contexts.iQuantity = Convert.ToDecimal(dt.Rows[i]["LotQty"].ToString());
contexts.cBatch = dt.Rows[i]["VenderLotNO"].ToString();
contexts.AutoID = dt.Rows[i]["Free1"].ToString();
contexts.iNum = Convert.ToDecimal(dt.Rows[i]["iNum"].ToString());
contextlists.Add(contexts);
if (i == dt.Rows.Count - 1)
{
context.list = contextlists;
contextlist.Add(context);
}
}
string Istr = JsonConvert.SerializeObject(contextlist);
string iresult = HttpPost(IStockIN, Istr);
Result StockInResult = new Result();
StockInResult = JsonConvert.DeserializeObject<Result>(iresult);
if (StockInResult.code != "200")
{
throw new Exception(StockInResult.msg);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
}
return isSuccess;
}
/// <summary>
/// 半成品入库
/// </summary>
/// <param name="sqlConnection"></param>
/// <param name="trans"></param>
/// <param name="pocodeList"></param>
/// <param name="porowList"></param>
/// <param name="poqtyList"></param>
/// <param name="barcodeList"></param>
/// <param name="bincodeList"></param>
/// <param name="wrokpointCode"></param>
/// <param name="Type"></param>
/// <param name="ScanType"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static bool StockInForMo(SqlConnection sqlConnection, SqlTransaction trans, string[] barcodeList, string[] bincodeList, string userCode, string userName, string wrokpointCode, string Type, string ScanType, string dsconn)
{
string sql = "";
string BarCode = "";
string BinCode = "";
string cBarCode = "";
int result = 0;
string ConnectString = dsconn;
bool isSuccess = true;
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Transaction = trans;
try
{
for (int i = 0; i < barcodeList.Length; i++)
{
BarCode = barcodeList[i];
BinCode = bincodeList[i];
if (cBarCode == "")
{
cBarCode += "'" + BarCode + "'";
}
else
{
cBarCode += ",'" + BarCode + "'";
}
#region 获取仓库库位信息
string WHGuid = "";
string BinGuid = "";
string WHCode = "";
sql = @"select A.Serial,B.Serial AS WHGUID,B.StorageCode from ICSStack A
INNER JOIN ICSStorage B
on B.Serial=A.Storage_Serial
where StackCode='{0}'";
sql = string.Format(sql, BinCode);
DataTable dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取仓库信息失败！");
}
else
{
WHGuid = dt.Rows[0]["WHGUID"].ToString();
BinGuid = dt.Rows[0]["Serial"].ToString();
WHCode = dt.Rows[0]["StorageCode"].ToString();
}
#endregion
#region 获取物料信息
string INVCode = "";
string INVGuid = "";
decimal LotQTY = 0;
string TransNo = "";
string TransLine = "";
DateTime ReceDate = DateTime.Now;
sql = @"select A.TransNO
,A.TransLine
,B.INVCODE
,B.ID,A.LOTQTY
,A.PRODUCTDATE
from ICSITEMLot A
left join ICSINVENTORY B
on A.ItemCode=B.INVCODE
where LotNO='{0}'";
sql = string.Format(sql, BarCode);
dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取物料信息失败！");
}
else
{
INVCode = dt.Rows[0]["INVCODE"].ToString();
INVGuid = dt.Rows[0]["ID"].ToString();
LotQTY = Convert.ToDecimal(dt.Rows[0]["LOTQTY"].ToString());
ReceDate = Convert.ToDateTime(dt.Rows[0]["PRODUCTDATE"].ToString());
TransNo = dt.Rows[0]["TransNO"].ToString();
TransLine = dt.Rows[0]["TransLine"].ToString();
}
#endregion
#region 存入ICSWareHouseInfo表
sql = @"select * from ICSWareHouseInfo
where INVCode='{0}' AND WHGUID='{1}' AND BinGUID='{2}'";
sql = string.Format(sql, INVCode, WHGuid, BinGuid);
dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
sql = @"insert into ICSWareHouseInfo
Values
('{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}','{9}','{10}','')";
sql = string.Format(sql, WHGuid, WHCode, BinGuid, BinCode, INVGuid, INVCode, LotQTY, wrokpointCode, userCode, userName, DateTime.Now);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表存入失败！");
}
}
else
{
sql = @"update ICSWareHouseInfo
set QTY=QTY+{0},MTIME='{1}'
where INVCode='{2}' AND WHGUID='{3}' AND BinGUID='{4}'";
sql = string.Format(sql, LotQTY, DateTime.Now, INVCode, WHGuid, BinGuid);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表更新失败！");
}
}
#endregion
#region 存入ICSWareHouseLotInfo表
sql = @"insert into ICSWareHouseLotInfo
Values
(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{10}',{6},GETDATE(),'{7}','{8}','{9}',GETDATE(),'','','')";
sql = string.Format(sql, BarCode, WHGuid, WHCode, BinGuid, BinCode, INVGuid, LotQTY, wrokpointCode, userCode, userName, INVCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存子表存入失败！");
}
#endregion
#region 存入ICSWareHouseLotInfoLog表（日志）
sql = @"insert into ICSWareHouseLotInfoLog
Values
(NEWID(),'{0}','{1}','{2}','{3}',null,null,'{4}','{5}'
,{6},null,'{7}','(半)成品入库','{8}','{9}',GETDATE(),'{10}','','','','','')";
sql = string.Format(sql, TransNo, TransLine, INVCode, BarCode, WHCode, BinCode, LotQTY, Type, userCode, userName, wrokpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表存入失败！");
}
#endregion
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
if (message == "Open")
{
#region 接口调用
try
{
sql = @"select A.EATTRIBUTE2
,A.EATTRIBUTE1
,B.LotNO
,A.MOCODE
,A.MOSEQ
,C.WHCode
,B.ItemCode
,C.LotQty
,B.VenderLotNO
,ISNULL(B.EATTRIBUTE3,1) AS EATTRIBUTE3
,A.CUSITEMCODE
from ICSMO A
LEFT JOIN ICSITEMLot B
ON B.TransNO=A.MOCODE AND B.TransLine=A.MOSEQ
LEFT JOIN ICSWareHouseLotInfo C
ON C.LotNO=B.LotNO
WHERE C.LotNO IN ({0})";
sql = string.Format(sql, cBarCode);
DataTable dt = SQlReturnData(sql, cmd);
List<mom_order> contextlist = new List<mom_order>();
mom_order context = new mom_order();
List<ICSSoft.Entity.mom_order.mom_orderdetail> contextlists = new List<ICSSoft.Entity.mom_order.mom_orderdetail>();
ICSSoft.Entity.mom_order.mom_orderdetail contexts = new ICSSoft.Entity.mom_order.mom_orderdetail();
string MOCODE = "";
string WareHouse = "";
string DeptCode = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
if (MOCODE != dt.Rows[i]["MOCODE"].ToString() || WareHouse != dt.Rows[i]["WHCode"].ToString()
|| DeptCode != dt.Rows[i]["CUSITEMCODE"].ToString())
{
if (i > 0)
{
context.list = contextlists;
contextlist.Add(context);
}
context = new mom_order();
MOCODE = dt.Rows[i]["MOCODE"].ToString();
WareHouse = dt.Rows[i]["WHCode"].ToString();
DeptCode = dt.Rows[i]["CUSITEMCODE"].ToString();
context.MoCode = dt.Rows[i]["MOCODE"].ToString();
context.cWhCode = dt.Rows[i]["WHCode"].ToString();
context.MoId = dt.Rows[i]["EATTRIBUTE2"].ToString();
context.UserCode = userCode;
context.IsReturn = "0";
context.MDeptCode = dt.Rows[i]["CUSITEMCODE"].ToString(); ;
contextlists = new List<ICSSoft.Entity.mom_order.mom_orderdetail>();
}
contexts = new ICSSoft.Entity.mom_order.mom_orderdetail();
contexts.cInvCode = dt.Rows[i]["ItemCode"].ToString();
contexts.iQuantity = Convert.ToDecimal(dt.Rows[i]["LotQty"].ToString());
contexts.cBatch = dt.Rows[i]["VenderLotNO"].ToString();
contexts.MoDId = dt.Rows[i]["EATTRIBUTE1"].ToString();
contexts.SortSeq = dt.Rows[i]["MOSEQ"].ToString();
contexts.iNum = Convert.ToDecimal(dt.Rows[i]["LotQty"].ToString()) * Convert.ToDecimal(dt.Rows[i]["EATTRIBUTE3"].ToString());
contextlists.Add(contexts);
if (i == dt.Rows.Count - 1)
{
context.list = contextlists;
contextlist.Add(context);
}
}
string Istr = JsonConvert.SerializeObject(contextlist);
string iresult = HttpPost(IStockINForMO, Istr);
Result MOInResult = new Result();
MOInResult = JsonConvert.DeserializeObject<Result>(iresult);
if (MOInResult.code != "200")
{
throw new Exception(MOInResult.msg);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
}
return isSuccess;
}
/// <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;
}
/// <summary>
/// 验证采购退货单信息（采购退货）
/// </summary>
/// <param name="lotNo"></param>
/// <param name="workpointCode"></param>
/// <returns></returns>
public static DataTable CheckPOCode(string cCode, string workpointCode, string dsconn)
{
string sql = @"select * from ICSPOArrive
where cCode='{0}'
AND Free3='退货'
AND WorkPoint='{1}'
ORDER BY irowno";
sql = string.Format(sql, cCode, workpointCode);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 获取采购退货信息（采购退货）
/// </summary>
/// <param name="POCode"></param>
/// <param name="workpointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable GetPOReturnData(string POCode, string workpointCode, string dsconn)
{
string sql = @"SELECT A.cCode AS POCode, A.irowno AS PORow, A.cInvCode AS INVCODE, B.INVNAME AS INVNAME,
B.INVSTD AS INVSTD, ISNULL(A.iQuantity,0) AS RequestQty, ISNULL(A.INQty,0) AS Inqty,
B.INVUOM AS INVUOM, A.Batch, A.cWhCode FROM ICSPOArrive A
LEFT JOIN ICSINVENTORY B ON B.INVCODE=A.cInvCode WHERE A.cCode = '{0}' AND A.WorkPoint = '{1}'
ORDER BY INVCODE ";
sql = string.Format(sql, POCode, 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 CheckLotnoForReturn(string BarCode, string workpointCode, string dsconn)
{
string sql = @"select A.LotNO
,B.LotNO AS WHLotno
,B.INVCode
,B.BinCode
,C.INVNAME
,C.INVSTD
,C.INVUOM
,B.LotQty
,B.WHCode
,A.EATTRIBUTE3
,ISNULL(A.VenderLotNO,'') AS VenderLotNO
from ICSITEMLot A
left join ICSWareHouseLotInfo B
ON B.LotNO=A.LotNO
LEFT JOIN ICSINVENTORY C
ON C.INVCODE=B.INVCode
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 CheckLotnoForMOOut(string BarCode, string workpointCode, string dsconn)
{
string sql = @"select ISNULL(D.cBatchProperty6,'') as cBatchProperty6
,ISNULL(D.cDefine22,'') as cDefine22
,ISNULL(D.cDefine24,'') as cDefine24
,A.LotNO
,B.LotNO AS WHLotno
,B.INVCode
,B.BinCode
,C.INVNAME
,C.INVSTD
,C.INVUOM
,B.LotQty
,B.WHCode
,A.EATTRIBUTE3
,ISNULL(A.VenderLotNO,'') AS VenderLotNO
from ICSITEMLot A
left join ICSWareHouseLotInfo B
ON B.LotNO=A.LotNO
LEFT JOIN ICSINVENTORY C
ON C.INVCODE=B.INVCode
LEFT JOIN ICSPOArrive D
ON D.cCode=A.TransNO AND D.irowno=A.TransLine
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 CheckLotnoForStackTrans(string BarCode, string workpointCode, string dsconn)
{
string sql = @"select A.LotNO
,B.LotNO AS WHLotno,B.INVCode
,B.BinCode
,C.INVNAME
,C.INVSTD
,C.INVUOM
,B.LotQty
,B.WHCode
from ICSITEMLot A
left join ICSWareHouseLotInfo B
ON B.LotNO=A.LotNO
LEFT JOIN ICSINVENTORY C
ON C.INVCODE=B.INVCode
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 CheckBinCodeForStackTrans(string BinCode, string workpointCode, string dsconn)
{
string sql = @"SELECT A.LotNO, B.LotNO AS WHLotno, B.BinCode, C.INVNAME, C.INVSTD, C.INVUOM, B.LotQty, B.WHCode,A.ItemCode
FROM ICSITEMLot A INNER JOIN ICSWareHouseLotInfo B ON B.LotNO = A.LotNO
LEFT JOIN ICSINVENTORY C ON C.INVCODE = B.INVCode WHERE B.BinCode = '{0}' AND A.WorkPoint = '{1}' AND B.LotQty > 0 ";
sql = string.Format(sql, BinCode, 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 CheckBoxnoForStackTrans(string BarCode, string workpointCode, string dsconn)
{
string sql = @"select A.LotNO
,B.LotNO AS WHLotno
,B.BinCode
,C.INVNAME
,C.INVSTD
,C.INVUOM
,B.LotQty
,B.WHCode
from ICSITEMLot A
left join ICSWareHouseLotInfo B
ON B.LotNO=A.LotNO
LEFT JOIN ICSINVENTORY C
ON C.INVCODE=B.INVCode
LEFT JOIN ICSPalletLotNO E
ON E.LotNO=B.LotNO
WHERE E.PalletCode='{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="SOCode"></param>
/// <param name="BarCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckLotnoForSO(string SOCode, string BarCode, string workpointCode, string dsconn)
{
string sql = @"select A.LotNO
,B.LotNO AS WHLotno
,B.INVCode
,B.BinCode
,C.INVNAME
,C.INVSTD
,C.INVUOM
,B.LotQty AS LotQty
,B.WHCode
,A.VenderLotNO
,A.EATTRIBUTE3
from ICSITEMLot A
left join ICSWareHouseLotInfo B
ON B.LotNO=A.LotNO
LEFT JOIN ICSINVENTORY C
ON C.INVCODE=B.INVCode
WHERE A.LotNO='{1}' AND A.WorkPoint='{2}'";
sql = string.Format(sql, SOCode, BarCode, workpointCode);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 验证销售退库条码信息
/// </summary>
/// <param name="SOCode"></param>
/// <param name="BarCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckLotnoForSOReturn(string BarCode, string workpointCode, string dsconn)
{
string sql = @" select B.DispatchCode
,B.DispatchRow
,A.LotNO
,A.ItemCode
,C.INVNAME
,C.INVSTD
,C.INVUOM
,A.LotQty
,A.EATTRIBUTE3
,A.VenderLotNO
,B.cBatch
,B.WHCode
from ICSITEMLot A
LEFT JOIN ICSSODispatch B
ON B.DispatchCode=A.TransNO AND B.DispatchRow=A.TransLine
LEFT JOIN ICSINVENTORY C
ON C.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="POCode"></param>
/// <param name="porowList"></param>
/// <param name="poqtyList"></param>
/// <param name="barcodeList"></param>
/// <param name="barcodeqtyList"></param>
/// <param name="workpointCode"></param>
/// <param name="Type"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static bool StockReturn(SqlConnection sqlConnection, SqlTransaction trans, string cCode, string[] irownoList, string[] inqtyList, string[] barcoderowList, string[] barcodeList, string[] barcodeqtyList, string userCode, string userName, string workpointCode, string Type, string dsconn)
{
string sql = "";
string BarCode = "";
decimal BarCodeQty = 0;
string BarCodeRow = "";
string Errormessage = "";
string cBarCode = "";
string irowno = "";
decimal INQty = 0;
string ConnectString = dsconn;
int result = 0;
bool isSuccess = true;
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Transaction = trans;
try
{
for (int i = 0; i < irownoList.Length; i++)
{
#region 更新ICSPOArrive表
irowno = irownoList[i];
INQty = Convert.ToDecimal(inqtyList[i]);
sql = @"update ICSPOArrive set INQty=INQty+{0}
where cCode='{1}' AND irowno='{2}' AND WorkPoint='{3}'";
sql = string.Format(sql, INQty, cCode, irowno, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("采购订单表更新失败！");
}
#endregion
}
for (int i = 0; i < barcodeList.Length; i++)
{
if (cBarCode == "")
{
cBarCode += "'" + barcodeList[i] + "'";
}
{
cBarCode += ",'" + barcodeList[i] + "'";
}
BarCode = barcodeList[i];
BarCodeQty = Convert.ToDecimal(barcodeqtyList[i]);
BarCodeRow = barcoderowList[i];
#region 获取仓库库位等信息
string INVCode = "";
string BinCode = "";
string WHCode = "";
string TranNo = "";
string TranLine = "";
sql = @"select WHCode
,BinCode
,INVCode
,B.TransNO
,B.TransLine
from ICSWareHouseLotInfo A
left join ICSITEMLot B
on B.LotNO=A.LotNO
where A.LotNO='{0}' AND A.WorkPoint='{1}'";
sql = string.Format(sql, BarCode, workpointCode);
DataTable dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取仓库信息失败！");
}
else
{
INVCode = dt.Rows[0]["INVCode"].ToString();
BinCode = dt.Rows[0]["BinCode"].ToString();
WHCode = dt.Rows[0]["WHCode"].ToString();
TranNo = dt.Rows[0]["TransNO"].ToString();
TranLine = dt.Rows[0]["TransLine"].ToString();
}
#endregion
#region 更新ICSWareHouseInfo表
sql = @"update ICSWareHouseInfo
set QTY=QTY-{0},MTIME='{1}'
where INVCode='{2}' AND WHCode='{3}' AND BinCode='{4}'";
sql = string.Format(sql, BarCodeQty, DateTime.Now, INVCode, WHCode, BinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表更新失败！");
}
#endregion
#region 更新ICSWareHouseLotInfo表
sql = @"update ICSWareHouseLotInfo set LotQty=LotQty-{0}
where LotNO='{1}'";
sql = string.Format(sql, BarCodeQty, BarCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存子表更新失败！");
}
#endregion
#region 存入ICSWareHouseLotInfoLog表（日志）
sql = @"insert into ICSWareHouseLotInfoLog
Values
(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}',null,null
,{6},null,'{7}','采购退货','{8}','{9}',GETDATE(),'{10}','','','','','未回写')";
sql = string.Format(sql, cCode, BarCodeRow, INVCode, BarCode, WHCode, BinCode, BarCodeQty, Type, userCode, userName, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表存入失败！");
}
#endregion
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
if (message == "Open")
{
#region 接口调用
try
{
sql = @"SELECT A.VENDORCODE, B.TransNO, B.FRMStorageCODE AS WHCode, C.ErpID AS WBS, A.ItemCode,
B.TransQTY, A.VenderLotNO, C.ERPAutoid AS Free1, A.EATTRIBUTE3 * B.TransQTY iNum FROM ICSITEMLot A
INNER JOIN ICSWareHouseLotInfoLog B ON B.LotNO = A.LotNO AND B.BusinessCode = '采购退货' AND B.EATTRIBUTE5 = '未回写'
INNER JOIN ICSPOArrive C ON C.cCode = B.TransNO AND C.irowno = B.TransLine AND Free3 = '退货'
WHERE A.LotNO IN ({0}) ORDER BY A.TransNO ";
sql = string.Format(sql, cBarCode);
DataTable dt = SQlReturnData(sql, cmd);
List<PU_ArrivalVouch> contextlist = new List<PU_ArrivalVouch>();
PU_ArrivalVouch context = new PU_ArrivalVouch();
List<PU_ArrivalVouchs> contextlists = new List<PU_ArrivalVouchs>();
PU_ArrivalVouchs contexts = new PU_ArrivalVouchs();
string POCode = "";
string WareHouse = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
if (POCode != dt.Rows[i]["TransNO"].ToString() || WareHouse != dt.Rows[i]["WHCode"].ToString())
{
if (i > 0)
{
context.list = contextlists;
contextlist.Add(context);
}
context = new PU_ArrivalVouch();
POCode = dt.Rows[i]["TransNO"].ToString();
WareHouse = dt.Rows[i]["WHCode"].ToString();
context.cWhCode = dt.Rows[i]["WHCode"].ToString();
context.ID = dt.Rows[i]["WBS"].ToString(); ;
context.UserCode = userCode;
context.IsReturn = "1";
contextlists = new List<PU_ArrivalVouchs>();
}
contexts = new PU_ArrivalVouchs();
contexts.cInvCode = dt.Rows[i]["ItemCode"].ToString();
contexts.iQuantity = Convert.ToDecimal(dt.Rows[i]["TransQTY"].ToString());
contexts.cBatch = dt.Rows[i]["VenderLotNO"].ToString();
contexts.AutoID = dt.Rows[i]["Free1"].ToString();
contexts.iNum = Convert.ToDecimal(dt.Rows[i]["iNum"].ToString());
contextlists.Add(contexts);
if (i == dt.Rows.Count - 1)
{
context.list = contextlists;
contextlist.Add(context);
}
}
sql = @"UPDATE ICSWareHouseLotInfoLog SET EATTRIBUTE5 = '已回写'
where LotNO in ({0}) AND BusinessCode='采购退货'";
sql = string.Format(sql, cBarCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表更新失败！");
}
string Istr = JsonConvert.SerializeObject(contextlist);
string iresult = HttpPost(IStockIN, Istr);
Result StockInResult = new Result();
StockInResult = JsonConvert.DeserializeObject<Result>(iresult);
if (StockInResult.code != "200")
{
throw new Exception(StockInResult.msg);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
}
return isSuccess;
}
/// <summary>
/// 验证调拨申请单号
/// </summary>
/// <param name="TransferNo"></param>
/// <param name="workpointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckTransferNoMO(string TransferNo, string TransferLine, string workpointCode, string dsconn)
{
string sql = @"SELECT a.MOCODE,
a.SEQ AS TransferLine
FROM
ICSMOBOM a
where a.WorkPoint = '{0}' AND a.MOCODE='{1}' AND a.SEQ='{2}' ORDER BY a.SEQ,a.MOBOMLINE";
sql = string.Format(sql, workpointCode, TransferNo, TransferLine);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 获取调拨申请单信息
/// </summary>
/// <param name="TransferNo"></param>
/// <param name="workpointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable GetTransferDataMO(string TransferNo, string TransferLine, string workpointCode, string dsconn)
{
string sql = @"SELECT a.MOCODE,
a.SEQ AS 行号,
a.MOBOMLINE AS TransferLine,
a.MOBITEMCODE AS ITEMCODE,
a.MOBITEMNAME AS INVNAME,
c.INVSTD AS INVSTD,
a.MOBITEMQTY AS PLANQTY,
ISNULL(b.HasQuantity, 0) AS ACTQTY
,c.INVUOM AS INVUOM
,'' AS FROMStorageCode
,'' AS TOStorageCode
FROM
ICSMOBOM a
left join ICSMOPickLog b
on b.MOCODE=a.MOCODE and b.MOSEQ=a.MOBOMLINE And b.SEQ=a.SEQ AND b.EATTRIBUTE1='调拨'
left join ICSINVENTORY c
on c.INVCODE=a.MOBITEMCODE
where a.WorkPoint = '{0}' AND a.MOCODE='{1}' AND a.SEQ='{2}' ORDER BY a.SEQ,a.MOBOMLINE";
sql = string.Format(sql, workpointCode, TransferNo, TransferLine);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
//验证ICSMaterial表状态
public static DataTable CheckMaterialStatus(string VouchCode, string workpointCode, string dsconn)
{
string sql = @"SELECT PickingSTATUS FROM ICSMaterial
where VouchCode='{0}' and WorkPoint='{1}'";
sql = string.Format(sql, VouchCode, workpointCode);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 验证调拨申请单号
/// </summary>
/// <param name="TransferNo"></param>
/// <param name="workpointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckTransferNo(string TransferNo, string workpointCode, string dsconn)
{
string sql = @"select * from ICSTransVouchs
where TransferNO='{0}' and WorkPoint='{1}'
ORDER BY TransferLine ";
sql = string.Format(sql, TransferNo, workpointCode);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 获取调拨申请单信息
/// </summary>
/// <param name="TransferNo"></param>
/// <param name="workpointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable GetTransferData(string TransferNo, string workpointCode, string dsconn)
{
string sql = @"select TransferLine
,FROMStorageCode
,TOStorageCode
,ITEMCODE
,B.INVNAME
,B.INVSTD
,A.PLANQTY
,A.ACTQTY
,B.INVUOM
,A.EATTRIBUTE4
,ISNULL(A.cTVBatch,'') AS cTVBatch
from ICSTransVouchs A
LEFT JOIN ICSINVENTORY B
ON B.INVCODE=A.ITEMCODE
where TransferNO='{0}' AND A.WorkPoint='{1}'";
sql = string.Format(sql, TransferNo, workpointCode);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 物料调拨
/// </summary>
/// <param name="POCode"></param>
/// <param name="porowList"></param>
/// <param name="poqtyList"></param>
/// <param name="barcodeList"></param>
/// <param name="barcodeqtyList"></param>
/// <param name="workpointCode"></param>
/// <param name="Type"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static bool WareHouseTrans(SqlConnection sqlConnection, SqlTransaction trans, string POCode, string[] porowList, string[] poqtyList, string[] barcoderowList, string[] barcodeList, string[] barcodeqtyList, string[] bincodeList, string userCode, string userName, string workpointCode, string Type, string dsconn)
{
string sql = "";
string BarCode = "";
decimal BarCodeQty = 0;
string cBarCode = "";
string BarCodeRow = "";
string Errormessage = "";
string PORow = "";
decimal POQty = 0;
string NewBinCode = "";
string ConnectString = dsconn;
int result = 0;
bool isSuccess = true;
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Transaction = trans;
try
{
for (int i = 0; i < porowList.Length; i++)
{
#region 更新ICSTransVouchs表
PORow = porowList[i];
POQty = Convert.ToDecimal(poqtyList[i]);
sql = @"update ICSTransVouchs
set ACTQTY=ACTQTY+{0}
where TransferNO='{1}' AND TransferLine='{2}'
and WorkPoint='{3}'";
sql = string.Format(sql, POQty, POCode, PORow, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("调拨申请单已调拨数量更新失败！");
}
#endregion
}
for (int i = 0; i < barcodeList.Length; i++)
{
BarCode = barcodeList[i];
BarCodeQty = Convert.ToDecimal(barcodeqtyList[i]);
BarCodeRow = barcoderowList[i];
NewBinCode = bincodeList[i];
if (cBarCode == "")
{
cBarCode += "'" + barcodeList[i] + "'";
}
else
{
cBarCode += ",'" + barcodeList[i] + "'";
}
string INVCode = "";
string OldBinCode = "";
string NewWHCode = "";
string OldWHCode = "";
string TranNo = "";
string TranLine = "";
#region 获取调拨申请单目标仓库信息
sql = @"select TOStorageCode from ICSTransVouchs
where TransferNO='{0}' AND WorkPoint='{1}'";
sql = string.Format(sql, POCode, workpointCode);
DataTable dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取调拨申请单信息失败！");
}
else
{
NewWHCode = dt.Rows[0]["TOStorageCode"].ToString();
}
#endregion
#region 获取仓库库位等信息
sql = @"select WHCode
,BinCode
,INVCode
,B.TransNO
,B.TransLine
from ICSWareHouseLotInfo A
left join ICSITEMLot B
on B.LotNO=A.LotNO
where A.LotNO='{0}' AND A.WorkPoint='{1}'";
sql = string.Format(sql, BarCode, workpointCode);
dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取仓库信息失败！");
}
else
{
INVCode = dt.Rows[0]["INVCode"].ToString();
OldBinCode = dt.Rows[0]["BinCode"].ToString();
OldWHCode = dt.Rows[0]["WHCode"].ToString();
TranNo = dt.Rows[0]["TransNO"].ToString();
TranLine = dt.Rows[0]["TransLine"].ToString();
}
#endregion
#region 更新ICSWareHouseInfo表
sql = @"update ICSWareHouseInfo
set QTY=QTY-{0},MTIME='{1}'
where INVCode='{2}' AND WHCode='{3}' AND BinCode='{4}'";
sql = string.Format(sql, BarCodeQty, DateTime.Now, INVCode, OldWHCode, OldBinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表库存扣减失败！");
}
sql = @"select * from ICSWareHouseInfo
where WHCode='{0}' AND BinCode='{1}' AND INVCode='{2}'";
sql = string.Format(sql, NewWHCode, NewBinCode, INVCode);
dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count != 0)
{
sql = @"update ICSWareHouseInfo
set QTY=QTY+{0},MTIME='{1}'
where INVCode='{2}' AND WHCode='{3}' AND BinCode='{4}'";
sql = string.Format(sql, BarCodeQty, DateTime.Now, INVCode, NewWHCode, NewBinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表数量增加失败！");
}
}
else
{
sql = @"INSERT INTO dbo.ICSWareHouseInfo
( WHGUID ,WHCode ,BinGUID ,BinCode ,INVGUID ,
INVCode ,QTY ,WorkPoint ,MUSER ,MUSERName ,
MTIME ,EATTRIBUTE1)
SELECT A.Serial,'{0}',B.Serial,'{1}',C.ID,'{2}',{3},'{4}','{5}','{6}','{7}','' FROM ICSStorage A
LEFT JOIN ICSStack B
ON B.Storage_Serial=A.Serial
LEFT JOIN ICSINVENTORY C
ON C.WorkPoint=B.WorkPoint
WHERE A.StorageCode='{0}' AND
B.StackCode='{1}' AND
C.INVCODE='{2}'";
sql = string.Format(sql, NewWHCode, NewBinCode, INVCode, BarCodeQty, workpointCode, userCode, userName, DateTime.Now);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表存入失败！");
}
}
#endregion
#region 更新ICSWareHouseLotInfo表
sql = @"update ICSWareHouseLotInfo set
WHGUID=(select Serial from ICSStorage where StorageCode='{0}'),
WHCode='{0}',
BinGUID=(select Serial from ICSStack where StackCode='{1}'),
BinCode='{1}'
where LotNO='{2}'";
sql = string.Format(sql, NewWHCode, NewBinCode, BarCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存子表更新失败！");
}
#endregion
#region 存入ICSWareHouseLotInfoLog表（日志）
sql = @"insert into ICSWareHouseLotInfoLog
Values
(NEWID(),'{0}','{1}','{2}','{3}','{11}','{12}','{4}','{5}'
,{6},null,'{7}','物料调拨','{8}','{9}',GETDATE(),'{10}','','','','','未回写')";
sql = string.Format(sql, POCode, BarCodeRow, INVCode, BarCode, NewWHCode, NewBinCode, BarCodeQty, Type, userCode, userName, workpointCode, OldWHCode, OldBinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志存入失败！");
}
#endregion
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
if (message == "Open")
{
#region 接口调用
try
{
sql = @"select A.TransferNO
,A.ITEMCODE
,B.TransQTY
,A.EATTRIBUTE1 ,A.EATTRIBUTE2,A.FROMStorageCode,A.TOStorageCode
,C.VenderLotNO AS Batch
,C.EATTRIBUTE3
from ICSTransVouchs A
inner join ICSWareHouseLotInfoLog B
on B.TransNO=A.TransferNO AND B.TransLine=A.TransferLine
LEFT JOIN ICSITEMLot C
ON C.LotNO=B.LotNO
WHERE B.LotNO IN ({0}) AND B.BusinessCode='物料调拨'
AND B.EATTRIBUTE5 = '未回写'";
sql = string.Format(sql, cBarCode);
DataTable dt = SQlReturnData(sql, cmd);
if (dt == null || dt.Rows.Count <= 0)
return false;
List<TransVouch> contextlist = new List<TransVouch>();
TransVouch context = new TransVouch();
List<TransVouchs> contextlists = new List<TransVouchs>();
TransVouchs contexts = new TransVouchs();
context.UserCode = userCode;
context.ID = dt.Rows[0]["EATTRIBUTE2"].ToString();
context.cTVCode = dt.Rows[0]["TransferNO"].ToString();
context.UserCode = userCode;
for (int i = 0; i < dt.Rows.Count; i++)
{
contexts = new TransVouchs();
contexts.cInvCode = dt.Rows[i]["ITEMCODE"].ToString();
contexts.iQuantity = Convert.ToDecimal(dt.Rows[i]["TransQTY"].ToString());
contexts.AutoID = dt.Rows[i]["EATTRIBUTE1"].ToString();
contexts.cTVBatch = dt.Rows[i]["Batch"].ToString();
contexts.iNum = Convert.ToDecimal(dt.Rows[i]["TransQTY"].ToString()) * Convert.ToDecimal(dt.Rows[i]["EATTRIBUTE3"].ToString());
contextlists.Add(contexts);
}
context.list = contextlists;
contextlist.Add(context);
sql = @"UPDATE ICSWareHouseLotInfoLog SET EATTRIBUTE5='已回写'
where LotNO in ({0}) AND BusinessCode='物料调拨'
AND EATTRIBUTE5 = '未回写'";
sql = string.Format(sql, cBarCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表更新失败！");
}
string Istr = JsonConvert.SerializeObject(contextlist);
string iresult = HttpPost(IInvTrans, Istr);
Result INVTransResult = new Result();
INVTransResult = JsonConvert.DeserializeObject<Result>(iresult);
if (INVTransResult.code != "200")
{
throw new Exception(INVTransResult.msg);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
}
return isSuccess;
}
/// <summary>
/// 验证材料出申请单信息（工单）
/// </summary>
/// <param name="VouchCode"></param>
/// <param name="WorkPointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckVouchCode(string VouchCode, string WorkPointCode, string dsconn)
{
string sql = @" SELECT d.MOTYPE,d.REMOITEMDESC,a.MOCODE,
a.SEQ AS 生产订单行号,
a.MOBOMLINE AS 子件行号,
a.MOBITEMCODE AS 存货编码,
a.MOBITEMNAME AS 存货名称,
c.INVSTD AS 规格型号,
a.MOBITEMQTY AS 计划数量,
ISNULL(b.HasQuantity, 0) AS 已发数量
,c.INVUOM AS 单位
FROM ICSMOBOM a
left join ICSMOPickLog b
on b.MOCODE=a.MOCODE and b.MOSEQ=a.SEQ And b.SEQ=a.MOBOMLINE AND b.EATTRIBUTE1='领料'
left join ICSINVENTORY c
on c.INVCODE=a.MOBITEMCODE
left join ICSMO d
on d.MOCODE=a.MOCODE AND d.MOSEQ=a.SEQ
where a.WorkPoint = '{0}' AND a.MOCODE='{1}' ORDER BY a.SEQ, a.MOBOMLINE";
sql = string.Format(sql, WorkPointCode, VouchCode);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 验证材料出申请单信息（领料单）
/// </summary>
/// <param name="VouchCode"></param>
/// <param name="WorkPointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckMaterialPickCode(string VouchCode, string WorkPointCode, string dsconn)
{
string sql = @" SELECT a.VouchCode,
a.VouchRow AS 行号,
a.InvCode AS 存货编码,
b.INVNAME AS 存货名称,
b.INVSTD AS 规格型号,
a.Quantity AS 计划数量,
ISNULL(a.HasQuantity, 0) AS 已发数量
,b.INVUOM AS 单位
,c.PickingSTATUS AS 单据状态
FROM ICSMaterialPick a
left join ICSINVENTORY b
on b.INVCODE=a.InvCode
left join ICSMaterial c
on c.VouchCode=a.VouchCode
where a.WorkPoint = '{0}' AND a.VouchCode='{1}' AND MoveType='领料'
Order BY VouchRow";
sql = string.Format(sql, WorkPointCode, VouchCode);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 验证材料出申请单信息
/// </summary>
/// <param name="VouchCode"></param>
/// <param name="WorkPointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckVouchCodePick(string VouchCode, string WorkPointCode, string dsconn)
{
string sql = @"SELECT a.VouchCode AS MOCODE,
a.VouchRow AS 主行号,
a.VouchRow AS 子行号,
a.SubInvCode AS 存货编码,
c.INVNAME AS 存货名称,
c.INVSTD AS 规格型号,
a.Quantity AS 计划数量,
ISNULL(a.HasQuantity, 0) AS 已发数量
,c.INVUOM AS 单位
FROM
ICSMaterialPick a
left join ICSINVENTORY c
on c.INVCODE=a.SubInvCode
where a.WorkPoint = '{0}' AND a.VouchCode='{1}' AND a.MoveType='领料' ORDER BY a.VouchRow";
sql = string.Format(sql, WorkPointCode, VouchCode);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 获取销售出库单信息
/// </summary>
/// <param name="SOCode"></param>
/// <param name="WrokPointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckSOCode(string SOCode, string WorkPointCode, string dsconn)
{
string sql = @"select DispatchRow
,A.InvCode
,INVNAME
,INVSTD
,ISNULL(Quantity,0) AS Quantity
,ISNULL(HasQuantity,0) AS HasQuantity
,INVUOM
,WHCode
,A.cBatch
from ICSSODispatch A
LEFT JOIN ICSINVENTORY B
ON B.INVCODE=A.InvCode
where DispatchCode='{0}' AND A.WorkPoint='{1}'
AND Type='发货'
ORDER BY DispatchRow";
sql = string.Format(sql, SOCode, WorkPointCode);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 获取销售退库单信息
/// </summary>
/// <param name="SOCode"></param>
/// <param name="WorkPointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckSOCodeForReturn(string SOCode, string WorkPointCode, string dsconn)
{
string sql = @"select DispatchRow
,A.InvCode
,INVNAME
,INVSTD
,ISNULL(Quantity,0) AS Quantity
,ISNULL(HasQuantity,0) AS HasQuantity
,INVUOM
,WHCode
,A.cBatch
from ICSSODispatch A
LEFT JOIN ICSINVENTORY B
ON B.INVCODE=A.InvCode
where DispatchCode='{0}' AND A.WorkPoint='{1}'
AND Type='退货' AND A.Class='合格'
ORDER BY DispatchRow";
sql = string.Format(sql, SOCode, WorkPointCode);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 材料出库（领料单）
/// </summary>
/// <param name="POCode"></param>
/// <param name="porowList"></param>
/// <param name="poqtyList"></param>
/// <param name="barcodeList"></param>
/// <param name="barcodeqtyList"></param>
/// <param name="bincodeList"></param>
/// <param name="userCodt"></param>
/// <param name="workpointCode"></param>
/// <param name="Type"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static bool WareHouseMterialOut(SqlConnection sqlConnection, SqlTransaction trans, string POCode, string[] porowList, string[] poqtyList, string[] barcodeRowList, string[] barcodeList, string[] barcodeqtyList, string SubmitUserName, string userCode, string userName, string workpointCode, string Type, string dsconn)
{
string sql = "";
string BarCode = "";
decimal BarCodeQty = 0;
string BarCodeRow = "";
string Errormessage = "";
string POMainRow = "";
string PORow = "";
decimal POQty = 0;
string NewBinCode = "";
string ConnectString = dsconn;
int result = 0;
bool isSuccess = true;
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Transaction = trans;
try
{
for (int i = 0; i < porowList.Length; i++)
{
#region 更新ICSMaterialPick表已领数量
PORow = porowList[i];
POQty = Convert.ToDecimal(poqtyList[i]);
sql = @"Update ICSMaterialPick set HasQuantity=HasQuantity+{0}
where VouchCode='{1}' AND VouchRow='{2}' AND WorkPoint='{3}' AND MoveType='领料'";
sql = string.Format(sql, POQty, POCode, PORow, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("领料单表更新失败！");
}
#endregion
}
for (int i = 0; i < barcodeList.Length; i++)
{
BarCode = barcodeList[i];
BarCodeQty = Convert.ToDecimal(barcodeqtyList[i]);
BarCodeRow = barcodeRowList[i];
string INVCode = "";
string WHCode = "";
string BinCode = "";
string TranNo = "";
string TranLine = "";
#region 获取仓库库位等信息
sql = @"select WHCode
,BinCode
,INVCode
,B.TransNO
,B.TransLine
from ICSWareHouseLotInfo A
left join ICSITEMLot B
on B.LotNO=A.LotNO
where A.LotNO='{0}' AND A.WorkPoint='{1}'";
sql = string.Format(sql, BarCode, workpointCode);
DataTable dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取仓库信息失败！");
}
else
{
INVCode = dt.Rows[0]["INVCode"].ToString();
BinCode = dt.Rows[0]["BinCode"].ToString();
WHCode = dt.Rows[0]["WHCode"].ToString();
TranNo = dt.Rows[0]["TransNO"].ToString();
TranLine = dt.Rows[0]["TransLine"].ToString();
}
#endregion
#region 更新ICSWareHouseInfo表
sql = @"update ICSWareHouseInfo
set QTY=QTY-{0},MTIME='{1}'
where INVCode='{2}' AND WHCode='{3}' AND BinCode='{4}'";
sql = string.Format(sql, BarCodeQty, DateTime.Now, INVCode, WHCode, BinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表更新失败！");
}
#endregion
#region 更新ICSWareHouseLotInfo表
sql = @"update ICSWareHouseLotInfo
set LotQty=LotQty-{0}
where LotNO='{1}'
and WorkPoint='{2}'";
sql = string.Format(sql, BarCodeQty, BarCode, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存子表更新失败！");
}
#endregion
#region 存入ICSWareHouseLotInfoLog表（日志）
sql = @"insert into ICSWareHouseLotInfoLog
Values
(NEWID(),'{0}','{1}','{2}','{3}','{9}','{10}','',''
,{4},null,'{5}','领料单领料','{6}','{7}',GETDATE(),'{8}','','','','','未回写')";
sql = string.Format(sql, POCode, BarCodeRow, INVCode, BarCode, BarCodeQty, Type, userCode, userName, workpointCode, WHCode, BinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表存入失败！");
}
#endregion
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
if (message == "Open")
{
#region 接口调用
try
{
string IBarCodeList = "";
for (int i = 0; i < barcodeList.Length; i++)
{
if (IBarCodeList == "")
{
IBarCodeList += "'" + barcodeList[i] + "'";
}
else
{
IBarCodeList += ",'" + barcodeList[i] + "'";
}
}
sql = @"select C.FRMStorageCODE
,A.OutCategory
,C.ITEMCODE
,D.VenderLotNO
,C.TransQTY
,D.EATTRIBUTE3
,B.Dept
,B.RDCode,a.MEMO
,A.VENDORCODE,A.VouchCode
from ICSMaterial A
LEFT JOIN ICSMaterialPick B
ON B.VouchCode=A.VouchCode
LEFT JOIN ICSWareHouseLotInfoLog C
ON C.TransNO=B.VouchCode AND C.TransLine=B.VouchRow
LEFT JOIN ICSITEMLot D
ON D.LotNO=C.LotNO
WHERE C.LotNO IN ({0}) AND C.WorkPoint='{1}' AND C.BusinessCode='领料单领料'
AND C.EATTRIBUTE5='未回写'";
sql = string.Format(sql, IBarCodeList, workpointCode);
DataTable dt = SQlReturnData(sql, cmd);
List<ICSSoft.Entity.mom_orderdetail.mom_orderdetail> contextlist = new List<ICSSoft.Entity.mom_orderdetail.mom_orderdetail>();
ICSSoft.Entity.mom_orderdetail.mom_orderdetail context = new ICSSoft.Entity.mom_orderdetail.mom_orderdetail();
List<mom_moallocate> contextlists = new List<mom_moallocate>();
mom_moallocate contexts = new mom_moallocate();
List<root> rootlist = new List<root>();
root rootinfo = new root();
string WareHouse = "";
string Dept = "";
decimal AssQTY = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
AssQTY = Convert.ToDecimal(dt.Rows[i]["TransQTY"]) * Convert.ToDecimal(dt.Rows[i]["EATTRIBUTE3"]);
if (WareHouse != dt.Rows[i]["FRMStorageCODE"].ToString()
|| Dept != dt.Rows[i]["Dept"].ToString())
{
if (i > 0)
{
context.list = contextlists;
contextlist.Add(context);
}
context = new ICSSoft.Entity.mom_orderdetail.mom_orderdetail();
WareHouse = dt.Rows[i]["FRMStorageCODE"].ToString();
Dept = dt.Rows[i]["Dept"].ToString();
context.UserCode = userCode;
context.cWhCode = dt.Rows[i]["FRMStorageCODE"].ToString();
context.IsReturn = "0";
context.cRdCode = dt.Rows[i]["OutCategory"].ToString();
context.cBatch = dt.Rows[i]["Dept"].ToString();
context.cDefine1 = dt.Rows[i]["RDCode"].ToString();
context.cDefine2 = SubmitUserName;
context.cDefine9 = dt.Rows[i]["VENDORCODE"].ToString();
context.cDefine10 = dt.Rows[i]["VouchCode"].ToString();
context.MEMO = dt.Rows[i]["MEMO"].ToString();
contextlists = new List<mom_moallocate>();
}
contexts = new mom_moallocate();
contexts.cInvCode = dt.Rows[i]["ITEMCODE"].ToString();
contexts.iQuantity = Convert.ToDecimal(dt.Rows[i]["TransQTY"].ToString());
contexts.cBatch = dt.Rows[i]["VenderLotNO"].ToString();
contexts.iNum = AssQTY;
contextlists.Add(contexts);
if (i == dt.Rows.Count - 1)
{
context.list = contextlists;
contextlist.Add(context);
}
}
rootinfo.NoSourceBills = contextlist;
rootlist.Add(rootinfo);
sql = @"UPDATE ICSWareHouseLotInfoLog SET EATTRIBUTE5='已回写'
where LotNO in ({0}) AND BusinessCode='领料单领料'
UPDATE ICSMaterial SET EATTRIBUTE1='{1}'
WHERE VouchCode='{2}' AND WorkPoint='{3}'";
sql = string.Format(sql, IBarCodeList, SubmitUserName, POCode, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表更新失败！");
}
//Root root = new Root();
//root.Bills = contextlist;
//root.RedBills = null;
//string Istr = JsonConvert.SerializeObject(root);
string Istr = JsonConvert.SerializeObject(rootinfo);
string iresult = HttpPost(IInvOut, Istr);
Result INVOUTResult = new Result();
INVOUTResult = JsonConvert.DeserializeObject<Result>(iresult);
if (INVOUTResult.code != "200")
{
throw new Exception(INVOUTResult.msg);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
}
return isSuccess;
}
/// <summary>
/// 材料出库（工单）
/// </summary>
/// <param name="sqlConnection"></param>
/// <param name="trans"></param>
/// <param name="POCode"></param>
/// <param name="pomainrowlist"></param>
/// <param name="porowList"></param>
/// <param name="poqtyList"></param>
/// <param name="barcodeRowList"></param>
/// <param name="barcodeList"></param>
/// <param name="barcodeqtyList"></param>
/// <param name="rateList"></param>
/// <param name="userCode"></param>
/// <param name="userName"></param>
/// <param name="workpointCode"></param>
/// <param name="Type"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static bool WareHouseMOMterialOut(SqlConnection sqlConnection, SqlTransaction trans, string POCode, string[] pomainrowlist, string[] porowList, string[] poqtyList, string[] barcodeRowList, string[] barcodeList, string[] barcodeqtyList, string userCode, string userName, string workpointCode, string Type, string dsconn)
{
string sql = "";
string BarCode = "";
decimal BarCodeQty = 0;
string BarCodeRow = "";
string Errormessage = "";
string POMainRow = "";
string PORow = "";
decimal POQty = 0;
string NewBinCode = "";
string ConnectString = dsconn;
int result = 0;
bool isSuccess = true;
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Transaction = trans;
try
{
for (int i = 0; i < porowList.Length; i++)
{
#region 更新ICSMOPickLog表
POMainRow = pomainrowlist[i];
PORow = porowList[i];
POQty = Convert.ToDecimal(poqtyList[i]);
sql = @"select * from ICSMOPickLog
where MOCODE='{0}' AND SEQ='{3}' AND MOSEQ='{1}' AND WorkPoint='{2}' AND EATTRIBUTE1='领料'";
sql = string.Format(sql, POCode, POMainRow, workpointCode, PORow);
DataTable dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count != 0)
{
sql = @"update ICSMOPickLog
set HasQuantity=ISNULL(HasQuantity,0)+{0}
where MOCODE='{4}' AND MOSEQ='{1}' AND SEQ='{2}'
and WorkPoint='{3}' AND EATTRIBUTE1='领料'";
sql = string.Format(sql, POQty, POMainRow, PORow, workpointCode, POCode);
}
else
{
sql = @"Insert into ICSMOPickLog
VALUES
('{0}',(select ITEMCODE from ICSMOBOM where MOCODE='{0}' AND SEQ='{1}' AND MOBOMLINE='{5}')
,'{5}',(select MOBITEMCODE from ICSMOBOM where MOCODE='{0}' AND SEQ='{1}' AND MOBOMLINE='{5}')
,'{1}',{2},'领料','{3}',GETDATE(),'{4}')";
sql = string.Format(sql, POCode, POMainRow, POQty, userCode, workpointCode, PORow);
}
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("领料单表更新失败！");
}
#endregion
}
for (int i = 0; i < barcodeList.Length; i++)
{
BarCode = barcodeList[i];
BarCodeQty = Convert.ToDecimal(barcodeqtyList[i]);
BarCodeRow = barcodeRowList[i];
string INVCode = "";
string WHCode = "";
string BinCode = "";
string TranNo = "";
string TranLine = "";
#region 获取仓库库位等信息
sql = @"select WHCode
,BinCode
,INVCode
,B.TransNO
,B.TransLine
from ICSWareHouseLotInfo A
left join ICSITEMLot B
on B.LotNO=A.LotNO
where A.LotNO='{0}' AND A.WorkPoint='{1}'";
sql = string.Format(sql, BarCode, workpointCode);
DataTable dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取仓库信息失败！");
}
else
{
INVCode = dt.Rows[0]["INVCode"].ToString();
BinCode = dt.Rows[0]["BinCode"].ToString();
WHCode = dt.Rows[0]["WHCode"].ToString();
TranNo = dt.Rows[0]["TransNO"].ToString();
TranLine = dt.Rows[0]["TransLine"].ToString();
}
#endregion
#region 更新ICSWareHouseInfo表
sql = @"update ICSWareHouseInfo
set QTY=QTY-{0},MTIME='{1}'
where INVCode='{2}' AND WHCode='{3}' AND BinCode='{4}'";
sql = string.Format(sql, BarCodeQty, DateTime.Now, INVCode, WHCode, BinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表更新失败！");
}
#endregion
#region 更新ICSWareHouseLotInfo表
sql = @"update ICSWareHouseLotInfo
set LotQty=LotQty-{0}
where LotNO='{1}'
and WorkPoint='{2}'";
sql = string.Format(sql, BarCodeQty, BarCode, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存子表更新失败！");
}
#endregion
#region 存入ICSWareHouseLotInfoLog表（日志）
sql = @"insert into ICSWareHouseLotInfoLog
Values
(NEWID(),'{0}','{1}','{2}','{3}','{9}','{10}','',''
,{4},null,'{5}','工单领料','{6}','{7}',GETDATE(),'{8}','','','','','未回写')";
sql = string.Format(sql, POCode, BarCodeRow, INVCode, BarCode, BarCodeQty, Type, userCode, userName, workpointCode, WHCode, BinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表存入失败！");
}
#endregion
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
if (message == "Open")
{
#region 接口调用
try
{
string IBarCodeList = "";
for (int i = 0; i < barcodeList.Length; i++)
{
if (IBarCodeList == "")
{
IBarCodeList += "'" + barcodeList[i] + "'";
}
else
{
IBarCodeList += ",'" + barcodeList[i] + "'";
}
}
sql = @"select A.MOCODE,A.MOSEQ,A.ITEMCODE,A.EATTRIBUTE1
,B.MOBITEMECN,B.MOBITEMCODE AS BOMITEMCODE,B.MOBOMLINE
,C.FRMStorageCODE,C.TransQTY,D.VenderLotNO,D.EATTRIBUTE3
,A.MOVER
from ICSMO A
INNER JOIN ICSMOBOM B
ON B.MOCODE=A.MOCODE AND B.SEQ=A.MOSEQ
INNER JOIN ICSWareHouseLotInfoLog C
ON C.TransNO=A.MOCODE AND C.TransLine=A.MOSEQ AND B.MOBITEMCODE=C.ITEMCODE AND C.BusinessCode='工单领料'
INNER JOIN ICSITEMLot D
ON D.LotNO=C.LotNO
WHERE A.MOCODE='{0}'
AND C.EATTRIBUTE5 = '未回写'
ORDER BY MOSEQ,ITEMCODE,FRMStorageCODE,VenderLotNO";
sql = string.Format(sql, POCode);
DataTable dt = SQlReturnData(sql, cmd);
List<ICSSoft.Entity.mom_orderdetail.mom_orderdetail> contextlist = new List<ICSSoft.Entity.mom_orderdetail.mom_orderdetail>();
ICSSoft.Entity.mom_orderdetail.mom_orderdetail context = new ICSSoft.Entity.mom_orderdetail.mom_orderdetail();
List<mom_moallocate> contextlists = new List<mom_moallocate>();
mom_moallocate contexts = new mom_moallocate();
List<root> rootlist = new List<root>();
root rootinfo = new root();
string WareHouse = "";
string SEQ = "";
string ITEMCODE = "";
decimal AssQTY = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
AssQTY = Convert.ToDecimal(dt.Rows[i]["EATTRIBUTE3"]) * Convert.ToDecimal(dt.Rows[i]["TransQTY"]);
if (WareHouse != dt.Rows[i]["FRMStorageCODE"].ToString()
|| SEQ != dt.Rows[i]["MOSEQ"].ToString()
|| ITEMCODE != dt.Rows[i]["ITEMCODE"].ToString())
{
if (i > 0)
{
context.list = contextlists;
contextlist.Add(context);
}
context = new ICSSoft.Entity.mom_orderdetail.mom_orderdetail();
WareHouse = dt.Rows[i]["FRMStorageCODE"].ToString();
SEQ = dt.Rows[i]["MOSEQ"].ToString();
ITEMCODE = dt.Rows[i]["ITEMCODE"].ToString();
context.MoDId = dt.Rows[i]["EATTRIBUTE1"].ToString();
context.UserCode = userCode;
context.cWhCode = dt.Rows[i]["FRMStorageCODE"].ToString();
context.IsReturn = "0";
context.MoCode = POCode;
context.SortSeq = dt.Rows[i]["MOSEQ"].ToString();
contextlists = new List<mom_moallocate>();
}
contexts = new mom_moallocate();
contexts.AllocateId = dt.Rows[i]["MOBITEMECN"].ToString();
contexts.cInvCode = dt.Rows[i]["BOMITEMCODE"].ToString();
contexts.iQuantity = Convert.ToDecimal(dt.Rows[i]["TransQTY"].ToString());
contexts.cBatch = dt.Rows[i]["VenderLotNO"].ToString();
contexts.iNum = AssQTY;
contexts.MoLotCode = dt.Rows[i]["MOVER"].ToString();
contextlists.Add(contexts);
if (i == dt.Rows.Count - 1)
{
context.list = contextlists;
contextlist.Add(context);
}
}
rootinfo.Bills = contextlist;
rootlist.Add(rootinfo);
sql = @"UPDATE ICSWareHouseLotInfoLog SET EATTRIBUTE5='已回写'
where LotNO in ({0}) AND BusinessCode='工单领料'";
sql = string.Format(sql, IBarCodeList);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表更新失败！");
}
//Root root = new Root();
//root.Bills = contextlist;
//root.RedBills = null;
//string Istr = JsonConvert.SerializeObject(root);
string Istr = JsonConvert.SerializeObject(rootinfo);
string iresult = HttpPost(IInvOut, Istr);
Result INVOUTResult = new Result();
INVOUTResult = JsonConvert.DeserializeObject<Result>(iresult);
if (INVOUTResult.code != "200")
{
throw new Exception(INVOUTResult.msg);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
}
return isSuccess;
}
/// <summary>
/// 销售出库
/// </summary>
/// <param name="sqlConnection"></param>
/// <param name="trans"></param>
/// <param name="POCode"></param>
/// <param name="porowList"></param>
/// <param name="poqtyList"></param>
/// <param name="barcodeList"></param>
/// <param name="barcodeqtyList"></param>
/// <param name="userCode"></param>
/// <param name="workpointCode"></param>
/// <param name="Type"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static bool WareHouseDisPatchOut(SqlConnection sqlConnection, SqlTransaction trans, string POCode, string[] porowList, string[] poqtyList, string[] barcoderowList, string[] barcodeList, string[] barcodeqtyList, string userCode, string userName, string workpointCode, string Type, string dsconn)
{
string sql = "";
string BarCode = "";
decimal BarCodeQty = 0;
string BarCodeRow = "";
string Errormessage = "";
string PORow = "";
decimal POQty = 0;
string NewBinCode = "";
string IBarCodeList = "";
string cBarCode = "";
string ConnectString = dsconn;
int result = 0;
bool isSuccess = true;
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Transaction = trans;
try
{
for (int i = 0; i < porowList.Length; i++)
{
#region 更新ICSSODispatch表
PORow = porowList[i];
POQty = Convert.ToDecimal(poqtyList[i]);
sql = @"update ICSSODispatch
set HasQuantity=ISNULL(HasQuantity,0)+{0}
where DispatchCode='{1}' AND DispatchRow='{2}' AND WorkPoint='{3}'";
sql = string.Format(sql, POQty, POCode, PORow, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("销售订单已发数量更新失败！");
}
#endregion
}
for (int i = 0; i < barcodeList.Length; i++)
{
if (IBarCodeList == "")
{
IBarCodeList += "'" + barcodeList[i] + "'";
}
else
{
IBarCodeList += ",'" + barcodeList[i] + "'";
}
BarCode = barcodeList[i];
BarCodeQty = Convert.ToDecimal(barcodeqtyList[i]);
BarCodeRow = barcoderowList[i];
if (cBarCode == "")
{
cBarCode += "'" + barcodeList[i] + "'";
}
else
{
cBarCode += ",'" + barcodeList[i] + "'";
}
string INVCode = "";
string WHCode = "";
string BinCode = "";
string TranNo = "";
string TranLine = "";
#region 获取仓库库位等信息
sql = @"select WHCode
,BinCode
,INVCode
,B.TransNO
,B.TransLine
from ICSWareHouseLotInfo A
left join ICSITEMLot B
on B.LotNO=A.LotNO
where A.LotNO='{0}' AND A.WorkPoint='{1}'";
sql = string.Format(sql, BarCode, workpointCode);
DataTable dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取仓库信息失败！");
}
else
{
INVCode = dt.Rows[0]["INVCode"].ToString();
BinCode = dt.Rows[0]["BinCode"].ToString();
WHCode = dt.Rows[0]["WHCode"].ToString();
TranNo = dt.Rows[0]["TransNO"].ToString();
TranLine = dt.Rows[0]["TransLine"].ToString();
}
#endregion
#region 更新ICSWareHouseInfo表
sql = @"update ICSWareHouseInfo
set QTY=QTY-{0},MTIME='{1}'
where INVCode='{2}' AND WHCode='{3}' AND BinCode='{4}'";
sql = string.Format(sql, BarCodeQty, DateTime.Now, INVCode, WHCode, BinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表数量扣减失败！");
}
#endregion
#region 更新ICSWareHouseLotInfo表
sql = @"update ICSWareHouseLotInfo
set LotQty=LotQty-{0}
where LotNO='{1}'
and WorkPoint='{2}'";
sql = string.Format(sql, BarCodeQty, BarCode, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存子表数量扣减失败！");
}
#endregion
#region 存入ICSWareHouseLotInfoLog表（日志）
sql = @"insert into ICSWareHouseLotInfoLog
Values
(NEWID(),'{0}','{1}','{2}','{3}','{9}','{10}','',''
,{4},null,'{5}','销售出库','{6}','{7}',GETDATE(),'{8}','','','','','未回写')";
sql = string.Format(sql, POCode, BarCodeRow, INVCode, BarCode, BarCodeQty, Type, userCode, userName, workpointCode, WHCode, BinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表存入失败！");
}
#endregion
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
if (message == "Open")
{
#region 接口调用
try
{
sql = @"select A.Term1
,A.DispatchCode
,B.FRMStorageCODE
,A.Term2
,B.TransQTY
,C.VenderLotNO AS Batch
,C.ItemCode
,C.EATTRIBUTE3
,case when (c.EATTRIBUTE6 like '02%' or c.EATTRIBUTE6 like 'qichu%')
then c.VoucherNO else arr.cDefine22 end cBatchProperty7 ----cBatchProperty7
,case when (c.EATTRIBUTE6 like '02%' or c.EATTRIBUTE6 like 'qichu%')
then c.TwoMUSER else arr.cBatchProperty6 end cBatchProperty6 ------cBatchProperty6
from ICSSODispatch A
INNER JOIN ICSWareHouseLotInfoLog B
ON B.TransNO=A.DispatchCode AND B.TransLine=A.DispatchRow
AND B.BusinessCode='销售出库' AND B.EATTRIBUTE5='未回写'
INNER JOIN ICSITEMLot C
ON C.LotNO=B.LotNO
left join ICSITEMLot item on item.LotNO=c.EATTRIBUTE6
left join ICSPOArrive arr on arr.cCode=item.TransNO and arr.irowno=item.TransLine
WHERE B.LotNO IN ({0}) AND A.DispatchCode='{1}'";
sql = string.Format(sql, cBarCode, POCode);
DataTable dt = SQlReturnData(sql, cmd);
List<Dispatchlist> contextlist = new List<Dispatchlist>();
Dispatchlist context = new Dispatchlist();
List<Dispatchlists> contextlists = new List<Dispatchlists>();
Dispatchlists contexts = new Dispatchlists();
string SOCode = "";
string WareHouse = "";
decimal AssQTY = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
AssQTY = Convert.ToDecimal(dt.Rows[i]["EATTRIBUTE3"]) * Convert.ToDecimal(dt.Rows[i]["TransQTY"]);
if (SOCode != dt.Rows[i]["DispatchCode"].ToString()
|| WareHouse != dt.Rows[i]["FRMStorageCODE"].ToString())
{
if (i > 0)
{
context.list = contextlists;
contextlist.Add(context);
}
context = new Dispatchlist();
SOCode = dt.Rows[i]["DispatchCode"].ToString();
WareHouse = dt.Rows[i]["FRMStorageCODE"].ToString();
context.DLID = dt.Rows[i]["Term1"].ToString();
context.cDLCode = dt.Rows[i]["DispatchCode"].ToString();
context.cWhCode = dt.Rows[i]["FRMStorageCODE"].ToString();
context.UserCode = userCode;
context.IsReturn = "0";
contextlists = new List<Dispatchlists>();
}
contexts = new Dispatchlists();
contexts.iDLsID = dt.Rows[i]["Term2"].ToString();
contexts.cInvCode = dt.Rows[i]["ItemCode"].ToString();
contexts.iQuantity = Convert.ToDecimal(dt.Rows[i]["TransQTY"].ToString());
contexts.cBatch = dt.Rows[i]["Batch"].ToString();
contexts.iNum = AssQTY;
contexts.cBatchProperty6 = dt.Rows[i]["cBatchProperty6"].ToString();
contexts.cBatchProperty7 = dt.Rows[i]["cBatchProperty7"].ToString();
contextlists.Add(contexts);
if (i == dt.Rows.Count - 1)
{
context.list = contextlists;
contextlist.Add(context);
}
}
sql = @"UPDATE ICSWareHouseLotInfoLog SET EATTRIBUTE5='已回写'
where LotNO in ({0}) AND BusinessCode='销售出库'";
sql = string.Format(sql, IBarCodeList);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表更新失败！");
}
string Istr = JsonConvert.SerializeObject(contextlist);
string iresult = HttpPost(IDispatch, Istr);
Result DispatchResult = new Result();
DispatchResult = JsonConvert.DeserializeObject<Result>(iresult);
if (DispatchResult.code != "200")
{
throw new Exception(DispatchResult.msg);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
}
return isSuccess;
}
/// <summary>
/// 销售退库
/// </summary>
/// <param name="sqlConnection"></param>
/// <param name="trans"></param>
/// <param name="POCode"></param>
/// <param name="porowList"></param>
/// <param name="poqtyList"></param>
/// <param name="barcodeList"></param>
/// <param name="barcodeqtyList"></param>
/// <param name="userCode"></param>
/// <param name="workpointCode"></param>
/// <param name="Type"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static bool WareHouseDisPatchIN(SqlConnection sqlConnection, SqlTransaction trans, string POCode, string[] porowList, string[] barcodeList, string[] barcodeqtyList, string[] stackcodeList, string userCode, string userName, string workpointCode, string Type, string dsconn)
{
string sql = "";
string cBarCode = "";
string PORow = "";
decimal BarCodeQty = 0;
string BarCode = "";
string BinCode = "";
string BinGUID = "";
string WHCODE = "";
string WHGUID = "";
string INVGUID = "";
string INVCODE = "";
string ConnectString = dsconn;
int result = 0;
bool isSuccess = true;
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Transaction = trans;
try
{
for (int i = 0; i < porowList.Length; i++)
{
#region 更新ICSSODispatch表
PORow = porowList[i];
BarCodeQty = Convert.ToDecimal(barcodeqtyList[i]);
BarCode = barcodeList[i];
BinCode = stackcodeList[i];
if (cBarCode == "")
{
cBarCode += "'" + BarCode + "'";
}
else
{
cBarCode += ",'" + BarCode + "'";
}
sql = @"update ICSSODispatch
set HasQuantity=ISNULL(HasQuantity,0)+{0}
where DispatchCode='{1}' AND DispatchRow='{2}' AND WorkPoint='{3}'";
sql = string.Format(sql, BarCodeQty, POCode, PORow, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("销售订单表已退数量更新失败！");
}
#endregion
#region 获取仓库信息
sql = @"select A.Serial AS BinGUID
,B.Serial AS WHGUID
,B.StorageCode AS WHCODE
from ICSStack A
LEFT JOIN ICSStorage B
ON B.Serial=A.Storage_Serial
where A.StackCode='{0}' AND A.WorkPoint='{1}'";
sql = string.Format(sql, BinCode, workpointCode);
DataTable dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count > 0)
{
BinGUID = dt.Rows[0]["BinGUID"].ToString();
WHGUID = dt.Rows[0]["WHGUID"].ToString();
WHCODE = dt.Rows[0]["WHCODE"].ToString();
}
else
{
throw new Exception("仓库信息获取失败");
}
#endregion
#region 获取物料信息
sql = @"SELECT A.INVCODE,A.ID
FROM ICSINVENTORY A
LEFT JOIN ICSITEMLot B
ON B.ItemCode=A.INVCODE
WHERE B.LotNO='{0}' AND A.WorkPoint='{1}'";
sql = string.Format(sql, BarCode, workpointCode);
dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count > 0)
{
INVGUID = dt.Rows[0]["ID"].ToString();
INVCODE = dt.Rows[0]["INVCODE"].ToString();
}
else
{
throw new Exception("物料信息获取失败!");
}
#endregion
#region 更新ICSWareHouseInfo表
sql = @"IF EXISTS (SELECT * FROM ICSWareHouseInfo WHERE WHCode='{0}' AND BinCode='{1}'
AND INVCode=(SELECT ItemCode FROM ICSITEMLot WHERE LotNO='{2}'))
UPDATE ICSWareHouseInfo SET QTY=QTY+{3}
WHERE WHCode='{0}' AND BinCode='{1}' AND INVCode=(SELECT ItemCode FROM ICSITEMLot WHERE LotNO='{2}')
ELSE
INSERT INTO ICSWareHouseInfo
VALUES
('{4}','{0}','{5}','{1}','{6}','{7}',{3},'{8}','{9}','{10}',GETDATE(),'' )";
sql = string.Format(sql, WHCODE, BinCode, BarCode, BarCodeQty, WHGUID, BinGUID, INVGUID, INVCODE
, workpointCode, userCode, userName);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表数量更新失败！");
}
#endregion
#region 更新ICSWareHouseLotInfo表
sql = @"select * from ICSWareHouseLotInfo
where LotNO='{0}' AND WorkPoint='{1}'";
sql = string.Format(sql, BarCode, workpointCode);
dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count != 0)
{
throw new Exception("条码已经入库！");
}
sql = @"insert into ICSWareHouseLotInfo
Values
(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7}
,GETDATE(),'{8}','{9}','{10}',GETDATE(),'',NULL,NULL)";
sql = string.Format(sql, BarCode, WHGUID, WHCODE, BinGUID, BinCode, INVGUID, INVCODE, BarCodeQty
, workpointCode, userCode, userName);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存子表数量更新失败！");
}
#endregion
#region 更新ICSWareHouseLotInfoLog表
sql = @"insert into ICSWareHouseLotInfoLog
VALUES
(NEWID(),'{0}','{1}','{2}','{3}','','','{4}','{5}'
,{6},NULL,'{7}','销售退库','{8}','{9}',GETDATE(),'{10}',NULL,NULL,NULL,NULL,'未回写')";
sql = string.Format(sql, POCode, PORow, INVCODE, BarCode, WHCODE, BinCode, BarCodeQty, Type
, userCode, userName, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表表存入失败！");
}
#endregion
}
if (message == "Open")
{
#region 接口调用
try
{
sql = @"select A.Term1
,A.DispatchCode
,B.TOStorageCODE
,A.Term2
,B.TransQTY
,C.VenderLotNO AS Batch
,C.ItemCode
,C.EATTRIBUTE3
from ICSSODispatch A
INNER JOIN ICSWareHouseLotInfoLog B
ON B.TransNO=A.DispatchCode AND B.TransLine=A.DispatchRow AND B.BusinessCode='销售退库' AND B.EATTRIBUTE5='未回写'
INNER JOIN ICSITEMLot C
ON C.LotNO=B.LotNO
WHERE B.LotNO IN ({0})";
sql = string.Format(sql, cBarCode);
DataTable dt = SQlReturnData(sql, cmd);
List<Dispatchlist> contextlist = new List<Dispatchlist>();
Dispatchlist context = new Dispatchlist();
List<Dispatchlists> contextlists = new List<Dispatchlists>();
Dispatchlists contexts = new Dispatchlists();
string SOCode = "";
string WareHouse = "";
decimal AssQTY = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
AssQTY = Convert.ToDecimal(dt.Rows[i]["EATTRIBUTE3"]) * Convert.ToDecimal(dt.Rows[i]["TransQTY"]);
if (SOCode != dt.Rows[i]["DispatchCode"].ToString()
|| WareHouse != dt.Rows[i]["TOStorageCODE"].ToString())
{
if (i > 0)
{
context.list = contextlists;
contextlist.Add(context);
}
context = new Dispatchlist();
SOCode = dt.Rows[i]["DispatchCode"].ToString();
WareHouse = dt.Rows[i]["TOStorageCODE"].ToString();
context.DLID = dt.Rows[i]["Term1"].ToString();
context.cDLCode = dt.Rows[i]["DispatchCode"].ToString();
context.cWhCode = dt.Rows[i]["TOStorageCODE"].ToString();
context.UserCode = userCode;
context.IsReturn = "1";
contextlists = new List<Dispatchlists>();
}
contexts = new Dispatchlists();
contexts.iDLsID = dt.Rows[i]["Term2"].ToString();
contexts.cInvCode = dt.Rows[i]["ItemCode"].ToString();
contexts.iQuantity = Convert.ToDecimal(dt.Rows[i]["TransQTY"].ToString());
contexts.cBatch = dt.Rows[i]["Batch"].ToString();
contexts.iNum = AssQTY;
contextlists.Add(contexts);
if (i == dt.Rows.Count - 1)
{
context.list = contextlists;
contextlist.Add(context);
}
}
sql = @"UPDATE ICSWareHouseLotInfoLog SET EATTRIBUTE5='已回写'
where LotNO in ({0}) AND BusinessCode='销售退库'";
sql = string.Format(sql, cBarCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表更新失败！");
}
string Istr = JsonConvert.SerializeObject(contextlist);
string iresult = HttpPost(IDispatch, Istr);
Result DispatchResult = new Result();
DispatchResult = JsonConvert.DeserializeObject<Result>(iresult);
if (DispatchResult.code != "200")
{
throw new Exception(DispatchResult.msg);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return isSuccess;
}
/// <summary>
/// 物料退料
/// </summary>
/// <param name="POCode"></param>
/// <param name="porowList"></param>
/// <param name="poqtyList"></param>
/// <param name="barcodeList"></param>
/// <param name="barcodeqtyList"></param>
/// <param name="userCode"></param>
/// <param name="workpointCode"></param>
/// <param name="Type"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static bool MaterialIN(SqlConnection sqlConnection, SqlTransaction trans, string[] pocodeList, string[] porowList, string[] poqtyList, string[] barcoderowList, string[] barcodeList, string[] barcodeqtyList, string[] bincodelist, string userCode, string userName, string workpointCode, string Type, string VouchType, string dsconn)
{
string sql = "";
string BarCode = "";
decimal BarCodeQty = 0;
string BarCodeRow = "";
string Errormessage = "";
string PORow = "";
string POCode = "";
decimal POQty = 0;
string IBarCodeList = "";
string NewBinCode = "";
string ConnectString = dsconn;
int result = 0;
bool isSuccess = true;
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Transaction = trans;
try
{
for (int i = 0; i < porowList.Length; i++)
{
#region 更新ICSMaterialPick表
POCode = pocodeList[i];
PORow = porowList[i];
POQty = Convert.ToDecimal(poqtyList[i]);
sql = @"update ICSMaterialPick
set HasQuantity=ISNULL(HasQuantity,0)+{0}
where VouchCode='{1}' AND VouchRow='{2}'
and WorkPoint='{3}'";
sql = string.Format(sql, POQty, POCode, PORow, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("退料表已退数量更新失败！");
}
#endregion
if (VouchType != "MRO")
{
#region 扣减领料单已领数量
sql = @"update ICSMOPickLog set HasQuantity=HasQuantity-{2}
where MOCODE=
(select MOCODE from ICSMaterialPick where VouchCode='{0}' and VouchRow='{1}')
AND MOSEQ=
(select MORow from ICSMaterialPick where VouchCode='{0}' and VouchRow='{1}')
AND MOBITEMCODE=
(SELECT SubInvCode FROM ICSMaterialPick where VouchCode='{0}' and VouchRow='{1}')
AND WorkPoint='{3}'";
sql = string.Format(sql, POCode, PORow, POQty, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("领料表已领数量扣减失败！");
}
}
#endregion
}
for (int i = 0; i < barcodeList.Length; i++)
{
if (IBarCodeList == "")
{
IBarCodeList += "'" + barcodeList[i] + "'";
}
else
{
IBarCodeList += ",'" + barcodeList[i] + "'";
}
BarCode = barcodeList[i];
BarCodeQty = Convert.ToDecimal(barcodeqtyList[i]);
BarCodeRow = barcoderowList[i];
NewBinCode = bincodelist[i];
string INVCode = "";
string WHCode = "";
string TranNo = "";
string TranLine = "";
#region 获取仓库库位等信息
sql = @"select ItemCode
,TransNO
,TransLine
from ICSITEMLot
where LotNO='{0}' AND WorkPoint='{1}'";
sql = string.Format(sql, BarCode, workpointCode);
DataTable dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取条码信息失败！");
}
else
{
INVCode = dt.Rows[0]["ItemCode"].ToString();
TranNo = dt.Rows[0]["TransNO"].ToString();
TranLine = dt.Rows[0]["TransLine"].ToString();
}
sql = @"select B.StorageCode
from ICSStack A
LEFT JOIN ICSStorage B
ON B.Serial=A.Storage_Serial
where StackCode='{0}'";
sql = string.Format(sql, NewBinCode);
dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取仓库信息失败！");
}
else
{
WHCode = dt.Rows[0]["StorageCode"].ToString();
}
#endregion
#region 更新ICSWareHouseInfo表
sql = @"update ICSWareHouseInfo
set QTY=QTY+{0},MTIME='{1}'
where INVCode='{2}' AND WHCode='{3}' AND BinCode='{4}'";
sql = string.Format(sql, BarCodeQty, DateTime.Now, INVCode, WHCode, NewBinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
sql = @"insert into ICSWareHouseInfo
values
((select Serial from ICSStorage where StorageCode='{0}')
,'{0}'
,(SELECT Serial FROM ICSStack WHERE StackCode='{1}')
,'{1}'
,(SELECT ID FROM ICSINVENTORY WHERE INVCODE='{2}')
,'{2}',{3},'{4}','{5}','{6}',GETDATE(),NULL)";
sql = string.Format(sql, WHCode, NewBinCode, INVCode, BarCodeQty, workpointCode, userCode, userName);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表存入失败！");
}
}
#endregion
#region 更新ICSWareHouseLotInfo表
sql = @"insert into ICSWareHouseLotInfo
Values
(NEWID(),'{0}',
(select Serial from ICSStorage where StorageCode='{1}'),
'{1}',
(SELECT Serial FROM ICSStack WHERE StackCode='{2}'),
'{2}',
(SELECT ID FROM ICSINVENTORY WHERE INVCODE='{3}'),
'{3}',{4},GETDATE(),'{5}','{6}','{7}',GETDATE(),'','','')";
sql = string.Format(sql, BarCode, WHCode, NewBinCode, INVCode, BarCodeQty, workpointCode, userCode, userName);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存子表数量增加失败！");
}
#endregion
#region 存入ICSWareHouseLotInfoLog表（日志）
sql = @"insert into ICSWareHouseLotInfoLog
Values
(NEWID(),'{0}','{1}','{2}','{3}','','','{9}','{10}'
,{4},null,'{5}','物料退料','{6}','{7}',GETDATE(),'{8}','','','','','未回写')";
sql = string.Format(sql, TranNo, TranLine, INVCode, BarCode, BarCodeQty, Type, userCode, userName, workpointCode, WHCode, NewBinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表存入失败！");
}
#endregion
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
if (message == "Open")
{
#region 接口调用
try
{
string CodeList = "";
for (int i = 0; i < pocodeList.Length; i++)
{
if (CodeList == "")
{
CodeList += "'" + pocodeList[i] + "'";
}
else
{
CodeList += ",'" + pocodeList[i] + "'";
}
}
sql = @"select A.MOCODE,A.MORow AS MOSEQ,A.InvCode AS ITEMCODE,E.EATTRIBUTE1,A.VoucherNO
,B.MOBITEMECN,B.MOBITEMCODE,B.SEQ
,C.TOStorageCODE,C.TransQTY,D.VenderLotNO AS VenderLotNO
,D.EATTRIBUTE3,F.OutCategory,E.MOVER,C.ItemCode AS CItemCode,f.MEMO,a.Dept
from ICSMaterialPick A
INNER JOIN ICSWareHouseLotInfoLog C
ON C.TransNO=A.VouchCode AND C.TransLine=A.VouchRow
AND C.BusinessCode='物料退料' AND C.EATTRIBUTE5='未回写'
LEFT JOIN ICSMO E
ON E.MOCODE=A.MOCode AND E.MOSEQ=A.MORow
LEFT JOIN ICSMOBOM B
ON B.MOCODE=A.MOCODE AND B.SEQ=A.MORow AND B.MOBITEMCODE=A.SubInvCode
LEFT JOIN ICSITEMLot D
ON D.LotNO=C.LotNO
LEFT JOIN ICSMaterial F
ON F.VouchCode=A.VouchCode
WHERE A.VouchCode in ({0})
ORDER BY MOSEQ,E.ITEMCODE,TOStorageCODE,VenderLotNO";
sql = string.Format(sql, CodeList);
DataTable dt = SQlReturnData(sql, cmd);
decimal ASSQTY = 0;
List<ICSSoft.Entity.mom_orderdetail.mom_orderdetail> contextlist = new List<ICSSoft.Entity.mom_orderdetail.mom_orderdetail>();
List<mom_moallocate> contextlists = new List<mom_moallocate>();
List<ICSSoft.Entity.mom_orderdetail.mom_orderdetail> contextlistMO = new List<ICSSoft.Entity.mom_orderdetail.mom_orderdetail>();
List<mom_moallocate> contextlistsMO = new List<mom_moallocate>();
if (VouchType == "MRO")
{
#region 无来源单据的退料单
ICSSoft.Entity.mom_orderdetail.mom_orderdetail context = new ICSSoft.Entity.mom_orderdetail.mom_orderdetail();
mom_moallocate contexts = new mom_moallocate();
string WareHouse = "";
string SEQ = "";
string ITEMCODE = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
ASSQTY = Convert.ToDecimal(dt.Rows[i]["TransQTY"].ToString()) * Convert.ToDecimal(dt.Rows[i]["EATTRIBUTE3"].ToString());
if (WareHouse != dt.Rows[i]["TOStorageCODE"].ToString()
|| ITEMCODE != dt.Rows[i]["ITEMCODE"].ToString())
{
if (i > 0)
{
context.list = contextlists;
contextlist.Add(context);
}
context = new ICSSoft.Entity.mom_orderdetail.mom_orderdetail();
WareHouse = dt.Rows[i]["TOStorageCODE"].ToString();
ITEMCODE = dt.Rows[i]["ITEMCODE"].ToString();
context.UserCode = userCode;
context.cWhCode = dt.Rows[i]["TOStorageCODE"].ToString();
context.IsReturn = "1";
context.cRdCode = dt.Rows[i]["OutCategory"].ToString();
context.MEMO = dt.Rows[i]["MEMO"].ToString();
context.cBatch = dt.Rows[i]["Dept"].ToString();
contextlists = new List<mom_moallocate>();
}
contexts = new mom_moallocate();
contexts.cInvCode = dt.Rows[i]["CItemCode"].ToString();
contexts.iQuantity = Convert.ToDecimal(dt.Rows[i]["TransQTY"].ToString());
contexts.cBatch = dt.Rows[i]["VenderLotNO"].ToString();
contexts.iNum = ASSQTY;
contextlists.Add(contexts);
if (i == dt.Rows.Count - 1)
{
context.list = contextlists;
contextlist.Add(context);
}
}
#endregion
}
else
{
#region 工单退料单
ICSSoft.Entity.mom_orderdetail.mom_orderdetail contextMO = new ICSSoft.Entity.mom_orderdetail.mom_orderdetail();
mom_moallocate contextsMO = new mom_moallocate();
string WareHouseMO = "";
string SEQMO = "";
string ITEMCODEMO = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
ASSQTY = Convert.ToDecimal(dt.Rows[i]["TransQTY"].ToString()) * Convert.ToDecimal(dt.Rows[i]["EATTRIBUTE3"].ToString());
if (WareHouseMO != dt.Rows[i]["TOStorageCODE"].ToString()
|| SEQMO != dt.Rows[i]["MOSEQ"].ToString()
|| ITEMCODEMO != dt.Rows[i]["ITEMCODE"].ToString())
{
if (i > 0)
{
contextMO.list = contextlistsMO;
contextlistMO.Add(contextMO);
}
contextMO = new ICSSoft.Entity.mom_orderdetail.mom_orderdetail();
WareHouseMO = dt.Rows[i]["TOStorageCODE"].ToString();
SEQMO = dt.Rows[i]["MOSEQ"].ToString();
ITEMCODEMO = dt.Rows[i]["ITEMCODE"].ToString();
contextMO.MoDId = dt.Rows[i]["EATTRIBUTE1"].ToString();
contextMO.MoCode = dt.Rows[i]["MOCODE"].ToString();
contextMO.SortSeq = dt.Rows[i]["MOSEQ"].ToString();
contextMO.UserCode = userCode;
contextMO.cWhCode = dt.Rows[i]["TOStorageCODE"].ToString();
contextMO.IsReturn = "1";
contextlistsMO = new List<mom_moallocate>();
}
contextsMO = new mom_moallocate();
contextsMO.AllocateId = dt.Rows[i]["MOBITEMECN"].ToString();
contextsMO.cInvCode = dt.Rows[i]["MOBITEMCODE"].ToString();
contextsMO.iQuantity = Convert.ToDecimal(dt.Rows[i]["TransQTY"].ToString());
contextsMO.cBatch = dt.Rows[i]["VenderLotNO"].ToString();
contextsMO.iNum = ASSQTY;
contextsMO.MoLotCode = dt.Rows[i]["MOVER"].ToString();
contextlistsMO.Add(contextsMO);
if (i == dt.Rows.Count - 1)
{
contextMO.list = contextlistsMO;
contextlistMO.Add(contextMO);
}
}
#endregion
}
List<root> rootlist = new List<root>();
root rootinfo = new root();
rootinfo.Bills = contextlistMO;
rootinfo.NoSourceBills = contextlist;
rootlist.Add(rootinfo);
sql = @"UPDATE ICSWareHouseLotInfoLog SET EATTRIBUTE5='已回写'
where LotNO in ({0}) AND BusinessCode='物料退料'";
sql = string.Format(sql, IBarCodeList);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表更新失败！");
}
//Root root = new Root();
//root.Bills = contextlistMO;
//root.RedBills = contextlist;
//string IstrMO = JsonConvert.SerializeObject(root);
string IstrMO = JsonConvert.SerializeObject(rootinfo);
string iresultMO = HttpPost(IInvOut, IstrMO);
Result INVINResultMO = new Result();
INVINResultMO = JsonConvert.DeserializeObject<Result>(iresultMO);
if (INVINResultMO.code != "200")
{
throw new Exception(INVINResultMO.msg);
}
// List<ZYPDAServiceForINVOut.mom_orderdetail> contextList = new List<ZYPDAServiceForINVOut.mom_orderdetail>();
// List<ZYPDAServiceForINVOut.mom_moallocate> contextLists = new List<ZYPDAServiceForINVOut.mom_moallocate>();
// ZYPDAServiceForINVOut.mom_orderdetail context = new ZYPDAServiceForINVOut.mom_orderdetail();
// ZYPDAServiceForINVOut.mom_moallocate contexts = new ZYPDAServiceForINVOut.mom_moallocate();
// string WareHouse = "";
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// if (WareHouse != dt.Rows[i]["TOStorageCODE"].ToString())
// {
// if (i > 0)
// {
// context.bodyList = contextLists.ToArray();
// contextList.Add(context);
// }
// context = new ZYPDAServiceForINVOut.mom_orderdetail();
// WareHouse = dt.Rows[i]["TOStorageCODE"].ToString();
// context.MoCode = dt.Rows[i]["MOCODE"].ToString();
// context.SortSeq = dt.Rows[i]["SEQ"].ToString();
// context.UserCode = userCode;
// context.cWhCode = dt.Rows[i]["TOStorageCODE"].ToString();
// context.ItemCode = dt.Rows[i]["InvCode"].ToString();
// contextLists = new List<ZYPDAServiceForINVOut.mom_moallocate>();
// }
// contexts = new ZYPDAServiceForINVOut.mom_moallocate();
// contexts.AllocateId = dt.Rows[i]["MOBITEMECN"].ToString();
// contexts.cInvCode = dt.Rows[i]["BOMITEMCODE"].ToString();
// contexts.iQuantity = Convert.ToDecimal(dt.Rows[i]["TransQTY"].ToString());
// contexts.cBatch = dt.Rows[i]["VenderLotNO"].ToString();
// //contexts.modid = dt.Rows[i]["Free1"].ToString();
// contextLists.Add(contexts);
// if (i == dt.Rows.Count - 1)
// {
// context.bodyList = contextLists.ToArray();
// contextList.Add(context);
// }
// }
// ZYPDAServiceForINVOut.Imom2Rd11Client client = new ZYPDAServiceForINVOut.Imom2Rd11Client();
// ZYPDAServiceForINVOut.Result cresult = new ZYPDAServiceForINVOut.Result();
// sql = @"UPDATE ICSWareHouseLotInfoLog SET EATTRIBUTE5='已回写'
// where LotNO in ({0}) AND BusinessCode='物料退料'";
// sql = string.Format(sql, IBarCodeList);
// cmd.CommandText = sql;
// result = cmd.ExecuteNonQuery();
// if (isSuccess && result <= 0)
// {
// throw new Exception("日志表更新失败！");
// }
// cresult = client.InsertRd11(contextList.ToArray(), "1");
// if (cresult.IsSuccess == false)
// {
// throw new Exception(cresult.MESSAGE);
// }
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
}
return isSuccess;
}
/// <summary>
/// 移库扫描
/// </summary>
/// <param name="sqlConnection"></param>
/// <param name="trans"></param>
/// <param name="oldBinCodeList"></param>
/// <param name="newBinCodeList"></param>
/// <param name="barcodeList"></param>
/// <param name="barcodeqtyList"></param>
/// <param name="userCode"></param>
/// <param name="workpointCode"></param>
/// <param name="Type"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static bool StackTransfer(SqlConnection sqlConnection, SqlTransaction trans, string[] oldBinCodeList, string[] newBinCodeList, string[] barcodeList, string[] barcodeqtyList, string userCode, string userName, string workpointCode, string Type, string dsconn)
{
string sql = "";
string BarCode = "";
decimal BarCodeQty = 0;
string NewBinCode = "";
string OldBinCode = "";
string ConnectString = dsconn;
int result = 0;
bool isSuccess = true;
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Transaction = trans;
try
{
for (int i = 0; i < barcodeList.Length; i++)
{
BarCode = barcodeList[i];
BarCodeQty = Convert.ToDecimal(barcodeqtyList[i].ToString());
NewBinCode = newBinCodeList[i];
OldBinCode = oldBinCodeList[i];
string INVCode = "";
string WHCode = "";
string TranNo = "";
string TranLine = "";
#region 获取仓库库位等信息
sql = @"select WHCode
,BinCode
,INVCode
,B.TransNO
,B.TransLine
from ICSWareHouseLotInfo A
left join ICSITEMLot B
on B.LotNO=A.LotNO
where A.LotNO='{0}' AND A.WorkPoint='{1}'";
sql = string.Format(sql, BarCode, workpointCode);
DataTable dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取仓库信息失败！");
}
else
{
INVCode = dt.Rows[0]["INVCode"].ToString();
WHCode = dt.Rows[0]["WHCode"].ToString();
TranNo = dt.Rows[0]["TransNO"].ToString();
TranLine = dt.Rows[0]["TransLine"].ToString();
}
#endregion
#region 更新ICSWareHouseInfo表
sql = @"update ICSWareHouseInfo
set QTY=QTY-{0},MTIME='{1}'
where INVCode='{2}' AND WHCode='{3}' AND BinCode='{4}'";
sql = string.Format(sql, BarCodeQty, DateTime.Now, INVCode, WHCode, OldBinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表数量扣减失败！");
}
sql = @"select * from ICSWareHouseInfo
where WHCode='{0}' AND BinCode='{1}' AND INVCode='{2}'";
sql = string.Format(sql, WHCode, NewBinCode, INVCode);
dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count != 0)
{
sql = @"update ICSWareHouseInfo
set QTY=QTY+{0},MTIME='{1}'
where INVCode='{2}' AND WHCode='{3}' AND BinCode='{4}'";
sql = string.Format(sql, BarCodeQty, DateTime.Now, INVCode, WHCode, NewBinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表数量增加失败！");
}
}
else
{
sql = @"INSERT INTO dbo.ICSWareHouseInfo
( WHGUID ,WHCode ,BinGUID ,BinCode ,INVGUID ,
INVCode ,QTY ,WorkPoint ,MUSER ,MUSERName ,
MTIME ,EATTRIBUTE1)
SELECT A.Serial,'{0}',B.Serial,'{1}',C.ID,'{2}',{3},'{4}','{5}','{6}','{7}','' FROM ICSStorage A
LEFT JOIN ICSStack B
ON B.Storage_Serial=A.Serial
LEFT JOIN ICSINVENTORY C
ON C.WorkPoint=B.WorkPoint
WHERE A.StorageCode='{0}' AND
B.StackCode='{1}' AND
C.INVCODE='{2}'";
sql = string.Format(sql, WHCode, NewBinCode, INVCode, BarCodeQty, workpointCode, userCode, userName, DateTime.Now);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表数量存入失败！");
}
}
#endregion
#region 更新ICSWareHouseLotInfo表
sql = @"update ICSWareHouseLotInfo set
BinGUID=(select Serial from ICSStack where StackCode='{0}'),
BinCode='{0}'
where LotNO='{1}'";
sql = string.Format(sql, NewBinCode, BarCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存子表数量更新失败！");
}
#endregion
#region 存入ICSWareHouseLotInfoLog表（日志）
sql = @"insert into ICSWareHouseLotInfoLog
Values
(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}'
,'{7}',{8},null,'{9}','物料移库','{10}','{11}',GETDATE(),'{12}','','','','','')";
sql = string.Format(sql, TranNo, TranLine, INVCode, BarCode, WHCode, OldBinCode, WHCode, NewBinCode, BarCodeQty, Type, userCode, userName, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表存入失败！");
}
#endregion
}
return isSuccess;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 验证盘点单号信息
/// </summary>
/// <param name="CheckNo"></param>
/// <param name="WorkPointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckCheckNo(string CheckNo, string WorkPointCode, string dsconn)
{
string sql = @"select * from ICSToCheck
where ToCheckNO='{0}' AND WorkPoint='{1}'";
sql = string.Format(sql, CheckNo, WorkPointCode);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 验证盘点条码信息
/// </summary>
/// <param name="CheckNo"></param>
/// <param name="BarCode"></param>
/// <param name="WorkPointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckLotnoForCheck(string CheckNo, string BarCode, string WorkPointCode, string dsconn)
{
DataTable dts = new DataTable();
#region 获取盘点单物料及仓库信息
string sql = @"select ItemCode,StorageCode from ICSToCheck
where ToCheckNO='{0}' AND WorkPoint='{1}'";
sql = string.Format(sql, CheckNo, WorkPointCode);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
sql = @"select WHCode
,BinCode
,A.LotNO
,A.INVCode
,B.INVNAME
,B.INVSTD
,A.LotQty
,D.LOTQTY AS ItemLotQTY
,ISNULL(BarCodeQty,0) AS BarCodeQty
,B.INVUOM
from ICSWareHouseLotInfo A
left join ICSINVENTORY B
on B.INVCODE=A.INVCode
LEFT JOIN ICSToChecks C
ON C.BarCode=A.LotNO
left join ICSITEMLot D
ON D.LOTNO=A.LotNO
where A.LotNO='{0}' AND A.INVCode='{1}' AND A.WHCode='{2}' AND A.WorkPoint='{3}'";
sql = string.Format(sql, BarCode, dt.Rows[i]["ItemCode"].ToString(), dt.Rows[i]["StorageCode"].ToString(), WorkPointCode);
DataTable lotdt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
if (lotdt.Rows.Count != 0)
{
dts = lotdt.Copy();
}
}
}
#endregion
return dts;
}
/// <summary>
/// 盘点扫描
/// </summary>
/// <param name="sqlConnection"></param>
/// <param name="trans"></param>
/// <param name="CheckNo"></param>
/// <param name="BarCodeList"></param>
/// <param name="BarCodeQtyList"></param>
/// <param name="UserCode"></param>
/// <param name="WorkPointCode"></param>
/// <param name="Type"></param>
/// <param name="EditType"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static bool CheckIn(SqlConnection sqlConnection, SqlTransaction trans, string CheckNo, string[] BarCodeList, string[] BarCodeQtyList, string userCode, string userName, string WorkPointCode, string Type, string EditType, string dsconn)
{
string sql = "";
string BarCode = "";
decimal BarCodeQty = 0;
decimal ItemLotQTY = 0;
string ConnectString = dsconn;
int result = 0;
bool isSuccess = true;
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Transaction = trans;
try
{
for (int i = 0; i < BarCodeList.Length; i++)
{
BarCode = BarCodeList[i];
BarCodeQty = Convert.ToDecimal(BarCodeQtyList[i].ToString());
string INVCode = "";
string WHCode = "";
string TranNo = "";
string TranLine = "";
decimal LotQty = 0;
string BinCode = "";
#region 获取仓库库位等信息
sql = @"select WHCode
,A.LotQty
,BinCode
,INVCode
,B.TransNO
,B.TransLine
from ICSWareHouseLotInfo A
left join ICSITEMLot B
on B.LotNO=A.LotNO
where A.LotNO='{0}' AND A.WorkPoint='{1}'";
sql = string.Format(sql, BarCode, WorkPointCode);
DataTable dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取仓库信息失败！");
}
else
{
LotQty = Convert.ToDecimal(dt.Rows[0]["LotQty"].ToString());
INVCode = dt.Rows[0]["INVCode"].ToString();
WHCode = dt.Rows[0]["WHCode"].ToString();
TranNo = dt.Rows[0]["TransNO"].ToString();
TranLine = dt.Rows[0]["TransLine"].ToString();
BinCode = dt.Rows[0]["BinCode"].ToString();
}
#endregion
#region 更新ICSToCheck表
sql = @"update ICSToCheck set ActualQty=ActualQty+{0}
where ToCheckNO='{1}' AND ItemCode='{2}' AND StorageCode='{3}' AND WorkPoint='{4}'";
sql = string.Format(sql, BarCodeQty, CheckNo, INVCode, WHCode, WorkPointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("盘点主表实盘数量增加失败！");
}
#endregion
#region 更新ICSToChecks表
sql = @"select * from ICSToChecks where BarCode='{0}'";
sql = string.Format(sql, BarCode);
dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
sql = @"insert into ICSToChecks
values
(NEWID(),'{0}','{1}',{7},{2},'{3}',GETDATE(),'{4}','{5}','','{6}','insert','{6}',GETDATE())";
sql = string.Format(sql, INVCode, BarCode, BarCodeQty, userName, WorkPointCode, CheckNo, userCode, LotQty);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("盘点子表存入失败！");
}
}
else
{
sql = @"update ICSToChecks
set BarCodeQty={0},Status='Update'
,Updater='{1}',UpdateTime=GETDATE()
where BarCode='{2}'
update ICSToCheck
set ActualQty=ActualQty-{3}
where ToCheckNO='{4}' and ItemCode='{5}' and StorageCode='{6}'
update ICSToCheck
set ActualQty=ActualQty+{0}
where ToCheckNO='{4}' and ItemCode='{5}' and StorageCode='{6}'";
sql = string.Format(sql, BarCodeQty, userCode, BarCode, Convert.ToDecimal(dt.Rows[0]["BarCodeQty"].ToString()), CheckNo, INVCode, WHCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("盘点子表复盘数量更新失败！");
}
}
#endregion
if (EditType == "修正")
{
#region 检查原条码数量
sql = @"select LotQty from ICSWareHouseLotInfo
where LotNO='{0}' AND WorkPoint='{1}'";
sql = string.Format(sql, BarCode, WorkPointCode);
DataTable lotdt = SQlReturnData(sql, cmd);
ItemLotQTY = Convert.ToDecimal(lotdt.Rows[0]["LotQty"]);
#endregion
#region 更新ICSWareHouseInfo表
sql = @"update ICSWareHouseInfo
set QTY=QTY-{0},MTIME='{1}'
where INVCode='{2}' AND WHCode='{3}' AND BinCode='{4}'
update ICSWareHouseInfo
set QTY=QTY+{5},MTIME='{1}'
where INVCode='{2}' AND WHCode='{3}' AND BinCode='{4}'";
sql = string.Format(sql, LotQty, DateTime.Now, INVCode, WHCode, BinCode, BarCodeQty);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表库存调整失败！");
}
#endregion
#region 更新ICSWareHouseLotInfo表
sql = @"update ICSWareHouseLotInfo set
LotQty={0}
where LotNO='{1}'";
sql = string.Format(sql, BarCodeQty, BarCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存表库存调整失败！");
}
#endregion
#region 存入ICSWareHouseLotInfoLog表（日志）
if (BarCodeQty > ItemLotQTY)
{
sql = @"insert into ICSWareHouseLotInfoLog(ID,TransNO,TransLine,ITEMCODE,LotNO,FRMStorageCODE,FRMStackCODE,TOStorageCODE,TOStackCODE,TransQTY,TransType,BusinessCode,MUSER,MUSERName,MTIME,WorkPoint)
Values
(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}',null,null,'{6}','盈','盘点','{7}','{8}',GETDATE(),'{9}')";
}
else
{
sql = @"insert into ICSWareHouseLotInfoLog(ID,TransNO,TransLine,ITEMCODE,LotNO,FRMStorageCODE,FRMStackCODE,TOStorageCODE,TOStackCODE,TransQTY,TransType,BusinessCode,MUSER,MUSERName,MTIME,WorkPoint)
Values
(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}',null,null,'{6}','亏','盘点','{7}','{8}',GETDATE(),'{9}')";
}
sql = string.Format(sql, CheckNo, "1", INVCode, BarCode, WHCode, BinCode, BarCodeQty - ItemLotQTY, userCode, userName, WorkPointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表存入失败！");
}
#endregion
}
}
return isSuccess;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 验证条码信息（其他出库）
/// </summary>
/// <param name="lotNo"></param>
/// <param name="workpointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckLotNoForOtherOut(string lotNo, string workpointCode, string dsconn)
{
string sql = @"select LotNO from ICSWareHouseLotInfo
WHERE LotNO='{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>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckLotNoForOtherIN(string TransferNO, string lotNo, string workpointCode, string dsconn)
{
string sql = @"select A.LotNO,B.cCode from ICSITEMLot A
LEFT JOIN ICSRdrecord09s B
ON B.cCode=A.TransNO AND B.iRSRowNO=A.TransLine AND FREE3='入'
WHERE A.LotNO='{0}' AND A.WorkPoint='{1}' AND B.cCode='{2}'";
sql = string.Format(sql, lotNo, workpointCode, TransferNO);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 验证物料退料库位信息
/// </summary>
/// <param name="Stack"></param>
/// <param name="WHCode"></param>
/// <param name="workpointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckStackCodeForBack(string Stack, string workpointCode, string dsconn)
{
string sql = @"select B.StorageCode from ICSStack A
LEFT JOIN ICSStorage B
ON B.Serial=A.Storage_Serial
WHERE StackCode='{0}' AND A.WorkPoint='{1}'";
sql = string.Format(sql, Stack, workpointCode);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 获取其他出库单源头单据信息
/// </summary>
/// <param name="TransferNO"></param>
/// <param name="workpointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckCodeForOtherOut(string TransferNO, string workpointCode, string dsconn)
{
string sql = @"select iRSRowNO
,cInvCode
,B.INVNAME
,B.INVSTD
,ISNULL(A.iQuantity,0) AS iQuantity
,ISNULL(A.iFQuantity,0) AS iFQuantity
,B.INVUOM
,A.cWhCode
from ICSRdrecord09s A
LEFT JOIN ICSINVENTORY B
ON B.INVCODE=A.cInvCode
WHERE cCode='{0}' AND Free3='出'";
sql = string.Format(sql, TransferNO);
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 GetLotDataForOtherOut(string BarCode, string WorkPointCode, string dsconn)
{
string sql = @"select A.LotNO
,A.WHCode
,A.INVCode
,B.INVNAME
,ISNULL(A.LotQty,0) AS LotQty
,A.BinCode
from ICSWareHouseLotInfo A
left join ICSINVENTORY B
ON B.INVCODE=A.INVCode
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 GetLotDataForOtherIN(string BarCode, string WorkPointCode, string dsconn)
{
string sql = @"select A.LotNO
,C.cWhCode
,A.ItemCode
,B.INVNAME
,ISNULL(A.LotQty,0) AS LotQty
from ICSITEMLot A
left join ICSINVENTORY B
ON B.INVCODE=A.ItemCode
left join ICSRdrecord09s C
on C.cCode=A.TransNO AND C.iRSRowNO=A.TransLine AND FREE3='入'
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="POCode"></param>
/// <param name="porowList"></param>
/// <param name="poqtyList"></param>
/// <param name="barcodeList"></param>
/// <param name="barcodeqtyList"></param>
/// <param name="bincodeList"></param>
/// <param name="userCodt"></param>
/// <param name="workpointCode"></param>
/// <param name="Type"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static bool WareHouseOtherOut(SqlConnection sqlConnection, SqlTransaction trans, string[] barcodeList, string OutType, string userCode, string userName, string workpointCode, string Type, string dsconn)
{
string sql = "";
string BarCode = "";
string cBarCode = "";
string Errormessage = "";
decimal LotQTY = 0;
string ConnectString = dsconn;
int result = 0;
bool isSuccess = true;
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Transaction = trans;
try
{
for (int i = 0; i < barcodeList.Length; i++)
{
BarCode = barcodeList[i];
if (cBarCode == "")
{
cBarCode += "'" + barcodeList[i] + "'";
}
else
{
cBarCode += ",'" + barcodeList[i] + "'";
}
string INVCode = "";
string WHCode = "";
string BinCode = "";
string TranNo = "";
string TranLine = "";
#region 获取仓库库位等信息
sql = @"select WHCode
,BinCode
,INVCode
,A.LotQty
,B.TransNO
,B.TransLine
from ICSWareHouseLotInfo A
left join ICSITEMLot B
on B.LotNO=A.LotNO
where A.LotNO='{0}' AND A.WorkPoint='{1}'";
sql = string.Format(sql, BarCode, workpointCode);
DataTable dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取仓库信息失败！");
}
else
{
LotQTY = Convert.ToDecimal(dt.Rows[0]["LotQty"].ToString());
INVCode = dt.Rows[0]["INVCode"].ToString();
BinCode = dt.Rows[0]["BinCode"].ToString();
WHCode = dt.Rows[0]["WHCode"].ToString();
TranNo = dt.Rows[0]["TransNO"].ToString();
TranLine = dt.Rows[0]["TransLine"].ToString();
}
#endregion
#region 更新ICSWareHouseInfo表
sql = @"update ICSWareHouseInfo
set QTY=QTY-{0},MTIME='{1}'
where INVCode='{2}' AND WHCode='{3}' AND BinCode='{4}'";
sql = string.Format(sql, LotQTY, DateTime.Now, INVCode, WHCode, BinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表更新失败！");
}
#endregion
#region 更新ICSWareHouseLotInfo表
sql = @"update ICSWareHouseLotInfo
set LotQty=LotQty-{0}
where LotNO='{1}'
and WorkPoint='{2}'";
sql = string.Format(sql, LotQTY, BarCode, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存子表更新失败！");
}
#endregion
#region 存入ICSWareHouseLotInfoLog表（日志）
sql = @"insert into ICSWareHouseLotInfoLog
Values
(NEWID(),'{0}','{1}','{2}','{3}','{9}','{10}','',''
,{4},null,'{5}','其他出库','{6}','{7}',GETDATE(),'{8}','','','','','未回写')";
sql = string.Format(sql, "", "", INVCode, BarCode, LotQTY, Type, userCode, userName, workpointCode, WHCode, BinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表存入失败！");
}
#endregion
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
if (message == "Open")
{
#region WEBAPI接口调用
// try
// {
// sql = @"select INVCode,WHCode,A.LotQty,B.VenderLotNO from ICSWareHouseLotInfo A
// LEFT JOIN ICSITEMLot B
// ON B.LotNO=A.LotNO
// where A.LotNO in ({0})
// Order by INVCode,WHCode";
// sql = string.Format(sql, cBarCode);
// DataTable dt = SQlReturnData(sql, cmd);
// List<RdRecord09> contextlist = new List<RdRecord09>();
// RdRecord09 context = new RdRecord09();
// List<rdrecords09> contextlists = new List<rdrecords09>();
// rdrecords09 contexts = new rdrecords09();
// string WareHouse = "";
// string ItemCode = "";
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// if (WareHouse != dt.Rows[i]["WHCode"].ToString() || ItemCode != dt.Rows[i]["INVCode"].ToString())
// {
// if (i > 0)
// {
// context.list = contextlists;
// contextlist.Add(context);
// }
// context = new RdRecord09();
// WareHouse = dt.Rows[0]["WHCode"].ToString();
// ItemCode = dt.Rows[0]["INVCode"].ToString();
// context.cWhCode = dt.Rows[0]["WHCode"].ToString();
// context.cRdCode = OutType;
// context.UserCode = userCode;
// contextlists = new List<rdrecords09>();
// }
// contexts = new rdrecords09();
// contexts.iQuantity = Convert.ToDecimal(dt.Rows[0]["LotQty"].ToString());
// contexts.cInvCode = dt.Rows[0]["INVCode"].ToString();
// contextlists.Add(contexts);
// if (i == dt.Rows.Count - 1)
// {
// context.list = contextlists;
// contextlist.Add(context);
// }
// }
// string Istr = JsonConvert.SerializeObject(contextlist);
// string iresult = HttpPost(IOtherOut, Istr);
// Result OtherOutResult = new Result();
// OtherOutResult = JsonConvert.DeserializeObject<Result>(iresult);
// if (OtherOutResult.code != "200")
// {
// throw new Exception(OtherOutResult.msg);
// }
// }
// catch (Exception ex)
// {
// throw new Exception(ex.Message);
// }
#endregion
}
return isSuccess;
}
/// <summary>
/// 验证条码信息（物料退料）
/// </summary>
/// <param name="BarCode"></param>
/// <param name="workpointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckLotnoForINVIN(string BarCode, string workpointCode, string dsconn)
{
string sql = @"select A.VouchCode
,A.VouchRow
,A.InvCode
,C.INVNAME
,C.INVSTD
,D.LOTNO AS WHLOTNO
,ISNULL(B.LOTQTY,0) LOTQTY
,ISNULL(A.Quantity,0) Quantity
,ISNULL(A.HasQuantity,0) HasQuantity
,C.INVUOM
,A.WHCode
,ISNULL(E.IsMROItem,0) AS IsMROItem
from ICSMaterialPick A
LEFT JOIN ICSITEMLot B
ON B.TransNO=A.VouchCode AND B.TransLine=A.VouchRow
LEFT JOIN ICSINVENTORY C
ON C.INVCODE=A.InvCode
LEFT JOIN ICSWareHouseLotInfo D
ON D.LOTNO=B.LOTNO
LEFT JOIN ICSMaterial E
ON E.VouchCode=A.VouchCode
WHERE B.LotNO='{0}' AND A.WorkPoint='{1}' AND A.MoveType='退料'";
sql = string.Format(sql, BarCode, workpointCode);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 获取其他入库单源头单据信息
/// </summary>
/// <param name="TransferNO"></param>
/// <param name="workpointCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable CheckCodeForOtherIN(string TransferNO, string workpointCode, string dsconn)
{
string sql = @"select iRSRowNO
,cInvCode
,B.INVNAME
,B.INVSTD
,ISNULL(A.iQuantity,0) AS iQuantity
,ISNULL(A.iFQuantity,0) AS iFQuantity
,B.INVUOM
,A.cWhCode
from ICSRdrecord09s A
LEFT JOIN ICSINVENTORY B
ON B.INVCODE=A.cInvCode
WHERE cCode='{0}' AND Free3='入'";
sql = string.Format(sql, TransferNO);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 获取用户信息
/// </summary>
/// <param name="UserCode"></param>
/// <param name="WorkPoint"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static DataTable GetUserInfo(string UserCode, string WorkPoint, string dsconn)
{
string sql = @"select UserCode,UserName from Sys_User
where UserCode='{0}' AND WorkPointCode='{1}'";
sql = string.Format(sql, UserCode, WorkPoint);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
/// <summary>
/// 其他入库
/// </summary>
/// <param name="POCode"></param>
/// <param name="porowList"></param>
/// <param name="poqtyList"></param>
/// <param name="barcodeList"></param>
/// <param name="barcodeqtyList"></param>
/// <param name="bincodeList"></param>
/// <param name="userCodt"></param>
/// <param name="workpointCode"></param>
/// <param name="Type"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static bool WareHouseOtherIN(SqlConnection sqlConnection, SqlTransaction trans, string POCode, string[] porowList, string[] poqtyList, string[] barcodeRowList, string[] barcodeList, string[] barcodeqtyList, string[] bincodelist, string userCode, string userName, string workpointCode, string Type, string dsconn)
{
string sql = "";
string BarCode = "";
decimal BarCodeQty = 0;
string BarCodeRow = "";
string cBarCode = "";
string Errormessage = "";
string PORow = "";
decimal POQty = 0;
string NewBinCode = "";
string ConnectString = dsconn;
int result = 0;
bool isSuccess = true;
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Transaction = trans;
try
{
for (int i = 0; i < porowList.Length; i++)
{
#region 更新ICSRdrecord09s表
PORow = porowList[i];
POQty = Convert.ToDecimal(poqtyList[i]);
sql = @"update ICSRdrecord09s set iQuantity=iQuantity+{0}
where cCode='{1}' and iRSRowNO='{2}'";
sql = string.Format(sql, POQty, POCode, PORow);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("源头单据更新失败！");
}
#endregion
}
for (int i = 0; i < barcodeList.Length; i++)
{
string INVCode = "";
string WHCode = "";
string BinCode = "";
string TranNo = "";
string TranLine = "";
BarCode = barcodeList[i];
BarCodeQty = Convert.ToDecimal(barcodeqtyList[i]);
BarCodeRow = barcodeRowList[i];
BinCode = bincodelist[i];
if (cBarCode == "")
{
cBarCode += "'" + barcodeList[i] + "'";
}
else
{
cBarCode += ",'" + barcodeList[i] + "'";
}
#region 获取仓库库位等信息
sql = @"select A.cWhCode
,B.ItemCode
,B.TransNO
,B.TransLine
from ICSRdrecord09s A
left join ICSITEMLot B
on B.TransNO=A.cCode AND B.TransLine=A.iRSRowNO
where B.LotNO='{0}' AND B.WorkPoint='{1}'";
sql = string.Format(sql, BarCode, workpointCode);
DataTable dt = SQlReturnData(sql, cmd);
if (dt.Rows.Count == 0)
{
throw new Exception("获取仓库信息失败！");
}
else
{
INVCode = dt.Rows[0]["ItemCode"].ToString();
WHCode = dt.Rows[0]["cWhCode"].ToString();
TranNo = dt.Rows[0]["TransNO"].ToString();
TranLine = dt.Rows[0]["TransLine"].ToString();
}
#endregion
#region 更新ICSWareHouseInfo表
sql = @"update ICSWareHouseInfo
set QTY=QTY+{0},MTIME='{1}'
where INVCode='{2}' AND WHCode='{3}' AND BinCode='{4}'";
sql = string.Format(sql, BarCodeQty, DateTime.Now, INVCode, WHCode, BinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
sql = @"insert into ICSWareHouseInfo
values
((select Serial from ICSStorage where StorageCode='{0}')
,'{0}'
,(SELECT Serial FROM ICSStack WHERE StackCode='{1}')
,'{1}'
,(SELECT ID FROM ICSINVENTORY WHERE INVCODE='{2}')
,'{2}',{3},'{4}','{5}','{6}',GETDATE(),NULL)";
sql = string.Format(sql, WHCode, BinCode, INVCode, BarCodeQty, workpointCode, userCode, userName);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存主表存入失败！");
}
}
#endregion
#region 更新ICSWareHouseLotInfo表
sql = @"update ICSWareHouseLotInfo
set LotQty=LotQty+{0}
where LotNO='{1}'
and WorkPoint='{2}'";
sql = string.Format(sql, BarCodeQty, BarCode, workpointCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
sql = @"insert into ICSWareHouseLotInfo
Values
(NEWID(),'{0}',
(select Serial from ICSStorage where StorageCode='{1}'),
'{1}',
(SELECT Serial FROM ICSStack WHERE StackCode='{2}'),
'{2}',
(SELECT ID FROM ICSINVENTORY WHERE INVCODE='{3}'),
'{3}',{4},GETDATE(),'{5}','{6}','{7}',GETDATE(),'','','')";
sql = string.Format(sql, BarCode, WHCode, BinCode, INVCode, BarCodeQty, workpointCode, userCode, userName);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("库存子表存入失败！");
}
}
#endregion
#region 存入ICSWareHouseLotInfoLog表（日志）
sql = @"insert into ICSWareHouseLotInfoLog
Values
(NEWID(),'{0}','{1}','{2}','{3}','','','{9}','{10}'
,{4},null,'{5}','其他入库','{6}','{7}',GETDATE(),'{8}','','','','','')";
sql = string.Format(sql, POCode, BarCodeRow, INVCode, BarCode, BarCodeQty, Type, userCode, userName, workpointCode, WHCode, BinCode);
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (isSuccess && result <= 0)
{
throw new Exception("日志表存入失败！");
}
#endregion
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
if (message == "Open")
{
#region 接口调用
// try
// {
// sql = @"select ID,
// cCode,
// cWhCode,
// cInvCode,
// AutoID,
// cBatch
// from ICSRdrecord09s
// where cCode='{0}' AND Free3='入'
// ORDER BY iRSRowNO";
// sql = string.Format(sql, POCode);
// DataTable dt = SQlReturnData(sql, cmd);
// List<ZYPDAServiceForOtherIN.Rdrecord08> contextList = new List<ZYPDAServiceForOtherIN.Rdrecord08>();
// List<ZYPDAServiceForOtherIN.Rdrecords08> contextLists = new List<ZYPDAServiceForOtherIN.Rdrecords08>();
// ZYPDAServiceForOtherIN.Rdrecord08 context = new ZYPDAServiceForOtherIN.Rdrecord08();
// ZYPDAServiceForOtherIN.Rdrecords08 contexts = new ZYPDAServiceForOtherIN.Rdrecords08();
// string WareHouse = "";
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// if (WareHouse != dt.Rows[i]["cWhCode"].ToString())
// {
// if (i > 0)
// {
// context.bodyList = contextLists.ToArray();
// contextList.Add(context);
// }
// context = new ZYPDAServiceForOtherIN.Rdrecord08();
// WareHouse = dt.Rows[i]["cWhCode"].ToString();
// context.ID = dt.Rows[i]["ID"].ToString();
// context.cCode = dt.Rows[i]["cCode"].ToString();
// context.cWhCode = dt.Rows[i]["cWhCode"].ToString();
// context.UserCode = userCode;
// contextLists = new List<ZYPDAServiceForOtherIN.Rdrecords08>();
// }
// contexts = new ZYPDAServiceForOtherIN.Rdrecords08();
// contexts.cInvCode = dt.Rows[i]["cInvCode"].ToString();
// contexts.iQuantity = Convert.ToDecimal(poqtyList[i].ToString());
// contexts.cBatch = dt.Rows[i]["cBatch"].ToString();
// contexts.AutoID = dt.Rows[i]["AutoID"].ToString();
// contextLists.Add(contexts);
// if (i == dt.Rows.Count - 1)
// {
// context.bodyList = contextLists.ToArray();
// contextList.Add(context);
// }
// }
// ZYPDAServiceForOtherIN.VerifiRd08Client client = new ZYPDAServiceForOtherIN.VerifiRd08Client();
// ZYPDAServiceForOtherIN.Result cresult = new ZYPDAServiceForOtherIN.Result();
// cresult = client.Verifi08(contextList.ToArray());
// if (cresult.IsSuccess == false)
// {
// throw new Exception(cresult.MESSAGE);
// }
// }
// catch (Exception ex)
// {
// throw new Exception(ex.Message);
// }
#endregion
}
return isSuccess;
}
/// <summary>
/// 先进先出批次管控
/// </summary>
/// <param name="LotNo"></param>
/// <param name="WorkPoitCode"></param>
/// <param name="dsconn"></param>
/// <returns></returns>
public static string CanOut(string LotNo, string WorkPoitCode, string dsconn)
{
try
{
string sql = @"DECLARE @CountNO INT
SET @CountNO=(
SELECT
COUNT(LotNO)
FROM
ICSWareHouseLotInfo a
WHERE CONVERT(varchar(100), MTIME, 23)<(SELECT CONVERT(varchar(100), MTIME, 23) FROM ICSWareHouseLotInfo WHERE INVCode=a.INVCode AND LotNO='{0}' AND WorkPoint='{1}' AND ISNULL(LotQty,0)>0)
AND WorkPoint='{1}' AND ISNULL(LotQty,0)>0
)
IF @CountNO>0
BEGIN
SELECT TOP 1 CASE EnumValue WHEN '0000501' THEN '1' WHEN '0000502' THEN '2' ELSE '3' END AS Type
FROM Sys_EnumValues
WHERE EnumKey='00005' AND StartFlag='1'
ORDER BY ID DESC
END
ELSE
BEGIN
SELECT @CountNO
END";
//0 - 已经是最早的批次
//1 - 不管控
//2 - 提醒
//3 - 限制
sql = string.Format(sql, LotNo, WorkPoitCode);
DataTable whDt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
if (whDt == null || whDt.Rows.Count == 0)
{
throw new Exception("查询管控信息失败!!");
}
else
return whDt.Rows[0][0].ToString();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 接口调用方法
/// </summary>
/// <param name="url"></param>
/// <param name="body"></param>
/// <returns></returns>
public static string HttpPost(string url, string body)
{
try
{
Encoding encoding = Encoding.UTF8;
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
request.Method = "POST";
request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*";
request.ContentType = "application/json; charset=utf-8";
byte[] buffer = encoding.GetBytes(body);
request.ContentLength = buffer.Length;
request.GetRequestStream().Write(buffer, 0, buffer.Length);
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
using (StreamReader reader = new StreamReader(response.GetResponseStream(), encoding))
{
return reader.ReadToEnd();
}
}
catch (WebException ex)
{
var res = (HttpWebResponse)ex.Response;
StringBuilder sb = new StringBuilder();
StreamReader sr = new StreamReader(res.GetResponseStream(), Encoding.UTF8);
sb.Append(sr.ReadToEnd());
//string ssb = sb.ToString();
throw new Exception(sb.ToString());
}
}
#region
public static DataTable FormINVSer(string INVCode, string WorkPoint, string dsconn)
{
string sql = @"select A.WHCode
, A.BinCode
, A.INVCode
, B.INVNAME
, B.INVSTD
, A.LotQty
,C.BinQTY
,D.WHLotQTY
,E.VenderLotNO
from ICSWareHouseLotInfo A
LEFT JOIN ICSINVENTORY B
ON B.INVCODE=A.INVCode
LEFT JOIN ICSITEMLot E
ON E.LotNO=A.LotNO
LEFT JOIN (select SUM(LotQty) as BinQTY,BinCode from ICSWareHouseLotInfo
where BinCode IN
(select BinCode from ICSWareHouseLotInfo
where INVCode=
(select INVCode from ICSWareHouseLotInfo
where LotNO='{0}'
AND WorkPoint='{1}')
AND WorkPoint='{1}')
AND INVCode=
(select INVCode from ICSWareHouseLotInfo
where LotNO='{0}'
AND WorkPoint='{1}') GROUP BY BinCode) C
ON C.BinCode=A.BinCode
LEFT JOIN (select Sum(LotQty) AS WHLotQTY,INVCode from ICSWareHouseLotInfo
where INVCode =
(select INVCode from ICSWareHouseLotInfo
where LotNO='{0}'
AND WorkPoint='{1}') GROUP BY INVCode) D
ON D.INVCode=A.INVCode
WHERE A.INVCode=
(select ItemCode from ICSITEMLot
where LotNO='{0}'
AND WorkPoint='{1}')
AND A.WorkPoint='{1}' AND A.LotQty > 0";
sql = string.Format(sql, INVCode, WorkPoint);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
public static DataTable FormBinSer(string BinCode, string WorkPoint, string dsconn)
{
string sql = @"SELECT A.WHCode, A.BinCode, A.INVCode, B.INVNAME, B.INVSTD, A.LotQty, B.INVUOM,C.VenderLotNO
FROM ICSWareHouseLotInfo A LEFT JOIN ICSINVENTORY B ON A.INVCode = B.INVCODE
LEFT JOIN ICSITEMLot C ON C.LotNO=A.LotNO
WHERE 1=1 AND A.BinCode = '{0}' AND A.WorkPoint = '{1}' AND A.LotQty > 0 ";
sql = string.Format(sql, BinCode, WorkPoint);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
public static DataTable FormLotSer(string LotNO, string WorkPoint, string dsconn)
{
string sql = @"SELECT A.WHCode, A.BinCode, A.INVCode, B.INVNAME, B.INVSTD, A.LotQty, B.INVUOM,
QTY = (SELECT SUM(C.QTY) FROM ICSWareHouseInfo C LEFT JOIN ICSWareHouseLotInfo D ON C.INVCode = D.INVCode
WHERE 1=1 AND D.LotNO = '{0}' AND D.WorkPoint = '{1}')
FROM ICSWareHouseLotInfo A LEFT JOIN ICSINVENTORY B ON A.INVCode = B.INVCODE
WHERE 1=1 AND A.LotNO = '{0}' AND A.WorkPoint = '{1}' ";
sql = string.Format(sql, LotNO, WorkPoint);
DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
return dt;
}
#endregion
}
}