public void ConstructorTest() { // cad amount Assert.AreEqual(1.0 * ExcoExRate.GetToCADRate(calendarCA, "CA"), moneyCA.amountCA); Assert.AreEqual(2.0 * ExcoExRate.GetToCADRate(calendarUS, "US"), moneyUS.amountCA); Assert.AreEqual(3.3 * ExcoExRate.GetToCADRate(calendarCP, "CP"), moneyCP.amountCA); // usd amount Assert.AreEqual(1.0 * ExcoExRate.GetToUSDRate(calendarCA, "CA"), moneyCA.amountUS); Assert.AreEqual(2.0 * ExcoExRate.GetToUSDRate(calendarUS, "US"), moneyUS.amountUS); Assert.AreEqual(3.3 * ExcoExRate.GetToUSDRate(calendarCP, "CP"), moneyCP.amountUS); // peso amount Assert.AreEqual(1.0 * ExcoExRate.GetToPESORate(calendarCA, "CA"), moneyCA.amountCP); Assert.AreEqual(2.0 * ExcoExRate.GetToPESORate(calendarUS, "US"), moneyUS.amountCP); Assert.AreEqual(3.3 * ExcoExRate.GetToPESORate(calendarCP, "CP"), moneyCP.amountCP); }
private void FillSheet(Excel.Worksheet sheet, int plant) { // insert title sheet.Cells[1, 1] = sheet.Name + " Sales Report (Without Surcharge)"; sheet.Cells.get_Range("A1").Font.Bold = true; sheet.Cells.get_Range("A1").Font.Size = 20; sheet.Cells.get_Range("A1").Font.ColorIndex = 3; sheet.Cells.get_Range("A1", "H1").Merge(); // build header int row = 3; int col = 1; sheet.Cells[row, col++] = "Cust #"; sheet.Cells[row, col++] = "Cust Name"; sheet.Cells[row, col++] = "Territory"; sheet.Cells[row, col++] = "Currency"; sheet.Cells[row, col++] = "2014 Budget"; sheet.Cells[row, col++] = "Yearly 2013"; sheet.Cells[row, col++] = "Jan"; sheet.Cells[row, col++] = "Feb"; sheet.Cells[row, col++] = "Mar"; sheet.Cells[row, col++] = "Apr"; sheet.Cells[row, col++] = "May"; sheet.Cells[row, col++] = "Jun"; sheet.Cells[row, col++] = "Jul"; sheet.Cells[row, col++] = "Aug"; sheet.Cells[row, col++] = "Sep"; sheet.Cells[row, col++] = "Oct"; sheet.Cells[row, col++] = "Nov"; sheet.Cells[row, col++] = "Dec"; Excel.Range headerRange = sheet.Cells.get_Range("A" + row.ToString(), "R" + row.ToString()); headerRange.Font.Bold = true; headerRange.Cells.Interior.ColorIndex = 40; headerRange.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; row++; // write content Plant caTotal = new Plant(); caTotal.curr = "CA"; Plant usTotal = new Plant(); usTotal.curr = "US"; Plant cpTotal = new Plant(); cpTotal.curr = "CP"; foreach (Customer cust in custList) { if ((1 == plant && 0.0 == cust.mar.actualTotal) || (3 == plant && 0.0 == cust.mic.actualTotal) || (5 == plant && 0.0 == cust.tex.actualTotal) || (4 == plant && 0.0 == cust.col.actualTotal)) { continue; } col = 1; sheet.Cells[row, col].NumberFormat = "@"; sheet.Cells[row, col++] = cust.id; sheet.Cells[row, col++] = cust.name; sheet.Cells[row, col++] = cust.terr; sheet.Cells[row, col++] = cust.curr; col++; if (1 == plant) { WritePlant(sheet, cust.mar, row, col); if (0 == cust.curr.CompareTo("CA")) { caTotal += cust.mar; } else if (0 == cust.curr.CompareTo("US")) { usTotal += cust.mar; } else if (0 == cust.curr.CompareTo("CP")) { cpTotal += cust.mar; } } else if (3 == plant) { WritePlant(sheet, cust.mic, row, col); if (0 == cust.curr.CompareTo("CA")) { caTotal += cust.mic; } else if (0 == cust.curr.CompareTo("US")) { usTotal += cust.mic; } else if (0 == cust.curr.CompareTo("CP")) { cpTotal += cust.mic; } } else if (5 == plant) { WritePlant(sheet, cust.tex, row, col); if (0 == cust.curr.CompareTo("CA")) { caTotal += cust.tex; } else if (0 == cust.curr.CompareTo("US")) { usTotal += cust.tex; } else if (0 == cust.curr.CompareTo("CP")) { cpTotal += cust.tex; } } else if (4 == plant) { WritePlant(sheet, cust.col, row, col); if (0 == cust.curr.CompareTo("CA")) { caTotal += cust.col; } else if (0 == cust.curr.CompareTo("US")) { usTotal += cust.col; } else if (0 == cust.curr.CompareTo("CP")) { cpTotal += cust.col; } } row += 2; } // write summary if (caTotal.actualTotal > 0.0) { WriteTotal(sheet, caTotal, row); row += 3; } if (usTotal.actualTotal > 0.0) { WriteTotal(sheet, usTotal, row); row += 3; } if (cpTotal.actualTotal > 0.0) { WriteTotal(sheet, cpTotal, row++); row += 3; } // write PESO summary if (4 == plant) { row++; col = 4; sheet.Cells[row, col++] = "Grand Total (CP):"; col++; // totals in peso double[] grandTotal = new double[13]; for (int i = 0; i < 13; i++) { grandTotal[i] = 0.0; } for (int i = 0; i < 12; i++) { ExcoCalendar calendar = new ExcoCalendar(13, i + 1, false, plant); grandTotal[i + 1] = ExcoExRate.GetToPESORate(calendar, "US") * usTotal.actual[i] + cpTotal.actual[i]; grandTotal[0] += grandTotal[i + 1]; } for (int i = 0; i < 13; i++) { sheet.Cells[row, col++] = grandTotal[i].ToString("C2"); } // summary style Excel.Range range = sheet.get_Range("A" + row.ToString(), "R" + row.ToString()); range.Font.Size = 9; range.Font.ColorIndex = 16; range.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; } // write CAD summary if (1 == plant) { row++; col = 4; sheet.Cells[row, col++] = "Grand Total (CA):"; col++; // totals in CAD double[] grandTotal = new double[13]; for (int i = 0; i < 13; i++) { grandTotal[i] = 0.0; } for (int i = 0; i < 12; i++) { ExcoCalendar calendar = new ExcoCalendar(13, i + 1, false, plant); grandTotal[i + 1] = ExcoExRate.GetToCADRate(calendar, "US") * usTotal.actual[i] + caTotal.actual[i]; grandTotal[0] += grandTotal[i + 1]; } for (int i = 0; i < 13; i++) { sheet.Cells[row, col++] = grandTotal[i].ToString("C2"); } // summary style Excel.Range range = sheet.get_Range("A" + row.ToString(), "R" + row.ToString()); range.Font.Size = 9; range.Font.ColorIndex = 16; range.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; } // adjust style sheet.Cells.Columns.AutoFit(); sheet.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; }
private double[] WriteToSalesTable(GridView gridView, Process process, string currency) { DataTable table = new DataTable(); table.Columns.Add("Cust#"); table.Columns.Add("Cust Name"); table.Columns.Add("Territory"); table.Columns.Add("Currency"); table.Columns.Add(" "); table.Columns.Add("Period01"); table.Columns.Add("Period02"); table.Columns.Add("Period03"); table.Columns.Add("Period04"); table.Columns.Add("Period05"); table.Columns.Add("Period06"); table.Columns.Add("Period07"); table.Columns.Add("Period08"); table.Columns.Add("Period09"); table.Columns.Add("Period10"); table.Columns.Add("Period11"); table.Columns.Add("Period12"); table.Columns.Add("Total"); List <ExcoMoney> actualSummary = new List <ExcoMoney>(); List <ExcoMoney> budgetSummary = new List <ExcoMoney>(); List <ExcoMoney> lastYearSummary = new List <ExcoMoney>(); for (int i = 0; i < 13; i++) { actualSummary.Add(new ExcoMoney()); budgetSummary.Add(new ExcoMoney()); lastYearSummary.Add(new ExcoMoney()); } // add sales data foreach (Customer cust in process.plant.custList) { if (cust.excoCustomer.Currency.Contains(currency) && (!cust.actualTotal.IsZero() || !cust.budgetTotal.IsZero())) { DataRow rowActual = table.NewRow(); rowActual["Cust#"] = cust.excoCustomer.BillToID; rowActual["Cust Name"] = cust.excoCustomer.Name; rowActual["Territory"] = cust.excoCustomer.Territory; rowActual["Currency"] = cust.excoCustomer.Currency; rowActual[" "] = "Actual:"; DataRow rowBudget = table.NewRow(); rowBudget[" "] = "Budget:"; DataRow rowLastYear = table.NewRow(); rowLastYear[" "] = "Last Year:"; for (int i = 1; i <= 12; i++) { double actual = cust.actualList[i].GetAmount(currency); double budget = cust.budgetList[i].GetAmount(currency); double lastYear = cust.actualListLastYear[i].GetAmount(currency); string index = "Period" + i.ToString("D2"); rowActual[index] = actual.ToString("C2"); actualSummary[i - 1] += cust.actualList[i]; rowBudget[index] = budget.ToString("C2"); budgetSummary[i - 1] += cust.budgetList[i]; rowLastYear[index] = lastYear.ToString("C2"); lastYearSummary[i - 1] += cust.actualListLastYear[i]; } rowActual["Total"] = cust.actualTotal.GetAmount(currency).ToString("C2"); actualSummary[12] += cust.actualTotal; rowBudget["Total"] = cust.budgetTotal.GetAmount(currency).ToString("C2"); budgetSummary[12] += cust.budgetTotal; rowLastYear["Total"] = cust.actualTotalLastYear.GetAmount(currency).ToString("C2"); lastYearSummary[12] += cust.actualTotalLastYear; table.Rows.Add(rowActual); table.Rows.Add(rowBudget); table.Rows.Add(rowLastYear); } } // add empty rows table.Rows.Add(table.NewRow()); table.Rows.Add(table.NewRow()); // add summary double[] summaryList = { 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0 }; DataRow summaryActual = table.NewRow(); DataRow summaryBudget = table.NewRow(); DataRow summaryLastYear = table.NewRow(); summaryActual["Currency"] = "TOTAL (" + currency + "):"; summaryActual[" "] = "Actual:"; summaryBudget[" "] = "Budget:"; summaryLastYear[" "] = "Last Year:"; for (int i = 0; i < 13; i++) { summaryActual[i + 5] = actualSummary[i].GetAmount(currency).ToString("C2"); summaryBudget[i + 5] = budgetSummary[i].GetAmount(currency).ToString("C2"); summaryLastYear[i + 5] = lastYearSummary[i].GetAmount(currency).ToString("C2"); if ((1 == plantID && currency.Contains("CA")) || (4 == plantID && currency.Contains("CP")) || 3 == plantID || 5 == plantID) { summaryList[i] += actualSummary[i].GetAmount(currency); } } table.Rows.Add(summaryActual); table.Rows.Add(summaryBudget); table.Rows.Add(summaryLastYear); // add currency conversion if (1 == plantID && currency.Contains("US")) { // add empty rows table.Rows.Add(table.NewRow()); table.Rows.Add(table.NewRow()); // add conversion rate DataRow rateRow = table.NewRow(); DataRow actualRow = table.NewRow(); DataRow budgetRow = table.NewRow(); DataRow lastYearRow = table.NewRow(); rateRow["Currency"] = "Convert to CAD"; rateRow[" "] = "Rate:"; actualRow[" "] = "Actual:"; budgetRow[" "] = "Budget:"; lastYearRow[" "] = "Last Year:"; for (int i = 1; i <= 12; i++) { rateRow["Period" + i.ToString("D2")] = ExcoExRate.GetToCADRate(new ExcoCalendar(14, i, true, 1), "US").ToString("F2"); actualRow["Period" + i.ToString("D2")] = actualSummary[i - 1].GetAmount("CA").ToString("C2"); budgetRow["Period" + i.ToString("D2")] = budgetSummary[i - 1].GetAmount("CA").ToString("C2"); lastYearRow["Period" + i.ToString("D2")] = lastYearSummary[i - 1].GetAmount("CA").ToString("C2"); summaryList[i - 1] += actualSummary[i - 1].GetAmount("CA"); } actualRow["Total"] = actualSummary[12].GetAmount("CA").ToString("C2"); summaryList[12] += actualSummary[12].GetAmount("CA"); budgetRow["Total"] = budgetSummary[12].GetAmount("CA").ToString("C2"); lastYearRow["Total"] = lastYearSummary[12].GetAmount("CA").ToString("C2"); table.Rows.Add(rateRow); table.Rows.Add(actualRow); table.Rows.Add(budgetRow); table.Rows.Add(lastYearRow); } if (4 == plantID && currency.Contains("US")) { // add empty rows table.Rows.Add(table.NewRow()); table.Rows.Add(table.NewRow()); // add conversion rate DataRow rateRow = table.NewRow(); DataRow actualRow = table.NewRow(); DataRow budgetRow = table.NewRow(); DataRow lastYearRow = table.NewRow(); rateRow["Currency"] = "Convert to PESO"; rateRow[" "] = "Rate:"; actualRow[" "] = "Actual:"; budgetRow[" "] = "Budget:"; for (int i = 1; i <= 12; i++) { if (i <= 3) { rateRow["Period" + i.ToString("D2")] = ExcoExRate.GetToPESORate(new ExcoCalendar(13, i + 9, true, 4), "US").ToString("F2"); } else { rateRow["Period" + i.ToString("D2")] = ExcoExRate.GetToPESORate(new ExcoCalendar(14, i - 3, true, 4), "US").ToString("F2"); } actualRow["Period" + i.ToString("D2")] = actualSummary[i - 1].GetAmount("CP").ToString("C2"); summaryList[i - 1] += actualSummary[i - 1].GetAmount("CP"); budgetRow["Period" + i.ToString("D2")] = budgetSummary[i - 1].GetAmount("CP").ToString("C2"); lastYearRow["Period" + i.ToString("D2")] = lastYearSummary[i - 1].GetAmount("CP").ToString("C2"); } actualRow["Total"] = actualSummary[12].GetAmount("CP").ToString("C2"); summaryList[12] += actualSummary[12].GetAmount("CP"); budgetRow["Total"] = budgetSummary[12].GetAmount("CP").ToString("C2"); lastYearRow["Total"] = lastYearSummary[12].GetAmount("CP").ToString("C2"); table.Rows.Add(rateRow); table.Rows.Add(actualRow); table.Rows.Add(budgetRow); table.Rows.Add(lastYearRow); } // write to grid view table.AcceptChanges(); gridView.DataSource = table; gridView.DataBind(); // adjust style foreach (GridViewRow row in gridView.Rows) { Style style = new Style(); style.ForeColor = Color.Gray; style.Font.Size = 9; // actual row if (row.Cells[4].Text.Contains("Actual") || row.Cells[4].Text.Contains("Rate")) { row.ForeColor = Color.Black; row.Font.Bold = true; row.Font.Size = 12; row.Cells[4].ForeColor = Color.Gray; row.Cells[4].Font.Size = 9; } // budget/last year row else { row.ApplyStyle(style); } } return(summaryList); }