|
|
using Newtonsoft.Json.Linq;using Newtonsoft.Json;using NFine.Code;using NFine.Data.Extensions;using NFine.Domain._03_Entity.SRM;using NFine.Repository;using System;using System.Collections.Generic;using System.Configuration;using System.Data.Common;using System.Data;using System.IO;using System.Linq;using System.Net;using System.Text;using System.Threading.Tasks;using NFine.Application.Entity;using NFine.Application.Models;using NFine.Application.WMS;using System.Web.UI.WebControls;using System.Net.NetworkInformation;using System.Collections;using System.Xml;using System.Data.SqlClient;using System.Reflection.Emit;
namespace NFine.Application.MFWMS{ public class ICSMOIssueSendBackApp : RepositoryFactory<ICSVendor> { public static DataTable Invmes = new DataTable();
/// <summary>
/// 产成品退库 汇总查询
/// </summary>
/// <param name="queryJson"></param>
/// <param name="jqgridparam"></param>
/// <returns></returns>
public DataTable GetMOIssueSendBackApplyNeg(string queryJson, ref Pagination jqgridparam) { //站点信息
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; DataTable dt = new DataTable(); List<DbParameter> parameter = new List<DbParameter>(); var queryParam = queryJson.ToJObject(); object Figure = GetDecimalDigits(); #region [SQL]
// 同步过来的红字（退库） 产成品入库
string sql = @" select * from (select a.RCVCode, a.MUSERName,a.MTIME,a.Status,a.Type ,b.WarehouseCode as WHCode,b.WarehouseName as WHName
from ICSManufactureReceive a join ICSWarehouse b on a.WHCode = b.WarehouseCode and a.WorkPoint = b.WorkPoint join ICSInventory c on a.InvCode = c.InvCode and a.WorkPoint = b.WorkPoint where a.Type = '2' and ISNULL(a.SourceCode,'') != '' ";
sql = string.Format(sql, Figure); sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName()); #endregion
if (!string.IsNullOrWhiteSpace(queryJson)) { if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString())) { sql += " and a.RCVCode like '%" + queryParam["POCode"].ToString() + "%' "; } if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString())) { sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' "; } if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString())) { sql += " and c.InvName like '%" + queryParam["InvName"].ToString() + "%' "; } if (!string.IsNullOrWhiteSpace(queryParam["WHCode"].ToString())) { sql += " and b.WarehouseCode like '%" + queryParam["WHCode"].ToString() + "%' "; } if (!string.IsNullOrWhiteSpace(queryParam["WHName"].ToString())) { sql += " and b.WarehouseName like '%" + queryParam["WHName"].ToString() + "%' "; } if (!string.IsNullOrWhiteSpace(queryParam["FromTime"].ToString())) { sql += " and a.CreateDateTime >= '" + queryParam["FromTime"].ToString() + "' "; } if (!string.IsNullOrWhiteSpace(queryParam["ToTime"].ToString())) { sql += " and a.CreateDateTime <= '" + queryParam["ToTime"].ToString() + "' "; } if (!string.IsNullOrWhiteSpace(queryParam["F_Type"].ToString())) { var status = queryParam["F_Type"].ToString(); if (status == "1") { sql += " and a.Status in ('2','3') "; } else if (status == "2") { sql += " and a.Status = '1' "; } } } if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin") { sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'"; } sql += @" GROUP BY a.RCVCode, a.MUSERName,a.MTIME,a.Status,a.Type,b.WarehouseCode,b.WarehouseName ) t
where 1=1 ";
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam); } public object GetDecimalDigits() { string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(','); try { string sql = string.Empty; sql = @"select Figure from ICSConfiguration where Code='Figure001' and Enable='1' and WorkPoint='" + WorkPoint + "'"; object Figure = SqlHelper.ExecuteScalar(sql); return Figure; } catch (Exception ex) { throw new Exception(ex.Message.ToString()); } } /// <summary>
/// 订单备料明细查询
/// </summary>
/// <param name="jqgridparam"></param>
/// <returns></returns>
public DataTable GetMOIssueApplyNegDetail(string RCVCode, ref Pagination jqgridparam) { DataTable dt = new DataTable(); //object Figure = GetDecimalDigits();
List<DbParameter> parameter = new List<DbParameter>(); string sql = @"SELECT a.ID,a.RCVCode,a.Sequence,a.SourceSequence,a.SourceCode,a.InvCode,a.Quantity,a.Amount,a.RCVQuantity,a.WHCode,e.WarehouseName as WHName,a.Type
,a.ExtensionID,a.MUSER,a.MUSERName,a.MTIME,a.WorkPoint,b.InvName,a.CreateDateTime,a.CreatePerson ,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4 ,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10 ,a.EATTRIBUTE1,a.EATTRIBUTE2,a.EATTRIBUTE3,a.EATTRIBUTE4,a.EATTRIBUTE5,a.EATTRIBUTE6,a.EATTRIBUTE7,a.EATTRIBUTE8,a.EATTRIBUTE9,a.EATTRIBUTE10from ICSManufactureReceive a left join ICSInventory b on a.InvCode = b.InvCode and a.WorkPoint = b.WorkPoint left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint left join ICSWarehouse e on a.WHCode = e.WarehouseCode and a.WorkPoint=e.WorkPoint where a.RCVCode = '" + RCVCode + "' ";
//sql = string.Format(sql, Figure);
DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam); return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam); }
public void ClearTemp() { Invmes.Rows.Clear(); } /// <summary>
/// 弃审
/// </summary>
/// <param name="keyValue"></param>
/// <returns></returns>
public string ICSMOIssueSendBackAuditRollback(string keyValue) { string MUSER = OperatorProvider.Provider.GetCurrent().UserCode; string MUSERNAME = OperatorProvider.Provider.GetCurrent().UserName; string WorkPoint = OperatorProvider.Provider.GetCurrent().Location; var Dates = DateTime.Now; var sql = string.Empty; var Batchids = Guid.NewGuid().ToString(); var sqlBuilder = new StringBuilder(); try { // 优先根据Batchids去找修改记录 完成弃审反向修改库存 旧数据ICSManufactureReceive没有EATTRIBUTE1记录批次id，所以保留旧逻辑
string bidInfosql = @"select a.TransCode,a.TransSequence,a.LotNo,a.InvCode,a.Quantity,a.FromWarehouseCode,a.FromLocationCode from ICSWareHouseLotInfoLog a
join (select EATTRIBUTE1 from ICSManufactureReceive where RCVCode in ({0}) group by EATTRIBUTE1) b on a.Identification = b.EATTRIBUTE1 where a.WorkPoint='{1}'";
bidInfosql = string.Format(bidInfosql, keyValue, WorkPoint); DataTable bidInfosqldt = SqlHelper.GetDataTableBySql(bidInfosql); if (bidInfosqldt.Rows.Count > 0) { foreach (DataRow grMFitem in bidInfosqldt.Rows) { var qty = grMFitem["Quantity"].ToDecimal(); // 1. 库存更新
sqlBuilder.AppendFormat( @"UPDATE ICSWareHouseLotInfo
SET Quantity = Quantity + {0}, MUSER = '{1}', MUSERName = '{2}', MTIME = '{3}' WHERE LotNo = '{4}'; ",
-(qty), MUSER.Replace("'", "''"), MUSERNAME.Replace("'", "''"), Dates.ToString("yyyy-MM-dd HH:mm:ss").Replace("'", "''"), grMFitem["LotNo"].ToString().Replace("'", "''") ); // 2. 库存日志
sqlBuilder.AppendFormat( @"INSERT INTO ICSWareHouseLotInfoLog (ID, Identification, TransCode, TransSequence,
FromWarehouseCode, FromLocationCode, LotNo, [Lock], TransType, BusinessCode, Quantity, InvCode, MUSER, MUSERName, MTIME, WorkPoint) VALUES (NEWID(), '{11}', '{0}', '{1}', '{2}', '{3}', '{4}', 0, '17', '75', {5}, '{6}', '{7}', '{8}', '{9}', '{10}'); ",
grMFitem["TransCode"].ToString().Replace("'", "''"), // 0: TransCode
grMFitem["TransSequence"].ToString().Replace("'", "''"), // 1: TransSequence
grMFitem["FromWarehouseCode"].ToString().Replace("'", "''"), // 2: FromWarehouseCode
grMFitem["FromLocationCode"].ToString().Replace("'", "''"), // 3: FromLocationCode
grMFitem["LotNo"].ToString().Replace("'", "''"), // 4: LotNo
-(qty), // 5: 数量
grMFitem["InvCode"].ToString().Replace("'", "''"), // 6: InvCode
MUSER.Replace("'", "''"), // 7: MUSER
MUSERNAME.Replace("'", "''"), // 8: MUSERName
Dates.ToString("yyyy-MM-dd HH:mm:ss").Replace("'", "''"), // 9: MTIME
WorkPoint.Replace("'", "''"), // 10: WorkPoint
Batchids // 11: Identification
); } } else {
//增加库存逆向记录
#region 入库表修改库存
//选中单据根据物料分组
string groupManufactureReceivesql = @"select a.RCVCode,a.Sequence,a.InvCode,Sum(a.Quantity) as InvQty,status,WHCode,e.BatchCode
from ICSManufactureReceive a inner JOIN ICSInventory c ON a.InvCode=c.InvCode and a.WorkPoint=c.WorkPoint --and c.InvIQC='1' inner JOIN ICSExtension e on a.ExtensionID = e.ID and a.WorkPoint = e.WorkPoint where a.RCVCode in ({0}) and a.WorkPoint='{1}' group by a.RCVCode,a.Sequence,a.InvCode,status,e.BatchCode,WHCode";
groupManufactureReceivesql = string.Format(groupManufactureReceivesql, keyValue, WorkPoint); DataTable groupManufactureReceivesqldt = SqlHelper.GetDataTableBySql(groupManufactureReceivesql);
//关联库存表 单个或多个库存 库存数量先进先出扣除
sql = @"
select a.RCVCode,a.Sequence,a.InvCode,d.LotNo,g.Quantity,g.InDate,g.ID,g.WarehouseCode,g.LocationCode,a.Quantity as RCVQuantity,e.BatchCode from ICSManufactureReceive a inner JOIN ICSInventory c ON a.InvCode=c.InvCode and a.WorkPoint=c.WorkPoint --and c.InvIQC='1' inner JOIN ICSInventoryLot d ON a.ExtensionID=d.ExtensionID and a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint and d.type<>'101' --and d.Type = '13' inner JOIN ICSExtension e on a.ExtensionID = e.ID and ISNULL(e.BatchCode,'') != '' and a.WorkPoint = e.WorkPoint inner JOIN dbo.ICSWareHouseLotInfo g ON g.LotNo=d.LotNo and a.WHCode = g.WarehouseCode and g.WorkPoint=a.WorkPoint where a.RCVCode in ({0}) and a.WorkPoint='{1}' order by e.BatchCode asc";
sql = string.Format(sql, keyValue, WorkPoint); DataTable MfRetables = SqlHelper.GetDataTableBySql(sql); if (MfRetables.Rows.Count == 0) { throw new Exception("产成品单据库存不足，请先检查库存"); }
foreach (DataRow grMFitem in groupManufactureReceivesqldt.Rows) { //检查状态
if (grMFitem["status"].ToString() != "2") { throw new Exception("选中行数据非审核状态，弃审失败。"); } //var First = MfRetables.Select("RCVCode = '" + grMFitem["RCVCode"].ToString() + "'and Sequence='" + grMFitem["Sequence"].ToString() + "' and LotNO <> '' and BatchCode = '" + grMFitem["BatchCode"].ToString() + "'").FirstOrDefault();
//if (First == null)
//{
// throw new Exception($"未找到对应的库存记录（RCVCode: {grMFitem["RCVCode"]}, Sequence: {grMFitem["Sequence"]}, BatchCode: {grMFitem["BatchCode"]}）");
//}
// 获取库存记录
var filter = $"RCVCode = '{grMFitem["RCVCode"]}' and Sequence='{grMFitem["Sequence"]}'and LotNO <> '' and WarehouseCode = '{grMFitem["WHCode"]}' and BatchCode = '{grMFitem["BatchCode"]}'"; DataRow First = MfRetables.Select(filter).FirstOrDefault();
if (First == null) { throw new Exception($"未找到库存记录（RCVCode: {grMFitem["RCVCode"]}, " + $"Sequence: {grMFitem["Sequence"]},仓库编码: {grMFitem["WHCode"]}）"); } #region 库存表修改 注释
//库存表修改 根据退库表的数量 按先进先出 扣除库存
//var LotEntity = MsSqlData.Get<ICSWareHouseLotInfo>(First["ID"].ToString());
//if (LotEntity != null)
//{
// //此处改为扣除数量后的剩余数量
// LotEntity.Quantity = LotEntity.Quantity + grMFitem["InvQty"].ToDecimal();
// LotEntity.MUSER = MUSER;
// LotEntity.MUSERName = MUSERNAME;
// LotEntity.MTIME = dateNow.ToString("yyyy-MM-dd HH:mm:ss");
// var result2 = MsSqlData.Update<ICSWareHouseLotInfo>(LotEntity);
// if (!result2)
// {
// returnValue = "产成品库存表修改失败";
// }
//}
#endregion
// 1. 库存更新
sqlBuilder.AppendFormat( @"UPDATE ICSWareHouseLotInfo
SET Quantity = Quantity + {0}, MUSER = '{1}', MUSERName = '{2}', MTIME = '{3}' WHERE ID = '{4}'; ",
grMFitem["InvQty"].ToDecimal(), MUSER.Replace("'", "''"), MUSERNAME.Replace("'", "''"), Dates.ToString("yyyy-MM-dd HH:mm:ss").Replace("'", "''"), First["ID"].ToString().Replace("'", "''") ); // 2. 库存日志
sqlBuilder.AppendFormat( @"INSERT INTO ICSWareHouseLotInfoLog (ID, Identification, TransCode, TransSequence,
FromWarehouseCode, FromLocationCode, LotNo, [Lock], TransType, BusinessCode, Quantity, InvCode, MUSER, MUSERName, MTIME, WorkPoint) VALUES (NEWID(), '{11}', '{0}', '{1}', '{2}', '{3}', '{4}', 0, '17', '75', {5}, '{6}', '{7}', '{8}', '{9}', '{10}'); ",
First["RCVCode"].ToString().Replace("'", "''"), // 0: TransCode
First["Sequence"].ToString().Replace("'", "''"), // 1: TransSequence
First["WarehouseCode"].ToString().Replace("'", "''"), // 2: FromWarehouseCode
First["LocationCode"].ToString().Replace("'", "''"), // 3: FromLocationCode
First["LotNo"].ToString().Replace("'", "''"), // 4: LotNo
-(grMFitem["InvQty"].ToDecimal()), // 5: 数量
First["InvCode"].ToString().Replace("'", "''"), // 6: InvCode
MUSER.Replace("'", "''"), // 7: MUSER
MUSERNAME.Replace("'", "''"), // 8: MUSERName
Dates.ToString("yyyy-MM-dd HH:mm:ss").Replace("'", "''"), // 9: MTIME
WorkPoint.Replace("'", "''"), // 10: WorkPoint
Batchids // 11: Identification
); #region 库存日志记录 注释
//库存日志记录 产成品退库
//var entity = new ICSWareHouseLotInfoLog();
////ConvertExt.Mapping<ICSWareHouseLotInfoLogEdit, ICSWareHouseLotInfoLog>(model, entity);
//entity.ID = Guid.NewGuid().ToString();
//entity.Identification = Guid.NewGuid().ToString();
//entity.TransCode = MfRetables.Rows[0]["RCVCode"].ToString();
//entity.TransSequence = MfRetables.Rows[0]["Sequence"].ToString();
//entity.FromWarehouseCode = MfRetables.Rows[0]["WarehouseCode"].ToString();
//entity.FromLocationCode = MfRetables.Rows[0]["LocationCode"].ToString();
//entity.LotNo = MfRetables.Rows[0]["LotNo"].ToString();
//entity.Lock = false;
//entity.TransType = "17";//铭锋产成品退库
//entity.BusinessCode = "75";//铭锋产成品退库
//entity.Quantity = -grMFitem["InvQty"].ToDecimal();
//entity.InvCode = MfRetables.Rows[0]["InvCode"].ToString();
//entity.MUSER = MUSER;
//entity.MUSERName = MUSERNAME;
//entity.MTIME = Dates;
//entity.WorkPoint = WorkPoint;
//var result = MsSqlData.Insert<ICSWareHouseLotInfoLog>(entity);
//if (!result)
//{
// returnValue = "新增库存日志记录失败";
//}
#endregion
} #endregion
#region 材料出库表修改库存
// 红字生产入库单ICSManufactureReceive 会生成对应的红字生成领料单 也就是ICSMOIssue材料出库表
//选中的物料 计算扣除数量
string groupmoIssuesql = @"select a.InvCode,Sum(a.Quantity) as InvQty,a.WHCode,e.BatchCode
from ICSMOIssue a inner JOIN ICSInventory c ON a.InvCode=c.InvCode and a.WorkPoint=c.WorkPoint inner JOIN ICSExtension e on a.ExtensionID = e.ID and a.WorkPoint = e.WorkPoint where a.EATTRIBUTE1 in ({0}) and a.WorkPoint='{1}' group by a.InvCode,a.WHCode,e.BatchCode";
groupmoIssuesql = string.Format(groupmoIssuesql, keyValue, WorkPoint); DataTable groupmoIssuesqldt = SqlHelper.GetDataTableBySql(groupmoIssuesql); //铭锋 因为客户在ERP操作了入库 但在WMS操作退库 没有找到条码就生成一个条码
if (groupmoIssuesqldt.Rows.Count <= 0) { //returnValue = "未找到对应的材料出库单。";
throw new Exception("未找到对应的材料出库单。"); } //关联库存表 单个或多个库存 库存数量不够时先进先出扣除
sql = @"
select a.IssueCode,a.Sequence,a.InvCode,d.LotNo,g.Quantity,g.InDate,g.ID,g.WarehouseCode,g.LocationCode,e.BatchCode from ICSMOIssue a inner JOIN ICSInventory c ON a.InvCode=c.InvCode and a.WorkPoint=c.WorkPoint --and c.InvIQC='1' inner JOIN ICSInventoryLot d ON a.ExtensionID=d.ExtensionID and a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint -- and d.type<>'101' and d.Type = '13' inner JOIN ICSExtension e on a.ExtensionID = e.ID and ISNULL(e.BatchCode,'') != '' and a.WorkPoint = e.WorkPoint inner JOIN dbo.ICSWareHouseLotInfo g ON g.LotNo=d.LotNo and a.WHCode = g.WarehouseCode and g.WorkPoint=a.WorkPoint where a.EATTRIBUTE1 in ({0}) and a.WorkPoint='{1}' order by e.BatchCode asc";
sql = string.Format(sql, keyValue, WorkPoint); DataTable tables = SqlHelper.GetDataTableBySql(sql); if (tables.Rows.Count <= 0) { //returnValue = "物料未找到条码库存，请联系管理员补充资料。";
throw new Exception("物料未找到条码库存，请联系管理员补充资料。"); } //材料出库数据 物料循环 物料总数量扣除
foreach (DataRow groupdr in groupmoIssuesqldt.Rows) { string InvCode = groupdr["InvCode"].ToString().Replace("'", "''"); string WhCode = groupdr["WHCode"].ToString().Replace("'", "''"); //同一产品需增加的数量 退库为负数
var InvQty = decimal.Parse(groupdr["InvQty"].ToString()); DataRow[] tableRows = tables.Select("InvCode = '" + groupdr["InvCode"].ToString() + "' and WarehouseCode = '" + groupdr["WHCode"].ToString() + "' and BatchCode = '" + groupdr["BatchCode"].ToString() + "'"); if (tableRows.Length <= 0) { throw new Exception($"物料编码:{InvCode}，仓库编码：{WhCode}，批次：{groupdr["BatchCode"].ToString()}，未找到条码库存。"); } //先进先出第一个条码 增加数量
DataRow MoIssueLot = tableRows[0]; string lotId = MoIssueLot["ID"].ToString().Replace("'", "''"); // 条码当前数量
var Quantity = decimal.Parse(MoIssueLot["Quantity"].ToString());
// 本次修改后的数量
var LogQty = Quantity + InvQty; // 1. 库存更新
sqlBuilder.AppendFormat( @"UPDATE ICSWareHouseLotInfo
SET Quantity = {0}, MUSER = '{1}', MUSERName = '{2}', MTIME = '{3}' WHERE ID = '{4}'; ",
LogQty, MUSER.Replace("'", "''"), MUSERNAME.Replace("'", "''"), Dates.ToString("yyyy-MM-dd HH:mm:ss").Replace("'", "''"), lotId ); // 2. 库存日志
sqlBuilder.AppendFormat( @"INSERT INTO ICSWareHouseLotInfoLog (ID, Identification, TransCode, TransSequence,
FromWarehouseCode, FromLocationCode, LotNo, [Lock], TransType, BusinessCode, Quantity, InvCode, MUSER, MUSERName, MTIME, WorkPoint) VALUES (NEWID(), '{11}', '{0}', '{1}', '{2}', '{3}', '{4}', 0, '17', '75', {5}, '{6}', '{7}', '{8}', '{9}', '{10}'); ",
MoIssueLot["IssueCode"].ToString().Replace("'", "''"), MoIssueLot["Sequence"].ToString().Replace("'", "''"), MoIssueLot["WarehouseCode"].ToString().Replace("'", "''"), MoIssueLot["LocationCode"].ToString().Replace("'", "''"), MoIssueLot["LotNo"].ToString().Replace("'", "''"), -InvQty, InvCode, MUSER.Replace("'", "''"), MUSERNAME.Replace("'", "''"), Dates.ToString("yyyy-MM-dd HH:mm:ss.fff").Replace("'", "''"), WorkPoint.Replace("'", "''"), Batchids ); #region 库存日志记录 库存表修改 注释
//库存表修改 根据退库表的数量 按先进先出 扣除库存
//var LotEntity = MsSqlData.Get<ICSWareHouseLotInfo>(MoIssueLot["ID"].ToString());
//if (LotEntity != null)
//{
// LotEntity.Quantity = LogQty;
// LotEntity.MUSER = MUSER;
// LotEntity.MUSERName = MUSERNAME;
// LotEntity.MTIME = dateNow.ToString("yyyy-MM-dd HH:mm:ss");
// var result2 = MsSqlData.Update<ICSWareHouseLotInfo>(LotEntity);
// if (!result2)
// {
// returnValue = "库存表修改失败";
// }
//}
//库存日志记录
//var entity = new ICSWareHouseLotInfoLog();
////ConvertExt.Mapping<ICSWareHouseLotInfoLogEdit, ICSWareHouseLotInfoLog>(model, entity);
//entity.ID = Guid.NewGuid().ToString();
//entity.Identification = Guid.NewGuid().ToString();
//entity.TransCode = MoIssueLot["IssueCode"].ToString();
//entity.TransSequence = MoIssueLot["Sequence"].ToString();
//entity.FromWarehouseCode = MoIssueLot["WarehouseCode"].ToString();
//entity.FromLocationCode = MoIssueLot["LocationCode"].ToString();
//entity.LotNo = MoIssueLot["LotNo"].ToString();
//entity.Lock = false;
//entity.TransType = "17";//铭锋产成品退库
//entity.BusinessCode = "75";//铭锋产成品退库
//entity.Quantity = -InvQty;
//entity.InvCode = groupdr["InvCode"].ToString();
//entity.MUSER = MUSER;
//entity.MUSERName = MUSERNAME;
//entity.MTIME = Dates;
//entity.WorkPoint = WorkPoint;
//var result = MsSqlData.Insert<ICSWareHouseLotInfoLog>(entity);
//if (!result)
//{
// returnValue = "新增库存日志记录失败";
//}
#endregion
}
#endregion
}
//修改审核状态
// 1. 材料出库表状态
sqlBuilder.AppendFormat( @"UPDATE ICSMOIssue SET Status = '1'
WHERE EATTRIBUTE1 IN ({0}) AND WorkPoint = '{1}' AND Status = '2'; IF @@ROWCOUNT = 0 RAISERROR('修改材料出库表状态失败', 16, 1); ",
keyValue, WorkPoint ); // 2. 产成品入库表状态
sqlBuilder.AppendFormat( @"UPDATE ICSManufactureReceive SET Status = '1',EATTRIBUTE1 = '{2}'
WHERE RCVCode IN ({0}) AND WorkPoint = '{1}' AND Status = '2'; IF @@ROWCOUNT = 0 RAISERROR('修改产成品入库表状态失败', 16, 1); ",
keyValue, WorkPoint,Batchids ); // 3. 修改工单入库数量
//工单表：ICSMO表RCVQuantity字段 ----减去产红字产成品入库单数量
//工单子件表：ICSMOPICK表IssueQuantity字段 ----- 加上红字材料出库单的数量
sqlBuilder.AppendFormat( @"UPDATE ICSMO
SET RCVQuantity = RCVQuantity + b.SumQuantity FROM ICSMO a JOIN ( SELECT SourceCode, SourceSequence, SUM(Quantity) AS SumQuantity FROM ICSManufactureReceive WHERE RCVCode IN ({0}) AND WorkPoint = '{1}' GROUP BY SourceCode, SourceSequence ) b ON b.SourceCode = a.MOCode AND b.SourceSequence = a.Sequence; IF @@ROWCOUNT = 0 RAISERROR('修改工单数量失败', 16, 1); ",
keyValue, WorkPoint ); // 4. 领料单数量
sqlBuilder.AppendFormat( @"UPDATE ICSMOPick
SET IssueQuantity = IssueQuantity + b.SumQuantity FROM ICSMOPick a JOIN ( SELECT PickID, SUM(Quantity) AS SumQuantity FROM ICSMOIssue WHERE EATTRIBUTE1 IN ({0}) AND WorkPoint = '{1}' GROUP BY PickID ) b ON a.PickID = b.PickID; IF @@ROWCOUNT = 0 RAISERROR('修改领料单数量失败', 16, 1); ",
keyValue, WorkPoint );
var count = SqlHelper.CmdExecuteNonQueryLi(sqlBuilder.ToString()); #region 废弃注释
// string IChecksql = @"select EATTRIBUTE1
// from ICSMOIssue a
// where a.EATTRIBUTE1 in ({0}) and a.WorkPoint='{1}' and Status = '2'";
// IChecksql = string.Format(IChecksql, keyValue, WorkPoint);
// DataTable IChecksqldt = SqlHelper.GetDataTableBySql(IChecksql);
// //审核完成 修改材料出库表状态
// if (IChecksqldt.Rows.Count > 0)
// {
// string sql1 = @"
//update ICSMOIssue set Status = '1' where EATTRIBUTE1 in (" + keyValue + ") and WorkPoint ='" + WorkPoint + "' and Status = '2'";
// sql1 = string.Format(sql1, keyValue, WorkPoint);
// int Figure1 = SqlHelper.ExecuteNonQuery(sql1);
// if (Figure1 <= 0)
// {
// //returnValue = "修改材料出库表状态失败！" + sql1;
// throw new Exception("修改材料出库表状态失败！" + sql1);
// }
// }
// string IChecksql2 = @"select RCVCode from ICSManufactureReceive a
// where a.RCVCode in ({0}) and a.WorkPoint='{1}' and Status = '2'";
// IChecksql2 = string.Format(IChecksql2, keyValue, WorkPoint);
// DataTable IChecksqldt2 = SqlHelper.GetDataTableBySql(IChecksql2);
// //修改产成品入库表ICSManufactureReceive状态
// if (IChecksqldt2.Rows.Count > 0)
// {
// string sql2 = @"
//update ICSManufactureReceive set Status = '1' where RCVCode in (" + keyValue + ") and WorkPoint ='" + WorkPoint + "' and Status = '2'";
// sql2 = string.Format(sql2, keyValue, WorkPoint);
// int Figure1 = SqlHelper.ExecuteNonQuery(sql2);
// if (Figure1 <= 0)
// {
// //returnValue = "修改产成品入库表状态失败！" + sql2;
// throw new Exception("修改产成品入库表状态失败！" + sql2);
// }
// }
//工单表：ICSMO表RCVQuantity字段 ----减去产红字产成品入库单数量
//工单子件表：ICSMOPICK表IssueQuantity字段 ----- 加上红字材料出库单的数量
//string IMosql = @"select a.Quantity,b.RCVQuantity from ICSManufactureReceive a
// join ICSMO b on a.SourceCode = b.MOCode and a.SourceSequence = b.Sequence
// where a.RCVCode in ({0}) and a.WorkPoint='{1}' ";
//IMosql = string.Format(IMosql, keyValue, WorkPoint);
//DataTable IMosqldt = SqlHelper.GetDataTableBySql(IMosql);
////审核完成 修改工单入库数量 弃审数量加
//if (IMosqldt.Rows.Count > 0)
//{
// string sql1 = @"
// update ICSMO
// set RCVQuantity = RCVQuantity + SumQuantity
// from ICSMO a join (
// select SUM(a.Quantity) as SumQuantity,a.SourceCode,a.SourceSequence from ICSManufactureReceive a
// where a.RCVCode in ({0}) and a.WorkPoint='{1}'
// group by a.SourceCode,a.SourceSequence,a.InvCode
// ) b on b.SourceCode = a.MOCode and b.SourceSequence = a.Sequence";
// sql1 = string.Format(sql1, keyValue, WorkPoint);
// int Figure1 = SqlHelper.ExecuteNonQuery(sql1);
// if (Figure1 <= 0)
// {
// //returnValue = "修改材料出库表状态失败！" + sql1;
// throw new Exception("修改工单数量失败！" + sql1);
// }
//}
// string IMopciksql = @"select a.IssueQuantity,b.Quantity from ICSMOPick a
// join ICSMOIssue b on a.PickID = b.PickID
// where b.EATTRIBUTE1 in ({0}) and b.WorkPoint='{1}'";
// IMopciksql = string.Format(IMopciksql, keyValue, WorkPoint);
// DataTable IMopicksqldt = SqlHelper.GetDataTableBySql(IMopciksql);
// //审核完成 修改领料单数量 弃审 数量减
// if (IMopicksqldt.Rows.Count > 0)
// {
// string sql1 = @"
// update ICSMOPick set IssueQuantity = IssueQuantity + SumQuantity
// from ICSMOPick a
// join ( select b.PickID,b.InvCode,SUM(b.Quantity) as SumQuantity from
//ICSMOIssue b
// where b.EATTRIBUTE1 in ({0}) and b.WorkPoint='{1}'
// group by b.PickID,b.InvCode
// ) b on a.PickID = b.PickID";
// sql1 = string.Format(sql1, keyValue, WorkPoint);
// int Figure1 = SqlHelper.ExecuteNonQuery(sql1);
// if (Figure1 <= 0)
// {
// //returnValue = "修改材料出库表状态失败！" + sql1;
// throw new Exception("修改领料单数量失败！" + sql1);
// }
// }
#endregion
return ""; } catch (Exception ex) { return ex.Message; } } /// <summary>
/// 多个产成品退库单审核 减库存 调用其他系统同步
/// </summary>
/// <param name="keyValue"></param>
/// <returns></returns>
public string ICSMOIssueSendBackAndReduceStocks(string keyValue) { var sql = string.Empty; var sqlBuilder = new StringBuilder(); var batchId = Guid.NewGuid().ToString(); // 批次ID用于日志记录
string MUSER = OperatorProvider.Provider.GetCurrent().UserCode; string MUSERNAME = OperatorProvider.Provider.GetCurrent().UserName; string WorkPoint = OperatorProvider.Provider.GetCurrent().Location; var dateNow = DateTime.Now; try { // 1. 产成品入库和材料出库 根据物料过滤分组 判断产品分组库存数量是否足够扣除
// 2. 关联库存表
// 3. 计算修改后的库存
// 4. 修改库存表 记录 请求erp
// 5. 错误回调
#region 入库表修改库存
//选中单据根据物料分组
string groupManufactureReceivesql = @"select * from ICSManufactureReceive a
inner join (select a.InvCode,b.ExtensionID,a.WorkPoint,a.WarehouseCode,sum(a.Quantity) Quantity from ICSWareHouseLotInfo ainner join ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint group by a.InvCode,b.ExtensionID,a.WorkPoint,a.WarehouseCode) b on a.WorkPoint=b.WorkPoint and a.ExtensionID=b.ExtensionID and a.InvCode=b.InvCode and a.WHCode=b.WarehouseCodewhere a.RCVCode in ({0}) and a.WorkPoint='{1}' and a.Quantity>b.Quantity ";
groupManufactureReceivesql = string.Format(groupManufactureReceivesql, keyValue, WorkPoint); DataTable groupManufactureReceivesqldt = SqlHelper.GetDataTableBySql(groupManufactureReceivesql);
if (groupManufactureReceivesqldt.Rows.Count > 0) { throw new Exception("产成品单据库存不足，请先检查库存"); }
//关联库存表 单个或多个库存 库存数量先进先出扣除
sql = @"
--查询单据信息 SELECT ROW_NUMBER() OVER (ORDER BY a.RCVCode,a.Sequence) AS rowNo, a.RCVCode AS Code, a.Sequence, a.WhCode AS WarehouseCode, ISNULL(a.Quantity, 0) - ISNULL(a.RCVQuantity, 0) AS iQuantity, a.InvCode, b.InvName, b.InvStd, b.InvUnit, a.ExtensionID, ISNULL(c.ProjectCode, '') AS ProjectCode, ISNULL(c.BatchCode, '') AS BatchCode, ISNULL(c.Version, '') AS Version, ISNULL(c.Brand, '') AS Brand, ISNULL(c.cFree1, '') AS cFree1, ISNULL(c.cFree2, '') AS cFree2, ISNULL(c.cFree3, '') AS cFree3, ISNULL(c.cFree4, '') AS cFree4, ISNULL(c.cFree5, '') AS cFree5, ISNULL(c.cFree6, '') AS cFree6, ISNULL(c.cFree7, '') AS cFree7, ISNULL(c.cFree8, '') AS cFree8, ISNULL(c.cFree9, '') AS cFree9, ISNULL(c.cFree10, '') AS cFree10, CAST(NULL AS nvarchar(100)) AS LocationCode, CAST(NULL AS nvarchar(100)) AS LotNO, CAST(NULL AS DECIMAL(18,6)) AS QTY, CAST(NULL AS DECIMAL(18,6)) AS QTYTotal, CAST(NULL AS DECIMAL(18,6)) AS ReserveQuantity, CAST(NULL AS nvarchar(100)) AS MTIME INTO #TempVouchs FROM ICSManufactureReceive a LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint = b.WorkPoint LEFT JOIN ICSExtension c ON a.ExtensionID = c.ID AND a.WorkPoint = c.WorkPoint WHERE a.WorkPoint = '{1}' AND a.RCVCode in ({0}) AND ISNULL(a.Quantity, 0) > ISNULL(a.RCVQuantity, 0)
DECLARE @VouchsTotal INT=@@rowcount, --单据总行数 @VouchsRowCurrent INT=1 --当前单据行
--查询库存信息 SELECT a.WarehouseCode, a.Quantity - a.LockQuantity AS LotQuantity, a.INVCode, b.LotNo, ISNULL(c.BatchCode, '1') AS MTIME, a.inDate, a.LocationCode, b.ExtensionID, b.ProductDate, b.ExpirationDate, ISNULL(c.ProjectCode, '') AS ProjectCode, ISNULL(c.BatchCode, '') AS BatchCode, ISNULL(c.Version, '') AS Version, ISNULL(c.Brand, '') AS Brand, ISNULL(c.cFree1, '') AS cFree1, ISNULL(c.cFree2, '') AS cFree2, ISNULL(c.cFree3, '') AS cFree3, ISNULL(c.cFree4, '') AS cFree4, ISNULL(c.cFree5, '') AS cFree5, ISNULL(c.cFree6, '') AS cFree6, ISNULL(c.cFree7, '') AS cFree7, ISNULL(c.cFree8, '') AS cFree8, ISNULL(c.cFree9, '') AS cFree9, ISNULL(c.cFree10, '') AS cFree10 INTO #TempWH FROM ICSWareHouseLotInfo a INNER JOIN ICSInventoryLot b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint INNER JOIN ICSExtension c ON b.ExtensionID = c.ID AND b.WorkPoint = c.WorkPoint WHERE a.WorkPoint = 'UFDATA_008_2022' AND a.Inspect='0' --在检状态 AND a.Freeze='0' --锁定状态 AND a.Quantity > a.LockQuantity --库存数量大于该条吗占用数量 AND a.InvCode IN (SELECT InvCode FROM #TempVouchs) --物料预留信息 SELECT a.InvCode,a.Quantity AS QTYTotal,b.Quantity AS ReserveQuantity,a.Quantity-ISNULL(b.Quantity, 0) AS CanOutQuantity INTO #TempWHTotal FROM (SELECT InvCode,Sum(LotQuantity) AS Quantity FROM #TempWH GROUP BY InvCode) a LEFT JOIN ICSReserve b ON a.InvCode=b.InvCode AND b.Enable='1' AND b.WorkPoint='UFDATA_008_2022' AND (GETDATE() BETWEEN b.BeginTime AND b.EndTime) -- SELECT * FROM #TempWH -- SELECT * FROM #TempWHTotal --初始化返回结果临时表 SELECT TOP 0 Code,Sequence,InvCode,InvName,InvStd,InvUnit,iQuantity,WarehouseCode,ExtensionID,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,LocationCode,LotNO,QTY,QTYTotal,ReserveQuantity,MTIME INTO #TempResult FROM #TempVouchs --设置行号为自增列 ALTER TABLE #TempResult ADD [rowNo] INT IDENTITY(1,1);
--处理捡料逻辑 WHILE @VouchsRowCurrent<=@VouchsTotal BEGIN SELECT ROW_NUMBER() OVER (ORDER BY a.rowNo,b.MTIME,b.LotNO) AS rowNo,a.Code,a.Sequence,a.InvCode,a.InvName,a.InvStd,a.InvUnit,a.iQuantity ,ISNULL(b.WarehouseCode,'') AS WarehouseCode,ISNULL(b.ExtensionID,'') AS ExtensionID ,ISNULL(b.ProjectCode,'') AS ProjectCode,ISNULL(b.BatchCode,'') AS BatchCode ,ISNULL(b.Version,'') AS Version,ISNULL(b.Brand,'') AS Brand, ISNULL(b.cFree1, '') AS cFree1, ISNULL(b.cFree2, '') AS cFree2, ISNULL(b.cFree3, '') AS cFree3, ISNULL(b.cFree4, '') AS cFree4, ISNULL(b.cFree5, '') AS cFree5, ISNULL(b.cFree6, '') AS cFree6, ISNULL(b.cFree7, '') AS cFree7, ISNULL(b.cFree8, '') AS cFree8, ISNULL(b.cFree9, '') AS cFree9, ISNULL(b.cFree10, '') AS cFree10, b.LocationCode,b.LotNO,b.LotQuantity,c.QTYTotal,c.ReserveQuantity,b.MTIME,a.iQuantity AS NeedQuantity,c.CanOutQuantity INTO #TempWHVouchs FROM #TempVouchs a LEFT JOIN #TempWH b ON a.InvCode=b.INVCode AND ((LEN(a.WarehouseCode)>0 AND a.WarehouseCode=b.WarehouseCode)) AND (LEN(a.ProjectCode)<=0 OR (LEN(a.ProjectCode)>0 AND a.ProjectCode=b.ProjectCode)) AND (LEN(a.BatchCode)<=0 OR (LEN(a.BatchCode)>0 AND a.BatchCode=b.BatchCode)) AND (LEN(a.Version)<=0 OR (LEN(a.Version)>0 AND a.Version=b.Version)) AND (LEN(a.Brand)<=0 OR (LEN(a.Brand)>0 AND a.Brand=b.Brand)) AND (LEN(a.cFree1)<=0 OR (LEN(a.cFree1)>0 AND a.cFree1=b.cFree1)) AND (LEN(a.cFree2)<=0 OR (LEN(a.cFree2)>0 AND a.cFree2=b.cFree2)) AND (LEN(a.cFree3)<=0 OR (LEN(a.cFree3)>0 AND a.cFree3=b.cFree3)) AND (LEN(a.cFree4)<=0 OR (LEN(a.cFree4)>0 AND a.cFree4=b.cFree4)) AND (LEN(a.cFree5)<=0 OR (LEN(a.cFree5)>0 AND a.cFree5=b.cFree5)) AND (LEN(a.cFree6)<=0 OR (LEN(a.cFree6)>0 AND a.cFree6=b.cFree6)) AND (LEN(a.cFree7)<=0 OR (LEN(a.cFree7)>0 AND a.cFree7=b.cFree7)) AND (LEN(a.cFree8)<=0 OR (LEN(a.cFree8)>0 AND a.cFree8=b.cFree8)) AND (LEN(a.cFree9)<=0 OR (LEN(a.cFree9)>0 AND a.cFree9=b.cFree9)) AND (LEN(a.cFree10)<=0 OR (LEN(a.cFree10)>0 AND a.cFree10=b.cFree10)) LEFT JOIN #TempWHTotal c ON a.InvCode=c.INVCode WHERE rowNo=@VouchsRowCurrent
DECLARE @WHTotal INT=@@rowcount, --库存总行数 @WHRowCurrent INT=1 --当前库存行 --如果可出库数量（已排除预留）小于等于单据数量，更新单据数量为可出库数量（已排除预留） IF EXISTS(SELECT rowNo FROM #TempWHTotal a LEFT JOIN #TempVouchs b ON a.InvCode=b.InvCode WHERE rowNo=@VouchsRowCurrent AND a.CanOutQuantity<=ISNULL(b.iQuantity, 0)) BEGIN --更新单据数量为可出库数量（已排除预留） UPDATE #TempWHVouchs SET NeedQuantity=CanOutQuantity --更新物料汇总表可出库数量（已排除预留） UPDATE a SET CanOutQuantity=0 FROM #TempWHTotal a INNER JOIN #TempVouchs b ON a.InvCode=b.InvCode WHERE rowNo=@VouchsRowCurrent --可出库数量（已排除预留）为0时，删除库存临时表的相关物料信息 DELETE FROM #TempWH WHERE InvCode=(SELECT InvCode FROM #TempVouchs WHERE rowNo=@VouchsRowCurrent) END --处理库存预留：如果可出库数量（已排除预留）大于单据数量，更新可出库数量（已排除预留） ELSE BEGIN --更新物料汇总表可出库数量（已排除预留） UPDATE a SET CanOutQuantity=a.CanOutQuantity-b.iQuantity FROM #TempWHTotal a INNER JOIN #TempVouchs b ON a.InvCode=b.InvCode WHERE rowNo=@VouchsRowCurrent END
WHILE @WHRowCurrent<=@WHTotal BEGIN --剩余需领数量小于等于条码数量时，将结果插入结果集,并更新条码数量 IF EXISTS(SELECT rowNo FROM #TempWHVouchs WHERE rowNo=@WHRowCurrent AND NeedQuantity<=LotQuantity) BEGIN INSERT INTO #TempResult SELECT a.Code,a.Sequence,a.InvCode,a.InvName,a.InvStd,a.InvUnit,a.iQuantity,a.WarehouseCode,a.ExtensionID,a.ProjectCode,a.BatchCode,a.Version,a.Brand,a.cFree1,a.cFree2,a.cFree3,a.cFree4,a.cFree5,a.cFree6,a.cFree7,a.cFree8,a.cFree9,a.cFree10, a.LocationCode,a.LotNO,a.NeedQuantity,a.QTYTotal,a.ReserveQuantity,a.MTIME FROM #TempWHVouchs a WHERE rowNo=@WHRowCurrent --剩余应领数量和条码数量相同时，删除条码 IF EXISTS(SELECT rowNo FROM #TempWHVouchs WHERE rowNo=@WHRowCurrent AND NeedQuantity=LotQuantity) BEGIN --删除已使用条码 DELETE FROM #TempWH WHERE LotNO=(SELECT LotNO FROM #TempWHVouchs WHERE rowNo=@WHRowCurrent) END ELSE BEGIN --更新已使用条码剩余数量 UPDATE a SET LotQuantity=b.LotQuantity-b.NeedQuantity FROM #TempWH a INNER JOIN #TempWHVouchs b ON a.LotNO=b.LotNO WHERE rowNo=@WHRowCurrent END --单据行处理完成，跳出库存循环，处理下一个单据行 BREAK END --剩余需领数量大于条码数量时，直接将结果插入结果集 ELSE BEGIN INSERT INTO #TempResult SELECT a.Code,a.Sequence,a.InvCode,a.InvName,a.InvStd,a.InvUnit,a.iQuantity,a.WarehouseCode,a.ExtensionID,a.ProjectCode,a.BatchCode,a.Version,a.Brand,a.cFree1,a.cFree2,a.cFree3,a.cFree4,a.cFree5,a.cFree6,a.cFree7,a.cFree8,a.cFree9,a.cFree10, a.LocationCode,a.LotNO,a.LotQuantity,a.QTYTotal,a.ReserveQuantity,a.MTIME FROM #TempWHVouchs a WHERE rowNo=@WHRowCurrent --更新还需要领的数量、更新可出库数量（已排除预留） UPDATE #TempWHVouchs SET NeedQuantity=NeedQuantity-(SELECT LotQuantity FROM #TempWHVouchs WHERE rowNo=@WHRowCurrent) --删除已使用条码 DELETE FROM #TempWH WHERE LotNO=(SELECT LotNO FROM #TempWHVouchs WHERE rowNo=@WHRowCurrent) END --库存行号加1 SET @WHRowCurrent=@WHRowCurrent+1 END
--单据行号加1 SET @VouchsRowCurrent=@VouchsRowCurrent+1 DROP TABLE #TempWHVouchs END -- SELECT * FROM #TempWH -- SELECT * FROM #TempWHTotal -- SELECT * FROM #TempVouchs SELECT * FROM #TempResult ORDER BY cast(Sequence as int), rowNo --删除临时表 DROP TABLE #TempVouchs DROP TABLE #TempWH DROP TABLE #TempWHTotal DROP TABLE #TempResult ";
sql = string.Format(sql, keyValue, WorkPoint); DataTable MfRetables = SqlHelper.GetDataTableBySql(sql);
if (MfRetables.Rows.Count == 0) { throw new Exception("产成品单据库存不足，请先检查库存"); } foreach (DataRow MfReitem in MfRetables.Rows) { // 本次扣除数量
var Quantity = decimal.Parse(MfReitem["QTY"].ToString()); if (Quantity <= 0) continue; string LotNO = MfReitem["LotNO"].ToString().Replace("'", "''"); string InvCode = MfReitem["InvCode"].ToString().Replace("'", "''"); // 1. 库存更新SQL
sqlBuilder.AppendFormat( @"UPDATE ICSWareHouseLotInfo
SET Quantity = Quantity + {0}, MUSER = '{1}', MUSERName = '{2}', MTIME = '{3}' WHERE LotNO = '{4}'; ",
-Quantity, MUSER.Replace("'", "''"), MUSERNAME.Replace("'", "''"), dateNow.ToString("yyyy-MM-dd HH:mm:ss").Replace("'", "''"), LotNO );
// 2. 库存日志插入SQL
sqlBuilder.AppendFormat( @"INSERT INTO ICSWareHouseLotInfoLog (ID, Identification, TransCode, TransSequence,
FromWarehouseCode, FromLocationCode, LotNo, [Lock], TransType, BusinessCode, Quantity, InvCode, MUSER, MUSERName, MTIME, WorkPoint) VALUES (NEWID(), '{11}', '{0}', '{1}', '{2}', '{3}', '{4}', 0, '17', '75', {5}, '{6}', '{7}', '{8}', '{9}', '{10}'); ",
MfReitem["Code"].ToString().Replace("'", "''"), MfReitem["Sequence"].ToString().Replace("'", "''"), MfReitem["WarehouseCode"].ToString().Replace("'", "''"), MfReitem["LocationCode"].ToString().Replace("'", "''"), LotNO, -Quantity, InvCode, MUSER.Replace("'", "''"), MUSERNAME.Replace("'", "''"), dateNow.ToString("yyyy-MM-dd HH:mm:ss").Replace("'", "''"), WorkPoint.Replace("'", "''"), batchId ); }
#region 废弃逻辑 使用捡料
//foreach (DataRow grMFitem in groupManufactureReceivesqldt.Rows)
//{
// if (grMFitem["status"].ToString() != "1")
// {
// throw new Exception("选中行数据已审核。");
// }
// string invCode = grMFitem["InvCode"].ToString().Replace("'", "''");
// string whCode = grMFitem["WHCode"].ToString().Replace("'", "''");
// string batchCode = grMFitem["BatchCode"].ToString().Replace("'", "''");
// //红字产成品入库表 同一产品需扣除数量
// var InvQty = decimal.Parse(grMFitem["InvQty"].ToString());
// DataRow[] tableRows = MfRetables.Select($"InvCode = '{invCode}' and WarehouseCode = '{whCode}' and BatchCode = '{batchCode}'");
// //DataRow[]求和 LotQtySum当前物料的库存数量
// // 计算库存总量
// var LotQtySum = decimal.Parse(tableRows.Sum(x => x.Field<decimal>("Quantity")).ToString());
// if (LotQtySum < InvQty)
// {
// throw new Exception($"产品编码：{invCode}，仓库编码：{whCode}，批次：{batchCode}，库存数量不足！当前库存：{LotQtySum}，需要扣除：{InvQty}");
// }
// //循环 修改产成品的库存 新增库存日志
// foreach (DataRow MfReitem in tableRows)
// {
// #region 回滚 废弃注释
// //是否回滚
// //if (!string.IsNullOrEmpty(returnValue))
// //{
// // //回滚
// // if (ReList.Any())
// // {
// // foreach (var item in ReList)
// // {
// // ReSql += "update ICSWareHouseLotInfo set Quantity = " + item.Value + " where LotNo = '" + item.Key + "' and WorkPoint ='" + WorkPoint + "' ";
// // }
// // var ReFlag = SqlHelper.ExecuteNonQuery(ReSql);
// // if (ReFlag > 0)
// // {
// // if (string.IsNullOrEmpty(ReCode))
// // {
// // // 将库存记录删除
// // ReSql = "delete from ICSWareHouseLotInfoLog where TransCode in (" + ReCode + ") and WorkPoint ='" + WorkPoint + "'";
// // var ReLogFlag = SqlHelper.ExecuteNonQuery(ReSql);
// // }
// // throw new Exception(returnValue + " 已修改的数据已回滚。");
// // }
// // else
// // {
// // throw new Exception(returnValue + " 数据回滚失败。");
// // }
// // }
// // else
// // {
// // throw new Exception(returnValue);
// // }
// //}
// #endregion
// //扣除数量未清零则继续操作库存
// if (InvQty <= 0) continue;
// var Quantity = decimal.Parse(MfReitem["Quantity"].ToString());
// if (Quantity <= 0) continue;
// // 本次扣除数量
// var LogQty = Quantity <= InvQty ? Quantity : InvQty;
// InvQty = Quantity <= InvQty ? InvQty - Quantity : 0;
// string lotId = MfReitem["ID"].ToString().Replace("'", "''");
// // 1. 库存更新SQL
// sqlBuilder.AppendFormat(
// @"UPDATE ICSWareHouseLotInfo
// SET Quantity = Quantity + {0},
// MUSER = '{1}',
// MUSERName = '{2}',
// MTIME = '{3}'
// WHERE ID = '{4}'; ",
// -LogQty,
// MUSER.Replace("'", "''"),
// MUSERNAME.Replace("'", "''"),
// dateNow.ToString("yyyy-MM-dd HH:mm:ss").Replace("'", "''"),
// lotId
// );
// // 2. 库存日志插入SQL
// sqlBuilder.AppendFormat(
// @"INSERT INTO ICSWareHouseLotInfoLog (ID, Identification, TransCode, TransSequence,
// FromWarehouseCode, FromLocationCode, LotNo, [Lock], TransType, BusinessCode,
// Quantity, InvCode, MUSER, MUSERName, MTIME, WorkPoint)
// VALUES (NEWID(), '{11}', '{0}', '{1}', '{2}', '{3}', '{4}', 0, '17', '75',
// {5}, '{6}', '{7}', '{8}', '{9}', '{10}'); ",
// MfReitem["RCVCode"].ToString().Replace("'", "''"),
// MfReitem["Sequence"].ToString().Replace("'", "''"),
// MfReitem["WarehouseCode"].ToString().Replace("'", "''"),
// MfReitem["LocationCode"].ToString().Replace("'", "''"),
// MfReitem["LotNo"].ToString().Replace("'", "''"),
// LogQty,
// invCode,
// MUSER.Replace("'", "''"),
// MUSERNAME.Replace("'", "''"),
// dateNow.ToString("yyyy-MM-dd HH:mm:ss").Replace("'", "''"),
// WorkPoint.Replace("'", "''"),
// batchId
// );
// #region 废弃逻辑
// // if (InvQty > 0)
// // {
// // var Quantity = decimal.Parse(MfReitem["Quantity"].ToString());
// // if (Quantity > 0)
// // {
// // // 本次扣除数量
// // var LogQty = Quantity <= InvQty ? Quantity : InvQty;
// // // 扣除数量
// // InvQty = Quantity <= InvQty ? InvQty - Quantity : 0;
// // string lotId = MfReitem["ID"].ToString().Replace("'", "''");
// // //库存表修改 根据退库表的数量 按先进先出 扣除库存
// // var LotEntity = MsSqlData.Get<ICSWareHouseLotInfo>(MfReitem["ID"].ToString());
// // if (LotEntity != null)
// // {
// // //回调时使用
// // ReList.Add(LotEntity.LotNo, LotEntity.Quantity);
// // //此处改为扣除数量后的剩余数量
// // LotEntity.Quantity = LotEntity.Quantity - LogQty;
// // LotEntity.MUSER = MUSER;
// // LotEntity.MUSERName = MUSERNAME;
// // LotEntity.MTIME = dateNow.ToString("yyyy-MM-dd HH:mm:ss");
// // var result2 = MsSqlData.Update<ICSWareHouseLotInfo>(LotEntity);
// // if (!result2)
// // {
// // returnValue = "库存表修改失败";
// // }
// // }
// // //库存日志记录 回滚时删除数据
// // ReCode = string.IsNullOrEmpty(ReCode) ? "'" + MfReitem["RCVCode"].ToString() + "'" : ReCode + ",'" + MfReitem["RCVCode"].ToString() + "'";
// // //库存日志记录 产成品退库
// // var entity = new ICSWareHouseLotInfoLog();
// // //ConvertExt.Mapping<ICSWareHouseLotInfoLogEdit, ICSWareHouseLotInfoLog>(model, entity);
// // entity.ID = Guid.NewGuid().ToString();
// // entity.Identification = Guid.NewGuid().ToString();
// // entity.TransCode = MfReitem["RCVCode"].ToString();
// // entity.TransSequence = MfReitem["Sequence"].ToString();
// // entity.FromWarehouseCode = MfReitem["WarehouseCode"].ToString();
// // entity.FromLocationCode = MfReitem["LocationCode"].ToString();
// // entity.LotNo = MfReitem["LotNo"].ToString();
// // entity.Lock = false;
// // entity.TransType = "17";//铭锋产成品退库
// // entity.BusinessCode = "75";//铭锋产成品退库
// // entity.Quantity = LogQty;
// // entity.InvCode = MfReitem["InvCode"].ToString();
// // entity.MUSER = MUSER;
// // entity.MUSERName = MUSERNAME;
// // entity.MTIME = Dates;
// // entity.WorkPoint = WorkPoint;
// // var result = MsSqlData.Insert<ICSWareHouseLotInfoLog>(entity);
// // if (!result)
// // {
// // returnValue = "新增库存日志记录失败";
// // }
// // // 2. 库存日志插入SQL
// // sqlBuilder.AppendFormat(
// // @"INSERT INTO ICSWareHouseLotInfoLog (ID, Identification, TransCode, TransSequence,
// //FromWarehouseCode, FromLocationCode, LotNo, [Lock], TransType, BusinessCode,
// //Quantity, InvCode, MUSER, MUSERName, MTIME, WorkPoint)
// //VALUES (NEWID(), '{11}', '{0}', '{1}', '{2}', '{3}', '{4}', 0, '17', '75',
// //{5}, '{6}', '{7}', '{8}', '{9}', '{10}'); ",
// // MfReitem["RCVCode"].ToString().Replace("'", "''"),
// // MfReitem["Sequence"].ToString().Replace("'", "''"),
// // MfReitem["WarehouseCode"].ToString().Replace("'", "''"),
// // MfReitem["LocationCode"].ToString().Replace("'", "''"),
// // MfReitem["LotNo"].ToString().Replace("'", "''"),
// // LogQty,
// // invCode,
// // MUSER.Replace("'", "''"),
// // MUSERNAME.Replace("'", "''"),
// // dateNow.ToString("yyyy-MM-dd HH:mm:ss").Replace("'", "''"),
// // WorkPoint.Replace("'", "''"),
// // batchId
// // );
// // }
// // }
// // else
// // {
// // continue;
// // }
// #endregion
// }
//}
#endregion
#endregion
#region 材料出库表修改库存
// 红字生产入库单ICSManufactureReceive 会生成对应的红字生成领料单 也就是ICSMOIssue材料出库表
//选中的物料 计算扣除数量
string groupmoIssuesql = @"select a.InvCode,Sum(a.Quantity) as InvQty,a.WHCode,e.BatchCode
from ICSMOIssue a inner JOIN ICSInventory c ON a.InvCode=c.InvCode and a.WorkPoint=c.WorkPoint inner JOIN ICSExtension e on a.ExtensionID = e.ID and a.WorkPoint = e.WorkPoint where a.EATTRIBUTE1 in ({0}) and a.WorkPoint='{1}' group by a.InvCode,a.WHCode,e.BatchCode";
groupmoIssuesql = string.Format(groupmoIssuesql, keyValue, WorkPoint); DataTable groupmoIssuesqldt = SqlHelper.GetDataTableBySql(groupmoIssuesql); //铭锋 因为客户在ERP操作了入库 但在WMS操作退库 没有找到条码就生成一个条码
if (groupmoIssuesqldt.Rows.Count <= 0) { throw new Exception("未找到对应的材料出库单。"); } #region 回滚 废弃
//if (!string.IsNullOrEmpty(returnValue))
//{
// //回滚
// if (ReList.Any())
// {
// foreach (var item in ReList)
// {
// ReSql += "update ICSWareHouseLotInfo set Quantity = " + item.Value + " where LotNo = '" + item.Key + "' and WorkPoint ='" + WorkPoint + "' ";
// }
// var ReFlag = SqlHelper.ExecuteNonQuery(ReSql);
// if (ReFlag > 0)
// {
// if (string.IsNullOrEmpty(ReCode))
// {
// // 将库存记录删除
// ReSql = "delete from ICSWareHouseLotInfoLog where TransCode in (" + ReCode + ") and WorkPoint ='" + WorkPoint + "'";
// var ReLogFlag = SqlHelper.ExecuteNonQuery(ReSql);
// }
// throw new Exception(returnValue + " 已修改的数据已回滚。");
// }
// else
// {
// throw new Exception(returnValue + " 数据回滚失败。");
// }
// }
// else
// {
// throw new Exception(returnValue);
// }
//}
#endregion
string moIssuesql = @"select a.InvCode,a.IssueCode,a.Sequence,a.WHCode,e.BatchCode
from ICSMOIssue a join ICSExtension e on a.ExtensionID = e.ID and ISNULL(e.BatchCode,'') != '' and a.WorkPoint = e.WorkPoint where a.EATTRIBUTE1 in ({0}) and a.WorkPoint='{1}'";
moIssuesql = string.Format(moIssuesql, keyValue, WorkPoint); DataTable moIssue = SqlHelper.GetDataTableBySql(moIssuesql); if (moIssue.Rows.Count <= 0) { throw new Exception("材料出库单未找到关联批次。"); } else { foreach (DataRow item in moIssue.Rows) { sql = @"
select a.IssueCode,a.Sequence,a.InvCode,d.LotNo,g.Quantity,g.InDate,g.ID,g.WarehouseCode,g.LocationCode from ICSMOIssue a inner JOIN ICSInventory c ON a.InvCode=c.InvCode and a.WorkPoint=c.WorkPoint inner JOIN ICSInventoryLot d ON a.ExtensionID=d.ExtensionID and a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint inner JOIN ICSExtension e on a.ExtensionID = e.ID and ISNULL(e.BatchCode,'') != '' and a.WorkPoint = e.WorkPoint inner JOIN (select a.*,b.ExtensionID from ICSWareHouseLotInfo a left join ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint ) g ON g.LotNo=d.LotNo and a.WHCode = g.WarehouseCode and g.WorkPoint=a.WorkPoint and a.ExtensionID=g.ExtensionID where a.IssueCode = '{0}' and a.Sequence = {1} and a.WorkPoint='{2}' order by e.BatchCode asc";
sql = string.Format(sql, item["IssueCode"].ToString(), item["Sequence"].ToString(), WorkPoint); DataTable tablesDemo = SqlHelper.GetDataTableBySql(sql); if (tablesDemo.Rows.Count <= 0) { throw new Exception("物料未找到条码库存，请联系管理员补充资料。物料：" + item["InvCode"].ToString() + "；仓库：" + item["WHCode"].ToString() + "；批次：" + item["BatchCode"].ToString() + "；"); } } } // 获取库存数据
sql = @"select a.IssueCode,a.Sequence,a.InvCode,d.LotNo,g.Quantity,g.InDate,g.ID,g.WarehouseCode,g.LocationCode,e.BatchCode
from ICSMOIssue a inner JOIN ICSInventory c ON a.InvCode=c.InvCode and a.WorkPoint=c.WorkPoint inner JOIN ICSInventoryLot d ON a.ExtensionID=d.ExtensionID and a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint inner JOIN ICSExtension e on a.ExtensionID = e.ID and a.WorkPoint = e.WorkPoint inner JOIN (select a.*,b.ExtensionID from ICSWareHouseLotInfo a left join ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint ) g ON g.LotNo=d.LotNo and a.WHCode = g.WarehouseCode and g.WorkPoint=a.WorkPoint and a.ExtensionID=g.ExtensionID where a.EATTRIBUTE1 in ({0}) and a.WorkPoint='{1}' order by e.BatchCode asc;";
sql = string.Format(sql, keyValue, WorkPoint); DataTable tables = SqlHelper.GetDataTableBySql(sql);
if (tables.Rows.Count <= 0) { throw new Exception("物料未找到条码库存，请联系管理员补充资料。"); } #region 废弃
//关联库存表 单个或多个库存 库存数量不够时先进先出扣除
// sql = @"
// select a.IssueCode,a.Sequence,a.InvCode,d.LotNo,g.Quantity,g.InDate,g.ID,g.WarehouseCode,g.LocationCode,e.BatchCode
//from ICSMOIssue a
//inner JOIN ICSInventory c ON a.InvCode=c.InvCode and a.WorkPoint=c.WorkPoint --and c.InvIQC='1'
//inner JOIN ICSInventoryLot d ON a.ExtensionID=d.ExtensionID and a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint --and d.Type = '13'
//inner JOIN ICSExtension e on a.ExtensionID = e.ID and a.WorkPoint = e.WorkPoint
//inner JOIN (select a.*,b.ExtensionID from ICSWareHouseLotInfo a
//left join ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint ) g ON g.LotNo=d.LotNo and a.WHCode = g.WarehouseCode and g.WorkPoint=a.WorkPoint and a.ExtensionID=g.ExtensionID
// where a.EATTRIBUTE1 in ({0}) and a.WorkPoint='{1}' order by e.BatchCode asc";
// sql = string.Format(sql, keyValue, WorkPoint);
// DataTable tables = SqlHelper.GetDataTableBySql(sql);
// if (tables.Rows.Count <= 0)
// {
// returnValue = "物料未找到条码库存，请联系管理员补充资料。";
// }
#endregion
//材料出库数据 物料循环 物料总数量扣除
foreach (DataRow groupdr in groupmoIssuesqldt.Rows) { //同一产品需增加的数量 退库为负数
var InvQty = decimal.Parse(groupdr["InvQty"].ToString()); string invCode = groupdr["InvCode"].ToString().Replace("'", "''"); string wHCode = groupdr["WHCode"].ToString().Replace("'", "''"); string batchCode = groupdr["BatchCode"].ToString().Replace("'", "''"); // 筛选库存记录
DataRow[] tableRows = tables.Select($"InvCode = '{invCode}' and WarehouseCode = '{wHCode}' and BatchCode = '{batchCode}'");
if (tableRows.Length <= 0) { throw new Exception($"物料编码:{invCode}，未找到条码库存。"); } //先进先出第一个条码增加数量
DataRow MoIssueLot = tableRows[0]; // 条码当前数量
var Quantity = decimal.Parse(MoIssueLot["Quantity"].ToString());
// 先进先出处理
string lotId = MoIssueLot["ID"].ToString().Replace("'", "''"); // 本次修改后的数量
var LogQty = Quantity - InvQty; // 1. 库存更新SQL
sqlBuilder.AppendFormat( @"UPDATE ICSWareHouseLotInfo
SET Quantity = {0}, MUSER = '{1}', MUSERName = '{2}', MTIME = '{3}' WHERE ID = '{4}'; ",
LogQty, MUSER.Replace("'", "''"), MUSERNAME.Replace("'", "''"), dateNow.ToString("yyyy-MM-dd HH:mm:ss").Replace("'", "''"), lotId ); // 2. 库存日志插入SQL
sqlBuilder.AppendFormat( @"INSERT INTO ICSWareHouseLotInfoLog (ID, Identification, TransCode, TransSequence,
FromWarehouseCode, FromLocationCode, LotNo, [Lock], TransType, BusinessCode, Quantity, InvCode, MUSER, MUSERName, MTIME, WorkPoint) VALUES (NEWID(), '{11}', '{0}', '{1}', '{2}', '{3}', '{4}', 0, '17', '75', {5}, '{6}', '{7}', '{8}', '{9}', '{10}'); ",
MoIssueLot["IssueCode"].ToString().Replace("'", "''"), MoIssueLot["Sequence"].ToString().Replace("'", "''"), MoIssueLot["WarehouseCode"].ToString().Replace("'", "''"), MoIssueLot["LocationCode"].ToString().Replace("'", "''"), MoIssueLot["LotNo"].ToString().Replace("'", "''"), -(InvQty), invCode, MUSER.Replace("'", "''"), MUSERNAME.Replace("'", "''"), dateNow.ToString("yyyy-MM-dd HH:mm:ss").Replace("'", "''"), WorkPoint.Replace("'", "''"), batchId ); } #region 库存表修改 废弃
//var LotEntity = MsSqlData.Get<ICSWareHouseLotInfo>(MoIssueLot["ID"].ToString());
//if (LotEntity != null)
//{
// //ReList回滚时使用
// ReList.Add(LotEntity.LotNo, LotEntity.Quantity);
// LotEntity.Quantity = LogQty;
// LotEntity.MUSER = MUSER;
// LotEntity.MUSERName = MUSERNAME;
// LotEntity.MTIME = dateNow.ToString("yyyy-MM-dd HH:mm:ss");
// var result2 = MsSqlData.Update<ICSWareHouseLotInfo>(LotEntity);
// if (!result2)
// {
// returnValue = "库存表修改失败";
// }
//}
//库存日志记录 回滚时删除数据
//ReCode = string.IsNullOrEmpty(ReCode) ? "'" + MoIssueLot["IssueCode"].ToString() + "'" : ReCode + ",'" + MoIssueLot["IssueCode"].ToString() + "'";
////库存日志记录
//var entity = new ICSWareHouseLotInfoLog();
////ConvertExt.Mapping<ICSWareHouseLotInfoLogEdit, ICSWareHouseLotInfoLog>(model, entity);
//entity.ID = Guid.NewGuid().ToString();
//entity.Identification = Guid.NewGuid().ToString();
//entity.TransCode = MoIssueLot["IssueCode"].ToString();
//entity.TransSequence = MoIssueLot["Sequence"].ToString();
//entity.FromWarehouseCode = MoIssueLot["WarehouseCode"].ToString();
//entity.FromLocationCode = MoIssueLot["LocationCode"].ToString();
//entity.LotNo = MoIssueLot["LotNo"].ToString();
//entity.Lock = false;
//entity.TransType = "17";//铭锋产成品退库
//entity.BusinessCode = "75";//铭锋产成品退库
//entity.Quantity = InvQty;
//entity.InvCode = groupdr["InvCode"].ToString();
//entity.MUSER = MUSER;
//entity.MUSERName = MUSERNAME;
//entity.MTIME = Dates;
//entity.WorkPoint = WorkPoint;
//var result = MsSqlData.Insert<ICSWareHouseLotInfoLog>(entity);
//if (!result)
//{
// returnValue = "新增库存日志记录失败";
//}
//}
#endregion
#endregion
#region 修改状态
// 3. 工单入库数量
sqlBuilder.AppendFormat( @"UPDATE ICSMO
SET RCVQuantity = RCVQuantity - b.SumQuantity,MTIME = GETDATE() FROM ICSMO a JOIN ( SELECT SourceCode, SourceSequence, SUM(Quantity) AS SumQuantity FROM ICSManufactureReceive WHERE RCVCode IN ({0}) AND WorkPoint = '{1}' GROUP BY SourceCode, SourceSequence ) b ON b.SourceCode = a.MOCode AND b.SourceSequence = a.Sequence; ",
keyValue, WorkPoint );
// 4. 领料单数量
sqlBuilder.AppendFormat( @"UPDATE ICSMOPick
SET IssueQuantity = IssueQuantity - b.SumQuantity,MTIME = GETDATE() FROM ICSMOPick a JOIN ( SELECT PickID, SUM(Quantity) AS SumQuantity FROM ICSMOIssue WHERE EATTRIBUTE1 IN ({0}) AND WorkPoint = '{1}' GROUP BY PickID ) b ON a.PickID = b.PickID; ",
keyValue, WorkPoint ); // 1. 材料出库表状态
sqlBuilder.AppendFormat( @"UPDATE ICSMOIssue SET Status = '2' ,MTIME = GETDATE()
WHERE EATTRIBUTE1 IN ({0}) AND WorkPoint = '{1}' AND Status = '1'; ",
keyValue, WorkPoint ); // 2. 产成品入库表状态
sqlBuilder.AppendFormat( @"UPDATE ICSManufactureReceive SET Status = '2',EATTRIBUTE1 = '{2}',MTIME = GETDATE()
WHERE RCVCode IN ({0}) AND WorkPoint = '{1}' AND Status = '1'; ",
keyValue, WorkPoint, batchId );
#endregion
// 执行所有数据库操作
//SqlHelper.CmdExecuteNonQueryLi(sqlBuilder.ToString());
// 查找 ICSManufactureReceive 入库单表
string mofactureReceivesql = @"select a.RCVCode,a.InvCode,a.RCVID
from ICSManufactureReceive a where a.RCVCode in ({0}) and a.WorkPoint='{1}' group by a.RCVCode,a.InvCode,a.RCVID";
mofactureReceivesql = string.Format(mofactureReceivesql, keyValue, WorkPoint); DataTable moReceiveqldt = SqlHelper.GetDataTableBySql(mofactureReceivesql);
// 查找 ICSMOIssue 入库单表
string MOIssuesql = @"select a.IssueID from ICSMOIssue a where a.EATTRIBUTE1 in ({0}) and a.WorkPoint='{1}' group by a.IssueID"; MOIssuesql = string.Format(MOIssuesql, keyValue, WorkPoint); DataTable MOIssuesqldt = SqlHelper.GetDataTableBySql(MOIssuesql); using (SqlConnection conn = SqlHelper.GetDataCenterConn()) { conn.Open(); SqlTransaction sqlTran = conn.BeginTransaction(); SqlCommand cmd = new SqlCommand(); cmd.Transaction = sqlTran; cmd.Connection = conn; try { cmd.CommandText = sqlBuilder.ToString(); int RES = cmd.ExecuteNonQuery();
#region 调用ERP接口
var erplist = new List<ApproveInput>();
foreach (DataRow item in moReceiveqldt.Rows) { if (!erplist.Any(q => q.ID == item["RCVID"].ToString() && q.Type == "入库")) { erplist.Add(new ApproveInput { ID = item["RCVID"].ToString(), User = MUSER, WorkPoint = WorkPoint, Type = "入库", UpdateStock = true, MTime = dateNow, UpdateTodoQuantity = true, }); } }
foreach (DataRow item in MOIssuesqldt.Rows) { if (!erplist.Any(q => q.ID == item["IssueID"].ToString() && q.Type == "倒冲")) { erplist.Add(new ApproveInput { ID = item["IssueID"].ToString(), User = MUSER, WorkPoint = WorkPoint, Type = "倒冲", UpdateStock = true, MTime = dateNow, UpdateTodoQuantity = true, }); } } string APIURL = ConfigurationManager.ConnectionStrings["APIURL"].ConnectionString + "FinishedProductReToWHErp/Audit"; var erpinput = JsonConvert.SerializeObject(erplist.Distinct()); string result1 = HttpPost(APIURL, erpinput); JObject Obj = (JObject)JsonConvert.DeserializeObject(result1);//或者JObject jo = JObject.Parse(jsonText);
string MessAge = Obj["Message"].ToString(); string Success = Obj["Success"].ToString(); if (Success.ToUpper() == "FALSE") { throw new Exception("调用ERP请求失败，返回:" + MessAge); } #endregion
cmd.Transaction.Commit();
} catch (Exception ex) { cmd.Transaction.Rollback(); throw new Exception(ex.Message); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); conn.Dispose(); } } } #region 废弃
// string IChecksql = @"select EATTRIBUTE1
// from ICSMOIssue a
// where a.EATTRIBUTE1 in ({0}) and a.WorkPoint='{1}' and Status = '1'";
// IChecksql = string.Format(IChecksql, keyValue, WorkPoint);
// DataTable IChecksqldt = SqlHelper.GetDataTableBySql(IChecksql);
// //审核完成 修改材料出库表状态
// if (IChecksqldt.Rows.Count > 0)
// {
// string sql1 = @"
//update ICSMOIssue set Status = '2' where EATTRIBUTE1 in (" + keyValue + ") and WorkPoint ='" + WorkPoint + "' and Status = '1'";
// sql1 = string.Format(sql1, keyValue, WorkPoint);
// int Figure1 = SqlHelper.ExecuteNonQuery(sql1);
// if (Figure1 <= 0)
// {
// //returnValue = "修改材料出库表状态失败！" + sql1;
// throw new Exception("修改材料出库表状态失败！" + sql1);
// }
// }
// string IChecksql2 = @"select RCVCode from ICSManufactureReceive a
// where a.RCVCode in ({0}) and a.WorkPoint='{1}' and Status = '1'";
// IChecksql2 = string.Format(IChecksql2, keyValue, WorkPoint);
// DataTable IChecksqldt2 = SqlHelper.GetDataTableBySql(IChecksql2);
// //修改产成品入库表ICSManufactureReceive状态
// if (IChecksqldt2.Rows.Count > 0)
// {
// string sql2 = @"
//update ICSManufactureReceive set Status = '2' where RCVCode in (" + keyValue + ") and WorkPoint ='" + WorkPoint + "' and Status = '1'";
// sql2 = string.Format(sql2, keyValue, WorkPoint);
// int Figure1 = SqlHelper.ExecuteNonQuery(sql2);
// if (Figure1 <= 0)
// {
// //returnValue = "修改产成品入库表状态失败！" + sql2;
// throw new Exception("修改产成品入库表状态失败！" + sql2);
// }
// }
// //工单表：ICSMO表RCVQuantity字段 ----减去产红字产成品入库单数量
// //工单子件表：ICSMOPICK表IssueQuantity字段 ----- 加上红字材料出库单的数量
// string IMosql = @"select a.Quantity,b.RCVQuantity from ICSManufactureReceive a
// join ICSMO b on a.SourceCode = b.MOCode and a.SourceSequence = b.Sequence
// where a.RCVCode in ({0}) and a.WorkPoint='{1}' ";
// IMosql = string.Format(IMosql, keyValue, WorkPoint);
// DataTable IMosqldt = SqlHelper.GetDataTableBySql(IMosql);
// //审核完成 修改工单入库数量 审核 数量减
// if (IMosqldt.Rows.Count > 0)
// {
// string sql1 = @"
// update ICSMO
// set RCVQuantity = RCVQuantity - SumQuantity
// from ICSMO a join (
// select SUM(a.Quantity) as SumQuantity,a.SourceCode,a.SourceSequence from ICSManufactureReceive a
// where a.RCVCode in ({0}) and a.WorkPoint='{1}'
// group by a.SourceCode,a.SourceSequence,a.InvCode
// ) b on b.SourceCode = a.MOCode and b.SourceSequence = a.Sequence";
// sql1 = string.Format(sql1, keyValue, WorkPoint);
// int Figure1 = SqlHelper.ExecuteNonQuery(sql1);
// if (Figure1 <= 0)
// {
// //returnValue = "修改材料出库表状态失败！" + sql1;
// throw new Exception("修改工单数量失败！" + sql1);
// }
// }
// string IMopciksql = @"select a.IssueQuantity,b.Quantity from ICSMOPick a
// join ICSMOIssue b on a.PickID = b.PickID
// where b.EATTRIBUTE1 in ({0}) and b.WorkPoint='{1}'";
// IMopciksql = string.Format(IMopciksql, keyValue, WorkPoint);
// DataTable IMopicksqldt = SqlHelper.GetDataTableBySql(IMopciksql);
// //审核完成 修改领料单数量 审核 数量加
// if (IMopicksqldt.Rows.Count > 0)
// {
// string sql1 = @"
// update ICSMOPick set IssueQuantity = IssueQuantity - SumQuantity
// from ICSMOPick a
// join ( select b.PickID,b.InvCode,SUM(b.Quantity) as SumQuantity from
//ICSMOIssue b
// where b.EATTRIBUTE1 in ({0}) and b.WorkPoint='{1}'
// group by b.PickID,b.InvCode
// ) b on a.PickID = b.PickID and a.InvCode = b.InvCode";
// sql1 = string.Format(sql1, keyValue, WorkPoint);
// int Figure1 = SqlHelper.ExecuteNonQuery(sql1);
// if (Figure1 <= 0)
// {
// //returnValue = "修改材料出库表状态失败！" + sql1;
// throw new Exception("修改领料单数量失败！" + sql1);
// }
// }
#endregion
return ""; } catch (Exception ex) { return ex.Message; } } //拣料
public DataTable GetICSMOPickMergeTemp(string ID, string Type) { string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode; string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName; string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location; ID = ID.Replace("'", "''"); ID = string.IsNullOrWhiteSpace(ID) ? "''" : ID.TrimEnd(',');
string Code = SqlHelper.GetItemsDetailEnabledMark("MtimeControl");
string sql = @"EXEC ICSPicking '{0}','{1}','0','{2}'"; sql = string.Format(sql, ID, Type, WorkPoint);
var dataset = Repository().FindDataSetBySql(sql); if (dataset.Tables[0].Rows.Count == 0) return null; DataTable table = dataset.Tables[0]; try { DataRow[] dss = table.Select("LotNO=''"); foreach (var item in dss) { DataRow[] dsss = table.Select("Code='" + item["Code"].ToString() + "' and Sequence='" + item["Sequence"].ToString() + "' and LotNO <> '' "); if (dsss != null && dsss.Length > 0) { table.Rows.Remove(item); } } var result = ConvertCellToString(table); if (Invmes.Rows.Count > 0) { result.Merge(Invmes, false); }
var groupedData = from row in table.AsEnumerable() group row by new { Code = row.Field<string>("Code"), Sequence = row.Field<string>("Sequence") } into g select new { Code = g.Key.Code, Sequence = g.Key.Sequence, TotalQTY = g.Sum(x => { return !x.IsNull("QTY") ? x.Field<decimal>("QTY") : 0; }), TotaliQuantity = g.First().IsNull("iQuantity") ? 0 : g.First().Field<decimal>("iQuantity"),
};
foreach (var item in groupedData) { if (item.TotalQTY != item.TotaliQuantity) { string msg = "单据号:" + item.Code + "行号:" + item.Sequence + "库存不足，请先检查库存"; throw new Exception(msg); } } return result; } catch (Exception ex) { throw new Exception(ex.Message); } } public DataTable ConvertCellToString(DataTable data) { DataTable dtCloned = data.Clone(); foreach (DataColumn col in dtCloned.Columns) { col.DataType = typeof(string); } foreach (DataRow row in data.Rows) { DataRow newrow = dtCloned.NewRow(); foreach (DataColumn column in dtCloned.Columns) { newrow[column.ColumnName] = row[column.ColumnName].ToString();
} dtCloned.Rows.Add(newrow); } return dtCloned; } //接口api解析
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) { throw new Exception(ex.Message); } } } /// <summary>
/// api/Rd1011/Approve 调用产成品入库单、材料出库单审核
/// </summary>
public class ApproveInput { public string ID { get; set; }//ID
public string User { get; set; }//用户
public string Type { get; set; }//类型（入库，倒冲）
public string WorkPoint { get; set; }//站点
public bool UpdateStock { get; set; }//是否更新现存量
public DateTime MTime { get; set; }//时间
public bool UpdateTodoQuantity { get; set; }//更新待出入数量
}}
|
