/// <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>
/// 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="sql"></param>
//public void DeleteData(string sql)
//{
// try
// {
// OracleCommand tmpcommand = new OracleCommand(sql, conn_oracle);
// tmpcommand.CommandTimeout = 90;
// tmpcommand.ExecuteNonQuery();
// }
// catch (Exception e)
// {
// CSyntaxErrorLog.AddLine(e.ToString());
// Close();
// throw new CParserException(-100, "Error Executing Query", -1);
// }
//}
///// <summary>
/////
///// </summary>
///// <param name="Sql"></param>
///// <returns></returns>
//public DataSet GetDataSet(string Sql)
//{
// try
// {
// OracleCommand tmpcommand = new OracleCommand(Sql, conn_oracle);
// tmpcommand.CommandTimeout = 90;
// DataSet ds = new DataSet();
// OracleDataAdapter da = new OracleDataAdapter(tmpcommand);
// da.Fill(ds);
// return ds;
// }
// catch (Exception e)
// {
// System.Diagnostics.EventLog.WriteEntry("ForeCastLog", e.ToString());
// CSyntaxErrorLog.AddLine(e.ToString());
// Close();
// throw new CParserException(-100, e.ToString(), -1);
// }
//}
/// <summary>
///
/// </summary>
/// <param name="SheetName"></param>
/// <param name="CellName"></param>
/// <param name="TableName"></param>
/// <param name="ColumnName"></param>
/// <returns></returns>
///
//public bool LookupInDB(String SheetName, String CellName, String TableName, String ColumnName)
//{
// String Clm = ConvertCellToColumn(SheetName, CellName);
// String Sql = "Select " + ColumnName + " from " + TableName;
// Sql += " Where UPPER(" + ColumnName + ") = '" + Clm.Trim().ToUpper() + "'";
// DataSet rset = GetDataSet(Sql);
// bool front = rset.Tables[0].Rows.Count > 0;
// rset.Clear();
// rset.Dispose();
// return front;
//}
/// <summary>
/// Case Sensitive
/// </summary>
/// <param name="SheetName"></param>
/// <param name="CellName"></param>
/// <param name="TableName"></param>
/// <param name="ColumnName"></param>
/// <returns></returns>
//public bool LookupInDBCaseSensitive(String SheetName, String CellName, String TableName, String ColumnName)
//{
// String Clm = ConvertCellToColumn(SheetName, CellName);
// String Sql = "Select " + ColumnName + " from " + TableName;
// Sql += " Where " + ColumnName + " = '" + Clm.Trim() + "'";
// DataSet rset = GetDataSet(Sql);
// bool front = rset.Tables[0].Rows.Count > 0;
// rset.Clear();
// rset.Dispose();
// return front;
//}
/// <summary>
///
/// </summary>
/// <returns></returns>
//public bool ScanCurrency(String currency)
//{
// String country;
// String segment;
// //////////////////////////////////////////
// ///
// /// Retrieve the Country and the segment from the spread sheet
// ///
// country = GetCellValue("CONTROL", "B2");
// segment = GetCellValue("CONTROL", "B3");
// country = country.Trim();
// segment = segment.Trim();
// String Sql = "Select DISTINCT Currency_code from OB_HOURLY_REVENUE ";
// Sql += "Where " + "UPPER(country_code) = '" + country.ToUpper() + "' AND UPPER(EI_SEGMENT_CODE) = '" + segment.ToUpper() + "'";
// Sql += " AND UPPER(Currency_code) = '" + currency.Trim().ToUpper() + "'";
// DataTable rset = GetDataSet(Sql).Tables[0];
// int rs = rset.Rows.Count;
// rset.Dispose();
// return rs > 0;
//}
///// <summary>
/////
///// </summary>
///// <param name="SheetName"></param>
///// <param name="CellName"></param>
///// <param name="TableName"></param>
///// <param name="ColumnName"></param>
///// <returns></returns>
//public bool ScanBrand()
//{
// String SheetName;
// String CellName;
// String ColumnName;
// SheetName = "OB_REVENUE_UNITS";
// CellName = "BRAND_ID";
// ColumnName = "BRAND_ID";
// String Sql = "Select BRAND_ID from BRAND";
// DataTable rset = GetDataSet(Sql).Tables[0];
// DataColumnCollection col = rset.Columns;
// DataTable st = GetDataTable(SheetName);
// int iter = st.Rows.Count;
// int index = 0;
// while (index < iter)
// {
// object ars = st.Rows[index][CellName];
// Type ars_type = ars.GetType();
// int rs = 0;
// bool nFound = false;
// // object desc = st.Rows[index]["BRAND_LOB"];
// if (ars.GetType() != Type.GetType("System.Int32") &&
// ars.GetType() != Type.GetType("System.Double"))
// {
// return false;
// }
// // if ( desc.GetType() != Type.GetType("System.String") )
// // {
// // return false;
// //
// // }
// // String desc_str = desc.ToString().Trim().ToUpper();
// int br_value = Convert.ToInt32(ars);
// if (br_value == -1)
// {
// index++;
// continue;
// }
// while (rs < rset.Rows.Count)
// {
// object rw = rset.Rows[rs][ColumnName];
// if (rw.GetType() != Type.GetType("System.Double") &&
// rw.GetType() != Type.GetType("System.Int32") &&
// rw.GetType() != Type.GetType("System.Int16") &&
// rw.GetType() != Type.GetType("System.Decimal"))
// return false;
// if (ars_type == Type.GetType("System.String"))
// {
// String a = Convert.ToString(ars);
// String b = Convert.ToString(rw);
// if (a == b)
// {
// nFound = true;
// break;
// }
// }
// else if (ars_type == Type.GetType("System.Double"))
// {
// Double a = Convert.ToDouble(ars);
// Double b = Convert.ToDouble(rw);
// if (a == b)
// {
// nFound = true;
// break;
// }
// }
// else if (ars_type == Type.GetType("System.Int32"))
// {
// Int32 a = Convert.ToInt32(ars);
// Int32 b = Convert.ToInt32(rw);
// if (a == b)
// {
// nFound = true;
// break;
// }
// }
// else if (ars_type == Type.GetType("System.DateTime"))
// {
// DateTime a = Convert.ToDateTime(ars);
// DateTime b = Convert.ToDateTime(rw);
// if (a == b)
// {
// nFound = true;
// break;
// }
// }
// else if (ars_type == Type.GetType("System.Decimal"))
// {
// Decimal a = Convert.ToDecimal(ars);
// Decimal b = Convert.ToDecimal(rw);
// if (a == b)
// {
// nFound = true;
// break;
// }
// }
// rs++;
// }
// if (nFound == false)
// return false;
// index++;
// }
// return true;
//}
///// <summary>
///// Scan the DB by sending excel data
///// </summary>
///// <param name="SheetName"></param>
///// <param name="CellName"></param>
///// <param name="TableName"></param>
///// <param name="ColumnName"></param>
///// <returns></returns>
//public bool ScanBySendingExcelData(String SheetName, String CellName, String TableName, String ColumnName)
//{
// DataTable st = GetDataTable(SheetName);
// int iter = st.Rows.Count;
// int index = 0;
// String Sql;
// while (index < iter)
// {
// object ars = st.Rows[index][CellName];
// Type ars_type = ars.GetType();
// Sql = "Select " + ColumnName + " from " + TableName;
// Sql += " Where UPPER(" + ColumnName + ")=";
// if (ars_type == Type.GetType("System.String"))
// {
// String a = Convert.ToString(ars);
// Sql = Sql + "'" + a.ToUpper() + "'";
// }
// else if (ars_type == Type.GetType("System.Double"))
// {
// Double a = Convert.ToDouble(ars);
// Sql = Sql + Convert.ToString(a);
// }
// else if (ars_type == Type.GetType("System.Int32"))
// {
// Int32 a = Convert.ToInt32(ars);
// Sql = Sql + Convert.ToString(a);
// }
// else if (ars_type == Type.GetType("System.DateTime"))
// {
// DateTime a = Convert.ToDateTime(ars);
// Sql = Sql + "'" + Convert.ToString(a) + "'";
// }
// else if (ars_type == Type.GetType("System.Decimal"))
// {
// Decimal a = Convert.ToDecimal(ars);
// Sql = Sql + Convert.ToString(a);
// }
// DataTable rset = GetDataSet(Sql).Tables[0];
// if (!(rset.Rows.Count > 0))
// return false;
// index++;
// }
// return true;
//}
/// <summary>
///
/// </summary>
/// <param name="SheetName"></param>
/// <param name="CellName"></param>
/// <param name="TableName"></param>
/// <param name="ColumnName"></param>
/// <returns></returns>
//public bool ScanInDB(String SheetName, String CellName, String TableName, String ColumnName)
//{
// String Sql = "Select " + ColumnName + " from " + TableName;
// DataTable rset = GetDataSet(Sql).Tables[0];
// DataColumnCollection col = rset.Columns;
// DataTable st = GetDataTable(SheetName);
// int iter = st.Rows.Count;
// int index = 0;
// while (index < iter)
// {
// object ars = st.Rows[index][CellName];
// Type ars_type = ars.GetType();
// int rs = 0;
// bool nFound = false;
// while (rs < rset.Rows.Count)
// {
// object rw = rset.Rows[rs][ColumnName];
// if (rw.GetType() != ars_type)
// return false;
// if (ars_type == Type.GetType("System.String"))
// {
// String a = Convert.ToString(ars).ToUpper();
// String b = Convert.ToString(rw).ToUpper();
// if (a == b)
// {
// nFound = true;
// break;
// }
// }
// else if (ars_type == Type.GetType("System.Double"))
// {
// Double a = Convert.ToDouble(ars);
// Double b = Convert.ToDouble(rw);
// if (a == b)
// {
// nFound = true;
// break;
// }
// }
// else if (ars_type == Type.GetType("System.Int32"))
// {
// Int32 a = Convert.ToInt32(ars);
// Int32 b = Convert.ToInt32(rw);
// if (a == b)
// {
// nFound = true;
// break;
// }
// }
// else if (ars_type == Type.GetType("System.Decimal"))
// {
// Decimal a = Convert.ToDecimal(ars);
// Decimal b = Convert.ToDecimal(rw);
// if (a == b)
// {
// nFound = true;
// break;
// }
// }
// else if (ars_type == Type.GetType("System.DateTime"))
// {
// DateTime a = Convert.ToDateTime(ars);
// DateTime b = Convert.ToDateTime(rw);
// if (a == b)
// {
// nFound = true;
// break;
// }
// }
// rs++;
// }
// if (nFound == false)
// return false;
// index++;
// }
// 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 Exception(expression);
}
if (!equal)
{
break;
}
j++;
}
if (equal)
{
return(false);
}
i++;
}
return(true);
}