/// <summary>
/// Update a user's profile. Maybe we could instead have properties for each
/// member and update the database in the destructor ...
/// NOTE: That would not be a good idea due to the garbage collection issues
/// that I discovered with the DBDriver class.
/// </summary>
public void updateProfile()
{
//this isn't working... why? is my update wrong?
//no, a multiple update like that works...
//is it losing something elsewhere?
DBDriver myDB = new DBDriver();
myDB.Query = "update users set password=@pwd, security=@sec where id=@uID;";
//TODO: Add in changing username(need to verify availability)
//myDB.addParam("@name", this.userName);
myDB.addParam("@pwd", this.password);
myDB.addParam("@sec", this.role);
myDB.addParam("@uID", this.id);
myDB.nonQuery();
myDB.Query = "update person set firstName=@first, lastName=@last, address=@address, city=@city, "
+ "state=@state, zip=@zip, phoneNumber=@phone, email=@mail where id=@uID;";
myDB.addParam("@first", this.firstName);
myDB.addParam("@last", this.lastName);
myDB.addParam("@address", this.address);
myDB.addParam("@city", this.city);
myDB.addParam("@state", this.state);
myDB.addParam("@zip", this.zip);
myDB.addParam("@phone", this.phone);
myDB.addParam("@mail", this.email);
myDB.addParam("@uID", this.id);
myDB.nonQuery();
}
/// <summary>
/// Remove a Task
/// </summary>
/// <param name="id">project id</param>
public static void remove(string id)
{
DBDriver myDB = new DBDriver();
myDB.Query = "delete from Tasks where id=@id";
myDB.addParam("@id", id);
myDB.nonQuery();
}
static public void declineNewUser(string delID)
{
DBDriver myDB = new DBDriver();
myDB.Query = "delete from newUsers where id=@id;";
myDB.addParam("@id", delID);
myDB.nonQuery();
myDB.Query = "delete from person where id=@id;";
myDB.addParam("@id", delID);
myDB.nonQuery();
}
/// <summary>
/// Gets a DataSet with all Projects
/// </summary>
/// <returns></returns>
public static DataSet getProjectsDataSet()
{
DBDriver myDB = new DBDriver();
myDB.Query = "select * from projects;";
DataSet ds = new DataSet();
myDB.createAdapter().Fill(ds);
return(ds);
}
/// <summary>
/// Gets a DataSet of Tasks in the specified Module
/// </summary>
/// <param name="id">Module id</param>
/// <returns>Tasks DataSet</returns>
public static DataSet getTasksDataSet(string id)
{
DBDriver myDB = new DBDriver();
myDB.Query = "select * from tasks\n" +
"where moduleID = @ID;";
myDB.addParam("@ID", id);
DataSet ds = new DataSet();
myDB.createAdapter().Fill(ds);
return(ds);
}
/// <summary>
/// Verify that an email address exists in the database
/// </summary>
/// <param name="email">email address to verify</param>
/// <returns>true if it exists, false if it doesn't</returns>
static public bool verifyEmailExists(string email)
{
DBDriver myDB = new DBDriver();
myDB.Query = "select count(*) from person where email=@email;";
myDB.addParam("@email", email);
int k = Convert.ToInt32(myDB.scalar());
if (k == 1)
{
return(true);
}
return(false);
}
/// <summary>
/// Insert a Project in the database
/// </summary>
/// <param name="name"></param>
/// <param name="mID">Assigned manager</param>
/// <param name="description"></param>
/// <param name="startDate">Start date</param>
public static void create(string name, string mID, string description, string startDate)
{
DBDriver myDB = new DBDriver();
myDB.Query = "insert into projects (name, managerID, description, startDate) \n"
+ "values ( @name, @mID, @desc, @start, @end );";
myDB.addParam("@name", name);
myDB.addParam("@mID", mID);
myDB.addParam("@desc", description);
myDB.addParam("@start", startDate);
myDB.nonQuery();
}
/// <summary>
/// Insert a Module in the database
/// </summary>
/// <param name="name"></param>
/// <param name="pID">Project to add to</param>
/// <param name="description"></param>
/// <param name="startDate">Start date</param>
public static void create(string name, string pID, string description, string startDate)
{
DBDriver myDB = new DBDriver();
myDB.Query = "insert into modules (name, projectID, description, startDate) \n"
+ "values ( @name, @pID, @desc, @start);";
myDB.addParam("@name", name);
myDB.addParam("@pID", pID);
myDB.addParam("@desc", description);
myDB.addParam("@start", startDate);
myDB.nonQuery();
}
/// <summary>
/// Gets a DataSet with Projects assigned to a PM
/// </summary>
/// <param name="mgrID">Project Manager</param>
/// <returns></returns>
public static DataSet getProjectsDataSet(string mgrID)
{
DBDriver myDB = new DBDriver();
myDB.Query = "select * from projects \n"
+ "where managerID=@mgrID;";
myDB.addParam("@mgrID", mgrID);
DataSet ds = new DataSet();
myDB.createAdapter().Fill(ds);
return(ds);
}
static public User approveNewUser(string ID)
{
DBDriver myDB = new DBDriver();
myDB.Query = "insert into users select * from newUsers where id=@id;";
myDB.addParam("@id", ID);
myDB.nonQuery();
//need to delete said info from the newUsers table
myDB.Query = "delete from newUsers where id=@id;";
myDB.addParam("@id", ID);
myDB.nonQuery();
return(new User(ID));
}
/// <summary>
/// Insert a Task in the database
/// </summary>
/// <param name="name"></param>
/// <param name="mID">Module to add to</param>
/// <param name="description"></param>
/// <param name="startDate">Start date</param>
public static void create(string name, string mID, string description, string startDate)
{
DBDriver myDB = new DBDriver();
myDB.Query = "insert into tasks (name, moduleID, description, startDate, complete) \n"
+ "values ( @name, @mID, @desc, @start, @complete );";
myDB.addParam("@name", name);
myDB.addParam("@mID", mID);
myDB.addParam("@desc", description);
myDB.addParam("@start", startDate);
myDB.addParam("@complete", PMT.TaskStatus.UNASSIGNED);
myDB.nonQuery();
}
/// <summary>
/// Gets a DataSet filled with a projects fields
/// </summary>
public DataSet getDataSet()
{
DBDriver myDB = new DBDriver();
myDB.Query =
"select * from projects \n" +
"where ID = @ID;";
myDB.addParam("@ID", id);
DataSet ds = new DataSet();
myDB.createAdapter().Fill(ds);
return(ds);
}
bool CustomAuthenticate(string username, string password)
{
DBDriver db = new DBDriver();
string q = "select count(*) from softeng4.users where userName='******';";
db.Query = q;
int k = (int)db.scalar();
if (k == 0)
{
//user does not exist in DB
ErrorLabel.Text = "You have entered an unknown username.";
return(false);
}
else
{
q = "select count(*) from softeng4.users u where u.userName='******' and u.password='******'";
db.Query = q;
k = (int)db.scalar();
if (k == 0)
{
//password incorrect
ErrorLabel.Text = "You have entered an incorrect password.";
return(false);
}
else
{
//successful authentication
q = "select u.security s, u.ID id, p.firstName fname, p.lastName lname from softeng4.users u, softeng4.person p where u.ID = p.ID and u.username='******'";
db.Query = q;
SqlDataReader dr = db.createReader();
dr.Read();
user = new User(dr["id"].ToString());
db.close();
// create the cookie
Response.Cookies["user"].Values.Add("role", user.Role);
Response.Cookies["user"].Values.Add("id", user.ID);
Response.Cookies["user"].Values.Add("name", user.UserName);
Response.Cookies["user"].Values.Add("fname", user.FirstName);
Response.Cookies["user"].Values.Add("lname", user.LastName);
return(true);
}
}
}
/// <summary>
/// Update a Task
/// </summary>
/// <param name="name"></param>
/// <param name="description"></param>
/// <param name="startDate"></param>
new public void update(string name, string description, string startDate)
{
base.update(name, description, startDate);
DBDriver myDB = new DBDriver();
myDB.Query = "update tasks \n"
+ "set name=@name, description=@desc, startDate=@start\n"
+ "where id=@id;";
myDB.addParam("@id", this.id);
myDB.addParam("@name", name);
myDB.addParam("@desc", description);
myDB.addParam("@start", startDate);
myDB.nonQuery();
}
bool CustomAuthenticate(string username, string password)
{
DBDriver db = new DBDriver();
string q="select count(*) from softeng4.users where userName='******';";
db.Query = q;
int k=(int)db.scalar();
if(k==0)
{
//user does not exist in DB
ErrorLabel.Text = "You have entered an unknown username.";
return false;
}
else
{
q="select count(*) from softeng4.users u where u.userName='******' and u.password='******'";
db.Query = q;
k=(int)db.scalar();
if(k==0)
{
//password incorrect
ErrorLabel.Text = "You have entered an incorrect password.";
return false;
}
else
{
//successful authentication
q="select u.security s, u.ID id, p.firstName fname, p.lastName lname from softeng4.users u, softeng4.person p where u.ID = p.ID and u.username='******'";
db.Query = q;
SqlDataReader dr=db.createReader();
dr.Read();
user = new User(dr["id"].ToString());
db.close();
// create the cookie
Response.Cookies["user"].Values.Add("role", user.Role);
Response.Cookies["user"].Values.Add("id", user.ID);
Response.Cookies["user"].Values.Add("name", user.UserName);
Response.Cookies["user"].Values.Add("fname", user.FirstName);
Response.Cookies["user"].Values.Add("lname", user.LastName);
return true;
}
}
}
public Task(string name, string moduleID, string description, string startDate, string compexity)
: base(name, description, startDate)
{
DBDriver db = new DBDriver();
// Create Adapter
db.Query = "select * from Tasks";
SqlDataAdapter da = db.createAdapter();
// We need this to get an ID back from the database
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// Create and initialize CommandBuilder
SqlCommandBuilder dbCB = new SqlCommandBuilder(da);
// New DataSet
DataSet ds = new DataSet();
// Populate DataSet with data
da.Fill(ds, "Tasks");
// Get reference to our table
DataTable table = ds.Tables["Tasks"];
// Create new row
DataRow row = table.NewRow();
// Store data in the row
row["Name"] = this.name = name;
row["moduleID"] = this.modID = moduleID;
row["description"] = this.description = description;
row["startDate"] = this.startDate = startDate;
row["complete"] = this.status = TaskStatus.UNASSIGNED;
row["complexity"] = this.complexity = compexity;
// Add row back to table
table.Rows.Add(row);
// Update data source
da.Update(ds, "Tasks");
// Get newFileID
if (!row.IsNull("ID"))
{
id = row["ID"].ToString();
}
}
/// <summary>
/// Create a new User
/// </summary>
/// <param name="un">The username</param>
/// <param name="pwd">the Password</param>
/// <param name="r">the security/role</param>
/// <param name="fn">the first name</param>
/// <param name="ln">the last name</param>
/// <param name="em">the email address</param>
/// <param name="ph">the phone number</param>
/// <param name="ad">the address</param>
/// <param name="c">the city</param>
/// <param name="s">the state</param>
/// <param name="z">thezip code</param>
public User(string un, string pwd, string r, string fn, string ln,
string em, string ph, string ad, string c, string s, string z)
{
this.userName = un;
this.password = Encryption.encrypt(pwd);
//this.id = id;
this.role = r;
this.firstName=fn;
this.lastName=ln;
this.email=em;
this.phone=ph;
this.address=ad;
this.city=c;
this.state=s;
this.zip=z;
myDB=new DBDriver();
myDB.Query = "insert into person (firstName, lastName, address, city, state, zip, phoneNumber, email) " +
"values (@first,@last,@address,@city,@state,@zip,@phone,@email);";
myDB.addParam("@first", firstName);
myDB.addParam("@last", lastName);
myDB.addParam("@address", address);
myDB.addParam("@city", city);
myDB.addParam("@state", state);
myDB.addParam("@zip", zip);
myDB.addParam("@phone", phone);
myDB.addParam("@email", email);
myDB.nonQuery();
//get the user id from the person table to satisfy the user tables foreign constraint
myDB.Query="select id from person where email=@email;";
myDB.addParam("@email", email);
this.id=myDB.scalar().ToString();
// TODO: when administrator approves, this is transferred to the users table
// for now, this is stored in the newUsers table
myDB.Query = "insert into newUsers (ID, password, userName, security)\n" +
"values (@id, @pwd,@username,@sec);";
myDB.addParam("@id", id);
myDB.addParam("@pwd", this.password);
myDB.addParam("@username", this.userName);
myDB.addParam("@sec", this.role);
myDB.nonQuery();
}
/// <summary>
/// Create a new User
/// </summary>
/// <param name="un">The username</param>
/// <param name="pwd">the Password</param>
/// <param name="r">the security/role</param>
/// <param name="fn">the first name</param>
/// <param name="ln">the last name</param>
/// <param name="em">the email address</param>
/// <param name="ph">the phone number</param>
/// <param name="ad">the address</param>
/// <param name="c">the city</param>
/// <param name="s">the state</param>
/// <param name="z">thezip code</param>
public User(string un, string pwd, string r, string fn, string ln,
string em, string ph, string ad, string c, string s, string z)
{
this.userName = un;
this.password = Encryption.encrypt(pwd);
//this.id = id;
this.role = r;
this.firstName = fn;
this.lastName = ln;
this.email = em;
this.phone = ph;
this.address = ad;
this.city = c;
this.state = s;
this.zip = z;
myDB = new DBDriver();
myDB.Query = "insert into person (firstName, lastName, address, city, state, zip, phoneNumber, email) " +
"values (@first,@last,@address,@city,@state,@zip,@phone,@email);";
myDB.addParam("@first", firstName);
myDB.addParam("@last", lastName);
myDB.addParam("@address", address);
myDB.addParam("@city", city);
myDB.addParam("@state", state);
myDB.addParam("@zip", zip);
myDB.addParam("@phone", phone);
myDB.addParam("@email", email);
myDB.nonQuery();
//get the user id from the person table to satisfy the user tables foreign constraint
myDB.Query = "select id from person where email=@email;";
myDB.addParam("@email", email);
this.id = myDB.scalar().ToString();
// TODO: when administrator approves, this is transferred to the users table
// for now, this is stored in the newUsers table
myDB.Query = "insert into newUsers (ID, password, userName, security)\n" +
"values (@id, @pwd,@username,@sec);";
myDB.addParam("@id", id);
myDB.addParam("@pwd", this.password);
myDB.addParam("@username", this.userName);
myDB.addParam("@sec", this.role);
myDB.nonQuery();
}
/// <summary>
/// Create a project object from the database
/// </summary>
/// <param name="id">a project id</param>
public Project(string id)
: base(id)
{
myDB = new DBDriver();
myDB.Query = "select * from projects \n" +
"where ID = @ID;";
myDB.addParam("@ID", id);
DataSet ds = new DataSet();
myDB.createAdapter().Fill(ds);
DataTable table = ds.Tables[0];
DataRow row = table.Rows[0];
this.name = row["Name"].ToString();
this.id = row["id"].ToString();
this.mgrID = row["managerID"].ToString();
this.description = row["description"].ToString();
this.startDate = row["startDate"].ToString();
this.expEndDate = row["expEndDate"].ToString();
this.actEndDate = row["actEndDate"].ToString();
}
/// <summary>
/// Create user from id
/// </summary>
/// <param name="id">User ID number</param>
public User(string id)
{
// get user info from db
this.id = id;
DBDriver myDB = new DBDriver();
myDB.Query = "select * from person p, users u where p.id=@id and u.id=p.id;";
myDB.addParam("@id", this.id);
SqlDataReader dr = myDB.createReader();
dr.Read();
this.userName = dr["userName"].ToString();
this.address = dr["address"].ToString();
this.city = dr["city"].ToString();
this.email = dr["email"].ToString();
this.firstName = dr["firstName"].ToString();
this.lastName = dr["lastName"].ToString();
this.password = dr["password"].ToString();
this.phone = dr["phoneNumber"].ToString();
this.role = dr["security"].ToString();
this.state = dr["state"].ToString();
this.zip = dr["zip"].ToString();
myDB.close();
}
/// <summary>
/// Verify that a username exists in the database
/// </summary>
/// <param name="userName">the username to verify</param>
/// <returns>true if it exists, false if it doesn't</returns>
static public bool verifyUserNameExists(string userName, bool isNew)
{
DBDriver myDB = new DBDriver();
myDB.Query = "select count(*) from users where userName=@name;";
myDB.addParam("@name", userName);
int k = Convert.ToInt32(myDB.scalar());
if (k != 1)
{
if (isNew)
{
myDB.Query = "select count(*) from newUsers where userName=@name;";
myDB.addParam("@name", userName);
k = Convert.ToInt32(myDB.scalar());
}
}
if (k == 1)
{
return(true);
}
return(false);
}
public Module(string name, string projectID, string description, string startDate)
: base(name, description, startDate)
{
DBDriver db = new DBDriver();
// Create Adapter
db.Query = "select * from Modules";
SqlDataAdapter da = db.createAdapter();
// We need this to get an ID back from the database
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// Create and initialize CommandBuilder
SqlCommandBuilder dbCB = new SqlCommandBuilder(da);
// New DataSet
DataSet ds = new DataSet();
// Populate DataSet with data
da.Fill(ds, "Modules");
// Get reference to our table
DataTable table = ds.Tables["Modules"];
// Create new row
DataRow row = table.NewRow();
// Store data in the row
row["Name"] = this.name = name;
row["projectID"] = this.projID = projectID;
row["description"] = this.description = description;
row["startDate"] = this.startDate = startDate;
// Add row back to table
table.Rows.Add(row);
// Update data source
da.Update(ds, "Modules");
// Get newFileID
if( !row.IsNull("ID") )
id = row["ID"].ToString();
}
public static void declineNewUser(string delID)
{
DBDriver myDB=new DBDriver();
myDB.Query="delete from newUsers where id=@id;";
myDB.addParam("@id", delID);
myDB.nonQuery();
myDB.Query="delete from person where id=@id;";
myDB.addParam("@id", delID);
myDB.nonQuery();
}
public static User approveNewUser(string ID)
{
DBDriver myDB=new DBDriver();
myDB.Query="insert into users select * from newUsers where id=@id;";
myDB.addParam("@id", ID);
myDB.nonQuery();
//need to delete said info from the newUsers table
myDB.Query="delete from newUsers where id=@id;";
myDB.addParam("@id", ID);
myDB.nonQuery();
return new User(ID);
}
/// <summary>
/// Create user from id
/// </summary>
/// <param name="id">User ID number</param>
public User(string id)
{
// get user info from db
this.id=id;
DBDriver myDB=new DBDriver();
myDB.Query="select * from person p, users u where p.id=@id and u.id=p.id;";
myDB.addParam("@id", this.id);
SqlDataReader dr=myDB.createReader();
dr.Read();
this.userName=dr["userName"].ToString();
this.address=dr["address"].ToString();
this.city=dr["city"].ToString();
this.email=dr["email"].ToString();
this.firstName=dr["firstName"].ToString();
this.lastName=dr["lastName"].ToString();
this.password=dr["password"].ToString();
this.phone=dr["phoneNumber"].ToString();
this.role=dr["security"].ToString();
this.state=dr["state"].ToString();
this.zip=dr["zip"].ToString();
myDB.close();
}
/// <summary>
/// Update a Task
/// </summary>
/// <param name="name"></param>
/// <param name="description"></param>
/// <param name="startDate"></param>
public new void update(string name, string description, string startDate)
{
base.update(name, description, startDate);
DBDriver myDB = new DBDriver();
myDB.Query="update tasks \n"
+"set name=@name, description=@desc, startDate=@start\n"
+"where id=@id;";
myDB.addParam("@id", this.id);
myDB.addParam("@name", name);
myDB.addParam("@desc", description);
myDB.addParam("@start", startDate);
myDB.nonQuery();
}
/// <summary>
/// Assign a Developer
/// </summary>
/// <param name="dev"></param>
public void assignDeveloper(string devID)
{
this.devID = devID;
DBDriver db = new DBDriver();
db.Query = "insert into assignments (taskID, devID, dateAss)\n"
+ "values (@taskID, @devID, @date)";
db.addParam("@taskID", this.id);
db.addParam("@devID", devID);
db.addParam("@date", Convert.ToString(DateTime.Now));
db.nonQuery();
db.Query = "update tasks set complete = @complete\n"
+ "where ID = @taskID;";
db.addParam("@complete", PMT.TaskStatus.INPROGRESS);
db.addParam("@taskID", this.id);
db.nonQuery();
db.Query = "select competence from compLevels where ID = @devID";
db.addParam("@devID", devID);
SqlDataReader dr = db.createReader();
dr.Read();
string competence = dr["competence"].ToString();
db.close();
string length;
if( complexity == "Low" )
db.Query = "select lowComplexity as length from compmatrix where compLevel = @competence";
else if ( complexity == "Medium" )
db.Query = "select medComplexity as length from compmatrix where compLevel = @competence";
else if ( complexity == "High" )
db.Query = "select highComplexity as length from compmatrix where compLevel = @competence";
db.addParam("@competence", competence);
dr = db.createReader();
dr.Read();
length = dr["length"].ToString();
db.close();
//TimeSpan temp = new TimeSpan(Convert.ToInt32(length), 0, 0, 0);
DateTime start = Convert.ToDateTime(this.startDate);
double hours = Convert.ToDouble(length);
double days = Math.Ceiling(hours/8);
DateTime end = start.AddDays(days);
this.expEndDate = end.ToShortDateString();
db.Query = "update tasks set expEndDate = @expEndDate\n"
+ "where ID = @taskID;";
db.addParam("@expEndDate", this.expEndDate);
db.addParam("@taskID", this.id);
db.nonQuery();
//TODO
// modid = ||select moduleid from tasks where id = @taskid;
//
//maximum = max of ||select tasks.expenddate from tasks where tasks.moduleid = @modid
//
//update modules set expenddate = @maximum where id = @modid
//
//projid = ||select projectid from modules where id = @modid
//
//maximum = max of ||select expenddate from modules where projectid = @projid
//
//update project set expenddate = @maximum where id = @projid
}
/// <summary>
/// Create a project object from the database
/// </summary>
/// <param name="id">a project id</param>
public Project(string id)
: base(id)
{
myDB = new DBDriver();
myDB.Query = "select * from projects \n" +
"where ID = @ID;";
myDB.addParam("@ID", id);
DataSet ds = new DataSet();
myDB.createAdapter().Fill(ds);
DataTable table = ds.Tables[0];
DataRow row = table.Rows[0];
this.name = row["Name"].ToString();
this.id = row["id"].ToString();
this.mgrID = row["managerID"].ToString();
this.description = row["description"].ToString();
this.startDate = row["startDate"].ToString();
this.expEndDate = row["expEndDate"].ToString();
this.actEndDate = row["actEndDate"].ToString();
}
/// <summary>
/// Update a user's profile. Maybe we could instead have properties for each
/// member and update the database in the destructor ...
/// NOTE: That would not be a good idea due to the garbage collection issues
/// that I discovered with the DBDriver class.
/// </summary>
public void updateProfile()
{
//this isn't working... why? is my update wrong?
//no, a multiple update like that works...
//is it losing something elsewhere?
DBDriver myDB=new DBDriver();
myDB.Query="update users set password=@pwd, security=@sec where id=@uID;";
//TODO: Add in changing username(need to verify availability)
//myDB.addParam("@name", this.userName);
myDB.addParam("@pwd", this.password);
myDB.addParam("@sec", this.role);
myDB.addParam("@uID", this.id);
myDB.nonQuery();
myDB.Query="update person set firstName=@first, lastName=@last, address=@address, city=@city, "
+"state=@state, zip=@zip, phoneNumber=@phone, email=@mail where id=@uID;";
myDB.addParam("@first", this.firstName);
myDB.addParam("@last", this.lastName);
myDB.addParam("@address", this.address);
myDB.addParam("@city", this.city);
myDB.addParam("@state", this.state);
myDB.addParam("@zip", this.zip);
myDB.addParam("@phone", this.phone);
myDB.addParam("@mail", this.email);
myDB.addParam("@uID", this.id);
myDB.nonQuery();
}
/// <summary>
/// Assign a Developer
/// </summary>
/// <param name="dev"></param>
public void assignDeveloper(string devID)
{
this.devID = devID;
DBDriver db = new DBDriver();
db.Query = "insert into assignments (taskID, devID, dateAss)\n"
+ "values (@taskID, @devID, @date)";
db.addParam("@taskID", this.id);
db.addParam("@devID", devID);
db.addParam("@date", Convert.ToString(DateTime.Now));
db.nonQuery();
db.Query = "update tasks set complete = @complete\n"
+ "where ID = @taskID;";
db.addParam("@complete", PMT.TaskStatus.INPROGRESS);
db.addParam("@taskID", this.id);
db.nonQuery();
db.Query = "select competence from compLevels where ID = @devID";
db.addParam("@devID", devID);
SqlDataReader dr = db.createReader();
dr.Read();
string competence = dr["competence"].ToString();
db.close();
string length;
if (complexity == "Low")
{
db.Query = "select lowComplexity as length from compmatrix where compLevel = @competence";
}
else if (complexity == "Medium")
{
db.Query = "select medComplexity as length from compmatrix where compLevel = @competence";
}
else if (complexity == "High")
{
db.Query = "select highComplexity as length from compmatrix where compLevel = @competence";
}
db.addParam("@competence", competence);
dr = db.createReader();
dr.Read();
length = dr["length"].ToString();
db.close();
//TimeSpan temp = new TimeSpan(Convert.ToInt32(length), 0, 0, 0);
DateTime start = Convert.ToDateTime(this.startDate);
double hours = Convert.ToDouble(length);
double days = Math.Ceiling(hours / 8);
DateTime end = start.AddDays(days);
this.expEndDate = end.ToShortDateString();
db.Query = "update tasks set expEndDate = @expEndDate\n"
+ "where ID = @taskID;";
db.addParam("@expEndDate", this.expEndDate);
db.addParam("@taskID", this.id);
db.nonQuery();
//TODO
// modid = ||select moduleid from tasks where id = @taskid;
//
//maximum = max of ||select tasks.expenddate from tasks where tasks.moduleid = @modid
//
//update modules set expenddate = @maximum where id = @modid
//
//projid = ||select projectid from modules where id = @modid
//
//maximum = max of ||select expenddate from modules where projectid = @projid
//
//update project set expenddate = @maximum where id = @projid
}
/// <summary>
/// Gets a DataSet filled with a projects fields
/// </summary>
public DataSet getDataSet()
{
DBDriver myDB = new DBDriver();
myDB.Query =
"select * from projects \n" +
"where ID = @ID;";
myDB.addParam("@ID", id);
DataSet ds = new DataSet();
myDB.createAdapter().Fill(ds);
return ds;
}
/// <summary>
/// Gets a DataSet with Projects assigned to a PM
/// </summary>
/// <param name="mgrID">Project Manager</param>
/// <returns></returns>
public static DataSet getProjectsDataSet(string mgrID)
{
DBDriver myDB = new DBDriver();
myDB.Query = "select * from projects \n"
+"where managerID=@mgrID;";
myDB.addParam("@mgrID", mgrID);
DataSet ds = new DataSet();
myDB.createAdapter().Fill(ds);
return ds;
}
/// <summary>
/// Gets a DataSet with all Projects
/// </summary>
/// <returns></returns>
public static DataSet getProjectsDataSet()
{
DBDriver myDB = new DBDriver();
myDB.Query = "select * from projects;";
DataSet ds = new DataSet();
myDB.createAdapter().Fill(ds);
return ds;
}
/// <summary>
/// Insert a Project in the database
/// </summary>
/// <param name="name"></param>
/// <param name="mID">Assigned manager</param>
/// <param name="description"></param>
/// <param name="startDate">Start date</param>
public static void create(string name, string mID, string description, string startDate)
{
DBDriver myDB = new DBDriver();
myDB.Query="insert into projects (name, managerID, description, startDate) \n"
+"values ( @name, @mID, @desc, @start, @end );";
myDB.addParam("@name", name);
myDB.addParam("@mID", mID);
myDB.addParam("@desc", description);
myDB.addParam("@start", startDate);
myDB.nonQuery();
}
/// <summary>
/// Verify that an email address exists in the database
/// </summary>
/// <param name="email">email address to verify</param>
/// <returns>true if it exists, false if it doesn't</returns>
public static bool verifyEmailExists(string email)
{
DBDriver myDB=new DBDriver();
myDB.Query="select count(*) from person where email=@email;";
myDB.addParam("@email", email);
int k=Convert.ToInt32(myDB.scalar());
if(k==1)
return true;
return false;
}
/// <summary>
/// Gets a DataSet of Tasks in the specified Module
/// </summary>
/// <param name="id">Module id</param>
/// <returns>Tasks DataSet</returns>
public static DataSet getTasksDataSet(string id)
{
DBDriver myDB = new DBDriver();
myDB.Query = "select * from tasks\n" +
"where moduleID = @ID;";
myDB.addParam("@ID", id);
DataSet ds = new DataSet();
myDB.createAdapter().Fill(ds);
return ds;
}
/// <summary>
/// Verify that a username exists in the database
/// </summary>
/// <param name="userName">the username to verify</param>
/// <returns>true if it exists, false if it doesn't</returns>
public static bool verifyUserNameExists(string userName, bool isNew)
{
DBDriver myDB=new DBDriver();
myDB.Query="select count(*) from users where userName=@name;";
myDB.addParam("@name", userName);
int k=Convert.ToInt32(myDB.scalar());
if(k!=1)
if(isNew)
{
myDB.Query="select count(*) from newUsers where userName=@name;";
myDB.addParam("@name", userName);
k=Convert.ToInt32(myDB.scalar());
}
if(k==1)
return true;
return false;
}
/// <summary>
/// Remove a Task
/// </summary>
/// <param name="id">project id</param>
public static void remove(string id)
{
DBDriver myDB = new DBDriver();
myDB.Query = "delete from Tasks where id=@id";
myDB.addParam("@id", id);
myDB.nonQuery();
}
/// <summary>
/// Insert a Task in the database
/// </summary>
/// <param name="name"></param>
/// <param name="mID">Module to add to</param>
/// <param name="description"></param>
/// <param name="startDate">Start date</param>
public static void create(string name, string mID, string description, string startDate)
{
DBDriver myDB = new DBDriver();
myDB.Query="insert into tasks (name, moduleID, description, startDate, complete) \n"
+"values ( @name, @mID, @desc, @start, @complete );";
myDB.addParam("@name", name);
myDB.addParam("@mID", mID);
myDB.addParam("@desc", description);
myDB.addParam("@start", startDate);
myDB.addParam("@complete", PMT.TaskStatus.UNASSIGNED);
myDB.nonQuery();
}
/// <summary>
/// Insert a Module in the database
/// </summary>
/// <param name="name"></param>
/// <param name="pID">Project to add to</param>
/// <param name="description"></param>
/// <param name="startDate">Start date</param>
public static void create(string name, string pID, string description, string startDate)
{
DBDriver myDB = new DBDriver();
myDB.Query="insert into modules (name, projectID, description, startDate) \n"
+"values ( @name, @pID, @desc, @start);";
myDB.addParam("@name", name);
myDB.addParam("@pID", pID);
myDB.addParam("@desc", description);
myDB.addParam("@start", startDate);
myDB.nonQuery();
}