using getepay_sdk;
using iTextSharp.text.html.simpleparser;
using iTextSharp.text.pdf;
using iTextSharp.text;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Reflection.Emit;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Security.Cryptography;

namespace Newdelhibarassocia
{
    public partial class registration : System.Web.UI.Page
    {
        Class1 obj = new Class1();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {

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

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

            if (!DateTime.TryParseExact(
                    TextBox27.Text.Trim(),
                    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;

            // ✅ Create month-wise table
            DataTable result = new DataTable();
            result.Columns.Add("Month");
            result.Columns.Add("Year");
            result.Columns.Add("Amount");

            // ✅ 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 = 100; // full discount
                }
                else if (loopDate >= dtt)
                {
                    // 👉 You can change this logic if you have count
                    monthAmount = 100; // default
                }
                else
                {
                    monthAmount = 50;
                }

                totalAmount += monthAmount;

                result.Rows.Add(
                    loopDate.ToString("MMMM"),
                    loopDate.Year,
                    monthAmount
                );

                loopDate = loopDate.AddMonths(1);
            }

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

            // ✅ Add TOTAL row
            DataRow totalRow = result.NewRow();
            totalRow["Month"] = "TOTAL";
            totalRow["Amount"] = totalAmount;
            result.Rows.Add(totalRow);

            // ✅ Add FINAL row
            DataRow finalRow = result.NewRow();
            finalRow["Month"] = "FINAL";
            finalRow["Amount"] = finalAmount;
            result.Rows.Add(finalRow);

            // ✅ Bind Grid
            GridView3.DataSource = result;
            GridView3.DataBind();

            // ✅ Labels
            Label1.Text = finalAmount.ToString("0.00");
            Label2.Text = membershipDate.ToString("yyyy-MM-dd");
            Label3.Text = today.ToString("yyyy-MM-dd");
        }


        public void LoadData(string srno = "")
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                string query = @"SELECT 
                Recp_no,
                CONVERT(VARCHAR(10), Recp_dt, 103) AS Recp_dt,
                Recp_md,
                Dd_no,
               
                Amount,
                Dd_bnk
            FROM Membersallrecpit
            WHERE 1=1";

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;

                // 🔍 Filter Srno
                if (!string.IsNullOrEmpty(srno))
                {
                    query += " AND Bcdenroll = @Bcdenroll";
                    cmd.Parameters.AddWithValue("@Bcdenroll", srno);
                }



                cmd.CommandText = query;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dtbl = new DataTable();
                da.Fill(dtbl);

                GridView1.DataSource = dtbl;
                GridView1.DataBind();
            }
        }
        private void GetTotals()
        {
            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 = '" + TextBox3.Text + "'", 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 = '" + TextBox3.Text + "'", 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(Label1.Text) && decimal.TryParse(Label1.Text, out dues))
            {
                decimal due = totalBills + totalMembers;
                decimal duetotal = dues - due;

                Literal3.Text = duetotal.ToString("0.00");
            }
            else
            {
                Literal3.Text = "0.00"; // or handle error
            }
        }
        public void newLoadData(string srno = "")
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                string query = @"SELECT 
                Billno as Recp_no ,
                CONVERT(VARCHAR(10), Daten, 103) AS Recp_dt,
               Paymentmode as Recp_md,
               Chequeno as Dd_no,
               totalamount as Amount
            FROM newdelhibar_bill
            WHERE 1=1";

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;

                // 🔍 Filter Srno
                if (!string.IsNullOrEmpty(srno))
                {
                    query += " AND BCDno = @BCDno";
                    cmd.Parameters.AddWithValue("@BCDno", srno);
                }



                cmd.CommandText = query;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dtbl = new DataTable();
                da.Fill(dtbl);

                GridView2.DataSource = dtbl;
                GridView2.DataBind();
            }
        }


        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            string signaturePath = "";
            string photoPath = "";

            // Save Signature file
            if (fuSignature.HasFile)
            {
                string signatureFileName = "Signature_" + Guid.NewGuid() + System.IO.Path.GetExtension(fuSignature.FileName);
                string signatureFullPath = Server.MapPath("~/Uploads/" + signatureFileName);
                fuSignature.SaveAs(signatureFullPath);
                signaturePath = "Uploads/" + signatureFileName;
            }

            // Save Photo file
            if (fuPhoto.HasFile)
            {
                string photoFileName = "Photo_" + Guid.NewGuid() + System.IO.Path.GetExtension(fuPhoto.FileName);
                string photoFullPath = Server.MapPath("~/Uploads/" + photoFileName);
                fuPhoto.SaveAs(photoFullPath);
                photoPath = "Uploads/" + photoFileName;
            }

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                string query = @"INSERT INTO NDBAMembers (
            Name, FatherHusbandName, DOB, BarEnrollment, OtherStateEnrollment, PlacePractice, 
            GraduationDetails, ChamberNo, ResAddress, Mobile, Email, Aadhar, VoterId, 
            BloodGroup, PendingCase, Retired, ApplicantName, Recommendations,
            SignaturePath, PhotoPath,Paymentsatats,daten
        ) VALUES (
            @Name, @FatherHusbandName, @DOB, @BarEnrollment, @OtherStateEnrollment, @PlacePractice,
            @GraduationDetails, @ChamberNo, @ResAddress, @Mobile, @Email, @Aadhar, @VoterId,
            @BloodGroup, @PendingCase, @Retired, @ApplicantName, @Recommendations,
            @SignaturePath, @PhotoPath,@Paymentsatats,@daten
        )";

                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.AddWithValue("@Name", txtName.Text);
                cmd.Parameters.AddWithValue("@FatherHusbandName", txtFatherHusbandName.Text);
                cmd.Parameters.AddWithValue("@DOB", txtDOB.Text);
                cmd.Parameters.AddWithValue("@BarEnrollment", txtBarEnrollment.Text);
                cmd.Parameters.AddWithValue("@OtherStateEnrollment", txtOtherStateEnrollment.Text);
                cmd.Parameters.AddWithValue("@PlacePractice", txtPlacePractice.Text);
                cmd.Parameters.AddWithValue("@GraduationDetails", txtGraduationDetails.Text);
                cmd.Parameters.AddWithValue("@ChamberNo", txtChamberNo.Text);
                cmd.Parameters.AddWithValue("@ResAddress", txtResAddress.Text);
                cmd.Parameters.AddWithValue("@Mobile", txtMobile.Text);
                cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
                cmd.Parameters.AddWithValue("@Aadhar", txtAadhar.Text);
                cmd.Parameters.AddWithValue("@VoterId", txtVoterId.Text);
                cmd.Parameters.AddWithValue("@BloodGroup", txtBloodGroup.Text);
                cmd.Parameters.AddWithValue("@PendingCase", txtPendingCase.Text);
                cmd.Parameters.AddWithValue("@Retired", txtRetired.Text);
                cmd.Parameters.AddWithValue("@ApplicantName", txtApplicantName.Text);
                cmd.Parameters.AddWithValue("@Recommendations", txtRecommendations.Text);
                cmd.Parameters.AddWithValue("@SignaturePath", signaturePath);
                cmd.Parameters.AddWithValue("@PhotoPath", photoPath);
                cmd.Parameters.AddWithValue("@Paymentsatats", "Pending");
                cmd.Parameters.AddWithValue("@daten", System.DateTime.Now);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                string bnd = "ND" + System.DateTime.Now.Ticks.ToString().Substring(12);
                HttpCookie myCookie = new HttpCookie("Siteid");
                myCookie.Value = bnd;
                myCookie.Expires = DateTime.Now.AddDays(1);
                Response.Cookies.Add(myCookie);
                //Label bcno = (Label)e.Item.FindControl("lblContactName");
                //TextBox pamount = (TextBox)e.Item.FindControl("Label5");

                Session["billno"] = txtMobile.Text;
                Session["bnreorll"] = bnd;


                conn.Open();
                cmd = new SqlCommand("insert into newdelhibar_bill (Billno,Name,Mobile,NDBAno,BCDno,Daten,Address,Headermessage,Paymentmode,Footermessage,totalamount,btype,Status) values (@Billno,@Name,@Mobile,@NDBAno,@BCDno,@Daten,@Address,@Headermessage,@Paymentmode,@Footermessage,@totalamount,@btype,@Status)", conn);

                cmd.Parameters.Add("@Billno", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Billno"].Value = bnd;
                cmd.Parameters.Add("@Name", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Name"].Value = txtName.Text;
                cmd.Parameters.Add("@Mobile", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Mobile"].Value = txtMobile.Text;
                cmd.Parameters.Add("@NDBAno", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@NDBAno"].Value = "";
                cmd.Parameters.Add("@BCDno", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@BCDno"].Value = txtBarEnrollment.Text;
                cmd.Parameters.Add("@Daten", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Daten"].Value = System.DateTime.Now;
                cmd.Parameters.Add("@Address", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Address"].Value = txtResAddress.Text;
                cmd.Parameters.Add("@Headermessage", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Headermessage"].Value = "New Member Fee";
                cmd.Parameters.Add("@Paymentmode", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Paymentmode"].Value = "Online";
                cmd.Parameters.Add("@Footermessage", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Footermessage"].Value = "Online Fee";
                cmd.Parameters.Add("@totalamount", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@totalamount"].Value = "1700";
                cmd.Parameters.Add("@btype", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@btype"].Value = "Member ship";
                cmd.Parameters.Add("@Status", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Status"].Value = "Pending";

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



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

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

                cmd.Parameters.Add("@Particulars", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@Particulars"].Value = "New Membership Fee";


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



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

                try
                {
                    string mid = ConfigurationSettings.AppSettings["Mid"].ToString();
                    string terminal_Id = ConfigurationSettings.AppSettings["terminalId"].ToString();
                    string mkey = ConfigurationSettings.AppSettings["key"].ToString();
                    string miv = ConfigurationSettings.AppSettings["iv"].ToString();
                    string murl = ConfigurationSettings.AppSettings["url"].ToString();

                    GetepayConfig config = new GetepayConfig();
                    config.mid = mid;
                    config.terminalId = terminal_Id;
                    config.key = mkey;
                    config.iv = miv;
                    config.url = murl;


                    //added to solve ssl error for requery
                    System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12;
                    //GetepayRequeryResponse requeryResponse = Getepay.requeryRequest(config, getepayRequery);
                    //  Console.WriteLine("11", requeryResponse.txnStatus);
                    // string rns=requeryResponse.txnStatus.ToString();

                    GetepayRequest request = new GetepayRequest();

                    request.mid = mid.ToString();
                    request.amount = "1700";
                    request.merchantTransactionId = bnd;
                    request.transactionDate = System.DateTime.Now.ToString("yyyy-mm-dd hh:mm:ss");//2024-02-16 04:31:23
                    request.terminalId = terminal_Id.ToString();
                    request.udf1 = txtMobile.Text;
                    request.udf2 = "ndba.patialahousecourt@gmail.com";
                    request.udf3 = txtName.Text;
                    request.udf4 = "";
                    request.udf5 = "";
                    request.udf6 = "";
                    request.udf7 = "";
                    request.udf8 = "";
                    request.udf9 = "";
                    request.udf10 = murl;
                    request.ru = "https://newdelhibarassociation.in/Paymentstatus.aspx";
                    // request.ru = "https://localhost:44346/Paymentstatus.aspx";
                    request.callbackUrl = "https://newdelhibarassociation.in/Paymentstatus.aspx";
                    request.callbackUrl = "";
                    request.currency = "INR";
                    request.paymentMode = "ALL";
                    request.bankId = "";
                    request.txnType = "";
                    request.productType = "";
                    request.txnNote = "";
                    request.vpa = "";

                    // GetepayRequest getreq= new GetepayRequest(config,request);

                    GetepayOrderResponse orderResponse = Getepay.generateRequest(config, request);
                    //Session["pid"] = orderResponse.paymentId;

                    HttpCookie nameCookie = new HttpCookie("Siteid");
                    nameCookie = Request.Cookies["Siteid"];
                    string valss = nameCookie.Value.Replace("Siteid=", "");
                    string up = "update newdelhibar_bill set payment_id='" + orderResponse.paymentId.ToString() + "' where Billno='" + bnd + "'";
                    obj.SaveRecord(up);
                    Response.Redirect(orderResponse.paymentUrl, true);
                    //string rrn=orderResponse.ToString();
                }
                catch (Exception ex)
                {
                    RegisterStartupScript("12", "<script>alert('" + ex.Message.ToString() + "')</script>");

                }
                lblMessage.Text = "Form submitted successfully with uploads!";
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
             if (HiddenField1.Value == TextBox17.Text)
                {
                    
                if (DropDownList1.SelectedItem.Text == "Select Type")
                {
                    RegisterStartupScript("12", "<script>alert('Choose Type of Serach')</script>");

                }
                else
                {
                    string empt = "";
                    if (DropDownList1.SelectedItem.Text == "NDBA.No.")
                    {
                        empt = " and Ndbano='" + TextBox1.Text + "'";
                    }
                    else if (DropDownList1.SelectedItem.Text == "Mobile No.")
                    {
                        empt = " and Mobile_1='" + TextBox1.Text + "'";
                    }
                    else if (DropDownList1.SelectedItem.Text == "BCD Enrollment No.")
                    {
                        empt = " and Bcdenroll='" + TextBox1.Text + "'";
                    }
                    string qrajneeti = "select * from Memberslist where id!='' " + empt + " 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());
                            // DateTime dtd1 = Convert.ToDateTime(dsrajneeti.Tables[0].Rows[0]["dob"].ToString());
                            TextBox27.Text = dsrajneeti.Tables[0].Rows[0]["Ndbadate"].ToString();
                            TextBox7.Text = dsrajneeti.Tables[0].Rows[0]["dob"].ToString();
                        }
                        catch { }
                        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();
                        //TextBox28.Text= dsrajneeti.Tables[0].Rows[0]["Ceased"].ToString();
                        //TextBox29.Text = dsrajneeti.Tables[0].Rows[0]["CeasedDate"].ToString();
                        //TextBox30.Text = dsrajneeti.Tables[0].Rows[0]["Reasonceas"].ToString();
                        Image1.ImageUrl = dsrajneeti.Tables[0].Rows[0]["Photo"].ToString();
                        Image2.ImageUrl = dsrajneeti.Tables[0].Rows[0]["M_Sign"].ToString();
                        TextBox14.Text = dsrajneeti.Tables[0].Rows[0]["Email_id"].ToString();
                        TextBox21.Text = dsrajneeti.Tables[0].Rows[0]["Aadhar_id"].ToString();
                        //TextBox22.Text = dsrajneeti.Tables[0].Rows[0]["Voter_id"].ToString();
                        //TextBox23.Text = dsrajneeti.Tables[0].Rows[0]["Locker_no"].ToString();
                        //TextBox24.Text = dsrajneeti.Tables[0].Rows[0]["Lid_Card_No"].ToString();
                        //TextBox25.Text = dsrajneeti.Tables[0].Rows[0]["I_card_no"].ToString();
                        TextBox15.Text = dsrajneeti.Tables[0].Rows[0]["Chamber_Alloted"].ToString();
                        TextBox16.Text = dsrajneeti.Tables[0].Rows[0]["Chamber_no"].ToString();
                        // TextBox17.Text = dsrajneeti.Tables[0].Rows[0]["Member_Ship_of_other_Bars"].ToString();
                        TextBox18.Text = dsrajneeti.Tables[0].Rows[0]["Vehicle_no"].ToString();
                        TextBox20.Text = dsrajneeti.Tables[0].Rows[0]["Sticker_no"].ToString();
                        lbstatus.Text = dsrajneeti.Tables[0].Rows[0]["sstatus"].ToString();

                        string qrdue = "select * from Membersduelist where  Bcdenroll='" + dsrajneeti.Tables[0].Rows[0]["Bcdenroll"].ToString() + "'  order by id desc";
                        DataSet dsdue = obj.ReturnDataSet(qrdue);
                        string ddue = "0";
                        if (dsdue != null && dsdue.Tables[0].Rows.Count > 0)
                        {
                            ddue = dsdue.Tables[0].Rows[0]["dues"].ToString();
                            lbamount.Text = ddue;
                            DateTime dt = Convert.ToDateTime(dsdue.Tables[0].Rows[0]["Ndbadate"]);
                            lbduedate.Text = dt.ToString("dd/MM/yyyy");
                        }
                        if (ddue == "0")
                        {
                            string qrdues = "select * from newdelhibar_bill where  BCDno='" + dsrajneeti.Tables[0].Rows[0]["Bcdenroll"].ToString() + "'  order by id desc";
                            DataSet dsdues = obj.ReturnDataSet(qrdues);
                            // int ddate = 0;
                            if (dsdues != null && dsdues.Tables[0].Rows.Count > 0)
                            {
                                if (dsdues.Tables[0].Rows[0]["btype"].ToString() == "NEW MEMBERSHIP")
                                {
                                    DateTime dt = Convert.ToDateTime(TextBox27.Text);
                                    int ddate = 12;

                                    DateTime lastDay = dt.AddMonths(ddate + 1).AddDays(-1);

                                    lbduedate.Text = lastDay.ToString("dd/MM/yyyy");
                                }
                            }
                        }
                        LoadData(dsrajneeti.Tables[0].Rows[0]["Bcdenroll"].ToString());
                        newLoadData(dsrajneeti.Tables[0].Rows[0]["Bcdenroll"].ToString());

                        DateTime dts = System.DateTime.Now;

                        string dateValue = dsrajneeti.Tables[0].Rows[0]["Ndbadate"].ToString();
                        DateTime minDate = new DateTime(2016, 1, 1);

                        // ✅ Membership Date from 
                        if (Convert.ToDateTime(dateValue) <= minDate)
                        {
                            dateValue = minDate.ToString("yyyy-MM-dd");
                        }
                        string[] formats = { "dd/MM/yyyy", "yyyy-MM-dd", "MM/dd/yyyy" };

                        if (!string.IsNullOrWhiteSpace(dateValue) && dateValue != "0")
                        {
                            if (DateTime.TryParseExact(dateValue, formats,
                                CultureInfo.InvariantCulture,
                                DateTimeStyles.None, out dts))
                            {
                                // ✅ success
                                Literal4.Text = dateValue;
                            }
                            else
                            {
                                // ❌ invalid date
                                dts = DateTime.MinValue;
                            }
                        }
                        LoadLedger(TextBox3.Text, dts);
                        // DateTime dts = Convert.ToDateTime(dsrajneeti.Tables[0].Rows[0]["Ndbadate"].ToString());
                        // DateTime dtt = Convert.ToDateTime("01/01/2016"); // less than 50 per months and after 100 per months
                        //01-04-2020 to 30-09-2021 -all amount discount ..
                        CalculateMembershipDue();
                        GetTotals();
                        Panel3.Visible= false;
                        Panel4.Visible = false;
                        Panel2.Visible = true;
                    }
                }
            }
             else
            {
                RegisterStartupScript("12", "<script>alert('Invalid OTP')</script>");

            }
        }
        public DataTable GetMembershipLedger(DateTime startDate)
        {
            DataTable dt = new DataTable();

            dt.Columns.Add("Date", typeof(DateTime));
            dt.Columns.Add("RefNo");
            dt.Columns.Add("Type");
            dt.Columns.Add("Credit", typeof(decimal));
            dt.Columns.Add("Debit", typeof(decimal));

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

            DateTime loopDate = new DateTime(startDate.Year, startDate.Month, 1);

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

                if (loopDate >= discountStart && loopDate <= discountEnd)
                {
                    amount = 100; // discount period (as per your rule)
                }
                else if (loopDate >= dtt)
                {
                    amount = 100;
                }
                else
                {
                    amount = 100;
                }

                dt.Rows.Add(loopDate, "SUB", "SUBSCRIPTION", 0, amount);

                loopDate = loopDate.AddMonths(1);
            }

            return dt;
        }
        // ✅ MAIN LEDGER FUNCTION
        public void LoadLedger(string memberId, DateTime membershipDate)
        { 
        string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(connStr))
            {
                string query = @"
SELECT 
    Recp_dt AS Date,
    Recp_no AS RefNo,
    CASE 
        WHEN CAST(Recp_dt AS DATE) = '2021-09-30' THEN 'COVID WAVE OFF'
        ELSE 'RECEIPT'
    END AS Type,
    Amount AS Credit,
    0 AS Debit
FROM Membersallrecpit
WHERE Bcdenroll = @id

UNION ALL

SELECT 
    Daten AS Date,
    Billno AS RefNo,
    btype AS Type,
    CASE 
        WHEN btype = 'SUBSCRIPTION FEE' THEN TRY_CAST(totalamount AS DECIMAL(18,2))
        ELSE 0
    END AS Credit,
    CASE 
        WHEN btype <> 'SUBSCRIPTION FEE' THEN TRY_CAST(totalamount AS DECIMAL(18,2))
        ELSE 0
    END AS Debit
FROM newdelhibar_bill
WHERE BCDno = @id AND btype = @btype

ORDER BY Date";

                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@id", memberId);
                cmd.Parameters.AddWithValue("@btype", "SUBSCRIPTION FEE");

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);

                // ✅ ADD MEMBERSHIP MONTHLY DATA
                DataTable membership = GetMembershipLedger(membershipDate);
                dt.Merge(membership);

                // ✅ SORT DATE WISE
                DataView dv = dt.DefaultView;
                dv.Sort = "Date ASC";
                dt = dv.ToTable();

                // ✅ RUNNING BALANCE
                dt.Columns.Add("Balance", typeof(decimal));

                decimal balance = 0;

                foreach (DataRow row in dt.Rows)
                {
                    decimal cr = Convert.ToDecimal(row["Credit"]);
                    decimal dr = Convert.ToDecimal(row["Debit"]);

                    balance += cr - dr;
                    row["Balance"] = balance;
                }

                GridViewLedger.DataSource = dt;
                GridViewLedger.DataBind();

               // ViewState["Ledger"] = dt;
            }
        }

       
        protected void Button2_Click(object sender, EventArgs e)
        {
            try
            {
                string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (DropDownList1.SelectedItem.Text == "Select Type")
                {
                    RegisterStartupScript("12", "<script>alert('Choose Type of Serach')</script>");

                }
                else
                {
                    if (DropDownList1.SelectedItem.Text == "NDBA.No.")
                    {
                        string sel = "select top 1 * from Memberslist where Ndbano='" + TextBox1.Text + "'";
                        SqlDataAdapter da = new SqlDataAdapter(sel, con);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            Panel4.Visible = true;
                            Panel3.Visible= false;
                            Literal1.Text = dt.Rows[0]["Mobile_1"].ToString();
                           
                            string[] saAllowedCharacters = { "1", "2", "3", "4", "5", "6", "7", "8", "9", "0" };

                            string sRandomOTP = GenerateRandomOTP(4, saAllowedCharacters);
                            HiddenField1.Value = sRandomOTP;
                            string messag = " NDBA User, Your OTP is " + sRandomOTP + ". Do Not share this with anyone. TEAM NDBA";
                            string SmsStatusMsg = string.Empty;
                            WebClient client = new WebClient();
                            string mm = dt.Rows[0]["Mobile_1"].ToString();
                            mm = "9871482014";
                            if (dt.Rows[0]["Mobile_1"].ToString() != "0")
                            {
                                string URL = "http://www.canlog.in/api/mt/SendSMS?user=NDBA1234&password=NDBA1234&senderid=NDBATM&channel=Trans&DCS=0&flashsms=0&number=" + mm + "&text=" + messag + "&route=2";

                                SmsStatusMsg = client.DownloadString(URL);
                                if (SmsStatusMsg.Contains("<br>"))
                                {
                                    SmsStatusMsg = SmsStatusMsg.Replace("<br>", ", ");

                                }
                            }
                            else
                            {
                                RegisterStartupScript("12", "<script>alert('number not valid')</script>");
                            }

                            //Repeater1.DataSource = dt;
                            //Repeater1.DataBind();
                            //Repeater1.Visible = true;
                        }
                        else
                        {
                            // Repeater1.Visible = false;
                        }
                    }

                    else if (DropDownList1.SelectedItem.Text == "Mobile No.")
                    {
                        string sel = "select top 1 * from Memberslist where  Mobile_1 like '%" + TextBox1.Text + "%'";
                        SqlDataAdapter da = new SqlDataAdapter(sel, con);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            Panel4.Visible = true;
                            Panel3.Visible = false;
                            //Panel2.Visible = true;
                            Literal1.Text = dt.Rows[0]["Mobile_1"].ToString();
                           

                            string[] saAllowedCharacters = { "1", "2", "3", "4", "5", "6", "7", "8", "9", "0" };

                            string sRandomOTP = GenerateRandomOTP(4, saAllowedCharacters);
                            HiddenField1.Value = sRandomOTP;
                            //NDBA User, Your OTP is 464654. Do Not share this with anyone. TEAM NDBA
                            //	NDBA User, Your OTP is " + sRandomOTP + ". Do Not share this with anyone. TEAM NDBA
                            string messag = " NDBA User, Your OTP is " + sRandomOTP + ". Do Not share this with anyone. TEAM NDBA";
                            string SmsStatusMsg = string.Empty;
                            WebClient client = new WebClient();
                            // mm = "9871482014";
                            if (dt.Rows[0]["Mobile_1"].ToString() != "0")
                            {
                                string URL = "http://www.canlog.in/api/mt/SendSMS?user=NDBA1234&password=NDBA1234&senderid=NDBATM&channel=Trans&DCS=0&flashsms=0&number=" + dt.Rows[0]["Mobile_1"].ToString() + "&text=" + messag + "&route=2";

                                SmsStatusMsg = client.DownloadString(URL);
                                if (SmsStatusMsg.Contains("<br>"))
                                {
                                    SmsStatusMsg = SmsStatusMsg.Replace("<br>", ", ");

                                }
                            }
                            else
                            {
                                RegisterStartupScript("12", "<script>alert('number not valid')</script>");
                            }
                           
                        }
                        else
                        {
                            // Repeater1.Visible = false;
                        }
                    }
                    else if (DropDownList1.SelectedItem.Text == "BCD Enrollment No.")
                    {
                        string sel = "select top 1 * from Memberslist where Bcdenroll like '%" + TextBox1.Text + "%'";
                        SqlDataAdapter da = new SqlDataAdapter(sel, con);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            Panel4.Visible = true;
                            Panel3.Visible = false;
                            Literal1.Text = dt.Rows[0]["Mobile_1"].ToString();
                           

                            //Panel2.Visible = true;
                            //Literal1.Text = dt.Rows[0]["Mobile_1"].ToString();
                            string[] saAllowedCharacters = { "1", "2", "3", "4", "5", "6", "7", "8", "9", "0" };

                            string sRandomOTP = GenerateRandomOTP(4, saAllowedCharacters);
                            HiddenField1.Value = sRandomOTP;
                            string messag = sRandomOTP + " is the OTP for your NDBA. do not share this with anyone.";
                            string SmsStatusMsg = string.Empty;
                            WebClient client = new WebClient();
                            // mm = "9871482014";
                            string URL = "http://www.canlog.in/api/mt/SendSMS?user=NDBA1234&password=NDBA1234&senderid=NDBATM&channel=Trans&DCS=0&flashsms=0&number=" + dt.Rows[0]["Mobile_1"].ToString() + "&text=" + messag + "&route=2";

                            SmsStatusMsg = client.DownloadString(URL);
                            if (SmsStatusMsg.Contains("<br>"))
                            {
                                SmsStatusMsg = SmsStatusMsg.Replace("<br>", ", ");

                            }
                            //Repeater1.DataSource = dt;
                            //Repeater1.DataBind();
                            //Repeater1.Visible = true;
                        }
                        else
                        {
                            //Repeater1.Visible = false;
                        }
                    }
                }
                // Literal1.Text = "9871482014";
               // Panel2.Visible = false;
            }
            catch (Exception ex)
            {
                RegisterStartupScript("12", "<script>alert('Contact the systems administrator to check your registration details')</script>");
            }
        }
        private string GenerateRandomOTP(int iOTPLength, string[] saAllowedCharacters)

        {

            string sOTP = String.Empty;

            string sTempChars = String.Empty;

            Random rand = new Random();

            for (int i = 0; i < iOTPLength; i++)

            {

                int p = rand.Next(0, saAllowedCharacters.Length);

                sTempChars = saAllowedCharacters[rand.Next(0, saAllowedCharacters.Length)];

                sOTP += sTempChars;

            }

            return sOTP;

        }
    }
}