public static Administrator GetByUserId(long userId)
{
DBconnection dbConnection = new DBconnection();
dbConnection.OpenConnection();
Administrator administrator;
string query = $"SELECT * FROM administrators WHERE user_id = @userId";
using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection))
{
cmd.Parameters.AddWithValue("@userId", userId);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
long id = Convert.ToInt64(reader["id"]);
User user = User.GetById(Convert.ToInt64(reader["user_id"]));
administrator = new Administrator(id, user);
dbConnection.CloseConnection();
return(administrator);
}
}
dbConnection.CloseConnection();
return(null);
}
public void CarregarGV()
{
if (con.OpenConnection() == true)
{
try
{
string query = "SELECT r.id, r.startDate, r.endDate, r.STATUS, u.NAME," +
" s.address AS destination, s1.address AS origin FROM rent r" +
" LEFT join user u ON u.id = r.userId" +
" left join station s ON s.id = r.destinationId" +
" left join station s1 ON s1.id = r.startStationId WHERE u.id =" + id +
" order by r.id desc";
MySqlCommand cmd = new MySqlCommand(query, con.getConnection());
DataTable dataTable = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dataTable);
GridView1.DataSource = dataTable;
GridView1.DataBind();
con.CloseConnection();
}
catch
{ }
}
else
{
Console.WriteLine("Connection Failed");
}
}
public static Department GetById(long id)
{
DBconnection dbConnection = new DBconnection();
dbConnection.OpenConnection();
string query = $"SELECT * FROM departments WHERE id = @id LIMIT 1";
using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection))
{
cmd.Parameters.AddWithValue("@id", id);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string name = reader["name"].ToString();
int colIndex = reader.GetOrdinal("supervisor_id");
long supervisorId = 0;
if (!reader.IsDBNull(colIndex))
{
supervisorId = Convert.ToInt64(reader["supervisor_id"]);
}
dbConnection.CloseConnection();
return(new Department(id, name, supervisorId));
}
}
dbConnection.CloseConnection();
return(null);
}
public static List <Department> GetAll()
{
DBconnection dbConnection = new DBconnection();
dbConnection.OpenConnection();
List <Department> departments = new List <Department>();
string query = "SELECT * FROM departments";
using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection))
{
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
long id = Convert.ToInt64(reader["id"]);
string name = reader["name"].ToString();
long supervisorId = 0;
int colIndex = reader.GetOrdinal("supervisor_id");
if (!reader.IsDBNull(colIndex))
{
supervisorId = Convert.ToInt64(reader["supervisor_id"]);
}
departments.Add(new Department(id, name, supervisorId));
}
}
dbConnection.CloseConnection();
return(departments);
}
public void Clubs()
{
labelText.Text = "Club";
Type = "Club";
string query = "select clubs.name from clubs order by clubs.id";
if (_connection.OpenConnection())
{
cbItems.Items.Add("");
using (var cmd = new MySqlCommand(query, DBconnection.Instance.Connection))
{
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
cbItems.Items.Add(dataReader["name"].ToString());
}
}
_connection.CloseConnection();
}
else
{
return;
}
}
public static Dictionary <string, int> GetSoldQuantity()
{
DBconnection dbConnection = new DBconnection();
dbConnection.OpenConnection();
Dictionary <string, int> productNameSoldQuantity = new Dictionary <string, int>();
string query =
@"
SELECT SUM(od.quantity) as quantity, p.name as name
FROM orders as o
INNER JOIN order_details as od
ON o.id = od.order_id
INNER JOIN products as p
ON od.product_id = p.id
GROUP BY od.product_id;
";
using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection))
{
//cmd.Parameters.AddWithValue("@productId", productId);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
productNameSoldQuantity.Add(reader["name"].ToString(), Convert.ToInt32(reader["quantity"]));
}
dbConnection.CloseConnection();
return(productNameSoldQuantity);
}
}
public static Dictionary <string, int> GetProductRevenue()
{
DBconnection dbConnection = new DBconnection();
dbConnection.OpenConnection();
Dictionary <string, int> productNamerevenue = new Dictionary <string, int>();
string query =
@"
SELECT p.name, od.quantity*(p.selling_price-p.buying_price) as revenue
FROM `order_details` as od
INNER JOIN products as p
ON od.product_id = p.id
GROUP BY(p.id)
";
using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection))
{
//cmd.Parameters.AddWithValue("@productId", productId);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
productNamerevenue.Add(reader["name"].ToString(), Convert.ToInt32(reader["revenue"]));
}
dbConnection.CloseConnection();
return(productNamerevenue);
}
}
public static Department GetByName(string name)
{
DBconnection dbConnection = new DBconnection();
dbConnection.OpenConnection();
string query = $"SELECT * FROM departments WHERE name = @name";
using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection))
{
var nameParam = cmd.Parameters.AddWithValue("@name", name);
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
long id = Convert.ToInt64(dataReader["id"]);
long supervisorId = 0;
if (dataReader["supervisor_id"] != DBNull.Value)
{
supervisorId = Convert.ToInt64(dataReader["supervisor_id"]);
}
return(new Department(id, name, supervisorId));
}
}
dbConnection.CloseConnection();
return(null);
}
public static Dictionary <string, int> GetProductsByDepartment()
{
DBconnection dbConnection = new DBconnection();
dbConnection.OpenConnection();
Dictionary <string, int> result = new Dictionary <string, int>();
string query =
@"
SELECT COUNT(*) as num, d.Name as name
FROM products as p
INNER JOIN departments as d
ON p.Department_id = d.Id
GROUP BY d.Name;
";
using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection))
{
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
result.Add(reader["name"].ToString(), Convert.ToInt32(reader["num"]));
}
}
dbConnection.CloseConnection();
return(result);
}
public static List <StockRequest> GetAllPendingRequests()
{
DBconnection dbConnection = new DBconnection();
dbConnection.OpenConnection();
List <StockRequest> stockRequests = new List <StockRequest>();
string query = "SELECT id, quantity, completed, product_id, user_id FROM stock_requests;";
using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection))
{
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = Convert.ToInt32(reader["id"]);
int quantity = Convert.ToInt32(reader["quantity"]);
int completed = Convert.ToInt32(reader["completed"]);
int product_id = Convert.ToInt32(reader["product_id"]);
int user_id = Convert.ToInt32(reader["user_id"]);
if (completed == 0)
{
stockRequests.Add(new StockRequest(id, quantity, completed, product_id, user_id));
}
}
dbConnection.CloseConnection();
return(stockRequests);
}
}
public static List <LeaveRequest> GetAll()
{
DBconnection dbConnection = new DBconnection();
dbConnection.OpenConnection();
List <LeaveRequest> requests = new List <LeaveRequest>();
string query = "SELECT * FROM leaves WHERE pending = 1";
using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection))
{
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
long id = Convert.ToInt64(reader["id"]);
int workerId = Convert.ToInt32(reader["worker_id"]);
int accepted = Convert.ToInt32(reader["approved"]);
int pending = Convert.ToInt32(reader["pending"]);
DateTime from = DateTime.Parse(reader["from"].ToString());
DateTime to = DateTime.Parse(reader["to"].ToString());
string description = reader["description"].ToString();
requests.Add(new LeaveRequest(id, workerId, from, to, accepted, pending, description));
}
}
dbConnection.CloseConnection();
return(requests);
}
ICollection CreateDataSource()
{
DataView dv = null;
// Create a table to store data for the DropDownList control.
DataTable dt = new DataTable();
// Define the columns of the table.
dt.Columns.Add(new DataColumn("StationTextField", typeof(String)));
dt.Columns.Add(new DataColumn("StationValueField", typeof(String)));
// Populate the table with sample values.
try
{
MySqlDataReader myReader = con.listStations();
while (myReader.Read())
{
int id = Convert.ToInt32(myReader["id"]);
string address = Convert.ToString(myReader["address"]);
int numOfBikes = Convert.ToInt32(myReader["numOfBikes"]);
dt.Rows.Add(CreateRow(" ____" + address + "___ Nº of bikes available: " + numOfBikes, id.ToString(), dt));
}
con.CloseConnection();
// Create a DataView from the DataTable to act as the data source
// for the DropDownList control.
dv = new DataView(dt);
return(dv);
}
catch (Exception ex)
{
Console.WriteLine(ex);
return(dv);
}
}
public static Product GetById(int productId)
{
DBconnection dbConnection = new DBconnection();
dbConnection.OpenConnection();
Product product;
string query =
@"
SELECT products.id AS p_id, products.name AS p_name, description, buying_price, selling_price, quantity, min_quantity, departments.name AS d_name
FROM products
INNER JOIN departments
ON products.department_id = departments.id
WHERE products.id = @id
";
using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection))
{
cmd.Parameters.AddWithValue("@id", productId);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
long id = Convert.ToInt64(reader["p_id"]);
string name = reader["p_name"].ToString();
string description = reader["description"].ToString();
double buyingPrice = Convert.ToDouble(reader["buying_price"]);
double sellingPrice = Convert.ToDouble(reader["selling_price"]);
int quantity = Convert.ToInt32(reader["quantity"]);
int minQuantity = Convert.ToInt32(reader["min_quantity"]);
string department = reader["d_name"].ToString();
product = new Product(id, name, description, buyingPrice, sellingPrice, quantity, minQuantity, department);
dbConnection.CloseConnection();
return(product);
}
dbConnection.CloseConnection();
return(null);
}
}
// funkcja która zwraca pojedyńczy obiekt do wyświetlania dwuklikiem
public object GetItem(int id, string table)
{
if (_connection.OpenConnection())
{
Dictionary <string, object> par = new Dictionary <string, object>()
{
{ "@id", id }
};
if (table == "Players")
{
object result = _connection.GetPlayer($"select players.id ID, players.name Firstname, lastname Lastname," +
$"dateofbirth Dateofbirth, position Position, height Height, weight Weight, nationality Nationality, clubs.name Club" +
$" from players, clubs where players.club = clubs.id and players.id=@id", par);
_connection.CloseConnection();
return(result);
}
else if (table == "Clubs")
{
object result = _connection.GetClub($"select clubs.id ID, clubs.name Club, clubs.city City," +
$" clubs.founded Founded, clubs.active League from clubs where clubs.id=@id", par);
_connection.CloseConnection();
return(result);
}
else if (table == "Kits")
{
object result = _connection.GetKit($"select kits.id ID, kits.home Homekit, kits.away Awaykit," +
$"kits.clubcolours Clubcolours, clubs.name Club from kits, clubs where kits.club = clubs.id and kits.id=@id", par);
_connection.CloseConnection();
return(result);
}
else if (table == "Coaches")
{
object result = _connection.GetCoach($"select coaches.id ID, coaches.name Firstname, coaches.lastname Lastname," +
$"coaches.dateofbirth Dateofbirth, coaches.nationality Nationality, clubs.name Club from coaches, clubs where coaches.club = clubs.id and coaches.id=@id", par);
_connection.CloseConnection();
return(result);
}
else if (table == "Stadiums")
{
object result = _connection.GetStadium($"SELECT stadiums.id ID, stadiums.name Name, stadiums.city City, " +
$"stadiums.capacity Capacity, stadiums.buildyear YearOfBuilt, clubs.name Club FROM stadiums " +
$"JOIN clubs_has_stadiums JOIN clubs WHERE clubs_id_clubs = clubs.id " +
$"AND stadiums_id_stadiums = stadiums.id and stadiums.id = @id ", par);
_connection.CloseConnection();
return(result);
}
return(new object());
}
else
{
return(new object());
}
}
public void CompleteStockRequest()
{
DBconnection dbConnection = new DBconnection();
dbConnection.OpenConnection();
string query = "UPDATE stock_requests SET completed = 1 WHERE id = @id;";
using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection))
{
cmd.Parameters.AddWithValue("@id", this.Id);
cmd.ExecuteNonQuery();
}
dbConnection.CloseConnection();
}
public static long GetTheNewestDepartmentId()
{
DBconnection dbConnection = new DBconnection();
dbConnection.OpenConnection();
long Id = 0;
string query = "SELECT id FROM `departments` ORDER BY ID DESC LIMIT 1";
using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection))
{
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Id = Convert.ToInt64(reader["id"]);
}
}
dbConnection.CloseConnection();
return(Id);
}
public void checkCredentials()
{
Label1.Visible = false;
Label1.Text = "";
string login = TextBox1.Text;
string password = TextBox2.Text;
if (con.OpenConnection() == true)
{
try
{
string query = "SELECT id, name FROM user WHERE login ='******' AND PASSWORD ='******'";
MySqlCommand cmd = new MySqlCommand(query, con.getConnection());
MySqlDataReader myReader;
myReader = cmd.ExecuteReader();
int id = -1;
string name = "";
while (myReader.Read())
{
name = Convert.ToString(myReader["name"]);
id = Convert.ToInt32(myReader["id"]);
}
con.CloseConnection();
if (id != -1)
{
Response.Redirect("userForm.aspx?id=" + id + "&name=" + name, false);
}
else
{
Label1.Visible = true;
Label1.Text = "Something wrong. User name not found or incorrect password.";
}
}catch {}
}
else
{
Console.WriteLine("Connection Failed");
}
}
public void Update(Model obj, bool isApproved)
{
DBconnection dbConnection = new DBconnection();
dbConnection.OpenConnection();
LeaveRequest request = (LeaveRequest)obj;
string query;
if (isApproved)
{
query = $"UPDATE leaves SET pending = 0 ,approved= 1 WHERE id ={request.Id}";
}
else
{
query = $"UPDATE leaves SET pending = 0 ,approved= 0 WHERE id ={request.Id}";
}
using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection))
{
cmd.ExecuteNonQuery();
}
dbConnection.CloseConnection();
}
public static List <Product> GetAll()
{
DBconnection dbConnection = new DBconnection();
dbConnection.OpenConnection();
List <Product> products = new List <Product>();
string query =
@"
SELECT products.id AS p_id, products.name AS p_name, description, buying_price, selling_price, quantity, min_quantity, departments.name AS d_name
FROM products
INNER JOIN departments
ON products.department_id = departments.id
";
using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection))
{
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
long id = Convert.ToInt64(reader["p_id"]);
string name = reader["p_name"].ToString();
string description = reader["description"].ToString();
double buyingPrice = Convert.ToDouble(reader["buying_price"]);
double sellingPrice = Convert.ToDouble(reader["selling_price"]);
int quantity = Convert.ToInt32(reader["quantity"]);
int minQuantity = Convert.ToInt32(reader["min_quantity"]);
string department = reader["d_name"].ToString();
products.Add(new Product(id, name, description, buyingPrice, sellingPrice, quantity, minQuantity, department));
}
}
dbConnection.CloseConnection();
return(products);
}
