public async Task <MemoryStream> ExportToExcel(string where)
{
string sql = $@"select m.FullName 姓名,stuff(m.WechatName,2,1,'*') 微信名,
case m.Sex when 0 then '男' else '女' end 性别, stuff(m.CardID,8,4,'****') 卡号,stuff(m.Mobile,8,4,'****') 手机号,m.Balance 余额
,m.Integral 积分,convert(nvarchar(100),m.RegTime,20) 注册时间,l.Title 等级,b.BusinessName 所属商户,
c.Name 所属门店 from dbo.TMember m left join dbo.TMemberLevel l on m.levelId=l.id left join dbo.TBusiness b on
m.businessId=b.Id left join dbo.TChainStore c on m.chainstoreId=c.id where 1=1";
sql += where.Replace("*", "m");
// DataTable dt = _sqlExecuter.ExecuteDataSet(sql).Tables[0];
return(await _comServices.DataTableToExcel(sql));
}
public async Task <MemoryStream> ExportToExcel(string where)
{
string sql = $@"select convert(nvarchar(100),o.CreationTime,120) 下单时间,cast(o.Point as float) 支付积分,
o.BillNumber 订单号,stuff((select g.Name +' ￥'+convert(nvarchar,cast(i.Point as float)) +'×'+convert(nvarchar, i.Count) +'；'
from dbo.TGiftOrderItem i
left join dbo.TGift g on i.GiftId=g.Id
where i.GiftOrderId =o.Id
for xml path('')),1,1,'') 礼品信息,
o.ReceiveTime 领取时间,o.Memo 备注,c.Name 门店,stuff(m.CardID,8,4,'****') 会员卡号,m.WeChatName 微信名 from dbo.TGiftOrder o left join dbo.TChainStore c on o.ChainStoreId=c.Id
left join dbo.TMember m on o.MemberId=m.Id where 1=1";
sql += where.Replace("*", "c");
return(await _comService.DataTableToExcel(sql));
}
public async Task <MemoryStream> ExportToExcel(string where)
{
string sql = $@"select convert(nvarchar(100),o.CreationTime,120) 下单时间,
case o.status when 0 then '已付款'
when -1 then '未付款' when 2 then '已撤销' end 状态,
cast(o.TotalPay as float) 总计支付,
cast(o.WeChatPay as float) 微信支付,
cast(o.AliPay as float) 支付宝支付,
cast(o.BalancePay as float) 余额支付,
cast(o.IntegralPay as float) 支付积分,
cast(o.CouponPay as float) 优惠券积分,
o.OrderNumber 订单号,stuff((select f.Title +' ￥'+convert(nvarchar,cast(i.Price as float)) +'×'+convert(nvarchar, i.Count) +'；'
from dbo.TFlashSaleOrderItem i
left join dbo.TFlashSale f on i.FlashSaleId=f.Id
where i.OrderId =o.Id
for xml path('')),1,1,'') 商品信息,c.Name 门店,stuff(m.CardID,8,4,'****') 会员卡号,
m.WeChatName 微信名 from dbo.TFlashSaleOrder o left join dbo.TChainStore c on o.ChainStoreId=c.Id
left join dbo.TMember m on o.MemberId=m.Id where 1=1";
sql += where.Replace("*", "o");
return(await _comService.DataTableToExcel(sql));
}