/// <summary>
/// 获取获取礼券列表
/// </summary>
/// <param name="query">查询条件</param>
/// <returns></returns>
public BasePageList<CouponViewModel> GetCouponPageList(CouponQueryModel query)
{
/*
礼券类型多选时的SQL（后面做）
select
a.innerid, a.title, a.titlesub, a.amount, a.logourl, a.vtype, a.vstart, a.vend, a.value1, a.value2, a.maxcount, a.count,a.codetype, a.createdtime,a.modifiedtime, a.isenabled,
(select group_concat(bc.codename) from base_code as bc inner join coupon_type_relation as tr on bc.codevalue=tr.cardtype and bc.typekey='coupon_type' where tr.cardid=a.innerid) as cardtypename,
(select group_concat(bc.remark) from base_code as bc inner join coupon_type_relation as tr on bc.codevalue=tr.cardtype and bc.typekey='coupon_type' where tr.cardid=a.innerid) as cardtyperemark
from
coupon_card as a
where 1=1
and innerid in (select cardid from coupon_type_relation where cardtype in(1,2))
order by a.createdtime desc;
*/
const string spName = "sp_common_pager";
const string tableName = @"coupon_card as a left join base_code as bc on a.cardtype=bc.codevalue and bc.typekey='coupon_type'";
const string fields =
"a.innerid, a.title, a.titlesub, a.amount, a.logourl, a.vtype, a.vstart, a.vend, a.value1, a.value2, a.maxcount, a.count,a.codetype, a.createdtime,a.modifiedtime, a.isenabled,bc.codename as cardtypename,bc.remark as cardtyperemark";
var orderField = string.IsNullOrWhiteSpace(query.Order) ? "a.createdtime desc" : query.Order;
//查询条件
var sqlWhere = new StringBuilder("1=1");
if (query.IsEnabled.HasValue)
{
sqlWhere.Append($" and a.isenabled={query.IsEnabled}");
}
if (!string.IsNullOrWhiteSpace(query.Shopid))
{
sqlWhere.Append($" and a.shopid='{query.Shopid}'");
}
if (query.Cardtype != null)
{
sqlWhere.Append($" and a.cardtype={query.Cardtype}");
}
if (!string.IsNullOrWhiteSpace(query.Title))
{
sqlWhere.Append($" and a.title like '%{query.Title}%'");
}
if (!string.IsNullOrWhiteSpace(query.Titlesub))
{
sqlWhere.Append($" and a.titlesub like '%{query.Titlesub}%'");
}
if (query.MinAmount > 0)
{
sqlWhere.Append($" and a.amount>={query.MinAmount}");
}
if (query.MaxAmount > 0)
{
sqlWhere.Append($" and a.amount<={query.MaxAmount}");
}
var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
var list = Helper.ExecutePaging<CouponViewModel>(model, query.Echo);
return list;
}
