using Rhea.Common;
|
using Tiger.Model;
|
using SqlSugar;
|
using System;
|
using System.Collections.Generic;
|
using System.Threading.Tasks;
|
using System.Linq;
|
using Newtonsoft.Json;
|
using Rhea.Common.Authorization;
|
using Newtonsoft.Json.Linq;
|
using Tiger.Model.Minsun;
|
using Tiger.IBusiness;
|
using System.Data.Entity;
|
|
namespace Tiger.Business.WMS
|
{
|
public partial class KanBan : IKanBan
|
{
|
/// <summary>
|
/// warehousing看板全部数据对接
|
/// </summary>
|
/// <param name="input"></param>
|
/// <returns></returns>
|
public async Task<MaterialKBOutput> GetReceiptDetail(MaterialKBInput input)
|
{
|
try
|
{
|
DateTime dateTime = DateTime.Now.AddDays(-2);
|
int nYear = DateTime.Now.Year;
|
//var sql = $"SELECT SUM(INSTOCKQTY) as Total, convert(varchar(100),datepart(mm, LastModificationTime))+'月' as Month FROM WMS_RECEIPT_D WHERE LINESTATUS=8 and datepart(yy,LastModificationTime)=@nYear GROUP BY datepart(mm, LastModificationTime)";
|
|
//var sList = Db.Queryable<BIZ_ERP_RECEIPT_DTL, V_USER_ORG>((d, r) =>
|
// new JoinQueryInfos(
|
// JoinType.Left, d.CREATE_USER == r.USER_ID
|
// ))
|
// .Where((d, r) => d.LINESTATUS == 2 && d.UPDATE_TIME.Year == nYear && r.USERGROUP_CODE == "清溪结构仓")
|
// //.GroupBy((d, r) => d.UPDATE_TIME)
|
// .Select((d, r) => new TotalByMonth
|
// {
|
// Total = SqlFunc.AggregateSum(d.INSTOCKQTY),
|
// Month = $"{d.UPDATE_TIME.Month}月"
|
// })
|
//.ToList();
|
|
var sList = Biz.Db.Queryable<V_MONTH_IN_SUM>().ToList();
|
|
//var sList = Db.Queryable<BIZ_ERP_RECEIPT_DTL>()
|
// .LeftJoin<V_USER_ORG>((d, r) => d.CREATE_USER==r.USER_ID)
|
// .GroupBy((d, r) => d.UPDATE_TIME.Month)
|
// .Where((d, r) => d.LINESTATUS == 1 && d.UPDATE_TIME.Year == nYear && r.USERGROUP_CODE == "清溪结构仓")
|
// //.GroupBy((d, r) => new { Month = d.UPDATE_TIME.Month })
|
// .Select((d, r) => new TotalByMonth
|
// {
|
// Total = SqlFunc.AggregateSum(d.INSTOCKQTY),
|
// Month = $"{d.UPDATE_TIME.Month}月"
|
// })
|
// .ToList();
|
|
//SqlParameter[] nParams = new SqlParameter[]
|
//{
|
// new SqlParameter("@nYear",System.Data.SqlDbType.Int),
|
//};
|
//nParams[0].Value = nYear;
|
|
//var sList = SqlQuery<TotalByMonth>(sql, nParams).ToList();
|
|
//var taskTotalSql = $"SELECT SUM(QTY-INSTOCKQTY) as Total,'' as Month FROM WMS_RECEIPT_D WHERE LINESTATUS = 4 and datepart(yy,LastModificationTime)=@nYear ";
|
|
var sTaskTotal = Biz.Db.Queryable<BIZ_ERP_RECEIPT_DTL, V_USER_ORG>((d, r) =>
|
new JoinQueryInfos(
|
JoinType.Left, d.CREATE_USER == r.USER_ID
|
))
|
.Where((d, r) => (d.LINESTATUS == 1) && d.UPDATE_TIME.Year == nYear && r.USERGROUP_CODE == "清溪结构仓")
|
.Select((d, r) => new TotalByMonth
|
{
|
Total = SqlFunc.RowCount(),
|
Month = ""
|
}).First();
|
|
//SqlParameter[] nParams2 = new SqlParameter[]
|
//{
|
//new SqlParameter("@nYear",System.Data.SqlDbType.Int),
|
//};
|
//nParams2[0].Value = nYear;
|
|
//var sTaskTotal = this._receiptDetailRepository.SqlQuery<TotalByMonth>(taskTotalSql, nParams2).FirstOrDefault();
|
|
//var unChecklSql = $"SELECT SUM(QTY) as Total,'' as Month FROM WMS_RECEIPT_D WHERE LINESTATUS = 2 and datepart(yy,LastModificationTime)=@nYear ";
|
|
var sUnCheckNumber = Biz.Db.Queryable<BIZ_ERP_RECEIPT_DTL, V_USER_ORG>((d, r) =>
|
new JoinQueryInfos(
|
JoinType.Left, d.CREATE_USER == r.USER_ID
|
))
|
.Where((d, r) => d.LINESTATUS == 1 && d.UPDATE_TIME.Year == nYear && r.USERGROUP_CODE == "清溪结构仓")
|
.Select((d, r) => new TotalByMonth
|
{
|
Total = SqlFunc.AggregateSum(d.QTY),
|
Month = ""
|
}).First();
|
|
//SqlParameter[] nParams3 = new SqlParameter[]
|
//{
|
//new SqlParameter("@nYear",System.Data.SqlDbType.Int),
|
//};
|
//nParams3[0].Value = nYear;
|
|
//var sUnCheckNumber = this._receiptDetailRepository.SqlQuery<TotalByMonth>(unChecklSql, nParams3).FirstOrDefault();
|
|
//var sTotalSql = $"SELECT SUM(INSTOCKQTY) as Total,'' as Month FROM WMS_RECEIPT_D WHERE LINESTATUS in (4, 8) and datepart(yy,LastModificationTime)=@nYear ";
|
//结构仓数量
|
var Structure = Biz.Db.Queryable<BIZ_ERP_RECEIPT_DTL, V_USER_ORG>((d, r) =>
|
new JoinQueryInfos(
|
JoinType.Left, d.CREATE_USER == r.USER_ID
|
))
|
.Where((d, r) => (d.LINESTATUS == 2) && d.UPDATE_TIME.Year == nYear && r.USERGROUP_CODE == "清溪结构仓")
|
.Select((d, r) => new TotalByMonth
|
{
|
Total = SqlFunc.AggregateSum(d.INSTOCKQTY),
|
Month = ""
|
}).First();
|
|
//电子仓数量
|
var Electron = Biz.Db.Queryable<BIZ_ERP_RECEIPT_DTL, V_USER_ORG>((d, r) =>
|
new JoinQueryInfos(
|
JoinType.Left, d.CREATE_USER == r.USER_ID
|
))
|
.Where((d, r) => (d.LINESTATUS == 2) && d.UPDATE_TIME.Year == nYear && r.USERGROUP_CODE == "清溪电子仓")
|
.Select((d, r) => new TotalByMonth
|
{
|
Total = SqlFunc.AggregateSum(d.INSTOCKQTY),
|
Month = ""
|
}).First();
|
//SqlParameter[] nParams4 = new SqlParameter[]
|
//{
|
//new SqlParameter("@nYear",System.Data.SqlDbType.Int),
|
//};
|
//nParams4[0].Value = nYear;
|
|
//var sTotal = this._receiptDetailRepository.SqlQuery<TotalByMonth>(sTotalSql, nParams4).FirstOrDefault();
|
|
var nOutput = new MaterialKBOutput()
|
{
|
Structure = Structure.Total,
|
Electron = Electron.Total,
|
UnCheckNumber = sUnCheckNumber.Total,
|
TaskTotal = sTaskTotal.Total,
|
TotalByMonthList = sList,
|
// IncomingRptList = nResult
|
};
|
return nOutput;
|
}
|
catch (Exception ex)
|
{
|
|
throw;
|
}
|
}
|
/// <summary>
|
/// warehousing入库明细信息
|
/// </summary>
|
/// <param name="input"></param>
|
/// <returns></returns>
|
public async Task<MaterialKBOutput> GetIncomingDetail(MaterialKBInput input)
|
{
|
|
int pagenumber = Convert.ToInt16(input.Barcode); // pagenumber是从1开始的不是从零开始的
|
int pageSize = 10;
|
int totalCount = 0;
|
|
DateTime dateTime = DateTime.Now.AddDays(-2);
|
//入库信息
|
var nResult = Biz.Db.Queryable<BIZ_ERP_RECEIPT_DTL, BAS_ITEM, V_USER_ORG>((r, m, s) =>
|
new JoinQueryInfos(
|
JoinType.Left, r.ITEM_CODE == m.ITEM_CODE,
|
JoinType.Left, r.CREATE_USER == s.USER_ID
|
))
|
.Where((r, m, s) => r.CREATE_TIME > dateTime && s.USERGROUP_CODE == "清溪结构仓")
|
.OrderByDescending((r, m, s) => r.CREATE_TIME)
|
.Select((r, m, s) => new IncomingRptOutput
|
{
|
CreationTime = r.CREATE_TIME,
|
BillCode = r.SOURCECODE,
|
WarehouseCode = r.WAREHOUSECODE,
|
MaterialCode = r.ITEM_CODE,
|
MaterialName = m.ITEM_NAME,
|
Status = r.LINESTATUS,
|
Qty = r.QTY,
|
InstockQty = r.INSTOCKQTY,
|
LeftQty = r.QTY - r.INSTOCKQTY
|
})
|
.ToPageList(pagenumber, pageSize, ref totalCount);
|
//nResult = nResult.OrderByDescending(q=>q.CreationTime).ToList();
|
//totalCount = 21;
|
if (totalCount > pageSize * pagenumber)
|
{
|
pagenumber += 1;
|
}
|
else
|
{
|
pagenumber = 1;
|
}
|
var nOutput = new MaterialKBOutput()
|
{
|
Structure = pagenumber,
|
IncomingRptList = nResult
|
};
|
return nOutput;
|
}
|
}
|
}
|
