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 mview : System.Web.UI.Page
    {
        Class1 obj=new Class1();
        protected void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack)
            {
                if (Request.QueryString["id"] != null)
                {
                    string qrajneeti = "select * from Memberslist where id='" + Request.QueryString["id"].ToString() + "' 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());
                            DateTime dt = Convert.ToDateTime(dsrajneeti.Tables[0].Rows[0]["Ndbadate"]);
                            TextBox27.Text = dt.ToString("dd/MM/yyyy");
                            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 = 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();
                    }
                }
            }
        }
      

private void CalculateMembershipDue()
    {
        DateTime membershipDate;

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

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

        DateTime today = DateTime.Today;

        // ✅ Fee Rules
        DateTime fee100Start = new DateTime(2016, 1, 1);

        DateTime fee50Start = new DateTime(2010, 1, 1);
        DateTime fee50End = new DateTime(2015, 12, 31);

        DateTime fee20End = new DateTime(2009, 12, 31);

        // ✅ Discount Period
        DateTime discountStart = new DateTime(2020, 4, 1);
        DateTime discountEnd = new DateTime(2021, 9, 30);

        decimal totalAmount = 0;

        // ✅ Create Table
        DataTable result = new DataTable();
        result.Columns.Add("Month");
        result.Columns.Add("Year");
        result.Columns.Add("Amount");

        // ✅ Start From Member Join Month
        DateTime loopDate = new DateTime(membershipDate.Year, membershipDate.Month, 1);

        // ✅ Loop Till Current Month
        while (loopDate <= today)
        {
            decimal monthAmount = 0;

            // ✅ Discount Period
            if (loopDate >= discountStart && loopDate <= discountEnd)
            {
                monthAmount = 100;
            }

            // ✅ Fee = 100
            else if (loopDate >= fee100Start)
            {
                monthAmount = 100;
            }

            // ✅ Fee = 50
            else if (loopDate >= fee50Start && loopDate <= fee50End)
            {
                monthAmount = 50;
            }

            // ✅ Fee = 20
            else if (loopDate <= fee20End)
            {
                monthAmount = 20;
            }

            totalAmount += monthAmount;

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

            loopDate = loopDate.AddMonths(1);
        }

        // ✅ Total Row
        DataRow totalRow = result.NewRow();
        totalRow["Month"] = "TOTAL";
        totalRow["Year"] = "";
        totalRow["Amount"] = totalAmount.ToString("0.00");
        result.Rows.Add(totalRow);

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

        // ✅ Labels
        Label1.Text = totalAmount.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 btype='SUBSCRIPTION FEE' 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);
                }
                if (!string.IsNullOrEmpty(srno))
                {
                    query += " AND btype = @btype";
                    cmd.Parameters.AddWithValue("@btype", "SUBSCRIPTION FEE");
                }




                cmd.CommandText = query;

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

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

        protected void Button1_Click(object sender, EventArgs e)
        {
            Response.Redirect("accountledger.aspx?id=" + Request.QueryString["id"]);
        }
    }
}