#region Name Spaces
using System;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Globalization;
using System.Threading;
using System.Resources;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.HSSF.Util;
using System.Web;
using NPOI.SS.Util;
#endregion
public partial class Reports_TA_UI_Report_WeeklyWorkHours : Base.BasePage
{
    #region Variable Declaration
    Cls_CheckUser objCheckUser = new Cls_CheckUser();
    DataSet ds_Checkuser = new DataSet();
    HeaderInformation hdrTable = new HeaderInformation();
    GetReports rptEmp = new GetReports();
    Rpt_Common rptcommon = new Rpt_Common();
    EmployeeEventData objEmployee = new EmployeeEventData();
    cls_Common objCommon = new cls_Common();
    ResourceManager rm;
    CultureInfo ci;
    string dtFrom = "", dtTo = "", EmployeeId = "", gstrDateFormat, strFromdate, strTodate;
    int intIndividualUser = 0;
    string gStrLevel = string.Empty, ParenetLevelid = string.Empty;
    double totalLateIn, TotalEarlyExit, TotMins = 0.0;
    TimeSpan result;
    string cumulateHrs;
    #endregion
    #region Page Load
    protected override void Page_Load(object sender, EventArgs e)
    {
        // session checking
        base.Page_Load(sender, e);
        if (!Page.IsPostBack)
        {
            objCommon.menu("Shift", "160");
            Hidden1.Value = Session["IDateFormat"].ToString();
            base.CheckPages(0);
        }
        //get the culture info to set the language        
        string source_Language;
        source_Language = Session["Session_ActiveLanguage"].ToString();
        rm = new ResourceManager(source_Language, System.Reflection.Assembly.Load("App_GlobalResources"));
        ci = Thread.CurrentThread.CurrentCulture;
        site.SiteMapProvider = Convert.ToString(Session["SiteMapProvider"]);
        LoadStrings(ci);
        //Set UserRights
        UserRights();
        if (!IsPostBack)
        {
            // Bindings the employee category and type.
            BindingEmployeeCategoryandType();
            FillDesignation();
            string strDayName = Convert.ToString(DateTime.Now.Date.DayOfWeek).ToLower();
            double iFromDate = strDayName.Contains("monday") ? 0 : strDayName.Contains("tuesday") ? -1 : strDayName.Contains("wednesday") ? -2 : strDayName.Contains("thursday") ? -3 : strDayName.Contains("friday") ? -4 : -5;
            txtFromDate.Text = (Convert.ToDateTime(objCommon.CurrentMonthStartDate())).ToString(Session["MDateFormat"].ToString());
            txtToDate.Text = (Convert.ToDateTime(System.DateTime.Now.ToShortDateString())).ToString(Session["MDateFormat"].ToString());
            fillShift();
            if (Request.QueryString["Source"] != null)
            {
                if (Request.QueryString["Source"].ToString() == "Email")
                {
                    divdateTime.Visible = false;
                    trView.Visible = false;
                    trSave.Visible = true;
                    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)
                            {
                                txtEmployeeName.Text = Convert.ToString(ds.Tables[0].Rows[0]["EmployeeName"]);
                                ddlEmpCatagory.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["EmployeeCategory"]);
                                txtEmployeeId.Text = Convert.ToString(ds.Tables[0].Rows[0]["EmployeeID"]);
                                ddlEmpType.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["EmployeeType"]);
                                Session["EMailParentId"] = Convert.ToString(ds.Tables[0].Rows[0]["ParentID"]);
                                ddlGender.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["Gender"]);
                                ddlDesignation.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["DesignationID"]);
                                ddlShift.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["shift"]);
                                if (Convert.ToString(ds.Tables[0].Rows[0]["ReportType"]) == "1")
                                {
                                    rbtnDetailedReport.Checked = false;
                                    rbtnSummaryReport.Checked = true;
                                }
                                else
                                {
                                    rbtnSummaryReport.Checked = false;
                                    rbtnDetailedReport.Checked = true;
                                }
                            }
                        }
                    }
                    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];
                            }
                            txtEmployeeId.Text = strArray1[2].ToString().Replace("~", ",");
                            txtEmployeeName.Text = strArray1[3].ToString().Replace("~", ",");
                            ddlEmpCatagory.SelectedValue = strArray1[5].ToString();
                            ddlEmpType.SelectedValue = strArray1[6].ToString();
                            ddlShift.SelectedValue = strArray1[12].ToString();
                            ddlDesignation.SelectedValue = strArray1[8].ToString();
                            ddlGender.SelectedValue = strArray1[9].ToString();
                            Session["EMailParentId"] = Convert.ToString(strArray1[11]);
                            if (Convert.ToString(strArray1[14]) == "1")
                            {
                                rbtnDetailedReport.Checked = false;
                                rbtnSummaryReport.Checked = true;
                            }
                            else
                            {
                                rbtnSummaryReport.Checked = false;
                                rbtnDetailedReport.Checked = true;
                            }
                        }
                    }
                }
            }
            else
            {
                divdateTime.Visible = true;
                trView.Visible = true;
                trSave.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);
            }
        }
        //Check whether the employee is Individual User
        intIndividualUser = objCheckUser.CheckIndividualUser(Session["UserGroupName"].ToString());
        if (intIndividualUser >= 1)
        {
            PlaceHolderCumulative.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);

        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 Button Event
    /// <summary>
    /// Load Late Entry report
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btn_View_Click(object sender, EventArgs e)
    {
        int EmpCategory = 0, EmpType = 0;
        string Level = string.Empty, EmpName = string.Empty, EmpId = string.Empty, SwipeCard = string.Empty;
        try
        {
            if (Page.IsValid == true)
            {
                // set employee category and type
                if (Convert.ToInt32(ddlEmpCatagory.SelectedValue) != 0)
                    EmpCategory = Convert.ToInt32(ddlEmpCatagory.SelectedValue);
                if (Convert.ToInt32(ddlEmpType.SelectedValue) != 0)
                    EmpType = Convert.ToInt32(ddlEmpType.SelectedValue);
                if (txtFromDate.Text != "")
                    dtFrom = strFromdate;
                if (txtToDate.Text != "")
                    dtTo = strTodate;
                //Get the Report source from the Server and load this into the report table\
                DataSet dsWeeklyHrs = new DataSet();
                if (intIndividualUser >= 1)
                {
                    EmpId = Convert.ToString(Session["EmployeeCodeId"]);
                    EmployeeId = Convert.ToString(Session["EmployeeCodeId"]);
                }
                else
                {
                    EmpId = txtEmployeeId.Text.Trim().ToString();
                    EmployeeId = txtEmployeeId.Text.Trim().ToString();
                    EmpName = txtEmployeeName.Text.Replace("'", "''").Replace(' ', '%');
                    SwipeCard = txtSwipeCard.Text.Trim().ToString().Replace("'", "''");
                    Level = rptcommon.getNodes(Convert.ToInt16(Session["parentLevelId"]), Convert.ToString(Session["LevelRights"]));
                    gStrLevel = Level;
                }
                int shft = 0;
                shft = Convert.ToInt16(this.ddlShift.SelectedValue);
                Session["LateInsLevel"] = Level;
                string strEmpAutoId = string.Empty;
                if (Session["ReportsBasedOn"] != null)
                {
                    if (Session["ReportsBasedOn"].ToString() == "False")
                    {
                        strEmpAutoId = "0";
                    }
                    else
                    {
                        strEmpAutoId = Session["EmpAutoID"].ToString();
                    }
                }
                string strReportType = rbtnSummaryReport.Checked ? "SummaryReport" : "DetailedReport";
                string strReportName = "WeeklyWorkHours";
                string strEmpCategory = EmpCategory.ToString();
                string strEmpType = EmpType.ToString();
                string strStatus = string.Empty;
                string strScript = string.Empty;
                //if (Convert.ToInt16(Session["parentLevelId"]) == 0)
                //{
                //    strScript = strScript + "<script language='javascript'>";
                //    strStatus = rm.GetString("Alert_SelectCompany", ci);
                //    strScript = strScript + "alert('" + strStatus + "');";
                //    strScript = strScript + "</script>";
                //    Page.RegisterStartupScript("AlertManualApproval", strScript);
                //    return;
                //}
                //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=document.documentMode;' />";
                strScript = strScript + "<script language='javascript'>";
                strScript = strScript + "window.open('TA_UI_Report_ReportViewer.aspx?ReportName=" + strReportName + "&dtFrom=" + dtFrom + "&dtTo=" + dtTo + "&empid=" + EmpId + "&EmpName=" + EmpName + "&Level=" + Level + "&EmpCategory=" + strEmpCategory + "&EmpType=" + strEmpType + "&ParentLevelId=" + Convert.ToInt16(Session["parentLevelId"]) + "&SwipeCard=" + SwipeCard + "&shft=" + shft + "&ReportType=" + strReportType + "&Designation=" + Convert.ToInt32(ddlDesignation.SelectedValue) + "&Gender=" + ddlGender.SelectedValue + "','','left=10px, top=60px, width=1000px, height=600px, scrollbars=yes, status=no, resizable=yes');";
                strScript = strScript + "</script>";
                Response.Write(strScript);
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }
    }
    protected void btn_Export_Click(object sender, EventArgs e)
    {
        DataSet dsEmp = new DataSet();
        int EmpCategory = 0, EmpType = 0;
        string Level = string.Empty, EmpName = string.Empty, EmpId = string.Empty, SwipeCard = string.Empty;
        try
        {
            if (Page.IsValid == true)
            {
                // set employee category and type
                if (Convert.ToInt32(ddlEmpCatagory.SelectedValue) != 0)
                    EmpCategory = Convert.ToInt32(ddlEmpCatagory.SelectedValue);
                if (Convert.ToInt32(ddlEmpType.SelectedValue) != 0)
                    EmpType = Convert.ToInt32(ddlEmpType.SelectedValue);
                if (txtFromDate.Text != "")
                    dtFrom = strFromdate;
                if (txtToDate.Text != "")
                    dtTo = strTodate;
                //Get the Report source from the Server and load this into the report table\
                DataSet dsWeeklyHrs = new DataSet();
                if (intIndividualUser >= 1)
                {
                    EmpId = Convert.ToString(Session["EmployeeCodeId"]);
                    EmployeeId = Convert.ToString(Session["EmployeeCodeId"]);
                }
                else
                {
                    EmpId = txtEmployeeId.Text.Trim().ToString();
                    EmployeeId = txtEmployeeId.Text.Trim().ToString();
                    EmpName = txtEmployeeName.Text.Replace("'", "''").Replace(' ', '%');
                    SwipeCard = txtSwipeCard.Text.Trim().ToString().Replace("'", "''");
                    Level = rptcommon.getNodes(Convert.ToInt16(Session["parentLevelId"]), Convert.ToString(Session["LevelRights"]));
                    gStrLevel = Level;
                }
                int shft = 0;
                shft = Convert.ToInt16(this.ddlShift.SelectedValue);
                Session["LateInsLevel"] = Level;
                string strEmpAutoId = string.Empty;
                if (Session["ReportsBasedOn"] != null)
                {
                    if (Session["ReportsBasedOn"].ToString() == "False")
                    {
                        strEmpAutoId = "0";
                    }
                    else
                    {
                        strEmpAutoId = Session["EmpAutoID"].ToString();
                    }
                }
                string strReportType = rbtnSummaryReport.Checked ? "SummaryReport" : "DetailedReport";
                string strReportName = "WeeklyWorkHours";
                string strEmpCategory = EmpCategory.ToString();
                string strEmpType = EmpType.ToString();
                string strStatus = string.Empty;
                string strScript = string.Empty;
                dsEmp = rptEmp.SelectWeeklyWorkHours(EmployeeId, Level, dtFrom, dtTo, EmpName, Convert.ToInt16(ddlEmpCatagory.SelectedValue), Convert.ToInt16(ddlEmpType.SelectedValue), Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), SwipeCard, Convert.ToInt16(Session["parentLevelId"]), Convert.ToInt32(ddlShift.SelectedValue), Convert.ToInt16(ddlDesignation.SelectedValue), ddlGender.SelectedValue, Convert.ToInt32(Session["EmpAutoID"]));
                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.ToString());
        }
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
            string strSwipeCard = "";
            if (Page.IsValid == true)
            {
                string dtFrom = "", dtTo = "";
                if (txtFromDate.Text != "")
                    dtFrom = strFromdate;
                if (txtToDate.Text != "")
                    dtTo = strTodate;
                string Level = string.Empty;
                string employeeName = string.Empty;
                string empName = string.Empty;
                string empId = string.Empty;
                int shift;
                shift = Convert.ToInt16(this.ddlShift.SelectedValue);
                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();
                strFilters.Append("@FromDate=" + " Duration_From " + ",");
                strFilters.Append("@ToDate=" + " Duration_To " + ",");
                strFilters.Append("@EmployeeId=" + empId + ",");
                strFilters.Append("@EmployeeName=" + employeeName + ",");
                strFilters.Append("@LevelID=" + Level + ",");
                strFilters.Append("@employeeCategory=" + Convert.ToInt32(ddlEmpCatagory.SelectedValue) + ",");
                strFilters.Append("@employeeType=" + Convert.ToInt32(ddlEmpType.SelectedValue) + ",");
                strFilters.Append("@UserGroupName=" + Convert.ToString(Session["UserGroupName"]) + ",");
                strFilters.Append("@DesignationID=" + ddlDesignation.SelectedValue + ",");
                strFilters.Append("@Gender=" + ddlGender.SelectedValue + ",");
                strFilters.Append("@GradeLevel=" + Convert.ToString(Session["GradeLevel"]) + ",");
                strFilters.Append("@ParentId=" + Convert.ToInt32(Session["parentLevelId"]) + ",");
                strFilters.Append("@shift=" + shift + ",");
                strFilters.Append("@EmpSwipeCard=" + strSwipeCard + ",");
                int reporttype = rbtnSummaryReport.Checked == true ? 1 : 0;
                strFilters.Append("@ReportType=" + reporttype + ",");
                strFilters.Append("@EmpAutoId=0");
                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 + "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;
    }
    #endregion
    #region User Define Function
    /// <summary>
    /// Validate the FromDate
    /// </summary>
    /// <param name="s"></param>
    /// <param name="e"></param>
    public void ValidateFromDate(object s, ServerValidateEventArgs e)
    {
        e.IsValid = false;
        try
        {
            DateTime dtCurrentDate = Convert.ToDateTime(DateTime.Today.Date);
            DateTime dtFromDate = DateTime.Parse(strFromdate);
            TimeSpan diff = dtFromDate.Subtract(dtCurrentDate);
            int day = diff.Days;
            e.IsValid = (day <= 0) && (dtFromDate.ToLongDateString().Split(',')[0].Equals("Monday"));
            if (!e.IsValid)
                base.Message((day > 0) ? "Enter Valid From Date" : "Select Monday in From Date");
        }
        catch
        {
            base.Message("Enter Valid From Date");
        }
    }
    /// <summary>
    /// Validate the ToDate
    /// </summary>
    /// <param name="s"></param>
    /// <param name="e"></param>
    public void ValidateToDate(object s, ServerValidateEventArgs e)
    {
        e.IsValid = false;
        try
        {
            DateTime dtCurrentDate = Convert.ToDateTime(DateTime.Today.Date);
            DateTime dtToDate = DateTime.Parse(strTodate);
            TimeSpan diff = dtToDate.Subtract(dtCurrentDate);
            int day = diff.Days;
            e.IsValid = (dtToDate.ToLongDateString().Split(',')[0].Equals("Saturday"));
            if (!e.IsValid)
                base.Message((day > 0) ? "Enter Valid To Date" : "Select Saturday in To Date");
        }
        catch
        {
            base.Message("Enter Valid To Date");
        }
    }
    /// <summary>
    /// Compare ToDate with FromDate
    /// </summary>
    /// <param name="s"></param>
    /// <param name="e"></param>
    public void CompareDate(object s, ServerValidateEventArgs e)
    {
        try
        {
            strFromdate = txtFromDate.Text;
            strTodate = txtToDate.Text;
            gstrDateFormat = Convert.ToString(Session["MDateFormat"]);
            strTodate = objCommon.DateConvertion(gstrDateFormat, strTodate);
            strFromdate = objCommon.DateConvertion(gstrDateFormat, strFromdate);
            DateTime dtToDate = DateTime.Parse(strTodate);
            DateTime dtFromDate = DateTime.Parse(strFromdate);
            TimeSpan diff = dtToDate.Subtract(dtFromDate);
            int day = diff.Days;
            if (day >= 0)
                e.IsValid = true;
            else
            {
                base.Message(rm.GetString("Msg_SelectvaliddatecomparewithFromdate", ci));
                e.IsValid = false;
            }
        }
        catch
        {            //
        }
    }
    //Getting Information from resource file and display it in label
    /// <summary>
    /// Bindings the employee category and type.
    /// </summary>
    private void BindingEmployeeCategoryandType()
    {
        // employee category binding
        DataSet dsCategory = new DataSet();
        dsCategory = objEmployee.SelectCategoryDetailsEmployee("");
        if (dsCategory != null)
        {
            if (dsCategory.Tables[0].Rows.Count > 0)
            {
                ddlEmpCatagory.DataSource = dsCategory;
                ddlEmpCatagory.DataTextField = "CategoryName";
                ddlEmpCatagory.DataValueField = "CategoryId";
                ddlEmpCatagory.DataBind();
            }
            dsCategory.Dispose();
        }
        // employee type binding
        DataSet dsType = new DataSet();
        dsType = objEmployee.SelectTypeDetailsEmployee("");
        if (dsType != null)
        {
            if (dsType.Tables[0].Rows.Count > 0)
            {
                ddlEmpType.DataSource = dsType;
                ddlEmpType.DataTextField = "TypeName";
                ddlEmpType.DataValueField = "TypeId";
                ddlEmpType.DataBind();
            }
            dsType.Dispose();
        }
    }
    public void fillShift()
    {
        ShiftEvenData getShift = new ShiftEvenData();
        DataSet ds = new DataSet();
        string strusergroup = string.Empty;
        //ds = getShift.SelectShift(strusergroup, Convert.ToString(Session["LevelRights"]), 0);
        ds = getShift.SelectShift(Session["UserGroupName"].ToString(), Convert.ToString(Session["LevelRights"]), 0);
        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 = rm.GetString("ExportData_All", ci);
        ddlShift.Items.Insert(0, lstItem);
    }
    private void LoadStrings(CultureInfo ci)
    {
        btn_View.Text = "&nbsp;&nbsp;" + rm.GetString("btn_View", ci);
        lblWeeklyWorkHours.Text = rm.GetString("Menu_Reports_WeeklyWorkHoursLabel", ci);
        lblShift.Text = rm.GetString("Menu_Reports_Shift", 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);
        lblEmpSwipe.Text = rm.GetString("TerminalDataType_Card_No", ci);
        lblFilter.Text = rm.GetString("lblFilter", 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);
        btnSave.Text = "&nbsp;&nbsp;" + rm.GetString("btn_Save", ci);
        btnBack.Text = "&nbsp;&nbsp;" + rm.GetString("btn_Cancel", ci);
        lblDesignation.Text = rm.GetString("EmployeeMaster_Designation", ci);
        rbtnSummaryReport.Text = rm.GetString("EmployeeInOutSummary", ci);
        rbtnDetailedReport.Text = rm.GetString("EmployeeInOutDetails", ci);
        lblReportType.Text = rm.GetString("EmployeeInOutReportType", ci);
        lblGender.Text = rm.GetString("EmployeeMaster_Gender", ci);
        ddlEmpCatagory.Items[0].Text = rm.GetString("Select", ci);
        ddlEmpType.Items[0].Text = rm.GetString("Select", ci);
        ddlGender.Items[0].Text = rm.GetString("Caption_Both", ci);
        ddlGender.Items[1].Text = rm.GetString("rbtn_EmployeeMaster_Male", ci);
        ddlGender.Items[2].Text = rm.GetString("rbtn_EmployeeMaster_Female", ci);
        ddlDesignation.Items[0].Text = rm.GetString("ExportData_All", ci);
        
        
    }
    private void UserRights()
    {
        ds_Checkuser = objCheckUser.checkRights(160, 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>
    /// 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>
    /// 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();
        ISheet sheet = workbook.CreateSheet("Cumulative Minimum Work Week Hours 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;
        HSSFFont IFont = (HSSFFont)workbook.CreateFont();
        IFont.Boldweight = (short)FontBoldWeight.Bold;
        IFont.Color = HSSFColor.Black.Index;
        IFont.FontHeightInPoints = 10;
        HSSFCellStyle IhStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        IhStyle.SetFont(IFont);
        IhStyle.FillForegroundColor = IndexedColors.White.Index;
        IhStyle.FillPattern = FillPattern.SolidForeground;
        IhStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        IhStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        IhStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        IhStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        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;
        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;
        string NewTimeFormat = string.Empty;
        string TimeFormat = Convert.ToString(Session["ITimeFormat"]);
        if (TimeFormat == "HH:mm")
        {
            NewTimeFormat = "HH:mm";
        }
        else
        {
            NewTimeFormat = "hh:mm tt";
        }
        if (dtLevelName.Rows.Count > 0)
        {
            row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
            if (rbtnSummaryReport.Checked == true)
            {
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 6));
            }
            else
            {
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 10));
            }
            row.GetCell(0).CellStyle = MainStyle;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
        }

        row.CreateCell(0).SetCellValue("Cumulative Minimum Work Week Hours Report From - " + Convert.ToString(txtFromDate.Text) + " To - " + Convert.ToString(txtToDate.Text));
        row.GetCell(0).CellStyle = MainStyle;
        if (rbtnSummaryReport.Checked == true)
        {
            sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 6));
        }
        else
        {
            sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 10));
        }
        rowIndex++;

        row = sheet.CreateRow(rowIndex);
        rowIndex++;
        row = sheet.CreateRow(rowIndex);
        if (rbtnSummaryReport.Checked == true)
        {
            row.CreateCell(0).SetCellValue("Sl.No");
            row.CreateCell(1).SetCellValue("Emp. No.");
            row.CreateCell(2).SetCellValue("Employee Name");
            row.CreateCell(3).SetCellValue("Employee Category");
            row.CreateCell(4).SetCellValue("Minimum Mandatory Cumulative Work Hours Per Week");
            row.CreateCell(5).SetCellValue("Holiday/Leave Hours");
            row.CreateCell(6).SetCellValue("Actual Worked Hours");
            for (int i = 0; i <= 6; i++)
            {
                row.GetCell(i).CellStyle = hStyle;
            }
            rowIndex++;
            string strPre = string.Empty; int EmployeeDatacount = 1;
            foreach (DataRow DR in dsEmp.Tables[1].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["EmployeeName"]));
                row.CreateCell(3).SetCellValue(Convert.ToString(DR["CategoryName"]));
                row.CreateCell(4).SetCellValue(Convert.ToString(DR["DefinedWorkHours"]) + '0');
                Int64 TotalMintToDeduct = Convert.ToInt64(DR["HoursToDeduct"]);
                string TotalHoursToDeduct = (1 == Convert.ToString(TotalMintToDeduct / 60).Length ? "0" + Convert.ToString(TotalMintToDeduct / 60) : Convert.ToString(TotalMintToDeduct / 60)) + ":" + (1 == Convert.ToString(TotalMintToDeduct % 60).Length ? "0" +
             Convert.ToString(TotalMintToDeduct % 60) : Convert.ToString(TotalMintToDeduct % 60));
                row.CreateCell(5).SetCellValue(Convert.ToString(TotalHoursToDeduct));
                row.CreateCell(6).SetCellValue(Convert.ToString(DR["ActualWorkHours"]));
                strPre = Convert.ToString(DR["Employee_Code_Id"]);
                for (int i = 0; i <= 6; i++)
                {
                    row.GetCell(i).CellStyle = IStyle;
                    sheet.AutoSizeColumn(i);
                }
                rowIndex++;
            }
         
        }
        else
        {
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            row.CreateCell(0).SetCellValue("Sl.No");
            row.CreateCell(1).SetCellValue("Employee ID");
            row.CreateCell(2).SetCellValue("");
            row.CreateCell(3).SetCellValue("Employee Name");
            row.CreateCell(4).SetCellValue("");
            row.CreateCell(5).SetCellValue("Employee Category");
            row.CreateCell(6).SetCellValue("");
            row.CreateCell(7).SetCellValue("Holiday / Leave Hours");
            row.CreateCell(8).SetCellValue("");
            row.CreateCell(9).SetCellValue("Total Worked Hours");
            row.CreateCell(10).SetCellValue("");
            //row.RowStyle = hStyle;
            
            for (int i = 0; i <= 10; i++)
            {
                row.GetCell(i).CellStyle = hStyle;
            }
            sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2));
            sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 4));
            sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 5, 6));
            sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 7, 8));
            sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 9, 10));
            rowIndex++;
            string strPre = string.Empty;
            Int32 SlNo = 0;
            foreach (DataRow DR in dsEmp.Tables[1].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("");
                row.CreateCell(3).SetCellValue(Convert.ToString(DR["EmployeeName"]));
                row.CreateCell(4).SetCellValue("");
                row.CreateCell(5).SetCellValue(Convert.ToString(DR["CategoryName"]));
                row.CreateCell(6).SetCellValue("");
                result = TimeSpan.FromMinutes(Convert.ToDouble(DR["HoursToDeduct"]));
                row.CreateCell(7).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                row.CreateCell(8).SetCellValue("");
                row.CreateCell(9).SetCellValue(Convert.ToString(DR["ActualWorkHours"]));
                row.CreateCell(10).SetCellValue("");
                strPre = Convert.ToString(DR["Employee_Code_ID"]);
                ////Style Added the created Headers.
                //for (int i = 0; i <= 5; i++)
                //{
                //    row.GetCell(i).CellStyle = IStyle;
                //    sheet.AutoSizeColumn(i);
                //}
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 4));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 5, 6));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 7, 8));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 9, 10));
                rowIndex++;
                DataRow[] dr = dsEmp.Tables[0].Select("Employee_Code_ID = '" + strPre + "'");
                if (dr.Length > 0)
                {
                    int slno = 0;
                    row = sheet.CreateRow(rowIndex);
                    row.CreateCell(0).SetCellValue("");
                    row.CreateCell(1).SetCellValue("Sl.No");
                    row.CreateCell(2).SetCellValue("Date");
                    row.CreateCell(3).SetCellValue("Day");
                    row.CreateCell(4).SetCellValue("In Time");
                    row.CreateCell(5).SetCellValue("Out Time");
                    row.CreateCell(6).SetCellValue("Late In");
                    row.CreateCell(7).SetCellValue("Early Out");
                    row.CreateCell(8).SetCellValue("Working Hours");
                    rowIndex++;
                    //row.RowStyle = hStyle;
                    for (int i = 1; i <= 8; i++)
                    {
                        row.GetCell(i).CellStyle = IhStyle;
                    }
                    totalLateIn = 0.0;
                    TotalEarlyExit = 0.0;
                    TotMins = 0.0;
                    string strWeekOrder = "";
                    for (int j = 0; j <= dr.Length - 1; j++)
                    {
                        
                        if (strWeekOrder != string.Empty)
                        {
                            if (strWeekOrder != Convert.ToString(dr[j]["weekOrder"]))
                            {
                                row = sheet.CreateRow(rowIndex);
                                row.CreateCell(0).SetCellValue("");
                                row.CreateCell(1).SetCellValue("Cumulative Work Hours Per Week : ");
                                row.CreateCell(2).SetCellValue("");
                                row.CreateCell(3).SetCellValue("");
                                row.CreateCell(4).SetCellValue(cumulateHrs);
                                row.CreateCell(5).SetCellValue("Total Hours :");
                                result = TimeSpan.FromMinutes(totalLateIn);
                                row.CreateCell(6).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                                result = TimeSpan.FromMinutes(TotalEarlyExit);
                                row.CreateCell(7).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                                result = TimeSpan.FromMinutes(TotMins);
                                row.CreateCell(8).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 3));
                                for (int i = 1; i <= 8; i++)
                                {
                                    row.GetCell(i).CellStyle = IhStyle;
                                    sheet.AutoSizeColumn(i);
                                }
                                totalLateIn = 0.0;
                                TotalEarlyExit = 0.0;
                                TotMins = 0.0;
                                slno = 0;
                                rowIndex++;
                            }
                        }
                        slno++;
                        row = sheet.CreateRow(rowIndex);
                        row.CreateCell(0).SetCellValue("");
                        row.CreateCell(1).SetCellValue(slno);
                        row.CreateCell(2).SetCellValue((Convert.ToDateTime(Convert.ToString(dr[j]["Date"]))).ToString(Convert.ToString(Session["MDateFormat"])));
                        row.CreateCell(3).SetCellValue(Convert.ToString(dr[j]["Day"]));
                        row.CreateCell(4).SetCellValue(Convert.ToString(dr[j]["InTime"]));
                        row.CreateCell(5).SetCellValue(Convert.ToString(dr[j]["OutTime"]));
                        row.CreateCell(6).SetCellValue(Convert.ToString(dr[j]["LateIn"]));
                        row.CreateCell(7).SetCellValue(Convert.ToString(dr[j]["EarlyExit"]));
                        row.CreateCell(8).SetCellValue(Convert.ToString(dr[j]["WorkingHours"]));
                        totalLateIn = totalLateIn + Convert.ToDouble(dr[j]["LateInMins"]);
                        TotalEarlyExit = TotalEarlyExit + Convert.ToDouble(dr[j]["EarlyOutMins"]);
                        TotMins = TotMins + Convert.ToDouble(dr[j]["TotalMinsWorked"]);
                        cumulateHrs = Convert.ToString(dr[j]["DefinedWorkHours"]);
                        strWeekOrder = Convert.ToString(dr[j]["weekOrder"]);
                        //row.RowStyle = hStyle;
                        for (int i = 1; i <= 8; i++)
                        {
                            row.GetCell(i).CellStyle = IStyle;
                            sheet.AutoSizeColumn(i);
                        }
                        rowIndex++;
                    }
                    row = sheet.CreateRow(rowIndex);
                    row.CreateCell(0).SetCellValue("");
                    row.CreateCell(1).SetCellValue("Cumulative Work Hours Per Week : ");
                    row.CreateCell(2).SetCellValue("");
                    row.CreateCell(3).SetCellValue("");
                    row.CreateCell(4).SetCellValue(cumulateHrs);
                    row.CreateCell(5).SetCellValue("Total Hours :");
                    result = TimeSpan.FromMinutes(totalLateIn);
                    row.CreateCell(6).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                    result = TimeSpan.FromMinutes(TotalEarlyExit);
                    row.CreateCell(7).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                    result = TimeSpan.FromMinutes(TotMins);
                    row.CreateCell(8).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 3));
                    for (int i = 1; i <= 8; i++)
                    {
                        row.GetCell(i).CellStyle = IhStyle;
                        sheet.AutoSizeColumn(i);
                    }
                    rowIndex++; rowIndex++;
                }
            }
        }
     
        using (MemoryStream exportData = new MemoryStream())
        {
            workbook.Write(exportData);
            string saveAsFileName;
            if (rbtnSummaryReport.Checked == true)
            {
                saveAsFileName = string.Format("CumulativeMinimumWorkWeekHoursSummary-{0:d}.xls", DateTime.Now).Replace("/", "-");
            }
            else
            {
                saveAsFileName = string.Format("CumulativeMinimumWorkWeekHoursDetailed-{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
}