public static void Update(User user) { using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"UPDATE residents SET phone_number =@phone,email = @email WHERE resident_id = @user_id"; cmd.Parameters.AddWithValue("@email", user.Email); cmd.Parameters.AddWithValue("@phone", user.Phone); cmd.Parameters.AddWithValue("@username", user.Username); cmd.Parameters.AddWithValue("@user_id", user.Id); cmd.ExecuteNonQuery(); } using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"UPDATE billing_residents SET username =@username WHERE resident = @user_id"; cmd.Parameters.AddWithValue("@username", user.Username); cmd.Parameters.AddWithValue("@user_id", user.Id); cmd.ExecuteNonQuery(); } } }
public static void Vote(Vote vote) { using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand command = new MySqlCommand()) { command.Connection = conn; command.CommandText = @"INSERT INTO votes (resident,option_id) VALUES(@user_id,@option_id) "; command.Parameters.AddWithValue("@user_id", vote.UserId); command.Parameters.AddWithValue("@option_id", vote.Option.Id); command.ExecuteNonQuery(); } using (MySqlCommand command = new MySqlCommand()) { command.Connection = conn; command.CommandText = @"UPDATE poll_options SET count =@count WHERE option_id = @option_id"; command.Parameters.AddWithValue("@option_id", vote.Option.Id); command.Parameters.AddWithValue("@count", vote.Option.Count); command.ExecuteNonQuery(); } } }
public static int UserExists(string username, string password) { int id; using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "checkUser"; cmd.Connection = conn; cmd.Parameters.AddWithValue("login", username); cmd.Parameters.AddWithValue("pass", password); cmd.Parameters.Add("result", MySqlDbType.Int32).Direction = ParameterDirection.ReturnValue; cmd.ExecuteNonQuery(); try { id = (int)cmd.Parameters["result"].Value; } catch { return(-1); } } return(id); }
public static int CheckUserByEmail(string email) { int id; using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "checkUserByEmail"; cmd.Connection = conn; cmd.Parameters.AddWithValue("e", email); cmd.Parameters.Add("result", MySqlDbType.Int32).Direction = ParameterDirection.ReturnValue; cmd.ExecuteNonQuery(); try { id = (int)cmd.Parameters["result"].Value; } catch { return(-1); } } return(id); }
public static Billing GetBilling(int id) { Billing billing = new Billing(); using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"SELECT * FROM billings as bi INNER JOIN balances as b ON balance = b.balance_id INNER JOIN residents_addresses as ra ON bi.address = ra.address WHERE resident = @resident"; cmd.Parameters.AddWithValue("@resident", id); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { billing.Payed = (double)reader["payed"]; billing.Remain = (double)reader["remain"]; billing.TotalBilling = (double)reader["total_billing"]; } } return(billing); } } }
public static List <News> GetNews() { List <News> newsList = new List <News>(); using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.CommandText = "SELECT * FROM news"; cmd.Connection = conn; using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { News news = new News() { Id = (int)reader["id"], Title = reader["title"].ToString(), Description = reader["description"].ToString(), Image = reader["image"] is DBNull ? null : Convert.ToString(reader["image"]), Date = (DateTime)reader["date"], }; newsList.Add(news); } } } } return(newsList); }
public static void AddOption(Option option) { option.Count = 0; int optionId, pollId; using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand command = new MySqlCommand()) { command.Connection = conn; command.Parameters.AddWithValue("@name", option.Name); command.Parameters.AddWithValue("@count", option.Count); command.CommandText = "INSERT INTO poll_options (name,count) " + "VALUES(@name,@count)"; command.ExecuteNonQuery(); } GetId(out optionId, out pollId, conn); using (MySqlCommand command = new MySqlCommand()) { command.Connection = conn; command.Parameters.AddWithValue("@poll", pollId); command.Parameters.AddWithValue("@option", optionId); command.CommandText = "INSERT INTO poll_poll_options (poll_poll_id,poll_option_id)" + "VALUES(@poll,@option)"; command.ExecuteNonQuery(); } } }
public static List <Activity> GetActivitiesByDate(Dates dates, int id) { List <Activity> activities = new List <Activity>(); using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"SELECT A.*,Ad.* FROM activities as A INNER JOIN addresses as Ad ON address_id = A.address INNER JOIN residents_addresses as u ON u.address = address_id WHERE year(date) = @yaer AND u.resident = @user_id AND month(date) = @month"; cmd.Parameters.AddWithValue("@yaer", dates.Years[0]); cmd.Parameters.AddWithValue("@month", dates.Months[0]); cmd.Parameters.AddWithValue("@user_id", id); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Activity activity = new Activity(); activity.Id = (int)reader["id"]; activity.ServiceName = reader["service_name"].ToString(); activity.CardNumber = reader["card_number"].ToString(); activity.Fee = Convert.ToDouble(reader["fee"]); activity.Total = Convert.ToDouble(reader["total"]); activity.OrderId = reader["order_id"].ToString(); activity.Rrn = reader["rrn"].ToString(); activity.Customer = reader["customer"].ToString(); activity.CustomerId = (int)reader["customer_id"]; activity.Description = reader["description"].ToString(); activity.Amount = Convert.ToDouble(reader["amount"]); activity.Date = Convert.ToDateTime(reader["date"]); Address address = new Address { Block = reader["block"].ToString(), Number = (int)reader["number"], Street = reader["street"].ToString(), Id = (int)reader["address"], }; activity.Address = address; activities.Add(activity); } } } } return(activities); }
public static PollOption GetPoll(int id) { PollOption poll = new PollOption(); poll.Options = new List <Option>(); using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"SELECT * FROM polls INNER JOIN poll_poll_options ON poll_id = poll_poll_id"; using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { poll.Id = id; poll.Poll = new Poll(); poll.Poll.Id = Convert.ToInt32(reader["poll_id"]); poll.Poll.Title = reader["title"].ToString(); poll.Poll.Description = reader["description"].ToString(); poll.Poll.Date = Convert.ToDateTime(reader["date"]); } } } using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.Parameters.AddWithValue("@id", poll.Poll.Id); cmd.CommandText = @"SELECT * FROM poll_options INNER JOIN poll_poll_options ON option_id = poll_option_id WHERE poll_poll_id = @id"; using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Option option = new Option() { Id = Convert.ToInt32(reader["poll_option_id"]), Name = reader["name"].ToString(), Count = Convert.ToInt32(reader["count"]) }; poll.Options.Add(option); } } } } return(poll); }
public static List <Activity> GetActivity() { List <Activity> activityList = new List <Activity>(); using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.CommandText = @"SELECT * FROM activities INNER JOIN addresses AS a ON address = a.address_id INNER JOIN residents_addresses as ra ON a.address_id = ra.address "; cmd.Connection = conn; using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Activity activity = new Activity(); activity.Id = (int)reader["id"]; activity.ServiceName = reader["service_name"].ToString(); activity.CardNumber = reader["card_number"].ToString(); activity.Fee = Convert.ToDouble(reader["fee"]); activity.Total = Convert.ToDouble(reader["total"]); activity.OrderId = reader["order_id"].ToString(); activity.Rrn = reader["rrn"].ToString(); activity.Customer = reader["customer"].ToString(); activity.Description = reader["description"].ToString(); activity.CustomerId = (int)reader["customer_id"]; activity.Amount = Convert.ToDouble(reader["amount"]); activity.Date = Convert.ToDateTime(reader["date"]); Address address = new Address { Block = reader["block"].ToString(), Number = (int)reader["number"], Street = reader["street"].ToString(), Id = (int)reader["address"], }; activity.Address = address; activityList.Add(activity); } } } } return(activityList); }
public static void AddOrder(string title) { using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"INSERT INTO orders (orderNumber) VALUES(@orderNumber)"; cmd.Parameters.AddWithValue("@orderNumber", title); cmd.ExecuteNonQuery(); } } }
public static void AddCard(string bindingId) { using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand command = new MySqlCommand()) { command.Connection = conn; command.CommandText = "INSERT INTO cards(binding_id)" + "VALUES(@bindingId)"; command.Parameters.AddWithValue("@bindingId", bindingId); command.ExecuteNonQuery(); } } }
public static List <Point> GetUtilityRecordsByDate(Dates dates, int userId) { List <Point> points = new List <Point>(); User user = UserManager.GetUserById(userId); DateTime date1 = new DateTime(dates.Years[0], dates.Months[0], 28); DateTime date2 = new DateTime(dates.Years[1], dates.Months[1], 28); using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"SELECT utility_record_id,date,value,U.address 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 = @user_id AND name = @name AND (YEAR(`date`)=@year1 AND MONTH(`date`) BETWEEN @month1 AND @month2) OR (YEAR(`date`)=@year2 AND MONTH(`date`) BETWEEN @month1 AND @month2) ORDER BY date "; cmd.Parameters.AddWithValue(@"year1", date1.Year); cmd.Parameters.AddWithValue(@"year2", date2.Year); cmd.Parameters.AddWithValue("@month1", date1.Month); cmd.Parameters.AddWithValue("@month2", date2.Month); cmd.Parameters.AddWithValue(@"user_id", userId); cmd.Parameters.AddWithValue(@"name", dates.Name); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { points.Add(new Point { X = reader["date"].ToString(), Y = (int)reader["value"] }); } } } } return(points); }
public static void UpdatePassword(string password, int id) { using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"UPDATE billing_residents SET password = @password WHERE resident = @user_id"; cmd.Parameters.AddWithValue("@password", password); cmd.Parameters.AddWithValue("@user_id", id); cmd.ExecuteNonQuery(); } } }
public static User GetUserById(int id) { User user = new User(); user.Address = new Address(); using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"SELECT * FROM billing_residents INNER JOIN residents as r ON resident = resident_id INNER JOIN residents_addresses as ra ON resident_id = ra.resident INNER JOIN addresses ON address_id = address WHERE resident_id = @id"; cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = id; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { user.Id = Convert.ToInt32(reader["resident_id"]); user.FirstName = reader["FirstName"].ToString(); user.LastName = reader["LastName"].ToString(); user.Username = reader["Username"].ToString(); user.Phone = reader["Phone_number"].ToString(); user.Email = reader["Email"].ToString(); user.Address.Id = (int)reader["address_id"]; user.Address.Block = reader["block"].ToString(); user.Address.Street = reader["street"].ToString(); user.Address.Number = (int)reader["number"]; user.QrCode = reader["qr_code"].ToString(); } } } } return(user); }
public static void AddNews(News news) { using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { news.Date = DateTime.Now; conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.Parameters.AddWithValue("@title", news.Title); cmd.Parameters.AddWithValue("@description", news.Description); cmd.Parameters.AddWithValue("@image", news.Image); cmd.Parameters.AddWithValue("@date", news.Date); cmd.CommandText = $"INSERT INTO news(title,description,image,date)" + $" VALUES(@title,@description,@image,@date)"; cmd.ExecuteNonQuery(); } } }
public static void UpdateCard(List <CardChange> cards) { using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); foreach (var card in cards) { using (MySqlCommand command = new MySqlCommand()) { command.Connection = conn; command.CommandText = @"UPDATE cards SET name = @name WHERE binding_id= @binding_id"; command.Parameters.AddWithValue("@bindingId", card.BindingId); command.Parameters.AddWithValue("@name", card.Name); command.ExecuteNonQuery(); } } } }
public static void AddActivity(OrderStatusModel orderStatus, int id) { Activity activity = new Activity { ServiceName = "Payment", Amount = Convert.ToDouble(orderStatus.Amount) / 100, Fee = 0, Rrn = orderStatus.AuthRefNum, Date = Convert.ToDateTime(orderStatus.Date), CardNumber = orderStatus.CardAuthInfo.Pan, OrderId = orderStatus.OrderNumber, Customer = orderStatus.CardAuthInfo.CardHolderName, Description = "Payment", }; User user = UserManager.GetUserById(id); using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"INSERT INTO activities (service_name,amount,order_id,fee,total,rrn,customer_id,customer,address,description,card_number,date) VALUES(@service_name,@amount,@order_id,@fee,@total,@rrn,@customer_id,@customer,@address,@description,@card_number,@date)"; cmd.Parameters.AddWithValue("@service_name", activity.ServiceName); cmd.Parameters.AddWithValue("@amount", activity.Amount); cmd.Parameters.AddWithValue("order_id", activity.OrderId); cmd.Parameters.AddWithValue("@fee", activity.Fee); cmd.Parameters.AddWithValue("@total", activity.Total); cmd.Parameters.AddWithValue("@rrn", activity.Rrn); cmd.Parameters.AddWithValue("@card_number", activity.CardNumber); cmd.Parameters.AddWithValue("@customer_id", user.Id); cmd.Parameters.AddWithValue("@customer", activity.Customer); cmd.Parameters.AddWithValue("@address", user.Address.Id); cmd.Parameters.AddWithValue("@description", activity.Description); cmd.Parameters.AddWithValue("@date", activity.Date); cmd.ExecuteNonQuery(); } } }
public static void AddPoll(PollOption poll) { poll.Poll.Date = DateTime.Now; using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand command = new MySqlCommand()) { command.Connection = conn; command.CommandText = "INSERT INTO polls(title,description,date)" + "VALUES(@title,@description,@datetime)"; command.Parameters.AddWithValue("@title", poll.Poll.Title); command.Parameters.AddWithValue("@description", poll.Poll.Description); command.Parameters.AddWithValue("@datetime", poll.Poll.Date); command.ExecuteNonQuery(); } foreach (var option in poll.Options) { OptionManager.AddOption(option); } } }
public static List <Dates> GetActivityDates(int id) { List <Dates> dates = new List <Dates>(); using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"SELECT open_date FROM billings INNER JOIN balances ON balance_id = balance INNER JOIN residents_addresses as ra ON billings.address = ra.address WHERE resident = @user_id "; cmd.Parameters.AddWithValue("@user_id", id); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { DateTime date = (DateTime)reader["open_date"]; Dates dates1 = new Dates { Months = new int[] { date.Month }, Years = new int[] { date.Year } }; dates.Add(dates1); } } } } return(dates); }
public static int CheckLastOrder() { int id = 0; using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"SELECT * FROM orders ORDER BY id DESC LIMIT 1"; using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { id = (int)reader["id"]; } } } } return(id); }
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); }
public static List <Point> GetPriceHistory(Dates dates, int userId) { List <Point> points = new List <Point>(); List <UtilityRecord> utilityRecords = new List <UtilityRecord>(); DateTime date1 = new DateTime(dates.Years[0], dates.Months[0], 1); DateTime date2 = new DateTime(dates.Years[1], dates.Months[1], 1); using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"SELECT price,value,date 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 = @user_id AND name=@name AND (YEAR(`date`)=@year1 AND MONTH(`date`) BETWEEN @month1 AND @month2) OR (YEAR(`date`)=@year2 AND MONTH(`date`) BETWEEN @month1 AND @month2) ORDER BY date "; cmd.Parameters.AddWithValue(@"year1", date1.Year); cmd.Parameters.AddWithValue(@"year2", date2.Year); cmd.Parameters.AddWithValue("@month1", date1.Month); cmd.Parameters.AddWithValue("@month2", date2.Month); cmd.Parameters.AddWithValue(@"user_id", userId); cmd.Parameters.AddWithValue(@"name", dates.Name); 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 { Price = Convert.ToDouble(reader["price"]) }; utilityRecord.Utility = utility; utilityRecords.Add(utilityRecord); } } } for (int i = 0; i < utilityRecords.Count - 1; i++) { double price = utilityRecords[i].Utility.Price; int currentRecord = utilityRecords[i].Value; int nextRecord = utilityRecords[i + 1].Value; Point point = new Point { X = utilityRecords[i].Date.ToString(), Y = Convert.ToInt32((nextRecord - currentRecord) * price) }; points.Add(point); } } return(points); }
public static List <PollOption> GetPolls(int userId) { List <PollOption> pollOptions = new List <PollOption>(); List <Option> options = new List <Option>(); using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build())) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"SELECT * FROM polls "; using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { PollOption pollOption = new PollOption { Id = Convert.ToInt32(reader["poll_id"]), Poll = new Poll() { Id = Convert.ToInt32(reader["poll_id"]), Title = reader["title"].ToString(), Description = reader["description"].ToString(), Date = (DateTime)reader["date"] }, Options = new List <Option>(), Selected = new VoteCheck() { OptionId = 0 } }; pollOptions.Add(pollOption); } } } using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"SELECT poll_poll_id,poll_option_id,name,count FROM poll_poll_options RIGHT JOIN poll_options ON option_id = poll_option_id"; cmd.Parameters.AddWithValue("@user_id", userId); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { int id = Convert.ToInt32(reader["poll_poll_id"]); Option option = new Option { Id = Convert.ToInt32(reader["poll_option_id"]), Name = reader["name"].ToString(), Count = Convert.ToInt32(reader["count"]) }; var pollOption = pollOptions.Where(i => i.Id == id).SingleOrDefault(); pollOption.Options.Add(option); } } } using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"SELECT poll_poll_id,poll_option_id,resident,name,count FROM poll_poll_options AS ppo LEFT JOIN votes ON poll_option_id = option_id INNER JOIN poll_options AS o ON o.option_id = ppo.poll_option_id WHERE resident = @user_id"; cmd.Parameters.AddWithValue("@user_id", userId); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { int id = Convert.ToInt32(reader["poll_poll_id"]); int userIdFromDB = Convert.ToInt32(reader["resident"]); var pollOption = pollOptions.Where(i => i.Id == id).SingleOrDefault(); Vote vote = new Vote(); if (userId == userIdFromDB) { pollOption.Selected.Selected = true; pollOption.Selected.OptionId = Convert.ToInt32(reader["poll_option_id"]); } } } } } return(pollOptions); }
public static Report GetReport(Dates dates, int id) { Report report = new Report(); using (MySqlConnection connection = new MySqlConnection(ConnectionString.Build())) { connection.Open(); using (MySqlCommand cmd = new MySqlCommand()) { cmd.Connection = connection; cmd.CommandText = @"SELECT balances.*,billings.*,residents.firstname,residents.lastname,addresses.* FROM billing_residents INNER JOIN residents ON resident = residents.resident_id INNER JOIN residents_addresses as ra ON billing_residents.resident = ra.resident INNER JOIN billings ON billings.address = ra.address INNER JOIN balances ON balances.balance_id = balance INNER JOIN addresses ON ra.address = address_id WHERE resident_id = @id AND year(open_date) = @year AND month(open_date) = @month "; 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()) { report = new Report { Balance = new Balance { Id = (int)reader["balance_id"], Utilities = (double)reader["utilities"], Services = (double)reader["services"], OpenDate = (DateTime)reader["open_date"], CloseDate = (DateTime)reader["close_date"] }, Billing = new Billing { Id = (int)reader["billing_id"], Payed = (double)reader["payed"], Remain = (double)reader["remain"], IsApproved = Convert.ToBoolean((sbyte)reader["is_approved"]), TotalBilling = (double)reader["total_billing"], Address = new Address { Id = (int)reader["address_id"], Street = (string)reader["street"], Number = (int)reader["number"] } }, User = new User { FirstName = reader["firstname"].ToString(), LastName = reader["lastname"].ToString() } }; } } } } return(report); }