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);
}
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);
}
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());
}
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);
}
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);
}
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);
}
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);
}