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 System.IO;
using NPOI.HSSF.Util;
using System.Web;
using NPOI.SS.Util;
public partial class Reports_TA_UI_Report_LeaveApplication : Base.BasePage
{
    #region Variable Declaration
    Cls_CheckUser objCheckUser = new Cls_CheckUser();
    DataSet ds_Checkuser = new DataSet();
    GetReports rptEmp = new GetReports();
    Rpt_Common rptcommon = new Rpt_Common();
    ResourceManager rm;
    LeaveEventData objLeaveEventData = new LeaveEventData();
    cls_Common objCommon = new cls_Common();
    EmployeeEventData objEmployee = new EmployeeEventData();
    YearSettingsEventData objYearSettingsEventData = new YearSettingsEventData();
    string strFromdate, gstrDateFormat, strTodate, strInTime, strOutTime;
    DataSet ds_LeaveType = new DataSet();
    CultureInfo ci;
    int intIndividualUser = 0;
    bool blnNormal;
    string strReportType = string.Empty;
    string dtFrom = "", dtTo = "";
    bool intdata = true;
    #endregion
    #region Page Load event
    /// <summary>
    /// On page loading
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected override void Page_Load(object sender, EventArgs e)
    {
        // session checking
        base.Page_Load(sender, e);
        if (!Page.IsPostBack)
        {
            base.CheckPages(0);
            objCommon.menu("LeaveReports", "35");

        }
        //get the culture info to set the language       
        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_EnterDate", ci) + "','" + rm.GetString("Alert_EnterToDate", 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 + "EnableDateFormat();";
        strSetAlert = strSetAlert + "</script>";
        Page.RegisterStartupScript("SetAlertMessages", strSetAlert);
        //Set UserRights
        UserRights();
        if (!IsPostBack)
        {
            // Bindings the employee category and type.
            BindingEmployeeCategoryandType();
            FillDesignation();
            LoadLeaveType();
            BindFinancialYear();
            if (Request.QueryString["Source"] != null)
            {
                if (Request.QueryString["Source"].ToString() == "Email")
                {
                    trTime.Visible = false;
                    trView.Visible = false;
                    trSave.Visible = true;
                    IDTest1.Visible = false;
                    if (Request.QueryString["ScheduleId"] != null)
                    {
                        DataSet ds = new DataSet();
                        ds = rptEmp.GetEmailSchedulerFilterDetails(Convert.ToInt32(Request.QueryString["ScheduleId"]));
                        if (ds.Tables.Count > 0)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                Session["parentLevelId_modify"] = Convert.ToString(ds.Tables[0].Rows[0]["ParentId"]);
                                txtEmployeeId.Text = Convert.ToString(ds.Tables[0].Rows[0]["empId"]);
                                txtEmployeeName.Text = Convert.ToString(ds.Tables[0].Rows[0]["EmployeeName"]);
                                ddlDesignation.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["DesignationID"]);
                                ddlGender.SelectedItem.Text = Convert.ToString(ds.Tables[0].Rows[0]["Gender"]);
                                ddlEmpCatagory.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["EmployeeCategory"]);
                                ddlEmpType.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["EmployeeType"]);
                                ddlLeaveStatus.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["LeaveStatus"]);
                                Session["EMailParentId"] = Convert.ToString(ds.Tables[0].Rows[0]["ParentID"]);
                                if (Convert.ToString(ds.Tables[1].Rows[0]["ReportType"]) == "1")
                                {
                                    rbtnSummary.Checked = true;
                                    trLeaveType.Style.Add("display", "none");
                                }
                                else
                                {
                                    rbtnDetails.Checked = true;
                                    trLeaveType.Style.Add("display", "");
                                }
                                //   ddlLeaveType.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["LeaveType"]);
                            }
                        }
                    }
                }
                else
                {
                    intdata = true;
                }
            }
            else
            {
                trTime.Visible = true;
                trView.Visible = true;
                trSave.Visible = false;
                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 = false;
            }
            else
            {
                if (Convert.ToString(Session["hdnReportFilters"]) != string.Empty)
                {
                    string strhdnParameters = Convert.ToString(Session["hdnReportFilters"]);
                    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];
                    }
                    if (Convert.ToString(Session["ReportType"]) == "1")
                    {
                        rbtnSummary.Checked = true;
                        trLeaveType.Style.Add("display", "none");
                        txtEmployeeName.Text = strArray1[4].ToString().Replace("~", ",");
                        txtEmployeeId.Text = strArray1[2].ToString().Replace("~", ",");
                        ddlEmpCatagory.SelectedValue = Convert.ToString(strArray1[5]);
                        ddlEmpType.SelectedValue = Convert.ToString(strArray1[6]);
                        ddlDesignation.SelectedValue = Convert.ToString(strArray1[11]);
                        ddlGender.SelectedValue = Convert.ToString(strArray1[12]);
                        ddlLeaveType.SelectedValue = Convert.ToString(strArray1[13]);
                        ddlLeaveStatus.SelectedValue = Convert.ToString(strArray1[14]);
                        Session["EMailParentId"] = Convert.ToString(strArray1[10]);
                    }
                    else
                    {
                        rbtnDetails.Checked = true;
                        trLeaveType.Style.Add("display", "");
                        txtEmployeeName.Text = strArray1[4].ToString().Replace("~", ",");
                        txtEmployeeId.Text = strArray1[2].ToString().Replace("~", ",");
                        ddlEmpCatagory.SelectedValue = Convert.ToString(strArray1[6]);
                        ddlEmpType.SelectedValue = Convert.ToString(strArray1[7]);
                        ddlDesignation.SelectedValue = Convert.ToString(strArray1[12]);
                        ddlGender.SelectedValue = Convert.ToString(strArray1[13]);
                        ddlLeaveType.SelectedValue = Convert.ToString(strArray1[14]);
                        ddlLeaveStatus.SelectedValue = Convert.ToString(strArray1[15]);
                        Session["EMailParentId"] = Convert.ToString(strArray1[11]);
                    }
                }
            }
            Hidden1.Value = Convert.ToString(Session["IDateFormat"]);
            ddlDesignation.PreRender += LB_PreRender;
            txtFromDate.Text = (Convert.ToDateTime(objCommon.CurrentMonthStartDate())).ToString(Session["MDateFormat"].ToString());
            txtToDate.Text = (Convert.ToDateTime(System.DateTime.Now.ToShortDateString())).ToString(Session["MDateFormat"].ToString());
            if (Session["parentLevelId"] != null || Convert.ToInt16(Session["parentLevelId"]) != 0)
            {
                Session["parentLevelId"] = null;
            }
        }
        else
        {
            if (Request.QueryString["Source"] != null)
            {
                if (Request.QueryString["Source"].ToString() == "Email")
                {
                    trTime.Visible = false;
                    trView.Visible = false;
                    trSave.Visible = true;
                }
            }
            else
            {
                trTime.Visible = true;
                trView.Visible = true;
                trSave.Visible = false;
            }
        }
        if (IsPostBack)
        {
            if (rbtnSummary.Checked == true)
            {
                if (intdata = true)
                {
                    trTime.Style.Add("display", "none");
                    trLeaveType.Style.Add("display", "none");
                }
                else
                {
                    trLeaveType.Style.Add("display", "none");
                }
            }
            else
            {
                if (intdata = true)
                {
                    trTime.Style.Add("display", "");
                    trLeaveType.Style.Add("display", "");
                }
                else
                {
                    trLeaveType.Style.Add("display", "");
                }
            }
        }
        //Check whether the employee is Individual User
        intIndividualUser = objCheckUser.CheckIndividualUser(Convert.ToString(Session["UserGroupName"]));
        if (intIndividualUser >= 1)
        {
            PlaceHolderLeaveApplication.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);
        strTodate = txtToDate.Text;
        strTodate = objCommon.DateConvertion(gstrDateFormat, strTodate);
        int leaveSanctionExists = 0;
        leaveSanctionExists = objCommon.CheckPageExistsInLicense(248);
        //if Standar Edition set the visibility to false
        //if (Session["Edition"].ToString() == "1")
        if (leaveSanctionExists == 0)
        {
            lblStatus.Style.Add("display", "none");
            ddlLeaveStatus.Style.Add("display", "none");
        }
        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 Functions
    /// <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>
    /// Display the leave types in DropDownList
    /// </summary>
    public void LoadLeaveType()
    {
        string strusergroupname = string.Empty;
        ds_LeaveType = objLeaveEventData.SelectLeaveTypeDetails(strusergroupname);
        if (ds_LeaveType.Tables[0].Rows.Count > 0)
        {
            ddlLeaveType.DataSource = ds_LeaveType;
            ddlLeaveType.DataTextField = "LeaveType";
            ddlLeaveType.DataValueField = "LeaveType_Id";
            ddlLeaveType.DataBind();
            ListItem liAll = new ListItem();
            liAll.Value = "All";
            liAll.Text = rm.GetString("ExportData_All", ci);
            ddlLeaveType.Items.Insert(0, liAll);
            int indexcount = ds_LeaveType.Tables[0].Rows.Count;
            ListItem liRes = new ListItem();
            liRes.Value = "Restricted Holiday";
            liRes.Text = rm.GetString("Holiday_RestrictedHoliday", ci);
            ddlLeaveType.Items.Insert(indexcount + 1, liRes);
            ListItem liComp = new ListItem();
            liComp.Value = "Comp Off";
            liComp.Text = rm.GetString("Caption_CompOff", ci);
            ddlLeaveType.Items.Insert(indexcount + 2, liComp);
            ListItem liESI = new ListItem();
            liESI.Value = "ESI";
            liESI.Text = rm.GetString("Caption_ESI", ci);
            ddlLeaveType.Items.Insert(indexcount + 3, liESI);
            ListItem liLOP = new ListItem();
            liLOP.Value = "LOP";
            liLOP.Text = rm.GetString("lbl_LOP", ci);
            ddlLeaveType.Items.Insert(indexcount + 4, liLOP);
            foreach (ListItem item in ddlLeaveType.Items)
            {
                item.Attributes.Add("title", item.Text);
            }
            //ddlLeaveType.Items.Insert(0, rm.GetString("ExportData_All", ci));
            //int indexcount = ds_LeaveType.Tables[0].Rows.Count;
            //ddlLeaveType.Items.Insert(indexcount + 1, rm.GetString("Holiday_RestrictedHoliday", ci));
            //ddlLeaveType.Items.Insert(indexcount + 2, rm.GetString("Caption_CompOff", ci));
            //ddlLeaveType.Items.Insert(indexcount + 3, rm.GetString("Caption_ESI", ci));
            //ddlLeaveType.Items.Insert(indexcount + 4, rm.GetString("lbl_LOP", ci));
        }
        else
        {
            ddlLeaveType.DataSource = ds_LeaveType;
            ddlLeaveType.DataBind();
            ListItem liAll = new ListItem();
            liAll.Value = "All";
            liAll.Text = rm.GetString("ExportData_All", ci);
            ddlLeaveType.Items.Insert(0, liAll);
            ListItem liRes = new ListItem();
            liRes.Value = "Restricted Holiday";
            liRes.Text = rm.GetString("Holiday_RestrictedHoliday", ci);
            ddlLeaveType.Items.Insert(1, liRes);
            ListItem liComp = new ListItem();
            liComp.Value = "Comp Off";
            liComp.Text = rm.GetString("Caption_CompOff", ci);
            ddlLeaveType.Items.Insert(2, liComp);
            ListItem liESI = new ListItem();
            liESI.Value = "ESI";
            liESI.Text = rm.GetString("Caption_ESI", ci);
            ddlLeaveType.Items.Insert(3, liESI);
            ListItem liLOP = new ListItem();
            liLOP.Value = "LOP";
            liLOP.Text = rm.GetString("lbl_LOP", ci);
            ddlLeaveType.Items.Insert(4, liLOP);
            //ddlLeaveType.Items.Insert(0, "All");
            //ddlLeaveType.Items.Insert(1, "Restricted Holiday");
            //ddlLeaveType.Items.Insert(2, "Comp Off");
            //ddlLeaveType.Items.Insert(3, "ESI");
            //ddlLeaveType.Items.Insert(4, "LOP");
            //ddlLeaveType.Items.Insert(0, rm.GetString("ExportData_All", ci));
            //ddlLeaveType.Items.Insert(1, rm.GetString("Holiday_RestrictedHoliday", ci));
            //ddlLeaveType.Items.Insert(2, rm.GetString("Caption_CompOff", ci));
            //ddlLeaveType.Items.Insert(3, rm.GetString("Caption_ESI", ci));
            //ddlLeaveType.Items.Insert(4, rm.GetString("lbl_LOP", ci));
        }
    }
    /// <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
            {
                Message(rm.GetString("Alert_SelectvalidFromDate", ci));
                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
            {
                //Message("Select Valid To Date");
                //Message(rm.GetString("Alert_EnterValidToDate", ci));
                //e.IsValid = false;
                e.IsValid = true;
            }
        }
        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
            {
                //Message("Select valid Todate compare with Fromdate");
                Message(rm.GetString("Msg_SelectvaliddatecomparewithFromdate", ci));
                e.IsValid = false;
            }
        }
        catch
        {
            //
        }
    }
    private void BindFinancialYear()
    {
        DataSet ds_YearDetails = new DataSet();
        ds_YearDetails = objYearSettingsEventData.GetAllYearSettings();
        ddlYear.DataSource = ds_YearDetails;
        ddlYear.DataValueField = "YearSettings_Id";
        ddlYear.DataTextField = "YearSetting";
        ddlYear.DataBind();
        ListItem liAll = new ListItem();
        liAll.Value = "0";
        liAll.Text = "All";
        ddlYear.Items.Insert(0, liAll);
        foreach (ListItem item in ddlYear.Items)
        {
            item.Attributes.Add("title", item.Text);
        }
    }
    /// <summary>
    /// Getting Information from resource file and display it in label
    /// </summary>
    /// <param name="ci"></param>
    private void LoadStrings(CultureInfo ci)
    {
        lblYear.Text = rm.GetString("PayrollProcess_Year", ci);
        lblFilter.Text = rm.GetString("lblFilter", ci);
        btn_View.Text = "&nbsp;&nbsp;" + rm.GetString("btn_View", 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);
        lblLeaveType.Text = rm.GetString("lbl_leavetype", ci);
        lblStatus.Text = rm.GetString("email_Status", ci);
        lblLeaveReport.Text = rm.GetString("Reports_Leave_LeaveReport", ci);
        lblEmpSwipe.Text = rm.GetString("TerminalDataType_Card_No", ci);
        ddlLeaveStatus.Items[0].Text = rm.GetString("ExportData_All", ci);
        ddlLeaveStatus.Items[1].Text = rm.GetString("btn_Approved", ci);
        ddlLeaveStatus.Items[2].Text = rm.GetString("btn_Forwarded", ci);
        ddlLeaveStatus.Items[3].Text = rm.GetString("btn_Pending", ci);
        ddlLeaveStatus.Items[4].Text = rm.GetString("btn_Reject", ci);
        reqFromDate.ErrorMessage = rm.GetString("Alert_PleaseselectFromDate", ci);
        reqToDate.ErrorMessage = rm.GetString("Alert_PleaseselectToDate", 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);
        lblReportType.Text = rm.GetString("EmployeeInOutReportType", ci);
        rbtnSummary.Text = rm.GetString("EmployeeInOutSummary", ci);
        rbtnDetails.Text = rm.GetString("EmployeeInOutDetails", ci);
        btnSave.Text = "&nbsp;&nbsp;" + rm.GetString("btn_Save", ci);
        btnBack.Text = "&nbsp;&nbsp;" + rm.GetString("btn_Cancel", ci);
        lblDateFilter.Text = rm.GetString("lbl_DateFilter", ci);
        btn_Export.Text = "&nbsp;&nbsp;" + rm.GetString("btn_ExporttoExcel", ci);
        rbCustom.Text = rm.GetString("rb_Custom", ci);
        rbpreviousMonth.Text = rm.GetString("rb_PreviousMonth", ci);
        rbpreviousweek.Text = rm.GetString("rb_PreviousWeek", ci);
        rbThisMonth.Text = rm.GetString("rb_ThisMonth", ci);
        rbThisweek.Text = rm.GetString("rb_ThisWeek", ci);
    }
    /// <summary>
    /// To check the user rights
    /// </summary>
    private void UserRights()
    {
        ds_Checkuser = objCheckUser.checkRights(35, 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);
        }
    }
    #endregion
    #region Button Events
    /// <summary>
    /// Load the Leave report
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btn_View_Click(object sender, EventArgs e)
    {
        try
        {
            if (Page.IsValid == true)
            {
                if (rbtnSummary.Checked == true)
                {
                    cls_Common objClassCommon = new cls_Common();
                    DataSet dsYear = new DataSet();
                    strReportType = "Summary";
                    //Call to a function to get the Year setting details
                    dsYear = objClassCommon.GetYearsettingForLeaveReport();
                    if (dsYear.Tables.Count > 0)
                    {
                        if (dsYear.Tables[0].Rows.Count > 0)
                        {
                            dtFrom = Convert.ToString(dsYear.Tables[0].Rows[0]["YearSettingFromDate"]);
                            dtTo = Convert.ToString(dsYear.Tables[0].Rows[0]["YearSettingToDate"]);
                        }
                    }
                }
                else
                {
                    //trTime.Visible = true;
                    if (txtFromDate.Text != "")
                        dtFrom = strFromdate;
                    if (txtToDate.Text != "")
                        dtTo = strTodate;
                    strReportType = "Details";
                }
                int shft = 0;
                string Level = string.Empty;
                string employeeName = string.Empty;
                string empName = string.Empty;
                string empId = string.Empty;
                //shft =Convert.ToInt32(ddlShift.SelectedValue);
                if (intIndividualUser >= 1)
                {
                    empName = string.Empty;
                    shft = 0;
                    Level = string.Empty;
                    empId = Convert.ToString(Session["EmployeeCodeId"]);
                }
                else
                {
                    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 (rbtNormalReport.Checked == true)
                {
                    blnNormal = true;
                }
                else
                {
                    blnNormal = false;
                }
                //Redirect the page to the report viewer
                string strScript = "";
                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=edge' />";
                strScript = strScript + "<script language='javascript'>";
                strScript = strScript + "window.open('TA_UI_Report_ReportViewer.aspx?ReportName=Leave&dtFrom=" + dtFrom + "&dtTo=" + dtTo + "&empid=" + empId + "&shft=" + shft + "&EmpName=" + employeeName + "&Level=" + Level + "&EmpCategory=" + Convert.ToInt32(ddlEmpCatagory.SelectedValue) + "&YrSettingID=" + Convert.ToInt32(ddlYear.SelectedValue) + "&EmpType=" + Convert.ToInt32(ddlEmpType.SelectedValue) + "&ParentLevelId=" + Convert.ToInt16(Session["parentLevelId"]).ToString() + "&UserGroupName=" + Convert.ToString(Session["UserGroupName"]) + "&GradeLevel=" + Convert.ToString(Session["GradeLevel"]) + "&Normal=" + blnNormal + "&LeaveType=" + ddlLeaveType.SelectedValue + "&Status=" + ddlLeaveStatus.SelectedValue + "&SwipeCard=" + txtSwipeCard.Text.ToString().Trim().Replace("'", "''") + "&Designation=" + Convert.ToInt32(ddlDesignation.SelectedValue) + "&Gender=" + ddlGender.SelectedValue + "&LeaveReportType=" + strReportType + "','','left=10px, top=60px, width=1000px, height=600px, scrollbars=yes, status=no, resizable=yes');";
                strScript = strScript + "</script>";
                Response.Write(strScript);
            }
        }
        catch (Exception ex)
        {
            Message(ex.ToString());
        }
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
            if (Page.IsValid == true)
            {
                string employeeName = string.Empty;
                string empName = "", empId = "";
                string Level = string.Empty;
                if (intIndividualUser >= 1)
                {
                    empName = string.Empty;
                    Level = string.Empty;
                    empId = Convert.ToString(Session["EmployeeCodeId"]);
                }
                else
                {
                    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"]));
                    if (Level.Contains(","))
                    {
                        Level = Level.Replace(",", "~");
                    }
                    if (employeeName.Contains(","))
                    {
                        employeeName = employeeName.Replace(",", "~");
                    }
                    if (empId.Contains(","))
                    {
                        empId = empId.Replace(",", "~");
                    }
                }
                StringBuilder strFilters = new StringBuilder();
                string ReportType = "";
                if (rbtnDetails.Checked == true)
                {
                    strFilters.Append("@fromDat=" + " Duration_From " + ",");
                    strFilters.Append("@ToDat=" + " Duration_To " + ",");
                    strFilters.Append("@empId=" + empId + ",");
                    strFilters.Append("@shift= 0,");
                    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("@LeaveType=" + ddlLeaveType.SelectedValue + ",");
                    strFilters.Append("@LeaveStatus=" + ddlLeaveStatus.SelectedValue + ",");
                    strFilters.Append("@EmpAutoId=0");
                    ReportType = "0";
                }
                else
                {
                    strFilters.Append("@fromDat=" + " Duration_From " + ",");
                    strFilters.Append("@ToDat=" + " Duration_To " + ",");
                    strFilters.Append("@empId=" + empId + ",");
                    strFilters.Append("@shift= 0,");
                    strFilters.Append("@EmployeeName=" + employeeName + ",");
                    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("@LeaveType=" + ddlLeaveType.SelectedValue + ",");
                    strFilters.Append("@LeaveStatus=" + ddlLeaveStatus.SelectedValue + ",");
                    strFilters.Append("@EmpAutoId=0");
                    ReportType = "1";
                }
                Session["ReportType"] = ReportType;
                Session["hdnReportFilters"] = string.Empty;
                Session["hdnReportFilters"] = strFilters;
                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() + "';window.top.parent.document.getElementById('ctl00_BodyContentPH_hdnReportType').value='" + ReportType + "'}";
                //strScript = strScript + "self.close();";
                strScript = strScript + "parent.$('#myModalReport').modal('hide');";
                strScript = strScript + "</script>";
                Response.Write(strScript);
            }
        }
        catch
        {
            //
        }
    }
    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);
            btnSave.Visible = true;
            btn_View.Visible = false;
            btnBack.Visible = true;
            IDTest1.Visible = false;
        }
    }
    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)
    {
        try
        {
            DataSet dsEmp = new DataSet();
            string strEmpAutoId = "0";
            if (Page.IsValid == true)
            {
                if (Session["ReportsBasedOn"] != null)
                {
                    if (Convert.ToString(Session["ReportsBasedOn"]) == "False")
                    {
                        strEmpAutoId = "0";
                    }
                    else
                    {
                        strEmpAutoId = Convert.ToString(Session["EmpAutoID"]);
                    }
                }
                string dtFrom = "", dtTo = "";
                if (rbtnSummary.Checked == true)
                {
                    cls_Common objClassCommon = new cls_Common();
                    DataSet dsYear = new DataSet();
                    strReportType = "Summary";
                    //Call to a function to get the Year setting details
                    dsYear = objClassCommon.GetYearsettingForLeaveReport();
                    //  dtFrom = Convert.ToDateTime(dsYear.Tables[0].Rows[0]["YearSettingFromDate"]).ToString(Session["MDateFormat"].ToString());
                    //  dtTo = Convert.ToDateTime(dsYear.Tables[0].Rows[0]["YearSettingToDate"]).ToString(Session["MDateFormat"].ToString());
                    dtFrom = Convert.ToString(dsYear.Tables[0].Rows[0]["YearSettingFromDate"]);
                    dtTo = Convert.ToString(dsYear.Tables[0].Rows[0]["YearSettingToDate"]);
                }
                else
                {
                    //trTime.Visible = true;
                    if (txtFromDate.Text != "")
                        dtFrom = strFromdate;
                    if (txtToDate.Text != "")
                        dtTo = strTodate;
                    strReportType = "Details";
                }
                int shft = 0;
                string empName = "", empId = "";
                string employeeName = string.Empty;
                string Level = string.Empty;
                blnNormal = true;
                if (intIndividualUser >= 1)
                {
                    empName = string.Empty;
                    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("'", "''");
                    Level = rptcommon.getNodes(Convert.ToInt16(Session["parentLevelId"]), Convert.ToString(Session["LevelRights"]));
                }

                if (strReportType == "Details")
                {
                    dsEmp = rptEmp.SelectLeave(dtFrom, dtTo, empId, shft, employeeName, Level, ddlLeaveType.SelectedValue, ddlLeaveStatus.SelectedValue, Convert.ToInt16(ddlEmpCatagory.SelectedValue), Convert.ToInt16(ddlEmpType.SelectedValue), Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), txtSwipeCard.Text.ToString().Trim().Replace("'", "''"), Convert.ToInt16(Session["parentLevelId"]), Convert.ToInt16(ddlDesignation.SelectedValue), ddlGender.SelectedValue, Convert.ToInt32(Session["EmpAutoID"]));

                }
                else if (strReportType == "Summary")
                {
                    dsEmp = rptEmp.SelectLeaveSummary(dtFrom, dtTo, empId, shft, employeeName, ddlLeaveType.SelectedValue, ddlLeaveStatus.SelectedValue, Convert.ToInt16(ddlEmpCatagory.SelectedValue), Convert.ToInt16(ddlEmpType.SelectedValue), Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), txtSwipeCard.Text.ToString().Trim().Replace("'", "''"), Convert.ToInt16(Session["parentLevelId"]), Convert.ToInt16(ddlDesignation.SelectedValue), ddlGender.SelectedValue, Convert.ToInt32(Session["EmpAutoID"]), Convert.ToInt32(ddlYear.SelectedValue));

                }
                DataTable dtLevelName = new DataTable();
                if (Convert.ToInt16(Session["parentLevelId"]) != 0)
                {
                    dtLevelName = rptEmp.getHeader(Convert.ToInt16(Session["parentLevelId"]));
                }

                if (dsEmp.Tables["Table"] == null || dsEmp.Tables["Table"].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 Message", strMsg);
                    return;
                }
                else
                {
                    ExportExcel(dsEmp, dtLevelName);
                }
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex);
        }
    }
    #endregion
    #region Message.
    /// <summary>
    /// Messages the specified message content.
    /// </summary>
    /// <param name="messageContent">Content of the message.</param>
    private void Message(string messageContent)
    {
        string strScript = "<script language='javascript' type='text/javascript'>";
        strScript += "alert('" + messageContent + "');";
        strScript += " </script>";
        this.RegisterStartupScript("setScript", strScript);
    }
    /// <summary>
    /// Export to excel Functionality operation
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void ExportExcel(DataSet dsEmp, DataTable dtLevelName)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        string dtFrom = "", dtTo = "";
        if (rbtnSummary.Checked == true)
        {
            cls_Common objClassCommon = new cls_Common();
            DataSet dsYear = new DataSet();
            //Call to a function to get the Year setting details
            dsYear = objClassCommon.GetYearsettingForLeaveReport();
            //  dtFrom = Convert.ToDateTime(dsYear.Tables[0].Rows[0]["YearSettingFromDate"]).ToString(Session["MDateFormat"].ToString());
            //  dtTo = Convert.ToDateTime(dsYear.Tables[0].Rows[0]["YearSettingToDate"]).ToString(Session["MDateFormat"].ToString());
            dtFrom = Convert.ToString(dsYear.Tables[0].Rows[0]["YearSettingFromDate"]);
            dtTo = Convert.ToString(dsYear.Tables[0].Rows[0]["YearSettingToDate"]);
        }


        if (rbtnDetails.Checked == true)
        {
            ISheet sheet = workbook.CreateSheet("Leave Report Detailed");
            //if (rbtnSummary.Checked == true)
            //{
            //  Sheet  sheet = workbook.CreateSheet("Leave Report Summary");
            //}
            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 MainStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            MainStyle.SetFont(hFont);
            MainStyle.Alignment = HorizontalAlignment.Center;

            HSSFFont hFont1 = (HSSFFont)workbook.CreateFont();
            hFont1.Boldweight = (short)FontBoldWeight.Bold;
            hFont1.Color = HSSFColor.Black.Index;
            hFont1.FontHeightInPoints = 10;

            HSSFCellStyle hStyle1 = (HSSFCellStyle)workbook.CreateCellStyle();

            hStyle1.SetFont(hFont1);

            HSSFCellStyle hStyle2 = (HSSFCellStyle)workbook.CreateCellStyle();
            hStyle2.SetFont(hFont);

            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;

            if (dtLevelName.Rows.Count > 0)
            {
                row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 12));
                row.GetCell(0).CellStyle = MainStyle;
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
            }
            row.CreateCell(0).SetCellValue("Leave Report From - " + Convert.ToString(txtFromDate.Text) + " To - " + Convert.ToString(txtToDate.Text));
            row.GetCell(0).CellStyle = MainStyle;
            sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 12));
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            double ValCount = 0.0;
            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("From Date");
            row.CreateCell(6).SetCellValue("To Date");
            row.CreateCell(7).SetCellValue("From Session");
            row.CreateCell(8).SetCellValue("To Session");
            row.CreateCell(9).SetCellValue("No. Of Days");
            row.CreateCell(10).SetCellValue("Leave Type");
            row.CreateCell(11).SetCellValue("Status");
            row.CreateCell(12).SetCellValue("Reason");

            //row.RowStyle = hStyle;
            for (int i = 0; i <= 12; i++)
            {
                row.GetCell(i).CellStyle = hStyle;
            }
            rowIndex++;
            string strPre = string.Empty; int EmployeeDatacount = 1;
            foreach (DataRow DR in dsEmp.Tables[0].Rows)
            {
                row = sheet.CreateRow(rowIndex);

                if (strPre == string.Empty || strPre != Convert.ToString(DR["Employee_Id"]))
                {
                    if (EmployeeDatacount > 1)
                    {
                        row = sheet.CreateRow(rowIndex);
                        row.CreateCell(8).SetCellValue("Sub Total :");
                        row.GetCell(8).CellStyle = hStyle;
                        // TS_Subtotal = TimeSpan.FromMinutes(ValCount);
                        row.CreateCell(9).SetCellValue(Convert.ToString(ValCount));
                        row.GetCell(9).CellStyle = hStyle;
                        ValCount = 0.0;
                        rowIndex++;
                    }
                    row = sheet.CreateRow(rowIndex);
                    row.CreateCell(0).SetCellValue(Convert.ToString(EmployeeDatacount));
                    EmployeeDatacount++;
                }
                else
                {
                    row.CreateCell(0).SetCellValue(string.Empty);
                }
                row.CreateCell(1).SetCellValue(Convert.ToString(DR["Employee_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["fromDate"]));
                row.CreateCell(6).SetCellValue(Convert.ToString(DR["toDate"]));
                row.CreateCell(7).SetCellValue(Convert.ToString(DR["Leave_SessionFrom"]));
                row.CreateCell(8).SetCellValue(Convert.ToString(DR["Leave_SessionTo"]));
                row.CreateCell(9).SetCellValue(Convert.ToString(DR["display"]));
                row.CreateCell(10).SetCellValue(Convert.ToString(DR["leavetype"]));
                row.CreateCell(11).SetCellValue(Convert.ToString(DR["Status"]));
                row.CreateCell(12).SetCellValue(Convert.ToString(DR["Reason"]));
                ValCount = ValCount + Convert.ToDouble(DR["NumberOfDays"]);
                strPre = Convert.ToString(DR["Employee_Id"]);
                for (int i = 0; i <= 12; i++)
                {
                    row.GetCell(i).CellStyle = IStyle;
                    sheet.AutoSizeColumn(i);
                }
                rowIndex++;
            }
            row = sheet.CreateRow(rowIndex);
            row.CreateCell(8).SetCellValue("Sub Total :");
            row.GetCell(8).CellStyle = hStyle;
            // TS_Subtotal = TimeSpan.FromMinutes(ValCount);
            row.CreateCell(9).SetCellValue(Convert.ToString(ValCount));
            row.GetCell(9).CellStyle = hStyle;
            ValCount = 0.0;


            ISheet sheet1 = workbook.CreateSheet("Legends");
            sheet1.IsPrintGridlines = true;

            sheet1.VerticallyCenter = true;
            int rowIndex1 = 0;
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(rowIndex1);

            row1.CreateCell(0).SetCellValue("");
            row1.CreateCell(1).SetCellValue("B- Before Weekly off \\ Holiday");
            sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
            row1.GetCell(1).CellStyle = hStyle;
            rowIndex1++;
            row1 = sheet1.CreateRow(rowIndex1);
            row1.CreateCell(0).SetCellValue("");
            row1.CreateCell(1).SetCellValue("A- After Weekly off \\ Holiday");
            sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
            row1.GetCell(1).CellStyle = hStyle;
            rowIndex1++;
            row1 = sheet1.CreateRow(rowIndex1);
            row1.CreateCell(0).SetCellValue("");
            row1.CreateCell(1).SetCellValue("I-Intervening Weekly off \\ Holiday");
            sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
            row1.GetCell(1).CellStyle = hStyle;
            rowIndex1++;
        }
        else
        {
            ISheet sheet = workbook.CreateSheet("Leave Summary 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 MainStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            MainStyle.SetFont(hFont);
            MainStyle.Alignment = HorizontalAlignment.Center;

            HSSFFont hFont1 = (HSSFFont)workbook.CreateFont();
            hFont1.Boldweight = (short)FontBoldWeight.Bold;
            hFont1.Color = HSSFColor.Black.Index;
            hFont1.FontHeightInPoints = 10;

            HSSFCellStyle hStyle1 = (HSSFCellStyle)workbook.CreateCellStyle();

            hStyle1.SetFont(hFont1);

            HSSFCellStyle hStyle2 = (HSSFCellStyle)workbook.CreateCellStyle();
            hStyle2.SetFont(hFont);

            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;

            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 = MainStyle;
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
            }
            row.CreateCell(0).SetCellValue("Leave Summary Report From - " + (Convert.ToDateTime(dtFrom).ToString(Convert.ToString(Session["MDateFormat"]))) + " To - " + (Convert.ToDateTime(dtTo).ToString(Convert.ToString(Session["MDateFormat"]))));
            row.GetCell(0).CellStyle = MainStyle;
            sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 9));
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            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("Policy");
            row.CreateCell(5).SetCellValue("Leave Type");
            row.CreateCell(6).SetCellValue("Available Days");
            row.CreateCell(7).SetCellValue("Approved");
            row.CreateCell(8).SetCellValue("In Progress");
            row.CreateCell(9).SetCellValue("Remaining");

            //row.RowStyle = hStyle;
            for (int i = 0; i <= 9; i++)
            {
                row.GetCell(i).CellStyle = hStyle;
            }
            rowIndex++;
            string strPre = string.Empty; int EmployeeDatacount = 1;
            foreach (DataRow DR in dsEmp.Tables[0].Rows)
            {
                row = sheet.CreateRow(rowIndex);

                if (strPre == string.Empty || strPre != Convert.ToString(DR["Employee_Code_Id"]))
                {
                    row.CreateCell(0).SetCellValue(Convert.ToString(EmployeeDatacount));
                    EmployeeDatacount++;
                }
                else
                {
                    row.CreateCell(0).SetCellValue(string.Empty);
                }

                row.CreateCell(1).SetCellValue(Convert.ToString(DR["Employee_Code_Id"]));
                row.CreateCell(2).SetCellValue(Convert.ToString(DR["Employee_Name"]));
                row.CreateCell(3).SetCellValue(Convert.ToString(DR["Designation"]));
                row.CreateCell(4).SetCellValue(Convert.ToString(DR["PolicyName"]));
                row.CreateCell(5).SetCellValue(Convert.ToString(DR["LeaveType_Name"]));
                row.CreateCell(6).SetCellValue(Convert.ToString(DR["Leave_Days"]));
                row.CreateCell(7).SetCellValue(Convert.ToString(DR["Leave_Days_Availed_Approved"]));
                row.CreateCell(8).SetCellValue(Convert.ToString(DR["Leave_Days_Availed_InProgress"]));
                row.CreateCell(9).SetCellValue(Convert.ToString(DR["Leave_Days_Remaining"]));
                strPre = Convert.ToString(DR["Employee_Code_Id"]);
                for (int i = 0; i <= 9; i++)
                {
                    row.GetCell(i).CellStyle = IStyle;
                    sheet.AutoSizeColumn(i);
                }
                rowIndex++;
            }
        }


        using (MemoryStream exportData = new MemoryStream())
        {
            workbook.Write(exportData);
            string saveAsFileName;
            if (rbtnDetails.Checked == true)
            {
                saveAsFileName = string.Format("LeaveReport_Detailed-{0:d}.xls", DateTime.Now).Replace("/", "-");
            }
            else
            {
                saveAsFileName = string.Format("LeaveReport_Summary-{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();
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }

    }

    #endregion
}
