Hi,
I am trying building a dashboard application where i want to get default data on page load and when filters applied i want to get the data based on filters
now my problem is when i select date filter and site filter i cannot able to get the data it shows empty data and datefilter item is selected startdate and end date textboxes are not hiding please help me with the code
protected void applyFilters_Click(object sender, EventArgs e)
{
ApplyFilters();
}
private void ApplyFilters()
{
string selectedDateFilter = dateFilter.SelectedValue;
siteFilter = ddlSites.SelectedIndex > 0 ? ddlSites.SelectedItem.Text : null;
switch (selectedDateFilter)
{
case "today":
startDate = DateTime.Now.Date;
endDate = DateTime.Now;
break;
case "thisWeek":
startDate = DateTime.Now.StartOfWeek(DayOfWeek.Monday);
endDate = DateTime.Now;
break;
case "thisMonth":
startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
endDate = DateTime.Now;
break;
case "yesterday":
startDate = DateTime.Now.Date.AddDays(-1);
endDate = DateTime.Now.Date;
break;
case "monthly":
startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
endDate = DateTime.Now;
break;
case "quarterly":
int currentQuarter = (DateTime.Now.Month - 1) / 3 + 1;
startDate = new DateTime(DateTime.Now.Year, (currentQuarter - 1) * 3 + 1, 1);
endDate = DateTime.Now;
break;
case "halfYearly":
int currentHalf = DateTime.Now.Month <= 6 ? 1 : 2;
startDate = new DateTime(DateTime.Now.Year, (currentHalf - 1) * 6 + 1, 1);
endDate = DateTime.Now;
break;
case "yearly":
startDate = new DateTime(DateTime.Now.Year, 1, 1);
endDate = DateTime.Now;
break;
case "Custom":
startDate = Convert.ToDateTime(txtStartDate.Text);
endDate = Convert.ToDateTime(txtEndDate.Text);
break;
default:
startDate = DateTime.Now.AddMonths(-1);
endDate = DateTime.Now;
break;
}
LoadDashboardData();
}
ALTER PROCEDURE [dbo].[GetDashboardData]
@FromDate DATE = NULL,
@ToDate DATE = NULL,
@PeriodType NVARCHAR(50) = NULL,
@SiteFilter NVARCHAR(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
-- Determine the minimum date from the data in the CustOrderDetails table
DECLARE @MinDate DATE;
SELECT @MinDate = MIN(OrderedDate) FROM dbo.CustOrderDetails;
-- Define date range based on PeriodType if not provided
IF @PeriodType IS NOT NULL
BEGIN
IF @PeriodType = 'Today'
BEGIN
SET @FromDate = CAST(GETDATE() - 1 AS DATE);
SET @ToDate = CAST(GETDATE() - 1 AS DATE);
END
ELSE IF @PeriodType = 'This Week'
BEGIN
SET @FromDate = DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0);
SET @ToDate = DATEADD(DAY, 6, @FromDate);
END
ELSE IF @PeriodType = 'Monthly'
BEGIN
SET @FromDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);
SET @ToDate = DATEADD(DAY, -1, DATEADD(MONTH, 1, @FromDate));
END
ELSE IF @PeriodType = 'Quarterly'
BEGIN
DECLARE @Quarter INT;
SET @Quarter = DATEPART(QUARTER, GETDATE());
SET @FromDate = DATEADD(QUARTER, @Quarter - 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0));
SET @ToDate = DATEADD(DAY, -1, DATEADD(QUARTER, @Quarter, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)));
END
ELSE IF @PeriodType = 'Half Yearly'
BEGIN
DECLARE @CurrentMonth INT = MONTH(GETDATE());
IF @CurrentMonth <= 6
BEGIN
SET @FromDate = DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0);
SET @ToDate = DATEADD(DAY, -1, DATEADD(MONTH, 6, @FromDate));
END
ELSE
BEGIN
SET @FromDate = DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0));
SET @ToDate = DATEADD(DAY, -1, DATEADD(YEAR, 1, @FromDate));
END
END
ELSE IF @PeriodType = 'Yearly'
BEGIN
SET @FromDate = DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0);
SET @ToDate = DATEADD(DAY, -1, DATEADD(YEAR, 1, @FromDate));
END
END
-- Ensure @FromDate is not earlier than the minimum date available in the database
SET @FromDate = ISNULL(@FromDate, @MinDate);
-- Fetch data based on the provided date range and site name filter
-- Total Sales
SELECT
TotalSales = (
SELECT SUM(cd.TotalAmount)
FROM CustOrderDetails cd
WHERE cd.OrderedDate BETWEEN @FromDate AND @ToDate
AND (
@SiteFilter IS NULL
OR CASE
WHEN cd.[CustOrderNumber] LIKE 'FE%' THEN 'FE'
ELSE 'Other'
END = @SiteFilter
)
),
-- Orders Count
OrdersCount = (
SELECT COUNT(*)
FROM CustOrderDetails cd
WHERE cd.OrderedDate BETWEEN @FromDate AND @ToDate
AND (
@SiteFilter IS NULL
OR CASE
WHEN cd.[CustOrderNumber] LIKE 'FE%' THEN 'FE'
ELSE 'Other'
END = @SiteFilter
)
)
-- Highest Product Price
SELECT TOP 1
coi.Sku,
coi.ProductPrice,
SUM(coi.Quantity) AS TotalQuantitySold
FROM dbo.CustOrderItems coi
JOIN dbo.CustOrderDetails cd ON coi.CSOID = cd.CSOID
WHERE cd.OrderedDate BETWEEN @FromDate AND @ToDate
AND (
@SiteFilter IS NULL
OR CASE
WHEN cd.[CustOrderNumber] LIKE 'FE%' THEN 'FE'
ELSE 'Other'
END = @SiteFilter
)
GROUP BY coi.Sku, coi.ProductPrice
ORDER BY TotalQuantitySold DESC, coi.ProductPrice DESC;
END;
I want to create time period filter like Today, yesterday, this week, this moth, this quarter, half year, this year.