/// <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();
}
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();
}
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>
/// 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>
/// 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>
/// 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>
/// 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 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>
/// 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();
}
/// <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>
/// 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();
}
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();
}
/// <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>
/// 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();
}
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>
/// 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>
/// 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 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>
/// 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
}
