using HH.WMS.Common;
|
using HH.WMS.Entitys.Common;
|
/********************************************************************************
|
|
** auth： DBS
|
|
** date： 2019/1/17 16:59:55
|
|
** desc： 尚未编写描述
|
|
** Ver.: V1.0.0
|
|
*********************************************************************************/
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace HH.WMS.DAL.AllQuery
|
{
|
public class ReportDal : DapperBaseDAL
|
{
|
#region 获取KPI报表
|
/// <summary>
|
/// 获取KPI报表
|
/// </summary>
|
/// <param name="searchModel"></param>
|
/// <param name="total">out 总行数</param>
|
/// <returns></returns>
|
/// <History>[Hanhe(DBS)] created by 2019/1/3</History>
|
public System.Data.DataTable GetKpiList(Entitys.Common.SearchModel searchModel, out long total)
|
{
|
var v = new
|
{
|
CN_S_STOCK_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_STOCK_CODE),
|
CN_S_CREATOR_BY = Util.ToStringInput(searchModel.SearchCondition.CN_S_USER_NAME),
|
CN_T_START = Util.ToStringInput(searchModel.SearchCondition.CN_T_START),
|
CN_T_END = Util.ToStringInput(searchModel.SearchCondition.CN_T_END)
|
};
|
string whereSql = "";
|
if (!string.IsNullOrEmpty(v.CN_S_STOCK_CODE))
|
{
|
if (whereSql.Length > 0)
|
whereSql += " and ";
|
whereSql += " CN_S_STOCK_CODE='" + v.CN_S_STOCK_CODE + "' ";
|
}
|
if (!string.IsNullOrEmpty(v.CN_S_CREATOR_BY))
|
{
|
if (whereSql.Length > 0)
|
whereSql += " and ";
|
whereSql += " CN_S_CREATOR_BY='" + v.CN_S_CREATOR_BY + "' ";
|
}
|
if (!string.IsNullOrEmpty(v.CN_T_START))
|
{
|
if (whereSql.Length > 0)
|
whereSql += " and ";
|
whereSql += " CN_T_CREATE >=CONVERT(datetime,'" + v.CN_T_START + "-01') ";
|
}
|
if (!string.IsNullOrEmpty(v.CN_T_END))
|
{
|
if (whereSql.Length > 0)
|
whereSql += " and ";
|
whereSql += " CN_T_CREATE < DATEADD(MONTH,1,CONVERT(datetime,'" + v.CN_T_END + "-01')) ";
|
}
|
if (whereSql.Length > 0)
|
whereSql = " where " + whereSql;
|
string sql = @"(select ISNULL(user1,ISNULL(user2,user3)) userName ,ISNULL(create1,ISNULL(create2,create3)) createTime,upQty,ISNULL(upQtyPer,0) upQtyPer,downQty,ISNULL(downQtyPer,0) downQtyPer,recheckQty,ISNULL(recheckQtyPer,0) recheckQtyPer from
|
( select CN_S_CREATOR_BY user1,CONVERT(VarChar(7), CN_T_CREATE, 120) create1,SUM(CN_F_QUANTITY) upQty,
|
upQtyPer = cast(SUM(CN_F_QUANTITY)*1.0/(select SUM(CN_F_QUANTITY) from TN_WM_UP_HISTORY " + whereSql + @") as decimal(28,2))*100 from TN_WM_UP_HISTORY
|
" + whereSql + @"
|
group by CN_S_CREATOR_BY ,CONVERT(VarChar(7), CN_T_CREATE, 120)) a
|
full join
|
( select CN_S_CREATOR_BY user2,CONVERT(VarChar(7), CN_T_CREATE, 120) create2,SUM(CN_F_QUANTITY) downQty,
|
downQtyPer = cast(SUM(CN_F_QUANTITY)*1.0/(select SUM(CN_F_QUANTITY) from tn_wm_sorting_result " + whereSql + @") as decimal(28,2))*100 from tn_wm_sorting_result
|
" + whereSql + @"
|
group by CN_S_CREATOR_BY,CONVERT(VarChar(7), CN_T_CREATE, 120)) b
|
on a.create1=b.create2 and a.user1=b.user2
|
full join
|
( select CN_S_CREATOR_BY user3,CONVERT(VarChar(7), CN_T_CREATE, 120) create3,SUM(CN_F_QUANTITY) recheckQty,
|
recheckQtyPer = cast(SUM(CN_F_QUANTITY)*1.0/(select SUM(CN_F_QUANTITY) from TN_WM_ORDER_SORTING_REL " + whereSql + @") as decimal(28,2))*100 from TN_WM_ORDER_SORTING_REL
|
" + whereSql + @"
|
group by CN_S_CREATOR_BY,CONVERT(VarChar(7), CN_T_CREATE, 120)) c
|
on a.create1=c.create3 and a.user1=c.user3) t ";
|
return ExecutePagingData(sql, searchModel.PageIndex, searchModel.PageSize, out total, " order by createTime", "");
|
}
|
|
public DataTable GetStockCompareReport(SearchModel searchModel, out long total)
|
{
|
|
var condition = searchModel.SearchCondition;
|
string whereSql = "";
|
if (!string.IsNullOrEmpty(condition.CN_T_START.ToString()))
|
{
|
whereSql = whereSql + " and CN_T_COMARE_DATE >= '" + Util.ToStringInput(condition.CN_T_START) + " 00:00:00'";
|
}
|
if (!string.IsNullOrEmpty(condition.CN_T_END.ToString()))
|
{
|
whereSql = whereSql + " and CN_T_COMARE_DATE <= '" + Util.ToStringInput(condition.CN_T_END) + " 23:59:59'";
|
}
|
|
string sql = @"(select * from TN_WM_STOCKQTY_COMPARE WHERE 1=1 " + whereSql + @") t";
|
return ExecutePagingData(sql, searchModel.PageIndex, searchModel.PageSize, out total, " order by CN_T_COMARE_DATE", "");
|
|
}
|
#endregion
|
|
#region 物流公司统计
|
/// <summary>
|
/// 物流公司统计
|
/// </summary>
|
/// <param name="searchModel"></param>
|
/// <param name="total"></param>
|
/// <returns></returns>
|
public DataTable GetLogisticsReport(SearchModel searchModel, out long total)
|
{
|
var v = new
|
{
|
LogisticsFlag = Util.ToStringInput(searchModel.SearchCondition.LogisticsFlag),
|
StartTime = Util.ToStringInput(searchModel.SearchCondition.StartTime),
|
EndTime = Util.ToStringInput(searchModel.SearchCondition.EndTime),
|
};
|
|
string where = string.Empty;
|
if (!string.IsNullOrEmpty(v.LogisticsFlag))
|
{
|
where += " and CN_S_LOGISTICS_FLAG='" + v.LogisticsFlag + "'";
|
}
|
if (!string.IsNullOrEmpty(v.StartTime))
|
{
|
where += " and CN_T_CREATE>=CONVERT(datetime,'" + v.StartTime + "-01') ";
|
}
|
if (!string.IsNullOrEmpty(v.EndTime))
|
{
|
where += " and CN_T_CREATE<CONVERT(datetime,'" + v.EndTime + "-01') ";
|
}
|
|
string sql = @"(select CN_S_LOGISTICS_FLAG,CN_S_LOGISTICS_NAME,
|
sum(case when CN_S_STATE='已完成' then 1 else 0 end) DeliverGoodsNum,
|
sum(case when CN_S_STATE<>'已完成' then 1 else 0 end) StayNum,
|
CONVERT(VarChar(7), CN_T_CREATE, 120) CN_T_CREATE
|
from tn_wm_out_mst where (CN_S_LOGISTICS_FLAG IS NOT NULL AND CN_S_LOGISTICS_FLAG<>'')
|
" + where + @"
|
group by CN_S_LOGISTICS_FLAG,CN_S_LOGISTICS_NAME,CONVERT(VarChar(7), CN_T_CREATE, 120) ) t";
|
return ExecutePagingData(sql, searchModel.PageIndex, searchModel.PageSize, out total, " order by CN_T_CREATE", "");
|
}
|
#endregion
|
|
#region 物流出库详情
|
/// <summary>
|
/// 物流出库详情
|
/// </summary>
|
/// <param name="searchModel"></param>
|
/// <returns></returns>
|
public DataTable GetLogisticsDetail(SearchModel searchModel)
|
{
|
var v = new
|
{
|
LogisticsFlag = Util.ToStringInput(searchModel.SearchCondition.LogisticsFlag),
|
StartTime = Util.ToStringInput(searchModel.SearchCondition.StartTime),
|
EndTime = Util.ToStringInput(searchModel.SearchCondition.EndTime),
|
};
|
string where = string.Empty;
|
if (!string.IsNullOrEmpty(v.LogisticsFlag))
|
{
|
where += " and CN_S_LOGISTICS_FLAG='" + v.LogisticsFlag + "'";
|
}
|
if (!string.IsNullOrEmpty(v.StartTime))
|
{
|
where += " and CN_T_CREATE>=CONVERT(datetime,'" + v.StartTime + "-01') ";
|
}
|
if (!string.IsNullOrEmpty(v.EndTime))
|
{
|
where += " and CN_T_CREATE<CONVERT(datetime,'" + v.EndTime + "-01') ";
|
}
|
string sql = @"SELECT
|
CN_S_LOGISTICS_FLAG,
|
CN_S_LOGISTICS_NAME,
|
CN_S_WAYBILL_NO,
|
CN_S_CUSTOMER_NAME,
|
CN_S_RECEIVER_NAME,
|
CN_S_DETAIL_ADDRESS,
|
CN_T_CREATE,
|
ISNULL((SELECT COUNT(1) FROM TN_WM_B_OUT_PACK P WHERE
|
EXISTS(SELECT * FROM TN_WM_B_BOX_DTL WHERE P.CN_S_PACKING_CODE=CN_S_PACKING_CODE ) AND CN_S_OP_NO =T.CN_S_OP_NO
|
GROUP by CN_S_OP_NO),0) PackNum
|
FROM TN_WM_OUT_MST T WHERE (CN_S_LOGISTICS_FLAG IS NOT NULL AND CN_S_LOGISTICS_FLAG<>'') " + where;
|
return ExecuteDataTable(sql);
|
}
|
#endregion
|
}
|
}
|
