Given the code here:
and the precursor post to that here:
I am wondering if it's possible (now that I have custom iProperty data for parameter count, feature count and occurrence count being pulled into the vault) to pull historical values for each and every file in an assembly into a spreadsheet such that I can show time-relative-progress based on the last time one of the value(s) changed/increased (for each file)?
Thanks,
Alex
PS. below (contained in a spoiler tag to prevent this page trailing on for ages) is the latest iteration of my code for querying properties from Vault into an Excel Table:
using Autodesk.Connectivity.WebServices; using Autodesk.DataManagement.Client.Framework.Vault.Currency.Entities; using System; using System.Linq; using System.Collections; using System.Collections.Generic; using System.Text.RegularExpressions; using System.Windows.Forms; using VaultBrowserSample; using Excel = Microsoft.Office.Interop.Excel; using ACW = Autodesk.Connectivity.WebServices; using Framework = Autodesk.DataManagement.Client.Framework; using Vault = Autodesk.DataManagement.Client.Framework.Vault; using VDF = Autodesk.DataManagement.Client.Framework; using Autodesk.Connectivity.WebServicesTools; using Autodesk.DataManagement.Client.Framework.Vault.Currency.Properties; namespace QueryVault { public partial class ThisAddIn { #region Member Variables public WebServiceManager ServiceManager { get; set; } private Vault.Currency.Connections.Connection m_conn = null; public bool NoMatch = true; private List<Framework.Forms.Controls.GridLayout> m_availableLayouts = new List<Framework.Forms.Controls.GridLayout>(); private PropDef[] defs = null; public ListBoxFileItem selectedfile; public List<ListBoxFileItem> FoundList; #endregion private void ThisAddIn_Startup(object sender, System.EventArgs e) { } private void ThisAddIn_Shutdown(object sender, System.EventArgs e) { } #region VSTO generated code /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InternalStartup() { this.Startup += new System.EventHandler(ThisAddIn_Startup); this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown); } #endregion public void InitializeSearchFromExcel() { m_conn = Vault.Forms.Library.Login(null); ServiceManager = m_conn.WebServiceManager; if (m_conn != null) try { Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook; Excel._Worksheet ws = wb.ActiveSheet; if (!ws.Name.Contains("MODELLING")) { MessageBox.Show("Try switching to one the tabs labelled \"MODELLING\" and try again!"); return; } //property definitions retrieved from Vault Settings -> Behaviours Tab -> Properties screen and the "System Name" column in the resulting table. PropDef materialPropDef = ServiceManager.PropertyService.GetPropertyDefinitionsByEntityClassId("FILE").First(propDef => propDef.SysName.Equals("Material")); PropDef titlePropDef = ServiceManager.PropertyService.GetPropertyDefinitionsByEntityClassId("FILE").First(propDef => propDef.SysName.Equals("Title")); PropDef revNumberPropDef = ServiceManager.PropertyService.GetPropertyDefinitionsByEntityClassId("FILE").First(propDef => propDef.SysName.Equals("RevNumber")); PropDef legacyDwgNumPropDef = ServiceManager.PropertyService.GetPropertyDefinitionsByEntityClassId("FILE").First(propDef => propDef.SysName.Equals("Subject")); //an FYI for possible future usage: //Comments sysname = Comments //Description sysname = Description //Part Number sysname = PartNumber //Project sysname = Project //(Vault) Revision sysname = Revision Excel.Range range = ws.UsedRange; FoundList = new List<ListBoxFileItem>(); for (int i = 3; i < range.Rows.Count; i++) //need to start at row 3 accounting for headers and such-like. { Excel.Range r = range.Cells[i, 2]; //column B string str = r.Value2 != null ? r.Value2.ToString() : ""; if (str != string.Empty) { //MessageBox.Show("Drawing number= " + str); Match match = Regex.Match(str, @"(.*)(\w{2}-\d{5,}-000|\w{2}-\w\d{5,}-000)(.*)", RegexOptions.IgnoreCase); if (match.Success) { //search for the drawing number in the vault if (match.Groups[2].Captures.Count > 0) { Console.WriteLine("Drawing number matches the ###### or A##### pattern : " + match.Groups[2].ToString()); str = match.Groups[2].ToString(); } //else if (match.Groups[5].Captures.Count > 0) //{ // Console.WriteLine("Drawing number matches the ###### or A##### pattern : " + match.Groups[5].ToString()); // str = match.Groups[5].ToString(); //} Excel.Range rVaultedFileName = range.Cells[i, 3]; //hidden in Excel! Excel.Range rState = range.Cells[i, 4]; Excel.Range rRevision = range.Cells[i, 5]; Excel.Range rFileType = range.Cells[i, 6]; Excel.Range rVaulted = range.Cells[i, 7]; Excel.Range rVaultLocation = range.Cells[i, 10]; Excel.Range rTitle = range.Cells[i, 11]; Excel.Range rDrawingRevision = range.Cells[i, 12]; Excel.Range rLegacyDrawingNumber = range.Cells[i, 13]; Excel.Range rFeatureCount = range.Cells[i, 14]; Excel.Range rParameterCount = range.Cells[i, 15]; Excel.Range rOccurrenceCount = range.Cells[i, 16]; Excel.Range rMaterial = range.Cells[i, 17]; rVaultLocation.Select(); //scroll with the active cell string searchedAlready = rVaultedFileName.Value2 != null ? rVaultedFileName.Value2.ToString() : string.Empty; DoSearch(str, searchedAlready); if (NoMatch == false && selectedfile != null) { FoundList.Add(selectedfile); if (selectedfile.File.EntityName.EndsWith(".iam")) { if (selectedfile.File.EntityName.StartsWith("AS-")) { rFileType.Value2 = "Assembly"; rMaterial.Value2 = "No Material Assigned or Required"; } else if (selectedfile.File.EntityName.StartsWith("DT-")) { rFileType.Value2 = "Detail Assembly"; rMaterial.Value2 = "No Material Assigned or Required"; } } else if(selectedfile.File.EntityName.EndsWith(".ipt")) { rFileType.Value2 = "Part"; //only bother with material for part files. PropInst materialPropInst = ServiceManager.PropertyService.GetProperties("FILE", new long[] { selectedfile.File.EntityIterationId }, new long[] { materialPropDef.Id }).First(); if (materialPropInst.Val != null) { rMaterial.Value2 = materialPropInst.Val; } else { rMaterial.Value2 = "No Material Assigned or Required"; } } //add/update some information about the file in the Excel spreadsheet. //storing the filename that was selected means we don't need to prompt the user to choose again. rVaultedFileName.Value2 = selectedfile.File.EntityName.ToString(); rState.Value2 = selectedfile.File.LifecycleInfo.StateName; rRevision.Value2 = selectedfile.File.RevisionInfo.RevisionLabel; #region Is Vaulted //change the font to Wingdings rVaulted.Font.Name = "Wingdings"; //set the value to the right character to present a tick. rVaulted.Value2 = ((char)0xFC).ToString(); //need to perform a check here for the vault we're signed into? //MessageBox.Show(m_conn.Vault.ToString()); if (m_conn.Vault.ToString() == "Legacy Vault") { rVaultLocation.Value2 = selectedfile.folder.FullName.ToString().Replace("/", "\\").Replace("$", "C:\\Legacy Vault Working Folder") + "\\" + selectedfile.File.EntityName; } else { rVaultLocation.Value2 = selectedfile.folder.FullName.ToString().Replace("/", "\\").Replace("$", "C:\\Vault Working Folder") + "\\" + selectedfile.File.EntityName; } //deals with pulling title, rev number & subject values from the vaulted parts. if (rTitle.Value2 == "" || rTitle.Value2 == null) { PropInst titlePropInst = ServiceManager.PropertyService.GetProperties("FILE", new long[] { selectedfile.File.EntityIterationId }, new long[] { titlePropDef.Id }).First(); if (titlePropInst.Val != null) { rTitle.Value2 = titlePropInst.Val; } else { rTitle.Value2 = "No Title iProperty Found!"; } } if (rDrawingRevision.Value2 == "" || rDrawingRevision.Value2 == null) { PropInst revNumberPropInst = ServiceManager.PropertyService.GetProperties("FILE", new long[] { selectedfile.File.EntityIterationId }, new long[] { revNumberPropDef.Id }).First(); if (revNumberPropInst.Val != null) { rDrawingRevision.Value2 = revNumberPropInst.Val; } else { rDrawingRevision.Value2 = "No Rev Number iProperty Found!"; } } if (rLegacyDrawingNumber.Value2 =="" || rLegacyDrawingNumber.Value2 == null) { PropInst legacyDrawingNumberPropInst = ServiceManager.PropertyService.GetProperties("FILE", new long[] { selectedfile.File.EntityIterationId }, new long[] { legacyDwgNumPropDef.Id }).First(); if (legacyDrawingNumberPropInst.Val != null) { rLegacyDrawingNumber.Value2 = legacyDrawingNumberPropInst.Val; } else { rLegacyDrawingNumber.Value2 = "No Legacy Drawing Number (Subject) iProperty Found!"; } } if (selectedfile.FeatureCount != null) { rFeatureCount.Value2 = Convert.ToInt32(selectedfile.FeatureCount); } else { rFeatureCount.Value2 = 0; } //rFeatureCount.Value2 = selectedfile.FeatureCount.ToString(); if (selectedfile.ParameterCount != null) { rParameterCount.Value2 = Convert.ToInt32(selectedfile.ParameterCount); } else { rParameterCount.Value2 = 0; } if (selectedfile.OccurrenceCount != null) { rOccurrenceCount.Value2 = Convert.ToInt32(selectedfile.OccurrenceCount); } else { rOccurrenceCount.Value2 = 0; } #endregion //reset the NoMatch bool & selectedfile NoMatch = true; selectedfile = null; } else if(NoMatch == true) //need to mark the "VAULTED" Column with a Cross { #region Is NOT Vaulted rState.Value2 = "NA"; rRevision.Value2 = "NA"; //change the font to Wingdings rVaulted.Font.Name = "Wingdings"; //set the value to the right character to present a tick. rVaulted.Value2 = ((char)0xFB).ToString(); #endregion //reset the NoMatch bool & selectedfile NoMatch = true; selectedfile = null; } } } } } catch (Exception ex) { MessageBox.Show("The error was: " + ex.Message + "\n" + ex.StackTrace); throw; } //we need to be sure to release all our connections when the app closes Vault.Library.ConnectionManager.CloseAllConnections(); } private void DoSearch(string p, string VaultedFileName) { SrchCond searchCondition = new SrchCond(); searchCondition.PropDefId = 9; //filename bool searchedAlready = false; if (VaultedFileName != string.Empty) { searchedAlready = true; searchCondition.PropTyp = PropertySearchType.SingleProperty; searchCondition.SrchOper = 3; //equals searchCondition.SrchTxt = VaultedFileName; //our filename } else { searchCondition.PropTyp = PropertySearchType.SingleProperty; searchCondition.SrchOper = 1; //contains searchCondition.SrchTxt = p; //our drawing number } SrchCond[] conditions = new SrchCond[1]; conditions[0] = searchCondition; string bookmark = string.Empty; SrchStatus status = null; //search for files List<File> fileList = new List<File>(); PropertyDefinitionDictionary props = m_conn.PropertyManager.GetPropertyDefinitions(VDF.Vault.Currency.Entities.EntityClassIds.Files, null, PropertyDefinitionFilter.IncludeAll); PropertyDefinition propDef; PropertyDefinition myUDP_FeatureCount = null; PropertyDefinition myUDP_OccurrenceCount = null; PropertyDefinition myUDP_ParameterCount = null; foreach (var myKeyValuePair in props) { propDef = myKeyValuePair.Value; switch (propDef.DisplayName) { case "FeatureCount": myUDP_FeatureCount = propDef; break; case "OccurrenceCount": myUDP_OccurrenceCount = propDef; break; case "ParameterCount": myUDP_ParameterCount = propDef; break; default: break; } } while (status == null || fileList.Count < status.TotalHits) { File[] files = m_conn.WebServiceManager.DocumentService.FindFilesBySearchConditions( conditions, null, null, true, true, ref bookmark, out status); if (files != null) fileList.AddRange(files); } if (fileList.Count > 0) { /*we have a match in Vault for our drawing number now we need to decide whether we completely automate the next step or allow the user to select the expected file. */ /*ideally, if we search the fileList object we should find at least one .ipt or .iam if we find neither, we can safely assume the file hasn't been vaulted yet! */ if (searchedAlready) { foreach (File file in fileList) { ListBoxFileItem fileItem = new ListBoxFileItem(new VDF.Vault.Currency.Entities.FileIteration(m_conn, file)); Autodesk.Connectivity.WebServices.Folder folder = m_conn.WebServiceManager.DocumentService.GetFolderById(file.FolderId); fileItem.folder = folder; //User Defined Properties - add more as necessary fileItem.FeatureCount = m_conn.PropertyManager.GetPropertyValue(fileItem.File, myUDP_FeatureCount, null); fileItem.OccurrenceCount = m_conn.PropertyManager.GetPropertyValue(fileItem.File, myUDP_OccurrenceCount, null); fileItem.ParameterCount = m_conn.PropertyManager.GetPropertyValue(fileItem.File, myUDP_ParameterCount, null); Globals.ThisAddIn.NoMatch = false; Globals.ThisAddIn.selectedfile = fileItem; //we should only be returning one file because we're searching for it directly! return; } } else { List<File> results = fileList.FindAll(x => x.Name.EndsWith(".ipt") || x.Name.EndsWith(".iam")); if (results.Count > 1) { foreach (File file in results) { ListBoxFileItem findfile = new ListBoxFileItem(new VDF.Vault.Currency.Entities.FileIteration(m_conn, file)); if (Globals.ThisAddIn.FoundList.Contains(findfile)) { Globals.ThisAddIn.NoMatch = false; Globals.ThisAddIn.selectedfile = findfile; return; } } //we have multiple to choose from and should get the user to pick //create a form object to display found items string selectedfilename = string.Empty; FileSelectionForm fileForm = new FileSelectionForm(m_conn); //iterate through found files and display them in the search results list box foreach (File file in results) { ListBoxFileItem fileItem = new ListBoxFileItem(new VDF.Vault.Currency.Entities.FileIteration(m_conn, file)); fileForm.m_searchResultsListBox.Items.Add(fileItem); } //update the items count label fileForm.m_SearchingForLabel.Text = "Searching for filename(s) containing: " + p; fileForm.m_itemsCountLabel.Text = (results.Count > 0) ? results.Count + " Items" : "0 Items"; //display the form and wait for it to close using the ShowDialog() method. fileForm.ShowDialog(); } else if (results.Count == 1) { //get the first and only file we found whose name contains .iam or .ipt File foundfile = results[0]; ListBoxFileItem fileItem = new ListBoxFileItem(new VDF.Vault.Currency.Entities.FileIteration(m_conn, foundfile)); Globals.ThisAddIn.NoMatch = false; Globals.ThisAddIn.selectedfile = fileItem; return; } else //no matches or all .pdf files { Globals.ThisAddIn.NoMatch = true; } } } } private void InitializePropertyDefs() { defs = m_conn.WebServiceManager.PropertyService.GetPropertyDefinitionsByEntityClassId(VDF.Vault.Currency.Entities.EntityClassIds.Files); if (defs != null && defs.Length > 0) { Array.Sort(defs, new PropertyDefinitionSorter()); } } } #region "Search Condition Item Class" class SrchCondItem { public SrchCond SrchCond; public PropDef PropDef; public SrchCondItem(SrchCond srchCond, PropDef propDef) { this.SrchCond = srchCond; this.PropDef = propDef; } public override string ToString() { string conditionName = Condition.GetCondition(SrchCond.SrchOper).DisplayName; return String.Format("{0} {1} {2}", PropDef.DispName, conditionName, SrchCond.SrchTxt); } } #endregion #region PropertyDefinitionSorter Class /// <summary> /// Used for sorting collections of PropertyDefinition's. /// </summary> class PropertyDefinitionSorter : IComparer { /// <summary> /// Class (static) constructor that creates a static Comparer class instane used for sorting PropertyDefinition's. /// </summary> static PropertyDefinitionSorter() { m_comparer = new Comparer(Application.CurrentCulture); } private static Comparer m_comparer; public int Compare(object x, object y) { PropDef propDefX = x as PropDef; PropDef propDefY = y as PropDef; lock (m_comparer) { return m_comparer.Compare(propDefX.DispName, propDefY.DispName); } } } #endregion #region "ListBoxFileItem" /// <summary> /// A list box item which contains a File object /// </summary> public class ListBoxFileItem { private FileIteration file; public FileIteration File { get { return file; } } public ListBoxFileItem(FileIteration f) { file = f; } public Autodesk.Connectivity.WebServices.Folder folder; /// <summary> /// Determines the text displayed in the ListBox /// </summary> public override string ToString() { return this.file.EntityName; } public object FeatureCount { get; set; } public object OccurrenceCount { get; set; } public object ParameterCount { get; set; } } #endregion }
Solved! Go to Solution.
Solved by wayne.brill. Go to Solution.
Hi Alex,
I am not sure I fully understand what you need. Maybe the DocumentService GetFilesByHistoryType() Method will meet your requirement.
I added the code below to this SDK sample to test:
C:\Program Files (x86)\Autodesk\Autodesk Vault 2014 SDK\VS10\CSharp\VaultBrowserSample
I see in the myFileArray a list of files for each version. Maybe the ModDate field for the file will provide what you need.
void m_model_SelectedContentChanged(object sender, Forms.Currency.SelectionChangedEventArgs e)
{
//WB added - testing
if (e.SelectedEntities.Count() > 0)
{
long[] myFileIds = new long[] { e.SelectedEntities.ElementAt(0).EntityIterationId };
ACW.
FileArray[] myFileArray = null;
myFileArray = m_conn.WebServiceManager.DocumentService.GetFilesByHistoryType(myFileIds, ACW.
FileHistoryTypeOptions.All);
}
//WB end added
Thanks,
Wayne
Thanks Wayne, that's a useful example!
The resultant FileArray[] gives me a bunch of file Ids which I think I can now query for the specific PropInst.Val results I am looking for.
Now I just have to figure out the best way to represent/store them that makes them usable as a metric for project progress.
Thanks again!