<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:TextBox ID="txtFromDate" runat="server" TextMode="Date"></asp:TextBox>
<asp:TextBox ID="txtToDate" runat="server" TextMode="Date"></asp:TextBox>
<asp:CheckBoxList ID="chkLocations" runat="server">
<asp:ListItem Text="1" Value="1"></asp:ListItem>
<asp:ListItem Text="2" Value="2"></asp:ListItem>
</asp:CheckBoxList>
<asp:CheckBoxList ID="chkCategories" runat="server">
<asp:ListItem Text="1" Value="1"></asp:ListItem>
<asp:ListItem Text="2" Value="2"></asp:ListItem>
</asp:CheckBoxList>
<asp:Button ID="btnGenerateReport" runat="server" Text="Generate Report" OnClick="btnGenerateReport_Click" />
<asp:GridView ID="gvEmployees" runat="server">
</asp:GridView>
</form>
</body>
</html>
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
// Any initial setup can be done here
}
}
protected void btnGenerateReport_Click(object sender, EventArgs e)
{
string fromDate = txtFromDate.Text;
string toDate = txtToDate.Text;
string locations = string.Join("','", chkLocations.Items.Cast<ListItem>()
.Where(li => li.Selected)
.Select(li => li.Value));
string categories = string.Join("','", chkCategories.Items.Cast<ListItem>()
.Where(li => li.Selected)
.Select(li => li.Value));
// Ensure that selected items are not empty to avoid SQL syntax errors
if (string.IsNullOrEmpty(locations))
{
locations = "''"; // To handle cases where no location is selected
}
if (string.IsNullOrEmpty(categories))
{
categories = "''"; // To handle cases where no category is selected
}
// Build the SQL query
string query = "SELECT EmployeeName, JoinDate, Locations, Category FROM EmployeesLoctCate WHERE JoinDate BETWEEN @FromDate AND @ToDate " +
"AND Locations IN ('" + locations + @"') AND Category IN ('" + categories + @"') ORDER BY JoinDate";
string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlDataAdapter da = new SqlDataAdapter(query, conn);
da.SelectCommand.Parameters.AddWithValue("@FromDate", fromDate);
da.SelectCommand.Parameters.AddWithValue("@ToDate", toDate);
DataTable dt = new DataTable();
da.Fill(dt);
gvEmployees.DataSource = dt;
gvEmployees.DataBind();
// Display the total count
//lblTotalCount.Text = "Total Employees: " + dt.Rows.Count;
}
}
0 Comments