public List <TicketStub> PullClosedTicketStubs(int maxResults) { List <TicketStub> ticketStubs = new List <TicketStub>(maxResults); MySqlConnection dataBase = new MySqlConnection(connectionString); MySqlCommand firstDatabaseCommand = new MySqlCommand("SELECT * FROM `ost_ticket` WHERE `status_id` = 3 LIMIT " + maxResults + "", dataBase); /* TODO: Optimize this command, Could probbably update the sql call to * "SELECT * FROM `ost_ticket__cdata` where `ticket_id` is" + currentListTicketstub.TicketId + ";" * Do this to avoid pulling down the entire damn table. */ MySqlCommand SecondDatabaseCommand = new MySqlCommand("SELECT * FROM `ost_ticket__cdata`;", dataBase); try { dataBase.Open(); MySqlDataReader ticketsReader = firstDatabaseCommand.ExecuteReader(); while (ticketsReader.Read()) { TicketStub currentRow = new TicketStub(); currentRow.trueNumber = ticketsReader.GetInt32(Constants.tableTicketID); currentRow.number = ticketsReader.GetInt32(Constants.tableTicketNumber); currentRow.creatorUserId = ticketsReader.GetInt32(Constants.tableUserID); currentRow.deparmentId = ticketsReader.GetInt32(Constants.tableDeptID); currentRow.lastResponse = DateTime.Parse(ticketsReader.GetString(Constants.lastMessage)); currentRow.creationDate = DateTime.Parse(ticketsReader.GetString(Constants.creationDate)); currentRow.isOpen = false; ticketStubs.Add(currentRow); } ticketsReader.Close(); ticketsReader = SecondDatabaseCommand.ExecuteReader(); int totalTicketsProcessed = 0, ticketMatchesFound = 0; while (ticketsReader.Read()) { if (ticketMatchesFound >= ticketStubs.Count) { break; } if (ticketsReader.GetInt32(Constants.tableTicketID) == ticketStubs[ticketMatchesFound].trueNumber) { ticketStubs[ticketMatchesFound].subject = ticketsReader.GetString(Constants.subject); ticketStubs[ticketMatchesFound].priority = ticketsReader.GetInt32(Constants.priority); totalTicketsProcessed++; ticketMatchesFound++; } else { totalTicketsProcessed++; } } ticketsReader.Close(); } catch (MySqlException) { throw; } finally { dataBase.Close(); } return(ticketStubs); }
public void CloseTicket(TicketStub stub) { MySqlConnection database = new MySqlConnection(connectionString); int numberToCLose = stub.trueNumber; try { MySqlCommand closeTicketCommand = new MySqlCommand("UPDATE `ost_ticket` SET `status`=\"closed\" WHERE `ticket_id`=" + numberToCLose + ";", database); MySqlCommand addTimeClosedCommand = new MySqlCommand("UPDATE `ost_ticket` SET `closed`=\"" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:tt") + "\" WHERE `ticket_id`=" + numberToCLose + ";", database); database.Open(); if (closeTicketCommand.ExecuteNonQuery() == 0) { throw new Exception("Zero rows affected."); } if (addTimeClosedCommand.ExecuteNonQuery() == 0) { throw new Exception("Zero rows affected."); } Events.AddCloseTicketEvent(database, dbName, stub); } catch (Exception) { throw; } finally { database.Close(); } }
public Ticket PullFullTicket(TicketStub stub) { MySqlConnection dataBase = new MySqlConnection(connectionString); MySqlCommand pullThreadsCommand = new MySqlCommand("SELECT * FROM `ost_ticket_thread` WHERE `ticket_id` = " + stub.trueNumber + ";", dataBase); Ticket newTicket = new Ticket(stub); try { dataBase.Open(); MySqlDataReader reader = pullThreadsCommand.ExecuteReader(); newTicket.responses = new List <Message>(); while (reader.Read()) { Message currentMessage = new Message(); currentMessage.responseNumber = reader.GetInt32("id"); currentMessage.threadType = reader.GetChar("thread_type"); currentMessage.posterName = reader.GetString("poster"); currentMessage.messageTitle = reader.GetString("title"); currentMessage.messageData = reader.GetString("body"); currentMessage.timeRecieved = DateTime.Parse(reader.GetString(Constants.creationDate)); newTicket.responses.Add(currentMessage); } reader.Close(); } catch (Exception) { throw; } finally { dataBase.Close(); } return(newTicket); }
public List<TicketStub> PullClosedTicketStubs(int maxResults) { List<TicketStub> ticketStubs = new List<TicketStub>(maxResults); MySqlConnection dataBase = new MySqlConnection(connectionString); MySqlCommand firstDatabaseCommand = new MySqlCommand("SELECT * FROM `ost_ticket` WHERE `status_id` = 3 LIMIT " + maxResults + "", dataBase); /* TODO: Optimize this command, Could probbably update the sql call to "SELECT * FROM `ost_ticket__cdata` where `ticket_id` is" + currentListTicketstub.TicketId + ";" Do this to avoid pulling down the entire damn table. */ MySqlCommand SecondDatabaseCommand = new MySqlCommand("SELECT * FROM `ost_ticket__cdata`;", dataBase); try { dataBase.Open(); MySqlDataReader ticketsReader = firstDatabaseCommand.ExecuteReader(); while (ticketsReader.Read()) { TicketStub currentRow = new TicketStub(); currentRow.trueNumber = ticketsReader.GetInt32(Constants.tableTicketID); currentRow.number = ticketsReader.GetInt32(Constants.tableTicketNumber); currentRow.creatorUserId = ticketsReader.GetInt32(Constants.tableUserID); currentRow.deparmentId = ticketsReader.GetInt32(Constants.tableDeptID); currentRow.lastResponse = DateTime.Parse(ticketsReader.GetString(Constants.lastMessage)); currentRow.creationDate = DateTime.Parse(ticketsReader.GetString(Constants.creationDate)); currentRow.isOpen = false; ticketStubs.Add(currentRow); } ticketsReader.Close(); ticketsReader = SecondDatabaseCommand.ExecuteReader(); int totalTicketsProcessed = 0, ticketMatchesFound = 0; while (ticketsReader.Read()) { if (ticketMatchesFound >= ticketStubs.Count) break; if (ticketsReader.GetInt32(Constants.tableTicketID) == ticketStubs[ticketMatchesFound].trueNumber) { ticketStubs[ticketMatchesFound].subject = ticketsReader.GetString(Constants.subject); ticketStubs[ticketMatchesFound].priority = ticketsReader.GetInt32(Constants.priority); totalTicketsProcessed++; ticketMatchesFound++; } else totalTicketsProcessed++; } ticketsReader.Close(); } catch (MySqlException) { throw; } finally { dataBase.Close(); } return ticketStubs; }
public Ticket(TicketStub stub) { this.trueNumber = stub.trueNumber; this.number = stub.number; this.subject = stub.subject; this.creatorUserId = stub.creatorUserId; this.priority = stub.priority; this.isOpen = stub.isOpen; this.company = stub.company; this.creationDate = stub.creationDate; this.lastResponse = stub.lastResponse; }
public static void AddOpenedTicketEvent(MySqlConnection db, string dbName, TicketStub stub) { //TODO: Get rid of dfgrimes HERE string insertCloseEventString = string.Format(@"INSERT INTO `{0}`.`ost_ticket_event` (`ticket_id`, `staff_id`, `team_id`, `dept_id`, `topic_id`, `state`, `staff`, `annulled`, `timestamp`) VALUES ('" + stub.trueNumber + "', '-1', '0', '" + stub.deparmentId + "'', '0', 'created', 'SYSTEM TAPP', '0', '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:tt") + "')", dbName); MySqlCommand insertOpenEvent = new MySqlCommand(insertCloseEventString, db); try { if (insertOpenEvent.ExecuteNonQuery() != 1) throw new Exception(); } catch (Exception) { throw; } }
public static void AddOpenedTicketEvent(MySqlConnection db, string dbName, TicketStub stub) { string insertCloseEventString = string.Format(@"INSERT INTO `{0}`.`ost_ticket_event` (`ticket_id`, `staff_id`, `team_id`, `dept_id`, `topic_id`, `state`, `staff`, `annulled`, `timestamp`) VALUES ('" + stub.trueNumber + "', '-1', '0', '" + stub.deparmentId + "'', '0', 'created', 'SYSTEM TAPP', '0', '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:tt") + "')", dbName); MySqlCommand insertOpenEvent = new MySqlCommand(insertCloseEventString, db); try { if (insertOpenEvent.ExecuteNonQuery() != 1) { throw new Exception(); } } catch (Exception) { throw; } }
public void CloseTicket(TicketStub stub) { MySqlConnection database = new MySqlConnection(connectionString); int numberToCLose = stub.trueNumber; try { MySqlCommand closeTicketCommand = new MySqlCommand("UPDATE `ost_ticket` SET `status`=\"closed\" WHERE `ticket_id`=" + numberToCLose + ";", database); MySqlCommand addTimeClosedCommand = new MySqlCommand("UPDATE `ost_ticket` SET `closed`=\"" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:tt") + "\" WHERE `ticket_id`=" + numberToCLose + ";", database); database.Open(); if (closeTicketCommand.ExecuteNonQuery() == 0) throw new Exception("Zero rows affected."); if (addTimeClosedCommand.ExecuteNonQuery() == 0) throw new Exception("Zero rows affected."); Events.AddCloseTicketEvent(database, dbName, stub); } catch (Exception) { throw; } finally { database.Close(); } }
public Ticket PullFullTicket(TicketStub stub) { MySqlConnection dataBase = new MySqlConnection(connectionString); MySqlCommand pullThreadsCommand = new MySqlCommand("SELECT * FROM `ost_ticket_thread` WHERE `ticket_id` = " + stub.trueNumber + ";", dataBase); Ticket newTicket = new Ticket(stub); try { dataBase.Open(); MySqlDataReader reader = pullThreadsCommand.ExecuteReader(); newTicket.responses = new List<Message>(); while (reader.Read()) { Message currentMessage = new Message(); currentMessage.responseNumber = reader.GetInt32("id"); currentMessage.threadType = reader.GetChar("thread_type"); currentMessage.posterName = reader.GetString("poster"); currentMessage.messageTitle = reader.GetString("title"); currentMessage.messageData = reader.GetString("body"); currentMessage.timeRecieved = DateTime.Parse(reader.GetString(Constants.creationDate)); newTicket.responses.Add(currentMessage); } reader.Close(); } catch (Exception) { throw; } finally { dataBase.Close(); } return newTicket; }