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.
1695 lines
95 KiB
1695 lines
95 KiB
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.EATTRIBUTE10
|
|
from 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 a
|
|
inner 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.WarehouseCode
|
|
where 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; }//更新待出入数量
|
|
}
|
|
}
|