using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
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 pendingpaymnet : System.Web.UI.Page
    {
        Class1 obj = new Class1();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                HealthNewsdata();
            }
        }
        public void HealthNewsdata()
        {
            string qrajneeti = "select  * from Membersduelist where dues!='0'  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_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
        {
            if (DataGrid2.Items.Count >= 0)
            {
                DataGrid2.CurrentPageIndex = e.NewPageIndex;
                HealthNewsdata();
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string empty = "";
            
            if (TextBox3.Text != "")
            {
                empty = empty + " and Bcdenroll = '" + TextBox3.Text + "'";
            }
            if (TextBox5.Text != "")
            {
                empty = empty + " and Ndbano = '" + TextBox5.Text + "'";
            }
            string qrajneeti = "select * from Membersduelist 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;
            }
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            string qrajneeti = "select  Ndbano,F_name as Name,dues as Due,Ndbadate as Due_Date, Paid, Bcdenroll from Membersduelist where Ndbadate<='" + TextBox1.Text+ "'  and dues!=''  and dues!='0' and  Ndbano not like '%-%' ORDER BY CASE WHEN ISNUMERIC(Ndbano) = 1 THEN CAST(Ndbano AS INT) END, Ndbano";
            DataSet dsrajneeti = obj.ReturnDataSet(qrajneeti);
            if (dsrajneeti != null && dsrajneeti.Tables[0].Rows.Count > 0)
            {

                DataGrid1.DataSource = dsrajneeti;
                DataGrid1.DataBind();
                DataGrid1.Visible = true;
            }
            Response.Clear();
            Response.Buffer = true;
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "inline;filename=Memberdue.xls");
            Response.Charset = "";
            this.EnableViewState = false;

            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);


            // Render the DataGrid
            DataGrid1.RenderControl(oHtmlTextWriter);



            Response.Write(oStringWriter.ToString());
            Response.End();
        }
        protected void Button2_Click(object sender, EventArgs e)
        {
            string connStr = ConfigurationManager
                .ConnectionStrings["ConnectionString"]
                .ConnectionString;

            string qrajneeti = "SELECT * FROM Membersduelist ORDER BY id DESC";

            DataSet dsrajneeti = obj.ReturnDataSet(qrajneeti);

            if (dsrajneeti != null &&
                dsrajneeti.Tables.Count > 0 &&
                dsrajneeti.Tables[0].Rows.Count > 0)
            {
                DateTime uptoDate = DateTime.Today;

                // ✅ Upto Date
                if (!string.IsNullOrWhiteSpace(TextBox1.Text))
                {
                    DateTime.TryParse(TextBox1.Text, out uptoDate);
                }

                foreach (DataRow row in dsrajneeti.Tables[0].Rows)
                {
                    string Ndbanos = row["Ndbano"].ToString();
                    string Bcdenroll = row["Bcdenroll"].ToString();
                    string dateStr = "";
                    string qra = "select * from Memberslist where Ndbano='" + Ndbanos + "' ";
                    DataSet dsr = obj.ReturnDataSet(qra);
                    if (dsr != null && dsr.Tables[0].Rows.Count > 0)
                    {
                        try
                        {
                            DateTime dt = Convert.ToDateTime(dsr.Tables[0].Rows[0]["Ndbadate"]);
                            dateStr = dt.ToString("MM/dd/yyyy");
                            
                        }
                        catch { }
                    }
                       

                        DateTime lastDueDate;

                    // ✅ Safe Date
                    if (!DateTime.TryParse(dateStr, out lastDueDate))
                        continue;

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

                    DateTime endDate = new DateTime(
                        uptoDate.Year,
                        uptoDate.Month,
                        1
                    );

                    decimal membershipDue = 0;

                    // ✅ Membership Fee Calculation
                    while (loopDate <= endDate)
                    {
                        decimal amount = 0;

                        // Before 2010
                        if (loopDate.Year <= 2009)
                        {
                            amount = 20;
                        }

                        // 2010 - 2015
                        else if (loopDate.Year >= 2010 &&
                                 loopDate.Year <= 2015)
                        {
                            amount = 50;
                        }

                        // 2016 onward
                        else
                        {
                            amount = 100;
                        }

                        membershipDue += amount;

                        loopDate = loopDate.AddMonths(1);
                    }

                    decimal totalReceipt = 0;
                    decimal totalBills = 0;

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

                        // ✅ Total Receipt
                        string query1 = @"
                    SELECT ISNULL(SUM(Amount),0) 
                    FROM Membersallrecpit 
                    WHERE Bcdenroll = @BCD";

                        using (SqlCommand cmd1 = new SqlCommand(query1, con))
                        {
                            cmd1.Parameters.AddWithValue("@BCD", Bcdenroll);

                            object result1 = cmd1.ExecuteScalar();

                            if (result1 != null)
                            {
                                decimal.TryParse(result1.ToString(), out totalReceipt);
                            }
                        }

                        // ✅ Total Bills
                        string query2 = @"
                    SELECT ISNULL(
                        SUM(TRY_CAST(totalamount AS DECIMAL(18,2))),0
                    )
                    FROM newdelhibar_bill
                    WHERE btype = 'SUBSCRIPTION FEE'
                    AND BCDno = @BCD";

                        using (SqlCommand cmd2 = new SqlCommand(query2, con))
                        {
                            cmd2.Parameters.AddWithValue("@BCD", Bcdenroll);

                            object result2 = cmd2.ExecuteScalar();

                            if (result2 != null)
                            {
                                decimal.TryParse(result2.ToString(), out totalBills);
                            }
                        }
                    }

                    // ✅ Total Paid
                    decimal totalPaid = totalReceipt + totalBills;

                    // ✅ Final Due
                    decimal finalDue = membershipDue - totalPaid;

                    if (finalDue < 0)
                    {
                        finalDue = 0;
                    }

                    // ✅ Update Membersduelist
                    string updateQuery = @"UPDATE Membersduelist 
                                   SET dues='" + finalDue + @"',
                                   Ndbadate='" + endDate.ToString("yyyy-MM-dd") + @"'
                                   WHERE Ndbano='" + Ndbanos + "'";

                    obj.SaveRecord(updateQuery);
                }

                Label2.Text = "Final Due Updated Successfully";
            }
        }
        //protected void Button2_Click(object sender, EventArgs e)
        //{
        //    string qrajneeti = "SELECT * FROM Membersduelist ORDER BY id DESC";
        //    DataSet dsrajneeti = obj.ReturnDataSet(qrajneeti);

        //    if (dsrajneeti != null && dsrajneeti.Tables.Count > 0 &&
        //        dsrajneeti.Tables[0].Rows.Count > 0)
        //    {
        //        DateTime uptoDate = DateTime.Now;

        //        if (!string.IsNullOrEmpty(TextBox1.Text))
        //        {
        //            uptoDate = Convert.ToDateTime(TextBox1.Text);
        //        }

        //        for (int i = 0; i < dsrajneeti.Tables[0].Rows.Count; i++)
        //        {
        //            string duesStr = dsrajneeti.Tables[0].Rows[i]["dues"].ToString();
        //            string Ndbanos = dsrajneeti.Tables[0].Rows[i]["Ndbano"].ToString();

        //            if (string.IsNullOrEmpty(duesStr) || duesStr == "0")
        //                continue;

        //            int dues = Convert.ToInt32(duesStr);
        //            DateTime lastDueDate = Convert.ToDateTime(
        //                dsrajneeti.Tables[0].Rows[i]["Ndbadate"]
        //            );

        //            // Calculate month difference
        //            int monthCount = (uptoDate.Year - lastDueDate.Year) * 12 +
        //                             (uptoDate.Month - lastDueDate.Month);

        //            if (monthCount <= 0)
        //                continue;

        //            int additionalDue = monthCount * 100;
        //            int updatedDue = dues + additionalDue;

        //            DateTime newDueDate = lastDueDate.AddMonths(monthCount);

        //            string updue = @"UPDATE Membersduelist 
        //                     SET dues = '" + updatedDue + @"',
        //                         Ndbadate = '" + newDueDate.ToString("yyyy-MM-dd") + @"'
        //                     WHERE Ndbano = '" + Ndbanos + "'";

        //            obj.SaveRecord(updue);
        //        }
        //    }
        //}

        protected void Button4_Click(object sender, EventArgs e)
        {
            string qrajneeti = "select  Ndbano,F_name as Name,dues as Due,Ndbadate as Due_Date, Paid, Bcdenroll from Membersduelist where   Ndbano not like '%-%' ORDER BY CASE WHEN ISNUMERIC(Ndbano) = 1 THEN CAST(Ndbano AS INT) END, Ndbano";
            DataSet dsrajneeti = obj.ReturnDataSet(qrajneeti);
            if (dsrajneeti != null && dsrajneeti.Tables[0].Rows.Count > 0)
            {

                DataGrid1.DataSource = dsrajneeti;
                DataGrid1.DataBind();
                DataGrid1.Visible = true;
            }
            Response.Clear();
            Response.Buffer = true;
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "inline;filename=Memberduelist_1.xls");
            Response.Charset = "";
            this.EnableViewState = false;

            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);


            // Render the DataGrid
            DataGrid1.RenderControl(oHtmlTextWriter);



            Response.Write(oStringWriter.ToString());
            Response.End();
        }
    }
}