/// <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());
}
