/// <summary>
/// 根据字段求和 统计报表
/// </summary>
/// <returns></returns>
public List <AutoReport> GetSumReport(SumReportInput sumReportInput)
{
var dbContext = Ioc.Resolve <IAlaboUserRepository>().RepositoryContext;
var queryResult = GetSumReportQuerySql(sumReportInput);
var strSql = queryResult.Item1;
var chartCols = queryResult.Item2;
var chartRows = new List <object>();
using (var dr = dbContext.ExecuteDataReader(strSql)) {
while (dr.Read())
{
var dic = new Dictionary <string, string>();
chartCols.ForEach(p => { dic.Add(p, dr[p].ToString()); });
chartRows.Add(dic);
}
}
var line = (ReportChartType)Enum.Parse(typeof(ReportChartType), "0");
var result = new List <AutoReport>
{
new AutoReport
{
Name = "数据走势图", Icon = Flaticon.Alarm.GetIcon(),
AutoReportChart = new AutoReportChart {
Type = line, Columns = chartCols, Rows = chartRows
}
}
};
return(result);
}
public ApiResult <PagedList <SumReportTable> > GetSumTable([FromBody] SumReportInput reportInput)
{
if (!this.IsFormValid())
{
return(ApiResult.Failure <PagedList <SumReportTable> >(this.FormInvalidReason()));
}
var result = Resolve <IAutoReportService>().GetCountSumTable(reportInput);
return(ToResult(result));
}
/// <summary>
/// 获取Sum报表Sql语句
/// </summary>
/// <param name="sumReportInput"></param>
/// <returns></returns>
private Tuple <string, List <string> > GetSumReportQuerySql(SumReportInput sumReportInput)
{
var tempSqlField = string.Empty;
var chartCols = new List <string>();
chartCols.Add("日期");
//实体字段 过滤
foreach (var field in sumReportInput.Fields)
{
var tempDisplayName = sumReportInput.EntityType.GetFiledDisplayName(field);
tempSqlField = tempSqlField + " ,sum(" + field + ") as " + tempDisplayName;
chartCols.Add(tempDisplayName); //根据传入的查询字段个数
}
////查询条件
//if (!string.IsNullOrEmpty(sqlWhere))
//{
// tempSqlWhere = tempSqlWhere + " and " + sqlWhere;
//}
//日期验证
var tempSqlWhere = string.Empty;
if (sumReportInput.Condition.StartTime.ToString().IndexOf("0001") == -1 ||
sumReportInput.Condition.EndTime.ToString().IndexOf("0001") == -1)
{
tempSqlWhere = " and CreateTime between CONVERT(VARCHAR(100), '" + sumReportInput.Condition.StartTime +
"', 23) and CONVERT(VARCHAR(100), '" + sumReportInput.Condition.EndTime + "', 23)";
}
sumReportInput.Condition.EntityType = sumReportInput.EntityType;
var tableName = sumReportInput.Condition.GetTableName();
var sqlBaseExec = @" select CONVERT(VARCHAR(100), CreateTime, 23) as 日期
" + tempSqlField + @"
from " + tableName + @"
where 1=1 " + tempSqlWhere +
@"group by CONVERT(VARCHAR(100), CreateTime, 23)";
if (FilterSqlScript(sqlBaseExec))
{
throw new ArgumentNullException("sql语句有异常！");
}
return(Tuple.Create(sqlBaseExec, chartCols));
}
/// <summary>
/// 按天统计实体增加数据,输出表格形式
/// </summary>
/// <param name="reportInput"></param>
/// <param name="EnumName"></param>
/// <returns></returns>
public List <SumReportTable> GetSumReportTable(SumReportInput reportInput, string EnumName)
{
var dbContext = Ioc.Resolve <IAlaboUserRepository>().RepositoryContext;
var queryResult = GetSumTableQuerySql(reportInput, EnumName);
var strSql = queryResult.Item1;
var chartCols = queryResult.Item2;
var chartRows = new List <object>();
using (var dr = dbContext.ExecuteDataReader(strSql)) {
while (dr.Read())
{
var dic = new Dictionary <string, string>();
foreach (var item in chartCols)
{
var value = item.name;
var key = item.type;
if (!dic.ContainsKey(key))
{
dic.Add(key, dr[key].ToString());
}
}
chartRows.Add(dic);
}
}
var result = new List <SumReportTable>
{
new SumReportTable
{
Name = "报表数据", SumReportTableItem = new SumReportTableItems {
Columns = chartCols, Rows = chartRows
}
}
};
return(result);
}
/// <summary>
/// 根据字段求和 统计报表
/// </summary>
/// <param name="reportInput"></param>
/// <returns></returns>
public Tuple <ServiceResult, List <AutoReport> > GetSumReport(SumReportInput reportInput)
{
var returnList = new List <AutoReport>();
#region 安全验证
if (reportInput == null)
{
if (string.IsNullOrEmpty(reportInput.EntityType))
{
return(Tuple.Create(ServiceResult.FailedMessage("实体类型不能为空"), returnList));
}
return(Tuple.Create(ServiceResult.FailedMessage("实体类型不能为空"), returnList));
}
Type typeFind = null;
object instanceFind = null;
var checkType = Resolve <IUIBaseService>().CheckType(reportInput.EntityType, ref typeFind, ref instanceFind);
if (!checkType.Succeeded)
{
return(Tuple.Create(ServiceResult.FailedMessage("实体类型不存在"), returnList));
}
if (!(instanceFind is IEntity)) // 非实体类型不能完成数据统计
{
return(Tuple.Create(ServiceResult.FailedMessage("非实体类型不能进行数据统计"), returnList));
}
if (reportInput.Fields == null)
{
return(Tuple.Create(ServiceResult.FailedMessage("统计的字段不能为空"), returnList));
}
//验证传入的统计字段和表是否匹配
var fieldsList = reportInput.Fields;
#endregion 安全验证
var table = Resolve <ITableService>().GetSingle(r => r.Key == typeFind.Name);
if (table == null)
{
return(Tuple.Create(ServiceResult.FailedMessage("查询的表不存在"), returnList));
}
if (table.TableType == TableType.Mongodb)
{
}
if (table.TableType == TableType.SqlServer)
{
var tableName = table.TableName;
if (tableName == null)
{
return(Tuple.Create(ServiceResult.FailedMessage("查询在表不存在"), returnList));
}
returnList = _autoReportRepository.GetSumReport(reportInput);
}
return(Tuple.Create(ServiceResult.Success, returnList));
}
/// <summary>
/// 根据字段求和 生成报表表格
/// </summary>
/// <param name="reportInput"></param>
/// <returns></returns>
public Tuple <ServiceResult, PagedList <SumReportTable> > GetCountSumTable(SumReportInput reportInput)
{
var resultPage = new PagedList <SumReportTable>();
#region 安全验证
if (reportInput == null)
{
if (string.IsNullOrEmpty(reportInput.EntityType))
{
return(Tuple.Create(ServiceResult.FailedMessage("实体类型不能为空"), resultPage));
}
return(Tuple.Create(ServiceResult.FailedMessage("实体类型不能为空"), resultPage));
}
Type typeFind = null;
object instanceFind = null;
var checkType = Resolve <IUIBaseService>().CheckType(reportInput.EntityType, ref typeFind, ref instanceFind);
if (!checkType.Succeeded)
{
return(Tuple.Create(ServiceResult.FailedMessage("所输入实体不存在"), resultPage));
}
if (!(instanceFind is IEntity)) // 非实体类型不能完成数据统计
{
return(Tuple.Create(ServiceResult.FailedMessage("非实体类型不能进行数据统计"), resultPage));
}
if (reportInput.Fields.Count <= 0)
{
return(Tuple.Create(ServiceResult.FailedMessage("统计的字段不能为空"), resultPage));
}
#endregion 安全验证
var EnumName = string.Empty;
// 特殊字段处理
if (reportInput.Field.IsNotNullOrEmpty())
{
var property = instanceFind.GetType().GetProperty(reportInput.Field);
var filedType = property.GetType();
var filed = reportInput.Field.GetPropertyValue(instanceFind);
if (filed.ToString() != "0")
{
EnumName = property.PropertyType.Name;
}
}
var table = Resolve <ITableService>().GetSingle(r => r.Key == typeFind.Name);
if (table == null)
{
return(Tuple.Create(ServiceResult.FailedMessage("查询的表不存在"), resultPage));
}
if (table.TableType == TableType.Mongodb)
{
}
if (table.TableType == TableType.SqlServer)
{
var tableName = table.TableName;
if (string.IsNullOrEmpty(tableName))
{
return(Tuple.Create(ServiceResult.FailedMessage("查询的表不存在"), resultPage));
}
var resultList = _autoReportRepository.GetSumReportTable(reportInput, EnumName);
resultPage = PagedList <SumReportTable> .Create(resultList, resultList.Count(), reportInput.PageIndex,
reportInput.PageSize);
return(Tuple.Create(ServiceResult.Success, resultPage));
}
return(Tuple.Create(ServiceResult.FailedMessage("查询失败"), resultPage));
}
/// <summary>
/// sum报表表格Sql语句
/// </summary>
/// <param name="reportInput">表字段</param>
/// <returns></returns>
private Tuple <string, List <SumColumns> > GetSumTableQuerySql(SumReportInput reportInput, string EnumName)
{
//还得扩展 SpecialField
var tempSqlWhere = string.Empty;
var tempSqlField = string.Empty;
var tempSqlStatusCount = string.Empty;
var colList = new List <SumColumns>();
var col = new SumColumns();
col.name = "日期";
col.type = "Date";
colList.Add(col);
if (!string.IsNullOrEmpty(EnumName))
{
var typeEnum = EnumName.GetTypeByName();
if (typeEnum != null)
{
foreach (Enum item in Enum.GetValues(typeEnum))
{
var itemName = item.GetDisplayName();
itemName = FilterSpecial(itemName);
var itemValue = item.ToString();
var itemKey = Convert.ToInt16(item);
col = new SumColumns();
//实体字段
foreach (var field in reportInput.Fields)
{
var tempDisplayName = reportInput.EntityType.GetFiledDisplayName(field);
//统计 Sum状态数量
var TotalAmountName = tempDisplayName + itemName;
var TotalAmountValue = field + itemValue;
//待完成 已完成 等状态
tempSqlStatusCount = tempSqlStatusCount + @" , SUM(CASE WHEN " + reportInput.Field + "=" +
itemKey + " THEN " + field + " ELSE 0 END) AS " + TotalAmountValue;
//var tempTableHeadName= tempDisplayName+"["+ itemName + "]";
col.name = TotalAmountName;
col.type = TotalAmountValue;
colList.Add(col);
}
}
}
}
if (reportInput.Fields.Count > 0) //实体字段 过滤
{
foreach (var field in reportInput.Fields)
{
var tempDisplayName = reportInput.EntityType.GetFiledDisplayName(field);
tempSqlField = tempSqlField + " ,sum(" + field + ") as " + field;
col = new SumColumns();
col.name = tempDisplayName;
col.type = field;
colList.Add(col);
}
}
////查询条件
//if (!string.IsNullOrEmpty(sqlWhere))
//{
// tempSqlWhere = tempSqlWhere + " and " + sqlWhere;
//}
//日期验证
if (reportInput.Condition.StartTime.ToString().IndexOf("0001") == -1 ||
reportInput.Condition.EndTime.ToString().IndexOf("0001") == -1)
{
tempSqlWhere = tempSqlWhere + " and CreateTime between CONVERT(VARCHAR(100), '" +
reportInput.Condition.StartTime + "', 23) and CONVERT(VARCHAR(100), '" +
reportInput.Condition.EndTime + "', 23)";
}
reportInput.Condition.EntityType = reportInput.EntityType;
var tableName = reportInput.Condition.GetTableName();
var sqlBaseExec = @" select CONVERT(VARCHAR(100), CreateTime, 23) as Date
" + tempSqlField + tempSqlStatusCount + @"
from " + tableName + @"
where 1=1 " + tempSqlWhere + @"
group by CONVERT(VARCHAR(100), CreateTime, 23)";
if (FilterSqlScript(sqlBaseExec))
{
throw new ArgumentNullException("sql语句有异常！");
}
return(Tuple.Create(sqlBaseExec, colList));
}
