Ejemplo n.º 1
0
        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)));
        }
Ejemplo n.º 2
0
        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)));
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
        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);
        }