public void UpdateAccountLevelPolicy(int distributorId, DistributorAccountLevelRate rates)
{
_databaseInstance.ExecuteNonQuery(
"delete DistributorAccountLevelPolicyRates where DistributorId = @DistributorId",
new { DistributorId = distributorId });
_databaseInstance.Insert(rates, "DistributorAccountLevelPolicyRates");
}
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");
}
}
private void Update(AccountLevelPolicy policy)
{
List <DbParameter> parameters = new List <DbParameter>();
var sql = "update Accounts set AccountLevelName = @name, accountLevel = @level where accountLevel <= @level and TotalPoint >= @TotalPointStart and accountTypeId = @accountTypeId";
_databaseInstance.ExecuteNonQuery(sql, new { name = policy.DisplayName, level = policy.Level, TotalPointStart = policy.TotalPointStart, accountTypeId = policy.AccountTypeId });
}
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");
}
}
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 int DeleteOrderDetail(int commodityId, string orderNo)
{
string sql = "delete from fz_OrderDetails where commodityId=@commodityId and orderNo=@orderNo";
return(_databaseInstance.ExecuteNonQuery(sql, new { commodityId = commodityId, orderNo = orderNo }));
}
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();
}
public void DeleteUsersForRole(int roleId)
{
var sql = @"delete from UserRoles where Role_RoleId = @roleId";
_databaseInstance.ExecuteNonQuery(sql, new { roleId = roleId });
}
protected override void OnExecute(DateTime date)
{
DatabaseInstance.ExecuteNonQuery("CreateDealLogDaily", new { @start = date.Date }, CommandType.StoredProcedure);
}
public int ExecuteNonQuery(CommandType commandType, string commandText)
{
return(Invoke(() => DatabaseInstance.ExecuteNonQuery(commandType, commandText)));
}
public int ExecuteNonQuery(string storedProcedureName, params object[] parameterValues)
{
return(Invoke(() => DatabaseInstance.ExecuteNonQuery(storedProcedureName, parameterValues)));
}
public int ExecuteNonQuery(DbCommand command)
{
return(Invoke(() => DatabaseInstance.ExecuteNonQuery(command)));
}
public int DeleteOrderDetials(string OrderId)
{
string sql = string.Format(@"delete Orderdetial where OrderId='{0}'", OrderId);
return(_databaseInstance.ExecuteNonQuery(sql, null));
}
public int UpdateLiquidateId(List <int> ids, int liquidateId, int originalId, int shopId)
{
var sql = @"update shopdeallogs set LiquidateDealLogId = @liquidateId where state <> @state and shopdeallogid in (@ids) and dealtype in (1,2,4,6,8) and shopid = @shopid and LiquidateDealLogId = @originalId";
return(_databaseInstance.ExecuteNonQuery(sql, new { shopId = shopId, state = DealLogStates.Normal_, liquidateId = liquidateId, ids = ids, originalId = originalId }));
}
public void Delete(PrePay prePay)
{
var sql = "delete prepays where prepayId = @prepayId ";
_databaseInstance.ExecuteNonQuery(sql, new { prepayId = prePay.PrePayId });
}
public void DisableMessageOfDeal(int accountTypeId)
{
_databaseInstance.ExecuteNonQuery("account.DisableMessageOfDeal", new { accountTypeId = accountTypeId });
}
public void UpdateCurrenUserId(int posEndPointId, int userId)
{
var sql = "update posEndPoints set currentUserId = @userId, recordversion = recordversion +1 where posEndPointId = @posEndPointId ";
_databaseInstance.ExecuteNonQuery(sql, new { posEndPointId = posEndPointId, userId = userId });
}
