using System;
using System.Collections;
using System.Data;
using System.Globalization;
using System.IO;
using System.Resources;
using System.Text;
using System.Threading;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Organization_Settings_Organization_ExportData : Base.BasePage
{
    #region VariableDeclaration
    MonthClosureEventData objMonthClosureEventData = new MonthClosureEventData();
    ExportData objExportData = new ExportData();
    Cls_CheckUser objCheckUser = new Cls_CheckUser();
    DataSet ds_Checkuser = new DataSet();
    ResourceManager rm;
    string strMonthName = string.Empty, strMonthValue = string.Empty;
    int intYear;
    CultureInfo ci;
    DataSet dsCustomFormat = new DataSet();
    DataSet dsPayrollProcess = new DataSet();
    DataSet dsCustomFields = new DataSet();
    string strMonthDate = string.Empty;
    cls_Common objClassCommon = new cls_Common();
    #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
        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;
        if (source_Language == "Resources.Resource_AR")
        {
            btn_RemoveAll.ImageUrl = "../dist/img/button4.jpg";
            btn_Remove.ImageUrl = "../dist/img/button3.jpg";
            btn_Add.ImageUrl = "../dist/img/button2.jpg";
            btn_AddAll.ImageUrl = "../dist/img/button1.jpg";
        }
        else
        {
            btn_RemoveAll.ImageUrl = "../dist/img/button1.jpg";
            btn_Remove.ImageUrl = "../dist/img/button2.jpg";
            btn_Add.ImageUrl = "../dist/img/button3.jpg";
            btn_AddAll.ImageUrl = "../dist/img/button4.jpg";

            //btn_RemoveAll.ImageUrl = "../dist/img/button4.jpg";
            //btn_Remove.ImageUrl = "../dist/img/button3.jpg";
            //btn_Add.ImageUrl = "../dist/img/button2.jpg";
            //btn_AddAll.ImageUrl = "../dist/img/button1.jpg";

        }
        site.SiteMapProvider = Convert.ToString(Session["SiteMapProvider"]);
        LoadStrings(ci);
        string strSetAlert = "";
        strSetAlert = strSetAlert + "<script language='javascript'>";
        strSetAlert = strSetAlert + "SetAlertMessages('" + rm.GetString("Alert_SelectanyoneOptiontomove") + "','" + rm.GetString("Alert_Select_Company") + "','" + rm.GetString("Alert_SelectDuration") + "','" + rm.GetString("Alert_SureToExportData") + "');";
        strSetAlert = strSetAlert + "</script>";
        Page.RegisterStartupScript("SetAlertMessages", strSetAlert);
        //Call to a function to implement the UserRights
        UserRights();
        if (!IsPostBack)
        {
            objClassCommon.menu("ExportTran", "66");
            CompanyBinding();
            ddlCompany.Focus();
        }
        if (ddlCompany.SelectedValue != "-Select-")
        {
            FillFieldListBox();
            BindFields();
        }
        hdnSelectedOption.Value = rbOption.SelectedValue;
        hdnSelectedDataType.Value = rblMonthDateWise.SelectedValue;
        //To apply toot tip for the list box items
        lst_PayrollFieldList.PreRender += LB_PreRender;
        if (Convert.ToString(Session["Demo"]) != string.Empty)
        {
            if (Convert.ToString(Session["Demo"]) == "Express")
            {
                rbOption.Items.Remove(rbOption.Items.FindByValue("OT"));
                rbOption.Items.Remove(rbOption.Items.FindByValue("Attendance"));
            }
        }
    }
    //To apply toot tip for the list box items
    private void LB_PreRender(object sender, System.EventArgs e)
    {
        foreach (ListItem item in lst_PayrollFieldList.Items)
        {
            item.Attributes.Add("title", item.Text);
        }
    }
    #endregion
    #region UserDefinedFunctions
    private void CompanyBinding()
    {
        ShiftEvenData objShift = new ShiftEvenData();
        string gstrUserGroupName = string.Empty;
        DataSet dsCompany = new DataSet();
        gstrUserGroupName = Convert.ToString(Session["UserGroupName"]);
        dsCompany = objShift.SelectCompany(gstrUserGroupName, Convert.ToString(Session["LevelRights"]), 0);
        if (dsCompany != null)
        {
            if (dsCompany.Tables[0].Rows.Count > 0)
            {
                ddlCompany.DataSource = dsCompany;
                ddlCompany.DataTextField = "CompanyName";
                ddlCompany.DataValueField = "CompanyId";
                ddlCompany.DataBind();
            }
        }
        foreach (ListItem item in ddlCompany.Items)
        {
            item.Attributes.Add("title", item.Text);
        }
    }
    private void filldurationDetails(int companyId)
    {
        DataSet ds_DurationDetails = new DataSet();
        ds_DurationDetails = objMonthClosureEventData.SelectPayrollDurtaion(companyId);
        ddlDuration.DataSource = ds_DurationDetails;
        ddlDuration.DataValueField = "MonthClosure_Id";
        ddlDuration.DataTextField = "Duration";
        ddlDuration.DataBind();
        ddlDuration.Items.Insert(0, "-Select-");
        foreach (ListItem item in ddlDuration.Items)
        {
            item.Attributes.Add("title", item.Text);
        }
    }
    /// <summary>
    /// This will delete the tempary files from the folder
    /// </summary>
    protected void DeleteExistingFiles(string strFilePath)
    {
        if (Directory.Exists(strFilePath))
        {
            foreach (string strFilePaths in Directory.GetFiles(strFilePath))
            {
                File.Delete(strFilePaths);
            }
        }
    }
    public StringBuilder Export(DataSet ds)
    {
        StringBuilder columnnames = new StringBuilder();
        StringBuilder body = new StringBuilder();
        StringBuilder record = new StringBuilder();
        int RowCount = ds.Tables[0].Columns.Count;
        int increment = 0;
        foreach (DataColumn col in ds.Tables[0].Columns)
        {
            increment = increment + 1;
            columnnames.Append(" ");
            columnnames.Append(col.ColumnName);
            columnnames.Append(" ");
            if (increment != RowCount)
            {
                columnnames.Append(",");
            }
        }
        foreach (DataRow row in ds.Tables[0].Rows)
        {
            object[] arr = row.ItemArray;
            for (int i = 0; i <= arr.Length - 1; i++)
            {
                if (arr[i].ToString().IndexOf(",") > 0)
                {
                    record.Append(" ");
                    record.Append(arr[i].ToString());
                    record.Append(" ");
                    record.Append(",");
                }
                else
                {
                    record.Append(arr[i].ToString());
                    record.Append(",");
                }
            }
            body.Append(record.ToString().Substring(0, record.Length - 1) + System.Environment.NewLine);
            record.Remove(0, record.Length);
        }
        columnnames.Append(System.Environment.NewLine);
        return (columnnames.Append(body.ToString()));
    }
    /// <summary>
    /// To change the date time format of the fields in the dataset.
    /// </summary>
    private string ChangeDateTimeFormats()
    {
        string ErrorStatus = "Success";
        //To change the date format of the dataset in the specified format.
        dsCustomFields.Tables[0].DefaultView.RowFilter = "FieldType='Date'";
        if (dsCustomFields.Tables[0].DefaultView.Table.Rows.Count > 0)
        {
            if (dsCustomFormat.Tables.Count > 0)
            {
                if (dsCustomFormat.Tables[0].Rows.Count > 0)
                {
                    if ((dsCustomFormat.Tables[0].Rows[0]["DateFormat"].ToString() != string.Empty) || (dsCustomFormat.Tables[0].Rows[0]["DateFormat"] != null))
                    {
                        string DateFormat = dsCustomFormat.Tables[0].Rows[0]["DateFormat"].ToString();
                        foreach (DataRow rowDefaultView in dsCustomFields.Tables[0].DefaultView.ToTable().Rows)
                        {
                            foreach (DataRow rowPayRollProcess in dsPayrollProcess.Tables[0].Rows)
                            {
                                try
                                {
                                    string FieldName = rowDefaultView["FieldName"].ToString();
                                    string FieldData = rowPayRollProcess[FieldName].ToString();
                                    if (FieldData != string.Empty)
                                    {
                                        string ConvertedData = DateTime.Parse(FieldData).ToString(DateFormat);
                                        rowPayRollProcess[FieldName] = ConvertedData;
                                    }
                                }
                                catch (Exception ex)
                                {
                                    ErrorStatus = "Date";
                                    return ErrorStatus;
                                }
                            }
                        }
                    }
                }
            }
        }
        //To change the time format of the dataset in the specified format.
        dsCustomFields.Tables[0].DefaultView.RowFilter = "FieldType='Time'";
        if (dsCustomFields.Tables[0].DefaultView.Table.Rows.Count > 0)
        {
            if (dsCustomFormat.Tables.Count > 0)
            {
                if (dsCustomFormat.Tables[0].Rows.Count > 0)
                {
                    if ((dsCustomFormat.Tables[0].Rows[0]["TimeFormat"].ToString() != string.Empty) || (dsCustomFormat.Tables[0].Rows[0]["TimeFormat"] != null))
                    {
                        string TimeFormat = dsCustomFormat.Tables[0].Rows[0]["TimeFormat"].ToString();
                        foreach (DataRow rowDefaultView in dsCustomFields.Tables[0].DefaultView.ToTable().Rows)
                        {
                            foreach (DataRow rowPayRollProcess in dsPayrollProcess.Tables[0].Rows)
                            {
                                try
                                {
                                    string FieldName = rowDefaultView["FieldName"].ToString();
                                    string FieldData = rowPayRollProcess[FieldName].ToString();
                                    if (FieldData != string.Empty)
                                    {
                                        string ConvertedData = DateTime.Parse(FieldData).ToString(TimeFormat);
                                        rowPayRollProcess[FieldName] = ConvertedData;
                                    }
                                }
                                catch (Exception ex)
                                {
                                    ErrorStatus = "Time";
                                    return ErrorStatus;
                                }
                            }
                        }
                    }
                }
            }
        }
        //To change the time format of the dataset in the specified format.
        dsCustomFields.Tables[0].DefaultView.RowFilter = "FieldType='Date&Time'";
        if (dsCustomFields.Tables[0].DefaultView.Table.Rows.Count > 0)
        {
            if (dsCustomFormat.Tables.Count > 0)
            {
                if (dsCustomFormat.Tables[0].Rows.Count > 0)
                {
                    if ((dsCustomFormat.Tables[0].Rows[0]["DateTimeFormat"].ToString() != string.Empty) || (dsCustomFormat.Tables[0].Rows[0]["DateTimeFormat"] != null))
                    {
                        string DateTimeFormat = dsCustomFormat.Tables[0].Rows[0]["DateTimeFormat"].ToString();
                        foreach (DataRow rowDefaultView in dsCustomFields.Tables[0].DefaultView.ToTable().Rows)
                        {
                            foreach (DataRow rowPayRollProcess in dsPayrollProcess.Tables[0].Rows)
                            {
                                try
                                {
                                    string FieldName = rowDefaultView["FieldName"].ToString();
                                    string FieldData = rowPayRollProcess[FieldName].ToString();
                                    if (FieldData != string.Empty)
                                    {
                                        string ConvertedData = DateTime.Parse(FieldData).ToString(DateTimeFormat);
                                        rowPayRollProcess[FieldName] = ConvertedData;
                                    }
                                }
                                catch (Exception ex)
                                {
                                    ErrorStatus = "DateTime";
                                    return ErrorStatus;
                                }
                            }
                        }
                    }
                }
            }
        }
        return ErrorStatus;
    }
    private void FillFieldListBox()
    {
        DataSet dsPayrollFieldNames = new DataSet();
        dsPayrollFieldNames = objExportData.GetCustomFieldNamesForExportData("All", rblMonthDateWise.SelectedValue, Convert.ToInt16(ddlCompany.SelectedValue));
        if (dsPayrollFieldNames != null)
        {
            if (dsPayrollFieldNames.Tables[0].Rows.Count == 0)
            {
                dsPayrollFieldNames = objMonthClosureEventData.GetPayrollFieldNames(rblMonthDateWise.SelectedValue, Convert.ToInt16(ddlCompany.SelectedValue));
                if (dsPayrollFieldNames.Tables[0].Rows.Count > 0)
                {
                    lst_PayrollFieldList.DataSource = dsPayrollFieldNames;
                    lst_PayrollFieldList.DataTextField = "FieldName";
                    lst_PayrollFieldList.DataValueField = "FieldName";
                    lst_PayrollFieldList.DataBind();
                }
            }
            else
            {
                lst_PayrollFieldList.DataSource = dsPayrollFieldNames;
                lst_PayrollFieldList.DataTextField = "column_name";
                lst_PayrollFieldList.DataValueField = "FieldName";
                lst_PayrollFieldList.DataBind();
            }
            foreach (ListItem item in lst_PayrollFieldList.Items)
            {
                item.Attributes.Add("title", item.Text);
            }
        }
    }
    public string Export(DataSet ds, Boolean exportcolumnheadings, string month, int year, string strReason)
    {
        string columnnames = "", body = "", record = "";
        if (exportcolumnheadings)
        {
            foreach (DataColumn col in ds.Tables[0].Columns)
            {
                columnnames = columnnames + col.ColumnName + ",";
            }
            columnnames = columnnames + "remarks,";
            columnnames = columnnames.Substring(0, columnnames.Length - 1);
        }
        foreach (DataRow row in ds.Tables[0].Rows)
        {
            object[] arr = row.ItemArray;
            for (int i = 0; i <= arr.Length - 1; i++)
            {
                if (arr[i].ToString().IndexOf(",") > 0)
                {
                    record = record + arr[i].ToString() + ",";
                }
                else
                {
                    record = record + arr[i].ToString() + ",";
                }
            }
            record = record + "" + strReason + ",";
            body = body + record.Substring(0, record.Length - 1) + System.Environment.NewLine;
            record = "";
        }
        if (exportcolumnheadings)
        {
            return (columnnames + System.Environment.NewLine + body);
        }
        else
        {
            return (body);
        }
    }
    // Getting Information from resource file and display it in label
    private void LoadStrings(CultureInfo ci)
    {
        lblHeader_ExportData.Text = rm.GetString("SystemSettings_Exportdata", ci);
        lbl_IndicatesMandatoryFields.Text = rm.GetString("IndicatesMandatoryFields", ci);
        lbl_FieldList.Text = rm.GetString("ExportData_FieldList", ci);
        lbl_SelectedFieldList.Text = rm.GetString("ExportData_SelectedFieldList", ci);
        btn_Export.Text = rm.GetString("btn_Export", ci);
        lblCompany.Text = rm.GetString("EntranceMaster_Company", ci);
        lblDuration.Text = rm.GetString("Report_Duration", ci);
        rbOption.Items[0].Text = rm.GetString("ExportData_All", ci);
        if (Convert.ToString(Session["Demo"]) != string.Empty)
        {
            if (Convert.ToString(Session["Demo"]) == "Express")
            {
            }
            else
            {
                rbOption.Items[1].Text = rm.GetString("ExportData_OTData", ci);
                rbOption.Items[2].Text = rm.GetString("Dashboard_AttData", ci);
            }
        }
        RequiredFieldValidator8.ErrorMessage = rm.GetString("Alert_SelectDuration", ci);
        RequiredFieldValidator2.ErrorMessage = rm.GetString("Alert_Select_Company", ci);
        ddlCompany.Items[0].Text = rm.GetString("Select", ci);
        ddlDuration.Items[0].Text = rm.GetString("Select", ci);
        rbExportToXLS.Text = rm.GetString("Caption_ExportToXLS", ci);
        rbExportToCSV.Text = rm.GetString("Caption_ExportToCSV", ci);
        rbExportToXML.Text = rm.GetString("Caption_ExportToXML", ci);
        lblConfiguration.Text = rm.GetString("Caption_Configuration", ci);
        lnkCustomFields.Text = rm.GetString("Caption_CustomFields", ci);
        rblMonthDateWise.Items[0].Text = rm.GetString("Caption_SummarizedData", ci);
        rblMonthDateWise.Items[1].Text = rm.GetString("Caption_DailyData", ci);
        lblDataType.Text = rm.GetString("Caption_DataType", ci);
        lblOption.Text = rm.GetString("Option", ci);
    }
    protected void BindFields()
    {
        lst_PayrollSelectedFieldList.Items.Clear();
        if (rbOption.SelectedIndex == 0)
        {
            rblMonthDateWise.Items[1].Enabled = true;
            DataSet dsPayrollFieldNames = new DataSet();
            dsPayrollFieldNames = objExportData.GetCustomFieldNamesForExportData("All", rblMonthDateWise.SelectedValue, Convert.ToInt16(ddlCompany.SelectedValue));
            if (dsPayrollFieldNames != null)
            {
                if (dsPayrollFieldNames.Tables[0].Rows.Count == 0)
                {
                    dsPayrollFieldNames = objMonthClosureEventData.GetPayrollFieldNames(rblMonthDateWise.SelectedValue, Convert.ToInt16(ddlCompany.SelectedValue));
                    if (dsPayrollFieldNames.Tables[0].Rows.Count > 0)
                    {
                        lst_PayrollFieldList.DataSource = dsPayrollFieldNames;
                        lst_PayrollFieldList.DataTextField = "FieldName";
                        lst_PayrollFieldList.DataValueField = "FieldName";
                        lst_PayrollFieldList.DataBind();
                    }
                }
                else
                {
                    lst_PayrollFieldList.DataSource = dsPayrollFieldNames;
                    lst_PayrollFieldList.DataTextField = "column_name";
                    lst_PayrollFieldList.DataValueField = "FieldName";
                    lst_PayrollFieldList.DataBind();
                }
                foreach (ListItem item in lst_PayrollFieldList.Items)
                {
                    item.Attributes.Add("title", item.Text);
                }
            }
            btn_Add.Enabled = true;
            btn_AddAll.Enabled = true;
            btn_Remove.Enabled = true;
            btn_RemoveAll.Enabled = true;
        }
        else if (rbOption.SelectedIndex == 1)
        {
            lst_PayrollFieldList.Items.Clear();
            rblMonthDateWise.Items[1].Enabled = false;
            rblMonthDateWise.SelectedIndex = 0;
            btn_Add.Enabled = false;
            btn_AddAll.Enabled = false;
            btn_Remove.Enabled = false;
            btn_RemoveAll.Enabled = false;
            //Over Time
            DataSet dsPayrollFieldNames = new DataSet();
            dsPayrollFieldNames = objExportData.GetCustomFieldNamesForExportData("OT", rblMonthDateWise.SelectedValue, Convert.ToInt16(ddlCompany.SelectedValue));
            if (dsPayrollFieldNames != null)
            {
                if (dsPayrollFieldNames.Tables[0].Rows.Count > 0)
                {
                    lst_PayrollSelectedFieldList.DataSource = dsPayrollFieldNames;
                    lst_PayrollSelectedFieldList.DataTextField = "column_name";
                    lst_PayrollSelectedFieldList.DataValueField = "FieldName";
                    lst_PayrollSelectedFieldList.DataBind();
                }
                else
                {
                    lst_PayrollSelectedFieldList.Items.Clear();
                    lst_PayrollSelectedFieldList.Items.Add("employee_code");
                    lst_PayrollSelectedFieldList.Items.Add("pay_year");
                    lst_PayrollSelectedFieldList.Items.Add("pay_month");
                    lst_PayrollSelectedFieldList.Items.Add("NormOTMins");
                    lst_PayrollSelectedFieldList.Items.Add("WeekOffOTMins");
                    lst_PayrollSelectedFieldList.Items.Add("HolidayOTMins");
                }
            }
            else
            {
                lst_PayrollSelectedFieldList.Items.Clear();
                lst_PayrollSelectedFieldList.Items.Add("employee_code");
                lst_PayrollSelectedFieldList.Items.Add("pay_year");
                lst_PayrollSelectedFieldList.Items.Add("pay_month");
                lst_PayrollSelectedFieldList.Items.Add("NormOTMins");
                lst_PayrollSelectedFieldList.Items.Add("WeekOffOTMins");
                lst_PayrollSelectedFieldList.Items.Add("HolidayOTMins");
            }
            foreach (ListItem item in lst_PayrollSelectedFieldList.Items)
            {
                item.Attributes.Add("title", item.Text);
            }
        }
        else if (rbOption.SelectedIndex == 2)
        {
            lst_PayrollFieldList.Items.Clear();
            rblMonthDateWise.Items[1].Enabled = false;
            rblMonthDateWise.SelectedIndex = 0;
            btn_Add.Enabled = false;
            btn_AddAll.Enabled = false;
            btn_Remove.Enabled = false;
            btn_RemoveAll.Enabled = false;
            //Attandance
            DataSet dsPayrollFieldNames = new DataSet();
            dsPayrollFieldNames = objExportData.GetCustomFieldNamesForExportData("Attendance", rblMonthDateWise.SelectedValue, Convert.ToInt16(ddlCompany.SelectedValue));
            if (dsPayrollFieldNames != null)
            {
                if (dsPayrollFieldNames.Tables[0].Rows.Count > 0)
                {
                    lst_PayrollSelectedFieldList.DataSource = dsPayrollFieldNames;
                    lst_PayrollSelectedFieldList.DataTextField = "column_name";
                    lst_PayrollSelectedFieldList.DataValueField = "FieldName";
                    lst_PayrollSelectedFieldList.DataBind();
                }
                else
                {
                    lst_PayrollSelectedFieldList.Items.Clear();
                    lst_PayrollSelectedFieldList.Items.Add("employee_code");
                    lst_PayrollSelectedFieldList.Items.Add("pay_month");
                    lst_PayrollSelectedFieldList.Items.Add("pay_year");
                    lst_PayrollSelectedFieldList.Items.Add("PresentDays");
                    lst_PayrollSelectedFieldList.Items.Add("AbsentDays");
                    lst_PayrollSelectedFieldList.Items.Add("LeaveDays");
                }
            }
            else
            {
                lst_PayrollSelectedFieldList.Items.Clear();
                lst_PayrollSelectedFieldList.Items.Add("employee_code");
                lst_PayrollSelectedFieldList.Items.Add("pay_month");
                lst_PayrollSelectedFieldList.Items.Add("pay_year");
                lst_PayrollSelectedFieldList.Items.Add("PresentDays");
                lst_PayrollSelectedFieldList.Items.Add("AbsentDays");
                lst_PayrollSelectedFieldList.Items.Add("LeaveDays");
            }
        }
    }
    public string DataTable2ExcelString(System.Data.DataTable dt)
    {
        StringBuilder sbTop = new StringBuilder();
        sbTop.Append("<html xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ");
        sbTop.Append("xmlns=\"http://www.w3.org/TR/REC-html40\"><head><meta http-equiv=Content-Type content=\"text/html; charset=windows-1252\">");
        sbTop.Append("<meta name=ProgId content=Excel.Sheet><meta name=Generator content=\"Microsoft Excel 9\"><!--[if gte mso 9]>");
        sbTop.Append("<xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>" + dt.TableName + "</x:Name><x:WorksheetOptions>");
        sbTop.Append("<x:Selected/><x:ProtectContents>False</x:ProtectContents><x:ProtectObjects>False</x:ProtectObjects>");
        sbTop.Append("<x:ProtectScenarios>False</x:ProtectScenarios></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets>");
        sbTop.Append("<x:ProtectStructure>False</x:ProtectStructure><x:ProtectWindows>False</x:ProtectWindows></x:ExcelWorkbook></xml>");
        sbTop.Append("<![endif]--></head><body><table>");
        string bottom = "</table></body></html>";
        StringBuilder sb = new StringBuilder();
        //Header
        sb.Append("<tr>");
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            sb.Append("<td>" + dt.Columns[i].ColumnName + "</td>");
        }
        sb.Append("</tr>");
        //Items
        for (int x = 0; x < dt.Rows.Count; x++)
        {
            sb.Append("<tr>");
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sb.Append("<td>" + dt.Rows[x][i] + "</td>");
            }
            sb.Append("</tr>");
        }
        string SSxml = sbTop.ToString() + sb.ToString() + bottom;
        return SSxml;
    }
    private void UserRights()
    {
        ds_Checkuser = objCheckUser.checkRights(66, Convert.ToInt32(Session["userGroup_Id"]), "addPrv");
        if (ds_Checkuser.Tables[0].Rows.Count == 0)
        {
            Response.Redirect("SystemLandingPage.aspx");
        }
    }
    #endregion
    #region ButtonEvents
    protected void btn_Export_Click(object sender, EventArgs e)
    {
        DataTable dtMonthCloseDetails = new DataTable();
        int intMonthClosureId = Convert.ToInt32(ddlDuration.SelectedValue);
        // Get the Details of the Payroll Process Details for a particular Month Closure ID by passing the Month Closure ID
        DataSet dsMonthCloseDetails = objMonthClosureEventData.SelectMonthCloseDetailsByIDview(intMonthClosureId);
        dtMonthCloseDetails = dsMonthCloseDetails.Tables[0];
        // If Record Exists
        if (dtMonthCloseDetails.Rows.Count > 0)
        {
            if (dtMonthCloseDetails.Rows[0]["MonthClosure_Month"] != DBNull.Value)
            {
                strMonthName = dtMonthCloseDetails.Rows[0]["MonthClosure_Month"].ToString();
            }
            if (dtMonthCloseDetails.Rows[0]["MonthClosure_Year"] != DBNull.Value)
            {
                intYear = Convert.ToInt32(dtMonthCloseDetails.Rows[0]["MonthClosure_Year"].ToString());
            }
            if (dtMonthCloseDetails.Rows[0]["MonthClosure_StartDate"] != DBNull.Value)
            {
                strMonthValue = Convert.ToDateTime((DateTime.Parse(dtMonthCloseDetails.Rows[0]["MonthClosure_StartDate"].ToString())).ToString(Session["MDateFormat"].ToString())).Month.ToString();
            }
        }
        bool blnProcessDataExists = false;
        string strHead = "";
        strHead = "<HTML xmlns:x='urn:schemas-microsoft-com:office:excel'>";
        strHead += "<HEAD><meta charset='utf-8'><style type=\"text/css\">  <!--table";
        strHead += "  @page {mso-header-data:'&CMultiplication Table\\000ADate\\: &D\\000APage &P';mso-page-orientation:landscape;}";
        strHead += "br{mso-data-placement:same-cell;}";
        strHead += "--></style>";
        strHead += "<!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>";
        strHead += "<x:Name>Sheet1</x:Name><x:WorksheetOptions><x:Print><x:ValidPrinterInfo/>";
        strHead += "</x:Print></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook>";
        strHead += "</xml><![endif]--></HEAD><body><table><tr>";
        //If All option is selected
        if (rbOption.SelectedIndex == 0)
        {
            if (hdn_SelectedFieldNames.Value == "")
            {
                base.Message(rm.GetString("Alert_SelectFieldsfromthefieldlist", ci));
                return;
            }
            Rpt_Common rptcommon = new Rpt_Common();
            string strLevel = "";
            strLevel = rptcommon.getNodes(Convert.ToInt16(ddlCompany.SelectedValue), Convert.ToString(Session["LevelRights"]));
            dsPayrollProcess = objMonthClosureEventData.Payroll(hdn_SelectedFieldNames.Value, strMonthName, intYear, Convert.ToInt16(ddlCompany.SelectedValue), strLevel, strMonthValue, intMonthClosureId, Convert.ToString(Session["UserGroupName"]), Convert.ToInt16(ddlCompany.SelectedValue), rblMonthDateWise.SelectedValue);
            if (dsPayrollProcess.Tables.Count > 0)
            {
                if (dsPayrollProcess.Tables[0].Rows.Count > 0)
                {
                    blnProcessDataExists = true;
                    //To get custom formats.
                    dsCustomFormat = objExportData.GetCustomFormatsForExportData("All", rblMonthDateWise.SelectedValue, Convert.ToInt16(ddlCompany.SelectedValue));
                    //To get custom field names.
                    dsCustomFields = objExportData.GetCustomFieldNamesForExportData("All", rblMonthDateWise.SelectedValue, Convert.ToInt16(ddlCompany.SelectedValue));
                    ChangeDateTimeFormats();
                    //To change  the column name of the dataset with custom field name.
                    if (dsCustomFields.Tables.Count > 0)
                    {
                        if (dsCustomFields.Tables[0].Rows.Count > 0)
                        {
                            Hashtable CustomFields = new Hashtable();
                            foreach (DataRow row in dsCustomFields.Tables[0].Rows)
                            {
                                CustomFields.Add(row["FieldName"], row["UserDefinedFieldName"]);
                            }
                            foreach (DataColumn col in dsPayrollProcess.Tables[0].Columns)
                            {
                                if (CustomFields.Contains(col.ColumnName))
                                {
                                    if (CustomFields[col.ColumnName].ToString() != string.Empty)
                                    {
                                        col.ColumnName = CustomFields[col.ColumnName].ToString();
                                    }
                                }
                            }
                        }
                    }
                    if (rbExportToXLS.Checked == true)
                    {
                        dsPayrollProcess.Tables[0].TableName = "Sheet 1";
                        DataTable dt = dsPayrollProcess.Tables[0];
                        dt.TableName = "Sheet 1";
                        string attachment = "attachment; filename=" + Convert.ToString(ddlCompany.SelectedItem.Text).Replace(' ', '_').ToString() + "_Payroll_" + strMonthName + "_" + intYear.ToString() + ".xls";
                        Response.ClearContent();
                        Response.AddHeader("content-disposition", attachment);
                        Response.ContentType = "application/vnd.ms-excel";
                        string tab = "";
                        Response.Write(strHead);
                        foreach (DataColumn dc in dt.Columns)
                        {
                            Response.Write("<td>" + dc.ColumnName + "</td>");
                        }
                        Response.Write("</tr><tr>");
                        int i;
                        foreach (DataRow dr in dt.Rows)
                        {
                            tab = "";
                            for (i = 0; i < dt.Columns.Count; i++)
                            {
                                Response.Write("<td>" + dr[i].ToString() + "</td>");
                            }
                            Response.Write("</tr><tr>");
                        }
                        Response.Write("</tr></BODY></HTML>");
                        Response.End();
                    }
                    else if (rbExportToCSV.Checked == true)
                    {
                        StringBuilder strData = Export(dsPayrollProcess);
                        byte[] data = System.Text.ASCIIEncoding.ASCII.GetBytes(strData.ToString());
                        string attachment = "attachment; filename=" + Convert.ToString(ddlCompany.SelectedItem.Text).Replace(' ', '_').ToString() + "_Payroll_" + strMonthName + "_" + intYear.ToString() + ".csv";
                        Response.Clear();
                        Response.AddHeader("Content-Type", "application/Excel");
                        Response.AddHeader("Content-Disposition", attachment);
                        Response.BinaryWrite(data);
                        Response.End();
                    }
                    else if (rbExportToXML.Checked == true)
                    {
                        if (dsCustomFormat.Tables.Count > 0)
                        {
                            if (dsCustomFormat.Tables[0].Rows.Count > 0)
                            {
                                if (dsCustomFormat.Tables[0].Rows[0]["XML_RootTag"].ToString() != string.Empty)
                                {
                                    dsPayrollProcess.DataSetName = dsCustomFormat.Tables[0].Rows[0]["XML_RootTag"].ToString();
                                }
                                if (dsCustomFormat.Tables[0].Rows[0]["XML_FirstChildTag"].ToString() != string.Empty)
                                {
                                    dsPayrollProcess.Tables[0].TableName = dsCustomFormat.Tables[0].Rows[0]["XML_FirstChildTag"].ToString();
                                }
                            }
                        }
                        DeleteExistingFiles(Server.MapPath("~\\System Settings\\Download"));
                        dsPayrollProcess.WriteXml(Server.MapPath("~/System Settings/Download/" + Convert.ToString(ddlCompany.SelectedItem.Text).Replace(' ', '_').ToString() + "_Payroll_" + strMonthName + "_" + intYear.ToString() + ".xml"));
                        Response.ContentType = "text/xml";
                        string strFile = Server.MapPath("~/System Settings/Download/" + Convert.ToString(ddlCompany.SelectedItem.Text).Replace(' ', '_').ToString() + "_Payroll_" + strMonthName + "_" + intYear.ToString() + ".xml");
                        Response.AddHeader("content-disposition", "attachment; filename=" + Convert.ToString(ddlCompany.SelectedItem.Text).Replace(' ', '_').ToString() + "_Payroll_" + strMonthName + "_" + intYear.ToString() + ".xml");
                        Response.WriteFile(strFile);
                        Response.Flush();
                        Response.End();
                    }
                }
            }
        }
        //If OT option is selected
        else if (rbOption.SelectedIndex == 1)
        {
            //by overtime
            Rpt_Common rptcommon = new Rpt_Common();
            string strLevel = "";
            strLevel = rptcommon.getNodes(Convert.ToInt16(ddlCompany.SelectedValue), Convert.ToString(Session["LevelRights"]));
            dsPayrollProcess = objMonthClosureEventData.PayrollByOverTime(hdn_SelectedFieldNames.Value, strMonthName, intYear, Convert.ToInt16(ddlCompany.SelectedValue), strLevel, strMonthValue, intMonthClosureId, Convert.ToString(Session["UserGroupName"]), Convert.ToInt16(ddlCompany.SelectedValue));
            if (dsPayrollProcess.Tables.Count > 0)
            {
                if (dsPayrollProcess.Tables[0].Rows.Count > 0)
                {
                    blnProcessDataExists = true;
                    //To get custom formats.
                    dsCustomFormat = objExportData.GetCustomFormatsForExportData("OT", rblMonthDateWise.SelectedValue, Convert.ToInt16(ddlCompany.SelectedValue));
                    //To get custom field names.
                    dsCustomFields = objExportData.GetCustomFieldNamesForExportData("OT", rblMonthDateWise.SelectedValue, Convert.ToInt16(ddlCompany.SelectedValue));
                    ChangeDateTimeFormats();
                    //To change  the column name of the dataset with custom field name.
                    if (dsCustomFields.Tables.Count > 0)
                    {
                        if (dsCustomFields.Tables[0].Rows.Count > 0)
                        {
                            Hashtable CustomFields = new Hashtable();
                            foreach (DataRow row in dsCustomFields.Tables[0].Rows)
                            {
                                CustomFields.Add(row["FieldName"], row["UserDefinedFieldName"]);
                            }
                            foreach (DataColumn col in dsPayrollProcess.Tables[0].Columns)
                            {
                                if (CustomFields.Contains(col.ColumnName))
                                {
                                    if (CustomFields[col.ColumnName].ToString() != string.Empty)
                                    {
                                        col.ColumnName = CustomFields[col.ColumnName].ToString();
                                    }
                                }
                            }
                        }
                    }
                    //Export to XLS
                    if (rbExportToXLS.Checked == true)
                    {
                        dsPayrollProcess.Tables[0].TableName = "Sheet 1";
                        DataTable dt = dsPayrollProcess.Tables[0];
                        dt.TableName = "Sheet 1";
                        string attachment = "attachment; filename=" + Convert.ToString(ddlCompany.SelectedItem.Text).Replace(' ', '_').ToString() + "_Payroll_" + strMonthName + "_" + intYear.ToString() + ".xls";
                        Response.ClearContent();
                        Response.AddHeader("content-disposition", attachment);
                        Response.ContentType = "application/vnd.ms-excel";
                        string tab = "";
                        Response.Write(strHead);
                        foreach (DataColumn dc in dt.Columns)
                        {
                            Response.Write("<td>" + dc.ColumnName + "</td>");
                        }
                        Response.Write("</tr><tr>");
                        int i;
                        foreach (DataRow dr in dt.Rows)
                        {
                            tab = "";
                            for (i = 0; i < dt.Columns.Count; i++)
                            {
                                Response.Write("<td>" + dr[i].ToString() + "</td>");
                            }
                            Response.Write("</tr><tr>");
                        }
                        Response.Write("</tr></BODY></HTML>");
                        Response.End();
                    }
                    //Export to CSV
                    else if (rbExportToCSV.Checked == true)
                    {
                        StringBuilder strData = Export(dsPayrollProcess);
                        byte[] data = System.Text.ASCIIEncoding.ASCII.GetBytes(strData.ToString());
                        string attachment = "attachment; filename=" + Convert.ToString(ddlCompany.SelectedItem.Text).Replace(' ', '_').ToString() + "_Payroll_" + strMonthName + "_" + intYear.ToString() + ".csv";
                        Response.Clear();
                        Response.AddHeader("Content-Type", "application/Excel");
                        Response.AddHeader("Content-Disposition", attachment);
                        Response.BinaryWrite(data);
                        Response.End();
                    }
                    //Export to XML
                    else if (rbExportToXML.Checked == true)
                    {
                        if (dsCustomFormat.Tables.Count > 0)
                        {
                            if (dsCustomFormat.Tables[0].Rows.Count > 0)
                            {
                                if (dsCustomFormat.Tables[0].Rows[0]["XML_RootTag"].ToString() != string.Empty)
                                {
                                    dsPayrollProcess.DataSetName = dsCustomFormat.Tables[0].Rows[0]["XML_RootTag"].ToString();
                                }
                                if (dsCustomFormat.Tables[0].Rows[0]["XML_FirstChildTag"].ToString() != string.Empty)
                                {
                                    dsPayrollProcess.Tables[0].TableName = dsCustomFormat.Tables[0].Rows[0]["XML_FirstChildTag"].ToString();
                                }
                            }
                        }
                        DeleteExistingFiles(Server.MapPath("~\\System Settings\\Download"));
                        dsPayrollProcess.WriteXml(Server.MapPath("~/System Settings/Download/" + Convert.ToString(ddlCompany.SelectedItem.Text).Replace(' ', '_').ToString() + "_Payroll_" + strMonthName + "_" + intYear.ToString() + ".xml"));
                        Response.ContentType = "text/xml";
                        string strFile = Server.MapPath("~/System Settings/Download/" + Convert.ToString(ddlCompany.SelectedItem.Text).Replace(' ', '_').ToString() + "_Payroll_" + strMonthName + "_" + intYear.ToString() + ".xml");
                        Response.AddHeader("content-disposition", "attachment; filename=" + Convert.ToString(ddlCompany.SelectedItem.Text).Replace(' ', '_').ToString() + "_Payroll_" + strMonthName + "_" + intYear.ToString() + ".xml");
                        Response.WriteFile(strFile);
                        Response.Flush();
                        Response.End();
                    }
                }
            }
        }
        //If Attendance option is selected
        else if (rbOption.SelectedIndex == 2)
        {
            //by Attandance
            Rpt_Common rptcommon = new Rpt_Common();
            string strLevel = "";
            strLevel = rptcommon.getNodes(Convert.ToInt16(ddlCompany.SelectedValue), Convert.ToString(Session["LevelRights"]));
            dsPayrollProcess = objMonthClosureEventData.PayrollByAttandance(hdn_SelectedFieldNames.Value, strMonthName, intYear, Convert.ToInt16(ddlCompany.SelectedValue), strLevel, strMonthValue, intMonthClosureId, Convert.ToString(Session["UserGroupName"]), Convert.ToInt16(ddlCompany.SelectedValue));
            if (dsPayrollProcess.Tables.Count > 0)
            {
                if (dsPayrollProcess.Tables[0].Rows.Count > 0)
                {
                    //To get custom formats.
                    dsCustomFormat = objExportData.GetCustomFormatsForExportData("Attendance", rblMonthDateWise.SelectedValue, Convert.ToInt16(ddlCompany.SelectedValue));
                    //To get custom field names.
                    dsCustomFields = objExportData.GetCustomFieldNamesForExportData("Attendance", rblMonthDateWise.SelectedValue, Convert.ToInt16(ddlCompany.SelectedValue));
                    ChangeDateTimeFormats();
                    //To change  the column name of the dataset with custom field name.
                    if (dsCustomFields.Tables.Count > 0)
                    {
                        if (dsCustomFields.Tables[0].Rows.Count > 0)
                        {
                            Hashtable CustomFields = new Hashtable();
                            foreach (DataRow row in dsCustomFields.Tables[0].Rows)
                            {
                                CustomFields.Add(row["FieldName"], row["UserDefinedFieldName"]);
                            }
                            foreach (DataColumn col in dsPayrollProcess.Tables[0].Columns)
                            {
                                if (CustomFields.Contains(col.ColumnName))
                                {
                                    if (CustomFields[col.ColumnName].ToString() != string.Empty)
                                    {
                                        col.ColumnName = CustomFields[col.ColumnName].ToString();
                                    }
                                }
                            }
                        }
                    }
                    //Export to CSV
                    if (rbExportToCSV.Checked == true)
                    {
                        StringBuilder strData = Export(dsPayrollProcess);
                        byte[] data = System.Text.ASCIIEncoding.ASCII.GetBytes(strData.ToString());
                        string attachment = "attachment; filename=" + Convert.ToString(ddlCompany.SelectedItem.Text).Replace(' ', '_').ToString() + "_Payroll_" + strMonthName + "_" + intYear.ToString() + ".csv";
                        Response.Clear();
                        Response.AddHeader("Content-Type", "application/Excel");
                        Response.AddHeader("Content-Disposition", attachment);
                        Response.BinaryWrite(data);
                        Response.End();
                    }
                    //Export to XLS
                    else if (rbExportToXLS.Checked == true)
                    {
                        dsPayrollProcess.Tables[0].TableName = "Sheet 1";
                        DataTable dt = dsPayrollProcess.Tables[0];
                        dt.TableName = "Sheet 1";
                        string attachment = "attachment; filename=" + Convert.ToString(ddlCompany.SelectedItem.Text).Replace(' ', '_').ToString() + "_Payroll_" + strMonthName + "_" + intYear.ToString() + ".xls";
                        Response.ClearContent();
                        Response.AddHeader("content-disposition", attachment);
                        Response.ContentType = "application/vnd.ms-excel";
                        string tab = "";
                        Response.Write(strHead);
                        foreach (DataColumn dc in dt.Columns)
                        {
                            Response.Write("<td>" + dc.ColumnName + "</td>");
                        }
                        Response.Write("</tr><tr>");
                        int i;
                        foreach (DataRow dr in dt.Rows)
                        {
                            tab = "";
                            for (i = 0; i < dt.Columns.Count; i++)
                            {
                                Response.Write("<td>" + dr[i].ToString() + "</td>");
                                // tab = "\t";
                            }
                            Response.Write("</tr><tr>");
                        }
                        Response.Write("</tr></BODY></HTML>");
                        Response.End();
                    }
                    //Export to XML
                    else if (rbExportToXML.Checked == true)
                    {
                        if (dsCustomFormat.Tables.Count > 0)
                        {
                            if (dsCustomFormat.Tables[0].Rows.Count > 0)
                            {
                                if (dsCustomFormat.Tables[0].Rows[0]["XML_RootTag"].ToString() != string.Empty)
                                {
                                    dsPayrollProcess.DataSetName = dsCustomFormat.Tables[0].Rows[0]["XML_RootTag"].ToString();
                                }
                                if (dsCustomFormat.Tables[0].Rows[0]["XML_FirstChildTag"].ToString() != string.Empty)
                                {
                                    dsPayrollProcess.Tables[0].TableName = dsCustomFormat.Tables[0].Rows[0]["XML_FirstChildTag"].ToString();
                                }
                            }
                        }
                        DeleteExistingFiles(Server.MapPath("~\\System Settings\\Download"));
                        dsPayrollProcess.WriteXml(Server.MapPath("~/System Settings/Download/" + Convert.ToString(ddlCompany.SelectedItem.Text).Replace(' ', '_').ToString() + "_Payroll_" + strMonthName + "_" + intYear.ToString() + ".xml"));
                        Response.ContentType = "text/xml";
                        string strFile = Server.MapPath("~/System Settings/Download/" + Convert.ToString(ddlCompany.SelectedItem.Text).Replace(' ', '_').ToString() + "_Payroll_" + strMonthName + "_" + intYear.ToString() + ".xml");
                        Response.AddHeader("content-disposition", "attachment; filename=" + Convert.ToString(ddlCompany.SelectedItem.Text).Replace(' ', '_').ToString() + "_Payroll_" + strMonthName + "_" + intYear.ToString() + ".xml");
                        Response.WriteFile(strFile);
                        Response.Flush();
                        Response.End();
                    }
                    blnProcessDataExists = true;
                }
            }
        }
        if (blnProcessDataExists == false)
        {
            string strScript = "alert('" + rm.GetString("Alert_NoProcessrecordfoundforthisduration", ci) + "')";
            Page.ClientScript.RegisterStartupScript(this.GetType(), "Alert", strScript, true);
        }
    }
    protected void rbOption_SelectedIndexChanged(object sender, EventArgs e)
    {
        hdnSelectedOption.Value = rbOption.SelectedValue;
        BindFields();
    }
    protected void rblMonthDateWise_SelectedIndexChanged(object sender, EventArgs e)
    {
        hdnSelectedDataType.Value = rblMonthDateWise.SelectedValue;
        hdn_SelectedFieldNames.Value = string.Empty;
    }
    protected void ddlCompany_SelectedIndexChanged(object sender, EventArgs e)
    {
        hdn_SelectedFieldNames.Value = string.Empty;
        int companyId;
        if (ddlCompany.SelectedValue != "-Select-")
        {
            companyId = Convert.ToInt32(ddlCompany.SelectedValue);
            filldurationDetails(companyId);
        }
        else
        {
            ddlDuration.DataSource = "";
            ddlDuration.DataBind();
            ddlDuration.Items.Insert(0, "-Select-");
            lst_PayrollFieldList.Items.Clear();
            lst_PayrollSelectedFieldList.Items.Clear();
        }
    }
    #endregion
}