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 static bool CreateDBTablesAndProcedures()
{
string file = Directory.GetParent(Environment.CurrentDirectory).Parent.FullName + "\\../initialDatabaseSetup.txt";
var query = File.ReadAllText(file);
return(SQLFunctions.ExecuteNonQuery(query));
}
public static bool ImportCustomers()
{
string file = Directory.GetParent(Environment.CurrentDirectory).Parent.FullName + "\\../companyData.csv";
var data = File.ReadAllText(file);
int i = 0;
foreach (string row in data.Split('\n'))
{
if (!string.IsNullOrEmpty(row))
{
string[] customer = row.Split(',');
string query = $"INSERT INTO customers (name,phone,email) VALUES ('{customer[0]}','{customer[1]}','{customer[2]}')";
Console.WriteLine(query);
SQLFunctions.ExecuteNonQuery(query);
i++;
}
}
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
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 <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);
}
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 static bool InsertDefaultStatus()
{
string[] data = { "Received", "Open", "In progress", "Awaiting Customer", "Awaiting Approval", "On Hold", "Closed" };
int i = 0;
foreach (string status in data)
{
Console.WriteLine($"Inserted \"{status}\" into Status table");
string query = $"INSERT INTO status (title) VALUES ('{status}')";
SQLFunctions.ExecuteNonQuery(query);
i++;
}
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public static bool ImportCases()
{
string file = Directory.GetParent(Environment.CurrentDirectory).Parent.FullName + "\\../caseData.csv";
var data = File.ReadAllText(file);
int i = 0;
foreach (string row in data.Split('\n'))
{
if (!string.IsNullOrEmpty(row))
{
string[] import_case = row.Split('*');
string query = $"INSERT INTO cases (short_description,description,customer,customer_user,case_employee,status) VALUES ('{import_case[0]}','{import_case[1]}',(SELECT customer FROM customer_users WHERE id={import_case[2]}),{import_case[2]},{import_case[3]},{import_case[4]})";
SQLFunctions.ExecuteNonQuery(query);
}
}
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool CreateCase(string short_description, string description, int customer, int customer_user, int case_employee, int status)
{
var query = $"CALL create_case('{short_description}','{description}',{customer},{customer_user},{case_employee},{status})";
return(SQLFunctions.ExecuteNonQuery(query));
}
public bool UpdateCase(int id, string short_description, string description, int customer, int customer_user, int case_employee, int status, float time_spent, string hidden_information)
{
var query = $"CALL update_case({id},'{short_description}','{description}',{customer},{customer_user},{case_employee},{status},{time_spent},'{hidden_information}')";
return(SQLFunctions.ExecuteNonQuery(query));
}
public bool DeleteCase(int id)
{
var query = $"CALL delete_case({id})";
return(SQLFunctions.ExecuteNonQuery(query));
}
public bool DeleteCustomerUser(int id)
{
var query = $"CALL delete_customer_user({id})";
return(SQLFunctions.ExecuteNonQuery(query));
}
public bool CreateCustomerUser(string fname, string lname, int company, string phone, string email)
{
var query = $"CALL create_customer_user('{fname}','{lname}',{company},'{phone}','{email}')";
return(SQLFunctions.ExecuteNonQuery(query));
}
public bool UpdateCustomer(int id, string name, Nullable <int> contact_person, string phone, string email)
{
var query = $"CALL update_customer({id},'{name}',{contact_person},'{phone}','{email}')";
return(SQLFunctions.ExecuteNonQuery(query));
}
public bool UpdateCustomerUser(int id, string fname, string lname, string phone, string email)
{
var query = $"CALL update_customer_user({id},'{fname}','{lname}','{phone}','{email}')";
return(SQLFunctions.ExecuteNonQuery(query));
}
public bool CreateEmployee(string fname, string lname, string phone, string email)
{
var query = $"CALL create_employee('{fname}','{lname}','{phone}','{email}')";
return(SQLFunctions.ExecuteNonQuery(query));
}
public bool UpdateEmployee(int id, string fname, string lname, string phone, string email)
{
var query = $"CALL update_employee({id},'{fname}','{lname}','{phone}','{email}')";
return(SQLFunctions.ExecuteNonQuery(query));
}
public bool UpdateCustomerContactPerson(int id, int contact_person)
{
var query = $"CALL update_customer_contact_person({id},{contact_person})";
return(SQLFunctions.ExecuteNonQuery(query));
}
public bool CreateStatus(string title)
{
var query = $"CALL create_status('{title}')";
return(SQLFunctions.ExecuteNonQuery(query));
}
public bool CreateCustomer(string name, string phone, string email)
{
var query = $"CALL create_customer('{name}','{phone}','{email}')";
return(SQLFunctions.ExecuteNonQuery(query));
}