public JsonResult QueryProductVendorStatistics()
{
QF_Report filter = BuildQueryFilterEntity <QF_Report>();
var list = ReportBigService.ProductVendorStatisticsList(filter);
return(AjaxGridJson(list));
}
public JsonResult QueryExternalDepartmentStatistics()
{
QF_Report filter = BuildQueryFilterEntity <QF_Report>();
var list = ReportBigService.ExternalDepartmentStatisticsList(filter);
return(AjaxGridJson(list));
}
public static List <ReportChartVO> ActivityStatisticsChart(QF_Report filter)
{
var cmd = new DataCommand("Report.ActivityStatisticsChart");
string formatKey = GetFormatKey(filter.DateTimeType.Value);
cmd.SetParameter("@FormatDate", DbType.String, formatKey);
cmd.QuerySetCondition("tud.create_time", ConditionOperation.MoreThanEqual, DbType.DateTime, filter.StartTime);
cmd.QuerySetCondition("tud.create_time", ConditionOperation.LessThan, DbType.DateTime, handleEndTime(filter.EndTime));
if (filter.DepartmentId.HasValue)
{
cmd.ReplaceAndSetSQLTag("TagFilterDepartment");
cmd.QuerySetCondition("tud2.department_id", ConditionOperation.Equal, DbType.Int32, filter.DepartmentId);
}
else
{
cmd.ReplaceSQLTag("TagFilterDepartment", string.Empty);
}
if (filter.DateTimeType.Value == DateTimeTypeEnum.Week)
{
cmd.CommandText = cmd.CommandText.Replace("#filterDistinct#", string.Empty);
}
else
{
cmd.CommandText = cmd.CommandText.Replace("#filterDistinct#", "distinct ");
}
cmd.CommandText = cmd.CommandText.Replace("#STRWHERE#", cmd.QueryConditionString);
return(cmd.ExecuteEntityList <ReportChartVO>());
}
public JsonResult QueryArticleInterviewStatistics(QF_Report filter)
{
var chartList = ReportBigService.ArticleInterviewStatisticsChart(filter);
return(Json(new AjaxResult()
{
Success = true,
Data = chartList
}));
}
public static List <ReportChartVO> SubjectStatisticsChart(QF_Report filter)
{
var cmd = new DataCommand("Report.SubjectStatisticsChart");
cmd.QuerySetCondition("ts.create_time", ConditionOperation.MoreThanEqual, DbType.DateTime, filter.StartTime);
cmd.QuerySetCondition("ts.create_time", ConditionOperation.LessThan, DbType.DateTime, handleEndTime(filter.EndTime));
cmd.QuerySetCondition("ts.department_id", ConditionOperation.Equal, DbType.Int32, filter.DepartmentId);
cmd.QuerySetCondition("ts.is_delete", ConditionOperation.Equal, DbType.Int32, DeletedEnums.Actived);
cmd.CommandText = cmd.CommandText.Replace("#STRWHERE#", cmd.QueryConditionString);
return(cmd.ExecuteEntityList <ReportChartVO>());
}
public List <ActivityStatisticsVO> ActivityStatisticsList(QF_Report filter)
{
if (!filter.DateTimeType.HasValue)
{
throw new BusinessException("请选择统计模式");
}
if (!filter.StartTime.HasValue || !filter.EndTime.HasValue)
{
throw new BusinessException("请选择开始时间/结束时间");
}
return(ReportBigDA.ActivityStatisticsList(filter));
}
public static List <ReportChartVO> ComplaintStatisticsChart(QF_Report filter)
{
var cmd = new DataCommand("Report.ComplaintStatisticsChart");
string formatKey = GetFormatKey(filter.DateTimeType.Value);
cmd.SetParameter("@FormatDate", DbType.String, formatKey);
cmd.QuerySetCondition("create_time", ConditionOperation.MoreThanEqual, DbType.DateTime, filter.StartTime);
cmd.QuerySetCondition("create_time", ConditionOperation.LessThan, DbType.DateTime, handleEndTime(filter.EndTime));
cmd.QuerySetCondition("department_id", ConditionOperation.Equal, DbType.Int32, filter.DepartmentId);
cmd.QuerySetCondition("is_delete", ConditionOperation.Equal, DbType.Int32, DeletedEnums.Actived);
cmd.CommandText = cmd.CommandText.Replace("#STRWHERE#", cmd.QueryConditionString);
return(cmd.ExecuteEntityList <ReportChartVO>());
}
private List <ReportChartVO> BindDateTimeFullKey(QF_Report filter, List <ReportChartVO> list)
{
List <DateTime> dateTimes = new List <DateTime>();
switch (filter.DateTimeType.Value)
{
case DateTimeTypeEnum.Month:
dateTimes = this.GetFullMonthKeyList();
break;
case DateTimeTypeEnum.Year:
dateTimes = this.GetFullYearKeyList();
break;
}
dateTimes.ForEach(p =>
{
var hasExists = list.Exists(item => DateTime.Parse(item.Key) == p);
if (!hasExists)
{
list.Add(new ReportChartVO()
{
Key = p.ToString("yyyy-MM-dd"),
Value = 0
});
}
});
list = list.OrderBy(p => DateTime.Parse(p.Key)).ToList();
list.ForEach(item =>
{
switch (filter.DateTimeType.Value)
{
case DateTimeTypeEnum.Week:
item.Key = DateTime.Parse(item.Key).ToString("MM-dd");
break;
case DateTimeTypeEnum.Month:
item.Key = DateTime.Parse(item.Key).ToString("MM月");
break;
case DateTimeTypeEnum.Year:
item.Key = DateTime.Parse(item.Key).ToString("yyyy年");
break;
default:
item.Key = DateTime.Parse(item.Key).ToString("MM-dd");
break;
}
});
return(list);
}
public List <ReportChartVO> ArticleInterviewStatisticsChart(QF_Report filter)
{
if (!filter.DateTimeType.HasValue)
{
throw new BusinessException("请选择统计模式");
}
if (!filter.StartTime.HasValue || !filter.EndTime.HasValue)
{
throw new BusinessException("请选择开始时间/结束时间");
}
List <ReportChartVO> reportChartVOs = ReportBigDA.ArticleInterviewStatisticsChart(filter);
return(this.BindDateTimeFullKey(filter, reportChartVOs));
}
public JsonResult QueryComplaintStatistics(QF_Report filter)
{
var list = ReportBigService.ComplaintStatisticsList(filter);
var chartList = ReportBigService.ComplaintStatisticsChart(filter);
return(Json(new AjaxResult()
{
Success = true,
Data = new
{
chartList,
list
}
}));
}
public static List <ActivityStatisticsVO> ActivityStatisticsList(QF_Report filter)
{
var cmd = new DataCommand("Report.ActivityStatisticsList");
cmd.SetParameter("@StartTime", DbType.DateTime, filter.StartTime);
cmd.SetParameter("@EndTime", DbType.DateTime, handleEndTime(filter.EndTime));
cmd.QuerySetCondition("tud2.create_time", ConditionOperation.MoreThanEqual, DbType.DateTime, filter.StartTime);
cmd.QuerySetCondition("tud2.create_time", ConditionOperation.LessThan, DbType.DateTime, handleEndTime(filter.EndTime));
cmd.QuerySetCondition("td.id", ConditionOperation.Equal, DbType.Int32, filter.DepartmentId);
if (filter.DateTimeType != null && filter.DateTimeType.Value == DateTimeTypeEnum.Week)
{
cmd.CommandText = cmd.CommandText.Replace("#filterDistinct#", string.Empty);
}
else
{
cmd.CommandText = cmd.CommandText.Replace("#filterDistinct#", "distinct ");
}
cmd.CommandText = cmd.CommandText.Replace("#STRWHERE#", cmd.QueryConditionString);
return(cmd.ExecuteEntityList <ActivityStatisticsVO>());
}
public QueryResult <ExternalDepartmentStatistics> ExternalDepartmentStatisticsList(QF_Report filter)
{
return(ReportBigDA.ExternalDepartmentStatisticsList(filter));
}
public List <ReportChartVO> SubjectStatisticsChart(QF_Report filter)
{
return(ReportBigDA.SubjectStatisticsChart(filter));
}
public QueryResult <ProductVendorStatisticsVO> ProductVendorStatisticsList(QF_Report filter)
{
return(ReportBigDA.ProductVendorStatisticsList(filter));
}
public static QueryResult <ExternalDepartmentStatistics> ExternalDepartmentStatisticsList(QF_Report filter)
{
var externalTagValue = int.Parse(AppSettingManager.GetSetting("ExternalDepartmentTag", "ExternalTag"));
var cmd = new DataCommand("Report.ExternalDepartmentStatisticsList");
cmd.SetParameter("@TagValue", DbType.Int32, externalTagValue);
cmd.QuerySetCondition("a.create_time", ConditionOperation.MoreThanEqual, DbType.DateTime, filter.StartTime);
cmd.QuerySetCondition("a.create_time", ConditionOperation.LessThan, DbType.DateTime, handleEndTime(handleEndTime(filter.EndTime)));
cmd.QuerySetCondition("c.department_id", ConditionOperation.Equal, DbType.Int32, filter.DepartmentId);
cmd.QuerySetCondition("c.is_delete", ConditionOperation.Equal, DbType.Int32, DeletedEnums.Actived);
cmd.CommandText = cmd.CommandText.Replace("#STRWHERE#", cmd.QueryConditionString);
return(cmd.Query <ExternalDepartmentStatistics>(filter, "a.create_time DESC", null, true));
}
public static QueryResult <ProductVendorStatisticsVO> ProductVendorStatisticsList(QF_Report filter)
{
var cmd = new DataCommand("Report.ProductVendorStatisticsList");
if (filter.StatisticsObject == StatisticsObject.Product)
{
cmd.ReplaceAndSetSQLTag("TagProductFragment");
cmd.ReplaceSQLTag("TagVendorFragment", string.Empty);
}
else
{
cmd.ReplaceAndSetSQLTag("TagVendorFragment");
cmd.ReplaceSQLTag("TagProductFragment", string.Empty);
}
cmd.QuerySetCondition("ts.create_time", ConditionOperation.MoreThanEqual, DbType.DateTime, filter.StartTime);
cmd.QuerySetCondition("ts.create_time", ConditionOperation.LessThan, DbType.DateTime, handleEndTime(handleEndTime(filter.EndTime)));
cmd.QuerySetCondition("ts.department_id", ConditionOperation.Equal, DbType.Int32, filter.DepartmentId);
cmd.QuerySetCondition("ts.is_delete", ConditionOperation.Equal, DbType.Int32, DeletedEnums.Actived);
cmd.CommandText = cmd.CommandText.Replace("#STRWHERE#", cmd.QueryConditionString);
return(cmd.Query <ProductVendorStatisticsVO>(filter, "count(1) DESC", null, true));
}