public void AddCustomer(string name, string phoneNumber, string postalCode, string address, int countryId, string city, string userName)
{
TimeConversion timeConversion = new TimeConversion();
if (!checkIfDuplicateCityExists(city))
{
addCity(city, countryId, userName);
}
if (!checkIfDuplicateAddressExists(address, postalCode))
{
addAddress(address, city, postalCode, phoneNumber, userName);
}
if (!checkIfDuplicateCustomerExists(address, name))
{
using (connection = new MySqlConnection(this.connectionDetails))
{
connection.Open();
using (MySqlCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO customer (customerName, addressId, active, createDate, createdBy, lastUpdate, lastUpdateBy) VALUES (@customerName, @addressId, @active, convert_tz(@createDate, '" + timeConversion.getUserTimeZoneOffset() + "', '+00:00'), @createdBy, convert_tz(@lastupdate, '" + timeConversion.getUserTimeZoneOffset() + "', '+00:00'), @lastUpdateBy)";
command.Parameters.AddWithValue("@customerName", name);
command.Parameters.AddWithValue("@addressId", Convert.ToUInt32(GetQueryResultAsString("SELECT addressId FROM address WHERE address = '" + address + "'")));
command.Parameters.AddWithValue("@active", 1);
command.Parameters.AddWithValue("@createDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
command.Parameters.AddWithValue("@createdBy", userName);
command.Parameters.AddWithValue("@lastUpdate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
command.Parameters.AddWithValue("@lastUpdateBy", userName);
command.ExecuteNonQuery();
}
}
}
}
public void SetFields()
{
TimeConversion timeConversion = new TimeConversion();
customerDataGridView.DataSource = this.appointmentManager.GetQueryResultAsDatatable("SELECT customerId, customerName FROM customer");
titleTextbox.Text = this.appointmentManager.GetQueryResultAsString("SELECT title from appointment where appointmentId = '" + this.appointmentId + "'");
descriptionTextbox.Text = this.appointmentManager.GetQueryResultAsString("SELECT description from appointment where appointmentId = '" + this.appointmentId + "'");
locationTextbox.Text = this.appointmentManager.GetQueryResultAsString("SELECT location from appointment where appointmentId = '" + this.appointmentId + "'");
contactTextbox.Text = this.appointmentManager.GetQueryResultAsString("SELECT contact from appointment where appointmentId = '" + this.appointmentId + "'");
urlTextbox.Text = this.appointmentManager.GetQueryResultAsString("SELECT url from appointment where appointmentId = '" + this.appointmentId + "'");
typeTextbox.Text = this.appointmentManager.GetQueryResultAsString("SELECT type from appointment where appointmentId = '" + this.appointmentId + "'");
startDateTimePicker.Value = DateTime.Parse(this.appointmentManager.GetQueryResultAsString("SELECT convert_tz(start, '+00:00', '" + timeConversion.getUserTimeZoneOffset() + "') from appointment where appointmentId = '" + this.appointmentId + "'"));
endDateTimePicker.Value = DateTime.Parse(this.appointmentManager.GetQueryResultAsString("SELECT convert_tz(end, '+00:00', '" + timeConversion.getUserTimeZoneOffset() + "') from appointment where appointmentId = '" + this.appointmentId + "'"));
}
private void addCity(string city, int countryId, string userName)
{
TimeConversion timeConversion = new TimeConversion();
using (connection = new MySqlConnection(this.connectionDetails))
{
connection.Open();
using (MySqlCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO city (city, countryId, createDate, createdBy, lastUpdate, lastUpdateBy) VALUES (@city, @countryId, convert_tz(@createDate, '" + timeConversion.getUserTimeZoneOffset() + "', '+00:00'), @createdBy, convert_tz(@lastupdate, '" + timeConversion.getUserTimeZoneOffset() + "', '+00:00'), @lastUpdateBy)";
command.Parameters.AddWithValue("@city", city);
command.Parameters.AddWithValue("@countryId", countryId);
command.Parameters.AddWithValue("@createDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
command.Parameters.AddWithValue("@createdBy", userName);
command.Parameters.AddWithValue("@lastUpdate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
command.Parameters.AddWithValue("@lastUpdateBy", userName);
command.ExecuteNonQuery();
}
}
}
private void updateCity(string city, int countryId, string userName, int cityId)
{
TimeConversion timeConversion = new TimeConversion();
using (connection = new MySqlConnection(this.connectionDetails))
{
connection.Open();
using (MySqlCommand command = connection.CreateCommand())
{
command.CommandText = "UPDATE city SET city = @city, countryid = @countryid, lastupdate = convert_tz(@lastupdate, '" + timeConversion.getUserTimeZoneOffset() + "', '+00:00'), lastupdateBy = @lastupdateBy WHERE cityId = @cityid";
command.Parameters.AddWithValue("@city", city);
command.Parameters.AddWithValue("@countryid", countryId);
command.Parameters.AddWithValue("@lastupdate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
command.Parameters.AddWithValue("@lastupdateBy", userName);
command.Parameters.AddWithValue("@cityid", cityId);
command.ExecuteNonQuery();
}
}
}
private void updateAddress(string address, string city, string postalCode, string phoneNumber, string userName)
{
TimeConversion timeConversion = new TimeConversion();
using (connection = new MySqlConnection(this.connectionDetails))
{
connection.Open();
using (MySqlCommand command = connection.CreateCommand())
{
command.CommandText = "UPDATE address SET address = @address, cityId = @cityId, postalCode = @postalCode, phone = @phone, lastUpdate = convert_tz(@lastupdate, '" + timeConversion.getUserTimeZoneOffset() + "', '+00:00'), lastUpdateBy = @lastUpdateBy";
command.Parameters.AddWithValue("@address", address);
command.Parameters.AddWithValue("@cityId", Convert.ToInt32(GetQueryResultAsString("SELECT cityId FROM city WHERE city = '" + city + "'")));
command.Parameters.AddWithValue("@postalCode", postalCode);
command.Parameters.AddWithValue("@phone", phoneNumber);
command.Parameters.AddWithValue("@lastUpdate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
command.Parameters.AddWithValue("@lastUpdateBy", userName);
command.ExecuteNonQuery();
}
}
}
public Boolean AddAppointment(int customerId, string title, string description, string location, string contact, string url, string start, string end, string createDate, string createdBy, string lastUpdate, string lastUpdateBy, string type, int userId)
{
if (checkIfTimeIsValid(start))
{
TimeConversion timeConversion = new TimeConversion();
using (connection = new MySqlConnection(this.connectionDetails))
{
connection.Open();
using (MySqlCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO appointment(customerid, title, description, location, contact, url, start, end, createdate, createdby, lastupdate, lastupdateby, type, userid) VALUES(@customerid, @title, @description, @location, @contact, @url, convert_tz(@start, '" + timeConversion.getUserTimeZoneOffset() + "', '+00:00'), convert_tz(@end, '" + timeConversion.getUserTimeZoneOffset() + "', '+00:00'), convert_tz(@createdate, '" + timeConversion.getUserTimeZoneOffset() + "', '+00:00'), @createdby, convert_tz(@lastupdate, '" + timeConversion.getUserTimeZoneOffset() + "', '+00:00'), @lastupdateby, @type, @userid)";
command.Parameters.AddWithValue("@customerid", customerId);
command.Parameters.AddWithValue("@title", title);
command.Parameters.AddWithValue("@description", description);
command.Parameters.AddWithValue("@location", location);
command.Parameters.AddWithValue("@contact", contact);
command.Parameters.AddWithValue("@url", url);
command.Parameters.AddWithValue("@start", start);
command.Parameters.AddWithValue("@end", end);
command.Parameters.AddWithValue("@createdate", createDate);
command.Parameters.AddWithValue("@createdby", createdBy);
command.Parameters.AddWithValue("@lastupdate", lastUpdate);
command.Parameters.AddWithValue("@lastupdateby", lastUpdateBy);
command.Parameters.AddWithValue("@type", type);
command.Parameters.AddWithValue("@userid", userId);
command.ExecuteNonQuery();
}
}
return(true);
}
else
{
return(false);
}
}
public Boolean UpdateAppointment(int customerId, string title, string description, string location, string contact, string url, string start, string end, string lastUpdate, string lastUpdateBy, string type, int userId, int appointmentId)
{
TimeConversion timeConversion = new TimeConversion();
if (checkIfTimeIsValid(start))
{
using (connection = new MySqlConnection(this.connectionDetails))
{
connection.Open();
using (MySqlCommand command = connection.CreateCommand())
{
command.CommandText = "Update appointment SET customerid = @customerid, title = @title, description = @description, location = @location, contact = @contact, url = @url, start = convert_tz(@start, '" + timeConversion.getUserTimeZoneOffset() + "', '+00:00'), end = convert_tz(@end, '" + timeConversion.getUserTimeZoneOffset() + "', '+00:00'), lastupdate = convert_tz(@lastupdate, '" + timeConversion.getUserTimeZoneOffset() + "', '+00:00'), lastupdateby = @lastupdateby, type = @type, userid = @userid WHERE appointmentId = '" + appointmentId + "'";
command.Parameters.AddWithValue("@customerid", customerId);
command.Parameters.AddWithValue("@title", title);
command.Parameters.AddWithValue("@description", description);
command.Parameters.AddWithValue("@location", location);
command.Parameters.AddWithValue("@contact", contact);
command.Parameters.AddWithValue("@url", url);
command.Parameters.AddWithValue("@start", start);
command.Parameters.AddWithValue("@end", end);
command.Parameters.AddWithValue("@lastupdate", lastUpdate);
command.Parameters.AddWithValue("@lastupdateby", lastUpdateBy);
command.Parameters.AddWithValue("@type", type);
command.Parameters.AddWithValue("@userid", userId);
command.ExecuteNonQuery();
}
}
return(true);
}
else
{
return(false);
}
}
public DataTable GetSchedules()
{
TimeConversion timeConversion = new TimeConversion();
return(GetQueryResultAsDatatable("select appointment.userId, userName as 'User Name', appointmentId as 'Appointment ID', convert_tz(start, '+00:00', '" + timeConversion.getUserTimeZoneOffset() + "') as 'Start', convert_tz(end, '+00:00', '" + timeConversion.getUserTimeZoneOffset() + "') as 'End' from appointment join user on appointment.userId = user.userId order by userId, start"));
}