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 System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util;
using System.Web;
using System.Drawing;

public partial class Reports_TA_UI_Report_MonthlyAttendance : 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;
    CultureInfo ci;
    cls_Common objCommon = new cls_Common();
    EmployeeEventData objEmployee = new EmployeeEventData();
    string strFromdate, gstrDateFormat, strTodate;
    int payrollConfig = 0;
    int intIndividualUser = 0;
    string strSwipeCard = "";
    string dtFrom = "", dtTo = "";
    string Level = string.Empty;
    string employeeName = string.Empty;
    string empName = string.Empty;
    string empId = string.Empty;
    int shift;
    string strEmpAutoId = "0";
    string MinWorkHours = "";
    HSSFFont pageheaderFont;
    HSSFCellStyle empDetailStyle, DetailStyle, MainStyle, fieldHeaderStyle, totalStyle;
    #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)
    {
        base.Page_Load(sender, e);
        gvData.Styles.CssFilePath = Convert.ToString(Session["GridCSS"]);
        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"]);
        string strSetAlert = "";

        if (!Page.IsPostBack)
        {
            base.CheckPages(0);
            objCommon.menu("Attendance Reports", "40");
            payrollConfig = objCommon.CheckPageExistsInLicense(185);
            /*Below parameter is used for Angler*/
            if (payrollConfig == 1)
            {
                divProduct.Style.Add("display", "none");
            }
            strSetAlert = strSetAlert + "<script language='javascript'>";
            strSetAlert = strSetAlert + "SetAlertMessages('" + rm.GetString("Alert_EnterDate", ci) + "','" + rm.GetString("Alert_EnterToDate", ci) + "','" + rm.GetString("Alert_EntervalidMinimumWorkHours", ci) + "');";
            strSetAlert = strSetAlert + "MaxLengthh('" + rm.GetString("Alert_MaximumLengthis", ci) + "');";
            strSetAlert = strSetAlert + "SetAlertCheckDate('" + rm.GetString("Alert_Validmonthdatefourdigit", ci) + "','" + rm.GetString("Alert_Validdatemonthfourdigit", ci) + "','" + rm.GetString("Alert_Invalidyearentry", ci) + "','" + rm.GetString("Alert_Invalidmonthentry", ci) + "','" + rm.GetString("Alert_Invaliddateentry", ci) + "','" + rm.GetString("Alert_IncorrectdateformatDDMONYYYY", ci) + "','" + rm.GetString("Alert_Entertime", ci) + "','" + rm.GetString("Alert_Invalidvalueforhours", ci) + "','" + rm.GetString("Alert_Invalidvalueforminites", ci) + "','" + rm.GetString("Alert_Invalidtimeformat1", ci) + "','" + rm.GetString("Alert_PleaseentervalidDateandTime", ci) + "','" + rm.GetString("Alert_ValiddatemonthMMDDYYHHMM", ci) + "','" + rm.GetString("Alert_ValiddatemonthMMDDYYHHMMTT", ci) + "','" + rm.GetString("Alert_PleaseentervalidDateandTime", ci) + "','" + rm.GetString("Alert_Invalidvalueforhours", ci) + "','" + rm.GetString("Alert_Invalidvalueforminites", ci) + "');";
            strSetAlert = strSetAlert + "</script>";
            Page.RegisterStartupScript("SetAlertMessages", strSetAlert);
            //Cache["MonthlyAttendanceVertical"] = null;
            Cache.Remove("MonthlyAttendanceVertical");
            LoadStrings(ci);
            UserRights();
            BindingEmployeeCategoryandType();
            BindShift();
            BindDesignation();
            if (Request.QueryString["Source"] != null)
            {
                if (Request.QueryString["Source"].ToString() == "Email")
                {
                    divdateTime.Visible = false;
                    trView.Visible = false;
                    trSave.Visible = true;
                    lnkbtnGrid.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)
                            {
                                txtEmployeeName.Text = Convert.ToString(ds.Tables[0].Rows[0]["EmployeeName"]);
                                ddlEmpCatagory.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["EmployeeCategory"]);
                                ddlDesignation.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["DesignationID"]);
                                txtEmployeeId.Text = Convert.ToString(ds.Tables[0].Rows[0]["EmployeeID"]);
                                ddlShift.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["shift"]);
                                ddlGender.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["Gender"]);
                                ddlEmpType.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["EmployeeType"]);
                                txtMinWorkHours.Text = Convert.ToString(ds.Tables[0].Rows[0]["MinWorkHours"]);
                                Session["EMailParentId"] = Convert.ToString(ds.Tables[0].Rows[0]["ParentID"]);
                                rbReportType.SelectedValue = Convert.ToString(ds.Tables[1].Rows[0]["ReportType"]);
                            }
                        }
                    }
                    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];
                            }
                            txtMinWorkHours.Text = strArray1[0].ToString();
                            ddlShift.SelectedValue = strArray1[4].ToString();
                            txtEmployeeName.Text = strArray1[5].ToString().Replace("~", ",");
                            txtEmployeeId.Text = strArray1[3].ToString().Replace("~", ",");
                            ddlEmpCatagory.SelectedValue = strArray1[7].ToString();
                            ddlEmpType.SelectedValue = strArray1[8].ToString();
                            ddlDesignation.SelectedValue = Convert.ToString(strArray1[13]);
                            ddlGender.SelectedValue = Convert.ToString(strArray1[14]);
                            ddlDataLoading.SelectedValue = strArray1[15].ToString();
                            Session["EMailParentId"] = Convert.ToString(strArray1[12]);
                            rbReportType.SelectedValue = Convert.ToString(Session["ReportType"]);
                        }
                    }
                }
            }
            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);
            }
            if (Session["parentLevelId"] != null || Convert.ToInt16(Session["parentLevelId"]) != 0)
                Session["parentLevelId"] = null;
            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());
        }
        else
        {
            if (Request.QueryString["Source"] != null)
            {
                if (Request.QueryString["Source"].ToString() == "Email")
                {
                    divdateTime.Visible = false;
                    trView.Visible = false;
                    trSave.Visible = true;
                }
            }
            else
            {
                divdateTime.Visible = true;
                trView.Visible = true;
                trSave.Visible = false;
            }
        }
        if (ViewState["vsintIndividualUser"] != null)
        {
            intIndividualUser = Convert.ToInt16(ViewState["vsintIndividualUser"]);
        }
        else
        {
            intIndividualUser = objCheckUser.CheckIndividualUser(Convert.ToString(Session["UserGroupName"]));
            ViewState["vsintIndividualUser"] = intIndividualUser;
        }
        if (intIndividualUser >= 1)
        {
            PlaceHolderMonthlyAttendance.Controls.Clear();
            divEmployeeDetails.Visible = false;
        }
        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;
        divCustom.Style.Add("display", "none");
        if (Convert.ToString(Session["packageID"]) != "")
        {
            if (Convert.ToString(Session["packageID"]) == "156")
            {
                rbReportType.Items[1].Enabled = false;
                divCustom.Style.Add("display", "");
                divproducts.Style.Add("display", "none");
            }
        }
        if (Cache["MonthlyAttendanceVertical"] != null)
        {
            gvData.DataSource = (DataSet)Cache["MonthlyAttendanceVertical"];
            //gvData.DataBind();
        }
    }
    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>
    /// Load Shift DropDown
    /// </summary>
    public void BindShift()
    {
        ShiftEvenData getShift = new ShiftEvenData();
        DataSet ds = new DataSet();
        string strusergroup = string.Empty;
        ds = getShift.SelectShift(Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["LevelRights"]), 0);
        foreach (DataRow drRow in ds.Tables[0].Rows)
        {
            switch (drRow["Shift_Name"].ToString())
            {
                case "No Shift":
                    drRow["Shift_Name"] = rm.GetString("Caption_NoShift", ci);
                    break;
            }
        }
        if (ds.Tables[0].Rows.Count > 0)
        {
            this.ddlShift.DataSource = ds;
            this.ddlShift.DataTextField = "Shift_Name";
            this.ddlShift.DataValueField = "Shift_Id";
            this.ddlShift.DataBind();
        }
        ListItem lstItem = new ListItem();
        lstItem.Value = "0";
        lstItem.Text = rm.GetString("ExportData_All", ci);
        ddlShift.Items.Insert(0, lstItem);
        BindTitle(ddlShift);
    }
    private void BindTitle(DropDownList ddl)
    {
        foreach (ListItem item in ddl.Items)
        {
            item.Attributes.Add("title", item.Text);
        }
    }
    /// <summary>
    /// Fills the designation in the drop down
    /// </summary>
    private void BindDesignation()
    {
        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();
            BindTitle(ddlDesignation);
        }
    }
    /// <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();
            BindTitle(ddlEmpCatagory);
        }
        // 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();
            BindTitle(ddlEmpType);
        }
    }
    /// <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"]);
            strFromdate = objCommon.DateConvertion(gstrDateFormat, strFromdate);
            DateTime dtCurrentDate = Convert.ToDateTime(DateTime.Today.Date);
            DateTime dtFromDate = DateTime.Parse(strFromdate);
            TimeSpan diff = dtFromDate.Subtract(dtCurrentDate);
            int day = diff.Days;
            if (day <= 0)
                e.IsValid = true;
            else
            {
                base.Message(rm.GetString("Alert_EnterValidFromDate", 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"]);
            strTodate = txtToDate.Text;
            strTodate = objCommon.DateConvertion(gstrDateFormat, strTodate);
            DateTime dtCurrentDate = Convert.ToDateTime(DateTime.Today.Date);
            DateTime dtFromDate = DateTime.Parse(strTodate);
            TimeSpan diff = dtFromDate.Subtract(dtCurrentDate);
            int day = diff.Days;
            if (day <= 0)
                e.IsValid = true;
            else
            {
                base.Message(rm.GetString("Alert_EnterValidToDate", ci));
                e.IsValid = false;
            }
        }
        catch
        {
        }
    }
    /// <summary>
    /// Compare ToDate with FromDate
    /// </summary>
    /// <param name="s"></param>
    /// <param name="e"></param>
    public void CompareDate(object s, ServerValidateEventArgs e)
    {
        try
        {
            strFromdate = txtFromDate.Text;
            strTodate = txtToDate.Text;
            gstrDateFormat = Convert.ToString(Session["MDateFormat"]);
            strTodate = objCommon.DateConvertion(gstrDateFormat, strTodate);
            strFromdate = objCommon.DateConvertion(gstrDateFormat, strFromdate);
            DateTime dtToDate = DateTime.Parse(strTodate);
            DateTime dtFromDate = DateTime.Parse(strFromdate);
            TimeSpan diff = dtToDate.Subtract(dtFromDate);
            int day = diff.Days;
            if (day >= 0)
                e.IsValid = true;
            else
            {
                base.Message(rm.GetString("Msg_SelectvaliddatecomparewithFromdate", ci));
                e.IsValid = false;
            }
        }
        catch
        {
        }
    }
    /// <summary>
    /// Getting Information from resource file and display it in label
    /// </summary>
    /// <param name="ci"></param>
    private void LoadStrings(CultureInfo ci)
    {
        lblFilter.Text = rm.GetString("lblFilter", ci);
        btn_View.Text = "&nbsp;&nbsp;" + rm.GetString("btn_View", ci);
        lblMonthlyAttendanceReport.Text = rm.GetString("Reports_MonthlyAttendance_MonthlyAttendanceReport", ci);
        lblEmployeeName.Text = rm.GetString("email_EmployeeName", ci);
        lblEmployeeId.Text = rm.GetString("email_EmployeeId", ci);
        lblEmpCategory.Text = rm.GetString("Master_Category", ci);
        lblEmpType.Text = rm.GetString("Master_Type", ci);
        lblTimePeriod.Text = rm.GetString("Reports_TimePeriod", ci);
        lblFrom.Text = rm.GetString("email_FromDate", ci);
        lblTo.Text = rm.GetString("email_ToDate", ci);
        lblMinimumWorkHours.Text = rm.GetString("Report_Minimum_Work_Hours", ci);
        lblEmpSwipe.Text = rm.GetString("TerminalDataType_Card_No", ci);
        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);
        lblShift.Text = rm.GetString("Menu_Reports_Shift", ci);
        btnSave.Text = "&nbsp;&nbsp;" + rm.GetString("btn_Save", ci);
        btnBack.Text = "&nbsp;&nbsp;" + rm.GetString("btn_Cancel", ci);
        lblReportType.Text = rm.GetString("MonthlyAttendanceReportType", ci);
        lblDateFilter.Text = rm.GetString("lbl_DateFilter", 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);
        lblShift.Text = rm.GetString("Menu_Reports_Shift", ci);
        lblDataloading.Text = rm.GetString("EmployeeInOutReportType", ci);
        rbReportType.Items[0].Text = rm.GetString("lbl_Vertical", ci);
        rbReportType.Items[1].Text = rm.GetString("lbl_Horizontal", ci);
        btn_Export.Text = "&nbsp;&nbsp;" + rm.GetString("btn_ExporttoExcel", ci);
        //lnkbtnCSV.Text = "&nbsp;&nbsp;" + rm.GetString("btn_ExporttoExcel", ci);
        ddlDataLoading.Items[0].Text = rm.GetString("ExportData_All", ci);
    }
    /// <summary>
    /// To check rights for the logged in user
    /// </summary>
    private void UserRights()
    {
        if (ViewState["vsUserRights"] != null)
            ds_Checkuser = (DataSet)ViewState["vsUserRights"];
        else
            ds_Checkuser = objCheckUser.checkRights(40, Convert.ToInt32(Session["userGroup_Id"]), "viewPrv");
        if (ds_Checkuser.Tables[0].Rows.Count > 0)
        {
            ViewState["vsUserRights"] = ds_Checkuser;
            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
    private void commonfieldsAndValues()
    {

        if (txtFromDate.Text != "")
            dtFrom = strFromdate;
        if (txtToDate.Text != "")
            dtTo = strTodate;
        shift = Convert.ToInt32(ddlShift.SelectedValue);
        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 (Session["ReportsBasedOn"] != null)
        {
            switch (Convert.ToString(Session["ReportsBasedOn"]))
            {
                case "False":
                    strEmpAutoId = "0";
                    break;
                default:
                    strEmpAutoId = Convert.ToString(Session["EmpAutoID"]);
                    break;
            }
        }
        strSwipeCard = txtSwipeCard.Text.ToString().Trim().Replace("'", "''");
        MinWorkHours = Convert.ToString(txtMinWorkHours.Text);
    }
    private DataSet getAttendanceData()
    {
        DataSet dsEmp = new DataSet();
        if (rbReportType.SelectedValue == "0" || rbReportType.SelectedValue == "2")
        {
            if (Convert.ToString(Session["packageID"]) != "" && Convert.ToString(Session["packageID"]) == "161")
            {
                //Ardlysis
                dsEmp = rptEmp.SelectMonthlyAttendance_Ardlysis(MinWorkHours, dtFrom, dtTo, employeeName, Level, empId, Convert.ToInt32(ddlEmpCatagory.SelectedValue), Convert.ToInt32(ddlEmpType.SelectedValue),
                    Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), strSwipeCard, Convert.ToInt16(Session["parentLevelId"]),
                    Convert.ToInt32(ddlDesignation.SelectedValue), ddlGender.SelectedValue, shift,
                    Convert.ToInt32(ddlDataLoading.SelectedValue), Convert.ToInt64(strEmpAutoId), 0);
            }
            else
            {
                dsEmp = rptEmp.SelectMonthlyAttendanceReport("Proc_TA_RptMonthlyAttendance", MinWorkHours, dtFrom, dtTo, empName, Level, empId, Convert.ToInt32(ddlEmpCatagory.SelectedValue), Convert.ToInt32(ddlEmpType.SelectedValue),
                    Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), strSwipeCard,
                    Convert.ToInt16(Session["parentLevelId"]), Convert.ToInt32(ddlDesignation.SelectedValue), ddlGender.SelectedValue, shift,
                    Convert.ToInt32(ddlDataLoading.SelectedValue), Convert.ToInt64(strEmpAutoId), 0);
            }
        }
        else
        {
            dsEmp = rptEmp.SelectMonthlyAttendanceDetailReport(MinWorkHours, dtFrom, dtTo, employeeName, Level, empId, Convert.ToInt32(ddlEmpCatagory.SelectedValue), Convert.ToInt32(ddlEmpType.SelectedValue),
                   Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), strSwipeCard,
                   Convert.ToInt16(Session["parentLevelId"]), Convert.ToInt32(ddlDesignation.SelectedValue), ddlGender.SelectedValue, shift,
                   Convert.ToInt32(ddlDataLoading.SelectedValue), Convert.ToInt32(Session["EmpAutoID"]));
        }
        return dsEmp;
    }
    protected void btn_View_Click(object sender, EventArgs e)
    {
        BindDataToReport("View", 0);
    }
    private void BindDataToReport(string whichBtn, int isgrid)
    {
        if (Page.IsValid == true)
        {
            DataSet dsEmp = new DataSet();
            #region From Date and ToDate Adding
            if (rbReportType.SelectedItem.Text == "Horizontal")
            {
                gstrDateFormat = Convert.ToString(Session["MDateFormat"]);
                string strdtFromDate, strdtToDate;
                strdtFromDate = txtFromDate.Text.Trim();
                strdtToDate = txtToDate.Text.Trim();
                strdtFromDate = objCommon.DateConvertion(gstrDateFormat, strdtFromDate);
                strdtToDate = objCommon.DateConvertion(gstrDateFormat, strdtToDate);
                DateTime dtFromDate1;
                DateTime dtToDate1;
                dtFromDate1 = Convert.ToDateTime(strdtFromDate).Date;
                dtToDate1 = DateTime.Parse(strdtToDate);
                TimeSpan diff = dtToDate1.Subtract(dtFromDate1);
                int day = diff.Days;
                day = day + 1;
                if (day > 31)
                {
                    base.Message("Selected Date range should not exceed 31 Days");
                    return;
                }
            }
            #endregion
            commonfieldsAndValues();
            dsEmp = getAttendanceData();
            if (dsEmp.Tables["Table"] == null || dsEmp.Tables["Table"].Rows.Count == 0)
            {
                Message(rm.GetString("Alert_Thereisnorecord", ci));
                return;
            }
            else
            {
                switch (whichBtn)
                {
                    //view btn
                    case "View":
                        LoadReport(dsEmp, isgrid);
                        break;
                    //export btn
                    case "Export":
                        switch (rbReportType.SelectedValue)
                        {
                            case "0":
                            case "2":
                                if (dsEmp.Tables[0].Columns.Contains("SPEC_NormOT1"))
                                    dsEmp.Tables[0].Columns.Remove("SPEC_NormOT1");
                                if (dsEmp.Tables[0].Columns.Contains("SPEC_NormOT2"))
                                    dsEmp.Tables[0].Columns.Remove("SPEC_NormOT2");
                                if (dsEmp.Tables[0].Columns.Contains("OFFOT"))
                                    dsEmp.Tables[0].Columns.Remove("OFFOT");
                                if (dsEmp.Tables[0].Columns.Contains("SPEC_OffOT1"))
                                    dsEmp.Tables[0].Columns.Remove("SPEC_OffOT1");
                                if (dsEmp.Tables[0].Columns.Contains("SPEC_OffOT2"))
                                    dsEmp.Tables[0].Columns.Remove("SPEC_OffOT2");
                                if (dsEmp.Tables[0].Columns.Contains("HOLOT"))
                                    dsEmp.Tables[0].Columns.Remove("HOLOT");
                                if (dsEmp.Tables[0].Columns.Contains("SPEC_HOLOT1"))
                                    dsEmp.Tables[0].Columns.Remove("SPEC_HOLOT1");
                                if (dsEmp.Tables[0].Columns.Contains("SPEC_HOLOT2"))
                                    dsEmp.Tables[0].Columns.Remove("SPEC_HOLOT2");
                                if (dsEmp.Tables[0].Columns.Contains("SUPPOSEDWORKHOURS"))
                                    dsEmp.Tables[0].Columns.Remove("SUPPOSEDWORKHOURS");
                                if (dsEmp.Tables[0].Columns.Contains("TOTALMINSWORKED"))
                                    dsEmp.Tables[0].Columns.Remove("TOTALMINSWORKED");
                                if (dsEmp.Tables[0].Columns.Contains("StatusCode"))
                                    dsEmp.Tables[0].Columns.Remove("StatusCode");
                                if (dsEmp.Tables[0].Columns.Contains("NORMOTMIN"))
                                    dsEmp.Tables[0].Columns.Remove("NORMOTMIN");
                                if (dsEmp.Tables[0].Columns.Contains("OFFOTMIN"))
                                    dsEmp.Tables[0].Columns.Remove("OFFOTMIN");
                                if (dsEmp.Tables[0].Columns.Contains("HOLOTMIN"))
                                    dsEmp.Tables[0].Columns.Remove("HOLOTMIN");
                                if (dsEmp.Tables[0].Columns.Contains("LATEINMIN"))
                                    dsEmp.Tables[0].Columns.Remove("LATEINMIN");
                                if (dsEmp.Tables[0].Columns.Contains("EARLYOUTMIN"))
                                    dsEmp.Tables[0].Columns.Remove("EARLYOUTMIN");
                                if (dsEmp.Tables[0].Columns.Contains("TOTDEFWORKHRS"))
                                    dsEmp.Tables[0].Columns.Remove("TOTDEFWORKHRS");
                                if (dsEmp.Tables[0].Columns.Contains("TOTSUPWORKHRS"))
                                    dsEmp.Tables[0].Columns.Remove("TOTSUPWORKHRS");
                                if (dsEmp.Tables[0].Columns.Contains("OVERTIMESTATUS"))
                                    dsEmp.Tables[0].Columns.Remove("OVERTIMESTATUS");
                                if (dsEmp.Tables[0].Columns.Contains("NIGHTORMORN"))
                                    dsEmp.Tables[0].Columns.Remove("NIGHTORMORN");
                                if (dsEmp.Tables[0].Columns.Contains("LUNCHINTIME"))
                                    dsEmp.Tables[0].Columns.Remove("LUNCHINTIME");
                                if (dsEmp.Tables[0].Columns.Contains("LUNCHOUTTIME"))
                                    dsEmp.Tables[0].Columns.Remove("LUNCHOUTTIME");
                                if (dsEmp.Tables[0].Columns.Contains("INNOTATION"))
                                    dsEmp.Tables[0].Columns.Remove("INNOTATION");
                                if (dsEmp.Tables[0].Columns.Contains("OUTNOTATION"))
                                    dsEmp.Tables[0].Columns.Remove("OUTNOTATION");
                                if (dsEmp.Tables[0].Columns.Contains("SHIFT_STARTTIME"))
                                    dsEmp.Tables[0].Columns.Remove("SHIFT_STARTTIME");
                                if (dsEmp.Tables[0].Columns.Contains("SHIFT_ENDTIME"))
                                    dsEmp.Tables[0].Columns.Remove("SHIFT_ENDTIME");
                                if (dsEmp.Tables[0].Columns.Contains("BREAKHOURSMINS"))
                                    dsEmp.Tables[0].Columns.Remove("BREAKHOURSMINS");
                                if (dsEmp.Tables[0].Columns.Contains("LUNCHHOURSMINS"))
                                    dsEmp.Tables[0].Columns.Remove("LUNCHHOURSMINS");
                                if (dsEmp.Tables[0].Columns.Contains("WEEKDAY_WORKHRS"))
                                    dsEmp.Tables[0].Columns.Remove("WEEKDAY_WORKHRS");
                                if (dsEmp.Tables[0].Columns.Contains("WEEKNUMBER"))
                                    dsEmp.Tables[0].Columns.Remove("WEEKNUMBER");
                                if (dsEmp.Tables[0].Columns.Contains("FNATTENDANCECODE"))
                                    dsEmp.Tables[0].Columns.Remove("FNATTENDANCECODE");
                                if (dsEmp.Tables[0].Columns.Contains("ANATTENDANCECODE"))
                                    dsEmp.Tables[0].Columns.Remove("ANATTENDANCECODE");
                                if (dsEmp.Tables[0].Columns.Contains("DESIGNATION"))
                                    dsEmp.Tables[0].Columns.Remove("DESIGNATION");
                                //if (dsEmp.Tables[0].Columns.Contains("BRANCHNAME"))
                                //    dsEmp.Tables[0].Columns.Remove("BRANCHNAME");
                                if (dsEmp.Tables[0].Columns.Contains("processDate"))
                                    dsEmp.Tables[0].Columns.Remove("processDate");
                                if (dsEmp.Tables[0].Columns.Contains("processDay"))
                                    dsEmp.Tables[0].Columns.Remove("processDay");
                                if (dsEmp.Tables[0].Columns.Contains("processMonth"))
                                    dsEmp.Tables[0].Columns.Remove("processMonth");
                                if (dsEmp.Tables[0].Columns.Contains("OVERTIME"))
                                    dsEmp.Tables[0].Columns.Remove("OVERTIME");
                                if (dsEmp.Tables[0].Columns.Contains("Intime1"))
                                    dsEmp.Tables[0].Columns.Remove("Intime1");
                                if (dsEmp.Tables[0].Columns.Contains("Intime2"))
                                    dsEmp.Tables[0].Columns.Remove("Intime2");
                                if (dsEmp.Tables[0].Columns.Contains("Intime3"))
                                    dsEmp.Tables[0].Columns.Remove("Intime3");
                                if (dsEmp.Tables[0].Columns.Contains("Intime4"))
                                    dsEmp.Tables[0].Columns.Remove("Intime4");
                                if (dsEmp.Tables[0].Columns.Contains("Intime5"))
                                    dsEmp.Tables[0].Columns.Remove("Intime5");
                                if (dsEmp.Tables[0].Columns.Contains("OutTime1"))
                                    dsEmp.Tables[0].Columns.Remove("OutTime1");
                                if (dsEmp.Tables[0].Columns.Contains("OutTime2"))
                                    dsEmp.Tables[0].Columns.Remove("OutTime2");
                                if (dsEmp.Tables[0].Columns.Contains("OutTime3"))
                                    dsEmp.Tables[0].Columns.Remove("OutTime3");
                                if (dsEmp.Tables[0].Columns.Contains("OutTime4"))
                                    dsEmp.Tables[0].Columns.Remove("OutTime4");
                                if (dsEmp.Tables[0].Columns.Contains("OutTime5"))
                                    dsEmp.Tables[0].Columns.Remove("OutTime5");
                                if (dsEmp.Tables[0].Columns.Contains("BRANCHNAME"))
                                    dsEmp.Tables[0].Columns["BRANCHNAME"].ColumnName = "Department";
                                if (dsEmp.Tables[0].Columns.Contains("EARLYOUT1"))
                                    dsEmp.Tables[0].Columns.Remove("EARLYOUT1");
                                if (dsEmp.Tables[0].Columns.Contains("LATEIN1"))
                                    dsEmp.Tables[0].Columns.Remove("LATEIN1");
                                if (dsEmp.Tables[0].Columns.Contains("NORMOT1"))
                                    dsEmp.Tables[0].Columns.Remove("NORMOT1");
                                if (dsEmp.Tables[0].Columns.Contains("OFFOT1"))
                                    dsEmp.Tables[0].Columns.Remove("OFFOT1");
                                if (dsEmp.Tables[0].Columns.Contains("HOLOT1"))
                                    dsEmp.Tables[0].Columns.Remove("HOLOT1");
                                if (dsEmp.Tables[0].Columns.Contains("Department"))
                                    dsEmp.Tables[0].Columns.Remove("Department");
                                if (dsEmp.Tables[0].Columns.Contains("AcutalWorkedHours"))
                                    dsEmp.Tables[0].Columns.Remove("AcutalWorkedHours");
                                if (dsEmp.Tables[0].Columns.Contains("MonthNo"))
                                    dsEmp.Tables[0].Columns.Remove("MonthNo");
                                if (dsEmp.Tables[0].Columns.Contains("TLH"))
                                    dsEmp.Tables[0].Columns.Remove("TLH");
                                if (dsEmp.Tables[0].Columns.Contains("TBH"))
                                    dsEmp.Tables[0].Columns.Remove("TBH");
                                if (rbReportType.SelectedValue == "2")
                                {
                                    if (dsEmp.Tables[0].Columns.Contains("secondsession"))
                                        dsEmp.Tables[0].Columns.Remove("secondsession");
                                }
                                Session["Export_ExcelData"] = dsEmp;
                                Response.Redirect("Excel_Export.aspx?ReportName=Monthly Attendance&FrmDate=" + Convert.ToString(txtFromDate.Text) + "&toDate=" + Convert.ToString(txtToDate.Text) + "");
                                break;
                            case "1":
                                ExportToExcelHorizontal(dsEmp);
                                break;
                        }
                        break;
                }
            }
        }
    }
    private void LoadReport(DataSet dsEmp, int grid)
    {
        switch (rbReportType.SelectedValue)
        {
            case "0":
            case "2":
                if (grid == 1)
                {
                    gvData.Visible = true;
                    btnCustWindow.Visible = true;
                    lnkbtnpdf.Visible = true;
                    lnktbnexcel.Visible = true;
                    lnbtnClear.Visible = true;
                    lblmanual.Visible = true;
                    lnkbtnCSV.Visible = false;
                    lnkbtnCollapse.Visible = true;
                    lnkbtnExpand.Visible = true;
                    gvData.DataSource = dsEmp;
                    gvData.DataBind();
                    gvData.SearchPanelFilter = "";
                    gvData.SettingsText.Title = "Monthly Attendance Report - " + txtFromDate.Text + " To " + txtToDate.Text;
                    ApplyLayout(0);
                    if (Convert.ToString(Session["packageID"]) == "" || Convert.ToString(Session["packageID"]) != "161") // hide in demo and not ardlysis clients
                    {
                        gvData.Columns[6].Visible = false;
                        gvData.Columns[7].Visible = false;
                        gvData.Columns[8].Visible = false;
                        gvData.Columns[9].Visible = false;
                        gvData.Columns[10].Visible = false;
                        gvData.Columns[11].Visible = false;
                        gvData.Columns[12].Visible = false;
                        gvData.Columns[13].Visible = false;
                        gvData.Columns[14].Visible = false;
                        gvData.Columns[15].Visible = false;
                        gvData.Columns[17].Visible = false;
                    }
                    if (rbReportType.SelectedValue == "2")
                    {
                        gvData.Columns[24].Visible = false;
                    }
                    if (Convert.ToString(Session["packageID"]) != "")
                        if (Convert.ToString(Session["packageID"]) == "156")
                        {
                            DisableControls();
                        }
                    Cache["MonthlyAttendanceVertical"] = dsEmp;
                }
                else
                {
                    Session["MonthlyAttendanceVertical"] = dsEmp;
                    OpenreportViewer("window.open('TA_UI_Report_ReportViewer.aspx?isStraightReport=" + rbReportType.SelectedValue + "&Iscustomizedrpt=" + ((rdbtnReport.Items[0].Selected == true) ? 0 : 1) + "&ReportName=MonthlyAttendance&MonthlydataLoadingVer=" + Convert.ToInt32(ddlDataLoading.SelectedValue) + "&dtFrom=" + dtFrom + "&dtTo=" + dtTo + "&empid=" + empId + "&shft=" + shift + "&EmpName=" + employeeName + "&Level=" + Level + "&EmpCategory=" + Convert.ToInt32(ddlEmpCatagory.SelectedValue) + "&EmpType=" + Convert.ToInt32(ddlEmpType.SelectedValue) + "&ParentLevelId=" + Convert.ToInt16(Session["parentLevelId"]).ToString() + "&UserGroupName=" + Convert.ToString(Session["UserGroupName"]) + "&GradeLevel=" + Convert.ToString(Session["GradeLevel"]) + "&MinWorkHours=" + MinWorkHours + "&SwipeCard=" + strSwipeCard + "&Designation=" + Convert.ToInt32(ddlDesignation.SelectedValue) + "&Gender=" + ddlGender.SelectedValue + "','','left=10px, top=60px, width=1000px, height=600px, scrollbars=yes, status=no, resizable=yes');");
                }
                break;
            case "1":
                Session["MonthlyAttendanceHorizontal"] = dsEmp;
                OpenreportViewer("window.open('TA_UI_Report_ReportViewer.aspx?ReportName=MonthlyAttendanceDetail&MonthlydataLoadingVer=" + Convert.ToInt32(ddlDataLoading.SelectedValue) + "&dtFrom=" + dtFrom + "&dtTo=" + dtTo + "&empid=" + empId + "&shft=" + shift + "&EmpName=" + employeeName + "&Level=" + Level + "&EmpCategory=" + Convert.ToInt32(ddlEmpCatagory.SelectedValue) + "&EmpType=" + Convert.ToInt32(ddlEmpType.SelectedValue) + "&ParentLevelId=" + Convert.ToInt16(Session["parentLevelId"]) + "&UserGroupName=" + Convert.ToString(Session["UserGroupName"]) + "&GradeLevel=" + Convert.ToString(Session["GradeLevel"]) + "&MinWorkHours=" + MinWorkHours + "&SwipeCard=" + strSwipeCard + "&Designation=" + Convert.ToInt32(ddlDesignation.SelectedValue) + "&Gender=" + ddlGender.SelectedValue + "','','left=10px, top=60px, width=1000px, height=600px, scrollbars=yes, status=no, resizable=yes');");
                break;
        }
    }
    private void DisableControls()
    {
        if (rdbtnReport.Items[0].Selected == true)
        {
            //special Overtime fields
            gvData.Columns[10].Visible = false;
            gvData.Columns[11].Visible = false;
            gvData.Columns[13].Visible = false;
            gvData.Columns[14].Visible = false;
            // gvData.Columns[16].Visible = false; //HW field , the order changed due to neyveli client new fields
            gvData.Columns[17].Visible = false;
        }
        else
        {
            gvData.Columns[10].Visible = true;
            gvData.Columns[11].Visible = true;
            gvData.Columns[13].Visible = true;
            gvData.Columns[14].Visible = true;
            gvData.Columns[16].Visible = true; //HW field , the order changed due to neyveli client new fields
            gvData.Columns[17].Visible = true;
        }
    }
    private void OpenreportViewer(string redirection)
    {
        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 + redirection;
        strScript = strScript + "</script>";
        Response.Write(strScript);
    }
    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.ToInt32(ddlShift.SelectedValue);
                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 (Level.Contains(","))
                        Level = Level.Replace(",", "~");
                    if (employeeName.Contains(","))
                        employeeName = employeeName.Replace(",", "~");
                    if (empId.Contains(","))
                        empId = empId.Replace(",", "~");
                }
                strSwipeCard = txtSwipeCard.Text.ToString().Trim().Replace("'", "''");
                string MinWorkHours = Convert.ToString(txtMinWorkHours.Text);
                StringBuilder strFilters = new StringBuilder();
                switch (rbReportType.SelectedValue)
                {
                    case "0":
                    case "2":
                        strFilters.Append("@MinWorkHours=" + MinWorkHours + ",");
                        strFilters.Append("@fromDat=" + " Duration_From " + ",");
                        strFilters.Append("@ToDat=" + " Duration_To " + ",");
                        strFilters.Append("@EmployeeId=" + empId + ",");
                        strFilters.Append("@shift=" + shift + ",");
                        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("@GradeLevel=" + Convert.ToString(Session["GradeLevel"]) + ",");
                        strFilters.Append("@EmpSwipeCard=" + strSwipeCard + ",");
                        strFilters.Append("@ParentId=" + Convert.ToInt32(Session["parentLevelId"]) + ",");
                        strFilters.Append("@DesignationID=" + ddlDesignation.SelectedValue + ",");
                        strFilters.Append("@Gender=" + ddlGender.SelectedValue + ",");
                        strFilters.Append("@DataLoading=" + Convert.ToInt32(ddlDataLoading.SelectedValue) + ",");
                        strFilters.Append("@EmpAutoId=0" + ",");
                        strFilters.Append("@Export=0");
                        break;
                    case "1":
                        strFilters.Append("@MinWorkHours=" + MinWorkHours + ",");
                        strFilters.Append("@fromDat=" + " Duration_From " + ",");
                        strFilters.Append("@ToDat=" + " Duration_To " + ",");
                        strFilters.Append("@EmployeeId=" + empId + ",");
                        strFilters.Append("@shift=" + shift + ",");
                        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("@GradeLevel=" + Convert.ToString(Session["GradeLevel"]) + ",");
                        strFilters.Append("@EmpSwipeCard=" + strSwipeCard + ",");
                        strFilters.Append("@ParentId=" + Convert.ToInt32(Session["parentLevelId"]) + ",");
                        strFilters.Append("@DesignationID=" + ddlDesignation.SelectedValue + ",");
                        strFilters.Append("@Gender=" + ddlGender.SelectedValue + ",");
                        strFilters.Append("@DataLoading=" + Convert.ToInt32(ddlDataLoading.SelectedValue) + ",");
                        strFilters.Append("@EmpAutoId=0");
                        break;
                }
                Session["hdnReportFilters"] = string.Empty;
                Session["hdnReportFilters"] = strFilters;
                Session["ReportType"] = rbReportType.SelectedValue;
                string strScript = "<script language=\"javascript\">";
                strScript = strScript + "{window.top.parent.document.getElementById('ctl00_BodyContentPH_hdnFilters').value='" + strFilters + "'; window.top.parent.document.getElementById('ctl00_BodyContentPH_hdnReportType').value='" + rbReportType.SelectedValue + "'; window.top.parent.document.getElementById('ctl00_BodyContentPH_hdnCompanyId').value='" + Convert.ToInt32(Session["parentLevelId"]).ToString() + "';}";
                strScript = strScript + "parent.$('#myModalReport').modal('hide');";
                strScript = strScript + "</script>";
                Response.Write(strScript);
            }
        }
        catch
        {
        }
    }
    protected void getStyles(HSSFWorkbook workbook)
    {
        #region objectCreation
        pageheaderFont = (HSSFFont)workbook.CreateFont();
        empDetailStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        DetailStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        MainStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        fieldHeaderStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        totalStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        #endregion
        #region PageHeaderStyles
        pageheaderFont.Boldweight = (short)FontBoldWeight.Bold;
        pageheaderFont.Color = HSSFColor.Maroon.Index;
        pageheaderFont.FontHeightInPoints = 16;
        MainStyle.SetFont(pageheaderFont);
        MainStyle.Alignment = HorizontalAlignment.Center;
        #endregion
        #region EmpHeaderStyle
        empDetailStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        empDetailStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        empDetailStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        empDetailStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        empDetailStyle.Alignment = HorizontalAlignment.Center;
        empDetailStyle.VerticalAlignment = VerticalAlignment.Center;
        empDetailStyle.WrapText = true;
        empDetailStyle.FillPattern = FillPattern.SolidForeground;
        HSSFFont empheaderfont = (HSSFFont)workbook.CreateFont();
        empheaderfont.Boldweight = (short)FontBoldWeight.Bold;
        empheaderfont.Color = HSSFColor.Black.Index;
        empheaderfont.FontHeightInPoints = 12;
        empDetailStyle.SetFont(empheaderfont);
        #endregion
        #region fieldHeaderStyle
        fieldHeaderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        fieldHeaderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        fieldHeaderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        fieldHeaderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        fieldHeaderStyle.Alignment = HorizontalAlignment.Center;
        fieldHeaderStyle.VerticalAlignment = VerticalAlignment.Center;
        fieldHeaderStyle.FillForegroundColor = HSSFColor.Aqua.Index;
        fieldHeaderStyle.WrapText = true;
        fieldHeaderStyle.FillPattern = FillPattern.SolidForeground;
        HSSFFont h2font = (HSSFFont)workbook.CreateFont();
        h2font.Boldweight = (short)FontBoldWeight.Bold;
        h2font.Color = HSSFColor.White.Index;
        h2font.FontHeightInPoints = 10;
        fieldHeaderStyle.SetFont(h2font);
        #endregion
        #region DetailsStyle
        DetailStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        DetailStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        DetailStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        DetailStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        DetailStyle.VerticalAlignment = VerticalAlignment.Center;
        DetailStyle.Alignment = HorizontalAlignment.Center;
        #endregion
        #region TotalFieldStyle
        totalStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        totalStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        totalStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        totalStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        totalStyle.FillPattern = FillPattern.SolidForeground;
        totalStyle.VerticalAlignment = VerticalAlignment.Center;
        totalStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
        HSSFFont h3font = (HSSFFont)workbook.CreateFont();
        h3font.Boldweight = (short)FontBoldWeight.Bold;
        h3font.FontHeightInPoints = 12;
        totalStyle.SetFont(h3font);
        #endregion
    }
    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)
    {
        BindDataToReport("Export", 0);
    }
    private void ExportToExcelHorizontal(DataSet dsEmp)
    {
        DataTable dtLevelName = new DataTable();
        HSSFWorkbook workbook = new HSSFWorkbook();
        ISheet sheet = workbook.CreateSheet("Monthly Attendance Report");
        #region objectCreation
        pageheaderFont = (HSSFFont)workbook.CreateFont();
        HSSFCellStyle empDetailStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        DetailStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        MainStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        fieldHeaderStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        totalStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        #endregion
        #region PageHeaderStyles
        pageheaderFont.Boldweight = (short)FontBoldWeight.Bold;
        pageheaderFont.Color = HSSFColor.Maroon.Index;
        pageheaderFont.FontHeightInPoints = 16;
        MainStyle.SetFont(pageheaderFont);
        MainStyle.Alignment = HorizontalAlignment.Center;
        #endregion
        #region EmpHeaderStyle
        empDetailStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        empDetailStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        empDetailStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        empDetailStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        empDetailStyle.Alignment = HorizontalAlignment.Center;
        empDetailStyle.VerticalAlignment = VerticalAlignment.Center;
        empDetailStyle.WrapText = true;
        empDetailStyle.FillPattern = FillPattern.SolidForeground;
        HSSFFont empheaderfont = (HSSFFont)workbook.CreateFont();
        empheaderfont.Boldweight = (short)FontBoldWeight.Bold;
        empheaderfont.Color = HSSFColor.Black.Index;
        empheaderfont.FontHeightInPoints = 12;
        empDetailStyle.SetFont(empheaderfont);
        #endregion
        #region fieldHeaderStyle
        fieldHeaderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        fieldHeaderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        fieldHeaderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        fieldHeaderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        fieldHeaderStyle.Alignment = HorizontalAlignment.Center;
        fieldHeaderStyle.VerticalAlignment = VerticalAlignment.Center;
        fieldHeaderStyle.FillForegroundColor = HSSFColor.Aqua.Index;
        fieldHeaderStyle.WrapText = true;
        fieldHeaderStyle.FillPattern = FillPattern.SolidForeground;
        HSSFFont h2font = (HSSFFont)workbook.CreateFont();
        h2font.Boldweight = (short)FontBoldWeight.Bold;
        h2font.Color = HSSFColor.White.Index;
        h2font.FontHeightInPoints = 10;
        fieldHeaderStyle.SetFont(h2font);
        #endregion
        #region DetailsStyle
        DetailStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        DetailStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        DetailStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        DetailStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        DetailStyle.VerticalAlignment = VerticalAlignment.Center;
        DetailStyle.Alignment = HorizontalAlignment.Center;
        #endregion
        #region TotalFieldStyle
        totalStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        totalStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        totalStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        totalStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        totalStyle.FillPattern = FillPattern.SolidForeground;
        totalStyle.VerticalAlignment = VerticalAlignment.Center;
        totalStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
        HSSFFont h3font = (HSSFFont)workbook.CreateFont();
        h3font.Boldweight = (short)FontBoldWeight.Bold;
        h3font.FontHeightInPoints = 12;
        totalStyle.SetFont(h3font);
        #endregion
        sheet.IsPrintGridlines = true;
        sheet.VerticallyCenter = true;
        int rowIndex = 0;
        NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
        if (Convert.ToInt16(Session["parentLevelId"]) != 0)
        {
            dtLevelName = rptEmp.getHeader(Convert.ToInt16(Session["parentLevelId"]));
            if (dtLevelName.Rows.Count > 0)
            {
                row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 21));
                row.GetCell(0).CellStyle = MainStyle;
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
            }
        }
        string NewTimeFormat = string.Empty;
        string TimeFormat = Convert.ToString(Session["ITimeFormat"]);
        if (TimeFormat == "HH:mm")
            NewTimeFormat = "HH:mm";
        else
            NewTimeFormat = "hh:mm tt";
        row.CreateCell(0).SetCellValue("Monthly Attendance report From - " + Convert.ToString(txtFromDate.Text) + " To - " + Convert.ToString(txtToDate.Text));
        row.GetCell(0).CellStyle = MainStyle;
        sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 21));
        rowIndex++;
        row = sheet.CreateRow(rowIndex);
        rowIndex++;
        strFromdate = txtFromDate.Text;
        strTodate = txtToDate.Text;
        gstrDateFormat = Convert.ToString(Session["MDateFormat"]);
        strFromdate = objCommon.DateConvertion(gstrDateFormat, strFromdate);
        DateTime dtFromDate = DateTime.Parse(strFromdate);
        int date = dtFromDate.Day;
        int i = 0;
        try
        {
            string strPre = string.Empty;
            foreach (DataRow DR in dsEmp.Tables[0].Rows)
            {
                #region BindData
                if (strPre == string.Empty || strPre != Convert.ToString(DR["EMPLOYEEID"]))
                {
                    row = sheet.CreateRow(rowIndex);
                    row.CreateCell(0).SetCellValue(i + 1);
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 0));
                    row.CreateCell(1).SetCellValue(Convert.ToString(DR["EMPLOYEEName"]));
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 4));
                    row.CreateCell(5).SetCellValue(Convert.ToString(DR["EMPLOYEEID"]));
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 5, 7));
                    row.CreateCell(8).SetCellValue(Convert.ToString(DR["Designation"]));
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 10));
                    //row.GetCell(1).CellStyle = empDetailStyle;
                    //row.GetCell(5).CellStyle = empDetailStyle;
                    //row.GetCell(8).CellStyle = empDetailStyle;
                    //row.GetCell(0).CellStyle = empDetailStyle;
                    rowIndex++;
                    fillCells(row, DR, " ", "Date", sheet, rowIndex++, DetailStyle, dtFromDate);
                    fillCells(row, DR, "Attn_Day", "Attendance", sheet, rowIndex++, DetailStyle, null);
                    fillCells(row, DR, "Shift_Day", "Shift", sheet, rowIndex++, DetailStyle, null);
                    fillCells(row, DR, "In_Day", "In Time", sheet, rowIndex++, DetailStyle, null);
                    fillCells(row, DR, "Out_Day", "Out Time", sheet, rowIndex++, DetailStyle, null);
                    //fillCells(row, DR, "Out_Day", "Break Out", sheet, rowIndex++, DetailStyle, null);
                    //fillCells(row, DR, "Out_Day", "Break In", sheet, rowIndex++, DetailStyle, null);
                    fillCells(row, DR, "TWH_Day", "THW", sheet, rowIndex++, DetailStyle, null);
                    fillCells(row, DR, "LateIn_Day", "*P Hrs", sheet, rowIndex++, DetailStyle, null);
                    rowIndex++;
                }
                else
                {
                    row = sheet.CreateRow(rowIndex);
                    row.CreateCell(0).SetCellValue(string.Empty);
                }
                i = i + 1;
                #endregion
            }
            ExportToExcel(workbook, "MonthlyAttendanceReport");
        }
        catch (Exception ex)
        {
            Message(ex.ToString());
        }
    }
    public void ExportToExcel(HSSFWorkbook workbook, string fileName)
    {
        using (MemoryStream memoryStream = new MemoryStream())
        {
            fileName = string.Format(fileName + "-{0:d}.xls", DateTime.Now).Replace("/", "-");
            workbook.Write(memoryStream);
            memoryStream.Flush();
            try
            {
                HttpResponse response = HttpContext.Current.Response;
                response.ClearContent();
                response.ClearHeaders();
                response.Buffer = true;
                response.ContentType = "application/vnd.ms-excel";
                response.AddHeader("Content-Length", memoryStream.Length.ToString());
                response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
                response.BinaryWrite(memoryStream.GetBuffer());
                response.Flush();
                response.End();
            }
            catch
            {
            }
        }
    }
    private static void fillCells(IRow row, DataRow DR, string fieldName, string firstcellValue, ISheet sheet, int rowIndex, HSSFCellStyle DetailStyle, DateTime? dt)
    {
        row = sheet.CreateRow(rowIndex);
        for (int i = 0; i <= 31; i++)
        {
            if (i == 0) row.CreateCell(i).SetCellValue(firstcellValue);
            else
            {
                if (firstcellValue == "Date")
                {
                    if (dt.HasValue)
                        row.CreateCell(i).SetCellValue(Convert.ToDateTime(dt).AddDays(i - 1).ToString("dd/MM/yyyy"));
                }
                else
                {
                    string field = fieldName + i;

                    if (firstcellValue == "In Time" || firstcellValue == "Out Time")
                    {
                        if (Convert.ToString(DR[field]) != "00:00")
                        {
                            row.CreateCell(i).SetCellValue(Convert.ToString(DR[field]));
                        }
                        else
                        {
                            row.CreateCell(i).SetCellValue("");
                        }
                    }
                    else
                    {
                        row.CreateCell(i).SetCellValue(Convert.ToString(DR[field]));
                    }
                }
            }
            //row.GetCell(i).CellStyle = DetailStyle;
            //sheet.AutoSizeColumn(i);
        }
    }
    protected void btnExport_Click(object sender, EventArgs e)
    {
        LinkButton lnkbtn = (LinkButton)sender;  //Called From  Grid Report Icon click / both excel and pdf
        DisableControls();
        ASPxGridViewExporter1.Landscape = true;
        ASPxGridViewExporter1.LeftMargin = 10;
        ASPxGridViewExporter1.RightMargin = 10;
        ASPxGridViewExporter1.ExportedRowType = DevExpress.Web.GridViewExportedRowType.All;
        ASPxGridViewExporter1.MaxColumnWidth = 400;
        //ASPxGridViewExporter1.GridViewID = "gvData";
        string saveAsFileName = string.Format("Monthly Attendance Report -{0:d}", DateTime.Now).Replace("/", "-").Replace(" ", "");
        ASPxGridViewExporter1.FileName = saveAsFileName;
        ASPxGridViewExporter1.PaperKind = System.Drawing.Printing.PaperKind.A3;
        //ASPxGridViewExporter1.PageHeader.Center = "Monthly Attendance report From - " + Convert.ToString(txtFromDate.Text) + " To - " + Convert.ToString(txtToDate.Text);
        switch (lnkbtn.CommandName)
        {
            case "excel":
                ASPxGridViewExporter1.WriteXlsxToResponse();
                break;
            case "pdf":
                ASPxGridViewExporter1.WritePdfToResponse();
                break;
            case "csv":
                ASPxGridViewExporter1.WriteCsvToResponse();
                break;
        }
    }
    #endregion
    protected void lnkbtnGrid_Click(object sender, EventArgs e)
    {
        BindDataToReport("View", 1);
    }
    protected void lnbtnClear_Click(object sender, EventArgs e)
    {
        gvData.DataSource = null;
        gvData.DataBind();
        Cache.Remove("MonthlyAttendanceVertical");
        //Cache["MonthlyAttendanceVertical"] = null;
        gvData.Visible = false;
        lnkbtnCollapse.Visible = false;
        lnkbtnExpand.Visible = false;
        lnkbtnpdf.Visible = false;
        lnktbnexcel.Visible = false;
        lblmanual.Visible = false;
        lnkbtnCSV.Visible = false;
        lnbtnClear.Visible = false;
        gvData.FilterExpression = "";
        btnCustWindow.Visible = false;
    }
    protected void gvData_HtmlDataCellPrepared(object sender, DevExpress.Web.ASPxGridViewTableDataCellEventArgs e)
    {
        //if (e.DataColumn.FieldName == "LATEIN")
        //    if (Convert.ToString(e.CellValue) != "00:00")
        //    {
        //        e.Cell.ForeColor = Color.White;
        //        e.Cell.BackColor = Color.Green;
        //    }
        //if (e.DataColumn.FieldName == "EARLYOUT")
        //    if (Convert.ToString(e.CellValue) != "00:00")
        //    {
        //        e.Cell.ForeColor = Color.White;
        //        e.Cell.BackColor = Color.Green;
        //    }
    }
    void ApplyLayout(int layoutIndex)
    {
        gvData.BeginUpdate();
        try
        {
            gvData.ClearSort();
            switch (layoutIndex)
            {
                case 0:
                    gvData.GroupBy(gvData.Columns["Month"]);
                    break;
                case 1:
                    gvData.GroupBy(gvData.Columns["Employee Id"]);
                    //gvData.GroupBy(gvData.Columns["Month"]);
                    break;
                case 2:
                    //gvData.GroupBy(gvData.Columns["Month"]);
                    gvData.GroupBy(gvData.Columns["Employee Id"]);
                    break;
                case 3:
                    gvData.GroupBy(gvData.Columns["Employee Id"]);
                    //gvData.GroupBy(gvData.Columns["Month"]);
                    gvData.GroupBy(gvData.Columns["Shift Name"]);
                    break;
                case 4:
                    //gvData.GroupBy(gvData.Columns["Month"]);
                    gvData.GroupBy(gvData.Columns["Employee Id"]);
                    gvData.GroupBy(gvData.Columns["Shift Name"]);
                    break;
            }
        }
        finally
        {
            gvData.EndUpdate();
        }
        gvData.ExpandAll();
    }

    protected void gvData_CustomCallback(object sender, DevExpress.Web.ASPxGridViewCustomCallbackEventArgs e)
    {
        ApplyLayout(Int32.Parse(e.Parameters));
    }

    protected void lnkbtnExpandCollapse_Click(object sender, EventArgs e)
    {
        if (gvData.SettingsBehavior.AutoExpandAllGroups == true)
        {
            gvData.SettingsBehavior.AutoExpandAllGroups = false;
        }
        else
        {
            gvData.SettingsBehavior.AutoExpandAllGroups = true;
        }
    }

    protected void lnkbtnTawaZunRpt_Click(object sender, EventArgs e)
    {
        try
        {
            Response.Redirect("TA_UI_Report_TawazunAttendanceDetails.aspx",true);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}
