using System;
using System.Data;
using System.Globalization;
using System.Resources;
using System.Text;
using System.Threading;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util;
using System.IO;
/*
 * Modified by      :   Manjula.P 
 * Modified on      :   28-Mar-2014
 * Descriptions     :   Adding Shift Allocation and Not shift allocation option : POA
 * */
public partial class Reports_TA_UI_Report_EmployeeShiftAllocation : 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();
    ResourceManager rm;
    cls_Common objCommon = new cls_Common();
    EmployeeEventData objEmployee = new EmployeeEventData();
    string strFromdate, gstrDateFormat, strTodate;
    //Cls_Connect cl = new Cls_Connect();
    CultureInfo ci;
    int intIndividualUser = 0;
    DataSet dsShiftAllocation = new DataSet();
    DataTable dtTempShiftAllocation = new DataTable();
    Boolean blnCheckDataExits = true;
    int cellCounts = 0;
    string SheetName, ReportName, HeaderName;
    #endregion
    #region PageLoad
    protected override void Page_Load(object sender, EventArgs e)
    {
        // session checking
        base.Page_Load(sender, e);
        //get the culture info to set the language
        if (!Page.IsPostBack)
        {
            base.CheckPages(0);
        }
        string source_Language;
        source_Language = Convert.ToString(Session["Session_ActiveLanguage"]);
        rm = new ResourceManager(source_Language, System.Reflection.Assembly.Load("App_GlobalResources"));
        ci = Thread.CurrentThread.CurrentCulture;
        site.SiteMapProvider = Convert.ToString(Session["SiteMapProvider"]);
        LoadStrings(ci);
        string strSetAlert = "";
        strSetAlert = strSetAlert + "<script language='javascript'>";
        strSetAlert = strSetAlert + "SetAlertMessages('" + rm.GetString("Alert_EnterDate", ci) + "','" + rm.GetString("Alert_EnterToDate", 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);
        //Set UserRights
        UserRights();
        if (!IsPostBack)
        {
            objCommon.menu("ShiftReports", "32");
            Hidden1.Value = Convert.ToString(Session["IDateFormat"]);
            // Bindings the employee category and type.
            BindingEmployeeCategoryandType();
            FillDesignation();
            if (Request.QueryString["Source"] != null)
            {
                if (Convert.ToString(Request.QueryString["Source"]) == "Email")
                {
                    divdateTime.Visible = false;
                    trView.Visible = false;
                    trSave.Visible = 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);
                DataSet ds = new DataSet();
                ds = rptEmp.GetEmailSchedulerFilterDetails(Convert.ToInt32(Request.QueryString["ScheduleId"]));
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        txtEmployeeId.Text = Convert.ToString(ds.Tables[0].Rows[0]["EmployeeID"]);
                        txtEmployeeName.Text = Convert.ToString(ds.Tables[0].Rows[0]["EmployeeName"]);
                        ddlEmpCatagory.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["EmployeeCategory"]);
                        ddlEmpType.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["EmployeeType"]);
                        ddlDesignation.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["DesignationID"]);
                        ddlGender.SelectedValue = Convert.ToString(ds.Tables[0].Rows[0]["Gender"]);
                        Session["EMailParentId"] = Convert.ToString(ds.Tables[0].Rows[0]["ParentID"]);
                        if (Convert.ToString(ds.Tables[1].Rows[0][0]) == "1")
                        {
                            rblReportType1.Checked = true;
                        }
                        else
                        {
                            rblReportType1.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];
                    }
                    txtEmployeeName.Text = Convert.ToString(strArray1[3]).Replace("~", ",");
                    txtEmployeeId.Text = Convert.ToString(strArray1[2]).Replace("~", ",");
                    ddlEmpCatagory.SelectedValue = Convert.ToString(strArray1[5]);
                    ddlEmpType.SelectedValue = Convert.ToString(strArray1[6]);
                    ddlDesignation.SelectedValue = Convert.ToString(strArray1[12]);
                    ddlGender.SelectedValue = Convert.ToString(strArray1[13]);
                    Session["EMailParentId"] = Convert.ToString(strArray1[10]);
                    if (Convert.ToString(Session["ReportTypeID"]) == "1")
                    {
                        rblReportType1.Checked = true;
                    }
                    else
                    {
                        rblReportType1.Checked = true;
                    }
                }
            }
            ddlDesignation.PreRender += LB_PreRender;
            txtFromDate.Text = (Convert.ToDateTime(objCommon.CurrentMonthStartDate())).ToString(Convert.ToString(Session["MDateFormat"]));
            txtToDate.Text = (Convert.ToDateTime(System.DateTime.Now.ToShortDateString())).ToString(Convert.ToString(Session["MDateFormat"]));
            if (Session["parentLevelId"] != null || Convert.ToInt32(Session["parentLevelId"]) != 0)
            {
                Session["parentLevelId"] = null;
            }
            rbCustom.Checked = true;
        }
        //Check whether the employee is Individual User
        intIndividualUser = objCheckUser.CheckIndividualUser(Convert.ToString(Session["UserGroupName"]));
        if (intIndividualUser >= 1)
        {
            PlaceHolderShiftAllocation.Controls.Clear();
            divEmployeeDetails.Visible = false;
            lblEmpSwipe.Visible = false;
            txtSwipeCard.Visible = false;
        }
        if (Convert.ToString(Page.MasterPageFile) == "TA_Popup_Report_MasterPage.master")
        {
            conHeader.Visible = false;
        }
    }
    protected void Page_PreInit(object sender, EventArgs e)
    {
        if (Request.QueryString["Source"] != null)
            this.Page.MasterPageFile = "TA_Popup_Report_MasterPage.master";
    }
    #endregion
    #region User Defined function
    /// <summary>
    /// Fills the designation in the drop down
    /// </summary>
    private void FillDesignation()
    {
        DataSet ds_designation = new DataSet();
        ds_designation = objEmployee.select_CommonDesignation();
        if (ds_designation != null)
        {
            if (ds_designation.Tables[0].Rows.Count > 0)
            {
                ddlDesignation.DataSource = ds_designation;
                ddlDesignation.DataTextField = "TitleName";
                ddlDesignation.DataValueField = "TitleId";
                ddlDesignation.DataBind();
            }
            ds_designation.Dispose();
            foreach (ListItem item in ddlDesignation.Items)
            {
                item.Attributes.Add("title", item.Text);
            }
        }
    }
    /// <summary>
    /// Bindings the employee category and type.
    /// </summary>
    private void BindingEmployeeCategoryandType()
    {
        // employee category binding
        DataSet dsCategory = new DataSet();
        dsCategory = objEmployee.SelectCategoryDetailsEmployee("");
        if (dsCategory != null)
        {
            if (dsCategory.Tables[0].Rows.Count > 0)
            {
                ddlEmpCatagory.DataSource = dsCategory;
                ddlEmpCatagory.DataTextField = "CategoryName";
                ddlEmpCatagory.DataValueField = "CategoryId";
                ddlEmpCatagory.DataBind();
            }
            dsCategory.Dispose();
            foreach (ListItem item in ddlEmpCatagory.Items)
            {
                item.Attributes.Add("title", item.Text);
            }
        }
        // employee type binding
        DataSet dsType = new DataSet();
        dsType = objEmployee.SelectTypeDetailsEmployee("");
        if (dsType != null)
        {
            if (dsType.Tables[0].Rows.Count > 0)
            {
                ddlEmpType.DataSource = dsType;
                ddlEmpType.DataTextField = "TypeName";
                ddlEmpType.DataValueField = "TypeId";
                ddlEmpType.DataBind();
            }
            dsType.Dispose();
            foreach (ListItem item in ddlEmpType.Items)
            {
                item.Attributes.Add("title", item.Text);
            }
        }
    }
    /// <summary>
    /// Validate the FromDate
    /// </summary>
    /// <param name="s"></param>
    /// <param name="e"></param>
    public void ValidateFromDate(object s, ServerValidateEventArgs e)
    {
        try
        {
            strFromdate = txtFromDate.Text;
            gstrDateFormat = Convert.ToString(Session["MDateFormat"]);
            //strCurrentDate = objCommon.DateConvertion(gstrDateFormat,DateTime.Today.Date.ToString());
            strFromdate = objCommon.DateConvertion(gstrDateFormat, strFromdate);
            //strTodate = objCommon.DateConvertion(gstrDateFormat, strTodate);
            DateTime dtCurrentDate = Convert.ToDateTime(DateTime.Today.Date);
            DateTime dtFromDate = DateTime.Parse(strFromdate);
            TimeSpan diff = dtFromDate.Subtract(dtCurrentDate);
            int day = diff.Days;
            if (day <= 0)
                e.IsValid = true;
            else
            {
                //base.Message("Enter Valid From Date"); 
                base.Message(rm.GetString("Alert_EnterValidFromDate", ci));
                e.IsValid = false;
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }
    }
    /// <summary>
    /// Validate the ToDate
    /// </summary>
    /// <param name="s"></param>
    /// <param name="e"></param>
    public void ValidateToDate(object s, ServerValidateEventArgs e)
    {
        try
        {
            gstrDateFormat = Convert.ToString(Session["MDateFormat"]);
            //strCurrentDate = objCommon.DateConvertion(gstrDateFormat, DateTime.Today.Date.ToString());
            strTodate = txtToDate.Text;
            strTodate = objCommon.DateConvertion(gstrDateFormat, strTodate);
            DateTime dtCurrentDate = Convert.ToDateTime(DateTime.Today.Date);
            DateTime dtFromDate = DateTime.Parse(strTodate);
            TimeSpan diff = dtFromDate.Subtract(dtCurrentDate);
            int day = diff.Days;
            int FromMonthvalue = Convert.ToDateTime(strFromdate).Month;
            int ToMonthvalue = Convert.ToDateTime(strTodate).Month;
            if (day <= 0)
                e.IsValid = true;
            else
            {
                base.Message(rm.GetString("Alert_EnterValidToDate", ci));
                e.IsValid = false;
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }
    }
    /// <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;
                if (Convert.ToString(Session["packageID"]) != "")
                    if (Convert.ToString(Session["packageID"]) == "158") /*Hotel Surguru -TimecheckV2.7*/
                    {
                        int FromMonthvalue = Convert.ToDateTime(strFromdate).Month;
                        int ToMonthvalue = Convert.ToDateTime(strTodate).Month;
                        if (ToMonthvalue != FromMonthvalue)
                        {
                            base.Message("FromDate and Todate Should be in Same Month");
                            e.IsValid = false;
                        }
                    }
            }
            else
            {
                //base.Message("Select valid date compare with Fromdate");
                base.Message(rm.GetString("Msg_SelectvaliddatecomparewithFromdate", ci));
                e.IsValid = false;
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }
    }
    #region NotUsed
    /// <summary>
    /// To get the details of Temporary Shift Allocation
    /// </summary>
    /// <param name="dtFrom"></param>
    /// <param name="dtTo"></param>
    /// <param name="employeeName"></param>
    /// <param name="levelId"></param>
    /// <returns></returns>
    //private DataTable TempShiftAllocation(string dtFrom, string dtTo, string employeeName, string levelId, string EmployeeId, int employeeCategory, int employeeType, string userGroupName, string gradeLevel, string strSwipeCard)
    //{
    //    //DataTable dtTempShiftAllocation= new DataTable();
    //    DataTable dtSift = new DataTable();
    //    try
    //    {
    //        // Get Data from Temporary Shift Allocation
    //        DataSet dsTempShiftAllocation = new DataSet();
    //        //Fill Data to Dataset dsTempShiftAllocation
    //        dsTempShiftAllocation = rptEmp.SelectTempShiftAllocationReport(dtFrom, dtTo, employeeName, levelId, EmployeeId, employeeCategory, employeeType, userGroupName, gradeLevel, strSwipeCard, Convert.ToInt32(Session["parentLevelId"]));
    //        //Check the Empty table 
    //        if (dsTempShiftAllocation.Tables["Table"] == null || dsTempShiftAllocation.Tables["Table"].Rows.Count == 0)
    //        {
    //            //string strMsg;
    //            //strMsg = "<script language='javascript'>";
    //            //strMsg = strMsg + "alert('There is no record ');";
    //            //strMsg = strMsg + "</script>";
    //            //Page.RegisterStartupScript("Display Message", strMsg);
    //            dtSift = null;
    //        }
    //        else
    //        {
    //            //start
    //            strFromdate = txtFromDate.Text;
    //            strFromdate = objCommon.DateConvertion(gstrDateFormat, strFromdate);
    //            int fromDay = DateTime.Parse(strFromdate).Day;
    //            int fromMonth = DateTime.Parse(strFromdate).Month;
    //            int fromYear = DateTime.Parse(strFromdate).Year;
    //            strTodate = txtToDate.Text;
    //            strTodate = objCommon.DateConvertion(gstrDateFormat, strTodate);
    //            int toDay = DateTime.Parse(strTodate).Day;
    //            int toMonth = DateTime.Parse(strTodate).Month;
    //            int toYear = DateTime.Parse(strTodate).Year;
    //            //int chkFlag = 0;
    //            // int endFlag = 0;
    //            int j = 0;
    //            // DataTable dtSift = new DataTable();
    //            DataTable dtShiftShortName = new DataTable();
    //            //The following line commented due to Multi-Database usage
    //            //cl.fillDT("Proc_TA_GetShiftShortName", dtShiftShortName);
    //            dtShiftShortName = rptEmp.GetShiftShortName();
    //            for (int k = 0; k <= dsTempShiftAllocation.Tables[0].Columns.Count - 4; k++)
    //            {
    //                if (k == 0)
    //                {
    //                    dtSift.Columns.Add("Employee Name");
    //                }
    //                else if (k == 1)
    //                {
    //                    dtSift.Columns.Add("Employee Id");
    //                }
    //                else if (k == 2)
    //                {
    //                    dtSift.Columns.Add("Year");
    //                }
    //                else if (k == 3)
    //                {
    //                    dtSift.Columns.Add("Month");
    //                }
    //                else
    //                {
    //                    dtSift.Columns.Add((k - 3).ToString());
    //                }
    //            }
    //            foreach (DataRow dr in dsTempShiftAllocation.Tables[0].Rows)
    //            {
    //                j = j + 1;
    //                dtSift.Rows.Add();
    //                for (int i = 0; i <= dsTempShiftAllocation.Tables[0].Columns.Count - 4; i++)
    //                {
    //                    // Response.Write(chkFlag == 0 && i == 4);
    //                    if (i == 4 && Convert.ToInt32(dr["Month"]) == fromMonth)
    //                    {
    //                        if (Convert.ToInt32(dr["Year"]) != fromYear)
    //                        {
    //                        }
    //                        else
    //                        {
    //                            i = fromDay + 3;
    //                            //chkFlag = 1;
    //                        }
    //                    }
    //                    //Response.Write("i = " + i);
    //                    // Response.Write(dr[i].ToString() + "<br>");
    //                    if (i > 3)
    //                    {
    //                        foreach (DataRow dRow in dtShiftShortName.Rows)
    //                        {
    //                            if (dr[i].ToString() == dRow["Shift_Id"].ToString())
    //                            {
    //                                dtSift.Rows[j - 1][i] = dRow["Shift_ShortName"];
    //                            }
    //                            else if (dr[i].ToString() == "-1")
    //                            {
    //                                dtSift.Rows[j - 1][i] = "Off";
    //                            }
    //                        }
    //                    }
    //                    else if (i == 3)
    //                    {
    //                        DateTimeFormatInfo dtfi = new CultureInfo("en-US", true).DateTimeFormat;
    //                        string temp = dtfi.GetMonthName(Convert.ToInt32(dr[i].ToString()));
    //                        dtSift.Rows[j - 1][i] = temp;
    //                    }
    //                    else
    //                    {
    //                        dtSift.Rows[j - 1][i] = dr[i];
    //                    }
    //                    //Response.Write((toDay + 3 == i && j == dsShiftAllocation.Tables[0].Rows.Count - 1));
    //                    //if (toDay + 3 == i && j == dsTempShiftAllocation.Tables[0].Rows.Count)
    //                    if (toDay + 3 == i && Convert.ToInt32(dr["Month"]) == toMonth && Convert.ToInt32(dr["Year"]) == toYear)
    //                    {
    //                        i = dsTempShiftAllocation.Tables[0].Columns.Count;
    //                    }
    //                }
    //            }
    //        }
    //    }
    //    catch (Exception ex)
    //    {
    //        Response.Write(ex.Message);
    //    }
    //    return dtSift;
    //}
    #endregion
    /// <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);
        lblEmployeeShiftAllocationReport.Text = rm.GetString("Reports_EmployeeShiftAllocation_EmployeeShiftAllocationReport", 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);
        reqToDate.ErrorMessage = rm.GetString("Alert_PleaseselectToDate", ci);
        reqFromDate.ErrorMessage = rm.GetString("Alert_PleaseselectFromDate", ci);
        ddlEmpCatagory.Items[0].Text = rm.GetString("Select", ci);
        ddlEmpType.Items[0].Text = rm.GetString("Select", ci);
        lblDesignation.Text = rm.GetString("EmployeeMaster_Designation", ci);
        lblGender.Text = rm.GetString("EmployeeMaster_Gender", ci);
        ddlGender.Items[0].Text = rm.GetString("Caption_Both", ci);
        ddlGender.Items[1].Text = rm.GetString("rbtn_EmployeeMaster_Male", ci);
        ddlGender.Items[2].Text = rm.GetString("rbtn_EmployeeMaster_Female", ci);
        ddlDesignation.Items[0].Text = rm.GetString("ExportData_All", ci);
        //Added by Manjula.P on 28-Mar-2014 
        rblReportType0.Text = rm.GetString("Label_ShiftAllocated", ci);
        rblReportType1.Text = rm.GetString("Alert_Shiftnotallocated", ci);
        lblReportType.Text = rm.GetString("EmployeeInOutReportType", ci);
        btnSave.Text = "&nbsp;&nbsp;" + rm.GetString("btn_Save", ci);
        btnBack.Text = "&nbsp;&nbsp;" + rm.GetString("btn_Cancel", ci);
        lblDateFilter.Text = rm.GetString("lbl_DateFilter", ci);
        btn_Export.Text = "&nbsp;&nbsp;" + rm.GetString("btn_ExporttoExcel", ci);
        rbCustom.Text = rm.GetString("rb_Custom", ci);
        rbpreviousMonth.Text = rm.GetString("rb_PreviousMonth", ci);
        rbpreviousweek.Text = rm.GetString("rb_PreviousWeek", ci);
        rbThisMonth.Text = rm.GetString("rb_ThisMonth", ci);
        rbThisweek.Text = rm.GetString("rb_ThisWeek", ci);
    }
    /// <summary>
    /// To check rights for the loggedin user
    /// </summary>
    private void UserRights()
    {
        ds_Checkuser = objCheckUser.checkRights(32, Convert.ToInt32(Session["userGroup_Id"]), "viewPrv");
        if (ds_Checkuser.Tables[0].Rows.Count > 0)
        {
            if (ds_Checkuser.Tables[0].Rows[0]["viewPrv"] == DBNull.Value || Convert.ToInt32(ds_Checkuser.Tables[0].Rows[0]["viewPrv"]) == 1)
            {
                btn_View.Visible = true;
            }
            else
            {
                btn_View.Visible = false;
            }
        }
        else
        {
            Response.Redirect("ReportsLandingPage.aspx");
        }
    }
    /// <summary>
    /// To apply tooltip for the designation in the drop down
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void LB_PreRender(object sender, System.EventArgs e)
    {
        foreach (ListItem item in ddlDesignation.Items)
        {
            item.Attributes.Add("title", item.Text);
        }
    }
    #endregion
    #region Button Event
    /// <summary>
    /// Load the Shift Allocation report
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btn_View_Click(object sender, EventArgs e)
    {
        try
        {
            if (Page.IsValid == true)
            {
                gstrDateFormat = Convert.ToString(Session["MDateFormat"]);
                string dtFrom = "", dtTo = "";
                if (txtFromDate.Text != "")
                {
                    strFromdate = txtFromDate.Text;
                    strFromdate = objCommon.DateConvertion(gstrDateFormat, strFromdate);
                    dtFrom = strFromdate;
                }
                if (txtToDate.Text != "")
                {
                    strTodate = txtToDate.Text;
                    strTodate = objCommon.DateConvertion(gstrDateFormat, strTodate);
                    dtTo = strTodate;
                }
                int shft = 0;
                string Level = string.Empty;
                string employeeName = string.Empty;
                string empName = string.Empty;
                string empId = string.Empty;
                if (intIndividualUser >= 1)
                {
                    empName = string.Empty;
                    shft = 0;
                    Level = string.Empty;
                    empId = Convert.ToString(Session["EmployeeCodeId"]);
                }
                else
                {
                    empName = Convert.ToString(txtEmployeeName.Text);
                    empId = Convert.ToString(txtEmployeeId.Text.Trim());
                    employeeName = this.txtEmployeeName.Text.Replace("'", "''");
                    Level = rptcommon.getNodes(Convert.ToInt32(Session["parentLevelId"]), Convert.ToString(Session["LevelRights"]));
                }
                //Added by Manjula.P on 28-Mar-2014 for getting report type dynamically
                string strReportName = "EmployeeShiftAllocation";
                if (hdnReporType.Value == "1")
                    strReportName = "ShiftNotAllocated";
                //**************************************************************
                //Redirect the page to the report viewer
                string strScript = "";
                strScript = strScript + "<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>";
                strScript = strScript + "<meta http-equiv='X-UA-Compatible' content='IE=edge' />";
                strScript = strScript + "<script language='javascript'>";
                //Comemnted below calling method and added after this line for calling fun with dynamic reportname
                //strScript = strScript + "window.open('TA_UI_Report_ReportViewer.aspx?ReportName=EmployeeShiftAllocation&dtFrom=" + dtFrom + "&dtTo=" + dtTo + "&empid=" + empId + "&shft=" + shft + "&EmpName=" + employeeName + "&Level=" + Level + "&EmpCategory=" + Convert.ToInt32(ddlEmpCatagory.SelectedValue) + "&EmpType=" + Convert.ToInt32(ddlEmpType.SelectedValue) + "&ParentLevelId=" + Convert.ToInt32(Session["parentLevelId"]).ToString() + "&UserGroupName=" + Convert.ToString(Session["UserGroupName"]) + "&GradeLevel=" + Convert.ToString(Session["GradeLevel"]) + "&SwipeCard=" + txtSwipeCard.Text.ToString().Trim().Replace("'", "''") + "&Designation=" + Convert.ToInt32(ddlDesignation.SelectedValue) + "&Gender=" + ddlGender.SelectedValue + "','','left=10px, top=60px, width=1000px, height=600px, scrollbars=yes, status=no, resizable=yes');";
                //strScript = strScript + "window.open('TA_UI_Report_ReportViewer.aspx?ReportName=EmployeeShiftAllocation&dtFrom=" + dtFrom + "&dtTo=" + dtTo + "&empid=" + empId + "&shft=" + shft + "&EmpName=" + employeeName + "&Level=" + Level + "&EmpCategory=" + Convert.ToInt32(ddlEmpCatagory.SelectedValue) + "&EmpType=" + Convert.ToInt32(ddlEmpType.SelectedValue) + "&ParentLevelId=" + Convert.ToInt32(Session["parentLevelId"]).ToString() + "&UserGroupName=" + Convert.ToString(Session["UserGroupName"]) + "&GradeLevel=" + Convert.ToString(Session["GradeLevel"]) + "&SwipeCard=" + txtSwipeCard.Text.ToString().Trim().Replace("'", "''") + "&Designation=" + Convert.ToInt32(ddlDesignation.SelectedValue) + "&Gender=" + ddlGender.SelectedValue + "','','left=10px, top=60px, width=1000px, height=600px, scrollbars=yes, status=no, resizable=yes');";
                strScript = strScript + "window.open('TA_UI_Report_ReportViewer.aspx?ReportName=" + strReportName + "&dtFrom=" + dtFrom + "&dtTo=" + dtTo + "&empid=" + empId + "&shft=" + shft + "&EmpName=" + employeeName + "&Level=" + Level + "&EmpCategory=" + Convert.ToInt32(ddlEmpCatagory.SelectedValue) + "&EmpType=" + Convert.ToInt32(ddlEmpType.SelectedValue) + "&ParentLevelId=" + Convert.ToInt32(Session["parentLevelId"]) + "&UserGroupName=" + Convert.ToString(Session["UserGroupName"]) + "&GradeLevel=" + Convert.ToString(Session["GradeLevel"]) + "&SwipeCard=" + Convert.ToString(txtSwipeCard.Text).Trim().Replace("'", "''") + "&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.Message);
        }
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
            if (Page.IsValid == true)
            {
                string employeeName = string.Empty;
                string empId = string.Empty;
                string Level = string.Empty;
                if (intIndividualUser >= 1)
                {
                    employeeName = string.Empty;
                    Level = string.Empty;
                    empId = Convert.ToString(Session["EmployeeCodeId"]);
                }
                else
                {
                    empId = Convert.ToString(txtEmployeeId.Text.Trim());
                    employeeName = Convert.ToString(txtEmployeeName.Text).Replace("'", "''");
                    Level = rptcommon.getNodes(Convert.ToInt32(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("@fromDat=" + " Duration_From " + ",");
                strFilters.Append("@ToDat=" + " Duration_To " + ",");
                strFilters.Append("@EmployeeId=" + empId + ",");
                //strFilters.Append("@shift=" + ddlShift.SelectedValue + ",");
                strFilters.Append("@EmployeeName=" + employeeName + ",");
                strFilters.Append("@LevelID=" + Level + ",");
                strFilters.Append("@EmployeeCategory=" + ddlEmpCatagory.SelectedValue + ",");
                strFilters.Append("@EmployeeType=" + ddlEmpType.SelectedValue + ",");
                strFilters.Append("@UserGroupName=" + Convert.ToString(Session["UserGroupName"]) + ",");
                strFilters.Append("@GradeLevel=" + Convert.ToString(Session["GradeLevel"]) + ",");
                strFilters.Append("@EmpSwipeCard=" + txtSwipeCard.Text.ToString().Trim().Replace("'", "''") + ",");
                strFilters.Append("@ParentId=" + Convert.ToInt32(Session["parentLevelId"]).ToString() + ",");
                strFilters.Append("@DesignationID=" + ddlDesignation.SelectedValue + ",");
                strFilters.Append("@Religion=0,");
                strFilters.Append("@Gender=" + ddlGender.SelectedValue + ",");
                strFilters.Append("@EmpAutoId=0");
                Session["hdnReportFilters"] = string.Empty;
                Session["hdnReportFilters"] = strFilters;
                Session["ReportTypeID"] = (rblReportType0.Checked == true) ? 0 : 1;
                string reportType = "";
                reportType = (rblReportType0.Checked == true) ? "0" : "1";
                string strScript = "<script language=\"javascript\">";
                strScript = strScript + "{window.top.parent.document.getElementById('ctl00_BodyContentPH_hdnFilters').value='" + strFilters + "'; window.top.parent.document.getElementById('ctl00_BodyContentPH_hdnCompanyId').value='" + Convert.ToInt32(Session["parentLevelId"]).ToString() + "';window.top.parent.document.getElementById('ctl00_BodyContentPH_hdnReportType').value='" + reportType + "';}";
                //strScript = strScript + "self.close();";
                strScript = strScript + "parent.$('#myModalReport').modal('hide');";
                strScript = strScript + "</script>";
                Response.Write(strScript);
            }
        }
        catch
        {
            //
        }
    }
    protected void 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)
    {
        bool blnNormal;
        try
        {
            DataSet dsEmp = new DataSet();
            string strEmpAutoId = "0";
            if (Page.IsValid == true)
            {
                if (Session["ReportsBasedOn"] != null)
                {
                    if (Convert.ToString(Session["ReportsBasedOn"]) == "False")
                    {
                        strEmpAutoId = "0";
                    }
                    else
                    {
                        strEmpAutoId = Convert.ToString(Session["EmpAutoID"]);
                    }
                }
                int shft;
                string dtFrom = "", dtTo = "";
                if (txtFromDate.Text != "")
                    dtFrom = strFromdate;
                if (txtToDate.Text != "")
                    dtTo = strTodate;
                string empName = "", empId = "";
                string employeeName = string.Empty;
                string Level = string.Empty;
                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.ToInt32(Session["parentLevelId"]), Convert.ToString(Session["LevelRights"]));
                }
                if (rblReportType0.Checked == true)
                {
                    dsShiftAllocation = rptEmp.SelectShiftAllocationReport(dtFrom, dtTo, employeeName, Level, empId, Convert.ToInt32(ddlEmpCatagory.SelectedValue), Convert.ToInt32(ddlEmpType.SelectedValue), Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), Convert.ToString(txtSwipeCard.Text).Trim().Replace("'", "''"), Convert.ToInt32(Session["parentLevelId"]), Convert.ToInt32(ddlDesignation.SelectedValue), ddlGender.SelectedValue, 0, Convert.ToInt64(strEmpAutoId));
                    // Call to Temporary Shift Allocation function, it returns the data as DataTable
                    dtTempShiftAllocation = TempShiftAllocation(dtFrom, dtTo, employeeName, Level, empId, Convert.ToInt32(ddlEmpCatagory.SelectedValue), Convert.ToInt32(ddlEmpType.SelectedValue), Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), Convert.ToString(txtSwipeCard.Text).Trim().Replace("'", "''"), Convert.ToInt32(Session["parentLevelId"]), Convert.ToInt32(ddlDesignation.SelectedValue), ddlGender.SelectedValue);
                    DataTable dtSift = new DataTable();
                    //Check the Empty table                     
                    if ((dsShiftAllocation.Tables["Table"] == null || dsShiftAllocation.Tables["Table"].Rows.Count == 0) && (dtTempShiftAllocation == null || dtTempShiftAllocation.Rows.Count == 0))
                    {
                        blnCheckDataExits = false;
                        string strMsg;
                        strMsg = "<script language='javascript'>";
                        strMsg = strMsg + "alert('" + rm.GetString("Alert_Thereisnorecord", ci) + "');window.close();";
                        strMsg = strMsg + "</script>";
                        Page.RegisterStartupScript("Display Message", strMsg);
                        return;
                    }
                    else if ((dsShiftAllocation.Tables["Table"] == null || dsShiftAllocation.Tables["Table"].Rows.Count == 0) && (dtTempShiftAllocation != null && dtTempShiftAllocation.Rows.Count != 0))
                    {
                        dtSift = dtTempShiftAllocation;
                    }
                    else
                    {
                        int fromDay = 0;
                        int fromMonth = 0;
                        int fromYear = 0;
                        int toMonth = 0;
                        int toDay = 0;
                        int toYear = 0;
                        DataTable dtmonthdayYear = new DataTable();
                        dtmonthdayYear = rptEmp.GetDateMonthYear(dtFrom, dtTo);
                        if (dtmonthdayYear.Rows.Count > 0)
                        {
                            fromDay = Convert.ToInt32(dtmonthdayYear.Rows[0][2]);
                            fromMonth = Convert.ToInt32(dtmonthdayYear.Rows[0][1]);
                            fromYear = Convert.ToInt32(dtmonthdayYear.Rows[0][0]);
                            toMonth = Convert.ToInt32(dtmonthdayYear.Rows[1][1]);
                            toDay = Convert.ToInt32(dtmonthdayYear.Rows[1][2]);
                            toYear = Convert.ToInt32(dtmonthdayYear.Rows[1][0]);
                        }
                        int j = 0;
                        DataTable dtShiftShortName = new DataTable();
                        dtShiftShortName = rptEmp.GetShiftShortNameWithOutTime();
                        dtSift.Columns.Add("Employee Name");
                        dtSift.Columns.Add("Employee Id");
                        dtSift.Columns.Add("Year");
                        dtSift.Columns.Add("Month");
                        dtSift.Columns.Add("1");
                        dtSift.Columns.Add("2");
                        dtSift.Columns.Add("3");
                        dtSift.Columns.Add("4");
                        dtSift.Columns.Add("5");
                        dtSift.Columns.Add("6");
                        dtSift.Columns.Add("7");
                        dtSift.Columns.Add("8");
                        dtSift.Columns.Add("9");
                        dtSift.Columns.Add("10");
                        dtSift.Columns.Add("11");
                        dtSift.Columns.Add("12");
                        dtSift.Columns.Add("13");
                        dtSift.Columns.Add("14");
                        dtSift.Columns.Add("15");
                        dtSift.Columns.Add("16");
                        dtSift.Columns.Add("17");
                        dtSift.Columns.Add("18");
                        dtSift.Columns.Add("19");
                        dtSift.Columns.Add("20");
                        dtSift.Columns.Add("21");
                        dtSift.Columns.Add("22");
                        dtSift.Columns.Add("23");
                        dtSift.Columns.Add("24");
                        dtSift.Columns.Add("25");
                        dtSift.Columns.Add("26");
                        dtSift.Columns.Add("27");
                        dtSift.Columns.Add("28");
                        dtSift.Columns.Add("29");
                        dtSift.Columns.Add("30");
                        dtSift.Columns.Add("31");
                        if (dsShiftAllocation.Tables[0].Columns.Contains("emp_id"))
                            dsShiftAllocation.Tables[0].Columns.Remove("emp_id");
                        if (dsShiftAllocation.Tables[0].Columns.Contains("TimeStamp"))
                            dsShiftAllocation.Tables[0].Columns.Remove("TimeStamp");
                        if (dsShiftAllocation.Tables[0].Columns.Contains("DateShift"))
                            dsShiftAllocation.Tables[0].Columns.Remove("DateShift");
                        if (dsShiftAllocation.Tables[0].Columns.Contains("employee_id1"))
                            dsShiftAllocation.Tables[0].Columns.Remove("employee_id1");
                        if (dsShiftAllocation.Tables[0].Columns.Contains("TitleName"))
                            dsShiftAllocation.Tables[0].Columns.Remove("TitleName");
                        if (dsShiftAllocation.Tables[0].Columns.Contains("Gender"))
                            dsShiftAllocation.Tables[0].Columns.Remove("Gender");
                        foreach (DataRow dr in dsShiftAllocation.Tables[0].Rows)
                        {
                            j = j + 1;
                            dtSift.Rows.Add();
                            for (int i = 0; i < dsShiftAllocation.Tables[0].Columns.Count; i++)
                            {
                                switch (i)
                                {
                                    case 0:
                                    case 1:
                                    case 2:
                                        dtSift.Rows[j - 1][i] = dr[i];
                                        break;
                                    case 3:
                                        DateTimeFormatInfo dtfi = new CultureInfo("en-US", true).DateTimeFormat;
                                        string temp = dtfi.GetMonthName(Convert.ToInt32(dr[i]));
                                        dtSift.Rows[j - 1][i] = temp;
                                        break;
                                    default:
                                        foreach (DataRow dRow in dtShiftShortName.Rows)
                                        {
                                            if (Convert.ToString(dr[i]) == Convert.ToString(dRow["Shift_Id"]))
                                            {
                                                dtSift.Rows[j - 1][i] = dRow["Shift_ShortName"];
                                                break;
                                            }
                                            else if (Convert.ToString(dr[i]) == "-1")
                                            {
                                                dtSift.Rows[j - 1][i] = rm.GetString("lbl_Off", ci);
                                                break;
                                            }
                                        }
                                        break;
                                }
                            }
                        }
                        //Check Temporary shift Exists or not 
                        Boolean boolTempShiftExists = false;
                        if (dtTempShiftAllocation != null)
                        {
                            if (dtTempShiftAllocation.Columns.Contains("Gender"))
                                dtTempShiftAllocation.Columns.Remove("Gender");
                            foreach (DataRow dRowTempShift in dtTempShiftAllocation.Rows)
                            {
                                boolTempShiftExists = false;
                                foreach (DataRow dRowPermanentShift in dtSift.Rows)
                                {
                                    if (Convert.ToString(dRowTempShift["Employee Id"]) == Convert.ToString(dRowPermanentShift["Employee Id"]) && Convert.ToString(dRowTempShift["Year"]) == Convert.ToString(dRowPermanentShift["Year"]) && Convert.ToString(dRowTempShift["Month"]) == Convert.ToString(dRowPermanentShift["Month"]))
                                    {
                                        boolTempShiftExists = true;
                                        for (int i = 0; i < dtTempShiftAllocation.Columns.Count; i++)
                                        {
                                            if (i > 3)
                                            {
                                                if (Convert.ToString(dRowTempShift[i]) == "0" || Convert.ToString(dRowTempShift[i]) == "")
                                                {
                                                }
                                                else
                                                {
                                                    dRowPermanentShift[i] = dRowTempShift[i];
                                                }
                                            }
                                        }
                                    }
                                }
                                if (boolTempShiftExists == false)
                                {
                                    DataRow dRowTemp;
                                    dRowTemp = dRowTempShift;
                                    dtSift.ImportRow(dRowTemp);
                                }
                            }
                        }
                    }
                    if (dtSift == null || dtSift.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
                    {
                        cellCounts = 36;
                        SheetName = "Shift Allocation";
                        ReportName = "ShiftAllocationReport";
                        DataSet dsShifts = new DataSet();
                        dsShifts.Tables.Add(dtSift);
                        Session["Export_ExcelData"] = dsShifts;
                        Response.Redirect("Excel_Export.aspx?ReportName=" + SheetName + "&FrmDate=" + Convert.ToString(txtFromDate.Text) + "&toDate=" + Convert.ToString(txtToDate.Text) + "");
                    }
                }
                else
                {
                    DataSet dsShiftNotAllocated = new DataSet();
                    dsShiftNotAllocated = rptEmp.SelectNotAllocationReport(dtFrom, dtTo, employeeName, Level, empId, Convert.ToInt32(ddlEmpCatagory.SelectedValue), Convert.ToInt32(ddlEmpType.SelectedValue), Convert.ToString(Session["UserGroupName"]), Convert.ToString(Session["GradeLevel"]), Convert.ToString(txtSwipeCard.Text).Trim().Replace("'", "''"), Convert.ToInt32(Session["parentLevelId"]), Convert.ToInt32(ddlDesignation.SelectedValue), ddlGender.SelectedValue, Convert.ToInt64(strEmpAutoId));
                    if (dsShiftNotAllocated == null || dsShiftNotAllocated.Tables[0].Rows.Count == 0)
                    {
                        string strMsg;
                        strMsg = "<script language='javascript'>";
                        strMsg = strMsg + "alert('" + rm.GetString("Alert_Thereisnorecord", ci) + "');window.close();";
                        strMsg = strMsg + "</script>";
                        Page.RegisterStartupScript("Display Message", strMsg);
                        return;
                    }
                    else
                    {
                        cellCounts = 6;
                        SheetName = "Shift Not Allocated";
                        ReportName = "ShiftNotAllocatedReport";
                        Session["Export_ExcelData"] = dsShiftNotAllocated;
                        Response.Redirect("Excel_Export.aspx?ReportName=" + SheetName + "&FrmDate=" + Convert.ToString(txtFromDate.Text) + "&toDate=" + Convert.ToString(txtToDate.Text) + "");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Message(ex.ToString());
        }
    }
    private DataTable TempShiftAllocation(string dtFrom, string dtTo, string employeeName, string levelId, string EmployeeId, int employeeCategory, int employeeType, string userGroupName, string gradeLevel, string strSwipeCard, int ParentId, int DesignationId, string Gender)
    {
        DataTable dtSift = new DataTable();
        try
        {
            string strEmpAutoId = "0";
            if (Session["ReportsBasedOn"] != null)
            {
                if (Convert.ToString(Session["ReportsBasedOn"]) == "False")
                {
                    strEmpAutoId = "0";
                }
                else
                {
                    strEmpAutoId = Convert.ToString(Session["EmpAutoID"]);
                }
            }
            // Get Data from Temporary Shift Allocation
            DataSet dsTempShiftAllocation = new DataSet();
            //Fill Data to Dataset dsTempShiftAllocation
            dsTempShiftAllocation = rptEmp.SelectTempShiftAllocationReport(dtFrom, dtTo, employeeName, levelId, EmployeeId, employeeCategory, employeeType, Convert.ToString(Session["UserGroupName"]), gradeLevel, strSwipeCard, ParentId, DesignationId, Gender, 1, Convert.ToInt64(strEmpAutoId));
            //Check the Empty table 
            if (dsTempShiftAllocation.Tables["Table"] == null || dsTempShiftAllocation.Tables["Table"].Rows.Count == 0)
            {
                dtSift = null;
            }
            else
            {
                int fromDay = DateTime.Parse(dtFrom).Day;
                int fromMonth = DateTime.Parse(dtFrom).Month;
                int fromYear = DateTime.Parse(dtFrom).Year;
                int toDay = DateTime.Parse(dtTo).Day;
                int toMonth = DateTime.Parse(dtTo).Month;
                int toYear = DateTime.Parse(dtTo).Year;
                int j = 0;
                DataTable dtShiftShortName = new DataTable();
                dtShiftShortName = rptEmp.GetShiftShortNameWithOutTime();
                for (int k = 0; k <= dsTempShiftAllocation.Tables[0].Columns.Count - 5; k++)
                {
                    if (k == 0)
                    {
                        dtSift.Columns.Add("Employee Name");
                    }
                    else if (k == 1)
                    {
                        dtSift.Columns.Add("Employee Id");
                    }
                    else if (k == 2)
                    {
                        dtSift.Columns.Add("Year");
                    }
                    else if (k == 3)
                    {
                        dtSift.Columns.Add("Month");
                    }
                    else if (k == dsTempShiftAllocation.Tables[0].Columns.Count - 5)
                    {
                        dtSift.Columns.Add("Gender");
                    }
                    else
                    {
                        dtSift.Columns.Add(Convert.ToString((k - 3)));
                    }
                }
                foreach (DataRow dr in dsTempShiftAllocation.Tables[0].Rows)
                {
                    j = j + 1;
                    dtSift.Rows.Add();
                    for (int i = 0; i <= dsTempShiftAllocation.Tables[0].Columns.Count - 5; i++)
                    {
                        if (i == 4 && Convert.ToInt32(dr["Month"]) == fromMonth)
                        {
                            if (Convert.ToInt32(dr["Year"]) != fromYear)
                            {
                            }
                            else
                            {
                                i = fromDay + 3;
                            }
                        }
                        if (i > 3 && i < dsTempShiftAllocation.Tables[0].Columns.Count - 5)
                        {
                            foreach (DataRow dRow in dtShiftShortName.Rows)
                            {
                                if (Convert.ToString(dr[i]) == Convert.ToString(dRow["Shift_Id"]))
                                {
                                    dtSift.Rows[j - 1][i] = dRow["Shift_ShortName"];
                                    break;
                                }
                                else if (Convert.ToString(dr[i]) == "-1")
                                {
                                    dtSift.Rows[j - 1][i] = rm.GetString("lbl_Off", ci);
                                    break;
                                }
                            }
                        }
                        else if (i == 3)
                        {
                            DateTimeFormatInfo dtfi = new CultureInfo("en-US", true).DateTimeFormat;
                            string temp = dtfi.GetMonthName(Convert.ToInt32(dr[i]));
                            dtSift.Rows[j - 1][i] = temp;
                        }
                        else if (i == dsTempShiftAllocation.Tables[0].Columns.Count - 5)
                        {
                            dtSift.Rows[j - 1][i] = dr["Gender"];
                        }
                        else
                        {
                            dtSift.Rows[j - 1][i] = dr[i];
                        }
                        if (toDay + 3 == i && Convert.ToInt32(dr["Month"]) == toMonth && Convert.ToInt32(dr["Year"]) == toYear)
                        {
                            i = dsTempShiftAllocation.Tables[0].Columns.Count - 6;
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Message(ex.ToString());
        }
        return dtSift;
    }
    #endregion
    #region Export Excel
    protected void ExportExcel(DataTable dtShift)
    {
        DataTable dtLevelName = new DataTable();
        if (Convert.ToInt32(Session["parentLevelId"]) != 0)
        {
            dtLevelName = rptEmp.getHeader(Convert.ToInt32(Session["parentLevelId"]));
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        ISheet sheet = workbook.CreateSheet(SheetName);
        int rowIndex = 0;
        NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
        HSSFFont hFont = (HSSFFont)workbook.CreateFont();
        hFont.Boldweight = (short)FontBoldWeight.Bold;
        hFont.Color = HSSFColor.Maroon.Index;
        hFont.FontHeightInPoints = 10;
        HSSFCellStyle hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        hStyle.SetFont(hFont);
        hStyle.FillForegroundColor = IndexedColors.White.Index;
        hStyle.FillPattern = FillPattern.SolidForeground;
        hStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        hStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        hStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        hStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        HSSFCellStyle MainHStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        MainHStyle.SetFont(hFont);
        MainHStyle.Alignment = HorizontalAlignment.Center;
        HSSFCellStyle IStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        IStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        IStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        IStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        IStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        if (dtLevelName.Rows.Count > 0)
        {
            row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
            sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, cellCounts));
            row.GetCell(0).CellStyle = MainHStyle;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
        }
        row.CreateCell(0).SetCellValue(SheetName + " From - " + Convert.ToString(txtFromDate.Text) + " - " + " To - " + Convert.ToString(txtToDate.Text));
        row.GetCell(0).CellStyle = MainHStyle;
        sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, cellCounts));
        rowIndex++;
        row = sheet.CreateRow(rowIndex);
        rowIndex++;
        row = sheet.CreateRow(rowIndex);
        if (rblReportType0.Checked == true)
        {
            row.CreateCell(0).SetCellValue("Sl.No");
            row.CreateCell(1).SetCellValue("Employee ID");
            row.CreateCell(2).SetCellValue("Employee Name");
            row.CreateCell(3).SetCellValue("Gender");
            row.CreateCell(4).SetCellValue("Year");
            row.CreateCell(5).SetCellValue("Month");
            int column = 0;
            for (int i = 6; i <= 36; i++)
            {
                column++;
                row.CreateCell(i).SetCellValue(column.ToString());
            }
            //row.RowStyle = hStyle;
            for (int i = 0; i <= 36; i++)
            {
                row.GetCell(i).CellStyle = hStyle;
            }
            rowIndex++;
            string strPre = string.Empty;
            Int32 SlNo = 0;
            foreach (DataRow DR in dtShift.Rows)
            {
                row = sheet.CreateRow(rowIndex);
                if (strPre == string.Empty || strPre != Convert.ToString(DR["Employee Id"]))
                {
                    SlNo++;
                    row.CreateCell(0).SetCellValue(SlNo);
                }
                else
                {
                    row.CreateCell(0).SetCellValue("");
                }
                row.CreateCell(1).SetCellValue(Convert.ToString(DR["Employee Id"]));
                row.CreateCell(2).SetCellValue(Convert.ToString(DR["Employee Name"]));
                row.CreateCell(3).SetCellValue(Convert.ToString(DR["Gender"]));
                row.CreateCell(4).SetCellValue(Convert.ToString(DR["Year"]));
                row.CreateCell(5).SetCellValue(Convert.ToString(DR["Month"]));
                for (int i = 6; i <= 36; i++)
                {
                    row.CreateCell(i).SetCellValue(Convert.ToString(DR[Convert.ToString(i - 5)]));
                }
                strPre = Convert.ToString(DR["Employee Id"]);
                //Style Added the created Headers.
                for (int i = 0; i <= 36; i++)
                {
                    row.GetCell(i).CellStyle = IStyle;
                    sheet.AutoSizeColumn(i);
                }
                rowIndex++;
            }
        }
        else
        {
            row.CreateCell(0).SetCellValue("Sl.No");
            row.CreateCell(1).SetCellValue("Employee ID");
            row.CreateCell(2).SetCellValue("Employee Name");
            row.CreateCell(3).SetCellValue("Level Name");
            row.CreateCell(4).SetCellValue("Designation");
            row.CreateCell(5).SetCellValue("Gender");
            row.CreateCell(6).SetCellValue("Dates");
            //row.RowStyle = hStyle;
            for (int i = 0; i <= 6; i++)
            {
                row.GetCell(i).CellStyle = hStyle;
            }
            rowIndex++;
            string strPre = string.Empty;
            Int32 SlNo = 0;
            foreach (DataRow DR in dtShift.Rows)
            {
                row = sheet.CreateRow(rowIndex);
                if (strPre == string.Empty || strPre != Convert.ToString(DR["Employee_Code_Id"]))
                {
                    SlNo++;
                    row.CreateCell(0).SetCellValue(SlNo);
                }
                else
                {
                    row.CreateCell(0).SetCellValue("");
                }
                row.CreateCell(1).SetCellValue(Convert.ToString(DR["Employee_Code_Id"]));
                row.CreateCell(2).SetCellValue(Convert.ToString(DR["Empname"]));
                row.CreateCell(3).SetCellValue(Convert.ToString(DR["DeptName"]));
                row.CreateCell(4).SetCellValue(Convert.ToString(DR["TitleName"]));
                row.CreateCell(5).SetCellValue(Convert.ToString(DR["Gender"]));
                row.CreateCell(6).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["NotAllocatedDates"]))).ToString(Session["MDateFormat"].ToString()));
                strPre = Convert.ToString(DR["Employee_Code_Id"]);
                //Style Added the created Headers.
                for (int i = 0; i <= 6; i++)
                {
                    row.GetCell(i).CellStyle = IStyle;
                    sheet.AutoSizeColumn(i);
                }
                rowIndex++;
            }
        }
        using (MemoryStream exportData = new MemoryStream())
        {
            workbook.Write(exportData);
            string saveAsFileName = string.Format(ReportName + "-{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();
        }
    }
    #endregion
}
