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 int parseHRAAUDITR(DataSet ds, string _qy)
{
Nullable <DateTime> createdt, statusdt, modifydt, dob;
int empno, age, _uid;
string ssn, name, status, _period;
int _count = 0;
List <int> cols;
string _tableName = "AUDITRTable";
String[] colsH = new String[] { "ssno", "empno", "name", "dob", "age", "retiredt", "modifydt" };
HRAImportDAL iObj = new HRAImportDAL();
cols = getColsIndices(ds, _tableName, colsH);
for (int i = 0; i < ds.Tables[_tableName].Rows.Count; i++)
{
ssn = ds.Tables[_tableName].Rows[i][cols[0]].ToString();
empno = Convert.ToInt32(ds.Tables[_tableName].Rows[i][cols[1]].ToString());
name = ds.Tables[_tableName].Rows[i][cols[2]].ToString().Replace("'", "''");
dob = Convert.ToDateTime(ds.Tables[_tableName].Rows[i][cols[3]]);
age = Convert.ToInt32(ds.Tables[_tableName].Rows[i][cols[4]]);
statusdt = Convert.ToDateTime(ds.Tables[_tableName].Rows[i][cols[5]]);
modifydt = Convert.ToDateTime(ds.Tables[_tableName].Rows[i][cols[6]]);
iObj.insertAUDITR(_qy, ssn, empno, name, dob, age, statusdt, modifydt);
_count++;
}
iObj.insertImportStatus(_qy, "HRAAUDITR");
return(_count);
}
public int ImportPtnmInvoice(string filepath, string _qy)
{
string vendor, period;
int partcount = 0;
decimal amount = 0;
HRA hraobj = new HRA();
HRAImportDAL iobj = new HRAImportDAL();
FileStream fs = File.OpenRead(filepath);
StreamReader d = new StreamReader(fs);
string pattern = @"^(?<Vendor>WageWorks|Mercer)\s+(?<Period>(Q[1234]\s+\d{4})|(\w{3}\-\d{2}))\s+(?<Participants>(((\d{1,3},)+\d{3})|\d+))\s+(?<Amount>\$(((\d{1,3},)+\d{3})|\d+)\.\d{2})$";
Match match1;
string txtline = "";
int records = 0;
try
{
d.BaseStream.Seek(0, SeekOrigin.Begin);
while (d.Peek() > -1)
{
txtline = d.ReadLine();
match1 = Regex.Match(txtline, pattern);
if (match1.Success)
{
records++;
vendor = match1.Groups["Vendor"].Value;
period = match1.Groups["Period"].Value;
if (vendor.Equals("WageWorks"))
{
period = hraobj.GetYRMO(period);
}
partcount = int.Parse(match1.Groups["Participants"].Value, System.Globalization.NumberStyles.AllowThousands);
amount = Decimal.Parse(match1.Groups["Amount"].Value, System.Globalization.NumberStyles.Currency);
iobj.insertPutnamInvoice(_qy, vendor, period, partcount, amount);
}
}
if (records != 4)
{
throw new Exception("Cannot find all required records. Check format of the report");
}
iobj.insertImportStatus(_qy, "ptnm_invoice");
}
finally
{
d.Close();
fs.Close();
}
return(records);
}
public int ImportHRAAUDITR(string _filepath, string _source, string _qy)
{
int _counter = 0;
HRAImportDAL iobj = new HRAImportDAL();
HRAConvertExcel tObj = new HRAConvertExcel();
HRAParseData pObj = new HRAParseData();
DataSet ds = new DataSet(); ds.Clear();
DataTable dt = new DataTable("AUDITRTable");
iobj.Rollback(_source, _qy);
ds.Tables.Add(tObj.ConvertXLS(_filepath, dt));
_counter = pObj.parseHRAAUDITR(ds, _qy);
return(_counter);
}
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 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 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 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);
}
