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);
}
public void GetData(int plant, string id, string curr)
{
this.curr = curr;
this.id = plant;
GetFiscal();
// get period details
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.CMSDAT);
for (int i = 0; i < 12; i++)
{
double value = 0.0;
// invoice
string query = "select coalesce(sum(dipric*(max(diqtso,diqtsp))), 0.0) from cmsdat.oih, cmsdat.oid where dhbcs#='" + id + "' and dhincr='I' and dhpost='Y' and dhinv#=diinv# and diglcd='SAL' and dhplnt='" + plant.ToString("D3") + "' and dharpr=" + fiscalPeriod[i].ToString() + " and dharyr=" + fiscalYear[i].ToString();
OdbcDataReader reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
}
reader.Close();
// credit
query = "select coalesce(sum(dipric*(max(diqtso,diqtsp))), 0.0) from cmsdat.oih, cmsdat.oid where dhbcs#='" + id + "' and dhincr='C' and dhpost='Y' and dhinv#=diinv# and diglcd='SAL' and dhplnt='" + plant.ToString("D3") + "' and dharpr=" + fiscalPeriod[i].ToString() + " and dharyr=" + fiscalYear[i].ToString();
reader = database.RunQuery(query);
if (reader.Read())
{
value -= Convert.ToDouble(reader[0]);
}
reader.Close();
// discount and fast track
query = "select coalesce(sum(fldext), 0.0) from cmsdat.oih, cmsdat.ois where dhbcs#='" + id + "' and dhpost='Y' and dhinv#=flinv# and (fldisc like 'D%' or fldisc like 'M%' or fldisc like 'F%') and dhplnt='" + plant.ToString("D3") + "' and dharpr=" + fiscalPeriod[i].ToString() + " and dharyr=" + fiscalYear[i].ToString();
reader = database.RunQuery(query);
if (reader.Read())
{
value += Convert.ToDouble(reader[0]);
}
reader.Close();
// net sale
ExcoCalendar calendar = new ExcoCalendar(year, i + 1, true, plant);
actual[i] = value;
actualTotal += actual[i];
actualTotalCAD += actual[i] * ExcoExRate.GetToCADRate(calendar, curr);
ExcoBudget excoBudget = ExcoBudget.Instance;
budget[i] = excoBudget.GetMonthlyBudget(id, plant);
budgetTotal += budget[i];
budgetTotalCAD += budget[i] * ExcoExRate.GetToCADRate(calendar, curr);
}
}
public void GetToCADRateTest()
{
ExcoCalendar calendar = new ExcoCalendar(12, 3, false, 1);
Assert.AreEqual(1.0, ExcoExRate.GetToCADRate(calendar, "CA"));
calendar.month = 2;
Assert.AreEqual(0.000558, ExcoExRate.GetToCADRate(calendar, "CP"));
calendar.year = 2012;
calendar.month = 11;
Assert.AreEqual(0.99, ExcoExRate.GetToCADRate(calendar, "US"));
// exception
try
{
ExcoExRate.GetToCADRate(calendar, "UaS");
}
catch (Exception e)
{
Assert.AreEqual("Invalid currency UaS", e.Message);
}
}
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);
}
