Retrieve historical data for files and their properties

[AlexFielder]

Given the code here:

http://forums.autodesk.com/t5/vault-customization/find-vaulted-filepath-based-on-fileiteration/m-p/5...

and the precursor post to that here:

http://forums.autodesk.com/t5/vault-customization/query-vault-professional-2014-for-part-assembly-de...

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:

Spoiler

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

}

[wayne.brill · Accepted 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

[AlexFielder]

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!