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