/// <summary> /// 根据配置统计单一数字 /// </summary> /// <param name="singleDataInpu"></param> /// <returns></returns> public Tuple <ServiceResult, decimal> GetSingleData(SingleReportInput singleDataInpu) { var returnValue = 0m; #region 安全验证 if (singleDataInpu == null) { if (string.IsNullOrEmpty(singleDataInpu.EntityType)) { return(Tuple.Create(ServiceResult.FailedMessage("实体类型不能为空"), returnValue)); } return(Tuple.Create(ServiceResult.FailedMessage("实体类型不能为空"), returnValue)); } Type typeFind = null; object instanceFind = null; var checkType = Resolve <IUIBaseService>() .CheckType(singleDataInpu.EntityType, ref typeFind, ref instanceFind); if (!checkType.Succeeded) { return(Tuple.Create(ServiceResult.FailedMessage("实体类型不存在"), returnValue)); } if (!(instanceFind is IEntity)) // 非实体类型不能完成数据统计 { return(Tuple.Create(ServiceResult.FailedMessage("非实体类型不能进行数据统计"), returnValue)); } #endregion 安全验证 var table = Resolve <ITableService>().GetSingle(r => r.Key == typeFind.Name); if (table == null) { return(Tuple.Create(ServiceResult.FailedMessage("表未找到"), returnValue)); } if (table.TableType == TableType.Mongodb) { var rs = DynamicService.ResolveMethod(typeFind.Name, "GetSingleReportData", singleDataInpu); returnValue = rs.Item2.ConvertToDecimal(); } if (table.TableType == TableType.SqlServer) { var tableName = table.TableName; var rs = _autoReportRepository.GetSingleData(singleDataInpu); return(Tuple.Create(ServiceResult.Success, rs)); // returnValue = _autoReportRepository.GetSingleData(tableName, singleDataInpu.Field, singleDataInpu.ReportType, singleDataInpu.StartTime, singleDataInpu.EndTime, singleDataInpu.SqlWhere); } return(Tuple.Create(ServiceResult.Success, returnValue)); }
public ApiResult <decimal> GetSingleReport([FromBody] SingleReportInput singleReportInput) { if (!this.IsFormValid()) { return(ApiResult.Failure <decimal>(this.FormInvalidReason())); } var result = Resolve <IAutoReportService>().GetSingleData(singleReportInput); return(ToResult(result)); }
public decimal GetSingleReportData(SingleReportInput singleReportInput) { return(0m); }
/// <summary> /// </summary> /// <param name="singleReportInput"></param> /// <returns></returns> public decimal GetSingleData(SingleReportInput singleReportInput) { if (string.IsNullOrEmpty(singleReportInput.Field)) { singleReportInput.Field = "id"; } singleReportInput.Condition.EntityType = singleReportInput.EntityType; var tableName = singleReportInput.Condition.GetTableName(); var dbContext = Ioc.Resolve <IAlaboUserRepository>().RepositoryContext; var sql = new StringBuilder(); switch (singleReportInput.Style) { case ReportStyle.Count: sql.Append(@"SELECT ISNULL(COUNT(" + singleReportInput.Field + "),0) AS " + singleReportInput.Field + " FROM " + tableName); break; case ReportStyle.Sum: sql.Append(@"SELECT ISNULL(SUM(" + singleReportInput.Field + "),0) AS " + singleReportInput.Field + " FROM " + tableName); break; case ReportStyle.Avg: sql.Append(@"SELECT ISNULL(AVG(" + singleReportInput.Field + "),0) AS " + singleReportInput.Field + " FROM " + tableName); break; case ReportStyle.Min: sql.Append(@"SELECT ISNULL(MIN(" + singleReportInput.Field + "),0) AS " + singleReportInput.Field + " FROM " + tableName); break; case ReportStyle.Max: sql.Append(@"SELECT ISNULL(MAX(" + singleReportInput.Field + "),0) AS " + singleReportInput.Field + " FROM " + tableName); break; case ReportStyle.ChainRatioYesterday: var yesterday = DateTime.Now.AddDays(-1); var today = DateTime.Now; sql.Append(@" SELECT ISNULL(T2." + singleReportInput.Field + " - T1." + singleReportInput.Field + ",0) AS " + singleReportInput.Field + " FROM "); sql.Append(@" (SELECT ISNULL(SUM(" + singleReportInput.Field + "),0) AS " + singleReportInput.Field + " FROM " + tableName + " where convert(varchar(10),CreateTime,120)=convert(varchar(10),'" + yesterday + "',120) )T1 left join "); sql.Append(@" (SELECT ISNULL(SUM(" + singleReportInput.Field + "),0) AS " + singleReportInput.Field + " FROM " + tableName + " where convert(varchar(10),CreateTime,120)=convert(varchar(10),'" + today + "',120) )T2 on 1=1"); break; case ReportStyle.ChainRatioLastWeek: var day = (int)DateTime.Now.DayOfWeek; var thisWeekStart = DateTime.Now.AddDays(-day + 1).ToString("yyyy-MM-dd 00:00:00"); //本周第一天 var thisWeekNow = DateTime.Now; //当前天 var lastWeekStart = DateTime.Now.AddDays(-day - 6).ToString("yyyy-MM-dd 00:00:00"); //上周第一天 var lastWeekEnd = DateTime.Now.AddDays(-day - 6).AddDays(day - 1).ToString("yyyy-MM-dd 23:59:59"); //上周对应当天 sql.Append(@" SELECT ISNULL(T2." + singleReportInput.Field + "-T1." + singleReportInput.Field + ",0) AS " + singleReportInput.Field + " FROM "); sql.Append(@" (SELECT ISNULL(SUM(" + singleReportInput.Field + "),0) AS " + singleReportInput.Field + " FROM " + tableName + " where CreateTime between '" + lastWeekStart + "' and '" + lastWeekEnd + "' )T1 left join "); sql.Append(@" (SELECT ISNULL(SUM(" + singleReportInput.Field + "),0) AS " + singleReportInput.Field + " FROM " + tableName + " where CreateTime between '" + thisWeekStart + "' and '" + thisWeekNow + "' )T2 on 1=1"); break; case ReportStyle.ChainRatioLastMonth: var thisMonthStart = DateTime.Now.GetMonthBegin().ToString("yyyy-MM-dd 00:00:00"); //本月第一天 var thisMonthNow = DateTime.Now; //当前天 var lastMonthStart = DateTime.Now.GetMonthBegin().AddMonths(-1); //上个月第一天 var lastMonthEnd = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd 23:59:59"); //上月对应当天 sql.Append(@" SELECT ISNULL(T2." + singleReportInput.Field + "-T1." + singleReportInput.Field + ",0) AS " + singleReportInput.Field + " FROM "); sql.Append(@" (SELECT ISNULL(SUM(" + singleReportInput.Field + "),0) AS " + singleReportInput.Field + " FROM " + tableName + " where CreateTime between '" + lastMonthStart + "' and '" + lastMonthEnd + "' )T1 left join "); sql.Append(@" (SELECT ISNULL(SUM(" + singleReportInput.Field + "),0) AS " + singleReportInput.Field + " FROM " + tableName + " where CreateTime between '" + thisMonthStart + "' and '" + thisMonthNow + "' )T2 on 1=1"); break; case ReportStyle.ChainRatioLastQuarter: sql.Append(@" SELECT ISNULL(T2." + singleReportInput.Field + "- T1." + singleReportInput.Field + ",0) AS " + singleReportInput.Field + " FROM "); sql.Append(@"(select ISNULL(SUM(" + singleReportInput.Field + "),0) AS " + singleReportInput.Field + " FROM " + tableName + " where DATEPART(qq,CreateTime)= DATEPART(qq,GETDATE())-1)T1 left join "); sql.Append(@"(select ISNULL(SUM(" + singleReportInput.Field + "),0) AS " + singleReportInput.Field + " FROM " + tableName + " where DATEPART(qq,CreateTime)= DATEPART(qq,GETDATE()))T2 on 1=1 "); break; case ReportStyle.ChainRatioLastYear: sql.Append(@"SELECT ISNULL(SUM(" + singleReportInput.Field + "),0) AS " + singleReportInput.Field + " FROM " + tableName); break; } sql.Append(singleReportInput.Condition.ToSqlWhere(singleReportInput.Style)); var returnValue = dbContext.ExecuteScalar(sql.ToString()); if (returnValue == null) { returnValue = 0; } return(returnValue.ToDecimal()); }