private ExcoCustomerTable()
{
//string query = "select bvcust, bvname, bvcurr, bvterr from cmsdat.cust order by bvterr, bvname";
string query = "select bvcust, bvname, bvcurr, bvterr from cmsdat.cust where BVCUST not like '000265S%' order by bvterr, bvname";
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.CMSDAT);
OdbcDataReader reader = database.RunQuery(query);
while (reader.Read())
{
string id = reader[0].ToString().Trim();
string name = reader[1].ToString().Trim();
string curr = reader[2].ToString().Trim();
string terr = reader[3].ToString().Trim();
if (custList.Count == 0 || 0 != name.CompareTo(custList[custList.Count - 1].Name) || (0 == name.CompareTo(custList[custList.Count - 1].Name) && id.Contains('S')) || 0 != curr.CompareTo(custList[custList.Count - 1].Currency))
{
ExcoCustomer cust = new ExcoCustomer();
cust.ShipToID = id;
cust.BillToID = id;
cust.SillyID = id;
cust.Name = name;
cust.Currency = curr;
cust.Territory = terr;
custList.Add(cust);
}
else
{
if (id.Contains("S"))
{
if (custList[custList.Count - 1].ShipToID.Contains("S"))
{
custList[custList.Count - 1].SillyID = id;
}
else
{
custList[custList.Count - 1].ShipToID = id;
}
}
else
{
custList[custList.Count - 1].BillToID = id;
custList[custList.Count - 1].Territory = terr;
}
}
}
reader.Close();
}
public double GetMonthlyBudget(ExcoCustomer customer, int plant, ExcoCalendar calendar)
{
double value = 0.0;
try
{
if (1 == plant)
{
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.DECADE_MARKHAM);
string query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + " from tiger.dbo.Markham_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.BillToID + "'";
OdbcDataReader reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + "from tiger.dbo.Markham_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.ShipToID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + "from tiger.dbo.Markham_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.SillyID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
}
reader.Close();
}
}
}
else if (3 == plant)
{
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.DECADE_MARKHAM);
string query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + " from tiger.dbo.Michigan_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.BillToID + "'";
OdbcDataReader reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + "from tiger.dbo.Michigan_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.ShipToID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + "from tiger.dbo.Michigan_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.SillyID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
}
reader.Close();
}
}
}
else if (5 == plant)
{
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.DECADE_MARKHAM);
string query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + " from tiger.dbo.Texas_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.BillToID + "'";
OdbcDataReader reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + "from tiger.dbo.Texas_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.ShipToID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + calendar.GetFiscalMonth().ToString("D2") + "from tiger.dbo.Texas_Budget where Year=20" + calendar.GetFiscalYear().ToString("D2") + " and CustomerID='" + customer.SillyID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
}
reader.Close();
}
}
}
else if (4 == plant)
{
int year;
int period;
if (calendar.GetFiscalMonth() >= 10)
{
year = calendar.GetFiscalYear() + 1;
period = calendar.GetFiscalMonth() - 9;
}
else
{
year = calendar.GetFiscalYear();
period = calendar.GetFiscalMonth() + 3;
}
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.DECADE_MARKHAM);
string query = "select Period" + period.ToString("D2") + " from tiger.dbo.Colombia_Budget where Year=20" + year.ToString("D2") + " and CustomerID='" + customer.BillToID + "'";
OdbcDataReader reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + period.ToString("D2") + "from tiger.dbo.Colombia_Budget where Year=20" + year.ToString("D2") + " and CustomerID='" + customer.ShipToID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
reader.Close();
}
else
{
reader.Close();
query = "select Period" + period.ToString("D2") + "from tiger.dbo.Colombia_Budget where Year=20" + year.ToString("D2") + " and CustomerID='" + customer.SillyID + "'";
reader = database.RunQuery(query);
if (reader.Read())
{
value = Convert.ToDouble(reader[0]);
}
reader.Close();
}
}
}
}
catch
{
return(0.0);
}
return(value);
}