/// <summary>
/// Check for Duplicate in a Sheet
/// </summary>
/// <param name="sheet"></param>
/// <param name="arr"></param>
/// <returns></returns>
public bool CheckDuplicate(String sheet, ArrayList arr)
{
int nCount = arr.Count;
if (nCount == 0)
{
return(false);
}
DataTable t = GetDataTable(sheet);
DataColumnCollection cols = t.Columns;
try
{
int nItems = t.Rows.Count;
int index = 0;
while (index < nItems)
{
DataRow row = t.Rows[index];
if (CompareRecord(t, row, arr, index) == false)
{
return(false);
}
index++;
}
return(true);
}
catch (Exception e)
{
CSyntaxErrorLog.AddLine(e.ToString());
throw e;
}
}
/// <summary>
/// Update All the data from the spread sheet
/// </summary>
/// <returns></returns>
///
public bool UpdateAll()
{
String country;
String segment;
String currency;
double ordc; // order cancel factor
String forecast_name;
try
{
batch_upd = new CBatchDBUpdator(m_OrclConnstr);
country = m_xlsreader.GetCellValue("CONTROL", "B2");
currency = m_xlsreader.GetCellValue("CONTROL", "B5");
segment = m_xlsreader.GetCellValue("CONTROL", "B3");
String ordcstr = m_xlsreader.GetCellValue("CONTROL", "B6");
string time_zone_code = m_xlsreader.GetCellValue("CONTROL", "B4");
GetTimeZoneOffset(time_zone_code);
ordcstr = ordcstr.Trim().ToUpper();
int index = 0;
String temp = "";
while (char.IsDigit(ordcstr[index]))
{
temp = temp + ordcstr[index];
index++;
}
if (ordcstr[index] == '.')
{
index++;
temp = temp + ".";
while (char.IsDigit(ordcstr[index]))
{
temp = temp + ordcstr[index];
index++;
}
}
ordc = Convert.ToDouble(temp);
forecast_name = m_xlsreader.GetCellValue("CONTROL", "B7");
country = country.Trim().ToUpper();
segment = segment.Trim().ToUpper();
currency = currency.Trim().ToUpper();
forecast_name = forecast_name.Trim();
if (country.Length == 0 || segment.Length == 0 ||
currency.Length == 0)
{
throw new Exception("Country , Segment or Currency is zero length");
}
if (segment.Length > 10)
{
segment = segment.Substring(0, 10);
}
if (forecast_name.Length > 20)
{
forecast_name = forecast_name.Substring(0, 20);
}
upd_ob_revenue_units(country, segment, currency);
update_click_stream(country, segment);
update_ob_order_count(country, segment);
update_odg_click_count(country, segment);
update_odg_order_count_revenue(country, segment, currency);
update_control_to_ei_cancel_factor(country, segment, forecast_name, ordc);
batch_upd.Commit();
batch_upd.Close();
batch_upd = null;
return(true);
}
catch (CParserException e)
{
CSyntaxErrorLog.AddLine(e.ToString());
batch_upd.Abort();
batch_upd.Close();
batch_upd = null;
return(false);
}
catch (Exception e)
{
CSyntaxErrorLog.AddLine(e.ToString());
batch_upd.Abort();
batch_upd.Close();
batch_upd = null;
return(false);
}
}
public bool ProcessForeCastPath(XmlNode node)
{
try
{
////////////////////////////////////////
/// Retrieve the root directory
///
String root_dir = node.InnerText;
if (!Directory.Exists(root_dir))
{
Directory.CreateDirectory(root_dir);
}
#if false
////////////////////////////////////////////////
///
/// Retrieve the Country names
///
XmlNode nd = node.SelectSingleNode("COUNTRY_LIST");
XmlNodeList nd_list = nd.SelectNodes("COUNTRY");
int i = 0;
while (i < nd_list.Count)
{
//////////////////////////////
/// Retrieve Country node
///
XmlNode country_nd = nd_list.Item(i);
/////////////////////////////////////
/// Retrieve country name
///
String country_name = country_nd.SelectSingleNode("COUNTRY_NAME").InnerText;
///////////////////////////////////////////////
/// Try to create Folder for the country
///
///
if (!Directory.Exists(root_dir + "\\" + country_name))
{
Directory.CreateDirectory(root_dir + "\\" + country_name);
}
XmlNode Temp = country_nd.SelectSingleNode("SEGMENT_LIST");
XmlNodeList Temp_List = null;
if (Temp != null)
{
Temp_List = Temp.ChildNodes;
}
if (Temp_List != null)
{
if (Temp_List.Count > 0)
{
int j = 0;
while (j < Temp_List.Count)
{
String segment = Temp_List.Item(j).InnerText;
if (!Directory.Exists(root_dir + "\\" + country_name + "\\" + segment))
{
Directory.CreateDirectory(root_dir + "\\" + country_name + "\\" + segment);
}
j++;
}
}
}
i++;
}
#endif
}
catch (Exception e)
{
CSyntaxErrorLog.AddLine(e.ToString());
throw e;
}
return(true);
}
/// <summary>
/// Read the Stuff from the initialization file
/// </summary>
private void Initialize()
{
try
{
XmlNode scratch;
XmlDocument doc = new System.Xml.XmlDocument();
doc.Load(m_xmlfile);
XmlNode config_nd = doc.SelectSingleNode("CONFIG");
scratch = config_nd.SelectSingleNode("DECIMAL_ROUNDING");
GlobalSettings.AddEntry("DECIMAL_ROUNDING", Convert.ToInt32(scratch.InnerText));
scratch = config_nd.SelectSingleNode("ALLOW_EMAILS");
GlobalSettings.AddEntry("ALLOW_EMAILS", scratch.InnerText);
scratch = config_nd.SelectSingleNode("QUARANTINE_SHEETS");
GlobalSettings.AddEntry("QUARANTINE_SHEETS", scratch.InnerText);
scratch = config_nd.SelectSingleNode("BACKUP_SHEETS");
GlobalSettings.AddEntry("BACKUP_SHEETS", scratch.InnerText);
scratch = config_nd.SelectSingleNode("EVENT_LOGGING");
GlobalSettings.AddEntry("EVENT_LOGGING", scratch.InnerText);
//
// Retrieve notification parameters
//
//
XmlNode notify_nd = config_nd.SelectSingleNode("NOTIFICATION");
scratch = notify_nd.SelectSingleNode("EMAIL_LIST");
XmlNodeList iter = scratch.ChildNodes;
foreach (XmlNode rnode in iter)
{
notify.AddEmail(rnode.InnerText);
}
scratch = notify_nd.SelectSingleNode("SUBJECT_SUCCESS");
notify.SubjectSuccess = scratch.InnerText;
scratch = notify_nd.SelectSingleNode("SUBJECT_FAILURE");
notify.SubjectFailure = scratch.InnerText;
scratch = notify_nd.SelectSingleNode("SMTPSERVER");
notify.Server = scratch.InnerText;
scratch = notify_nd.SelectSingleNode("SENDER");
notify.From = scratch.InnerText;
//
//
// Retrieve oracle string
//
//
XmlNode retr_node = config_nd.SelectSingleNode("ORACLE_CONNECTION_STRING");
GlobalSettings.AddEntry("ORACLE_CONNECTION_STRING", retr_node.InnerText);
retr_node = config_nd.SelectSingleNode("SCRIPT");
GlobalSettings.AddEntry("VALIDATION_SCRIPT", retr_node.InnerText);
retr_node = config_nd.SelectSingleNode("FORECAST_PATH");
GlobalSettings.AddEntry("FORECAST_PATH", retr_node.InnerText);
ProcessForeCastPath(retr_node);
m_rootPath = retr_node.InnerText;
retr_node = config_nd.SelectSingleNode("EXCEL_CONNECTION_STRING");
XmlNode temp_node = retr_node.SelectSingleNode("PROVIDER");
GlobalSettings.AddEntry("EXCEL_PROVIDER", temp_node.InnerText);
temp_node = retr_node.SelectSingleNode("PROPERTIES");
GlobalSettings.AddEntry("EXCEL_PROPERTIES", temp_node.InnerText);
}
catch (Exception e)
{
CSyntaxErrorLog.AddLine(e.ToString());
throw e;
}
}
/// <summary>
/// Clean up data in a sheet. This routine is written
/// to elimate those rows which are considered by excel
/// as record and will appear empty as humans.
/// Such rows should be in the tail of the cell.
/// </summary>
/// <param name="sheet"></param>
/// <param name="arr"></param>
/// <returns></returns>
public bool CleanUpCells(String sheet, ArrayList arr)
{
//////////////////////////////////////////////////////
/// Retrieve table associated with sheet
///
DataTable tab = (DataTable)m_Table[sheet + "$"];
////////////////////////////////////
/// Retrieve meta data for the columns
///
DataColumnCollection colls = tab.Columns;
/////////////////////////////////////
///
/// # of Row
///
int row_count = tab.Rows.Count;
if (row_count == 0)
{
return(false);
}
//////////////////////////////////////
/// index used for iteration of celss
///
int index = 0;
/////////////////////////////////////////
///
///
bool first_time = false;
int start_rec = -1;
while (index < row_count)
{
DataRow rw = tab.Rows[index];
if (CompareOne(rw, colls, arr) == false)
{
/////////////////////////////////////////
/// An Empty Record
///
if (first_time == false)
{
start_rec = index;
first_time = true;
}
}
else
{
///////////////////////////////////////
/// if already an empty record and found
/// a genuine record after that , return
/// failure. Sheet is invalid
///
if (first_time == true)
{
return(false);
}
}
index++;
}
////////////////////////////////
///
/// IF whitespace rows are found
///
if (start_rec != -1)
{
///////////////////////////////
/// # of records to be cleaned up
///
int num_rec = row_count - start_rec;
int i = 0;
while (i < num_rec)
{
///////////////////////////////////
/// Iterate the list and delete
/// note :- start_rec is not advanced
tab.Rows[start_rec++].Delete();
i++;
}
//////////////////////////////////////
/// Accept the changes
///
tab.AcceptChanges();
if (tab.Rows.Count == 0)
{
CSyntaxErrorLog.AddLine("All the record is invalid because of some missing fields");
return(false);
}
}
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="t"></param>
/// <param name="rw"></param>
/// <param name="arr"></param>
/// <param name="index"></param>
/// <returns></returns>
public bool CompareRecord(
DataTable t,
DataRow rw,
ArrayList arr,
int index)
{
DataColumnCollection colls = t.Columns;
int st = t.Rows.Count;
int i = 0;
Stack pstack = new Stack();
while (i < st)
{
if (i == index)
{
i++;
continue;
}
DataRow rw1 = t.Rows[i];
int sn = arr.Count;
int j = 0;
bool equal = true;
while (j < sn)
{
String s = (String)arr[j];
if (colls[s].DataType == Type.GetType("System.DateTime") &&
(rw[s].GetType() == rw1[s].GetType() && rw[s].GetType() != Type.GetType("System.DBNull")))
{
DateTime r = Convert.ToDateTime(rw[s]);
DateTime r1 = Convert.ToDateTime(rw1[s]);
equal = equal && (r == r1);
}
else if (colls[s].DataType == Type.GetType("System.Double") &&
(rw[s].GetType() == rw1[s].GetType() && rw[s].GetType() != Type.GetType("System.DBNull")))
{
System.Double r = Convert.ToDouble(rw[s]);
Double r1 = Convert.ToDouble(rw1[s]);
equal = equal && (r == r1);
}
else if (colls[s].DataType == Type.GetType("System.Int32") &&
(rw[s].GetType() == rw1[s].GetType() && rw[s].GetType() != Type.GetType("System.DBNull")))
{
Int32 r = Convert.ToInt32(rw[s]);
Int32 r1 = Convert.ToInt32(rw1[s]);
equal = equal && (r == r1);
}
else if (colls[s].DataType == Type.GetType("System.String") &&
(rw[s].GetType() == rw1[s].GetType() && rw[s].GetType() != Type.GetType("System.DBNull")))
{
String r = Convert.ToString(rw[s]).ToUpper();
String r1 = Convert.ToString(rw1[s]).ToUpper();
equal = equal && (r == r1);
}
else if (colls[s].DataType == Type.GetType("System.String") &&
(rw[s].GetType() == rw1[s].GetType() && rw[s].GetType() != Type.GetType("System.DBNull")))
{
Decimal r = Convert.ToDecimal(rw[s]);
Decimal r1 = Convert.ToDecimal(rw1[s]);
equal = equal && (r == r1);
}
else
{
String expression = "";
expression = "Invalid Type reference at row" + Convert.ToString(i);
CSyntaxErrorLog.AddLine(expression);
throw new CParserException(-100, expression, -1);
}
if (!equal)
{
break;
}
j++;
}
if (equal)
{
return(false);
}
i++;
}
return(true);
}