using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using System.IO;

namespace EQTrack
{
    public partial class FormCSVData : Form
    {
        DataTable dt = new DataTable();
        DataTable dt2 = new DataTable();
        public FormCSVData(string fileName)
        {
            string ext = Path.GetExtension(fileName);
            //MessageBox.Show(ext);
            bool readExcel = false;
            if (ext.ToUpper() == ".XLS")
            {

                try
                {
                    string xls = fileName;
                    InitializeComponent();
                    string dbpath = xls;
                    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + dbpath + ";" + "Extended Properties=Excel 8.0;";
                    OleDbConnection OleDbConnection = new OleDbConnection(strConn);
                    OleDbConnection.Open();

                    DataTable dtSchema = OleDbConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                    OleDbConnection.Close();
                    string tableName = dtSchema.Rows[0][2].ToString().Trim();

//                    MessageBox.Show(tableName);


                    OleDbCommand OleDbCommand = new OleDbCommand("SELECT * FROM [" + tableName + "A1:AB" + ((int)Globals.htOptions["Max_Units"]+2) + "]", OleDbConnection);
                    OleDbDataAdapter oa = new OleDbDataAdapter(OleDbCommand);

                    oa.FillSchema(dt, SchemaType.Source);  
                    oa.Fill(dt);
                    oa.FillSchema(dt2, SchemaType.Source);  
                    oa.Fill(dt2);

                    RemoveEndEmptyLines(ref dt2, dt2.Rows.Count - 1);
                    dt2.Rows.RemoveAt(0);



                    dataGridView1.DataSource = dt2;
                    readExcel = true;
                }
                catch (Exception e)
                {
                    MessageBox.Show("error 101:" + e.Message);
                }


                if (readExcel)
                {

                    for (int i = 0; i < dt.Columns.Count; ++i)
                    {
                        //    if (dt.Columns[i].ColumnName == "IMS")
                        bool foundMatch = false;
                        try
                        {
                            foundMatch = Regex.IsMatch(dt.Columns[i].ColumnName, @"SIM Card Nmber|F\d+");
                            if (foundMatch)
                            {
                                dt.Columns.RemoveAt(i--);
                            }
                        }
                        catch (ArgumentException ex)
                        {
                            // Syntax error in the regular expression
                            Console.WriteLine(ex);
                        }



                    }
                    RemoveEndEmptyLines(ref dt, dt.Rows.Count - 1);
                    dt.Rows.RemoveAt(0);
                    FillZeros(ref dt);
                    //      RemoveEndEmptyColumns(ref dt, dt.Columns.Count - 1);



                    dataGridView2.DataSource = dt;
                    if (ValidXLS(dt2))
                    {
                        Console.WriteLine("Xls file is valid");
                        CreateCSVFile(dt, "fill.csv");


                    }
                    else
                    {
                        MessageBox.Show("Xls file is invalid");
                    }
                }
            }
            else
            {
                MessageBox.Show("This is neither a excel file nor a csv file");
            }

        }

        public void CreateCSVFile(DataTable dt, string strFilePath)
        {
            #region Export Grid to CSV
            // Create the CSV file to which grid data will be exported.
           
            // First we will write the headers.
            //DataTable dt = m_dsProducts.Tables[0];
            try
            {
                StreamWriter sw = new StreamWriter(strFilePath, false);
                int iColCount = dt.Columns.Count;
                for (int i = 0; i < iColCount; i++)
                {
                    sw.Write(dt.Columns[i]);
                    if (i < iColCount - 1)
                    {
                        sw.Write(",");
                    }
                }
                sw.Write(sw.NewLine);
                // Now write all the rows.
                foreach (DataRow dr in dt.Rows)
                {
                    for (int i = 0; i < iColCount; i++)
                    {
                        if (!Convert.IsDBNull(dr[i]))
                        {
                            string tempStr = dr[i].ToString();
                            if (i == 1)
                            {
                                if (tempStr[0] == '0'){
                                    tempStr = "64" + tempStr.Substring(1);
                                }
                                
                            }
                            sw.Write(tempStr);
                        }
                        if (i < iColCount - 1)
                        {
                            sw.Write(",");
                        }
                    }
                    sw.Write(sw.NewLine);
                }
                sw.Close();
             //   MessageBox.Show("The fill.csv file is generated in the current folder", "Success");
                Globals.csvCreated = true;
            }
            catch (IOException e)
            {
                MessageBox.Show("Exception:" + e);
            }
            #endregion
        }

        private void customButton19_Click(object sender, EventArgs e)
        {
            CreateCSVFile(dt, "fill.csv");
           
        }
        private bool ValidXLS(DataTable dt)
        {

         
            bool valid = true;
            foreach (DataRow dr in dt.Rows)
            {
             
                for (int i = 0; i < 5; i++)
                {
                    
                    if (Convert.IsDBNull(dr[i]))
                    {
                        
                        valid = false;
                        
                    }


                }
                if (!valid)
                {
                   // Console.WriteLine(i + "");
                    break;
                }
               
            }
            
            return valid;
        }

        public bool IsEmptyLine(DataTable dt, int index)
        {

            bool isEmpty = true;
            int iColCount = dt.Columns.Count;
            for (int i = 0; i < iColCount; i++)
            {
               // Console.WriteLine(dt.Rows[index][i]);
                if (!Convert.IsDBNull(dt.Rows[index][i]))
                {
                    isEmpty = false;
                    break;
                }

            }
            return isEmpty;
         

        }
        //public bool IsEmptyColumn(DataTable dt, int index)
        //{

        //    bool isEmpty = true;
           
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        // Console.WriteLine(dt.Rows[index][i]);
        //        if (!Convert.IsDBNull(dr[index]))
        //        {
        //            isEmpty = false;
        //            break;
        //        }

        //    }
        //    return isEmpty;


        //}
        private void RemoveEndEmptyLines(ref DataTable dt, int index)
        {
            if (IsEmptyLine(dt, index))
            {
           //     MessageBox.Show("empty");
                dt.Rows.RemoveAt(index);
                RemoveEndEmptyLines(ref dt, index - 1);

            }
        }
        //private void RemoveEndEmptyColumns(ref DataTable dt, int index)
        //{
        //    if (IsEmptyColumn(dt, index))
        //    {
        //        //     MessageBox.Show("empty");
        //        dt.Columns.RemoveAt(index--);
        //        RemoveEndEmptyColumns(ref dt, index);

        //    }
        //}
        private void FillZeros(ref DataTable dt)
        {
            foreach (DataRow dr in dt.Rows)
            {
                //dr[4] = 1;
                dr["Enabled"] = 1;
                dr["Date_First_Enabled"] = DateTime.Now.ToShortDateString();
                for (int i = 8; i < dt.Columns.Count - 2; i++)
                {
                    dr[i] = 0;
                }
                dr["BHN"] = dr["Serial_Number"];
                dr["HeartbeatRate"] = 24;
                dr["Customer_ID"] = 80675;
                
            }
            
        }

    }
}