Files
GX.O3.WMS/APP.O3/Repositories/WMS/STKInventoryRepository.cs

163 lines
7.9 KiB
C#
Executable File

using Microsoft.Extensions.DependencyInjection;
using Newtonsoft.Json.Linq;
using Opsoft.Data;
using Opsoft.NetCore.O3App.Domain;
using Opsoft.NetCore.O3App.Domain.Interface;
using Opsoft.NetCore.O3App.Domain.UnitOfWork.WMS.Model;
using Opsoft.NetCore.O3App.Domain.UnitOfWork.WMS.IRepositories;
using Opsoft.NetCore.O3App.Shared;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
namespace Opsoft.NetCore.O3App.Repositories.WMS
{
[MapTo(typeof(ISTKInventoryRepository), ServiceLifetime.Transient)]
public partial class STKInventoryRepository : RepositoryBase<BaseEntity>, ISTKInventoryRepository
{
public STKInventoryRepository() : base()
{
}
/// <summary>
/// 库存查询
/// </summary>
/// <param name="stockList"></param>
/// <returns></returns>
public async Task<Dictionary<string, object>> QueryInventory(JObject paramaters)
{
string strSql = string.Empty;
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
if (paramaters["scanType"].ToInt() == 1)
{
strSql = @$"SELECT
A.FSTOCKID
,A.FSTOCKLOCID
,B.FNUMBER AS FMATERIALNUMBER
,B.FNAME AS FMATERIALNAME
,C.FNUMBER AS FSCSTOCKNUMBER
,C.FISOPENLOCATION
,D.FNUMBER AS FSCSPNUMBER --仓位代码
,A.FLOT --批号
FROM T_BD_BARCODERS A
LEFT JOIN T_BD_MATERIAL B ON A.FMATERIALID = B.FINTERID
LEFT JOIN T_BD_STOCK C ON A.FSTOCKID = C.FINTERID
LEFT JOIN T_BAS_FLEXVALUESDETAIL D ON A.FSTOCKLOCID = D.FINTERID
WHERE A.FBARCODE = '{paramaters["barCodeNum"].ToStringEx()}'";
DataTable cursor = context.Database.SqlQuery(strSql);
if (cursor == null || cursor.Rows.Count <= 0)
{
throw new O3AppException(string.Format("条码【{0}】信息未找到", paramaters["barCodeNum"].ToStringEx()), 400);
}
//操作数据
#region 默认带出仓库仓位
string strLocation = string.Empty;
if (string.IsNullOrEmpty(cursor.Rows[0]["FSCSTOCKNUMBER"].ToStringEx()))
{
Hashtable sysParams = context.PBValue("STOCKCHAR,C;FLEXCHAR,C;");
string stcokChar = sysParams["STOCKCHAR"].ToStringEx();
string valueChar = stcokChar ?? "-";
string flexChar = sysParams["FLEXCHAR"].ToStringEx();
flexChar ??= ".";
strLocation = (!cursor.Rows[0]["FISOPENLOCATION"].ToBoolean() || string.IsNullOrEmpty(cursor.Rows[0]["FSCSPNUMBER"].ToString())) ?
cursor.Rows[0]["FSCSTOCKNUMBER"].ToString() :
cursor.Rows[0]["FSCSTOCKNUMBER"].ToString() + valueChar + cursor.Rows[0]["FSCSPNUMBER"].ToString().Replace(".", flexChar);
}
#endregion
//返回值
return await Task.Run(() => new Dictionary<string, object> { { "itemNumber", cursor.Rows[0]["FMATERIALNUMBER"] }, { "itemName", cursor.Rows[0]["FMATERIALNAME"] }, { "FLocation", strLocation }, { "FLot", cursor.Rows[0]["FLOT"] } });
}
else
{
//查询的sql
strSql = @$"SELECT TOP {paramaters["count"].ToInt()}
CONVERT(FLOAT,ISNULL(SUM(A.FBASEUNITQTY),0)) AS FQTY,
C.FNUMBER AS FSTOCKNUMBER,
C.FNAME as FSTOCKNAME,
D.FNAME as FSPNAME,
D.FNUMBER AS FSPNUMBER,
B.FNUMBER AS FMATERIALNUMBER, B.FNAME AS FMATERIALNAME,
A.FLOT,
B.FSPECIFICATION,
E.FNAME AS FUNITNAME,
F.FNAME AS FAUXPROPID
FROM T_STK_INVENTORY (nolock) A
LEFT JOIN T_BD_MATERIAL B ON A.FMATERIALID = B.FINTERID
LEFT JOIN T_BD_STOCK C ON A.FSTOCKID = C.FINTERID
LEFT JOIN T_BAS_FLEXVALUESDETAIL D ON A.FSTOCKLOCID = D.FINTERID
LEFT JOIN T_BD_UNIT E ON A.FBASEUNITID = E.FINTERID
LEFT JOIN T_BD_FLEXSITEMDETAILV F ON A.FAUXPROPID=F.FINTERID
WHERE 1=1 ";
//查询条件
//物料代码
string itemNumber = paramaters["itemNumber"].ToStringEx().Trim();
if (!string.IsNullOrEmpty(itemNumber))
{
if (paramaters["itemNumber"].ToStringEx().Contains(","))
{
strSql += " AND B.FNUMBER IN ('" + string.Join("','", itemNumber.Split(',')) + "')";
}
else
{
strSql += " AND B.FNUMBER LIKE '%" + itemNumber + "%'";
}
}
//物料名称
string itemName = paramaters["itemName"].ToStringEx().Trim();
if (!string.IsNullOrEmpty(itemName))
{
if (itemName.Contains(","))
{
strSql += " AND B.FNAME IN ('" + string.Join("','", itemName.Split(',')) + "')";
}
else
{
strSql += " AND B.FNAME LIKE '%" + itemName + "%'";
}
}
string lot = paramaters["lot"].ToStringEx().Trim();
if (!string.IsNullOrEmpty(lot))
{
if (lot.Contains(","))
{
strSql += " AND A.FLOT IN ('" + string.Join("','", lot.Split(',')) + "')";
}
else
{
strSql += " AND A.FLOT LIKE '%" + lot + "%'";
}
}
//仓库仓位
string stockData = paramaters["stockData"].ToStringEx().Trim();
if (!string.IsNullOrEmpty(stockData))
{
OrgID = paramaters["orgid"].ToStringEx();
LOCATION location = await AnalyzingStock(context, stockData, false);
if (location == null)
{
throw new O3AppException(string.Format("位置信息【{0}】未找到", stockData), 400);
}
if (!string.IsNullOrEmpty(location.FSTOCKID))
{
strSql += " AND A.FSTOCKID = '" + location.FSTOCKID + "'";
}
if (!string.IsNullOrEmpty(location.FSTOCKLOCID))
{
strSql += " AND A.FSTOCKLOCID = '" + location.FSTOCKLOCID + "'";
}
}
//分组
strSql += " GROUP BY C.FNUMBER,C.FNAME,D.FNAME,D.FNUMBER,B.FNUMBER,B.FNAME,A.FLOT,B.FSPECIFICATION,E.FNAME,A.FAUXPROPID,F.FNAME";
DataTable cursor = context.Database.SqlQuery(strSql);
//返回值
return await Task.Run(() => new Dictionary<string, object> { { "data", cursor } });
}
}
}
}