/// <summary>
/// Read through rows until the specified column changes value.
/// Leaves CurrentValues positioned at the first row with a different value of "columnName".
/// </summary>
/// <param name="columnName"></param>
/// <returns>A DelimitedRow with the sums of the rows read before the new CurrentValues.</returns>
public DelimitedRow ReadUntilChange(string columnName)
{
ColumnTotals = new DelimitedRow(ColumnIndices, CurrentValues);
string initialValue = CurrentValues.GetValue(columnName).ToString();
for (;;)
{
CurrentValues = ReadLine(false);
if (CurrentValues == null)
{
break;
}
string newValue = CurrentValues.GetValue(columnName).ToString();
if (newValue != initialValue)
{
break;
}
for (int index = 0; index < ColumnNames.Count; index++)
{
object currentValue = CurrentValues[index];
object columnTotal = ColumnTotals[index];
ColumnTotals[index] = Add(columnTotal, currentValue);
}
}
return(ColumnTotals);
}
/// <summary>
/// Employee wage record
/// </summary>
/// <param name="writer"></param>
/// <param name="paySummary"></param>
/// <param name="empRow"></param>
private void OutputRWRecord(TextWriter writer, DelimitedRow paySummary, DelimitedRow empRow, Accumulators accum)
{
decimal grossPay = paySummary.GetDecimal("Gross Taxable Income");
accum.EmployeeRecordCount++;
FixedWidthRecord rec = new FixedWidthRecord(mRecordLength);
decimal dummy = 0M;
//MessageBox.Show("Name=" + empSummary.GetString("Name") + " GrossPay=" + grossPay.ToString() +
// " Address=" + empRow.GetString("Address1"));
rec.SetString(1, 2, "RW", "Record Identifier");
rec.SetString(3, 9, paySummary.GetString("SSN").Replace("-", "").PadLeft(9, '0'), "SSN");
rec.SetString(12, 15, empRow.GetString("FName"), "First Name");
rec.SetString(27, 15, empRow.GetString("MInit"), "Middle Initial");
rec.SetString(42, 20, empRow.GetString("LName"), "Last Name");
string address = empRow.GetString("Address1") + " " + empRow.GetString("Address2");
rec.SetString(66, 22, address, "Location Address");
rec.SetString(88, 22, address, "Delivery Address");
rec.SetString(110, 22, empRow.GetString("City"), "City");
rec.SetString(132, 2, empRow.GetString("State"), "State");
string zip = empRow.GetInteger("Zip Code").ToString("00000");
rec.SetString(134, 5, zip, "Zipcode");
rec.SetMoney(188, 11, grossPay, ref accum.FederalWages, "Federal Taxable Wages");
rec.SetMoney(199, 11, paySummary.GetDecimal("Federal Tax"), ref accum.FITW, "FITW");
rec.SetMoney(210, 11, paySummary.GetDecimal("Gross SS Taxable Income"), ref accum.SocialSecurityWages, "Social Security Wages");
rec.SetMoney(221, 11, paySummary.GetDecimal("Social Security Tax"), ref accum.SocialSecurityTaxes, "Social Security Tax");
rec.SetMoney(232, 11, paySummary.GetDecimal("Gross FICA Taxable Income"), ref accum.MedicareWages, "Medicare Wages");
rec.SetMoney(243, 11, paySummary.GetDecimal("Medicare Tax"), ref accum.MedicareTaxes, "Medicare Tax");
rec.SetMoney(254, 11, paySummary.GetDecimal("Tips"), ref accum.SocialSecurityTips, "Social Security Tips");
rec.SetMoney(276, 11, 0M, ref dummy, "Dependent Care Benefits");
rec.SetMoney(287, 11, 0M, ref dummy, "Deferred Comp 401(K)");
rec.SetMoney(298, 11, 0M, ref dummy, "Deferred Comp 403(B)");
rec.SetMoney(309, 11, 0M, ref dummy, "Deferred Comp 408(K)");
rec.SetMoney(320, 11, 0M, ref dummy, "Deferred Comp 457(b)");
rec.SetMoney(331, 11, 0M, ref dummy, "Deferred Comp 501(c)");
rec.SetMoney(353, 11, 0M, ref dummy, "Non-Qualified 457 Dist/Cont");
rec.SetMoney(364, 11, 0M, ref dummy, "Employer FSA");
rec.SetMoney(375, 11, 0M, ref dummy, "Non-qualified NOT 457");
rec.SetMoney(386, 11, 0M, ref dummy, "Non-taxable Combat Pay");
rec.SetMoney(408, 11, 0M, ref dummy, "Employer Group Term Premiums");
rec.SetMoney(419, 11, 0M, ref dummy, "Income from Nonstatutory Stock Options");
rec.SetMoney(430, 11, 0M, ref dummy, "Section 409A Deferrals");
rec.SetMoney(441, 11, 0M, ref dummy, "ROTH Contributions 401(k)");
rec.SetMoney(452, 11, 0M, ref dummy, "ROTH Contributions 403(b)");
rec.SetMoney(463, 11, 0M, ref dummy, "Cost of Employer Health Coverage");
rec.SetMoney(474, 11, 0M, ref dummy, "Small Employer Health Reimbursement Arrangement");
rec.SetString(486, 1, "1", "Statutory Employee");
rec.SetString(488, 1, "0", "Retirement Plan");
rec.SetString(489, 1, "0", "Third Party Sick Pay");
writer.WriteLine(rec.GetContents());
}
private void ReaderHeaderLine()
{
ColumnNames = ReadLine(true);
ColumnIndices = new Dictionary <string, int>();
for (int index = 0; index < ColumnNames.Count; index++)
{
ColumnIndices.Add((string)ColumnNames[index], index);
}
}
public DelimitedReader(TextReader reader, char delimiter, bool allowQuoted)
{
mReader = reader;
mDelimiter = delimiter;
mAllowQuoted = allowQuoted;
ReaderHeaderLine();
// Starts with the first line after the header row loaded into CurrentValues.
CurrentValues = ReadLine(false);
// Undefined until ReadUntilChange() called.
ColumnTotals = null;
}
private void OutputRSRecord(TextWriter writer, DelimitedRow paySummary, DelimitedRow empRow, DelimitedRow supRow,
decimal statewideTransitTax, Accumulators accum)
{
decimal grossPay = paySummary.GetDecimal("Gross Taxable Income");
FixedWidthRecord rec = new FixedWidthRecord(mRecordLength);
rec.SetString(1, 2, "RS", "Record Identifier");
rec.SetString(3, 2, "41", "State Numeric Code");
rec.SetString(10, 9, paySummary.GetString("SSN").Replace("-", "").PadLeft(9, '0'), "SSN");
rec.SetString(19, 15, empRow.GetString("FName"), "First Name");
rec.SetString(34, 15, empRow.GetString("MInit"), "Middle Initial");
rec.SetString(49, 20, empRow.GetString("LName"), "Last Name");
string address = empRow.GetString("Address1") + " " + empRow.GetString("Address2");
rec.SetString(73, 22, address, "Location Address");
rec.SetString(95, 22, address, "Delivery Address");
rec.SetString(117, 22, empRow.GetString("City"), "City");
rec.SetString(139, 2, empRow.GetString("State"), "State");
string zip = empRow.GetInteger("Zip Code").ToString("00000");
rec.SetString(141, 5, zip, "Zipcode");
//rec.SetString(197, 6, "12" + (string)cboTaxYear.SelectedItem, "Reporting Period");
DateTime hireDate = DateTime.Parse(supRow.GetString("HireDate"));
rec.SetString(227, 8, hireDate.ToString("MMddyyyy"), "Hire Date");
string termDate = supRow.GetString("TerminationDate");
if (string.IsNullOrEmpty(termDate))
{
termDate = "00000000";
}
else
{
termDate = DateTime.Parse(termDate).ToString("MMddyyyy");
}
rec.SetString(235, 8, termDate, "Termination Date");
rec.SetString(248, 20, "013375439", "Oregon BIN");
rec.SetString(274, 2, "41", "State Numeric Code");
rec.SetMoney(276, 11, grossPay, ref accum.StateWages, "State Taxable Wages");
rec.SetMoney(287, 11, paySummary.GetDecimal("State Tax"), ref accum.SITW, "SITW");
rec.SetMoney(348, 11, grossPay, ref accum.StatewideTransitWages, "Statewide Transit Wages");
rec.SetMoney(359, 11, statewideTransitTax, ref accum.StatewideTransitTax, "Statewide Transit Tax");
writer.WriteLine(rec.GetContents());
}
private DelimitedRow ReadLine(bool forceStrings)
{
DelimitedRow newValues = new DelimitedRow(ColumnIndices);
string line = mReader.ReadLine();
if (string.IsNullOrEmpty(line))
{
return(null);
}
for (int nextIndex = 0; ;)
{
if (nextIndex > line.Length)
{
break;
}
if (nextIndex == line.Length)
{
newValues.Add(MakeValue("", forceStrings));
break;
}
char firstChar = line[nextIndex];
if (firstChar == '"' && mAllowQuoted)
{
nextIndex++;
int secondQuoteIndex = line.IndexOf('"', nextIndex);
if (secondQuoteIndex <= 0)
{
newValues.Add(MakeValue(line.Substring(nextIndex), forceStrings));
break;
}
newValues.Add(MakeValue(line.Substring(nextIndex, secondQuoteIndex - nextIndex), forceStrings));
nextIndex = secondQuoteIndex + 2;
}
else
{
int nextCommaIndex = line.IndexOf(mDelimiter, nextIndex);
if (nextCommaIndex < 0)
{
newValues.Add(MakeValue(line.Substring(nextIndex), forceStrings));
break;
}
newValues.Add(MakeValue(line.Substring(nextIndex, nextCommaIndex - nextIndex), forceStrings));
nextIndex = nextCommaIndex + 1;
}
}
return(newValues);
}
private Dictionary <string, DelimitedRow> LoadEmployeeDictionary(string fileName, Func <DelimitedRow, string> getKey)
{
Dictionary <string, DelimitedRow> result = new Dictionary <string, DelimitedRow>();
using (TextReader empText = new StreamReader(fileName))
{
DelimitedReader empParser = new DelimitedReader(empText, ',', true);
for (;;)
{
DelimitedRow empRow = empParser.CurrentValues;
if (empRow == null)
{
break;
}
if (empRow.Count > 0)
{
result.Add(getKey(empRow), empRow);
}
empParser.NextLine();
}
}
return(result);
}
private void ReadTestFile1()
{
try
{
mFileName = "TestFile1.txt";
using (TextReader reader = new StreamReader(mFileName))
{
DelimitedReader delReader = new DelimitedReader(reader, ',', true);
DelimitedRow row = delReader.CurrentValues;
DelimitedRow totals = delReader.ColumnTotals;
if (row == null)
{
Failure("Initial row is null");
}
if (row.Count != 6)
{
Failure("Initial row column wrong count");
}
if (totals != null)
{
Failure("Initial totals must be null");
}
if (!delReader.HasCurrent)
{
Failure("Initial has no current");
}
if (row.GetIndex("Name") != 0)
{
Failure("Name index != 0");
}
if (row.GetIndex("Weight") != 2)
{
Failure("Weight index != 2");
}
if ((string)row.GetValue("Name") != "Dog")
{
Failure("Name!=Dog");
}
if (!(row.GetValue("Weight") is decimal))
{
Failure("Dog weight is not decimal");
}
if ((decimal)row.GetValue("Weight") != 20.5M)
{
Failure("Dog weight is not 20.5M");
}
if (row.GetDecimal("Weight") != 20.5M)
{
Failure("Dog weight is not 20.5M (2)");
}
if (!(row.GetValue("Length") is int))
{
Failure("Length is not integer");
}
if (row.GetInteger("Length") != 26)
{
Failure("Dog Length is not 26");
}
row = delReader.NextLine();
if (!delReader.HasCurrent)
{
Failure("Second has no current");
}
if (row.Count != 6)
{
Failure("Second row column count wrong");
}
if (row.GetString("Name") != "Big Fish")
{
Failure("Second name is not Fish");
}
if (row.GetString("Color") != "Silver")
{
Failure("Second color is not Silver");
}
if (row.GetDecimal("Weight") != 3.5M)
{
Failure("Second Weight should be 3.5M");
}
if (row.GetInteger("Units") != 200)
{
Failure("Second units is not 200");
}
totals = delReader.ReadUntilChange("Break");
row = delReader.CurrentValues;
if (row.GetString("Name") != "Small Fish")
{
Failure("UntilChange Break wrong row");
}
if (!(totals.GetValue("Name") is string))
{
Failure("UntilChange Name should be string");
}
if (!(totals.GetValue("Weight") is decimal))
{
Failure("UntilChange Weight should be decimal");
}
if (totals.GetDecimal("Weight") != 3.5M)
{
Failure("UntilChange Weight should be 3.5M");
}
totals = delReader.ReadUntilChange("Break");
row = delReader.CurrentValues;
if (row.GetString("Name") != "guppy")
{
Failure("UntilChange2 Break wrong row");
}
if (totals.GetDecimal("Weight") != ((decimal)1 + 8.5M + 300.5M))
{
Failure("UntilChange2 wrong Weight total");
}
if (totals.GetInteger("Units") != (3000 + 5 + 1))
{
Failure("UntilChange2 wrong Units total");
}
if (!(row.GetValue("Units") is int))
{
Failure("UntilChange2 wrong Units type");
}
row = delReader.NextLine();
if (row.GetString("Name") != "")
{
Failure("Blank Name is not blank");
}
if (row.GetString("Color") != "none")
{
Failure("Blank Color is not none");
}
if (row.GetString("Weight") != "")
{
Failure("Blank Weight is not blank");
}
if (row.GetString("Length") != "")
{
Failure("Blank Length is not blank");
}
if (row.GetInteger("Units") != 100)
{
Failure("Blank Units is not 100");
}
row = delReader.NextLine();
if (row.GetString("Name") != "tall grass")
{
Failure("tall grass wrong name");
}
if (!delReader.HasCurrent)
{
Failure("tall grass has no current");
}
delReader.NextLine();
if (delReader.HasCurrent)
{
Failure("should not have current");
}
if (!(delReader.Add("a", "B") is string))
{
Failure("Add string string is not string");
}
if (!(delReader.Add("a", 1) is string))
{
Failure("Add string int is not string");
}
if (!(delReader.Add(2, "B") is string))
{
Failure("Add int string is not string");
}
if (!(delReader.Add(2.5M, "B") is string))
{
Failure("Add decimal string is not string");
}
if (!(delReader.Add(2, 1) is int))
{
Failure("Add int int is not int");
}
if (!(delReader.Add(2.5M, 3.5M) is decimal))
{
Failure("Add decimal decimal is not decimal");
}
if (!(delReader.Add(2.5M, 3) is decimal))
{
Failure("Add decimal int is not decimal");
}
if (!(delReader.Add(2, 3.5M) is decimal))
{
Failure("Add int decimal is not decimal");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnCreateW2_Click(object sender, EventArgs e)
{
if (cboTaxYear.SelectedItem == null)
{
MessageBox.Show("Please select a tax year");
return;
}
Accumulators accum = new Accumulators();
Dictionary <string, DelimitedRow> employees = LoadEmployeeDictionary(txtEmployeeFile.Text,
empRow => empRow.GetString("SSN"));
Dictionary <string, DelimitedRow> supplemental = LoadEmployeeDictionary(txtEmployeeSupplemental.Text,
empRow => empRow.GetString("SSN"));
Dictionary <string, DelimitedRow> statewideQ1 = LoadEmployeeDictionary(txtStatewideTransitQ1.Text,
empRow => empRow.GetInteger("SSN").ToString("000-00-0000"));
Dictionary <string, DelimitedRow> statewideQ2 = LoadEmployeeDictionary(txtStatewideTransitQ2.Text,
empRow => empRow.GetInteger("SSN").ToString("000-00-0000"));
Dictionary <string, DelimitedRow> statewideQ3 = LoadEmployeeDictionary(txtStatewideTransitQ3.Text,
empRow => empRow.GetInteger("SSN").ToString("000-00-0000"));
Dictionary <string, DelimitedRow> statewideQ4 = LoadEmployeeDictionary(txtStatewideTransitQ4.Text,
empRow => empRow.GetInteger("SSN").ToString("000-00-0000"));
int paycheckCount = 0;
using (TextWriter writer = new StreamWriter("W2Output.txt"))
{
OutputRARecord(writer);
OutputRERecord(writer);
using (TextReader payText = new StreamReader(txtPayrollFile.Text))
{
DelimitedReader payParser = new DelimitedReader(payText, ',', true);
for (;;)
{
if (!payParser.HasCurrent)
{
break;
}
DelimitedRow paySummary = payParser.ReadUntilChange("SSN");
string ssn = paySummary.GetString("SSN");
if (!employees.TryGetValue(ssn, out DelimitedRow empRow))
{
MessageBox.Show("Could not find SSN in employee file: " + ssn);
return;
}
if (!supplemental.TryGetValue(ssn, out DelimitedRow supRow))
{
MessageBox.Show("Could not find SSN in supplemental file: " + ssn);
return;
}
OutputRWRecord(writer, paySummary, empRow, accum);
decimal statewideTransitTax =
GetStatewideTaxAmount(ssn, statewideQ1) +
GetStatewideTaxAmount(ssn, statewideQ2) +
GetStatewideTaxAmount(ssn, statewideQ3) +
GetStatewideTaxAmount(ssn, statewideQ4);
OutputRSRecord(writer, paySummary, empRow, supRow, statewideTransitTax, accum);
paycheckCount++;
}
}
OutputRTRecord(writer, accum);
// No "RU" record, because no "RO" records.
OutputRVRecord(writer, accum);
OutputRFRecord(writer, accum);
}
MessageBox.Show("Wrote W2Output.txt to working directory with " + paycheckCount +
" employees (if number is too large, maybe input file was not sorted by SSN).");
MessageBox.Show(
"Federal wages: " + accum.FederalWages.ToString("F2") + Environment.NewLine +
"FITW: " + accum.FITW.ToString("F2") + Environment.NewLine +
"State wages: " + accum.StateWages.ToString("F2") + Environment.NewLine +
"SITW: " + accum.SITW.ToString("F2") + Environment.NewLine +
"Transit wages: " + accum.StatewideTransitWages.ToString("F2") + Environment.NewLine +
"Transit taxes: " + accum.StatewideTransitTax.ToString("F2") + Environment.NewLine +
"W-2 count: " + accum.EmployeeRecordCount.ToString());
}
private void ReadTestFile2()
{
try
{
mFileName = "TestFile2.txt";
using (TextReader reader = new StreamReader(mFileName))
{
DelimitedReader delReader = new DelimitedReader(reader, '\t', false);
DelimitedRow row = delReader.CurrentValues;
DelimitedRow totals = delReader.ColumnTotals;
if (row == null)
{
Failure("Initial row is null");
}
if (totals != null)
{
Failure("Initial totals must be null");
}
if (!delReader.HasCurrent)
{
Failure("Initial has no current");
}
if (row.GetString("Color") != "Red")
{
Failure("Initial color must be Red");
}
if (row.GetString("Group") != "Reds")
{
Failure("Initial group must be Reds");
}
if (row.GetString("Depth") != "\"Deep\"")
{
Failure("Initial Depth is not Deep");
}
if (row.GetString("Description") != "\"Very deep red\"")
{
Failure("Initial description wrong");
}
if (!(row.GetValue("Level") is int))
{
Failure("Initial Level must be int");
}
if (row.GetInteger("Level") != 100)
{
Failure("Initial Level must be 100");
}
totals = delReader.ReadUntilChange("Group");
row = delReader.CurrentValues;
if (row != null)
{
Failure("UntilChanges did not read through end");
}
if (totals.GetInteger("Level") != 150)
{
Failure("UntilChanges Level was not 150");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public DelimitedRow NextLine()
{
CurrentValues = ReadLine(false);
return(CurrentValues);
}
