//Class Methods
/// <summary>
/// Fills the class with Address information from database using addressId
/// </summary>
/// <param name="existingAddressId"></param>
/// <param name="conn"></param>
private void setDatabaseAddressInfo(int existingAddressId, MySqlConnection conn)
{
using (conn)
{
string myQuery = "SELECT * FROM address WHERE addressId = " + existingAddressId.ToString();
MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myQuery);
while (reader.Read())
{
addressId = (int)reader["addressId"];
address = reader["address"].ToString();
address2 = reader["address2"].ToString();
cityId = (int)reader["cityId"];
postalCode = reader["postalCode"].ToString();
phone = reader["phone"].ToString();
//createDate = (DateTime)reader["createDate"];
createdBy = reader["createdBy"].ToString();
//lastUpdate = (DateTime)reader["lastUpdate"];
lastUpdateBy = reader["lastUpdateBy"].ToString();
}
conn.Close();
if (addressId != existingAddressId)
{
throw new Exception("Address ID wasn't found");
}
cityObject = new City(cityId, loggedInUser, conn);
}
}
//Class Methods
/// <summary>
/// Finds a city by the city ID, and fills an object with the information from the database
/// </summary>
/// <param name="CityId"></param>
/// <param name="CreatedBy"></param>
/// <param name="conn"></param>
private void setDatabaseCityInfo(int CityId, string CreatedBy, MySqlConnection conn)
{
using (conn)
{
string myExecuteQuery = "SELECT * FROM city WHERE cityId = " + CityId.ToString();
MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myExecuteQuery);
while (reader.Read())
{
cityId = (int)reader["cityId"];
cityName = reader["city"].ToString();
countryId = (int)reader["countryId"];
createDate = (DateTime)reader["createDate"];
createdBy = reader["createdBy"].ToString();
lastUpdate = (DateTime)reader["lastUpdate"];
lastUpdateBy = reader["lastUpdateBy"].ToString();
}
conn.Close();
if (cityId != CityId)
{
throw new Exception("City ID wasn't found");
}
countryObject = new Country(countryId, conn);
}
}
// Class Methods
/// <summary>
/// Sets the Customer Object with Database info
/// </summary>
/// <param name="existingCustomerId"></param>
/// <param name="conn"></param>
private void setDatabaseCustomerInfo(int existingCustomerId, MySqlConnection conn)
{
using (conn)
{
string myExecuteQuery = "SELECT * FROM customer WHERE customerId = " + existingCustomerId.ToString();
MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myExecuteQuery);
while (reader.Read())
{
customerID = (int)reader["customerId"];
customerName = reader["customerName"].ToString();
addressID = (int)reader["addressId"];
bool activeCheck = (bool)reader["active"];
if (activeCheck == true)
{
active = 1;
}
else
{
active = 0;
}
createdBy = reader["createdBy"].ToString();
lastUpdateBy = reader["lastUpdateBy"].ToString();
}
conn.Close();
if (customerID != existingCustomerId)
{
throw new Exception("Customer ID wasn't found");
}
addressObject = new Address(addressID, conn);
}
}
/// <summary>
/// Constructor to Add Customer to Database
/// </summary>
/// <param name="CustomerId"></param>
/// <param name="UserId"></param>
/// <param name="Title"></param>
/// <param name="Description"></param>
/// <param name="Location"></param>
/// <param name="Contact"></param>
/// <param name="Type"></param>
/// <param name="Url"></param>
/// <param name="StartTime"></param>
/// <param name="EndTime"></param>
/// <param name="CreatedBy"></param>
/// <param name="UpdatedBy"></param>
/// <param name="AppointmentType"></param>
public Appointment(int CustomerId, int UserId, string Title, string Description, string Location, string Contact, string Url, DateTime StartTime, DateTime EndTime, string CreatedBy, string UpdatedBy, string AppointmentType)
{
using (var conn = new MySqlConnection(connecterString))
{
string findHighestAppointmentId = "SELECT MAX(appointmentId) FROM appointment";
var reader = DataChanges.GetMySqlReader(conn, findHighestAppointmentId);
while (reader.Read())
{
try
{
highestAppointmentId = (int)reader["MAX(appointmentId)"];
}
catch
{
highestAppointmentId = 0;
}
}
conn.Close();
type = AppointmentType;
highestAppointmentId++;
appointmentId = highestAppointmentId;
customerId = CustomerId;
userId = UserId;
title = Title;
description = Description;
location = Location;
contact = Contact;
url = Url;
start = StartTime;
end = EndTime;
createdBy = CreatedBy;
createDate = DateTime.Now;
lastUpdate = DateTime.Now;
lastUpdateBy = UpdatedBy;
string addAppointmentQuery = "INSERT INTO appointment VALUES(" + appointmentId.ToString()
+ ", " + customerId.ToString() + ", '" + title + "', '" + description + "', '"
+ location + "', '" + contact + "', '" + url + "', '"
+ start.ToString("yyyy-MM-dd HH:mm:ss") + "', '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "', '"
+ createDate.ToString("yyyy-MM-dd") + "', '" + createdBy + "', '" + lastUpdate.ToString("yyyy-MM-dd")
+ "', '" + lastUpdateBy + "', '" + type + "', " + userId.ToString() + ")";
//DataChanges db = new DataChanges();
DataChanges.ExecuteMySqlCommand(addAppointmentQuery, conn);
}
}
//Class Methods
/// <summary>
/// Sets the Object's properties with the Database information based on Country ID with the given connection
/// </summary>
/// <param name="CountryId"></param>
/// <param name="conn"></param>
private void getDatabaseCountryInfo(int CountryId, MySqlConnection conn)
{
//Search Database to find country by ID, then populate the information with the returned information.
using (conn)
{
string myExecuteQuery = "SELECT * FROM country WHERE countryId = " + CountryId.ToString();
MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myExecuteQuery);
while (reader.Read())
{
countryId = (int)reader["countryId"];
country = reader["country"].ToString();
createDate = (DateTime)reader["createDate"];
createdBy = reader["createdBy"].ToString();
lastUpdate = (DateTime)reader["lastUpdate"];
lastUpdatedBy = reader["lastUpdateBy"].ToString();
}
}
}
/// <summary>
/// Constructor to create a new user
/// </summary>
/// <param name="Username"></param>
/// <param name="Password"></param>
/// <param name="userCreating"></param>
/// <param name="Connection"></param>
public User(string Username, string Password, string userCreating, MySqlConnection Connection)
{
using (Connection)
{
try
{
int numOfUsers = 0;
string getAllUsersQuery = "SELECT * FROM user";
// Create a user based on the Username and password given
//Get the number of users currently in the database, Add one, and save it as a variable to use as a new ID
MySqlDataReader reader = DataChanges.GetMySqlReader(Connection, getAllUsersQuery);
while (reader.Read())
{
numOfUsers++;
}
Connection.Close();
numOfUsers++;
DateTime today = DateTime.Now;
//Fill User Object with
userId = numOfUsers;
userName = Username;
password = Password;
lastUpdate = today;
lastUpdatedBy = userCreating;
createdBy = userCreating;
createDate = today;
active = 1;
//DataChanges db = new DataChanges();
//Create a Command to insert a new user with the id as the count + 1 of users, the username and password given, the user adding the user.
string insertCommand = "INSERT INTO user VALUES (" + userId.ToString() + ", '" + userName + "', '" + password + "', " + active.ToString()
+ ", '" + createdBy + "', '" + createDate.ToString("yyyy-mm-dd") + "', '" + lastUpdate.ToString("yyyy-mm-dd") + "', '" + lastUpdatedBy + "')";
DataChanges.ExecuteMySqlCommand(insertCommand, Connection);
//See how many users are in Database
//reader = DataChanges.GetMySqlReader(conn, "SELECT * FROM user");
}
catch
{
throw new Exception("Failed to Add User");
}
}
}
// Constructors
/// <summary>
/// City Constructor using City Name
/// </summary>
/// <param name="CityName"></param>
/// <param name="CountryName"></param>
/// <param name="CreatedBy"></param>
/// <param name="conn"></param>
public City(string CityName, string CountryName, string CreatedBy, MySqlConnection conn)
{
//Search Database for City that matches.
//Check to see if Country with given name exists. If not, update the database to add the country
using (conn)
{
countryObject = new Country(CountryName, CreatedBy, conn);
int cityCount = 0;
bool cityExists = false;
int existingCityId = 0;
bool databaseEmpty = true;
int newMaxCityId = 0;
//Get the max value of id instead of counting all of the accounts.
string myQuery = "SELECT MAX(cityId) FROM city";
MySqlDataReader reader1 = DataChanges.GetMySqlReader(conn, myQuery);
//Use this to see if the country exists, and to count the number of countries
while (reader1.Read())
{
newMaxCityId = (int)reader1["Max(cityId)"];
databaseEmpty = false;
}
conn.Close();
myQuery = "SELECT * FROM city";
MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myQuery);
//Use this to see if the city exists, and to count the number of cities
while (reader.Read())
{
cityCount++;
string checkCity = reader["city"].ToString();
int checkCountry = (int)reader["countryId"];
if (checkCity == CityName)
{
if (countryObject.countryId == checkCountry)
{
cityExists = true;
var cityId = reader["cityId"];
existingCityId = (int)cityId;
}
}
}
conn.Close();
if (cityExists == true)
{
setDatabaseCityInfo(existingCityId, CreatedBy, conn);
}
else
{
//Increment the number of countries by one to use as the id
cityCount++;
if (databaseEmpty == true)
{
cityId = 1;
}
else
{
newMaxCityId++;
cityId = newMaxCityId;
}
//Set values of Class
cityName = CityName;
//Create a country object to check if a country is already created, and if it's not, create it, and use the id of the existing or new Country
countryId = countryObject.countryId;
createDate = DateTime.Now;
createdBy = CreatedBy;
lastUpdate = DateTime.Now;
lastUpdateBy = CreatedBy;
//add city to database if it doesn't exist
//DataChanges db = new DataChanges();
myQuery = "INSERT INTO city VALUES(" + cityId.ToString() + ", '" + cityName + "', "
+ countryId.ToString() + ", '" + createDate.ToString("yyyy-MM-dd") + "', '" + createdBy
+ "', '" + lastUpdate.ToString("yyyy-MM-dd") + "', '" + lastUpdateBy + "')";
DataChanges.ExecuteMySqlCommand(myQuery, conn);
}
}
//If there isn't one that matches, add it with the information given
}
//Constructors
/// <summary>
/// Constructor based on Country Name
/// </summary>
/// <param name="CountryName"></param>
/// <param name="CreatedBy"></param>
/// <param name="conn"></param>
public Country(string CountryName, string CreatedBy, MySqlConnection conn)
{
//Check to see if Country with given name exists. If not, update the database to add the country
using (conn)
{
int countryCount = 0;
bool countryExists = false;
int existingCountryId = 0;
bool databaseEmpty = true;
int newMaxCountryId = 0;
//Get the max value of id instead of counting all of the accounts.
string myQuery = "SELECT MAX(countryId) FROM country";
MySqlDataReader reader1 = DataChanges.GetMySqlReader(conn, myQuery);
//Use this to see if the country exists, and to count the number of countries
while (reader1.Read())
{
newMaxCountryId = (int)reader1["Max(countryId)"];
databaseEmpty = false;
}
conn.Close();
myQuery = "SELECT * FROM country";
MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myQuery);
//Use this to see if the country exists, and to count the number of countries
while (reader.Read())
{
countryCount++;
string checkCountry = reader["country"].ToString();
if (checkCountry == CountryName)
{
countryExists = true;
var countryId = reader["countryId"];
existingCountryId = (int)countryId;
}
}
conn.Close();
if (countryExists == true)
{
getDatabaseCountryInfo(existingCountryId, conn);
}
else
{
//Increment the number of countries by one to use as the id
countryCount++;
//Set values of Class
if (databaseEmpty == true)
{
countryId = 1;
}
else
{
newMaxCountryId++;
countryId = newMaxCountryId;
}
country = CountryName;
createDate = DateTime.Now;
createdBy = CreatedBy;
lastUpdate = DateTime.Now;
lastUpdatedBy = CreatedBy;
//add country to database if it doesn't exist
//DataChanges db = new DataChanges();
myQuery = "INSERT INTO country VALUES(" + countryId.ToString() + ", '" + country + "', '"
+ createDate.ToString("yyyy-MM-dd") + "', '" + createdBy + "', '"
+ lastUpdate.ToString("yyyy-MM-dd") + "', '" + lastUpdatedBy + "')";
DataChanges.ExecuteMySqlCommand(myQuery, conn);
}
}
}
// Constructors
/// <summary>
/// Address Constructor Using Address Information.
/// </summary>
/// <param name="Address"></param>
/// <param name="Address2"></param>
/// <param name="City"></param>
/// <param name="CountryName"></param>
/// <param name="PostalCode"></param>
/// <param name="Phone"></param>
/// <param name="CreatedBy"></param>
/// <param name="conn"></param>
public Address(string Address, string Address2, string City, String CountryName, string PostalCode, string Phone, string CreatedBy, MySqlConnection conn)
{
//Check Database to see if the address matches exactly with any records.
//If It does, populate information with information from Database
//If it's not, use the given information to add a new record to the database
//Get City ID by searching for matches for City
//Use current date/time for creade date and last update
//Throw error if Insert failed
cityObject = new City(City, CountryName, CreatedBy, conn);
int addressCount = 0;
bool addressExists = false;
int existingAddressId = 0;
string myQuery = "SELECT * FROM address";
MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myQuery);
//Use this to see if the country exists, and to count the number of countries
while (reader.Read())
{
addressCount++;
string checkAddress = reader["address"].ToString() + reader["address2"].ToString() + reader["cityId"].ToString();
string givenAddress = Address + Address2 + cityObject.cityId.ToString();
if (givenAddress == checkAddress)
{
//Set existingAddressId to be able to Call function that fills class from reader by id
//after connection is closed
addressExists = true;
existingAddressId = (int)reader["addressId"];
}
}
conn.Close();
if (addressExists == true)
{
setDatabaseAddressInfo(existingAddressId, conn);
}
else
{
//Increment the number of addresses by one to use as the id
addressCount++;
//Set values of Class
addressId = addressCount;
address = Address;
address2 = Address2;
//Create a country object to check if a country is already created, and if it's not, create it, and use the id of the existing or new Country
cityId = cityObject.cityId;
postalCode = PostalCode;
phone = Phone;
createDate = DateTime.Now;
createdBy = CreatedBy;
lastUpdate = DateTime.Now;
lastUpdateBy = CreatedBy;
//add country to database if it doesn't exist
//DataChanges db = new DataChanges();
myQuery = "INSERT INTO address VALUES(" + addressId.ToString() + ", '" + address + "', '"
+ address2 + "', " + cityId + ", '" + postalCode + "', '" + phone + "', '"
+ createDate.ToString("yyyy-MM-dd") + "', '" + createdBy + "', '"
+ lastUpdate.ToString("yyyy-MM-dd") + "', '" + lastUpdateBy + "')";
DataChanges.ExecuteMySqlCommand(myQuery, conn);
}
}
//Constructors
/// <summary>
/// Constructor for Adding a new Customer, or loading customer object if matching customer exists
/// </summary>
/// <param name="Name"></param>
/// <param name="Address1"></param>
/// <param name="Address2"></param>
/// <param name="City"></param>
/// <param name="CountryName"></param>
/// <param name="PostalCode"></param>
/// <param name="Phone"></param>
/// <param name="CreatedBy"></param>
/// <param name="conn"></param>
public Customer(string Name, string Address1, string Address2, string City, string CountryName, string PostalCode, string Phone, string CreatedBy, MySqlConnection conn)
{
//Check Database for number of customers, add one, and use as customer ID
using (conn)
{
addressObject = new Address(Address1, Address2, City, CountryName, PostalCode, Phone, CreatedBy, conn);
bool customerExists = false;
int existingCustomerId = 0;
bool databaseEmpty = true;
int newMaxCityId = 0;
//Get the max value of id instead of counting all of the accounts.
string myQuery = "SELECT MAX(customerId) FROM customer";
MySqlDataReader reader1 = DataChanges.GetMySqlReader(conn, myQuery);
//Use this to see if the country exists, and to count the number of countries
while (reader1.Read())
{
newMaxCityId = (int)reader1["Max(customerId)"];
databaseEmpty = false;
}
conn.Close();
//Check to see if there are any matches to the customer
string mySqlQuery = "SELECT * FROM customer JOIN address ON customer.addressId = address.addressId";
MySqlDataReader reader = DataChanges.GetMySqlReader(conn, mySqlQuery);
while (reader.Read())
{
string TempName = reader["customerName"].ToString();
if (TempName == Name)
{
string tempAddress = reader["address"].ToString() + reader["address2"].ToString();
string tempPhone = reader["phone"].ToString();
if (tempAddress == Address1 + Address2 && tempPhone == Phone)
{
customerExists = true;
existingCustomerId = (int)reader["customerId"];
}
}
}
conn.Close();
if (customerExists == true)
{
setDatabaseCustomerInfo(existingCustomerId, conn);
}
else
{
newMaxCityId++;
if (databaseEmpty == true)
{
customerID = 1;
}
else
{
customerID = newMaxCityId;
}
//Set name = the name given
//use address constructor to set phone, and address along with postal Code, etc in an object
//get address ID from address object to add address ID
//Set active,
//Use current date/time to set create and last update dates
//Use the given logged in user name to set createdBy, and lastupdated by
customerName = Name;
addressID = addressObject.addressId;
active = 1;
createDate = DateTime.Now;
CreatedBy = loggedInUser;
lastUpdate = DateTime.Now;
lastUpdateBy = loggedInUser;
//add customer to database if it doesn't exist
//DataChanges db = new DataChanges();
myQuery = "INSERT INTO customer VALUES(" + customerID.ToString() + ", '" + customerName + "', "
+ addressID.ToString() + ", " + active.ToString() + ", '" + createDate.ToString("yyyy-MM-dd") + "', '" + createdBy
+ "', '" + lastUpdate.ToString("yyyy-MM-dd") + "', '" + lastUpdateBy + "')";
DataChanges.ExecuteMySqlCommand(myQuery, conn);
}
}
}