Wednesday, March 4, 2015

Employee Time Log : Reporting using SSRS and Generic Handler

Hi Guys,

I added new page to the Employee Time Log to generate a simple report which I made using SQL Server Reporting Services (SSRS) and a ASP.NET Generic Handler (.ashx) File. Hope you like it.































Solution

1) Add a Generic Handler File, Report File and Report.html file as shown in the image.


2) Add the following html in Report.html

<form name="ReportForm" ng-submit="ReportForm.$valid && GenerateReport(CurrentLog)" novalidate>




    <div>
        <div class="panel panel-primary">

            <div class="panel-body">







                <div class="form-group">
                    <label>Start Time</label>
                    <span> {{ CurrentLog.StartTime = (StartTime | date:'yyyy-MM-dd HH:mm:ss')}}</span>
                    <div class="dropdown">
                        <a class="dropdown-toggle" id="dropdown1" role="button" data-toggle="dropdown" data-target="#" href="#">Click here to Set Start Time</a>
                        <ul class="dropdown-menu" role="menu">
                            <datetimepicker data-ng-model="StartTime" data-datetimepicker-config="{ dropdownSelector: '#dropdown1' }" />
                        </ul>
                    </div>
                </div>

                <div class="form-group">
                    <label>End Time</label>
                    <span> {{ CurrentLog.EndTime = (EndTime | date:'yyyy-MM-dd HH:mm:ss')}}</span>
                    <div class="dropdown">
                        <a class="dropdown-toggle" id="dropdown2" role="button" data-toggle="dropdown" data-target="#" href="#">Click here to Set End Time</a>
                        <ul class="dropdown-menu" role="menu">
                            <datetimepicker data-ng-model="EndTime" data-datetimepicker-config="{ dropdownSelector: '#dropdown2' }" />
                        </ul>
                    </div>
                </div>

                




                <div data-ng-hide="message == ''" class="alert " ng-class="alertClass">
                    {{message}}
                </div>


                <div class="pull-right">


                    <button type="submit" class="btn btn-primary"><span class="glyphicon glyphicon-print"></span> Generate</button>


                    <button type="reset" class="btn btn-primary" ng-click="Reset()"><span class="glyphicon glyphicon-remove"></span> Reset</button>
                </div>

            </div>

        </div>





</form>

3) Add the following code in ReportHandler.ashx


using Microsoft.Reporting.WebForms;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Web;

namespace EmployeeTimeLog.Handlers
{
    /// <summary>
    /// Summary description for ReportHandler
    /// </summary>
    public class ReportHandler : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {


            ReportViewer viewer = new ReportViewer();

            viewer.LocalReport.ReportPath = context.Server.MapPath("~/Reports/SummaryReport.rdlc");

            Warning[] warnings;
            string[] streamids;
            string mimeType;
            string encoding;
            string filenameExtension;

            viewer.LocalReport.DataSources.Add(new ReportDataSource("TimeLog", GetTimeLogsReport(context.Request.QueryString["username"],
               context.Request.QueryString["startdate"], context.Request.QueryString["enddate"])));
            CultureInfo enCultr = new CultureInfo("en-US");
            viewer.LocalReport.SetParameters(new ReportParameter("StartDate",
                DateTime.ParseExact(context.Request.QueryString["startdate"], "yyyy-MM-dd HH:mm:ss", enCultr).ToShortDateString()
                ));
            viewer.LocalReport.SetParameters(new ReportParameter("EndDate", 
                DateTime.ParseExact(context.Request.QueryString["enddate"], "yyyy-MM-dd HH:mm:ss", enCultr).ToShortDateString()
                ));

            viewer.LocalReport.Refresh();

            byte[] bytes = viewer.LocalReport.Render(
                "PDF", null, out mimeType, out encoding, out filenameExtension,
                out streamids, out warnings);                
            
            context.Response.Buffer = true;
            context.Response.Clear();
            context.Response.ContentType = mimeType;
            context.Response.AddHeader("content-disposition", "attachment; filename=" + System.IO.Path.GetTempFileName() + ".pdf");
            context.Response.BinaryWrite(bytes);
            context.Response.Flush();

        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }




        private List<EP_TimeLog> GetTimeLogsReport(string userName, string startDate, string endDate)
        {
            CultureInfo enCultr = new CultureInfo("en-US");
            using (EmployeePortalDataContext context = new EmployeePortalDataContext())
            {

                var temp = (from r in context.EP_TimeLogs
                            where r.CreatedBy.Trim().ToLower() == userName.Trim().ToLower() &&
                            r.StartTime.Date >= DateTime.ParseExact(startDate, "yyyy-MM-dd HH:mm:ss", enCultr).Date &&
                            r.EndTime.Value.Date <= DateTime.ParseExact(endDate, "yyyy-MM-dd HH:mm:ss", enCultr).Date
                            orderby r.StartTime ascending
                            select r);
                return temp.ToList();

            }
        }

      
    }
}


5) Add the following code to the app.js file

function ReportController($scope, $http, authService, $filter,$sce) {
    var scope = $scope;

    scope.collection = [];
    scope.message = '';
    scope.alertClass = 'alert-danger';

    scope.rowCollection = [];
    scope.CurrentLog = [];

    scope.StartTime = '';
    scope.EndTime = '';
    

    scope.authentication = authService.authentication;

    scope.GenerateReport = function (log) {
        
        
        window.open("/Handlers/ReportHandler.ashx?username=" + scope.authentication.userName
            + "&startdate=" + log.StartTime
            + "&enddate=" + log.EndTime);


     

       
    }

    scope.Reset = function () {



        var responsePromise = getById(2, $http);

        responsePromise.success(function (data, status, headers, config) {

            scope.CurrentLog = clone(data[0]);
            scope.message = '';
            scope.StartTime = '';
            scope.EndTime = '';
            scope.CurrentLog.Duration = 0;
            scope.alertClass = 'alert-danger';

        });
        responsePromise.error(function (data, status, headers, config) {
            scope.alertClass = 'alert-danger';
            scope.message = data;

        });

    }



    
    scope.Reset();


   





}