Files

563 lines
29 KiB
C#
Executable File

using Microsoft.Extensions.DependencyInjection;
using Opsoft.Data;
using Opsoft.NetCore.O3App.Domain;
using Opsoft.NetCore.O3App.Domain.Interface;
using Opsoft.NetCore.O3App.Domain.UnitOfWork.WIPBarCode.IRepositories;
using Opsoft.NetCore.O3App.Domain.UnitOfWork.WIPBarCode.Model;
using Opsoft.NetCore.O3App.Shared;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using System.Linq;
using Newtonsoft.Json.Linq;
using System.Collections;
using Opsoft.NetCore.O3App.Repositories.IPadReport;
using Opsoft.NetCore.O3App.Domain.UnitOfWork.IPadReport.Model;
namespace Opsoft.NetCore.O3App.Repositories.WIPBarCode
{
[MapTo(typeof(IWIPInventoryRepository), ServiceLifetime.Transient)]
public class WIPInventoryRepository : RepositoryBase<BaseEntity>, IWIPInventoryRepository
{
public WIPInventoryRepository() : base()
{
}
/// <summary>
/// 查询盘点方案
/// </summary>
/// <param name="SData"></param>
/// <returns></returns>
public async Task<GetWIPSchemeTo> GetWIPScheme(GetWIPSchemeFrom SData)
{
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
GetWIPSchemeTo data = await IsWIPData(SData, context);
string sql = string.Empty;
#region 盘点明细
sql = $@"SELECT * FROM ({GetSqlCheck()}) S
WHERE S.FINTERID='{data.FINTERID}'
ORDER BY S.FBARCODE";
data.WIPINVENTORYENTRY = await context.Database.SqlQueryAsync<T_SFC_WIPINVENTORYENTRY>(sql);
#endregion
return data;
}
/// <summary>
/// 查询条码
/// </summary>
/// <param name="sData"></param>
/// <returns></returns>
public async Task<RetMessage> GetWIPBarcode(JObject SData)
{
RetMessage jsonRet = new RetMessage
{
Status = 0,
Message = string.Empty,
Data = new Dictionary<string, object>()
};
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
//验证盘点方案信息
GetWIPSchemeFrom scheme = new GetWIPSchemeFrom();
scheme.sourceBillNO = SData["sourceBillNO"].ToStringEx();
scheme.orgID = SData["orgID"].ToStringEx();
GetWIPSchemeTo schemeData = await IsWIPData(scheme, context);
string sql = $@"SELECT * FROM ({GetSqlBarcode()}) S
WHERE S.FBARCODE ='{SData["barCodeNum"].ToStringEx()}' AND S.FSTOCKID='{schemeData.FSTOCKID}'";
List<WIPCodeInfo> barList = await context.Database.SqlQueryAsync<WIPCodeInfo>(sql);
if (barList == null || barList.Count == 0)
throw new Exception($@"条码{SData["barCodeNum"].ToStringEx()}不正确,请扫描{schemeData.FSTOCKNUMBER}仓库下,在库且合格的条码");
WIPCodeInfo wipCodeInfo = barList[0];
#region 默认带出仓库仓位
string strLocation = string.Empty;
if (!string.IsNullOrEmpty(wipCodeInfo.FSTOCKNUMBER))
{
Hashtable sysParams = context.PBValue("STOCKCHAR,C;FLEXCHAR,C;");
string stcokChar = sysParams["STOCKCHAR"].ToStringEx();
string valueChar = stcokChar ?? "-";
string flexChar = sysParams["FLEXCHAR"].ToStringEx();
flexChar ??= ".";
strLocation = (!wipCodeInfo.FISOPENLOCATION || string.IsNullOrEmpty(wipCodeInfo.FSTOCKLOCNUMBER)) ?
wipCodeInfo.FSTOCKNUMBER :
wipCodeInfo.FSTOCKNUMBER + valueChar + wipCodeInfo.FSTOCKLOCNUMBER.Replace(".", flexChar);
}
wipCodeInfo.FLOCATION = strLocation;
#endregion
#region 返回条码信息
jsonRet.Status = 1;
jsonRet.Data.Add("data", wipCodeInfo);
return jsonRet;
#endregion
}
/// <summary>
/// 更新条码信息
/// </summary>
/// <param name="sData"></param>
/// <returns></returns>
public async Task<object> UpdateWIPBarcode(GetWIPSchemeFrom SData)
{
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
GetWIPSchemeTo data = await IsWIPData(SData, context);
string sql = string.Empty;
sql = $@"SELECT * FROM ({GetSqlCheck()}) S
WHERE S.FINTERID='{data.FINTERID}'";
DataTable dtSheme = context.Database.SqlQuery(sql);
if (dtSheme == null)
throw new O3AppException($@"查询错误,盘点方案【{SData.sourceBillNO}】未找到!", 404);
var shemeBarcode = dtSheme.AsEnumerable().Select(o => o.Field<string>("FBARCODE")).Distinct().ToList();
var updateBar = shemeBarcode.Intersect(SData.barcodeList.Select(o => o.FBARCODE));
//需要插入的条码
var insertBar = SData.barcodeList.Select(o => o.FBARCODE).Except(updateBar);
//异常条码
var exceptBar = SData.barcodeList.Select(o => o.FBARCODE).ToList().Except(updateBar).Except(insertBar).ToList();
if (exceptBar.Count > 0)
throw new Exception($@"其中条码{string.Join("','", exceptBar)}不正确");
#region 更新
//执行sql集合
StringBuilder exeSql = new StringBuilder();
//更新已有条码数量
updateBar.ForEach(o => { exeSql.AppendLine($@"UPDATE T_SFC_WIPINVENTORYENTRY
SET FPRACTICALQTY={SData.barcodeList.FirstOrDefault(p=>p.FBARCODE == o)?.FADJUSTQTY},
FPRACTICALSTOCKID='{SData.barcodeList.FirstOrDefault(p => p.FBARCODE == o)?.FSTOCKID}',
FPRACTICALSPID='{SData.barcodeList.FirstOrDefault(p => p.FBARCODE == o)?.FSTOCKLOCID}'
WHERE FINTERID='{data.FINTERID}' AND FBARCODE='{o}';"); });
//不存在的条码插入
sql = $@"SELECT * FROM ({GetSqlBarcode()}) S
WHERE S.FSTOCKID='{data.FSTOCKID}'";
DataTable dtWIP = context.Database.SqlQuery(sql);
if (dtWIP == null || dtWIP.Rows.Count == 0)
throw new O3AppException($@"查询错误,仓库【{data.FSTOCKNUMBER}】下没有条码!", 404);
List<WipStockBillEntry> badWipStockBillEntryList = new List<WipStockBillEntry>();
int entryId = dtSheme.Rows.Count;
//盘点表体信息
//需要插入的Value值
if (insertBar.ToList().Count > 0)
{
var sqlValues = string.Join(",", insertBar.Select(o => dtWIP.AsEnumerable().Cast<DataRow>().FirstOrDefault(p => p["FBARCODE"].ToStringEx() == o)).Select(item => @$"('{Guid.NewGuid().ToStringEx()}',
'{data.FINTERID}', '{Guid.NewGuid().ToStringEx()}', {++entryId},'{item["FBARCODE"].ToStringEx()}', '{item["FMATERIALID"].ToStringEx()}','{item["FUNITID"].ToStringEx()}','{item["FUNITID"].ToStringEx()}',
{SData.barcodeList.FirstOrDefault(q => q.FBARCODE == item["FBARCODE"].ToStringEx())?.FADJUSTQTY},{SData.barcodeList.FirstOrDefault(q => q.FBARCODE == item["FBARCODE"].ToStringEx())?.FADJUSTQTY},
'{item["FLOT"].ToStringEx()}', '{item["FSTOCKID"].ToStringEx()}', '{item["FSTOCKLOCID"].ToStringEx()}', '{SData.barcodeList.FirstOrDefault(q => q.FBARCODE == item["FBARCODE"].ToStringEx())?.FSTOCKID}',
'{SData.barcodeList.FirstOrDefault(q => q.FBARCODE == item["FBARCODE"].ToStringEx())?.FSTOCKLOCID}','{item["FFLOWCARDID"].ToStringEx()}','{item["FFLOWCARDDETAILID"].ToStringEx()}',
'{item["FCURPROCESSID"].ToStringEx()}','{item["FMOID"].ToStringEx()}','{item["FICMONO"].ToStringEx()}','{item["FMODETAILID"].ToStringEx()}',
'{item["FRESOURCEID"].ToStringEx()}','{DateTime.Now}','{LoginInfo.UserID}')"));
string strSQL = " INSERT INTO T_SFC_WIPINVENTORYENTRY(\r\n" +
" UID\r\n" +
" ,FINTERID\r\n" +
" ,FDETAILID\r\n" +
" ,FENTRYID\r\n" +
" ,FBARCODE\r\n" +
" ,FMATERIALID\r\n" +
" ,FUNITID\r\n" +
" ,FSECUNITID\r\n" +
" ,FPRACTICALQTY\r\n" +
" ,FPRACTICALSECQTY\r\n" +
" ,FBATCHNO\r\n" +
" ,FSTOCKID\r\n" +
" ,FSTOCKLOCID\r\n" +
" ,FPRACTICALSTOCKID\r\n" +
" ,FPRACTICALSPID\r\n" +
" ,FFLOWCARDID\r\n" +
" ,FFLOWCARDDETAILID\r\n" +
" ,FPROCESSID\r\n" +
" ,FICMOID\r\n" +
" ,FICMOBILLNO\r\n" +
" ,FICMODETAILID\r\n" +
" ,FRESOURCESID\r\n" +
" ,C_YMD\r\n" +
" ,C_USER\r\n" +
" )\r\n" +
" VALUES " + sqlValues;
exeSql.AppendLine(strSQL);
}
#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
#endregion
return await Task.Run(() => "提交成功");
}
/// <summary>
/// 盘点明细
/// </summary>
/// <param name="finterid"></param>
/// <returns></returns>
private string GetSqlCheck()
{
return $@"
select T1.* ,
T2.FNUMBER as FMATERIALNUMBER,
T2.FNAME as FMATERIALNAME,
T2.FSPECIFICATION,
T4.FNAME as FSTOCKNAME,
T4.FNUMBER as FSTOCKNUMBER,
T6.FNAME as FUNITNAME,
T6.FNUMBER as FUNITNUMBER,
T7.FNAME as FSECUNITNAME,
T7.FNUMBER as FSECUNITNUMBER,
T10.FBILLNO as FFLOWCARDBILLNO ,
T11.FNUMBER as FRESOURCENUMBER,
T11.FNAME as FRESOURCENAME ,
T12.FNUMBER as FPROCESSNUMBER,
T12.FNAME as FPROCESSNAME ,
T15.FNUMBER as FNEXTPROCESSNUMBER,
T15.FNAME as FNEXTPROCESSNAME,
CASE WHEN T13.FBARSTATUS=1 OR T13.FBARSTATUS=2 THEN '在库'
ELSE CASE WHEN T14.FQTY-T14.FOUTQTY>0 AND T14.FMARGINQTY=0 THEN '在制'
END
END as FTYPENAME,
T1.FACCOUNTQTY-T1.FPRACTICALQTY as FDECQTY,
'T_SFC_BARCODEWIP' as FTABLENAME,
T16.FNAME AS FAUXPROPNAME,
T17.FNAME AS FSTOCKLOCNAME
from T_SFC_WIPINVENTORYENTRY T1
left join T_BD_MATERIAL T2 on T2.FINTERID=T1.FMATERIALID
left join T_BD_STOCK T4 on T4.FINTERID=T1.FSTOCKID
left join T_BD_UNIT T6 on T6.FINTERID=T1.FUNITID
left join T_BD_UNIT T7 on T7.FINTERID=T1.FSECUNITID
LEFT JOIN T_SFC_FLOWCARDENTRY T9 ON T1.FFLOWCARDDETAILID=T9.FDETAILID
LEFT JOIN T_SFC_FLOWCARD T10 ON T10.FINTERID=T1.FFLOWCARDID
LEFT JOIN T_ENG_RESOURCE T11 ON T11.FINTERID=T1.FRESOURCESID
LEFT JOIN T_SFC_PROCESS T12 ON T12.FINTERID=T1.FPROCESSID
LEFT JOIN T_SFC_BARCODEWIP T13 ON T1.FBARCODE=T13.FWIPCODE
LEFT JOIN T_SFC_REPORTENTRY3 T14 ON T14.FWIPNUMBER=T1.FBARCODE
LEFT JOIN T_SFC_PROCESS T15 ON T15.FINTERID=T13.FNEXTPROCESSID
LEFT JOIN T_BD_FLEXSITEMDETAILV T16 ON T1.FAUXPROPID=T16.FINTERID
LEFT JOIN T_BAS_FLEXVALUESDETAIL T17 ON T1.FSTOCKLOCID=T17.FINTERID
WHERE T13.FWIPCODE<>''
UNION
select
T1.* ,
T2.FNUMBER as FMATERIALNUMBER,
T2.FNAME as FMATERIALNAME,
T2.FSPECIFICATION,
T4.FNAME as FSTOCKNAME,
T4.FNUMBER as FSTOCKNUMBER,
T6.FNAME as FUNITNAME,
T6.FNUMBER as FUNITNUMBER,
T7.FNAME as FSECUNITNAME,
T7.FNUMBER as FSECUNITNUMBER,
T10.FBILLNO as FFLOWCARDBILLNO ,
T11.FNUMBER as FRESOURCENUMBER,
T11.FNAME as FRESOURCENAME ,
T12.FNUMBER as FPROCESSNUMBER,
T12.FNAME as FPROCESSNAME ,
T15.FNUMBER as FNEXTPROCESSNUMBER,
T15.FNAME as FNEXTPROCESSNAME,
CASE WHEN T13.FWIPSTATUS=1 OR T13.FWIPSTATUS=2 THEN '在库'
ELSE CASE WHEN T14.FQTY-T14.FOUTQTY>0 AND T14.FMARGINQTY=0 THEN '在制'
END
END as FTYPENAME,
T1.FACCOUNTQTY-T1.FPRACTICALQTY as FDECQTY,
'T_SFC_BARCODESN' as FTABLENAME,
T16.FNAME AS FAUXPROPNAME,
T17.FNAME AS FSTOCKLOCNAME
from T_SFC_WIPINVENTORYENTRY T1
left join T_BD_MATERIAL T2 on T2.FINTERID=T1.FMATERIALID
left join T_BD_STOCK T4 on T4.FINTERID=T1.FSTOCKID
left join T_BD_UNIT T6 on T6.FINTERID=T1.FUNITID
left join T_BD_UNIT T7 on T7.FINTERID=T1.FSECUNITID
LEFT JOIN T_SFC_FLOWCARDENTRY T9 ON T1.FFLOWCARDDETAILID=T9.FDETAILID
LEFT JOIN T_SFC_FLOWCARD T10 ON T10.FINTERID=T1.FFLOWCARDID
LEFT JOIN T_ENG_RESOURCE T11 ON T11.FINTERID=T1.FRESOURCESID
LEFT JOIN T_SFC_PROCESS T12 ON T12.FINTERID=T1.FPROCESSID
LEFT JOIN T_SFC_BARCODESN T13 ON T1.FBARCODE=T13.FSNCODE
LEFT JOIN T_SFC_REPORTENTRY3 T14 ON T14.FWIPNUMBER=T1.FBARCODE
LEFT JOIN T_SFC_PROCESS T15 ON T15.FINTERID=T13.FNEXTPROCESSID
LEFT JOIN T_BD_FLEXSITEMDETAILV T16 ON T1.FAUXPROPID=T16.FINTERID
LEFT JOIN T_BAS_FLEXVALUESDETAIL T17 ON T1.FSTOCKLOCID=T17.FINTERID
WHERE T13.FSNCODE<>''
UNION
select
T1.* ,
T2.FNUMBER as FMATERIALNUMBER,
T2.FNAME as FMATERIALNAME,
T2.FSPECIFICATION,
T4.FNAME as FSTOCKNAME,
T4.FNUMBER as FSTOCKNUMBER,
T6.FNAME as FUNITNAME,
T6.FNUMBER as FUNITNUMBER,
T7.FNAME as FSECUNITNAME,
T7.FNUMBER as FSECUNITNUMBER,
T10.FBILLNO as FFLOWCARDBILLNO ,
T11.FNUMBER as FRESOURCENUMBER,
T11.FNAME as FRESOURCENAME ,
T12.FNUMBER as FPROCESSNUMBER,
T12.FNAME as FPROCESSNAME ,
T15.FNUMBER as FNEXTPROCESSNUMBER,
T15.FNAME as FNEXTPROCESSNAME,
CASE WHEN T13.FWIPSTATUS=1 OR T13.FWIPSTATUS=2 THEN '在库'
ELSE CASE WHEN T14.FQTY-T14.FOUTQTY>0 AND T14.FMARGINQTY=0 THEN '在制'
END
END as FTYPENAME,
T1.FACCOUNTQTY-T1.FPRACTICALQTY as FDECQTY,
'T_SFC_BARCODETC' as FTABLENAME,
T16.FNAME AS FAUXPROPNAME,
T17.FNAME AS FSTOCKLOCNAME
from T_SFC_WIPINVENTORYENTRY T1
left join T_BD_MATERIAL T2 on T2.FINTERID=T1.FMATERIALID
left join T_BD_STOCK T4 on T4.FINTERID=T1.FSTOCKID
left join T_BD_UNIT T6 on T6.FINTERID=T1.FUNITID
left join T_BD_UNIT T7 on T7.FINTERID=T1.FSECUNITID
LEFT JOIN T_SFC_FLOWCARDENTRY T9 ON T1.FFLOWCARDDETAILID=T9.FDETAILID
LEFT JOIN T_SFC_FLOWCARD T10 ON T10.FINTERID=T1.FFLOWCARDID
LEFT JOIN T_ENG_RESOURCE T11 ON T11.FINTERID=T1.FRESOURCESID
LEFT JOIN T_SFC_PROCESS T12 ON T12.FINTERID=T1.FPROCESSID
LEFT JOIN T_SFC_BARCODETC T13 ON T1.FBARCODE=T13.FTCCODE
LEFT JOIN T_SFC_REPORTENTRY3 T14 ON T14.FWIPNUMBER=T1.FBARCODE
LEFT JOIN T_SFC_PROCESS T15 ON T15.FINTERID=T13.FNEXTPROCESSID
LEFT JOIN T_BD_FLEXSITEMDETAILV T16 ON T1.FAUXPROPID=T16.FINTERID
LEFT JOIN T_BAS_FLEXVALUESDETAIL T17 ON T1.FSTOCKLOCID=T17.FINTERID
WHERE T13.FTCCODE<>''";
}
/// <summary>
/// 在制品条码
/// </summary>
/// <returns></returns>
private string GetSqlBarcode()
{
return $@"SELECT *
FROM (
SELECT
T.FWIPCODE AS FBARCODE,
T.FBARSTATUS, T.FQTY, T.FBATCHNO AS FLOT,
T9.FINTERID AS FFLOWCARDID,T9.FBILLNO AS FFLOWCARDBILLNO,T9.FMOID,
T9.FMODETAILID,
T.FMOBILLNO AS FICMONO,
T.FRESOURCEID,
T8.FNUMBER AS FRESOURCENUM,
T8.FNAME AS FRESOURCENAME,
T.FMATERIALID,
T1.FNUMBER AS FMATERIALNUMBER,
T1.FNAME AS FMATERIALNAME,
T1.FSPECIFICATION AS FMODEL,
T1.FKFUNIT,T1.FKFPERIOD,
T.FSTOCKID,
T2.FNUMBER AS FSTOCKNUMBER,
T2.FNAME AS FSTOCKNAME,
T2.FISOPENLOCATION,
T3.FNUMBER AS FSTOCKLOCNUMBER, --仓位代码
T.FSTOCKLOCID,
T.FFLOWCARDDETAILID,
T4.FCODE AS FCURRCODE,
T.FUNITID,
T5.FNUMBER AS FUNITNUM,
T5.FNAME AS FUNITNAME,
T.FPROCESSID AS FCURPROCESSID,
T6.FNUMBER AS FCURPROCESSNUMBER,
T6.FNAME AS FCURPROCESSNAME,
T.FNEXTPROCESSID,
T7.FNUMBER AS FNEXTPROCESSNUMBER,
T7.FNAME AS FNEXTPROCESSNAME,
T.FNEXTCODE,
CASE WHEN T.FBARSTATUS=1 OR T.FBARSTATUS=2 THEN '在库'
ELSE CASE WHEN T10.FQTY-T10.FOUTQTY>0 AND T10.FMARGINQTY=0 THEN '在制' END
END AS FTYPENAME
FROM T_SFC_BARCODEWIP T
LEFT JOIN T_BD_MATERIAL T1 ON T.FMATERIALID = T1.FINTERID
LEFT JOIN T_BD_STOCK T2 ON T.FSTOCKID = T2.FINTERID
LEFT JOIN T_BAS_FLEXVALUESDETAIL T3 ON T.FSTOCKLOCID=T3.FINTERID
LEFT JOIN T_SFC_FLOWCARDENTRY T4 ON T.FFLOWCARDDETAILID = T4.FDETAILID
LEFT JOIN T_BD_UNIT T5 ON T.FUNITID = T5.FINTERID
LEFT JOIN T_SFC_PROCESS T6 ON T.FPROCESSID = T6.FINTERID
LEFT JOIN T_SFC_PROCESS T7 ON T.FNEXTPROCESSID = T7.FINTERID
LEFT JOIN T_ENG_RESOURCE T8 ON T.FRESOURCEID = T8.FINTERID
LEFT JOIN T_SFC_FLOWCARD T9 ON T.FFLOWCARDID = T9.FINTERID
LEFT JOIN T_SFC_REPORTENTRY3 T10 ON T10.FWIPNUMBER=T.FWIPCODE
WHERE T.FQUALITYSTATUS=0 AND T.FSTATUS!=80 AND T.FDELETED=0
UNION
SELECT
T.FSNCODE AS FBARCODE,
T.FWIPSTATUS AS FBARSTATUS,
T.FQTY,
T.FBATCHNO AS FLOT,
T9.FINTERID AS FFLOWCARDID,
T9.FBILLNO AS FFLOWCARDBILLNO,
T9.FMOID,
T9.FMODETAILID,
T.FMOBILLNO AS FICMONO,
T.FRESOURCEID,
T8.FNUMBER AS FRESOURCENUM,
T8.FNAME AS FRESOURCENAME,
T.FMATERIALID,
T1.FNUMBER AS FMATERIALNUMBER,
T1.FNAME AS FMATERIALNAME,
T1.FSPECIFICATION AS FMODEL,
T1.FKFUNIT,T1.FKFPERIOD,
T.FSTOCKID,
T2.FNUMBER AS FSTOCKNUMBER,
T2.FNAME AS FSTOCKNAME,
T2.FISOPENLOCATION,
T3.FNUMBER AS FSTOCKLOCNUMBER, --仓位代码
T.FSTOCKLOCID,
T4.FDETAILID AS FFLOWCARDDETAILID,
T4.FCODE AS FCURRCODE,
T.FUNITID,
T5.FNUMBER AS FUNITNUM,
T5.FNAME AS FUNITNAME,
T.FCURPROCESSID,
T6.FNUMBER AS FCURPROCESSNUMBER,
T6.FNAME AS FCURPROCESSNAME,
T.FNEXTPROCESSID,
T7.FNUMBER AS FNEXTPROCESSNUMBER,
T7.FNAME AS FNEXTPROCESSNAME,
'' AS FNEXTCODE,
CASE WHEN T.FWIPSTATUS=1 OR T.FWIPSTATUS=2 THEN '在库'
ELSE CASE WHEN T10.FQTY-T10.FOUTQTY>0 AND T10.FMARGINQTY=0 THEN '在制' END
END AS FTYPENAME
FROM T_SFC_BARCODESN T
LEFT JOIN T_BD_MATERIAL T1 ON T.FMATERIALID = T1.FINTERID
LEFT JOIN T_BD_STOCK T2 ON T.FSTOCKID = T2.FINTERID
LEFT JOIN T_BAS_FLEXVALUESDETAIL T3 ON T.FSTOCKLOCID=T3.FINTERID
LEFT JOIN T_SFC_FLOWCARDENTRY T4 ON T.FSOURCEDETAILID = T4.FDETAILID
LEFT JOIN T_BD_UNIT T5 ON T.FUNITID = T5.FINTERID
LEFT JOIN T_SFC_PROCESS T6 ON T.FCURPROCESSID = T6.FINTERID
LEFT JOIN T_SFC_PROCESS T7 ON T.FNEXTPROCESSID = T7.FINTERID
LEFT JOIN T_ENG_RESOURCE T8 ON T.FRESOURCEID = T8.FINTERID
LEFT JOIN T_SFC_FLOWCARD T9 ON T.FSOURCEINTERID = T9.FINTERID
LEFT JOIN T_SFC_REPORTENTRY3 T10 ON T10.FWIPNUMBER=T.FSNCODE
WHERE T.FSOURCETRANTYPE=3 AND T.FQUALITYSTATUS=0 AND T.FSTATUS!=80 AND T.FDELETED=0
UNION
SELECT
T.FTCCODE AS FBARCODE,
T.FWIPSTATUS AS FBARSTATUS,
T.FQTY,
T.FBATCHNO AS FLOT,
T9.FINTERID AS FFLOWCARDID,
T9.FBILLNO AS FFLOWCARDBILLNO,
T9.FMOID,
T9.FMODETAILID,
T.FMOBILLNO AS FICMONO,
T.FRESOURCEID,
T8.FNUMBER AS FRESOURCENUM,
T8.FNAME AS FRESOURCENAME,
T.FMATERIALID,
T1.FNUMBER AS FMATERIALNUMBER,
T1.FNAME AS FMATERIALNAME,
T1.FSPECIFICATION AS FMODEL,
T1.FKFUNIT,T1.FKFPERIOD,
T.FSTOCKID,
T2.FNUMBER AS FSTOCKNUMBER,
T2.FNAME AS FSTOCKNAME,
T2.FISOPENLOCATION,
T3.FNUMBER AS FSTOCKLOCNUMBER, --仓位代码
T.FSTOCKLOCID,
T4.FDETAILID AS FFLOWCARDDETAILID,
T4.FCODE AS FCURRCODE,
T.FUNITID,
T5.FNUMBER AS FUNITNUM,
T5.FNAME AS FUNITNAME,
T.FCURPROCESSID,
T6.FNUMBER AS FCURPROCESSNUMBER,
T6.FNAME AS FCURPROCESSNAME,
T.FNEXTPROCESSID,
T7.FNUMBER AS FNEXTPROCESSNUMBER,
T7.FNAME AS FNEXTPROCESSNAME,
'' AS FNEXTCODE,
CASE WHEN T.FWIPSTATUS=1 OR T.FWIPSTATUS=2 THEN '在库'
ELSE CASE WHEN T10.FQTY-T10.FOUTQTY>0 AND T10.FMARGINQTY=0 THEN '在制' END
END AS FTYPENAME
FROM T_SFC_BARCODETC T
LEFT JOIN T_BD_MATERIAL T1 ON T.FMATERIALID = T1.FINTERID
LEFT JOIN T_BD_STOCK T2 ON T.FSTOCKID = T2.FINTERID
LEFT JOIN T_BAS_FLEXVALUESDETAIL T3 ON T.FSTOCKLOCID=T3.FINTERID
LEFT JOIN T_SFC_FLOWCARDENTRY T4 ON T.FSOURCEDETAILID = T4.FDETAILID
LEFT JOIN T_BD_UNIT T5 ON T.FUNITID = T5.FINTERID
LEFT JOIN T_SFC_PROCESS T6 ON T.FCURPROCESSID = T6.FINTERID
LEFT JOIN T_SFC_PROCESS T7 ON T.FNEXTPROCESSID = T7.FINTERID
LEFT JOIN T_ENG_RESOURCE T8 ON T.FRESOURCEID = T8.FINTERID
LEFT JOIN T_SFC_FLOWCARD T9 ON T.FSOURCEINTERID = T9.FINTERID
LEFT JOIN T_SFC_REPORTENTRY3 T10 ON T10.FWIPNUMBER=T.FTCCODE
WHERE T.FSOURCETRANTYPE=1 AND T.FQUALITYSTATUS=0 AND T.FSTATUS!=80 AND T.FDELETED=0
) T
";
}
/// <summary>
/// 验证盘点方案是否正确
/// </summary>
/// <param name="SData"></param>
/// <param name="context"></param>
/// <returns></returns>
private async Task<GetWIPSchemeTo> IsWIPData(GetWIPSchemeFrom SData, APPServerContext context)
{
string sqlWhere = string.Empty, sql = string.Empty;
if (!SData.sourceBillNO.IsNullOrEmpty())
{
sqlWhere += $@" AND FBILLNO='{SData.sourceBillNO}'";
}
if (!SData.orgID.IsNullOrEmpty())
{
sqlWhere += $@" AND FCOMPANYID='{SData.orgID}'";
}
GetWIPSchemeTo data = new GetWIPSchemeTo();
sql = $@"SELECT
FINTERID,
FBILLNO AS FPLANNO,
FNAME AS FPLANNAME,
FSTATUS,
FSTOCKID,
FISCHECK
FROM T_SFC_WIPINVENTORY
WHERE 1=1 {sqlWhere}";
var WIPSchemeTos = await context.Database.SqlQueryAsync<GetWIPSchemeTo>(sql);
if (WIPSchemeTos == null || WIPSchemeTos.Count == 0)
throw new O3AppException($@"查询错误,盘点方案【{SData.sourceBillNO}】未找到!", 404);
else
data = WIPSchemeTos[0];
if (data.FSTATUS == 40)
{
throw new O3AppException($@"盘点方案【{SData.sourceBillNO}】已审核!!");
}
if (data.FSTOCKID.IsNullOrEmpty())
throw new O3AppException($@"盘点方案【{SData.sourceBillNO}】未维护仓库!");
#region 仓库编码名称赋值
if (!data.FSTOCKID.IsNullOrEmpty())
{
DataTable dtStocks = context.Database.SqlQuery($@"SELECT FNUMBER,FNAME FROM T_BD_STOCK
WHERE FINTERID IN ({string.Join(",", data.FSTOCKID.Split(',').ToList().Select(o => "'" + o + "'").ToList())})");
data.FSTOCKNUMBER = string.Join(",", dtStocks.AsEnumerable().Select(o => o["FNUMBER"].ToStringEx()).ToList());
data.FSTOCKNAME = string.Join(",", dtStocks.AsEnumerable().Select(o => o["FNAME"].ToStringEx()).ToList());
}
#endregion
return data;
}
}
}