/// <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);
        }