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