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 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;
}