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 }); }