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