using System;
using System.Data;
using System.Globalization;
using System.Resources;
using System.Text;
using System.Threading;
using System.Web.UI;
using System.Web.UI.WebControls;
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_Shift : Base.BasePage
{
    #region Variable Declaration
    Cls_CheckUser objCheckUser = new Cls_CheckUser();
    DataSet ds_Checkuser = new DataSet();
    HeaderInformation hdrTable = new HeaderInformation();
    GetReports rptEmp = new GetReports();
    ResourceManager rm;
    CultureInfo ci;
    cls_Common objCommon = new cls_Common();
    EmployeeEventData objEmployee = new EmployeeEventData();
    string strFromdate, gstrDateFormat;
    Rpt_Common rptcommon = new Rpt_Common();
    int intIndividualUser = 0;
    string strEmpAutoId = "0";
    Boolean blnCheckDataExits;
    DataTable dtPermanentShift = new DataTable();
    #endregion
    #region PageLoad
    protected override void Page_Load(object sender, EventArgs e)
    {
        // session checking
        base.Page_Load(sender, e);
        //get the culture info to set the language        
        if (!Page.IsPostBack)
        {
            base.CheckPages(0);
        }
        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);
        string strSetAlert = "";
        strSetAlert = strSetAlert + "<script language='javascript'>";
        strSetAlert = strSetAlert + "MaxLengthh('" + rm.GetString("Alert_MaximumLengthis", ci) + "');";
        strSetAlert = strSetAlert + "SetAlertMessages('" + rm.GetString("Alert_EnterValidFromDate", ci) + "');";
        strSetAlert = strSetAlert + "SetAlertCheckDate('" + rm.GetString("Alert_Validmonthdatefourdigit", ci) + "','" + rm.GetString("Alert_Validdatemonthfourdigit", ci) + "','" + rm.GetString("Alert_Invalidyearentry", ci) + "','" + rm.GetString("Alert_Invalidmonthentry", ci) + "','" + rm.GetString("Alert_Invaliddateentry", ci) + "','" + rm.GetString("Alert_IncorrectdateformatDDMONYYYY", ci) + "','" + rm.GetString("Alert_Entertime", ci) + "','" + rm.GetString("Alert_Invalidvalueforhours", ci) + "','" + rm.GetString("Alert_Invalidvalueforminites", ci) + "','" + rm.GetString("Alert_Invalidtimeformat1", ci) + "','" + rm.GetString("Alert_PleaseentervalidDateandTime", ci) + "','" + rm.GetString("Alert_ValiddatemonthMMDDYYHHMM", ci) + "','" + rm.GetString("Alert_ValiddatemonthMMDDYYHHMMTT", ci) + "','" + rm.GetString("Alert_PleaseentervalidDateandTime", ci) + "','" + rm.GetString("Alert_Invalidvalueforhours", ci) + "','" + rm.GetString("Alert_Invalidvalueforminites", ci) + "');";
        strSetAlert = strSetAlert + "</script>";
        Page.RegisterStartupScript("SetAlertMessages", strSetAlert);
        //Set UserRights
        UserRights();
        if (!IsPostBack)
        {
            if (Request.QueryString["Source"] != null)
            {
                if (Request.QueryString["Source"].ToString() == "Email")
                {
                    string strScript = "";
                    strScript = strScript + "<script language='javascript'>";
                    strScript = strScript + "document.getElementById('ctl00_TA_uc_MainMenu1_divMenu').style.display = 'none';";
                    strScript = strScript + "document.getElementById('ctl00_TA_uc_MainMenu1_Div1').style.display = 'none';";
                    strScript = strScript + "document.getElementById('ctl00_TA_uc_MainMenu1_Div2').style.display = 'none';";
                    strScript = strScript + "document.getElementById('ctl00_TA_uc_MainMenu1_Div3').style.display = 'none';";
                    //strScript = strScript + "document.getElementById('ctl00_TA_uc_Header1_RptLogoHeader').style.display = 'none';";
                    //strScript = strScript + "document.getElementById('ctl00_BodyContentPH_Modifylevel').style.display = 'none';";
                    //strScript = strScript + "document.getElementById('ctl00_BodyContentPH_TimePeriod').style.display = 'none';";
                    strScript = strScript + "</script>";
                    Page.RegisterStartupScript("Script", strScript);
                    trSave.Visible = true;
                    trView.Visible = false;
                    IDTest1.Visible = false;
                }
            }
            else
            {
                string strScript = "";
                strScript = strScript + "<script language='javascript'>";
                strScript = strScript + "document.getElementById('ctl00_BodyContentPH_Modifylevel').style.display = 'none';";
                strScript = strScript + "</script>";
                Page.RegisterStartupScript("Script", strScript);
                IDTest1.Visible = false;
            }
            if (Request.QueryString["ScheduleId"] != null)
            {
                string strGet = hdnReportFilters.Value;
                string strScript = "<script language='javascript' type='text/javascript'>";
                strScript += "GethiddenValue();";
                strScript += " </script>";
                this.RegisterStartupScript("setScript", strScript);
                IDTest1.Visible = true;
                //string strScript1 = "<script language=\"javascript\">";
                //strScript1 = strScript1 + "{window.opener.parent.document.getElementById('ctl00_BodyContentPH_hdnFilters').value='" + hdnReportFilters.Value + "';}";
                //strScript1 = strScript1 + "self.close();";
                //strScript1 = strScript1 + "</script>";
                ////string str = strScript;
                //Response.Write(strScript1);
                //Page.RegisterStartupScript("GethiddenValue", "<script>GethiddenValue();</script>");
            }
            Hidden1.Value = Convert.ToString(Session["IDateFormat"]);
            Hidden2.Value = Convert.ToString(Session["IDateFormat"]);
            // Bindings the employee category and type.
            BindingEmployeeCategoryandType();
            FillDesignation();
            fillShift();
            ddlDesignation.PreRender += LB_PreRender;
            txtFromDate.Text = (Convert.ToDateTime(objCommon.CurrentMonthStartDate())).ToString(Session["MDateFormat"].ToString());
            // Alert Message 28-1-2011
            if (Session["parentLevelId"] != null || Convert.ToInt16(Session["parentLevelId"]) != 0)
            {
                Session["parentLevelId"] = null;
            }
            objCommon.menu("ShiftReports", "44");
        }
        //Check whether the employee is Individual User
        intIndividualUser = objCheckUser.CheckIndividualUser(Convert.ToString(Session["UserGroupName"]));
        if (intIndividualUser >= 1)
        {
            PlaceHolderShift.Controls.Clear();
            divEmployeeDetails.Visible = false;
            lblEmpSwipe.Visible = false;
            txtSwipeCard.Visible = false;
        }
        //Convert date time to MM/dd/YYYY format
        gstrDateFormat =Convert.ToString(Session["MDateFormat"]);
        strFromdate = txtFromDate.Text;
        strFromdate = objCommon.DateConvertion(gstrDateFormat, strFromdate);
        if (Page.MasterPageFile.ToString() == "TA_Popup_Report_MasterPage.master")
        {
            conHeader.Visible = false;
        }
    }
    protected void Page_PreInit(object sender, EventArgs e)
    {
        if (Request.QueryString["Source"] != null)
            this.Page.MasterPageFile = "TA_Popup_Report_MasterPage.master";
    }
    #endregion
    #region User Defined function
    /// <summary>
    /// Fills the designation in the drop down
    /// </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>
    /// 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();
            foreach (ListItem item in ddlEmpCatagory.Items)
            {
                item.Attributes.Add("title", item.Text);
            }
        }
        // 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();
            foreach (ListItem item in ddlEmpType.Items)
            {
                item.Attributes.Add("title", item.Text);
            }
        }
    }
    // <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"); 
                base.Message(rm.GetString("Alert_EnterValidFromDate", ci));
                e.IsValid = false;
            }
        }
        catch
        {
            //
        }
    }
    /// <summary>
    /// Getting Information from resource file and display it in label
    /// </summary>
    /// <param name="ci"></param>
    private void LoadStrings(CultureInfo ci)
    {
        btn_View.Text = "&nbsp;&nbsp;" + rm.GetString("btn_View", ci);
        lblShiftReport.Text = rm.GetString("Reports_Shift_ShiftReport", ci);
        lblTimePeriod.Text = rm.GetString("ShiftDate", 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);
        lblShift.Text = rm.GetString("Menu_Reports_Shift", ci);
        lblEmpSwipe.Text = rm.GetString("TerminalDataType_Card_No", ci);
        reqFromDate.ErrorMessage = rm.GetString("Alert_PleaseselectFromDate", ci);
        ddlEmpCatagory.Items[0].Text = rm.GetString("Select", ci);
        ddlEmpType.Items[0].Text = rm.GetString("Select", ci);
        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);
        lblFilter.Text = rm.GetString("lblFilter", ci);
        btnSave.Text = "&nbsp;&nbsp;" + rm.GetString("btn_Save", ci);
        btnBack.Text = "&nbsp;&nbsp;" + rm.GetString("btn_Cancel", ci);
        btn_Export.Text = "&nbsp;&nbsp;" + rm.GetString("btn_ExporttoExcel", ci);
        rbReportType.Items[0].Text = rm.GetString("ShiftReportReportType1", ci);
        rbReportType.Items[1].Text = rm.GetString("ShiftReportReportType2", ci);
        lblReportType.Text = rm.GetString("EmployeeInOutReportType", ci);
    }
    /// <summary>
    /// To check rights for the logged in user
    /// </summary>
    private void UserRights()
    {
        ds_Checkuser = objCheckUser.checkRights(44, 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");
        }
    }
    /// <summary>
    /// To apply tooltip for the designation in the drop down
    /// </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);
        }
    }
    /// <summary>
    /// Load Shift Dropdown
    /// </summary>
    public void fillShift()
    {
        ShiftEvenData getShift = new ShiftEvenData();
        DataSet ds = new DataSet();
        string strusergroup = string.Empty;
        //ds = getShift.SelectShift(strusergroup, Convert.ToString(Session["LevelRights"]));
        ds = getShift.SelectShift(Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["LevelRights"]), 0);
        foreach (DataRow drRow in ds.Tables[0].Rows)
        {
            switch (drRow["Shift_Name"].ToString())
            {
                case "No Shift":
                    drRow["Shift_Name"] = rm.GetString("Caption_NoShift", ci);
                    break;
            }
        }
        if (ds.Tables[0].Rows.Count > 0)
        {
            this.ddlShift.DataSource = ds;
            this.ddlShift.DataTextField = "Shift_Name";
            this.ddlShift.DataValueField = "Shift_Id";
            this.ddlShift.DataBind();
        }
        ListItem lstItem = new ListItem();
        lstItem.Value = "0";
        //lstItem.Text = "All";
        lstItem.Text = rm.GetString("ExportData_All", ci);
        ddlShift.Items.Insert(0, lstItem);
        foreach (ListItem item in ddlShift.Items)
        {
            item.Attributes.Add("title", item.Text);
        }
    }
    public void GetData()
    {
        int employeeCategory = 0, employeeType = 0;
        string strSwipeCard = "";
        strSwipeCard = txtSwipeCard.Text.ToString().Trim().Replace("'", "''");
        string dtFrom = "";
        if (txtFromDate.Text != "")
            dtFrom = strFromdate;
        if (Session["ReportsBasedOn"] != null)
        {
            if (Convert.ToString(Session["ReportsBasedOn"]) == "False")
            {
                strEmpAutoId = "0";
            }
            else
            {
                strEmpAutoId = Convert.ToString(Session["EmpAutoID"]);
            }
        }
        // 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;
        }
        int shft = 0;
        shft = Convert.ToInt16(this.ddlShift.SelectedValue);
        string Level = string.Empty;
        string employeeName = string.Empty;
        string empName = string.Empty;
        string empId = string.Empty;
        string EmployeeId = string.Empty;
        if (intIndividualUser >= 1)
        {
            empName = string.Empty;
            Level = string.Empty;
            empId = Convert.ToString(Session["EmployeeCodeId"]);
            EmployeeId = Convert.ToString(Session["EmployeeCodeId"]);
        }
        else
        {
            EmployeeId = txtEmployeeId.Text.Trim();
            empName = txtEmployeeName.Text.ToString();
            empId = txtEmployeeId.Text.Trim().ToString();
            employeeName = this.txtEmployeeName.Text.Replace("'", "''");
            //  employeeName = employeeName.Replace(' ', '%');
            Level = rptcommon.getNodes(Convert.ToInt16(Session["parentLevelId"]), Convert.ToString(Session["LevelRights"]));
        }
        DataSet dsEmployee = new DataSet();
        dsEmployee = rptEmp.SelectEmployeeShiftNotAlloc("", DateTime.Now.ToShortDateString(), empId, 0, employeeName, Level, employeeCategory, employeeType, "Active", Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), strSwipeCard, Convert.ToInt16(Session["parentLevelId"]), Convert.ToInt32(ddlDesignation.SelectedValue), ddlGender.SelectedValue, Convert.ToInt64(strEmpAutoId));
        dsEmployee.Tables[0].DefaultView.RowFilter = "Report_Exclude = False";
        DataTable dtEmployeeFiltered = new DataTable();
        dtEmployeeFiltered = dsEmployee.Tables[0].DefaultView.ToTable();
        dsEmployee.Tables.Clear();
        dsEmployee.Tables.Add(dtEmployeeFiltered);
        string employeeId = string.Empty, notMonth = string.Empty, notYear = string.Empty;
        int ShiftCount = 0;
        DataTable dtShiftNotAllocated = new DataTable();
        dtShiftNotAllocated.TableName = "dtShiftNotAllocated";
        DataColumn dcShiftId = new DataColumn("ShiftId", System.Type.GetType("System.Int32"));
        DataColumn dcEmployeeId = new DataColumn("EmployeeId", System.Type.GetType("System.Int32"));
        DataColumn dcEmployee_Code_Id = new DataColumn("Employee_Code_Id");
        DataColumn dcShift_shortName = new DataColumn("Shift_ShortName");
        DataColumn dcempname = new DataColumn("empname");
        DataColumn dcname = new DataColumn("name");
        DataColumn dcTitleName = new DataColumn("TitleName");
        DataColumn dcGender = new DataColumn("Gender");
        dtShiftNotAllocated.Columns.Add(dcShiftId);
        dtShiftNotAllocated.Columns.Add(dcEmployeeId);
        dtShiftNotAllocated.Columns.Add(dcEmployee_Code_Id);
        dtShiftNotAllocated.Columns.Add(dcShift_shortName);
        dtShiftNotAllocated.Columns.Add(dcempname);
        dtShiftNotAllocated.Columns.Add(dcname);
        dtShiftNotAllocated.Columns.Add(dcTitleName);
        dtShiftNotAllocated.Columns.Add(dcGender);
        DataRow dRowShiftNotAllocated;
        foreach (DataRow drEmpName in dsEmployee.Tables[0].Rows)
        {
            string strDay = "Day" + DateTime.Parse(dtFrom).Day + "Shift";
            ShiftCount = rptEmp.SelectShiftNotAllocatedEmployees(strDay, Convert.ToInt32(drEmpName["empAutoId"]), DateTime.Parse(dtFrom).Year, DateTime.Parse(dtFrom).Month);
            if (ShiftCount == 0)
            {
                dRowShiftNotAllocated = dtShiftNotAllocated.NewRow();
                dRowShiftNotAllocated["ShiftId"] = 0;
                dRowShiftNotAllocated["EmployeeId"] = Convert.ToInt32(drEmpName["empAutoId"]);
                dRowShiftNotAllocated["Employee_Code_Id"] = drEmpName["Employee_Id"];
                dRowShiftNotAllocated["Shift_shortName"] = "Not Allocated";
                dRowShiftNotAllocated["empname"] = drEmpName["empname"];
                dRowShiftNotAllocated["name"] = drEmpName["name"];
                dRowShiftNotAllocated["TitleName"] = drEmpName["TitleName"];
                dRowShiftNotAllocated["Gender"] = drEmpName["Gender"];
                dRowShiftNotAllocated.EndEdit();
                dtShiftNotAllocated.Rows.Add(dRowShiftNotAllocated);
            }
        }
        DataSet dsPermanentShift = new DataSet();
        dsPermanentShift = rptEmp.SelectPermanentShiftReport(dtFrom, Level, empId, employeeName, employeeCategory, employeeType, Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), strSwipeCard, Convert.ToInt16(Session["parentLevelId"]), Convert.ToInt32(ddlDesignation.SelectedValue), ddlGender.SelectedValue, shft, Convert.ToInt64(strEmpAutoId));
        DataSet dsTempShift = new DataSet();
        dsTempShift = rptEmp.SelectTempShiftReport(dtFrom, Level, empId, employeeName, employeeCategory, employeeType, Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), strSwipeCard, Convert.ToInt16(Session["parentLevelId"]), Convert.ToInt32(ddlDesignation.SelectedValue), ddlGender.SelectedValue, shft, Convert.ToInt64(strEmpAutoId));
        DataSet dsHeader = new DataSet();
        DataTable dt = new DataTable();
        if (Convert.ToInt16(Session["parentLevelId"]) != 0)
        {
            dt = rptEmp.getHeader(Convert.ToInt16(Session["parentLevelId"]));
            Session["Ses_rptHeader_Shift"] = dt;
        }
        else
        {
            Session["Ses_rptHeader_Shift"] = dt;
        }

        if ((dsPermanentShift.Tables["Table"] == null || dsPermanentShift.Tables["Table"].Rows.Count == 0) && (dsTempShift != null || dsTempShift.Tables.Count != 0))
        {
            if (dtShiftNotAllocated.Rows.Count == 0)
            {
                blnCheckDataExits = false;
                string strMsg;
                strMsg = "<script language='javascript'>";
                strMsg = strMsg + "alert('" + rm.GetString("Alert_Thereisnorecord", ci) + "');window.close();";
                strMsg = strMsg + "</script>";
                Page.RegisterStartupScript("Display Message", strMsg);
                return;
            }
            else
            {
                if (dsTempShift.Tables.Count > 0 && dsTempShift.Tables[0].Rows.Count > 0)
                {
                    dsPermanentShift = dsTempShift;
                    dtPermanentShift = dsTempShift.Tables[0].Copy();
                }
                else if (dtShiftNotAllocated.Rows.Count == 0)
                {
                    blnCheckDataExits = false;
                    string strMsg;
                    strMsg = "<script language='javascript'>";
                    strMsg = strMsg + "alert('" + rm.GetString("Alert_Thereisnorecord", ci) + "');window.close();";
                    strMsg = strMsg + "</script>";
                    Page.RegisterStartupScript("Display Message", strMsg);
                    return;
                }
            }
        }
        else
        {
            dtPermanentShift = dsPermanentShift.Tables["Table"].Copy();
            foreach (DataRow dRow in dtPermanentShift.Rows)
            {
                if (Convert.ToInt32(dRow["ShiftId"]) == -1)
                {
                    dRow["Shift_ShortName"] = rm.GetString("lbl_Off", ci);
                }
                else if (Convert.ToInt32(dRow["ShiftId"]) == 0)
                {
                    dRow["Shift_ShortName"] = rm.GetString("Report_ShiftNotAllocated", ci);
                }
            }
        }
        //Check the  Temporary Shift Allocation table is Empty
        DataTable dtTempShift = new DataTable();
        if (dsTempShift.Tables["Table"] == null || dsTempShift.Tables["Table"].Rows.Count == 0)
        {
        }
        else
        {
            dtTempShift = dsTempShift.Tables["Table"].Copy();
            foreach (DataRow dRow in dtTempShift.Rows)
            {
                if (Convert.ToInt32(dRow["ShiftId"]) == -1)
                {
                    dRow["Shift_ShortName"] = rm.GetString("lbl_Off", ci);
                }
                else if (Convert.ToInt32(dRow["ShiftId"]) == 0)
                {
                    dRow["Shift_ShortName"] = rm.GetString("Report_ShiftNotAllocated", ci);
                }
            }
        }
        foreach (DataRow dPermanentShiftRow in dtPermanentShift.Rows)
        {
            foreach (DataRow dTempShiftRow in dtTempShift.Rows)
            {
                if (Convert.ToInt32(dTempShiftRow["EmployeeId"]) == Convert.ToInt32(dPermanentShiftRow["EmployeeId"]))
                {
                    if (Convert.ToInt32(dTempShiftRow["ShiftId"]) != 0)
                    {
                        dPermanentShiftRow["Shift_ShortName"] = dTempShiftRow["Shift_ShortName"];
                        dPermanentShiftRow["ShiftId"] = dTempShiftRow["ShiftId"];
                    }
                }
            }
        }
        DataRow dRowPermanentShiftRow;
        foreach (DataRow dTempShiftRow in dtTempShift.Rows)
        {
            dtPermanentShift.DefaultView.RowFilter = "EmployeeId=" + Convert.ToInt32(dTempShiftRow["EmployeeId"]);
            if (dtPermanentShift.DefaultView.Count == 0)
            {
                dRowPermanentShiftRow = dtPermanentShift.NewRow();
                dRowPermanentShiftRow["ShiftId"] = Convert.ToInt32(dTempShiftRow["ShiftId"]);
                dRowPermanentShiftRow["EmployeeId"] = Convert.ToInt32(dTempShiftRow["EmployeeId"]);
                dRowPermanentShiftRow["Employee_Code_Id"] = dTempShiftRow["Employee_Code_Id"];
                dRowPermanentShiftRow["Shift_shortName"] = dTempShiftRow["Shift_shortName"];
                dRowPermanentShiftRow["empname"] = dTempShiftRow["empname"];
                dRowPermanentShiftRow["name"] = dTempShiftRow["name"];
                dRowPermanentShiftRow["TitleName"] = dTempShiftRow["TitleName"];
                dRowPermanentShiftRow["Gender"] = dTempShiftRow["Gender"];
                dRowPermanentShiftRow.EndEdit();
                dtPermanentShift.Rows.Add(dRowPermanentShiftRow);
            }
        }
        //Add the link column
        dtPermanentShift.TableName = "Table";
        if (dtPermanentShift.Rows.Count == 0)
        {
            dtPermanentShift = dtShiftNotAllocated.Copy();
        }
        else
        {
            DataRow dRowPermanentShiftRowNotAllocated;
            foreach (DataRow dShiftNotAllocatedRow in dtShiftNotAllocated.Rows)
            {
                dtPermanentShift.DefaultView.RowFilter = "EmployeeId=" + Convert.ToInt32(dShiftNotAllocatedRow["EmployeeId"]);
                if (dtPermanentShift.DefaultView.Count == 0)
                {
                    dRowPermanentShiftRowNotAllocated = dtPermanentShift.NewRow();
                    dRowPermanentShiftRowNotAllocated["ShiftId"] = 0;
                    dRowPermanentShiftRowNotAllocated["EmployeeId"] = Convert.ToInt32(dShiftNotAllocatedRow["EmployeeId"]);
                    dRowPermanentShiftRowNotAllocated["Employee_Code_Id"] = dShiftNotAllocatedRow["Employee_Code_Id"];
                    dRowPermanentShiftRowNotAllocated["Shift_shortName"] = "Not Allocated";
                    dRowPermanentShiftRowNotAllocated["empname"] = dShiftNotAllocatedRow["empname"];
                    dRowPermanentShiftRowNotAllocated["name"] = dShiftNotAllocatedRow["name"];
                    dRowPermanentShiftRowNotAllocated["TitleName"] = dShiftNotAllocatedRow["TitleName"];
                    dRowPermanentShiftRowNotAllocated["Gender"] = dShiftNotAllocatedRow["Gender"];
                    dRowPermanentShiftRowNotAllocated.EndEdit();
                    dtPermanentShift.Rows.Add(dRowPermanentShiftRowNotAllocated);
                }
            }
        }
    }

    #endregion
    #region Button Events
    /// <summary>
    /// Load the Shift report
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btn_View_Click(object sender, EventArgs e)
    {
        int employeeCategory = 0, employeeType = 0;
        string strSwipeCard = "";
        strSwipeCard = txtSwipeCard.Text.ToString().Trim().Replace("'", "''");
        try
        {
            if (Page.IsValid == true)
            {
                string dtFrom = "";
                if (txtFromDate.Text != "")
                    dtFrom = strFromdate;
                // 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;
                }
                if (Session["ReportsBasedOn"] != null)
                {
                    if (Convert.ToString(Session["ReportsBasedOn"]) == "False")
                    {
                        strEmpAutoId = "0";
                    }
                    else
                    {
                        strEmpAutoId = Convert.ToString(Session["EmpAutoID"]);
                    }
                }
                int shft = 0;
                shft = Convert.ToInt16(this.ddlShift.SelectedValue);
                string Level = string.Empty;
                string employeeName = string.Empty;
                string empName = string.Empty;
                string empId = string.Empty;
                string EmployeeId = string.Empty;
                if (intIndividualUser >= 1)
                {
                    empName = string.Empty;
                    Level = string.Empty;
                    empId = Convert.ToString(Session["EmployeeCodeId"]);
                    EmployeeId = Convert.ToString(Session["EmployeeCodeId"]);
                }
                else
                {
                    EmployeeId = txtEmployeeId.Text.Trim();
                    empName = txtEmployeeName.Text.ToString();
                    empId = txtEmployeeId.Text.Trim().ToString();
                    employeeName = this.txtEmployeeName.Text.Replace("'", "''");
                    //  employeeName = employeeName.Replace(' ', '%');
                    Level = rptcommon.getNodes(Convert.ToInt16(Session["parentLevelId"]), Convert.ToString(Session["LevelRights"]));
                }
                if (rbReportType.Items[0].Selected == true)
                {
                    GetData();
                    //Redirect the page to the report viewer
                    StringBuilder strScript = new StringBuilder();
                    strScript.Append("<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>");
                    strScript.Append("<meta http-equiv='X-UA-Compatible' content='IE=edge' />");
                    strScript.Append("<script language='javascript'>");
                    strScript.Append("window.open('TA_UI_Report_ReportViewer.aspx?ReportName=Shift&dtFrom=" + dtFrom + "&dtTo=" + "" + "&empid=" + empId + "&shft=" + shft + "&EmpName=" + employeeName + "&Level=" + Level + "&EmpCategory=" + employeeCategory + "&EmpType=" + employeeType + "&ParentLevelId=" + Convert.ToInt16(Session["parentLevelId"]).ToString() + "&UserGroupName=" + Convert.ToString(Session["UserGroupName"]) + "&GradeLevel=" + Convert.ToString(Session["GradeLevel"]) + "&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.Append("</script>");
                    Response.Write(strScript);
                }
                else
                {
                    GetNearestShiftDetails(dtFrom, empId, shft, empName, employeeCategory, employeeType, Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), Convert.ToInt16(Session["parentLevelId"]), Convert.ToInt32(ddlDesignation.SelectedValue), Convert.ToString(ddlGender.SelectedValue), Convert.ToInt64(strEmpAutoId));
                }
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
    }
    protected void GetNearestShiftDetails(string dtFromDate, string EmployeeId, int shift, string empName, int employeeCategory, int employeeType, string UserGroupName, string GradeLevel, int ParentId, int DesignationId, string Gender, Int64 EmpAutoID)
    {
        DataSet dsNearShiftDetails = new DataSet();
        dsNearShiftDetails = rptEmp.GetNearestShiftDetails(dtFromDate, EmployeeId, shift, empName, employeeCategory, employeeType, UserGroupName, GradeLevel, ParentId, DesignationId, Gender, EmpAutoID);
        if (dsNearShiftDetails.Tables.Count > 0)
        {
            if (dsNearShiftDetails.Tables[0].Rows.Count == 0)
            {
                string strMsg;
                strMsg = "<script language='javascript'>";
                strMsg = strMsg + "alert('" + rm.GetString("Alert_Thereisnorecord", ci) + "');window.close();";
                strMsg = strMsg + "</script>";
                Page.RegisterStartupScript("Display", strMsg);
                return;
            }
            else
            {
                StringBuilder strScript = new StringBuilder();
                Session["NearestShiftDetails"] = dsNearShiftDetails;
                strScript.Append("<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>");
                strScript.Append("<meta http-equiv='X-UA-Compatible' content='IE=edge' />");
                strScript.Append("<script language='javascript'>");
                strScript.Append("window.open('TA_UI_Report_ReportViewer.aspx?ReportName=NearestShift&dtFrom=" + dtFromDate + "&ParentLevelId=" + Convert.ToInt16(Session["parentLevelId"]).ToString() + "','','left=10px, top=60px, width=1000px, height=600px, scrollbars=yes, status=no, resizable=yes');");
                strScript.Append("</script>");
                Response.Write(strScript);

            }
        }
    }
    protected void IDTest1_Click(object senser, EventArgs e)
    {
        if (hdnReportFilters.Value != "")
        {
            string strhdnParameters = hdnReportFilters.Value;
            string[] strArray = new string[50];
            string[] strArray1 = new string[50];
            strArray = strhdnParameters.Split(',');
            for (int i = 0; i < strArray.Length; i++)
            {
                string[] strTemp = new string[5];
                strTemp = strArray[i].Split('=');
                strArray1[i] = strTemp[1];
            }
            txtEmployeeId.Text = strArray1[2].ToString();
            ddlShift.SelectedValue = strArray1[3].ToString();
            txtEmployeeName.Text = Convert.ToString(strArray1[4].ToString()).Trim().Replace("''", "'");
            txtEmployeeName.Text = Convert.ToString(strArray1[4].ToString()).Trim().Replace("%", " ");
            ddlEmpCatagory.SelectedValue = strArray1[6].ToString();
            ddlEmpType.SelectedValue = strArray1[7].ToString();
            txtSwipeCard.Text = strArray1[10].ToString();
            ucOrganizationLevel.Visible = false;
            Session["parentLevelId_modify"] = hdnCompanyId1.Value;
            ddlDesignation.SelectedValue = strArray1[12].ToString();
            ddlGender.SelectedValue = strArray1[13].ToString();
            string strScript = "";
            strScript = strScript + "<script language='javascript'>";
            strScript = strScript + "document.getElementById('ctl00_TA_uc_MainMenu1_divMenu').style.display = 'none';";
            strScript = strScript + "document.getElementById('ctl00_TA_uc_MainMenu1_Div1').style.display = 'none';";
            strScript = strScript + "document.getElementById('ctl00_TA_uc_MainMenu1_Div2').style.display = 'none';";
            strScript = strScript + "document.getElementById('ctl00_TA_uc_MainMenu1_Div3').style.display = 'none';";
            //strScript = strScript + "document.getElementById('ctl00_TA_uc_Header1_RptLogoHeader').style.display = 'none';";
            //strScript = strScript + "document.getElementById('ctl00_BodyContentPH_TimePeriod').style.display = 'none';";
            strScript = strScript + "</script>";
            Page.RegisterStartupScript("Script", strScript);
            trSave.Visible = true;
            trView.Visible = false;
            IDTest1.Visible = false;
        }
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
            if (Page.IsValid == true)
            {
                string employeeName = string.Empty;
                string empId = string.Empty;
                if (intIndividualUser >= 1)
                {
                    empId = Convert.ToString(Session["EmployeeCodeId"]);
                }
                else
                {
                    empId = txtEmployeeId.Text.Trim().ToString();
                    employeeName = this.txtEmployeeName.Text.Replace("'", "''");
                    employeeName = employeeName.Replace(' ', '%');
                }
                StringBuilder strFilters = new StringBuilder();
                strFilters.Append("@fromDat=" + " Duration_From " + ",");
                strFilters.Append("@ToDat=" + " Duration_To " + ",");
                strFilters.Append("@empId=" + empId + ",");
                strFilters.Append("@shift=" + ddlShift.SelectedValue + ",");
                strFilters.Append("@EmployeeName=" + employeeName + ",");
                strFilters.Append("@LevelID=" + "0,");
                strFilters.Append("@EmployeeCategory=" + ddlEmpCatagory.SelectedValue + ",");
                strFilters.Append("@EmployeeType=" + ddlEmpType.SelectedValue + ",");
                strFilters.Append("@UserGroupName=" + Convert.ToString(Session["UserGroupName"]) + ",");
                strFilters.Append("@GradeLevel=" + Convert.ToString(Session["GradeLevel"]) + ",");
                strFilters.Append("@EmpSwipeCard=" + txtSwipeCard.Text.ToString().Trim().Replace("'", "''") + ",");
                strFilters.Append("@ParentId=" + Convert.ToInt32(Session["parentLevelId"]).ToString() + ",");
                strFilters.Append("@DesignationID=" + ddlDesignation.SelectedValue + ",");
                strFilters.Append("@Gender=" + ddlGender.SelectedValue + ",");
                strFilters.Append("@EmpAutoId=");
                string strScript = "<script language=\"javascript\">";
                strScript = strScript + "{window.top.parent.document.getElementById('ctl00_BodyContentPH_hdnFilters').value='" + strFilters + "'; window.top.parent.document.getElementById('ctl00_BodyContentPH_hdnCompanyId').value='" + Convert.ToInt32(Session["parentLevelId"]).ToString() + "';}";
                //strScript = strScript + "self.close();";
                strScript = strScript + "parent.$('#myModalReport').modal('hide');";
                strScript = strScript + "</script>";
                Response.Write(strScript);
            }
        }
        catch
        {
            //
        }
    }
    protected void btnBack_Click(object sender, EventArgs e)
    {
        string strMsg;
        strMsg = "<script language='javascript'>";
        strMsg = strMsg + "parent.$('#myModalReport').modal('hide');";
        strMsg = strMsg + "</script>";
        Page.RegisterStartupScript("close report window", strMsg);
        return;
    }
    protected void btn_Export_Click(object sender, EventArgs e)
    {
        string strMsg;
        int employeeCategory = 0, employeeType = 0;
        string strSwipeCard = "";
        strSwipeCard = txtSwipeCard.Text.ToString().Trim().Replace("'", "''");
        strMsg = "<script language='javascript'>";
        strMsg = strMsg + "alert('" + rm.GetString("Alert_Thereisnorecord", ci) + "');";
        strMsg = strMsg + "</script>";
        if (rbReportType.Items[0].Selected == true)
        {
            GetData();
            if (dtPermanentShift == null || dtPermanentShift.Rows.Count == 0)
            {
                Page.RegisterStartupScript("Display Message", strMsg);
                return;
            }
            else
            {
                ExportExcel(dtPermanentShift);
            }
        }
        else
        {
            DataSet dsNearShiftDetails = new DataSet();
            string dtFrom = "";
            string Level = string.Empty;
            string employeeName = string.Empty;
            string empName = string.Empty;
            string empId = string.Empty;
            string EmployeeId = string.Empty;
            if (txtFromDate.Text != "")
                dtFrom = strFromdate;

            employeeCategory = Convert.ToInt32(ddlEmpCatagory.SelectedValue) != 0 ? Convert.ToInt32(ddlEmpCatagory.SelectedValue) : 0;
            employeeType = Convert.ToInt32(ddlEmpType.SelectedValue) != 0 ? Convert.ToInt32(ddlEmpType.SelectedValue) : 0;
            if (Session["ReportsBasedOn"] != null)
            {
                if (Convert.ToString(Session["ReportsBasedOn"]) == "False")
                {
                    strEmpAutoId = "0";
                }
                else
                {
                    strEmpAutoId = Convert.ToString(Session["EmpAutoID"]);
                }
            }
            int shft = 0;
            shft = Convert.ToInt16(this.ddlShift.SelectedValue);
            if (intIndividualUser >= 1)
            {
                empName = string.Empty;
                Level = string.Empty;
                empId = Convert.ToString(Session["EmployeeCodeId"]);
                EmployeeId = Convert.ToString(Session["EmployeeCodeId"]);
            }
            else
            {
                EmployeeId = txtEmployeeId.Text.Trim();
                empName = txtEmployeeName.Text.ToString();
                empId = txtEmployeeId.Text.Trim().ToString();
                employeeName = this.txtEmployeeName.Text.Replace("'", "''");
                Level = rptcommon.getNodes(Convert.ToInt16(Session["parentLevelId"]), Convert.ToString(Session["LevelRights"]));
            }
            dsNearShiftDetails = rptEmp.GetNearestShiftDetails(dtFrom, EmployeeId, shft, empName, employeeCategory, employeeType, Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), Convert.ToInt16(Session["parentLevelId"]), Convert.ToInt32(ddlDesignation.SelectedValue), Convert.ToString(ddlGender.SelectedValue), Convert.ToInt64(strEmpAutoId));
            if (dsNearShiftDetails.Tables.Count > 0)
            {
                if (dsNearShiftDetails.Tables[0].Rows.Count == 0)
                {
                    Page.RegisterStartupScript("Display", strMsg);
                    return;
                }
                else
                {
                    ExportExcel(dsNearShiftDetails.Tables[0]);
                }
            }
        }
    }

    #endregion
    #region Export Excel
    protected void ExportExcel(DataTable dsEmp)
    {
        DataTable dtLevelName = new DataTable();
        if (Convert.ToInt16(Session["parentLevelId"]) != 0)
        {
            dtLevelName = rptEmp.getHeader(Convert.ToInt16(Session["parentLevelId"]));
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        ISheet sheet;
        if (rbReportType.Items[0].Selected == true)
            sheet = workbook.CreateSheet("Shift Report");
        else
            sheet = workbook.CreateSheet("Nearest Matching Shift Report");
        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;
        hFont.FontHeightInPoints = 10;
        HSSFCellStyle hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        hStyle.SetFont(hFont);
        hStyle.FillForegroundColor = IndexedColors.White.Index;
        hStyle.FillPattern = FillPattern.SolidForeground;
        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;
        HSSFCellStyle MainHStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        MainHStyle.SetFont(hFont);
        MainHStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        MainHStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        MainHStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        MainHStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        MainHStyle.Alignment = HorizontalAlignment.Center;
        HSSFCellStyle IStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        IStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        IStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        IStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        IStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        string saveAsFileName;
        if (rbReportType.Items[0].Selected == true)
        {
            if (dtLevelName.Rows.Count > 0)
            {
                row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 6));
                row.GetCell(0).CellStyle = MainHStyle;
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
            }
            row.CreateCell(0).SetCellValue("Shift Report for - " + Convert.ToString(txtFromDate.Text));
            sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 6));
            saveAsFileName = string.Format("ShiftReport-{0:d}.xls", DateTime.Now).Replace("/", "-");
        }
        else
        {
            if (dtLevelName.Rows.Count > 0)
            {
                row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 9));
                row.GetCell(0).CellStyle = MainHStyle;
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
            }
            saveAsFileName = string.Format("NearestMatchingShiftReport-{0:d}.xls", DateTime.Now).Replace("/", "-");
            row.CreateCell(0).SetCellValue("Nearest Matching Shift Report for - " + Convert.ToString(txtFromDate.Text));
            sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 9));
        }
        row.GetCell(0).CellStyle = MainHStyle;
        rowIndex++;
        row = sheet.CreateRow(rowIndex);
        rowIndex++;
        row = sheet.CreateRow(rowIndex);
        if (rbReportType.Items[0].Selected == true)
        {
            row.CreateCell(0).SetCellValue("Sl.No");
            row.CreateCell(1).SetCellValue("Employee ID");
            row.CreateCell(2).SetCellValue("Employee Name");
            row.CreateCell(3).SetCellValue("Designation");
            row.CreateCell(4).SetCellValue("Gender");
            row.CreateCell(5).SetCellValue("Shift");
            row.CreateCell(6).SetCellValue("Level");
            //row.RowStyle = hStyle;
            for (int i = 0; i <= 6; i++)
            {
                row.GetCell(i).CellStyle = hStyle;
            }
            rowIndex++;
            string strPre = string.Empty;
            Int32 SlNo = 0;
            foreach (DataRow DR in dsEmp.Rows)
            {
                row = sheet.CreateRow(rowIndex);
                if (strPre == string.Empty || strPre != Convert.ToString(DR["Employee_Code_ID"]))
                {
                    SlNo++;
                    row.CreateCell(0).SetCellValue(SlNo);
                }
                else
                {
                    row.CreateCell(0).SetCellValue("");
                }
                row.CreateCell(1).SetCellValue(Convert.ToString(DR["Employee_Code_ID"]));
                row.CreateCell(2).SetCellValue(Convert.ToString(DR["EmpName"]));
                row.CreateCell(3).SetCellValue(Convert.ToString(DR["TitleName"]));
                row.CreateCell(4).SetCellValue(Convert.ToString(DR["Gender"]));
                row.CreateCell(5).SetCellValue(Convert.ToString(DR["Shift_ShortName"]));
                row.CreateCell(6).SetCellValue(Convert.ToString(DR["name"]));
                strPre = Convert.ToString(DR["Employee_Code_ID"]);
                //Style Added the created Headers.
                for (int i = 0; i <= 6; i++)
                {
                    row.GetCell(i).CellStyle = IStyle;
                    sheet.AutoSizeColumn(i);
                }
                rowIndex++;
            }
        }
        else
        {
            row.CreateCell(0).SetCellValue("Sl.No");
            row.CreateCell(1).SetCellValue("Employee Id");
            row.CreateCell(2).SetCellValue("Employee Name");
            row.CreateCell(3).SetCellValue("Level");
            row.CreateCell(4).SetCellValue("Designation");
            row.CreateCell(5).SetCellValue("Gender");
            row.CreateCell(6).SetCellValue("Date");
            row.CreateCell(7).SetCellValue("Actual Shift In Time");
            row.CreateCell(8).SetCellValue("Shift Start Time");
            row.CreateCell(9).SetCellValue("Nearest Matching Shifts");
            //row.RowStyle = hStyle;
            for (int i = 0; i <= 9; i++)
            {
                row.GetCell(i).CellStyle = hStyle;
            }
            rowIndex++;
            string strPre = string.Empty;
            Int32 SlNo = 0;
            foreach (DataRow DR in dsEmp.Rows)
            {
                row = sheet.CreateRow(rowIndex);
                if (strPre == string.Empty || strPre != Convert.ToString(DR["id"]))
                {
                    SlNo++;
                    row.CreateCell(0).SetCellValue(SlNo);
                }
                else
                {
                    row.CreateCell(0).SetCellValue("");
                }
                row.CreateCell(1).SetCellValue(Convert.ToString(DR["id"]));
                row.CreateCell(2).SetCellValue(Convert.ToString(DR["Name"]));
                row.CreateCell(3).SetCellValue(Convert.ToString(DR["Level"]));
                row.CreateCell(4).SetCellValue(Convert.ToString(DR["Title"]));
                row.CreateCell(5).SetCellValue(Convert.ToString(DR["Gender"]));
                row.CreateCell(6).SetCellValue(Convert.ToString(DR["Date"]));
                row.CreateCell(7).SetCellValue(Convert.ToString(DR["ActualShiftIn"]));
                row.CreateCell(8).SetCellValue(Convert.ToString(DR["AllocatedShift"]));
                row.CreateCell(9).SetCellValue(Convert.ToString(DR["NearestShift"]));
                strPre = Convert.ToString(DR["id"]);
                //Style Added the created Headers.
                for (int i = 0; i <= 9; i++)
                {
                    row.GetCell(i).CellStyle = IStyle;
                    sheet.AutoSizeColumn(i);
                }
                rowIndex++;
            }
        }
        using (MemoryStream exportData = new MemoryStream())
        {
            workbook.Write(exportData);
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
            Response.Clear();
            Response.BinaryWrite(exportData.GetBuffer());
            Response.End();
        }
    }
    #endregion
}
