using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml.Linq;

namespace BCD.admin
{
    public partial class Membersexcel : System.Web.UI.Page
    {
        Class1 obj = new Class1();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                string script = "$(document).ready(function () { $('[id*=btnSubmit]').click(); });";
                ClientScript.RegisterStartupScript(this.GetType(), "load", script, true);
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string kl = "";
            if (FileUpload1.FileBytes.Length > 1024)
            {
                string nname = DateTime.Now.Ticks.ToString();
                FileUpload FileUpload7 = FileUpload1;
                string virtualFolder7 = "~/dataimp1/" + nname;
                string physicalFolder7 = Server.MapPath(virtualFolder7);
                FileUpload7.SaveAs(physicalFolder7 + FileUpload7.FileName);
                kl = "~/dataimp1/" + nname + FileUpload7.FileName;
            }
            if (kl == "")
            {
                Label1.Text = "Data formart (as shown in above excel image). Fill in all the data in the template and upload";
            }
            else
            {
                OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(kl) + ";Extended Properties=Excel 12.0");
                try
                {
                    OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
                    oconn.Open();
                    OleDbDataReader odr = ocmd.ExecuteReader();

                    string M_Type="", Ndbano="", Ndbadate="", Bcdenroll="", Prefix="", F_name="", M_name="", L_name="", Father_name="", Dob="", Resid_1="", Resid_2="", Resid_3="", Resid_pin="", Mobile_1="", Mobile_2="", Ceased="", CeasedDate="", Reasonceas="";

                    while (odr.Read())
                    {
                        M_Type = valid(odr, 0);
                        Ndbano = valid(odr, 1);
                        //TIME1 = String.Format("{hh:mm tt}", DateTime.Now); 30-12-1899 07:04:00               
                        Ndbadate = valid(odr, 2);
                        // mdate = valid(odr, 2);
                        Bcdenroll = valid(odr, 3);
                        Prefix = valid(odr, 4);
                        F_name = valid(odr, 5);
                        M_name = valid(odr, 6);
                        L_name = valid(odr, 7);
                        Father_name = valid(odr, 8);
                        Dob = valid(odr, 9);
                        Resid_1 = valid(odr, 10);
                        Resid_2 = valid(odr, 11);
                        Resid_3 = valid(odr, 12);
                        Resid_pin = valid(odr, 13);
                        Mobile_1 = valid(odr, 14);
                        Mobile_2 = valid(odr, 15);
                        Ceased = valid(odr, 16);
                        CeasedDate = valid(odr, 17);
                        Reasonceas = valid(odr, 18);

                        insertdataintosql(M_Type, Ndbano, Ndbadate, Bcdenroll, Prefix, F_name, M_name, L_name, Father_name, Dob, Resid_1, Resid_2, Resid_3, Resid_pin, Mobile_1, Mobile_2, Ceased, CeasedDate, Reasonceas);

                    }
                    oconn.Close();
                }

                catch (DataException ee)
                {
                    Label1.Text = ee.Message;
                }
                finally
                {

                    Label1.Text = "Data Inserted Sucessfully";
                    //RegisterStartupScript("12", "<script>alert('Data Sucessfully insert')</script>");
                }
            }
        }
        public void insertdataintosql(string M_Type, string Ndbano,  string Ndbadate, string Bcdenroll, string Prefix, string F_name, string M_name, string L_name, string Father_name, string Dob, string Resid_1, string Resid_2, string Resid_3, string Resid_pin, string Mobile_1, string Mobile_2, string Ceased, string CeasedDate, string Reasonceas)
        {
            
            if (obj.RecordExist("Select * from Memberslist where Bcdenroll='" + Bcdenroll + "' ") == true)
            {
                //RegisterStartupScript("12", "<script>alert('EC No already exists')</script>");
                // Label2.Text = "Admission Fee Already Paid";
                //txtadmission.Focus();
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                
                conn.Open();
                cmd = new SqlCommand("update Memberslist set M_Type=@M_Type,Ndbano=@Ndbano,mdate=@mdate,Ndbadate=@Ndbadate,Bcdenroll=@Bcdenroll,Prefix=@Prefix,F_name=@F_name,M_name=@M_name,L_name=@L_name,Father_name=@Father_name,Dob=@Dob,Resid_1=@Resid_1,Resid_2=@Resid_2,Resid_3=@Resid_3,Resid_pin=@Resid_pin,Mobile_1=@Mobile_1,Mobile_2=@Mobile_2,Ceased=@Ceased,CeasedDate=@CeasedDate,Reasonceas=@Reasonceas where Bcdenroll='" + Bcdenroll + "' ", conn);
                cmd.Parameters.Add("@M_Type", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@M_Type"].Value = M_Type;
                cmd.Parameters.Add("@Ndbano", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Ndbano"].Value = Ndbano;
                cmd.Parameters.Add("@mdate", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@mdate"].Value = System.DateTime.Now.ToString("MM/dd/yyyy");
                cmd.Parameters.Add("@Ndbadate", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Ndbadate"].Value = Ndbadate;
                cmd.Parameters.Add("@Bcdenroll", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Bcdenroll"].Value = Bcdenroll;
                cmd.Parameters.Add("@Prefix", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Prefix"].Value = Prefix;
                cmd.Parameters.Add("@F_name", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@F_name"].Value = F_name;
                cmd.Parameters.Add("@M_name", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@M_name"].Value = M_name;
                cmd.Parameters.Add("@L_name", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@L_name"].Value = L_name;
                cmd.Parameters.Add("@Father_name", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Father_name"].Value = Father_name;
                cmd.Parameters.Add("@Dob", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Dob"].Value = Dob;
                cmd.Parameters.Add("@Resid_1", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Resid_1"].Value = Resid_1;
                cmd.Parameters.Add("@Resid_2", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Resid_2"].Value = Resid_2;
                cmd.Parameters.Add("@Resid_3", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Resid_3"].Value = Resid_3;

                cmd.Parameters.Add("@Resid_pin", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Resid_Pin"].Value = Resid_pin;

                cmd.Parameters.Add("@Mobile_1", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Mobile_1"].Value = Mobile_1;

                cmd.Parameters.Add("@Mobile_2", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Mobile_2"].Value = Mobile_2;

                cmd.Parameters.Add("@Ceased", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Ceased"].Value = Ceased;
                cmd.Parameters.Add("@CeasedDate", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@CeasedDate"].Value = CeasedDate;
                cmd.Parameters.Add("@Reasonceas", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Reasonceas"].Value = Reasonceas;
                cmd.ExecuteNonQuery();
                //
                conn.Close();
            }
            else
            {
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                
                conn.Open();
                cmd = new SqlCommand("insert into Memberslist (M_Type,Ndbano,mdate,Ndbadate,Bcdenroll,Prefix,F_name,M_name,L_name,Father_name,Dob,Resid_1,Resid_2,Resid_3,Resid_pin,Mobile_1,Mobile_2,Ceased,CeasedDate,Reasonceas) values (@M_Type,@Ndbano,@mdate,@Ndbadate,@Bcdenroll,@Prefix,@F_name,@M_name,@L_name,@Father_name,@Dob,@Resid_1,@Resid_2,@Resid_3,@Resid_pin,@Mobile_1,@Mobile_2,@Ceased,@CeasedDate,@Reasonceas)", conn);
                cmd.Parameters.Add("@M_Type", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@M_Type"].Value = M_Type;
                cmd.Parameters.Add("@Ndbano", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Ndbano"].Value = Ndbano;
                cmd.Parameters.Add("@mdate", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@mdate"].Value = System.DateTime.Now.ToString("MM/dd/yyyy");
                cmd.Parameters.Add("@Ndbadate", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Ndbadate"].Value = Ndbadate;
                cmd.Parameters.Add("@Bcdenroll", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Bcdenroll"].Value = Bcdenroll;
                cmd.Parameters.Add("@Prefix", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Prefix"].Value = Prefix;
                cmd.Parameters.Add("@F_name", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@F_name"].Value = F_name;
                cmd.Parameters.Add("@M_name", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@M_name"].Value = M_name;
                cmd.Parameters.Add("@L_name", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@L_name"].Value = L_name;
                cmd.Parameters.Add("@Father_name", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Father_name"].Value = Father_name;
                cmd.Parameters.Add("@Dob", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Dob"].Value = Dob;
                cmd.Parameters.Add("@Resid_1", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Resid_1"].Value = Resid_1;
                cmd.Parameters.Add("@Resid_2", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Resid_2"].Value = Resid_2;
                cmd.Parameters.Add("@Resid_3", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Resid_3"].Value = Resid_3;

                cmd.Parameters.Add("@Resid_pin", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Resid_Pin"].Value = Resid_pin;

                cmd.Parameters.Add("@Mobile_1", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Mobile_1"].Value = Mobile_1;

                cmd.Parameters.Add("@Mobile_2", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Mobile_2"].Value = Mobile_2;

                cmd.Parameters.Add("@Ceased", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Ceased"].Value = Ceased;
                cmd.Parameters.Add("@CeasedDate", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@CeasedDate"].Value = CeasedDate;
                cmd.Parameters.Add("@Reasonceas", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Reasonceas"].Value = Reasonceas;
                //flags

                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

        protected string valid(OleDbDataReader myreader, int stval)//if any columns are found null then they are replaced by zero
        {
            object val = myreader[stval];
            if (val != DBNull.Value)
                return val.ToString();
            else
                return Convert.ToString(0);
        }

    }
}