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