prs/prs.desktop/Dashboards/System/DatabaseActivityDashboard.xaml.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using Comal.Classes;
using InABox.Clients;
using InABox.Configuration;
using InABox.Core;
using InABox.WPF;
using InABox.Wpf;
using PRSDesktop.WidgetGroups;
using Syncfusion.UI.Xaml.Charts;

namespace PRSDesktop
{
    public class DatabaseActivityDashboardProperties : IUserConfigurationSettings, IDashboardProperties { }

    public class DatabaseActivityDashboardElement : DashboardElement<DatabaseActivityDashboard, WidgetGroups.System, DatabaseActivityDashboardProperties> { }

    /// <summary>
    ///     Interaction logic for DatabaseActivityDashboard.xaml
    /// </summary>
    public partial class DatabaseActivityDashboard : UserControl, IPanel<UserTracking>, IDashboardWidget<WidgetGroups.System, DatabaseActivityDashboardProperties>
    {
        public enum DatabaseAction
        {
            Read,
            Update
        }

        public enum HistoryView
        {
            Day,
            Week,
            Month,
            Year
        }

        private CoreTable _employees;

        private readonly BitmapImage back = PRSDesktop.Resources.back.AsBitmapImage(32, 32);

        private HistoryViewModel history;
        private readonly BitmapImage next = PRSDesktop.Resources.next.AsBitmapImage(32, 32);

        public DatabaseActivityDashboard()
        {
            InitializeComponent();

            PrevDay.Content = new Image { Source = back };
            NextDay.Content = new Image { Source = next };
        }

        public bool IsReady { get; set; }

        public void CreateToolbarButtons(IPanelHost host)
        {
        }

        public string SectionName => "Database Activity";

        public DatabaseActivityDashboardProperties Properties { get; set; }
        public event LoadSettings<DatabaseActivityDashboardProperties>? LoadSettings;
        public event SaveSettings<DatabaseActivityDashboardProperties>? SaveSettings;

        public DataModel DataModel(Selection selection)
        {
            return new DatabaseActivityDataModel(history);
        }

        public void Refresh()
        {
            if (!IsReady)
                return;

            if (history.To == DateTime.MinValue)
                history.To = DateTime.Today;

            if (history.View == HistoryView.Day)
                CurrentDate.Text = string.Format("{0:ddd, dd MMMM yyyy}", history.To);
            else
                CurrentDate.Text = string.Format("{0:dd MMM yy} - {1:dd MMM yy}", history.From, history.To);


            var colors = new List<Color>();
            var props = typeof(Colors).GetProperties(BindingFlags.Static | BindingFlags.Public).Where(x => x.PropertyType == typeof(Color));
            foreach (var prop in props)
            {
                var color = (Color)prop.GetValue(null);
                if (color != Colors.Transparent && (color.R < 128 || color.G < 128 || color.B < 128))
                    colors.Add(color);
            }

            Hours.Series.Clear();
            foreach (var key in history.Summary.Keys)
            {
                var seriesdata = history.Summary[key];
                if (HasData(seriesdata, history.Action))
                {
                    var series = new ScatterSeries();
                    series.ShowEmptyPoints = false;
                    series.ShowTooltip = true;
                    series.TooltipTemplate = Hours.Resources["tooltipTemplate"] as DataTemplate;
                    series.ItemsSource = seriesdata;
                    series.XBindingPath = "Name";
                    series.YBindingPath = history.Action == DatabaseAction.Read ? "Reads" : "Writes";
                    series.Label = history.Categories[key];
                    series.Tag = key;
                    var colorindex = Hours.Series.Count % colors.Count;
                    var color = colors[colorindex];
                    series.Stroke = new SolidColorBrush(color);
                    series.Interior = new SolidColorBrush(color);
                    Hours.Series.Add(series);
                }
            }

            AllStaff.Width = history.UserID.Equals(Guid.Empty) ? new GridLength(0) : new GridLength(70);
            AllTypes.Width = string.IsNullOrWhiteSpace(history.Type) ? new GridLength(0) : new GridLength(70);
        }

        public Dictionary<string, object[]> Selected()
        {
            return new Dictionary<string, object[]>();
        }

        public void Setup()
        {
            history = new HistoryViewModel();

            DataContext = history;

            var emplookups = new Dictionary<Guid, string> { { Guid.Empty, "All Staff" } };
            _employees = new Client<Employee>().Query(
                LookupFactory.DefineFilter<Employee>(),
                Columns.None<Employee>().Add(
                    x => x.ID,
                    x => x.UserLink.ID,
                    x => x.Name
                ),
                new SortOrder<Employee>(x => x.Name)
            );
            foreach (var row in _employees.Rows)
                emplookups[row.Get<Employee, Guid>(x => x.UserLink.ID)] = row.Get<Employee, string>(x => x.Name);
            StaffView.ItemsSource = emplookups;

            var types = new Dictionary<string, string> { { "", "All Types" } };
            var entities = CoreUtils.Entities.Where(
                x => x.IsSubclassOf(typeof(Entity)) && x.GetInterfaces().Contains(typeof(IRemotable))
            );
            foreach (var type in entities.OrderBy(x => x.Name))
                types[type.Name] = type.Name;
            TypesView.ItemsSource = types;

            LoadCategories("");

            Hours.Legend = new ChartLegend
            {
                LegendPosition = LegendPosition.Outside,
                DockPosition = ChartDock.Right,
                FontSize = 11
            };
        }

        public void Shutdown(CancelEventArgs? cancel)
        {
        }

        public event DataModelUpdateEvent? OnUpdateDataModel;

        public void Heartbeat(TimeSpan time)
        {
        }

        private bool HasData(List<History> history, DatabaseAction action)
        {
            foreach (var record in history)
            {
                var hasdata = action == DatabaseAction.Read ? record.Reads > 0 : record.Writes > 0;
                if (hasdata)
                    return true;
            }

            return false;
        }

        private bool IsContrasted(byte b1, byte b2, int threshold)
        {
            var bDelta = b1 > b2 ? b1 - b2 : b2 - b1;
            return bDelta >= threshold;
        }

        private void LoadCategories(string typename)
        {
            history.Categories.Clear();
            if (string.IsNullOrWhiteSpace(typename))
            {
                var entities = CoreUtils.Entities.Where(
                    x => x.IsSubclassOf(typeof(Entity)) && x.GetInterfaces().Contains(typeof(IRemotable))
                );
                foreach (var type in entities.OrderBy(x => x.Name))
                    history.Categories[type.Name] = type.Name;
            }
            else
            {
                foreach (var row in _employees.Rows)
                    history.Categories[row.Get<Employee, Guid>(x => x.UserLink.ID).ToString()] = row.Get<Employee, string>(x => x.Name);
            }
        }


        private void ViewStyle_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (history == null)
                return;
            history.View = ViewStyle.SelectedIndex == 0 ? HistoryView.Day :
                ViewStyle.SelectedIndex == 1 ? HistoryView.Week :
                ViewStyle.SelectedIndex == 2 ? HistoryView.Month : HistoryView.Year;
            Refresh();
        }

        private void StaffView_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (history == null)
                return;
            if (e.AddedItems.Count > 0)
            {
                var id = ((KeyValuePair<Guid, string>)e.AddedItems[0]).Key;
                history.UserID = id;
                Refresh();
            }
        }

        private void AllStaff_Click(object sender, RoutedEventArgs e)
        {
            StaffView.SelectedValue = Guid.Empty;
        }

        private void TypesView_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (e.AddedItems.Count > 0)
            {
                var type = ((KeyValuePair<string, string>)e.AddedItems[0]).Key;
                LoadCategories(type);
                history.Type = type;
                Refresh();
            }

            Refresh();
        }

        private void AllTypes_Click(object sender, RoutedEventArgs e)
        {
            TypesView.SelectedValue = "";
        }

        private void ActionsView_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (history == null)
                return;
            history.Action = ActionsView.SelectedIndex == 0 ? DatabaseAction.Read : DatabaseAction.Update;
            Refresh();
        }

        private void Search_KeyUp(object sender, KeyEventArgs e)
        {
            if (string.IsNullOrWhiteSpace(Search.Text) || e.Key == Key.Return)
            {
                history.Search = Search.Text;
                Refresh();
            }
        }

        private void PrevDay_Click(object sender, RoutedEventArgs e)
        {
            history.Back();
            Refresh();
        }

        private void CurrentDate_MouseDoubleClick(object sender, MouseButtonEventArgs e)
        {
            history.To = DateTime.Today;
            Refresh();
        }

        private void NextDay_Click(object sender, RoutedEventArgs e)
        {
            history.Forward();
            Refresh();
        }

        private void Export_Click(object sender, RoutedEventArgs e)
        {
        }

        private void Hours_SelectionChanged(object sender, ChartSelectionChangedEventArgs e)
        {
            if (e.SelectedIndex == -1 && e.PreviousSelectedIndex > -1)
            {
                var series = Hours.Series.Where(x => x.IsSeriesVisible).ToArray();
                var value = series.ElementAt(e.PreviousSelectedIndex).Tag as string;

                if (string.IsNullOrWhiteSpace(history.Type))
                    TypesView.SelectedValue = value;
                else
                    StaffView.SelectedValue = Guid.Parse(value);
            }
        }

        public class History : BaseObject
        {
            public string Key;

            public History()
            {
                Reads = double.NaN;
                Writes = double.NaN;
            }

            public string Name { get; set; }
            public double Reads { get; set; }
            public double Writes { get; set; }
        }

        public class HistoryViewModel
        {
            private DatabaseAction _action = DatabaseAction.Update;

            private string _search = "";

            private DateTime _to = DateTime.MinValue;

            private string _type = "";

            private Guid _userid = Guid.Empty;

            private HistoryView _view = HistoryView.Day;


            public HistoryViewModel()
            {
                Categories = new Dictionary<string, string>();
                Summary = new Dictionary<string, List<History>>();
                To = DateTime.Today;
            }

            public HistoryView View
            {
                get => _view;
                set
                {
                    _view = value;
                    Refresh();
                }
            }

            public Guid UserID
            {
                get => _userid;
                set
                {
                    _userid = value;
                    Refresh();
                }
            }

            public string Type
            {
                get => _type;
                set
                {
                    _type = value;
                    Refresh();
                }
            }

            public DatabaseAction Action
            {
                get => _action;
                set
                {
                    _action = value;
                    Refresh();
                }
            }

            public DateTime To
            {
                get => _to.Date;
                set
                {
                    _to = value;
                    Refresh();
                }
            }

            public DateTime From => _view.Equals(HistoryView.Day) ? _to :
                _view.Equals(HistoryView.Week) ? _to.AddDays(-6) :
                _view.Equals(HistoryView.Month) ? _to.AddMonths(-1).AddDays(1) : _to.AddYears(-1).AddDays(1);

            public Dictionary<string, string> Categories { get; }

            public Dictionary<string, List<History>> Summary { get; set; }

            public string Search
            {
                get => _search;
                set
                {
                    _search = value;
                    Refresh();
                }
            }

            public void Forward()
            {
                To = _view.Equals(HistoryView.Day) ? _to.AddDays(1) :
                    _view.Equals(HistoryView.Week) ? _to.AddDays(7) :
                    _view.Equals(HistoryView.Month) ? _to.AddMonths(1) : _to.AddYears(1);
            }

            public void Back()
            {
                To = _view.Equals(HistoryView.Day) ? _to.AddDays(-1) :
                    _view.Equals(HistoryView.Week) ? _to.AddDays(-7) :
                    _view.Equals(HistoryView.Month) ? _to.AddMonths(-1) : _to.AddYears(-1);
            }

            private void AddSearchCriteria(Filter<UserTracking> filter, string search, params Expression<Func<UserTracking, object>>[] expressions)
            {
                var comps = search.Trim().Split(' ');
                foreach (var comp in comps)
                {
                    Filter<UserTracking> result = null;
                    foreach (var expression in expressions)
                        result = result == null ? new Filter<UserTracking>(expression).Contains(comp) : result.Or(expression).Contains(comp);
                    filter.Ands.Add(result);
                }
            }

            private List<History> CreateHistory(DateTime from)
            {
                var result = new List<History>();
                if (View == HistoryView.Day)
                    for (var i = 0; i < 24; i++)
                    {
                        var history = new History();
                        history.Key = string.Format("{0:hhtt}", DateTime.MinValue.AddHours(i));
                        history.Name = history.Key;
                        result.Add(history);
                    }
                else
                    for (var date = from; date <= _to; date = date.AddDays(1))
                    {
                        var history = new History();
                        history.Key = string.Format("{0:dd/MM}", date);
                        history.Name = history.Key;
                        result.Add(history);
                    }

                return result;
            }

            private void UpdateHistory(List<History> list, CoreRow row, DateTime from)
            {
                if (View == HistoryView.Day)
                {
                    for (var i = 0; i < 24; i++)
                    {
                        var reads = row.Get<int>(string.Format("Hour{0:D2}Read", i));
                        if (reads > 0)
                            list[i].Reads = (double.IsNaN(list[i].Reads) ? 0.00F : list[i].Reads) + reads;

                        var writes = row.Get<int>(string.Format("Hour{0:D2}Write", i));
                        if (writes > 0)
                            list[i].Writes = (double.IsNaN(list[i].Writes) ? 0.00F : list[i].Writes) + writes;
                    }
                }
                else
                {
                    var index = row.Get<UserTracking, DateTime>(x => x.Date).Date.Subtract(from.Date).Days;

                    var reads = row.Get<UserTracking, int>(x => x.TotalRead);
                    if (reads > 0)
                        list[index].Reads = (double.IsNaN(list[index].Reads) ? 0.00F : list[index].Reads) + reads;

                    var writes = row.Get<UserTracking, int>(x => x.TotalWrite);
                    if (writes > 0)
                        list[index].Writes = (double.IsNaN(list[index].Writes) ? 0.00F : list[index].Writes) + writes;
                }
            }

            private void Refresh()
            {
                var from = _view.Equals(HistoryView.Day) ? _to.AddDays(-1) :
                    _view.Equals(HistoryView.Week) ? _to.AddDays(-7) :
                    _view.Equals(HistoryView.Month) ? _to.AddMonths(-1) : _to.AddYears(-1);

                var criteria = new Filter<UserTracking>(x => x.Date).IsGreaterThan(from).And(x => x.Date).IsLessThanOrEqualTo(_to);

                if (!_userid.Equals(Guid.Empty))
                    criteria = criteria.And(x => x.User.ID).IsEqualTo(_userid);

                if (!string.IsNullOrWhiteSpace(_type))
                    criteria = criteria.And(x => x.Type).IsEqualTo(_type);

                if (!string.IsNullOrWhiteSpace(_search))
                    criteria = criteria.TextSearch(
                        Search,
                        x => x.User.UserID,
                        x => x.Type
                    );

                var data = new Client<UserTracking>().Query(
                    criteria,
                    null,
                    new SortOrder<UserTracking>(x => x.User.UserID)
                );

                Summary.Clear();
                foreach (var key in Categories.Keys)
                    Summary[key] = CreateHistory(from);
                foreach (var row in data.Rows)
                {
                    var entity = "";
                    if (string.IsNullOrEmpty(_type))
                        entity = row.Get<UserTracking, string>(x => x.Type);
                    else
                        entity = row.Get<UserTracking, Guid>(x => x.User.ID).ToString();

                    if (Summary.ContainsKey(entity))
                        UpdateHistory(Summary[entity], row, from);
                }
            }
        }
    }
}