194 lines
7.9 KiB
C#
194 lines
7.9 KiB
C#
<Query Kind="Program">
|
|
<Connection>
|
|
<ID>6a7da5d6-5345-4fd8-9ca6-ab567ac5f639</ID>
|
|
<Persist>true</Persist>
|
|
<Server>D0014-SQL-01.sdt.local</Server>
|
|
<Database>jazz tst UNC 20190716.1</Database>
|
|
<ShowServer>true</ShowServer>
|
|
</Connection>
|
|
<Reference><ProgramFilesX64>\Microsoft SDKs\Azure\.NET SDK\v2.9\bin\plugins\Diagnostics\Newtonsoft.Json.dll</Reference>
|
|
<NuGetReference>Amazon.Lambda.S3Events</NuGetReference>
|
|
<NuGetReference>AWSSDK.Core</NuGetReference>
|
|
<NuGetReference>AWSSDK.S3</NuGetReference>
|
|
<Namespace>Amazon</Namespace>
|
|
<Namespace>Amazon.Auth.AccessControlPolicy</Namespace>
|
|
<Namespace>Amazon.Auth.AccessControlPolicy.ActionIdentifiers</Namespace>
|
|
<Namespace>Amazon.Internal</Namespace>
|
|
<Namespace>Amazon.Lambda.S3Events</Namespace>
|
|
<Namespace>Amazon.MissingTypes</Namespace>
|
|
<Namespace>Amazon.Runtime</Namespace>
|
|
<Namespace>Amazon.Runtime.CredentialManagement</Namespace>
|
|
<Namespace>Amazon.Runtime.CredentialManagement.Internal</Namespace>
|
|
<Namespace>Amazon.Runtime.EventStreams</Namespace>
|
|
<Namespace>Amazon.Runtime.EventStreams.Internal</Namespace>
|
|
<Namespace>Amazon.Runtime.Internal</Namespace>
|
|
<Namespace>Amazon.Runtime.Internal.Auth</Namespace>
|
|
<Namespace>Amazon.Runtime.Internal.Settings</Namespace>
|
|
<Namespace>Amazon.Runtime.Internal.Transform</Namespace>
|
|
<Namespace>Amazon.Runtime.Internal.Util</Namespace>
|
|
<Namespace>Amazon.Runtime.SharedInterfaces</Namespace>
|
|
<Namespace>Amazon.Runtime.SharedInterfaces.Internal</Namespace>
|
|
<Namespace>Amazon.S3</Namespace>
|
|
<Namespace>Amazon.S3.Encryption</Namespace>
|
|
<Namespace>Amazon.S3.Encryption.Internal</Namespace>
|
|
<Namespace>Amazon.S3.Internal</Namespace>
|
|
<Namespace>Amazon.S3.IO</Namespace>
|
|
<Namespace>Amazon.S3.Model</Namespace>
|
|
<Namespace>Amazon.S3.Model.Internal.MarshallTransformations</Namespace>
|
|
<Namespace>Amazon.S3.Transfer</Namespace>
|
|
<Namespace>Amazon.S3.Util</Namespace>
|
|
<Namespace>Amazon.Util</Namespace>
|
|
<Namespace>Amazon.Util.Internal</Namespace>
|
|
<Namespace>Amazon.Util.Internal.PlatformServices</Namespace>
|
|
<Namespace>Newtonsoft.Json</Namespace>
|
|
<Namespace>Newtonsoft.Json.Converters</Namespace>
|
|
<Namespace>Newtonsoft.Json.Linq</Namespace>
|
|
<Namespace>Newtonsoft.Json.Schema</Namespace>
|
|
<Namespace>Newtonsoft.Json.Serialization</Namespace>
|
|
<Namespace>System</Namespace>
|
|
<Namespace>System.Diagnostics</Namespace>
|
|
<Namespace>System.Diagnostics.Tracing</Namespace>
|
|
<Namespace>System.IO.Compression</Namespace>
|
|
<Namespace>ThirdParty.BouncyCastle.Asn1</Namespace>
|
|
<Namespace>ThirdParty.BouncyCastle.Asn1.Utilities</Namespace>
|
|
<Namespace>ThirdParty.BouncyCastle.Math</Namespace>
|
|
<Namespace>ThirdParty.BouncyCastle.OpenSsl</Namespace>
|
|
<Namespace>ThirdParty.BouncyCastle.Utilities.IO.Pem</Namespace>
|
|
<Namespace>ThirdParty.Ionic.Zlib</Namespace>
|
|
<Namespace>ThirdParty.Json.LitJson</Namespace>
|
|
<Namespace>ThirdParty.MD5</Namespace>
|
|
</Query>
|
|
|
|
void Main()
|
|
{
|
|
var Departments = (from dept in DeptPayPeriodSummaries
|
|
select new DeptPayPeriodSummary(dept))
|
|
.ToList()
|
|
.GroupBy(d => d.DepartmentID, (key, dx) => new { key, data = dx.OrderBy(d => d.PeriodEndDate).ToList() })
|
|
.Where(d => d.data.Sum(x => x.TotalVariance) != 0.0d)
|
|
.Select(grp => new DeptInfo(grp.data));
|
|
Departments.Dump("Raw List");
|
|
var top5 = Departments
|
|
.OrderBy(d => d.latest_total_variance)
|
|
.ThenBy(d => d.latest_pay_variance)
|
|
.ThenBy(d => d.latest_flex_variance)
|
|
.Take(5).ToList();
|
|
var bottom5 = Departments
|
|
.OrderByDescending(d => d.latest_total_variance)
|
|
.ThenByDescending(d => d.latest_pay_variance)
|
|
.ThenByDescending(d => d.latest_flex_variance)
|
|
.Take(5).ToList();
|
|
bottom5.Dump("Bottom5");
|
|
top5.Dump("top5");
|
|
var departments = (top5.Union(bottom5).Distinct())
|
|
.OrderBy(d => d.latest_total_variance).ToList();
|
|
departments.Dump("departments");
|
|
}
|
|
|
|
// Define other methods and classes here
|
|
public partial class DeptPayPeriodSummary
|
|
{
|
|
public int DepartmentID { get; set; }
|
|
public int PayPeriodID { get; set; }
|
|
public string Department { get; set; }
|
|
public DateTime PeriodStartDate { get; set; }
|
|
public DateTime PeriodEndDate { get; set; }
|
|
public double ActualHours { get; set; }
|
|
public double ActualDollars { get; set; }
|
|
public double ActualUOS { get; set; }
|
|
public double TargetHours { get; set; }
|
|
public double TargetDollars { get; set; }
|
|
public double TargetUOS { get; set; }
|
|
public double VarianceHours { get; set; }
|
|
public double VarianceDollars { get; set; }
|
|
public double VarianceUOS { get; set; }
|
|
public double ActualPayRate => MathExtensions.DivideBy((double)ActualDollars, ActualHours);
|
|
public double TargetPayRate => MathExtensions.DivideBy((double)TargetDollars, TargetHours);
|
|
public double VariancePayRate => TargetPayRate - ActualPayRate;
|
|
public double PayRateVariance => VariancePayRate * ActualHours;
|
|
public double FlexVariance => VarianceHours * TargetPayRate;
|
|
public double TotalVariance => PayRateVariance + FlexVariance;
|
|
public double VariancePayDollars => (ActualPayRate - TargetPayRate) * ActualHours;
|
|
public double VarianceFlexDollars => TargetPayRate * VarianceHours;
|
|
|
|
public DeptPayPeriodSummary(LINQPad.User.DeptPayPeriodSummary dept)
|
|
{
|
|
DepartmentID = dept.DepartmentID;
|
|
PayPeriodID = dept.PayPeriodID;
|
|
Department = dept.Department;
|
|
PeriodStartDate = dept.PeriodStartDate;
|
|
PeriodEndDate = dept.PeriodEndDate;
|
|
ActualHours = dept.ActualHours;
|
|
ActualDollars = dept.ActualDollars;
|
|
ActualUOS = dept.ActualUOS;
|
|
TargetHours = dept.TargetHours;
|
|
TargetDollars = dept.TargetDollars;
|
|
TargetUOS = dept.TargetUOS;
|
|
VarianceHours = dept.VarianceHours;
|
|
VarianceDollars = dept.VarianceDollars;
|
|
VarianceUOS = dept.VarianceUOS;
|
|
}
|
|
|
|
}
|
|
|
|
public class DeptInfo
|
|
{
|
|
public string hyperlink => "";
|
|
|
|
internal int deptid { get; set; }
|
|
public string name { get; set; }
|
|
internal double latest_total_variance { get; set; }
|
|
internal double latest_pay_variance { get; set; }
|
|
internal double latest_flex_variance { get; set; }
|
|
public string total_variance => $"{latest_total_variance:C}";
|
|
public string pay_variance => $"{latest_pay_variance:C}";
|
|
public string flex_variance => $"{latest_flex_variance:C}";
|
|
public string six_period_total_chart { get; set; }
|
|
public string six_period_pay_chart { get; set; }
|
|
public string six_period_flex_chart { get; set; }
|
|
public IEnumerable<int> totalGraph { get; set; }
|
|
public IEnumerable<int> payGraph { get; set; }
|
|
public IEnumerable<int> flexGraph { get; set; }
|
|
public int graphMin { get; set; }
|
|
public int graphMax { get; set; }
|
|
public string total_color => latest_total_variance >= 0 ? "positive" : "negative";
|
|
public string pay_color => latest_pay_variance >= 0 ? "positive" : "negative";
|
|
public string flex_color => latest_flex_variance >= 0 ? "positive" : "negative";
|
|
|
|
public DeptInfo(List<DeptPayPeriodSummary> department)
|
|
{
|
|
deptid = department.First().DepartmentID;
|
|
name = department.First().Department;
|
|
latest_total_variance = department.Sum(d => d.TotalVariance);
|
|
latest_pay_variance = department.Sum(d => d.PayRateVariance);
|
|
latest_flex_variance = department.Sum(d => d.FlexVariance);
|
|
totalGraph = department.Select(d => Convert.ToInt32(d.TotalVariance));
|
|
payGraph = department.Select(d => Convert.ToInt32(d.PayRateVariance));
|
|
flexGraph = department.Select(d => Convert.ToInt32(d.FlexVariance));
|
|
graphMin = Math.Min(Math.Min(totalGraph.Min(), payGraph.Min()), flexGraph.Min());
|
|
graphMax = Math.Max(Math.Max(totalGraph.Max(), payGraph.Max()), flexGraph.Max());
|
|
}
|
|
|
|
public DeptInfo(int departmentId, string deptName, double total, double pay, double flex)
|
|
{
|
|
deptid = departmentId;
|
|
name = deptName;
|
|
latest_total_variance = total;
|
|
latest_pay_variance = pay;
|
|
latest_flex_variance = flex;
|
|
}
|
|
|
|
internal string GetLinkUrlPath() => $"/PR/DepartmentProductivity.aspx?deptid={deptid}&mode=payperiod";
|
|
|
|
public bool Equals(DeptInfo x, DeptInfo y)
|
|
{
|
|
if (object.ReferenceEquals(x, y)) return true;
|
|
if (object.ReferenceEquals(x, null) || object.ReferenceEquals(y, null)) return false;
|
|
return (x.deptid == y.deptid);
|
|
}
|
|
|
|
public int GetHashCode(DeptInfo obj)
|
|
{
|
|
return obj.GetHashCode();
|
|
}
|
|
} |