public static bool ApproveUsers(int id)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string updateStatement =
"UPDATE users SET approved = 1 WHERE id = @id";
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@id", id);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
private static void Insers_Products(List <Order_Products> products, int order_id) //method 2
{
foreach (Order_Products product in products)
{
SqlConnection connection_Products = CompanyDB_Class.GetConnection();
string insertStatement_Products = "INSERT INTO orders_products " +
"( order_id, product_id, amount ) VALUES ( @order_id, @product_id, @amount)";
SqlCommand insertCommand_Products = new SqlCommand(insertStatement_Products, connection_Products);
insertCommand_Products.Parameters.AddWithValue("@order_id", order_id);
insertCommand_Products.Parameters.AddWithValue("@product_id", product.product_id);
insertCommand_Products.Parameters.AddWithValue("@amount", product.ammount);
try
{
connection_Products.Open();
int check = insertCommand_Products.ExecuteNonQuery();
if (check > 0)
{
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection_Products.Close();
}
}
}
private static int Order_Products() //method 1
{
int order_id = 0;
SqlConnection connectionOrderID = CompanyDB_Class.GetConnection();
string selectStatementOrderID = "SELECT MAX(id) as id FROM orders";
SqlCommand selectcommandOrderID = new SqlCommand(selectStatementOrderID, connectionOrderID);
try
{
connectionOrderID.Open();
SqlDataReader order_id_Reader = selectcommandOrderID.ExecuteReader();
if (order_id_Reader.Read())
{
order_id = Convert.ToInt32(order_id_Reader["id"]);
}
order_id_Reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connectionOrderID.Close();
}
return(order_id);
}
public static List <ProductStatusClass> GetProduct_status()
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string selectStatement
= "SELECT id, status "
+ "FROM product_status";
SqlCommand selectcommand =
new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader custReader =
selectcommand.ExecuteReader();
List <ProductStatusClass> status = new List <ProductStatusClass>();
while (custReader.Read())
{
ProductStatusClass productStatus = new ProductStatusClass();
productStatus.id = (int)custReader["id"];
productStatus.status = custReader["status"].ToString();
status.Add(productStatus);
}
custReader.Close();
return(status);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool DeleteUsers(int id)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string deleteStatement =
"DELETE FROM users " +
"WHERE id = @id";
SqlCommand deleteCommand =
new SqlCommand(deleteStatement, connection);
deleteCommand.Parameters.AddWithValue("@id", id);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool AddClient(ClientClass client)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string insertStatement =
"INSERT INTO clients " +
"( first_name, last_name, address, city, country, postal_code, " +
"company, phone, payment, email, created, updated ) " +
"VALUES ( @first_name, @last_name, @address, @city, @country, @postal_code, " +
"@company, @phone, @payment, @email, @created, @updated )";
SqlCommand insertCommand =
new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue(
"@first_name", client.firstNameClient);
insertCommand.Parameters.AddWithValue(
"@last_name", client.lastNameClient);
insertCommand.Parameters.AddWithValue(
"@address", client.addressClient);
insertCommand.Parameters.AddWithValue(
"@city", client.cityClient);
insertCommand.Parameters.AddWithValue(
"@country", client.countryClient);
insertCommand.Parameters.AddWithValue(
"@postal_code", client.postalCodeClient);
insertCommand.Parameters.AddWithValue(
"@company", client.companyClient);
insertCommand.Parameters.AddWithValue(
"@phone", client.phoneClient);
insertCommand.Parameters.AddWithValue(
"@payment", client.paymentClient);
insertCommand.Parameters.AddWithValue(
"@email", client.emailClient);
insertCommand.Parameters.AddWithValue(
"@created", client.createdClient);
insertCommand.Parameters.AddWithValue(
"@updated", client.updatedClient);
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool CreateProfile(UsersClass user)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string insertStatement =
"INSERT INTO Users " +
"( first_name, last_name, email, phone, " +
"password, role, approved, created, updated ) " +
"VALUES ( @firstNameUsers, @lastNameUsers, @emailUsers, @phoneUsers, " +
" @passwordUsers, @roleUsers, @approvedUsers, @createdUsers, @updatedUsers)";
SqlCommand insertCommand =
new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue(
"@firstNameUsers", user.firstNameUsers);
insertCommand.Parameters.AddWithValue(
"@lastNameUsers", user.lastNameUsers);
insertCommand.Parameters.AddWithValue(
"@emailUsers", user.emailUsers);
insertCommand.Parameters.AddWithValue(
"@phoneUsers", user.phoneUsers);
insertCommand.Parameters.AddWithValue(
"@roleUsers", user.roleUsers);
insertCommand.Parameters.AddWithValue(
"@createdUsers", user.createdUsers);
insertCommand.Parameters.AddWithValue(
"@updatedUsers", user.updatedUsers);
insertCommand.Parameters.AddWithValue(
"@approvedUsers", user.approvedUsers);
insertCommand.Parameters.AddWithValue(
"@passwordUsers", user.passwordUsers);
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool ChangeUsers(UsersClass user)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string updateStatement =
"UPDATE users SET " +
"first_name = @first_name, " +
"last_name = @last_name, " +
"email = @email, " +
"phone = @phone, " +
"role = @role, " +
"updated = @updated, " +
"password = @password " +
" WHERE id = @id ";
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue(
"@id", user.userID);
updateCommand.Parameters.AddWithValue(
"@first_name", user.firstNameUsers);
updateCommand.Parameters.AddWithValue(
"@last_name", user.lastNameUsers);
updateCommand.Parameters.AddWithValue(
"@email", user.emailUsers);
updateCommand.Parameters.AddWithValue(
"@phone", user.phoneUsers);
updateCommand.Parameters.AddWithValue(
"@role", user.roleUsers);
updateCommand.Parameters.AddWithValue(
"@updated", user.updatedUsers);
updateCommand.Parameters.AddWithValue(
"@password", user.passwordUsers);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <ClientClass> SearchClient(string search)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string selectStatement
= "SELECT id, first_name, last_name, address, city, country, postal_code, " +
"company, phone, payment, email, created, updated "
+ "FROM clients "
+ "WHERE last_name = @last_name" +
" OR first_name = @first_name" +
" OR email = @email";
SqlCommand selectcommand =
new SqlCommand(selectStatement, connection);
selectcommand.Parameters.AddWithValue("@last_name", search);
selectcommand.Parameters.AddWithValue("@first_name", search);
selectcommand.Parameters.AddWithValue("@email", search);
try
{
connection.Open();
SqlDataReader custReader =
selectcommand.ExecuteReader();
List <ClientClass> clients = new List <ClientClass>();
while (custReader.Read())
{
ClientClass client = new ClientClass();
client.idClient = (int)custReader["id"];
client.firstNameClient = custReader["first_name"].ToString();
client.lastNameClient = custReader["last_name"].ToString();
client.addressClient = custReader["address"].ToString();
client.cityClient = custReader["city"].ToString();
client.countryClient = custReader["country"].ToString();
client.postalCodeClient = custReader["postal_code"].ToString();
client.companyClient = custReader["company"].ToString();
client.phoneClient = custReader["phone"].ToString();
client.paymentClient = custReader["payment"].ToString();
client.emailClient = custReader["email"].ToString();
client.updatedClient = Convert.ToDateTime(custReader["updated"]);
client.createdClient = Convert.ToDateTime(custReader["created"]);
clients.Add(client);
}
custReader.Close();
return(clients);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static UsersClass GetUser(string userEmail)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string selectStatement
= "SELECT id, first_name, last_name, email, phone, " +
"password, role, approved, created, updated "
+ "FROM users "
+ "WHERE email = @userEmail" +
" AND role = 'user'" +
" AND approved = '1'";
SqlCommand selectcommand =
new SqlCommand(selectStatement, connection);
selectcommand.Parameters.AddWithValue("@userEmail", userEmail);
try
{
connection.Open();
SqlDataReader custReader =
selectcommand.ExecuteReader(CommandBehavior.SingleRow);
if (custReader.Read())
{
UsersClass user = new UsersClass();
user.userID = (int)custReader["id"];
user.firstNameUsers = custReader["first_name"].ToString();
user.lastNameUsers = custReader["last_name"].ToString();
user.emailUsers = custReader["email"].ToString();
user.phoneUsers = custReader["phone"].ToString();
user.roleUsers = custReader["role"].ToString();
user.createdUsers = Convert.ToDateTime(custReader["created"]);
user.updatedUsers = Convert.ToDateTime(custReader["updated"]);
user.approvedUsers = custReader["approved"].ToString();
user.passwordUsers = custReader["password"].ToString();
return(user);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool ModifyProduct(ProductClass product)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string updateStatement =
"UPDATE products SET " +
"name = @name, " +
"description = @description, " +
"updated = @updated, " +
"cost = @cost, " +
"status = @status " +
" WHERE id = @id ";
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue(
"@id", product.idProduct);
updateCommand.Parameters.AddWithValue(
"@name", product.nameProduct);
updateCommand.Parameters.AddWithValue(
"@description", product.descriptionProduct);
updateCommand.Parameters.AddWithValue(
"@updated", product.updatedProduct);
updateCommand.Parameters.AddWithValue(
"@cost", product.costProduct);
updateCommand.Parameters.AddWithValue(
"@status", product.idStatusProduct);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <UsersClass> GetUsers()
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string selectStatement
= "SELECT id, first_name, last_name, email, phone, " +
"password, role, approved, created, updated "
+ "FROM users";
SqlCommand selectcommand =
new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader custReader =
selectcommand.ExecuteReader();
List <UsersClass> users = new List <UsersClass>();
while (custReader.Read())
{
UsersClass user = new UsersClass();
user.userID = (int)custReader["id"];
user.firstNameUsers = custReader["first_name"].ToString();
user.lastNameUsers = custReader["last_name"].ToString();
user.emailUsers = custReader["email"].ToString();
user.phoneUsers = custReader["phone"].ToString();
user.roleUsers = custReader["role"].ToString();
user.createdUsers = Convert.ToDateTime(custReader["created"]);
user.updatedUsers = Convert.ToDateTime(custReader["updated"]);
user.approvedUsers = custReader["approved"].ToString();
user.passwordUsers = custReader["password"].ToString();
users.Add(user);
}
custReader.Close();
return(users);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool AddOrder(OrderClass order, List <Order_Products> products)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string insertStatement =
"INSERT INTO orders " +
"( date, subtotal, tax, total, status_order_id, client_id) " +
"VALUES ( @date, @subtotal, @tax, @total, @status_order_id, @client_id)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@date", order.dateOrder);
insertCommand.Parameters.AddWithValue("@subtotal", order.subtotalOrders);
insertCommand.Parameters.AddWithValue("@tax", order.taxOrder);
insertCommand.Parameters.AddWithValue("@total", order.totalOrder);
insertCommand.Parameters.AddWithValue("@status_order_id", order.statusOrderId);
insertCommand.Parameters.AddWithValue("@client_id", order.client_id);
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count > 0)
{
connection.Close();
int order_id = Order_Products(); // method 1
Insers_Products(products, order_id); // method 2
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool AddProduct(ProductClass product)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string insertStatement =
"INSERT INTO products " +
"( name, description, created, updated, cost, status ) " +
"VALUES ( @name, @description, @created, @updated, @cost, @status )";
SqlCommand insertCommand =
new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue(
"@name", product.nameProduct);
insertCommand.Parameters.AddWithValue(
"@description", product.descriptionProduct);
insertCommand.Parameters.AddWithValue(
"@created", product.createdProduct);
insertCommand.Parameters.AddWithValue(
"@updated", product.updatedProduct);
insertCommand.Parameters.AddWithValue(
"@cost", product.costProduct);
insertCommand.Parameters.AddWithValue(
"@status", product.idStatusProduct);
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <ProductClass> SearchProduct(string search) ////////////////////////////
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string selectStatement
= "SELECT id, name, description, created, updated, cost, status "
+ "FROM products "
+ "WHERE name = @name" +
" OR cost = @cost";
SqlCommand selectcommand =
new SqlCommand(selectStatement, connection);
selectcommand.Parameters.AddWithValue("@name", search);
selectcommand.Parameters.AddWithValue("@cost", search);
try
{
connection.Open();
SqlDataReader custReader =
selectcommand.ExecuteReader();
List <ProductClass> products = new List <ProductClass>();
while (custReader.Read())
{
ProductClass product = new ProductClass();
product.idProduct = (int)custReader["id"];
product.nameProduct = custReader["name"].ToString();
product.descriptionProduct = custReader["description"].ToString();
product.updatedProduct = Convert.ToDateTime(custReader["updated"]);
product.createdProduct = Convert.ToDateTime(custReader["created"]);
product.costProduct = Convert.ToDouble(custReader["cost"]);
product.idStatusProduct = Convert.ToInt32(custReader["status"]);
products.Add(product);
}
custReader.Close();
return(products);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <OrderClass> GetOrders()
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string selectStatement
= "SELECT id, date, subtotal, tax, total, status_order_id, client_id "
+ "FROM orders";
SqlCommand selectcommand =
new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader custReader =
selectcommand.ExecuteReader();
List <OrderClass> orders = new List <OrderClass>();
while (custReader.Read())
{
OrderClass order = new OrderClass();
order.idOrders = (int)custReader["id"];
order.dateOrder = Convert.ToDateTime(custReader["date"]);
order.subtotalOrders = Convert.ToDecimal(custReader["subtotal"]);
order.taxOrder = Convert.ToDecimal(custReader["tax"]);
order.totalOrder = Convert.ToDecimal(custReader["total"]);
order.statusOrderId = Convert.ToInt32(custReader["status_order_id"]);
order.client_id = Convert.ToInt32(custReader["client_id"]);
orders.Add(order);
}
custReader.Close();
return(orders);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <ProductClass> GetProductByID(int order_id)
{
List <ProductClass> products = new List <ProductClass>();
SqlConnection connection = CompanyDB_Class.GetConnection();
string selectStatement = "SELECT products.name, products.description, products.cost, orders_products.amount "
+ " FROM products "
+ " INNER JOIN orders_products ON orders_products.product_id = products.id "
+ " WHERE order_id = @order_id";
SqlCommand selectcommand = new SqlCommand(selectStatement, connection);
selectcommand.Parameters.AddWithValue("@order_id", order_id);
try
{
connection.Open();
SqlDataReader custReader = selectcommand.ExecuteReader();
while (custReader.Read())
{
ProductClass product = new ProductClass();
product.nameProduct = custReader["name"].ToString();
product.descriptionProduct = custReader["description"].ToString();
product.costProduct = Convert.ToDouble(custReader["cost"]);
product.ammountProduct = Convert.ToInt32(custReader["amount"]);
products.Add(product);
}
return(products);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool ChangeStatusOrder(OrderClass order)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string updateStatement =
"UPDATE orders SET " +
"status_order_id = @status_order_id " +
" WHERE id = @id ";
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue(
"@id", order.idOrders);
updateCommand.Parameters.AddWithValue(
"@status_order_id", order.statusOrderId);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <ProductClass> GetProductsName()
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string selectStatement
= "SELECT id, name, cost "
+ "FROM products";
SqlCommand selectcommand =
new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader custReader =
selectcommand.ExecuteReader();
List <ProductClass> products = new List <ProductClass>();
while (custReader.Read())
{
ProductClass product = new ProductClass();
product.idProduct = (int)custReader["id"];
product.nameProduct = custReader["name"].ToString();
product.costProduct = Convert.ToDouble(custReader["cost"]);
products.Add(product);
}
custReader.Close();
return(products);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool ModifyClient(ClientClass client)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string updateStatement =
"UPDATE clients SET " +
"first_name = @first_name, " +
"last_name = @last_name, " +
"address = @address, " +
"city = @city, " +
"country = @country, " +
"postal_code = @postal_code, " +
"company = @company, " +
"phone = @phone, " +
"payment = @payment, " +
"email = @email, " +
"created = @created, " +
"updated = @updated " +
" WHERE id = @id ";
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue(
"@id", client.idClient);
updateCommand.Parameters.AddWithValue(
"@first_name", client.firstNameClient);
updateCommand.Parameters.AddWithValue(
"@last_name", client.lastNameClient);
updateCommand.Parameters.AddWithValue(
"@address", client.addressClient);
updateCommand.Parameters.AddWithValue(
"@city", client.cityClient);
updateCommand.Parameters.AddWithValue(
"@country", client.countryClient);
updateCommand.Parameters.AddWithValue(
"@postal_code", client.postalCodeClient);
updateCommand.Parameters.AddWithValue(
"@company", client.companyClient);
updateCommand.Parameters.AddWithValue(
"@phone", client.phoneClient);
updateCommand.Parameters.AddWithValue(
"@payment", client.paymentClient);
updateCommand.Parameters.AddWithValue(
"@email", client.emailClient);
updateCommand.Parameters.AddWithValue(
"@created", client.createdClient);
updateCommand.Parameters.AddWithValue(
"@updated", client.updatedClient);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}