using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Text;


public partial class Bulkmail_ImportData : System.Web.UI.Page
{
    static int img;
    Page p = new Page();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.IsPostBack == false)
        {
            if (Session["Bulkmail"] == null)
            {
                Response.Redirect("BulkmailLogin.aspx");
            }
            try
            {
            p = this.Page;
            img = 0;
            hlExcel.NavigateUrl = "~\\bulkmail\\ExcelData" + "\\Data.xls";
            ModCommon.Fill_Combo("Select Category_Kid, Category_Name from BEM_Category", ddlCategory);
        }
        catch
        {
        }
        }
        lblmsg.Visible = false;
        lblCount.Visible = false;
    }
    protected void btnUploadExcel_Click(object sender, EventArgs e)
    {
        try
        {

       

        if (fuExcel.HasFile)
        {
            string FileName;
            string FileType = Path.GetExtension(fuExcel.FileName);
            if (FileType == ".xls")
            {
               string FolderPath = Server.MapPath("ExcelData");
                FileName = fuExcel.FileName;
                fuExcel.SaveAs(FolderPath + "\\"+  FileName);
                ModCommon.showmessage("Excel Sheet Is Uploded Successfully.", lblmsg);
            }
            else
            {
                ModCommon.showmessage("Please Select .xls (Excel) File Only.", lblmsg);

            }
        }
        else
        {
            ModCommon.showmessage("Please Select .xls (Excel) File.", lblmsg);
        }
    }
    catch (System.Exception ex)
    {
        lblmsg.Visible = true;
        
        lblmsg.Text=ex.Message;
    }
    }
    protected void btnImport_Click(object sender, EventArgs e)
    {
        //string ExcelConnectionString ="Provider=Miscrosoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("ExcelData")+"\\Data.xls" + ";Extended Properties=""Excel 8.0;HDR=YES;"/"";

        try
        {
        string ExcelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("ExcelData") + "\\Data.xls" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

        OleDbConnection ExcelCon = new OleDbConnection(ExcelConnectionString);
        OleDbCommand ExcelCom = new OleDbCommand();
        ExcelCom.CommandText = "Select * From [Data$]";
        ExcelCom.CommandType = CommandType.Text;
        ExcelCom.Connection = ExcelCon;

        OleDbDataAdapter Exceladp = new OleDbDataAdapter();
        Exceladp.SelectCommand = ExcelCom;
        DataTable Exceldt = new DataTable();
        Exceldt.TableName = "ExcelDT";
        if (ExcelCon.State == ConnectionState.Open)
        {
            ExcelCon.Close();
        }



        ExcelCon.Open();
        Exceladp.Fill(Exceldt);
        ExcelCon.Close();

        System.Data.DataColumn dc1=new DataColumn();
        dc1.ColumnName="prospect_KID";
        //dc1.DataType = System.Type.GetType("System.String");
        
        System.Data.DataColumn dc2 = new DataColumn();
        dc2.ColumnName = "prospect_Code";
        //dc2.DataType = System.Type.GetType("System.String");

        System.Data.DataColumn dc3 = new DataColumn();
        dc3.ColumnName = "Categoryid";

        Exceldt.Columns.Add(dc1);
        Exceldt.Columns.Add(dc2);
        Exceldt.Columns.Add(dc3);

        DataTable Sqldt=new DataTable();
        Sqldt.TableName = "SQLDT";
        SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
        SqlCommand cmd = new SqlCommand("Select Email From BEM_Prospect_Detail where Categoryid ='"+ ddlCategory.SelectedItem.Value + "'", con);
        cmd.CommandType = CommandType.Text;
        SqlDataAdapter adp = new SqlDataAdapter();
        adp.SelectCommand = cmd;
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        adp.Fill(Sqldt);
        con.Close();

        


        Exceldt.TableName = "ExcelDT";
        Sqldt.TableName = "SQLDT";
       

       //ds.Relations.Add("DR", new DataColumn("Email"), new DataColumn("Email"));

        if (Exceldt.Rows.Count > 0)
        {
            for (int i = 0; i < Exceldt.Rows.Count; i++)
            {
                Exceldt.Rows[i]["prospect_KID"] = ModCommon.NewPrimaryKey(p);
                Exceldt.Rows[i]["prospect_Code"] = "BPC/" + DateTime.Now.Year + "/" + DateTime.Now.Month + "/" + DateTime.Now.Day + "/" + Convert.ToString(i);
//                Exceldt.Rows[i]["prospect_Code"] = CommonData.Generate_Code("select count(*) from BEM_Prospect_Detail where Prospect_Code='", "BPC");
                Exceldt.Rows[i]["Categoryid"] = ddlCategory.SelectedItem.Value;
                if (ModCommon.IsValidMail(Exceldt.Rows[i]["Email"].ToString(),p) == false)
                {
                    Exceldt.Rows[i].Delete();
                }
            }

            DataSet ds = new DataSet();
            ds.Tables.Add(Exceldt);
            ds.Tables.Add(Sqldt);

            string email;
            email = "";
           Boolean Bool=true;

           if (Sqldt.Rows.Count > 0)
           {
               for (int i = 0; i < Sqldt.Rows.Count; i++)
               {
                   if (Bool)
                   {
                       email = "'" + Sqldt.Rows[i]["Email"] + "'";
                       Bool = false;
                   }
                   else
                   {
                       email += ",";
                       email += "'" + Sqldt.Rows[i]["Email"] + "'";
                   }

               }

               Exceldt.DefaultView.RowFilter = "Email Not In(" + email + ")";
               Exceldt.AcceptChanges();
            

           }

              



            SqlBulkCopy SBC = new SqlBulkCopy(ConfigurationManager.AppSettings["ConnectionString"]);
            SBC.DestinationTableName = "BEM_Prospect_Detail";
            SBC.ColumnMappings.Add("prospect_KID", "prospect_KID");
            SBC.ColumnMappings.Add("prospect_Code", "prospect_Code");
            SBC.ColumnMappings.Add("CompanyName", "CompanyName");
            SBC.ColumnMappings.Add("ContactPerson", "ContactPerson");
            SBC.ColumnMappings.Add("Address", "Address");
            SBC.ColumnMappings.Add("Pin", "Pin");
            SBC.ColumnMappings.Add("Email", "Email");
            SBC.ColumnMappings.Add("AlternateEmail", "AlternateEmail");
            SBC.ColumnMappings.Add("MobileNo", "MobileNo");
            SBC.ColumnMappings.Add("PhoneNo", "PhoneNo");
            SBC.ColumnMappings.Add("Products", "Products");
            SBC.ColumnMappings.Add("AdditionalInfo", "AdditionalInfo");
            SBC.ColumnMappings.Add("Categoryid", "Categoryid");
            //SBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnRowsCopied);
            //SBC.NotifyAfter = 1000;
            SBC.WriteToServer(Exceldt.DefaultView.ToTable());

            ModCommon.showmessage(Exceldt.DefaultView.ToTable().Rows.Count + "Rows Copied. Remaining Records May Be Duplicates Or Not In Proper Format.", lblCount);

            


        }
        else
        {
            ModCommon.showmessage("No Record Found.", lblmsg);
            
        }

        Exceldt.Clear();
        Sqldt.Clear();
           
    }
    catch
    {
    }

        //Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(sExcelFileName) & ";Extended Properties=""Excel 8.0;HDR=YES;"""
    }
    //private void OnRowsCopied(object sender, SqlRowsCopiedEventArgs args)
    //{
    //    lblCount.Text += args.RowsCopied.ToString() + "Rows Are Copied"; 
    //}
}