PivotData getSamplePivotData(bool diag = false)
{
var vals = new List <object>();
var keys = new List <uint[]>();
for (uint a = 0; a < 3; a++)
{
for (uint b = (diag?a:0); b < 3; b++)
{
for (uint c = (diag?b:0); c < 3; c++)
{
keys.Add(new uint[] { a, b + 3, c + 6 });
vals.Add(2);
}
}
}
var pvtState = new PivotDataState()
{
DimCount = 3,
KeyValues = new object[] { "A1", "A2", "A3", "B1", "B2", "B3", "C1", "C2", "C3" },
Values = vals.ToArray(),
ValueKeys = keys.ToArray()
};
var pvt = new PivotData(new string[] { "A", "B", "C" }, new CountAggregatorFactory(), true);
pvt.SetState(pvtState);
/*foreach (var v in pvt.AllValues) {
* Console.WriteLine(v.Key.ToString() );
* }
* Console.WriteLine("*******");*/
return(pvt);
}
private JObject GetActivity(string rootCode, PivotData initData)
{
var rootModel = this.storageProvider.GetRootModel(rootCode);
if (string.IsNullOrEmpty(rootModel?.JsonStructure))
{
throw new Exception($"No root model with name {rootModel}");
}
var tree = ObjectHelper.GetInnerObject(JObject.Parse(rootModel.JsonStructure));
var bpNode = tree[ParserHelper.GetPivotName(PivotType.BusinessProcess)];
if (bpNode == null)
{
throw new Exception("No BP in your model");
}
var ac = bpNode[initData.GetJTokenName()];
if (!(ac is JObject))
{
throw new Exception($"No ac with objectCode {initData.MainValue}");
}
var res = new JObject {
[initData.MainValue] = ac
};
return(res);
}
public static List <string> GetJTokenPath(this PivotData pivotData)
{
var result = new List <string>();
var chunks = new List <string>();
// Do not include pivot name for Transactions
if (pivotData.PivotDefinition.Type != PivotType.Transaction)
{
chunks.Add(ParserHelper.GetPivotName(pivotData.PivotDefinition.Type));
}
// Do not include pivot main values for Activity
if (pivotData.PivotDefinition.Type != PivotType.Activity)
{
if (!string.IsNullOrEmpty(pivotData.MainValue))
{
chunks.Add(pivotData.MainValue);
}
}
result.Add(string.Join(".", chunks));
// Do not include pivot secondary values for Activity
if (pivotData.PivotDefinition.Type != PivotType.Activity)
{
if (pivotData.SecondaryValues.Any())
{
result.Add(pivotData.SecondaryValues.First());
}
}
return(result);
}
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 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 async Task ReadPivotData(string measurementName)
{
PivotData data = await _reader.QueryAsync(measurementName, new QueryRange(-7, RangeUnit.day));
Console.WriteLine(data.ToJson());
Console.WriteLine("-----------------");
}
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 PivotData_Slice()
{
var testData = generateData();
var pvtData = new PivotData(new string[] { "name", "date", "qty", "total" }, new CountAggregatorFactory(), testData);
var slice1Data = pvtData.Slice(new string[] { "name", "qty" }, false);
Assert.Equal(2, slice1Data.Dimensions.Length);
var vk = new ValueKey("Test0", 0);
Assert.Equal(pvtData["Test0", Key.Empty, 0, Key.Empty].Value, slice1Data[vk].Value);
Assert.Equal(pvtData[Key.Empty, Key.Empty, Key.Empty, Key.Empty].Value, slice1Data[Key.Empty, Key.Empty].Value);
var slice2Data = pvtData.Slice(new string[] { "name", "qty" }, false, (v) => {
return(v.Key.DimKeys[0].Equals("Test0"));
});
Assert.Equal(((uint)pvtData[Key.Empty, Key.Empty, Key.Empty, Key.Empty].Value) / 2, slice2Data[Key.Empty, Key.Empty].Value);
// check zero-dim slice
var sliceZeroDimData = slice2Data.Slice(null, false);
Assert.Equal(slice2Data[Key.Empty, Key.Empty].Value, sliceZeroDimData[new object[0]].Value);
}
public void PivotData_GetDimensionKeys()
{
var testData = generateData();
var pvtData = new PivotData(new string[] { "name", "qty", "i" }, new CountAggregatorFactory(), testData);
var dimKeys = pvtData.GetDimensionKeys();
Assert.Equal(3, dimKeys.Length);
// check "i" dim keys
Assert.Equal(1, dimKeys[2].Length);
Assert.Equal(2, dimKeys[2][0]);
// check "i" dim keys
Assert.Equal(10, dimKeys[1].Length);
var onlyOneDimKeys = pvtData.GetDimensionKeys(new [] { "i" });
Assert.Equal(1, onlyOneDimKeys.Length);
Assert.Equal(1, onlyOneDimKeys[0].Length);
// check custom comparers
var qtyDimKeys = pvtData.GetDimensionKeys(new [] { "i", "qty" },
new IComparer <object> [] {
null, //use default for i
NaturalSortKeyComparer.ReverseInstance
});
Assert.Equal(9, qtyDimKeys[1][0]);
}
protected void Page_Load(object sender, EventArgs e)
{
string pCode = "", pName = ""; bool defaultVal;
defaultVal = false;
pName = Page.RouteData.Values["pivotname"] as string;
pCode = Page.RouteData.Values["pivotcode"] as string;
if (pCode == null)
{
defaultVal = true;
}
string redirPth = "~/home.aspx";
try
{
if (UserData.checkPivot(pName))
{
PivotData p = PivotData.getPivotRedir(pName, pCode, defaultVal);
if (p.PivotType == "pl")
{
redirPth = p.PivotContent;
}
//Server.ClearError();
}
else
{
redirPth = "~/error.aspx";
}
}
catch (Exception exp)
{ redirPth = "~/error.aspx"; }
//}
Response.Redirect(redirPth);
}
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));
}
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);
}
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();
}
private void updatePivotRotation(PivotData pd)
{
//vector from pivot to sun
//finding angle to turn towards based on direction of vector on a single axis
if (userLock)
{
float y = userRotation;
if (pd.pivotTransform.lossyScale.z < 0)
{
y = -y;
}
Quaternion current = pd.pivotTransform.rotation;
pd.pivotTransform.localRotation = pd.defaultOrientation;
Quaternion target = pd.pivotTransform.rotation * Quaternion.Euler(0f, y, 0f);
pd.pivotTransform.rotation = Quaternion.Lerp(current, target, TimeWarp.deltaTime * this.trackingSpeed);
}
else
{
Vector3 vector = pd.pivotTransform.InverseTransformPoint(sunTransform.position);
float y = (float)SSTUUtils.toDegrees(Mathf.Atan2(vector.x, vector.z));
if (pd.pivotTransform.lossyScale.z < 0)
{
y = -y;
}
Quaternion to = pd.pivotTransform.rotation * Quaternion.Euler(0f, y, 0f);
pd.pivotTransform.rotation = Quaternion.Lerp(pd.pivotTransform.rotation, to, TimeWarp.deltaTime * this.trackingSpeed);
}
}
protected void CheckSerializerPerf(PivotData pvtData, string serializerName, Func <PivotDataState, object> serialize, Func <object, PivotDataState> deserialize)
{
var sw = new Stopwatch();
sw.Start();
var pvtState = pvtData.GetState();
sw.Stop();
Console.WriteLine("PivotData GetState time: {0}", sw.Elapsed);
sw.Restart();
var serializedState = serialize(pvtState);
sw.Stop();
Console.WriteLine("PivotDataState {0} serialize time: {1}", serializerName, sw.Elapsed);
sw.Restart();
var pvtStateFromSerialized = deserialize(serializedState);
sw.Stop();
Console.WriteLine("PivotDataState {0} Deserialize time: {1}", serializerName, sw.Elapsed);
var pvtData3 = new PivotData(new string[] { "year", "month", "a", "i" }, new CountAggregatorFactory(), true);
sw.Restart();
pvtData3.SetState(pvtStateFromSerialized);
sw.Stop();
Console.WriteLine("PivotData SetState time: {0}", sw.Elapsed);
Assert.Equal(pvtState.KeyValues.Length, pvtStateFromSerialized.KeyValues.Length);
Assert.Equal(pvtState.ValueKeys.Length, pvtStateFromSerialized.ValueKeys.Length);
Assert.Equal(pvtState.Values.Length, pvtStateFromSerialized.Values.Length);
}
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_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);
}
protected void Page_Load(object sender, EventArgs e)
{
PvtData = GetDataCube();
if (!IsPostBack)
{
// bind years filter items
var yearKeys = PvtData.GetDimensionKeys(new[] { "Order Year" })[0];
filterYear.DataSource = yearKeys.Select(y => new ListItem(y.ToString(), y.ToString())).ToArray();
filterYear.DataBind();
}
}
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 static string GetJTokenName(this PivotData pivotData, bool escape = true)
{
var result = string.Empty;
var tokens = pivotData.GetJTokenPath().Take(1).ToList();
tokens = escape ? tokens.EscapeJTokenNames() : tokens;
result = string.Join(".", tokens);
return(result);
}
public static QueryNode SetPivot(this QueryNode queryNode, PivotType type, string mainValue,
params string[] secondaryValues)
{
var pivotData = new PivotData
{
MainValue = mainValue,
SecondaryValues = secondaryValues?.ToList() ?? new List <string>(),
PivotDefinition = ParserHelper.GetPivot(type)
};
queryNode.PivotData = pivotData;
return(queryNode);
}
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);
}
private PivotData GetPivotData(string valX, string valY)
{
IEnumerable <PivotData> flt = SummaryOfData.Where(d => d.ValueX == valX && d.ValueY == valY);
if (flt.Count() > 0)
{
return(flt.ElementAt(0));
}
PivotData pd = new PivotData()
{
ValueX = valX, ValueY = valY
};
SummaryOfData.Add(pd);
return(pd);
}
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 void PivotData_GetState()
{
var testData = generateData();
var pvtData = new PivotData(new string[] { "name", "qty" }, new CountAggregatorFactory(), testData, false);
var pvtState = pvtData.GetState();
var pvtData2 = new PivotData(new string[] { "name", "qty" }, new CountAggregatorFactory(), false);
pvtData2.SetState(pvtState);
Action <PivotData, PivotData> check = (pvt1, pvt2) => {
Assert.Equal(pvt1.AllValues.Count, pvt2.AllValues.Count);
Assert.Equal(pvt1[ValueKey.Empty2D].Value, pvt2[ValueKey.Empty2D].Value);
foreach (var vk in pvt1.AllValues)
{
Assert.Equal(pvt1[vk.Key].Value, pvt2[vk.Key].Value);
Assert.Equal(pvt1[vk.Key].Count, pvt2[vk.Key].Count);
}
};
check(pvtData, pvtData2);
var jsonState = JsonConvert.SerializeObject(pvtState);
//Output.WriteLine(jsonState);
var pvtStateFromJson = JsonConvert.DeserializeObject <PivotDataState>(jsonState);
var pvtData3 = new PivotData(new string[] { "name", "qty" }, new CountAggregatorFactory(), false);
pvtData3.SetState(pvtStateFromJson);
check(pvtData, pvtData3);
// internal serialization
var memStream = new MemoryStream();
pvtState.Serialize(memStream);
memStream = new MemoryStream(memStream.ToArray());
var pvtStateFromSerialized = PivotDataState.Deserialize(memStream);
var pvtData4 = new PivotData(new string[] { "name", "qty" }, new CountAggregatorFactory(), false);
pvtData4.SetState(pvtStateFromSerialized);
check(pvtData, pvtData4);
}
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 static PivotData GetPivotData(string nodeName)
{
var pivotName = nodeName.Split('.').First();
var pivotValue = nodeName.Split('.').Last();
var pivotType = ParserHelper.GetPivotTypeByName(pivotName);
// if (!pivotType.HasValue)
// return null;
var pivotData = new PivotData
{
PivotDefinition = ParserHelper.GetPivot(pivotType ?? PivotType.Transaction),
MainValue = pivotValue,
};
return(pivotData);
}
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 void updatePivotRotation(PivotData pd, bool lerp = true)
{
//vector from pivot to sun
Vector3 vector = pd.pivotTransform.InverseTransformPoint(sunTransform.position);
//finding angle to turn towards based on direction of vector on a single axis
float y = (float)SSTUUtils.toDegrees(Mathf.Atan2(vector.x, vector.z));
if (pd.pivotTransform.lossyScale.z < 0) { y = -y; }
if (lerp) // lerp towards destination rotation by trackingSpeed amount
{
Quaternion to = pd.pivotTransform.rotation * Quaternion.Euler(0f, y, 0f);
pd.pivotTransform.rotation = Quaternion.Lerp(pd.pivotTransform.rotation, to, TimeWarp.deltaTime * this.trackingSpeed);
}
else // or just set to the new rotation
{
Quaternion to = pd.pivotTransform.rotation * Quaternion.Euler(0f, y, 0f);
pd.pivotTransform.rotation = to;
}
}
private void findTransforms()
{
String[] suncatcherNames = SSTUUtils.parseCSV(rayTransforms);
String[] pivotNames = SSTUUtils.parseCSV(pivotTransforms);
String[] secPivotNames = SSTUUtils.parseCSV(secondaryPivotTransforms);
PivotData pd;
SuncatcherData sd;
int len2;
Transform[] trs;
String name;
List<PivotData> tempPivotData = new List<PivotData>();
int len = pivotNames.Length;
for (int i = 0; i < len; i++)
{
name = pivotNames[i];
if (String.IsNullOrEmpty(name)) { MonoBehaviour.print("ERROR: Empty name for solar pivot for part: " + part); }
trs = part.transform.FindChildren(name);
len2 = trs.Length;
if (len2 == 0) { MonoBehaviour.print("ERROR: Could not locate solar pivot transforms for name: "+name + " for part: " + part); }
for (int k = 0; k < len2; k++)
{
pd = new PivotData();
pd.pivotTransform = trs[k];
pd.defaultOrientation = pd.pivotTransform.localRotation;
tempPivotData.Add(pd);
}
}
pivotData = tempPivotData.ToArray();
tempPivotData.Clear();
len = secPivotNames.Length;
for (int i = 0; i < len; i++)
{
name = secPivotNames[i];
if (String.IsNullOrEmpty(name)) { continue; }
trs = part.transform.FindChildren(name);
len2 = trs.Length;
if (len2 == 0) { MonoBehaviour.print("ERROR: Could not locate secondary pivot transforms for name: " + name + " for part: " + part); }
for (int k = 0; k < len2; k++)
{
pd = new PivotData();
pd.pivotTransform = trs[k];
pd.defaultOrientation = pd.pivotTransform.localRotation;
tempPivotData.Add(pd);
}
}
secondaryPivotData = tempPivotData.ToArray();
tempPivotData.Clear();
List<SuncatcherData> tempSunData = new List<SuncatcherData>();
len = suncatcherNames.Length;
for (int i = 0; i < len; i++)
{
name = suncatcherNames[i];
if (String.IsNullOrEmpty(name)) { MonoBehaviour.print("ERROR: Empty name for suncatcher for part: "+ part); }
trs = part.transform.FindChildren(name);
len2 = trs.Length;
if (len2 == 0) { MonoBehaviour.print("ERROR: Could not locate suncatcher transforms for name: " + name+" for part: "+part); }
for (int k = 0; k < len2; k++)
{
sd = new SuncatcherData();
sd.suncatcherTransform = trs[k];
tempSunData.Add(sd);
}
}
suncatcherData = tempSunData.ToArray();
Transform t1;
if (windBreakTransformName != null && windBreakTransformName.Length > 0)
{
t1 = part.FindModelTransform(windBreakTransformName);
if (t1 != null)
{
windBreakTransform = t1;
}
}
if (windBreakTransform == null && pivotData.Length > 0)
{
windBreakTransform = pivotData[0].pivotTransform;
}//else it will use default vessel transform
}
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();
}
}