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 decimal GetPutnamHRARecordRate(string _qy)
{
HRA hobj = new HRA();
decimal rate = 0;
string yrmo = hobj.GetMaxQuarterYRMO(_qy);
string cmdstr = "SELECT rate FROM hra_rates "
+ "WHERE yrmo = "
+ "( "
+ "SELECT MAX(yrmo) FROM hra_rates "
+ "WHERE yrmo <= @yrmo AND type = 'Putnam' "
+ ") "
+ "AND type = 'Putnam'";
try
{
if (connect == null || connect.State == ConnectionState.Closed)
{
connect.Open();
}
command = new SqlCommand(cmdstr, connect);
command.Parameters.AddWithValue("@yrmo", yrmo);
object result = command.ExecuteScalar();
if ((result == null) || (result == DBNull.Value))
{
throw new Exception("Cannot find Putnam HRA Record Rate for YRMO - " + yrmo + " or less");
}
rate = Decimal.Parse(result.ToString());
return(rate);
}
finally
{
connect.Close();
}
}
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 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();
}
}
string GetEligFileHeader(string hraPlanNum)
{
StringBuilder sb = new StringBuilder();
sb.Append("PUTSY");
sb.Append(HRA.GetFixedLengthString(hraPlanNum, 6));
sb.Append(HRA.GetFixedLengthString("CENSUS", 9));
sb.Append(' ', 60);
return(sb.ToString());
}
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 void Pass1(String yrmo, Decimal begBal)
{
HRASofoDAL sobj = new HRASofoDAL();
HRA gobj = new HRA();
String priyrmo = gobj.getPrevYRMO(yrmo);
Decimal diffamt, prev_endBal;
String errMsg = "";
prev_endBal = sobj.getPrevEndBal(priyrmo);
diffamt = begBal - prev_endBal;
if (diffamt != 0)
{
throw new Exception("Previous month ending balance " + prev_endBal.ToString("C") + " does not equal current month beginning balance " + begBal.ToString("C") + " !");
}
}
string GetEligFileFooter(int counter, string hraPlanNum)
{
string strcntr = counter.ToString();
strcntr = strcntr.PadLeft(11, '0');
StringBuilder sb = new StringBuilder();
sb.Append("PUTE");
sb.Append(' ', 1);
sb.Append(HRA.GetFixedLengthString(hraPlanNum, 6));
sb.Append(HRA.GetFixedLengthString("CENSUS", 9));
sb.Append(' ', 29);
sb.Append(strcntr);
sb.Append(' ', 20);
return(sb.ToString());
}
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 static Boolean AUDITRInserted(string _qy)
{
HRA hobj = new HRA();
int count = 0;
string cmdstr = "SELECT COUNT(*) FROM [hra_AUDITR] WHERE [period] = @qy";
try
{
if (connect == null || connect.State == ConnectionState.Closed)
{
connect.Open();
}
command = new SqlCommand(cmdstr, connect);
command.Parameters.AddWithValue("@qy", _qy);
count = Convert.ToInt32(command.ExecuteScalar());
if (count == 0)
{
if (Convert.ToInt32(hobj.GetMaxQuarterYRMO(_qy)) < 200804)
{
return(true);
}
else
{
return(false);
}
}
else
{
return(true);
}
}
finally
{
connect.Close();
}
}
public int parsePutnamAdj(DataSet ds, string _yrmo)
{
DateTime distdt;
string transType = "Adj";
Decimal distamt;
String ssn, name, lname, fname;
int _count = 0;
List <int> cols;
string _tableName = "PutnamAdjTable";
String[] colsH = new String[] { "TRADE", "Amount", "SSN", "Name" };
HRAImportDAL iObj = new HRAImportDAL();
cols = getColsIndices(ds, _tableName, colsH);
for (int i = 0; i < ds.Tables[_tableName].Rows.Count; i++)
{
distdt = Convert.ToDateTime(ds.Tables[_tableName].Rows[i][cols[0]]);
distamt = Decimal.Parse(ds.Tables[_tableName].Rows[i][cols[1]].ToString(), System.Globalization.NumberStyles.Currency);
ssn = ds.Tables[_tableName].Rows[i][cols[2]].ToString();
if (ssn.Contains("-"))
{
ssn = ssn.Replace("-", "");
}
name = ds.Tables[_tableName].Rows[i][cols[3]].ToString();
lname = name.Substring(0, name.IndexOf(',')).Trim(); lname = HRA.GetProperCase(lname);
fname = name.Substring(name.IndexOf(',') + 1).Trim(); fname = HRA.GetProperCase(fname);
if (iObj.insertPutnamAdj(_yrmo, distdt, transType, distamt, ssn, lname, fname))
{
_count++;
}
}
iObj.insertImportStatus(_yrmo, "PutnamAdj");
return(_count);
}
public static void InsertAuditR()
{
DataSet ds = new DataSet(); ds.Clear();
ds = GetEligAuditData("HRAAUDITR", String.Empty);
string cmdstr1, cmdstr2, ssn, name, status, _period;
int empno, age, _uid;
Nullable <DateTime> createdt, statusdt, modifydt, dob;
if (connect == null || connect.State == ConnectionState.Closed)
{
connect.Open();
}
try
{
_uid = Convert.ToInt32(HttpContext.Current.User.Identity.Name);
createdt = DateTime.Today;
_period = HRA.GetQuarterYear(DateTime.Today);
cmdstr1 = "DELETE FROM [hra_AUDITR] WHERE [period] = @period";
command = new SqlCommand(cmdstr1, connect);
command.Parameters.AddWithValue("@period", _period);
command.ExecuteNonQuery(); command.Dispose();
foreach (DataRow row in ds.Tables[0].Rows)
{
empno = Convert.ToInt32(row["Emp#"]);
ssn = row["SSN"].ToString();
name = row["Name"].ToString();
dob = Convert.ToDateTime(row["Birth Date"]);
age = Convert.ToInt32(row["Age"]);
status = row["Status"].ToString();
statusdt = Convert.ToDateTime(row["Status Date"]);
modifydt = Convert.ToDateTime(row["Modify Date"]);
cmdstr2 = "INSERT INTO [hra_AUDITR] "
+ "([empno] "
+ ",[period] "
+ ",[userID] "
+ ",[createdt] "
+ ",[ssn] "
+ ",[name] "
+ ",[dob] "
+ ",[age] "
+ ",[status] "
+ ",[statusdt] "
+ ",[modifydt]) "
+ "VALUES "
+ "(@empno "
+ ",@period "
+ ",@uid "
+ ",@createdt "
+ ",@ssn "
+ ",@name "
+ ",@dob "
+ ",@age "
+ ",@status "
+ ",@statusdt "
+ ",@modifydt)";
command = new SqlCommand(cmdstr2, connect);
command.Parameters.AddWithValue("@empno", empno);
command.Parameters.AddWithValue("@period", _period);
command.Parameters.AddWithValue("@uid", _uid);
command.Parameters.AddWithValue("@createdt", createdt);
command.Parameters.AddWithValue("@ssn", ssn);
command.Parameters.AddWithValue("@name", name);
command.Parameters.AddWithValue("@dob", dob);
command.Parameters.AddWithValue("@age", age);
command.Parameters.AddWithValue("@status", status);
command.Parameters.AddWithValue("@statusdt", statusdt);
command.Parameters.AddWithValue("@modifydt", modifydt);
command.ExecuteNonQuery(); command.Dispose();
}
}
finally
{
connect.Close();
}
}
public int parseWageworkInvoice(DataSet ds, string _fname, string source, string yrmo)
{
String last4ssn, lname, fname;
int _count = 0;
List <int> cols;
String _tableName = "WgwkInvTable";
String[] colsH = new String[] { "Last Name", "First Name", "ID Code" };
HRAImportDAL iObj = new HRAImportDAL();
cols = getColsIndices(ds, _tableName, colsH);
for (int i = 0; i < ds.Tables[_tableName].Rows.Count; i++)
{
lname = ds.Tables[_tableName].Rows[i][cols[0]].ToString().Trim(); lname = HRA.GetProperCase(lname);
if (lname.Contains(" "))
{
lname = lname.Replace(" ", " ");
lname = lname.Replace("'", "");
}
fname = ds.Tables[_tableName].Rows[i][cols[1]].ToString().Trim(); fname = HRA.GetProperCase(fname);
if (fname.Contains(" "))
{
fname = fname.Replace(" ", " ");
fname = fname.Replace("'", "");
}
last4ssn = ds.Tables[_tableName].Rows[i][cols[2]].ToString().Trim();
if (iObj.insertWageworkInvoice(lname, fname, last4ssn, source, yrmo))
{
_count++;
}
}
iObj.insertImportStatus(yrmo, "wgwk_invoice");
return(_count);
}
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 int parseWageworks(DataSet ds, string _yrmo)
{
DateTime createdt;
Decimal amt;
String transtype, lname, fname, last4ssn;
int _count = 0;
List <int> cols;
string _tableName = "WageworksTable";
String[] colsH = new String[] { "CREATE_DATE", "Amount", "TRANSACTION_TYPE", "Last_Name", "First_Name", "LAST_4_SSN" };
HRAImportDAL iObj = new HRAImportDAL();
cols = getColsIndices(ds, _tableName, colsH);
for (int i = 0; i < ds.Tables[_tableName].Rows.Count; i++)
{
createdt = Convert.ToDateTime(ds.Tables[_tableName].Rows[i][cols[0]]);
amt = Decimal.Parse(ds.Tables[_tableName].Rows[i][cols[1]].ToString(), System.Globalization.NumberStyles.Currency);
transtype = ds.Tables[_tableName].Rows[i][cols[2]].ToString();
lname = ds.Tables[_tableName].Rows[i][cols[3]].ToString().Trim(); lname = HRA.GetProperCase(lname);
fname = ds.Tables[_tableName].Rows[i][cols[4]].ToString().Trim(); fname = HRA.GetProperCase(fname);
last4ssn = ds.Tables[_tableName].Rows[i][cols[5]].ToString();
if (iObj.insertWageworks(_yrmo, createdt, amt, transtype, lname, fname, last4ssn))
{
_count++;
}
}
iObj.insertImportStatus(_yrmo, "Wageworks");
return(_count);
}
public int parsePutnam(DataSet ds, string _yrmo, string _fname)
{
DateTime distdt;
Decimal distamt;
String transType, ssn, lname, fname;
int _count = 0;
List <int> cols;
String _tableName = "PutnamTable";
String[] colsH = new String[] { "First Name", "Last Name", "SSN", "Distribution Date", "Transaction Code", "Distribution Amount" };
HRAImportDAL iObj = new HRAImportDAL();
cols = getColsIndices(ds, _tableName, colsH);
for (int i = 0; i < ds.Tables[_tableName].Rows.Count; i++)
{
distdt = Convert.ToDateTime(ds.Tables[_tableName].Rows[i][cols[3]]);
transType = ds.Tables[_tableName].Rows[i][cols[4]].ToString();
distamt = Decimal.Parse(ds.Tables[_tableName].Rows[i][cols[5]].ToString(), System.Globalization.NumberStyles.Currency);
ssn = ds.Tables[_tableName].Rows[i][cols[2]].ToString();
if (ssn.Contains("-"))
{
ssn = ssn.Replace("-", "");
}
lname = ds.Tables[_tableName].Rows[i][cols[1]].ToString().Trim(); lname = HRA.GetProperCase(lname);
fname = ds.Tables[_tableName].Rows[i][cols[0]].ToString().Trim(); fname = HRA.GetProperCase(fname);
if (iObj.insertPutnam(_yrmo, distdt, transType, distamt, ssn, lname, fname))
{
_count++;
}
}
iObj.insertImportStatus(_yrmo, "Putnam");
return(_count);
}
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 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 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);
}