using System;
using System.Collections.Generic;
using System.Configuration;
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 Members : System.Web.UI.Page
    {
        Class1 obj = new Class1();
        SqlConnection connect = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        SqlDataAdapter adp;
        SqlCommand cmd;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                HealthNewsdata();
            }
        }
        public void HealthNewsdata()
        {
            string qrajneeti = "select top 100 * from Memberslist  order by id desc";
            DataSet dsrajneeti = obj.ReturnDataSet(qrajneeti);
            if (dsrajneeti != null && dsrajneeti.Tables[0].Rows.Count > 0)
            {
                DataGrid2.DataSource = dsrajneeti;
                DataGrid2.DataBind();
                DataGrid2.Visible = true;
            }
            else
            {
                DataGrid2.Visible = false;
            }
        }


        protected void DataGrid2_ItemCommand1(object source, DataGridCommandEventArgs e)
        {


            if (e.CommandName == "delete")
            {
                HiddenField idn = (HiddenField)e.Item.FindControl("HiddenField5");
                // HiddenField idn = (HiddenField)e.Item.FindControl("HiddenFidfeld4");          
                string del = "delete from Memberslist where id='" + idn.Value + "'";
                obj.DeleteRecord(del);
                HealthNewsdata();
                RegisterStartupScript("12", "<script>alert('Deleted.')</script>");

            }
            if (e.CommandName == "Edit")
            {
                HiddenField idn = (HiddenField)e.Item.FindControl("HiddenField5");
                HiddenField2.Value = idn.Value;
                string qrajneeti = "select * from Memberslist where id='" + idn.Value + "' order by id desc";
                DataSet dsrajneeti = obj.ReturnDataSet(qrajneeti);
                if (dsrajneeti != null && dsrajneeti.Tables[0].Rows.Count > 0)
                {
                    TextBox1.Text = dsrajneeti.Tables[0].Rows[0]["s_no"].ToString();
                    /* string []hj = dsrajneeti.Tables[0].Rows[0]["mdate"].ToString().Split('/');
                     string[] hj1 = dsrajneeti.Tables[0].Rows[0]["dob"].ToString().Split('/');
                     int jk = hj.Length;
                     int jk1 = hj1.Length;*/
                    TextBox26.Text = dsrajneeti.Tables[0].Rows[0]["Ndbano"].ToString();
                    TextBox3.Text = dsrajneeti.Tables[0].Rows[0]["Bcdenroll"].ToString();
                    /*if (jk>1)
                    {
                        string[] daten = hj[2].Split(' ');
                        TextBox2.Text = daten[0]+"-"+hj[0]+"-"+hj[1];
                    }*/
                    try
                    {
                        DateTime dtd = Convert.ToDateTime(dsrajneeti.Tables[0].Rows[0]["Ndbadate"].ToString());
                        TextBox27.Text = dtd.ToString("yyyy-MM-dd");
                        DateTime dtd1 = Convert.ToDateTime(dsrajneeti.Tables[0].Rows[0]["dob"].ToString());
                        TextBox7.Text = dtd1.ToString("yyyy-MM-dd");
                        DateTime dtd2 = Convert.ToDateTime(dsrajneeti.Tables[0].Rows[0]["datebcd"].ToString());
                        TextBox33.Text = dtd2.ToString("yyyy-MM-dd");
                        DateTime dtd3 = Convert.ToDateTime(dsrajneeti.Tables[0].Rows[0]["CeasedDate"].ToString());
                        TextBox29.Text = dtd3.ToString("yyyy-MM-dd");
                       // DateTime dtd4 = Convert.ToDateTime(dsrajneeti.Tables[0].Rows[0]["Member_Ship_of_other_Bars"].ToString());

                       
                       
                       
                       
                       // TextBox17.Text = dtd4.ToString("yyyy-MM-dd");
                    }
                    catch { }
                    TextBox34.Text = dsrajneeti.Tables[0].Rows[0]["Member_Ship_of_other_Bars"].ToString();
                    TextBox13.Text = dsrajneeti.Tables[0].Rows[0]["Prefix"].ToString();
                    TextBox2.Text = dsrajneeti.Tables[0].Rows[0]["F_name"].ToString();
                    TextBox4.Text = dsrajneeti.Tables[0].Rows[0]["M_name"].ToString();
                    TextBox5.Text = dsrajneeti.Tables[0].Rows[0]["L_name"].ToString();
                    TextBox19.Text = dsrajneeti.Tables[0].Rows[0]["shop_no"].ToString();
                    
                    /*if (jk1 > 1)
                    {
                        string[] daten = hj1[2].Split(' ');
                        TextBox9.Text = daten[0] + "-" + hj1[0] + "-" + hj1[1];
                    }*/
                    TextBox6.Text = dsrajneeti.Tables[0].Rows[0]["Father_name"].ToString();
                    TextBox8.Text = dsrajneeti.Tables[0].Rows[0]["Resid_1"].ToString();

                    TextBox9.Text = dsrajneeti.Tables[0].Rows[0]["Resid_2"].ToString();
                    TextBox10.Text = dsrajneeti.Tables[0].Rows[0]["Resid_3"].ToString();
                    TextBox11.Text = dsrajneeti.Tables[0].Rows[0]["Mobile_1"].ToString();
                    TextBox12.Text = dsrajneeti.Tables[0].Rows[0]["Mobile_2"].ToString();

                    DropDownList1.SelectedValue = dsrajneeti.Tables[0].Rows[0]["sstatus"].ToString();
                }
            }


        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string kl = "";
            if (FileUpload2.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;
            }
            string kl1 = "";
            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);
                kl1 = "../dataimp1/" + nname + FileUpload7.FileName;
            }

            if (HiddenField2.Value == "")
            {
                if (obj.RecordExist("Select * from Memberslist where Bcdenroll='" + TextBox3.Text + "'") == true)
                {
                    RegisterStartupScript("12", "<script>alert('Enroll_id already exists')</script>");
                }
                else
                {
                    connect.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,Photo,M_Sign,sstatus,s_no,Email_id,Aadhar_id,Voter_id,Locker_no,Lid_Card_No,I_card_no,Chamber_Alloted,Chamber_no,Member_Ship_of_other_Bars,Vehicle_no,Sticker_no,shop_no,datebcd) 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,@Photo,@M_Sign,@sstatus,@s_no,@Email_id,@Aadhar_id,@Voter_id,@Locker_no,@Lid_Card_No,@I_card_no,@Chamber_Alloted,@Chamber_no,@Member_Ship_of_other_Bars,@Vehicle_no,@Sticker_no,@shop_no,@datebcd)", connect);
                    cmd.Parameters.Add("@M_Type", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@M_Type"].Value = "M";
                    cmd.Parameters.Add("@s_no", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@s_no"].Value = TextBox1.Text;
                    cmd.Parameters.Add("@Ndbano", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Ndbano"].Value = TextBox26.Text;
                    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 = TextBox27.Text;
                    cmd.Parameters.Add("@Bcdenroll", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Bcdenroll"].Value = TextBox3.Text;
                    cmd.Parameters.Add("@Prefix", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Prefix"].Value = TextBox13.Text;
                    cmd.Parameters.Add("@F_name", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@F_name"].Value = TextBox2.Text;
                    cmd.Parameters.Add("@M_name", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@M_name"].Value = TextBox4.Text;
                    cmd.Parameters.Add("@L_name", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@L_name"].Value = TextBox5.Text;
                    cmd.Parameters.Add("@Father_name", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Father_name"].Value = TextBox6.Text;
                    cmd.Parameters.Add("@Dob", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Dob"].Value = TextBox7.Text;
                    cmd.Parameters.Add("@Resid_1", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Resid_1"].Value = TextBox8.Text;
                    cmd.Parameters.Add("@Resid_2", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Resid_2"].Value = TextBox9.Text;
                    cmd.Parameters.Add("@Resid_3", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Resid_3"].Value = TextBox10.Text;

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

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

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

                    cmd.Parameters.Add("@Ceased", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Ceased"].Value = TextBox28.Text;
                    cmd.Parameters.Add("@CeasedDate", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@CeasedDate"].Value = TextBox29.Text;
                    cmd.Parameters.Add("@Reasonceas", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Reasonceas"].Value = TextBox30.Text;

                    cmd.Parameters.Add("@Photo", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Photo"].Value = kl;
                    cmd.Parameters.Add("@M_Sign", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@M_Sign"].Value = kl1;
                    cmd.Parameters.Add("@sstatus", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@sstatus"].Value = DropDownList1.SelectedValue;
                    //,,,,,,,,,
                    cmd.Parameters.Add("@Email_id", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Email_id"].Value = TextBox14.Text;
                    cmd.Parameters.Add("@Aadhar_id", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Aadhar_id"].Value = TextBox21.Text;
                    cmd.Parameters.Add("@Voter_id", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Voter_id"].Value = TextBox22.Text;
                    cmd.Parameters.Add("@Locker_no", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Locker_no"].Value = TextBox23.Text;
                    cmd.Parameters.Add("@Lid_Card_No", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Lid_Card_No"].Value = TextBox24.Text;
                    cmd.Parameters.Add("@I_card_no", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@I_card_no"].Value = TextBox25.Text;
                    cmd.Parameters.Add("@Chamber_Alloted", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Chamber_Alloted"].Value = TextBox15.Text;
                    cmd.Parameters.Add("@Chamber_no", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Chamber_no"].Value = TextBox16.Text;

                    cmd.Parameters.Add("@Member_Ship_of_other_Bars", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Member_Ship_of_other_Bars"].Value = TextBox34.Text;
                    cmd.Parameters.Add("@Vehicle_no", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Vehicle_no"].Value = TextBox18.Text;
                    cmd.Parameters.Add("@Sticker_no", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Sticker_no"].Value = TextBox20.Text;
                    cmd.Parameters.Add("@shop_no", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@shop_no"].Value = TextBox19.Text;
                    cmd.Parameters.Add("@datebcd", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@datebcd"].Value = TextBox33.Text;

                    //flags

                    cmd.ExecuteNonQuery();
                    connect.Close();
                    HealthNewsdata();
                    RegisterStartupScript("12", "<script>alert('Inserted Succssesfully.')</script>");
                }
            }
            else
            {
                connect.Open();
                cmd = new SqlCommand("update Memberslist set datebcd=@datebcd,shop_no=@shop_no, Email_id=@Email_id,Aadhar_id=@Aadhar_id,Voter_id=@Voter_id,Locker_no=@Locker_no,Lid_Card_No=@Lid_Card_No,I_card_no=@I_card_no,Chamber_Alloted=@Chamber_Alloted,Chamber_no=@Chamber_no,Member_Ship_of_other_Bars=@Member_Ship_of_other_Bars,Vehicle_no=@Vehicle_no,Sticker_no=@Sticker_no, Photo=@Photo,M_Sign=@M_Sign,sstatus=@sstatus,s_no=@s_no,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 id='" + HiddenField2.Value + "' ", connect);
                cmd.Parameters.Add("@M_Type", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@M_Type"].Value = "M";
                cmd.Parameters.Add("@s_no", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@s_no"].Value = TextBox1.Text;
                cmd.Parameters.Add("@Ndbano", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Ndbano"].Value = TextBox26.Text;
                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 = TextBox27.Text;
                cmd.Parameters.Add("@Bcdenroll", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Bcdenroll"].Value = TextBox3.Text;
                cmd.Parameters.Add("@Prefix", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Prefix"].Value = TextBox13.Text;
                cmd.Parameters.Add("@F_name", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@F_name"].Value = TextBox2.Text;
                cmd.Parameters.Add("@M_name", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@M_name"].Value = TextBox4.Text;
                cmd.Parameters.Add("@L_name", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@L_name"].Value = TextBox5.Text;
                cmd.Parameters.Add("@Father_name", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Father_name"].Value = TextBox6.Text;
                cmd.Parameters.Add("@Dob", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Dob"].Value = TextBox7.Text;
                cmd.Parameters.Add("@Resid_1", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Resid_1"].Value = TextBox8.Text;
                cmd.Parameters.Add("@Resid_2", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Resid_2"].Value = TextBox9.Text;
                cmd.Parameters.Add("@Resid_3", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Resid_3"].Value = TextBox10.Text;

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

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

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

                cmd.Parameters.Add("@Ceased", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Ceased"].Value = TextBox28.Text;
                cmd.Parameters.Add("@CeasedDate", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@CeasedDate"].Value = TextBox29.Text;
                cmd.Parameters.Add("@Reasonceas", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Reasonceas"].Value = TextBox30.Text;

                cmd.Parameters.Add("@Photo", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Photo"].Value = kl;
                cmd.Parameters.Add("@M_Sign", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@M_Sign"].Value = kl1;
                cmd.Parameters.Add("@sstatus", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@sstatus"].Value = DropDownList1.SelectedValue;
                cmd.Parameters.Add("@Email_id", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Email_id"].Value = TextBox14.Text;
                cmd.Parameters.Add("@Aadhar_id", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Aadhar_id"].Value = TextBox21.Text;
                cmd.Parameters.Add("@Voter_id", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Voter_id"].Value = TextBox22.Text;
                cmd.Parameters.Add("@Locker_no", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Locker_no"].Value = TextBox23.Text;
                cmd.Parameters.Add("@Lid_Card_No", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Lid_Card_No"].Value = TextBox24.Text;
                cmd.Parameters.Add("@I_card_no", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@I_card_no"].Value = TextBox25.Text;
                cmd.Parameters.Add("@Chamber_Alloted", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Chamber_Alloted"].Value = TextBox15.Text;
                cmd.Parameters.Add("@Chamber_no", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Chamber_no"].Value = TextBox16.Text;

                cmd.Parameters.Add("@Member_Ship_of_other_Bars", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Member_Ship_of_other_Bars"].Value = TextBox34.Text;
                cmd.Parameters.Add("@Vehicle_no", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Vehicle_no"].Value = TextBox18.Text;
                cmd.Parameters.Add("@Sticker_no", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Sticker_no"].Value = TextBox20.Text;
                cmd.Parameters.Add("@shop_no", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@shop_no"].Value = TextBox19.Text;
                cmd.Parameters.Add("@datebcd", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@datebcd"].Value = TextBox33.Text;

                cmd.ExecuteNonQuery();
                connect.Close();
                HealthNewsdata();
                HiddenField2.Value = "";
                RegisterStartupScript("12", "<script>alert('Updated Succssesfully.')</script>");
            }
        }

        protected void DataGrid2_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
        {
            if (DataGrid2.Items.Count >= 0)
            {
                DataGrid2.CurrentPageIndex = e.NewPageIndex;
                HealthNewsdata();
            }
        }
        protected void Button2_Click(object sender, EventArgs e)
        {
            string empty = "";
            if (TextBox31.Text != "")
            {
                empty = empty + " and F_name like '%" + TextBox31.Text + "%'";
            }
            if (TextBox71.Text != "")
            {
                empty = empty + " and Mobile_1 = '" + TextBox71.Text + "'";
            }
            if (TextBox32.Text != "")
            {
                empty = empty + " and Ndbano = '" + TextBox32.Text + "'";
            }
            if (TextBox121.Text != "")
            {
                empty = empty + " and Bcdenroll = '" + TextBox121.Text + "'";
            }
            string qrajneeti = "select * from Memberslist where id!='0' " + empty + "  order by id desc";
            DataSet dsrajneeti = obj.ReturnDataSet(qrajneeti);
            if (dsrajneeti != null && dsrajneeti.Tables[0].Rows.Count > 0)
            {
                DataGrid2.DataSource = dsrajneeti;
                DataGrid2.DataBind();
                DataGrid2.Visible = true;
            }
            else
            {
                DataGrid2.Visible = false;
            }
        }
    }
}