using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Reports_Excel_Export : Base.BasePage
{
    GetReports rptEmp = new GetReports();
    string ReportName = string.Empty;
    string frmDate = string.Empty;
    string toDate = string.Empty;
    int rptoption = 0;
    int reportType = 0;
    protected override void Page_Load(object sender, EventArgs e)
    {
        if (Request.QueryString["ReportName"] != null)
            ReportName = Convert.ToString(Request.QueryString["ReportName"]);
        if (Request.QueryString["FrmDate"] != null)
            frmDate = Convert.ToString(Request.QueryString["FrmDate"]);
        if (Request.QueryString["toDate"] != null)
            toDate = Convert.ToString(Request.QueryString["toDate"]);
        if (Request.QueryString["rptoption"] != null)
            rptoption = Convert.ToInt32(Request.QueryString["rptoption"]);
        if (Request.QueryString["reportType"] != null)
            reportType = Convert.ToInt32(Request.QueryString["reportType"]);
        if (Session["Export_ExcelData"] != null)
        {
            try
            {
                ExportToExcel((DataSet)Session["Export_ExcelData"], ReportName, frmDate, toDate);
                Session["Export_ExcelData"] = null;
            }
            catch (Exception ex)
            {
                Message(ex.ToString());
            }
        }
    }
    public void ExportToExcel(DataSet dsData, string rptName, string frmDate, string toDate)
    {
        dsData = RemoveUnwantedFields(rptName, dsData);

        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 += ".style0{color:#000000;font-size:11.0pt;font-weight:400;font-style:normal;text-decoration:none;font-family:Calibri;mso-font-charset:134;border:none;mso-protection:locked visible;mso-style-name:'Normal';mso-style-id:0;}";
        strHead += "td{mso-style-parent:style0;padding-top:1px;padding-right:1px;padding-left:1px;mso-ignore:padding;color:#000000;font-size:11.0pt;font-weight:400;font-style:normal;text-decoration:none;font-family:Calibri;mso-font-charset:134;border:none;mso-protection:locked visible;}";
        strHead += ".xl65{mso-style-parent:style0;text-align:center;white-space:normal;font-size:14.0pt;font-weight:700;mso-generic-font-family:auto;mso-font-charset:134;border:.5pt solid windowtext;}";
        strHead += ".xl66{ mso - style - parent:style0; text - align:center; white - space:normal; font - size:16.0pt; mso - font - charset:134; border: .5pt solid windowtext;}";
        strHead += ".xl67{mso-style-parent:style0;text-align:center;white-space:normal;font-size:12.0pt;font-weight:700;mso-generic-font-family:auto;mso-font-charset:134;	border:.5pt solid windowtext;}";
        strHead += ".xl68{ mso - style - parent:style0; text - align:center; white - space:normal; mso - pattern:auto none; background:#00B0F0;color:#F2F2F2;font-size:12.0pt;font-weight:700;mso-font-charset:134;border:.5pt solid windowtext;}";
        strHead += ".xl69{mso-style-parent:style0; text - align:center;white-space:normal;mso-pattern:autonone;background:#00B0F0;color:#F2F2F2;font-size:12.0pt;font-weight:700;mso-font-charset:134;border:.5ptsolidwindowtext;}";
        strHead += ".xl70{mso-style-parent:style0;white-space:normal;mso-font-charset:134;border:.5pt solid windowtext;}";
        strHead += ".xl71{mso-style-parent:style0;mso-number-format:'dd\\-mm\\-yyyy';white-space:normal;mso-font-charset:134;border:.5pt solid windowtext;}";
        strHead += "td{ mso - style - parent:style0; padding - top:1px; padding - right:1px; padding - left:1px; mso - ignore:padding; color:#000000;font-size:11.0pt;font-weight:400;font-style:normal;text-decoration:none;font-family:Calibri;mso-font-charset:134;border:none;mso-protection:locked visible;}";
        strHead += "--></style>";
        strHead += "<!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>";
        strHead += "<x:Name>" + rptName + " Report</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 width='868' style='width:651.00pt;border-collapse:collapse;table-layout:fixed;' border='0' cellspacing='0' cellpadding='0'><colgroup><col width='45' style='mso-width-source:userset;mso-width-alt:1645;'><col width='90' style='mso-width-source:userset;mso-width-alt:3291;'><col width='200' style='mso-width-source:userset;mso-width-alt:7314;'><col width='171' style='mso-width-source:userset;mso-width-alt:6253;'><col width='55' style='mso-width-source:userset;mso-width-alt:2011;'><col width='78' style='mso-width-source:userset;mso-width-alt:2852;'><col width='32' style='mso-width-source:userset;mso-width-alt:1170;'><col width='88' style='mso-width-source:userset;mso-width-alt:3218;'><col width='109' style='mso-width-source:userset;mso-width-alt:3986;'></colgroup>";
        DataTable dt;
        DataTable dt1 = new DataTable();
        if (rptName == "OverTimeSanction")
        {
            if (dsData.Tables.Count > 1)
            {
                if (dsData.Tables[1] != null)
                {
                    dt1 = dsData.Tables[1];
                }
            }
        }

        switch (rptoption)
        {
            case 0:
                dt = dsData.Tables[0];
                break;
            default:
                dt = dsData.Tables[1];
                break;
        }
        string saveAsFileName = string.Empty;
        int colspan = GetColspanValue(rptName);
        if (rptName == "Canteen")
        {
            switch (rptoption)
            {
                case 0:
                    saveAsFileName = string.Format("Canteen - Occasion wise Detail " + "Report-{0:d}", DateTime.Now).Replace("/", "-").Replace(" ", "");
                    colspan = 8;
                    break;
                case 1:
                    saveAsFileName = string.Format("Canteen - Occasion wise Summary " + "Report-{0:d}", DateTime.Now).Replace("/", "-").Replace(" ", "");
                    colspan = 6;
                    break;
            }
        }
        else
        {
            saveAsFileName = string.Format(rptName + "Report-{0:d}", DateTime.Now).Replace("/", "-").Replace(" ", "");
        }
        string attachment = "attachment; filename=" + saveAsFileName + ".xls";
        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/vnd.ms-excel";
        Response.Write(strHead);
        DataTable dtLevelName = new DataTable();
        if (colspan == 9)
        {
            colspan = dt.Columns.Count + 1;
        }
        if (rptName != "OverTimeSanction")
        {
            if (Convert.ToInt32(Session["parentLevelId"]) != 0)
            {
                dtLevelName = rptEmp.getHeader(Convert.ToInt32(Session["parentLevelId"]));
                if (dtLevelName.Rows.Count > 0)
                {
                    Response.Write("<tr height='28' style='height:21.00pt;'>");
                    Response.Write("<td width = '1060.47' height = '28' class='xl65' style='height:21.00pt;width:795.35pt;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;' colspan=" + colspan + ">" + Convert.ToString(dtLevelName.Rows[0][0]) + "</td>");
                    Response.Write("</tr>");
                }
            }
            Response.Write("<tr height='25' style='height:19.50pt;'>");
            if (rptName == "OnDutyEntry")
                rptName = "OnDuty Entry";
            else if (rptName == "LateEntry")
                rptName = "Late Entry";
            else if (rptName == "EarlyExit")
                rptName = "Early Exit";
            else if (rptName == "ShiftNotAllocated")
                rptName = "Shift Not Allocated";

            switch (rptName)
            {
                case "Canteen":
                    if (rptoption == 0)
                    {
                        Response.Write("<td height='26' class='xl67' style='height: 19.50pt; border - right:.5pt solid windowtext; border - bottom:.5pt solid windowtext;' colspan=" + colspan + " x:str>" + "Canteen - Occasion wise Detail " + " Report From - " + frmDate + " To " + toDate + "</td>");
                    }
                    else if (rptoption == 1)
                    {
                        Response.Write("<td height='26' class='xl67' style='height: 19.50pt; border - right:.5pt solid windowtext; border - bottom:.5pt solid windowtext;' colspan=" + colspan + " x:str>" + "Canteen - Occasion wise Summary " + " Report From - " + frmDate + " To " + toDate + "</td>");
                    }
                    break;
                default:
                    Response.Write("<td height='26' class='xl67' style='height: 19.50pt; border - right:.5pt solid windowtext; border - bottom:.5pt solid windowtext;' colspan=" + colspan + " x:str>" + rptName + " Report From - " + frmDate + " To " + toDate + "</td>");
                    break;
            }
            Response.Write("</tr>");
        }
        Response.Write("<tr height='21' style='height:15.75pt;mso-height-source:userset;mso-height-alt:315;'>");
        int increment = 1;
        Response.Write("<td height='21' class='xl69' style='height: 15.75pt;' x:str>Sl.No.</td>");
        int colnCount = 0;
        foreach (DataColumn dc in dt.Columns)
        {
            switch (ReportName)
            {
                case "Daily Wages Penalty Summary":
                case "Daily Wages Penalty Detail":
                    switch (dc.ColumnName.ToUpper())
                    {
                        case "MONTHNO":
                            continue;
                    }
                    break;
                //Need to check

                case "Worked Days":
                    switch (dc.ColumnName.ToUpper())
                    {
                        case "TOTAL DAYS WORKED1":
                            continue;
                    }
                    break;
                case "Monthly Attendance":
                    if (reportType != 1)
                    {
                        if (Convert.ToString(Session["packageID"]) != "" && Convert.ToString(Session["packageID"]) == "161")
                        {
                            if (colnCount > Convert.ToInt32(24))  //Ardlysis monthly attendance correction did Nov 28
                                continue;
                        }
                    }
                    break;
                case "Labour - Monthly Attendance":

                    if (colnCount > 15)
                        continue;
                    break;
                case "OverTimeSanction":
                    if (colnCount > 30)
                        continue;
                    break;
            }
            colnCount += 1;
            if ((rptName == "OverTimeSanction") && (dc.ColumnName.ToUpper() == "HRSOT" || dc.ColumnName.ToUpper() == "OFFOT" || dc.ColumnName.ToUpper() == "HOLOT"))
            {
                switch (dc.ColumnName.ToUpper())
                {
                    case "HRSOT":
                        Response.Write("<td class='xl68'>" + Convert.ToString(HRSOT.Text) + "</td>");
                        break;
                    case "OFFOT":
                        Response.Write("<td class='xl68'>" + Convert.ToString(OFFOT.Text) + "</td>");
                        break;
                    case "HOLOT":
                        Response.Write("<td class='xl68'>" + Convert.ToString(HOLOT.Text) + "</td>");
                        break;
                }
            }
            else
            if (rptName == "Canteen" && (dc.ColumnName.ToUpper() == "PUNCHDATE" || dc.ColumnName.ToUpper() == "PUNCHTYPENAME" || dc.ColumnName.ToUpper() == "DEPTWISEPUNCHCOUNT" ||
                dc.ColumnName.ToUpper() == "LEVEL NAME" || dc.ColumnName.ToUpper() == "LEVELNAME" || dc.ColumnName.ToUpper() == "SHIFT_SHORTNAME" || dc.ColumnName.ToUpper() == "EMPLOYEE_FIRSTNAME" ||
                 dc.ColumnName.ToUpper() == "CLK_PUNCHDATETIME" || dc.ColumnName.ToUpper() == "CLK_PUNCHTYPE" || dc.ColumnName.ToUpper() == "CLK_DATE" || dc.ColumnName.ToUpper() == "EMPLOYEE_CODE_ID"))
            {
                switch (dc.ColumnName.ToUpper())
                {
                    case "EMPLOYEE_CODE_ID":
                        Response.Write("<td class='xl68'>Employee ID</td>");
                        break;
                    case "PUNCHDATE":
                        Response.Write("<td class='xl68'>Date</td>");
                        break;
                    case "PUNCHTYPENAME":
                        Response.Write("<td class='xl68'>Occasion Description</td>");
                        break;
                    case "DEPTWISEPUNCHCOUNT":
                        Response.Write("<td class='xl68'>No. of Employee(s)</td>");
                        break;
                    case "LEVEL NAME":
                        Response.Write("<td class='xl68'>Department Name</td>");
                        break;
                    case "LEVELNAME":
                        Response.Write("<td class='xl68'>Department Name</td>");
                        break;
                    case "SHIFT_SHORTNAME":
                        Response.Write("<td class='xl68'>Shift</td>");
                        break;
                    case "EMPLOYEE_FIRSTNAME":
                        Response.Write("<td class='xl68'>Employee Name</td>");
                        break;
                    case "CLK_PUNCHDATETIME":
                        Response.Write("<td class='xl68'>Punch Time</td>");
                        break;
                    case "CLK_PUNCHTYPE":
                        Response.Write("<td class='xl68'>Occasion</td>");
                        break;
                    case "CLK_DATE":
                        Response.Write("<td class='xl68'>Date</td>");
                        break;
                }
            }

            else
            if ((rptName == "Monthly Attendance" && Convert.ToString(Session["packageID"]) != "" && Convert.ToString(Session["packageID"]) == "161")
                && (dc.ColumnName.ToUpper() == "INTIME1" || dc.ColumnName.ToUpper() == "OUTTIME1" || dc.ColumnName.ToUpper() == "INTIME2" ||
                dc.ColumnName.ToUpper() == "OUTTIME2" || dc.ColumnName.ToUpper() == "INTIME3" || dc.ColumnName.ToUpper() == "OUTTIME3" ||
                 dc.ColumnName.ToUpper() == "INTIME4" || dc.ColumnName.ToUpper() == "OUTTIME4" || dc.ColumnName.ToUpper() == "INTIME5" ||
                  dc.ColumnName.ToUpper() == "OUTTIME5" || dc.ColumnName.ToUpper() == "ACUTALWORKEDHOURS"
                ))
            {
                switch (dc.ColumnName.ToUpper())
                {
                    case "INTIME1":
                        Response.Write("<td class='xl68'>In Time 1</td>");
                        break;
                    case "OUTTIME1":
                        Response.Write("<td class='xl68'>Out Time 1</td>");
                        break;

                    case "INTIME2":
                        Response.Write("<td class='xl68'>In Time 2</td>");
                        break;
                    case "OUTTIME2":
                        Response.Write("<td class='xl68'>Out Time 2</td>");
                        break;

                    case "INTIME3":
                        Response.Write("<td class='xl68'>In Time 3</td>");
                        break;
                    case "OUTTIME3":
                        Response.Write("<td class='xl68'>Out Time 3</td>");
                        break;

                    case "INTIME4":
                        Response.Write("<td class='xl68'>In Time 4</td>");
                        break;
                    case "OUTTIME4":
                        Response.Write("<td class='xl68'>Out Time 4</td>");
                        break;

                    case "INTIME5":
                        Response.Write("<td class='xl68'>In Time 5</td>");
                        break;
                    case "OUTTIME5":
                        Response.Write("<td class='xl68'>Out Time 5</td>");
                        break;

                    case "ACUTALWORKEDHOURS":
                        Response.Write("<td class='xl68'>Acutal Worked Hours</td>");
                        break;
                }
            }
            else if (rptName == "Over Time" && (dc.ColumnName.ToUpper() == "ACTHOLOT" || dc.ColumnName.ToUpper() == "ACTOFFOT" || dc.ColumnName.ToUpper() == "ACTHRSOT" || dc.ColumnName.ToUpper() == "HRSOT" || dc.ColumnName.ToUpper() == "OFFOT" || dc.ColumnName.ToUpper() == "HOLOT"))
            {
                switch (dc.ColumnName.ToUpper())
                {
                    case "HRSOT":
                        Response.Write("<td class='xl68'>Modified Week day OT</td>");
                        break;
                    case "OFFOT":
                        Response.Write("<td class='xl68'>Modified Week Off OT</td>");
                        break;
                    case "HOLOT":
                        Response.Write("<td class='xl68'>Modified Holiday OT</td>");
                        break;
                    case "ACTHRSOT":
                        Response.Write("<td class='xl68'>Week day OT</td>");
                        break;
                    case "ACTOFFOT":
                        Response.Write("<td class='xl68'>Weekly Off OT</td>");
                        break;
                    case "ACTHOLOT":
                        Response.Write("<td class='xl68'>Holiday OT</td>");
                        break;
                }
            }

            else
            {
                if (rptName == "OverTimeSanction")
                {
                    if (dt1 != null)
                    {
                        if (dt1.Rows.Count > 0)
                        {
                            switch (dc.ColumnName)
                            {
                                case "SPEC_NormOT1":
                                case "Modified_Spec_NormOTHrs1":
                                    if (Convert.ToInt32(dt1.Rows[0]["isShownSpecialOT"]) == 0)
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        GenerateExcelHeaders(dc);
                                    }
                                    break;
                                case "SPEC_NormOT2":
                                case "Modified_Spec_NormOTHrs2":
                                    if (Convert.ToInt32(dt1.Rows[0]["NormalSPOT2"]) == 0)
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        GenerateExcelHeaders(dc);
                                    }
                                    break;
                                case "SPEC_OffOT1":
                                case "Modified_Spec_OffOTHrs1":
                                    if (Convert.ToInt32(dt1.Rows[0]["OffSPOT1"]) == 0)
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        GenerateExcelHeaders(dc);
                                    }
                                    break;
                                case "SPEC_OffOT2":
                                case "Modified_Spec_OffOTHrs2":
                                    if (Convert.ToInt32(dt1.Rows[0]["OffSPOT2"]) == 0)
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        GenerateExcelHeaders(dc);
                                    }
                                    break;
                                case "SPEC_HOLOT1":
                                case "Modified_Spec_HolOTHrs1":
                                    if (Convert.ToInt32(dt1.Rows[0]["HolidaySPOT1"]) == 0)
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        GenerateExcelHeaders(dc);
                                    }
                                    break;
                                case "SPEC_HOLOT2":
                                case "Modified_Spec_HolOTHrs2":
                                    if (Convert.ToInt32(dt1.Rows[0]["HolidaySPOT2"]) == 0)
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        GenerateExcelHeaders(dc);
                                    }
                                    break;
                                case "Shift_Allow":
                                case "Modified_Shift_Allow":
                                    if (Convert.ToInt32(dt1.Rows[0]["ShiftAllow"]) == 0)
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        GenerateExcelHeaders(dc);
                                    }
                                    break;
                                case "Food_Allow":
                                case "Modified_Food_Allow":
                                    if (Convert.ToInt32(dt1.Rows[0]["FoodAllow"]) == 0)
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        GenerateExcelHeaders(dc);
                                    }
                                    break;
                                case "Meal_Allow":
                                case "Modified_Meal_Allow":
                                    if (Convert.ToInt32(dt1.Rows[0]["MealAllow"]) == 0)
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        GenerateExcelHeaders(dc);
                                    }
                                    break;
                                case "SH_Allow":
                                case "Modified_SH_Allow":
                                    if (Convert.ToInt32(dt1.Rows[0]["SHAllow"]) == 0)
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        GenerateExcelHeaders(dc);
                                    }
                                    break;
                                default:
                                    GenerateExcelHeaders(dc);
                                    break;
                            }
                        }
                    }
                }
                else
                {
                    GenerateExcelHeaders(dc);
                }
            }
        }
        Response.Write("</tr><tr height='40' style='height:16.00pt;'>");
        foreach (DataRow dr in dt.Rows)
        {
            Response.Write("<td height='20' class='xl70' style='height: 16.00pt;' x:num>" + increment + "</td>");
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if ((rptName == "Daily Wages Penalty Summary" || rptName == "Daily Wages Penalty Detail") && i > 9)
                    break;
                if (rptName == "Labour - Monthly Attendance" && i > 15)
                    break;
                else if (rptName == "Early Entry" && i > 9)
                    break;
                else if (rptName == "Early Exit" && i > 9)
                    break;
                else if (rptName == "Worked Days" && i > 5)
                    break;                
                else if (rptName == "Punctuality" && i > 5)
                    break;
                else if (rptName == "General Monthly" && i > 12)
                    break;
                else if (rptName == "Raw Data" && i > 6)
                    break;
                else if (rptName == "Monthly Attendance")
                {
                    if (reportType != 1)
                    {
                        if (Convert.ToString(Session["packageID"]) != "" && Convert.ToString(Session["packageID"]) == "161")
                        {
                            if (i > Convert.ToInt32(24))   //Ardlysis monthly attendance correction did Nov 28
                                break;
                        }
                        else
                        {
                            if (i > Convert.ToInt32(11))   //Demo and others
                                break;
                        }
                    }
                }
                else if (rptName == "Punctuality" && i > 8)
                    break;
                else if (rptName == "Worked Days" && i > 5)
                    break;
                else if (rptName == "OverTimeSanction" && i > 30)
                {
                    break;
                }
                if (rptName == "OverTimeSanction")
                {
                    if (dt1 != null)
                    {
                        if (dt1.Rows.Count > 0)
                        {
                            switch (i)
                            {
                                case 10:
                                case 11:
                                    if (Convert.ToInt32(dt1.Rows[0]["isShownSpecialOT"]) == 1)
                                    {
                                        Response.Write("<td class='xl70'>" + dr[i].ToString() + "</td>");
                                    }
                                    break;
                                case 12:
                                case 13:
                                    if (Convert.ToInt32(dt1.Rows[0]["NormalSPOT2"]) == 1)
                                    {
                                        Response.Write("<td class='xl70'>" + dr[i].ToString() + "</td>");
                                    }
                                    break;
                                case 14:
                                case 15:
                                    if (Convert.ToInt32(dt1.Rows[0]["OffSPOT1"]) == 1)
                                    {
                                        Response.Write("<td class='xl70'>" + dr[i].ToString() + "</td>");
                                    }
                                    break;
                                case 16:
                                case 17:
                                    if (Convert.ToInt32(dt1.Rows[0]["OffSPOT2"]) == 1)
                                    {
                                        Response.Write("<td class='xl70'>" + dr[i].ToString() + "</td>");
                                    }
                                    break;
                                case 18:
                                case 19:
                                    if (Convert.ToInt32(dt1.Rows[0]["HolidaySPOT1"]) == 1)
                                    {
                                        Response.Write("<td class='xl70'>" + dr[i].ToString() + "</td>");
                                    }
                                    break;
                                case 20:
                                case 21:
                                    if (Convert.ToInt32(dt1.Rows[0]["HolidaySPOT2"]) == 1)
                                    {
                                        Response.Write("<td class='xl70'>" + dr[i].ToString() + "</td>");
                                    }
                                    break;
                                case 22:
                                case 23:
                                    if (Convert.ToInt32(dt1.Rows[0]["ShiftAllow"]) == 1)
                                    {
                                        Response.Write("<td class='xl70'>" + dr[i].ToString() + "</td>");
                                    }
                                    break;
                                case 24:
                                case 25:
                                    if (Convert.ToInt32(dt1.Rows[0]["FoodAllow"]) == 1)
                                    {
                                        Response.Write("<td class='xl70'>" + dr[i].ToString() + "</td>");
                                    }
                                    break;
                                case 26:
                                case 27:
                                    if (Convert.ToInt32(dt1.Rows[0]["MealAllow"]) == 1)
                                    {
                                        Response.Write("<td class='xl70'>" + dr[i].ToString() + "</td>");
                                    }
                                    break;
                                case 28:
                                case 29:
                                    if (Convert.ToInt32(dt1.Rows[0]["SHAllow"]) == 1)
                                    {
                                        Response.Write("<td class='xl70'>" + dr[i].ToString() + "</td>");
                                    }
                                    break;
                                default:
                                    Response.Write("<td class='xl70'>" + dr[i].ToString() + "</td>");
                                    break;
                            }
                        }
                    }
                }
                else
                {
                    Response.Write("<td class='xl70'>" + dr[i].ToString() + "</td>");
                }
            }
            increment += 1;
            Response.Write("</tr>");
        }
        Response.Write("</BODY></HTML>");
        Response.End();
        return;
    }
    private DataSet RemoveUnwantedFields(string rptName, DataSet dsEmp)
    {
        switch (rptName)
        {
            case "Absent":
            case "Absentees":
                #region AbsentReport
                if (dsEmp.Tables[0].Columns.Contains("Name"))
                    dsEmp.Tables[0].Columns["Name"].ColumnName = "Department";
                if (dsEmp.Tables[0].Columns.Contains("Gender"))
                    dsEmp.Tables[0].Columns.Remove("Gender");
                #endregion
                break;
            case "Leave_Angler":
            case "Leave":
                #region LeaveReport
                if (dsEmp.Tables.Count > 0)
                {
                    if (dsEmp.Tables[0].Rows.Count > 0)
                    {
                        if (dsEmp.Tables[0].Columns.Contains("Remarks"))
                        {
                            dsEmp.Tables[0].Columns.Remove("Remarks");
                        }
                        if (dsEmp.Tables[0].Columns.Contains("display"))
                        {
                            dsEmp.Tables[0].Columns.Remove("display");
                        }
                        if (dsEmp.Tables[0].Columns.Contains("applicationDate"))
                        {
                            dsEmp.Tables[0].Columns.Remove("applicationDate");
                        }
                        if (dsEmp.Tables[0].Columns.Contains("name"))
                        {
                            //dsEmp.Tables[0].Columns.Remove("name");
                            dsEmp.Tables[0].Columns["name"].ColumnName = "Department Name";
                        }
                        if (dsEmp.Tables[0].Columns.Contains("Gender"))
                        {
                            dsEmp.Tables[0].Columns.Remove("Gender");
                        }
                        if (dsEmp.Tables[0].Columns.Contains("fromDate"))
                        {
                            dsEmp.Tables[0].Columns["fromDate"].ColumnName = "From Date";
                        }
                        if (dsEmp.Tables[0].Columns.Contains("todate"))
                        {
                            dsEmp.Tables[0].Columns["todate"].ColumnName = "To Date";
                        }
                        if (dsEmp.Tables[0].Columns.Contains("NumberOfDays"))
                        {
                            dsEmp.Tables[0].Columns["NumberOfDays"].ColumnName = "Number Of Days";
                        }
                        if (dsEmp.Tables[0].Columns.Contains("leavetype"))
                        {
                            dsEmp.Tables[0].Columns["leavetype"].ColumnName = "Leave Type";
                        }
                        if (dsEmp.Tables[0].Columns.Contains("Leave_SessionFrom"))
                        {
                            dsEmp.Tables[0].Columns["Leave_SessionFrom"].ColumnName = "From Session";
                        }
                        if (dsEmp.Tables[0].Columns.Contains("Leave_SessionTo"))
                        {
                            dsEmp.Tables[0].Columns["Leave_SessionTo"].ColumnName = "To Session";
                        }
                        if (dsEmp.Tables[0].Columns.Contains("Leave_SessionTo"))
                        {
                            dsEmp.Tables[0].Columns["Leave_SessionTo"].ColumnName = "To Session";
                        }
                    }
                }
                #endregion
                break;
            case "MonthlyAttendance":
            case "Present":
                #region MonthlyAttendance
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("EARLYOUT1"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("EARLYOUT1");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("LATEIN1"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("LATEIN1");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("NORMOT1"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("NORMOT1");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("OFFOT1"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("OFFOT1");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("HOLOT1"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("HOLOT1");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("Intime1"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("Intime1");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("Intime2"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("Intime2");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("Intime3"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("Intime3");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("Intime4"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("Intime4");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("OutTime1"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("OutTime1");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("OutTime2"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("OutTime2");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("OutTime3"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("OutTime3");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("OutTime4"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("OutTime4");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("OutTime5"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("OutTime5");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("AcutalWorkedHours"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("AcutalWorkedHours");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("Intime5"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("Intime5");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("Department"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("Department");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("SECONDSESSION"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("SECONDSESSION");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("BREAKHOURS"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("BREAKHOURS");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("LUNCHHOURS"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("LUNCHHOURS");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("BRANCHNAME"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("BRANCHNAME");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("SPEC_NormOT1"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("SPEC_NormOT1");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("SPEC_NormOT2"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("SPEC_NormOT2");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("OFFOT"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("OFFOT");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("SPEC_OffOT1"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("SPEC_OffOT1");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("SPEC_OffOT2"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("SPEC_OffOT2");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("HOLOT"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("HOLOT");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("SPEC_HOLOT1"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("SPEC_HOLOT1");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("SPEC_HOLOT2"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("SPEC_HOLOT2");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("SUPPOSEDWORKHOURS"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("SUPPOSEDWORKHOURS");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("TOTALMINSWORKED"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("TOTALMINSWORKED");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("StatusCode"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("StatusCode");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("NORMOTMIN"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("NORMOTMIN");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("OFFOTMIN"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("OFFOTMIN");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("HOLOTMIN"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("HOLOTMIN");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("LATEINMIN"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("LATEINMIN");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("EARLYOUTMIN"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("EARLYOUTMIN");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("TOTDEFWORKHRS"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("TOTDEFWORKHRS");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("TOTSUPWORKHRS"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("TOTSUPWORKHRS");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("OVERTIMESTATUS"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("OVERTIMESTATUS");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("NIGHTORMORN"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("NIGHTORMORN");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("LUNCHINTIME"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("LUNCHINTIME");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("LUNCHOUTTIME"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("LUNCHOUTTIME");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("INNOTATION"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("INNOTATION");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("OUTNOTATION"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("OUTNOTATION");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("SHIFT_STARTTIME"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("SHIFT_STARTTIME");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("SHIFT_ENDTIME"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("SHIFT_ENDTIME");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("BREAKHOURSMINS"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("BREAKHOURSMINS");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("LUNCHHOURSMINS"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("LUNCHHOURSMINS");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("WEEKDAY_WORKHRS"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("WEEKDAY_WORKHRS");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("WEEKNUMBER"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("WEEKNUMBER");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("FNATTENDANCECODE"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("FNATTENDANCECODE");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("ANATTENDANCECODE"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("ANATTENDANCECODE");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("DESIGNATION"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("DESIGNATION");
                //if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("BRANCHNAME"))
                //    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("BRANCHNAME");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("processDate"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("processDate");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("processDay"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("processDay");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("processMonth"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("processMonth");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("OVERTIME"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("OVERTIME");
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("BRANCHNAME"))
                    dsEmp.Tables[0].DefaultView.Table.Columns["BRANCHNAME"].ColumnName = "Level";
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("FirstSession"))
                    dsEmp.Tables[0].DefaultView.Table.Columns["FirstSession"].ColumnName = "Attendance";
                if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("NORMOT"))
                    dsEmp.Tables[0].DefaultView.Table.Columns.Remove("NORMOT");
                if (dsEmp.Tables[0].Columns.Contains("MonthNo"))
                    dsEmp.Tables[0].Columns.Remove("MonthNo");
                #endregion
                break;
            case "LateEntry":
                #region LateEntry
                if (dsEmp.Tables.Count > 0)
                {
                    if (dsEmp.Tables[0].Rows.Count > 0)
                    {
                        if (dsEmp.Tables[0].Columns.Contains("Peocess_Rec_ID"))
                            dsEmp.Tables[0].Columns.Remove("Peocess_Rec_ID");
                        if (dsEmp.Tables[0].Columns.Contains("Gender"))
                            dsEmp.Tables[0].Columns.Remove("Gender");
                        if (dsEmp.Tables[0].Columns.Contains("LateInMins"))
                            dsEmp.Tables[0].Columns.Remove("LateInMins");
                        if (dsEmp.Tables[0].Columns.Contains("format"))
                            dsEmp.Tables[0].Columns.Remove("format");
                    }
                }
                #endregion
                break;
            case "Punctuality":
                #region Punctuality
                if (dsEmp.Tables.Count > 0)
                {
                    if (dsEmp.Tables[0].Rows.Count > 0)
                    {
                        if (dsEmp.Tables[0].Columns.Contains("shift_shortName"))
                            dsEmp.Tables[0].Columns.Remove("shift_shortName");
                        if (dsEmp.Tables[0].Columns.Contains("shift_Id"))
                            dsEmp.Tables[0].Columns.Remove("shift_Id");
                        if (dsEmp.Tables[0].Columns.Contains("Level Name"))
                            dsEmp.Tables[0].Columns.Remove("Level Name");
                        if (dsEmp.Tables[0].Columns.Contains("TitleName"))
                            dsEmp.Tables[0].Columns.Remove("TitleName");
                        if (dsEmp.Tables[0].Columns.Contains("format"))
                            dsEmp.Tables[0].Columns.Remove("format");
                    }
                }
                #endregion
                break;
            case "EarlyExit":
                #region EarlyExit
                if (dsEmp.Tables.Count > 0)
                {
                    if (dsEmp.Tables[0].Rows.Count > 0)
                    {
                        if (dsEmp.Tables[0].Columns.Contains("Process_Rec_ID"))
                            dsEmp.Tables[0].Columns.Remove("Process_Rec_ID");
                        if (dsEmp.Tables[0].Columns.Contains("Gender"))
                            dsEmp.Tables[0].Columns.Remove("Gender");
                        if (dsEmp.Tables[0].Columns.Contains("LevelId"))
                            dsEmp.Tables[0].Columns.Remove("LevelId");
                        if (dsEmp.Tables[0].Columns.Contains("format"))
                            dsEmp.Tables[0].Columns.Remove("format");
                        if (dsEmp.Tables[0].Columns.Contains("shift_id"))
                            dsEmp.Tables[0].Columns.Remove("shift_id");
                        if (dsEmp.Tables[0].Columns.Contains("EarlyOutMins"))
                            dsEmp.Tables[0].Columns.Remove("EarlyOutMins");
                    }
                }
                #endregion
                break;
            case "OnDutyEntry":
            case "OnDuty":
                #region OnDuty
                if (dsEmp.Tables.Count > 0)
                {
                    if (dsEmp.Tables[0].Rows.Count > 0)
                    {
                        if (dsEmp.Tables[0].Columns.Contains("Gender"))
                            dsEmp.Tables[0].Columns.Remove("Gender");
                        if (dsEmp.Tables[0].Columns.Contains("Onduty_ID"))
                            dsEmp.Tables[0].Columns.Remove("Onduty_ID");
                        if (dsEmp.Tables[0].Columns.Contains("name"))
                            dsEmp.Tables[0].Columns["name"].ColumnName = "Level Name";
                        if (dsEmp.Tables[0].Columns.Contains("Onduty_ToDate"))
                            dsEmp.Tables[0].Columns["Onduty_ToDate"].ColumnName = "Out Date";
                        if (dsEmp.Tables[0].Columns.Contains("Onduty_ToTime"))
                            dsEmp.Tables[0].Columns["Onduty_ToTime"].ColumnName = "Out Time";
                        if (dsEmp.Tables[0].Columns.Contains("Onduty_EntryDate"))
                            dsEmp.Tables[0].Columns["Onduty_EntryDate"].ColumnName = "Entry Date";
                    }
                }
                #endregion
                break;
            case "ShiftNotAllocated":
                #region ShiftNotAllocated
                if (dsEmp.Tables[0].Rows.Count > 0)
                {


                    if (dsEmp.Tables[0].Columns.Contains("TotalMinsWorked"))
                        dsEmp.Tables[0].Columns.Remove("TotalMinsWorked");
                    if (dsEmp.Tables[0].Columns.Contains("TotalLessMins"))
                        dsEmp.Tables[0].Columns.Remove("TotalLessMins");
                    if (dsEmp.Tables[0].Columns.Contains("SHIFT_SHORTNAME"))
                        dsEmp.Tables[0].Columns.Remove("SHIFT_SHORTNAME");
                    if (dsEmp.Tables[0].Columns.Contains("Gender"))
                        dsEmp.Tables[0].Columns.Remove("Gender");
                    if (dsEmp.Tables[0].Columns.Contains("TOTALMINSALLOCATED"))
                        dsEmp.Tables[0].Columns.Remove("TOTALMINSALLOCATED");
                    if (dsEmp.Tables[0].Columns.Contains("BRANCHNAME"))
                        dsEmp.Tables[0].Columns.Remove("BRANCHNAME");
                }
                #endregion
                break;
            case "Discrepancy":
                #region Discrepancy
                if (dsEmp.Tables[0].Rows.Count > 0)
                {
                    if (dsEmp.Tables[0].Columns.Contains("clk_id"))
                        dsEmp.Tables[0].Columns.Remove("clk_id");
                    if (dsEmp.Tables[0].Columns.Contains("clk_EmpId"))
                        dsEmp.Tables[0].Columns["clk_EmpId"].ColumnName = "Employee ID";
                    if (dsEmp.Tables[0].Columns.Contains("clk_InTime"))
                        dsEmp.Tables[0].Columns["clk_InTime"].ColumnName = "In Time";
                    if (dsEmp.Tables[0].Columns.Contains("clk_OutTime"))
                        dsEmp.Tables[0].Columns["clk_OutTime"].ColumnName = "Out Time";
                    if (dsEmp.Tables[0].Columns.Contains("ClockDate"))
                        dsEmp.Tables[0].Columns["ClockDate"].ColumnName = "Shift Date";
                    if (dsEmp.Tables[0].Columns.Contains("format"))
                        dsEmp.Tables[0].Columns.Remove("format");
                    if (dsEmp.Tables[0].Columns.Contains("Gender"))
                        dsEmp.Tables[0].Columns.Remove("Gender");
                    if (dsEmp.Tables[0].Columns.Contains("Level Name"))
                        dsEmp.Tables[0].Columns["Level Name"].ColumnName = "Level";
                }
                #endregion
                break;
            case "Attendance Summary":
                #region AttendanceSummary
                if (dsEmp.Tables.Count > 0)
                {
                    if (dsEmp.Tables[0].Rows.Count > 0)
                    {
                        if (dsEmp.Tables[0].Columns.Contains("EARLYOUT1"))
                            dsEmp.Tables[0].Columns.Remove("EARLYOUT1");
                        if (dsEmp.Tables[0].Columns.Contains("LATEIN1"))
                            dsEmp.Tables[0].Columns.Remove("LATEIN1");
                        if (dsEmp.Tables[0].Columns.Contains("NORMOT1"))
                            dsEmp.Tables[0].Columns.Remove("NORMOT1");
                        if (dsEmp.Tables[0].Columns.Contains("OFFOT1"))
                            dsEmp.Tables[0].Columns.Remove("OFFOT1");
                        if (dsEmp.Tables[0].Columns.Contains("HOLOT1"))
                            dsEmp.Tables[0].Columns.Remove("HOLOT1");
                        if (dsEmp.Tables[0].Columns.Contains("Intime1"))
                            dsEmp.Tables[0].Columns.Remove("Intime1");
                        if (dsEmp.Tables[0].Columns.Contains("Intime2"))
                            dsEmp.Tables[0].Columns.Remove("Intime2");
                        if (dsEmp.Tables[0].Columns.Contains("Intime3"))
                            dsEmp.Tables[0].Columns.Remove("Intime3");
                        if (dsEmp.Tables[0].Columns.Contains("Intime4"))
                            dsEmp.Tables[0].Columns.Remove("Intime4");
                        if (dsEmp.Tables[0].Columns.Contains("OutTime1"))
                            dsEmp.Tables[0].Columns.Remove("OutTime1");
                        if (dsEmp.Tables[0].Columns.Contains("OutTime2"))
                            dsEmp.Tables[0].Columns.Remove("OutTime2");
                        if (dsEmp.Tables[0].Columns.Contains("OutTime3"))
                            dsEmp.Tables[0].Columns.Remove("OutTime3");
                        if (dsEmp.Tables[0].Columns.Contains("OutTime4"))
                            dsEmp.Tables[0].Columns.Remove("OutTime4");
                        if (dsEmp.Tables[0].Columns.Contains("OutTime5"))
                            dsEmp.Tables[0].Columns.Remove("OutTime5");
                        if (dsEmp.Tables[0].Columns.Contains("AcutalWorkedHours"))
                            dsEmp.Tables[0].Columns.Remove("AcutalWorkedHours");
                        if (dsEmp.Tables[0].Columns.Contains("Intime5"))
                            dsEmp.Tables[0].Columns.Remove("Intime5");
                        if (dsEmp.Tables[0].Columns.Contains("Department"))
                            dsEmp.Tables[0].Columns.Remove("Department");
                        if (dsEmp.Tables[0].Columns.Contains("SECONDSESSION"))
                            dsEmp.Tables[0].Columns.Remove("SECONDSESSION");
                        if (dsEmp.Tables[0].Columns.Contains("BREAKHOURS"))
                            dsEmp.Tables[0].Columns.Remove("BREAKHOURS");
                        if (dsEmp.Tables[0].Columns.Contains("LUNCHHOURS"))
                            dsEmp.Tables[0].Columns.Remove("LUNCHHOURS");
                        if (dsEmp.Tables[0].Columns.Contains("BRANCHNAME"))
                            dsEmp.Tables[0].Columns.Remove("BRANCHNAME");
                        //      TBH TLH Department   OutTime1    Intime2 OutTime2    Intime3 OutTime3    Intime4 OutTime4    Intime5 OutTime5    
                        //  TBH TLH 
                        if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("NORMOT"))
                            dsEmp.Tables[0].DefaultView.Table.Columns.Remove("NORMOT");
                        if (dsEmp.Tables[0].Columns.Contains("SPEC_NormOT1"))
                            dsEmp.Tables[0].Columns.Remove("SPEC_NormOT1");
                        if (dsEmp.Tables[0].Columns.Contains("SPEC_NormOT2"))
                            dsEmp.Tables[0].Columns.Remove("SPEC_NormOT2");
                        if (dsEmp.Tables[0].Columns.Contains("OFFOT"))
                            dsEmp.Tables[0].Columns.Remove("OFFOT");
                        if (dsEmp.Tables[0].Columns.Contains("SPEC_OffOT1"))
                            dsEmp.Tables[0].Columns.Remove("SPEC_OffOT1");
                        if (dsEmp.Tables[0].Columns.Contains("SPEC_OffOT2"))
                            dsEmp.Tables[0].Columns.Remove("SPEC_OffOT2");
                        if (dsEmp.Tables[0].Columns.Contains("HOLOT"))
                            dsEmp.Tables[0].Columns.Remove("HOLOT");
                        if (dsEmp.Tables[0].Columns.Contains("SPEC_HOLOT1"))
                            dsEmp.Tables[0].Columns.Remove("SPEC_HOLOT1");
                        if (dsEmp.Tables[0].Columns.Contains("SPEC_HOLOT2"))
                            dsEmp.Tables[0].Columns.Remove("SPEC_HOLOT2");
                        if (dsEmp.Tables[0].Columns.Contains("SUPPOSEDWORKHOURS"))
                            dsEmp.Tables[0].Columns.Remove("SUPPOSEDWORKHOURS");
                        if (dsEmp.Tables[0].Columns.Contains("TOTALMINSWORKED"))
                            dsEmp.Tables[0].Columns.Remove("TOTALMINSWORKED");
                        if (dsEmp.Tables[0].Columns.Contains("StatusCode"))
                            dsEmp.Tables[0].Columns.Remove("StatusCode");
                        if (dsEmp.Tables[0].Columns.Contains("NORMOTMIN"))
                            dsEmp.Tables[0].Columns.Remove("NORMOTMIN");
                        if (dsEmp.Tables[0].Columns.Contains("OFFOTMIN"))
                            dsEmp.Tables[0].Columns.Remove("OFFOTMIN");
                        if (dsEmp.Tables[0].Columns.Contains("HOLOTMIN"))
                            dsEmp.Tables[0].Columns.Remove("HOLOTMIN");
                        if (dsEmp.Tables[0].Columns.Contains("LATEINMIN"))
                            dsEmp.Tables[0].Columns.Remove("LATEINMIN");
                        if (dsEmp.Tables[0].Columns.Contains("EARLYOUTMIN"))
                            dsEmp.Tables[0].Columns.Remove("EARLYOUTMIN");
                        if (dsEmp.Tables[0].Columns.Contains("TOTDEFWORKHRS"))
                            dsEmp.Tables[0].Columns.Remove("TOTDEFWORKHRS");
                        if (dsEmp.Tables[0].Columns.Contains("TOTSUPWORKHRS"))
                            dsEmp.Tables[0].Columns.Remove("TOTSUPWORKHRS");
                        if (dsEmp.Tables[0].Columns.Contains("OVERTIMESTATUS"))
                            dsEmp.Tables[0].Columns.Remove("OVERTIMESTATUS");
                        if (dsEmp.Tables[0].Columns.Contains("NIGHTORMORN"))
                            dsEmp.Tables[0].Columns.Remove("NIGHTORMORN");
                        if (dsEmp.Tables[0].Columns.Contains("LUNCHINTIME"))
                            dsEmp.Tables[0].Columns.Remove("LUNCHINTIME");
                        if (dsEmp.Tables[0].Columns.Contains("LUNCHOUTTIME"))
                            dsEmp.Tables[0].Columns.Remove("LUNCHOUTTIME");
                        if (dsEmp.Tables[0].Columns.Contains("INNOTATION"))
                            dsEmp.Tables[0].Columns.Remove("INNOTATION");
                        if (dsEmp.Tables[0].Columns.Contains("OUTNOTATION"))
                            dsEmp.Tables[0].Columns.Remove("OUTNOTATION");
                        if (dsEmp.Tables[0].Columns.Contains("SHIFT_STARTTIME"))
                            dsEmp.Tables[0].Columns.Remove("SHIFT_STARTTIME");
                        if (dsEmp.Tables[0].Columns.Contains("SHIFT_ENDTIME"))
                            dsEmp.Tables[0].Columns.Remove("SHIFT_ENDTIME");
                        if (dsEmp.Tables[0].Columns.Contains("BREAKHOURSMINS"))
                            dsEmp.Tables[0].Columns.Remove("BREAKHOURSMINS");
                        if (dsEmp.Tables[0].Columns.Contains("LUNCHHOURSMINS"))
                            dsEmp.Tables[0].Columns.Remove("LUNCHHOURSMINS");
                        if (dsEmp.Tables[0].Columns.Contains("WEEKDAY_WORKHRS"))
                            dsEmp.Tables[0].Columns.Remove("WEEKDAY_WORKHRS");
                        if (dsEmp.Tables[0].Columns.Contains("WEEKNUMBER"))
                            dsEmp.Tables[0].Columns.Remove("WEEKNUMBER");
                        if (dsEmp.Tables[0].Columns.Contains("FNATTENDANCECODE"))
                            dsEmp.Tables[0].Columns.Remove("FNATTENDANCECODE");
                        if (dsEmp.Tables[0].Columns.Contains("ANATTENDANCECODE"))
                            dsEmp.Tables[0].Columns.Remove("ANATTENDANCECODE");
                        if (dsEmp.Tables[0].Columns.Contains("DESIGNATION"))
                            dsEmp.Tables[0].Columns.Remove("DESIGNATION");
                        //if (dsEmp.Tables[0].Columns.Contains("BRANCHNAME"))
                        //    dsEmp.Tables[0].Columns.Remove("BRANCHNAME");
                        if (dsEmp.Tables[0].Columns.Contains("processDate"))
                            dsEmp.Tables[0].Columns.Remove("processDate");
                        if (dsEmp.Tables[0].Columns.Contains("processDay"))
                            dsEmp.Tables[0].Columns.Remove("processDay");
                        if (dsEmp.Tables[0].Columns.Contains("processMonth"))
                            dsEmp.Tables[0].Columns.Remove("processMonth");
                        if (dsEmp.Tables[0].Columns.Contains("OVERTIME"))
                            dsEmp.Tables[0].Columns.Remove("OVERTIME");
                        if (dsEmp.Tables[0].Columns.Contains("MonthNo"))
                            dsEmp.Tables[0].Columns.Remove("MonthNo");
                        if (dsEmp.Tables[0].Columns.Contains("BRANCHNAME"))
                            dsEmp.Tables[0].Columns["BRANCHNAME"].ColumnName = "Level";
                        if (dsEmp.Tables[0].DefaultView.Table.Columns.Contains("FirstSession"))
                            dsEmp.Tables[0].DefaultView.Table.Columns["FirstSession"].ColumnName = "Attendance";
                    }
                }
                #endregion
                break;
            default:
                break;
        }
        return dsEmp;
    }
    private static int GetColspanValue(string rptName)
    {
        int colspan = 9;
        switch (rptName)
        {
            case "Less Hours Worked - Summary":
                colspan = 10;
                break;
            case "All Swipes":
                colspan = 17;
                break;
            case "All Swipes First and Last Punch Details":
            case "All Swipes Report With Matched Punches":
            case "Other All Swipes Details":
                colspan = 14;
                break;
            case "All Swipes Report Without Matched Punches":
                colspan = 16;
                break;
            case "Labour - Monthly Attendance":
                colspan = 17;
                break;
            case "General Monthly":
                colspan = 14;
                break;
            case "Raw Data":
                colspan = 8;
                break;
            case "Worked Days":
            case "Punctuality":
                colspan = 7;
                break;
            case "Less Hours Worked - Detail":
            case "Early Exit":
            case "Early Entry":
            case "Daily Wages Penalty Detail":
            case "Daily Wages Penalty Summary":
                colspan = 11;
                break;
            case "OverTimeSanction":
                colspan = 30;
                break;
        }
        return colspan;
    }
    private void GenerateExcelHeaders(DataColumn dc)
    {
        string fieldName = "";
        switch (dc.ColumnName.ToUpper())
        {
            case "SHIFT_ALLOW":
                fieldName = Convert.ToString(lblshift.Text);
                break;
            case "FOOD_ALLOW":
                fieldName = Convert.ToString(lblfood.Text);
                break;
            case "MEAL_ALLOW":
                fieldName = Convert.ToString(lblmeal.Text);
                break;
            case "SH_ALLOW":
                fieldName = Convert.ToString(lblSH.Text);
                break;
            case "MONTH OF DEDUCTION":
                fieldName = "Month";
                break;
            case "TOTALLATEIN":
                fieldName = "Late Hours";
                break;
            case "TOTALEARLYOUT":
                fieldName = "Early Exit Hours";
                break;
            case "LATEINDEDUCTION":
            case "EARLYOUTDEDUCTION":
                fieldName = "Deduction Wages";
                break;
            case "SLNO":
                fieldName = " Sl.No.";
                break;
            case "EMPLOYEE NAME":
            case "EMPNAME":
            case "EMPLOYEENAME":
            case "EMPLOYEE_FIRSTNAME":
            case "EMPLOYEE_NAME":
                fieldName = "Employee Name";
                break;
            case "EMPLOYEE ID":
            case "EMPLOYEE_ID":
            case "EMPLOYEE_CODE_ID":
            case "CARDNO":
            case "ONDUTY_EMPLOYEE_ID":
            case "MANUALTIMEENTRY_EMPLOYEE_ID":
                fieldName = " Employee ID";
                break;
            case "DESIGNATION":
            case "TITLENAME":
                fieldName = " Designation";
                break;
            case "LEVEL NAME":
            case "BRANCHNAME":
            case "LEVELNAME":
            case "DEPTNAME":
                fieldName = " Level Name";
                break;
            case "SHIFTOUT":
                fieldName = "Shift Out Time";
                break;
            case "EARLYOUT":
            case "EARLYBY":
                fieldName = "Early by Hrs";
                break;
            case "GENDER":
                fieldName = "Gender";
                break;
            case "TOTAL DAYS WORKED":
                fieldName = "Total Days Worked (Present)";
                break;
            case "SHIFT_SHORTNAME":
            case "SHFTSHRTNAME":
            case "SHIFT_SHARTNAME":
            case "SSN":
                fieldName = "SSN";
                break;
            case "DATE":
                fieldName = "Date";
                break;
            case "IN READER ID":
                fieldName = "In ReaderID";
                break;
            case "IN DATE":
            case "INDATE":
            case "MANUALTIMEENTRY_INDATE":
                fieldName = "In Date";
                break;
            case "IN TIME":
            case "INTIME":
            case "MANUALTIMEENTRY_INTIME":
                fieldName = "In Time";
                break;
            case "IN PUNCH TYPE":
            case "CLK_INPUNCHTYPE":
                fieldName = "In Punch Type";
                break;
            case "OUT PUNCH TYPE":
            case "CLK_OUTPUNCHTYPE":
                fieldName = "Out Punch Type";
                break;
            case "OUT READER ID":
                fieldName = "Out ReaderID";
                break;
            case "OUT DATE":
            case "MANUALTIMEENTRY_OUTDATE":
            case "OUTDATE":
            case "ONDUTY_TODATE":
                fieldName = "Out Date";
                break;
            case "OUT TIME":
            case "OUTTIME":
            case "ONDUTY_TOTIME":
            case "MANUALTIMEENTRY_OUTTIME":
                fieldName = "Out Time";
                break;
            case "REMARKS":
                fieldName = "Remarks";
                break;
            case "HRSWORK":
                fieldName = "Hours Worked";
                break;
            case "EXCESS":
                fieldName = "Excess / Discrepancy Hours(hh:mm)";
                break;
            case "TERMINAL_NAME":
                fieldName = "Terminal ID";
                break;
            case "SHIFT_TOTALWORKINGHOURS":
                fieldName = "Defined Work Hours";
                break;
            case "TOTALLESSHRS":
                fieldName = "Less Hours";
                break;
            case "PUNCHDATE":
                fieldName = "Punch Date";
                break;
            case "PUNCHTIME":
                fieldName = "Punch Time";
                break;
            case "PUNCHTYPE":
                fieldName = "Punch Type (Value)";
                break;
            case "PROCESS":
            case "ONDUTY_STATUS":
            case "MANUALTIMEENTRY_STATUS":
            case "OVERTIMESTATUS":
                fieldName = "Status";
                break;
            case "BREAKHOURS":
                fieldName = "TBH";
                break;
            case "LUNCHHOURS":
                fieldName = "TLH";
                break;
            case "OVERTIMEHOURS":
                fieldName = "OT";
                break;
            case "LATEIN":
                fieldName = "Late In";
                break;
            case "FIRSTSESSION":
            case "FIRST SESSION":
                fieldName = "First Session";
                break;
            case "SECONDSESSION":
            case "SECOND SESSION":
                fieldName = "Second Session";
                break;
            case "SHIFTIN":
                fieldName = "Shift Time";
                break;
            case "OUTPUNCH":
            case "INPUNCH":
                fieldName = "Punch Type";
                break;
            case "PRESENT":
                fieldName = "Present";
                break;
            case "ABSENT":
                fieldName = "Absent";
                break;
            case "TOTAL LATE DAYS":
                fieldName = "Total Late Days";
                break;
            case "TOTAL LATE HOURS":
                fieldName = "Total Late Hours";
                break;
            case "TOTAL EARLY EXIT DAYS":
                fieldName = "Total Early Exit Days";
                break;
            case "TOTAL EARLY EXIT HOURS":
                fieldName = "Total Early Exit Hours";
                break;
            case "TOTAL OT DAYS":
                fieldName = "Total OT Days";
                break;
            case "TOTAL OT HOURS":
                fieldName = "Total OT Hours";
                break;
            case "OFFOT":
                fieldName = "WO-OT Hours";
                break;
            case "HOLOT":
                fieldName = "H-OT Hours";
                break;
            case "NORMOT":
                fieldName = "WD-OT Hours";
                break;
            case "NORM OT HRS":
                fieldName = "Norm OT Hrs";
                break;
            case "WEEKOFF OT HRS":
                fieldName = "WeekOff OT Hrs";
                break;
            case "HOLIDAY OT HRS":
                fieldName = "Holiday OT Hrs";
                break;
            case "OTHERS":
                fieldName = "Others";
                break;
            case "TOTAL_HOURSWORKINMINS":
                fieldName = "Total Hours Worked";
                break;
            case "SHIFT_STARTTIME":
                fieldName = "Shift In Time";
                break;
            case "ONDUTY_FROMDATE":
                fieldName = "From Date";
                break;
            case "ONDUTY_FROMTIME":
                fieldName = "From Time";
                break;
            case "ONDUTY_REASON":
            case "MANUALTIMEENTRY_REASON":
                fieldName = "Reason";
                break;
            case "CREATED_BY":
                fieldName = "Created By";
                break;
            case "CREATED_DATETIME":
                fieldName = "Created on";
                break;
            case "TOTALMINSALLOCATED":
                fieldName = "Total Hours Allocated";
                break;
            case "TOTALLESSHRSCOUNTDAYS":
                fieldName = "Less Hours - Days";
                break;
            case "WorkingDays":
                fieldName = "Working Days";
                break;
            case "ACTHRSOT":
                fieldName = Convert.ToString(ACTHRSOT.Text);
                break;
            case "HRSOT":
                fieldName = "Modified Week Day OT Hrs";
                break;
            case "ACTOFFOT":
                fieldName = Convert.ToString(ACTOFFOT.Text);
                break;
            case "ACTHOLOT":
                fieldName = Convert.ToString(ACTHOLOT.Text);
                break;
            case "SPEC_NORMOT1":
                fieldName = Convert.ToString(SPEC_NormOT1.Text);
                break;
            case "MODIFIED_SPEC_NORMOTHRS1":
                fieldName = Convert.ToString(Modified_Spec_NormOTHrs1.Text);
                break;
            case "SPEC_NORMOT2":
                fieldName = Convert.ToString(SPEC_NormOT2.Text);
                break;
            case "MODIFIED_SPEC_NORMOTHRS2":
                fieldName = Convert.ToString(Modified_Spec_NormOTHrs2.Text);
                break;
            case "SPEC_OFFOT1":
                fieldName = Convert.ToString(SPEC_OffOT1.Text);
                break;
            case "MODIFIED_SPEC_OFFOTHRS1":
                fieldName = Convert.ToString(Modified_Spec_OffOTHrs1.Text);
                break;
            case "SPEC_OFFOT2":
                fieldName = Convert.ToString(SPEC_OffOT2.Text);
                break;
            case "MODIFIED_SPEC_OFFOTHRS2":
                fieldName = Convert.ToString(Modified_Spec_OffOTHrs2.Text);
                break;
            case "SPEC_HOLOT1":
                fieldName = Convert.ToString(SPEC_HOLOT1.Text);
                break;
            case "MODIFIED_SPEC_HOLOTHRS1":
                fieldName = Convert.ToString(Modified_Spec_HolOTHrs1.Text);
                break;
            case "SPEC_HOLOT2":
                fieldName = Convert.ToString(SPEC_HOLOT2.Text);
                break;
            case "MODIFIED_SPEC_HOLOTHRS2":
                fieldName = Convert.ToString(Modified_Spec_HolOTHrs2.Text);
                break;
            case "MODIFIED_SHIFT_ALLOW":
                fieldName = Convert.ToString(lblshiftModified.Text);
                break;
            case "MODIFIED_FOOD_ALLOW":
                fieldName = Convert.ToString(lblfoodModified.Text);
                break;
            case "MODIFIED_MEAL_ALLOW":
                fieldName = Convert.ToString(lblmealModified.Text);
                break;
            case "MODIFIED_SH_ALLOW":
                fieldName = Convert.ToString(lblSHmodified.Text);
                break;
            case "AF ATTENDANCE":
                fieldName = "AN Attendance";
                break;
            case "HRSEXTRA":
                fieldName = "Extra Hours";
                break;
            case "MODIFIED_BY":
                fieldName = "Modify By";
                break;
            case "MODIFIED_DATETIME":
                fieldName = "Modify On";
                break;
            case "NOTALLOCATEDDATES":
                fieldName = "Not Allocated Dates";
                break;
        }
        if (fieldName != "")
            Response.Write("<td class='xl68'> " + fieldName + "</td>");
        else
            Response.Write("<td class='xl68'> " + dc.ColumnName + "</td>");
    }
}