Untitled

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)
        {
            // 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 service plans available.");
        }

        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 (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 usage details available.");
        }

        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 (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 unsubscribed service plans available.");
        }

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