public async Task <IActionResult> Edit(int id, [Bind("UtilityId,Property,Value,Id,UserId,Status,ModDateTime,ModByUserId,Comment")] UtilityDetail utilityDetail)
{
if (id != utilityDetail.Id)
{
return(NotFound());
}
if (ModelState.IsValid)
{
try
{
_context.Update(utilityDetail);
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!UtilityDetailExists(utilityDetail.Id))
{
return(NotFound());
}
else
{
throw;
}
}
return(RedirectToAction(nameof(Index)));
}
ViewData["UtilityId"] = new SelectList(_context.Utilities, "Id", "MainName", utilityDetail.UtilityId);
return(View(_mapper.Map <UtilityDetailViewModel>(utilityDetail)));
}
public async Task <IActionResult> Create([Bind("UtilityId,Property,Value,Id,UserId,Status,ModDateTime,ModByUserId,Comment")] UtilityDetail utilityDetail)
{
if (ModelState.IsValid)
{
_context.Add(utilityDetail);
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Index)));
}
ViewData["UtilityId"] = new SelectList(_context.Utilities, "Id", "MainName", utilityDetail.UtilityId);
return(View(_mapper.Map <UtilityDetailViewModel>(utilityDetail)));
}
public static Total GetTotalGraphByYear(Dates dates, int userId)
{
Total total = new Total();
total.Utilities = new List <List <object> >();
List <string> names = new List <string>();
List <UtilityRecord> utilityRecords = new List <UtilityRecord>();
List <UtilityDetail> utilityDetails = new List <UtilityDetail>();
using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build()))
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = @"SELECT name FROM utilities";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
total.Utilities.Add(new List <object>()
{
reader["name"].ToString()
});
}
}
}
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = @"SELECT utility_record_id,date,value,U.address,name,price
FROM utility_records AS UR
INNER JOIN residents_addresses AS U
ON UR.address = U.address
INNER JOIN utilities
ON utility = utility_id
WHERE U.resident = @userId
AND year(date) = @year
ORDER BY utility,date";
cmd.Parameters.AddWithValue("@userId", (Convert.ToInt32(userId)));
cmd.Parameters.AddWithValue("@year", (Convert.ToInt32(dates.Years[0])));
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
UtilityRecord utilityRecord = new UtilityRecord
{
Value = (int)reader["value"],
Date = Convert.ToDateTime(reader["date"])
};
Utility utility = new Utility
{
Name = reader["name"].ToString(),
Price = (double)reader["price"],
};
utilityRecord.Utility = utility;
utilityRecords.Add(utilityRecord);
}
}
}
int next = 0, current = 0;
for (int i = 0; i < utilityRecords.Count - 1; i++)
{
if (utilityRecords[i].Utility.Name == utilityRecords[i + 1].Utility.Name)
{
double price = utilityRecords[i].Utility.Price;
current = utilityRecords[i].Value;
next = utilityRecords[i + 1].Value;
UtilityDetail utilityDetail = new UtilityDetail
{
Name = utilityRecords[i].Utility.Name,
Price = Convert.ToDouble((next - current) * price),
Date = utilityRecords[i].Date
};
utilityDetails.Add(utilityDetail);
}
}
foreach (List <object> utilities in total.Utilities)
{
foreach (var utilityDetail in utilityDetails)
{
if (utilityDetail.Name == utilities[0].ToString())
{
utilities.Add(utilityDetail.Price);
}
}
}
}
total.Services = new Dictionary <string, int>
{
{ "Nanny", 500 },
{ "Cleaning", 600 }
};
return(total);
}
public static List <UtilityDetail> GetUtilityDetails(Dates dates, int id)
{
List <UtilityRecord> utilityRecords = new List <UtilityRecord>();
List <UtilityDetail> utilityDetails = new List <UtilityDetail>();
using (MySqlConnection connection = new MySqlConnection(ConnectionString.Build()))
{
connection.Open();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = @"SELECT price,value,date,name FROM utility_records as UR
INNER JOIN utilities
ON utility = utility_id
INNER JOIN residents_addresses AS U
ON UR.address = U.address
WHERE U.resident = @id
AND
(YEAR(`date`)=@year AND MONTH(`date`) BETWEEN @month-1 AND @month) OR
(YEAR(`date`)=@year AND MONTH(`date`) BETWEEN @month-1 AND @month)
ORDER BY name";
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@year", dates.Years[0]);
cmd.Parameters.AddWithValue("@month", dates.Months[0]);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
UtilityRecord utilityRecord = new UtilityRecord
{
Value = (int)reader["value"],
Date = Convert.ToDateTime(reader["date"])
};
Utility utility = new Utility
{
Name = reader["name"].ToString(),
Price = (double)reader["price"],
};
utilityRecord.Utility = utility;
utilityRecords.Add(utilityRecord);
}
}
}
int next = 0, current = 0;
for (int i = 0; i < utilityRecords.Count - 1; i++)
{
if (utilityRecords[i].Utility.Name == utilityRecords[i + 1].Utility.Name)
{
double price = utilityRecords[i].Utility.Price;
current = utilityRecords[i].Value;
next = utilityRecords[i + 1].Value;
UtilityDetail utilityDetail = new UtilityDetail
{
Name = utilityRecords[i].Utility.Name,
Price = Convert.ToDouble((next - current) * price),
Date = utilityRecords[i].Date
};
utilityDetails.Add(utilityDetail);
}
}
}
return(utilityDetails);
}