public static TicketResource CreateTicket(String status, String firstName, String lastName, String middleName, String address, String city, String state, String zip, String telephone, String email, String comments,
String pickedUp, DateTime dateIn, DateTime dateReady, double totalPrice, double Deposit, String tailorName)
{
TicketResource ticket = new TicketResource
{
Status = status,
FirstName = firstName,
MiddleName = middleName,
LastName = lastName,
Address = address,
City = city,
State = state,
Zip = zip,
Telephone = telephone,
Email = email,
Comments = comments,
PickedUp = pickedUp,
DateIn = dateIn,
DateReady = dateReady,
TotalPrice = totalPrice,
Deposit = Deposit,
TailorName = tailorName
};
return ticket;
}
public static TicketResource CreateTicket(int ticketId, String status, String title, String firstName, String lastName, String middleName, String address, String city, String state, String zip, String telephone, String email, String comments,
String pickedUp, DateTime dateIn, DateTime dateReady, double totalPrice, double Deposit, String tailorName, String orderId, DateTime? completedDate, int customerId)
{
TicketResource ticket = new TicketResource
{
TicketId = ticketId,
Status = status,
Title = title,
FirstName = firstName,
MiddleName = middleName,
LastName = lastName,
Address = address,
City = city,
State = state,
Zip = zip,
Telephone = telephone,
Email = email,
Comments = comments,
PickedUp = pickedUp,
DateIn = dateIn,
DateReady = dateReady,
TotalPrice = totalPrice,
Deposit = Deposit,
TailorName = tailorName,
OrderId = orderId,
CompletedDate = completedDate,
CustomerID = customerId
};
return ticket;
}
public AlterationForm(TicketResource ticketResource)
{
SetupForm();
ConfigurationRepository configRepo = new ConfigurationRepository();
TicketRepository ticketRepo = new TicketRepository();
maxAlterations = Convert.ToDouble(configRepo.GetConfigrationSettings().Where(x => x.Setting == "maxAlterations").Select(x => x.Value).DefaultIfEmpty("").First());
alterationToatalForDays = ticketRepo.GetAlterationToatalForDays();
if (ticketResource.TicketId == 0)
{
isNewAlteration = true;
TicketNumberLabel.Hide();
previousStatus = "a";
NewTicketWithCustomerBttn.Enabled = false;
}
else
{
isNewAlteration = false;
ticketID = ticketResource.TicketId;
TicketNumberLabel.Text = ticketResource.TicketId.ToString();
previousStatus = ticketResource.Status ?? "a";
}
customerId = ticketResource.CustomerID;
TitleComboBox.Text = ticketResource.Title ?? "";
FirstNameTextBox.Text = ticketResource.FirstName ?? "";
MiddleNameTextBox.Text = ticketResource.MiddleName ?? "";
LastNameTextBox.Text = ticketResource.LastName ?? "";
AddressTextBox.Text = ticketResource.Address ?? "";
CityTextBox.Text = ticketResource.City ?? "";
StateTextBox.Text = ticketResource.State ?? "";
ZipTextBox.Text = ticketResource.Zip ?? "";
PhoneTextBox.Text = ticketResource.Telephone ?? "";
EmailTextBox.Text = ticketResource.Email ?? "";
CommentBox.Text = ticketResource.Comments ?? "";
if (ticketResource.Status == "a")
{
StatusComboBox.Text = "Active";
}
else if (ticketResource.Status == "d")
{
StatusComboBox.Text = "Done";
}
else if (ticketResource.Status == "c")
{
StatusComboBox.Text = "Cancelled";
}
else if (ticketResource.Status == "i")
{
StatusComboBox.Text = "In Progress";
}
else
{
StatusComboBox.Text = "";
}
PickedUpComboBox.Text = ticketResource.PickedUp ?? "n/a";
DateInPicker.Value = ticketResource.DateIn;
DateReadyPicker.Value = ticketResource.DateReady;
OrderIdTextBox.Text = ticketResource.OrderId ?? "";
if (isNewAlteration)
{
//cant add this above because it will just get overwritten down here, and it looks messy it the if logic is added below
DateReadyPicker.Value = DateTime.Today.AddDays(7);
}
DepositTextBox.Text = ticketResource.Deposit.ToString();
TailorComboBox.Text = ticketResource.TailorName == "" ? "In House" : ticketResource.TailorName;
}
public AlterationForm(TicketResource ticketResource, TicketAlterationResource alterationResource)
: this(ticketResource)
{
List<TicketAlterationResourceItem> gridItems = alterationResource.Alterations;
for (int i = 0; i < gridItems.Count; i++)
{
String quantity = gridItems[i].Quantity == 0 ? "" : gridItems[i].Quantity.ToString();
String description = gridItems[i].Description;
String price = gridItems[i].Price == 0 ? "" : gridItems[i].Price.ToString();
AlterationGrid.Rows.Add(quantity, description, price, gridItems[i].Taxable);
}
UpdateTotalDepositAndBalance();
}
private Dictionary<String, object> ConvertTicketIntoDictionary(TicketResource ticket)
{
Dictionary<String, object> ticketArgs = new Dictionary<string, object>();
ticketArgs.Add("name_title", ticket.Title);
ticketArgs.Add("first_name", ticket.FirstName);
ticketArgs.Add("middle_name", ticket.MiddleName);
ticketArgs.Add("last_name", ticket.LastName);
ticketArgs.Add("address", ticket.Address);
ticketArgs.Add("city", ticket.City);
ticketArgs.Add("state", ticket.State);
ticketArgs.Add("zip", ticket.Zip);
ticketArgs.Add("phone", ticket.Telephone);
ticketArgs.Add("email", ticket.Email);
ticketArgs.Add("date_in", string.Format("{0:yyyy-MM-dd HH:mm:ss}", ticket.DateIn));
ticketArgs.Add("date_ready", string.Format("{0:yyyy-MM-dd HH:mm:ss}", ticket.DateReady));
ticketArgs.Add("last_modified_timestamp", string.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now));
ticketArgs.Add("status", ticket.Status);
ticketArgs.Add("comments", ticket.Comments);
ticketArgs.Add("tailor_name", ticket.TailorName);
ticketArgs.Add("total_price", ticket.TotalPrice);
ticketArgs.Add("picked_up", ticket.PickedUp);
ticketArgs.Add("deposit", ticket.Deposit);
ticketArgs.Add("order_id", ticket.OrderId);
ticketArgs.Add("completed_date", string.Format("{0:yyyy-MM-dd}", ticket.CompletedDate));
return ticketArgs;
}
public void UpdateTicket(TicketResource ticket)
{
DBConnector connector = new DBConnector();
MySqlCommand updateCommand = new MySqlCommand();
updateCommand.Connection = connector.connection;
updateCommand.CommandText = "UPDATE Tickets SET ticket_id = @ticket_id, status = @status ,last_modified_timestamp = @last_modified_timestamp, date_in = @date_in, date_ready = @date_ready, comments = @comments, total_price = @total_price, picked_up = @picked_up, deposit = @deposit, tailor_name = @tailor_name, order_id = @order_id, completed_date = @completed_date WHERE ticket_id = @ticket_id";
updateCommand.Parameters.AddWithValue("@ticket_id", ticket.TicketId);
updateCommand.Parameters.AddWithValue("@status", ticket.Status);
updateCommand.Parameters.AddWithValue("@last_modified_timestamp", ConvertDateTimeToUTCString(DateTime.Now));
updateCommand.Parameters.AddWithValue("@date_in", ConvertDateTimeToUTCString(ticket.DateIn));
updateCommand.Parameters.AddWithValue("@date_ready", ConvertDateTimeToUTCString(ticket.DateReady));
updateCommand.Parameters.AddWithValue("@comments", ticket.Comments);
updateCommand.Parameters.AddWithValue("@total_price", ticket.TotalPrice);
updateCommand.Parameters.AddWithValue("@picked_up", ticket.PickedUp);
updateCommand.Parameters.AddWithValue("@deposit", ticket.Deposit);
updateCommand.Parameters.AddWithValue("@tailor_name", ticket.TailorName);
updateCommand.Parameters.AddWithValue("@order_id", ticket.OrderId);
updateCommand.Parameters.AddWithValue("@completed_date", ConvertDateTimeToUTCString(ticket.CompletedDate));
try
{
updateCommand.ExecuteNonQuery();
connector.CloseConnection();
}
catch (MySqlException ex)
{
MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
}
}
/* @return the generated ticketID of the new ticket */
public int InsertNewTicket(TicketResource ticket)
{
DBConnector connector = new DBConnector();
MySqlCommand insertCommand = new MySqlCommand();
insertCommand.Connection = connector.connection;
insertCommand.CommandText = @"INSERT into Tickets (ticket_id, status, last_modified_timestamp, date_in, date_ready, comments, total_price, picked_up, deposit, tailor_name, name_title, order_id, completed_date, CustomerId) values (@ticket_id, @status, @last_modified_timestamp, @date_in, @date_ready, @comments, @total_price, @picked_up, @deposit, @tailor_name, @name_title, @order_id, @completed_date, @CustomerId)";
insertCommand.Parameters.AddWithValue("@ticket_id", ticket.TicketId);
insertCommand.Parameters.AddWithValue("@status", ticket.Status);
insertCommand.Parameters.AddWithValue("@last_modified_timestamp", ConvertDateTimeToUTCString(DateTime.Now));
insertCommand.Parameters.AddWithValue("@date_in", ConvertDateTimeToUTCString(ticket.DateIn));
insertCommand.Parameters.AddWithValue("@date_ready", ConvertDateTimeToUTCString(ticket.DateReady));
insertCommand.Parameters.AddWithValue("@comments", ticket.Comments);
insertCommand.Parameters.AddWithValue("@total_price", ticket.TotalPrice);
insertCommand.Parameters.AddWithValue("@picked_up", ticket.PickedUp);
insertCommand.Parameters.AddWithValue("@deposit", ticket.Deposit);
insertCommand.Parameters.AddWithValue("@tailor_name", ticket.TailorName);
insertCommand.Parameters.AddWithValue("@name_title", ticket.Title);
insertCommand.Parameters.AddWithValue("@order_id", ticket.OrderId);
insertCommand.Parameters.AddWithValue("@CustomerId", ticket.CustomerID);
insertCommand.Parameters.AddWithValue("@completed_date", ConvertDateTimeToUTCString(ticket.CompletedDate));
//MySqlCommand insertCustomerCommand = new MySqlCommand();
//insertCustomerCommand.Connection = connector.connection;
//insertCustomerCommand.CommandText = @"INSERT into Customer (first_name, middle_name, last_name, address, city, state, zip, phone, email) values (@first_name, @middle_name, @last_name, @address, @city, @state, @zip, @phone, @email)";
//insertCustomerCommand.Parameters.AddWithValue("@first_name", ticket.FirstName);
//insertCustomerCommand.Parameters.AddWithValue("@middle_name", ticket.MiddleName);
//insertCustomerCommand.Parameters.AddWithValue("@last_name", ticket.LastName);
//insertCustomerCommand.Parameters.AddWithValue("@address", ticket.Address);
//insertCustomerCommand.Parameters.AddWithValue("@city", ticket.City);
//insertCustomerCommand.Parameters.AddWithValue("@state", ticket.State);
//insertCustomerCommand.Parameters.AddWithValue("@zip", ticket.Zip);
//insertCustomerCommand.Parameters.AddWithValue("@phone", ticket.Telephone);
//insertCustomerCommand.Parameters.AddWithValue("@email", ticket.Email);
//race condition prone. First on list to convert to stored proc
MySqlCommand getLastRowCommand = new MySqlCommand();
getLastRowCommand.Connection = connector.connection;
getLastRowCommand.CommandText = "SELECT ticket_id FROM Tickets ORDER BY ticket_id DESC LIMIT 1;";
try
{
insertCommand.ExecuteNonQuery();
//insertCustomerCommand.ExecuteNonQuery();
MySqlDataReader reader = getLastRowCommand.ExecuteReader();
int ticketId = 0;
while (reader.Read())
{
ticketId = (int)reader["ticket_id"];
}
connector.CloseConnection();
reader.Close();
return ticketId;
}
catch (MySqlException ex)
{
MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
}
return 1;
}
public List<TicketResource> GetDistinctMailingAddressBetweenDates(DateTime startDate, DateTime endDate)
{
DBConnector connector = new DBConnector();
MySqlCommand getTicketsCommand = new MySqlCommand();
getTicketsCommand.Connection = connector.connection;
getTicketsCommand.CommandText = "select distinct Address, first_name, last_name, city, state, zip from Tickets join Customer on Tickets.CustomerId = Customer.id where date_in >= @startDate AND date_in <= @endDate";
getTicketsCommand.Parameters.AddWithValue("@startDate", ConvertDateTimeToUTCString(startDate));
getTicketsCommand.Parameters.AddWithValue("@endDate", ConvertDateTimeToUTCString(endDate));
List<TicketResource> ticketResources = new List<TicketResource>();
try
{
MySqlDataReader reader = getTicketsCommand.ExecuteReader();
while (reader.Read())
{
TicketResource resource = new TicketResource();
resource.FirstName = Convert.ToString(reader["first_name"]);
resource.LastName = Convert.ToString(reader["last_name"]);
resource.Address = Convert.ToString(reader["address"]);
resource.City = Convert.ToString(reader["city"]);
resource.State = Convert.ToString(reader["state"]);
resource.Zip = Convert.ToString(reader["zip"]);
ticketResources.Add(resource);
}
reader.Close();
connector.CloseConnection();
}
catch (MySqlException ex)
{
MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
}
return ticketResources;
}
