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报表
///
/// 获取KPI报表
///
///
/// out 总行数
///
/// [Hanhe(DBS)] created by 2019/1/3
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 物流公司统计
///
/// 物流公司统计
///
///
///
///
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'已完成' 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 物流出库详情
///
/// 物流出库详情
///
///
///
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'') " + where;
return ExecuteDataTable(sql);
}
#endregion
}
}