public List <Case> GetAllCases()
{
var query = $"CALL get_all_cases()";
var conn = SQLFunctions.Connection();
MySqlCommand cmd = new MySqlCommand(query, conn);
conn.Open();
var result = cmd.ExecuteReader();
var resultArray = new List <Case>();
while (result.Read())
{
Nullable <int> customer_user = (result.IsDBNull(4)) ? null : (int?)result.GetInt32("customer_user");
Nullable <float> time_spent = (result.IsDBNull(7)) ? null : (float?)result.GetFloat("time_spent");
resultArray.Add(
new Case
{
id = result.GetInt32("id"),
short_description = result.GetString("short_description"),
description = result.GetString("description"),
customer = result.GetInt32("customer"),
customer_user = customer_user,
case_employee = result.GetInt32("case_employee"),
status = result.GetInt32("status"),
time_spent = time_spent
});
}
conn.Close();
return(resultArray);
}
public List <Customer> GetAllCustomers()
{
var query = $"CALL get_all_customers()";
var conn = SQLFunctions.Connection();
MySqlCommand cmd = new MySqlCommand(query, conn);
conn.Open();
var result = cmd.ExecuteReader();
var resultArray = new List <Customer>();
while (result.Read())
{
Nullable <int> contact_person = (result.IsDBNull(2)) ? null : (int?)result.GetInt32("contact_person");
resultArray.Add(new Customer
{
id = result.GetInt32("id"),
name = result.GetString("name"),
contact_person = contact_person,
phone = result.GetString("phone"),
email = result.GetString("email")
});
}
conn.Close();
return(resultArray);
}
public List <CustomerUser> GetAllCustomerUsers()
{
var query = $"CALL get_all_customer_users()";
var conn = SQLFunctions.Connection();
MySqlCommand cmd = new MySqlCommand(query, conn);
conn.Open();
var result = cmd.ExecuteReader();
var resultArray = new List <CustomerUser>();
while (result.Read())
{
resultArray.Add(new CustomerUser {
id = result.GetInt32("id"), first_name = result.GetString("first_name"), last_name = result.GetString("last_name"), phone = result.GetString("phone"), email = result.GetString("email")
});
}
conn.Close();
return(resultArray);
}
public List <Employee> GetEmployee(int id)
{
var query = $"CALL get_employee({id})";
var conn = SQLFunctions.Connection();
MySqlCommand cmd = new MySqlCommand(query, conn);
conn.Open();
var result = cmd.ExecuteReader();
var resultArray = new List <Employee>();
while (result.Read())
{
resultArray.Add(new Employee {
id = result.GetInt32("id"), first_name = result.GetString("first_name"), last_name = result.GetString("last_name"), phone = result.GetString("phone"), email = result.GetString("email")
});
}
conn.Close();
return(resultArray);
}
public List <Status> GetAllStatus()
{
var query = $"CALL get_all_status()";
var conn = SQLFunctions.Connection();
MySqlCommand cmd = new MySqlCommand(query, conn);
conn.Open();
var result = cmd.ExecuteReader();
var resultArray = new List <Status>();
while (result.Read())
{
resultArray.Add(new Status {
id = result.GetInt32("id"), title = result.GetString("title")
});
}
conn.Close();
return(resultArray);
}
