public int GetHRAAuditRCount(string yrmo) { int count = 0; DateTime date = HRA.GetLastDayofYRMO(yrmo); string _qy = HRA.GetQuarterYear(yrmo); string cmdstr = "SELECT COUNT(*) FROM hra_AUDITR WHERE modifydt <= @date AND period= @qy"; try { if (connect == null || connect.State == ConnectionState.Closed) { connect.Open(); } command = new SqlCommand(cmdstr, connect); command.Parameters.AddWithValue("@qy", _qy); command.Parameters.AddWithValue("@date", date); count = Convert.ToInt32(command.ExecuteScalar()); return(count); } finally { connect.Close(); } }
public int GetPutnamPartDataCount(string yrmo) { int count = 0; DateTime _date = HRA.GetLastDayofYRMO(yrmo); string _qy = HRA.GetQuarterYear(yrmo); string cmdstr = "SELECT COUNT(*) " + "FROM hra_PartDataInvoice " + "WHERE source = 'ptnm_partdata' " + "AND period = @qy " + "AND termdt <= @date " + "AND balance > 0 " + "AND (LOWER(RTRIM(LTRIM(partStatDesc))) <> 'terminated, paid out') "; try { if (connect == null || connect.State == ConnectionState.Closed) { connect.Open(); } command = new SqlCommand(cmdstr, connect); command.Parameters.AddWithValue("@qy", _qy); command.Parameters.AddWithValue("@date", _date); count = Convert.ToInt32(command.ExecuteScalar()); return(count); } finally { connect.Close(); } }
public static Boolean hasPartData_AuditR_Discp(string _qy) { Boolean _result; HRA hobj = new HRA(); int count = 0; string yrmo = hobj.GetMaxQuarterYRMO(_qy); DateTime date = HRA.GetLastDayofYRMO(yrmo); string cmdstr = "SELECT COUNT(*) " + "FROM hra_PartDataInvoice " + "WHERE source = 'ptnm_partdata' " + "AND period = @qy " + "AND termdt <= @date " + "AND balance <> 0 " + "AND (LOWER(RTRIM(LTRIM(partStatDesc))) <> 'terminated, paid out') " + "AND CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) NOT IN " + "( " + "SELECT CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) " + "FROM hra_AUDITR " + "WHERE modifydt <= @date " + "AND period= @qy " + ") " + "AND CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) NOT IN " + "( " + "SELECT CONVERT(INT, LTRIM(RTRIM(REPLACE(dpnd_ssn, '-', '')))) " + "FROM Dependant " + "WHERE dpnd_owner = 1 AND dpnd_validated = 1 " + ") "; try { if (connect != null && connect.State == ConnectionState.Closed) { connect.Open(); } command = new SqlCommand(cmdstr, connect); command.Parameters.AddWithValue("@qy", _qy); command.Parameters.AddWithValue("@date", date); count = Convert.ToInt32(command.ExecuteScalar()); if (count != 0) { _result = true; } else { _result = false; } command.Dispose(); return(_result); } finally { connect.Close(); } }
public DataSet GetPartData_AuditR_Discp(string yrmo) { DateTime date = HRA.GetLastDayofYRMO(yrmo); string _qy = HRA.GetQuarterYear(yrmo); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); ds.Clear(); string cmdstr = "SELECT ssn AS [SSN], " + "lastname AS [Last Name], " + "firstname AS [First Name], " + "CONVERT(VARCHAR(15),dob,101) AS [Birth Date], " + "CONVERT(VARCHAR(15),termdt,101) AS [Termination Date], " + "balance AS [Total Asset Balance] " + "FROM hra_PartDataInvoice " + "WHERE source = 'ptnm_partdata' " + "AND period = @qy " + "AND termdt <= @date " + "AND balance > 0 " + "AND (LOWER(RTRIM(LTRIM(partStatDesc))) <> 'terminated, paid out') " + "AND CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) NOT IN " + "( " + "SELECT CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) " + "FROM hra_AUDITR " + "WHERE modifydt <= @date " + "AND period= @qy " + ") " + "AND CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) NOT IN " + "( " + "SELECT CONVERT(INT, LTRIM(RTRIM(REPLACE(dpnd_ssn, '-', '')))) " + "FROM Dependant " + "WHERE dpnd_owner = 1 AND dpnd_validated = 1 " + ") " + "ORDER BY lastname, firstname"; try { if (connect == null || connect.State == ConnectionState.Closed) { connect.Open(); } command = new SqlCommand(cmdstr, connect); command.Parameters.AddWithValue("@qy", _qy); command.Parameters.AddWithValue("@date", date); da.SelectCommand = command; da.Fill(ds); return(ds); } finally { connect.Close(); } }
public static Boolean hasWgwkInv_PartData_noBal(string _qy) { Boolean _result; HRA hobj = new HRA(); int count = 0; string yrmo = hobj.GetMaxQuarterYRMO(_qy); string prevyrmo = hobj.getPrevYRMO(yrmo); string priyrmo = hobj.getPrevYRMO(prevyrmo); DateTime date = HRA.GetLastDayofYRMO(yrmo); string cmdstr = "SELECT COUNT(DISTINCT ssn) " + "FROM hra_PartDataInvoice " + "WHERE source = 'wgwk_invoice' " + "AND period IN (@yrmo, @prevyrmo, @priyrmo) " + "AND CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) IN " + "( " + "SELECT CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) " + "FROM hra_PartDataInvoice " + "WHERE source = 'ptnm_partdata' " + "AND period = @qy " + "AND termdt <= @date " + "AND (balance = 0 OR (LOWER(RTRIM(LTRIM(partStatDesc))) = 'terminated, paid out')) " + ") "; try { if (connect != null && connect.State == ConnectionState.Closed) { connect.Open(); } command = new SqlCommand(cmdstr, connect); command.Parameters.AddWithValue("@qy", _qy); command.Parameters.AddWithValue("@date", date); command.Parameters.AddWithValue("@yrmo", yrmo); command.Parameters.AddWithValue("@prevyrmo", prevyrmo); command.Parameters.AddWithValue("@priyrmo", priyrmo); count = Convert.ToInt32(command.ExecuteScalar()); if (count != 0) { _result = true; } else { _result = false; } command.Dispose(); return(_result); } finally { connect.Close(); } }
public DataSet GetWgwkInv_PartData_PaidOut(string yrmo) { DateTime date = HRA.GetLastDayofYRMO(yrmo); string _qy = HRA.GetQuarterYear(yrmo); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); ds.Clear(); string cmdstr = "SELECT " + "lastname AS [Last Name], " + "firstname AS [First Name], " + "ssn AS [SSN], " + "balance AS [Total Asset Balance] " + "FROM hra_PartDataInvoice " + "WHERE source = 'ptnm_partdata' " + "AND balance > 0 " + "AND period = @qy " + "AND termdt <= @date " + "AND (LOWER(RTRIM(LTRIM(partStatDesc))) = 'terminated, paid out') " + "AND CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) IN " + "( " + "SELECT CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) " + "FROM hra_PartDataInvoice " + "WHERE source = 'wgwk_invoice' " + "AND period = @yrmo " + ") " + "ORDER BY lastname, firstname"; try { if (connect == null || connect.State == ConnectionState.Closed) { connect.Open(); } command = new SqlCommand(cmdstr, connect); command.Parameters.AddWithValue("@qy", _qy); command.Parameters.AddWithValue("@date", date); command.Parameters.AddWithValue("@yrmo", yrmo); da.SelectCommand = command; da.Fill(ds); return(ds); } finally { connect.Close(); } }
public DataSet GetAuditR_PartData_Discp2(string yrmo) { DateTime date = HRA.GetLastDayofYRMO(yrmo); string _qy = HRA.GetQuarterYear(yrmo); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); ds.Clear(); string cmdstr = "SELECT ssn AS [SSN], " + "empno AS [EE#], " + "substring(name,charindex(',', name)+1,len(name)) AS [Last Name], " + "substring(name,1,charindex(',', name)-1) AS [First Name], " + "age AS [Age], " + "CONVERT(VARCHAR(15),statusdt,101) AS [Status Date], " + "CONVERT(VARCHAR(15),modifydt,101) AS [Modify Date] " + "FROM hra_AUDITR " + "WHERE modifydt > @date " + "AND period= @qy " + "AND CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) NOT IN " + "( " + "SELECT CONVERT(INT, LTRIM(RTRIM(REPLACE(ssn, '-', '')))) " + "FROM hra_PartDataInvoice " + "WHERE source = 'ptnm_partdata' " + "AND period = @qy " + "AND termdt <= @date " + "AND balance > 0 " + "AND (LOWER(RTRIM(LTRIM(partStatDesc))) <> 'terminated, paid out') " + ") " + "ORDER BY [Last Name], [First Name]"; try { if (connect == null || connect.State == ConnectionState.Closed) { connect.Open(); } command = new SqlCommand(cmdstr, connect); command.Parameters.AddWithValue("@qy", _qy); command.Parameters.AddWithValue("@date", date); da.SelectCommand = command; da.Fill(ds); return(ds); } finally { connect.Close(); } }
public static void Pass3_CurWageworks(string yrmo) { Nullable <DateTime> wgwkdt, wgwkLateDt, ptnmAdjLateDt, ptnmLateDt; Decimal totptnmamt, totwgwkamt, wgwkamt2, priCarryAmt, totPtnmAdjAmt; string trantype, CFids; DataSet dswgwk = new DataSet(); dswgwk.Clear(); dswgwk = HRA_ReconDAL.getWageworksData(yrmo); wgwkLateDt = HRA_ReconDAL.getWgwkCurLateDt(yrmo); CFids = ""; foreach (DataRow row in dswgwk.Tables[0].Rows) { if (row["createdt"] == null) { wgwkdt = null; } else { wgwkdt = Convert.ToDateTime(row["createdt"]); } totptnmamt = HRA_ReconDAL.getPtnmCurTotAmt(yrmo, row["ssn"].ToString()); totwgwkamt = (-1) * HRA_ReconDAL.getWgwkCurTotAmt(yrmo, row["ssn"].ToString()); wgwkamt2 = (-1) * HRA_ReconDAL.getWgwkAmt2(yrmo, row["ssn"].ToString()); priCarryAmt = HRA_ReconDAL.getPriCarryAmt(yrmo, row["ssn"].ToString()); ptnmLateDt = HRA_ReconDAL.getPtnmCurLateDt(yrmo, row["ssn"].ToString()); ptnmAdjLateDt = HRA_ReconDAL.getPtnmAdjCurLateDt(yrmo, row["ssn"].ToString()); totPtnmAdjAmt = (-1) * HRA_ReconDAL.getPtnmAdjCurTotAmt(yrmo, row["ssn"].ToString()); trantype = row["trantype"].ToString().Trim().ToUpper(); priCarryAmt = (-1) * priCarryAmt; totwgwkamt = totwgwkamt + priCarryAmt; wgwkamt2 = wgwkamt2 + priCarryAmt; if (ptnmLateDt == null && ptnmAdjLateDt == null) { if (totwgwkamt != 0) { CFids = CFids + ", " + Convert.ToInt32(row["id"]); continue; } } else { if (ptnmAdjLateDt == null) { if (totwgwkamt != totptnmamt) { if (wgwkamt2 == totptnmamt) { if (wgwkdt == wgwkLateDt) { CFids = CFids + ", " + Convert.ToInt32(row["id"]); continue; } } else { if (wgwkdt == ptnmLateDt) { if (!((trantype == "CARD TRANSACTION") && (totptnmamt > wgwkamt2))) { CFids = CFids + ", " + Convert.ToInt32(row["id"]); continue; } } else { if (wgwkdt > ptnmLateDt) { CFids = CFids + ", " + Convert.ToInt32(row["id"]); continue; } } } } } else { if ((totwgwkamt + totPtnmAdjAmt) != totptnmamt) { if (((wgwkdt > ptnmLateDt) && (wgwkdt > ptnmAdjLateDt)) || ((wgwkdt >= ptnmLateDt) && (wgwkdt >= ptnmAdjLateDt) && (wgwkdt == HRA.GetLastDayofYRMO(yrmo)))) { CFids = CFids + ", " + Convert.ToInt32(row["id"]); continue; } } } } } CFids = CFids.TrimStart(','); if (CFids == String.Empty) { CFids = "-1"; } HRA_ReconDAL.InsertCFData(yrmo, CFids); HRA_ReconDAL.InsertWageworks(yrmo, CFids); HRA_ReconDAL.InsertCarryAmt(yrmo, CFids); }
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); }