protected override void OnExecute(DateTime date)
{
var start = new DateTime(date.Year, date.Month, 1);
var end = new DateTime(date.AddMonths(1).Year, date.AddMonths(1).Month, 1);
// 1, pay, 4 doneprepay, 3, cancel, 6, canceldoneprepay
var sql =
string.Format(
@"select 0 ShopMonthReportId, convert(varchar(50), DatePart(yyyy, submittime)) + '-' + convert(varchar(50), DatePart(mm, submittime)) as month, accountId,
accountName, sum(amount) dealamount from deallogs
where (state <> {0}) and submittime >= @start and submittime < @end
group by accountName, convert(varchar(50), DatePart(yyyy, submittime)) + '-' + convert(varchar(50), DatePart(mm, submittime)), accountId",
DealLogStates.Normal_);
var parameters = new
{
start = start,
end = end,
};
var reports = DatabaseInstance.Query <AccountMonth>(sql, parameters).ToList();
reports.ForEach(x => x.Month = start.Date.ToString("yyyy-MM"));
DatabaseInstance.ExecuteNonQuery("delete ReportAccountMonth where month = '" + start.Date.ToString("yyyy-MM") + "'", null);
foreach (var report in reports)
{
DatabaseInstance.Insert(report, "ReportAccountMonth");
}
}
protected override string[] GetColumns()
{
var names = base.GetColumns();
var accountTypes = DatabaseInstance.Query <AccountType>("select * from accountTypes where state = 1", null).ToList();
return(names.Union(accountTypes.Select(x => x.DisplayName)).ToArray());
}
private void LoadParams()
{
DatabaseInstance context = _unityContainer.Resolve <DatabaseInstance>();
string sql = @"select * from sites";
_site = context.Query <Site>(sql, null).FirstOrDefault();
}
protected override string GetSql(string sqlName)
{
var accountTypes = DatabaseInstance.Query <AccountType>("select * from accountTypes where state = 1", null).ToList();
if (accountTypes.Count == 0)
{
throw new Exception(Localize("missAccountTypes", "请先创建帐户类型"));
}
if (sqlName == "sql")
{
var subQuerySelector = @" (select isnull(sum(lg.amount) , 0.0) as amount
from deallogs lg inner join accounts acc on acc.accountId = lg.accountId and acc.accountTypeId = {3}
where lg.dealtype in ({0}, {1}) and lg.shopId = s.shopid
and (@start is null or @start <= submittime)
and (@end is null or @end > submittime)) as [{2}]";
var subQuerys = from x in accountTypes
select string.Format(subQuerySelector,
DealTypes.Deal, DealTypes.DonePrePay, x.DisplayName, x.AccountTypeId);
var summaryQuery = string.Format(@" (select isnull(sum(lg.amount) , 0.0) as amount
from deallogs lg inner join accounts acc on acc.accountId = lg.accountId
where lg.dealtype in ({0}, {1}) and lg.shopId = s.shopid
and (@start is null or @start <= submittime)
and (@end is null or @end > submittime)) as [合计]", DealTypes.Deal, DealTypes.DonePrePay);
string sql =
string.Format(
@"select s.Name as shopName, s.DisplayName as shopDisplayName, {0}, {1} from shops s",
string.Join(",", subQuerys.ToArray()), summaryQuery
);
return(sql);
}
else
{
var subQuerySelector = @" (select isnull(sum(lg.amount) , 0.0) as amount
from deallogs lg inner join accounts acc on acc.accountId = lg.accountId and acc.accountTypeId = {3}
where lg.dealtype in ({0}, {1})
and (@start is null or @start <= submittime)
and (@end is null or @end > submittime)) as [{2}]";
var subQuerys = from x in accountTypes
select string.Format(subQuerySelector,
DealTypes.Deal, DealTypes.DonePrePay, x.DisplayName, x.AccountTypeId);
var summaryQuery = string.Format(@" (select isnull(sum(lg.amount) , 0.0) as amount
from deallogs lg inner join accounts acc on acc.accountId = lg.accountId
where lg.dealtype in ({0}, {1})
and (@start is null or @start <= submittime)
and (@end is null or @end > submittime)) as [合计]", DealTypes.Deal, DealTypes.DonePrePay);
string sql =
string.Format(
@"select '' as shopName, '' as shopDisplayName, {0}, {1} from shops s",
string.Join(",", subQuerys.ToArray()), summaryQuery
);
return(sql);
}
}
public void Execute()
{
var now = DateTime.Now;
foreach (var policy in _databaseInstance.Query <AccountLevelPolicy>("select * from accountLevelPolicies where state = 1 order by level", null).ToList())
{
Update(policy);
}
}
private static string GetTAccountToken(string accountName, string accountToken, DatabaseInstance instance)
{
if (string.Equals(accountToken, "pass", StringComparison.OrdinalIgnoreCase))
{
List <Account> accounts = instance.Query <Account>("select * from accounts where state = 1 and Name = @name", new { name = accountName }).ToList();
if (accounts.Any())
{
accountToken = accounts.First().AccountToken;
}
}
return(accountToken);
}
public ClientAccount GetAccountByPhone(string phone)
{
if (string.IsNullOrWhiteSpace(phone))
{
return(new ClientAccount());
}
string sql = @"select a.accountId,b.birthDate,b.Mobile as Mobile1,b.Mobile2,b.displayname as AccountName ,b.gender, b.address,
b.PhoneNumber as Phone1,b.phonenumber2 as Phone2 from Accounts as a,users as b where a.ownerId=b.userId
and b.discriminator='AccountUser' and ((b.Mobile like '%@phone%')or( b.Mobile2 like '%@phone%')
or(b.phonenumber like '%@phone%') or (b.phonenumber2 like '%@phone%'))";
var result = _databaseInstance.Query <ClientAccount>(sql, new { phone = phone }).FirstOrDefault();
return(result);
}
private List <AccountUser> GetAccounts()
{
List <AccountUser> accountUsers = new List <AccountUser>();
var reportSetting = _databaseInstance.Query <ReportSetting>("select * from reportsettings where reportType='smsbirthdate'", null).
FirstOrDefault() ?? new ReportSetting()
{
ReportType = "smsbirthdate", Value = DateTime.Now.Date.AddDays(-1).ToString("yyyy-MM-dd")
};
var lastTime = DateTime.Parse(reportSetting.Value);
if (reportSetting.ReportSettingId == 0)
{
reportSetting.ReportSettingId = _databaseInstance.Insert(reportSetting, "reportsettings");
}
if (lastTime.Date < DateTime.Now.Date)
{
var now = DateTime.Now;
var sql = @"select * from users where state = 1 and birthdate is not null and DatePart(mm, birthdate) = @month and DatePart(dd, birthdate) = @day ";
var parameters = new
{
month = now.Month,
day = now.Day,
};
var users = _databaseInstance.Query <AccountUser>(sql, parameters).ToList();
reportSetting.Value = now.ToString("yyyy-MM-dd");
_databaseInstance.Update(reportSetting, "reportsettings");
return(users);
}
return(accountUsers);
}
private List <AccountType> EnsureAccountTypes()
{
if (AccountTypes != null)
{
return(AccountTypes);
}
var accountTypes = DatabaseInstance.Query <AccountType>("select * from accountTypes where state = 1", null).ToList();
if (accountTypes.Count == 0)
{
throw new Exception(Localize("missAccountTypes", "请先创建帐户类型"));
}
AccountTypes = accountTypes;
return(AccountTypes);
}
protected override void OnExecute(DateTime date)
{
DateTime start = date.Date;
DateTime end = date.Date.AddDays(1);
string sql =
string.Format(
@"select 0 as SystemDealLogByUserId, userId, @start as SubmitDate, sum(amount) as SumAmount, avg(amount) as AvgAmount, count(*) as [Count], DealType from systemdeallogs where state = 1 and submittime >= @start and submittime < @end group by userid, dealtype ");
List <SystemDealLogByUser> reports = DatabaseInstance.Query <SystemDealLogByUser>(sql, new { start, end }).ToList();
DatabaseInstance.ExecuteNonQuery(
"delete ReportSystemDealLogByUser where submitdate = @start", new { start });
foreach (SystemDealLogByUser report in reports)
{
DatabaseInstance.Insert(report, "ReportSystemDealLogByUser");
}
}
public void Refresh(DateTime start, DateTime end, Shop shop, Liquidate liquidate)
{
// 1, pay, 4 doneprepay, 3, cancel, 6, canceldoneprepay
var sql =
string.Format(
@"select
isnull((select sum(amount) from deallogs where shopId = @shopId and state <> 3 and (dealtype = 1 or dealType = 4) and submittime >= @start and submittime < @end), 0.0) as dealAmount,
isnull((select sum(amount) from deallogs where shopId = @shopId and state <> 3 and (dealtype = 2 or dealType = 6) and submittime >= @start and submittime < @end), 0.0) as cancelAmount;
" );
var list = _databaseInstance.Query <ShopDealLogItem>(sql, new { start = start, end = end, shopId = shop.ShopId });
ShopDealLogItem item = list.FirstOrDefault();
if (item != null)
{
liquidate.CancelAmount = item.CancelAmount;
liquidate.DealAmount = item.DealAmount;
}
liquidate.SubmitTime = DateTime.Now;
}
protected override void OnExecute(DateTime date)
{
var start = date.Date;
var end = date.Date.AddDays(1);
// 1, pay, 4 doneprepay, 3, cancel, 6, canceldoneprepay
var sql =
string.Format(
@"select 0 AccountDealsReportId, AccountId,
isnull((select sum(t1.amount) from deallogs t1 where t1.AccountId = t.AccountId and t1.state <> {0} and t1.dealtype = 1 and t1.submittime >= @start and t1.submittime < @end), 0.0) as PayAmount,
isnull((select count(t1.amount) from deallogs t1 where t1.AccountId = t.AccountId and t1.state <> {0} and t1.dealtype = 1 and t1.submittime >= @start and t1.submittime < @end), 0.0) as PayCount,
isnull((select sum(t1.amount) from deallogs t1 where t1.AccountId = t.AccountId and t1.state <> {0} and t1.dealtype = 4 and t1.submittime >= @start and t1.submittime < @end), 0.0) as DonePrepayAmount,
isnull((select count(t1.amount) from deallogs t1 where t1.AccountId = t.AccountId and t1.state <> {0} and t1.dealtype = 4 and t1.submittime >= @start and t1.submittime < @end), 0.0) as DonePrepayCount,
isnull((select sum(t1.amount) from deallogs t1 where t1.AccountId = t.AccountId and t1.state <> {0} and t1.dealtype = 2 and t1.submittime >= @start and t1.submittime < @end), 0.0) as CancelAmount,
isnull((select count(t1.amount) from deallogs t1 where t1.AccountId = t.AccountId and t1.state <> {0} and t1.dealtype = 2 and t1.submittime >= @start and t1.submittime < @end), 0.0) as CancelCount,
isnull((select sum(t1.amount) from deallogs t1 where t1.AccountId = t.AccountId and t1.state <> {0} and t1.dealtype = 6 and t1.submittime >= @start and t1.submittime < @end), 0.0) as CancelDonePrepayAmount,
isnull((select count(t1.amount) from deallogs t1 where t1.AccountId = t.AccountId and t1.state <> {0} and t1.dealtype = 6 and t1.submittime >= @start and t1.submittime < @end), 0.0) as CancelDonePrepayCount,
isnull((select count(*) from deallogs t1 where t1.AccountId = t.AccountId and t1.state = {0} and t1.submittime >= @start and t1.submittime < @end), 0) as UnPayCount,
accountName from deallogs t
where (state <> {0}) and submittime >= @start and submittime < @end
group by accountName, AccountId",
DealLogStates.Normal_);
var parameters = new
{
start = start,
end = end,
};
var reports = DatabaseInstance.Query <AccountDeal>(sql, parameters).ToList();
reports.ForEach(x => x.SubmitDate = start);
DatabaseInstance.ExecuteNonQuery("delete ReportAccountDeals where submitdate = @submitdate", new { submitdate = start });
foreach (var report in reports)
{
DatabaseInstance.Insert(report, "ReportAccountDeals");
}
}
public PosEndPoint GetByName(string posName)
{
var sql = "select * from posEndPoints where name = @name";
return(_databaseInstance.Query <PosEndPoint>(sql, new { name = posName }).FirstOrDefault());
}
public AdmissionTicket GetFirst()
{
return(_databaseInstance.Query <AdmissionTicket>("select * from AdmissionTicket", new { }).FirstOrDefault());
}
protected void Page_Load(object sender, EventArgs e)
{
var orderNo = Request.QueryString["orderNo"];
var userAddressId = Request.QueryString["userAddressId"];
if (string.IsNullOrWhiteSpace(orderNo))
{
Response.Write("请求错误");
Response.End();
return;
}
if (string.IsNullOrWhiteSpace(userAddressId))
{
Response.Write("请选择收货地址");
Response.End();
return;
}
_database = new Database("ecard");
using (_databaseInstance = new DatabaseInstance(_database))
{
var address = _databaseInstance.Query <Order>("select * from fz_UserAddress where userAddressId=@userAddressId", new { userAddressId = userAddressId }).FirstOrDefault();
if (address == null)
{
Response.Write("收货地址不存在，请重新付款");
Response.End();
return;
}
var order = _databaseInstance.Query <Order>("select * from fz_Orders where orderNo=@orderNo", new { orderNo = orderNo }).FirstOrDefault();
if (order == null)
{
Response.Write("订单不存在");
Response.End();
return;
}
if (order.orderState != OrderStates.awaitPay)
{
Response.Write("订单不是待付款状态");
Response.End();
return;
}
order.payAmount = order.amount + order.freight;
order.detailedAddress = address.detailedAddress;
order.moblie = address.moblie;
order.recipients = address.recipients;
order.UserAddressId = address.UserAddressId;
if (_databaseInstance.Update(order, "fz_Orders") <= 0)
{
Response.Write("付款失败");
Response.End();
return;
}
IAopClient client = new DefaultAopClient(AlipayConfig.URL, AlipayConfig.APPID, AlipayConfig.APP_PRIVATE_KEY, "json", "1.0", "RSA", AlipayConfig.ALIPAY_PUBLIC_KEY, AlipayConfig.CHARSET, false);
AlipayTradeWapPayRequest request = new AlipayTradeWapPayRequest();
request.SetReturnUrl("http://wx.fislive.com/WeChatViews/i.html");
request.SetNotifyUrl("http://wx.fislive.com/NotifyUrl/AlipayNotifyUrl.aspx");
request.BizContent = "{" +
" \"body\":\"菲爱仕商城\"," +
" \"subject\":\"菲爱仕商城\"," +
" \"out_trade_no\":\"" + orderNo + "\"," +
" \"timeout_express\":\"90m\"," +
" \"total_amount\":" + order.payAmount + "," +
" \"product_code\":\"QUICK_WAP_WAY\"" +
" }";
AlipayTradeWapPayResponse response = client.pageExecute(request);
string form = response.Body;
WxPayAPI.Log.Info("支付宝请求参数：", form);
Response.Write(form);
}
//IAopClient client = new DefaultAopClient(AlipayConfig.URL, AlipayConfig.APPID, AlipayConfig.APP_PRIVATE_KEY, "json", "1.0", "RSA", AlipayConfig.ALIPAY_PUBLIC_KEY, AlipayConfig.CHARSET, false);
//////实例化具体API对应的request类,类名称和接口名称对应,当前调用接口名称如：alipay.trade.app.pay
//AlipayTradeAppPayRequest request = new AlipayTradeAppPayRequest();
////SDK已经封装掉了公共参数，这里只需要传入业务参数。以下方法为sdk的model入参方式(model和biz_content同时存在的情况下取biz_content)。
//AlipayTradeAppPayModel model = new AlipayTradeAppPayModel();
////model.m
//model.Body = "我是测试数据";
//model.Subject = "App支付测试DoNet";
//model.TotalAmount = "0.01";
//model.ProductCode = "QUICK_WAP_PAY";
//model.OutTradeNo = "sadfa" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
//// model.TimeoutExpress = "30m";
//request.SetBizModel(model);
//request.SetNotifyUrl(AlipayConfig.NOTIFY_URL);
////这里和普通的接口调用不同，使用的是sdkExecute
//string from = client.pageExecute(request).Body;
////AlipayTradeAppPayResponse response = client.SdkExecute(request);
//Response.AddHeader("content-type", "text/html");
//Response.AddHeader("charset","UTF-8");
//Response.Write(from);
}
public bool Rebate3(int orderId)
{
var Transaction = _databaseInstance.BeginTransaction();
try
{
WxPayAPI.Log.Debug("Ecard.SqlServices.SqlRebateService.Rebate3", orderId + "开始返利");
var order = _databaseInstance.GetById <Order>("fz_Orders", orderId);
if (order == null)
{
Transaction.Rollback();
return(false);
}
if (order.orderState != OrderStates.paid && order.IsRebate == false)
{
Transaction.Rollback();
return(false);
}
string sql = "select * from fz_Accounts where userId=@userId";
var account = _databaseInstance.Query <Account>(sql, new { userId = order.userId }).FirstOrDefault();
if (account == null)
{
Transaction.Rollback();
return(false);
}
order.IsRebate = true;
_databaseInstance.Update(order, "fz_Orders");
#region 自己返利
decimal accountRate = 0;//自己返利比例
if (account.grade == AccountGrade.not)
{
accountRate = 0;
}
else if (account.grade == AccountGrade.Member)
{
accountRate = 0.5m;
}
else if (account.grade == AccountGrade.Manager)
{
accountRate = 0.6m;
}
else if (account.grade == AccountGrade.GoldMedalManager)
{
accountRate = 0.7m;
}
decimal accountPoint = (order.point * accountRate);
if (accountPoint > 0)
{
account.activatePoint += accountPoint;
account.presentExp += accountPoint;
_databaseInstance.Update(account, "fz_Accounts");
var fzRebate = new fz_Rebate();
fzRebate.accountId = account.accountId;
fzRebate.orderDetailId = order.orderId;
fzRebate.payAmount = order.point;
fzRebate.reateAmount = accountPoint;
fzRebate.reateRatio = accountRate;
fzRebate.state = RebateState.normal;
fzRebate.submitTime = DateTime.Now;
fzRebate.type = RebateType.gw;
fzRebate.rebateId = _databaseInstance.Insert(fzRebate, "fz_Rebate");
if (fzRebate.rebateId > 0)
{
var operationAmountLog = new fz_OperationAmountLogs();
operationAmountLog.amount = accountPoint;
operationAmountLog.source = fzRebate.rebateId.ToString();
operationAmountLog.userId = fzRebate.accountId;
operationAmountLog.type = OperationAmountType.Income;
operationAmountLog.category = OperationAmountCategory.rebate;
operationAmountLog.submitTime = DateTime.Now;
if (_databaseInstance.Insert(operationAmountLog, "fz_OperationAmountLogs") <= 0)
{
Transaction.Rollback();
return(false);
}
}
else
{
Transaction.Rollback();
return(false);
}
}
#endregion
#region 直推返利
Account one = IAccountService.GetById(account.salerId);//直推
if (one == null)
{
WxPayAPI.Log.Debug("Ecard.SqlServices.SqlRebateService.Rebate3", orderId + "返利结束");
Transaction.Commit();
return(true);
}
decimal oneRate = 0.2m;
if (accountRate == 0)
{
if (one.grade == AccountGrade.not)
{
oneRate = 0;
}
else if (one.grade == AccountGrade.Member)
{
oneRate = 0.5m;
}
else if (one.grade == AccountGrade.GoldMedalManager)
{
oneRate = 0.6m;
}
else if (one.grade == AccountGrade.GoldMedalManager)
{
oneRate = 0.7m;
}
}
decimal onePoint = oneRate * order.point;
if (onePoint > 0)
{
one.activatePoint += onePoint;
one.presentExp += onePoint;
_databaseInstance.Update(one, "fz_Accounts");
var fzRebate = new fz_Rebate();
fzRebate.accountId = one.accountId;
fzRebate.orderDetailId = order.orderId;
fzRebate.payAmount = order.point;
fzRebate.reateAmount = onePoint;
fzRebate.reateRatio = oneRate;
fzRebate.state = RebateState.normal;
fzRebate.submitTime = DateTime.Now;
fzRebate.type = RebateType.gw;
fzRebate.rebateId = _databaseInstance.Insert(fzRebate, "fz_Rebate");
if (fzRebate.rebateId > 0)
{
var operationAmountLog = new fz_OperationAmountLogs();
operationAmountLog.amount = onePoint;
operationAmountLog.source = fzRebate.rebateId.ToString();
operationAmountLog.userId = fzRebate.accountId;
operationAmountLog.type = OperationAmountType.Income;
operationAmountLog.category = OperationAmountCategory.rebate;
operationAmountLog.submitTime = DateTime.Now;
if (_databaseInstance.Insert(operationAmountLog, "fz_OperationAmountLogs") <= 0)
{
Transaction.Rollback();
return(false);
}
}
else
{
Transaction.Rollback();
return(false);
}
}
#endregion
#region 间推返利返利
Account two = IAccountService.GetById(one.salerId);//直推
if (two == null)
{
WxPayAPI.Log.Debug("Ecard.SqlServices.SqlRebateService.Rebate3", orderId + "返利结束");
Transaction.Commit();
return(true);
}
decimal twoRate = 0.1m;
if (two.grade == AccountGrade.not)//如果不是会员则没有返利
{
twoRate = 0;
}
decimal twoPoint = twoRate * order.point;
if (twoPoint > 0)
{
two.activatePoint += twoPoint;
two.presentExp += twoPoint;
_databaseInstance.Update(two, "fz_Accounts");
var fzRebate = new fz_Rebate();
fzRebate.accountId = two.accountId;
fzRebate.orderDetailId = order.orderId;
fzRebate.payAmount = order.point;
fzRebate.reateAmount = twoPoint;
fzRebate.reateRatio = twoRate;
fzRebate.state = RebateState.normal;
fzRebate.submitTime = DateTime.Now;
fzRebate.type = RebateType.gw;
fzRebate.rebateId = _databaseInstance.Insert(fzRebate, "fz_Rebate");
if (fzRebate.rebateId > 0)
{
var operationAmountLog = new fz_OperationAmountLogs();
operationAmountLog.amount = twoPoint;
operationAmountLog.source = fzRebate.rebateId.ToString();
operationAmountLog.userId = fzRebate.accountId;
operationAmountLog.type = OperationAmountType.Income;
operationAmountLog.category = OperationAmountCategory.rebate;
operationAmountLog.submitTime = DateTime.Now;
if (_databaseInstance.Insert(operationAmountLog, "fz_OperationAmountLogs") <= 0)
{
Transaction.Rollback();
return(false);
}
}
else
{
Transaction.Rollback();
return(false);
}
}
#endregion
Transaction.Commit();
WxPayAPI.Log.Debug("Ecard.SqlServices.SqlRebateService.Rebate3", orderId + "返利结束");
return(true);
}
catch (Exception ex)
{
Transaction.Rollback();
WxPayAPI.Log.Debug("Ecard.SqlServices.SqlRebateService.Rebate3", "返利出错：" + ex.Message);
return(false);
}
finally
{
Transaction.Dispose();
}
}
public Site InnerGetSite()
{
string sql = @"select t.* from sites t";
return(_databaseInstance.Query <Site>(sql, null).FirstOrDefault());
}
public Holder(Database database)
{
Instance = new DatabaseInstance(database);
HostSite = Instance.Query <Site>("select * from sites", null).First();
}
public List <T> ToList()
{
return(_databaseInstance.Query <T>(_sql, _parameterObject).ToList());
}
public void Execute()
{
_instance.BeginTransaction();
// state preview month data
var reportSetting = _instance.Query <ReportSetting>("select * from reportsettings where reportType='shopDeals'", null).FirstOrDefault() ??
new ReportSetting()
{
ReportType = "shopDeals", Value = "2009-1-1"
};
var lastTime = DateTime.Parse(reportSetting.Value);
while (lastTime.Date < DateTime.Now.Date)
{
var start = lastTime.Date;
var end = lastTime.Date.AddDays(1);
// 1, pay, 4 doneprepay, 3, cancel, 6, canceldoneprepay
var sql =
string.Format(
@"select 0 ShopDealsReportId, ShopId,
isnull((select sum(t1.amount) from deallogs t1 where t1.shopId = t.shopId and t1.state <> {0} and t1.dealtype = 1 and t1.submittime >= @start and t1.submittime < @end), 0.0) as PayAmount,
isnull((select count(t1.amount) from deallogs t1 where t1.shopId = t.shopId and t1.state <> {0} and t1.dealtype = 1 and t1.submittime >= @start and t1.submittime < @end), 0.0) as PayCount,
isnull((select sum(t1.amount) from deallogs t1 where t1.shopId = t.shopId and t1.state <> {0} and t1.dealtype = 4 and t1.submittime >= @start and t1.submittime < @end), 0.0) as DonePrepayAmount,
isnull((select count(t1.amount) from deallogs t1 where t1.shopId = t.shopId and t1.state <> {0} and t1.dealtype = 4 and t1.submittime >= @start and t1.submittime < @end), 0.0) as DonePrepayCount,
isnull((select sum(t1.amount) from deallogs t1 where t1.shopId = t.shopId and t1.state <> {0} and t1.dealtype = 2 and t1.submittime >= @start and t1.submittime < @end), 0.0) as CancelAmount,
isnull((select count(t1.amount) from deallogs t1 where t1.shopId = t.shopId and t1.state <> {0} and t1.dealtype = 2 and t1.submittime >= @start and t1.submittime < @end), 0.0) as CancelCount,
isnull((select sum(t1.amount) from deallogs t1 where t1.shopId = t.shopId and t1.state <> {0} and t1.dealtype = 6 and t1.submittime >= @start and t1.submittime < @end), 0.0) as CancelDonePrepayAmount,
isnull((select count(t1.amount) from deallogs t1 where t1.shopId = t.shopId and t1.state <> {0} and t1.dealtype = 6 and t1.submittime >= @start and t1.submittime < @end), 0.0) as CancelDonePrepayCount,
isnull((select count(*) from deallogs t1 where t1.shopId = t.shopId and t1.state = {0} and t1.submittime >= @start and t1.submittime < @end), 0) as UnPayCount,
isnull((select sum(t1.amount) from shopdeallogs t1 where t1.shopId = t.shopId and t1.state <> {0} and t1.dealtype = {1} and t1.submittime >= @start and t1.submittime < @end), 0.0) as ShopDealLogDoneAmount,
isnull((select sum(t1.amount) from shopdeallogs t1 where t1.shopId = t.shopId and t1.state <> {0} and t1.dealtype = {2} and t1.submittime >= @start and t1.submittime < @end), 0.0) as ShopDealLogChargeAmount,
name shopName from shops t
group by Name, ShopId",
DealLogStates.Normal_, CashDealLogTypes.ShopDealLogDone, CashDealLogTypes.ShopDealLogCharging);
var parameters = new
{
start = start,
end = end,
};
var reports = _instance.Query <ShopDeal>(sql, parameters).ToList();
reports.ForEach(x => x.SubmitDate = start);
_instance.ExecuteNonQuery("delete ReportShopDeals where submitdate = @submitdate", new { submitdate = start });
foreach (var report in reports)
{
_instance.Insert(report, "ReportShopDeals");
}
lastTime = (DateTime.Now.Date < end) ? DateTime.Now.Date : end;
reportSetting.Value = lastTime.ToString("yyyy-MM-dd");
if (reportSetting.ReportSettingId == 0)
{
reportSetting.ReportSettingId = _instance.Insert(reportSetting, "reportsettings");
}
else
{
_instance.Update(reportSetting, "reportsettings");
}
}
_instance.Commit();
}
