public void PivotData_3D()
{
var pvtData = new PivotData(new string[] { "a", "year", "month" },
new CountAggregatorFactory());
pvtData.ProcessData(SampleGenerator(100000), GetRecordValue);
Assert.Equal(50000, Convert.ToInt32(
pvtData[pvtData.GetDimensionKeys()[0][0], Key.Empty, Key.Empty].Value));
Assert.Equal(100000, Convert.ToInt32(
pvtData[Key.Empty, pvtData.GetDimensionKeys()[1][0], Key.Empty].Value));
Assert.Equal(30240, Convert.ToInt32(
pvtData[Key.Empty, Key.Empty, pvtData.GetDimensionKeys()[2][0]].Value));
Assert.Equal(100000, Convert.ToInt32(
pvtData[Key.Empty, Key.Empty, Key.Empty].Value));
// incremental processing
pvtData.ProcessData(SampleGenerator(10000), GetRecordValue);
Assert.Equal(55000, Convert.ToInt32(
pvtData[pvtData.GetDimensionKeys()[0][0], Key.Empty, Key.Empty].Value));
Assert.Equal(40240, Convert.ToInt32(
pvtData[Key.Empty, Key.Empty, pvtData.GetDimensionKeys()[2][0]].Value));
Assert.Equal(110000, Convert.ToInt32(
pvtData[Key.Empty, Key.Empty, Key.Empty].Value));
}
public void QueryTest()
{
var pvtData = new PivotData(new[] { "a", "b", "d" },
new CompositeAggregatorFactory(
new CountAggregatorFactory(),
new SumAggregatorFactory("d")
),
false);
pvtData.ProcessData(DataUtils.getSampleData(10000), DataUtils.getProp);
var q = new SliceQuery(pvtData).Dimension("a").Where("a", "val1", "val2").Measure(0);
var pvtDataRes1 = q.Execute(false);
Assert.True(pvtDataRes1.AggregatorFactory is CountAggregatorFactory);
Assert.Equal(1, pvtDataRes1.Dimensions.Length);
Assert.Equal(2, pvtDataRes1.GetDimensionKeys()[0].Length);
Assert.Equal(((object[])pvtData["val1", Key.Empty, Key.Empty].Value)[0], pvtDataRes1["val1"].Value);
var grandTotalCount = Convert.ToInt32(((object[])pvtData[Key.Empty, Key.Empty, Key.Empty].Value)[0]);
var q2 = new SliceQuery(pvtData)
.Dimension("d")
.Measure(
new SumAggregatorFactory("i"), // since this is derived measure field name actually may not match source data fields
(sourceAggr) => {
var cntAggr = sourceAggr.AsComposite().Aggregators[0];
return(new SumAggregator("i", new object[] { cntAggr.Count, Convert.ToDecimal(cntAggr.Value) / grandTotalCount * 100 }));
}
);
var pvtDataRes2 = q2.Execute(true);
Assert.Equal(100M, pvtDataRes2[ValueKey.Empty1D].Value);
Assert.Equal(1M, pvtDataRes2[0].Value);
}
public void PivotTable_ES()
{
var pvtData = new PivotData(new[] { "District", "SubCounty", "wrd_name", "Sex", "Quarter", "HES" }, new CountAggregatorFactory());
pvtData.ProcessData(new DataTableReader(ReturnEconomic_strengthening()));
var pvtTbl = new PivotTable(
new[] { "Quarter", "District", "SubCounty" }, //rows
new[] { "HES" }, //columns
pvtData);
var outputWr = new StringWriter();
var pvtHtmlWr = new PivotTableHtmlWriter(outputWr);
pvtHtmlWr.SubtotalRows = true;
pvtHtmlWr.AllowHtml = true;
pvtHtmlWr.TotalsRowHeaderText = "Grand Total";
pvtHtmlWr.TotalsColumnHeaderText = "Sub County Totals";
pvtHtmlWr.TableClass = "table border = '1' table - bordered table-hover";
pvtHtmlWr.Write(pvtTbl);
var pvtTblHtml = outputWr.ToString();
LitPivot.Text = pvtTblHtml;
}
public void PivotData_Merge()
{
var pvtData = new PivotData(new string[] { "a", "year", "month" },
new AverageAggregatorFactory("i"));
var pvtData1 = new PivotData(new string[] { "a", "year", "month" },
new AverageAggregatorFactory("i"));
var pvtData2 = new PivotData(new string[] { "a", "year", "month" },
new AverageAggregatorFactory("i"));
var pvtData3 = new PivotData(new string[] { "a", "b" }, new CountAggregatorFactory());
Assert.Throws <ArgumentException>(() => {
pvtData1.Merge(pvtData3);
});
pvtData.ProcessData(SampleGenerator(20000), GetRecordValue);
pvtData1.ProcessData(SampleGenerator(10000), GetRecordValue);
pvtData2.ProcessData(SampleGenerator(10000, 10000), GetRecordValue);
pvtData1.Merge(pvtData2);
foreach (var v in pvtData.AllValues)
{
var aggr = pvtData[v.Key];
var aggrMerged = pvtData1[v.Key];
Assert.Equal(aggr.Count, aggrMerged.Count);
Assert.Equal(aggr.Value, aggrMerged.Value);
}
Assert.Equal(pvtData.GetDimensionKeys()[0].Length, pvtData1.GetDimensionKeys()[0].Length);
Assert.Equal(pvtData.GetDimensionKeys()[1].Length, pvtData1.GetDimensionKeys()[1].Length);
Assert.Equal(pvtData.GetDimensionKeys()[2].Length, pvtData1.GetDimensionKeys()[2].Length);
}
public void PivotData_LazyTotals()
{
var nameToIdx = new Dictionary <string, int>()
{
{ "A", 0 },
{ "B", 1 },
{ "C", 2 },
{ "D", 3 }
};
var data = new int[][] {
new int[] { 1, 2, 3, 4 },
new int[] { 1, 2, 4, 8 },
new int[] { 2, 2, 4, 4 },
new int[] { 3, 3, 4, 16 }
};
var pvt = new PivotData(
new string[] { "A", "B", "C", "D" }, new CountAggregatorFactory(), true);
pvt.ProcessData(data, (r, f) => {
return(((int[])r)[nameToIdx[f]]);
});
Assert.Equal(3, Convert.ToInt32(pvt[Key.Empty, 2, Key.Empty, Key.Empty].Value));
Assert.Equal(2, Convert.ToInt32(pvt[Key.Empty, 2, 4, Key.Empty].Value));
Assert.Equal(1, Convert.ToInt32(pvt[1, Key.Empty, 4, Key.Empty].Value));
}
static void Main(string[] args)
{
// sample dataset
var ordersTable = GetOrdersTable();
// build data cube by DataTable
var ordersPvtData = new PivotData(new[] { "product", "year" },
new CompositeAggregatorFactory(
new SumAggregatorFactory("quantity"),
new SumAggregatorFactory("total"),
new AverageAggregatorFactory("total")
));
ordersPvtData.ProcessData(new DataTableReader(ordersTable));
// lets calculate simple expression-based formula measure
var dynFormula = new DynamicFormulaMeasure("sumoftotal / sumofquantity", ordersPvtData);
var resPvtData = new SliceQuery(ordersPvtData)
.Measure("Weighted Total", dynFormula.GetFormulaValue, dynFormula.GetParentMeasureIndexes())
.Execute();
// now resPvtData has only one measure calculated by the formula
foreach (var k in resPvtData.GetDimensionKeys(new[] { "year" })[0])
{
Console.WriteLine("Weighted total for [{0}]: {1:0.##}", k, resPvtData[Key.Empty, k].Value);
}
Console.WriteLine("Weighted Grand Total: {0:0.##}", resPvtData[Key.Empty, Key.Empty].Value);
Console.WriteLine("\nPress any key to continue...");
Console.ReadKey();
}
static void GroupByMultipleWithCustomAccessor(IList <Customer> list, string[] groupByColumns)
{
Console.WriteLine("Aggregating by {0} with custom fields accessor...", String.Join(", ", groupByColumns));
var sw = new Stopwatch();
// configure pivot data
var pvtData = new PivotData(groupByColumns, new CountAggregatorFactory());
// process the list
sw.Start();
pvtData.ProcessData(list, (o, colName) => {
// returned values are totally controlled by accessor delegates
// this is fastest way but it is applicable only for typed collections
var customer = (Customer)o;
switch (colName)
{
case "Category": return(customer.Category);
case "Status": return(customer.Status);
}
return(null);
});
sw.Stop();
Console.WriteLine("Results: unique groups = {0}, processing time = {1} sec", pvtData.Count, sw.Elapsed.TotalSeconds);
}
protected Literal PlotStackedCol_TOTAL_byBudgetNbr(DataTable dt)
{
List <string> rows = new List <string> {
"ObjDesc", "caldate"
};
//List<string> cols = new List<string> { "budgetNbr", "budgetName", "BegDate", "EndDate", "PastProj" };
List <string> cols = new List <string> {
"const"
};
List <string> stats = new List <string> {
"Sum"
};
List <string> keepCols = new List <string>();
keepCols.AddRange(rows);
keepCols.AddRange(cols);
SumAggregatorFactory sumAggr = new SumAggregatorFactory("amt");
var cube = new PivotData(keepCols.ToArray(), sumAggr);
cube.ProcessData(new DataTableReader(dt));
PivotTable pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube);
PivotTableDataTableWriter foo = new PivotTableDataTableWriter("foo");
DataTable dt_totals = foo.Write(pivotTable);
string[] series_vals = new string[1] {
"ObjDesc"
};
PivotTable pivotTable_series = new PivotTable(series_vals, null, cube);
PivotTableDataTableWriter foo2 = new PivotTableDataTableWriter("foo2");
DataTable dt_series = foo2.Write(pivotTable_series);
//DataView dv =
//DataTable dt_series2 = sql.DataTable_from_SQLstring("select Name from fin.vwFTE_Previous_and_Projections where budgetNbr = '" + budgetNbr + "' group by Name");
//DataTable dtraw2 = sql.DataTable_from_SQLstring("select * from fin.vwFTE_Previous_and_Projections where budgetNbr = '" + budgetNbr + "' and calcFTE>0 order by Name");
//string x1 = utilCharts.Highcharts_StackedColumn()
Literal lit2 = new Literal();
string x2 = utilCharts.Highcharts_StackedColumn(dt_series, dt_totals, "caldate", "1[trial]", "ObjDesc", "ObjDesc", "chart1", "charttitle",
"Month", "Totals", -999, -999, -999, -999, 700, 400);
lit2.Text += x2;
////panel2.Controls.Add(lit2);
//sql.Close();
return(lit2);
}
public void PivotData_1D()
{
var pvtData = new PivotData(new string[] { "a" }, new SumAggregatorFactory("z"), true);
Func <object, string, object> getVal = (o, f) => f == "a" ? ((int)o % 2) == 0 ? "A" : "B" : o;
pvtData.ProcessData(new[] { 1 }, getVal);
pvtData.ProcessData(new[] { 2, 3 }, getVal);
Assert.Equal(6M, pvtData[ValueKey.Empty1D].Value);
Assert.Equal(4M, pvtData["B"].Value);
// test lazy-totals = false for 1D
var pvtData2 = new PivotData(new string[] { "a" }, new SumAggregatorFactory("z"), false);
pvtData2.ProcessData(new[] { 1, 2 }, getVal);
pvtData2.ProcessData(new[] { 3 }, getVal);
Assert.Equal(6M, pvtData2[ValueKey.Empty1D].Value);
Assert.Equal(4M, pvtData2["B"].Value);
}
public void PivotData_2D()
{
var testData = generateData();
var pvtData1 = new PivotData(new string[] { "name", "qty" }, new CountAggregatorFactory(), testData);
Assert.Equal(2, pvtData1.GetDimensionKeys()[0].Length);
Assert.Equal(10, pvtData1.GetDimensionKeys()[1].Length);
foreach (var cKey in pvtData1.GetDimensionKeys()[0])
{
foreach (var rKey in pvtData1.GetDimensionKeys()[1])
{
var v = pvtData1[cKey, rKey];
if (v.Count > 0)
{
Assert.Equal(100, Convert.ToInt32(v.Value));
}
}
}
var pvtData = new PivotData(new string[] { "name", "date" }, new SumAggregatorFactory("i"), testData);
Assert.Equal(2, pvtData.GetDimensionKeys()[0].Length);
Assert.Equal(42, pvtData.GetDimensionKeys()[1].Length);
var rowTest0Totals = new ValueKey("Test0", Key.Empty);
Assert.Equal(1000M, pvtData[rowTest0Totals].Value);
// calc test
var calcData = new object[5][] {
new object[] { "A", 10, 50 },
new object[] { "A", 15, 40 },
new object[] { "B", 20, 50 },
new object[] { "B", 25, 60 },
new object[] { "C", 10, 0 }
};
Func <object, string, object> getVal = (r, f) => {
return(((object[])r)[Convert.ToInt32(f)]);
};
var countPvtData = new PivotData(new string[] { "0", "1" }, new CountAggregatorFactory());
countPvtData.ProcessData(calcData, getVal);
Assert.Equal(2, Convert.ToInt32(countPvtData["A", Key.Empty].Value));
Assert.Equal(1, Convert.ToInt32(countPvtData["C", Key.Empty].Value));
var avgPvtData = new PivotData(new string[] { "0", "1" }, new AverageAggregatorFactory("2"));
avgPvtData.ProcessData(calcData, getVal);
Assert.Equal(45M, avgPvtData["A", Key.Empty].Value);
Assert.Equal(0M, avgPvtData["C", Key.Empty].Value);
Assert.Equal(25M, avgPvtData[Key.Empty, 10].Value);
}
public void PivotTable_LargeWithTotals()
{
// test for UlongCache impl
var pvtData = new PivotData(Repeat(63, i => "a" + i.ToString()), new CountAggregatorFactory());
pvtData.ProcessData(new int[] { 0, 1, 2, 3, 4, }, (row, field) => {
var fldIdx = Int32.Parse(field.Substring(1));
var rowIdx = (int)row;
if (fldIdx < rowIdx)
{
return(0);
}
return(rowIdx);
});
Check(new PivotTable(pvtData.Dimensions, null, pvtData));
Check(new PivotTable(Repeat(62, i => "a" + i.ToString()), new string[] { "a62" }, pvtData));
// test for BitArrayCache impl
var pvtData2 = new PivotData(Repeat(128, i => "a" + i.ToString()), new CountAggregatorFactory());
pvtData2.ProcessData(new int[] { 0, 1, 2, 3, 4, }, (row, field) => {
var fldIdx = Int32.Parse(field.Substring(1));
var rowIdx = (int)row;
if (fldIdx < rowIdx)
{
return(0);
}
return(rowIdx);
});
Check(new PivotTable(pvtData2.Dimensions, null, pvtData2));
Check(new PivotTable(Repeat(126, i => "a" + i.ToString()), new string[] { "a126", "a127" }, pvtData2));
void Check(IPivotTable pvtTbl)
{
Assert.Equal(3,
Convert.ToInt32(pvtTbl.GetValue(
new ValueKey(Repeat <object>(pvtTbl.Rows.Length, i => Key.Empty, new object[] { 0, 0, 0 })),
new ValueKey(Repeat <object>(pvtTbl.Columns.Length, i => Key.Empty))
).Value));
Assert.Equal(4,
Convert.ToInt32(pvtTbl.GetValue(
new ValueKey(Repeat <object>(pvtTbl.Rows.Length, i => Key.Empty, new object[] { 0, 0 })),
new ValueKey(Repeat <object>(pvtTbl.Columns.Length, i => Key.Empty))
).Value));
Assert.Equal(5,
Convert.ToInt32(pvtTbl.GetValue(
new ValueKey(Repeat <object>(pvtTbl.Rows.Length, i => Key.Empty)),
new ValueKey(Repeat <object>(pvtTbl.Columns.Length, i => Key.Empty))
).Value));
}
T[] Repeat <T>(int n, Func <int, T> getVal, params T[] explicitVals)
public void PivotDataState_SerializationPerf()
{
var pvtData = new PivotData(
new string[] { "year", "month", "a", "i" },
new CountAggregatorFactory(), true);
pvtData.ProcessData(
PivotDataTests.SampleGenerator(1000000), // 1 mln of unique aggregates
PivotDataTests.GetRecordValue);
/*var js = new JavaScriptSerializer();
* js.MaxJsonLength = Int32.MaxValue;
* CheckSerializerPerf(pvtData, "JSON",
* (state) => {
* var jsonStr = js.Serialize(state);
* Console.WriteLine("JSON length: {0}", jsonStr.Length);
* return jsonStr;
* },
* (state) => {
* return js.Deserialize<PivotDataState>( (string)state);
* });*/
var binFmt = new BinaryFormatter();
/*CheckSerializerPerf(pvtData, "BinaryFormatter",
* (state) => {
* var memStream = new MemoryStream();
* binFmt.Serialize(memStream, state);
* Console.WriteLine("Serialized bytes: {0}",memStream.Length);
* memStream.Position = 0;
* return memStream;
* },
* (state) => {
* return (PivotDataState)binFmt.Deserialize( (Stream)state );
* });*/
CheckSerializerPerf(pvtData, "PivotDataState",
(state) => {
var memStream = new MemoryStream();
state.Serialize(memStream);
var bytes = memStream.ToArray();
Console.WriteLine("Serialized bytes: {0}", bytes.Length);
return(bytes);
},
(state) => {
return(PivotDataState.Deserialize(new MemoryStream((byte[])state)));
});
}
public void FormulaTest()
{
// simple test for 1-measure
var pvtData1 = new PivotData(new[] { "a", "d" },
new SumAggregatorFactory("b"),
false);
pvtData1.ProcessData(DataUtils.getSampleData(1000), DataUtils.getProp);
var q1 = new SliceQuery(pvtData1).Dimension("a").Measure("a*2", (paramAggrs) => {
return(Convert.ToDecimal(paramAggrs[0].Value) * 2);
}, new[] { 0 });
var pvt1Res = q1.Execute();
Assert.Equal(
((decimal)pvtData1["val1", Key.Empty].Value) * 2,
pvt1Res["val1"].Value);
// test for composite aggregator
var pvtData = new PivotData(new[] { "a", "d" },
new CompositeAggregatorFactory(
new CountAggregatorFactory(),
new SumAggregatorFactory("b")
),
false);
pvtData.ProcessData(DataUtils.getSampleData(1000), DataUtils.getProp);
var q = new SliceQuery(pvtData);
q.Dimension("a");
q.Measure(1);
q.Measure("a*2", (paramAggrs) => {
return(Convert.ToDecimal(paramAggrs[0].Value) * 2);
}, new[] { 1 });
var pvtRes = q.Execute();
Assert.Equal(
((decimal)pvtData["val1", Key.Empty].AsComposite().Aggregators[1].Value) * 2,
pvtRes["val1"].AsComposite().Aggregators[1].Value);
// check that calculated measures are merged correctly
pvtRes.ProcessData(DataUtils.getSampleData(10), DataUtils.getProp);
Assert.Equal(
((decimal)pvtRes["val1"].AsComposite().Aggregators[0].Value) * 2,
pvtRes["val1"].AsComposite().Aggregators[1].Value);
}
static void GroupByMultipleWithObjectMember(IList <Customer> list, string[] groupByColumns)
{
Console.WriteLine("Aggregating by {0} with help of ObjectMember...", String.Join(", ", groupByColumns));
var sw = new Stopwatch();
// configure pivot data
var pvtData = new PivotData(groupByColumns, new CountAggregatorFactory());
// process the list
sw.Start();
pvtData.ProcessData(list, new ObjectMember().GetValue);
sw.Stop();
Console.WriteLine("Results: unique groups = {0}, processing time = {1} sec", pvtData.Count, sw.Elapsed.TotalSeconds);
}
public void WhereTest()
{
var pvtData = new PivotData(new[] { "a", "d" }, new CountAggregatorFactory());
pvtData.ProcessData(DataUtils.getSampleData(10000), DataUtils.getProp);
// empty filter = nothing is changed
Assert.Equal(pvtData.Count, new SliceQuery(pvtData).Where("a").Execute().Count);
// 1/3 of 10k
Assert.Equal(3334, Convert.ToInt32(
new SliceQuery(pvtData).Where("a", "val1").Dimension("a").Execute()[Key.Empty].Value));
// 2/3 of 10k
Assert.Equal(6667, Convert.ToInt32(
new SliceQuery(pvtData).Where("a", "val1", "val2").Dimension("a").Execute()[Key.Empty].Value));
}
public void PivotTable_SortPerformance()
{
var pvtData = new PivotData(new[] { "a", "b" },
new CompositeAggregatorFactory(
new CountAggregatorFactory(),
new SumAggregatorFactory("d")
),
true);
pvtData.ProcessData(DataUtils.getSampleData(50000), DataUtils.getProp);
var pvtTbl = new PivotTable(new[] { "b" }, new [] { "a" }, pvtData);
pvtTbl.SortRowKeys(0, ListSortDirection.Descending);
pvtTbl.SortRowKeys(null, ListSortDirection.Descending);
}
public PivotData GetDataCube()
{
if (cachedCube != null)
{
return(cachedCube);
}
// SQLite database file
var sqliteDbFile = Path.Combine(HttpContext.Server.MapPath("~/"), "../db/northwind.db");
var sqliteConn = new SQLiteConnection("Data Source=" + sqliteDbFile);
var selectCmd = sqliteConn.CreateCommand();
selectCmd.CommandText = @"
select o.OrderID, c.CompanyName, c.ContactName, o.OrderDate, p.ProductName, od.UnitPrice, od.Quantity, c.Country
from [Order Details] od
LEFT JOIN [Orders] o ON (od.OrderId=o.OrderId)
LEFT JOIN [Products] p ON (od.ProductId=p.ProductId)
LEFT JOIN [Customers] c ON (c.CustomerID=o.CustomerID)
" ;
// this example uses data reader as input and aggregates data with .NET
// it is possible to aggregate data on database level with GROUP BY
// (see "ToolkitDbSource" example from PivotData Toolkit Trial package ( https://www.nrecosite.com/pivot_data_library_net.aspx )
var dbCmdSource = new DbCommandSource(selectCmd);
// lets define derived fields for 'OrderDate' to get separate 'Year' and 'Month'
var derivedValSource = new DerivedValueSource(dbCmdSource);
derivedValSource.Register("Order Year", new DatePartValue("OrderDate").YearHandler);
derivedValSource.Register("Order Month", new DatePartValue("OrderDate").MonthNumberHandler);
// configuration of the serialized cube
var pvtData = new PivotData(new[] { "Country", "Order Year", "Order Month" },
// lets define 2 measures: count and sum of UnitPrice
new CompositeAggregatorFactory(
new CountAggregatorFactory(),
new SumAggregatorFactory("UnitPrice")
));
pvtData.ProcessData(derivedValSource);
cachedCube = pvtData;
return(pvtData);
}
static void Main(string[] args)
{
var fieldToIdx = new Dictionary <string, int>()
{
{ "name", 0 },
{ "age", 1 },
{ "company", 2 },
{ "date", 3 },
{ "hours", 4 },
};
Func <object, string, object> getValue = (r, f) => {
return(((object[])r)[fieldToIdx[f]]);
};
var dataChunks = GetDataChunks();
Console.WriteLine("Generating 50 data chunks (each 100,000 records) on the fly = 5 mln in total");
var dimensions = new string[] {
"company", "name", "age", "date"
};
var aggrFactory = new SumAggregatorFactory("hours");
var allPvtData = new PivotData(dimensions, aggrFactory, true);
Parallel.ForEach(dataChunks, (t) => {
var chunkPvtData = new PivotData(dimensions, aggrFactory, true);
chunkPvtData.ProcessData(t, getValue);
Console.WriteLine("Calculated pivot data chunk, aggregated values: {0}", chunkPvtData.Count);
lock (allPvtData) {
allPvtData.Merge(chunkPvtData);
}
});
Console.WriteLine("Parallel calculation of cube finished.\nTotal dimensions: {0}\nTotal aggregated values: {1}\nTotal hours: {2}",
dimensions.Length, allPvtData.Count,
allPvtData[Key.Empty, Key.Empty, Key.Empty, Key.Empty].Value);
Console.ReadKey();
}
public void Export_OVCSERV()
{
var pvtData = new PivotData(new[] { "District", "SubCounty", "wrd_name", "Sex", "Age", "Quarter" }, new CountAggregatorFactory());
pvtData.ProcessData(new DataTableReader(ReturnMembersServed()));
var pvtTbl = new PivotTable(
new[] { "Quarter", "District", "SubCounty" }, //rows
new[] { "Age", "Sex" }, //columns
pvtData);
var outputWr = new StringWriter();
var pvtHtmlWr = new PivotTableHtmlWriter(outputWr);
pvtHtmlWr.SubtotalRows = true;
pvtHtmlWr.AllowHtml = true;
pvtHtmlWr.TotalsRowHeaderText = "Grand Total";
pvtHtmlWr.TableClass = "table border = '1' table - bordered table-hover";
var pvtCsvWr = new PivotTableCsvWriter(outputWr);
pvtCsvWr.Write(pvtTbl);
}
public void PivotData_ByAnotherPivotData()
{
var testData = generateData();
var sourcePvtData = new PivotData(new string[] { "name", "qty", "i" },
new CompositeAggregatorFactory(
new AverageAggregatorFactory("total"),
new CountAggregatorFactory()
),
testData);
var pvtData = new PivotData(new[] { "name", "qty" }, new AverageAggregatorFactory("value"));
pvtData.ProcessData(sourcePvtData, "value");
Assert.Equal(225M, pvtData[Key.Empty, Key.Empty].Value);
Assert.Equal(50M, pvtData["Test1", 1].Value);
// lets check also single-aggregator pivotdata source
var pvtData2 = new PivotData(new[] { "name" }, new AverageAggregatorFactory("value"));
pvtData2.ProcessData(pvtData, "value");
Assert.Equal(225M, pvtData2[Key.Empty].Value);
}
static void Main(string[] args)
{
// sample dataset
var ordersTable = GetOrdersTable();
// build data cube by large DataTable (you may use database data reader directly)
var ordersPvtData = new PivotData(new[] { "product", "country", "year", "month", "day" },
new CompositeAggregatorFactory(
new SumAggregatorFactory("quantity"),
new SumAggregatorFactory("total"),
new AverageAggregatorFactory("total")
));
ordersPvtData.ProcessData(new DataTableReader(ordersTable));
var pkg = new ExcelPackage();
pkg.Compression = CompressionLevel.Default;
var wsPvt = pkg.Workbook.Worksheets.Add("Pivot Table");
var wsData = pkg.Workbook.Worksheets.Add("Source Data");
var pvtTbl = new PivotTable(
new[] { "country" }, //rows
new[] { "year" }, // columns
ordersPvtData
);
var excelPvtTblWr = new ExcelPivotTableWriter(wsPvt, wsData);
excelPvtTblWr.Write(pvtTbl);
//pkg.Workbook.Worksheets.Delete(wsData);
using (var excelFs = new FileStream("result.xlsx", FileMode.Create, FileAccess.Write)) {
pkg.SaveAs(excelFs);
}
Console.WriteLine("Generated Excel file with PivotTable: result.xlsx");
}
static void Main(string[] args)
{
// sample dataset
var ordersTable = GetOrdersTable();
// build data cube by DataTable
var ordersPvtData = new PivotData(new[] { "product", "country", "year", "month", "day" },
new CompositeAggregatorFactory(
new SumAggregatorFactory("quantity"),
new SumAggregatorFactory("total"),
new AverageAggregatorFactory("total")
));
ordersPvtData.ProcessData(new DataTableReader(ordersTable));
// query 1: select products from USA and Canada greater than $50
var northAmericaBigOrdersQuery = new SliceQuery(ordersPvtData)
.Dimension("product")
.Dimension("country")
.Where("country", "USA", "Canada")
.Where((dp) => {
// filter by measure value (index #1 => sum of total)
return(ConvertHelper.ConvertToDecimal(dp.Value.AsComposite().Aggregators[1].Value, 0M) > 50);
})
.Measure(1); // include only "sum of total" measure
var northAmericaPvtData = northAmericaBigOrdersQuery.Execute();
// resulting data cube:
// dimensions = {"product", "country"}, country dimension keys = {"USA", "Canada"}
// aggregator = sum of total
Console.WriteLine("North america big orders grand total: ${0:0.00}", northAmericaPvtData[Key.Empty, Key.Empty].Value);
Console.WriteLine("\tUSA grand total: ${0:0.00}", northAmericaPvtData[Key.Empty, "USA"].Value);
Console.WriteLine("\tCanada grand total: ${0:0.00}", northAmericaPvtData[Key.Empty, "Canada"].Value);
Console.WriteLine();
// query 2: calculated (formula) measure
// average item price = sum of total / sum of quantity
var avgItemPriceByYearQuery = new SliceQuery(ordersPvtData)
.Dimension("year")
.Measure("Avg item price",
(aggrArgs) => {
var sumOfTotal = ConvertHelper.ConvertToDecimal(aggrArgs[0].Value, 0M); // value of first argument (from measure #1)
var sumOfQuantity = ConvertHelper.ConvertToDecimal(aggrArgs[1].Value, 0M); // value of second argument (from measure #0)
if (sumOfQuantity == 0)
{
return(0M); // prevent div by zero
}
return(sumOfTotal / sumOfQuantity);
},
new int[] { 1, 0 } // indexes of measures for formula arguments
);
var avgItemPriceByYearPvtData = avgItemPriceByYearQuery.Execute();
Console.WriteLine("Average item price by years:");
foreach (var year in avgItemPriceByYearPvtData.GetDimensionKeys()[0])
{
Console.WriteLine("\t {0}: ${1:0.00}", year, avgItemPriceByYearPvtData[year].Value);
}
Console.WriteLine("\tTotal: ${0:0.00}", avgItemPriceByYearPvtData[Key.Empty].Value);
// query 3: calculated dimension
// lets introduce 'region' (calculated by 'country') with 2 possible values: North America, Europe
var regionQuery = new SliceQuery(ordersPvtData)
.Dimension("year")
.Dimension("region",
(dimKeys) => {
var country = dimKeys[1]; // depends on ordersPvtData configuration: index #1 is 'country'
if (country.Equals("USA") || country.Equals("Canada"))
{
return("North America");
}
return("Europe");
}
); // note that if measure selectors are not defined, measures remains unchanged
var regionPvtData = regionQuery.Execute();
var regionPvtTbl = new PivotTable(
new[] { "year" }, // row dimension
new[] { "region" }, // column dimension
regionPvtData);
Console.WriteLine("\nTotals by region:");
Console.Write("\t\t\t");
foreach (var colKey in regionPvtTbl.ColumnKeys)
{
Console.Write("{0}\t", colKey);
}
Console.WriteLine();
for (int r = 0; r < regionPvtTbl.RowKeys.Length; r++)
{
Console.Write("\t{0}:", regionPvtTbl.RowKeys[r]);
for (int c = 0; c < regionPvtTbl.ColumnKeys.Length; c++)
{
Console.Write("\t${0:######.00}", regionPvtTbl[r, c].AsComposite().Aggregators[1].Value);
}
Console.WriteLine();
}
Console.WriteLine("\nPress any key to continue...");
Console.ReadKey();
}
private void ExportReporttoPDF()
{
if (DateTime.TryParse(txtFrom.Text, out temp))
{
}
else
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "from date", "alert('Please select valid from date')", true);
return;
}
if (DateTime.TryParse(txtTo.Text, out temp))
{
}
else
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "to date", "alert('Please select valid to date')", true);
return;
}
if (Convert.ToDateTime(txtFrom.Text) > Convert.ToDateTime(txtTo.Text))
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "to date", "alert('To date should be greated than from date')", true);
return;
}
int weeks = Convert.ToInt32((Convert.ToDateTime(txtTo.Text) - Convert.ToDateTime(txtFrom.Text)).TotalDays / 7);
string from = Convert.ToDateTime(txtFrom.Text).ToString("MM/dd/yyyy");
for (int i = 0; i <= weeks - 1; i++)
{
string to = Convert.ToDateTime(from).AddDays(6).ToString("MM/dd/yyyy");
if (Convert.ToDateTime(to) > Convert.ToDateTime(txtTo.Text))
{
break;
}
lstDate.Add(from + "-" + to);
from = Convert.ToDateTime(to).AddDays(1).ToString("MM/dd/yyyy");
}
DataRow dr;
BuildTeamTable();
//double count = 0;
double average = 0;
//dtTeam = objADM.LoadTeams(ddlLocation.SelectedItem.Text);
//if (dtTeam.Rows.Count > 0)
//{
// foreach (DataRow drTeam in dtTeam.Rows)
// {
// dt = objADM.GetUserCount(drTeam["T_ID"].ToString());
// if (dt.Rows.Count > 0)
// {
// if (!string.IsNullOrEmpty(dt.Rows[0]["Count"].ToString()))
// {
// count = Convert.ToDouble(dt.Rows[0]["Count"]);
// }
// }
// foreach (var item in lstDate.Chunks)
// {
// dr = dtResult.NewRow();
// dr["Team"] = drTeam["T_TeamName"];
// dr["Date"] = item.ToString().Split('-')[0];
// dt = objADM.GetTotalWorkHours("team", item.ToString().Split('-')[0], item.ToString().Split('-')[1], drTeam["T_ID"].ToString());
// if (dt.Rows.Count > 0)
// {
// if (dt.Rows[0]["Total"].ToString() == "0")
// {
// continue;
// }
// if (count > 0)
// {
// average = (Convert.ToDouble(dt.Rows[0]["Total"])) / count;
// }
// }
// dr["Avg"] = Math.Round(average, 2, MidpointRounding.AwayFromZero);
// if (average > 0)
// {
// dtResult.Rows.Add(dr);
// }
// average = 0;
// }
// count = 0;
// }
//}
foreach (var item in lstDate.Chunks)
{
dt = objADM.GetTeamTotal(item.ToString().Split('-')[0], item.ToString().Split('-')[1], ddlLocation.SelectedItem.Text);
if (dt.Rows.Count > 0)
{
foreach (DataRow drRow in dt.Rows)
{
dr = dtResult.NewRow();
if (string.IsNullOrEmpty(drRow["Team1"].ToString()))
{
dr["Team"] = drRow["Team2"];
}
else
{
dr["Team"] = drRow["Team1"];
}
dr["Date"] = item.ToString().Split('-')[0];
dtUsers = objADM.GetUserCount(dr["Team"].ToString());
if (dtUsers.Rows.Count > 0)
{
average = Convert.ToDouble(drRow["Total"]) / Convert.ToDouble(dtUsers.Rows[0]["Count"].ToString());
dr["Avg"] = Math.Round(average, 2, MidpointRounding.AwayFromZero);
}
average = 0;
dtResult.Rows.Add(dr);
}
}
}
//if (dtResult.Rows.Count > 0)
//{
// DataView dv = dtResult.DefaultView;
// dv.Sort = "[Date] asc, [Team] asc";
// dtResult = dv.ToTable();
//}
var ordersPvtData = new PivotData(new[] { "Team", "Date" },
new SumAggregatorFactory("Avg"));
ordersPvtData.ProcessData(new DataTableReader(dtResult));
var pvtTbl = new PivotTable(
new[] { "Team" }, //rows
new[] { "Date" }, // columns
ordersPvtData,
new CustomSortKeyComparer(new[] { NaturalSortKeyComparer.Instance }),
new CustomSortKeyComparer(new[] { NaturalSortKeyComparer.Instance })
);
var pvtDataTableWr = new PivotTableDataTableWriter("PivotTable");
DataTable res = pvtDataTableWr.Write(pvtTbl);
res.Columns[0].ColumnName = "Team";
ExportToPDF(res);
}
private void ExportReport()
{
DateTime start = Convert.ToDateTime(txtFrom.Text);
var startDate = new DateTime(start.Year, start.Month, 1);
DateTime end = Convert.ToDateTime(txtTo.Text);
var endDate = end.AddMonths(1).AddDays(-1);
//string customerIds = Request.Form[hfCustomerId.UniqueID];
string customerIds = txtSearch.Text;
customerIds = customerIds.Trim();
customerIds = customerIds.Remove(customerIds.Length - 1);
//dt = objAdm.ContentMgmntReport("400541,400479", startDate.ToShortDateString(), endDate.ToShortDateString());
dt = objAdm.ContentMgmntReport(customerIds, startDate.ToShortDateString(), endDate.ToShortDateString());
hfCustomerId.Value = string.Empty;
txtSearch.Text = string.Empty;
if (dt.Rows.Count > 0)
{
var pkg = new ExcelPackage();
pkg.Compression = CompressionLevel.Default;
var wsPvt = pkg.Workbook.Worksheets.Add("Pivot Table");
var wsData = pkg.Workbook.Worksheets.Add("Source Data");
var PvtData = new PivotData(new[] { "Service Code", "User Name", "Month", "Reporting Manager" },
new SumAggregatorFactory("Total Hours"));
PvtData.ProcessData(new DataTableReader(dt));
var pvtTbl = new PivotTable(
new[] { "Service Code", "User Name" }, //rows
new[] { "Month" }, // columns
PvtData,
new CustomSortKeyComparer(new[] { NaturalSortKeyComparer.Instance }),
new CustomSortKeyComparer(new[] { NaturalSortKeyComparer.Instance })
);
//var pvtDataTableWr = new PivotTableDataTableWriter("PivotTable");
var excelPvtTblWr = new ExcelPivotTableWriter(wsPvt, wsData);
excelPvtTblWr.Write(pvtTbl);
string path = @"~/Files/result.xlsx";
if (File.Exists(path))
{
File.Delete(path);
}
using (var excelFs = new FileStream(Server.MapPath(path), FileMode.Create, FileAccess.Write))
{
pkg.SaveAs(excelFs);
}
WebClient req = new WebClient();
HttpResponse response = HttpContext.Current.Response;
string filePath = path;
response.Clear();
response.ClearContent();
response.ClearHeaders();
response.Buffer = true;
response.AddHeader("Content-Disposition", "attachment;filename=result.xlsx");
byte[] data = req.DownloadData(Server.MapPath(filePath));
response.BinaryWrite(data);
response.End();
}
else
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "No Data", "alert('No data found')", true);
}
}
static void Main(string[] args)
{
Console.Write("CsvDemo: illustrates how to use CSV file as input for PivotData and use several aggregators at once\nInput data: TechCrunch funds raised facts\n\n");
var csvConfig = new CsvConfiguration()
{
Delimiter = ",",
HasHeaderRecord = true
};
var file = "TechCrunchcontinentalUSA.csv";
using (var fileReader = new StreamReader(file)) {
var csvReader = new CsvReader(fileReader, csvConfig);
var fldToIdx = new Dictionary <string, int>();
// accessor for field values
Func <object, string, object> getValue = (r, f) => {
if (f == "fundedDate-year")
{
var foundedDate = ((string[])r)[fldToIdx["fundedDate"]];
DateTime dt;
if (DateTime.TryParse(foundedDate, out dt))
{
return(dt.Year);
}
else
{
return(null);
}
}
var csvColVal = ((string[])r)[fldToIdx[f]];
if (f == "raisedAmt")
{
return(Decimal.Parse(csvColVal));
}
return(csvColVal); // just return csv value
};
var pivotData = new PivotData(new [] { "category", "fundedDate-year", "round" },
new CompositeAggregatorFactory(
new IAggregatorFactory[] {
new SumAggregatorFactory("raisedAmt"),
new AverageAggregatorFactory("raisedAmt"),
new MaxAggregatorFactory("raisedAmt")
}
),
true // lazy totals
);
// calculate in-memory cube
pivotData.ProcessData(readCsvRows(csvReader, fldToIdx), getValue);
// lets show total raised by round
Console.WriteLine("Total raised $$ by round:");
var byRoundPivotData = new SliceQuery(pivotData).Dimension("round").Execute(); // slice by specific dimension
foreach (var round in byRoundPivotData.GetDimensionKeys()[0])
{
Console.WriteLine("Round '{0}': ${1:0.#}M", round,
GetMln(((object[])byRoundPivotData[round].Value)[0] /* 1st aggregator */));
}
Console.WriteLine("\nMax/Avg raised by year:");
var byYearPivotData = new SliceQuery(pivotData).Dimension("fundedDate-year").Execute();
foreach (var year in byYearPivotData.GetDimensionKeys()[0])
{
Console.WriteLine("Year {0}: max=${1:0.#}M avg=${2:0.#}M", year,
GetMln(((object[])byYearPivotData[year].Value)[2] /* 3rd aggregator */),
GetMln(((object[])byYearPivotData[year].Value)[1] /* 2nd aggregator */)
);
}
Console.WriteLine("\n\nPress any key to exit...");
Console.ReadKey();
}
}
//This takes a DataTable and returns a PivotTable on a given field (var_to_process) by a list of Rows and Cols
public static PivotTable DataTable_to_PivotTable(DataTable dtStacked, List <string> pivotRows, List <string> pivotCols, string fldname, List <AggrFx> requested_stats)
{
if (dtStacked.Rows.Count > 0)
{
List <string> keepCols = new List <string>();
keepCols.AddRange(pivotRows);
keepCols.AddRange(pivotCols);
keepCols.Add(fldname);
IAggregatorFactory[] aggs = new IAggregatorFactory[requested_stats.Count];
CountAggregatorFactory aggrN = new CountAggregatorFactory();
AverageAggregatorFactory aggrM = new AverageAggregatorFactory(fldname);
VarianceAggregatorFactory aggrSD = new VarianceAggregatorFactory(fldname, VarianceAggregatorValueType.StandardDeviation);
MinAggregatorFactory aggrMin = new MinAggregatorFactory(fldname);
MaxAggregatorFactory aggrMax = new MaxAggregatorFactory(fldname);
SumAggregatorFactory aggrSum = new SumAggregatorFactory(fldname);
ConcatAggregatorFactory aggrConcat = new ConcatAggregatorFactory(fldname);
CountUniqueAggregatorFactory aggrNunq = new CountUniqueAggregatorFactory(fldname);
Concat2AggregatorFactory aggrConcat2 = new Concat2AggregatorFactory(fldname);
int counter = 0;
foreach (AggrFx s in requested_stats)
{
if (s == AggrFx.M)
{
aggs[counter] = aggrM;
}
else if (s == AggrFx.SD)
{
aggs[counter] = aggrSD;
}
else if (s == AggrFx.N)
{
aggs[counter] = aggrN;
}
else if (s == AggrFx.Nunq)
{
aggs[counter] = aggrNunq;
}
else if (s == AggrFx.Min)
{
aggs[counter] = aggrMin;
}
else if (s == AggrFx.Max)
{
aggs[counter] = aggrMax;
}
else if (s == AggrFx.Sum)
{
aggs[counter] = aggrSum;
}
else if (s == AggrFx.Concat)
{
aggs[counter] = aggrConcat;
}
else if (s == AggrFx.Concat2)
{
aggs[counter] = aggrConcat2;
}
counter++;
}
if (aggs.Length > 1)
{
CompositeAggregatorFactory compositeFactory = new CompositeAggregatorFactory(aggs);
var cube = new PivotData(keepCols.ToArray(), compositeFactory);
cube.ProcessData(new DataTableReader(dtStacked));
var allKeys = cube.GetDimensionKeys(); // returns array of keys for each dimension
var pivotTable = new PivotTable(
pivotRows.ToArray(), // row dimension(s)
pivotCols.ToArray(), // column dimension(s)
cube);
return(pivotTable);
}
else
{
var cube = new PivotData(keepCols.ToArray(), aggs[0]);
cube.ProcessData(new DataTableReader(dtStacked));
var allKeys = cube.GetDimensionKeys(); // returns array of keys for each dimension
var pivotTable = new PivotTable(
pivotRows.ToArray(), // row dimension(s)
pivotCols.ToArray(), // column dimension(s)
cube);
return(pivotTable);
}
}
else
{
return(null);
}
}
// string GetData(IEnumerable<Dictionary<string,object>> inputData) {
// var pvtData = new PivotData(
// new [] {"Tournament","Category", "Gender","Player"}, // list of all dimensions used in pivot table
// new SumAggregatorFactory("Count"),
// true);
// pvtData.ProcessData( inputData ); // use appropriate overload for different data sources
// var pvtTbl = new PivotTable(
// new[] {"Tournament","Category"}, // rows
// new[] {"Gender", "Player"},
// pvtData
// );
//
// var strWr = new StringWriter();
// var htmlPvtTblWr = new PivotTableHtmlWriter(strWr);
// htmlPvtTblWr.Write(pvtTbl);
//
// return strWr.ToString();
// }
public override IDisplayResult Display(CrosstabVisualProfile profile, IUpdateModel updater)
{
var context = new PivotContext();
// var json = new System.Web.Script.Serialization.JavaScriptSerializer();
// var inputData = json.Deserialize<IEnumerable<Dictionary<string,object>>>(inputDataJson);
// var inputData = JsonConvert.DeserializeObject<IEnumerable<Dictionary<string,object>>>(profile.QueryResult.ToString());//json.Deserialize<IEnumerable<Dictionary<string,object>>>(inputDataJson);
//var inputData = JsonConvert.DeserializeObject<IEnumerable<Dictionary<string,object>>>(profile.QueryResult.ToString());//json.Deserialize<IEnumerable<Dictionary<string,object>>>(inputDataJson);
// var pvtData = GetData(inputData);
var converted = JsonConvert.DeserializeObject(profile.QueryResult.ToString());
var inputData = JsonConvert.DeserializeObject <IEnumerable <Dictionary <string, object> > >(converted);
// in this example filter is applied to in-memory data cube
// for large datasets it may be applied on database level (with SQL WHERE expression)
// var filteredPvtData = new SliceQuery(pvtData).Where("Order Year", year).Execute();
var pvtData = new PivotData(
new [] { "Tournament", "Category", "Gender", "Player" }, // list of all dimensions used in pivot table
new SumAggregatorFactory("Count"),
true);
pvtData.ProcessData(inputData); // use appropriate overload for different data sources
var pvtTbl = new PivotTable(
new[] { "Tournament", "Category" }, // rows
new[] { "Gender", "Player" },
pvtData
);
// render pivot table HTML
// var pvtTbl = new PivotTable(
// new[] {"Country"}, // rows
// new[] {"Order Year", "Order Month"} , // cols
// pvtData// filteredPvtData
// );
// sort by row total
// pvtTbl.SortRowKeys(null,
// 1, // lets order by measure #1 (sum of unit price)
// System.ComponentModel.ListSortDirection.Descending);
var strHtmlWr = new StringWriter();
var pvtHtmlWr = new PivotTableHtmlWriter(strHtmlWr);
pvtHtmlWr.TableClass = "table table-bordered table-condensed pvtTable";
pvtHtmlWr.Write(pvtTbl);
context.PivotTableHtml = strHtmlWr.ToString();
// prepare data for pie chart (total sum by country)
// var pvtDataForChart = new SliceQuery(filteredPvtData).Dimension("Country").Measure(1).Execute();
// var chartPvtTbl = new PivotTable(new[]{"Country"},null,pvtDataForChart);
// // sort by row total
// chartPvtTbl.SortRowKeys(null, System.ComponentModel.ListSortDirection.Descending);
// var strJsonWr = new StringWriter();
// var jsonWr = new PivotTableJsonWriter(strJsonWr);
// jsonWr.Write(chartPvtTbl);
// context.PivotTableJson = strJsonWr.ToString();
//var pager = await GetPagerAsync(context.Updater, listPart);
////giannis
//var settings = GetSettings(listPart);
//model.EnablePositioning = settings.EnablePositioning;
//model.AdminListViewName = settings.AdminListViewName;
//model.ListViewProviders = _listViewService.Providers.ToList();
//model.ListViewProvider = GetListViewProvider(model);// _listViewService.GetDefaultProvider();
//model.ItemsPerPage = listPart.ItemsPerPage;
//model.Skip = listPart.Skip;
//model.PagerSuffix = listPart.PagerSuffix;
//model.MaxItems = listPart.MaxItems;
//model.DisplayPager = listPart.DisplayPager;
//========
// model.ProjectionPart = listPart;
// model.ContentItems = (await QueryListItemsAsync(listPart, pager, true)).ToArray();
//model.PageOfContentItems = (await QueryListItemsAsync(listPart, pager, true)).ToArray();
//model.ContainedContentTypeDefinitions = GetContainedContentTypes(listPart);
//model.Context = context;
//model.Pager = await context.New.PagerSlim(pager);
//model.ListViewShape = model.ListViewProvider.BuildDisplay(context, model);// context.AdminListView.BuildDisplay(context);
return(Combine(
Initialize <CrosstabVisualProfileViewModel>("CrosstabVisualProfile", model =>
{
model.PivotContext = context;
model.QueryResult = profile.QueryResult;
model.AutoStart = profile.AutoStart;
model.Controls = profile.Controls;
model.Indicators = profile.Indicators;
model.Interval = profile.Interval;
model.Keyboard = profile.Keyboard;
model.Pause = profile.Pause;
model.Wrap = profile.Wrap;
})
.Location("Detail", "Content:1")//,
//Shape<TableLayoutProfileViewModel>("TableLayoutProfile_Summary", model =>
// {
// model.AutoStart = profile.AutoStart;
// model.Controls = profile.Controls;
// model.Indicators = profile.Indicators;
// model.Interval = profile.Interval;
// model.Keyboard = profile.Keyboard;
// model.Pause = profile.Pause;
// model.Wrap = profile.Wrap;
// })
// .Location("Summary", "Meta:5")
));
//return Combine(
// Shape("TableLayoutProfile_SummaryAdmin", model =>
// {
// model.Profile = profile;
// }).Location("Content:5")
// //Shape("BootStrapProfile_Buttons_SummaryAdmin", model =>
// //{
// // model.Profile = profile;
// //}).Location("Actions:2")
//);
}
// string GetData(IEnumerable<Dictionary<string,object>> inputData) {
// var pvtData = new PivotData(
// new [] {"Tournament","Category", "Gender","Player"}, // list of all dimensions used in pivot table
// new SumAggregatorFactory("Count"),
// true);
// pvtData.ProcessData( inputData ); // use appropriate overload for different data sources
// var pvtTbl = new PivotTable(
// new[] {"Tournament","Category"}, // rows
// new[] {"Gender", "Player"},
// pvtData
// );
//
// var strWr = new StringWriter();
// var htmlPvtTblWr = new PivotTableHtmlWriter(strWr);
// htmlPvtTblWr.Write(pvtTbl);
//
// return strWr.ToString();
// }
public override IDisplayResult Display(ChartVisualProfile profile, IUpdateModel updater)
{
var context = new PivotContext();
// var json = new System.Web.Script.Serialization.JavaScriptSerializer();
// var inputData = json.Deserialize<IEnumerable<Dictionary<string,object>>>(inputDataJson);
//var inputData = JsonConvert.DeserializeObject<IEnumerable<Dictionary<string,object>>>(profile.QueryResult.ToString());//json.Deserialize<IEnumerable<Dictionary<string,object>>>(inputDataJson);
var converted = JsonConvert.DeserializeObject(profile.QueryResult.ToString());
var inputData = JsonConvert.DeserializeObject <IEnumerable <Dictionary <string, object> > >(converted);
// in this example filter is applied to in-memory data cube
// for large datasets it may be applied on database level (with SQL WHERE expression)
// var filteredPvtData = new SliceQuery(pvtData).Where("Order Year", year).Execute();
// var pvtData = new PivotData(
// new [] {"Tournament","Category", "Gender","Player"}, // list of all dimensions used in pivot table
// new SumAggregatorFactory("Count"),
// true);
try
{
// lets define derived fields for 'OrderDate' to get separate 'Year' and 'Month'
// var derivedValSource = new DerivedValueSource(inputData);
// derivedValSource.Register("Order Year", new DatePartValue("OrderDate").YearHandler );
// derivedValSource.Register("Order Month", new DatePartValue("OrderDate").MonthNumberHandler );
// configuration of the serialized cube
var pvtData = new PivotData(new[] { "Country", "Order Year", "Order Month" },
// lets define 2 measures: count and sum of UnitPrice
new CompositeAggregatorFactory(
new CountAggregatorFactory(),
new SumAggregatorFactory("UnitPrice")
));
pvtData.ProcessData(inputData); // use appropriate overload for different data sources
var filteredPvtData = pvtData;// new SliceQuery(pvtData).Where("Order Year",1996 ).Execute();
// prepare data for pie chart (total sum by country)
var pvtDataForChart = new SliceQuery(filteredPvtData).Dimension("Country").Measure(1).Execute();
var chartPvtTbl = new PivotTable(new[] { "Country" }, null, pvtDataForChart);
// sort by row total
chartPvtTbl.SortRowKeys(null, System.ComponentModel.ListSortDirection.Descending);
var strJsonWr = new StringWriter();
var jsonWr = new PivotTableJsonWriter(strJsonWr);
jsonWr.Write(chartPvtTbl);
context.PivotTableJson = strJsonWr.ToString();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
// var pvtTbl = new PivotTable(
// new[] {"Country"}, // rows
// new[] {"Order Year", "Order Month"}, // cols
// filteredPvtData
// );
// render pivot table HTML
// var pvtTbl = new PivotTable(
// new[] {"Country"}, // rows
// new[] {"Order Year", "Order Month"} , // cols
// pvtData// filteredPvtData
// );
// sort by row total
// pvtTbl.SortRowKeys(null,
// 1, // lets order by measure #1 (sum of unit price)
// System.ComponentModel.ListSortDirection.Descending);
// var strHtmlWr = new StringWriter();
// var pvtHtmlWr = new PivotTableHtmlWriter(strHtmlWr);
// pvtHtmlWr.TableClass = "table table-bordered table-condensed pvtTable";
// pvtHtmlWr.Write(pvtTbl);
// context.PivotTableHtml = strHtmlWr.ToString();
return(Combine(
Initialize <ChartVisualProfileViewModel>("ChartVisualProfile", model =>
{
model.PivotContext = context;
model.QueryResult = profile.QueryResult;
model.AutoStart = profile.AutoStart;
model.Controls = profile.Controls;
model.Indicators = profile.Indicators;
model.Interval = profile.Interval;
model.Keyboard = profile.Keyboard;
model.Pause = profile.Pause;
model.Wrap = profile.Wrap;
})
.Location("Detail", "Content:1")//,
//Shape<TableLayoutProfileViewModel>("TableLayoutProfile_Summary", model =>
// {
// model.AutoStart = profile.AutoStart;
// model.Controls = profile.Controls;
// model.Indicators = profile.Indicators;
// model.Interval = profile.Interval;
// model.Keyboard = profile.Keyboard;
// model.Pause = profile.Pause;
// model.Wrap = profile.Wrap;
// })
// .Location("Summary", "Meta:5")
));
//return Combine(
// Shape("TableLayoutProfile_SummaryAdmin", model =>
// {
// model.Profile = profile;
// }).Location("Content:5")
// //Shape("BootStrapProfile_Buttons_SummaryAdmin", model =>
// //{
// // model.Profile = profile;
// //}).Location("Actions:2")
//);
}
//protected void LoadYr2(Panel p, List<string> rows, List<string> cols, List<string> functions, string title, bool useGTE0, bool useFromTo)
//{
// DataTable dt = new DataTable();
// if (useFromTo)
// {
// dt = GetValantData_From_To2(rows, cols, functions, useGTE0);
// }
// else
// {
// //this branch no longer used
// //dt = GetValantData(rows, cols, functions, useGTE0);
// }
// LoadYr2(dt, p, rows, cols, functions, title, useGTE0, useFromTo);
//}
protected void LoadYr2(DataTable dt, Panel p, List <string> rows, List <string> cols, List <string> functions, string title, bool useGTE0, bool useFromTo)
{
//DataTable dt = new DataTable();
//if (useFromTo)
//{
// dt = GetValantData_From_To2(rows, cols, functions, useGTE0);
//}
//else
//{
// //this branch no longer used
// //dt = GetValantData(rows, cols, functions, useGTE0);
//}
List <string> keepCols = new List <string>();
keepCols.AddRange(rows);
keepCols.AddRange(cols);
PivotData cube;
PivotTable pivotTable;
string[] aggr_labels = new string[functions.Count];
if (functions.Count > 1)
{
IAggregatorFactory[] aggs = new IAggregatorFactory[functions.Count];
for (int f = 0; f < functions.Count; f++)
{
if (functions[f] == "Sum")
{
aggs[f] = new SumAggregatorFactory("n");
aggr_labels[f] = "sum n";
}
else if (functions[f] == "Monthly Avg")
{
aggs[f] = new AverageAggregatorFactory("amt");
aggr_labels[f] = "Monthly Avg";
}
else if (functions[f] == "N Patients")
{
aggs[f] = new CountUniqueAggregatorFactory("patientID");
aggr_labels[f] = "N Patients";
}
else if (functions[f] == "N Patients Dx")
{
aggs[f] = new CountUniqueAggregatorFactory("patientID_DX");
aggr_labels[f] = "N Patients Dx";
}
else if (functions[f] == "N Patients Tx")
{
aggs[f] = new CountUniqueAggregatorFactory("patientID_TX");
aggr_labels[f] = "N Patients Tx";
}
else if (functions[f] == "N Visits")
{
aggs[f] = new SumAggregatorFactory("n_appt");
aggr_labels[f] = "N Visits";
}
else if (functions[f] == "N Visits Dx")
{
aggs[f] = new SumAggregatorFactory("n_appt_dx");
aggr_labels[f] = "N Visits Dx";
}
else if (functions[f] == "N Visits Tx")
{
aggs[f] = new SumAggregatorFactory("n_appt_Tx");
aggr_labels[f] = "N Visits Tx";
}
}
CompositeAggregatorFactory compositeFactory = new CompositeAggregatorFactory(aggs);
cube = new PivotData(keepCols.ToArray(), compositeFactory);
cube.ProcessData(new DataTableReader(dt));
pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube);
}
else
{
switch (functions[0])
{
case "Sum":
cube = new PivotData(keepCols.ToArray(), new SumAggregatorFactory("n"));
break;
case "Monthly Avg":
cube = new PivotData(keepCols.ToArray(), new AverageAggregatorFactory("amt"));
break;
case "N Patients":
cube = new PivotData(keepCols.ToArray(), new CountUniqueAggregatorFactory("patientID"));
break;
default:
cube = new PivotData(keepCols.ToArray(), new CountAggregatorFactory());
break;
}
cube.ProcessData(new DataTableReader(dt));
pivotTable = new PivotTable(rows.ToArray(), cols.ToArray(), cube);
}
var htmlResult = new StringWriter();
var pvtHtmlWr = new MyHtmlWriter2(htmlResult);
if (functions.Count >= 1)
{
pvtHtmlWr.FormatValue = (aggr, idx) =>
{
string newval = (Convert.ToDouble(aggr.Value.ToString()) >= 0) ? String.Format("{0:n0}", aggr.Value) : "<div class=\"negcell\">" + String.Format("{0:n0}", aggr.Value) + "</div>";
return(newval);
};
//pvtHtmlWr.FormatValue = (aggr, idx) => { return String.Format("{0:0}", aggr.Value); };
pvtHtmlWr.FormatMeasureHeader = (aggr, idx) => { return(aggr_labels[idx]); };
}
else
{
//pvtHtmlWr.FormatValue(aggr, idx) =>
//{
// string newval = (Convert.ToDouble(aggr.Value.ToString()) >= 0) ? String.Format("{0:n0}", aggr.Value) : "<div class=\"negcell\">" + String.Format("{0:n0}", aggr.Value) + "</div>";
// return newval;
//};
}
pvtHtmlWr.RowHeaderClass = "row1";
pvtHtmlWr.ColumnHeaderClass = "col1";
pvtHtmlWr.MeasureHeaderClass = "meashead";
pvtHtmlWr.SubtotalDimensions = new string[] { "Direct" };
pvtHtmlWr.SubtotalRows = true;
pvtHtmlWr.AllowHtml = true;
pvtHtmlWr.TotalsColumn = false;
pvtHtmlWr.TotalsRow = true;
pvtHtmlWr.GrandTotal = false;
pvtHtmlWr.Write(pivotTable);
Label header = new Label();
header.Text = "<br/>" + title;
header.Font.Size = 12;
header.Font.Bold = true;
Literal lit = new Literal();
lit.Text = htmlResult.ToString();
p.Controls.Add(header);
p.Controls.Add(lit);
}
public void Export3ItemGroupsToExcel(Stream stream, int year)
{
using (var package = new ExcelPackage(stream))
{
var query = from item in _dataContext.ReportDatas
where item.ChannelName.ToLower() == "direct"
&& (item.ItemGroupName.ToLower() == "prepenem" || item.ItemGroupName.ToLower() == "maxpenepm" || item.ItemGroupName.ToLower() == "combilipid")
&& (item.Year == year || item.Year == year - 1)
select item;
DataTable table = LinqExtension.LinqResultToDataTable(query);
var pivotData = new PivotData(new[] { "ChannelName", "ItemGroupName", "ClientName", "Year", "Month" },
new SumAggregatorFactory("CifAmount")
);
pivotData.ProcessData(new DataTableReader(table));
package.Compression = CompressionLevel.Default;
var pivot = package.Workbook.Worksheets.Add("PivotTable");
var data = package.Workbook.Worksheets.Add("Data");
var pivotTable = new PivotTable(
new[] { "ItemGroupName", "ClientName" }, //rows
new[] { "Year", "Month" }, // columns
pivotData
);
var excelPivotTableWriter = new ExcelPivotTableWriter(pivot, data);
excelPivotTableWriter.Write(pivotTable);
//pkg.Workbook.Worksheets.Delete(wsData);
package.Save();
}
}
public void ExportSalesTeamMrReportToExcel(Stream stream, int year)
{
if (stream == null)
throw new ArgumentNullException("stream");
using (var xlPackage = new ExcelPackage(stream))
{
// get handle to the existing worksheet
var worksheet = xlPackage.Workbook.Worksheets.Add("Sheet1");
//Format Header 1
int header1 = 2;
worksheet.Cells[1, header1].Value = "Total";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "January";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "February";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "March";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "April";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "May";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "June";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "July";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "August";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "September";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "October";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "November";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "December";
header1 = header1 + 6;
//Format Header 2
for (int month = 0; month <= 12; month++)
{
int i = month * 6;
worksheet.Cells[2, i + 2].Value = "Last actual amt";
worksheet.Cells[2, i + 3].Value = "Target amt";
worksheet.Cells[2, i + 4].Value = "Actual amt";
worksheet.Cells[2, i + 5].Value = "Achievement";
worksheet.Cells[2, i + 6].Value = "Growth";
worksheet.Cells[2, i + 7].Value = "Quantity";
}
//Data
int row = 3;
var thisYearTargetSales = from item in _dataContext.TargetSetups
where item.Year == year
&& item.IsTeamTarget == false
&& item.Item.Sku.ToLower() != "na"
select item;
var actualSalesQuery = from item in _dataContext.ReportDatas
where (item.Year == year || item.Year == year - 1)
select item;
if (_workContext.CurrentCustomer.IsInCustomerRole("Administrators") || _workContext.CurrentCustomer.IsInCustomerRole("Management"))
{
//sales = query;
}
else
{
if (_workContext.CurrentCustomer.IsInCustomerRole("MrManagers"))
{
actualSalesQuery = from item in actualSalesQuery
where item.SalesTeamCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower()
select item;
}
else
{
actualSalesQuery = from item in actualSalesQuery
where item.MrCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower()
select item;
thisYearTargetSales = from item in _dataContext.TargetSetups
where item.Year == year
&& item.IsTeamTarget == true
&& item.Mr.MrCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower()
select item;
}
}
DataTable table = LinqExtension.LinqResultToDataTable(actualSalesQuery);
var quantityPivotData = new PivotData(new[] { "ChannelName", "SalesTeamCode", "MrCode", "Year", "Month" },
new SumAggregatorFactory("Quantity")
);
quantityPivotData.ProcessData(new DataTableReader(table));
var amountPivotData = new PivotData(new[] { "ChannelName", "SalesTeamCode", "MrCode", "Year", "Month" },
new SumAggregatorFactory("TotalAmount")
);
amountPivotData.ProcessData(new DataTableReader(table));
foreach (var channel in _dataContext.Channels)
{
#region Channel line
CommonTargetActualSales channelLine = new CommonTargetActualSales();
channelLine.Code = channel.ChannelId.ToString();
channelLine.Name = channel.ChannelName;
var thisYearTargetSalesChannel = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId);
channelLine.ActualQty01 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 1].Value);
channelLine.ActualQty02 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 2].Value);
channelLine.ActualQty03 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 3].Value);
channelLine.ActualQty04 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 4].Value);
channelLine.ActualQty05 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 5].Value);
channelLine.ActualQty06 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 6].Value);
channelLine.ActualQty07 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 7].Value);
channelLine.ActualQty08 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 8].Value);
channelLine.ActualQty09 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 9].Value);
channelLine.ActualQty10 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 10].Value);
channelLine.ActualQty11 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 11].Value);
channelLine.ActualQty12 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 12].Value);
channelLine.ActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 1].Value);
channelLine.ActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 2].Value);
channelLine.ActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 3].Value);
channelLine.ActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 4].Value);
channelLine.ActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 5].Value);
channelLine.ActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 6].Value);
channelLine.ActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 7].Value);
channelLine.ActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 8].Value);
channelLine.ActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 9].Value);
channelLine.ActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 10].Value);
channelLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 11].Value);
channelLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year, 12].Value);
channelLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 1].Value);
channelLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 2].Value);
channelLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 3].Value);
channelLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 4].Value);
channelLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 5].Value);
channelLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 6].Value);
channelLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 7].Value);
channelLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 8].Value);
channelLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 9].Value);
channelLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 10].Value);
channelLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 11].Value);
channelLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, Key.Empty, year - 1, 12].Value);
channelLine.TargetAmt01 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 1);
channelLine.TargetAmt02 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 2);
channelLine.TargetAmt03 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 3);
channelLine.TargetAmt04 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 4);
channelLine.TargetAmt05 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 5);
channelLine.TargetAmt06 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 6);
channelLine.TargetAmt07 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 7);
channelLine.TargetAmt08 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 8);
channelLine.TargetAmt09 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 9);
channelLine.TargetAmt10 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 10);
channelLine.TargetAmt11 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 11);
channelLine.TargetAmt12 = GetMonthTargetAmount(thisYearTargetSalesChannel, year, 12);
if (channelLine.TargetAmt == 0 && channelLine.LastYearActualAmt == 0 && channelLine.ActualAmt == 0)
{
//do nothing
}
else
{
ExportSalesTeamMrReportToExcelRow(worksheet, row, Color.LightBlue, channelLine);
row++;
}
#endregion
foreach (var team in channel.SalesTeams)
{
#region team line
CommonTargetActualSales teamLine = new CommonTargetActualSales();
teamLine.Code = team.SalesTeamCode;
teamLine.Name = team.SalesTeamName;
var thisYearTargetItemGroup = thisYearTargetSales.Where(a => a.Mr.SalesTeam.SalesTeamCode.ToLower() == team.SalesTeamCode.ToLower());
teamLine.ActualQty01 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 1].Value);
teamLine.ActualQty02 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 2].Value);
teamLine.ActualQty03 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 3].Value);
teamLine.ActualQty04 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 4].Value);
teamLine.ActualQty05 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 5].Value);
teamLine.ActualQty06 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 6].Value);
teamLine.ActualQty07 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 7].Value);
teamLine.ActualQty08 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 8].Value);
teamLine.ActualQty09 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 9].Value);
teamLine.ActualQty10 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 10].Value);
teamLine.ActualQty11 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 11].Value);
teamLine.ActualQty12 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 12].Value);
teamLine.ActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 1].Value);
teamLine.ActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 2].Value);
teamLine.ActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 3].Value);
teamLine.ActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 4].Value);
teamLine.ActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 5].Value);
teamLine.ActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 6].Value);
teamLine.ActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 7].Value);
teamLine.ActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 8].Value);
teamLine.ActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 9].Value);
teamLine.ActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 10].Value);
teamLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 11].Value);
teamLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year, 12].Value);
teamLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 1].Value);
teamLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 2].Value);
teamLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 3].Value);
teamLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 4].Value);
teamLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 5].Value);
teamLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 6].Value);
teamLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 7].Value);
teamLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 8].Value);
teamLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 9].Value);
teamLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 10].Value);
teamLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 11].Value);
teamLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, Key.Empty, year - 1, 12].Value);
teamLine.TargetAmt01 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 1);
teamLine.TargetAmt02 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 2);
teamLine.TargetAmt03 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 3);
teamLine.TargetAmt04 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 4);
teamLine.TargetAmt05 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 5);
teamLine.TargetAmt06 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 6);
teamLine.TargetAmt07 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 7);
teamLine.TargetAmt08 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 8);
teamLine.TargetAmt09 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 9);
teamLine.TargetAmt10 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 10);
teamLine.TargetAmt11 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 11);
teamLine.TargetAmt12 = GetMonthTargetAmount(thisYearTargetItemGroup, year, 12);
if (teamLine.TargetAmt == 0 && teamLine.LastYearActualAmt == 0 && teamLine.ActualAmt == 0)
{
//do nothing
}
else
{
ExportSalesTeamMrReportToExcelRow(worksheet, row, Color.LightBlue, teamLine, 1);
row++;
}
#endregion
foreach (var item in team.Mrs.Where(a=>a.MrCode.ToLower()!=team.SalesTeamCode.ToLower()))
{
#region item line
CommonTargetActualSales mrLine = new CommonTargetActualSales();
mrLine.Code = item.MrCode;
mrLine.Name = item.MrName;
var thisYearTargetItem = thisYearTargetSales.Where(a => a.Mr.MrCode.ToLower() == item.MrCode.ToLower());
mrLine.ActualQty01 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 1].Value);
mrLine.ActualQty02 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 2].Value);
mrLine.ActualQty03 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 3].Value);
mrLine.ActualQty04 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 4].Value);
mrLine.ActualQty05 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 5].Value);
mrLine.ActualQty06 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 6].Value);
mrLine.ActualQty07 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 7].Value);
mrLine.ActualQty08 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 8].Value);
mrLine.ActualQty09 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 9].Value);
mrLine.ActualQty10 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 10].Value);
mrLine.ActualQty11 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 11].Value);
mrLine.ActualQty12 = Convert.ToDouble(quantityPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 12].Value);
mrLine.ActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 1].Value);
mrLine.ActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 2].Value);
mrLine.ActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 3].Value);
mrLine.ActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 4].Value);
mrLine.ActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 5].Value);
mrLine.ActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 6].Value);
mrLine.ActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 7].Value);
mrLine.ActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 8].Value);
mrLine.ActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 9].Value);
mrLine.ActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 10].Value);
mrLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 11].Value);
mrLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year, 12].Value);
mrLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 1].Value);
mrLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 2].Value);
mrLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 3].Value);
mrLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 4].Value);
mrLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 5].Value);
mrLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 6].Value);
mrLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 7].Value);
mrLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 8].Value);
mrLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 9].Value);
mrLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 10].Value);
mrLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 11].Value);
mrLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, team.SalesTeamCode, item.MrCode, year - 1, 12].Value);
mrLine.TargetAmt01 = GetMonthTargetAmount(thisYearTargetItem, year, 1);
mrLine.TargetAmt02 = GetMonthTargetAmount(thisYearTargetItem, year, 2);
mrLine.TargetAmt03 = GetMonthTargetAmount(thisYearTargetItem, year, 3);
mrLine.TargetAmt04 = GetMonthTargetAmount(thisYearTargetItem, year, 4);
mrLine.TargetAmt05 = GetMonthTargetAmount(thisYearTargetItem, year, 5);
mrLine.TargetAmt06 = GetMonthTargetAmount(thisYearTargetItem, year, 6);
mrLine.TargetAmt07 = GetMonthTargetAmount(thisYearTargetItem, year, 7);
mrLine.TargetAmt08 = GetMonthTargetAmount(thisYearTargetItem, year, 8);
mrLine.TargetAmt09 = GetMonthTargetAmount(thisYearTargetItem, year, 9);
mrLine.TargetAmt10 = GetMonthTargetAmount(thisYearTargetItem, year, 10);
mrLine.TargetAmt11 = GetMonthTargetAmount(thisYearTargetItem, year, 11);
mrLine.TargetAmt12 = GetMonthTargetAmount(thisYearTargetItem, year, 12);
if (mrLine.TargetAmt == 0 && mrLine.LastYearActualAmt == 0 && mrLine.ActualAmt == 0)
{
//do nothing
}
else
{
ExportSalesTeamMrReportToExcelRow(worksheet, row, Color.LightBlue, mrLine, 2);
row++;
}
#endregion
}
}
}
#region Total line
CommonTargetActualSales totalLine = new CommonTargetActualSales();
totalLine.Code = "Total";
totalLine.Name = "Total";
totalLine.ActualQty01 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 1].Value);
totalLine.ActualQty02 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 2].Value);
totalLine.ActualQty03 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 3].Value);
totalLine.ActualQty04 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 4].Value);
totalLine.ActualQty05 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 5].Value);
totalLine.ActualQty06 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 6].Value);
totalLine.ActualQty07 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 7].Value);
totalLine.ActualQty08 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 8].Value);
totalLine.ActualQty09 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 9].Value);
totalLine.ActualQty10 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 10].Value);
totalLine.ActualQty11 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 11].Value);
totalLine.ActualQty12 = Convert.ToDouble(quantityPivotData[Key.Empty, Key.Empty, Key.Empty, year, 12].Value);
totalLine.ActualAmt01 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 1].Value);
totalLine.ActualAmt02 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 2].Value);
totalLine.ActualAmt03 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 3].Value);
totalLine.ActualAmt04 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 4].Value);
totalLine.ActualAmt05 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 5].Value);
totalLine.ActualAmt06 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 6].Value);
totalLine.ActualAmt07 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 7].Value);
totalLine.ActualAmt08 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 8].Value);
totalLine.ActualAmt09 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 9].Value);
totalLine.ActualAmt10 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 10].Value);
totalLine.ActualAmt11 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 11].Value);
totalLine.ActualAmt11 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year, 12].Value);
totalLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 1].Value);
totalLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 2].Value);
totalLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 3].Value);
totalLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 4].Value);
totalLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 5].Value);
totalLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 6].Value);
totalLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 7].Value);
totalLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 8].Value);
totalLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 9].Value);
totalLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 10].Value);
totalLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 11].Value);
totalLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[Key.Empty, Key.Empty, Key.Empty, year - 1, 12].Value);
totalLine.TargetAmt01 = GetMonthTargetAmount(thisYearTargetSales, year, 1);
totalLine.TargetAmt02 = GetMonthTargetAmount(thisYearTargetSales, year, 2);
totalLine.TargetAmt03 = GetMonthTargetAmount(thisYearTargetSales, year, 3);
totalLine.TargetAmt04 = GetMonthTargetAmount(thisYearTargetSales, year, 4);
totalLine.TargetAmt05 = GetMonthTargetAmount(thisYearTargetSales, year, 5);
totalLine.TargetAmt06 = GetMonthTargetAmount(thisYearTargetSales, year, 6);
totalLine.TargetAmt07 = GetMonthTargetAmount(thisYearTargetSales, year, 7);
totalLine.TargetAmt08 = GetMonthTargetAmount(thisYearTargetSales, year, 8);
totalLine.TargetAmt09 = GetMonthTargetAmount(thisYearTargetSales, year, 9);
totalLine.TargetAmt10 = GetMonthTargetAmount(thisYearTargetSales, year, 10);
totalLine.TargetAmt11 = GetMonthTargetAmount(thisYearTargetSales, year, 11);
totalLine.TargetAmt12 = GetMonthTargetAmount(thisYearTargetSales, year, 12);
ExportItemGroupItemReportToExcelRow(worksheet, row, Color.LightBlue, totalLine);
#endregion
//Report name
worksheet.Cells[2, 1].Value = "Report by Channel - Sales team - Mr in " + year.ToString();
worksheet.Cells[2, 1].AutoFitColumns();
//Rows & columns count
int cols = 12 * 6 + 6 + 1;
int rows = row;
//Format header
var header = worksheet.Cells[1, 1, 2, cols];
header.Style.Font.Bold = true;
header.Style.Fill.PatternType = ExcelFillStyle.Solid;
header.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
//Format total
var total = worksheet.Cells[rows, 1, row, cols];
total.Style.Font.Bold = true;
total.Style.Fill.PatternType = ExcelFillStyle.Solid;
total.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
//Format row
var text = worksheet.Cells[1, 1, row, 1];
text.Style.Font.Bold = true;
text.Style.Fill.PatternType = ExcelFillStyle.Solid;
text.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
text.AutoFitColumns();
// save the new spreadsheet
xlPackage.Save();
}
}
public void ExportChannelReportToExcelUSD(Stream stream, int year)
{
if (stream == null)
throw new ArgumentNullException("stream");
using (var xlPackage = new ExcelPackage(stream))
{
// get handle to the existing worksheet
var worksheet = xlPackage.Workbook.Worksheets.Add("Sheet1");
//Format Header 1
int header1 = 2;
worksheet.Cells[1, header1].Value = "Total";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "January";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "February";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "March";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "April";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "May";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "June";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "July";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "August";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "September";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "October";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "November";
header1 = header1 + 6;
worksheet.Cells[1, header1].Value = "December";
header1 = header1 + 6;
//Format Header 2
for (int month = 0; month <= 12; month++)
{
int i = month * 6;
worksheet.Cells[2, i + 2].Value = "Last actual amt";
worksheet.Cells[2, i + 3].Value = "Target amt";
worksheet.Cells[2, i + 4].Value = "Actual amt";
worksheet.Cells[2, i + 5].Value = "Achievement";
worksheet.Cells[2, i + 6].Value = "Growth";
worksheet.Cells[2, i + 7].Value = "Quantity";
}
//Exc
double exchangeRate = 22500;
//Data
int row = 3;
var thisYearTargetSales = from item in _dataContext.TargetSetups
where item.Year == year
&& item.IsTeamTarget == true
//&& (item.Mr.SalesTeam.ChannelId == (int)ChannelEnum.Direct || item.Mr.SalesTeam.ChannelId == (int)ChannelEnum.Indirect)
select item;
var actualSalesQuery = from item in _dataContext.ReportDatas
where (item.Year == year || item.Year == year - 1)
select item;
if (_workContext.CurrentCustomer.IsInCustomerRole("Administrators") || _workContext.CurrentCustomer.IsInCustomerRole("Management"))
{
//sales = query;
}
else
{
if (_workContext.CurrentCustomer.IsInCustomerRole("MrManagers"))
{
actualSalesQuery = from item in actualSalesQuery
where item.SalesTeamCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower()
select item;
}
else
{
actualSalesQuery = from item in actualSalesQuery
where item.MrCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower()
select item;
thisYearTargetSales = from item in _dataContext.TargetSetups
where item.Year == year
&& item.IsTeamTarget == false
&& item.Mr.MrCode.ToLower() == _workContext.CurrentCustomer.Username.ToLower()
select item;
}
}
DataTable table = LinqExtension.LinqResultToDataTable(actualSalesQuery);
var quantityPivotData = new PivotData(new[] { "ChannelName", "Sku", "Year", "Month" },
new SumAggregatorFactory("Quantity")
);
quantityPivotData.ProcessData(new DataTableReader(table));
var amountPivotData = new PivotData(new[] { "ChannelName", "Sku", "Year", "Month" },
new SumAggregatorFactory("CifAmount")
);
amountPivotData.ProcessData(new DataTableReader(table));
foreach (var channel in _dataContext.Channels.Where(a => a.ChannelName.ToLower() != "na"))
{
CommonTargetActualSales channelLine = new CommonTargetActualSales();
channelLine.Code = channel.ChannelName;
channelLine.Name = channel.ChannelName;
channelLine.ActualQty01 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 1].Value);
channelLine.ActualQty02 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 2].Value);
channelLine.ActualQty03 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 3].Value);
channelLine.ActualQty04 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 4].Value);
channelLine.ActualQty05 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 5].Value);
channelLine.ActualQty06 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 6].Value);
channelLine.ActualQty07 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 7].Value);
channelLine.ActualQty08 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 8].Value);
channelLine.ActualQty09 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 9].Value);
channelLine.ActualQty10 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 10].Value);
channelLine.ActualQty11 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 11].Value);
channelLine.ActualQty12 = Convert.ToDouble(quantityPivotData[channel.ChannelName, Key.Empty, year, 12].Value);
channelLine.ActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 1].Value) ;
channelLine.ActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 2].Value) ;
channelLine.ActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 3].Value) ;
channelLine.ActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 4].Value) ;
channelLine.ActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 5].Value) ;
channelLine.ActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 6].Value) ;
channelLine.ActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 7].Value) ;
channelLine.ActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 8].Value) ;
channelLine.ActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 9].Value) ;
channelLine.ActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 10].Value) ;
channelLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 11].Value) ;
channelLine.ActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year, 12].Value) ;
channelLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 1].Value) ;
channelLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 2].Value) ;
channelLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 3].Value) ;
channelLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 4].Value) ;
channelLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 5].Value) ;
channelLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 6].Value) ;
channelLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 7].Value) ;
channelLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 8].Value) ;
channelLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 9].Value) ;
channelLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 10].Value) ;
channelLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 11].Value) ;
channelLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, Key.Empty, year - 1, 12].Value) ;
if (thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Count() > 0)
{
channelLine.TargetAmt01 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount01) / exchangeRate;
channelLine.TargetAmt02 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount02) / exchangeRate;
channelLine.TargetAmt03 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount03) / exchangeRate;
channelLine.TargetAmt04 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount04) / exchangeRate;
channelLine.TargetAmt05 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount05) / exchangeRate;
channelLine.TargetAmt06 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount06) / exchangeRate;
channelLine.TargetAmt07 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount07) / exchangeRate;
channelLine.TargetAmt08 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount08) / exchangeRate;
channelLine.TargetAmt09 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount09) / exchangeRate;
channelLine.TargetAmt10 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount10) / exchangeRate;
channelLine.TargetAmt11 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount11) / exchangeRate;
channelLine.TargetAmt12 = thisYearTargetSales.Where(a => a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount12) / exchangeRate;
}
if (channelLine.TargetAmt == 0 && channelLine.LastYearActualAmt == 0 && channelLine.ActualAmt == 0)
{
//do nothing
}
else
{
ExportChannelReportToExcelRow(worksheet, row, Color.LightBlue, channelLine);
row++;
}
foreach (var item in _dataContext.Items.OrderBy(a=>a.ItemName))
{
CommonTargetActualSales itemLine = new CommonTargetActualSales();
itemLine.Code = item.Sku;
itemLine.Name = item.ItemName;
itemLine.ActualQty01 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 1].Value);
itemLine.ActualQty02 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 2].Value);
itemLine.ActualQty03 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 3].Value);
itemLine.ActualQty04 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 4].Value);
itemLine.ActualQty05 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 5].Value);
itemLine.ActualQty06 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 6].Value);
itemLine.ActualQty07 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 7].Value);
itemLine.ActualQty08 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 8].Value);
itemLine.ActualQty09 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 9].Value);
itemLine.ActualQty10 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 10].Value);
itemLine.ActualQty11 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 11].Value);
itemLine.ActualQty12 = Convert.ToDouble(quantityPivotData[channel.ChannelName, item.Sku, year, 12].Value);
itemLine.ActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 1].Value) ;
itemLine.ActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 2].Value) ;
itemLine.ActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 3].Value) ;
itemLine.ActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 4].Value) ;
itemLine.ActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 5].Value) ;
itemLine.ActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 6].Value) ;
itemLine.ActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 7].Value) ;
itemLine.ActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 8].Value) ;
itemLine.ActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 9].Value) ;
itemLine.ActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 10].Value) ;
itemLine.ActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 11].Value) ;
itemLine.ActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year, 12].Value) ;
itemLine.LastYearActualAmt01 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 1].Value) ;
itemLine.LastYearActualAmt02 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 2].Value) ;
itemLine.LastYearActualAmt03 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 3].Value) ;
itemLine.LastYearActualAmt04 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 4].Value) ;
itemLine.LastYearActualAmt05 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 5].Value) ;
itemLine.LastYearActualAmt06 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 6].Value) ;
itemLine.LastYearActualAmt07 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 7].Value) ;
itemLine.LastYearActualAmt08 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 8].Value) ;
itemLine.LastYearActualAmt09 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 9].Value) ;
itemLine.LastYearActualAmt10 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 10].Value) ;
itemLine.LastYearActualAmt11 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 11].Value) ;
itemLine.LastYearActualAmt12 = Convert.ToDouble(amountPivotData[channel.ChannelName, item.Sku, year - 1, 12].Value) ;
if (thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Count() > 0)
{
itemLine.TargetAmt01 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount01) / exchangeRate;
itemLine.TargetAmt02 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount02) / exchangeRate;
itemLine.TargetAmt03 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount03) / exchangeRate;
itemLine.TargetAmt04 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount04) / exchangeRate;
itemLine.TargetAmt05 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount05) / exchangeRate;
itemLine.TargetAmt06 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount06) / exchangeRate;
itemLine.TargetAmt07 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount07) / exchangeRate;
itemLine.TargetAmt08 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount08) / exchangeRate;
itemLine.TargetAmt09 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount09) / exchangeRate;
itemLine.TargetAmt10 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount10) / exchangeRate;
itemLine.TargetAmt11 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount11) / exchangeRate;
itemLine.TargetAmt12 = thisYearTargetSales.Where(a => a.ItemId == item.ItemId && a.Mr.SalesTeam.ChannelId == channel.ChannelId).Sum(a => a.Amount12) / exchangeRate;
}
if (itemLine.TargetAmt == 0 && itemLine.LastYearActualAmt == 0 && itemLine.ActualAmt == 0)
{
//do nothing
}
else
{
ExportChannelReportToExcelRow(worksheet, row, Color.LightBlue, itemLine, 1);
row++;
}
}
}
//Report name
worksheet.Cells[2, 1].Value = "Report by Channel - product in " + year.ToString();
worksheet.Cells[2, 1].AutoFitColumns();
//Rows & columns count
int cols = 12 * 6 + 6 + 1;
int rows = row;
//Format header
var header = worksheet.Cells[1, 1, 2, cols];
header.Style.Font.Bold = true;
header.Style.Fill.PatternType = ExcelFillStyle.Solid;
header.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
//Format total
var total = worksheet.Cells[rows, 1, row, cols];
total.Style.Font.Bold = true;
total.Style.Fill.PatternType = ExcelFillStyle.Solid;
total.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
//Format row
var text = worksheet.Cells[1, 1, row, 1];
text.Style.Font.Bold = true;
text.Style.Fill.PatternType = ExcelFillStyle.Solid;
text.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
text.AutoFitColumns();
// save the new spreadsheet
xlPackage.Save();
}
}
private DataTable GetWeeklyHoursByEmployee()
{
DataRow dr;
BuildEmpTable();
//dtTeam = objADM.LoadUsers(ddlLocation.SelectedItem.Text);
//if (dtTeam.Rows.Count > 0)
//{
// foreach (DataRow drTeam in dtTeam.Rows)
// {
// foreach (var item in lstDate.Chunks)
// {
// dr = dtResult.NewRow();
// dr["Team"] = drTeam["T_TeamName"];
// dr["User"] = drTeam["UL_User_Name"].ToString().Trim();
// dr["Date"] = item.ToString().Split('-')[0];
// dt = objADM.GetTotalWorkHours("user", item.ToString().Split('-')[0], item.ToString().Split('-')[1], drTeam["UL_User_Name"].ToString());
// if (dt.Rows.Count > 0)
// {
// if (dt.Rows[0]["Total"].ToString() == "0")
// {
// continue;
// }
// dr["Avg"] = Math.Round(Convert.ToDouble(dt.Rows[0]["Total"].ToString()), 2, MidpointRounding.AwayFromZero);
// }
// dtResult.Rows.Add(dr);
// }
// }
//}
foreach (var item in lstDate.Chunks)
{
dt = objADM.GetUserTotal(item.ToString().Split('-')[0], item.ToString().Split('-')[1], ddlLocation.SelectedItem.Text);
if (dt.Rows.Count > 0)
{
foreach (DataRow drRow in dt.Rows)
{
dr = dtResult.NewRow();
if (string.IsNullOrEmpty(drRow["Team1"].ToString()))
{
dr["Team"] = drRow["Team2"];
}
else
{
dr["Team"] = drRow["Team1"];
}
if (string.IsNullOrEmpty(drRow["user1"].ToString()))
{
dr["User"] = drRow["user2"].ToString().Trim();
}
else
{
dr["User"] = drRow["user1"].ToString().Trim();
}
dr["Date"] = item.ToString().Split('-')[0];
dr["Avg"] = Math.Round(Convert.ToDecimal(drRow["Total"]), 2, MidpointRounding.AwayFromZero);
dtResult.Rows.Add(dr);
}
}
}
//if (dtResult.Rows.Count > 0)
//{
// DataView dv = dtResult.DefaultView;
// dv.Sort = "[Date] asc, [Team] asc";
// dtResult = dv.ToTable();
//}
var ordersPvtData = new PivotData(new[] { "Team", "User", "Date" },
new SumAggregatorFactory("Avg"));
ordersPvtData.ProcessData(new DataTableReader(dtResult));
var pvtTbl = new PivotTable(
new[] { "Team", "User" }, //rows
new[] { "Date" }, // columns
ordersPvtData,
new CustomSortKeyComparer(new[] { NaturalSortKeyComparer.Instance }),
new CustomSortKeyComparer(new[] { NaturalSortKeyComparer.Instance })
);
var pvtDataTableWr = new PivotTableDataTableWriter("PivotTable");
DataTable res = pvtDataTableWr.Write(pvtTbl);
res.Columns[0].ColumnName = "Team";
res.Columns[1].ColumnName = "User";
return(res);
}
