Display Days difference between two dates and specified dates in ASP.Net RDLC Report

kankon
 
on Aug 02, 2021 06:50 AM
1738 Views

hello,

In report viewer i would like add skip total vacations days

Friday + saturday, also date holiday

01-1

25-2

26-2

20-8

my code total in report viewer

=DateDiff("d", Fields!OPsec.Value, Fields!OPusers.Value)

i need it in report viewer, not only 2019, its yearly

protected void txtBoxDate2_TextChanged(object sender, EventArgs e)
{
    DateTime d1 = Convert.ToDateTime(txtBoxDate.Text);
    DateTime d2 = Convert.ToDateTime(txtBoxDate2.Text);
    var totalDays = (d2 - d1).Days;
    int wd = 0;
    for (int x = 0; x <= totalDays; x++)
    {
        DateTime currendDate = d1.AddDays(x);
        if (currendDate.DayOfWeek != DayOfWeek.Friday)
        {
            if (!Holiday().Contains(d1.AddDays(x)))
            {
                wd += 1;
            }
        }
    }
    lblLabel1.Text = wd.ToString();
}
 
private List<DateTime> Holiday()
{
    List<DateTime> dates = new List<DateTime>();
 
    //2019
    dates.Add(new DateTime(2019, 01, 01));
    dates.Add(new DateTime(2019, 02, 25));
    dates.Add(new DateTime(2019, 02, 26));
    dates.Add(new DateTime(2019, 04, 04));
    dates.Add(new DateTime(2019, 08, 31));
    dates.Add(new DateTime(2019, 09, 11));
    dates.Add(new DateTime(2019, 11, 10));
 
 
    return dates;
}
 
 
private void cal_vaca()
{
    DateTime d1 = Convert.ToDateTime(txtBoxDate.Text);
    DateTime d2 = Convert.ToDateTime(txtBoxDate2.Text);
    var totalDays = (d2 - d1).Days;
    int wd = 0;
    for (int x = 0; x <= totalDays; x++)
    {
        DateTime currendDate = d1.AddDays(x);
        if (currendDate.DayOfWeek != DayOfWeek.Friday)
        {
            if (!Holiday().Contains(d1.AddDays(x)))
            {
                wd += 1;
            }
        }
    }
    lblLabel1.Text = wd.ToString();
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Aug 09, 2021 01:54 AM

Hi kankon,

For calculation of days except vacations days you need to add another column in your TypeDataSet.

Then from code behind set the value by calculating with vacation days.

After that use the column in your report to display instead of using expression for calculation.

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:ScriptManager runat="server" />
<rsweb:ReportViewer ID="ReportViewer1" runat="server"></rsweb:ReportViewer>

Namespaces

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Reporting.WebForms;

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        ReportViewer1.ProcessingMode = ProcessingMode.Local;
        ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/EmployeeReport.rdlc");
        ReportDataSource datasource = new ReportDataSource("Employees", GetData().Tables[0]);
        ReportViewer1.LocalReport.DataSources.Clear();
        ReportViewer1.LocalReport.DataSources.Add(datasource);
    }
}

private Employees GetData()
{
    string query = "SELECT TOP 5 FirstName 'Name',CAST(BirthDate AS DATE) 'StartDate',CAST(HireDate AS DATE) 'EndDate',0 'Days' FROM Employees " +
        " UNION SELECT 'Dharmendra',NULL,NULL,0";
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;

            sda.SelectCommand = cmd;
            using (Employees employees = new Employees())
            {
                sda.Fill(employees, "DataTable1");

                foreach (DataRow employee in employees.Tables["DataTable1"].Rows)
                {
                    if (!Convert.IsDBNull(employee["StartDate"]) || !Convert.IsDBNull(employee["EndDate"]))
                    {
                        DateTime startDate = Convert.ToDateTime(employee["StartDate"]);
                        DateTime endDate = Convert.ToDateTime(employee["EndDate"]);
                        // Calculate Holidays.
                        int diff = (endDate - startDate).Days;
                        int holidays = Holiday().Where(s => s >= startDate && s <= endDate).ToList().Count;
                        employee["Days"] = diff - holidays;
                    }
                }

                return employees;
            }
        }
    }
}

private List<DateTime> Holiday()
{
    List<DateTime> dates = new List<DateTime>();
    dates.Add(new DateTime(1949, 02, 25));
    dates.Add(new DateTime(2019, 01, 01));
    dates.Add(new DateTime(2019, 02, 26));
    dates.Add(new DateTime(2019, 04, 04));
    dates.Add(new DateTime(2019, 08, 31));
    dates.Add(new DateTime(2019, 09, 11));
    dates.Add(new DateTime(2019, 11, 10));

    return dates;
}

Screenshot