private void GetITRInformation()
{
Range currentCell = Globals.ThisAddIn.Application.ActiveCell;
int firstRow = currentCell.Row;
//Dim lastRow As Integer = currentCell.SpecialCells(Constants.xlLastCell).Row
int lastRow = ExcelHlp.FindLast_PopulatedRow_InColumn(currentCell);
//Dim foundRows As Hashtable = New Hashtable()
Worksheet activeSheet = Globals.ThisAddIn.Application.ActiveSheet;
Range itrRow = default(Range);
//Debug.Print(String.Format("{0} - {1}", firstRow, lastRow))
//Dim ta As ApplicationDSTableAdapters.sp_ITRDetailTableAdapter = New ApplicationDSTableAdapters.sp_ITRDetailTableAdapter()
Data.ApplicationDS.sp_ITRDetailRow itrDataRow = null;
//Dim itrDataTable As ApplicationDS.sp_ITRDetailDataTable
//Dim tr As ApplicationDS.sp_ITRDetailRow
for (int i = firstRow; i <= lastRow; i++)
{
itrRow = activeSheet.Cells[i, currentCell.Column];
Common.ApplicationDS.sp_ITRDetail.ImportRow(itrDataRow);
itrRow.Cells[1, 2].Value = itrDataRow.Subject;
itrRow.Cells[1, 3].Value = itrDataRow.Application.Replace("/Life IT/Technical Services/", "");
try
{
itrRow.Cells[1, 4].Value = itrDataRow.Description.Length;
// Current Condition
}
catch (Exception)
{
itrRow.Cells[1, 4].Value = "<none>";
}
try
{
itrRow.Cells[1, 5].Value = itrDataRow.Justification.Length;
// Desired Outcome
}
catch (Exception)
{
itrRow.Cells[1, 5].Value = "<none>";
}
try
{
itrRow.Cells[1, 6].Value = itrDataRow.Comments.Length;
// Comments
}
catch (Exception)
{
itrRow.Cells[1, 6].Value = "<none>";
}
}
}
private void LoadInfoFromWorksheet_FileControl()
{
CountFC_ClaimRecord = ExcelHlp.FindLast_PopulatedRow_InColumn(_wsFileControl.Range["A1"]) - 1;
// Need populate these as sum of values.
Range sumRange;
//string sumRangeString;
//sumRangeString = string.Format("D2:D{0}", CountFC_ClaimRecord + 1);
sumRange = _wsFileControl.Range[string.Format("D2:D{0}", CountFC_ClaimRecord + 1)];
CountFC_ClaimPayment = (int)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
//sumRangeString = string.Format("E2:E{0}", CountFC_ClaimRecord + 1);
//sumRange = _wsFileControl.Range[sumRangeString];
sumRange = _wsFileControl.Range[string.Format("E2:E{0}", CountFC_ClaimRecord + 1)];
CountFC_XOBUDSCode = (int)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("F2:F{0}", CountFC_ClaimRecord + 1)];
ClaimPaymentTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("G2:G{0}", CountFC_ClaimRecord + 1)];
LoanPaymentTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("H2:H{0}", CountFC_ClaimRecord + 1)];
InterestPaymentTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("I2:I{0}", CountFC_ClaimRecord + 1)];
CareGiverTrainingTotal = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("J2:J{0}", CountFC_ClaimRecord + 1)];
TransitionPaymentTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("K2:K{0}", CountFC_ClaimRecord + 1)];
FederalWithholdingTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("L2:L{0}", CountFC_ClaimRecord + 1)];
StateWitholdingTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("M2:M{0}", CountFC_ClaimRecord + 1)];
CityWithholdingTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("N2:N{0}", CountFC_ClaimRecord + 1)];
CheckAmountTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("O2:O{0}", CountFC_ClaimRecord + 1)];
XOBUDSCodeAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
}
private void ProcessFundServiceFees(Worksheet ws)
{
Common.WriteToDebugWindow(String.Format("ProcessFundServiceFees()"));
// Delete the top two rows
((Range)ws.Rows[1]).Delete();
((Range)ws.Rows[1]).Delete();
// Remove the commas
((Range)ws.Columns["E:E"]).NumberFormat = "0.00";
// Determine the number of rows
RecordCount = ExcelHlp.FindLast_PopulatedRow_InColumn(ws.Range["A1"]);
// Replace the Fund Codes
UpdateFundCodes(ws);
// Add a new column
ws.Columns["A:A"].Insert(Shift: XlDirection.xlToRight, CopyOrigin: XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
// And populate with As of Date
DateTime asOfDate = DateTime.Parse(dtpAsOfDate.Text);
ws.Range[ws.Cells[1, 1], ws.Cells[RecordCount, 1]].Value = asOfDate.ToShortDateString();
// Gather some information for the control file
Range sumRange = ws.Range[string.Format("F1:F{0}", RecordCount)];
TotalAmount = (double)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
CleanOutExtraDataOnWorksheet(ws);
Common.WriteToDebugWindow(string.Format("CycleDate:{0},CurrentDate:{1},RecordCount={2},TotalAmount={3}",
CycleDate, CurrentDate, RecordCount, TotalAmount));
}
private void GetRowColInfo()
{
Range rng = Globals.ThisAddIn.Application.ActiveCell;
txtLastRowSpecial.Text = rng.SpecialCells(XlCellType.xlCellTypeLastCell).Row.ToString();
txtLastColSpecial.Text = rng.SpecialCells(XlCellType.xlCellTypeLastCell).Column.ToString();
// TODO (crhodes): Make FindXXXPopulated{Row,Col} take an enum: First, Next,Previous, Last
txtFirstRowSearch.Text = ExcelHlp.FindFirst_PopulatedRow_InColumn(rng).ToString();
txtFirstColSearch.Text = ExcelHlp.FindFirst_PopulatedColumn_InRow(rng).ToString();
txtPreviousRowSearch.Text = ExcelHlp.FindPrevious_PopulatedRow_InColumn(rng).ToString();
txtPreviousColSearch.Text = ExcelHlp.FindPrevious_PopulatedColumn_InRow(rng).ToString();
txtNextRowSearch.Text = ExcelHlp.FindNext_PopulatedRow_InColumn(rng).ToString();
txtNextColSearch.Text = ExcelHlp.FindNext_PopulatedColumn_InRow(rng).ToString();
txtLastRowSearch.Text = ExcelHlp.FindLast_PopulatedRow_InColumn(rng).ToString();
txtLastColSearch.Text = ExcelHlp.FindLast_PopulatedColumn_InRow(rng).ToString();
}
private void LoadInfoFromWorksheet_XOBInfo()
{
CountWS_XOBUDSCode = ExcelHlp.FindLast_PopulatedRow_InColumn(_wsXOBInfo.Range["A1"]) - 1;
}
private void LoadInfoFromWorksheet_Payments()
{
CountWS_ClaimPayment = ExcelHlp.FindLast_PopulatedRow_InColumn(_wsPayments.Range["A1"]) - 1;
}
private void LoadInfoFromWorksheet_Claims()
{
CountWS_ClaimRecord = ExcelHlp.FindLast_PopulatedRow_InColumn(_wsClaims.Range["A1"]) - 1;
}
private void MergeDuplicateRows()
{
Range currentCell = Globals.ThisAddIn.Application.ActiveCell;
int firstRow = currentCell.Row;
//Dim lastRow As Integer = currentCell.SpecialCells(Constants.xlLastCell).Row
int lastRow = ExcelHlp.FindLast_PopulatedRow_InColumn(currentCell);
Dictionary <string, Range> foundITRs = new Dictionary <string, Range>();
Dictionary <string, string> duplicateITRs = new Dictionary <string, string>();
Worksheet activeSheet = Globals.ThisAddIn.Application.ActiveSheet;
List <string> rowsToDelete = new List <string>();
Range rng = default(Range);
Debug.Print(string.Format("{0} - {1}", firstRow, lastRow));
for (int i = lastRow; i >= firstRow; i += -1)
{
rng = activeSheet.Cells[i, currentCell.Column];
//Debug.Print(string.Format("{0} - {1} - {2}", i, rng.Value, rng.Cells[1, 7].Value));
if (foundITRs.ContainsKey(rng.Value))
{
if (duplicateITRs.ContainsKey(rng.Value))
{
// This is not the first duplicate. Add resource if not there already.
if (!duplicateITRs[rng.Value].Contains(rng.Cells[1, 7].Value))
{
duplicateITRs[rng.Value] = duplicateITRs[rng.Value] + ", " + rng.Cells[1, 7].Value;
}
}
else
{
// Add the Resource from the duplicate row
duplicateITRs[rng.Value] = rng.Cells[1, 7].Value;
}
rowsToDelete.Add(string.Format("{0}:{1}", i, i));
}
else
{
foundITRs[rng.Value] = rng;
}
}
//foreach (KeyValuePair<string, Range> row in foundITRs)
//{
// //Debug.Print(string.Format("{0} - {1} - {2}", row.Value.Row, row.Value.Cells[1, 1].Value, row.Value.Cells[1, 7].Value));
//}
foreach (KeyValuePair <string, string> row in duplicateITRs)
{
//Debug.Print(string.Format("{0} - {1}", row.Key, row.Value));
string resources = activeSheet.Cells[foundITRs[row.Key].Row, 7].Value;
if (row.Value.Contains(resources))
{
activeSheet.Cells[foundITRs[row.Key].Row, 7].Value = row.Value;
}
else
{
activeSheet.Cells[foundITRs[row.Key].Row, 7].Value = resources + ", " + row.Value;
}
}
foreach (string row in rowsToDelete)
{
//Debug.Print(row);
activeSheet.Rows[row].Delete();
}
}
