using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Resources;
using System.Globalization;
using System.Threading;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util;
using System.IO;
public partial class Reports_TA_UI_Report_DailyDeviation : Base.BasePage
{
    #region Global variable declaration
    //Global variable declaration here.
    Cls_CheckUser objCheckUser = new Cls_CheckUser();
    DataSet ds_Checkuser = new DataSet();
    HeaderInformation hdrTable = new HeaderInformation();
    GetReports rptEmp = new GetReports();
    Rpt_Common rptcommon = new Rpt_Common();
    ResourceManager rm;
    CultureInfo ci;
    cls_Common objCommon = new cls_Common();
    EmployeeEventData objEmployee = new EmployeeEventData();
    string strFromdate, gstrDateFormat, strTodate;
    int intIndividualUser = 0;
    #endregion
    #region override Page Load Event
    /// <summary>
    /// Page load event 
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected override void Page_Load(object sender, EventArgs e)
    {
        try
        {
            // session checking
            base.Page_Load(sender, e);
            //get the culture info to set the language        
            if (!Page.IsPostBack)
            {
                Hidden1.Value = Convert.ToString(Session["IDateFormat"]);
                base.CheckPages(0);
                objCommon.menu("Attendance Reports", "162");
            }
            string source_Language;
            source_Language = Convert.ToString(Session["Session_ActiveLanguage"]);
            rm = new ResourceManager(source_Language, System.Reflection.Assembly.Load("App_GlobalResources"));
            ci = Thread.CurrentThread.CurrentCulture;
            site.SiteMapProvider = Convert.ToString(Session["SiteMapProvider"]);
            LoadStrings(ci);
            //Set UserRights
            UserRights();
            if (!IsPostBack)
            {
                // Bindings the employee category and type.                
                BindingEmployeeCategoryandType();
                txtFromDate.Text = (Convert.ToDateTime(objCommon.CurrentMonthStartDate())).ToString(Convert.ToString(Session["MDateFormat"]));
                txtToDate.Text = (Convert.ToDateTime(System.DateTime.Now.ToShortDateString())).ToString(Convert.ToString(Session["MDateFormat"]));
                //Added by Manjula.P on 06-May-2014
                FillDesignation();
                ddlDesignation.PreRender += LB_PreRender;
            }
            //Check whether the employee is Individual User
            intIndividualUser = objCheckUser.CheckIndividualUser(Convert.ToString(Session["UserGroupName"]));
            if (intIndividualUser >= 1)
            {
                PlaceHolderMonthlyAttendance.Controls.Clear();
                divEmployeeDetails.Visible = false;
            }
            //Convert date time to MM/dd/YYYY format
            gstrDateFormat = Convert.ToString(Session["MDateFormat"]);
            strFromdate = txtFromDate.Text;
            strFromdate = objCommon.DateConvertion(gstrDateFormat, strFromdate);
            strTodate = txtToDate.Text;
            strTodate = objCommon.DateConvertion(gstrDateFormat, strTodate);
        }
        catch (Exception ex)
        {
            base.Message(ex.Message);
        }
    }
    #endregion
    #region User Defined Load Functions
    //Getting Information from resource file and display it in label
    private void LoadStrings(CultureInfo ci)
    {
        btn_View.Text = "&nbsp;&nbsp;" + rm.GetString("btn_View", ci);
        lblDailyDeviationReport.Text = rm.GetString("Reports_DailyDeviation_DailyDeviationReport_Title", ci);
        lblEmployeeName.Text = rm.GetString("email_EmployeeName", ci);
        lblEmployeeId.Text = rm.GetString("email_EmployeeId", ci);
        lblEmpCategory.Text = rm.GetString("Master_Category", ci);
        lblEmpType.Text = rm.GetString("Master_Type", ci);
        lblTimePeriod.Text = rm.GetString("Reports_TimePeriod", ci);
        lblFrom.Text = rm.GetString("email_FromDate", ci);
        lblTo.Text = rm.GetString("email_ToDate", ci);
        lblMinimumWorkHours.Text = rm.GetString("Report_Minimum_Work_Hours", ci);
        lblEmpSwipe.Text = rm.GetString("TerminalDataType_Card_No", ci);
        //Added by Manjula.P on 06-May-2014 for filtering option for designation and gender
        lblDesignation.Text = rm.GetString("EmployeeMaster_Designation", ci);
        lblGender.Text = rm.GetString("EmployeeMaster_Gender", ci);
        ddlGender.Items[0].Text = rm.GetString("Caption_Both", ci);
        ddlGender.Items[1].Text = rm.GetString("rbtn_EmployeeMaster_Male", ci);
        ddlGender.Items[2].Text = rm.GetString("rbtn_EmployeeMaster_Female", ci);
        ddlDesignation.Items[0].Text = rm.GetString("ExportData_All", ci);
    }
    private void UserRights()
    {
        ds_Checkuser = objCheckUser.checkRights(163, Convert.ToInt32(Session["userGroup_Id"]), "viewPrv");
        if (ds_Checkuser.Tables[0].Rows.Count > 0)
        {
            if (ds_Checkuser.Tables[0].Rows[0]["viewPrv"] == DBNull.Value || Convert.ToInt32(ds_Checkuser.Tables[0].Rows[0]["viewPrv"]) == 1)
            {
                btn_View.Visible = true;
            }
            else
            {
                btn_View.Visible = false;
            }
        }
        else
        {
            Response.Redirect("ReportsLandingPage.aspx");
        }
    }
    #endregion
    #region User Defined Binding Functinos
    /// <summary>
    /// Bindings the employee category and type.
    /// </summary>
    private void BindingEmployeeCategoryandType()
    {
        // employee category binding
        DataSet dsCategory = new DataSet();
        dsCategory = objEmployee.SelectCategoryDetailsEmployee("");
        if (dsCategory != null)
        {
            if (dsCategory.Tables[0].Rows.Count > 0)
            {
                ddlEmpCatagory.DataSource = dsCategory;
                ddlEmpCatagory.DataTextField = "CategoryName";
                ddlEmpCatagory.DataValueField = "CategoryId";
                ddlEmpCatagory.DataBind();
            }
            dsCategory.Dispose();
        }
        // employee type binding
        DataSet dsType = new DataSet();
        dsType = objEmployee.SelectTypeDetailsEmployee("");
        if (dsType != null)
        {
            if (dsType.Tables[0].Rows.Count > 0)
            {
                ddlEmpType.DataSource = dsType;
                ddlEmpType.DataTextField = "TypeName";
                ddlEmpType.DataValueField = "TypeId";
                ddlEmpType.DataBind();
            }
            dsType.Dispose();
        }
    }
    /// <summary>
    /// Fills the designation in the drop down  :Added by Manjula.P on 06-May-2014
    /// </summary>
    private void FillDesignation()
    {
        DataSet ds_designation = new DataSet();
        ds_designation = objEmployee.select_CommonDesignation();
        if (ds_designation != null)
        {
            if (ds_designation.Tables[0].Rows.Count > 0)
            {
                ddlDesignation.DataSource = ds_designation;
                ddlDesignation.DataTextField = "TitleName";
                ddlDesignation.DataValueField = "TitleId";
                ddlDesignation.DataBind();
            }
            ds_designation.Dispose();
            foreach (ListItem item in ddlDesignation.Items)
            {
                item.Attributes.Add("title", item.Text);
            }
        }
    }
    /// <summary>
    /// To apply tooltip for the designation in the drop down : Fills the designation in the drop down  :Added by Manjula.P on 06-May-2014
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void LB_PreRender(object sender, System.EventArgs e)
    {
        foreach (ListItem item in ddlDesignation.Items)
        {
            item.Attributes.Add("title", item.Text);
        }
    }
    #endregion
    #region User defined functions for fromdate,todate,comparedate validation
    /// <summary>
    /// Validate the FromDate
    /// </summary>
    /// <param name="s"></param>
    /// <param name="e"></param>
    public void ValidateFromDate(object s, ServerValidateEventArgs e)
    {
        try
        {
            strFromdate = txtFromDate.Text;
            gstrDateFormat = Convert.ToString(Session["MDateFormat"]);
            //strCurrentDate = objCommon.DateConvertion(gstrDateFormat,DateTime.Today.Date.ToString());
            strFromdate = objCommon.DateConvertion(gstrDateFormat, strFromdate);
            //strTodate = objCommon.DateConvertion(gstrDateFormat, strTodate);
            //DateTime dtCurrentDate = Convert.ToDateTime(DateTime.Today.Date.ToString("MM/dd/yyyy")); ;
            DateTime dtCurrentDate = Convert.ToDateTime(DateTime.Today.Date);
            DateTime dtFromDate = DateTime.Parse(strFromdate);
            TimeSpan diff = dtFromDate.Subtract(dtCurrentDate);
            int day = diff.Days;
            if (day <= 0)
                e.IsValid = true;
            else
            {
                base.Message("Enter Valid From Date");
                e.IsValid = false;
            }
        }
        catch
        {
            //
        }
    }
    /// <summary>
    /// Validate the ToDate
    /// </summary>
    /// <param name="s"></param>
    /// <param name="e"></param>
    public void ValidateToDate(object s, ServerValidateEventArgs e)
    {
        try
        {
            gstrDateFormat = Convert.ToString(Session["MDateFormat"]);
            //strCurrentDate = objCommon.DateConvertion(gstrDateFormat, DateTime.Today.Date.ToString());
            strTodate = txtToDate.Text;
            strTodate = objCommon.DateConvertion(gstrDateFormat, strTodate);
            //DateTime dtCurrentDate = Convert.ToDateTime(DateTime.Today.Date.ToString("MM/dd/yyyy"));
            DateTime dtCurrentDate = Convert.ToDateTime(DateTime.Today.Date);
            DateTime dtFromDate = DateTime.Parse(strTodate);
            TimeSpan diff = dtFromDate.Subtract(dtCurrentDate);
            int day = diff.Days;
            if (day <= 0)
                e.IsValid = true;
            else
            {
                base.Message("Enter Valid To Date");
                e.IsValid = false;
            }
        }
        catch
        {
            //
        }
    }
    /// <summary>
    /// Compare ToDate with FromDate
    /// </summary>
    /// <param name="s"></param>
    /// <param name="e"></param>
    public void CompareDate(object s, ServerValidateEventArgs e)
    {
        try
        {
            strFromdate = txtFromDate.Text;
            strTodate = txtToDate.Text;
            gstrDateFormat = Convert.ToString(Session["MDateFormat"]);
            strTodate = objCommon.DateConvertion(gstrDateFormat, strTodate);
            strFromdate = objCommon.DateConvertion(gstrDateFormat, strFromdate);
            DateTime dtToDate = DateTime.Parse(strTodate);
            DateTime dtFromDate = DateTime.Parse(strFromdate);
            TimeSpan diff = dtToDate.Subtract(dtFromDate);
            int day = diff.Days;
            if (day >= 0)
                e.IsValid = true;
            else
            {
                base.Message("Select valid date compare with Fromdate");
                e.IsValid = false;
            }
        }
        catch
        {
            //
        }
    }
    #endregion
    #region Button click events for view
    /// <summary>
    /// Button click events for view click 
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btn_View_Click(object sender, EventArgs e)
    {
        try
        {
            int employeeCategory = 0, employeeType = 0;
            string strSwipeCard = "";
            if (Page.IsValid == true)
            {
                //Create the datatable ,that holds the header information
                DataTable dtHdr = new DataTable();
                dtHdr = hdrTable.CreateHeader();
                dtHdr.TableName = "Header";
                //Add the Header information
                DataRow drHdr = dtHdr.NewRow();
                drHdr["Link"] = 1;
                drHdr["Hdr1"] = "Neuland Laboratories Limited";
                drHdr["Hdr2"] = "NEULAND";
                drHdr["Address1"] = "HeadOffice";
                drHdr["Address2"] = "Hyderabad";
                drHdr["ReportName"] = "Daily Deviation Report";
                if (txtFromDate.Text != "")
                {
                    DateTime tmpFrom = DateTime.Parse(strFromdate);
                    drHdr["FromDate"] = tmpFrom.ToString("dd-MMM-yyyy");
                }
                else
                    drHdr["FromDate"] = "";
                if (txtToDate.Text != "")
                {
                    DateTime tmpTo = DateTime.Parse(strTodate);
                    drHdr["ToDate"] = tmpTo.ToString("dd-MMM-yyyy");
                }
                else
                    drHdr["ToDate"] = "";
                dtHdr.Rows.Add(drHdr);
                string dtFrom = "", dtTo = "";
                if (txtFromDate.Text != "")
                    dtFrom = strFromdate;
                if (txtToDate.Text != "")
                    dtTo = strTodate;
                // set employee category and type
                if (Convert.ToInt32(ddlEmpCatagory.SelectedValue) != 0)
                {
                    employeeCategory = Convert.ToInt32(ddlEmpCatagory.SelectedValue);
                }
                else
                {
                    employeeCategory = 0;
                }
                if (Convert.ToInt32(ddlEmpType.SelectedValue) != 0)
                {
                    employeeType = Convert.ToInt32(ddlEmpType.SelectedValue);
                }
                else
                {
                    employeeType = 0;
                }
                //Get the Report source from the Server and load this into the report table
                DataSet dsDailyDeviation = new DataSet();
                //this is new report
                int shft = 0;
                string Level = string.Empty;
                string employeeName = string.Empty;
                string empName = string.Empty;
                string empId = string.Empty;
                if (intIndividualUser >= 1)
                {
                    empName = string.Empty;
                    shft = 0;
                    Level = string.Empty;
                    empId = Convert.ToString(Session["EmployeeCodeId"]);
                }
                else
                {
                    empName = Convert.ToString(txtEmployeeName.Text);
                    empId = Convert.ToString(txtEmployeeId.Text.Trim());
                    employeeName = this.txtEmployeeName.Text.Replace("'", "''");
                    employeeName = employeeName.Replace(' ', ' ');
                    Level = rptcommon.getNodes(Convert.ToInt16(Session["parentLevelId"]), Convert.ToString(Session["LevelRights"]));
                }
                strSwipeCard = Convert.ToString(txtSwipeCard.Text).Trim().Replace("'", "''");
                string MinWorkHours = Convert.ToString(txtMinWorkHours.Text);
                string strEmpAutoId = "0";
                if (Session["ReportsBasedOn"] != null)
                {
                    if (Convert.ToString(Session["ReportsBasedOn"]) == "False")
                    {
                        strEmpAutoId = "0";
                    }
                    else
                    {
                        strEmpAutoId = Convert.ToString(Session["EmpAutoID"]);
                    }
                }

                dsDailyDeviation = rptEmp.SelectDailyDeviationReport(dtFrom, dtTo, employeeName, Level, empId, employeeCategory, employeeType, Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), strSwipeCard, Convert.ToInt64(strEmpAutoId), Convert.ToInt16(Session["parentLevelId"]), Convert.ToInt32(ddlDesignation.SelectedValue), ddlGender.SelectedValue, 0);
                Session["dsDailyDeviation"] = dsDailyDeviation;
                //Session["parentLevelId"] = null;
                DataTable dtMonthlyAttendance = new DataTable();
                //Check the Empty table 
                if (dsDailyDeviation.Tables[0] == null || dsDailyDeviation.Tables[0].Rows.Count == 0)
                {
                    string strMsg;
                    strMsg = "<script language='javascript'>";
                    strMsg = strMsg + "alert('There is no record ');";
                    strMsg = strMsg + "</script>";
                    Page.RegisterStartupScript("Display Message", strMsg);
                    return;
                }
                //Redirect the page to the report viewer
                string strReportName = "DailyDeviation";
                //Cryptographys.Cryptography.Encrypt("DailyDeviation", "AnglerReports");
                string strScript = "";
                string strUrl = "TA_UI_Report_ReportViewer.aspx";
                strScript = strScript + "<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>";
                strScript = strScript + "<meta http-equiv='X-UA-Compatible' content='IE=document.documentMode;' />";
                strScript = strScript + "<script language='javascript'>";
                strScript = strScript + "window.open('TA_UI_Report_ReportViewer.aspx?ReportName=" + strReportName + "&dtFrom=" + dtFrom + "&dtTo=" + dtTo + "&empid=" + empId + "&shft=0&EmpName=" + employeeName + "&Level=" + Level + "&EmpCategory=" + employeeCategory + "&EmpType=" + employeeType + "&ParentLevelId=" + Convert.ToInt16(Session["parentLevelId"]) + "&UserGroupName=" + Session["UserGroupName"] + "&GradeLevel=" + Session["GradeLevel"] + "&MinWorkHours=" + MinWorkHours + "&SwipeCard=" + strSwipeCard + "&Designation=" + Convert.ToInt32(ddlDesignation.SelectedValue) + "&Gender=" + ddlGender.SelectedValue + "','','left=10px, top=60px, width=1000px, height=600px,scrollbars=yes, status=no, resizable=yes');";
                strScript = strScript + "</script>";
                Response.Write(strScript);
            }
        }
        catch (Exception ex)
        {
            base.Message(ex.Message);
        }
    }
    protected void btn_Export_Click(object sender, EventArgs e)
    {
        try
        {
            int employeeCategory = 0, employeeType = 0;
            string strSwipeCard = "";
            if (Page.IsValid == true)
            {
                //Create the datatable ,that holds the header information
                DataTable dtHdr = new DataTable();
                dtHdr = hdrTable.CreateHeader();
                dtHdr.TableName = "Header";
                //Add the Header information
                DataRow drHdr = dtHdr.NewRow();
                drHdr["Link"] = 1;
                drHdr["Hdr1"] = "Neuland Laboratories Limited";
                drHdr["Hdr2"] = "NEULAND";
                drHdr["Address1"] = "HeadOffice";
                drHdr["Address2"] = "Hyderabad";
                drHdr["ReportName"] = "Daily Deviation Report";
                if (txtFromDate.Text != "")
                {
                    DateTime tmpFrom = DateTime.Parse(strFromdate);
                    drHdr["FromDate"] = tmpFrom.ToString("dd-MMM-yyyy");
                }
                else
                    drHdr["FromDate"] = "";
                if (txtToDate.Text != "")
                {
                    DateTime tmpTo = DateTime.Parse(strTodate);
                    drHdr["ToDate"] = tmpTo.ToString("dd-MMM-yyyy");
                }
                else
                    drHdr["ToDate"] = "";
                dtHdr.Rows.Add(drHdr);
                string dtFrom = "", dtTo = "";
                if (txtFromDate.Text != "")
                    dtFrom = strFromdate;
                if (txtToDate.Text != "")
                    dtTo = strTodate;
                // set employee category and type
                if (Convert.ToInt32(ddlEmpCatagory.SelectedValue) != 0)
                {
                    employeeCategory = Convert.ToInt32(ddlEmpCatagory.SelectedValue);
                }
                else
                {
                    employeeCategory = 0;
                }
                if (Convert.ToInt32(ddlEmpType.SelectedValue) != 0)
                {
                    employeeType = Convert.ToInt32(ddlEmpType.SelectedValue);
                }
                else
                {
                    employeeType = 0;
                }
                //Get the Report source from the Server and load this into the report table
                DataSet dsDailyDeviation = new DataSet();
                //this is new report
                int shft = 0;
                string Level = string.Empty;
                string employeeName = string.Empty;
                string empName = string.Empty;
                string empId = string.Empty;
                if (intIndividualUser >= 1)
                {
                    empName = string.Empty;
                    shft = 0;
                    Level = string.Empty;
                    empId = Convert.ToString(Session["EmployeeCodeId"]);
                }
                else
                {
                    empName = Convert.ToString(txtEmployeeName.Text);
                    empId = Convert.ToString(txtEmployeeId.Text.Trim());
                    employeeName = this.txtEmployeeName.Text.Replace("'", "''");
                    employeeName = employeeName.Replace(' ', ' ');
                    Level = rptcommon.getNodes(Convert.ToInt16(Session["parentLevelId"]), Convert.ToString(Session["LevelRights"]));
                }
                strSwipeCard = Convert.ToString(txtSwipeCard.Text).Trim().Replace("'", "''");
                string MinWorkHours = Convert.ToString(txtMinWorkHours.Text);
                string strEmpAutoId = "0";
                if (Session["ReportsBasedOn"] != null)
                {
                    if (Convert.ToString(Session["ReportsBasedOn"]) == "False")
                    {
                        strEmpAutoId = "0";
                    }
                    else
                    {
                        strEmpAutoId = Convert.ToString(Session["EmpAutoID"]);
                    }
                }
                dsDailyDeviation = rptEmp.SelectDailyDeviationReport(dtFrom, dtTo, employeeName, Level, empId, employeeCategory, employeeType, Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), strSwipeCard, Convert.ToInt64(strEmpAutoId), Convert.ToInt16(Session["parentLevelId"]), Convert.ToInt32(ddlDesignation.SelectedValue), ddlGender.SelectedValue, 1);
                Session["dsDailyDeviation"] = dsDailyDeviation;
                if (dsDailyDeviation.Tables[0] == null || dsDailyDeviation.Tables[0].Rows.Count == 0)
                {
                    string strMsg;
                    strMsg = "<script language='javascript'>";
                    strMsg = strMsg + "alert('There is no record ');";
                    strMsg = strMsg + "</script>";
                    Page.RegisterStartupScript("Display Message", strMsg);
                    return;
                }
                else
                {
                    HSSFWorkbook workbook = new HSSFWorkbook();
                    ISheet sheet = workbook.CreateSheet("Daily Deviation");
                    sheet.IsPrintGridlines = true;
                    sheet.VerticallyCenter = true;
                    int rowIndex = 0;
                    NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
                    HSSFFont hFont = (HSSFFont)workbook.CreateFont();
                    hFont.Boldweight = (short)FontBoldWeight.Bold;
                    hFont.Color = HSSFColor.Maroon.Index;
                    HSSFCellStyle hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                    hStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;                    
                    hStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    hStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    hStyle.BorderRight= NPOI.SS.UserModel.BorderStyle.Thin;                    
                    hStyle.SetFont(hFont);
                    row.CreateCell(0).SetCellValue("Sl.No");
                    row.CreateCell(1).SetCellValue("level");
                    row.CreateCell(2).SetCellValue("Emp.No");
                    row.CreateCell(3).SetCellValue("Employee Name");
                    row.CreateCell(4).SetCellValue("Designation");
                    row.CreateCell(5).SetCellValue("Gender");
                    row.CreateCell(6).SetCellValue("Employee Category");
                    row.CreateCell(7).SetCellValue("Date");
                    row.CreateCell(8).SetCellValue("Daily Minimum Working Hrs");
                    row.CreateCell(9).SetCellValue("Leave / Permission Hrs");
                    row.CreateCell(10).SetCellValue("Target Work Hrs");
                    row.CreateCell(11).SetCellValue("Actual Worked Hrs");
                    row.CreateCell(12).SetCellValue("Deviated Hrs");
                    //row.RowStyle = hStyle;
                    for (int i = 0; i <= 12; i++)
                    {
                        row.GetCell(i).CellStyle = hStyle;
                    }
                    rowIndex++;
                    string OldEmployeeId = "0";
                    string PreviousEmployeeId = "-1";
                    string Total = "";
                    string column = "";
                    foreach (DataRow DR in dsDailyDeviation.Tables[0].Rows)
                    {
                        row = sheet.CreateRow(rowIndex);
                        row.Height = 300;
                        OldEmployeeId = Convert.ToString(DR["Emp. No"]);
                        if (OldEmployeeId == PreviousEmployeeId)
                        {
                            row.CreateCell(7).SetCellValue(Convert.ToString(DR["Date"]));
                            row.CreateCell(8).SetCellValue(Convert.ToString(DR["Daily Minimum Working Hrs"]));
                            row.CreateCell(9).SetCellValue(Convert.ToString(DR["Leave/Permission Hrs"]));
                            row.CreateCell(10).SetCellValue(Convert.ToString(DR["Target Worked Hrs"]));
                            row.CreateCell(11).SetCellValue(Convert.ToString(DR["Actual Worked Hrs"]));
                            row.CreateCell(12).SetCellValue(Convert.ToString(DR["Deviated Hrs"]));
                            Total = Convert.ToString(DR["TotaldeviationHrs"]);
                        }
                        else
                        {
                            if (PreviousEmployeeId != "-1")
                            {
                                if (OldEmployeeId != PreviousEmployeeId)
                                {
                                    row.CreateCell(11).SetCellValue("Total Deviated Hours");
                                    row.GetCell(11).CellStyle = hStyle;
                                    row.CreateCell(12).SetCellValue(Total);
                                    row.GetCell(12).CellStyle = hStyle;
                                    Total = "";
                                    rowIndex++;
                                    row = sheet.CreateRow(rowIndex);
                                    row.Height = 300;
                                }
                            }
                            row.CreateCell(0).SetCellValue(Convert.ToInt32(DR["Sl.No"]));
                            row.CreateCell(1).SetCellValue(Convert.ToString(DR["level"]));
                            row.CreateCell(2).SetCellValue(Convert.ToString(DR["Emp. No"]));
                            row.CreateCell(3).SetCellValue(Convert.ToString(DR["Employee Name"]));
                            row.CreateCell(4).SetCellValue(Convert.ToString(DR["Designation"]));
                            row.CreateCell(5).SetCellValue(Convert.ToString(DR["Gender"]));
                            row.CreateCell(6).SetCellValue(Convert.ToString(DR["Employee Category"]));
                            row.CreateCell(7).SetCellValue(Convert.ToString(DR["Date"]));
                            row.CreateCell(8).SetCellValue(Convert.ToString(DR["Daily Minimum Working Hrs"]));
                            row.CreateCell(9).SetCellValue(Convert.ToString(DR["Leave/Permission Hrs"]));
                            row.CreateCell(10).SetCellValue(Convert.ToString(DR["Target Worked Hrs"]));
                            row.CreateCell(11).SetCellValue(Convert.ToString(DR["Actual Worked Hrs"]));
                            row.CreateCell(12).SetCellValue(Convert.ToString(DR["Deviated Hrs"]));
                            Total = Convert.ToString(DR["TotaldeviationHrs"]);
                        }
                        PreviousEmployeeId = OldEmployeeId;
                        rowIndex++;
                    }
                    if (PreviousEmployeeId != "-1")
                    {
                        row = sheet.CreateRow(rowIndex);
                        row.Height = 300;
                        row.CreateCell(11).SetCellValue("Total Deviated Hours");
                        row.GetCell(11).CellStyle = hStyle;
                        row.CreateCell(12).SetCellValue(Total);
                        row.GetCell(12).CellStyle = hStyle;
                        Total = "";
                    }
                    using (MemoryStream exportData = new MemoryStream())
                    {
                        workbook.Write(exportData);
                        string saveAsFileName = string.Format("Daily_Deviation_Report-{0:d}.xls", DateTime.Now).Replace("/", "-");
                        Response.ContentType = "application/vnd.ms-excel";
                        Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                        Response.Clear();
                        Response.BinaryWrite(exportData.GetBuffer());
                        Response.End();
                    }
                }
            }
        }
        catch (Exception ex)
        {
            base.Message(ex.Message);
        }
    }
    #endregion
}
