using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Reflection.Emit;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Newdelhibarassocia.dhadmin
{
    public partial class addpenalty : 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)
            {
                DateTime utcTime = DateTime.UtcNow;

                // Get Indian Standard Time Zone
                TimeZoneInfo indiaTimeZone = TimeZoneInfo.FindSystemTimeZoneById("India Standard Time");

                // Convert UTC to IST
                DateTime indiaTime = TimeZoneInfo.ConvertTimeFromUtc(utcTime, indiaTimeZone);

                TextBox121.Text = indiaTime.ToString("yyyy-MM-dd");
                
                billtypedatak();
                SetInitialRow();
                billadd();
               // userdetails();
               // datak();
            }
        }
        void billadd()
        {
            int kk = 0;
            string qras = "select top 1 * from newdelhibar_bill order by id desc";
            DataSet dss = obj.ReturnDataSet(qras);
            if (dss != null && dss.Tables[0].Rows.Count > 0)
            {
                kk = Convert.ToInt32(dss.Tables[0].Rows[0]["id"].ToString()) + 1;
            }
            TextBox7.Text=kk.ToString();
        }
        // array list 
        #region arrylist for objection
        private void SetInitialRow()

        {

            DataTable dt = new DataTable();

            DataRow dr = null;

            dt.Columns.Add(new DataColumn("RowNumber", typeof(string)));

            dt.Columns.Add(new DataColumn("Column1", typeof(string)));

            dt.Columns.Add(new DataColumn("Column2", typeof(string)));

           

            dr = dt.NewRow();

            dr["RowNumber"] = 1;

            dr["Column1"] = string.Empty;

            dr["Column2"] = string.Empty;

           

            dt.Rows.Add(dr);

            //dr = dt.NewRow();
            //Store the DataTable in ViewState

            ViewState["CurrentTable"] = dt;

            Gridview1.DataSource = dt;

            Gridview1.DataBind();

        }


        private void AddNewRowToGrid()

        {

            int rowIndex = 0;



            if (ViewState["CurrentTable"] != null)

            {

                DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];

                DataRow drCurrentRow = null;

                if (dtCurrentTable.Rows.Count > 0)

                {

                    for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)

                    {

                        //extract the TextBox values

                        TextBox box1 = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("TextBox1");

                        TextBox box2 = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("TextBox2");

                       

                        drCurrentRow = dtCurrentTable.NewRow();

                        drCurrentRow["RowNumber"] = i + 1;



                        dtCurrentTable.Rows[i - 1]["Column1"] = box1.Text;

                        dtCurrentTable.Rows[i - 1]["Column2"] = box2.Text;

                        

                        rowIndex++;

                    }

                    dtCurrentTable.Rows.Add(drCurrentRow);

                    ViewState["CurrentTable"] = dtCurrentTable;



                    Gridview1.DataSource = dtCurrentTable;

                    Gridview1.DataBind();

                }

            }

            else

            {

                Response.Write("ViewState is null");

            }



            //Set Previous Data on Postbacks

            SetPreviousData();

        }


        private void SetPreviousData()

        {

            int rowIndex = 0;

            if (ViewState["CurrentTable"] != null)

            {

                DataTable dt = (DataTable)ViewState["CurrentTable"];

                if (dt.Rows.Count > 0)

                {

                    for (int i = 0; i < dt.Rows.Count; i++)

                    {

                        TextBox box1 = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("TextBox1");

                        TextBox box2 = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("TextBox2");

                       


                        box1.Text = dt.Rows[i]["Column1"].ToString();

                        box2.Text = dt.Rows[i]["Column2"].ToString();

                        

                        rowIndex++;

                    }

                }

            }

        }
        protected void LinkButton1_Click(object sender, EventArgs e)
        {
            LinkButton lb = (LinkButton)sender;
            GridViewRow gvRow = (GridViewRow)lb.NamingContainer;
            int rowID = gvRow.RowIndex;
            if (ViewState["CurrentTable"] != null)
            {

                DataTable dt = (DataTable)ViewState["CurrentTable"];
                if (dt.Rows.Count > 1)
                {
                    if (gvRow.RowIndex < dt.Rows.Count - 1)
                    {
                        //Remove the Selected Row data and reset row number  
                        dt.Rows.Remove(dt.Rows[rowID]);
                        ResetRowID(dt);
                    }
                }

                //Store the current data in ViewState for future reference  
                ViewState["CurrentTable"] = dt;

                //Re bind the GridView for the updated data  
                Gridview1.DataSource = dt;
                Gridview1.DataBind();
            }

            //Set Previous Data on Postbacks  
            SetPreviousData();
        }
        private void ResetRowID(DataTable dt)
        {
            int rowNumber = 1;
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    row[0] = rowNumber;
                    rowNumber++;
                }
            }
        }
        protected void ButtonAdd_Click(object sender, EventArgs e)

        {

            AddNewRowToGrid();

        }

        #endregion

        protected void Button1_Click(object sender, EventArgs e)
        {
            if (TextBox31.Text != "")
            {
                if (Literal8.Text != "")
                {
                    billadd();
                    connect.Open();
                    cmd = new SqlCommand("insert into newdelhibar_bill (Billno,Name,Mobile,NDBAno,BCDno,Daten,Address,Headermessage,Paymentmode,Footermessage,totalamount,btype,Status,Chequeno,chedate,fdate,todate) values (@Billno,@Name,@Mobile,@NDBAno,@BCDno,@Daten,@Address,@Headermessage,@Paymentmode,@Footermessage,@totalamount,@btype,@Status,@Chequeno,@chedate,@fdate,@todate)", connect);

                    cmd.Parameters.Add("@Billno", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Billno"].Value = TextBox7.Text;
                    cmd.Parameters.Add("@Name", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Name"].Value = TextBox31.Text;
                    cmd.Parameters.Add("@Mobile", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Mobile"].Value = TextBox71.Text;
                    cmd.Parameters.Add("@NDBAno", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@NDBAno"].Value = TextBox2.Text;
                    cmd.Parameters.Add("@BCDno", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@BCDno"].Value = TextBox4.Text;
                    cmd.Parameters.Add("@Daten", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Daten"].Value = TextBox121.Text;
                    cmd.Parameters.Add("@Address", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Address"].Value = TextBox5.Text;
                    cmd.Parameters.Add("@Headermessage", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Headermessage"].Value = TextBox6.Text;
                    cmd.Parameters.Add("@Paymentmode", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Paymentmode"].Value = DropDownList2.SelectedValue;
                    cmd.Parameters.Add("@Footermessage", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Footermessage"].Value = TextBox3.Text;
                    cmd.Parameters.Add("@totalamount", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@totalamount"].Value = Literal8.Text;
                    cmd.Parameters.Add("@btype", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@btype"].Value = DropDownList1.SelectedValue;
                    cmd.Parameters.Add("@Status", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Status"].Value = "SUCCESS";

                    //Chequeno,chedate,fdate,todate
                    cmd.Parameters.Add("@Chequeno", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Chequeno"].Value = TextBox8.Text;
                    cmd.Parameters.Add("@chedate", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@chedate"].Value = TextBox9.Text;
                    cmd.Parameters.Add("@fdate", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@fdate"].Value = TextBox10.Text;
                    cmd.Parameters.Add("@todate", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@todate"].Value = TextBox11.Text;

                    cmd.ExecuteNonQuery();
                    connect.Close();

                    for (int i = 0; i < Gridview1.Rows.Count; i++)
                    {
                        TextBox bnn = (TextBox)Gridview1.Rows[i].FindControl("TextBox1");
                        TextBox namobileme = (TextBox)Gridview1.Rows[i].FindControl("TextBox2");


                        connect.Open();
                        cmd = new SqlCommand("insert into newdelhibar_rowbill (Billno,Particulars,Amount) values (@Billno,@Particulars,@Amount)", connect);

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

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

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



                        cmd.ExecuteNonQuery();
                        connect.Close();

                    }
                    Panel1.Visible = true;
                    Panel2.Visible = true;
                    userdetails();
                    datak();
                    billadd();
                    RegisterStartupScript("12", "<script>alert('Bill Created Succssesfully.')</script>");
                }
                else
                {
                    RegisterStartupScript("12", "<script>alert('Amount not blank..')</script>");
                }
            }
            else
            {
                RegisterStartupScript("12", "<script>alert('Name not blank..')</script>");
            }
        }

        void photowithname()
        {
            string qra = "select * from tb_members where Post='President' order by id desc";
            DataSet dsra = obj.ReturnDataSet(qra);
            if (dsra != null && dsra.Tables[0].Rows.Count > 0)
            {

                Literal24.Text = dsra.Tables[0].Rows[0]["Post"].ToString();
                Literal25.Text = dsra.Tables[0].Rows[0]["Name"].ToString();
               
                Image1.ImageUrl = dsra.Tables[0].Rows[0]["Photo"].ToString();

                Literal31.Text = dsra.Tables[0].Rows[0]["Post"].ToString();
                Literal30.Text = dsra.Tables[0].Rows[0]["Name"].ToString();

                Image4.ImageUrl = dsra.Tables[0].Rows[0]["Photo"].ToString();


            }
        }
        void photowithname1()
        {
            string qra = "select * from tb_members where Post='Hony. Secretary' order by id desc";
            DataSet dsra = obj.ReturnDataSet(qra);
            if (dsra != null && dsra.Tables[0].Rows.Count > 0)
            {

                Literal27.Text = dsra.Tables[0].Rows[0]["Post"].ToString();
               
                Literal26.Text = dsra.Tables[0].Rows[0]["Name"].ToString();
               
                Image2.ImageUrl = dsra.Tables[0].Rows[0]["Photo"].ToString();

                Literal33.Text = dsra.Tables[0].Rows[0]["Post"].ToString();

                Literal32.Text = dsra.Tables[0].Rows[0]["Name"].ToString();

                Image5.ImageUrl = dsra.Tables[0].Rows[0]["Photo"].ToString();



            }
        }
        void photowithname2()
        {
            string qra = "select * from tb_members where Post='Treasurer' order by id desc";
            DataSet dsra = obj.ReturnDataSet(qra);
            if (dsra != null && dsra.Tables[0].Rows.Count > 0)
            {

               
                Literal29.Text = dsra.Tables[0].Rows[0]["Post"].ToString();
               
                Literal28.Text = dsra.Tables[0].Rows[0]["Name"].ToString();

                Image3.ImageUrl = dsra.Tables[0].Rows[0]["Photo"].ToString();


                Literal35.Text = dsra.Tables[0].Rows[0]["Post"].ToString();

                Literal34.Text = dsra.Tables[0].Rows[0]["Name"].ToString();

                Image6.ImageUrl = dsra.Tables[0].Rows[0]["Photo"].ToString();



            }
        }
        public static string NumberToWords(int number)
        {
            if (number == 0)
                return "Zero";

            if (number < 0)
                return "Minus " + NumberToWords(Math.Abs(number));

            string words = "";

            if ((number / 1000) > 0)
            {
                words += NumberToWords(number / 1000) + " Thousand ";
                number %= 1000;
            }

            if ((number / 100) > 0)
            {
                words += NumberToWords(number / 100) + " Hundred ";
                number %= 100;
            }

            if (number > 0)
            {
                string[] unitsMap =
                { "Zero","One","Two","Three","Four","Five","Six","Seven","Eight","Nine",
          "Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen",
          "Seventeen","Eighteen","Nineteen" };

                string[] tensMap =
                { "Zero","Ten","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety" };

                if (number < 20)
                    words += unitsMap[number];
                else
                {
                    words += tensMap[number / 10];
                    if ((number % 10) > 0)
                        words += " " + unitsMap[number % 10];
                }
            }

            return words.Trim();
        }
        void userdetails()
        {
           // photowithname();
           // photowithname1();
          //  photowithname2();
            Panel3.Visible=true;
            Panel1.Visible = true;
            Panel2.Visible = true;
            //  string qras = "select * from newdelhibar_bill where Billno='" + TextBox7.Text + "'";
           // string bno = "7095";
            string bno = TextBox7.Text;
            string qras = "select * from newdelhibar_bill where Billno='"+ bno + "'";
            DataSet dss = obj.ReturnDataSet(qras);
            if (dss != null && dss.Tables[0].Rows.Count > 0)
            {
                
                Literal10.Text=DropDownList1.SelectedValue.ToString();
                
                Literal3.Text = dss.Tables[0].Rows[0]["Name"].ToString();
                if (dss.Tables[0].Rows[0]["Daten"].ToString() != "")
                {
                    DateTime dt = Convert.ToDateTime(dss.Tables[0].Rows[0]["Daten"].ToString());
                    if (dt.ToString("dd/MM/yyyy").Replace("12:00:00 AM", "") != "01/01/1900")
                    {
                        Literal2.Text = dt.ToString("dd/MM/yyyy").Replace("12:00:00 AM", "");
                    }
                }
                Literal4.Text = dss.Tables[0].Rows[0]["NDBAno"].ToString();
                Literal22.Text = Literal23.Text = dss.Tables[0].Rows[0]["Mobile"].ToString();
                Literal1.Text = dss.Tables[0].Rows[0]["Billno"].ToString();
                Literal4.Text = dss.Tables[0].Rows[0]["NDBAno"].ToString();
                Literal5.Text = dss.Tables[0].Rows[0]["BCDno"].ToString();
                Literal6.Text = dss.Tables[0].Rows[0]["Address"].ToString();
                Literal10.Text = Literal18.Text= dss.Tables[0].Rows[0]["btype"].ToString();
               // Literal7.Text = dss.Tables[0].Rows[0]["Headermessage"].ToString();
                Literal9.Text = dss.Tables[0].Rows[0]["Footermessage"].ToString();


                //

               // Literal18.Text = DropDownList1.SelectedValue.ToString();

                Literal13.Text = dss.Tables[0].Rows[0]["Name"].ToString();
                if (dss.Tables[0].Rows[0]["Daten"].ToString() != "")
                {
                    DateTime dt = Convert.ToDateTime(dss.Tables[0].Rows[0]["Daten"].ToString());
                    if (dt.ToString("dd/MM/yyyy").Replace("12:00:00 AM", "") != "01/01/1900")
                    {
                        Literal12.Text = dt.ToString("dd/MM/yyyy").Replace("12:00:00 AM", "").Replace("01/01/1900", "");
                    }
                }
                Literal14.Text = dss.Tables[0].Rows[0]["NDBAno"].ToString();

                Literal11.Text = dss.Tables[0].Rows[0]["Billno"].ToString();
                Literal14.Text = dss.Tables[0].Rows[0]["NDBAno"].ToString();
                Literal15.Text = dss.Tables[0].Rows[0]["BCDno"].ToString();
                Literal16.Text = dss.Tables[0].Rows[0]["Address"].ToString();
               
                string fmessadte = "";
                if (dss.Tables[0].Rows[0]["fdate"].ToString() != "")
                {
                    DateTime dts = Convert.ToDateTime(dss.Tables[0].Rows[0]["fdate"].ToString().Replace("12:00:00 AM", ""));
                    if (dts.ToString("dd/MM/yyyy").Replace("12:00:00 AM", "") != "01/01/1900")
                    {
                        fmessadte = "From Date:" + dts.ToString("dd/MM/yyy").Replace("12:00:00 AM", "").Replace("01/01/1900", "") + " ";
                    }
                }
                if (dss.Tables[0].Rows[0]["todate"].ToString() != "")
                {
                    DateTime dt2 = Convert.ToDateTime(dss.Tables[0].Rows[0]["todate"].ToString().Replace("12:00:00 AM", ""));
                    if (dt2.ToString("dd/MM/yyyy").Replace("12:00:00 AM", "") != "01/01/1900")
                    {
                        fmessadte = fmessadte + "To Date:" + dt2.ToString("dd/MM/yyy").Replace("12:00:00 AM", "").Replace("01/01/1900", "");
                    }
                }
                Literal20.Text = dss.Tables[0].Rows[0]["Footermessage"].ToString() ;
                Literal9.Text = dss.Tables[0].Rows[0]["Footermessage"].ToString()  ;

                string q = "Select SUM(CAST(Amount as int)) from newdelhibar_rowbill where Billno='"+ bno + "'";
                DataSet ds = obj.ReturnDataSet(q);
                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {

                    Literal8.Text = ds.Tables[0].Rows[0][0].ToString();
                    Literal19.Text = ds.Tables[0].Rows[0][0].ToString();
                    Literal38.Text = Literal42.Text= ds.Tables[0].Rows[0][0].ToString();
                    int number;
                    if (int.TryParse(Literal19.Text, out number))
                    {
                        Literal36.Text = Literal37.Text  ="Total Amount in Words : <br />"+ NumberToWords(number);
                    }

                    DateTime dts1 = Convert.ToDateTime(dss.Tables[0].Rows[0]["chedate"].ToString().Replace("12:00:00 AM", ""));
                    
                    //,,,
                    string kheadermessage = "Sir/Madam, We Acknowledge with thanks the receipt for Rs. "+ Literal8.Text + " vide Cheque No./UPI No. "+ dss.Tables[0].Rows[0]["Chequeno"].ToString() + " Dated : "+ dts1.ToString("dd/MM/yyyy").Replace("01/01/1900", "") + " , Bank towards the following:";

                    Literal7.Text = kheadermessage;
                    Literal17.Text = kheadermessage;
                    string upd = "update newdelhibar_bill set totalamount='" + Literal8.Text + "' where Billno='" + TextBox7.Text + "'";
                    obj.SaveRecord(upd);
                }
                if (Literal10.Text == "SUBSCRIPTION FEE")
                {
                    int outsdtingamt= Convert.ToInt32(Literal39.Text);
                    int paidamt = Convert.ToInt32(Literal38.Text);
                    int balnacek = outsdtingamt - paidamt;
                    Literal40.Text= Literal43.Text=Convert.ToString(balnacek);
                    Panel6.Visible = true;
                    Panel5.Visible = false;
                    Panel8.Visible = true;
                    Panel7.Visible = false;
                    string qrdue = "select * from Membersduelist where  Ndbano='" + Literal4.Text + "'  order by id desc";
                    DataSet dsdue = obj.ReturnDataSet(qrdue);
                    string ddue = "0";
                    int k = 0;
                    int kk = 0;
                    if (dsdue != null && dsdue.Tables[0].Rows.Count > 0)
                    {
                        ddue = dsdue.Tables[0].Rows[0]["dues"].ToString();
                        DateTime dt2 = Convert.ToDateTime(dsdue.Tables[0].Rows[0]["Ndbadate"].ToString());

                        DateTime dt1 = DateTime.Now.AddDays(-1);
                        var monthCount = dt1.Year != dt1.Year ? ((dt1.Year - dt2.Year - 1) * 12) + (13 - dt2.Month + dt1.Month) : dt1.Month - dt2.Month;
                        k = 100 * monthCount;
                        if (ddue != "")
                        {
                            int dueson = Convert.ToInt32(ddue) + k;
                            kk = Convert.ToInt32(dueson) - Convert.ToInt32(Literal8.Text);
                        }

                        if (kk >= 0)
                        {
                            string updue = "update Membersduelist set dues='" + kk + "',Ndbadate='" + TextBox12.Text + "' where Ndbano='" + Literal4.Text + "'";
                            obj.SaveRecord(updue);
                        }
                        else
                        {
                           // int result = kk / 100;
                            //string kj = result.ToString().Replace("-", "");

                            //dt2 = dt2.AddMonths(Convert.ToInt32(kj) + monthCount);
                            string updue = "update Membersduelist set dues='0' ,Ndbadate='" + TextBox12.Text + "' where Ndbano='" + Literal4.Text + "'";
                            obj.SaveRecord(updue);
                        }
                    }
                   
                }
                if (DropDownList1.SelectedValue == "NEW MEMBERSHIP")
                {
                   
                    connect.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)", connect);
                    cmd.Parameters.Add("@M_Type", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@M_Type"].Value = "";
                    cmd.Parameters.Add("@Ndbano", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Ndbano"].Value = TextBox2.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 = TextBox12.Text;
                    cmd.Parameters.Add("@Bcdenroll", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Bcdenroll"].Value = TextBox4.Text;
                    cmd.Parameters.Add("@F_name", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@F_name"].Value = TextBox31.Text;
                    cmd.Parameters.Add("@dues", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@dues"].Value = "0";
                    cmd.Parameters.Add("@Paid", System.Data.SqlDbType.VarChar);
                    cmd.Parameters["@Paid"].Value = Literal19.Text;

                    //flags
                    cmd.ExecuteNonQuery();
                    connect.Close();

                }
            }
        }
        void datak()
        {
           // string bno = "7095";
            string bno = TextBox7.Text;
            string qrajneeti = "select * from newdelhibar_rowbill where Billno='"+ bno + "' order by id asc";
            DataSet dsrajneeti = obj.ReturnDataSet(qrajneeti);
            if (dsrajneeti != null && dsrajneeti.Tables[0].Rows.Count > 0)
            {
                DataGrid5.Visible = true;
                DataGrid5.DataSource = dsrajneeti;
                DataGrid5.DataBind();

                DataGrid1.Visible = true;
                DataGrid1.DataSource = dsrajneeti;
                DataGrid1.DataBind();

            }
            else
            {
                DataGrid5.Visible = false;
            }
        }
        void billtypedatak()
        {
            string qrajneeti = "select * from tb_billmaster order by id desc";
            DataSet dsrajneeti = obj.ReturnDataSet(qrajneeti);
            if (dsrajneeti != null && dsrajneeti.Tables[0].Rows.Count > 0)
            {
                DropDownList1.Visible = true;
                DropDownList1.DataSource = dsrajneeti;
                DropDownList1.DataTextField = "btype";
                DropDownList1.DataValueField = "btype";
                DropDownList1.DataBind();
                DropDownList1.Items.Insert(0, "Select");

            }
            
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            string qrajneeti = "select * from Memberslist where Ndbano='"+ TextBox2.Text + "'  order by id desc";
            DataSet dsrajneeti = obj.ReturnDataSet(qrajneeti);
            if (dsrajneeti != null && dsrajneeti.Tables[0].Rows.Count > 0)
            {
                TextBox31.Text = dsrajneeti.Tables[0].Rows[0]["F_name"].ToString() + " " + dsrajneeti.Tables[0].Rows[0]["M_name"].ToString() + " " + dsrajneeti.Tables[0].Rows[0]["L_name"].ToString();
                TextBox71.Text = dsrajneeti.Tables[0].Rows[0]["Mobile_1"].ToString();
                TextBox4.Text = dsrajneeti.Tables[0].Rows[0]["Bcdenroll"].ToString();
                TextBox5.Text = dsrajneeti.Tables[0].Rows[0]["Resid_1"].ToString();
                if (dsrajneeti.Tables[0].Rows[0]["Ndbadate"].ToString() != "0")
                {
                    string dts =dsrajneeti.Tables[0].Rows[0]["Ndbadate"].ToString();
                    CalculateMembershipDue(dts);
                    GetTotals(dsrajneeti.Tables[0].Rows[0]["Bcdenroll"].ToString());
                }

            }
        }
        private void CalculateMembershipDue(string dts)
        {
            // ✅ Membership Date from TextBox
            DateTime membershipDate;

            string[] formats = { "yyyy-MM-dd", "dd/MM/yyyy" };

            if (!DateTime.TryParseExact(
                    dts,
                    formats,
                    CultureInfo.InvariantCulture,
                    DateTimeStyles.None,
                    out membershipDate))
            {
               // Label1.Text = "Invalid Date!";
                return;
            }

            DateTime today = DateTime.Today;

            DateTime dtt = new DateTime(2016, 1, 1);
            DateTime discountStart = new DateTime(2020, 4, 1);
            DateTime discountEnd = new DateTime(2021, 9, 30);

            decimal totalAmount = 0;

         

            // ✅ Loop month by month
            DateTime loopDate = new DateTime(membershipDate.Year, membershipDate.Month, 1);

            while (loopDate <= today)
            {
                decimal monthAmount = 0;

                if (loopDate >= discountStart && loopDate <= discountEnd)
                {
                    monthAmount = 0; // full discount
                }
                else if (loopDate >= dtt)
                {
                    // 👉 You can change this logic if you have count
                    monthAmount = 100; // default
                }
                else
                {
                    monthAmount = 50;
                }

                totalAmount += monthAmount;

              
                loopDate = loopDate.AddMonths(1);
            }

            // ✅ Final Discount (optional 10%)
            //  decimal discount = totalAmount * 0.10m;
            decimal finalAmount = totalAmount;

            // ✅ Labels
            Literal39.Text = Literal41.Text= finalAmount.ToString("0");
        }

        private void GetTotals( string bcd)
        {
            string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            decimal totalMembers = 0;
            decimal totalBills = 0;

            using (SqlConnection con = new SqlConnection(connStr))
            {
                con.Open();

                // ✅ 1. SUM from Membersallrecpit
                SqlCommand cmd1 = new SqlCommand(
                    "SELECT ISNULL(SUM(Amount),0) FROM Membersallrecpit where Bcdenroll = '" + bcd + "'", con);

                object result1 = cmd1.ExecuteScalar();
                totalMembers = Convert.ToDecimal(result1);


                // ✅ 2. SUM from newdelhibar_bill
                SqlCommand cmd2 = new SqlCommand(
                    "SELECT ISNULL(SUM(TRY_CAST(totalamount AS DECIMAL(18,2))),0) FROM newdelhibar_bill where  btype!='NEW MEMBERSHIP' and BCDno = '" + bcd + "'", con);

                object result2 = cmd2.ExecuteScalar();
                totalBills = Convert.ToDecimal(result2);
            }

            // ✅ Bind to Literals
           // Literal1.Text = totalMembers.ToString("0.00");
           // Literal2.Text = totalBills.ToString("0.00");
            decimal dues = 0;

            if (!string.IsNullOrWhiteSpace(Literal39.Text) && decimal.TryParse(Literal39.Text, out dues))
            {
                decimal due = totalBills + totalMembers;
                decimal duetotal = dues - due;

                Literal39.Text = Literal41.Text = duetotal.ToString("0");
            }
            else
            {
                Literal39.Text = "0"; // or handle error
            }
        }

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (DropDownList1.SelectedValue == "NEW MEMBERSHIP")
            {
                Panel4.Visible = true;
                Literal21.Text = "<a href='mcertificateview.aspx?id=" + TextBox2.Text + "' target='_blank' class='btn btn-primary'>Certificate</a>";

                DateTime today = DateTime.Today;
                DateTime firstDayOfMonth = new DateTime(today.Year, today.Month, 1);

                TextBox12.Text = firstDayOfMonth.ToString("yyyy-MM-dd");

                TextBox10.Text = firstDayOfMonth.ToString("yyyy-MM-dd");
                // Add 15 months, then get last day of that month
                DateTime lastDayAfter15Months = firstDayOfMonth
                                                .AddMonths(12)   // go to next month
                                                .AddDays(-1);    // go back 1 day

                TextBox11.Text = lastDayAfter15Months.ToString("yyyy-MM-dd");
               // TextBox11.Text = firstDayOfMonth.AddMonths(15).ToString("yyyy-MM-dd");
                TextBox3.Text = DropDownList1.SelectedValue + " SUBSCRIPTION FEE PAID FROM " + firstDayOfMonth.ToString("dd-MM-yyyy") + " TO " + lastDayAfter15Months.ToString("dd-MM-yyyy");
            }
            if (DropDownList1.SelectedValue == "SUBSCRIPTION FEE")
            {
                DateTime today = DateTime.Today;

                // First day of next month, then subtract 1 day
                DateTime lastDayOfMonth = new DateTime(today.Year, today.Month, 1)
                                            .AddMonths(1)
                                            .AddDays(-1);

                TextBox12.Text = lastDayOfMonth.ToString("yyyy-MM-dd");
                
                TextBox10.Text = lastDayOfMonth.ToString("yyyy-MM-dd");
                TextBox11.Text = lastDayOfMonth.ToString("yyyy-MM-dd");
                TextBox3.Text = "SUBSCRIPTION FEE PAID TILL " + lastDayOfMonth.ToString("dd-MM-yyyy") ;
            }
        }

        //protected void TextBox12_TextChanged(object sender, EventArgs e)
        //{
        //    if (DropDownList1.SelectedValue == "NEW MEMBERSHIP")
        //    {
        //        // Panel4.Visible = true;
        //        // Literal21.Text = "<a href='mcertificateview.aspx?id=" + TextBox2.Text + "' target='_blank' class='btn btn-primary'>Certificate</a>";

        //        DateTime today;

        //        if (DateTime.TryParse(TextBox12.Text, out today))
        //        {
        //            DateTime firstDayOfMonth = new DateTime(today.Year, today.Month, 1);

        //            TextBox12.Text = firstDayOfMonth.ToString("yyyy-MM-dd");
        //            TextBox10.Text = firstDayOfMonth.ToString("yyyy-MM-dd");
        //            // Add 15 months, then get last day of that month
        //            DateTime lastDayAfter15Months = firstDayOfMonth
        //                                            .AddMonths(12)   // go to next month
        //                                            .AddDays(-1);    // go back 1 day

        //            TextBox11.Text = lastDayAfter15Months.ToString("yyyy-MM-dd");
        //            // TextBox11.Text = firstDayOfMonth.AddMonths(15).ToString("yyyy-MM-dd");
        //            TextBox3.Text = DropDownList1.SelectedValue + " SUBSCRIPTION FEE PAID FROM " + firstDayOfMonth.ToString("dd-MM-yyyy") + " TO " + lastDayAfter15Months.ToString("dd-MM-yyyy");

        //        }
        //        else
        //        {
        //            // Handle invalid date input
        //           // TextBox12.Text = "Invalid Date";
        //        }
        //        }
        //    if (DropDownList1.SelectedValue == "SUBSCRIPTION FEE")
        //    {
        //        DateTime today;

        //        if (DateTime.TryParse(TextBox12.Text, out today))
        //        {
        //            DateTime firstDayOfMonth = new DateTime(today.Year, today.Month, 1);

                   
        //            // Add 15 months, then get last day of that month
        //            DateTime lastDayAfter15Months = firstDayOfMonth
        //                                            .AddDays(-1);    // go back 1 day
        //            TextBox12.Text = lastDayAfter15Months.ToString("yyyy-MM-dd");
        //            TextBox10.Text = lastDayAfter15Months.ToString("yyyy-MM-dd");
        //            TextBox11.Text = lastDayAfter15Months.ToString("yyyy-MM-dd");
        //            // TextBox11.Text = firstDayOfMonth.AddMonths(15).ToString("yyyy-MM-dd");
        //            TextBox3.Text = "SUBSCRIPTION FEE PAID TILL " + lastDayAfter15Months.ToString("dd-MM-yyyy") ;

        //        }
        //        else
        //        {
        //            // Handle invalid date input
        //            // TextBox12.Text = "Invalid Date";
        //        }
        //    }
        //}
    }
}