Untitled

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

namespace customerACC1
{
    public partial class ServicePlans : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void ServicePlanButton(object sender, EventArgs e)
        {
            String query = "SELECT * FROM allServicePlans";
            String connStr = WebConfigurationManager.ConnectionStrings["CA1"].ToString();
            SqlConnection conn = new SqlConnection(connStr);

            // Pass the SQL query to SqlCommand and set CommandType to Text
            SqlCommand GetAllServicePlansProc = new SqlCommand(query, conn)
            {
                CommandType = CommandType.Text // Specify query type
            };


            try
            {
                conn.Open();

                SqlDataReader reader = GetAllServicePlansProc.ExecuteReader();

                if (reader.HasRows)
                {
                    Response.Write("<div class='table-wrapper'>");
                    Response.Write("<table class='table'>");
                    Response.Write("<thead><tr>");

                    // Write column headers
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        Response.Write($"<th>{reader.GetName(i)}</th>");
                    }
                    Response.Write("</tr></thead>");
                    Response.Write("<tbody>");

                    // Write rows
                    while (reader.Read())
                    {
                        Response.Write("<tr>");
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            Response.Write($"<td>{reader[i]}</td>");
                        }
                        Response.Write("</tr>");
                    }

                    Response.Write("</tbody></table></div>");
                }
                else
                {
                    Response.Write("<div class='feedback'>No service plans available.</div>");
                }
              
             

                reader.Close();
            }
            catch (Exception ex)
            {
                Response.Write("Error: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        protected void UsageDetails_Click(object sender, EventArgs e)
        {
            String connStr = WebConfigurationManager.ConnectionStrings["CA1"].ToString();
            SqlConnection conn = new SqlConnection(connStr);
            String PlanName = planName.Text.Trim(); // Trim whitespace
            String StartDate = startDate.Text.Trim();
            String EndDate = endDate.Text.Trim();

            // Use a SELECT statement to query the TVF
            String query = "SELECT * FROM dbo.Consumption(@Plan_name, @start_date, @end_date)";
            SqlCommand cmd = new SqlCommand(query, conn);

            cmd.Parameters.AddWithValue("@Plan_name", PlanName);
            cmd.Parameters.AddWithValue("@start_date", StartDate);
            cmd.Parameters.AddWithValue("@end_date", EndDate);

            try
            {
                conn.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                if (PlanName == "" || StartDate == "" || EndDate == "")
                {
                    string errorMessage = "Please provide the following: ";
                    if (PlanName == "") errorMessage += "Plan Name, ";
                    if (StartDate == "") errorMessage += "Start Date, ";
                    if (EndDate == "") errorMessage += "End Date.";
                    errorMessage = errorMessage.TrimEnd(',', ' ');

                    Response.Write($"<div class='feedback'>{errorMessage}</div>");
                }
                else if (reader.HasRows)
                {
                    Response.Write("<div class='table-wrapper'>");
                    Response.Write("<table class='table'>");
                    Response.Write("<thead><tr>");

                    // Write column headers
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        Response.Write($"<th>{reader.GetName(i)}</th>");
                    }
                    Response.Write("</tr></thead>");
                    Response.Write("<tbody>");

                    // Write rows
                    while (reader.Read())
                    {
                        Response.Write("<tr>");
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            Response.Write($"<td>{reader[i]}</td>");
                        }
                        Response.Write("</tr>");
                    }

                    Response.Write("</tbody></table></div>");
                }
                else
                {
                    Response.Write("<div class='feedback'>No usage details available.</div>");
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                Response.Write("Error: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        protected void UnSubscribed_plans_button_Click(object sender, EventArgs e)
        {
            String connStr = WebConfigurationManager.ConnectionStrings["CA1"].ToString();
            SqlConnection conn = new SqlConnection(connStr);
            String mobileNo = UnSubscribed_plans.Text.Trim(); // Trim whitespace

            SqlCommand GetAllUnsubscribedServicePlansProc = new SqlCommand("dbo.Unsubscribed_Plans", conn)
            {
                CommandType = CommandType.StoredProcedure // Specify stored procedure type
            };

            GetAllUnsubscribedServicePlansProc.Parameters.Add(new SqlParameter("@mobile_num", mobileNo));

            try
            {
                conn.Open();

                SqlDataReader reader = GetAllUnsubscribedServicePlansProc.ExecuteReader();
                if (mobileNo == "")
                {
                    Response.Write("please enter MobileNo to see plans you are not subscribed to:");
                }
                else if (reader.HasRows)
                {
                    Response.Write("<div class='table-wrapper'>");
                    Response.Write("<table class='table'>");
                    Response.Write("<thead><tr>");

                    // Write column headers
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        Response.Write($"<th>{reader.GetName(i)}</th>");
                    }
                    Response.Write("</tr></thead>");
                    Response.Write("<tbody>");

                    // Write rows
                    while (reader.Read())
                    {
                        Response.Write("<tr>");
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            Response.Write($"<td>{reader[i]}</td>");
                        }
                        Response.Write("</tr>");
                    }

                    Response.Write("</tbody></table></div>");
                }
                else if (string.IsNullOrEmpty(mobileNo))
                {
                    Response.Write("<div class='feedback'>Please enter Mobile No to see plans you are not subscribed to.</div>");
                }
                else
                {
                    Response.Write("<div class='feedback'>No cashback transactions available.</div>");
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                Response.Write("Error: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        protected void ActivePlansButton_Click(object sender, EventArgs e)
        {
            String connStr = WebConfigurationManager.ConnectionStrings["CA1"].ToString();
            SqlConnection conn = new SqlConnection(connStr);
            String Activeplans = ActivePlans.Text.Trim(); // Trim whitespace

            // Use a SELECT statement to query the TVF
            String query = "SELECT * FROM dbo.Usage_Plan_CurrentMonth(@MobileNo)";
            SqlCommand cmd = new SqlCommand(query, conn);

            cmd.Parameters.AddWithValue("@MobileNo", Activeplans);

            try
            {
                conn.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                if (Activeplans == "")
                {
                    Response.Write("please enter mobile No to see active plans for your account during this month.");
                }
                else if (reader.HasRows)
                {
                    // Dynamically create a table
                    Response.Write("<table border='1'>");
                    Response.Write("<tr>");

                    // Write column headers
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        Response.Write($"<th>{reader.GetName(i)}</th>");
                    }
                    Response.Write("</tr>");

                    // Write rows
                    while (reader.Read())
                    {
                        Response.Write("<tr>");
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            Response.Write($"<td>{reader[i]}</td>");
                        }
                        Response.Write("</tr>");
                    }

                    Response.Write("</table>");
                }
                else
                {
                    Response.Write("No active plans for your account during this month.");
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                Response.Write("Error: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        protected void cashback_transactions_button_Click(object sender, EventArgs e)
        {
            String connStr = WebConfigurationManager.ConnectionStrings["CA1"].ToString();
            SqlConnection conn = new SqlConnection(connStr);
            String nationalID = cashback_transactions_textbox.Text.Trim(); // Trim whitespace

            // Use a SELECT statement to query the TVF
            String query = "SELECT * FROM dbo.Cashback_Wallet_Customer(@NationalID)";
            SqlCommand cmd = new SqlCommand(query, conn);

            cmd.Parameters.AddWithValue("@NationalID", nationalID);


            try
            {
                conn.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                if (nationalID == "")
                {
                    Response.Write("please enter your national ID to see your cashback transactions");
                }
                else if (reader.HasRows)
                {
                    // Dynamically create a table
                    Response.Write("<table border='1'>");
                    Response.Write("<tr>");

                    // Write column headers
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        Response.Write($"<th>{reader.GetName(i)}</th>");
                    }
                    Response.Write("</tr>");

                    // Write rows
                    while (reader.Read())
                    {
                        Response.Write("<tr>");
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            Response.Write($"<td>{reader[i]}</td>");
                        }
                        Response.Write("</tr>");
                    }

                    Response.Write("</table>");
                }
                else
                {
                    Response.Write("No cashback transactions available.");
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                Response.Write("Error: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}