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;

namespace Newdelhibarassocia.dhadmin
{
    public partial class Membersdueexcel : 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 = "", Paid = "", F_name = "", dues = "";

                    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, 7);
                        // mdate = valid(odr, 2);
                        Bcdenroll = valid(odr, 3);
                        
                        F_name = valid(odr, 4);
                        dues = valid(odr, 5);
                        Paid = valid(odr, 6);
                     

                        insertdataintosql(M_Type, Ndbano, Ndbadate, Bcdenroll, F_name, dues, Paid);

                    }
                    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 F_name, string dues, string Paid)
        {
            string[] dds = Ndbadate.Split(',');
            int dl=dds.Length;
            if (dl > 0)
            {
                if (dds[0]!="0")
                {
                    for (int i = 0; i < dl; i++)
                    {
                        if (i+1 == dl)
                        {
                            if (Convert.ToInt32(dds[i].Length) >= 5)
                            {
                                Ndbadate = dds[i];
                            }
                            else
                            {

                                Ndbadate = dds[i - 1];
                            }

                        }
                    }
                }
            }

            if (obj.RecordExist("Select * from Membersduelist 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 Membersduelist set M_Type=@M_Type,Ndbano=@Ndbano,mdate=@mdate,Ndbadate=@Ndbadate,Bcdenroll=@Bcdenroll,F_name=@F_name,dues=@dues,Paid=@Paid 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("@F_name", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@F_name"].Value = F_name;
                cmd.Parameters.Add("@dues", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@dues"].Value = dues;
                cmd.Parameters.Add("@Paid", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Paid"].Value = Paid;
                
                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 Membersduelist (M_Type,Ndbano,mdate,Ndbadate,Bcdenroll,F_name,dues,Paid) values (@M_Type,@Ndbano,@mdate,@Ndbadate,@Bcdenroll,@F_name,@dues,@Paid)", 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("@F_name", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@F_name"].Value = F_name;
                cmd.Parameters.Add("@dues", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@dues"].Value = dues;
                cmd.Parameters.Add("@Paid", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Paid"].Value = Paid;

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

    }
}