657 lines
34 KiB
C#
Executable File
657 lines
34 KiB
C#
Executable File
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using Microsoft.Extensions.DependencyInjection;
|
|
using Opsoft.Data;
|
|
using Opsoft.NetCore.O3App.Domain.Interface;
|
|
using Opsoft.NetCore.O3App.Domain.UnitOfWork.WMS.IRepositories;
|
|
using Opsoft.NetCore.O3App.Domain.UnitOfWork.WMS.Model;
|
|
using Opsoft.NetCore.O3App.Shared;
|
|
using Opsoft.NetCore.O3App.Domain;
|
|
using System.Linq;
|
|
using Opsoft.NetCore.O3App.Shared.Utils;
|
|
|
|
namespace Opsoft.NetCore.O3App.Repositories.WMS
|
|
{
|
|
[MapTo(typeof(ISTKStockSchemeRepository), ServiceLifetime.Transient)]
|
|
public class STKStockSchemeRepository : RepositoryBase<BaseEntity>, ISTKStockSchemeRepository
|
|
{
|
|
private IBarcodeRepository _barcodeRepository;
|
|
public STKStockSchemeRepository() : base()
|
|
{
|
|
}
|
|
|
|
public void Init(IBarcodeRepository barcodeRepository)
|
|
{
|
|
_barcodeRepository = barcodeRepository;
|
|
_barcodeRepository.LoginInfo = this.LoginInfo;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 查询盘点方案
|
|
/// </summary>
|
|
/// <param name="data"></param>
|
|
/// <returns></returns>
|
|
public async Task<QuerySourceInvPrgReturnDto> QuerySourceInvPrg(STKStockScheme SData)
|
|
{
|
|
await using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
|
|
|
|
QuerySourceInvPrgReturnDto returnDto = new QuerySourceInvPrgReturnDto();
|
|
#region 自定义变量AND传参
|
|
string retMsg = string.Empty, orgID = string.Empty;
|
|
string billNO = SData.sourceBillNO.ToString();
|
|
if (!SData.orgID.IsNullOrEmpty())
|
|
{
|
|
orgID = $@" AND (T1.FCOMPANYID='{SData.orgID}' OR T1.FCOMPANYID='')";
|
|
}
|
|
else
|
|
{
|
|
orgID = "";
|
|
}
|
|
#endregion
|
|
|
|
string SQL =
|
|
$@"SELECT
|
|
T1.FINTERID
|
|
,T1.FBILLNO
|
|
,T1.FSTATUS
|
|
,T1.FNAME
|
|
,T1.FSTOCKID
|
|
,T1.FSTOCKIDTO
|
|
,T1.FLOSEDATE
|
|
,T2.FNUMBER AS FSTOCKNUMBER
|
|
,T2.FNAME AS FSTOCKNAME
|
|
,T3.FNUMBER AS FSTOCKTONUMBER
|
|
,T3.FNAME AS FSTOCKTONAME
|
|
FROM T_STK_STOCKSCHEME T1
|
|
LEFT JOIN T_BD_STOCK T2 ON T2.FINTERID=T1.FSTOCKID
|
|
LEFT JOIN T_BD_STOCK T3 ON T3.FINTERID=T1.FSTOCKIDTO
|
|
WHERE FBILLNO='{billNO}'{orgID}";
|
|
DataTable cursor = context.Database.SqlQuery(SQL);
|
|
cursor.CaseSensitive = false;
|
|
if (cursor == null || cursor.Rows.Count <= 0)
|
|
{
|
|
throw new O3AppException($@"盘点方案【{billNO}】信息未找到!!", 204);
|
|
}
|
|
|
|
int FStatus = cursor.Rows[0]["FSTATUS"].ToInt();
|
|
if (FStatus == 40)
|
|
{
|
|
throw new O3AppException($@"盘点方案【{billNO}】已审核!!");
|
|
}
|
|
if (FStatus != 20)
|
|
{
|
|
throw new O3AppException($@"盘点失败,当前盘点方案【{billNO}】未提交!!");
|
|
}
|
|
var headInfo = await context.Database.SqlQueryAsync<STKStockSchemeHead>(SQL);
|
|
|
|
returnDto.HeadInfo = headInfo[0];
|
|
var entrySql =
|
|
$@"SELECT
|
|
T1.FMATERIALID
|
|
, T2.FNUMBER AS FMATERIALNUMBER
|
|
, T2.FNAME AS FMATERIALNAME
|
|
, T2.FSPECIFICATION
|
|
, T1.FSTOCKID
|
|
, T3.FNUMBER AS FSTOCKNUMBER
|
|
, T3.FNAME AS FSTOCKNAME
|
|
, T1.FSTOCKLOCID
|
|
, T4.FNAME AS FSTOCKLOCNAME
|
|
, T1.FSTOCKORGID
|
|
, T8.FNAME AS FSTOCKORGNAME
|
|
, T1.FKEEPERTYPEID
|
|
, T1.FKEEPERID
|
|
, T1.FOWNERTYPEID
|
|
, T1.FOWNERID
|
|
, T1.FAUXPROPID
|
|
, T5.FNAME AS FAUXPROPNAME
|
|
, T1.FSTOCKSTATUSID
|
|
, T9.FNAME AS FSTOCKSTATUSNAME
|
|
, T1.FBOMID
|
|
, T1.FMTONO
|
|
, T1.FBASEUNITID
|
|
, T6.FNUMBER AS FBASEUNITNUMBER
|
|
, T6.FNAME AS FBASEUNITNAME
|
|
, T1.FBASEUNITQTY
|
|
, T1.FSTOCKUNITID
|
|
, T7.FNUMBER AS FSTOCKUNITNUMBER
|
|
, T7.FNAME AS FSTOCKUNITNAME
|
|
, T1.FQTY
|
|
, CONVERT(VARCHAR(10),T1.FKFDATE,111) AS FKFDATE
|
|
, T1.FLOT
|
|
, 0 AS FSUMQTY
|
|
FROM dbo.T_STK_STOCKSCHEMEENTRY T1
|
|
LEFT JOIN dbo.T_BD_MATERIAL T2
|
|
ON T2.FINTERID = T1.FMATERIALID
|
|
LEFT JOIN dbo.T_BD_STOCK T3
|
|
ON T3.FINTERID = T1.FSTOCKID
|
|
LEFT JOIN dbo.T_BAS_FLEXVALUESDETAIL T4
|
|
ON T4.FINTERID = T1.FSTOCKLOCID
|
|
LEFT JOIN dbo.T_BD_FLEXSITEMDETAILV T5
|
|
ON T5.FINTERID = T1.FAUXPROPID
|
|
LEFT JOIN dbo.T_BD_UNIT T6
|
|
ON T6.FINTERID = T1.FBASEUNITID
|
|
LEFT JOIN dbo.T_BD_UNIT T7
|
|
ON T7.FINTERID = T1.FSTOCKUNITID
|
|
LEFT JOIN dbo.SYS_ORGSTRUCTURE T8
|
|
ON T8.FINTERID = T1.FSTOCKORGID
|
|
LEFT JOIN dbo.T_BD_STOCKSTATUS T9
|
|
ON T9.FNUMBER = T1.FSTOCKSTATUSID
|
|
WHERE T1.FINTERID='{returnDto.HeadInfo.FINTERID}'";
|
|
returnDto.OnBillEntryInfo = await context.Database.SqlQueryAsync<STKStockSchemeEntry>(entrySql);
|
|
|
|
#region 返回值
|
|
|
|
return returnDto;
|
|
|
|
#endregion
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 盘点方案提交
|
|
/// </summary>
|
|
/// <param name="SData"></param>
|
|
/// <returns></returns>
|
|
public async Task<string> SubmitBill(Barcode_Inv SData)
|
|
{
|
|
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
|
|
try
|
|
{
|
|
#region 自定义变量AND传参
|
|
string retMsg = string.Empty, orgID = SData.orgid.ToStringEx();
|
|
string sourceBillNO = SData.sourceBillNO.ToStringEx();
|
|
string tranType = "STK_STOCKDIFF";//盘点差异
|
|
List<Dictionary<string, object>> submitData = SData.submitData;
|
|
DataTable stkey1 = null;
|
|
bool isbox = false;
|
|
#endregion
|
|
|
|
#region 校检盘点方案
|
|
string interID = Guid.NewGuid().ToString();
|
|
bool isadd = false; // 是否允许新增物料
|
|
DataTable cursor = context.Database.SqlQuery($@"SELECT FINTERID,FSTATUS,FALLOWADDMATERIAL FROM T_STK_STOCKSCHEME
|
|
WHERE FBILLNO='{sourceBillNO}'");
|
|
if (cursor != null && cursor.Rows.Count > 0)
|
|
{
|
|
if (cursor.Rows.Count > 1)
|
|
{
|
|
throw new O3AppException($@"盘点方案【{sourceBillNO}】信息已存在多条记录!");
|
|
}
|
|
if (cursor.Rows[0]["FSTATUS"].ToInt() == 40)
|
|
{
|
|
throw new O3AppException($@"盘点方案【{sourceBillNO}】已审核!");
|
|
}
|
|
if (cursor.Rows[0]["FALLOWADDMATERIAL"].ToStringEx() == "0")
|
|
{
|
|
stkey1 = context.Database.SqlQuery($@"select FMATERIALID from T_STK_STOCKSCHEMEENTRY WHERE FINTERID='{cursor.Rows[0]["FInterID"].ToStringEx()}'");
|
|
}
|
|
else
|
|
{
|
|
isadd = true; //允许新增物料
|
|
}
|
|
interID = cursor.Rows[0]["FInterID"].ToStringEx();
|
|
}
|
|
else
|
|
{
|
|
throw new O3AppException($@"盘点方案【{sourceBillNO}】信息不存在!");
|
|
}
|
|
DataTable table_zt = context.Database.SqlQuery($@"select * from T_STK_STOCKSCHEMEENTRY3 WHERE FINTERID='{interID}'");
|
|
#endregion
|
|
|
|
#region 校验条码状态,若箱码需查询最底阶信息
|
|
List<BarCodeMsg<object>> barcodeEntry2 = new List<BarCodeMsg<object>>();//箱码最底阶信息汇总
|
|
for (int i = 0; i < submitData.Count; i++)
|
|
{
|
|
DataTable dtCusor = context.Database.SqlQuery($@"SELECT
|
|
S.FDELETED
|
|
,S.FBARTYPE
|
|
,S.FINBOX--被装箱
|
|
,S.FISBOX--箱码
|
|
,S.FQTY--数量
|
|
,S.FLOT--批号
|
|
,S.FKFDATE--保质日期
|
|
,S.FUNITID--基本单位
|
|
,S.FAUXPROPID--辅助属性
|
|
,S.FBASEQTY--基本数量
|
|
,S.FBASEUNITID--基本单位
|
|
,S.FSTOCKSTATUS
|
|
,S.FSTOCKSTATUS
|
|
,S1.FINTERID AS FITEMID
|
|
,S1.FNUMBER AS FITEMNUMBER
|
|
,S1.FNAME AS FITEMNAME
|
|
,S1.FDELETED AS FITEMDELETED
|
|
,S1.FISKFPERIOD
|
|
FROM T_BD_BARCODERS S
|
|
LEFT JOIN T_BD_MATERIAL S1 ON S1.FINTERID=S.FMATERIALID
|
|
WHERE S.FBARCODE='{submitData[i]["FBARCODE"].ToStringEx()}'");
|
|
dtCusor.CaseSensitive = false;
|
|
if (dtCusor == null || dtCusor.Rows.Count <= 0)
|
|
{
|
|
throw new O3AppException($@"条码【{submitData[i]["FBARCODE"]}】信息未找到!");
|
|
}
|
|
if (dtCusor.Rows.Count > 1)
|
|
{
|
|
throw new O3AppException($@"条码【{submitData[i]["FBARCODE"]}】信息存在多条记录!");
|
|
}
|
|
DataRow selectDr = dtCusor.Rows[0];
|
|
if (selectDr["FDELETED"].ToBoolean())
|
|
{
|
|
throw new O3AppException($@"条码【{submitData[i]["FBARCODE"]}】已禁用!");
|
|
}
|
|
// 禁用物料是否参与盘点
|
|
if (!cursor.Rows[0]["FALLOWADDMATERIAL"].ToBoolean() && selectDr["FITEMDELETED"].ToBoolean())
|
|
{
|
|
throw new O3AppException($@"条码对应物料【{selectDr["FITEMNUMBER"]}】已禁用!");
|
|
}
|
|
if (selectDr["FBARTYPE"].ToInt() < 3)
|
|
{
|
|
if (selectDr["FINBOX"].ToBoolean())
|
|
{
|
|
throw new O3AppException($@"条码【{submitData[i]["FBARCODE"]}】不符规则,原因:已入箱!");
|
|
}
|
|
retMsg = CheckBill.CheckBarCodeStatus(tranType, selectDr["FSTOCKSTATUS"].ToInt(), submitData[i]["FBARCODE"].ToStringEx());
|
|
if (!string.IsNullOrEmpty(retMsg))
|
|
{
|
|
throw new O3AppException(retMsg);
|
|
|
|
}
|
|
}
|
|
if (!isadd)
|
|
{
|
|
checkmaterialid(selectDr["FITEMID"].ToString(), stkey1);
|
|
}
|
|
checkbarcode(submitData[i]["FBARCODE"].ToStringEx(), table_zt);
|
|
BarCodeMsg<object> newClass = new BarCodeMsg<object>
|
|
{
|
|
FBARCODE = submitData[i]["FBARCODE"].ToStringEx(),//条码
|
|
FSRCFORMID = tranType,//盘点类型
|
|
FSRCBILLNO = sourceBillNO,//盘点方案编码
|
|
FQTY = submitData[i]["FQTY"].ToDecimal(),//实盘数量
|
|
FADJUSTQTY = submitData[i]["FADJUSTQTY"].ToDecimal(),//调整数量
|
|
FSTOCKID = submitData[i]["FSCSTOCKID"].ToStringEx(),//实盘仓库内码
|
|
FSTOCKLOCID = submitData[i]["FSCSTOCKPLACEID"].ToStringEx(),//实盘仓位内码
|
|
FMATERIALID = selectDr["FITEMID"].ToString(),//物料内码
|
|
FLOT = selectDr["FLOT"].ToString(),//批号
|
|
FISBOX = selectDr["FISBOX"].ToInt(),//是否箱码
|
|
FISKFPERIOD= selectDr["FISKFPERIOD"].ToBoolean(),//启用保质期管理
|
|
FKFDATE = selectDr["FKFDATE"].ToStringEx(),//保质日期
|
|
FUNITID = selectDr["FUNITID"].ToString(),//单位
|
|
FAUXPROPID = selectDr["FAUXPROPID"].ToString(),//辅助属性
|
|
FBASEQTY = selectDr["FBASEQTY"].ToDecimal(),//基本数量
|
|
FBASEUNITID = selectDr["FBASEUNITID"].ToString()//基本单位
|
|
};
|
|
|
|
if (!selectDr["FISBOX"].ToBoolean())
|
|
{
|
|
barcodeEntry2.Add(newClass);
|
|
}
|
|
else
|
|
{
|
|
isbox = true;
|
|
DataTable dtCusor2 = context.Database.SqlQuery($@"
|
|
DECLARE @idoc int;
|
|
DECLARE @doc xml;
|
|
SELECT @doc=FBOXINFO FROM T_BD_BARCODERS4 WHERE FBARCODE='{submitData[i]["FBARCODE"].ToStringEx()}'
|
|
|
|
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
|
|
SELECT
|
|
S.FBARCODE--条码
|
|
,S.FKFDATE--保质日期
|
|
,S3.FKFPERIOD--保质天数
|
|
,S3.FKFUNIT--保质期单位
|
|
,S.FAUXPROPID--辅助属性
|
|
,S.FINBOX--被装箱
|
|
,S.FQTY--数量
|
|
,S.FBASEQTY--基本数量
|
|
,S.FUNITID--单位内码
|
|
,S.FBARTYPE--条码类型
|
|
,S.FBARCODESTATUS--条码状态(1=初始;2=收料)
|
|
,S.FSTOCKSTATUS--库存状态(0.未入库 1.已入库 2.生产领料出库 3.销售出库 4.其他出库 5.委外调拨 8.已折分 9.已合并 10.已废弃)
|
|
,S.FKID --打印来源(1=采购订单;2=生产订单;3=委外订单;4=库存;5=物料)
|
|
,S.FLOT--批次
|
|
,S.FMATERIALID--物料内码
|
|
,S3.FNUMBER AS FITEMNUMBER --物料代码
|
|
,S3.FNAME AS FITEMNAME--物料名称
|
|
,S3.FISBATCHMANAGE--启用批次管理
|
|
,S3.FISKFPERIOD--启用保质期管理
|
|
,S3.FSPECIFICATION --规格型号
|
|
,S4.FNAME AS FUNITNAME--单位名称
|
|
,S.FBASEUNITID--辅助单位
|
|
,S5.FNAME AS FBASEUNITNAME--辅助单位名称
|
|
,S6.FINTERID AS FSUPPLYID --供应商
|
|
,S6.FNUMBER AS FSUPPLYNUMBER--供应商代码
|
|
,S6.FNAME AS FSUPPLYNAME --供应商名称
|
|
,S.FSTOCKID--仓库
|
|
,S.FSTOCKLOCID--仓位
|
|
,S7.FNUMBER AS FSCSTOCKNUMBER--仓库代码
|
|
,S7.FISOPENLOCATION --启用仓位管理
|
|
,S8.FNUMBER AS FSCSPNUMBER --仓位代码
|
|
FROM (
|
|
SELECT *
|
|
FROM OPENXML(@idoc, '/BARCODE/DATA',0)
|
|
WITH (
|
|
FBARCODE varchar(50) '@FBARCODE',
|
|
FQTY decimal(24,8) '@FQTY'
|
|
)
|
|
) A
|
|
LEFT JOIN T_BD_BARCODERS S(nolock) on S.FBARCODE =A.FBARCODE
|
|
LEFT JOIN T_BD_MATERIAL S3(nolock) on S.FMATERIALID =S3.FINTERID
|
|
LEFT JOIN T_BD_UNIT S4 on S.FUNITID = S4.FINTERID
|
|
LEFT JOIN T_BD_UNIT S5 on S.FBASEUNITID = S5.FINTERID
|
|
LEFT JOIN T_BD_SUPPLIER S6 on S.FSUPPLYID=S6.FINTERID
|
|
LEFT JOIN T_BD_STOCK S7 on S.FSTOCKID = S7.FINTERID
|
|
LEFT JOIN T_BAS_FLEXVALUESDETAIL S8 on S.FSTOCKLOCID = S8.FINTERID;
|
|
|
|
EXEC sp_xml_removedocument @idoc --释放指针
|
|
");
|
|
dtCusor2.CaseSensitive = false;
|
|
if (dtCusor2 == null || dtCusor2.Rows.Count <= 0)
|
|
{
|
|
throw new O3AppException($@"箱码【{submitData[i]["FBARCODE"].ToStringEx()}】信息未找到!");
|
|
}
|
|
foreach (DataRow selectDr2 in dtCusor2.Rows)
|
|
{
|
|
if (!isadd)
|
|
{
|
|
checkmaterialid(selectDr2["FITEMID"].ToString(), stkey1);
|
|
}
|
|
barcodeEntry2.Add(new BarCodeMsg<object>
|
|
{
|
|
FBOXBARCODE = submitData[i]["FBARCODE"].ToStringEx(),//箱码
|
|
FBARCODE = selectDr2["FBARCODE"].ToString(),//条码
|
|
FSRCFORMID = tranType,//盘点类型
|
|
FSRCBILLNO = sourceBillNO,//盘点方案编码
|
|
FQTY = selectDr2["FQTY"].ToDecimal(),//实盘数量
|
|
FSTOCKID = submitData[i]["FSCSTOCKID"].ToStringEx(),//实盘仓库内码
|
|
FSTOCKLOCID = submitData[i]["FSCSTOCKPLACEID"].ToStringEx(),//实盘仓位内码
|
|
FMATERIALID = selectDr2["FMATERIALID"].ToString(),//物料内码
|
|
FLOT = selectDr2["FLOT"].ToString(),//批号
|
|
FISBOX = 0,//是否箱码
|
|
FKFDATE = selectDr2["FKFDATE"].ToStringEx(),//保质日期
|
|
FUNITID = selectDr2["FUNITID"].ToString(),//单位
|
|
FAUXPROPID = selectDr2["FAUXPROPID"].ToString(),//辅助属性
|
|
FBASEQTY = selectDr2["FBASEQTY"].ToDecimal(),//辅助数量
|
|
FBASEUNITID = selectDr2["FBASEUNITID"].ToString(),//辅助单位
|
|
FADJUSTQTY = submitData[i]["FADJUSTQTY"].ToDecimal()//调整数量
|
|
});
|
|
}
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region 库中条码整合
|
|
//获取库里已经存在的数据合计
|
|
foreach (DataRow item in table_zt.Rows)
|
|
{
|
|
barcodeEntry2.Add(new BarCodeMsg<object>
|
|
{
|
|
FBOXBARCODE = item["FBOXBARCODE"].ToStringEx(),//箱码
|
|
FBARCODE = item["FBARCODE"].ToString(),//条码
|
|
FISBOX = item["FISBOX"].ToInt(),//是否箱码
|
|
FMATERIALID = item["FMATERIALID"].ToString(),//物料内码
|
|
FSTOCKID = item["FSTOCKID"].ToStringEx(),//实盘仓库内码
|
|
FSTOCKLOCID = item["FSTOCKLOCID"].ToStringEx(),//实盘仓位内码
|
|
FAUXPROPID = item["FAUXPROPID"].ToString(),//辅助属性
|
|
FLOT = item["FLOT"].ToString(),//批号
|
|
FKFDATE = item["FKFDATE"].ToStringEx(),//保质日期
|
|
FUNITID = item["FUNITID"].ToString(),//单位
|
|
FQTY = item["FQTY"].ToDecimal(),//实盘数量
|
|
FBASEUNITID = item["FBASEUNITID"].ToString(),//辅助单位
|
|
FBASEQTY = item["FBASEUNITQTY"].ToDecimal(),//辅助数量
|
|
FADJUSTQTY = item["FADJUSTQTY"].ToDecimal()//调整数量
|
|
});
|
|
}
|
|
#endregion
|
|
|
|
#region 按条码基本信息分组
|
|
barcodeEntry2.ForEach(o => { o.FADJUSTBASEQTY = GetMaterQty2(context, o.FMATERIALID, o.FUNITID, o.FBASEUNITID, o.FADJUSTQTY, 6); });
|
|
|
|
//按物料基本信息汇总数量
|
|
var myInBillList = barcodeEntry2.GroupBy(o => new
|
|
{
|
|
o.FMATERIALID,//物料内码
|
|
o.FLOT,//批号
|
|
o.FAUXPROPID,//辅助属性
|
|
o.FSTOCKID,//实盘仓库内码
|
|
o.FSTOCKLOCID,//实盘仓位内码
|
|
o.FKFDATE,//保质日期
|
|
o.FBASEUNITID
|
|
}).Select(p => new BarCodeMsg<object>
|
|
{
|
|
FMATERIALID = p.Key.FMATERIALID,//物料内码
|
|
FSTOCKID = p.Key.FSTOCKID,//实盘仓库内码
|
|
FSTOCKLOCID = p.Key.FSTOCKLOCID,//实盘仓位内码
|
|
FLOT = p.Key.FLOT,//批号
|
|
FKFDATE = p.Key.FKFDATE,//保质日期
|
|
FAUXPROPID = p.Key.FAUXPROPID,//辅助属性
|
|
FUNITID = string.Empty,
|
|
FBASEUNITID = p.Key.FBASEUNITID,//辅助单位
|
|
FQTY = 0, //库存数量
|
|
FADJUSTQTY = 0, //调整数量
|
|
FADJUSTBASEQTY = p.Sum(z => z.FADJUSTBASEQTY),//调整基本数量
|
|
FBASEQTY = p.Sum(z => z.FBASEQTY)
|
|
}).ToList();
|
|
#endregion
|
|
|
|
#region 创建中间表信息
|
|
StringBuilder exeSql = new StringBuilder();
|
|
//删除两个表体的数据
|
|
exeSql.AppendLine($"DELETE FROM T_STK_STOCKSCHEMEENTRY3 WHERE FINTERID='{interID}';");
|
|
exeSql.AppendLine($"DELETE FROM T_STK_STOCKSCHEMEENTRY1 WHERE FINTERID='{interID}';");
|
|
int entryID = 1;
|
|
barcodeEntry2.ForEach(o => { exeSql.AppendLine(ODK_InventoryPrgEntry3(interID, entryID++, o)); });
|
|
entryID = 1;
|
|
myInBillList.ForEach(o =>
|
|
{
|
|
var stockUnitTable = context.Database.SqlQuery(@$" SELECT
|
|
FNAME
|
|
,FSTOREUNITID
|
|
,FISBATCHMANAGE
|
|
,FISKFPERIOD
|
|
,FISSECUNIT
|
|
FROM T_BD_MATERIAL
|
|
WHERE FINTERID='{o.FMATERIALID}'");
|
|
if (stockUnitTable == null || stockUnitTable.Rows.Count <= 0)
|
|
{
|
|
throw new Exception($"物料【{o.FMATERIALID}】信息未找到");
|
|
}
|
|
string materialName = stockUnitTable.Rows[0]["FNAME"].ToStringEx();
|
|
string stockUnitId = stockUnitTable.Rows[0]["FSTOREUNITID"].ToStringEx();
|
|
if (stockUnitId.IsNullOrEmpty())
|
|
{
|
|
throw new Exception($"物料【{materialName}】库存单位未设置");
|
|
}
|
|
//库存单位数量
|
|
o.FUNITID = stockUnitId;
|
|
o.FQTY = GetMaterQty2(context, o.FMATERIALID, o.FBASEUNITID, stockUnitId, o.FBASEQTY, 6);
|
|
//调整数量
|
|
o.FADJUSTQTY = GetMaterQty2(context, o.FMATERIALID, o.FBASEUNITID, stockUnitId, o.FADJUSTBASEQTY, 6);
|
|
exeSql.AppendLine(ODK_InventoryPrgEntry(interID, entryID++, o));
|
|
});
|
|
#endregion
|
|
|
|
#region 执行事务操作
|
|
try
|
|
{
|
|
//开启事务
|
|
context.Database.BeginTransaction();
|
|
//创建中间表
|
|
await context.Database.SqlNonQueryAsync(exeSql.ToString());
|
|
//提交事务
|
|
context.Database.CommitTransaction();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
context.Database.RollbackTransaction();
|
|
throw new Exception("执行事务操作异常," + ex.Message, ex);
|
|
}
|
|
#endregion
|
|
|
|
return await Task.Run(() => "盘点成功" + retMsg);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception("盘点异常," + ex.Message, ex);
|
|
}
|
|
}
|
|
|
|
|
|
//盘点方案
|
|
public static string ODK_InventoryPrgEntry(string interid, int entryID, BarCodeMsg<object> data)
|
|
{
|
|
string strSQL;
|
|
strSQL = "INSERT INTO T_STK_STOCKSCHEMEENTRY1(\r\n" +
|
|
" FINTERID\r\n" +
|
|
" ,FENTRYID\r\n" +
|
|
" ,FDETAILID\r\n" +
|
|
" ,FMATERIALID\r\n" +
|
|
" ,FLOT\r\n" +
|
|
" ,FSTOCKUNITID\r\n" +
|
|
" ,FBASEUNITID\r\n" +
|
|
" ,FQTY\r\n" +
|
|
" ,FADJUSTQTY\r\n" +
|
|
" ,FBASEUNITQTY\r\n" +
|
|
" ,FSTOCKID\r\n" +
|
|
" ,FSTOCKLOCID\r\n" +
|
|
" ,FAUXPROPID\r\n" +
|
|
(CheckDateTime.IsDate(data.FKFDATE.ToStringEx()) == true ? " ,FKFDATE\r\n" : "") +
|
|
" ,UID\r\n" +
|
|
" )\r\n" +
|
|
"VALUES(\r\n" +
|
|
" '" + interid + "',\r\n" +
|
|
" " + entryID + ",\r\n" +
|
|
" '" + Guid.NewGuid().ToString() + "',\r\n" +
|
|
" '" + data.FMATERIALID + "',\r\n" +
|
|
" '" + data.FLOT + "',\r\n" +
|
|
" '" + data.FUNITID + "',\r\n" +
|
|
" '" + data.FBASEUNITID + "',\r\n" +
|
|
" " + data.FQTY + ",\r\n" +
|
|
" " + data.FADJUSTQTY + ",\r\n" +
|
|
" " + data.FBASEQTY + ",\r\n" +
|
|
" '" + data.FSTOCKID + "',\r\n" +
|
|
" '" + data.FSTOCKLOCID + "',\r\n" +
|
|
" '" + data.FAUXPROPID + "',\r\n" +
|
|
(CheckDateTime.IsDate(data.FKFDATE.ToStringEx()) == true ? "'" + data.FKFDATE + "' ,\r\n" : "") +
|
|
" '" + Guid.NewGuid().ToStringEx() + "'\r\n" +
|
|
" );\r\n";
|
|
return strSQL;
|
|
}
|
|
//条码
|
|
|
|
public static string ODK_InventoryPrgEntry3(string interid, int entryID, BarCodeMsg<object> data)
|
|
{
|
|
string strSQL;
|
|
strSQL = "INSERT INTO T_STK_STOCKSCHEMEENTRY3(\r\n" +
|
|
" FINTERID\r\n" +
|
|
" ,FENTRYID\r\n" +
|
|
" ,FDETAILID\r\n" +
|
|
" ,FBOXBARCODE\r\n" +
|
|
" ,FBARCODE\r\n" +
|
|
" ,FISBOX\r\n" +
|
|
" ,FMATERIALID\r\n" +
|
|
" ,FLOT\r\n" +
|
|
" ,FUNITID\r\n" +
|
|
" ,FBASEUNITID\r\n" +
|
|
" ,FQTY\r\n" +
|
|
" ,FADJUSTQTY\r\n" +
|
|
" ,FBASEUNITQTY\r\n" +
|
|
" ,FSTOCKID\r\n" +
|
|
" ,FSTOCKLOCID\r\n" +
|
|
" ,FAUXPROPID\r\n" +
|
|
(CheckDateTime.IsDate(data.FKFDATE.ToStringEx()) == true ? " ,FKFDATE\r\n" : "") +
|
|
" ,UID\r\n" +
|
|
" )\r\n" +
|
|
"VALUES(\r\n" +
|
|
" '" + interid + "',\r\n" +
|
|
" '" + entryID + "',\r\n" +
|
|
" '" + Guid.NewGuid().ToString() + "',\r\n" +
|
|
" '" + data.FBOXBARCODE + "',\r\n" +
|
|
" '" + data.FBARCODE + "',\r\n" +
|
|
" '" + data.FISBOX + "',\r\n" +
|
|
" '" + data.FMATERIALID + "',\r\n" +
|
|
" '" + data.FLOT + "',\r\n" +
|
|
" '" + data.FUNITID + "',\r\n" +
|
|
" '" + data.FBASEUNITID + "',\r\n" +
|
|
" " + data.FQTY + ",\r\n" +
|
|
" " + data.FADJUSTQTY + ",\r\n" +
|
|
" " + data.FBASEQTY + ",\r\n" +
|
|
" '" + data.FSTOCKID + "',\r\n" +
|
|
" '" + data.FSTOCKLOCID + "',\r\n" +
|
|
" '" + data.FAUXPROPID + "',\r\n" +
|
|
(CheckDateTime.IsDate(data.FKFDATE.ToStringEx()) == true ? "'" + data.FKFDATE + "' ,\r\n" : "") +
|
|
" '" + Guid.NewGuid().ToStringEx() + "'\r\n" +
|
|
" );\r\n";
|
|
return strSQL;
|
|
}
|
|
|
|
//箱码
|
|
public static string ODK_InventoryPrgEntry4(string interid, BarCodeMsg<object> data)
|
|
{
|
|
string strSQL;
|
|
strSQL = "INSERT INTO T_STK_STOCKSCHEMEENTRY4(\r\n" +
|
|
" FINTERID\r\n" +
|
|
" ,FDETAILID\r\n" +
|
|
" ,FBARCODE\r\n" +
|
|
" ,FBOXBARCODE\r\n" +
|
|
" ,FMATERIALID\r\n" +
|
|
" ,FLOT\r\n" +
|
|
" ,FBASEUNITID\r\n" +
|
|
" ,FQTY\r\n" +
|
|
" ,FBASEUNITQTY\r\n" +
|
|
" ,FSTOCKID\r\n" +
|
|
" ,FSTOCKLOCID\r\n" +
|
|
" ,FAUXPROPID\r\n" +
|
|
(CheckDateTime.IsDate(data.FKFDATE.ToStringEx()) == true ? " ,FKFDATE\r\n" : "") +
|
|
" ,UID\r\n" +
|
|
" )\r\n" +
|
|
"VALUES(\r\n" +
|
|
" '" + interid + "',\r\n" +
|
|
" '" + Guid.NewGuid().ToString() + "',\r\n" +
|
|
" '" + data.FBARCODE + "',\r\n" +
|
|
" '" + data.FBOXBARCODE + "',\r\n" +
|
|
" '" + data.FMATERIALID + "',\r\n" +
|
|
" '" + data.FLOT + "',\r\n" +
|
|
" '" + data.FBASEUNITID + "',\r\n" +
|
|
" " + data.FQTY + ",\r\n" +
|
|
" " + data.FBASEQTY + ",\r\n" +
|
|
" '" + data.FSTOCKID + "',\r\n" +
|
|
" '" + data.FSTOCKLOCID + "',\r\n" +
|
|
" '" + data.FAUXPROPID + "',\r\n" +
|
|
(CheckDateTime.IsDate(data.FKFDATE.ToStringEx()) == true ? "'" + data.FKFDATE + "' ,\r\n" : "") +
|
|
" '" + Guid.NewGuid().ToStringEx() + "'\r\n" +
|
|
" );\r\n";
|
|
return strSQL;
|
|
}
|
|
public void checkmaterialid(string MATERIALID, DataTable table)
|
|
{
|
|
|
|
foreach (DataRow selectDr2 in table.Rows)
|
|
{
|
|
if (MATERIALID != selectDr2["FMATERIALID"].ToStringEx())
|
|
{
|
|
throw new Exception($"{MATERIALID}该物料不在待盘点数据中,请确认!");
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 检查条码是否已扫描存在盘点数据
|
|
/// </summary>
|
|
/// <param name="FBARCODE"></param>
|
|
/// <param name="table"></param>
|
|
public void checkbarcode(string FBARCODE, DataTable table)
|
|
{
|
|
|
|
foreach (DataRow selectDr2 in table.Rows)
|
|
{
|
|
if (FBARCODE == selectDr2["FBARCODE"].ToStringEx())
|
|
{
|
|
throw new Exception($"{FBARCODE}已经在盘点数据中,请确认!");
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|