public void IsZeroTest()
{
ExcoMoney result = new ExcoMoney();
Assert.IsFalse(moneyCA.IsZero());
Assert.IsFalse(moneyUS.IsZero());
Assert.IsFalse(moneyCP.IsZero());
Assert.IsTrue(result.IsZero());
}
public void OperatorPlusTest()
{
ExcoMoney result = new ExcoMoney();
result = moneyCA + moneyCP + moneyUS;
Assert.AreEqual(moneyCA.amountCA + moneyCP.amountCA + moneyUS.amountCA, result.amountCA);
Assert.AreEqual(moneyCA.amountUS + moneyCP.amountUS + moneyUS.amountUS, result.amountUS);
Assert.AreEqual(moneyCA.amountCP + moneyCP.amountCP + moneyUS.amountCP, result.amountCP);
}
// default constructor
public ExcoCategory()
{
// initialize
for (int i = 0; i < 12; i++)
{
actualThisYear[i] = new ExcoMoney();
actualRatioThisYear[i] = 0.0;
budgetThisYear[i] = new ExcoMoney();
budgetRatioThisYear[i] = 0.0;
actualLastYear[i] = new ExcoMoney();
actualRatioLastYear[i] = 0.0;
}
}
// default constructor
public ExcoGroup(string sGroupName)
{
this.sGroupName = sGroupName;
for (int i = 0; i < 12; i++)
{
actualThisYear[i] = new ExcoMoney();
actualRatioThisYear[i] = 0.0;
budgetThisYear[i] = new ExcoMoney();
budgetRatioThisYear[i] = 0.0;
actualLastYear[i] = new ExcoMoney();
actualRatioLastYear[i] = 0.0;
}
}
public void GetInfo(ExcoCalendar calendar)
{
// fiscal period
string fp = calendar.GetFiscalMonth().ToString("D2");
// fiscal year
string fy = calendar.GetFiscalYear().ToString("D2");
if (name.Length > 0)
{
ExcoODBC solarsoft = ExcoODBC.Instance;
solarsoft.Open(Database.CMSDAT);
string query = "select coalesce(sum(aj4tt" + fp + "), 0.0), coalesce(sum(aj4cb" + fp + "), 0.0), coalesce(sum(";
int iPeriod = Convert.ToInt32(fp);
while (iPeriod > 0)
{
query += "aj4tt" + iPeriod.ToString("D2") + "+aj4ob" + iPeriod.ToString("D2");;
iPeriod--;
if (iPeriod > 0)
{
query += "+";
}
else
{
query += "), 0.0), coalesce(sum(";
}
}
iPeriod = Convert.ToInt32(fp);
while (iPeriod > 0)
{
query += "aj4cb" + iPeriod.ToString("D2");
iPeriod--;
if (iPeriod > 0)
{
query += "+";
}
else
{
query += "), 0.0) from cmsdat.glmt left join cmsdat.mast on aj4comp=" + plant + " and azcomp=aj4comp and azgl#1=aj4gl#1 and aj4gl#2=azgl#2 and aj4ccyy=20" + fy + " where azgrop like '" + groupID + "%'";
}
}
OdbcDataReader reader = solarsoft.RunQuery(query);
if (reader.Read())
{
tpActual = new ExcoMoney(calendar, Convert.ToDouble(reader[0]), currency);
tpBudget = new ExcoMoney(calendar, Convert.ToDouble(reader[1]), currency);
ytdActual = new ExcoMoney(calendar, Convert.ToDouble(reader[2]), currency);
ytdBudget = new ExcoMoney(calendar, Convert.ToDouble(reader[3]), currency);
}
reader.Close();
}
}
private void GetAccountsDataFiscalPlants()
{
// this year
ExcoODBC solarsoft = ExcoODBC.Instance;
solarsoft.Open(Database.CMSDAT);
string query = "select sum(coalesce(aj4tt01,0.0)) as actual01, sum(coalesce(aj4tt02,0.0)) as actual02, sum(coalesce(aj4tt03,0.0)) as actual03, sum(coalesce(aj4tt04,0.0)) as actual04, sum(coalesce(aj4tt05,0.0)) as actual05, sum(coalesce(aj4tt06,0.0)) as actual06, sum(coalesce(aj4tt07,0.0)) as actual07, sum(coalesce(aj4tt08,0.0)) as actual08, sum(coalesce(aj4tt09,0.0)) as actual09, sum(coalesce(aj4tt10,0.0)) as actual10, sum(coalesce(aj4tt11,0.0)) as actual11, sum(coalesce(aj4tt12,0.0)) as actual12, sum(coalesce(aj4cb01,0.0)) as budget01, sum(coalesce(aj4cb02,0.0)) as budget02, sum(coalesce(aj4cb03,0.0)) as budget03, sum(coalesce(aj4cb04,0.0)) as budget04, sum(coalesce(aj4cb05,0.0)) as budget05, sum(coalesce(aj4cb06,0.0)) as budget06, sum(coalesce(aj4cb07,0.0)) as budget07, sum(coalesce(aj4cb08,0.0)) as budget08, sum(coalesce(aj4cb09,0.0)) as budget09, sum(coalesce(aj4cb10,0.0)) as budget10, sum(coalesce(aj4cb11,0.0)) as budget11, sum(coalesce(aj4cb12,0.0)) as budget12 from cmsdat.glmt where aj4comp=" + plantID + " and aj4gl#1=" + accountList[0].glNo1 + " and (";
for (int i = 0; i < accountList.Count; i++)
{
query += "aj4gl#2=" + accountList[i].glNo2;
if (i < accountList.Count - 1)
{
query += " or ";
}
}
query += ") and aj4ccyy=20";
OdbcDataReader reader = solarsoft.RunQuery(query + calendar.GetFiscalYear());
if (reader.Read() && !reader.IsDBNull(0))
{
for (int i = 1; i <= 12; i++)
{
ExcoCalendar tempCalendar = new ExcoCalendar(calendar.GetFiscalYear(), i, true, plantID);
actualThisYearList[i] = new ExcoMoney(tempCalendar.GetNextCalendarMonth(), Convert.ToDouble(reader["actual" + i.ToString("D2")]) * -1.0, currency);
budgetThisYearList[i] = new ExcoMoney(tempCalendar.GetNextCalendarMonth(), Convert.ToDouble(reader["budget" + i.ToString("D2")]) * -1.0, currency);
}
}
reader.Close();
// last year
reader = solarsoft.RunQuery(query + calendar.GetFiscalYear() + "- 1");
if (reader.Read() && !reader.IsDBNull(0))
{
for (int i = 1; i <= 12; i++)
{
ExcoCalendar tempCalendar = new ExcoCalendar(calendar.GetFiscalYear() - 1, i, true, plantID);
actualLastYearList[i] = new ExcoMoney(tempCalendar.GetNextCalendarMonth(), Convert.ToDouble(reader["actual" + i.ToString("D2")]) * -1.0, currency);
}
}
reader.Close();
}
public void Run(int plantID)
{
switch (plantID)
{
case 1:
plant = new Plant(plantID, "CA");
break;
case 3:
case 5:
plant = new Plant(plantID, "US");
break;
case 4:
plant = new Plant(plantID, "CP");
break;
default:
throw new Exception("Invalid plant ID: " + plantID.ToString());
}
ExcoCalendar calendar = new ExcoCalendar(DateTime.Today.Year - 2000, DateTime.Today.Month, false, plantID);
// get customer, currency, territory, plant and year/period
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.CMSDAT);
string query = "select dhinv#, dhbcs#, dhscs#, dhcurr, dhterr, dharyr, dharpr, dhord# from cmsdat.oih where dhpost='Y' and dharyr>=" + (calendar.GetFiscalYear() - 2).ToString() + " and dhplnt=" + plantID.ToString();
OdbcDataReader reader = database.RunQuery(query);
while (reader.Read())
{
int invNum = Convert.ToInt32(reader["dhinv#"]);
int soNum = Convert.ToInt32(reader["dhord#"]);
string billTo = reader["dhbcs#"].ToString().Trim();
string shipTo = reader["dhscs#"].ToString().Trim();
string currency = reader["dhcurr"].ToString().Trim();
string territory = reader["dhterr"].ToString().Trim();
int invoiceYear = Convert.ToInt32(reader["dharyr"]);
int invoicePeriod = Convert.ToInt32(reader["dharpr"]);
ExcoCustomer customer;
if (customerBillToMap.ContainsKey(billTo))
{
customer = customerBillToMap[billTo];
}
else if (customerShipToMap.ContainsKey(billTo))
{
customer = customerShipToMap[billTo];
}
else if (customerBillToMap.ContainsKey(shipTo))
{
customer = customerBillToMap[shipTo];
}
else if (customerShipToMap.ContainsKey(shipTo))
{
customer = customerShipToMap[shipTo];
}
else
{
throw new Exception("Unknown customer: " + billTo + " " + shipTo + " invoice#:" + invNum.ToString());
}
Invoice invoice = new Invoice();
invoice.orderNum = soNum;
invoice.invoiceNum = invNum;
invoice.customer = customer;
invoice.currency = currency;
invoice.territory = territory;
invoice.plant = plantID;
invoice.calendar = new ExcoCalendar(invoiceYear, invoicePeriod, true, plantID);
invoiceMap.Add(invNum, invoice);
}
reader.Close();
// get sales
query = "select diinv#, sum(dipric*diqtsp) as sale from cmsdat.oid where diglcd='SAL' group by diinv#";
reader = database.RunQuery(query);
while (reader.Read())
{
int invNum = Convert.ToInt32(reader[0]);
if (invoiceMap.ContainsKey(invNum))
{
invoiceMap[invNum].sale += Convert.ToDouble(reader
[1]);
}
}
reader.Close();
// get discount
query = "select flinv#, coalesce(sum(fldext), 0.0) from cmsdat.ois where fldisc like 'D%' or fldisc like 'M%' group by flinv#";
reader = database.RunQuery(query);
while (reader.Read())
{
int invNum = Convert.ToInt32(reader[0]);
if (invoiceMap.ContainsKey(invNum))
{
invoiceMap[invNum].discount += Convert.ToDouble(reader
[1]);
}
}
reader.Close();
// get fast track
query = "select flinv#, coalesce(sum(fldext), 0.0) from cmsdat.ois where fldisc like 'F%' group by flinv#";
reader = database.RunQuery(query);
while (reader.Read())
{
int invNum = Convert.ToInt32(reader[0]);
if (invoiceMap.ContainsKey(invNum))
{
invoiceMap[invNum].fastTrack += Convert.ToDouble(reader[1]);
}
}
reader.Close();
// get steel surcharge
query = "select flinv#, coalesce(sum(fldext), 0.0) from cmsdat.ois where fldisc like 'S%' or fldisc like 'P%' group by flinv#";
reader = database.RunQuery(query);
while (reader.Read())
{
int invNum = Convert.ToInt32(reader[0]);
if (invoiceMap.ContainsKey(invNum))
{
invoiceMap[invNum].surcharge += Convert.ToDouble(reader[1]);
}
}
reader.Close();
// get credit
query = "select dhinv#, coalesce(dipric*(max(diqtso,diqtsp)), 0.0) from cmsdat.oih, cmsdat.oid where dhincr='C' and dhpost='Y' and dhinv#=diinv# and diglcd='SAL'";
reader = database.RunQuery(query);
while (reader.Read())
{
int invNum = Convert.ToInt32(reader[0]);
if (invoiceMap.ContainsKey(invNum))
{
invoiceMap[invNum].credit += Convert.ToDouble(reader[1]);
}
}
reader.Close();
// get budgets
database.Open(Database.DECADE_MARKHAM);
switch (plantID)
{
case 1:
query = "select CustomerID, Currency, Period01, Period02, Period03, Period04, Period05, Period06, Period07, Period08, Period09, Period10, Period11, Period12 from tiger.dbo.Markham_Budget where Year=20" + fiscalYear.ToString();
break;
case 4:
query = "select CustomerID, Currency, Period01, Period02, Period03, Period04, Period05, Period06, Period07, Period08, Period09, Period10, Period11, Period12 from tiger.dbo.Colombia_Budget where Year=20" + fiscalYear.ToString();
break;
case 3:
query = "select CustomerID, Currency, Period01, Period02, Period03, Period04, Period05, Period06, Period07, Period08, Period09, Period10, Period11, Period12 from tiger.dbo.Michigan_Budget where Year=20" + fiscalYear.ToString();
break;
case 5:
query = "select CustomerID, Currency, Period01, Period02, Period03, Period04, Period05, Period06, Period07, Period08, Period09, Period10, Period11, Period12 from tiger.dbo.Texas_Budget where Year=20" + fiscalYear.ToString();
break;
}
reader = database.RunQuery(query);
while (reader.Read())
{
string currency = reader[1].ToString();
List <ExcoMoney> budgetList = new List <ExcoMoney>();
for (int i = 0; i < 12; i++)
{
ExcoMoney budget = new ExcoMoney(new ExcoCalendar(fiscalYear, i + 1, true, 1), Convert.ToDouble(reader[i + 2]), currency);
budgetList.Add(budget);
}
budgetMap.Add(reader[0].ToString().Trim(), budgetList);
}
reader.Close();
// get parts
database.Open(Database.CMSDAT);
query = "select diord#, dimajr, didesc, dipart from cmsdat.oid where distkl='" + plantID.ToString("D3") + "PRD'";
reader = database.RunQuery(query);
while (reader.Read())
{
int soNum = Convert.ToInt32(reader["diord#"]);
string part = reader["dipart"].ToString().Trim();
string desc = reader["didesc"].ToString().Trim();
string type = reader["dimajr"].ToString().Trim();
if (desc.Contains("NCR ") || part.Contains("NCR "))
{
type = "NCR";
}
if (partMap.ContainsKey(soNum))
{
partMap[soNum].Add(type);
}
else
{
List <string> typeList = new List <string>();
typeList.Add(type);
partMap.Add(soNum, typeList);
}
}
reader.Close();
// build plant data structure
foreach (ExcoCustomer customer in customerBillToMap.Values)
{
plant.AddCustomer(customer, invoiceMap, fiscalYear, doesIncludeSurcharge, budgetMap, partMap);
}
plant.GetSurcharge();
plant.CountParts();
}
public void GetData(Dictionary <int, Invoice> invoiceMap, int fiscalYear, bool doesIncludeSurcharge, Dictionary <string, List <ExcoMoney> > budgetMap, Dictionary <int, List <string> > partsMap)
{
// get actual invoice amount
foreach (Invoice invoice in invoiceMap.Values)
{
if (invoice.plant == plantID)
{
if (invoice.customer.ShipToID.Contains(excoCustomer.ShipToID) || invoice.customer.ShipToID.Contains(excoCustomer.BillToID) || invoice.customer.ShipToID.Contains(excoCustomer.SillyID) || invoice.customer.BillToID.Contains(excoCustomer.ShipToID) || invoice.customer.BillToID.Contains(excoCustomer.BillToID) || invoice.customer.BillToID.Contains(excoCustomer.SillyID) || invoice.customer.SillyID.Contains(excoCustomer.ShipToID) || invoice.customer.SillyID.Contains(excoCustomer.BillToID) || invoice.customer.SillyID.Contains(excoCustomer.SillyID))
{
int month = invoice.calendar.GetFiscalMonth();
if (4 != plantID)
{
// build actual list this year
if (invoice.calendar.GetFiscalYear() == fiscalYear)
{
actualList[month] += invoice.GetAmount(doesIncludeSurcharge);
actualTotal += invoice.GetAmount(doesIncludeSurcharge);
surchargeList[month] += new ExcoMoney(invoice.calendar, invoice.surcharge, invoice.currency);
// count parts
if (partsMap.ContainsKey(invoice.orderNum))
{
foreach (string part in partsMap[invoice.orderNum])
{
if (0 == part.CompareTo("NCR"))
{
ncrList[month]++;
break;
}
else if (part.Contains("HD") || part.Contains("HU"))
{
hollowList[month]++;
break;
}
else if (part.Contains("BA") || part.Contains("SD") || part.Contains("BO") || part.Contains("FD") || part.Contains("RI") || part.Contains("SO") || part.Contains("BK"))
{
solidList[month]++;
break;
}
}
}
}
// build actual list last year
else if (invoice.calendar.GetFiscalYear() == fiscalYear - 1)
{
actualListLastYear[month] += invoice.GetAmount(doesIncludeSurcharge);
actualTotalLastYear += invoice.GetAmount(doesIncludeSurcharge);
}
}
else
{
// build actual list this year
if (invoice.calendar.GetFiscalYear() == fiscalYear && invoice.calendar.GetFiscalMonth() < 10)
{
actualList[month + 3] += invoice.GetAmount(doesIncludeSurcharge);
surchargeList[month + 3] += new ExcoMoney(invoice.calendar, invoice.surcharge, invoice.currency);
actualTotal += invoice.GetAmount(doesIncludeSurcharge);
// count parts
if (partsMap.ContainsKey(invoice.orderNum))
{
foreach (string part in partsMap[invoice.orderNum])
{
if (0 == part.CompareTo("NCR"))
{
ncrList[month]++;
}
else if (part.Contains("HD") || part.Contains("HU"))
{
hollowList[month]++;
}
else if (part.Contains("BA") || part.Contains("SD") || part.Contains("BO") || part.Contains("FD") || part.Contains("RI")) //|| part.Contains("SO") || part.Contains("BK"))
{
solidList[month]++;
}
}
}
}
else if (invoice.calendar.GetFiscalYear() == fiscalYear - 1 && invoice.calendar.GetFiscalMonth() > 9)
{
actualList[month - 9] += invoice.GetAmount(doesIncludeSurcharge);
surchargeList[month - 9] += new ExcoMoney(invoice.calendar, invoice.surcharge, invoice.currency);
actualTotal += invoice.GetAmount(doesIncludeSurcharge);
// count parts
if (partsMap.ContainsKey(invoice.orderNum))
{
foreach (string part in partsMap[invoice.orderNum])
{
if (0 == part.CompareTo("NCR"))
{
ncrList[month - 9]++;
}
else if (part.Contains("HD") || part.Contains("HU"))
{
hollowList[month - 9]++;
}
else if (part.Contains("BA") || part.Contains("SD") || part.Contains("BO") || part.Contains("FD") || part.Contains("RI")) //|| part.Contains("SO") || part.Contains("BK"))
{
solidList[month - 9]++;
}
}
}
}
// build actual list last year
else if (invoice.calendar.GetFiscalYear() == fiscalYear - 1 && invoice.calendar.GetFiscalMonth() < 10)
{
actualListLastYear[month + 3] += invoice.GetAmount(doesIncludeSurcharge);
actualTotalLastYear += invoice.GetAmount(doesIncludeSurcharge);
}
else if (invoice.calendar.GetFiscalYear() == fiscalYear - 2 && invoice.calendar.GetFiscalMonth() > 9)
{
actualListLastYear[month - 9] += invoice.GetAmount(doesIncludeSurcharge);
actualTotalLastYear += invoice.GetAmount(doesIncludeSurcharge);
}
}
}
}
}
for (int i = 0; i < 12; i++)
{
// get budget data this year
ExcoCalendar calendar = new ExcoCalendar(fiscalYear, i + 1, true, plantID);
ExcoMoney budget = new ExcoMoney();
if (budgetMap.ContainsKey(excoCustomer.ShipToID))
{
budget = budgetMap[excoCustomer.ShipToID][i];
}
else if (budgetMap.ContainsKey(excoCustomer.BillToID))
{
budget = budgetMap[excoCustomer.BillToID][i];
}
else if (budgetMap.ContainsKey(excoCustomer.SillyID))
{
budget = budgetMap[excoCustomer.SillyID][i];
}
budgetTotal += budget;
budgetList[i + 1] = budget;
}
}
private void GetAccountsDataCalendarColombiaPlants()
{
// calendar period
int cp = calendar.GetCalendarMonth();
int cy = calendar.GetCalendarYear();
//ExcoCalendar ttt = new ExcoCalendar(calendar.GetCalendarYear(), calendar.GetCalendarMonth(), true, 1);
//int cp = ttt.GetCalendarMonth();
//int cy = ttt.GetCalendarYear();
bool test = false;
// this year
ExcoODBC solarsoft = ExcoODBC.Instance;
solarsoft.Open(Database.CMSDAT);
//if (cp > 9)
//{
string query = "select sum(coalesce(aj4tt10+aj4ob10,0.0)) as actual10, sum(coalesce(aj4tt11+aj4ob11,0.0)) as actual11, sum(coalesce(aj4tt12+aj4ob12,0.0)) as actual12, sum(coalesce(aj4cb10,0.0)) as budget10, sum(coalesce(aj4cb11,0.0)) as budget11, sum(coalesce(aj4cb12,0.0)) as budget12 from cmsdat.glmt where aj4comp=" + plantID + " and ((";
for (int i = 0; i < accountList.Count; i++)
{
if (accountList[i].glNo2.Contains("206010"))
{
test = true;
}
query += "aj4gl#1=" + accountList[i].glNo1 + " and aj4gl#2=" + accountList[i].glNo2 + ")";
if (i < accountList.Count - 1)
{
query += " or (";
}
}
query += ") and aj4ccyy=20";
OdbcDataReader reader = solarsoft.RunQuery(query + (cy - 1));
//string g = (query) + (cy - 1);
//Console.Write(g);
if (reader.Read() && !reader.IsDBNull(0))
{
for (int i = 10; i <= 12; i++)
{
//if (test)
// Console.Write("!!");
ExcoCalendar tempCalendar = new ExcoCalendar(cy - 1, i, true, plantID);
actualThisYearList[i - 9] = new ExcoMoney(tempCalendar.GetNextCalendarMonth(), Convert.ToDouble(reader["actual" + i.ToString("D2")]) * -1.0, currency);
budgetThisYearList[i - 9] = new ExcoMoney(tempCalendar.GetNextCalendarMonth(), Convert.ToDouble(reader["budget" + i.ToString("D2")]) * -1.0, currency);
string gd = Convert.ToDouble(reader["actual" + i.ToString("D2")]).ToString();
string ggd = Convert.ToDouble(reader["actual" + i.ToString("D2")]).ToString();
//string pp = gd + ggd;
//Console.Write(pp);
}
}
reader.Close();
query = "select sum(coalesce(aj4tt01+aj4ob01,0.0)) as actual01, sum(coalesce(aj4tt02+aj4ob02,0.0)) as actual02, sum(coalesce(aj4tt03+aj4ob03,0.0)) as actual03, sum(coalesce(aj4tt04+aj4ob04,0.0)) as actual04, sum(coalesce(aj4tt05+aj4ob05,0.0)) as actual05, sum(coalesce(aj4tt06+aj4ob06,0.0)) as actual06, sum(coalesce(aj4tt07+aj4ob07,0.0)) as actual07, sum(coalesce(aj4tt08+aj4ob08,0.0)) as actual08, sum(coalesce(aj4tt09+aj4ob09,0.0)) as actual09, sum(coalesce(aj4cb01,0.0)) as budget01, sum(coalesce(aj4cb02,0.0)) as budget02, sum(coalesce(aj4cb03,0.0)) as budget03, sum(coalesce(aj4cb04,0.0)) as budget04, sum(coalesce(aj4cb05,0.0)) as budget05, sum(coalesce(aj4cb06,0.0)) as budget06, sum(coalesce(aj4cb07,0.0)) as budget07, sum(coalesce(aj4cb08,0.0)) as budget08, sum(coalesce(aj4cb09,0.0)) as budget09 from cmsdat.glmt where aj4comp=" + plantID + " and ((";
for (int i = 0; i < accountList.Count; i++)
{
query += "aj4gl#1=" + accountList[i].glNo1 + " and aj4gl#2=" + accountList[i].glNo2 + ")";
if (i < accountList.Count - 1)
{
query += " or (";
}
}
query += ") and aj4ccyy=20";
reader = solarsoft.RunQuery(query + cy);
if (reader.Read() && !reader.IsDBNull(0))
{
for (int i = 1; i <= 9; i++)
{
ExcoCalendar tempCalendar = new ExcoCalendar(cy, i, true, plantID);
actualThisYearList[i + 3] = new ExcoMoney(tempCalendar.GetNextCalendarMonth(), Convert.ToDouble(reader["actual" + i.ToString("D2")]) * -1.0, currency);
budgetThisYearList[i + 3] = new ExcoMoney(tempCalendar.GetNextCalendarMonth(), Convert.ToDouble(reader["budget" + i.ToString("D2")]) * -1.0, currency);
}
}
reader.Close();
// last year
query = "select sum(coalesce(aj4tt10+aj4ob10,0.0)) as actual10, sum(coalesce(aj4tt11+aj4ob11,0.0)) as actual11, sum(coalesce(aj4tt12+aj4ob12,0.0)) as actual12 from cmsdat.glmt where aj4comp=" + plantID + " and ((";
for (int i = 0; i < accountList.Count; i++)
{
query += "aj4gl#1=" + accountList[i].glNo1 + " and aj4gl#2=" + accountList[i].glNo2 + ")";
if (i < accountList.Count - 1)
{
query += " or (";
}
}
query += ") and aj4ccyy=20";
reader = solarsoft.RunQuery(query + (cy - 2));
if (reader.Read() && !reader.IsDBNull(0))
{
for (int i = 10; i <= 12; i++)
{
ExcoCalendar tempCalendar = new ExcoCalendar(cy - 2, i, true, plantID);
actualLastYearList[i - 9] = new ExcoMoney(tempCalendar.GetNextCalendarMonth(), Convert.ToDouble(reader["actual" + i.ToString("D2")]) * -1.0, currency);
}
}
reader.Close();
query = "select sum(coalesce(aj4tt01+aj4ob01,0.0)) as actual01, sum(coalesce(aj4tt02+aj4ob02,0.0)) as actual02, sum(coalesce(aj4tt03+aj4ob03,0.0)) as actual03, sum(coalesce(aj4tt04+aj4ob04,0.0)) as actual04, sum(coalesce(aj4tt05+aj4ob05,0.0)) as actual05, sum(coalesce(aj4tt06+aj4ob06,0.0)) as actual06, sum(coalesce(aj4tt07+aj4ob07,0.0)) as actual07, sum(coalesce(aj4tt08+aj4ob08,0.0)) as actual08, sum(coalesce(aj4tt09+aj4ob09,0.0)) as actual09 from cmsdat.glmt where aj4comp=" + plantID + " and ((";
for (int i = 0; i < accountList.Count; i++)
{
query += "aj4gl#1=" + accountList[i].glNo1 + " and aj4gl#2=" + accountList[i].glNo2 + ")";
if (i < accountList.Count - 1)
{
query += " or (";
}
}
query += ") and aj4ccyy=20";
reader = solarsoft.RunQuery(query + (cy - 1));
if (reader.Read() && !reader.IsDBNull(0))
{
for (int i = 1; i <= 9; i++)
{
ExcoCalendar tempCalendar = new ExcoCalendar(cy - 1, i, true, plantID);
actualLastYearList[i + 3] = new ExcoMoney(tempCalendar.GetNextCalendarMonth(), Convert.ToDouble(reader["actual" + i.ToString("D2")]) * -1.0, currency);
}
}
reader.Close();
/* }
* else
* {
* string query = "select sum(coalesce(aj4tt10+aj4ob10,0.0)) as actual10, sum(coalesce(aj4tt11+aj4ob11,0.0)) as actual11, sum(coalesce(aj4tt12+aj4ob12,0.0)) as actual12, sum(coalesce(aj4cb10,0.0)) as budget10, sum(coalesce(aj4cb11,0.0)) as budget11, sum(coalesce(aj4cb12,0.0)) as budget12 from cmsdat.glmt where aj4comp=" + plantID + " and ((";
* for (int i = 0; i < accountList.Count; i++)
* {
* query += "aj4gl#1=" + accountList[i].glNo1 + " and aj4gl#2=" + accountList[i].glNo2 + ")";
* if (i < accountList.Count - 1)
* {
* query += " or (";
* }
* }
* query += ") and aj4ccyy=20";
* OdbcDataReader reader = solarsoft.RunQuery(query + cy);
* if (reader.Read() && !reader.IsDBNull(0))
* {
* for (int i = 10; i <= 12; i++)
* {
* ExcoCalendar tempCalendar = new ExcoCalendar(cy, i, true, plantID);
* actualThisYearList[i - 9] = new ExcoMoney(tempCalendar.GetNextCalendarMonth(), Convert.ToDouble(reader["actual" + i.ToString("D2")]) * -1.0, currency);
* budgetThisYearList[i - 9] = new ExcoMoney(tempCalendar.GetNextCalendarMonth(), Convert.ToDouble(reader["budget" + i.ToString("D2")]) * -1.0, currency);
* }
* }
* reader.Close();
* query = "select sum(coalesce(aj4tt01+aj4ob01,0.0)) as actual01, sum(coalesce(aj4tt02+aj4ob02,0.0)) as actual02, sum(coalesce(aj4tt03+aj4ob03,0.0)) as actual03, sum(coalesce(aj4tt04+aj4ob04,0.0)) as actual04, sum(coalesce(aj4tt05+aj4ob05,0.0)) as actual05, sum(coalesce(aj4tt06+aj4ob06,0.0)) as actual06, sum(coalesce(aj4tt07+aj4ob07,0.0)) as actual07, sum(coalesce(aj4tt08+aj4ob08,0.0)) as actual08, sum(coalesce(aj4tt09+aj4ob09,0.0)) as actual09, sum(coalesce(aj4cb01,0.0)) as budget01, sum(coalesce(aj4cb02,0.0)) as budget02, sum(coalesce(aj4cb03,0.0)) as budget03, sum(coalesce(aj4cb04,0.0)) as budget04, sum(coalesce(aj4cb05,0.0)) as budget05, sum(coalesce(aj4cb06,0.0)) as budget06, sum(coalesce(aj4cb07,0.0)) as budget07, sum(coalesce(aj4cb08,0.0)) as budget08, sum(coalesce(aj4cb09,0.0)) as budget09 from cmsdat.glmt where aj4comp=" + plantID + " and ((";
* for (int i = 0; i < accountList.Count; i++)
* {
* query += "aj4gl#1=" + accountList[i].glNo1 + " and aj4gl#2=" + accountList[i].glNo2 + ")";
* if (i < accountList.Count - 1)
* {
* query += " or (";
* }
* }
* query += ") and aj4ccyy=20";
* reader = solarsoft.RunQuery(query + (cy + 1));
* if (reader.Read() && !reader.IsDBNull(0))
* {
* for (int i = 1; i <= 9; i++)
* {
* ExcoCalendar tempCalendar = new ExcoCalendar(cy + 1, i, true, plantID);
* actualThisYearList[i + 3] = new ExcoMoney(tempCalendar.GetNextCalendarMonth(), Convert.ToDouble(reader["actual" + i.ToString("D2")]) * -1.0, currency);
* budgetThisYearList[i + 3] = new ExcoMoney(tempCalendar.GetNextCalendarMonth(), Convert.ToDouble(reader["budget" + i.ToString("D2")]) * -1.0, currency);
* }
* }
* reader.Close();
* // last year
* query = "select sum(coalesce(aj4tt10+aj4ob10,0.0)) as actual10, sum(coalesce(aj4tt11+aj4ob11,0.0)) as actual11, sum(coalesce(aj4tt12+aj4ob12,0.0)) as actual12 from cmsdat.glmt where aj4comp=" + plantID + " and ((";
* for (int i = 0; i < accountList.Count; i++)
* {
* query += "aj4gl#1=" + accountList[i].glNo1 + " and aj4gl#2=" + accountList[i].glNo2 + ")";
* if (i < accountList.Count - 1)
* {
* query += " or (";
* }
* }
* query += ") and aj4ccyy=20";
* reader = solarsoft.RunQuery(query + (cy - 1));
* if (reader.Read() && !reader.IsDBNull(0))
* {
* for (int i = 10; i <= 12; i++)
* {
* ExcoCalendar tempCalendar = new ExcoCalendar(cy - 1, i, true, plantID);
* actualLastYearList[i - 9] = new ExcoMoney(tempCalendar.GetNextCalendarMonth(), Convert.ToDouble(reader["actual" + i.ToString("D2")]) * -1.0, currency);
* }
* }
* reader.Close();
* query = "select sum(coalesce(aj4tt01+aj4ob01,0.0)) as actual01, sum(coalesce(aj4tt02+aj4ob02,0.0)) as actual02, sum(coalesce(aj4tt03+aj4ob03,0.0)) as actual03, sum(coalesce(aj4tt04+aj4ob04,0.0)) as actual04, sum(coalesce(aj4tt05+aj4ob05,0.0)) as actual05, sum(coalesce(aj4tt06+aj4ob06,0.0)) as actual06, sum(coalesce(aj4tt07+aj4ob07,0.0)) as actual07, sum(coalesce(aj4tt08+aj4ob08,0.0)) as actual08, sum(coalesce(aj4tt09+aj4ob09,0.0)) as actual09 from cmsdat.glmt where aj4comp=" + plantID + " and ((";
* for (int i = 0; i < accountList.Count; i++)
* {
* query += "aj4gl#1=" + accountList[i].glNo1 + " and aj4gl#2=" + accountList[i].glNo2 + ")";
* if (i < accountList.Count - 1)
* {
* query += " or (";
* }
* }
* query += ") and aj4ccyy=20";
* reader = solarsoft.RunQuery(query + cy);
* if (reader.Read() && !reader.IsDBNull(0))
* {
* for (int i = 1; i <= 9; i++)
* {
* ExcoCalendar tempCalendar = new ExcoCalendar(cy, i, true, plantID);
* actualLastYearList[i + 3] = new ExcoMoney(tempCalendar.GetNextCalendarMonth(), Convert.ToDouble(reader["actual" + i.ToString("D2")]) * -1.0, currency);
* }
* }
* reader.Close();
* }*/
}
public void GetInfo(ExcoCalendar calendar, string groupID)
{
// fiscal period
string fp = calendar.GetFiscalMonth().ToString("D2");
// fiscal year
string fy = calendar.GetFiscalYear().ToString("D2");
// last period
string lp = (calendar.GetFiscalMonth() - 1).ToString("D2");
// get data
ExcoODBC solarsoft = ExcoODBC.Instance;
solarsoft.Open(Database.CMSDAT);
if (lp != "00")
{
string query = "select coalesce(sum(aj4tt" + fp + "), 0.0) as tpActual, coalesce(sum(aj4cb" + fp + "), 0.0) as tpBudget, coalesce(sum(aj4tt" + lp + "), 0.0) as lpActual, coalesce(sum(";
int iPeriod = Convert.ToInt32(fp);
while (iPeriod > 0)
{
query += "aj4tt" + iPeriod.ToString("D2");
iPeriod--;
if (iPeriod > 0)
{
query += "+";
}
else
{
query += "), 0.0) as ytdActual, coalesce(sum(";
}
}
iPeriod = Convert.ToInt32(fp);
while (iPeriod > 0)
{
query += "aj4cb" + iPeriod.ToString("D2");
iPeriod--;
if (iPeriod > 0)
{
query += "+";
}
else
{
query += "), 0.0) as ytdBudget from cmsdat.glmt left join cmsdat.mast on aj4comp=1 and aj4gl#1=100 and azcomp=aj4comp and azgl#1=aj4gl#1 and aj4gl#2=azgl#2 and aj4ccyy=20" + fy + " where azgrop like '" + id + "%'";
}
}
OdbcDataReader reader = solarsoft.RunQuery(query);
if (reader.Read())
{
tpActual = new ExcoMoney(calendar, Convert.ToDouble(reader["tpActual"]), currency);
lpActual = new ExcoMoney(calendar, Convert.ToDouble(reader["lpActual"]), currency);
tpBudget = new ExcoMoney(calendar, Convert.ToDouble(reader["tpBudget"]), currency);
ytdActual = new ExcoMoney(calendar, Convert.ToDouble(reader["ytdActual"]), currency);
ytdBudget = new ExcoMoney(calendar, Convert.ToDouble(reader["ytdBudget"]), currency);
}
reader.Close();
}
else
{
string query = "select coalesce(sum(aj4tt" + fp + "), 0.0) as tpActual, coalesce(sum(aj4cb" + fp + "), 0.0) as tpBudget, coalesce(sum(";
int iPeriod = Convert.ToInt32(fp);
while (iPeriod > 0)
{
query += "aj4tt" + iPeriod.ToString("D2");
iPeriod--;
if (iPeriod > 0)
{
query += "+";
}
else
{
query += "), 0.0) as ytdActual, coalesce(sum(";
}
}
iPeriod = Convert.ToInt32(fp);
while (iPeriod > 0)
{
query += "aj4cb" + iPeriod.ToString("D2");
iPeriod--;
if (iPeriod > 0)
{
query += "+";
}
else
{
query += "), 0.0) as ytdBudget from cmsdat.glmt left join cmsdat.mast on aj4comp=1 and aj4gl#1=100 and azcomp=aj4comp and azgl#1=aj4gl#1 and aj4gl#2=azgl#2 and aj4ccyy=20" + fy + " where azgrop like '" + id + "%'";
}
}
OdbcDataReader reader = solarsoft.RunQuery(query);
if (reader.Read())
{
tpActual = new ExcoMoney(calendar, Convert.ToDouble(reader["tpActual"]), currency);
//lpActual = new ExcoMoney(calendar, Convert.ToDouble(reader["lpActual"]), currency);
tpBudget = new ExcoMoney(calendar, Convert.ToDouble(reader["tpBudget"]), currency);
ytdActual = new ExcoMoney(calendar, Convert.ToDouble(reader["ytdActual"]), currency);
ytdBudget = new ExcoMoney(calendar, Convert.ToDouble(reader["ytdBudget"]), currency);
}
reader.Close();
//get last fiscal year period data
lp = "12";
fy = (calendar.GetFiscalYear() - 1).ToString("D2");
query = "select coalesce(sum(aj4tt" + lp + "), 0.0) as lpActual from cmsdat.glmt left join cmsdat.mast on aj4comp=1 and aj4gl#1=100 and azcomp=aj4comp and azgl#1=aj4gl#1 and aj4gl#2=azgl#2 and aj4ccyy=20" + fy + " where azgrop like '" + id + "%'";
reader = solarsoft.RunQuery(query);
if (reader.Read())
{
lpActual = new ExcoMoney(calendar, Convert.ToDouble(reader["lpActual"]), currency);
}
reader.Close();
}
}
public void MyTestInitialize()
{
moneyCA = new ExcoMoney(calendarCA, 1.0, "CA");
moneyUS = new ExcoMoney(calendarUS, 2.0, "US");
moneyCP = new ExcoMoney(calendarCP, 3.3, "CP");
}
