public DataSet GetNoBal_WgwkInv_PartData(string _qy) { HRA hobj = new HRA(); HRAAdminDAL dobj = new HRAAdminDAL(); string[] YRMOs = HRA.GetYRMOs(_qy); DataSet dsWgwkPart = new DataSet(); dsWgwkPart.Clear(); DataTable dsTable; foreach (string yrmo in YRMOs) { dsTable = new DataTable(); dsTable = dobj.GetWgwkInv_PartData_noBal(yrmo).Tables[0].Copy(); dsTable.TableName = "recon3_" + yrmo; dsWgwkPart.Tables.Add(dsTable); } return(dsWgwkPart); }
public DataSet GetPaidOut_WgwkInv_PartData(string _qy) { HRA hobj = new HRA(); HRAAdminDAL dobj = new HRAAdminDAL(); string[] YRMOs = HRA.GetYRMOs(_qy); DataSet dsWgwkPart2 = new DataSet(); DataTable dsTable; foreach (string yrmo in YRMOs) { dsTable = new DataTable(); dsTable = dobj.GetWgwkInv_PartData_PaidOut(yrmo).Tables[0].Copy(); dsTable.TableName = "recon4_" + yrmo; dsWgwkPart2.Tables.Add(dsTable); } return(dsWgwkPart2); }
public DataSet GetRecon_HRAAUDITR_PartData2(string _qy) { HRA hobj = new HRA(); HRAAdminDAL dobj = new HRAAdminDAL(); string[] YRMOs = HRA.GetYRMOs(_qy); DataSet dsAuditPart2 = new DataSet(); dsAuditPart2.Clear(); DataTable dsTable; foreach (string yrmo in YRMOs) { dsTable = new DataTable(); dsTable = dobj.GetAuditR_PartData_Discp2(yrmo).Tables[0].Copy(); dsTable.TableName = "recon2b_" + yrmo; dsAuditPart2.Tables.Add(dsTable); } return(dsAuditPart2); }
public void CheckRptsImported(string _qy) { string[] YRMOs = HRA.GetYRMOs(_qy); string errmsg = ""; string[] source = { "ptnm_invoice", "ptnm_partdata", "wgwk_invoice" }; HRAImportDAL iobj = new HRAImportDAL(); if (!HRAAdminDAL.AUDITRInserted(_qy)) { errmsg = "HRAAUDITR data is not present for Quarter/Year - " + _qy + ". Run HRA Operations process!<br/>"; } if (!iobj.PastImport(source[0], _qy)) { errmsg += "Putnam Invoice report not imported for Quarter/Year - " + _qy + "<br />"; } if (!iobj.PastImport(source[1], _qy)) { errmsg += "Putnam's Participant Data report not imported<br />"; } string temp = ""; for (int i = 0; i < 3; i++) { if (!iobj.PastImport(source[2], YRMOs[i])) { temp += YRMOs[i] + ", "; } } if (temp != "") { errmsg += "Wageworks Invoice report not imported for YRMO(s) - " + temp.Remove(temp.Length - 2) + "<br />"; } if (errmsg != "") { throw new Exception(errmsg); } }
public DataSet GetRecon_PartData_WgwkInv(string _qy) { HRA hobj = new HRA(); HRAAdminDAL dobj = new HRAAdminDAL(); string[] YRMOs = HRA.GetYRMOs(_qy); DataSet dsPartWgwkInv = new DataSet(); dsPartWgwkInv.Clear(); DataTable dsTable; foreach (string yrmo in YRMOs) { dsTable = new DataTable(); dsTable = dobj.GetPartData_WgwkInv_Discp(yrmo).Tables[0].Copy(); dsTable.TableName = "recon1_" + yrmo; dsPartWgwkInv.Tables.Add(dsTable); } return(dsPartWgwkInv); }
public DataSet GetPutnamSummaryRecon(string _qy) { HRAAdminDAL dobj = new HRAAdminDAL(); int ptnminv2_cnt, ptnmPart_cnt; decimal rate, ptnminv2_amt, ptnminv2_calcamt; DataSet ds = new DataSet(); ds.Clear(); DataSet dsPtnmSum = new DataSet(); dsPtnmSum.Clear(); DataTable dsTable; dsTable = dsPtnmSum.Tables.Add("PtnmSummary"); DataRow row; DataColumn col; col = new DataColumn("Period"); dsTable.Columns.Add(col); col = new DataColumn("Putnam Invoice Count"); dsTable.Columns.Add(col); col = new DataColumn("Putnam Participant Data Count"); dsTable.Columns.Add(col); col = new DataColumn("Diff(B-C)"); dsTable.Columns.Add(col); col = new DataColumn("Rate"); dsTable.Columns.Add(col); col = new DataColumn("Putnam Invoice Amount"); dsTable.Columns.Add(col); col = new DataColumn("Amount(BxE)"); dsTable.Columns.Add(col); ds = dobj.GetPutnamInvData(_qy); ptnminv2_cnt = GetPutnamInvPart2Count(ds); ptnmPart_cnt = dobj.GetPtnmPartDataCountHavingBal(_qy); rate = dobj.GetPutnamHRARecordRate(_qy); ptnminv2_amt = GetPutnamInvPart2Amt(ds); ptnminv2_calcamt = ptnminv2_cnt * rate; row = dsTable.NewRow(); row["Period"] = _qy; row["Putnam Invoice Count"] = ptnminv2_cnt; row["Putnam Participant Data Count"] = ptnmPart_cnt; row["Diff(B-C)"] = (ptnminv2_cnt - ptnmPart_cnt); row["Rate"] = rate; row["Putnam Invoice Amount"] = ptnminv2_amt; row["Amount(BxE)"] = ptnminv2_calcamt; dsTable.Rows.Add(row); return(dsPtnmSum); }
public DataSet GetWageworkSummaryRecon(string _qy) { HRA hobj = new HRA(); HRAAdminDAL dobj = new HRAAdminDAL(); string[] YRMOs = HRA.GetYRMOs(_qy); int ptnminv1_cnt, wgwkinv_cnt, hraAuditR_cnt, ptnmPartData_cnt; decimal rate, ptnminv1_amt, ptnminv1_calcamt; Decimal totamt1, totamt2; DataSet ds = new DataSet(); ds.Clear(); DataSet dsWgwkSum = new DataSet(); dsWgwkSum.Clear(); DataTable dsTable; dsTable = dsWgwkSum.Tables.Add("WgwkSummary"); DataRow row; DataColumn col; System.Type typeDecimal = System.Type.GetType("System.Decimal"); col = new DataColumn("Period"); dsTable.Columns.Add(col); col = new DataColumn("Putnam Invoice Count"); dsTable.Columns.Add(col); col = new DataColumn("Wageworks Invoice Count"); dsTable.Columns.Add(col); col = new DataColumn("Diff(B-C)"); dsTable.Columns.Add(col); col = new DataColumn("Putnam Participant Data Count"); dsTable.Columns.Add(col); col = new DataColumn("Diff(C-E)"); dsTable.Columns.Add(col); col = new DataColumn("HRAAUDITR Count(as of " + DateTime.Today.ToString("MM/dd/yyyy") + ")"); dsTable.Columns.Add(col); col = new DataColumn("Diff(E-G)"); dsTable.Columns.Add(col); col = new DataColumn("Rate"); dsTable.Columns.Add(col); col = new DataColumn("Putnam Invoice Amount", typeDecimal); dsTable.Columns.Add(col); col = new DataColumn("Amount(BxI)", typeDecimal); dsTable.Columns.Add(col); foreach (string yrmo in YRMOs) { ds = dobj.GetPutnamInvData(_qy); ptnminv1_cnt = GetPutnamInvPart1Count(ds, yrmo); wgwkinv_cnt = dobj.GetWgwkInvCount(yrmo); ptnmPartData_cnt = dobj.GetPutnamPartDataCount(yrmo); hraAuditR_cnt = dobj.GetHRAAuditRCount(yrmo); rate = dobj.GetWageworkHeadcountRate(yrmo); ptnminv1_amt = GetPutnamInvPart1Amt(ds, yrmo); ptnminv1_calcamt = ptnminv1_cnt * rate; row = dsTable.NewRow(); row["Period"] = hobj.GetYRMOformated(yrmo); row["Putnam Invoice Count"] = ptnminv1_cnt; row["Wageworks Invoice Count"] = wgwkinv_cnt; row["Diff(B-C)"] = (ptnminv1_cnt - wgwkinv_cnt); row["Putnam Participant Data Count"] = ptnmPartData_cnt; row["Diff(C-E)"] = (wgwkinv_cnt - ptnmPartData_cnt); row["HRAAUDITR Count(as of " + DateTime.Today.ToString("MM/dd/yyyy") + ")"] = hraAuditR_cnt; row["Diff(E-G)"] = (ptnmPartData_cnt - hraAuditR_cnt); row["Rate"] = rate; row["Putnam Invoice Amount"] = ptnminv1_amt; row["Amount(BxI)"] = ptnminv1_calcamt; dsTable.Rows.Add(row); } totamt1 = Convert.ToDecimal(dsWgwkSum.Tables[0].Compute("SUM([Putnam Invoice Amount])", String.Empty)); totamt2 = Convert.ToDecimal(dsWgwkSum.Tables[0].Compute("SUM([Amount(BxI)])", String.Empty)); row = dsTable.NewRow(); row["Rate"] = "TOTAL:"; row["Putnam Invoice Amount"] = totamt1; row["Amount(BxI)"] = totamt2; dsTable.Rows.Add(row); return(dsWgwkSum); }
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); }
public int parsePutnamPartData(DataSet ds, string _fname, string source, string _qy) { Decimal balance; String ssn, lname, fname, partStatDesc, dob, termdt; SqlDateTime dobSql, termdtSql; int _count = 0; List <int> cols; List <string> excludeSSNs; Boolean excludeSSNfound; String _tableName = "PutnamPartTable"; String[] colsH = new String[] { "SSN", "First Name", "Last Name", "Participant Status Description", "Date of Birth", "Termination Date", "Total Asset Balance" }; HRAImportDAL iObj = new HRAImportDAL(); cols = getColsIndices(ds, _tableName, colsH); excludeSSNs = HRAAdminDAL.GetExcludeSSNs(); for (int i = 0; i < ds.Tables[_tableName].Rows.Count; i++) { ssn = ds.Tables[_tableName].Rows[i][cols[0]].ToString().Trim(); if (ssn.Contains("-")) { ssn = ssn.Replace("-", ""); } ssn = ssn.TrimStart('0'); excludeSSNfound = false; foreach (string excludeSSN in excludeSSNs) { if (ssn.Equals(excludeSSN)) { excludeSSNfound = true; break; } } if (excludeSSNfound) { continue; } fname = ds.Tables[_tableName].Rows[i][cols[1]].ToString().Trim(); fname = HRA.GetProperCase(fname); if (fname.Contains(" ")) { fname = fname.Replace(" ", " "); fname = fname.Replace("'", ""); } lname = ds.Tables[_tableName].Rows[i][cols[2]].ToString().Trim(); lname = HRA.GetProperCase(lname); if (lname.Contains(" ")) { lname = lname.Replace(" ", " "); lname = lname.Replace("'", ""); } partStatDesc = ds.Tables[_tableName].Rows[i][cols[3]].ToString().Trim(); dob = ds.Tables[_tableName].Rows[i][cols[4]].ToString().Trim(); if (dob != null && dob != string.Empty) { dobSql = Convert.ToDateTime(dob); } else { dobSql = SqlDateTime.Null; } termdt = ds.Tables[_tableName].Rows[i][cols[5]].ToString().Trim(); if (termdt != null && termdt != string.Empty) { termdtSql = Convert.ToDateTime(termdt); } else { termdtSql = SqlDateTime.Null; } balance = Decimal.Parse(ds.Tables[_tableName].Rows[i][cols[6]].ToString(), System.Globalization.NumberStyles.Currency); if (iObj.insertPutnamPartData(ssn, fname, lname, partStatDesc, dobSql, termdtSql, balance, source, _qy)) { _count++; } } iObj.insertImportStatus(_qy, "ptnm_partdata"); return(_count); }