//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>
/// 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>
/// 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");
}
}
}
/// <summary>
/// Constructor to Fill Data from Data Given, or update record
/// </summary>
/// <param name="AppointmentId"></param>
/// <param name=""></param>
public Appointment(string UpdateOrFill, int AppointmentId, int CustomerId, int UserId, string Title, string Description, string Location, string Contact, string Url, DateTime StartTime, DateTime EndTime, string CreatedBy, DateTime CreateDate, string UpdatedBy, DateTime UpdateDate, string AppointmentType, string UserLoggedIn)
{
using (var conn = new MySqlConnection(connecterString))
{
loggedInUser = UserLoggedIn;
type = AppointmentType;
appointmentId = AppointmentId;
customerId = CustomerId;
fillCustomerObject(conn);
userId = UserId;
title = Title;
description = Description;
location = Location;
contact = Contact;
url = Url;
start = StartTime;
end = EndTime;
createdBy = CreatedBy;
createDate = CreateDate;
lastUpdate = UpdateDate;
lastUpdateBy = UpdatedBy;
if (UpdateOrFill.ToUpper() == "UPDATE")
{
lastUpdate = DateTime.Now;
lastUpdateBy = loggedInUser;
string updateQuery = "UPDATE appointment SET customerId = " + customerId.ToString()
+ ", title = '" + title + "', description = '" + description + "', location = '" + location
+ "', contact = '" + contact + "', url = '" + url + "', start = '" + start.ToString("yyyy-MM-dd HH:mm")
+ "', end = '" + end.ToString("yyyy-MM-dd HH:mm") + "', createDate = '" + createDate.ToString("yyyy-MM-dd")
+ "', createdBy = '" + createdBy + "', lastUpdateBy = '" + lastUpdateBy
+ "', appointmentType = '" + type + "', userId = " + userId.ToString() + " WHERE appointmentId = " + appointmentId;
//DataChanges db = new DataChanges();
DataChanges.ExecuteMySqlCommand(updateQuery, conn);
}
else
{
}
}
}
// 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);
}
}
}
