Tuesday, 11 June 2013

Import Project Activity Breakdown (Hierarchy)

I hope this is one of many import blog posts. No promises but I may look at the same written using the Data Import Export Framework to show a comparison.

To continue with the subject…

I wrote a job to import project work breakdown structure tree. The csv file is in this format.

Excel2013-06-11_2053

Final outcome is like so.

Breakdown2013-06-11_2053

The job code is below:
static void ImportActvities(Args _args)
{
AsciiIO asciiIO;
Filename filename;
NoYesId skipFirstLine;
Container line;
Dialog dialog;
DialogField dialogFileName, dialogSkipFirstLine;

int numProcessedRecords=0;

smmActivities smmActivities;
ProjTable projTable;
ProjActivity projActivity;
Hierarchy hierarchy;
HierarchyLinkTable hierarchyLinkTable;
HierarchyTreeTable rootHierarchyTreeTable, parentHierarchyTreeTable;

ProjId projId;
smmActivityNumber activityNumber, parentActivityNumber;
str name;
str parentElementNumber;
;

dialog = new Dialog("Import file");
dialogFileName = dialog.addField(extendedTypeStr(Filenameopen), "File name");
dialogSkipFirstLine = dialog.addField(extendedTypeStr(NoYesId), "Skip first line");

dialog.run();

if (dialog.run())
{
filename = dialogFileName.value();
skipFirstLine = dialogSkipFirstLine.value();
}

asciiIO = new AsciiIO(filename, 'R');

if (!asciiIO || asciiIO.status() != IO_Status::Ok)
{
throw error (strfmt("@SYS19312",filename));
}


asciiIO.inRecordDelimiter('\r\n');
asciiIO.inFieldDelimiter(',');

if (skipFirstLine)
line = asciiIO.read();

ttsBegin;
while (asciiIO.status() == IO_status::Ok)
{
line = asciiIO.read();

if (line)
{
projId = conpeek(line,1);
activityNumber = conpeek(line,2);
parentActivityNumber = conpeek(line,3);
name = conpeek(line,4);
}

projtable = Projtable::find(projId);
if (!projTable)
{
throw error(strFmt("Project %1 cannot be found", projId));
}

if (!activityNumber)
{
throw error(strFmt("Activity %1 cannot be found", activityNumber));
}

// if activityNumber is blank or activity can't be found then insert it
if (!smmActivities::find(activityNumber) && projTable)
{
//create root
hierarchyLinkTable = HierarchyLinkTable::findRefTableRecId(tableNum(ProjTable), projTable.RecId, true);

if (!hierarchyLinkTable)
{
//create Hierarchy record
hierarchy.clear();
hierarchy.initValue();
hierarchy.Name = projTable.Name;
hierarchy.Description = projTable.Name;
hierarchy.HierarchyType = HierarchyType::Project;
//use the number sequence or the project id as the hierarchyId. I prefer to use the Project id.
//hierarchy.HierarchyId = NumberSeq::newGetNum(CompanyInfo::numRefHierarchyId()).num();
hierarchy.HierarchyId = projTable.ProjId;
hierarchy.IsActive = NoYes::Yes;
hierarchy.insert();

//create HierarchyTreeTable root record
rootHierarchyTreeTable = HierarchyTreeTable::insertRoot(hierarchy.HierarchyId, projTable.ProjId);

HierarchyLinkTable::insertHierarchyLink(hierarchy.HierarchyId, tableNum(ProjTable), projTable.RecId);
}
else
{
if (hierarchyLinkTable)
{
hierarchy = Hierarchy::find(hierarchyLinkTable.HierarchyId, true);

select firstonly rootHierarchyTreeTable
where rootHierarchyTreeTable.HierarchyId == hierarchyLinkTable.HierarchyId &&
rootHierarchyTreeTable.ParentElementNumber == '';
}
}

parentElementNumber = '';
//parentActivityNumber is specified then find the node as the parent
if (parentActivityNumber)
{
parentHierarchyTreeTable = HierarchyTreeTable::findActivityNode(tableNum(ProjTable), projTable.RecId, parentActivityNumber);
if (parentHierarchyTreeTable)
{
parentElementNumber = parentHierarchyTreeTable.ElementNumber;
}
}

if (!parentElementNumber) //if parentElementNumber is blank, then use root node
{
parentElementNumber = rootHierarchyTreeTable.ElementNumber;
}

//create hierarchy tree node or activity
//if activityNumber is specified then create smmActivities. Otherwise, create a node without an activity
if (activityNumber)
{
// create smmActivities record
smmActivities.clear();
smmActivities.initValue();
smmActivities.ActivityNumber = activityNumber;
smmActivities.Purpose = name;
smmActivities.Category = smmActivityCategory::Task;
smmActivities.initFromProjTable(projTable);
smmActivities.insertParentLink(smmActivityParentType::Project, projTable.RecId, true);
smmActivities.insert();

// create ProjActivity record
if (!ProjActivity::exist(smmActivities.ActivityNumber))
{
projActivity.clear();
projActivity.initFromSmmActivities(smmActivities);
projActivity.insert();
}
HierarchyTreeTable::insertActivity(hierarchy.HierarchyId,parentElementNumber,smmActivities);
}
else
{
HierarchyTreeTable::insertNode(hierarchy.HierarchyId,parentElementNumber,name);
}

numProcessedRecords++;
info(strFmt("%1, %2 Record Processed", smmActivities.ActivityNumber,smmActivities.Purpose));
}
}
ttscommit;

info(strFmt("Imported: %1", numProcessedRecords));
}



Import dialog like so will appear.


Import2013-06-11_2100

5 comments:

Anonymous said...

Awesome!

Nice blog

Itsuki Minami said...

It worked great, thanks!

Anonymous said...

Munib, do you have job, what imports in WBS item—č for the project tasks?

Munib Ahmed said...

Nope. I haven't done that many imports around WBS.

Anonymous said...

Greate code.
Verry usefull