|
|
|
date: Fri, 8 Aug 2008 21:41:00 -0700,
group: microsoft.public.project.developer
back
RE: Assigning Resource's to task's using the MS Project API
Here is the entire code -
ApplicationClass projectApp = new ApplicationClass();
projectApp.FileNew(Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
Project project = projectApp.ActiveProject;
//QUERY THE RESOURCES AND ADD RESOURCES TO THE MPP FILES
sql_string = "select * from virtualresources union select
user_id,user_full_name from users";
cmd = new SqlCommand(sql_string, MyConnection);
read = cmd.ExecuteReader();
while (read.Read())
{
project.Resources.Add(read.GetString(1),
read.GetInt32(0));
}
read.Close();
//QUERY ALL THE PROJECTS FROM THE PROJECTS TABLE
sql_string = "select
pjid,pjname,pjcode,[R&DProjectPriority],user_full_name from projects_master";
SqlDataAdapter dbadap = new SqlDataAdapter(sql_string,
MyConnection);
DataSet dsPrj = new DataSet();
dbadap.Fill(dsPrj);
//ASSIGN START DATE TO PROJECTS AND % USAGE OF RESOURCES
float nperusage = 100;
sql_string = "select startdate,percentage_usage from
rdtrainstartdate where train_id=" + ntrain + " and
product_id=" + nProductId;
cmd = new SqlCommand(sql_string, MyConnection);
SqlDataReader dbread_per_usage = cmd.ExecuteReader();
while (dbread_per_usage.Read())
{
project.ProjectStart = dbread_per_usage.GetDateTime(0);
float fperusage = dbread_per_usage.GetInt32(1);
nperusage = fperusage / 100;
}
dbread_per_usage.Close();
projectApp.TableEdit("Entry", true, false, false,
Missing.Value, Missing.Value, "Priority", "Priority", 12,
PjAlignment.pjLeft, false, false, PjDateFormat.pjDate_mm_dd_yy, 1, 6,
PjAlignment.pjCenter, Missing.Value, Missing.Value);
projectApp.TableEdit("Entry", true, false, false,
Missing.Value, Missing.Value, "Text1", "Task Description",
24, PjAlignment.pjLeft, false, false, PjDateFormat.pjDate_mm_dd_yy, 1, 3,
PjAlignment.pjCenter, Missing.Value,
Missing.Value);
projectApp.TableEdit("Entry", true, false, false,
Missing.Value, Missing.Value, "Text2", "Project Owner", 24,
PjAlignment.pjLeft, false, false, PjDateFormat.pjDate_mm_dd_yy, 1, 8,
PjAlignment.pjCenter, Missing.Value, Missing.Value);
int counter = 2;
foreach (DataRow drPrj in dsPrj.Tables[0].Rows)
{
//HERE WE START CREATING THE TASKS (THIS WILL ESENTIALLY BE A PROJECT
AND NO INDENT APPLIED
Task task = project.Tasks.Add(drPrj[2].ToString(),
Missing.Value);
writeLog(drPrj[2].ToString());
task.OutlineLevel = 1;
if (drPrj[3].ToString() != "")
{
project.Tasks[task.ID].Priority =
Convert.ToInt32(drPrj[3]);
}
if (drPrj[4].ToString() != "")
{
project.Tasks[task.ID].Text2 =
Convert.ToString(drPrj[4]);
}
int nPjId = Convert.ToInt32(drPrj[0]);
sql_string = "select
taskid,taskname,effort,pjid,taskdesc from tasks where pjid=" + nPjId;
dbadap = new SqlDataAdapter(sql_string, MyConnection);
DataSet dsTsk = new DataSet();
dbadap.Fill(dsTsk);
//There are 2 for loop for tasks (back-to-back)
//1-Renders all the tasks for a project
foreach (DataRow drTsk in dsTsk.Tables[0].Rows)
{
//TASK CREATION FOR THE CORRESPONDING PROJECT
Task taskC =
project.OutlineChildren.Add(drTsk[1].ToString(), Missing.Value);
taskC.OutlineLevel = 2;
if (drPrj[3].ToString() != "")
{
taskC.Priority = Convert.ToInt32(drPrj[3]);
}
sql_string = "select taskid,resourceid from
taskresources where taskid=" + drTsk[0];
dbadap = new SqlDataAdapter(sql_string, MyConnection);
DataSet dsRes = new DataSet();
dbadap.Fill(dsRes);
//ERROR******** - COMMENTING THIS FOR LOOP HELP'S EXECUTE THE ENTIRE
PROCESS FASTER
foreach (DataRow drRes in dsRes.Tables[0].Rows)
{
if (Convert.ToInt32(drRes[0]) ==
Convert.ToInt32(drTsk[0]))
{
project.Tasks[taskC.ID].Assignments.Add(taskC.ID, Convert.ToInt32(drRes[1]),
nperusage);
}
}
//ERROR************ - COMMENTING THIS FOR LOOP HELP'S EXECUTE THE ENTIRE
PROCESS FASTER
project.Tasks[taskC.ID].Duration = drTsk[2];
project.Tasks[taskC.ID].ConstraintType =
PjConstraint.pjSNET;
project.Tasks[taskC.ID].Text1 = drTsk[4].ToString();
}
//2-Set the relationship among the tasks
//To set the relationship we should 1st render all the
tasks - Only then the
//relationship can be crated
foreach (DataRow drTsk in dsTsk.Tables[0].Rows)
{
sql_string = "select taskname,reltypeid,taskid_2
from tasks inner join relationship on
taskid=taskid_1 where taskid_2=" + drTsk[0];
dbadap = new SqlDataAdapter(sql_string, MyConnection);
DataSet dsRel = new DataSet();
dbadap.Fill(dsRel);
Task taskC = project.Tasks[counter];
foreach (DataRow drRel in dsRel.Tables[0].Rows)
{
if (Convert.ToInt32(drRel[1]) == 1)
{
project.Tasks[taskC.ID].LinkPredecessors(project.Tasks[drRel[0].ToString()],
PjTaskLinkType.pjFinishToStart, Missing.Value);
}
else if (Convert.ToInt32(drRel[1]) == 2)
{
project.Tasks[taskC.ID].LinkPredecessors(project.Tasks[drRel[0].ToString()],
PjTaskLinkType.pjFinishToFinish, Missing.Value);
}
else if (Convert.ToInt32(drRel[1]) == 3)
{
project.Tasks[taskC.ID].LinkPredecessors(project.Tasks[drRel[0].ToString()],
PjTaskLinkType.pjStartToStart, Missing.Value);
}
}
counter++;
}
project.Tasks[task.ID].Text1 = drPrj[1].ToString();
counter++;
}
projectApp.LevelingOptions(true, Missing.Value, true,
PjLevelOrder.pjLevelPriority, true, Missing.Value,
Missing.Value, Missing.Value, false, false, false);
projectApp.LevelNow(true);
string sLocation = "c:/Test.mpp";
string sMsfile_name = "Test.mpp";
if (File.Exists(sLocation))
{
File.Delete(sLocation);
}
projectApp.FileSaveAs(sLocation, PjFileFormat.pjMPP,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value);
projectApp.Quit(PjSaveType.pjSave);
Regards,
Safal
"Andrew Jacks" wrote:
> Safal,
>
> Is there any posibility of you posting the full code?
>
> I think the code needs to be streamlined a little, but can only tell if I
> see the full code
>
> Thanks
>
> "Safal" wrote:
>
> > Background : Onclick of a button on an ASPX web page we start building a MPP
> > file. Here is the entire process
> >
> > 1) Query all projects (SQL) and create on MPP (using MS Project API)
> > 2) Create a resource list of all user's (Again querying the user's table)
> > 3) Query all tasks for the corresponding project and render them
> > 4) Assign individual or multiple resources per task
> > 5) Set the task Priority etc.
> > 5) Once again loop through all tasks to set up the relationship among tasks
> > - 3rd loop
> > 6) Level Options
> >
> > Step 4) seems to be very time consuming.Commenting the same out the MPP
> > generation takes 10/15 mins (We are talking creation of 90 projects and lets
> > say min. 10 tasks per project). The script takes 5 hrs to build the MPP with
> > the resource assignment feature.
> >
> > The code on step 2 -
> >
> > while (read.Read())
> > {
> > project.Resources.Add(read.GetString(1),
> > read.GetInt32(0));
> > }
> >
> > The code on step 4 looks like below -
> >
> > sql_string = "select taskid,resourceid from resources where taskid=xxx"
> > dbadap = new SqlDataAdapter(sql_string,
> > MyConnection);
> > DataSet dsRes = new DataSet();
> > dbadap.Fill(dsRes);
> >
> > foreach (DataRow drRes in dsRes.Tables[0].Rows)
> > {
> > if (Convert.ToInt32(drRes[0]) ==
> > Convert.ToInt32(drTsk[0]))
> > {
> >
> > project.Tasks[taskC.ID].Assignments.Add(taskC.ID, Convert.ToInt32(drRes[1]),
> > Missing.Value);
> > }
> > }
> >
> > Is there a better way to assign resources ?
> >
> > Regards,
> > Safal
date: Mon, 25 Aug 2008 02:42:02 -0700
author: Safal
|
|
