public void Save()
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO admins (name, login_name, password) OUTPUT INSERTED.id VALUES (@AdminName, @UserName, @Password);", conn);
cmd.Parameters.Add(new SqlParameter("@AdminName", this.GetName()));
cmd.Parameters.Add(new SqlParameter("@UserName", this.GetUsername()));
cmd.Parameters.Add(new SqlParameter("@Password", this.GetPassword()));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
this._id = rdr.GetInt32(0);
}
DB.CloseSqlConnection(conn, rdr);
}
public void Save()
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO articles (title, date_created, text) OUTPUT INSERTED.id VALUES (@ArticleTitle, @ArticleDate, @ArticleText);", conn);
cmd.Parameters.Add(new SqlParameter("@ArticleTitle", this.GetTitle()));
cmd.Parameters.Add(new SqlParameter("@ArticleDate", this.GetDate()));
cmd.Parameters.Add(new SqlParameter("@ArticleText", this.GetText()));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
this._id = rdr.GetInt32(0);
}
DB.CloseSqlConnection(conn, rdr);
}
public static int GetNumberOpen()
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT COUNT(id) FROM tickets WHERE open_status = 1;", conn);
SqlDataReader rdr = cmd.ExecuteReader();
int numberOpen = 0;
while (rdr.Read())
{
numberOpen = rdr.GetInt32(0);
}
DB.CloseSqlConnection(conn, rdr);
return(numberOpen);
}
public void Save()
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO notes (date_created, admin_id, ticket_id, text) OUTPUT INSERTED.id VALUES (@NoteDate, @AdminId, @TicketId, @NoteText);", conn);
cmd.Parameters.Add(new SqlParameter("@NoteDate", this.GetDate()));
cmd.Parameters.Add(new SqlParameter("@AdminId", this.GetAdminId()));
cmd.Parameters.Add(new SqlParameter("@TicketId", this.GetTicketId()));
cmd.Parameters.Add(new SqlParameter("@NoteText", this.GetText()));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
this._id = rdr.GetInt32(0);
}
DB.CloseSqlConnection(conn, rdr);
}
public void Save()
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO users (name, address, phone, email) OUTPUT INSERTED.id VALUES (@UserName, @UserAddress, @UserPhone, @UserEmail);", conn);
cmd.Parameters.Add(new SqlParameter("@UserName", this.GetName()));
cmd.Parameters.Add(new SqlParameter("@UserAddress", this.GetAddress()));
cmd.Parameters.Add(new SqlParameter("@UserPhone", this.GetPhone()));
cmd.Parameters.Add(new SqlParameter("@UserEmail", this.GetEmail()));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
this._id = rdr.GetInt32(0);
}
DB.CloseSqlConnection(conn, rdr);
}
// end Constructors and Getters
public string GetAdminName()
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT admins.name FROM admins JOIN notes ON(admins.id = notes.admin_id) WHERE notes.id = @NoteId;", conn);
cmd.Parameters.Add(new SqlParameter("@NoteId", this.GetId()));
SqlDataReader rdr = cmd.ExecuteReader();
string foundName = null;
while (rdr.Read())
{
foundName = rdr.GetString(0);
}
DB.CloseSqlConnection(conn, rdr);
return(foundName);
}
public static List <Ticket> GetAllByDept(int id)
{
List <Ticket> allTickets = new List <Ticket> {
};
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT tickets.* FROM tickets JOIN departments ON(tickets.department_id = departments.id) WHERE departments.id = @DeptId", conn);
cmd.Parameters.Add(new SqlParameter("@DeptId", id));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int idTicket = rdr.GetInt32(0);
DateTime ticketNumberTicket = rdr.GetDateTime(1);
string productTicket = rdr.GetString(2);
int departmentIdTicket = rdr.GetInt32(3);
string severityTicket = rdr.GetString(4);
string descriptionTicket = rdr.GetString(5);
int userIdTicket = rdr.GetInt32(6);
int openTicket = rdr.GetByte(7);
string ticketStatus = rdr.GetString(8);
string convTicketNum = rdr.GetString(9);
Ticket newTicket = new Ticket(ticketNumberTicket, productTicket, descriptionTicket, departmentIdTicket, userIdTicket, severityTicket, idTicket, openTicket, ticketStatus);
newTicket.SetConvertedTicketNum(convTicketNum);
allTickets.Add(newTicket);
}
DB.CloseSqlConnection(conn, rdr);
return(allTickets);
}
public static List <Department> GetAll()
{
List <Department> allDepartments = new List <Department>();
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM departments ORDER BY name;", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int foundId = rdr.GetInt32(0);
string foundName = rdr.GetString(1);
Department foundDepartment = new Department(foundName, foundId);
allDepartments.Add(foundDepartment);
}
DB.CloseSqlConnection(conn, rdr);
return(allDepartments);
}
// will return 01/01/0001 00:00:00 if there is no close date !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
public DateTime GetClosedDate()
{
SqlConnection conn = DB.Connection();
conn.Open();
// SqlCommand cmd = new SqlCommand("SELECT COUNT(id) FROM tickets WHERE open_status = 1;", conn);
SqlCommand cmd = new SqlCommand("SELECT * FROM tickets WHERE id = @TicketId AND open_status = 0;", conn);
cmd.Parameters.Add(new SqlParameter("@TicketId", this.GetId()));
SqlDataReader rdr = cmd.ExecuteReader();
DateTime closeDate = new DateTime();
while (rdr.Read())
{
closeDate = rdr.GetDateTime(10);
}
DB.CloseSqlConnection(conn, rdr);
return(closeDate);
}
