using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Security.Cryptography;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Newdelhibarassocia.dhadmin
{
    public partial class Receiptpayment : System.Web.UI.Page
    {
        Class1 obj = new Class1();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                TextBox3.Text=System.DateTime.Now.ToString("yyyy-MM-dd");
                TextBox4.Text = System.DateTime.Now.ToString("yyyy-MM-dd");
                HealthNewsdata();
                billtypedatak();
            }
        }
        public void HealthNewsdata()
        {
            string qrajneeti = "select top 50 * from newdelhibar_bill where Status!='Cancel'  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;
               // DataGrid1.DataSource = dsrajneeti;
              //  DataGrid1.DataBind();
               // DataGrid1.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 (TextBox1.Text != "")
            {
                empty = empty + " and Billno = '" + TextBox1.Text + "'";
            }
            if (TextBox2.Text != "")
            {
                empty = empty + " and Mobile = '" + TextBox2.Text + "'";
            }
            
            if (TextBox5.Text != "")
            {
                empty = empty + " and Ndbano = '" + TextBox5.Text + "'";
            }
            if (TextBox4.Text != "")
            {
                empty = empty + " and Daten between '" + TextBox3.Text + "' and '" + TextBox4.Text + "'";
            }
            if (DropDownList3.SelectedValue != "Select")
            {
                empty = empty + " and btype = '" + DropDownList3.SelectedValue + "'";
            }
            string qrajneeti = "select * from newdelhibar_bill where Status!='Cancel' and 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;
              //  DataGrid1.DataSource = dsrajneeti;
               // DataGrid1.DataBind();
               // DataGrid1.Visible = true;
            }
            else
            {
                DataGrid2.Visible = false;
            }
        }

        protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
        {
            string qrajneeti = "select * from newdelhibar_bill where Paymentmode='" + DropDownList2.SelectedValue + "' and Status!='Cancel'  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;
              //  DataGrid1.DataSource = dsrajneeti;
               // DataGrid1.DataBind();
               // DataGrid1.Visible = true;
            }
            else
            {
                DataGrid2.Visible = false;
            }
        }

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string qrajneeti = "select * from newdelhibar_bill where Status='" + DropDownList1.SelectedValue + "' and Status!='Cancel'  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;
               // DataGrid1.DataSource = dsrajneeti;
               // DataGrid1.DataBind();
               // DataGrid1.Visible = true;
            }
            else
            {
                DataGrid2.Visible = false;
            }
        }

        protected void DataGrid2_ItemCommand(object source, DataGridCommandEventArgs e)
        {
            if (e.CommandName == "chk")
            {
                Label status = (Label)e.Item.FindControl("Labedl5");
                Label billno = (Label)e.Item.FindControl("Label1");
                Label nd = (Label)e.Item.FindControl("Label22");
                HiddenField ids = (HiddenField)e.Item.FindControl("HiddenField5");
                Label amount = (Label)e.Item.FindControl("Label4");
                if (status.Text!= "SUCCESS")
                {
                    string up = "update newdelhibar_bill set Status='SUCCESS' where Billno='" + billno.Text + "'";
                    obj.SaveRecord(up);
                    string qrdue = "select * from Membersduelist where  Ndbano='" + nd.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(amount.Text);
                        }
                        
                         if (kk >= 0)
                        {
                            string updue = "update Membersduelist set dues='" + kk + "' where Ndbano='" + nd.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='" + dt2.ToString() + "' where Ndbano='" + nd.Text + "'";
                            obj.SaveRecord(updue);
                        }
                        HealthNewsdata();
                        // string updue = "update Membersduelist set dues='" + kk + "' where Ndbano='" + nd.Text + "'";
                        // obj.SaveRecord(updue);
                    }
                }
            }
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            string empty = "";

            // Filters
            if (TextBox1.Text != "")
                empty += " AND Billno = '" + TextBox1.Text + "'";

            if (TextBox2.Text != "")
                empty += " AND Mobile = '" + TextBox2.Text + "'";

            if (TextBox5.Text != "")
                empty += " AND Ndbano = '" + TextBox5.Text + "'";

            if (TextBox4.Text != "")
                empty += " AND Daten BETWEEN '" + TextBox3.Text + "' AND '" + TextBox4.Text + "'";

            if (DropDownList3.SelectedValue != "Select")
                empty += " AND btype = '" + DropDownList3.SelectedValue + "'";


            // ✅ Query
            string query = @"
    SELECT 
        Billno AS Receipt_No,
        FORMAT(Daten,'dd/MM/yyyy') AS Date,
        Name,
        Ndbano,
        Chequeno AS Cheque_No,
        Paymentmode AS Payment,
        CASE 
            WHEN CAST(chedate AS DATE) = '1900-01-01' THEN '' 
            ELSE FORMAT(CAST(chedate AS DATE),'dd/MM/yyyy') 
        END AS Trans_Date,
        btype AS Type,
        CAST(ISNULL(totalamount,0) AS DECIMAL(10,2)) AS Amount
    FROM newdelhibar_bill
    WHERE Status='SUCCESS' AND id!='0' " + empty + @"
    ORDER BY btype, id ASC";

            DataSet ds = obj.ReturnDataSet(query);

            if (ds == null || ds.Tables[0].Rows.Count == 0)
                return;

            // ================= EXPORT =================
            Response.Clear();
            Response.Buffer = true;
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment;filename=TypeWiseReport.xls");
            Response.Charset = "";

            System.IO.StringWriter sw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);

            // ===== HEADER =====
            // ===== HEADER =====
            DateTime fromDate, toDate;

            // Try parsing input dates
            bool isFromValid = DateTime.TryParse(TextBox3.Text, out fromDate);
            bool isToValid = DateTime.TryParse(TextBox4.Text, out toDate);

            // Format safely
            string fromDateStr = isFromValid ? fromDate.ToString("dd/MM/yyyy") : "";
            string toDateStr = isToValid ? toDate.ToString("dd/MM/yyyy") : "";

            hw.Write("<table border='1' style='width:100%; font-weight:bold;'>");

            // Title
            hw.Write("<tr>");
            hw.Write("<td colspan='9' align='center' style='font-size:18px;'>Daily Receipt Report</td>");
            hw.Write("</tr>");

            // Date Range
            hw.Write("<tr>");
            hw.Write("<td colspan='9' align='right'>From Date: " + fromDateStr + " To: " + toDateStr + "</td>");
            hw.Write("</tr>");

            hw.Write("</table><br/>");

            decimal grandTotal = 0;
            decimal subTotal = 0;
            string currentType = "";
            int sno = 1; // ✅ S.No counter

            // ===== TABLE START =====
            hw.Write("<table border='1' style='width:100%;'>");

            // Column Header
            hw.Write("<tr style='font-weight:bold; background-color:#d3d3d3;'>");
            hw.Write("<td>S.No</td><td>Receipt No</td><td>Member Name</td><td>NDBA No</td><td>Payment Mode</td><td>Details</td><td>Transcation Date</td><td>Type</td><td>Amount</td>");
            hw.Write("</tr>");

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                string type = row["Type"].ToString();
                decimal amount = Convert.ToDecimal(row["Amount"]);

                // 👉 New Type Group
                if (currentType != type)
                {
                    // Print previous subtotal
                    if (currentType != "")
                    {
                        hw.Write("<tr style='font-weight:bold;'>");
                        hw.Write("<td colspan='7' align='right'>Subtotal (" + currentType + ")</td>");
                        hw.Write("<td>" + subTotal.ToString("N2") + "</td>");
                        hw.Write("</tr>");

                        subTotal = 0;
                    }
                    // ✅ RESET S.NO HERE
                    sno = 1;
                    // Print Type Header
                    hw.Write("<tr style='font-weight:bold; background-color:#f2f2f2;'>");
                    hw.Write("<td colspan='8'>Bill Type: " + type + "</td>");
                    hw.Write("</tr>");

                    currentType = type;
                }

                // 👉 Data Row
                hw.Write("<tr>");
                hw.Write("<td>" + sno + "</td>");
                hw.Write("<td>" + row["Receipt_No"] + "</td>");
                hw.Write("<td>" + row["Name"] + "</td>");
                hw.Write("<td>" + row["Ndbano"] + "</td>");
                hw.Write("<td>" + row["Payment"] + "</td>");
                hw.Write("<td>" + row["Cheque_No"] + "</td>");
                hw.Write("<td>" + row["Trans_Date"] + "</td>");
                hw.Write("<td>" + row["Type"] + "</td>");
                hw.Write("<td align='right'>" + amount.ToString("N2") + "</td>");
                hw.Write("</tr>");

                sno++; // increment S.No
                subTotal += amount;
                grandTotal += amount;
            }

            // 👉 Last Subtotal
            if (currentType != "")
            {
                hw.Write("<tr style='font-weight:bold;'>");
                hw.Write("<td colspan='7' align='right'>Subtotal (" + currentType + ")</td>");
                hw.Write("<td>" + subTotal.ToString("N2") + "</td>");
                hw.Write("</tr>");
            }

            // 👉 GRAND TOTAL
            hw.Write("<tr style='font-weight:bold; background-color:yellow;'>");
            hw.Write("<td colspan='7' align='right'>GRAND TOTAL</td>");
            hw.Write("<td>" + grandTotal.ToString("N2") + "</td>");
            hw.Write("</tr>");

            hw.Write("</table>");

            Response.Write(sw.ToString());
            Response.End();
        }
        protected void Button4_Click(object sender, EventArgs e)
        {
            string empty = "";
            if (TextBox1.Text != "")
            {
                empty = empty + " and Billno = '" + TextBox1.Text + "'";
            }
            if (TextBox2.Text != "")
            {
                empty = empty + " and Mobile = '" + TextBox2.Text + "'";
            }

            if (TextBox5.Text != "")
            {
                empty = empty + " and Ndbano = '" + TextBox5.Text + "'";
            }
            if (TextBox4.Text != "")
            {
                empty = empty + " and Daten between '" + TextBox3.Text + "' and '" + TextBox4.Text + "'";
            }
            if (DropDownList3.SelectedValue != "Select")
            {
                empty = empty + " and btype = '" + DropDownList3.SelectedValue + "'";
            }
            string qrajneeti = "select  ROW_NUMBER() OVER (ORDER BY newdelhibar_bill.id) AS S_No,newdelhibar_bill.Name,newdelhibar_bill.Ndbano,newdelhibar_bill.BCDno,Memberslist.Dob,newdelhibar_bill.Mobile,Memberslist.Resid_1 as Address,Memberslist.Email_id from newdelhibar_bill inner join Memberslist on Memberslist.Ndbano=newdelhibar_bill.Ndbano where newdelhibar_bill.Status!='Cancel' and newdelhibar_bill.Status='SUCCESS' and newdelhibar_bill.id!='0' " + empty + "  order by Daten desc";
            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=memberlistreport.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);

            // Add custom header manually (as HTML)
            oHtmlTextWriter.Write("<table border='1' style='width:100%; font-weight:bold;'>");
            oHtmlTextWriter.Write("<tr><td colspan='5' align='center' style='font-size:18px;'>Daily Payment Report</td></tr>");
            oHtmlTextWriter.Write("<tr><td colspan='8' align='right'>From Date: " + TextBox3.Text + " To:" + TextBox4.Text + "</td></tr>");
            oHtmlTextWriter.Write("</table><br/>");

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

            // Add custom footer manually
            //oHtmlTextWriter.Write("<br/><table border='1' style='width:100%; font-weight:bold;'>");
            //oHtmlTextWriter.Write("<tr><td colspan='9' align='right'> Total Amount: " + tamount + "</td></tr>");
            //oHtmlTextWriter.Write("</table>");

            Response.Write(oStringWriter.ToString());
            Response.End();
        }
        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)
            {
                DropDownList3.Visible = true;
                DropDownList3.DataSource = dsrajneeti;
                DropDownList3.DataTextField = "btype";
                DropDownList3.DataValueField = "btype";
                DropDownList3.DataBind();
                DropDownList3.Items.Insert(0, "Select");

            }

        }
        protected void DropDownList3_SelectedIndexChanged(object sender, EventArgs e)
        {
            string qrajneeti = "select * from newdelhibar_bill where Status!='Cancel' and btype='"+DropDownList3.SelectedValue+"'  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;
                //  DataGrid1.DataSource = dsrajneeti;
                // DataGrid1.DataBind();
                // DataGrid1.Visible = true;
            }
            else
            {
                DataGrid2.Visible = false;
            }
        }
    }
}