/// <summary>
/// 车商核销记录列表合计
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public CodeListSummaryModel GetCodeRecordTotal(CodeQueryModel query)
{
//查询条件
var sqlWhere = new StringBuilder("a.isused=1");
if (!string.IsNullOrWhiteSpace(query.Shopid))
{
sqlWhere.Append($" and b.shopid='{query.Shopid}'");
}
if (query.CardType != null)
{
sqlWhere.Append($" and b.cardtype={query.CardType}");
}
if (!string.IsNullOrWhiteSpace(query.Code))
{
sqlWhere.Append($" and a.code like '%{query.Code}%'");
}
if (query.StartTime != null)
{
sqlWhere.Append($" and a.usedtime>='{query.StartTime?.ToString("yyyy-MM-dd 00:00:00")}'");
}
if (query.EndTime != null)
{
sqlWhere.Append($" and a.usedtime<='{query.EndTime?.ToString("yyyy-MM-dd 23:59:59")}'");
}
var sql =
@"select count(1) as TotalNumber,sum(b.costprice) as TotalPrice from coupon_code as a inner join coupon_card as b on a.cardid=b.innerid where " +
sqlWhere;
var summaryModel = Helper.Query<CodeListSummaryModel>(sql).FirstOrDefault();
return summaryModel;
}
/// <summary>
/// 获取礼券实例
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public BasePageList<CouponCodeListModel> GetCouponCode(CodeQueryModel query)
{
const string spName = "sp_common_pager";
const string tableName = @"coupon_code as a inner join cust_info as b on a.custid=b.innerid";
const string fields = "a.*, b.custname";
var orderField = string.IsNullOrWhiteSpace(query.Order) ? "a.gettime desc" : query.Order;
//查询条件
var sqlWhere = new StringBuilder(" 1=1 ");
//if (!string.IsNullOrWhiteSpace(query.Shopid))
//{
// sqlWhere.Append($" and b.shopid='{query.Shopid}'");
//}
if (!string.IsNullOrWhiteSpace(query.CardId))
{
sqlWhere.Append($" and a.cardid = '{query.CardId}'");
}
//if (query.StartTime != null)
//{
// sqlWhere.Append($" and a.usedtime>={query.StartTime}");
//}
//if (query.EndTime != null)
//{
// sqlWhere.Append($" and a.usedtime<={query.EndTime}");
//}
var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
var list = Helper.ExecutePaging<CouponCodeListModel>(model, query.Echo);
return list;
}
/// <summary>
/// 车商核销记录列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public BasePageList<CodeViewListModel> GetCodeRecord(CodeQueryModel query)
{
const string spName = "sp_common_pager";
const string tableName = @"coupon_code as a inner join coupon_card as b on a.cardid=b.innerid";
const string fields = "a.innerid, a.code, a.usedtime, b.title, b.amount, b.buyprice, b.costprice";
var orderField = string.IsNullOrWhiteSpace(query.Order) ? "a.usedtime desc" : query.Order;
//查询条件
var sqlWhere = new StringBuilder("a.isused=1");
if (!string.IsNullOrWhiteSpace(query.Shopid))
{
sqlWhere.Append($" and b.shopid='{query.Shopid}'");
}
if (query.CardType != null)
{
sqlWhere.Append($" and b.cardtype={query.CardType}");
}
if (!string.IsNullOrWhiteSpace(query.Code))
{
sqlWhere.Append($" and a.code like '%{query.Code}%'");
}
if (query.StartTime != null)
{
sqlWhere.Append($" and a.usedtime>='{query.StartTime?.ToString("yyyy-MM-dd 00:00:00")}'");
}
if (query.EndTime != null)
{
sqlWhere.Append($" and a.usedtime<='{query.EndTime?.ToString("yyyy-MM-dd 23:59:59")}'");
}
var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
var list = Helper.ExecutePaging<CodeViewListModel>(model, query.Echo);
return list;
}
