public ActionResult SaveExchange(string parameterStr)
{
parameterStr = parameterStr.Replace('x', '.');
string[] parameters = parameterStr.Split(new[] { "," }, StringSplitOptions.None);
if (parameters.Length > 1)
{
try
{
CurrencyYear refCY = FinancialControls.CurrencyYearList[Convert.ToInt32(parameters[0])];
double originalRate = refCY.ExchangeRates[Convert.ToInt32(parameters[1])];
// Update new rate
refCY.ExchangeRates[Convert.ToInt32(parameters[1])] = Convert.ToDouble(parameters[2]);
Log.Append(String.Format("Rate updated ({0} - {1}) : orig={2}, new={3}, by {4}", refCY.CurrencyType,
refCY.Year, originalRate, parameters[2], HttpContext.Session["Email"]));
ExcoExRate.SaveExchangeRates(FinancialControls.CurrencyYearList);
}
catch (Exception ex)
{
// Non-integer error
}
}
return(RedirectToAction("ExchangeRates", new { parameterStr = "ER" }));
}
public ActionResult AddExchange(string paramOne)
{
string[] parameters = paramOne.Split(new[] { "," }, StringSplitOptions.None);
if (parameters.Length == 2 && !FinancialControls.CurrencyYearList.Any(x =>
x.Year.ToString() == parameters[0] && ExcoExRate.GetCurrency(parameters[1]) == x.CurrencyType))
{
CurrencyYear newCY = new CurrencyYear(ExcoExRate.GetCurrency(parameters[1]),
Convert.ToInt32(parameters[0]));
// Instantiate 0 values for all 12 periods
for (int i = 0; i < 12; i++)
{
newCY.ExchangeRates.Add(0);
}
FinancialControls.CurrencyYearList.Add(newCY);
// Order by currency -> year
FinancialControls.CurrencyYearList = FinancialControls.CurrencyYearList.OrderBy(x => x.CurrencyType)
.ThenBy(y => y.Year).ToList();
ExcoExRate.SaveExchangeRates(FinancialControls.CurrencyYearList);
}
return(RedirectToAction("ExchangeRates"));
}
public ActionResult DeleteRate(string currYear)
{
FinancialControls.CurrencyYearList.RemoveAt(Convert.ToInt32(currYear));
// Order by currency -> year
FinancialControls.CurrencyYearList = FinancialControls.CurrencyYearList.OrderBy(x => x.CurrencyType)
.ThenBy(y => y.Year).ToList();
ExcoExRate.SaveExchangeRates(FinancialControls.CurrencyYearList);
return(RedirectToAction("ExchangeRates", new { paramOne = "ER" }));
}
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 buttonStart_Click(object sender, EventArgs e)
{
ExcoExRate.GetExchangeRatesFromFile();
// disable start
buttonStart.Enabled = false;
// enable stop
buttonStop.Enabled = true;
Thread thread = new Thread(Run);
thread.Start();
// make sure thread works
while (!thread.IsAlive)
{
;
}
}
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;
}
public ActionResult ExchangeRates(string paramOne)
{
FinancialControls.CurrencyYearList = ExcoExRate.GetExchangeRatesList();
return(View());
}
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);
}
public void PESOtoCADTest()
{
ExcoCalendar calendar = new ExcoCalendar(12, 1, false, 1);
// short year
Assert.AreEqual(0.000556, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 2;
Assert.AreEqual(0.000558, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 3;
Assert.AreEqual(0.000557, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 4;
Assert.AreEqual(0.000560, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 5;
Assert.AreEqual(0.000564, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 6;
Assert.AreEqual(0.000571, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 7;
Assert.AreEqual(0.000559, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 8;
Assert.AreEqual(0.000540, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 9;
Assert.AreEqual(0.000546, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 10;
Assert.AreEqual(0.000546, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 11;
Assert.AreEqual(0.000548, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 12;
Assert.AreEqual(0.000563, ExcoExRate.PESOtoCAD(calendar));
calendar.year = 13;
calendar.month = 1;
Assert.AreEqual(0.000562, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 2;
Assert.AreEqual(0.000567, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 3;
Assert.AreEqual(0.000556, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 4;
Assert.AreEqual(0.000551, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 5;
Assert.AreEqual(0.000543, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 6;
Assert.AreEqual(0.000550, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 7;
Assert.AreEqual(0.0, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 8;
Assert.AreEqual(0.0, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 9;
Assert.AreEqual(0.0, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 10;
Assert.AreEqual(0.0, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 11;
Assert.AreEqual(0.0, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 12;
Assert.AreEqual(0.0, ExcoExRate.PESOtoCAD(calendar));
// long year
calendar.year = 2012;
calendar.month = 1;
Assert.AreEqual(0.000556, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 2;
Assert.AreEqual(0.000558, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 3;
Assert.AreEqual(0.000557, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 4;
Assert.AreEqual(0.000560, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 5;
Assert.AreEqual(0.000564, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 6;
Assert.AreEqual(0.000571, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 7;
Assert.AreEqual(0.000559, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 8;
Assert.AreEqual(0.000540, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 9;
Assert.AreEqual(0.000546, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 10;
Assert.AreEqual(0.000546, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 11;
Assert.AreEqual(0.000548, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 12;
Assert.AreEqual(0.000563, ExcoExRate.PESOtoCAD(calendar));
calendar.year = 2013;
calendar.month = 1;
Assert.AreEqual(0.000562, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 2;
Assert.AreEqual(0.000567, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 3;
Assert.AreEqual(0.000556, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 4;
Assert.AreEqual(0.000551, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 5;
Assert.AreEqual(0.000543, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 6;
Assert.AreEqual(0.000550, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 7;
Assert.AreEqual(0.0, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 8;
Assert.AreEqual(0.0, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 9;
Assert.AreEqual(0.0, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 10;
Assert.AreEqual(0.0, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 11;
Assert.AreEqual(0.0, ExcoExRate.PESOtoCAD(calendar));
calendar.month = 12;
Assert.AreEqual(0.0, ExcoExRate.PESOtoCAD(calendar));
// exception
try
{
calendar.year = 11;
ExcoExRate.PESOtoCAD(calendar);
}
catch (Exception e)
{
Assert.AreEqual("Invalid year 11", e.Message);
}
try
{
calendar.year = 12;
calendar.month = 33;
ExcoExRate.PESOtoCAD(calendar);
}
catch (Exception e)
{
Assert.AreEqual("Invalid month 33", e.Message);
}
}
public void USDtoCADTest()
{
ExcoCalendar calendar = new ExcoCalendar(12, 1, false, 1);
// short year
Assert.AreEqual(1.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 2;
Assert.AreEqual(0.99, ExcoExRate.USDtoCAD(calendar));
calendar.month = 3;
Assert.AreEqual(1.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 4;
Assert.AreEqual(0.99, ExcoExRate.USDtoCAD(calendar));
calendar.month = 5;
Assert.AreEqual(1.03, ExcoExRate.USDtoCAD(calendar));
calendar.month = 6;
Assert.AreEqual(1.02, ExcoExRate.USDtoCAD(calendar));
calendar.month = 7;
Assert.AreEqual(1.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 8;
Assert.AreEqual(0.99, ExcoExRate.USDtoCAD(calendar));
calendar.month = 9;
Assert.AreEqual(0.98, ExcoExRate.USDtoCAD(calendar));
calendar.month = 10;
Assert.AreEqual(1.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 11;
Assert.AreEqual(0.99, ExcoExRate.USDtoCAD(calendar));
calendar.month = 12;
Assert.AreEqual(0.99, ExcoExRate.USDtoCAD(calendar));
calendar.year = 13;
calendar.month = 1;
Assert.AreEqual(1.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 2;
Assert.AreEqual(1.03, ExcoExRate.USDtoCAD(calendar));
calendar.month = 3;
Assert.AreEqual(1.02, ExcoExRate.USDtoCAD(calendar));
calendar.month = 4;
Assert.AreEqual(1.01, ExcoExRate.USDtoCAD(calendar));
calendar.month = 5;
Assert.AreEqual(1.04, ExcoExRate.USDtoCAD(calendar));
calendar.month = 6;
Assert.AreEqual(1.01, ExcoExRate.USDtoCAD(calendar));
calendar.month = 7;
Assert.AreEqual(0.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 8;
Assert.AreEqual(0.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 9;
Assert.AreEqual(0.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 10;
Assert.AreEqual(0.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 11;
Assert.AreEqual(0.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 12;
Assert.AreEqual(0.0, ExcoExRate.USDtoCAD(calendar));
// long year
calendar.year = 2012;
calendar.month = 1;
Assert.AreEqual(1.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 2;
Assert.AreEqual(0.99, ExcoExRate.USDtoCAD(calendar));
calendar.month = 3;
Assert.AreEqual(1.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 4;
Assert.AreEqual(0.99, ExcoExRate.USDtoCAD(calendar));
calendar.month = 5;
Assert.AreEqual(1.03, ExcoExRate.USDtoCAD(calendar));
calendar.month = 6;
Assert.AreEqual(1.02, ExcoExRate.USDtoCAD(calendar));
calendar.month = 7;
Assert.AreEqual(1.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 8;
Assert.AreEqual(0.99, ExcoExRate.USDtoCAD(calendar));
calendar.month = 9;
Assert.AreEqual(0.98, ExcoExRate.USDtoCAD(calendar));
calendar.month = 10;
Assert.AreEqual(1.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 11;
Assert.AreEqual(0.99, ExcoExRate.USDtoCAD(calendar));
calendar.month = 12;
Assert.AreEqual(0.99, ExcoExRate.USDtoCAD(calendar));
calendar.year = 2013;
calendar.month = 1;
Assert.AreEqual(1.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 2;
Assert.AreEqual(1.03, ExcoExRate.USDtoCAD(calendar));
calendar.month = 3;
Assert.AreEqual(1.02, ExcoExRate.USDtoCAD(calendar));
calendar.month = 4;
Assert.AreEqual(1.01, ExcoExRate.USDtoCAD(calendar));
calendar.month = 5;
Assert.AreEqual(1.04, ExcoExRate.USDtoCAD(calendar));
calendar.month = 6;
Assert.AreEqual(1.01, ExcoExRate.USDtoCAD(calendar));
calendar.month = 7;
Assert.AreEqual(0.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 8;
Assert.AreEqual(0.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 9;
Assert.AreEqual(0.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 10;
Assert.AreEqual(0.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 11;
Assert.AreEqual(0.0, ExcoExRate.USDtoCAD(calendar));
calendar.month = 12;
Assert.AreEqual(0.0, ExcoExRate.USDtoCAD(calendar));
// exception
try
{
calendar.year = 11;
ExcoExRate.USDtoCAD(calendar);
}
catch (Exception e)
{
Assert.AreEqual("Invalid year 11", e.Message);
}
try
{
calendar.year = 12;
calendar.month = 33;
ExcoExRate.USDtoCAD(calendar);
}
catch (Exception e)
{
Assert.AreEqual("Invalid month 33", e.Message);
}
}
// @note This is the main function of the working
// thread.
public Process(string filePath, string plant, int year, int period)
{
ExcoExRate.GetExchangeRatesList();
try
{
int plantID = 0;
string plantName = string.Empty;
if (plant == "Markham")
{
plantID = 1;
plantName = "Markham";
}
else if (plant == "Michigan")
{
plantID = 3;
plantName = "Michigan";
}
else if (plant == "Texas")
{
plantID = 5;
plantName = "Texas";
}
else if (plant == "Colombia")
{
plantID = 4;
plantName = "Colombia";
}
ExcoCalendar calendar = new ExcoCalendar(year, period, true, plantID);
ExcoCalendar tempCalendar = new ExcoCalendar(year, period, false, 1);
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.CMSDAT);
Dictionary <int, Account> accountMap = new Dictionary <int, Account>();
string query = string.Empty;
OdbcDataReader reader;
if (plantID == 1)
{
query = "select distinct a.aj4gl#1 as no1, a.aj4gl#2 as no2, b.aztitl as title, b.azatyp as account_type, (a.aj4tt" + calendar.GetFiscalMonth().ToString("D2") + "+aj4ob" + calendar.GetFiscalMonth().ToString("D2") + ") as balance from cmsdat.glmt as a, cmsdat.mast as b where a.aj4comp=1 and a.aj4ccyy=" + (calendar.GetFiscalYear()).ToString("D2") + " and a.aj4gl#1=b.azgl#1 and a.aj4gl#2=b.azgl#2 and b.azcomp=1 and a.aj4gl#1!=200";
reader = database.RunQuery(query);
while (reader.Read())
{
Account account = new Account();
account.glNum1 = Convert.ToInt32(reader["no1"]) % 100;
account.glNum2 = Convert.ToInt32(reader["no2"]);
account.title = reader["title"].ToString().Trim();
account.balanceAmount01 = Convert.ToDouble(reader["balance"]);
int key = account.glNum1 * 1000000 + account.glNum2;
if (99999999 == key)
{
continue;
}
if (accountMap.ContainsKey(key))
{
accountMap[key].balanceAmount01 = account.balanceAmount01;
}
else
{
accountMap.Add(key, account);
}
}
reader.Close();
}
else if (plantID == 3)
{
query = "select a.aj4gl#1 as no1, a.aj4gl#2 as no2, b.aztitl as title, b.azatyp as account_type, (a.aj4tt" + calendar.GetFiscalMonth().ToString("D2") + "+aj4ob" + calendar.GetFiscalMonth().ToString("D2") + ") as balance from cmsdat.glmt as a, cmsdat.mast as b where a.aj4comp=3 and a.aj4ccyy=" + (calendar.GetFiscalYear()).ToString("D2") + " and a.aj4gl#1=b.azgl#1 and a.aj4gl#2=b.azgl#2 and b.azcomp=3";
reader = database.RunQuery(query);
while (reader.Read())
{
Account account = new Account();
account.glNum1 = Convert.ToInt32(reader["no1"]) % 100;
account.glNum2 = Convert.ToInt32(reader["no2"]);
account.title = reader["title"].ToString().Trim();
account.balanceAmount03 = Convert.ToDouble(reader["balance"]);
int key = account.glNum1 * 1000000 + account.glNum2;
if (99999999 == key)
{
continue;
}
if (accountMap.ContainsKey(key))
{
accountMap[key].balanceAmount03 = account.balanceAmount03;
}
else
{
accountMap.Add(key, account);
}
}
reader.Close();
}
else if (plantID == 5)
{
query = "select a.aj4gl#1 as no1, a.aj4gl#2 as no2, b.aztitl as title, b.azatyp as account_type, (a.aj4tt" + calendar.GetFiscalMonth().ToString("D2") + "+aj4ob" + calendar.GetFiscalMonth().ToString("D2") + ") as balance from cmsdat.glmt as a, cmsdat.mast as b where a.aj4comp=5 and a.aj4ccyy=" + (calendar.GetFiscalYear()).ToString("D2") + " and a.aj4gl#1=b.azgl#1 and a.aj4gl#2=b.azgl#2 and b.azcomp=5";
reader = database.RunQuery(query);
while (reader.Read())
{
Account account = new Account();
account.glNum1 = Convert.ToInt32(reader["no1"]) % 100;
account.glNum2 = Convert.ToInt32(reader["no2"]);
account.title = reader["title"].ToString().Trim();
account.balanceAmount05 = Convert.ToDouble(reader["balance"]);
int key = account.glNum1 * 1000000 + account.glNum2;
if (99999999 == key)
{
continue;
}
if (accountMap.ContainsKey(key))
{
accountMap[key].balanceAmount05 = account.balanceAmount05;
}
else
{
accountMap.Add(key, account);
}
}
reader.Close();
}
else if (plantID == 4)
{
// plant 04
query = "select a.aj4gl#1 as no1, a.aj4gl#2 as no2, b.aztitl as title, b.azatyp as account_type, (a.aj4tt" + calendar.GetFiscalMonth().ToString("D2") + "+aj4ob" + calendar.GetFiscalMonth().ToString("D2") + ") as balance from cmsdat.glmt as a, cmsdat.mast as b where a.aj4comp=4 and a.aj4ccyy=" + (calendar.GetFiscalYear()).ToString("D2") + " and a.aj4gl#1=b.azgl#1 and a.aj4gl#2=b.azgl#2 and b.azcomp=4";
reader = database.RunQuery(query);
while (reader.Read())
{
Account account = new Account();
account.glNum1 = Convert.ToInt32(reader["no1"]) % 100;
account.glNum2 = Convert.ToInt32(reader["no2"]);
account.title = reader["title"].ToString().Trim();
account.balanceAmount04 = Convert.ToDouble(reader["balance"]);
int key = account.glNum1 * 1000000 + account.glNum2;
if (99999999 == key)
{
continue;
}
if (accountMap.ContainsKey(key))
{
accountMap[key].balanceAmount04 = account.balanceAmount04;
}
else
{
accountMap.Add(key, account);
}
}
reader.Close();
// plant 48
query = "select a.aj4gl#1 as no1, a.aj4gl#2 as no2, b.aztitl as title, b.azatyp as account_type, (a.aj4tt" + calendar.GetFiscalMonth().ToString("D2") + "+aj4ob" + calendar.GetFiscalMonth().ToString("D2") + ") as balance from cmsdat.glmt as a, cmsdat.mast as b where a.aj4comp=48 and a.aj4ccyy=" + (calendar.GetFiscalYear() + 2000).ToString("D2") + " and a.aj4gl#1=b.azgl#1 and a.aj4gl#2=b.azgl#2 and b.azcomp=48";
reader = database.RunQuery(query);
while (reader.Read())
{
Account account = new Account();
account.glNum1 = Convert.ToInt32(reader["no1"]) % 100;
account.glNum2 = Convert.ToInt32(reader["no2"]);
account.title = reader["title"].ToString().Trim();
account.balanceAmount48 = Convert.ToDouble(reader["balance"]);
int key = account.glNum1 * 1000000 + account.glNum2;
if (99999999 == key)
{
continue;
}
if (accountMap.ContainsKey(key))
{
accountMap[key].balanceAmount48 = account.balanceAmount48;
}
else
{
accountMap.Add(key, account);
}
}
reader.Close();
// plant 41
query = "select a.aj4gl#1 as no1, a.aj4gl#2 as no2, b.aztitl as title, b.azatyp as account_type, (a.aj4tt" + tempCalendar.GetFiscalMonth().ToString("D2") + "+aj4ob" + tempCalendar.GetFiscalMonth().ToString("D2") + ") as balance from cmsdat.glmt as a, cmsdat.mast as b where a.aj4comp=41 and a.aj4ccyy=" + (tempCalendar.GetFiscalYear() + 2000).ToString("D2") + " and a.aj4gl#1=b.azgl#1 and a.aj4gl#2=b.azgl#2 and b.azcomp=41";
reader = database.RunQuery(query);
while (reader.Read())
{
Account account = new Account();
account.glNum1 = Convert.ToInt32(reader["no1"]) % 100;
account.glNum2 = Convert.ToInt32(reader["no2"]);
account.title = reader["title"].ToString().Trim();
account.balanceAmount41 = Convert.ToDouble(reader["balance"]);
int key = account.glNum1 * 1000000 + account.glNum2;
if (99999999 == key)
{
continue;
}
if (accountMap.ContainsKey(key))
{
accountMap[key].balanceAmount41 = account.balanceAmount41;
}
else
{
accountMap.Add(key, account);
}
}
reader.Close();
// plant 49
query = "select a.aj4gl#1 as no1, a.aj4gl#2 as no2, b.aztitl as title, b.azatyp as account_type, (a.aj4tt" + tempCalendar.GetFiscalMonth().ToString("D2") + "+aj4ob" + tempCalendar.GetFiscalMonth().ToString("D2") + ") as balance from cmsdat.glmt as a, cmsdat.mast as b where a.aj4comp=49 and a.aj4ccyy=" + (tempCalendar.GetFiscalYear() + 2000).ToString("D2") + " and a.aj4gl#1=b.azgl#1 and a.aj4gl#2=b.azgl#2 and b.azcomp=49";
reader = database.RunQuery(query);
while (reader.Read())
{
Account account = new Account();
account.glNum1 = Convert.ToInt32(reader["no1"]) % 100;
account.glNum2 = Convert.ToInt32(reader["no2"]);
account.title = reader["title"].ToString().Trim();
account.balanceAmount49 = Convert.ToDouble(reader["balance"]);
int key = account.glNum1 * 1000000 + account.glNum2;
if (99999999 == key)
{
continue;
}
if (accountMap.ContainsKey(key))
{
accountMap[key].balanceAmount49 = account.balanceAmount49;
}
else
{
accountMap.Add(key, account);
}
}
reader.Close();
}
else
{
throw new Exception("None of plants has been selected!");
}
// write to excel
Excel2.Application excel = new Excel2.Application();
object misValue = System.Reflection.Missing.Value;
Excel2.Workbook book = excel.Workbooks.Add(misValue);
//Excel.Worksheet sheet = book.Worksheets[1];
Excel2.Worksheet sheet = book.Worksheets.Add();//[1];
//sheet.Delete();
//sheet = book.Worksheets[1];
//sheet.Delete();
//sheet = book.Worksheets[1];
sheet.Name = "Trial Balance for " + plantName;
// write title
sheet.Cells[1, 1] = plantName;
Excel2.Range range = sheet.get_Range("A1");
range.Font.Color = Color.Red;
range.Font.Size = 14;
sheet.Cells[2, 1] = "Balance Consolidations";
range = sheet.get_Range("A2");
range.Font.Size = 10;
range.Font.Bold = true;
sheet.Cells[3, 1] = "Generated at " + DateTime.Today.ToString("MMMM-dd-yyyy");
range = sheet.get_Range("A3");
range.Font.Size = 10;
range.Font.Bold = true;
sheet.Cells[6, 1] = "Ending Balance";
range = sheet.get_Range("A6");
range.Font.Size = 11;
range.Font.Color = Color.Red;
range.Font.Bold = true;
sheet.Cells[7, 1] = "Account";
range = sheet.get_Range("A7");
range.Font.Size = 11;
range.Cells.Borders[Excel2.XlBordersIndex.xlEdgeBottom].LineStyle = Excel2.XlLineStyle.xlContinuous;
sheet.Cells[5, 2] = "Company Number";
range = sheet.get_Range("B5");
range.Font.Size = 11;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
sheet.Cells[6, 2] = "Period";
range = sheet.get_Range("B6");
range.Font.Size = 11;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
sheet.Cells[7, 2] = "Account Name / Company";
range = sheet.get_Range("B7");
range.Font.Size = 11;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
if (4 != plantID)
{
sheet.Cells[5, 3] = plantID.ToString("D2");
range = sheet.get_Range("C5");
range.Font.Size = 11;
range.Font.Color = Color.Red;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
sheet.Cells[6, 3] = (calendar.GetFiscalYear() + 2000).ToString() + "-" + calendar.GetFiscalMonth().ToString("D2");
range = sheet.get_Range("C6");
range.Font.Size = 11;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
sheet.Cells[7, 3] = plantName;
range = sheet.get_Range("C7");
range.Font.Size = 11;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
}
else
{
// 04
sheet.Cells[5, 3] = "04";
range = sheet.get_Range("C5");
range.Font.Size = 11;
range.Font.Color = Color.Red;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
sheet.Cells[6, 3] = (calendar.GetFiscalYear() + 2000).ToString() + "-" + calendar.GetFiscalMonth().ToString("D2");
range = sheet.get_Range("C6");
range.Font.Size = 11;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
sheet.Cells[7, 3] = "Exco GAAP";
range = sheet.get_Range("C7");
range.Font.Size = 11;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
// 41
sheet.Cells[5, 4] = "41";
range = sheet.get_Range("D5");
range.Font.Size = 11;
range.Font.Color = Color.Red;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
sheet.Cells[6, 4] = (tempCalendar.GetFiscalYear() + 2000).ToString() + "-" + tempCalendar.GetFiscalMonth().ToString("D2");
range = sheet.get_Range("D6");
range.Font.Size = 11;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
sheet.Cells[7, 4] = "Exco IFRS";
range = sheet.get_Range("D7");
range.Font.Size = 11;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
// 48
sheet.Cells[5, 5] = "48";
range = sheet.get_Range("E5");
range.Font.Size = 11;
range.Font.Color = Color.Red;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
sheet.Cells[6, 5] = (calendar.GetFiscalYear() + 2000).ToString() + "-" + calendar.GetFiscalMonth().ToString("D2");
range = sheet.get_Range("E6");
range.Font.Size = 11;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
sheet.Cells[7, 5] = "Coltooling GAAP";
range = sheet.get_Range("E7");
range.Font.Size = 11;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
// 49
sheet.Cells[5, 6] = "49";
range = sheet.get_Range("F5");
range.Font.Size = 11;
range.Font.Color = Color.Red;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
sheet.Cells[6, 6] = (tempCalendar.GetFiscalYear() + 2000).ToString() + "-" + tempCalendar.GetFiscalMonth().ToString("D2");
range = sheet.get_Range("F6");
range.Font.Size = 11;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
sheet.Cells[7, 6] = "Coltooling IFRS";
range = sheet.get_Range("F7");
range.Font.Size = 11;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
// cosolidated
sheet.Cells[7, 7] = "Consolidated";
range = sheet.get_Range("G7");
range.Font.Size = 11;
range.Cells.Borders.LineStyle = Excel2.XlLineStyle.xlContinuous;
range.Cells.Interior.Color = Color.PeachPuff;
}
// write content
int row = 8;
var accountList = from account in accountMap.Values orderby account.glNum1 * 1000000 + account.glNum2 select account;
foreach (Account account in accountList)
{
sheet.Cells[row, 1] = account.glNum1.ToString("D2") + "-" + (account.glNum2 / 100).ToString("D4") + "-" + (account.glNum2 % 100).ToString("D2");
sheet.Cells[row, 2] = account.title;
if (1 == plantID)
{
sheet.Cells[row++, 3] = account.balanceAmount01.ToString("C2");
}
else if (3 == plantID)
{
sheet.Cells[row++, 3] = account.balanceAmount03.ToString("C2");
}
else if (5 == plantID)
{
sheet.Cells[row++, 3] = account.balanceAmount05.ToString("C2");
}
else if (4 == plantID)
{
sheet.Cells[row, 3] = account.balanceAmount04.ToString("C2");
sheet.Cells[row, 4] = account.balanceAmount41.ToString("C2");
sheet.Cells[row, 5] = account.balanceAmount48.ToString("C2");
sheet.Cells[row, 6] = account.balanceAmount49.ToString("C2");
range = sheet.get_Range("G" + row.ToString());
range.Formula = "=sum(C" + row.ToString() + ":F" + row.ToString() + ")";
row++;
}
}
// write to file
sheet.Cells.Columns.AutoFit();
sheet.Cells.HorizontalAlignment = Excel2.XlHAlign.xlHAlignCenter;
File.Delete(filePath);
book.SaveAs(filePath, Excel2.XlFileFormat.xlOpenXMLWorkbook);
excel.Quit();
}
catch (Exception e)
{
}
}
public void Generate(string path, string plant, string fiscalYear, string fiscalMonth)
{
ExcoExRate.GetExchangeRatesList();
string plant_name = plant;
this.plant = plant;
int workbook_number = 10;
if (plant == "Colombia")
{
workbook_number = 10;
plant_name = "Colombia";
currency_string = "PESO";
}
else if (plant == "Texas")
{
workbook_number = 8;
plant_name = "Texas";
currency_string = "USD";
}
else if (plant == "Michigan")
{
workbook_number = 6;
plant_name = "Michigan";
currency_string = "USD";
}
else if (plant == "Markham")
{
workbook_number = 4;
plant_name = "Markham";
currency_string = "CAD";
}
if (native_currency)
{
workbook_number--;
}
else
{
currency_string = "CAD";
}
for (int i = 0; i < 200; i++)
{
for (int j = 0; j < 20; j++)
{
WORKSHEET_ARRAY[i, j] = "0";
}
}
for (int i = 0; i < 12; i++)
{
for (int j = 0; j < 400; j++)
{
ANNUAL_DATA[i, j] = "";
ANNUAL_PERCENTAGE[i, j] = "";
}
}
try
{
//while (true)
//{
int month = Convert.ToInt32(fiscalMonth);
int year = Convert.ToInt32(fiscalYear) - 2000;
double current_period_exch_rate = 0;
for (int i = 1; i < 13; i++)
{
Log.Append(" Processing period: " + month.ToString() + "/" +
year
.ToString()); // + "C:\\Sales Report\\Income Statement Report at " + i.ToString() + "-15.xlsx");
int Worksheet_NO = 1;
string fileName =
@"\\10.0.0.8\EmailAPI\Financials\YTD-IS-Reports\Repository\Income Statement Report at " +
month.ToString() + "-" + year.ToString() + ".xlsx";
try
{
//foreach (var worksheet in Workbook.Worksheets(@"C:\Sales Report\Income Statement Report at " + i.ToString() + "-" + fiscalYear.ToString() + ".xlsx"))
foreach (var worksheet in Workbook.Worksheets(fileName))
{
int row_count = 0;
int column_count = 0;
if (worksheet.Rows.Length > 0 && worksheet.Rows[0].Cells[0].Text.Contains(plant_name) &&
(
native_currency && worksheet.Rows[0].Cells[0].Text.Contains(currency_string) ||
!native_currency && worksheet.Rows[0].Cells[0].Text.Contains("CAD")
))
{
foreach (var row in worksheet.Rows)
//for (int iz = 0; iz < 200; iz++)
{
row_count++;
column_count = 0;
foreach (var cell in row.Cells)
{
column_count++;
if (column_count < 4 || (i == 1 && (column_count == 6 || column_count == 7)) ||
((column_count == 6 || column_count == 10 ||
column_count == 11))) //name/ actual/ percentage
//if (column_count < 4 || (i == 1 && (column_count == 6 || column_count == 7)) || (i == 1 && (column_count == 10 || column_count == 11))) //name/ actual/ percentage
{
if (column_count < 4)
{
if (total_found_on_row == 0 && cell.Text.Contains("TOTAL") &&
!cell.Text.Contains("SALES") &&
!cell.Text.Contains("SURCHARGE"))
{
total_found_on_row = row_count;
}
double a;
double b;
if (double.TryParse(WORKSHEET_ARRAY[row_count, column_count], out a) &&
double.TryParse(cell.Text, out b))
{
/*
* a = Convert.ToDouble(
* WORKSHEET_ARRAY[row_count, column_count]);
* b = Convert.ToDouble(cell.Text);*/
if (row_count == 2) // Get exchange rate
{
ANNUAL_DATA[i - 1, row_count] =
cell.Value.Length > 4 ? cell.Value : cell.Value;
if (plant == "Colombia" && native_currency)
{
current_period_exch_rate = Convert.ToDouble(cell.Value);
}
else if (native_currency)
{
current_period_exch_rate = 1;
}
//Log.Append(" Current Exchange rate: " + current_period_exch_rate);
}
else if (column_count == 2)
{
ANNUAL_DATA[i - 1, row_count] = cell.Text;
if (month <= Convert.ToInt32(fiscal_month))
{
WORKSHEET_ARRAY[row_count, column_count] =
(a + b).ToString();
}
}
else
{
ANNUAL_PERCENTAGE[i - 1, row_count] =
(Convert.ToDouble(cell.Value)).ToString("P2");
}
}
else
{
WORKSHEET_ARRAY[row_count, column_count] = cell.Text;
}
}
double c;
// Store budget information for current period
if (i == 1 && column_count == 6 &&
double.TryParse(cell.Value.ToString(), out c))
{
BUDGET_CURRENT_PERIOD[0, row_count] = c.ToString();
}
if (i == 1 && column_count == 7 &&
double.TryParse(cell.Value.ToString(), out c))
{
BUDGET_CURRENT_PERIOD[1, row_count] = c.ToString("P2");
}
// Store Y-T-D budget information for current period
if (column_count == 6 && row_count > 4 &&
double.TryParse(cell.Value.ToString(), out c))
//if (column_count == 10 && row_count > 4)
//if (i == 1 && column_count == 10 && row_count > 4)
{
//YTD_BUDGET_CURRENT_PERIOD[0, row_count] = cell.Text;//add
//if (Convert.ToDouble(cell.Text) > 1)
if (month <= Convert.ToInt32(fiscal_month))
{
//YTD_BUDGET_CURRENT_PERIOD[0, row_count] = (Convert.ToDouble(YTD_BUDGET_CURRENT_PERIOD[0, row_count]) + (Convert.ToDouble(cell.Text) / current_period_exch_rate)).ToString(); // ADD
YTD_BUDGET_CURRENT_PERIOD[0, row_count] =
(Convert.ToDouble(YTD_BUDGET_CURRENT_PERIOD[0, row_count]) +
c).ToString(); // ADD
}
//if (row_count < 26)
//Log.Append(" Ongoing total: " + YTD_BUDGET_CURRENT_PERIOD[0, row_count]);
}
if (i == 1 && column_count == 11 && row_count > 4 &&
double.TryParse(cell.Value.ToString(), out c)) //get percentage
{
YTD_BUDGET_CURRENT_PERIOD[1, row_count] = c.ToString("P2");
}
}
}
}
}
Worksheet_NO++;
}
}
catch (Exception ex)
{
Log.Append("Error: " + ex.ToString());
}
// Adjust to last year
PERIOD_VALUES[i - 1] = month.ToString();
month--;
if (month == 0)
{
month = 12;
year--;
}
}
//}
Log.Append(" Summarizing information...");
Log.Append(" Generating Excel file...");
// EXCEL WRITE
workBook = excel.Workbooks.Add(misValue);
// create work sheets
//consolidatedIS = workBook.Worksheets[3];
consolidatedIS = workBook.Worksheets.Add();
consolidatedIS.Name = "12-month " + plant_name + " (" + currency_string + ")";
consolidatedIS.Columns[2].NumberFormat = "$#,##0.00;([Red]$#,##0.00)";
consolidatedIS.Columns[4].NumberFormat = "$#,##0.00;([Red]$#,##0.00)";
consolidatedIS.Columns[6].NumberFormat = "$#,##0.00;([Red]$#,##0.00)";
consolidatedIS.Columns[8].NumberFormat = "$#,##0.00;([Red]$#,##0.00)";
consolidatedIS.Columns[10].NumberFormat = "$#,##0.00;([Red]$#,##0.00)";
consolidatedIS.Columns[12].NumberFormat = "$#,##0.00;([Red]$#,##0.00)";
consolidatedIS.Columns[14].NumberFormat = "$#,##0.00;([Red]$#,##0.00)";
consolidatedIS.Columns[16].NumberFormat = "$#,##0.00;([Red]$#,##0.00)";
consolidatedIS.Columns[18].NumberFormat = "$#,##0.00;([Red]$#,##0.00)";
consolidatedIS.Columns[20].NumberFormat = "$#,##0.00;([Red]$#,##0.00)";
consolidatedIS.Columns[22].NumberFormat = "$#,##0.00;([Red]$#,##0.00)";
consolidatedIS.Columns[24].NumberFormat = "$#,##0.00;([Red]$#,##0.00)";
consolidatedIS.Columns[26].NumberFormat = "$#,##0.00;([Red]$#,##0.00)";
consolidatedIS.Columns[28].NumberFormat = "$#,##0.00;([Red]$#,##0.00)";
consolidatedIS.Columns[29].NumberFormat = "##0.00%;-##0.00%";
consolidatedIS.Columns[30].NumberFormat = "$#,##0.00;([Red]$#,##0.00)";
if (workbook_number == 8) // If colombia
{
consolidatedIS.Rows[3].NumberFormat = "0.000###";
}
else
{
consolidatedIS.Rows[3].NumberFormat = "0.###";
}
int excel_row = 1;
// title
excel_row = WriteSheetHeader(
"Annual Summarized Income Statement for " + plant_name + " in " + currency_string + " (" +
fiscal_year + ")", consolidatedIS);
// header
excel_row++;
excel_row = WriteHeader(consolidatedIS, excel_row);
excel_row++;
excel_row = WriteLines(consolidatedIS, excel_row);
excel_row++;
AdjustSheetStyle(consolidatedIS, excel_row);
// write to file
File.Delete(path);
workBook.SaveAs(path, ExcelWriter.XlFileFormat.xlOpenXMLWorkbook);
//workBook.Close();
//excel.Quit();
_MARSHAL_KILL_EXCEL(path);
}
catch
{
}
}
