prs/prs.desktop/Panels/Jobs/DocumentSets/JobDocumentSetTree.xaml.cs

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Forms;
using System.Windows.Input;
using System.Windows.Media;
using Comal.Classes;
using FastReport.Utils;
using InABox.Clients;
using InABox.Configuration;
using InABox.Core;
using InABox.Core.Reports;
using InABox.DynamicGrid;
using InABox.Wpf;
using InABox.Wpf.Reports;
using InABox.WPF;
using javax.xml.crypto;
using NPOI.SS.Formula.Functions;
using org.apache.commons.lang3;
using Syncfusion.Compression.Zip;
using Syncfusion.Data.Extensions;
using Syncfusion.UI.Xaml.Grid;
using Syncfusion.UI.Xaml.TreeGrid;
using Syncfusion.UI.Xaml.TreeGrid.Helpers;
using Syncfusion.Windows.Controls.Cells;
using Syncfusion.XlsIO;
using Color = System.Drawing.Color;
using Environment = System.Environment;
using GridSelectionChangedEventArgs = Syncfusion.UI.Xaml.Grid.GridSelectionChangedEventArgs;
using JobDocumentSetFolder = Comal.Classes.JobDocumentSetFolder;
using MessageBox = System.Windows.MessageBox;
using UserControl = System.Windows.Controls.UserControl;

namespace PRSDesktop
{
    
    public class JobDocumentSetTreeSettings : IUserConfigurationSettings
    {
        public bool DetailsVisible { get; set; }

        public JobDocumentSetTreeSettings()
        {
            DetailsVisible = true;
        }
    }
    
    public delegate void JobDocumentSetMileStoneSelected(JobDocumentSetMileStoneBlock block);
    
    public partial class JobDocumentSetTree : UserControl
    {

        public event JobDocumentSetMileStoneSelected MileStoneSelected;
    
        private struct MileStone
        {
            public Guid TypeID { get; set; }
            public CoreRow Row { get; set; }
        }
        
        private struct MileStoneType
        {
            public String Code { get; set; }
            public String Description { get; set; }
            public Dictionary<Guid,List<CoreRow>> SetMileStones { get; set; }
            public List<String> Columns { get; set; }
        }
        
        public Job Job { get; set; }
        public Guid[] FolderIDs{ get; set; }

        //public bool DisciplineVisible { get; set; }
        public Guid DisciplineID { get; set; }
        
        //public bool TypeVisible { get; set; }
        public Guid TypeID { get; set; }
        
        //public bool CategoryVisible { get; set; }
        public Guid CategoryID { get; set; }
        
        //public bool AreaVisible { get; set; }
        public Guid AreaID { get; set; }
        
        public String SearchText { get; set; }

        private Dictionary<Guid,MileStoneType> _types = null;
        private CoreTable _milestones = null;
        public CoreTable Data { get; private set; } = null;

        private CoreTable _files = null;
        
        private bool _hidesuperceded = false;
        private bool _flatlist = false;
        private bool _includeretired = false;

        private DocumentSetNodes _documentsets = null;

        private JobDocumentSetTreeSettings _settings;
        
        public JobDocumentSetTree()
        {
            InitializeComponent();
            AddImage.Source = PRSDesktop.Resources.add.AsBitmapImage();
            EditImage.Source = PRSDesktop.Resources.pencil.AsBitmapImage();
            DeleteImage.Source = PRSDesktop.Resources.delete.AsBitmapImage();
            _settings = new UserConfiguration<JobDocumentSetTreeSettings>().Load();
            treeGrid.Loaded += (o, e) =>
            {
                treeGrid.GetTreePanel().RowHeights[1] = 0;
                treeGrid.UpdateDataRow(1);
            };
            
        }
        
        public void Refresh()
        {

            using (new WaitCursor())
            {
                var scrollviewer = WPFUtils.FindVisualChildren<ScrollViewer>(treeGrid).FirstOrDefault();
                var verticalOffset = scrollviewer != null ? scrollviewer.VerticalOffset : 0;
                var horizontalOffset = treeGrid.SelectedItem != null ? scrollviewer.HorizontalOffset : 0;
                treeGrid.ItemsSource = null;

                var setfilter = new Filter<JobDocumentSet>(x => x.Job.ID).IsEqualTo(Job.ID);
                
                if ((FolderIDs?.Any() == true) && !FolderIDs.Contains(CoreUtils.FullGuid))
                    setfilter = setfilter.And(x => x.Folder.ID).InList(FolderIDs);  
                
                if (DisciplineID != Guid.Empty)
                    setfilter = setfilter.And(x => x.Discipline.ID).IsEqualTo(DisciplineID);

                if (TypeID != Guid.Empty)
                    setfilter = setfilter.And(x => x.Type.ID).IsEqualTo(TypeID);

                if (CategoryID != Guid.Empty)
                    setfilter = setfilter.And(x => x.Category.ID).IsEqualTo(CategoryID);

                if (AreaID != Guid.Empty)
                    setfilter = setfilter.And(x => x.Area.ID).IsEqualTo(AreaID);
                
                if (!_includeretired)
                    setfilter = setfilter.And(x => x.Retired).IsEqualTo(DateTime.MinValue);

                if (!String.IsNullOrWhiteSpace(SearchText))
                    setfilter = setfilter.TextSearch(SearchText, x => x.Code, x => x.Description);

                MultiQuery query = new MultiQuery();
                query.Add(
                    setfilter,
                    new Columns<JobDocumentSet>(x => x.ID)
                        .Add(x => x.Parent.ID)
                        .Add(x => x.Code)
                        .Add(x => x.Description)
                        .Add(x => x.Date)
                        .Add(x => x.Size)
                        .Add(x => x.Scale)
                        .Add(x => x.Employee.Name)
                        .Add(x=>x.Folder.ID)
                        .Add(x=>x.Discipline.Description)
                        .Add(x=>x.Category.Description)
                        .Add(x=>x.Type.Description)
                        .Add(x=>x.Area.Description),
                    new SortOrder<JobDocumentSet>(x => x.Code)
                );


                var milestonefilter = new Filter<JobDocumentSetMileStone>(x => x.DocumentSet.Job.ID).IsEqualTo(Job.ID);
                if ((FolderIDs?.Any() == true) && !FolderIDs.Contains(CoreUtils.FullGuid))
                    milestonefilter = milestonefilter.And(x => x.DocumentSet.Folder.ID).InList(FolderIDs);

                query.Add(
                    milestonefilter,
                    new Columns<JobDocumentSetMileStone>(x => x.ID)
                        .Add(x => x.DocumentSet.ID)
                        .Add(x => x.DocumentSet.Code)
                        .Add(x => x.Type.ID)
                        .Add(x => x.Type.Code)
                        .Add(x => x.Status)
                        .Add(x => x.Notes)
                        .Add(x => x.Revision)
                        .Add(x => x.Due)
                        .Add(x => x.Submitted)
                        .Add(x => x.Closed)
                        .Add(x => x.Attachments)
                        .Add(x => x.Kanbans)
                        .Add(x => x.Watermark)
                );

                if (_types == null)
                {
                    query.Add<JobDocumentSetMileStoneType>(
                        null,
                        new Columns<JobDocumentSetMileStoneType>(x => x.ID)
                            .Add(x => x.Code)
                            .Add(x => x.Description),
                        new SortOrder<JobDocumentSetMileStoneType>(x => x.Sequence)
                    );
                }

                query.Query();

                Data = query.Get<JobDocumentSet>();
                _milestones = query.Get<JobDocumentSetMileStone>();

                if (_types == null)
                {
                    _types = query.Get<JobDocumentSetMileStoneType>().ToDictionary<JobDocumentSetMileStoneType, Guid, MileStoneType>(
                        x => x.ID,
                        r => new MileStoneType()
                        {
                            Code = r.Get<JobDocumentSetMileStoneType, String>(c => c.Code),
                            Description = r.Get<JobDocumentSetMileStoneType, String>(c => c.Description),
                            SetMileStones = new Dictionary<Guid, List<CoreRow>>(),
                            Columns = new List<string>()
                        }
                    );
                }
                else
                {
                    foreach (var typeid in _types.Keys)
                    {
                        _types[typeid].Columns.Clear();
                        _types[typeid].SetMileStones.Clear();
                    }
                }

                var milestones = _milestones.ToLookup<JobDocumentSetMileStone, Guid, MileStone>(
                    x => x.DocumentSet.ID,
                    r => new MileStone()
                    {
                        TypeID = r.Get<JobDocumentSetMileStone, Guid>(c => c.Type.ID),
                        Row = r
                    }
                );

                foreach (var milestone in milestones)
                {
                    foreach (var entry in milestone)
                    {
                        if (!_types[entry.TypeID].SetMileStones.ContainsKey(milestone.Key))
                            _types[entry.TypeID].SetMileStones[milestone.Key] = new List<CoreRow>();
                        if (_hidesuperceded)
                            _types[entry.TypeID].SetMileStones[milestone.Key].Clear();
                        _types[entry.TypeID].SetMileStones[milestone.Key].Add(entry.Row);
                    }
                }

                List<String> columns = new List<string>();
                foreach (var typeid in _types.Keys)
                {
                    int count = 1;
                    foreach (var setkey in _types[typeid].SetMileStones.Keys)
                        count = Math.Max(count, _types[typeid].SetMileStones[setkey].Count);
                    for (int i = 1; i <= count; i++)
                    {
                        String column = String.Format("{0}_{1}", _types[typeid].Code, i);
                        columns.Add(column);
                        _types[typeid].Columns.Add(String.Format("Blocks[{0}]", column));
                    }
                }

                _documentsets = new DocumentSetNodes(columns);

                foreach (var setrow in Data.Rows)
                {
                    
                    
                    Guid setid = setrow.Get<JobDocumentSet, Guid>(x => x.ID);
                    Guid parentid = _flatlist ? Guid.Empty : setrow.Get<JobDocumentSet, Guid>(x => x.Parent.ID);
                    String code = setrow.Get<JobDocumentSet, String>(c => c.Code);
                    String description = setrow.Get<JobDocumentSet, String>(c => c.Description);
                    var tags = new List<String>()
                    {
                        setrow.Get<JobDocumentSet, String>(c => c.Discipline.Description),
                        setrow.Get<JobDocumentSet, String>(c => c.Type.Description),
                        setrow.Get<JobDocumentSet, String>(c => c.Category.Description),
                        setrow.Get<JobDocumentSet, String>(c => c.Area.Description)
                    }.Where(x=>!String.IsNullOrWhiteSpace(x)).Distinct().ToArray();

                    var node = _documentsets.Add(setid, parentid);
                    
                    JobDocumentSetDescriptionBlock desc = new JobDocumentSetDescriptionBlock(
                        setid, code, description, tags);

                    node.Description = Serialization.Serialize(desc);

                    JobDocumentSetDetailsBlock dets = new JobDocumentSetDetailsBlock()
                    {
                        ID = setid,
                        Date = setrow.Get<JobDocumentSet, DateTime>(c => c.Date),
                        Size = setrow.Get<JobDocumentSet, PaperSize>(c => c.Size),
                        Scale = setrow.Get<JobDocumentSet, String>(c => c.Scale),
                        Employee = setrow.Get<JobDocumentSet, String>(c => c.Employee.Name)
                    };
                    node.Details = Serialization.Serialize(dets);

                    foreach (var typeid in _types.Keys)
                    {
                        if (_types[typeid].SetMileStones.TryGetValue(setid, out var rows))
                        {
                            int i = 1;
                            foreach (var row in rows)
                            {
                                JobDocumentSetMileStoneBlock block = new JobDocumentSetMileStoneBlock();

                                block.ID = row.Get<JobDocumentSetMileStone, Guid>(c => c.ID);

                                block.Revision = row.Get<JobDocumentSetMileStone, String>(c => c.Revision);

                                block.Status = row.Get<JobDocumentSetMileStone, JobDocumentSetMileStoneStatus>(c => c.Status);

                                block.Date = (block.Status == JobDocumentSetMileStoneStatus.Approved) ||
                                             (block.Status == JobDocumentSetMileStoneStatus.Cancelled) ||
                                             (block.Status == JobDocumentSetMileStoneStatus.Rejected)
                                    ? row.Get<JobDocumentSetMileStone, DateTime>(c => c.Closed)
                                    : block.Status == JobDocumentSetMileStoneStatus.Submitted
                                        ? block.Date = row.Get<JobDocumentSetMileStone, DateTime>(c => c.Submitted)
                                        : row.Get<JobDocumentSetMileStone, DateTime>(c => c.Due);

                                String[] notes = row.Get<JobDocumentSetMileStone, String[]>(c => c.Notes);
                                block.Notes = notes != null ? String.Join("\n", notes) : "";

                                block.Attachments = row.Get<JobDocumentSetMileStone, int>(c => c.Attachments);
                                block.Kanbans = row.Get<JobDocumentSetMileStone, int>(c => c.Kanbans);
                                block.Watermark = row.Get<JobDocumentSetMileStone, String>(c => c.Watermark);

                                node.Blocks[String.Format("{0}_{1}", _types[typeid].Code, i)] = Serialization.Serialize(block);
                                i++;
                            }
                        }
                    }
                }

                ConfigureColumns(_documentsets);

                ConfigureStackedHeader();

                treeGrid.ItemsSource = _documentsets.Nodes;
                DocumentCount.Content = $"{_documentsets.Nodes.Count} {(_documentsets.Nodes.Count > 1 ? "Records" : "Record")}";
                if (scrollviewer != null)
                {
                    scrollviewer.ScrollToVerticalOffset(verticalOffset);
                    scrollviewer.ScrollToHorizontalOffset(horizontalOffset);
                }
            }

        }

        #region Grid Configuration
        
        private void ConfigureColumns(DocumentSetNodes documentsets)
        {
            treeGrid.Columns.Clear();
            
            treeGrid.Columns.Add(new TreeGridTemplateColumn()
            {
                CellTemplate = FindResource("descriptionTemplate") as DataTemplate,
                MappingName = "Description",
                SetCellBoundValue = true,
                MinimumWidth = 250,
                ColumnSizer = TreeColumnSizer.Star
            });
            
            treeGrid.Columns.Add(new TreeGridTemplateColumn()
            {
                CellTemplate = FindResource("detailsTemplate") as DataTemplate,
                MappingName = "Details",
                SetCellBoundValue = true,
                Width = _settings.DetailsVisible ? 120 : 0
            });
            
            foreach (var column in documentsets.Columns)
            {
                var col = new TreeGridTemplateColumn()
                {
                    CellTemplate = FindResource("milestoneTemplate") as DataTemplate,
                    MappingName = String.Format("Blocks[{0}]",column),
                    SetCellBoundValue = true,
                    HeaderText = " ",
                    Width = 80,
                    ShowToolTip = true
                };
                treeGrid.Columns.Add(col);
            }
        }

        private void ConfigureStackedHeader()
        {
            stackedHeaderRow.StackedColumns.Clear();
            stackedHeaderRow.StackedColumns.Add(new StackedColumn()
            {
                ChildColumns = "Description,Details",
                HeaderText = "Document Register"
            });
        
            foreach (var typeid in _types.Keys)
            {
                stackedHeaderRow.StackedColumns.Add(new StackedColumn()
                {
                    
                    ChildColumns = String.Join(",", _types[typeid].Columns),
                    HeaderText = _types[typeid].Code
                });
            }
        }
        
        private void TreeGrid_OnItemsSourceChanged(object? sender, TreeGridItemsSourceChangedEventArgs e)
        {
            var panel = treeGrid.GetTreePanel();
            panel.RowHeights[1] = 0;
        }
        
        private void TreeGrid_OnNodeCollapsing(object? sender, NodeCollapsingEventArgs e)
        {
            e.Cancel = true;
        }
        
        public MenuItem CreateCalendar(ContextMenu menu, string text, DateTime startDate, CoreRow[] milestones, Action<CoreRow[], DateTime?>? action)
        {
            var item = new MenuItem();

            var calendarItem = new MenuItem();

            var calendar = new System.Windows.Controls.Calendar { DisplayDate = startDate, SelectedDate = null};

            calendar.SelectedDatesChanged += (o, e) =>
            {
                action?.Invoke(milestones, calendar.SelectedDate);
                menu.IsOpen = false;
            };
            calendarItem.Header = calendar;
            calendarItem.Style = DynamicGridUtils.Resources["NonHighlightMenuItem"] as Style;
            item.Header = text;
            item.Items.Add(calendarItem);
            item.IsCheckable = false;
            return item;
        }

        private IEnumerable<DocumentSetNode> GetSelectedNodes()
        {
            return treeGrid.SelectedItems
                .Select(x => x as DocumentSetNode)
                .NotNull();
        }

        private RowColumnIndex GetMouseRowColumnIndex(Point? mousePos = null)
        {
            var rci = treeGrid.GetTreePanel().PointToCellRowColumnIndex(mousePos ?? Mouse.GetPosition(treeGrid));
            return new RowColumnIndex(rci.RowIndex, rci.ColumnIndex);
        }

        private IEnumerable<JobDocumentSetMileStoneBlock> GetSelectedBlocks(Point? mousePos = null)
        {
            var rowColumnIndex = GetMouseRowColumnIndex(mousePos);
            if (rowColumnIndex.IsEmpty)
            {
                return Enumerable.Empty<JobDocumentSetMileStoneBlock>();
            }
            var treeNodeAtRowIndex = treeGrid.GetNodeAtRowIndex(rowColumnIndex.RowIndex);

            var mappingname = treeGrid.Columns[rowColumnIndex.ColumnIndex].MappingName;
            var blockkey = mappingname.Replace("Blocks[", "").Replace("]", "");
            
            return treeGrid.SelectedItems
                .Select(x => (x as DocumentSetNode)?.Blocks[blockkey])
                .Where(x => !x.IsNullOrWhiteSpace())
                .Select(x => Serialization.Deserialize<JobDocumentSetMileStoneBlock>(x))
                .NotNull();
        }
        private DocumentSetNode? GetHoveredSet(Point? mousePos = null)
        {
            var rowColumnIndex = GetMouseRowColumnIndex(mousePos);
            if (rowColumnIndex.IsEmpty)
                return null;
            var treeNodeAtRowIndex = treeGrid.GetNodeAtRowIndex(rowColumnIndex.RowIndex);
            return treeNodeAtRowIndex.Item as DocumentSetNode;
        }

        private JobDocumentSetMileStoneBlock? GetHoveredBlock(Point? mousePos = null)
        {
            var rowColumnIndex = GetMouseRowColumnIndex(mousePos);
            if (rowColumnIndex.IsEmpty)
                return null;

            var mappingname = treeGrid.Columns[rowColumnIndex.ColumnIndex].MappingName;
            var blockkey = mappingname.Replace("Blocks[", "").Replace("]", "");

            var treeNodeAtRowIndex = treeGrid.GetNodeAtRowIndex(rowColumnIndex.RowIndex);
            var block = (treeNodeAtRowIndex.Item as DocumentSetNode)?.Blocks[blockkey];
            if (block.IsNullOrWhiteSpace()) return null;

            return Serialization.Deserialize<JobDocumentSetMileStoneBlock>(block);
        }

        private bool CanCreateNewMileStone(Guid typeID, Guid[] setIDs)
        {
            foreach (var setID in setIDs)
            {
                var openmilestones = _milestones.Rows.Any(r =>
                    Guid.Equals(r.Get<JobDocumentSetMileStone, Guid>(c => c.DocumentSet.ID), setID)
                    && Guid.Equals(r.Get<JobDocumentSetMileStone, Guid>(c => c.Type.ID), typeID)
                    && (r.Get<JobDocumentSetMileStone, DateTime>(c => c.Closed).IsEmpty() ||
                        (r.Get<JobDocumentSetMileStone, JobDocumentSetMileStoneStatus>(c => c.Status) == JobDocumentSetMileStoneStatus.Approved))
                );
                if (openmilestones)
                    return false;
            }
            return true;
        }

        private void Grid_Drop(object sender, System.Windows.DragEventArgs e)
        {
            var block = GetHoveredBlock(e.GetPosition(treeGrid));
            if (block is not null)
            {
                var milestoneRow = _milestones.Rows.FirstOrDefault(r => r.Get<JobDocumentSetMileStone, Guid>(c => c.ID) == block.ID);
                if (milestoneRow is null) return;

                var result = DocumentUtils.HandleFileDrop(e.Data);
                if (result is null) return;

                UploadFiles(new CoreRow[] { milestoneRow }, result);
            }
            else
            {
                var rowColumnIndex = GetMouseRowColumnIndex(e.GetPosition(treeGrid));
                if (rowColumnIndex.IsEmpty) return;

                var mappingname = treeGrid.Columns[rowColumnIndex.ColumnIndex].MappingName;

                var typeID = _types.FirstOrDefault(x => x.Value.Columns.Contains(mappingname)).Key;
                var setID = GetHoveredSet(e.GetPosition(treeGrid))?.ID ?? Guid.Empty;
                if (setID == Guid.Empty) return;

                if(CanCreateNewMileStone(typeID, new Guid[] { setID }))
                {
                    var result = DocumentUtils.HandleFileDrop(e.Data);
                    if (result is null) return;

                    var documents = result.Select(x => CreateDocument(x.Item1, x.Item2)).ToList();

                    var milestone = new JobDocumentSetMileStone();

                    milestone.DocumentSet.ID = setID;
                    milestone.Type.ID = typeID;
                    milestone.Status = JobDocumentSetMileStoneStatus.NotStarted;
                    milestone.Due = DateTime.Today;

                    var grid = new JobDocumentSetMileStoneGrid();
                    if (grid.EditItems(new JobDocumentSetMileStone[] { milestone }))
                    {
                        Client.Save(documents, "Uploaded by user.");

                        var files = documents.Select(doc =>
                        {
                            var file = new JobDocumentSetMileStoneFile();
                            file.DocumentLink.ID = doc.ID;
                            file.EntityLink.ID = milestone.ID;
                            return file;
                        });
                        Client.Save(files, "Uploaded by user.");

                        Refresh();
                    }
                }
                else
                {
                    MessageWindow.ShowMessage("Cannot create a new milestone here.", "Error", image: MessageWindow.WarningImage);
                }
            }
        }

        private void TreeGrid_OnContextMenuOpening(object sender, ContextMenuEventArgs e)
        {
            if (treeGrid.SelectedItem == null)
            {
                e.Handled = true;
                return;
            }

            MileStoneMenu.Items.Clear();

            var rowColumnIndex = GetMouseRowColumnIndex();
            if (rowColumnIndex.IsEmpty)
                return;
            var treeNodeAtRowIndex = treeGrid.GetNodeAtRowIndex(rowColumnIndex.RowIndex);                       
 
            if (rowColumnIndex.ColumnIndex < 2)
            {
                var documents = GetSelectedNodes().ToArray();
                var ids = documents.Select(x => x.ID).ToArray();
                
                MileStoneMenu.AddItem("Edit Document Set", null, ids, EditDocumentSets);
                
                if (documents.Length == 1)
                {
                    MileStoneMenu.AddSeparator();
                    MileStoneMenu.AddItem("Add Child", null, documents.First(), AddChildDocument);
                }


                var movetofolder = new MenuItem();
                movetofolder.Header = "Move To Folder";
                bool hasfolders = PopulateFolders(movetofolder, documents);
                if (hasfolders)
                {
                    MileStoneMenu.AddSeparator();
                    MileStoneMenu.Items.Add(movetofolder);
                }

                MileStoneMenu.AddSeparator();
                MileStoneMenu.AddItem((treeGrid.Columns[1].Width > 0) ? "Hide Detail Column" : "Show Detail Column", null, ShowHideDetailsColumn);
                
                return;
            }
            
            var mappingname = treeGrid.Columns[rowColumnIndex.ColumnIndex].MappingName;
            var typeID = _types.FirstOrDefault(x => x.Value.Columns.Contains(mappingname)).Key;

            var setIDs = GetSelectedNodes().Select(x => x.ID).ToArray();

            var milestoneIDs = GetSelectedBlocks().Select(x => x.ID).ToArray();
            var milestoneRows = _milestones.Rows.Where(r => milestoneIDs.Contains(r.Get<JobDocumentSetMileStone, Guid>(c => c.ID))).ToArray();
            
            if (CanCreateNewMileStone(typeID, setIDs))
            {
                MileStoneMenu.AddItem("New Milestone", null, () => CreateMileStone(setIDs, typeID, DateTime.Today));
            }

            if (milestoneRows.Any())
            {
                var setStatus = MileStoneMenu.AddItem("Change Status", null, null);
                foreach (JobDocumentSetMileStoneStatus newstatus in Enum.GetValues(typeof(JobDocumentSetMileStoneStatus)))
                {
                    MenuItem setstatus2 = null;
                    switch (newstatus)
                    {
                        
                        case JobDocumentSetMileStoneStatus.Unknown:
                            break;
                        
                        case JobDocumentSetMileStoneStatus.NotStarted:
                        case JobDocumentSetMileStoneStatus.InProgress:
                        case JobDocumentSetMileStoneStatus.OnHold:
                        case JobDocumentSetMileStoneStatus.InfoRequired:
                            setStatus.AddItem(
                                newstatus.ToString().SplitCamelCase(),
                                null,
                                () => ChangeMileStoneStatus(milestoneRows, newstatus, DateTime.MinValue, DateTime.MinValue));
                            break;

                        case JobDocumentSetMileStoneStatus.Submitted:
                            setStatus.Items.Add(CreateCalendar(
                                MileStoneMenu,
                                newstatus.ToString().SplitCamelCase(),
                                DateTime.Today,
                                milestoneRows,
                                (r, t) => { ChangeMileStoneStatus(milestoneRows, newstatus, t, DateTime.MinValue); }
                            ));
                            break;
                            
                        case JobDocumentSetMileStoneStatus.Approved:
                        case JobDocumentSetMileStoneStatus.Cancelled:
                        case JobDocumentSetMileStoneStatus.Rejected:
                            setStatus.Items.Add(CreateCalendar(
                                MileStoneMenu,
                                newstatus.ToString().SplitCamelCase(),
                                DateTime.Today,
                                milestoneRows,
                                (r, t) => { ChangeMileStoneStatus(milestoneRows, newstatus, null, t); }
                            ));
                            break;

                    }
                }
                
                MileStoneMenu.AddItem("Edit Milestone", null, milestoneRows, EditMileStones);


                if (setIDs.Length == 1 && milestoneRows.Length == 1)
                {
                    var attachments = milestoneRows[0].Get<JobDocumentSetMileStone, int>(x => x.Attachments);
                    if (attachments > 1)
                    {
                        MileStoneMenu.AddItem("Split MileStone", null, () => { SplitMileStone(setIDs[0], milestoneRows[0]); });
                    }
                }
                
                if (milestoneRows.Any())
                {
                    MileStoneMenu.AddSeparator();
                    MileStoneMenu.AddItem(milestoneRows.Length > 1 ? "Upload and Match File Names" : "Upload Files", null, milestoneRows, UploadFiles);

                    var download = MileStoneMenu.AddItem("Download Files", null, null);
                    
                    download.Items.Add(new MenuItem());
                    download.SubmenuOpened += (o, e) =>
                    {
                        download.Items.Clear();
                        var files = new Client<JobDocumentSetMileStoneFile>().Query(
                            new Filter<JobDocumentSetMileStoneFile>(x => x.EntityLink.ID).InList(milestoneIDs),
                            new Columns<JobDocumentSetMileStoneFile>(x => x.ID)
                                .Add(x => x.DocumentLink.FileName)
                                .Add(x => x.DocumentLink.ID),
                            new SortOrder<JobDocumentSetMileStoneFile>(x => x.DocumentLink.FileName)
                        );
                        if (files.Rows.Any())
                        {
                            foreach (var row in files.Rows)
                            {
                                download.AddItem(
                                    row.Get<JobDocumentSetMileStoneFile, String>(x => x.DocumentLink.FileName),
                                    null,
                                    () => DownloadFiles(
                                        new CoreRow[] { milestoneRows[0] },
                                        row.Get<JobDocumentSetMileStoneFile, Guid>(x => x.DocumentLink.ID)));
                            }

                            if (download.Items.Count > 1)
                            {
                                download.AddSeparator();
                                download.AddItem("Download All", null, () => DownloadFiles(milestoneRows, Guid.Empty));
                            }
                        }
                        else
                        {
                            download.AddItem("No Files to download", null, null, enabled: false);
                        }
                    };
                }
                
                MileStoneMenu.AddSeparator();
                MileStoneMenu.AddItem("Export Files", null, milestoneRows, ExportFiles);

                if(milestoneIDs.Length == 1)
                {
                    MileStoneMenu.AddSeparatorIfNeeded();
                    var item = MileStoneMenu.AddItem("Forms", PRSDesktop.Resources.kanban, null);
                    DynamicGridUtils.PopulateFormMenu<JobDocumentSetMileStoneForm, JobDocumentSetMileStone, JobDocumentSetMileStoneLink>(
                        item,
                        milestoneIDs[0],
                        () => new Client<JobDocumentSetMileStone>().Load(new Filter<JobDocumentSetMileStone>(x => x.ID).IsEqualTo(milestoneIDs[0])).First(),
                        editOnAdd: true,
                        customiseEditor: (editor) =>
                        {
                            if (Security.IsAllowed<CanPrintReports>())
                            {
                                editor.CustomButtons.Add(new DynamicFormEditButton("Attach", AttachForm));
                            }
                        });
                }

                MileStoneMenu.AddSeparator();
                MileStoneMenu.AddItem("Delete MileStone", null, milestoneRows, DeleteMileStone);
            }

            if (MileStoneMenu.Items.Count == 0)
                e.Handled = true;
        }

        private void AttachForm(DynamicFormEditWindow window, DynamicFormEditButton button)
        {
            var dataModel = window.DataModel;
            if(dataModel is null)
            {
                return;
            }

            var menu = new ContextMenu();

            var model = new DigitalFormReportDataModel<JobDocumentSetMileStoneForm>(
                new Filter<JobDocumentSetMileStoneForm>(x => x.ID).IsEqualTo(dataModel.Instance.ID),
                dataModel.Instance.Form.ID);
            model.AddFormData(dataModel.Instance.ID, window.SaveValues().ToLoadStorage());

            var reports = ReportUtils.LoadReports(dataModel.Instance.Form.ID.ToString(), model).Where(x => x.Visible).ToList();
            if(reports.Count == 1)
            {
                AttachReport((reports[0], model, dataModel));
                return;
            }
            else if(reports.Count > 1)
            {
                foreach (var report in reports)
                {
                    menu.AddItem(report.Name, null, (report, model, dataModel), AttachReport);
                }
            }
            if(menu.Items.Count == 0)
            {
                menu.AddItem("No reports", null, null, enabled: false);
            }
            menu.IsOpen = true;
        }

        private void AttachReport((ReportTemplate report, DigitalFormReportDataModel<JobDocumentSetMileStoneForm> model, IDigitalFormDataModel dataModel) arg)
        {
            Progress.ShowModal("Please wait", (progress) =>
            {
                var (report, model, dataModel) = arg;
                var data = ReportUtils.ReportToPDF(report, model);

                var document = new Document
                {
                    Data = data,
                    FileName = $"Digital Form - {report.Name} - {DateTime.Now:yyyy-MM-dd hh:mm:ss.fff}.pdf",
                    CRC = CoreUtils.CalculateCRC(data),
                    TimeStamp = DateTime.Now
                };
                Client.Save(document, $"Generated from form report {report.Name}");

                var file = new JobDocumentSetMileStoneFile();
                file.DocumentLink.ID = document.ID;
                file.EntityLink.ID = dataModel.Entity.ID;
                Client.Save(file, "Attached from form.");
            });
            Refresh();
        }

        private void ExportFiles(CoreRow[] milestones)
        {
            var sfd = new SaveFileDialog();
            sfd.Filter = "Compressed Files (*.zip)|*.zip";
            sfd.AddExtension = true;
            if (sfd.ShowDialog() != DialogResult.OK)
                return;
            Progress.ShowModal("Exporting Files", (progress) =>
            {

                progress.Report("Getting File Links");
                var milestoneids = milestones.Select(r => r.Get<JobDocumentSetMileStone, Guid>(c => c.ID)).ToArray();
                var links = new Client<JobDocumentSetMileStoneFile>().Query(
                    new Filter<JobDocumentSetMileStoneFile>(c => c.EntityLink.ID).InList(milestoneids),
                    new Columns<JobDocumentSetMileStoneFile>(x => x.EntityLink.ID)
                        .Add(x=>x.EntityLink.DocumentSet.Code)
                        .Add(x=>x.EntityLink.Type.Code)
                        .Add(x=>x.EntityLink.Revision)
                        .Add(x=>x.EntityLink.Status)
                        .Add(x => x.DocumentLink.ID)
                        .Add(x=>x.DocumentLink.FileName)
                );


                Syncfusion.Compression.Zip.ZipArchive zip = new Syncfusion.Compression.Zip.ZipArchive();
                int i = 0;
                foreach (var row in links.Rows)
                {
                    i++;
                    String code = row.Get<JobDocumentSetMileStoneFile, String>(c => c.EntityLink.DocumentSet.Code);
                    String filename = Path.GetFileNameWithoutExtension(row.Get<JobDocumentSetMileStoneFile, String>(c => c.DocumentLink.FileName));
                    String extension = Path.GetExtension(row.Get<JobDocumentSetMileStoneFile, String>(c => c.DocumentLink.FileName));
                    String type = $"{row.Get<JobDocumentSetMileStoneFile,String>(c=>c.EntityLink.Type.Code)} {row.Get<JobDocumentSetMileStoneFile,String>(c=>c.EntityLink.Revision)}".Trim();
                    var status = row.Get<JobDocumentSetMileStoneFile, JobDocumentSetMileStoneStatus>(c => c.EntityLink.Status);
                    filename = $"{code}/{filename} {type} ({status}){extension}";
                    
                    progress.Report($"Processing {i} of {links.Rows.Count} files");
                    
                    Guid docid = row.Get<JobDocumentSetMileStoneFile, Guid>(c => c.DocumentLink.ID);
                    
                    var data = new Client<Document>().Query(
                        new Filter<Document>(x => x.ID).IsEqualTo(docid),
                        new Columns<Document>(x=>x.ID).Add(x => x.Data)
                    ).Rows.Select(r=>r.Get<Document,byte[]>(c=>c.Data)).FirstOrDefault();
                    
                    if (data != null)
                    {
                        var item = new ZipArchiveItem(zip, filename, new MemoryStream(data), true, FileAttributes.Normal);
                        zip.AddItem(item);

                    }

                }
                progress.Report("Closing archive");
                zip.Save(sfd.FileName);
                zip.Close();

            });
            MessageBox.Show("All Done!");
        }

        private void ShowHideDetailsColumn()
        {
            _settings.DetailsVisible = !_settings.DetailsVisible;
            new UserConfiguration<JobDocumentSetTreeSettings>().Save(_settings);
            treeGrid.Columns[1].Width = _settings.DetailsVisible ? 120: 0;
        }

        private bool PopulateFolders(MenuItem menu, IEnumerable<DocumentSetNode> documents)
        {
            var data = Client.Query(
                new Filter<JobDocumentSetFolder>(x => x.Job.ID).IsEqualTo(Job.ID),
                new Columns<JobDocumentSetFolder>(x => x.ID)
                    .Add(x => x.Parent.ID)
                    .Add(x => x.Name)
            );
            if (!data.Rows.Any())
                return false;

            var folders = new CoreTreeNodes();
            folders.Load<JobDocumentSetFolder>(data, x => x.ID, x => x.Parent.ID, x => x.Name);

            foreach (var folder in folders.Nodes)
                DoPopulateFolder(menu, folder, documents);

            return true;
        }

        private void DoPopulateFolder(MenuItem header, CoreTreeNode folder, IEnumerable<DocumentSetNode> documents)
        {
            var menu = header.AddItem(folder.Description, null, () => MoveToFolder(documents, folder));
            foreach (var childfolder in folder.Children)
                DoPopulateFolder(menu, childfolder, documents);
        }

        private void MoveToFolder(IEnumerable<DocumentSetNode> documents, CoreTreeNode folder)
        {
            using (new WaitCursor())
            {
                var updates = new List<JobDocumentSet>();
                foreach (var document in documents)
                {
                    var folderid = Data.Rows.FirstOrDefault(r => r.Get<JobDocumentSet, Guid>(c => c.ID) == document.ID)?.Get<JobDocumentSet, Guid>(c => c.Folder.ID) ?? Guid.Empty;
                    if (folderid != folder.ID)
                    {
                        var update = new JobDocumentSet();
                        update.ID = document.ID;
                        update.CommitChanges();
                        update.Folder.ID = folder.ID;
                        update.Parent.ID = Guid.Empty;
                        updates.Add(update);
                    }
                }

                if (updates.Any())
                    new Client<JobDocumentSet>().Save(updates, "Moved to Folder: " + folder.Description);
                else
                    MessageBox.Show("Nothing to Do!");
            }

            Refresh();
        }

        private void SplitMileStone(Guid setid, CoreRow milestone)
        {
            if (!MessageWindow.ShowYesNo("Are you sure you wish to split this Document Set?", "Confirm Split"))
                return;
            
            Guid milestoneid = milestone.Get<JobDocumentSetMileStone, Guid>(c => c.ID);
            var dlg = new MultiSelectDialog<JobDocumentSetMileStoneFile>(
                new Filter<JobDocumentSetMileStoneFile>(c => c.EntityLink.ID).IsEqualTo(milestoneid),
                null,
                true
            );
            if (dlg.ShowDialog() == true)
            {
                var files = dlg.Items();
                Progress.ShowModal("Splitting Document Set", (progress) =>
                {

                    JobDocumentSet newset = new Client<JobDocumentSet>().Query(
                        new Filter<JobDocumentSet>(x => x.ID).IsEqualTo(setid)
                    ).Rows.FirstOrDefault()?.ToObject<JobDocumentSet>();
                    if (newset != null)
                    {
                        newset.ID = Guid.Empty;
                        newset.CommitChanges();
                        newset.Parent.ID = setid;
                        newset.Code = String.Format("{0} (COPY)", newset.Code);
                        //newset.Description = "New Child";
                        new Client<JobDocumentSet>().Save(newset, "Created by Splitting MileStone");
                        progress.Report("Creating Milestone");
                        JobDocumentSetMileStone newms = new Client<JobDocumentSetMileStone>().Query(
                            new Filter<JobDocumentSetMileStone>(c=>c.ID).IsEqualTo(milestoneid)
                        ).Rows.FirstOrDefault()?.ToObject<JobDocumentSetMileStone>();
                        if (newms != null)
                        {
                            newms.ID = Guid.Empty;
                            newset.CommitChanges();
                            newms.DocumentSet.ID = newset.ID;
                            new Client<JobDocumentSetMileStone>().Save(newms, "Created By Splitting MileStone");
                            progress.Report("Moving Files");
                            foreach (var file in files)
                                file.EntityLink.ID = newms.ID;
                            new Client<JobDocumentSetMileStoneFile>().Save(files, "Moved when Splitting MileStone");
                        }
                    }
                });
                Refresh();
            }
        }

        private void AddChildDocument(DocumentSetNode node)
        {
            if (node == null)
                return;
            var folderid = Data.Rows.FirstOrDefault(r => r.Get<JobDocumentSet, Guid>(c => c.ID) == node.ID)?.Get<JobDocumentSet, Guid>(c => c.Folder.ID) ?? Guid.Empty;
            JobDocumentSet newset = new JobDocumentSet();
            newset.Parent.ID = node.ID;
            newset.Job.ID = Job.ID;
            newset.Job.Synchronise(Job);
            newset.Folder.ID = folderid;
            var grid = new DynamicDataGrid<JobDocumentSet>();
            if (grid.EditItems(new[] { newset }))
                Refresh();
            
        }

        private void DownloadFiles(CoreRow[] rows, Guid id)
        {
            
            FolderBrowserDialog dlg = new FolderBrowserDialog();
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                Progress.ShowModal("Downloading Files", (progress) =>
                {
                    foreach (var row in rows)
                    {
                        var status = row.Get<JobDocumentSetMileStone, JobDocumentSetMileStoneStatus>(c => c.Status);
                        var stage = row.Get<JobDocumentSetMileStone, String>(c => c.Type.Code);
                        var revision = row.Get<JobDocumentSetMileStone, String>(c => c.Revision);

                        String tag = String.Format(" - {0}{1} ({2})", stage, String.IsNullOrWhiteSpace(revision) ? "" : " - Rev " + revision,
                            status.ToString().SplitCamelCase());

                        var filter = id == Guid.Empty
                            ? new Filter<Document>(x => x.ID).InQuery(
                                new Filter<JobDocumentSetMileStoneFile>(x => x.EntityLink.ID).IsEqualTo(
                                    row.Get<JobDocumentSetMileStone, Guid>(c => c.ID)),
                                x => x.DocumentLink.ID
                            )
                            : new Filter<Document>(x => x.ID).IsEqualTo(id);

                        var files = new Client<Document>().Query(filter);

                        foreach (var filerow in files.Rows)
                        {
                            string filename = filerow.Get<Document, String>(c => c.FileName);
                            string extension = Path.GetExtension(filename);
                            string basefilename = Path.GetFileNameWithoutExtension(filename);
                            filename = String.Format("{0}{1}{2}", basefilename, tag, extension);
                            filename = Path.Combine(dlg.SelectedPath, filename);
                            File.WriteAllBytes(filename, filerow.Get<Document, byte[]>(c => c.Data));
                        }
                    }
                });
                Process.Start(new ProcessStartInfo(dlg.SelectedPath) { UseShellExecute = true });
            }
        }
        
        private bool SelectFiles(out string[] files)
        {
            Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
            dlg.Filter = "PDF Files (*.pdf)|*.pdf";
            dlg.Multiselect = true;
            if ((dlg.ShowDialog() == true) && (dlg.FileNames.Length > 0))
            {
                files = dlg.FileNames.ToArray();
                return true;
            }
            files = null;
            return false;
        }

        private Document CreateDocument(string file, Stream? stream)
        {
            var filename = Path.GetFileName(file).ToLower();

            Document doc;

            if (Path.GetExtension(filename) != ".pdf")
            {
                filename = Path.ChangeExtension(filename, ".pdf");
                if (stream is null)
                {
                    var data = DataEntryReGroupWindow.RenderToPDF(file).SaveToBytes();
                    doc = new Document
                    {
                        Data = data,
                        FileName = filename,
                        CRC = CoreUtils.CalculateCRC(data),
                        TimeStamp = new FileInfo(filename).LastWriteTime
                    };
                }
                else
                {
                    var data = DataEntryReGroupWindow.RenderToPDF(file, stream).SaveToBytes();
                    doc = new Document
                    {
                        Data = data,
                        FileName = filename,
                        CRC = CoreUtils.CalculateCRC(data),
                        TimeStamp = DateTime.Now
                    };
                }
            }
            else if (stream is null)
            {
                doc = Document.FromFile(file);
                doc.FileName = filename;
            }
            else
            {
                byte[] data;
                using (var ms = new MemoryStream())
                {
                    stream.CopyTo(ms);
                    data = ms.ToArray();
                }
                doc = new Document
                {
                    Data = data,
                    FileName = filename,
                    CRC = CoreUtils.CalculateCRC(data),
                    TimeStamp = new FileInfo(file).LastWriteTime
                };
            }

            return doc;
        }

        private void UploadFiles(CoreRow[] rows, IEnumerable<Tuple<string, Stream?>> fileStreams)
        {
            var files = fileStreams.AsArray();

            var setlookups = rows.Length > 1
                ? new Dictionary<string, Guid>(
                    rows.Select(
                        r => new KeyValuePair<String, Guid>(
                            r.Get<JobDocumentSetMileStone, String>(c => c.DocumentSet.Code),
                            r.Get<JobDocumentSetMileStone, Guid>(c => c.ID)
                        )
                    )
                )
                : null;

            if ((setlookups != null) && (rows.Length > 1))
            {
                var unmatched = files.Where(x => !setlookups.Keys.Any(key => Path.GetFileName(x.Item1).ToLower().ToLower().StartsWith(key.ToLower())));
                if (unmatched.Any())
                {
                    MessageBox.Show("Unable to match the following files:\n" + String.Join("\n", unmatched));
                    return;
                }
            }

            var milestoneids = rows.Select(r => r.Get<JobDocumentSetMileStone, Guid>(c => c.ID)).ToArray();

            var jobdocsetfiles = Client.Query(
                new Filter<JobDocumentSetMileStoneFile>(x => x.EntityLink.ID).InList(milestoneids),
                new Columns<JobDocumentSetMileStoneFile>(x => x.ID, x => x.DocumentLink.ID)
                    .Add(x => x.DocumentLink.FileName))
                .ToObjects<JobDocumentSetMileStoneFile>().ToList();

            var currentfiles = jobdocsetfiles.ToDictionary(x => x.DocumentLink.FileName.ToLower(), x => x.DocumentLink.ID);

            var matched = files.Select(x => x.Item1).Where(x => currentfiles.ContainsKey(Path.GetFileName(x).ToLower()));

            var replace = false;
            if (matched.Any())
            {
                var result = MessageWindow.New()
                    .Title("Replace Files")
                    .Message("The following files already exist!\n\n Do you wish to replace them?\n\n" + String.Join("\n", matched))
                    .AddYesButton("Replace Files")
                    .AddNoButton("Keep Existing")
                    .AddCancelButton()
                    .Display().Result;
                if (result == MessageWindowResult.Cancel) return;
                replace = result == MessageWindowResult.Yes;
            }

            int doccount = 0;
            Progress.ShowModal("Uploading Files", (progress) =>
            {
                var documents = new List<Document>();
                var newDocuments = new List<Document>();
                var linked = new List<JobDocumentSetMileStoneFile>();
                foreach (var (file, stream) in files)
                {
                    var filename = Path.GetFileName(file).ToLower();
                    if (replace || !matched.Contains(filename))
                    {
                        var doc = CreateDocument(file, stream);
                        documents.Add(doc);
                        if (currentfiles.TryGetValue(filename, out var docID))
                        {
                            doc.SetObserving(false);
                            doc.ID = docID;
                            doc.SetObserving(true);

                            var linkedfile = jobdocsetfiles.FirstOrDefault(x => x.DocumentLink.ID == doc.ID);
                            if (linkedfile != null)
                            {
                                linkedfile.DocumentLink.ID = doc.ID;
                                // Regenerate the thumbnail
                                linkedfile.Thumbnail = null;
                                linked.Add(linkedfile);
                            }
                        }
                        else
                        {
                            newDocuments.Add(doc);
                        }
                    }
                }
                Client.Save(documents, "Uploaded by User");

                progress.Report("Updating Links");

                foreach (var document in newDocuments)
                {
                    var link = new JobDocumentSetMileStoneFile();
                    if (setlookups != null)
                    {
                        var filename = Path.GetFileName(document.FileName).ToLower();
                        var code = setlookups.Keys.FirstOrDefault(key => filename.StartsWith(key.ToLower()));
                        link.EntityLink.ID = setlookups[code];
                    }
                    else
                        link.EntityLink.ID = rows.First().Get<JobDocumentSetMileStone, Guid>(c => c.ID);

                    link.DocumentLink.ID = document.ID;
                    linked.Add(link);
                }
                Client.Save(linked, "Uploaded by User");

                doccount = documents.Count;
            });
            MessageWindow.ShowMessage(string.Format("{0} Files Uploaded", doccount > 0 ? doccount : "No"), "Success");
            Refresh();
        }

        private void UploadFiles(CoreRow[] rows)
        {
            if (rows.Length < 1)
            {
                MessageBox.Show("No Rows Selected");
                return;
            }
            
            if (SelectFiles(out String[] filenames))
            {
                UploadFiles(rows, filenames.Select(x => new Tuple<string, Stream?>(x, null)));
            }
        }

        private Dictionary<Guid, JobDocumentSetMileStone> GetPreviousMileStones(Guid[] setids, Guid typeid)
        {
            var result = new Dictionary<Guid, JobDocumentSetMileStone>();
            foreach (var setid in setids)
            {
                var typeindex = _types.Keys.IndexOf(typeid);
                JobDocumentSetMileStone? last = null;
                while ((last == null) && (typeindex > 0))
                {
                    last = _milestones.Rows.LastOrDefault(r =>
                            (r.Get<JobDocumentSetMileStone, Guid>(c => c.DocumentSet.ID) == setid) &&
                            (r.Get<JobDocumentSetMileStone, Guid>(c => c.Type.ID) == _types.Keys.ToArray()[typeindex]))
                        ?.ToObject<JobDocumentSetMileStone>();
                    typeindex--;
                }

                if (last != null)
                    result[setid] = last;

            }
            return result;
        }

        private void CreateMileStone(Guid[] setids, Guid typeid, DateTime duedate)
        {
            bool bCopy = false;
            var lastmilestones = GetPreviousMileStones(setids, typeid);
            if (lastmilestones.Any(x => x.Value.Attachments > 0))
            {
                var confirm = MessageBox.Show("Do you wish to copy the files from the previous milestones?", "Copy Files",
                    MessageBoxButton.YesNoCancel);
                if (confirm == MessageBoxResult.Cancel)
                    return;
                bCopy = confirm == MessageBoxResult.Yes;
            }

            Dictionary<JobDocumentSetMileStone,JobDocumentSetMileStoneFile[]> updates = new Dictionary<JobDocumentSetMileStone, JobDocumentSetMileStoneFile[]>();
            foreach (var setid in setids)
            {
                JobDocumentSetMileStone milestone = new JobDocumentSetMileStone();
                milestone.DocumentSet.ID = setid;
                milestone.Type.ID = typeid;
                milestone.Status = JobDocumentSetMileStoneStatus.NotStarted;
                milestone.Due = duedate;

                JobDocumentSetMileStoneFile[] files = new JobDocumentSetMileStoneFile[] { };
                if (bCopy && lastmilestones.TryGetValue(setid, out var lastmilestone))
                {
                    if (lastmilestone.Attachments > 0)
                    {
                        files = new Client<JobDocumentSetMileStoneFile>().Query(
                            new Filter<JobDocumentSetMileStoneFile>(x => x.EntityLink.ID).InList(lastmilestone.ID),
                            new Columns<JobDocumentSetMileStoneFile>(x=>x.EntityLink.DocumentSet.ID)
                                .Add(x => x.DocumentLink.FileName)
                                .Add(x => x.DocumentLink.ID),
                            new SortOrder<JobDocumentSetMileStoneFile>(x => x.DocumentLink.FileName)
                        ).Rows.Select(r=>r.ToObject<JobDocumentSetMileStoneFile>()).ToArray();                        
                    }
                }
                
                updates[milestone] = files;
            }
            var grid = new JobDocumentSetMileStoneGrid();
            grid.OnAfterSave += (editor, items) =>
            {
                if (updates.Keys.Count == 1)
                    return;
                List<JobDocumentSetMileStoneFile> fileupdates = new List<JobDocumentSetMileStoneFile>();
                foreach (var milestone in updates.Keys)
                {
                    foreach (var file in updates[milestone])
                    {
                        file.EntityLink.ID = milestone.ID;
                        fileupdates.Add(file);
                    }
                }

                if (fileupdates.Any())
                    new Client<JobDocumentSetMileStoneFile>().Save(fileupdates,"");
            };

            if (grid.EditItems(updates.Keys.ToArray(), (t) =>
                {
                    if ((t == typeof(JobDocumentSetMileStoneFile)) && (updates.Keys.Count == 1))
                    {
                        CoreTable result = new CoreTable();
                        result.LoadColumns(typeof(JobDocumentSetMileStoneFile));
                        result.LoadRows(updates[updates.Keys.First()]);
                        return result;
                    }
                    return null;
                }, true))
            {
                Refresh();
            }
        }

        private void ChangeMileStoneStatus(CoreRow[] rows, JobDocumentSetMileStoneStatus newstatus, DateTime? issued, DateTime? closed)
        {
            var milestones = rows.Select(r=>r.ToObject<JobDocumentSetMileStone>()).ToArray();
            foreach (var milestone in milestones)
            {
                if (issued.HasValue)
                    milestone.Submitted = issued.Value;
                if (closed.HasValue)
                    milestone.Closed = closed.Value;
                milestone.Status = newstatus;
            }

            using (new WaitCursor())
                new Client<JobDocumentSetMileStone>().Save(milestones, "Changed Status to " + newstatus.ToString().SplitCamelCase());
            Refresh();
        }

        private void EditMileStones(CoreRow[] rows)
        {
            var ids = rows.Select(r => r.Get<JobDocumentSetMileStone, Guid>(x => x.ID)).ToArray();
            var milestones = new Client<JobDocumentSetMileStone>().Query(
                new Filter<JobDocumentSetMileStone>(x => x.ID).InList(ids)
            ).Rows.Select(r=>r.ToObject<JobDocumentSetMileStone>()).ToArray();
            var grid = new JobDocumentSetMileStoneGrid();
            if (grid.EditItems(milestones))
                Refresh();
        }

        private void DeleteMileStone(CoreRow[] rows)
        {
            var milestones = rows.Select(r=>r.ToObject<JobDocumentSetMileStone>()).ToArray();
            using (new WaitCursor())
                new Client<JobDocumentSetMileStone>().Delete(milestones,"Deleted by User");
            Refresh();
        }

        private void TreeGrid_OnCellToolTipOpening(object? sender, TreeGridCellToolTipOpeningEventArgs e)
        {
            var column = e.Column.MappingName.Replace("Blocks[", "").Replace("]", "");
            var data = (e.Record as DocumentSetNode).Blocks[column];
            if (String.IsNullOrWhiteSpace(data))
                return;

            var block = Serialization.Deserialize<JobDocumentSetMileStoneBlock>(data.ToString());
            Guid id = block.ID;

            TextBlock text = new TextBlock();
            if (!String.IsNullOrWhiteSpace(block.Notes))
            {
                text.Inlines.Add(new Run("Milestone Notes\n") { FontWeight = FontWeights.Bold, TextDecorations = TextDecorations.Underline });
                text.Inlines.Add(new Run(block.Notes.Replace("=", "").Replace("\n\n", "\n")) { FontStyle = FontStyles.Italic });
            }

            MultiQuery query = new MultiQuery();
            if (block.Attachments > 0)
            {

                query.Add<JobDocumentSetMileStoneFile>(
                    new Filter<JobDocumentSetMileStoneFile>(x => x.EntityLink.ID).IsEqualTo(block.ID),
                    new Columns<JobDocumentSetMileStoneFile>(x => x.DocumentLink.FileName),
                    new SortOrder<JobDocumentSetMileStoneFile>(x => x.DocumentLink.FileName)
                );
            }

            if (block.Kanbans > 0)
            {
                query.Add<JobDocumentSetMileStoneKanban>(
                    new Filter<JobDocumentSetMileStoneKanban>(x => x.Entity.ID).IsEqualTo(block.ID),
                    new Columns<JobDocumentSetMileStoneKanban>(x => x.Kanban.Number)
                        .Add(x => x.Kanban.Title)
                        .Add(x => x.Kanban.Completed),
                    new SortOrder<JobDocumentSetMileStoneKanban>(x => x.Kanban.Number)
                );
            }

            if (query.Count > 0)
            {
                query.Query();

                if (query.Contains<JobDocumentSetMileStoneFile>())
                {
                    var files = query.Get<JobDocumentSetMileStoneFile>();
                    if (files.Rows.Any())
                    {
                        if (text.Inlines.Any())
                            text.Inlines.Add(new Run("\n\n"));
                        text.Inlines.Add(new Run("Uploaded Files") { FontWeight = FontWeights.Bold, TextDecorations = TextDecorations.Underline });
                        foreach (var row in files.Rows)
                            text.Inlines.Add(new Run("\n" + row.Get<JobDocumentSetMileStoneFile, String>(c => c.DocumentLink.FileName))
                                { FontStyle = FontStyles.Italic });
                    }
                }

                if (query.Contains<JobDocumentSetMileStoneKanban>())
                {
                    var tasks = query.Get<JobDocumentSetMileStoneKanban>();
                    if (tasks.Rows.Any())
                    {
                        if (text.Inlines.Any())
                            text.Inlines.Add(new Run("\n\n"));
                        text.Inlines.Add(new Run("Task History") { FontWeight = FontWeights.Bold, TextDecorations = TextDecorations.Underline });
                        foreach (var row in tasks.Rows)
                            text.Inlines.Add(
                                new Run(
                                    String.Format(
                                        "\n{0}: {1}",
                                        row.Get<JobDocumentSetMileStoneKanban, int>(c => c.Kanban.Number),
                                        row.Get<JobDocumentSetMileStoneKanban, String>(c => c.Kanban.Title)
                                    )
                                )
                                {
                                    FontStyle = FontStyles.Italic,
                                    TextDecorations = row.Get<JobDocumentSetMileStoneKanban, DateTime>(x => x.Kanban.Completed).IsEmpty()
                                        ? null
                                        : TextDecorations.Strikethrough
                                });
                    }
                }

            }

            if (!text.Inlines.Any())
            {
                e.ToolTip.Template = null;
                return;
            }

            e.ToolTip.Template = TemplateGenerator.CreateControlTemplate(
                typeof(System.Windows.Controls.ToolTip),
                () =>
                {
                    var border = new Border
                    {
                        BorderBrush = new SolidColorBrush(Colors.Gray),
                        BorderThickness = new Thickness(0.75),
                        CornerRadius = new CornerRadius(5),
                        Background = new SolidColorBrush(Colors.LightYellow),
                        Padding = new Thickness(5),
                        Child = text
                    };
                    return border;                   
                }
            );

            
        }

        #endregion

        #region Button Bar Actions

        private void AddTypes(MenuItem parent, Action<Guid> addfunction)
        {
            if(_types.Count == 0)
            {
                MenuItem item = new MenuItem() { Header = "No Document Milestones", IsEnabled = false };
                parent.Items.Add(item);
            }
            else
            {
                foreach (var type in _types.Keys)
                {
                    MenuItem item = new MenuItem() { Header = _types[type].Description, Tag = type };
                    item.Click += (o, e) => addfunction(type);
                    parent.Items.Add(item);
                }
            }
        }
        
        private void Add_OnClick(object sender, RoutedEventArgs e)
        {
            if (FolderIDs?.Any() != true)
            {
                MessageBox.Show("Please choose a Folder first!");
                return;
            }
            else if(FolderIDs.First() == CoreUtils.FullGuid)
            {
                MessageBox.Show("Cannot add items to this folder.");
                return;
            }

            ContextMenu menu = new ContextMenu();
            var onetoone = new MenuItem() { Header = "Add Individual Files" };
            AddTypes(onetoone, AddOneToOneFiles);
            menu.Items.Add(onetoone);

            var manytoone = new MenuItem() { Header = "Add Sets of Files" };
            AddTypes(manytoone, AddManyToOneFiles);
            menu.Items.Add(manytoone);

            menu.Items.Add(new Separator());
            
            var manual = new MenuItem() { Header = "Add Document Set Manually" };
            manual.Click += (o, e) => { AddDocumentSet(); };
            menu.Items.Add(manual);

            menu.IsOpen = true;
        }

        private void AddOneToOneFiles(Guid type)
        {

            Guid folderid = FolderIDs?.FirstOrDefault() ?? Guid.Empty;
            
            if (!SelectFiles(out String[] filenames))
                return;

            Progress.ShowModal("Preparing Upload", (progress) =>
            {
                Dictionary<String, Tuple<Document, JobDocumentSet, JobDocumentSetMileStone, JobDocumentSetMileStoneFile>> map =
                    new Dictionary<string, Tuple<Document, JobDocumentSet, JobDocumentSetMileStone, JobDocumentSetMileStoneFile>>();

                foreach (var filename in filenames)
                {
                    var data = File.ReadAllBytes(filename);
                    
                    Document document = new Document()
                    {
                        FileName = Path.GetFileName(filename).ToLower(),
                        Data = data,
                        CRC = CoreUtils.CalculateCRC(data),
                        TimeStamp = new FileInfo(filename).LastWriteTime
                    }; 
                    
                    JobDocumentSet set = new JobDocumentSet();
                    set.Job.ID = Job.ID;
                    set.Job.Synchronise(Job);
                    set.Folder.ID = folderid;
                    set.Code = Path.GetFileNameWithoutExtension(filename).ToUpper();
                    set.Description = Path.GetFileNameWithoutExtension(filename).ToUpper();
                    set.Discipline.ID = DisciplineID;
                    set.Type.ID = TypeID;
                    set.Category.ID = CategoryID;
                    set.Area.ID = AreaID;

                    JobDocumentSetMileStone milestone = new JobDocumentSetMileStone();
                    milestone.Type.ID = type;
                    milestone.Status = JobDocumentSetMileStoneStatus.InProgress;
                    milestone.Due = DateTime.Today;
                
                    JobDocumentSetMileStoneFile file = new JobDocumentSetMileStoneFile();
                    map[filename] = new Tuple<Document, JobDocumentSet, JobDocumentSetMileStone, JobDocumentSetMileStoneFile>(
                        document,
                        set,
                        milestone,
                        file
                    );
                }

                progress.Report("Uploading Files");
                var docs = map.Select(x => x.Value.Item1);
                new Client<Document>().Save(docs, "Uploaded By File Selection");

                progress.Report("Creating Document Sets");
                var sets = map.Select(x => x.Value.Item2);
                new Client<JobDocumentSet>().Save(sets, "Uploaded by File Selection");
                
                progress.Report("Creating MileStones");
                foreach (var key in map.Keys)
                    map[key].Item3.DocumentSet.ID = map[key].Item2.ID; 
                var milestones = map.Select(x => x.Value.Item3);
                new Client<JobDocumentSetMileStone>().Save(milestones, "Uploaded by File Selection");
                
                progress.Report("Linking Documents");
                foreach (var key in map.Keys)
                {
                    map[key].Item4.EntityLink.ID = map[key].Item3.ID;
                    map[key].Item4.DocumentLink.ID = map[key].Item1.ID;
                }
                var files = map.Select(x => x.Value.Item4);
                new Client<JobDocumentSetMileStoneFile>().Save(files, "Uploaded by File Selection");
                
            });
            MessageBox.Show(String.Format("{0} Document Sets Created", filenames.Length));
            Refresh();
        }

        private void AddManyToOneFiles(Guid type)
        {
        
            Guid folderid = FolderIDs?.FirstOrDefault() ?? Guid.Empty;
            if (!SelectFiles(out String[] filenames))
                return;

            JobDocumentSet set = new JobDocumentSet();
            set.Job.ID = Job.ID;
            set.Job.Synchronise(Job);
            
            set.Folder.ID = folderid;
            
            set.Discipline.ID = DisciplineID;
            set.Type.ID = TypeID;
            set.Category.ID = CategoryID;
            set.Area.ID = AreaID;
            
            var grid = new DynamicDataGrid<JobDocumentSet>();
            grid.OnAfterSave += (form, items) =>
            {
                Progress.ShowModal("Creating MileStone", (progress) =>
                {
                    JobDocumentSetMileStone milestone = new JobDocumentSetMileStone();
                    milestone.DocumentSet.ID = set.ID;
                    milestone.Type.ID = type;
                    milestone.Status = JobDocumentSetMileStoneStatus.InProgress;
                    milestone.Due = DateTime.Today;
                    new Client<JobDocumentSetMileStone>().Save(milestone, "Uploaded By File Selection");

                    progress.Report("Uploading Files");
                    List<Document> documents = new List<Document>();
                    foreach (var filename in filenames)
                    {
                        var data = File.ReadAllBytes(filename);
                        Document document = new Document()
                        {
                            FileName = Path.GetFileName(filename).ToLower(),
                            Data = data,
                            CRC = CoreUtils.CalculateCRC(data),
                            TimeStamp = new FileInfo(filename).LastWriteTime
                        };
                        documents.Add(document);
                        new Client<Document>().Save(documents, "Uploaded by File Selection");
                    }

                    progress.Report("Creating File Links");
                    List<JobDocumentSetMileStoneFile> files = new List<JobDocumentSetMileStoneFile>();
                    foreach (var document in documents)
                    {
                        JobDocumentSetMileStoneFile file = new JobDocumentSetMileStoneFile();
                        file.EntityLink.ID = milestone.ID;
                        file.DocumentLink.ID = document.ID;
                        files.Add(file);
                    }

                    new Client<JobDocumentSetMileStoneFile>().Save(files, "Uploaded by File Selection");
                });
            };
            if (grid.EditItems(new[] { set }))
            {
                MessageBox.Show(String.Format("{0} files uploaded", filenames.Length));
                Refresh();
            }
            
        }

        private void AddDocumentSet()
        {
            Guid folderid = FolderIDs?.FirstOrDefault() ?? Guid.Empty;
            
            JobDocumentSet set = new JobDocumentSet();
            set.Job.ID = Job.ID;
            set.Job.Synchronise(Job);
            set.Folder.ID = folderid;
            set.Discipline.ID = DisciplineID;
            set.Type.ID = TypeID;
            set.Category.ID = CategoryID;
            set.Area.ID = AreaID;
            
            var grid = new DynamicDataGrid<JobDocumentSet>();
            if (grid.EditItems(new[] { set }))
                Refresh();
        }

        private void Edit_OnClick(object sender, RoutedEventArgs e)
        {
            if (treeGrid.SelectedItem == null)
            {
                MessageBox.Show("Please choose a Document Set first");
                return;
            }

            Guid[] setIDs = treeGrid.SelectedItems.Select(x => (x as DocumentSetNode).ID).ToArray();

            EditDocumentSets(setIDs);
        }

        private void EditDocumentSets(Guid[] setIDs)
        {
            var sets = new Client<JobDocumentSet>().Query(
                new Filter<JobDocumentSet>(x => x.ID).InList(setIDs)
            ).Rows.Select(x => x.ToObject<JobDocumentSet>()).ToArray();
            var grid = new DynamicDataGrid<JobDocumentSet>();
            // grid.OnCustomiseEditor += (form, items, column, editor) =>
            // {
            //     if (String.Equals(column.ColumnName, "Discipline.ID"))
            //         editor.Editable = DisciplineVisible ? Editable.Enabled : Editable.Hidden;
            //     if (String.Equals(column.ColumnName, "Type.ID"))
            //         editor.Editable = TypeVisible ? Editable.Enabled : Editable.Hidden;
            //     if (String.Equals(column.ColumnName, "Category.ID"))
            //         editor.Editable = CategoryVisible ? Editable.Enabled : Editable.Hidden;
            //     if (String.Equals(column.ColumnName, "Area.ID"))
            //         editor.Editable = AreaVisible ? Editable.Enabled : Editable.Hidden;
            // };
            if (grid.EditItems(sets))
                UpdateNodes(sets);
        }

        private void UpdateNodes(IEnumerable<JobDocumentSet> sets)
        {
            if (_documentsets == null)
                return;
            foreach (var set in sets)
            {
                var node = _documentsets.GetNode(set.ID);
                if (node != null)
                {
                    

                    var tags = new List<String>()
                    {
                        set.Discipline.Description,
                        set.Type.Description,
                        set.Category.Description,
                        set.Area.Description
                    }.Where(x=>!String.IsNullOrWhiteSpace(x)).Distinct().ToArray();
                    JobDocumentSetDescriptionBlock desc = new JobDocumentSetDescriptionBlock(
                        set.ID, set.Code, set.Description, tags);
                    node.Description = Serialization.Serialize(desc);

                    JobDocumentSetDetailsBlock dets = new JobDocumentSetDetailsBlock()
                    {
                        ID = set.ID,
                        Date = set.Date,
                        Size = set.Size,
                        Scale = set.Scale,
                        Employee = set.Employee.Name
                    };
                    node.Details = Serialization.Serialize(dets);
                }
            }
            
        }

        private void HideRejected_OnClick(object sender, RoutedEventArgs e)
        {
            _hidesuperceded = !_hidesuperceded;
            HideSupercededLabel.Content = _hidesuperceded ? "Show All" : "Last Only";
            Refresh();
        }

        private void Delete_OnClick(object sender, RoutedEventArgs e)
        {
            if ((treeGrid.SelectedItems == null) || !treeGrid.SelectedItems.Any())
            {
                MessageBox.Show("Please choose a Document Set first");
                return;
            }
            
            if (MessageBox.Show(
                    "Are you sure you wish to delete the selected Document Sets?", 
                    "Confirm Delete", 
                    MessageBoxButton.YesNo
                ) != MessageBoxResult.Yes)
                return;
            
            List<JobDocumentSet> updates = new List<JobDocumentSet>();
            List<DocumentSetNode> orphans = new List<DocumentSetNode>();
            
            var items = treeGrid.SelectedItems.Select(x => (DocumentSetNode)x).ToArray();
            foreach (DocumentSetNode item in items)
            {
                var children = item.Children.Where(x => !items.Contains(x));
                if (children.Any())
                orphans.AddRange(children);
            }

            if (orphans.Any())
            {
                var confirm = MessageBox.Show(
                    "These Document Sets contain children!\nDo you wish to delete these as well?",
                    "Delete Children",
                    MessageBoxButton.YesNoCancel
                );
                if (confirm == MessageBoxResult.Cancel)
                    return;
                if (confirm == MessageBoxResult.No)
                {
                    foreach (var orphan in orphans)
                    {
                        var update = new JobDocumentSet();
                        update.ID = orphan.ID;
                        update.Parent.ID = Guid.Empty;
                        updates.Add(update);
                    }
                    return;
                }
            }
            Progress.ShowModal("Deleting Document Set",(progress) =>
            {
                if (updates.Any())
                    new Client<JobDocumentSet>().Save(updates, "Parent Document Deleted");
                var deletes = items.Select(x=>new JobDocumentSet() { ID = x.ID }).ToArray();
                new Client<JobDocumentSet>().Delete(deletes, "Deleted By User");               
            });
            Refresh();
        }
        
        #endregion

        private void FlatList_OnClick(object sender, RoutedEventArgs e)
        {
            _flatlist = !_flatlist;
            FlatListLabel.Content = _flatlist ? "Tree View" : "Flat List";
            Refresh();
        }

        private void IncludeRetired_OnClick(object sender, RoutedEventArgs e)
        {
            _includeretired = !_includeretired;
            FlatListLabel.Content = _includeretired ? "Active Only" : "Include Retired";
            Refresh();
        }

        private void TreeGrid_OnSelectionChanged(object? sender, GridSelectionChangedEventArgs e)
        {
            //var treeColumn = treeGrid.Columns[e.CurrentRowColumnIndex.ColumnIndex];
            //var column = treeColumn.MappingName.Replace("Blocks[","").Replace("]","");
            // var column = e.Column.MappingName.Replace("Blocks[","").Replace("]","");
            // var data = (e.Record as DocumentSetNode).Blocks[column];
            // if (String.IsNullOrWhiteSpace(data))
            //     return;
            //
            // var block = Serialization.Deserialize<JobDocumentSetMileStoneBlock>(data.ToString());
            // Guid id = block.ID;           
        }

        private void TreeGrid_OnCurrentCellActivated(object? sender, CurrentCellActivatedEventArgs e)
        {

            var node = treeGrid.CurrentItem as DocumentSetNode;
            if (node == null)
                return;
            
            
            var treeColumn = treeGrid.Columns[e.CurrentRowColumnIndex.ColumnIndex];
            var column = treeColumn.MappingName.Replace("Blocks[","").Replace("]","");
            
            if (!node.Blocks.ContainsKey(column))
                MileStoneSelected(null);
            else
            {
                var block = Serialization.Deserialize<JobDocumentSetMileStoneBlock>(node.Blocks[column]);
                MileStoneSelected?.Invoke(block);               
            }

            
        }

        private void TreeGrid_OnCellDoubleTapped(object? sender, TreeGridCellDoubleTappedEventArgs e)
        {
            var set = e.Record as DocumentSetNode;
            if (set != null)
                EditDocumentSets(new Guid[] { set.ID });
        }

        private void Export_OnClick(object sender, RoutedEventArgs e)
        {
            var engine = new ExcelEngine();
            var application = engine.Excel;
            var workbook = application.Workbooks.Create(1);
            workbook.Version = ExcelVersion.Excel2007;
            
            var sheet = workbook.Worksheets[0];
            sheet.Name = "Document Register";
            
            int iRow = 1;
            int iCol = 1;
            
            SetHeader(sheet, iRow, iCol++, "Document Number", 40, false, false);
            SetHeader(sheet, iRow, iCol++, "Description", 80, false, false);

            SetHeader(sheet, iRow, iCol++, "Discipline", 15, true, false);
            SetHeader(sheet, iRow, iCol++, "Type", 15, true, false);
            SetHeader(sheet, iRow, iCol++, "Category", 15, true, false);
            SetHeader(sheet, iRow, iCol++, "ITP Area", 15, true, false);
            
            Dictionary<String, int> blkmap = new Dictionary<string, int>();

            foreach (var key in _types.Keys)
            {
                var _type = _types[key];
                SetHeader(sheet, iRow, iCol, _type.Code, 5, true, true);
                sheet.Range[iRow, iCol].Text = _type.Code;
                int iCount = _types[key].Columns.Count;
                if (iCount > 1)
                    sheet.Range[iRow, iCol, iRow, iCol + iCount - 1].Merge();
                foreach (var col in _type.Columns)
                {
                    sheet.SetColumnWidth(iCol,5);
                    blkmap[col] = iCol++;
                }
            }

            iRow++;
            foreach (var node in _documentsets.Nodes)
            {
                var desc = Serialization.Deserialize<JobDocumentSetDescriptionBlock>(node.Description);
                CoreRow row = Data.Rows.FirstOrDefault(r => r.Get<JobDocumentSet, Guid>(c => c.ID) == desc.ID);
                if (row != null)
                {
                    iCol = 1;
                    SetContent(sheet, iRow, iCol++, desc.Code, false, false, System.Drawing.Color.Transparent);
                    SetContent(sheet, iRow, iCol++, desc.Description, false, true, System.Drawing.Color.Transparent);

                    SetContent(sheet, iRow, iCol++, row.Get<JobDocumentSet, String>(c => c.Discipline.Description), true, false, System.Drawing.Color.Transparent);
                    SetContent(sheet, iRow, iCol++, row.Get<JobDocumentSet, String>(c => c.Type.Description), true, false, System.Drawing.Color.Transparent);
                    SetContent(sheet, iRow, iCol++, row.Get<JobDocumentSet, String>(c => c.Category.Description), true, false, System.Drawing.Color.Transparent);
                    SetContent(sheet, iRow, iCol++, row.Get<JobDocumentSet, String>(c => c.Area.Description), true, false, System.Drawing.Color.Transparent);
                    
                    foreach (var key in node.Blocks.Keys)
                    {
                        if (!String.IsNullOrWhiteSpace(node.Blocks[key]))
                        {
                            var block = Serialization.Deserialize<JobDocumentSetMileStoneBlock>(node.Blocks[key]);
                            iCol = blkmap[$"Blocks[{key}]"];
                            var color = JobDocumentSetMileStoneConverter.StatusColors[block.Status];
                            var status = String.IsNullOrWhiteSpace(block.Revision) ? "--" : block.Revision;
                            SetContent(sheet, iRow, iCol, status, true, false, color);
                        }
                    }
                }
                iRow++;
            }

            sheet.UsedRange.BorderAround();
            sheet.UsedRange.BorderInside();
            sheet.UsedRange.AutofitRows();
            foreach (var row in sheet.UsedRange.Rows)
            {
                row.RowHeight += 5;
                row.VerticalAlignment = ExcelVAlign.VAlignCenter;
            }
            
            var dlg = new SaveFileDialog();
            dlg.Filter = "Excel Files (*.xlsx)|*.xlsx";
            dlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            dlg.FileName = string.Format("Document Register {0:yyyy-MM-dd hh-mm-ss}.xlsx", DateTime.Now);
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    workbook.SaveAs(dlg.FileName, ExcelSaveType.SaveAsXLS);
                    Process.Start(new ProcessStartInfo(dlg.FileName) { UseShellExecute = true });
                }
                catch (Exception e2)
                {
                    MessageBox.Show("Error saving spreadsheet!\n\n" + e2.Message);
                }
            }
            
        }

        private void SetContent(IWorksheet sheet, int row, int col, string text, bool center, bool wrap, Color? color)
        {
            var range = sheet.Range[row, col];
            range.Text = text;
            range.WrapText = wrap;
            if (center)
                range.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
            if (color != null)
                range.CellStyle.Color = color.Value;
        }

        private void SetHeader(IWorksheet sheet, int row, int col, string text, double width, bool center, bool rotate)
        {
            var range = sheet.Range[row, col];
            range.Text = text;
            range.CellStyle.Color = System.Drawing.Color.Silver;
            sheet.SetColumnWidth(col,width);
            if (center)
                range.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
            if (rotate)
                range.CellStyle.Rotation = 90;
        }
    }
}