public JsonResult CreateBulkAdjustment([FromBody] BulkAdjustmentObject bao) { using (var context = new Data.ApplicationDbContext()) { String authUserId = User.GetClaim(OpenIdConnectConstants.Claims.Subject); Models.User authUser = context.Users.AsNoTracking().FirstOrDefault(u => u.Id.ToString().Equals(authUserId)); if (authUser == null) { return(Json(new { status_code = 2, status = "Unknown user '" + authUserId + "'" })); } if (!Helpers.PermissionChecker.CanDoAdjustments(authUser)) { return(Json(new { status_code = 1, status = "User '" + authUser.UserName + "' does not have permission to make adjustmetns" })); } String[] sites = bao.DelimitedSites.Split(','); List <AdjustmentWrapperObject> siteList = new List <AdjustmentWrapperObject>(); // Loop through once to verify info given is valid, then again to do actual reports foreach (String siteId in sites) { Site siteAdjusted = context.Sites.AsNoTracking().FirstOrDefault(s => s.SiteId.ToString().Equals(siteId)); if (siteAdjusted == null) { return(Json(new { status_code = 3, status = "Site '" + siteId + "' does not exist" })); } User distributor = context.Users.AsNoTracking().FirstOrDefault(u => u.Id == siteAdjusted.SiteDistributor); if (distributor == null) { return(Json(new { status_code = 4, status = "Site '" + siteAdjusted.SiteName + "' has no distributor" })); } Models.System system = context.Systems.AsNoTracking().FirstOrDefault(s => s.Id == siteAdjusted.SystemId); if (system == null) { return(Json(new { status_code = 4, status = "Site '" + siteAdjusted.SiteName + "' has no system" })); } AdjustmentWrapperObject awo = new AdjustmentWrapperObject(); awo.AdjustedSite = siteAdjusted; awo.Distributor = distributor; awo.SiteSystem = system; siteList.Add(awo); } foreach (var siteAdjusted in siteList) { Adjustment newAdjustment = new Adjustment(); newAdjustment.Copy(bao.BulkAdjustment, false); newAdjustment.SiteId = siteAdjusted.AdjustedSite.SiteId; newAdjustment.SubmissionDate = DateTime.UtcNow; newAdjustment.SubmittedBy = authUser.UserName; newAdjustment.SubmitterId = authUser.Id; newAdjustment.Completed = false; newAdjustment.CompletedDate = new DateTime(1970, 1, 1); newAdjustment.Ip = "127.0.0.1"; newAdjustment.SecretKey = Guid.NewGuid().ToString(); switch (bao.BulkAdjustment.Type) { case Adjustment.AdjustmentType.DropCommunityPrize: case Adjustment.AdjustmentType.DropGrandPrize: newAdjustment.RestartTime = null; break; } #if LOCAL using (MySqlConnection conn = new MySqlConnection(Startup.Configuration.GetConnectionString("LocalDatabase"))) { #else using (MySqlConnection conn = new MySqlConnection(Startup.Configuration.GetConnectionString("Database"))) { #endif conn.Open(); DateTime weekStart = newAdjustment.SubmissionDate.Subtract(new TimeSpan(7, 0, 0, 0)), monthStart = newAdjustment.SubmissionDate.Subtract(new TimeSpan(31, 0, 0, 0)); MySqlCommand cmd = new MySqlCommand(String.Format( "SELECT " + " s.site_id,s.site_name,s.site_number,WeekMoneyIN AS 'WeekIn', WeekMoneyOUT AS 'WeekOut',MonthMoneyIN AS 'MonthIn', MonthMoneyOUT AS 'MonthOut'"+ "FROM permission p " + "RIGHT JOIN " + " (SELECT "+ " site_id,site_name,site_active,site_number "+ " FROM sites) s "+ " ON p.site_id = s.site_id "+ "LEFT JOIN " + " (SELECT billing.site_id, SUM(iAmount) / 100 AS WeekMoneyIN "+ " FROM billing "+ " WHERE "+ " strStatus = 'MD' AND dtDate >= '{1}' AND dtDate < '{0}' "+ " GROUP BY site_id) WMI "+ " ON WMI.site_id = s.site_id "+ "LEFT JOIN " + " (SELECT billing.site_id, SUM(iAmount) / 100 AS WeekMoneyOUT "+ " FROM billing "+ " WHERE "+ " strStatus = 'MW' AND dtDate >= '{1}' AND dtDate < '{0}' "+ " GROUP BY site_id) WMO "+ " ON WMO.site_id = s.site_id "+ "LEFT JOIN " + " (SELECT billing.site_id, SUM(iAmount) / 100 AS MonthMoneyIN "+ " FROM billing " + " WHERE " + " strStatus = 'MD' AND dtDate >= '{2}' AND dtDate < '{0}' "+ " GROUP BY site_id) MMI "+ " ON MMI.site_id = s.site_id " + "LEFT JOIN " + " (SELECT billing.site_id, SUM(iAmount) / 100 AS MonthMoneyOUT "+ " FROM billing " + " WHERE " + " strStatus = 'MW' AND dtDate >= '{2}' AND dtDate < '{0}' "+ " GROUP BY site_id) MMO "+ " ON MMO.site_id = s.site_id " + "WHERE " + " (s.site_id = {3} AND s.site_active = 1);", bao.BulkAdjustment.SubmissionDate.ToString("yyyy-MM-dd HH:mm:ss"), weekStart.ToString("yyyy-MM-dd HH:mm:ss"), monthStart.ToString("yyyy-MM-dd HH:mm:ss"), siteAdjusted.AdjustedSite.SiteId), conn); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var money = new { WeekIn = DBNull.Value.Equals(reader["WeekIn"]) ? 0 : Convert.ToDecimal(reader["WeekIn"]), WeekOut = DBNull.Value.Equals(reader["WeekOut"]) ? 0 : Convert.ToDecimal(reader["WeekOut"]), MonthIn = DBNull.Value.Equals(reader["MonthIn"]) ? 0 : Convert.ToDecimal(reader["MonthIn"]), MonthOut = DBNull.Value.Equals(reader["MonthOut"]) ? 0 : Convert.ToDecimal(reader["MonthOut"]) }; newAdjustment.WeekMoneyIn = money.WeekIn; newAdjustment.WeekMoneyOut = money.WeekOut; newAdjustment.MonthMoneyIn = money.MonthIn; newAdjustment.MonthMoneyOut = money.MonthOut; } } conn.Close(); } context.Adjustments.Add(newAdjustment); context.SaveChanges(); // Log and notify actions var json = new { newAdjustment.WeekMoneyIn, newAdjustment.WeekMoneyOut, newAdjustment.MonthMoneyIn, newAdjustment.MonthMoneyOut, SubmissionDate = newAdjustment.SubmissionDate.ToString("yyyy-MM-ddTHH:mm:ss.fffZ"), newAdjustment.SubmittedBy, Type = newAdjustment.Type.ToString(), newAdjustment.ResetRequest, newAdjustment.Notes, RoomName = siteAdjusted.AdjustedSite.SiteName, siteAdjusted.AdjustedSite.SiteNumber, Distributor = siteAdjusted.Distributor.FName + " " + siteAdjusted.Distributor.LName, System = siteAdjusted.SiteSystem.Name }; Helpers.LogHelper.LogAction(Log.ActionType.CreateAdjustment, authUser.Id, newAdjustment.Id, String.Format("{0} (id: {1}) created adjustment (id: {2}) for site {3} (id: {4})", authUser.UserName, authUser.Id, newAdjustment.Id, siteAdjusted.AdjustedSite.SiteName, siteAdjusted.AdjustedSite.SiteId)); Helpers.LogHelper.NotifyAction(Log.ActionType.CreateAdjustment, json); } return(Json(new { status_code = 0 })); } }