/// <summary>
///
/// </summary>
/// <param name="xlWorksheetMLS"></param>
/// <param name="xlWorksheetAIM"></param>
/// <param name="xlRangeMLS"></param>
/// <param name="xlRangeAIM"></param>
/// <param name="rangeCount"></param>
/// <param name="relevantCols"></param>
/// <param name="thresholds"></param>
/// <param name="progress"></param>
public void determinerDoWork(Excel._Worksheet xlWorksheetMLS, Excel._Worksheet xlWorksheetAIM,
Excel.Range xlRangeMLS, Excel.Range xlRangeAIM, Dictionary <string, int> rangeCount,
Dictionary <string, int> relevantCols, Dictionary <string, double> thresholds, IProgress <int> progress,
mainForm form)
{
// loop through the files and do the main work
for (int currentMLSFile = 2; currentMLSFile <= rangeCount["rowCountMLS"]; currentMLSFile++)
{
// initialize the variables that will determine if file in row closed
//
// For addressMatch and ownerMatch: 0 = no match,
// 1 = likely a match, 2 = definitely a match
bool dateClosedMatch = false;
int addressMatch = 0;
int ownerMatch = 0;
int closedGFNumRow = 2;
List <int> consideredRowsAIM = new List <int>();
/// determine if date closed is a match
if (xlRangeMLS.Cells[currentMLSFile, relevantCols["MLSCloseDateCol"]].Value2 != null) // check that the next MLS close date cell is not empty
{
// parse MLS close date into a DateTime struct
string MLSRawCloseDate = xlRangeMLS.Cells[currentMLSFile, relevantCols["MLSCloseDateCol"]].Value.ToString();
DateTime MLSCloseDate = DateTime.Parse(MLSRawCloseDate);
for (int currentAIMFile = 2; currentAIMFile <= rangeCount["rowCountAIM"]; currentAIMFile++)
{
if (xlRangeAIM.Cells[currentAIMFile, relevantCols["AIMCloseDateCol"]].Value2 != null) // check that the next AIM close date cell is not empty
{
// parse AIM close date into a DateTime struct
string AIMRawCloseDate = xlRangeAIM.Cells[currentAIMFile, relevantCols["AIMCloseDateCol"]].Value.ToString();
DateTime AIMCloseDate = DateTime.Parse(AIMRawCloseDate);
if ((MLSCloseDate - AIMCloseDate).TotalDays <= 15) // check that the two close dates are within 15 days of each other
{
consideredRowsAIM.Add(currentAIMFile); // add the current AIM file row to the list of considered AIM rows
dateClosedMatch = true;
//Console.WriteLine("Found match in days between row " + currentMLSFile
// + " in MLS xl file and row " + currentAIMFile + " in AIM xl file");
}
}
}
}
/// determine if owner/seller name are a match only if date closed and addrees are already a match
if (dateClosedMatch && xlRangeMLS.Cells[currentMLSFile, relevantCols["MLSOwnerCol"]].Value2 != null)
{
for (int currentAIMFile = 2; currentAIMFile <= rangeCount["rowCountAIM"]; currentAIMFile++)
{
if (xlRangeAIM.Cells[currentAIMFile, relevantCols["AIMSellerCol"]].Value2 != null &&
consideredRowsAIM.Contains(currentAIMFile))
{
string owner = xlRangeMLS.Cells[currentMLSFile, relevantCols["MLSOwnerCol"]].Value2.ToString();
string seller = xlRangeAIM.Cells[currentAIMFile, relevantCols["AIMSellerCol"]].Value2.ToString();
string[] parsedOwner = owner.ToLower().Split(' ');
string[] parsedSeller = seller.ToLower().Split(' ');
// Check that the first 3 (if applicable) words
// in the owner string reasonably match with a
// word in the seller string
bool fnLnMatch = false;
if (parsedOwner.Length == 1)
{
for (int i = 0; i < parsedSeller.Length; i++)
{
if (StringDistance.GetStringDistance(parsedOwner[0], parsedSeller[i]) <= 1)
{
fnLnMatch = true;
break;
}
}
}
else if (parsedOwner.Length == 2)
{
int numMatches = 0; // number of owner words that matched with seller words
for (int i = 0; i < parsedSeller.Length; i++)
{
if (StringDistance.GetStringDistance(parsedOwner[0], parsedSeller[i]) <= 1)
{
numMatches++;
}
}
for (int i = 0; i < parsedSeller.Length; i++)
{
if (StringDistance.GetStringDistance(parsedOwner[1], parsedSeller[i]) <= 1)
{
numMatches++;
}
}
if (numMatches >= 1)
{
fnLnMatch = true;
}
}
else if (parsedOwner.Length == 3)
{
int numMatches = 0; // number of owner words that matched with seller words
for (int i = 0; i < parsedSeller.Length; i++)
{
if (StringDistance.GetStringDistance(parsedOwner[0], parsedSeller[i]) <= 1)
{
numMatches++;
break;
}
}
for (int i = 0; i < parsedSeller.Length; i++)
{
if (StringDistance.GetStringDistance(parsedOwner[1], parsedSeller[i]) <= 1)
{
numMatches++;
break;
}
}
for (int i = 0; i < parsedSeller.Length; i++)
{
if (StringDistance.GetStringDistance(parsedOwner[2], parsedSeller[i]) <= 1)
{
numMatches++;
break;
}
}
if (numMatches >= 2)
{
fnLnMatch = true;
}
}
else
{
int numMatches = 0; // number of owner words that matches with seller words
for (int i = 0, j = 1; i < parsedOwner.Length && j <= 3; i++)
{
if (!((parsedOwner[i].Length == 1) || (parsedOwner[i].Length == 2) ||
(parsedOwner[i].ToLower() == "and"))) // if not a word that should be ignored
{ // words that should be ignored: "and" and words with length 1 or 2
// go through parsedSeller and check that the current parsedOwner word is contained
for (int k = 0; k < parsedSeller.Length; k++)
{
if (StringDistance.GetStringDistance(parsedOwner[i], parsedSeller[k]) <= 1)
{
numMatches++;
break; // break out of inner for loop
}
}
j++;
}
}
if (numMatches >= 2)
{
fnLnMatch = true;
}
}
// check ownerDistance against the percentage threshold of the longer test string to see if it is a match
if (fnLnMatch)
{
ownerMatch = 2;
break;
}
else
{
ownerMatch = 1;
consideredRowsAIM.Remove(currentAIMFile);
}
}
else
{
consideredRowsAIM.Remove(currentAIMFile);
}
}
}
/// determine if property addresses are a match only if date closed is already a match
if (dateClosedMatch && ownerMatch > 0 && xlRangeMLS.Cells[currentMLSFile, relevantCols["MLSAddressCol"]].Value2 != null)
{
for (int currentAIMFile = 2; currentAIMFile <= rangeCount["rowCountAIM"]; currentAIMFile++)
{
if (xlRangeAIM.Cells[currentAIMFile, relevantCols["AIMAddressCol"]].Value2 != null &&
consideredRowsAIM.Contains(currentAIMFile))
{
// get the address strings from the xl files and parse them by the space character
string addressMLS = xlRangeMLS.Cells[currentMLSFile, relevantCols["MLSAddressCol"]].Value2.ToString();
string addressAIM = xlRangeAIM.Cells[currentAIMFile, relevantCols["AIMAddressCol"]].Value2.ToString();
string[] parsedAddressMLS = addressMLS.Split(' ');
string[] parsedAddressAIM = addressAIM.Split(' ');
if (parsedAddressMLS[0] == parsedAddressAIM[0]) // check that the address numbers match
{
// account for condominium, private road, and country road
if (addressMLS.ToLower().Contains("condominium"))
{
addressMLS = addressMLS.ToLower().Replace("condominium", "");
addressAIM = addressAIM.ToLower();
}
else if (addressAIM.ToLower().Contains("condominium"))
{
addressMLS = addressMLS.ToLower();
addressAIM = addressAIM.ToLower().Replace("condominium", "");
}
if (addressMLS.ToLower().Contains("pr") && addressAIM.ToLower().Contains("private road"))
{
addressMLS = addressMLS.ToLower().Replace("pr", "");
addressAIM = addressAIM.ToLower().Replace("private road", "");
}
else if (addressMLS.ToLower().Contains("private road") && addressAIM.ToLower().Contains("pr"))
{
addressMLS = addressMLS.ToLower().Replace("private road", "");
addressAIM = addressAIM.ToLower().Replace("pr", "");
}
if (addressMLS.ToLower().Contains("cr") && addressAIM.ToLower().Contains("county road"))
{
addressMLS = addressMLS.ToLower().Replace("cr", "");
addressAIM = addressAIM.ToLower().Replace("county road", "");
}
else if (addressMLS.ToLower().Contains("county road") && addressAIM.ToLower().Contains("cr"))
{
addressMLS = addressMLS.ToLower().Replace("county road", "");
addressAIM = addressAIM.ToLower().Replace("cr", "");
}
int addressDistance = StringDistance.GetStringDistance(addressMLS, addressAIM); // get distance between the two strings
// compute updated thresholds
int addressThresholdUpdated = (int)Math.Ceiling(thresholds["addressThreshold"] * Math.Max(addressMLS.Length, addressAIM.Length));
int addressThresholdWeakUpdated = (int)Math.Ceiling(thresholds["addressThresholdWeak"] * Math.Max(addressMLS.Length, addressAIM.Length));
// check addressDistance against the percentage threshold of the longer test string to see if it is a match
if (addressDistance <= addressThresholdUpdated)
{
addressMatch = 2;
closedGFNumRow = currentAIMFile;
Console.WriteLine("Found match in address between row " + currentMLSFile
+ " in MLS xl file and row " + currentAIMFile + " in AIM xl file");
break; // if a match is found, there's no need to search any further
}
else if (addressDistance <= addressThresholdWeakUpdated && addressDistance > addressThresholdUpdated)
{
addressMatch = 1;
closedGFNumRow = currentAIMFile;
Console.WriteLine("Found likely match in address between row " + currentMLSFile
+ " in MLS xl file and row " + currentAIMFile + " in AIM xl file");
}
}
}
}
}
/// determine whether the file was closed with hatco or not and print to xl file
if (dateClosedMatch && addressMatch == 2 && ownerMatch == 2)
{
string closedGF = xlRangeAIM.Cells[closedGFNumRow, relevantCols["AIMFileNoCol"]].Value.ToString();
string escrOff = xlRangeAIM.Cells[closedGFNumRow, relevantCols["AIMEscrowCol"]].Value.ToString();
xlRangeMLS.Cells[currentMLSFile, relevantCols["MLSGFCol"]].Value = closedGF;
xlRangeMLS.Cells[currentMLSFile, relevantCols["MLSEscrowOfficerCol"]].Value = escrOff;
Console.WriteLine("File on row " + currentMLSFile + " of MLS xl file closed with GF #"
+ closedGF);
}
else if (dateClosedMatch && addressMatch > 0 && ownerMatch > 0)
{
string closedGF = xlRangeAIM.Cells[closedGFNumRow, relevantCols["AIMFileNoCol"]].Value.ToString();
string escrOff = xlRangeAIM.Cells[closedGFNumRow, relevantCols["AIMEscrowCol"]].Value.ToString();
xlRangeMLS.Cells[currentMLSFile, relevantCols["MLSGFCol"]].Value = closedGF;
xlRangeMLS.Cells[currentMLSFile, relevantCols["MLSLikelyCloseCol"]].Value = "true";
xlRangeMLS.Cells[currentMLSFile, relevantCols["MLSEscrowOfficerCol"]].Value = escrOff;
Console.WriteLine("File on row " + currentMLSFile + " likely closed with GF #" + closedGF);
}
else
{
xlRangeMLS.Cells[currentMLSFile, relevantCols["MLSGFCol"]].Value = "did not close";
Console.WriteLine("File on row " + currentMLSFile + " did not close");
}
// update progress bar after each row of MLS file
if (progress != null)
{
progress.Report(100 / rangeCount["rowCountMLS"]);
}
string progressDetailedUpdate = (currentMLSFile - 1).ToString() + "/" + (rangeCount["rowCountMLS"] - 1).ToString();
MethodInvoker inv = delegate
{
form.progressDetailed.Text = progressDetailedUpdate;
};
form.Invoke(inv);
}
}
/// <summary>
/// run through each file in MLSFileName and check against AIMFileName files
/// to determine whether the files in MLSFileName closed with hatco.
///
/// addressThreshold, addressThresholdWeak,
/// ownerThreshold, and ownerThresholdWeak are percentages (between 0 and 1)
///
/// progress is used to update the windowsform's progress bar
/// </summary>
/// <param name="MLSFileName"></param>
/// <param name="AIMFileName"></param>
/// <param name="addressThreshold"></param>
/// <param name="ownerThreshold"></param>
public void mainDeterminer(string MLSFileName, string AIMFileName, double addressThreshold,
double addressThresholdWeak, double ownerThreshold, double ownerThresholdWeak, IProgress <int> progress,
mainForm form)
{
Application.UseWaitCursor = true; // set the cursor to waiting symbol
// open all excel files for use
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbookMLS = null;
Excel.Workbook xlWorkbookAIM = null;
try
{
xlWorkbookMLS = xlApp.Workbooks.Open(MLSFileName);
xlWorkbookAIM = xlApp.Workbooks.Open(AIMFileName);
}
catch (Exception ex) // catch possible "file could not open" exception
{
throw ex;
}
if (xlWorkbookAIM != null && xlWorkbookMLS != null) // check that excel files opened properly
{
// open worksheets and range in excel files for use
Excel._Worksheet xlWorksheetMLS = xlWorkbookMLS.Sheets[1];
Excel._Worksheet xlWorksheetAIM = xlWorkbookAIM.Sheets[1];
Excel.Range xlRangeMLS = xlWorksheetMLS.UsedRange;
Excel.Range xlRangeAIM = xlWorksheetAIM.UsedRange;
Dictionary <string, int> rangeCount = new Dictionary <string, int>();
Dictionary <string, int> relevantCols = new Dictionary <string, int>();
Dictionary <string, double> thresholds = new Dictionary <string, double>();
thresholds.Add("addressThreshold", addressThreshold);
thresholds.Add("addressThresholdWeak", addressThresholdWeak);
thresholds.Add("ownerThreshold", ownerThreshold);
thresholds.Add("ownerThresholdWeak", ownerThresholdWeak);
try
{
// do the main processing on the excel files and catch any exceptions that are thrown
// get the range of rows and columns for AIM excel file
rangeCount.Add("rowCountMLS", xlRangeMLS.Rows.Count);
rangeCount.Add("colCountMLS", xlRangeMLS.Columns.Count);
rangeCount.Add("rowCountAIM", xlRangeAIM.Rows.Count);
rangeCount.Add("colCountAIM", xlRangeAIM.Columns.Count);
// relevant columns indeces
relevantCols.Add("MLSOwnerCol", 0);
relevantCols.Add("MLSAddressCol", 0);
relevantCols.Add("MLSCloseDateCol", 0);
relevantCols.Add("MLSGFCol", 0);
relevantCols.Add("AIMFileNoCol", 0);
relevantCols.Add("AIMCloseDateCol", 0);
relevantCols.Add("AIMAddressCol", 0);
relevantCols.Add("AIMSellerCol", 0);
relevantCols.Add("AIMEscrowCol", 0);
// determine the columns in MLS file that have relevant information
for (int i = 1; i <= rangeCount["colCountMLS"]; i++)
{
if (xlRangeMLS.Cells[1, i].Value2 != null) // check that the cell is not empty
{
if (xlRangeMLS.Cells[1, i].Value2.ToString().Contains("Owner"))
{
relevantCols["MLSOwnerCol"] = i;
}
else if (xlRangeMLS.Cells[1, i].Value2.ToString().Contains("Address"))
{
relevantCols["MLSAddressCol"] = i;
}
else if (xlRangeMLS.Cells[1, i].Value2.ToString().Contains("Close Date"))
{
relevantCols["MLSCloseDateCol"] = i;
}
else if (xlRangeMLS.Cells[1, i].Value2.ToString().Contains("GF"))
{
relevantCols["MLSGFCol"] = i;
}
}
}
// determine the columns in AIM file that have relevant information
for (int i = 1; i <= rangeCount["colCountAIM"]; i++)
{
if (xlRangeAIM.Cells[1, i].Value2 != null) // check that the cell is not empty
{
if (xlRangeAIM.Cells[1, i].Value2.ToString().Contains("File Number"))
{
relevantCols["AIMFileNoCol"] = i;
}
else if (xlRangeAIM.Cells[1, i].Value2.ToString().Contains("Date"))
{
relevantCols["AIMCloseDateCol"] = i;
}
else if (xlRangeAIM.Cells[1, i].Value2.ToString().Contains("Property Address"))
{
relevantCols["AIMAddressCol"] = i;
}
else if (xlRangeAIM.Cells[1, i].Value2.ToString().Contains("Seller"))
{
relevantCols["AIMSellerCol"] = i;
}
else if (xlRangeAIM.Cells[1, i].Value2.ToString().Contains("Escrow"))
{
relevantCols["AIMEscrowCol"] = i;
}
}
}
// add new columns to MLS file
relevantCols.Add("MLSLikelyCloseCol", rangeCount["colCountMLS"] + 1);
relevantCols.Add("MLSEscrowOfficerCol", rangeCount["colCountMLS"] + 2);
xlRangeMLS.Cells[1, relevantCols["MLSLikelyCloseCol"]].Value = "Likely Closed";
xlRangeMLS.Cells[1, relevantCols["MLSEscrowOfficerCol"]].Value = "Escrow Officer";
// set the progress bar to the first little tick
if (progress != null)
{
progress.Report(100 / rangeCount["rowCountMLS"]);
}
DeterminerWork det = new DeterminerWork();
det.determinerDoWork(xlWorksheetMLS, xlWorksheetAIM, xlRangeMLS, xlRangeAIM,
rangeCount, relevantCols, thresholds, progress, form);
}
catch (Exception ex) // if an exception is caught, close the excel files so they aren't held hostage
{
Console.WriteLine("Problem with determiner processing. Closing excel files.");
// cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
// release com objects so the excel processes are
// fully killed from running in the background
Marshal.ReleaseComObject(xlRangeMLS);
Marshal.ReleaseComObject(xlRangeAIM);
Marshal.ReleaseComObject(xlWorksheetMLS);
Marshal.ReleaseComObject(xlWorksheetAIM);
// save, close, and release workbooks
xlWorkbookMLS.Close();
Console.WriteLine("closed MLS workbook");
xlWorkbookAIM.Close();
Console.WriteLine("closed AIM workbook");
Marshal.ReleaseComObject(xlWorkbookMLS);
Marshal.ReleaseComObject(xlWorkbookAIM);
// quit and release excel app
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
throw ex;
}
// cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
// release com objects so the excel processes are
// fully killed from running in the background
Marshal.ReleaseComObject(xlRangeMLS);
Marshal.ReleaseComObject(xlRangeAIM);
Marshal.ReleaseComObject(xlWorksheetMLS);
Marshal.ReleaseComObject(xlWorksheetAIM);
// save, close, and release workbooks
xlWorkbookMLS.Save();
Console.WriteLine("saved MLS workbook");
xlWorkbookMLS.Close();
Console.WriteLine("closed MLS workbook");
xlWorkbookAIM.Close();
Console.WriteLine("closed AIM workbook");
Marshal.ReleaseComObject(xlWorkbookMLS);
Marshal.ReleaseComObject(xlWorkbookAIM);
// quit and release excel app
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
Application.UseWaitCursor = false; // set cursor back to default
}