public void UpdateStory(string attname)
{
if (scStory != null)
{
var attribute1 = scStory.Attribute_FindByName(attname);
var attribute2 = scStory.Attribute_FindByName(testname);
// go through all the items to find the ones matching the filters
var filteredItems = new List <Item>();
foreach (var item in scStory.Items)
{
// check the attribute value of the item
if (item.GetAttributeIsAssigned(attribute1))
{
filteredItems.Add(item);
}
}
foreach (var item in filteredItems)
{
if (item.GetAttributeValueAsText(attribute1) == tmp)
{
double score = item.GetAttributeValueAsDouble(attribute2) + 10;
item.SetAttributeValue(attribute2, score);
}
}
Console.WriteLine("");
// save changes to the story
scStory.Save();
}
}
static void addAttribute(Story story, string[] attr)
{
foreach (var att in attr)
{
string[] split = att.Split('|');
var name = split[0];
var type = split[1];
if (story.Attribute_FindByName(name) == null)
{
if (type == "Text")
{
story.Attribute_Add(name, SC.API.ComInterop.Models.Attribute.AttributeType.Text);
}
else if (type == "Numeric")
{
story.Attribute_Add(name, SC.API.ComInterop.Models.Attribute.AttributeType.Numeric);
}
else if (type == "List")
{
story.Attribute_Add(name, SC.API.ComInterop.Models.Attribute.AttributeType.List);
}
else if (type == "Date")
{
story.Attribute_Add(name, SC.API.ComInterop.Models.Attribute.AttributeType.Date);
}
}
}
}
public static void CreateStoryFromXLTemplate(Story story, string XLFilename, Logger log, bool deleteItems)
{
EnsureStoryHasRightStructure(story, log);
// get categories
var catRisk = story.Category_FindByName(_risk);
var catEWI = story.Category_FindByName(_ewi);
var catCause = story.Category_FindByName(_cause);
var catCauseControl = story.Category_FindByName(_causeControls);
var catCauseAction = story.Category_FindByName(_causeControlActions);
var catConsequence = story.Category_FindByName(_consequence);
var catConsequenceControl = story.Category_FindByName(_consequenceControls);
var catConsequenceAction = story.Category_FindByName(_consequenceActions);
// get attributes
var attLikelihood = story.Attribute_FindByName(_attrLikelihood);
var attImpact = story.Attribute_FindByName(_attrImpact);
var attRationale = story.Attribute_FindByName(_attrRationale);
var attLikelihoodSafety = story.Attribute_FindByName(_attrLikelihoodSafety);
var attImpactSafety = story.Attribute_FindByName(_attrImpactSafety);
var attAppetiteSafety = story.Attribute_FindByName(_attrRiskAppetiteSafety);
var attRationaleSafety = story.Attribute_FindByName(_attrRationaleSafety);
var attLikelihoodPerformance = story.Attribute_FindByName(_attrLikelihoodPerformance);
var attImpactPerformance = story.Attribute_FindByName(_attrImpactPerformance);
var attAppetitePerformace = story.Attribute_FindByName(_attrRiskAppetitePerformance);
var attRationalePerformance = story.Attribute_FindByName(_attrRationalePerformance);
var attLikelihoodValue = story.Attribute_FindByName(_attrLikelihoodValue);
var attImpactValue = story.Attribute_FindByName(_attrImpactValue);
var attAppetiteValue = story.Attribute_FindByName(_attrRiskAppetiteValue);
var attRationaleValue = story.Attribute_FindByName(_attrRationaleValue);
var attLikelihoodPolitical = story.Attribute_FindByName(_attrLikelihoodPolitical);
var attImpactPolitical = story.Attribute_FindByName(_attrImpactPolitical);
var attAppetitePolitical = story.Attribute_FindByName(_attrRiskAppetitePolitical);
var attRationalePolitical = story.Attribute_FindByName(_attrRationalePolitical);
var attGrossImpact = story.Attribute_FindByName(_attrGrossImpact);
var attGrossLikelihood = story.Attribute_FindByName(_attrGrossLkelihood);
var attGrossFinance = story.Attribute_FindByName(_attrGrossFinance);
var attGrossRating = story.Attribute_FindByName(_attrGrossRating);
var attTargetImpact = story.Attribute_FindByName(_attrTargetImpact);
var attTargetLikelihood = story.Attribute_FindByName(_attrTargetLkelihood);
var attTargetFinance = story.Attribute_FindByName(_attrTargetFinance);
var attTargetRating = story.Attribute_FindByName(_attrTargetRating);
var attControlRating = story.Attribute_FindByName(_attrControlRating);
var attImapactedArea = story.Attribute_FindByName(_attrImpactedArea);
var attRiskOwner = story.Attribute_FindByName(_attrRiskOwner);
var attControlOwner = story.Attribute_FindByName(_attrControlOwner);
var attActionOwner = story.Attribute_FindByName(_attrActionOwner);
var attBasisOfOpinion = story.Attribute_FindByName(_attrBasisOfOpinion);
var attLinkedControls = story.Attribute_FindByName(_attrLinkedControls);
var attLinkedControlsTypes = story.Attribute_FindByName(_attrLinkedControlsTypes);
var attBaseline = story.Attribute_FindByName(_attrBaseline);
var attRevision = story.Attribute_FindByName(_attrRevision);
var attPercComplete = story.Attribute_FindByName(_attrPercComplete);
var attSortOrder = story.Attribute_FindByName(_attrOrder);
//var attPrior = story.Attribute_FindByName(_attrPrior);
var attCurrent = story.Attribute_FindByName(_attrCurrent);
var attWithinTolerance = story.Attribute_FindByName(_attrWithinTolerance);
var attControlOpinion = story.Attribute_FindByName(_attrControlOpinion);
var attPriority = story.Attribute_FindByName(_attrPriority);
var attStatus = story.Attribute_FindByName(_attrStatus);
var attClassification = story.Attribute_FindByName(_attrClassification);
var attVersion = story.Attribute_FindByName(_attrVersion);
var attLastUpdate = story.Attribute_FindByName(_attrlastUpdate);
var attManager = story.Attribute_FindByName(_attrManager);
var attRiskLevel = story.Attribute_FindByName(_attrRiskLevel);
var attReportingPriority = story.Attribute_FindByName(_attrReportingPriority);
var attDirectorate = story.Attribute_FindByName(_attrDirectorate);
try
{
var XL1 = new Application();
var pathMlstn = XLFilename;
log.Log($"Opening Excel Doc " + pathMlstn);
var wbBowTie = XL1.Workbooks.Open(pathMlstn);
var sheet = 1;
// validate template is correct version
var version = XL1.Sheets["Version Control"].Cells[1, 26].Text;
if (version != "SCApproved")
{
log.Log($"Spreadhseet is not in the approved version, missing 'SCApproved' at Z1 in 'Version Control' ");
KillProcessByMainWindowHwnd(XL1.Application.Hwnd);
return;
}
// set the story name
var level = XL1.Sheets[sheet].Cells(3, 4).Text;
var directorate = XL1.Sheets[sheet].Cells(4, 4).Text;
var title = XL1.Sheets[sheet].Cells(5, 4).Text;
story.Name = $"L{level}_{GetShortenedDirectorate(directorate)}_{title}";
Item risk = story.Item_FindByExternalId(_riskId) ?? story.Item_AddNew(title, false);
risk.ExternalId = _riskId;
risk.Description = XL1.Sheets[sheet].Cells(3, 19).Text;
risk.Category = catRisk;
SetAttributeWithLogging(log, risk, attClassification, XL1.Sheets[sheet].Cells(2, 4).Text);
SetAttributeWithLogging(log, risk, attRiskLevel, level);
SetAttributeWithLogging(log, risk, attDirectorate, directorate);
SetAttributeWithLogging(log, risk, attRiskOwner, XL1.Sheets[sheet].Cells(6, 4).Text);
SetAttributeWithLogging(log, risk, attManager, XL1.Sheets[sheet].Cells(7, 4).Text);
SetAttributeWithLogging(log, risk, attImapactedArea, LookupRiskLabel(XL1.Sheets[sheet].Cells(8, 4).Text));
SetAttributeWithLogging(log, risk, attControlRating, XL1.Sheets[sheet].Cells(9, 4).Text);
SetAttributeWithLogging(log, risk, attVersion, XL1.Sheets[sheet].Cells(10, 4).Text);
SetAttributeWithLogging(log, risk, attLastUpdate, XL1.Sheets[sheet].Cells(11, 4).Text);
// gross
SetAttributeWithLogging(log, risk, attGrossImpact, LookupRiskLabel(XL1.Sheets[sheet].Cells(15, 4).Text));
SetAttributeWithLogging(log, risk, attGrossLikelihood, LookupRiskLabel(XL1.Sheets[sheet].Cells(15, 7).Text));
SetAttributeWithLogging(log, risk, attGrossFinance, LookupRiskLabel(XL1.Sheets[sheet].Cells(15, 10).Text));
SetAttributeWithLogging(log, risk, attGrossRating, XL1.Sheets[sheet].Cells(15, 12).Text);
// target
SetAttributeWithLogging(log, risk, attTargetImpact, LookupRiskLabel(XL1.Sheets[sheet].Cells(16, 4).Text));
SetAttributeWithLogging(log, risk, attTargetLikelihood, LookupRiskLabel(XL1.Sheets[sheet].Cells(16, 7).Text));
SetAttributeWithLogging(log, risk, attTargetFinance, LookupRiskLabel(XL1.Sheets[sheet].Cells(16, 10).Text));
SetAttributeWithLogging(log, risk, attTargetRating, XL1.Sheets[sheet].Cells(16, 12).Text);
SetAttributeWithLogging(log, risk, attLikelihoodSafety, LookupRiskLabel(XL1.Sheets[sheet].Cells(20, 37).Text));
SetAttributeWithLogging(log, risk, attImpactSafety, LookupRiskLabel(XL1.Sheets[sheet].Cells(20, 35).Text));
SetAttributeWithLogging(log, risk, attAppetiteSafety, LookupYesNoRiskLabel(XL1.Sheets[sheet].Cells(22, 35).Text));
SetAttributeWithLogging(log, risk, attRationaleSafety, XL1.Sheets[sheet].Cells(20, 19).Text);
SetAttributeWithLogging(log, risk, attLikelihoodPerformance, LookupRiskLabel(XL1.Sheets[sheet].Cells(25, 37).Text));
SetAttributeWithLogging(log, risk, attImpactPerformance, LookupRiskLabel(XL1.Sheets[sheet].Cells(25, 35).Text));
SetAttributeWithLogging(log, risk, attAppetitePerformace, LookupYesNoRiskLabel(XL1.Sheets[sheet].Cells(27, 35).Text));
SetAttributeWithLogging(log, risk, attRationalePerformance, XL1.Sheets[sheet].Cells(25, 19).Text);
SetAttributeWithLogging(log, risk, attLikelihoodValue, LookupRiskLabel(XL1.Sheets[sheet].Cells(30, 37).Text));
SetAttributeWithLogging(log, risk, attImpactValue, LookupRiskLabel(XL1.Sheets[sheet].Cells(30, 35).Text));
SetAttributeWithLogging(log, risk, attAppetiteValue, LookupYesNoRiskLabel(XL1.Sheets[sheet].Cells(32, 35).Text));
SetAttributeWithLogging(log, risk, attRationaleValue, XL1.Sheets[sheet].Cells(30, 19).Text);
SetAttributeWithLogging(log, risk, attLikelihoodPolitical, LookupRiskLabel(XL1.Sheets[sheet].Cells(35, 37).Text));
SetAttributeWithLogging(log, risk, attImpactPolitical, LookupRiskLabel(XL1.Sheets[sheet].Cells(35, 35).Text));
SetAttributeWithLogging(log, risk, attAppetitePolitical, LookupYesNoRiskLabel(XL1.Sheets[sheet].Cells(37, 35).Text));
SetAttributeWithLogging(log, risk, attRationalePolitical, XL1.Sheets[sheet].Cells(35, 19).Text);
SetAttributeWithLogging(log, risk, attLikelihood, LookupRiskLabel(XL1.Sheets[sheet].Cells(2, 79).Text)); //TODO
SetAttributeWithLogging(log, risk, attImpact, LookupRiskLabel(XL1.Sheets[sheet].Cells(1, 79).Text)); //TODO
SetAttributeWithLogging(log, risk, attRationale, XL1.Sheets[sheet].Cells(40, 19).Text);
SetAttributeWithLogging(log, risk, attReportingPriority, GetReportingPriority(0));
string tagText = XL1.Sheets["Version Control"].Cells[21, 47].Text;
var tags = tagText.Split(',');
foreach (var t in tags)
{
risk.Tag_AddNew(t.Trim());
}
Item item;
string extId;
int order;
int counterEWI = 1;
string name;
string desc;
string text;
// data can be in the same place on 3 sheets (continuation sheets)
for (sheet = 1; sheet <= 3; sheet++)
{
// cause
for (int row = 23; row < 43; row += 2)
{
text = XL1.Sheets[sheet].Cells(row, 3).Text;
if (!string.IsNullOrWhiteSpace(text))
{
GetItemNameAndDescription(text, out name, out desc);
order = GetInt(XL1.Sheets[sheet].Cells(row, 2).Text.Trim());
extId = _causeId + $"{order:D2}";
item = story.Item_FindByExternalId(extId) ?? story.Item_AddNew(name, false);
item.ExternalId = extId;
item.Name = name;
item.Description = desc;
item.Category = catCause;
SetAttributeWithLogging(log, item, attLinkedControls, XL1.Sheets[sheet].Cells(row, 16).Text);
SetAttributeWithLogging(log, item, attSortOrder, order);
SetAttributeWithLogging(log, item, attReportingPriority, GetReportingPriority(order));
}
else if (deleteItems)
{
order = GetInt(XL1.Sheets[sheet].Cells(row, 2).Text.Trim());
extId = _causeId + $"{order:D2}";
DeleteItemWithLogging(log, story, extId);
}
}
// cause-control
for (int row = 48; row <= 59; row++)
{
text = XL1.Sheets[sheet].Cells(row, 3).Text;
if (!string.IsNullOrWhiteSpace(text))
{
GetItemNameAndDescription(text, out name, out desc);
order = GetInt(XL1.Sheets[sheet].Cells(row, 2).Text.Trim());
extId = _causeControlsId + $"{order:D2}";
item = story.Item_FindByExternalId(extId) ?? story.Item_AddNew(name, false);
item.ExternalId = extId;
item.Name = name;
item.Description = desc;
item.Category = catCauseControl;
SetAttributeWithLogging(log, item, attControlOwner, XL1.Sheets[sheet].Cells(row, 9).Text.Trim());
SetAttributeWithLogging(log, item, attControlOpinion,
LookupControlOpinion(XL1.Sheets[sheet].Cells(row, 10).Text.Trim()));
SetAttributeWithLogging(log, item, attBasisOfOpinion,
XL1.Sheets[sheet].Cells(row, 11).Text.Trim());
SetAttributeWithLogging(log, item, attSortOrder, order);
SetAttributeWithLogging(log, item, attReportingPriority, GetReportingPriority(order));
item.Relationship_AddItem(risk, "", Relationship.RelationshipDirection.AtoB);
}
else if (deleteItems)
{
order = GetInt(XL1.Sheets[sheet].Cells(row, 2).Text.Trim());
extId = _causeControlsId + $"{order:D2}";
DeleteItemWithLogging(log, story, extId);
}
}
// cause-action
for (int row = 48; row < 59; row++)
{
text = XL1.Sheets[sheet].Cells(row, 14).Text;
if (!string.IsNullOrWhiteSpace(text))
{
GetItemNameAndDescription(text, out name, out desc);
order = GetInt(XL1.Sheets[sheet].Cells(row, 13).Text.Trim());
extId = _causeControlActionsId + $"{order:D2}";
item = story.Item_FindByExternalId(extId) ?? story.Item_AddNew(name, false);
item.ExternalId = extId;
item.Name = name;
item.Description = desc;
item.Category = catCauseAction;
SetAttributeWithLogging(log, item, attActionOwner, XL1.Sheets[sheet].Cells(row, 21).Text.Trim());
SetAttributeWithLogging(log, item, attPriority, XL1.Sheets[sheet].Cells(row, 22).Text.Trim());
SetAttributeWithLogging(log, item, attBaseline, XL1.Sheets[sheet].Cells(row, 23).Text.Trim());
SetAttributeWithLogging(log, item, attRevision, XL1.Sheets[sheet].Cells(row, 24).Text.Trim());
SetAttributeWithLogging(log, item, attPercComplete,
XL1.Sheets[sheet].Cells(row, 25).Text.Trim().Replace("%", ""));
SetAttributeWithLogging(log, item, attStatus, XL1.Sheets[sheet].Cells(row, 26).Text.Trim());
SetAttributeWithLogging(log, item, attSortOrder, order);
SetAttributeWithLogging(log, item, attReportingPriority, GetReportingPriority(order));
}
else if (deleteItems)
{
order = GetInt(XL1.Sheets[sheet].Cells(row, 13).Text.Trim());
extId = _causeControlActionsId + $"{order:D2}";
DeleteItemWithLogging(log, story, extId);
}
}
// consequences
for (int row = 23; row < 43; row += 2)
{
text = XL1.Sheets[sheet].Cells(row, 41).Text;
if (!string.IsNullOrWhiteSpace(text))
{
GetItemNameAndDescription(text, out name, out desc);
order = GetInt(XL1.Sheets[sheet].Cells(row, 40).Text.Trim());
extId = _consequenceId + $"{order:D2}";
item = story.Item_FindByExternalId(extId) ?? story.Item_AddNew(name, false);
item.ExternalId = extId;
item.Name = name;
item.Description = desc;
item.Category = catConsequence;
SetAttributeWithLogging(log, item, attLinkedControls, XL1.Sheets[sheet].Cells(row, 56).Text);
SetAttributeWithLogging(log, item, attSortOrder, order);
SetAttributeWithLogging(log, item, attReportingPriority, GetReportingPriority(order));
}
else if (deleteItems)
{
order = GetInt(XL1.Sheets[sheet].Cells(row, 40).Text.Trim());
extId = _consequenceId + $"{order:D2}";
DeleteItemWithLogging(log, story, extId);
}
}
// consequence-control
for (int row = 48; row < 59; row++)
{
text = XL1.Sheets[sheet].Cells(row, 33).Text;
if (!string.IsNullOrWhiteSpace(text))
{
GetItemNameAndDescription(text, out name, out desc);
order = GetInt(XL1.Sheets[sheet].Cells(row, 32).Text.Trim());
extId = _consequenceControlsId + $"{order:D2}";
item = story.Item_FindByExternalId(extId) ?? story.Item_AddNew(name, false);
item.ExternalId = extId;
item.Name = name;
item.Description = desc;
item.Category = catConsequenceControl;
SetAttributeWithLogging(log, item, attControlOwner, XL1.Sheets[sheet].Cells(row, 39).Text.Trim());
SetAttributeWithLogging(log, item, attControlOpinion,
LookupControlOpinion(XL1.Sheets[sheet].Cells(row, 40).Text.Trim()));
SetAttributeWithLogging(log, item, attBasisOfOpinion,
XL1.Sheets[sheet].Cells(row, 41).Text.Trim());
SetAttributeWithLogging(log, item, attSortOrder, order);
SetAttributeWithLogging(log, item, attReportingPriority, GetReportingPriority(order));
item.Relationship_AddItem(risk, "", Relationship.RelationshipDirection.BtoA);
}
else if (deleteItems)
{
order = GetInt(XL1.Sheets[sheet].Cells(row, 32).Text.Trim());
extId = _consequenceControlsId + $"{order:D2}";
DeleteItemWithLogging(log, story, extId);
}
}
// consequence-action
for (int row = 48; row < 59; row++)
{
text = XL1.Sheets[sheet].Cells(row, 45).Text;
if (!string.IsNullOrWhiteSpace(text))
{
GetItemNameAndDescription(text, out name, out desc);
order = GetInt(XL1.Sheets[sheet].Cells(row, 32).Text.Trim());
extId = _consequenceControlActionsId + $"{order:D2}";
item = story.Item_FindByExternalId(extId) ?? story.Item_AddNew(name, false);
item.ExternalId = extId;
item.Name = name;
item.Description = desc;
item.Category = catConsequenceAction;
SetAttributeWithLogging(log, item, attActionOwner, XL1.Sheets[sheet].Cells(row, 53).Text.Trim());
SetAttributeWithLogging(log, item, attPriority, XL1.Sheets[sheet].Cells(row, 54).Text.Trim());
SetAttributeWithLogging(log, item, attBaseline, XL1.Sheets[sheet].Cells(row, 55).Text.Trim());
SetAttributeWithLogging(log, item, attRevision, XL1.Sheets[sheet].Cells(row, 56).Text.Trim());
SetAttributeWithLogging(log, item, attPercComplete,
XL1.Sheets[sheet].Cells(row, 57).Text.Trim().Replace("%", ""));
SetAttributeWithLogging(log, item, attStatus, XL1.Sheets[sheet].Cells(row, 58).Text.Trim());
SetAttributeWithLogging(log, item, attSortOrder, order);
SetAttributeWithLogging(log, item, attReportingPriority, GetReportingPriority(order));
}
else if (deleteItems)
{
order = GetInt(XL1.Sheets[sheet].Cells(row, 32).Text.Trim());
extId = _consequenceControlActionsId + $"{order:D2}";
DeleteItemWithLogging(log, story, extId);
}
}
for (int row = 9; row <= 17; row += 2)
{
text = XL1.Sheets[sheet].Cells(row, 22).Text;
if (!string.IsNullOrWhiteSpace(text))
{
GetItemNameAndDescription(text, out name, out desc);
order = counterEWI++;
extId = _ewiId + $"{order:D2}";
item = story.Item_FindByExternalId(extId) ?? story.Item_AddNew(name, false);
item.ExternalId = extId;
item.Name = name;
item.Description = desc;
item.Category = catEWI;
SetAttributeWithLogging(log, item, attLinkedControlsTypes,
XL1.Sheets[sheet].Cells(row, 19).Text);
SetAttributeWithLogging(log, item, attLinkedControls, XL1.Sheets[sheet].Cells(row, 21).Text);
//SetAttributeWithLogging(log, item, attPrior, XL1.Sheets[sheet].Cells(row, 35).Text);
SetAttributeWithLogging(log, item, attCurrent, XL1.Sheets[sheet].Cells(row, 35).Text);
SetAttributeWithLogging(log, item, attWithinTolerance, XL1.Sheets[sheet].Cells(row, 37).Text);
SetAttributeWithLogging(log, item, attSortOrder, order);
SetAttributeWithLogging(log, item, attReportingPriority, GetReportingPriority(order));
var link = XL1.Sheets[sheet].Cells(row, 19).Text;
}
else if (deleteItems)
{
order = counterEWI++;
extId = _ewiId + $"{order:D2}";
DeleteItemWithLogging(log, story, extId);
}
}
}
// process the relationships for the Causes & Conseqences
// the template only allows for a max of 30 of each
for (int c = 1; c <= 30; c++)
{
// Causes
extId = _causeId + $"{c:D2}";
item = story.Item_FindByExternalId(extId);
if (item != null)
{
//log.Log($"processing rels for {item.Name}");
var rels = item.GetAttributeValueAsText(attLinkedControls);
foreach (var r in rels.Split(','))
{
var i = GetInt(r);
var ex = _causeControlsId + $"{i:D2}";
var itm = story.Item_FindByExternalId(ex);
if (itm != null)
{
item.Relationship_AddItem(itm, "", Relationship.RelationshipDirection.AtoB);
}
else
{
log.Log($"Warning: Could not create relationships from '{item.Name}' to '{ex}'");
}
}
}
// Consequences
extId = _consequenceId + $"{c:D2}";
item = story.Item_FindByExternalId(extId);
if (item != null)
{
//log.Log($"processing rels for {item.Name}");
var rels = item.GetAttributeValueAsText(attLinkedControls);
foreach (var r in rels.Split(','))
{
var i = GetInt(r);
var ex = _consequenceControlsId + $"{i:D2}";
var itm = story.Item_FindByExternalId(ex);
if (itm != null)
{
item.Relationship_AddItem(itm, "", Relationship.RelationshipDirection.BtoA);
}
else
{
log.Log($"Warning: Could not create relationships from '{item.Name}' to '{ex}'");
}
}
}
// Consequences
extId = _consequenceId + $"{c:D2}";
item = story.Item_FindByExternalId(extId);
if (item != null)
{
//log.Log($"processing rels for {item.Name}");
var rels = item.GetAttributeValueAsText(attLinkedControls);
foreach (var r in rels.Split(','))
{
var i = GetInt(r);
var ex = _consequenceControlsId + $"{i:D2}";
var itm = story.Item_FindByExternalId(ex);
if (itm != null)
{
item.Relationship_AddItem(itm, "", Relationship.RelationshipDirection.BtoA);
}
else
{
log.Log($"Warning: Could not create relationships from '{item.Name}' to '{ex}'");
}
}
}
// Early Warning Indicators
extId = _ewiId + $"{c:D2}";
item = story.Item_FindByExternalId(extId);
if (item != null)
{
var relType = item.GetAttributeValueAsText(attLinkedControlsTypes);
var rels = item.GetAttributeValueAsText(attLinkedControls);
foreach (var r in rels.Split(','))
{
var i = GetInt(r);
var ex = _causeControlsId + $"{i:D2}";
if (relType == "Conseq.")
ex = _consequenceControlsId + $"{i:D2}";
var itm = story.Item_FindByExternalId(ex);
if (itm != null)
{
item.Relationship_AddItem(itm, "", Relationship.RelationshipDirection.AtoB);
}
else
{
log.Log($"Warning: Could not create relationships from '{item.Name}' to '{ex}'");
}
}
}
}
GC.Collect();
GC.WaitForFullGCComplete();
wbBowTie.Close(false);
Marshal.ReleaseComObject(wbBowTie);
KillProcessByMainWindowHwnd(XL1.Application.Hwnd);
GC.Collect();
GC.WaitForFullGCComplete();
GC.Collect();
GC.WaitForFullGCComplete();
}
catch ( Exception ex)
{
log.LogError(ex);
}
}
private static void LoadPanelData(Item item, Story story, string category, string[] attributes, double[] widths)
{
if (attributes.Length+2 != widths.Length)
{
throw new Exception("attributes and widths must match");
}
var sortby = story.Attribute_FindByName("SortOrder");
// add attributes so it won't blow up below
foreach (var attribute in attributes)
{
if (story.Attribute_FindByName(attribute) == null)
story.Attribute_Add(attribute, Attribute.AttributeType.List);
}
var table1 = new HTMLTable(attributes.Length + 2);
int col = 0;
table1.SetValue(0, col++, "#");
table1.SetValue(0, col++, "Name");
foreach (var attribute in attributes)
{
table1.SetValue(0, col++, attribute);
}
int row = 1;
foreach (var item2 in story.Items.OrderBy(i => i.GetAttributeValueAsDouble(sortby)))
{
if (item2.Category.Name == category)
{
col = 0;
table1.SetValue(row, col++, item2.GetAttributeValueAsDouble(sortby).ToString());
table1.SetValue(row, col++, item2.Name);
foreach (var attribute in attributes)
{
//Debug.WriteLine($"Attrubute = '{attribute}'");
var att = story.Attribute_FindByName(attribute);
//Debug.WriteLine($"Attrubute.Name = '{att.Name}'");
var text = item2.GetAttributeValueAsTextWithPrefixAndSuffix(att);
//Debug.WriteLine($"Text = '{text}'");
string color = item2.GetAttributeValueAsColorText(att);
table1.SetValue(row, col++, text, color);
}
row++;
}
}
// set the col width
for (int c=0;c<widths.Length; c++)
{
table1.SetColWidth(c, widths[c]);
}
var panel = item.Panel_FindByTitle(category);
if (panel == null)
panel = item.Panel_Add(category, Panel.PanelType.RichText);
panel.Data = table1.GetHTML;
}
private static Attribute EnsureAttributeExists(Attribute attrib, Story destination)
{
var attDest = destination.Attribute_FindByName(attrib.Name);
if (attDest == null)
{
attDest = destination.Attribute_Add(attrib.Name, attrib.Type);
foreach (var lab in attrib.Labels)
{
attDest.Labels_Add(lab.Text, lab.Color);
}
}
return attDest;
}
public static void EnsureStoryHasRightStructure(Story story, Logger log)
{
// make sure all categories we need exist
foreach (var c in _categoryNames)
{
if (story.Category_FindByName(c) == null) // catagory does not exist
{
log.Log($"Adding Category '{c}'");
story.Category_AddNew(c);
}
}
// make sure all attributes we need exist
foreach (var a in _textFields)
{
if (story.Attribute_FindByName(a) == null)
{
log.Log($"Adding Text Attribute '{a}'");
story.Attribute_Add(a, Attribute.AttributeType.Text);
}
}
foreach (var a in _dateFields)
{
if (story.Attribute_FindByName(a) == null)
{
log.Log($"Adding Date Attribute '{a}'");
story.Attribute_Add(a, Attribute.AttributeType.Date);
}
}
foreach (var a in _numberFields)
{
if (story.Attribute_FindByName(a) == null)
{
log.Log($"Adding Number Attribute '{a}'");
story.Attribute_Add(a, Attribute.AttributeType.Numeric);
}
}
foreach (var a in _listFields)
{
if (story.Attribute_FindByName(a) == null)
{
log.Log($"Adding List Attribute '{a}'");
story.Attribute_Add(a, Attribute.AttributeType.List);
}
}
// reserved list attributes need VL,L,M,H,VH values
foreach (var a in _listRiskFields)
{
if (story.Attribute_FindByName(a) == null)
{
log.Log($"Adding List Attribute '{a}'");
var att = story.Attribute_Add(a, Attribute.AttributeType.List);
foreach (var l in _riskLabels)
{
log.Log($"Adding List Label '{l}'");
att.Labels_Add(l);
}
}
}
}
private void update_Click(object sender, RoutedEventArgs e)
{
backgroundWorker1.RunWorkerAsync();
var progBar = sharpProgress;
update.IsEnabled = false;
var teamText = Team.Text;
var storyText = Story.Text;
var userText = Username.Text;
sc = new SharpCloudApi(userText, Password.Password.ToString());
try
{
Console.WriteLine(sc.Stories());
}
catch
{
MessageBox.Show("Invalid Username or Password");
update.IsEnabled = true;
return;
}
StoryLite[] teamBook = new StoryLite[30];
try
{
teamBook = sc.StoriesTeam(teamText);
}
catch
{
MessageBox.Show("Invalid Team");
update.IsEnabled = true;
return;
}
Story dashStory = null;
try
{
dashStory = sc.LoadStory(storyText);
}
catch
{
MessageBox.Show("Invalid Story");
update.IsEnabled = true;
return;
}
sharpProgress.Maximum = teamBook.Length - 1;
sharpProgress.Value = 0;
sharpProgress.Minimum = 1;
// Adds new attributes if story does not have it
if (dashStory.Attribute_FindByName("Appropriated Budget") == null)
{
dashStory.Attribute_Add("Appropriated Budget", SC.API.ComInterop.Models.Attribute.AttributeType.Numeric);
}
if (dashStory.Attribute_FindByName("RAG Status") == null)
{
dashStory.Attribute_Add("RAG Status", SC.API.ComInterop.Models.Attribute.AttributeType.List);
}
if (dashStory.Attribute_FindByName("New Requested Budget") == null)
{
dashStory.Attribute_Add("New Requested Budget", SC.API.ComInterop.Models.Attribute.AttributeType.Numeric);
}
if (dashStory.Attribute_FindByName("Project Business Value") == null)
{
dashStory.Attribute_Add("Project Business Value", SC.API.ComInterop.Models.Attribute.AttributeType.Text);
}
if (dashStory.Attribute_FindByName("Project Dependencies/Assumptions/Risks") == null)
{
dashStory.Attribute_Add("Project Dependencies/Assumptions/Risks", SC.API.ComInterop.Models.Attribute.AttributeType.Text);
}
if (dashStory.Attribute_FindByName("Total Spent to Date") == null)
{
dashStory.Attribute_Add("Total Spent to Date", SC.API.ComInterop.Models.Attribute.AttributeType.Numeric);
}
Story tagStory = sc.LoadStory(teamBook[0].Id);
// Add tags to new story
foreach (var tag in tagStory.ItemTags)
{
if (dashStory.ItemTag_FindByName(tag.Name) == null)
{
dashStory.ItemTag_AddNew(tag.Name, tag.Description, tag.Group);
}
}
MessageBox.Show("Updating");
sharpProgress.Value++;
foreach (StoryLite storyTeam in teamBook)
{
Story story = sc.LoadStory(storyTeam.Id);
highCost(dashStory, story);
sharpProgress.Value++;
}
dashStory.Save();
MessageBox.Show("Done");
update.IsEnabled = true;
}
// Adds Attribute data to item
static void addItem(Story story, Microsoft.SharePoint.Client.ListItem item, string[] attr)
{
Item storyItem = story.Item_AddNew(item["Title"].ToString());
string[] shareAtt = { "Project_x0020_Lead", "Project_x0020_Team", "External_x0020_ID", "Priority", "Category", "Status",
"Percent_x0020_Complete", "Due_x0020_Date", "New_x0020_Requested_x0020_Budget", "Appropriated_x0020_Budget",
"Project_x0020_Business_x0020_Val", "Project_x0020_Dependencies_x002f", "Status_x0020_Comments", "Total_x0020_Spent_x0020_to_x0020",
"Financial_x0020_Comments" };
for (var i = 0; i < attr.Length - 1; i++)
{
string[] split = attr[i].Split('|');
var name = split[0];
var type = split[1];
if (item[shareAtt[i]] != null)
{
if (type == "Text" || type == "List")
{
storyItem.SetAttributeValue(story.Attribute_FindByName(name), item[shareAtt[i]].ToString());
}
else if (type == "Numeric")
{
storyItem.SetAttributeValue(story.Attribute_FindByName(name), double.Parse(item[shareAtt[i]].ToString()));
}
else if (type == "Date")
{
storyItem.SetAttributeValue(story.Attribute_FindByName(name), DateTime.Parse(item[shareAtt[i]].ToString()));
}
}
}
if (item["Notes"] != null)
{
storyItem.Description = item["Notes"].ToString();
}
if (item["Start_x0020_Date"] != null)
{
storyItem.StartDate = DateTime.Parse(item["Start_x0020_Date"].ToString());
}
if (item["Tags_x002e_ETS_x0020_Initiatives"] != null)
{
string[] tagSplit = item["Tags_x002e_ETS_x0020_Initiatives"].ToString().Split(',');
foreach (var tag in tagSplit)
{
if (story.ItemTag_FindByNameAndGroup(tag, "ETS Initiatives") == null)
{
story.ItemTag_AddNew(tag, "", "ETS Initiatives");
storyItem.Tag_AddNew(story.ItemTag_FindByName(tag));
}
}
}
if (item["Tags_x002e_Governor_x0020_Priori"] != null)
{
string[] tagSplit = item["Tags_x002e_Governor_x0020_Priori"].ToString().Split(',');
foreach (var tag in tagSplit)
{
if (story.ItemTag_FindByNameAndGroup(tag, "Governor Priorities") == null)
{
story.ItemTag_AddNew(tag, "", "Governor Priorities");
storyItem.Tag_AddNew(story.ItemTag_FindByName(tag));
}
}
}
if (item["Tags_x002e_ETS_x0020_Priorities"] != null)
{
string[] tagSplit = item["Tags_x002e_ETS_x0020_Priorities"].ToString().Split(',');
foreach (var tag in tagSplit)
{
if (story.ItemTag_FindByNameAndGroup(tag, "ETS Priorities") == null)
{
story.ItemTag_AddNew(tag, "", "ETS Priorities");
storyItem.Tag_AddNew(story.ItemTag_FindByName(tag));
}
}
}
}
static void highCost(Story newStory, Story story)
{
String catName = "";
String newCat = "empty";
// Finds the category that contains the project
foreach (var cat in story.Categories)
{
var catLine = cat.Name.Split(' ');
// Checks to see if last word is project, if so, adds the other words to be a category for the dashboard
if (catLine[catLine.Length - 1] == "Projects")
{
catName = cat.Name;
String[] noProject = new String[catLine.Length - 1];
Array.Copy(catLine, noProject, catLine.Length - 1);
newCat = String.Join(" ", noProject);
if (newStory.Category_FindByName(newCat) == null)
{
newStory.Category_AddNew(newCat);
}
}
}
// Copies attribute data from team story to dashboard story
foreach (var item in story.Items)
{
// Checks to see if there's a bad item in the story
MatchCollection matchUrl = Regex.Matches(item.Name, @"Item \d+|(DELETE)");
// checks for category if there's no category with projects
if (newCat == "empty" && item.GetAllAttributeValues().Count > 8)
{
newCat = item.Category.Name;
catName = item.Category.Name;
if (newStory.Category_FindByName(newCat) == null)
{
newStory.Category_AddNew(newCat);
}
}
// Final check to see if item has attribute values for category
else if (newCat == "empty")
{
if (item.GetAttributeValueAsText(story.Attribute_FindByName("Appropriated Budget")) != null ||
item.GetAttributeValueAsText(story.Attribute_FindByName("Project Business Value")) != null)
{
newCat = item.Category.Name;
catName = item.Category.Name;
if (newStory.Category_FindByName(newCat) == null)
{
newStory.Category_AddNew(newCat);
}
}
}
double checkBudget = item.GetAttributeValueAsDouble(story.Attribute_FindByName("Appropriated Budget"));
// Inserts item into dashboard
if (item.Category.Name == catName)
{
Item scItem = null;
if (newStory.Item_FindByName(item.Name) == null && item.Name != "" && matchUrl.Count == 0)
{
scItem = newStory.Item_AddNew(item.Name);
scItem.Category = newStory.Category_FindByName(newCat);
scItem.StartDate = Convert.ToDateTime(item.StartDate.ToString());
scItem.DurationInDays = item.DurationInDays;
scItem.Description = item.Description;
// Get values from story
if (item.GetAttributeValueAsDouble(story.Attribute_FindByName("Appropriated Budget")) != null)
{
double appBudget = item.GetAttributeValueAsDouble(story.Attribute_FindByName("Appropriated Budget"));
scItem.SetAttributeValue(newStory.Attribute_FindByName("Appropriated Budget"), appBudget);
}
if (item.GetAttributeValueAsDouble(story.Attribute_FindByName("New Requested Budget")) != null)
{
double newBudget = item.GetAttributeValueAsDouble(story.Attribute_FindByName("New Requested Budget"));
scItem.SetAttributeValue(newStory.Attribute_FindByName("New Requested Budget"), newBudget);
}
if (item.GetAttributeValueAsText(story.Attribute_FindByName("RAG Status")) != null)
{
string RAG = item.GetAttributeValueAsText(story.Attribute_FindByName("RAG Status"));
scItem.SetAttributeValue(newStory.Attribute_FindByName("RAG Status"), RAG);
}
if (item.GetAttributeValueAsText(story.Attribute_FindByName("Project Business Value")) != null)
{
string value = item.GetAttributeValueAsText(story.Attribute_FindByName("Project Business Value"));
scItem.SetAttributeValue(newStory.Attribute_FindByName("Project Business Value"), value);
}
if (item.GetAttributeValueAsText(story.Attribute_FindByName("Project Dependencies/Assumptions/Risks")) != null)
{
string risks = item.GetAttributeValueAsText(story.Attribute_FindByName("Project Dependencies/Assumptions/Risks"));
scItem.SetAttributeValue(newStory.Attribute_FindByName("Project Dependencies/Assumptions/Risks"), risks);
}
if (item.GetAttributeValueAsDouble(story.Attribute_FindByName("Project Dependencies/Assumptions/Risks")) != null)
{
double total = item.GetAttributeValueAsDouble(story.Attribute_FindByName("Total Spent to Date"));
scItem.SetAttributeValue(newStory.Attribute_FindByName("Total Spent to Date"), total);
}
}
}
}
}