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 } }