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