/// <summary>
/// 优惠券查询
/// </summary>
/// <param name="queryFilter"></param>
/// <returns></returns>
public static QueryResult QueryCouponList(CouponQueryFilter queryFilter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCouponList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter, string.IsNullOrEmpty(queryFilter.SortFields) ? "c.SysNo ASC" : queryFilter.SortFields))
{
//Set SQL WHERE Condition:
if (queryFilter.SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryFilter.SysNo);
}
if (queryFilter.MerchantSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.MerchantSysNo", DbType.Int32, "@MerchantSysNo", QueryConditionOperatorType.Equal, queryFilter.MerchantSysNo);
}
if (!string.IsNullOrWhiteSpace(queryFilter.CouponName))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.CouponName", DbType.String, "@CouponName", QueryConditionOperatorType.Like, queryFilter.CouponName);
}
if (!string.IsNullOrWhiteSpace(queryFilter.CouponCode))
{
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist,
"Select Top 1 1 FROM [OverseaECommerceManagement].[dbo].[CouponCode] as cc with(nolock) WHERE cc.CouponSysNo=c.SysNo AND cc.CouponCode=@CouponCode");
command.AddInputParameter("@CouponCode", DbType.String, queryFilter.CouponCode);
}
if (queryFilter.Status.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.Status", DbType.String, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status);
}
if (queryFilter.BeginDateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.BeginDate", DbType.DateTime, "@BeginDateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.BeginDateFrom.Value.Date);
}
if (queryFilter.BeginDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.BeginDate", DbType.DateTime, "@BeginDateTo", QueryConditionOperatorType.LessThan, queryFilter.BeginDateTo.Value.Date.AddDays(1));
}
if (queryFilter.EndDateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.EndDate", DbType.DateTime, "@EndDateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.EndDateFrom.Value.Date);
}
if (queryFilter.EndDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.EndDate", DbType.DateTime, "@EndDateTo", QueryConditionOperatorType.LessThan, queryFilter.EndDateTo.Value.Date.AddDays(1));
}
command.CommandText = sqlBuilder.BuildQuerySql();
DataTable resultList = command.ExecuteDataTable(3, typeof(CouponStatus));
int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(new QueryResult(resultList, queryFilter, totalCount));// { PageInfo = new PageInfo() { PageIndex = queryFilter.PageIndex, PageSize = queryFilter.PageSize, TotalCount = totalCount, SortBy = queryFilter.SortFields }, ResultList = resultList };
}
}
public ActionResult AjaxCouponQuery()
{
CouponQueryFilter filter = BuildQueryFilterEntity <CouponQueryFilter>();
filter.MerchantSysNo = UserAuthHelper.GetCurrentUser().SellerSysNo;
var result = CouponService.QueryCouponList(filter);
return(AjaxGridJson(result));
}
public static QueryResult QueryCouponList(CouponQueryFilter queryFilter)
{
return(CouponDA.QueryCouponList(queryFilter));
}
