Example #1
0
        public DataSet GetStatChgData(DataSet dsCurr)
        {
            string     statcd_1, statcd_2;
            Boolean    _found;
            DataSet    dsPrev           = new DataSet(); dsPrev.Clear();
            DataSet    dsResult         = new DataSet(); dsResult.Clear();
            DataTable  dsTable; dsTable = dsResult.Tables.Add("dsStatChg");
            DataRow    row;
            DataColumn col;

            col = new DataColumn("EE#"); dsTable.Columns.Add(col);
            col = new DataColumn("Name"); dsTable.Columns.Add(col);
            col = new DataColumn("Status Code"); dsTable.Columns.Add(col);
            col = new DataColumn("Status Date"); dsTable.Columns.Add(col);

            dsPrev = HRAOperDAL.GetPrevEligData("EligStat");

            foreach (DataRow row1 in dsCurr.Tables[0].Rows)
            {
                _found = false;
                if (dsPrev.Tables.Count > 0)
                {
                    foreach (DataRow row2 in dsPrev.Tables[0].Rows)
                    {
                        if ((row1["empno"].ToString()).Equals(row2["empno"].ToString()))
                        {
                            _found   = true;
                            statcd_1 = row1["statcd"].ToString().Trim().ToUpper();
                            statcd_2 = row2["statcd"].ToString().Trim().ToUpper();

                            if (!statcd_1.Equals(statcd_2))
                            {
                                row                = dsTable.NewRow();
                                row["EE#"]         = row1["empno"];
                                row["Name"]        = row1["name"];
                                row["Status Code"] = row1["statcd"];
                                row["Status Date"] = row1["statdt"];
                                dsTable.Rows.Add(row);
                            }
                            break;
                        }
                    }
                }
                if (!_found)
                {
                    row                = dsTable.NewRow();
                    row["EE#"]         = row1["empno"];
                    row["Name"]        = row1["name"];
                    row["Status Code"] = row1["statcd"];
                    row["Status Date"] = row1["statdt"];
                    dsTable.Rows.Add(row);
                }
            }

            return(dsResult);
        }
Example #2
0
        public DataSet GetAuditFile()
        {
            DataSet    empds     = new DataSet();
            DataSet    dshrainfo = new DataSet();
            HRAOperDAL dobj      = new HRAOperDAL();

            empds.Clear();
            dshrainfo.Clear();
            int counter;

            DataSet dsAudit = new DataSet();

            dsAudit.Clear();
            DataTable dsTable;

            dsTable = dsAudit.Tables.Add("dsTable");
            DataRow    row2;
            DataColumn col;

            col = new DataColumn("SSN"); dsTable.Columns.Add(col);
            col = new DataColumn("Emp#"); dsTable.Columns.Add(col);
            col = new DataColumn("Name"); dsTable.Columns.Add(col);
            col = new DataColumn("SubCtr"); dsTable.Columns.Add(col);
            col = new DataColumn("CodeID"); dsTable.Columns.Add(col);
            col = new DataColumn("Code Desc"); dsTable.Columns.Add(col);
            col = new DataColumn("Value"); dsTable.Columns.Add(col);


            empds = dobj.GetEmployeeInfo();

            foreach (DataRow row in empds.Tables[0].Rows)
            {
                counter   = 0;
                dshrainfo = getHRaPartInfo(row);

                foreach (DataRow row1 in dshrainfo.Tables[0].Rows)
                {
                    if (row1["codedsc"].ToString() != "NAME" && row1["codedsc"].ToString() != "EMPNO")
                    {
                        counter++;
                        row2              = dsTable.NewRow();
                        row2["SSN"]       = row["ssn"].ToString().Trim();
                        row2["Emp#"]      = row["empno"].ToString();
                        row2["Name"]      = row["lname"].ToString().Trim() + ", " + row["fname"].ToString().Trim();
                        row2["SubCtr"]    = counter;
                        row2["CodeID"]    = row1["codeid"].ToString();
                        row2["Code Desc"] = row1["codedsc"].ToString();
                        row2["Value"]     = row1["value"].ToString();
                        dsTable.Rows.Add(row2);
                    }
                }
            }

            return(dsAudit);
        }
Example #3
0
        public string GetEligFile()
        {
            DataSet       empds     = new DataSet();
            DataSet       dshrainfo = new DataSet();
            StringBuilder sb        = new StringBuilder();
            HRAOperDAL    dobj      = new HRAOperDAL();

            empds.Clear();
            dshrainfo.Clear();
            int    counter = 0;
            string start, end;
            string hraPlanNum = HRAOperDAL.GetHRAPlanNum();


            /**************  Header of Elig File   ************************************/

            sb.Append(GetEligFileHeader(hraPlanNum) + Environment.NewLine);


            /**************  Build Records   ******************************************/

            empds = dobj.GetEmployeeInfo();

            foreach (DataRow row in empds.Tables[0].Rows)
            {
                //start and end of each line
                start = "813" + "  " + hraPlanNum.PadLeft(6, '0') + " " + row["ssn"].ToString().Trim().PadLeft(9, '0');
                end   = "";
                end   = end.PadRight(24, ' ') + Environment.NewLine;

                //participant info
                dshrainfo = getHRaPartInfo(row);

                foreach (DataRow row1 in dshrainfo.Tables[0].Rows)
                {
                    if (row1["codeid"].ToString() != "N/A" && row1["codeid"].ToString() != "RET")
                    {
                        sb.Append(start + row1["codeid"].ToString().PadLeft(3, '0') + HRA.GetFixedLengthString(row1["value"].ToString(), 32) + end);
                        counter++;
                    }
                }
            }

            /*****************  (Footer) Trailer Record   *****************************/

            sb.Append(GetEligFileFooter(counter, hraPlanNum));


            return(sb.ToString());
        }
Example #4
0
        public static Boolean hasStatChg()
        {
            Boolean hasStatChg;
            DataSet ds = new DataSet(); ds.Clear();

            ds = HRAOperDAL.GetChgEligData("ChgEligStat");

            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                hasStatChg = true;
            }
            else
            {
                hasStatChg = false;
            }

            return(hasStatChg);
        }
Example #5
0
        DataSet getHRaPartInfo(DataRow row)
        {
            Boolean    ownerfound = false;
            string     ownereffdt = "";
            string     origowner  = "";
            string     origDOB    = "";
            DataSet    dscodes    = new DataSet(); dscodes.Clear();
            DataSet    depds      = new DataSet(); depds.Clear();
            HRAOperDAL dobj       = new HRAOperDAL();
            DataSet    dsinfo     = new DataSet(); dsinfo.Clear();
            DataTable  dsTable;

            dsTable = dsinfo.Tables.Add("newTable1");
            DataRow    row1;
            DataColumn col;

            col = new DataColumn("codeid"); dsTable.Columns.Add(col);
            col = new DataColumn("codedsc"); dsTable.Columns.Add(col);
            col = new DataColumn("value"); dsTable.Columns.Add(col);

            if ((row["dob"] != DBNull.Value) && (row["dob"].ToString().Trim() != string.Empty))
            {
                origDOB = Convert.ToDateTime(row["dob"]).ToString("MM/dd/yyyy");
            }
            else
            {
                origDOB = "";
            }

            // Check if ownership has changed
            if (OwnerChanged(row["statuscd"].ToString().Trim()))
            {
                // check ownership information
                depds      = dobj.GetDependantInfo(row["empno"].ToString());
                ownerfound = OwnerFound(depds);
                if (ownerfound)
                {
                    origowner  = row["lname"].ToString().Trim() + ", " + row["fname"].ToString().Trim();
                    row        = OverlayOwnerInfo(row, depds); // Overlay ownership information
                    ownereffdt = GetOwnerEffDt(depds);         // Get owner effective date
                }
            }

            //Code Ids
            NameValueCollection _codeids = HRAOperDAL.GetCodeIds();

            //Previous Owner
            if (ownerfound)
            {
                row1            = dsTable.NewRow();
                row1["codeid"]  = _codeids.Get("PREVOWNR");
                row1["codedsc"] = "PREVOWNR";
                row1["value"]   = origowner;
                dsTable.Rows.Add(row1);
            }

            // Name
            row1            = dsTable.NewRow();
            row1["codeid"]  = _codeids.Get("NAME");
            row1["codedsc"] = "NAME";
            row1["value"]   = (row["lname"].ToString().Trim() + ", " + row["fname"].ToString().Trim());
            dsTable.Rows.Add(row1);

            //Employee Number
            row1            = dsTable.NewRow();
            row1["codeid"]  = _codeids.Get("EMPNO");
            row1["codedsc"] = "EMPNO";
            row1["value"]   = row["empno"].ToString();
            dsTable.Rows.Add(row1);

            // Sex Code
            string sexcd = row["sex"].ToString().Trim().ToUpper();

            if (sexcd.Equals("M"))
            {
                sexcd = "1";
            }
            else
            {
                sexcd = "2";
            }

            row1            = dsTable.NewRow();
            row1["codeid"]  = _codeids.Get("SEXCD");
            row1["codedsc"] = "SEXCD";
            row1["value"]   = sexcd;
            dsTable.Rows.Add(row1);

            // Date of Birth
            string dob = "";

            if ((row["dob"] != DBNull.Value) && (row["dob"].ToString().Trim() != string.Empty))
            {
                dob = Convert.ToDateTime(row["dob"]).ToString("yyyyMMdd");
            }
            else
            {
                dob = dob.PadRight(8, ' ');
            }

            row1            = dsTable.NewRow();
            row1["codeid"]  = _codeids.Get("DOB");
            row1["codedsc"] = "DOB";
            row1["value"]   = dob;
            dsTable.Rows.Add(row1);

            // Date of Death
            if (OwnerChanged(row["statuscd"].ToString().Trim()))
            {
                string statusdt = "";
                if ((row["statusdt"] != DBNull.Value) && (row["statusdt"].ToString().Trim() != string.Empty))
                {
                    statusdt = Convert.ToDateTime(row["statusdt"]).ToString("yyyyMMdd");
                }
                else
                {
                    statusdt = statusdt.PadRight(8, ' ');
                }

                row1            = dsTable.NewRow();
                row1["codeid"]  = _codeids.Get("DIED");
                row1["codedsc"] = "DIED";
                row1["value"]   = statusdt;
                dsTable.Rows.Add(row1);
            }

            // Permanent Full Time Date
            string permftdt = "";

            if ((row["permftdt"] != DBNull.Value) && (row["permftdt"].ToString().Trim() != string.Empty))
            {
                permftdt = Convert.ToDateTime(row["permftdt"]).ToString("yyyyMMdd");
            }
            else
            {
                permftdt = permftdt.PadRight(8, ' ');
            }

            row1            = dsTable.NewRow();
            row1["codeid"]  = _codeids.Get("PERMDT");
            row1["codedsc"] = "PERMDT";
            row1["value"]   = permftdt;
            dsTable.Rows.Add(row1);


            //062 record for status 'DTH'(eligible for claims)

            if (OwnerChanged(row["statuscd"].ToString().Trim()) && ((row["statusdt"] != DBNull.Value) && (row["statusdt"].ToString().Trim() != string.Empty)))
            {
                if (origDOB != String.Empty)
                {
                    row1            = dsTable.NewRow();
                    row1["codeid"]  = _codeids.Get("RET EFFDT / DIED");
                    row1["codedsc"] = "DIED";
                    row1["value"]   = GetDeathModifyDt(row, Convert.ToDateTime(origDOB));
                    dsTable.Rows.Add(row1);
                }
            }

            // Retirement Date (eligible for claims)
            if ((row["retiredt"] != DBNull.Value) && (row["retiredt"].ToString().Trim() != string.Empty))
            {
                DateTime retiredt = Convert.ToDateTime(row["retiredt"]);
                row1            = dsTable.NewRow();
                row1["codeid"]  = _codeids.Get("ORIGRET");
                row1["codedsc"] = "ORIGRET";
                row1["value"]   = retiredt.ToString("yyyyMMdd");
                dsTable.Rows.Add(row1);

                if (string.Compare(row["statuscd"].ToString().Trim(), "TRM", true) == 0)
                {
                    if (origDOB != String.Empty)
                    {
                        row1            = dsTable.NewRow();
                        row1["codeid"]  = _codeids.Get("RET EFFDT / DIED");
                        row1["codedsc"] = "RET EFFDT";
                        row1["value"]   = GetTrmModifyDt(Convert.ToDateTime(origDOB), retiredt);
                        dsTable.Rows.Add(row1);
                    }
                }
            }

            // Account Owner Effective Date
            if (ownerfound)
            {
                row1            = dsTable.NewRow();
                row1["codeid"]  = _codeids.Get("OWNER DT");
                row1["codedsc"] = "OWNER DT";
                row1["value"]   = ownereffdt;
                dsTable.Rows.Add(row1);
            }

            // Term Reason Code
            string _status  = row["statuscd"].ToString().Trim().ToUpper();
            string statuscd = String.Empty;

            if ((row["retiredt"] != DBNull.Value) && (row["retiredt"].ToString().Trim() != string.Empty) && (_status.Equals("TRM")))
            {
                statuscd = "4";
            }
            if ((row["statusdt"] != DBNull.Value) && (row["statusdt"].ToString().Trim() != string.Empty) && (_status.Equals("DTH")))
            {
                statuscd = "6";
            }

            if (!(statuscd.Equals(String.Empty)))
            {
                row1            = dsTable.NewRow();
                row1["codeid"]  = _codeids.Get("TERMREAS");
                row1["codedsc"] = "TERMREAS";
                row1["value"]   = statuscd;
                dsTable.Rows.Add(row1);
            }

            // Address line 1
            row1            = dsTable.NewRow();
            row1["codeid"]  = _codeids.Get("ADDR1");
            row1["codedsc"] = "ADDR1";
            row1["value"]   = row["addr1"].ToString().Trim();
            dsTable.Rows.Add(row1);

            // Address line 2
            string addr2 = row["addr2"].ToString().Trim();

            if (addr2 != "")
            {
                row1            = dsTable.NewRow();
                row1["codeid"]  = _codeids.Get("ADDR2");
                row1["codedsc"] = "ADDR2";
                row1["value"]   = addr2;
                dsTable.Rows.Add(row1);
            }

            // City
            row1            = dsTable.NewRow();
            row1["codeid"]  = _codeids.Get("CITY");
            row1["codedsc"] = "CITY";
            row1["value"]   = row["city"].ToString().Trim();
            dsTable.Rows.Add(row1);

            // State
            row1            = dsTable.NewRow();
            row1["codeid"]  = _codeids.Get("STATE");
            row1["codedsc"] = "STATE";
            row1["value"]   = row["state"].ToString().Trim();
            dsTable.Rows.Add(row1);

            // Zip Code
            row1            = dsTable.NewRow();
            row1["codeid"]  = _codeids.Get("ZIPCD");
            row1["codedsc"] = "ZIPCD";
            row1["value"]   = row["zip"].ToString().Trim();
            dsTable.Rows.Add(row1);

            // QDRO Flag
            row1            = dsTable.NewRow();
            row1["codeid"]  = _codeids.Get("QDRO");
            row1["codedsc"] = "QDRO";
            row1["value"]   = "0.00";
            dsTable.Rows.Add(row1);

            return(dsinfo);
        }
Example #6
0
        public DataSet GetAddrChgData(DataSet dsCurr)
        {
            string     addr1_1, addr1_2, addr2_1, addr2_2, city_1, city_2, state_1, state_2, zip_1, zip_2;
            Boolean    _found;
            DataSet    dsPrev           = new DataSet(); dsPrev.Clear();
            DataSet    dsResult         = new DataSet(); dsResult.Clear();
            DataTable  dsTable; dsTable = dsResult.Tables.Add("dsAddrChg");
            DataRow    row;
            DataColumn col;

            col = new DataColumn("EE#"); dsTable.Columns.Add(col);
            col = new DataColumn("Name"); dsTable.Columns.Add(col);
            col = new DataColumn("Prior Addr1"); dsTable.Columns.Add(col);
            col = new DataColumn("Prior Addr2"); dsTable.Columns.Add(col);
            col = new DataColumn("Prior City"); dsTable.Columns.Add(col);
            col = new DataColumn("Prior State"); dsTable.Columns.Add(col);
            col = new DataColumn("Prior Zip"); dsTable.Columns.Add(col);
            col = new DataColumn("New Addr1"); dsTable.Columns.Add(col);
            col = new DataColumn("New Addr2"); dsTable.Columns.Add(col);
            col = new DataColumn("New City"); dsTable.Columns.Add(col);
            col = new DataColumn("New State"); dsTable.Columns.Add(col);
            col = new DataColumn("New Zip"); dsTable.Columns.Add(col);

            dsPrev = HRAOperDAL.GetPrevEligData("EligAddr");

            foreach (DataRow row1 in dsCurr.Tables[0].Rows)
            {
                _found = false;
                if (dsPrev.Tables.Count > 0)
                {
                    foreach (DataRow row2 in dsPrev.Tables[0].Rows)
                    {
                        if ((row1["empno"].ToString()).Equals(row2["empno"].ToString()))
                        {
                            _found  = true;
                            addr1_1 = row1["addr1"].ToString().Trim().ToUpper();
                            addr1_2 = row2["addr1"].ToString().Trim().ToUpper();
                            addr2_1 = row1["addr2"].ToString().Trim().ToUpper();
                            addr2_2 = row2["addr2"].ToString().Trim().ToUpper();
                            city_1  = row1["city"].ToString().Trim().ToUpper();
                            city_2  = row2["city"].ToString().Trim().ToUpper();
                            state_1 = row1["state"].ToString().Trim().ToUpper();
                            state_2 = row2["state"].ToString().Trim().ToUpper();
                            zip_1   = row1["zip"].ToString().Trim().ToUpper();
                            zip_2   = row2["zip"].ToString().Trim().ToUpper();

                            if ((!addr1_1.Equals(addr1_2)) || (!addr2_1.Equals(addr2_2)) || (!city_1.Equals(city_2)) || (!state_1.Equals(state_2)) || (!zip_1.Equals(zip_2)))
                            {
                                row                = dsTable.NewRow();
                                row["EE#"]         = row1["empno"];
                                row["Name"]        = row1["name"];
                                row["Prior Addr1"] = row2["addr1"];
                                row["Prior Addr2"] = row2["addr2"];
                                row["Prior City"]  = row2["city"];
                                row["Prior State"] = row2["state"];
                                row["Prior Zip"]   = row2["zip"];
                                row["New Addr1"]   = row1["addr1"];
                                row["New Addr2"]   = row1["addr2"];
                                row["New City"]    = row1["city"];
                                row["New State"]   = row1["state"];
                                row["New Zip"]     = row1["zip"];
                                dsTable.Rows.Add(row);
                            }
                            break;
                        }
                    }
                }
                if (!_found)
                {
                    row                = dsTable.NewRow();
                    row["EE#"]         = row1["empno"];
                    row["Name"]        = row1["name"];
                    row["Prior Addr1"] = String.Empty;
                    row["Prior Addr2"] = String.Empty;
                    row["Prior City"]  = String.Empty;
                    row["Prior State"] = String.Empty;
                    row["Prior Zip"]   = String.Empty;
                    row["New Addr1"]   = row1["addr1"];
                    row["New Addr2"]   = row1["addr2"];
                    row["New City"]    = row1["city"];
                    row["New State"]   = row1["state"];
                    row["New Zip"]     = row1["zip"];
                    dsTable.Rows.Add(row);
                }
            }

            if ((dsPrev.Tables.Count > 0) && (dsPrev.Tables[0].Rows.Count > dsCurr.Tables[0].Rows.Count))
            {
                foreach (DataRow row2 in dsPrev.Tables[0].Rows)
                {
                    _found = false;
                    foreach (DataRow row1 in dsCurr.Tables[0].Rows)
                    {
                        if ((row1["empno"].ToString()).Equals(row2["empno"].ToString()))
                        {
                            _found = true;
                            break;
                        }
                    }
                    if (!_found)
                    {
                        row                = dsTable.NewRow();
                        row["EE#"]         = row2["empno"];
                        row["Name"]        = row2["name"];
                        row["Prior Addr1"] = row2["addr1"];
                        row["Prior Addr2"] = row2["addr2"];
                        row["Prior City"]  = row2["city"];
                        row["Prior State"] = row2["state"];
                        row["Prior Zip"]   = row2["zip"];
                        row["New Addr1"]   = String.Empty;
                        row["New Addr2"]   = String.Empty;
                        row["New City"]    = String.Empty;
                        row["New State"]   = String.Empty;
                        row["New Zip"]     = String.Empty;
                        dsTable.Rows.Add(row);
                    }
                }
            }

            return(dsResult);
        }
Example #7
0
        public static string GetReportContent(string _source, string _filename, string yrmo)
        {
            HRASofoDAL     sobj   = new HRASofoDAL();
            HRAEligFile    eobj   = new HRAEligFile();
            HRAAdminBill   hobj   = new HRAAdminBill();
            HRAExcelReport xlObj  = new HRAExcelReport();
            DataSet        ds     = new DataSet(); ds.Clear();
            DataSet        dsTemp = new DataSet(); dsTemp.Clear();

            string[]   sheetnames, titles, YRMOs;
            string[][] colsFormat, colsName;
            int[][]    colsWidth;
            string     _content = "";

            switch (_source)
            {
            case "Recon":
                sheetnames = new string[] { "Summary", "Detail" };
                titles     = new string[] { "HRA Reconciliation Summary Report for YRMO : " + yrmo + " Date Report Run : " + DateTime.Now.ToString(), "HRA Reconciliation Detail Report for YRMO : " + yrmo + " Date Report Run : " + DateTime.Now.ToString() };
                colsName   = new string[][] { new string[] { "SSN", "Last Name", "First Name", "Putnam Amount", "Wageworks Amount", "Adjustment Amount", "Current Diff Amount", "Total Diff Amount", "Putnam Record", "Wageworks Record" }, new string[] { "SSN", "Last Name", "First Name", "Transaction", "Transaction Date", "Putnam Amount", "Wageworks Amount", "Adjustment Amount" } };
                colsFormat = new string[][] { new string[] { "number", "string", "string", "currency", "currency", "currency", "currency", "currency", "number", "number" }, new string[] { "number", "string", "string", "string", "string", "currency", "currency", "currency" } };
                colsWidth  = new int[][] { new int[] { 55, 100, 100, 65, 65, 65, 65, 65, 45, 60 }, new int[] { 55, 100, 100, 105, 65, 65, 65, 65 } };

                dsTemp = HRA_ReconDAL.GetReconData(yrmo); dsTemp.Tables[0].TableName = "SummaryTable";
                ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "SummaryTableF"; dsTemp.Clear();
                dsTemp = HRA_ReconDAL.GetDetailReconData(yrmo); dsTemp.Tables[0].TableName = "DetailTable";
                ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "DetailTableF"; dsTemp.Clear();

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "CF":
                sheetnames = new string[] { "Carry_Forwards_" + yrmo };
                titles     = new string[] { "HRA Detail Carry Forward Report for YRMO : " + yrmo + " Date Report Run : " + DateTime.Now.ToString() };
                colsName   = new string[][] { new string[] { "SSN", "Last Name", "First Name", "Transaction", "Transaction Date", "Wageworks Amount" } };
                colsFormat = new string[][] { new string[] { "number", "string", "string", "string", "string", "currency" } };
                colsWidth  = new int[][] { new int[] { 55, 100, 100, 105, 65, 65 } };

                ds = HRA_ReconDAL.GetCFData(yrmo);

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "CFnotCleared":
                sheetnames = new string[] { "Prev_Carry_Forwards_" + yrmo };
                titles     = new string[] { "HRA Detail Report of Carry Forwards from prior month that did not clear in YRMO : " + yrmo + " Date Report Run : " + DateTime.Now.ToString() };
                colsName   = new string[][] { new string[] { "SSN", "Last Name", "First Name", "Transaction", "Transaction Date", "Wageworks Amount" } };
                colsFormat = new string[][] { new string[] { "number", "string", "string", "string", "string", "currency" } };
                colsWidth  = new int[][] { new int[] { 55, 100, 100, 105, 65, 65 } };

                ds = HRA_ReconDAL.GetPrevCFRptData(yrmo);

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "Transaction":
                sheetnames = new string[] { "Summary", "Detail" };
                titles     = new string[] { "HRA Transaction Summary Report for YRMO : " + yrmo + " Date Report Run : " + DateTime.Now.ToString(), "HRA Transaction Detail Report for YRMO : " + yrmo + " Date Report Run : " + DateTime.Now.ToString() };
                colsName   = new string[][] { new string[] { "SSN", "Last Name", "First Name", "Putnam Amount", "Putnam Adj Amount", "Current Wageworks", "Total Diff Amount", "Carry Forward" }, new string[] { "SSN", "Last Name", "First Name", "Transaction", "Transaction Date", "Putnam Amount", "PutnamAdj Amount", "Current Wageworks", "Carry Forward" } };
                colsFormat = new string[][] { new string[] { "number", "string", "string", "currency", "currency", "currency", "currency", "currency" }, new string[] { "number", "string", "string", "string", "string", "currency", "currency", "currency", "currency" } };
                colsWidth  = new int[][] { new int[] { 55, 100, 100, 65, 65, 65, 65, 65 }, new int[] { 55, 100, 100, 105, 65, 65, 65, 65, 65 } };

                dsTemp = HRA_ReconDAL.GetTransData(yrmo); dsTemp.Tables[0].TableName = "SummaryTable";
                ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "SummaryTableF"; dsTemp.Clear();
                dsTemp = HRA_ReconDAL.GetDtlTransData(yrmo); dsTemp.Tables[0].TableName = "DetailTable";
                ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "DetailTableF"; dsTemp.Clear();

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "SOFO":
                sheetnames = new string[] { "Summary", "Detail" };
                titles     = new string[] { "Putnam Summary of Fund Operations Reconciliation for YRMO: " + yrmo, "HRA Putnam Balance Detail report for YRMO: " + yrmo };
                colsName   = new string[][] { new string[] { "Title", "Value" }, new string[] { "SSN", "Last Name", "First Name", "Transaction", "Transaction Date", "Putnam Amount" } };
                colsFormat = new string[][] { new string[] { "string", "string" }, new string[] { "number", "string", "string", "string", "string", "currency" } };
                colsWidth  = new int[][] { new int[] { 100, 100 }, new int[] { 55, 100, 100, 105, 65, 65 } };

                dsTemp = sobj.getSOFOReconData(yrmo); dsTemp.Tables[0].TableName = "SummaryTable";
                ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "SummaryTableF"; dsTemp.Clear();
                dsTemp = sobj.getBalanceDtl(yrmo); dsTemp.Tables[0].TableName = "DetailTable";
                ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "DetailTableF"; dsTemp.Clear();

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "Elig":
                _content = eobj.GetEligFile();
                break;

            case "EligAudit":
                sheetnames = new string[] { "HRAAUDIT" };
                titles     = new string[] { "HRA Eligibility Audit Report" };
                colsName   = new string[][] { new string[] { "SSN", "EE#", "Name", "SubCtr", "CodeID", "Code Desc", "Value" } };
                colsFormat = new string[][] { new string[] { "number", "number", "string", "number", "string", "string", "string" } };
                colsWidth  = new int[][] { new int[] { 55, 55, 130, 40, 40, 100, 140 } };

                ds = eobj.GetAuditFile();

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "EligTermAudit":
                sheetnames = new string[] { "HRAAUDITR" };
                titles     = new string[] { "Report of Pilots who have Retired/Terminated/Died to date" };
                colsName   = new string[][] { new string[] { "SSN", "EE#", "Name", "Birth Date", "Age", "Status", "Status Date", "Modify Date" } };
                colsFormat = new string[][] { new string[] { "number", "number", "string", "string", "number", "string", "string", "string" } };
                colsWidth  = new int[][] { new int[] { 55, 55, 140, 65, 40, 55, 65, 77 } };

                ds = HRAOperDAL.GetEligAuditData("HRAAUDITR", String.Empty);

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "EligAddrChg":
                sheetnames = new string[] { "HRA_Audit_ADDR" };
                titles     = new string[] { "Report of Pilots who have Address Changes from the last time Eligibilty file is Generated" };
                colsName   = new string[][] { new string[] { "EE#", "Name", "Prior Addr1", "Prior Addr2", "Prior City", "Prior State", "Prior Zip", "New Addr1", "New Addr2", "New City", "New State", "New Zip" } };
                colsFormat = new string[][] { new string[] { "number", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string" } };
                colsWidth  = new int[][] { new int[] { 55, 140, 100, 50, 65, 50, 50, 100, 50, 65, 50, 50 } };

                ds = HRAOperDAL.GetChgEligData("ChgEligAddr");

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "EligStatChg":
                sheetnames = new string[] { "HRA_Retire" };
                titles     = new string[] { "Report of Pilots who have Status Changes from the last time Eligibilty file is generated" };
                colsName   = new string[][] { new string[] { "EE#", "Name", "Status Code", "Status Date" } };
                colsFormat = new string[][] { new string[] { "number", "string", "string", "string" } };
                colsWidth  = new int[][] { new int[] { 55, 140, 40, 65 } };

                ds = HRAOperDAL.GetChgEligData("ChgEligStat");

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "EligNoBen":
                sheetnames = new string[] { "HRA_NoBen" };
                titles     = new string[] { "Report of Pilots who died with no beneficiaries" };
                colsName   = new string[][] { new string[] { "SSN", "EE#", "Name", "Death Date" } };
                colsFormat = new string[][] { new string[] { "number", "number", "string", "string" } };
                colsWidth  = new int[][] { new int[] { 55, 55, 140, 65 } };

                ds = HRAOperDAL.GetEligAuditData("NoBen", String.Empty);

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "EligBen24":
                sheetnames = new string[] { "HRA_Ben24" };
                titles     = new string[] { "Report of Benificiaries who turned 24" };
                colsName   = new string[][] { new string[] { "SSN", "EE#", "Last Name", "First Name", "HRA Balance" } };
                colsFormat = new string[][] { new string[] { "number", "number", "string", "string", "currency" } };
                colsWidth  = new int[][] { new int[] { 55, 55, 100, 100, 65 } };

                ds = HRAOperDAL.GetEligAuditData("Ben24", String.Empty);

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "EligNoBenOwner":
                sheetnames = new string[] { "HRA_NoBenOwner" };
                titles     = new string[] { "Report of Pilots who died with no owner verified" };
                colsName   = new string[][] { new string[] { "SSN", "EE#", "Last Name", "First Name", "Print Date", "HRA Balance", "Death Date" } };
                colsFormat = new string[][] { new string[] { "number", "number", "string", "string", "string", "currency", "string" } };
                colsWidth  = new int[][] { new int[] { 55, 55, 100, 100, 65, 65, 65 } };

                ds = HRAOperDAL.GetEligAuditData("NoBenOwner", String.Empty);

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "EligChildBenLtr":
                sheetnames = new string[] { "HRA_childBenLtr60" };
                titles     = new string[] { "Report on Child Beneficiary letter generated > 60 days ago where a validation date is not been entered" };
                colsName   = new string[][] { new string[] { "SSN", "EE#", "Last Name", "First Name", "HRA Balance", "Letter Date" } };
                colsFormat = new string[][] { new string[] { "number", "number", "string", "string", "currency", "string" } };
                colsWidth  = new int[][] { new int[] { 55, 55, 100, 100, 65, 65 } };

                ds = HRAOperDAL.GetEligAuditData("ChildBenLtr60", String.Empty);

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "EligDeathsXdays":
                sheetnames = new string[] { "HRA_deaths" + yrmo + "days" };
                titles     = new string[] { "Report on beneficiary details of pilots who died " + yrmo + " days from today" };
                colsName   = new string[][] { new string[] { "SSN", "EE#", "Last Name", "First Name", "Ben Order", "Ben DOB", "Relation", "Validation Date" } };
                colsFormat = new string[][] { new string[] { "number", "number", "string", "string", "number", "string", "string", "string" } };
                colsWidth  = new int[][] { new int[] { 55, 55, 100, 100, 40, 65, 50, 65 } };

                ds = HRAOperDAL.GetEligAuditData("DeathsXdays", yrmo);

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "AdminRecon":
                DataSet dsWgwk = new DataSet(); dsWgwk.Clear();
                DataSet dsPtnm = new DataSet(); dsPtnm.Clear();
                sheetnames = new string[] { "Reconciliation_" + yrmo };
                titles     = new string[] { "HRA Admin Invoice Reconciliation Report for Quarter/Year : " + yrmo };
                string[][] subtitles = { new string[] { "Recon of active HRA(s) managed by Wageworks", "Recon of HRA records that have balance with Putnam" } };
                colsFormat = new string[][] { new string[] { "string", "number", "number", "number", "number", "number", "number", "number", "currency", "currency", "currency" }, new string[] { "string", "number", "number", "number", "currency", "currency", "currency" } };
                colsWidth  = new int[][] { new int[] { 65, 65, 65, 65, 65, 65, 65, 65, 65, 65, 65 } };

                dsWgwk = hobj.GetWageworkSummaryRecon(yrmo);
                dsPtnm = hobj.GetPutnamSummaryRecon(yrmo);

                _content = xlObj.ExcelXMLRpt(dsWgwk, dsPtnm, _filename, sheetnames, titles, subtitles, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "AdminDispAP":
                DataSet ds1 = new DataSet(); ds1.Clear();
                DataSet ds2 = new DataSet(); ds2.Clear();
                YRMOs = HRA.GetYRMOs(yrmo);
                DateTime _date1 = HRA.GetLastDayofYRMO(YRMOs[0]);
                DateTime _date2 = HRA.GetLastDayofYRMO(YRMOs[1]);
                DateTime _date3 = HRA.GetLastDayofYRMO(YRMOs[2]);
                sheetnames = new string[] { YRMOs[0], YRMOs[1], YRMOs[2] };
                titles     = new string[] { "HRAAUDITR to Putnam Discrepancy Report for YRMO : " + YRMOs[0], "HRAAUDITR to Putnam Discrepancy Report for YRMO : " + YRMOs[1], "HRAAUDITR to Putnam Discrepancy Report for YRMO : " + YRMOs[2] };
                string[][] subtitles2 = { new string[] { "Mismatches with Modify Date <= " + _date1.ToString("MM/dd/yyyy"), "Mismatches with Modify Date > " + _date1.ToString("MM/dd/yyyy") }, new string[] { "Mismatches with Modify Date <= " + _date2.ToString("MM/dd/yyyy"), "Mismatches with Modify Date > " + _date2.ToString("MM/dd/yyyy") }, new string[] { "Mismatches with Modify Date <= " + _date3.ToString("MM/dd/yyyy"), "Mismatches with Modify Date > " + _date3.ToString("MM/dd/yyyy") } };
                colsFormat = new string[][] { new string[] { "number", "number", "string", "string", "number", "string", "string" }, new string[] { "number", "number", "string", "string", "number", "string", "string" } };
                colsWidth  = new int[][] { new int[] { 55, 55, 100, 100, 55, 65, 65 }, new int[] { 55, 55, 100, 100, 55, 65, 65 }, new int[] { 55, 55, 100, 100, 55, 65, 65 } };

                ds1 = hobj.GetRecon_HRAAUDITR_PartData1(yrmo);
                ds2 = hobj.GetRecon_HRAAUDITR_PartData2(yrmo);

                _content = xlObj.ExcelXMLRpt(ds1, ds2, _filename, sheetnames, titles, subtitles2, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "AdminDispPA":
                YRMOs      = HRA.GetYRMOs(yrmo);
                sheetnames = new string[] { YRMOs[0], YRMOs[1], YRMOs[2] };
                titles     = new string[] { "Putnam to HRAAUDITR Discrepancy Report for YRMO : " + YRMOs[0], "Putnam to HRAAUDITR Discrepancy Report for YRMO : " + YRMOs[1], "Putnam to HRAAUDITR Discrepancy Report for YRMO : " + YRMOs[2] };
                colsName   = new string[][] { new string[] { "SSN", "Last Name", "First Name", "Birth Date", "Term Date", "Total Asset Balance" }, new string[] { "SSN", "Last Name", "First Name", "Birth Date", "Term Date", "Total Asset Balance" }, new string[] { "SSN", "Last Name", "First Name", "Birth Date", "Term Date", "Total Asset Balance" } };
                colsFormat = new string[][] { new string[] { "number", "string", "string", "string", "string", "currency" }, new string[] { "number", "string", "string", "string", "string", "currency" }, new string[] { "number", "string", "string", "string", "string", "currency" } };
                colsWidth  = new int[][] { new int[] { 55, 100, 100, 65, 65, 65 }, new int[] { 55, 100, 100, 65, 65, 65 }, new int[] { 55, 100, 100, 65, 65, 65 } };

                ds = hobj.GetRecon_PartData_HRAAUDITR(yrmo);

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "AdminDispWP":
                YRMOs      = HRA.GetYRMOs(yrmo);
                sheetnames = new string[] { YRMOs[0], YRMOs[1], YRMOs[2] };
                titles     = new string[] { "Wageworks to Putnam Discrepancy Report for YRMO : " + YRMOs[0], "Wageworks to Putnam Discrepancy Report for YRMO : " + YRMOs[1], "Wageworks to Putnam Discrepancy Report for YRMO : " + YRMOs[2] };
                colsName   = new string[][] { new string[] { "Last Name", "First Name", "SSN", "" }, new string[] { "Last Name", "First Name", "SSN", "" }, new string[] { "Last Name", "First Name", "SSN", "" } };
                colsFormat = new string[][] { new string[] { "string", "string", "number", "string" }, new string[] { "string", "string", "number", "string" }, new string[] { "string", "string", "number", "string" } };
                colsWidth  = new int[][] { new int[] { 100, 100, 65, 92 }, new int[] { 100, 100, 65, 92 }, new int[] { 100, 100, 65, 92 } };

                ds = hobj.GetRecon_WgwkInv_PartData(yrmo);

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "AdminDispPW":
                YRMOs      = HRA.GetYRMOs(yrmo);
                sheetnames = new string[] { YRMOs[0], YRMOs[1], YRMOs[2] };
                titles     = new string[] { "Putnam to Wageworks Discrepancy Report for YRMO : " + YRMOs[0], "Putnam to Wageworks Discrepancy Report for YRMO : " + YRMOs[1], "Putnam to Wageworks Discrepancy Report for YRMO : " + YRMOs[2] };
                colsName   = new string[][] { new string[] { "Last Name", "First Name", "SSN", "Participant Status Description", "Birth Date", "Term Date", "Total Asset Balance" }, new string[] { "Last Name", "First Name", "SSN", "Participant Status Description", "Birth Date", "Term Date", "Total Asset Balance" }, new string[] { "Last Name", "First Name", "SSN", "Participant Status Description", "Birth Date", "Term Date", "Total Asset Balance" } };
                colsFormat = new string[][] { new string[] { "string", "string", "number", "string", "string", "string", "currency" }, new string[] { "string", "string", "number", "string", "string", "string", "currency" }, new string[] { "string", "string", "number", "string", "string", "string", "currency" } };
                colsWidth  = new int[][] { new int[] { 100, 100, 55, 140, 65, 65, 65 }, new int[] { 100, 100, 55, 140, 65, 65, 65 }, new int[] { 100, 100, 55, 140, 65, 65, 65 } };

                ds = hobj.GetRecon_PartData_WgwkInv(yrmo);

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "AdminWPnoBal":
                DataSet ds3 = new DataSet();
                YRMOs      = HRA.GetYRMOs(yrmo);
                sheetnames = new string[] { YRMOs[0], YRMOs[1], YRMOs[2] };
                titles     = new string[] { "Participant(s) in Wageworks Report with paid out in Putnam Report for YRMO : " + YRMOs[0], "Participant(s) in Wageworks Report with paid out in Putnam Report for YRMO : " + YRMOs[1], "Participant(s) in Wageworks Report with paid out in Putnam Report for YRMO : " + YRMOs[2] };
                colsFormat = new string[][] { new string[] { "string", "string", "number", "currency" }, new string[] { "string", "string", "number", "currency" } };
                colsWidth  = new int[][] { new int[] { 100, 100, 55, 65 }, new int[] { 100, 100, 55, 65 }, new int[] { 100, 100, 55, 65 } };
                string[][] subtitles3 = { new string[] { "Participants with Zero Balance", "Participants with status 'Terminated, Paid Out' and have balance" }, new string[] { "Participants with Zero Balance", "Participants with status 'Terminated, Paid Out' and have balance" }, new string[] { "Participants with Zero Balance", "Participants with status 'Terminated, Paid Out' and have balance" } };

                ds  = hobj.GetNoBal_WgwkInv_PartData(yrmo);
                ds3 = hobj.GetPaidOut_WgwkInv_PartData(yrmo);

                _content = xlObj.ExcelXMLRpt(ds, ds3, _filename, sheetnames, titles, subtitles3, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "HRAAUDITR":
                sheetnames = new string[] { "HRAAUDITR" };
                titles     = new string[] { "HRAAUDITR Report used for Admin Bill Reconciliation in Quarter-Year: " + yrmo };
                colsName   = new string[][] { new string[] { "SSN", "EE#", "Name", "Birth Date", "Age", "Status", "Status Date", "Modify Date" } };
                colsFormat = new string[][] { new string[] { "number", "number", "string", "string", "number", "string", "string", "string" } };
                colsWidth  = new int[][] { new int[] { 55, 55, 140, 65, 40, 55, 65, 77 } };

                ds = HRAAdminDAL.GetAUDITR_Data(yrmo);

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;

            case "PtnmPartData":
                sheetnames = new string[] { "Putnam Participant Data" };
                titles     = new string[] { "Data from Putnam Participant Data Input Report used for Admin Bill Reconciliation in Quarter-Year: " + yrmo };
                colsName   = new string[][] { new string[] { "SSN", "Last Name", "First Name", "Participant Status Description", "Birth Date", "Term Date", "Total Asset Balance" } };
                colsFormat = new string[][] { new string[] { "number", "string", "string", "string", "string", "string", "currency" } };
                colsWidth  = new int[][] { new int[] { 60, 100, 100, 145, 65, 65, 65 } };

                ds = HRAAdminDAL.GetPutnamParticipant_Data(yrmo);

                _content = xlObj.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsName, colsFormat, colsWidth);
                _content = LetterGen.replaceIllegalXMLCharacters(_content);
                break;
            }

            return(_content);
        }