77b65bea-e766-4e3c-a1ed-c4c2534b6019 true D0001-SQL-11.sdt.local jazz dev master Prod true <ProgramFilesX64>\Microsoft SDKs\Azure\.NET SDK\v2.9\bin\plugins\Diagnostics\Newtonsoft.Json.dll Amazon.Lambda.S3Events AWSSDK.Core AWSSDK.S3 Amazon Amazon.Auth.AccessControlPolicy Amazon.Auth.AccessControlPolicy.ActionIdentifiers Amazon.Internal Amazon.Lambda.S3Events Amazon.MissingTypes Amazon.Runtime Amazon.Runtime.CredentialManagement Amazon.Runtime.CredentialManagement.Internal Amazon.Runtime.EventStreams Amazon.Runtime.EventStreams.Internal Amazon.Runtime.Internal Amazon.Runtime.Internal.Auth Amazon.Runtime.Internal.Settings Amazon.Runtime.Internal.Transform Amazon.Runtime.Internal.Util Amazon.Runtime.SharedInterfaces Amazon.Runtime.SharedInterfaces.Internal Amazon.S3 Amazon.S3.Encryption Amazon.S3.Encryption.Internal Amazon.S3.Internal Amazon.S3.IO Amazon.S3.Model Amazon.S3.Model.Internal.MarshallTransformations Amazon.S3.Transfer Amazon.S3.Util Amazon.Util Amazon.Util.Internal Amazon.Util.Internal.PlatformServices Newtonsoft.Json Newtonsoft.Json.Converters Newtonsoft.Json.Linq Newtonsoft.Json.Schema Newtonsoft.Json.Serialization System System.Diagnostics System.Diagnostics.Tracing System.IO.Compression ThirdParty.BouncyCastle.Asn1 ThirdParty.BouncyCastle.Asn1.Utilities ThirdParty.BouncyCastle.Math ThirdParty.BouncyCastle.OpenSsl ThirdParty.BouncyCastle.Utilities.IO.Pem ThirdParty.Ionic.Zlib ThirdParty.Json.LitJson ThirdParty.MD5 /* (from roleDetail in ViewS3RoleAssignmentDetail where roleDetail.RoleName.StartsWith("MR ") && roleDetail.LootID.Contains("MRDEPT|") select roleDetail.RoleName) .Distinct() .OrderBy(p => p).ToList() .Aggregate("var roles = new [] { ", (x, y) => x + $"\"{y}\", ").TrimEnd(", ".ToCharArray()) + " }" */ void Main() { var userNames = new List{ "dachee" }; // var userGUIDs = from up in UserProfiles // where userNames.Contains(up.UserName) // select up.UserGUID; // new List{ // //new Guid("0e8a4d58-f9c1-4a49-837c-dd7db45793fe") // new Guid("76CD86B8-D769-4AAE-9083-F8E6D005AA9D") // }; var users = (from user in UserProfiles select new User { UserInfo = new UserInfo { UserGUID = user.UserGUID, UserName = user.UserName, NameFirst = user.NameFirst, NameLast = user.NameLast, EmailAddress = user.EmailAddress } }) .Where(u => userNames.Contains(u.UserInfo.UserName)) .ToList(); var roles = new[] { // "MR - Commenter", "MR - Director", "MR - Exec Director AVP", "MR - Executive", // "MR - Finance", "MR - Manager", // "MR - Read Only", // "MR - Read Write Only", "MR - Vice President" }; users .ForEach(u => { int x; u.Roles = (from roleDetail in ViewS3RoleAssignmentDetail where roleDetail.UserGUID == u.UserInfo.UserGUID select roleDetail.RoleName).Distinct().OrderBy(r => r).ToList(); u.DepartmentIDs = (from roleDetail in ViewS3RoleAssignmentDetail where roleDetail.UserGUID == u.UserInfo.UserGUID && roleDetail.LootID.Contains("MRDEPT|") && roles.Contains(roleDetail.RoleName) select new { depts = roleDetail.LootID.Split('|').ToList() }) .ToList().SelectMany(d => d.depts).Where(d => int.TryParse(d, out x)).ToList().ConvertAll(int.Parse).Distinct() .ToList(); u.DeptPayPeriods = (from fact in FactDepartmentProductivityMetrics join pp in DimPayPeriods on fact.PayPeriodID equals pp.PayPeriodID where u.DepartmentIDs.Contains(fact.DepartmentID) && pp.PeriodEndCalendarDate < new DateTime(2019, 6, 17) && pp.PeriodStartCalendarDate.HasValue orderby pp.PeriodStartCalendarDate descending select new { fact.DepartmentID, pp.PayPeriodID, PeriodStartCalendarDate = pp.PeriodStartCalendarDate.Value, pp.PeriodEndCalendarDate }) .Distinct().ToList() .GroupBy(dpp => dpp.DepartmentID, (key, grp) => new DeptPayPeriods { DepartmentID = key, PayPeriods = grp.Select(g => new PayPeriod { PeriodID = g.PayPeriodID, StartDate = g.PeriodStartCalendarDate, EndDate = g.PeriodEndCalendarDate }) .ToList().OrderByDescending(pp => pp.StartDate).Take(6).ToList() }).ToList(); var payperiodIDs = u.DeptPayPeriods.SelectMany(d => d.PayPeriods.Select(pp => pp.PeriodID)); var hours = from fact in FactPayrollSampledPayPeriods join payperiod in DimPayPeriods on fact.PayPeriodID equals payperiod.PayPeriodID join paycode in DimPayCodeGroups on fact.PayCodeGroupID equals paycode.PayCodeGroupID join prodclass in DimProductiveClasses on paycode.ProductiveClassID equals prodclass.ProductiveClassID join department in DimDepartments on fact.DepartmentID equals department.DepartmentID where new[] { 1, 4 }.Contains(fact.TimeClassID) && prodclass.Name == "Productive" && u.DepartmentIDs.Contains(fact.DepartmentID) && payperiodIDs.Contains(fact.PayPeriodID) select new DeptPayPeriodTime(fact.DepartmentID, fact.PayPeriodID, fact.TimeClassID, fact.Hours, "hours"); var dollars = from fact in FactPayrollSampledPayPeriods join payperiod in DimPayPeriods on fact.PayPeriodID equals payperiod.PayPeriodID join paycode in DimPayCodeGroups on fact.PayCodeGroupID equals paycode.PayCodeGroupID //join prodclass in DimProductiveClasses on paycode.ProductiveClassID equals prodclass.ProductiveClassID join department in DimDepartments on fact.DepartmentID equals department.DepartmentID where new[] { 1, 4 }.Contains(fact.TimeClassID) //&& prodclass.Name == "Productive" && u.DepartmentIDs.Contains(fact.DepartmentID) && payperiodIDs.Contains(fact.PayPeriodID) select new DeptPayPeriodTime(fact.DepartmentID, fact.PayPeriodID, fact.TimeClassID, fact.Dollars, "dollars"); var uos = from fact in ViewPrimaryStatisticsStaffingByPayPeriod join payperiod in DimPayPeriods on fact.PayPeriodID equals payperiod.PayPeriodID //join paycode in DimPayCodeGroups on fact.PayCodeGroupID equals paycode.PayCodeGroupID //join prodclass in DimProductiveClasses on paycode.ProductiveClassID equals prodclass.ProductiveClassID join department in DimDepartments on fact.DepartmentID equals department.DepartmentID where new[] { 1, 4 }.Contains(fact.TimeClassID) //&& prodclass.Name == "Productive" && u.DepartmentIDs.Contains(fact.DepartmentID) && payperiodIDs.Contains(fact.PayPeriodID) select new DeptPayPeriodTime(fact.DepartmentID, fact.PayPeriodID, fact.TimeClassID, fact.Units.HasValue ? fact.Units.Value : 0.0m, "uos"); var unitSummary = hours.ToList().Concat(dollars.ToList()).Concat(uos.ToList()) .GroupBy(dppt => new { dppt.DepartmentID, dppt.PeriodID, dppt.TimeClassID, dppt.UnitType }, (key, grp) => new DeptPayPeriodTime(key.DepartmentID, key.PeriodID, key.TimeClassID, grp.Where(pp => u.DeptPeriodDict[pp.DepartmentID].Contains(pp.PeriodID)).Sum(i => i.Units), key.UnitType)) .Where(item => u.DeptPeriodDict[item.DepartmentID].Contains(item.PeriodID)) .OrderBy(item => item.DepartmentID) .ThenByDescending(item => item.PeriodID).ToList(); u.Units = unitSummary; }); users.Dump("Users"); users//.Where(u => userGUIDs.Contains(u.UserInfo.UserGUID)) .Aggregate("", (x, y) => x + y.DepartmentIDs.Aggregate(", ", (a, b) => $"{a}, {b}")).Substring(2) .Dump(); users//.Where(u => userGUIDs.Contains(u.UserInfo.UserGUID)) .Aggregate("", (x, y) => x + y.DeptPayPeriods.SelectMany(dpp => dpp.PayPeriods.Select(pp => pp.PeriodID)).Distinct() .Aggregate(", ", (a, b) => $"{a}, {b}")).Substring(2) .Dump(); } // Define other methods and classes here public class User { public UserInfo UserInfo { get; set; } public List Roles { get; set; } public List DepartmentIDs { get; set; } public List DeptPayPeriods { get; set; } internal Dictionary> DeptPeriodDict => DeptPayPeriods.ToDictionary(dpp => dpp.DepartmentID, dpp => dpp.PayPeriods.Select(pp => pp.PeriodID).ToList()); public List Units { get; set; } } public class UserInfo { public Guid UserGUID { get; set; } public string UserName { get; set; } public string NameFirst { get; set; } public string NameLast { get; set; } public string EmailAddress { get; set; } } public class DeptPayPeriods { public int DepartmentID { get; set; } public List PayPeriods { get; set; } //public List Units { get; set; } } public class PayPeriod { public short PeriodID { get; set; } public DateTime StartDate { get; set; } public DateTime EndDate { get; set; } } public class DeptPayPeriodTime { public int DepartmentID { get; set; } public short PeriodID { get; set; } public byte TimeClassID { get; set; } public decimal Units { get; set; } public string UnitType { get; set; } public DeptPayPeriodTime(int departmentID, short periodID, byte timeClassID, decimal units, string unitType) { DepartmentID = departmentID; PeriodID = periodID; TimeClassID = timeClassID; Units = units; UnitType = unitType; } }