/// <summary>
/// return -1 if user does not exist
/// return -2 if user does exist and is the same as the user passed in the parameter.
/// return the ID if the user does.
/// </summary>
/// <param name="u"></param>
/// <returns></returns>
public static int isUsernameAvailable(Users u)
{
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
//"@Username"
System.Data.SqlClient.SqlDataReader reader = dba.execSP("CheckUserNameExist",
new string[] { "@Username" },
new string[] { u.Username });
int ret = -1;
if (reader.Read())
{
ret = int.Parse(reader[0].ToString().Trim());
}
// if the returned user is positive and is the same as the passed in user, return -2
if (ret == u.UserID)
{
return(-2);
}
// if the returned user is positive then return the actual user
// if the returned user is negative(i.e. not in the database) then return -1
return(ret);
}
public static List <Users> GetAllUsers()
{
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
//"@Username"
System.Data.SqlClient.SqlDataReader reader = dba.execSP("GetAllUsers",
new string[] { },
new string[] { });
List <Users> userlist = new List <Users>(20);
while (reader.Read())
{
int id = int.Parse(reader["UserID"].ToString());
string username = reader["Username"].ToString();
string password = reader["Password"].ToString();
string firstname = reader["FirstName"].ToString();
string lastname = reader["LastName"].ToString();
bool isAdmin = bool.Parse(reader["IsAdmin"].ToString());
//bool isAdmin = false;
//if (isAdminString.Trim().Equals("1"))
// isAdmin = true;
Users u = new Users(id, username, password, firstname, lastname, isAdmin);
userlist.Add(u);
}
return(userlist);
}
public static int SaveStatusType(StatusType st)
{
int id = st.StatusID;
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
System.Data.SqlClient.SqlDataReader reader = null;
if (st.StatusID == -1)
{
reader = dba.execSP("InsertStatusType",
new string[] { "@Name", "@Description" },
new System.Data.SqlDbType[] { System.Data.SqlDbType.VarChar, System.Data.SqlDbType.VarChar },
new object[] { st.StatusName, st.StatusDescription });
if (reader.Read())
{
id = int.Parse(reader["StatusID"].ToString());
}
}
else
{
reader = dba.execSP("UpdateStatusType",
new string[] { "@ID", "@Name", "@Description" },
new System.Data.SqlDbType[] { System.Data.SqlDbType.Int, System.Data.SqlDbType.VarChar, System.Data.SqlDbType.VarChar },
new object[] { st.StatusID, st.StatusName, st.StatusDescription });
if (reader.Read())
{
id = int.Parse(reader["StatusID"].ToString());
}
}
dba.CloseConn();
return(id);
}
public static List <Item> GetAllItemsBasedOnStatus(int statusIDin, int projectID)
{
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
System.Data.SqlClient.SqlDataReader reader = dba.execSP("GetAllItemsBasedOnStatusType",
new string[] { "@StatusID", "@ProjectID" }, new int[] { statusIDin, projectID });
List <Item> listOfItems = new List <Item>();
while (reader.Read())
{
// ItemID, Title, Note, CreateDate, I.CreatedBy, U1.FirstName+' '+U1.LastName 'CreatedByName', EditDate, I.EditedBy, U2.FirstName+' '+U2.LastName 'EditedByName', I.StatusID, S.Name
int id = int.Parse(reader["ItemID"].ToString()); // ok
string title = reader["Title"].ToString(); // ok
string note = reader["Note"].ToString(); // ok
DateTime createdOn = DateTime.Parse(reader["CreateDate"].ToString()); //ok
DateTime editedOn = DateTime.Parse(reader["EditDate"].ToString()); //ok
int createdBy = int.Parse(reader["CreatedBy"].ToString()); //ok
string createdByName = reader["CreatedByName"].ToString(); //ok
int editedBy = int.Parse(reader["EditedBy"].ToString());
string editedByName = reader["EditedByName"].ToString();
int statusID = int.Parse(reader["StatusID"].ToString());
string statusName = reader["Name"].ToString();
listOfItems.Add(new Item(id, title, note, createdOn, editedOn, editedBy, createdBy, editedByName, createdByName, statusID, statusName));
}
dba.CloseConn();
return(listOfItems);
}
public Login(string username, string password)
{
if (username == null || password == null)
{
return;
}
if (username.Trim().Length == 0)
{
return;
}
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
System.Data.SqlClient.SqlDataReader reader = dba.execSP("LoginUser",
new string[] { "@Username", "@Password" },
new string[] { username, password });
this.loginStatus = false;
// THIS NEEDS TO BE COMING FROM THE DATABASE BUT WILL NOT RIGHT NOW UNTIL FIELDS IN DB ARE SET
this.userStatus = true;
if (reader.Read())
{
//UserID, Username, FirstName, LastName, DateCreated
this.User.UserID = int.Parse(reader["UserID"].ToString());
this.User.Username = reader["Username"].ToString();
this.User.FirstName = reader["FirstName"].ToString();
this.User.LastName = reader["LastName"].ToString();
this.User.IsAdmin = bool.Parse(reader["IsAdmin"].ToString());
this.loginStatus = true;
}
reader.Close();
dba.CloseConn();
}
public static void SaveAttachments(List <Attachment> attachments)
{
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
List <Attachment> itemsout = new List <Attachment>(attachments.Count);
foreach (Attachment a in attachments)
{
// insert the item
if (a.ItemID != -1)
{
System.Data.SqlClient.SqlDataReader reader = dba.execSP("InsertAttachment",
new string[] { "@ItemID", "@Name", "@CreateDate", "@FileName", "@FileType", "@fileSize", "@FileOriginalLocation", "@data" },
new System.Data.SqlDbType[] { System.Data.SqlDbType.Int, System.Data.SqlDbType.VarChar, System.Data.SqlDbType.DateTime,
System.Data.SqlDbType.VarChar, System.Data.SqlDbType.VarChar, System.Data.SqlDbType.Int, System.Data.SqlDbType.VarChar,
System.Data.SqlDbType.VarBinary },
new object[] { a.ItemID.ToString(), a.Name, DateTime.Now.ToString(), a.FileName, a.FileType, a.size.ToString(), a.OriginalLocation.ToString(), a.data });
if (reader.Read())
{
int iid = int.Parse(reader["ItemID"].ToString());
a.AttachmentID = iid;
itemsout.Add(a);
}
// History.Insert(a, 1);
}
dba.CloseConn();
}
}
public static Item SaveItem(Item itemIn)
{
Item itemOut = itemIn;
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
if (itemIn.itemID == -1)
{
// INSERT INTO [dbo].[Items] ([Title],[Note],[CreateDate],[CreatedBy],[EditDate],[EditedBy],[StatusID],[ProjectID])
// VALUES(@Title,@Note,getDate(),@CreatedBy ,getDate(), @EditedBy,@StatusID, @ProjectID)
System.Data.SqlClient.SqlDataReader reader = dba.execSP("InsertItem",
new string[] { "@Title", "@Note", "@CreatedBy", "@EditedBy", "@StatusID", "@ProjectID" },
new string[] { itemIn.Title, itemIn.Note, itemIn.CreatedByID.ToString(), itemIn.EditedByID.ToString(), itemIn.StatusID.ToString(), itemIn.ProjectID.ToString() });
if (reader.Read())
{
int iid = int.Parse(reader["ItemID"].ToString());
itemOut.itemID = iid;
}
History.Insert(itemOut, 1);
}
else
{
// [dbo].[UpdateItem]
// @ID int, @Title varchar(200),@Note varchar(200),@EditedBy int,@StatusID int,@ProjectID int
System.Data.SqlClient.SqlDataReader reader = dba.execSP("UpdateItem",
new string[] { "@ItemID", "@Title", "@Note", "@EditedBy", "@StatusID", "@ProjectID" },
new string[] { itemIn.ItemID.ToString(), itemIn.Title, itemIn.Note, itemIn.EditedByID.ToString(), itemIn.StatusID.ToString(), itemIn.ProjectID.ToString() });
reader.Read();
History.Insert(itemOut, 2);
}
dba.CloseConn();
return(null);
}
/// <summary>
/// This will get the list of all attachments based on the attachement id
/// </summary>
/// <param name="inItemID"></param>
/// <returns></returns>
public static List <Attachment> GetAttachments(int inItemID)
{
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
System.Data.SqlClient.SqlDataReader reader = dba.execSP("GetAllAttachmentsBasedOnItemID",
new string[] { "@ItemID" }, new int[] { inItemID });
List <Attachment> listOfItems = new List <Attachment>();
while (reader.Read())
{
// [AttachmentID],[ItemID],[Name],[CreateDate],[FileName],[FileType],[FileSize],[FileOriginalLocation],[FileData]
int id = int.Parse(reader["AttachmentID"].ToString()); // ok
int iid = int.Parse(reader["ItemID"].ToString());
string name = reader["Name"].ToString(); // ok
DateTime createdOn = DateTime.Parse(reader["CreateDate"].ToString()); //ok
string fileName = reader["FileName"].ToString();
string fileType = reader["FileType"].ToString();
int fileSize = int.Parse(reader["FileSize"].ToString());//ok
string fileOriginalLocation = reader["FileOriginalLocation"].ToString();
byte[] buffer = (byte[])reader["FileData"];
Attachment a = new Attachment(id, iid, name, fileName, fileType, fileSize, fileOriginalLocation, buffer);
listOfItems.Add(a);
}
dba.CloseConn();
return(listOfItems);
}
public static Item GetItemBasedOnIndex(int itemIdIn)
{
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
System.Data.SqlClient.SqlDataReader reader = dba.execSP("GetItemBasedOnItemID",
new string[] { "@ItemID" }, new int[] { itemIdIn });
Item dbItem = null;
if (reader.Read())
{
// ItemID, Title, Note, CreateDate, I.CreatedBy, U1.FirstName+' '+U1.LastName 'CreatedByName', EditDate, I.EditedBy, U2.FirstName+' '+U2.LastName 'EditedByName', I.StatusID, S.Name
int id = int.Parse(reader["ItemID"].ToString()); // ok
string title = reader["Title"].ToString(); // ok
string note = reader["Note"].ToString(); // ok
DateTime createdOn = DateTime.Parse(reader["CreateDate"].ToString()); //ok
DateTime editedOn = DateTime.Parse(reader["EditDate"].ToString()); //ok
int createdBy = int.Parse(reader["CreatedBy"].ToString()); //ok
string createdByName = reader["CreatedByName"].ToString(); //ok
int editedBy = int.Parse(reader["EditedBy"].ToString());
string editedByName = reader["EditedByName"].ToString();
int statusID = int.Parse(reader["StatusID"].ToString());
string statusName = reader["Name"].ToString();
dbItem = new Item(id, title, note, createdOn, editedOn, editedBy, createdBy, editedByName, createdByName, statusID, statusName);
}
dba.CloseConn();
return(dbItem);
}
public static List <History> GetHistoryOfItem(Item item)
{
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
System.Data.SqlClient.SqlDataReader reader = dba.execSP("GetHistoryBasedOnItemID",
new string[] { "@ItemID" },
new string[] { item.ItemID.ToString() });
List <History> l = new List <History>(20);
while (reader.Read())
{
//HistoryID, ItemID, Title, Note, CreateDate, U1.username 'CreatedBy', EditDate, U2.username 'EditedBy', ST.Name 'Status', HistoryType
int hid = int.Parse(reader["HistoryID"].ToString());
string title = reader["Title"].ToString();
string note = reader["Note"].ToString();
DateTime editDate = DateTime.Parse(reader["EditDate"].ToString());
string editedUser = reader["EditedBy"].ToString();
string status = reader["Status"].ToString();
string historyType = reader["HistoryType"].ToString();
History h = new History(hid, item.ItemID, title, note, editDate, editedUser, status, historyType);
l.Add(h);
}
dba.CloseConn();
return(l);
}
public static void Insert(Item item, int HistoryTypeID)
{
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
System.Data.SqlClient.SqlDataReader reader = dba.execSP("InsertHistory",
new string[] { "@ItemID", "@HistoryTypeID" },
new string[] { item.ItemID.ToString(), HistoryTypeID.ToString() });
reader.Read();
dba.CloseConn();
}
public static List <Project> GetAllProjects()
{
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
System.Data.SqlClient.SqlDataReader reader = dba.execSP("GetAllProjects");
List <Project> projectList = new List <Project>();
while (reader.Read())
{
//[ProjectID],[ProjectName],[ProjectDescription]
int id = int.Parse(reader["ProjectID"].ToString());
string projectName = reader["ProjectName"].ToString();
string projectDesc = reader["ProjectDescription"].ToString();
projectList.Add(new Project(id, projectName, projectDesc));
}
dba.CloseConn();
return(projectList);
}
public static int SaveProject(Project p)
{
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
System.Data.SqlClient.SqlDataReader reader = dba.execSP("SaveProject",
new string[] { "@ProjectID", "@ProjectName", "@ProjectDescription" },
new System.Data.SqlDbType[] { System.Data.SqlDbType.Int, System.Data.SqlDbType.VarChar, System.Data.SqlDbType.VarChar },
new object[] { p.ProjectID.ToString(), p.ProjectName, p.ProjectDescription });
int id = -1;
if (reader.Read())
{
id = int.Parse(reader["projectID"].ToString());
}
dba.CloseConn();
return(id);
}
public static void DeleteAttachments(Attachment attachment)
{
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
// insert the item
if (attachment.ItemID != -1)
{
System.Data.SqlClient.SqlDataReader reader = dba.execSP("DeleteAttachment",
new string[] { "@AttachmentID" },
new System.Data.SqlDbType[] { System.Data.SqlDbType.Int },
new object[] { attachment.AttachmentID });
reader.Read();
// History.Insert(a, 1);
}
dba.CloseConn();
}
public static List <StatusType> GetAllStatusTypes()
{
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
System.Data.SqlClient.SqlDataReader reader = dba.execSP("GetAllStatusTypes");
List <StatusType> listOfStatus = new List <StatusType>(4);// Default to save perfomance
while (reader.Read())
{
int id = int.Parse(reader["StatusID"].ToString());
string nm = reader["Name"].ToString();
string desc = reader["Description"].ToString();
listOfStatus.Add(new StatusType(id, nm, desc));
}
dba.CloseConn();
return(listOfStatus);
}
public static void SaveUsers(Users u)
{
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
if (u.UserID == -1)
{
//"@Username", "@Password", "@FirstName", "@LastName", "@DateCreated"
System.Data.SqlClient.SqlDataReader reader = dba.execSP("InsertUser",
new string[] { "@Username", "@Password", "@FirstName", "@LastName", "@isAdmin" },
new string[] { u.Username, u.Password, u.FirstName, u.LastName, u.IsAdmin.ToString() });
reader.Read();
}
else
{
//"@Username", "@Password", "@FirstName", "@LastName", "@DateCreated"
System.Data.SqlClient.SqlDataReader reader = dba.execSP("UpdateUser",
new string[] { "@Username", "@Password", "@FirstName", "@LastName", "@IsAdmin" },
new string[] { u.Username, u.Password, u.FirstName, u.LastName, u.IsAdmin.ToString() });
reader.Read();
}
}
public static List <SystemStat> GetProjectStats(int ProjectID)
{
ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection();
System.Data.SqlClient.SqlDataReader reader = dba.execSP("GetProjectStats",
new string[] { "@ProjectID" },
new string[] { ProjectID.ToString() });
List <SystemStat> li = new List <SystemStat>(20);
while (reader.Read())
{
string title = reader["Title"].ToString();
int amount = int.Parse(reader["Count"].ToString());
li.Add(new SystemStat(title, amount));
}
setPercentage(li);
return(li);
}
