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