private static void SetCondition(DataCommand command, QF_Subject filter, string type)
{
if (!string.IsNullOrEmpty(filter.Keyword))
{
command.ReplaceAndSetSQLTag("TagFilterKeyword", filter.Keyword);
command.ReplaceSQLTag("TagFilterKeyword", string.Empty);
}
else
{
command.ReplaceSQLTag("TagFilterKeyword", string.Empty);
}
command.QuerySetCondition("ts.create_time", ConditionOperation.MoreThanEqual, DbType.DateTime, filter.StartTime);
if (filter.EndTime.HasValue)
{
command.QuerySetCondition("ts.create_time", ConditionOperation.LessThan, DbType.DateTime, filter.EndTime.Value.AddDays(1));
}
command.QuerySetCondition("tc.id", ConditionOperation.Equal, DbType.Int32, filter.CatId);
command.QuerySetCondition("ts.department_id", ConditionOperation.Equal, DbType.Int32, filter.DepartmentId);
command.QuerySetCondition("ts.is_delete", ConditionOperation.Equal, DbType.Int32, DeletedEnums.Actived);
if (type == "Search")
{
command.QuerySetCondition("d.name", ConditionOperation.Like, DbType.String, filter.VendorName);
}
else
{
command.QuerySetCondition("vn.name", ConditionOperation.Like, DbType.String, filter.VendorName);
}
command.CommandText = command.CommandText.Replace("#STRWHERE#", command.QueryConditionString);
}
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 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));
}
/// <summary>
/// 批量插入数据
/// </summary>
/// <param name="list">数据列表</param>
public static void BatchInsert(List <ArticleDepartmentEntity> list)
{
var temp = list.First();
var cmd = new DataCommand("ArticleDepartment.BatchInsert");
cmd.SetParameter("@ArticleId", DbType.Int32, temp.ArticleId);
string tag = cmd.GetSQLTag("TagBatchInsert");
List <string> tagList = new List <string>();
foreach (var item in list)
{
tagList.Add(string.Format(tag, item.ArticleId, item.DepartmentId));
}
cmd.ReplaceSQLTag("TagBatchInsert", string.Empty);
cmd.CommandText = cmd.CommandText.Replace("#LIST#", string.Join(",", tagList));
cmd.ExecuteNonQuery();
}