public SalesQuoteItemCube(IZetboxContext ctx, DateTime from, DateTime thru)
{
DimDeliveryDate = new Dimension <DateTime, SalesQuoteItemCubeRecord>("Delivery date", g => g.Date.Date)
.BuildYearRange(from.Date, thru.Date)
.BuildMonths()
.Build <DateTime, SalesQuoteItemCubeRecord>();
DimParty = new Dimension <string, SalesQuoteItemCubeRecord>("Party", p => p.Party)
.BuildCustomerEnum(ctx)
.Build <string, SalesQuoteItemCubeRecord>();
SumTotal = new DecimalSumMeasure <SalesQuoteItemCubeRecord>("Total", g => g.Total);
SumTotalCorrected = new DecimalSumMeasure <SalesQuoteItemCubeRecord>("Total corrected", g => g.Total * g.Chance / 100.0M);
QrySalesQuotesItemMonth = new Query <SalesQuoteItemCubeRecord>("Sales quote items / month")
.WithCrossingDimension(DimDeliveryDate)
.WithCrossingDimension(DimParty)
.WithMeasure(SumTotal)
.WithMeasure(SumTotalCorrected);
}
public SalesInvoiceCube(IZetboxContext ctx, DateTime from, DateTime thru)
{
DimDate = new Dimension <DateTime, SalesInvoiceCubeRecord>("Date", g => g.Date.Date)
.BuildYearRange(from.Date, thru.Date)
.BuildMonths()
.Build <DateTime, SalesInvoiceCubeRecord>();
DimFulfillmentDate = new Dimension <DateTime, SalesInvoiceCubeRecord>("Fulfillment date", g => (g.FulfillmentDate ?? DateTime.MinValue).Date)
.BuildYearRange(from.Date, thru.Date)
.BuildMonths()
.Build <DateTime, SalesInvoiceCubeRecord>();
DimParty = new Dimension <string, SalesInvoiceCubeRecord>("Party", p => p.Party)
.BuildCustomerEnum(ctx)
.Build <string, SalesInvoiceCubeRecord>();
SumTotal = new DecimalSumMeasure <SalesInvoiceCubeRecord>("Total", g => g.Total);
SumTotalNet = new DecimalSumMeasure <SalesInvoiceCubeRecord>("Total net", g => g.TotalNet);
SumOpenTotal = new DecimalSumMeasure <SalesInvoiceCubeRecord>("Open Total", g => g.Open);
SumOpenTotalNet = new DecimalSumMeasure <SalesInvoiceCubeRecord>("Open Total net", g => g.Total != 0m ? g.Open * g.TotalNet / g.Total : 0m);
SumFulfillment = new DecimalSumMeasure <SalesInvoiceCubeRecord>("Fulfillment", g => g.Fulfillment);
SumFulfillmentNet = new DecimalSumMeasure <SalesInvoiceCubeRecord>("Fulfillment net", g => g.Total != 0m ? g.Fulfillment * g.TotalNet / g.Total : 0m);
AvgFulfillmentDuration = new FilteredMeasure <SalesInvoiceCubeRecord, double>(f => f.FulfillmentDate.HasValue, new DoubleSumMeasure <SalesInvoiceCubeRecord>("Avg. payment duration", g => (g.FulfillmentDate.Value - g.Date).TotalDays));
QryInvoicesDate = new Query <SalesInvoiceCubeRecord>("Invoices / month")
.WithCrossingDimension(DimDate)
.WithCrossingDimension(DimParty)
.WithMeasure(SumTotal)
.WithMeasure(SumTotalNet)
.WithMeasure(SumOpenTotal)
.WithMeasure(SumOpenTotalNet)
.WithMeasure(AvgFulfillmentDuration);
QryInvoicesFulfillmentDate = new Query <SalesInvoiceCubeRecord>("Invoices fulfillment / month")
.WithCrossingDimension(DimFulfillmentDate)
.WithCrossingDimension(DimParty)
.WithMeasure(SumFulfillment)
.WithMeasure(SumFulfillmentNet)
.WithMeasure(AvgFulfillmentDuration);
}
static void Main(string[] args)
{
Console.WriteLine("Testing Linq-Cube v{0}", typeof(Program).Assembly.GetName().Version);
Console.WriteLine("Building dimensions");
var time = new Dimension <DateTime, Person>("Time", k => k.Birthday)
.BuildYear(1978, Repository.CURRENT_YEAR)
.BuildMonths()
.Build <DateTime, Person>();
var time_empstart = new Dimension <DateTime, Person>("Time employment start", k => k.EmploymentStart.Value, k => k.EmploymentStart.HasValue)
.BuildYearSlice(Repository.MIN_DATE.Year, Repository.CURRENT_YEAR, 1, null, 9, null) // only look at jan-sept
.BuildMonths()
.Build <DateTime, Person>();
var time_employment = new Dimension <DateTime, Person>("Time employment", k => k.EmploymentStart.Value, k => k.EmploymentEnd ?? DateTime.MaxValue, k => k.EmploymentStart.HasValue)
.BuildYear(Repository.MIN_DATE.Year, Repository.CURRENT_YEAR)
.Build <DateTime, Person>();
var gender = new Dimension <string, Person>("Gender", k => k.Gender)
.BuildEnum("M", "F")
.Build <string, Person>();
var salary = new Dimension <decimal, Person>("Salary", k => k.Salary)
.BuildPartition(500, 1000, 2500, "up to {0}", "{0} up to {1}", "{0} and more")
.BuildPartition(100)
.Build <decimal, Person>();
var offices = new Dimension <string, Person>("Office", k => k.Office)
.BuildEnum(Repository.OFFICES)
.Build <string, Person>();
var is_active = new Dimension <bool, Person>("Active", k => k.Active)
.BuildBool()
.Build <bool, Person>();
Console.WriteLine("Building measures");
var countAll = new CountMeasure <Person>("Count", k => true);
var countEmployedFullMonth = new FilteredMeasure <Person, bool>("Count full month", k => k.EmploymentStart.HasValue && k.EmploymentStart.Value.Day == 1, countAll);
var countStartingEmployment = new CountMeasure <Person>("Count Starting Employment (whole year)", (k, entry) => entry.Count <DateTime>(time_employment, (e) => k.EmploymentStart.HasValue && e.Min.Year == k.EmploymentStart.Value.Year));
var sumSalary = new DecimalSumMeasure <Person>("Sum of Salaries", k => k.Salary);
Console.WriteLine("Building queries");
var genderAgeQuery = new Query <Person>("gender over birthday")
.WithChainedDimension(time)
.WithChainedDimension(gender)
.WithMeasure(countAll);
var salaryQuery = new Query <Person>("salary over gender and date of employment")
.WithChainedDimension(time_empstart)
.WithChainedDimension(salary)
.WithChainedDimension(gender)
.WithMeasure(countAll)
.WithMeasure(countEmployedFullMonth)
.WithMeasure(sumSalary);
var countByOfficeQuery = new Query <Person>("count currently employed by office")
.WithChainedDimension(offices)
.WithChainedDimension(time_employment)
.WithChainedDimension(is_active)
.WithMeasure(countAll)
.WithMeasure(countStartingEmployment);
// this query's dimensions can only be accessed in the order specified in "WithDimensions"
// internally this enables the query to optimise measuring significantly
var specialisedQuery = new Query <Person>("test-drive for a single-path query)")
.WithChainedDimension(offices)
.WithChainedDimension(time_empstart)
.WithChainedDimension(time_employment)
.WithChainedDimension(gender)
.WithChainedDimension(salary)
.WithMeasure(countAll)
.WithMeasure(sumSalary);
Stopwatch watch = new Stopwatch();
watch.Start();
CubeResult result;
using (var ctx = new Repository())
{
result = Cube.Execute(ctx.Persons.OrderBy(x => x.EmploymentStart),
genderAgeQuery,
salaryQuery,
countByOfficeQuery
);
}
watch.Stop();
Console.WriteLine("Cube ran for {0}", watch.Elapsed);
////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////
Console.WriteLine(salaryQuery.Name);
Console.WriteLine("==================");
Console.WriteLine();
foreach (var year in time_empstart)
{
Console.WriteLine(year.Label);
Console.WriteLine("==================");
foreach (var gPart in salary)
{
foreach (var gPart2 in gPart)
{
Console.WriteLine("{0}: {1,13}, M: {2,3} W: {3,3}, monthStart: {4,3}",
salary.Name,
gPart2.Label,
result[salaryQuery][year][gPart2][gender]["M"][countAll].IntValue,
result[salaryQuery][year][gPart2][gender]["F"][countAll].IntValue,
result[salaryQuery][year][gPart2][countEmployedFullMonth].IntValue
);
}
}
Console.WriteLine();
}
////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////
Console.WriteLine(countByOfficeQuery.Name);
Console.WriteLine("==================");
Console.WriteLine();
Console.WriteLine("{0,10}|{1}",
string.Empty,
string.Join("|", time_employment.Children.Select(c => string.Format(" {0,6} ", c.Label)).ToArray())
);
Console.WriteLine("----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------");
foreach (var officeEntry in offices)
{
var officeCounts = result[countByOfficeQuery][officeEntry];
Console.WriteLine("{0,10}|{1}",
officeEntry.Label,
string.Join("|", time_employment.Children.Select(c => string.Format(" {0,6} ", officeCounts[c][countAll].IntValue)).ToArray())
);
Console.WriteLine("starting |{0}",
string.Join("|", time_employment.Children.Select(c => string.Format(" {0,6} ", officeCounts[c][countStartingEmployment].IntValue)).ToArray())
);
Console.WriteLine("active |{0}",
string.Join("|", time_employment.Children.Select(c => string.Format(" {0,6} ", officeCounts[c][is_active][true.ToString()][countAll].IntValue)).ToArray())
);
}
Console.WriteLine("Finished, hit the anykey to exit");
Console.ReadKey();
}
public static void TestCube()
{
Console.WriteLine("Building DateTime dimensions");
// Dimension year - months
var birthday = new Dimension <DateTime, Person>("Birthday", k => k.Birthday)
.BuildYear(1978, Repository.CURRENT_YEAR)
.BuildMonths()
.Build <DateTime, Person>();
// A period dimension, only look at jan-sept. very use full for comparing time periods during a year
var time_empstart = new Dimension <DateTime, Person>("Time employment start", k => k.EmploymentStart.Value, k => k.EmploymentStart.HasValue)
.BuildYearSlice(Repository.MIN_DATE.Year, Repository.CURRENT_YEAR, sliceFromMonth: 1, sliceThruMonth: 9) // only look at jan-sept
.BuildMonths()
.Build <DateTime, Person>();
// Year only time dimension
var time_employment = new Dimension <DateTime, Person>("Time employment", k => k.EmploymentStart.Value, k => k.EmploymentEnd ?? DateTime.MaxValue, k => k.EmploymentStart.HasValue)
.BuildYear(Repository.MIN_DATE.Year, Repository.CURRENT_YEAR)
.Build <DateTime, Person>();
Console.WriteLine("Building string dimensions");
// "Enum" dimension with strings
var gender = new Dimension <string, Person>("Gender", k => k.Gender)
.BuildEnum("M", "F")
.Build <string, Person>();
// "Enum" dimension with strings
var offices = new Dimension <string, Person>("Office", k => k.Office)
.BuildEnum(Repository.OFFICES)
.Build <string, Person>();
Console.WriteLine("Building decimal dimensions");
// A partition dimension. Step size 500, from 1000 up to 2500, lower hierarchy has a step size of 100
var salary = new Dimension <decimal, Person>("Salary", k => k.Salary)
.BuildPartition(500, 1000, 2500, "up to {1}", "{0} up to {1}", "{0} and more")
.BuildPartition(100)
.Build <decimal, Person>();
Console.WriteLine("Building bool dimensions");
// Dimension boolean
var is_active = new Dimension <bool, Person>("Active", k => k.Active)
.BuildBool()
.Build <bool, Person>();
Console.WriteLine("Building measures");
var countAll = new CountMeasure <Person>("Count", k => true);
var countEmployedFullMonth = new FilteredMeasure <Person, bool>("Count full month", k => k.EmploymentStart.HasValue && k.EmploymentStart.Value.Day == 1, countAll);
var countStartingEmployment = new CountMeasure <Person>("Count Starting Employment (whole year)", (k, entry) =>
entry.Count <DateTime>(time_employment, (e) =>
k.EmploymentStart.HasValue && e.Min.Year == k.EmploymentStart.Value.Year));
var sumSalary = new DecimalSumMeasure <Person>("Sum of Salaries", k => k.Salary);
Console.WriteLine("Building queries");
var genderAgeQuery = new Query <Person>("gender over birthday")
.WithChainedDimension(gender)
.WithChainedDimension(birthday)
.WithMeasure(countAll);
var salaryQuery = new Query <Person>("salary over gender and date of employment")
.WithChainedDimension(time_empstart)
.WithChainedDimension(salary)
.WithChainedDimension(gender)
.WithMeasure(countAll)
.WithMeasure(countEmployedFullMonth)
.WithMeasure(sumSalary);
var countByOfficeQuery = new Query <Person>("count currently employed by office")
.WithChainedDimension(offices)
.WithChainedDimension(time_employment)
.WithChainedDimension(is_active)
.WithMeasure(countAll)
.WithMeasure(countStartingEmployment);
// this query's dimensions can only be accessed in the order specified in "WithDimensions"
// internally this enables the query to optimise measuring significantly
var specialisedQuery = new Query <Person>("test-drive for a single-path query)")
.WithChainedDimension(offices)
.WithChainedDimension(time_empstart)
.WithChainedDimension(time_employment)
.WithChainedDimension(gender)
.WithChainedDimension(salary)
.WithMeasure(countAll)
.WithMeasure(sumSalary);
Stopwatch watch = new Stopwatch();
watch.Start();
CubeResult <Person> result;
using (var ctx = new Repository())
{
result = Cube.Execute(ctx.Persons.OrderBy(x => x.EmploymentStart),
genderAgeQuery,
salaryQuery,
countByOfficeQuery);
}
watch.Stop();
Console.WriteLine("Cube ran for {0}", watch.Elapsed);
Console.WriteLine();
////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////
Console.WriteLine(genderAgeQuery.Name);
Console.WriteLine("==================");
Console.WriteLine();
foreach (var g in gender)
{
foreach (var b in birthday)
{
Console.WriteLine("{0}-{1}: {2}",
g.Name,
b.Name,
result[genderAgeQuery][g][b][countAll].IntValue);
}
}
////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////
Console.WriteLine(salaryQuery.Name);
Console.WriteLine("==================");
Console.WriteLine();
foreach (var year in time_empstart)
{
Console.WriteLine(year.Name);
Console.WriteLine("==================");
foreach (var gPart in salary)
{
foreach (var gPart2 in gPart)
{
Console.WriteLine("{0}: {1,13}, M: {2,3} W: {3,3}, monthStart: {4,3}",
salary.Name,
gPart2.Name,
result[salaryQuery][year][gPart2][gender]["M"][countAll].IntValue,
result[salaryQuery][year][gPart2][gender]["F"][countAll].IntValue,
result[salaryQuery][year][gPart2][countEmployedFullMonth].IntValue);
}
}
Console.WriteLine();
}
////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////
Console.WriteLine(countByOfficeQuery.Name);
Console.WriteLine("==================");
Console.WriteLine();
Console.WriteLine("{0,10}|{1}",
string.Empty,
string.Join("|", time_employment.Children.Select(c => string.Format(CultureInfo.InvariantCulture, " {0,6} ", c.Name)).ToArray())
);
Console.WriteLine("----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------");
foreach (var officeEntry in offices)
{
var officeCounts = result[countByOfficeQuery][officeEntry];
Console.WriteLine("{0,10}| {1,6} | | | | | | | | | | ",
officeEntry.Name,
officeCounts[countAll].IntValue
);
Console.WriteLine(" |{0}",
string.Join("|", time_employment.Children.Select(c => string.Format(CultureInfo.InvariantCulture, " {0,6} ", officeCounts[c][countAll].IntValue)).ToArray())
);
Console.WriteLine("starting |{0}",
string.Join("|", time_employment.Children.Select(c => string.Format(CultureInfo.InvariantCulture, " {0,6} ", officeCounts[c][countStartingEmployment].IntValue)).ToArray())
);
Console.WriteLine("active |{0}",
string.Join("|", time_employment.Children.Select(c => string.Format(CultureInfo.InvariantCulture, " {0,6} ", officeCounts[c][is_active][true.ToString(CultureInfo.InvariantCulture)][countAll].IntValue)).ToArray())
);
Console.WriteLine(" | | | | | | | | | | | ");
}
}
