public void queryDatabase(string whereClause, string endClause, dbResult findResult, System.Data.OleDb.OleDbConnection conn)
{
bool foundData = false;
string sqlString = "SELECT LastName, FirstName, MiddleName, PersonName, StreetAddress, " +
"Country, PlaceOfBirth, DateOfBirth, Passport, Notes, EyeColor, " +
"HairColor, Height, Weight, Ethnicity, MyNumber, Photo " +
"FROM tblPeeps " + whereClause + endClause;
OleDbCommand Com = new OleDbCommand();
Com.CommandText = sqlString;
Com.Connection = conn;
OleDbDataReader objDataReader = null;
objDataReader = Com.ExecuteReader();
if (objDataReader == null)
{
return;
}
while (objDataReader.Read())
{
findResult.SetLastName(objDataReader["LastName"].ToString());
findResult.SetFirstName(objDataReader["FirstName"].ToString());
findResult.SetMiddleName(objDataReader["MiddleName"].ToString());
findResult.SetCountry(objDataReader["Country"].ToString());
findResult.SetPOB(objDataReader["PlaceOfBirth"].ToString());
findResult.SetDOB(objDataReader["DateOfBirth"].ToString());
findResult.SetPassport(objDataReader["Passport"].ToString());
findResult.SetNotes(objDataReader["Notes"].ToString());
findResult.SetEyecolor(objDataReader["EyeColor"].ToString());
findResult.SetHaircolor(objDataReader["HairColor"].ToString());
findResult.SetHeight(objDataReader["Height"].ToString());
findResult.SetWeight(objDataReader["Weight"].ToString());
findResult.SetEthnicity(objDataReader["Ethnicity"].ToString());
findResult.SetPics(objDataReader["Photo"].ToString());
findResult.SetName(objDataReader["PersonName"].ToString());
findResult.SetIndex(objDataReader["MyNumber"].ToString());
foundData = true;
}
objDataReader.Close();
if (foundData)
{
sqlString = "SELECT Entries, Addresses, Technologies, DateOfAddresses, " +
"Associates, Universities, Degrees, FieldOfStudies, PlaceOfBiz, RoleInBiz, Links " +
"FROM tblOtherInfo " +
"WHERE (PersonID='" + findResult.GetIndex(findResult.GetIndexCount() - 1) + "')";
OleDbCommand Com2 = new OleDbCommand();
Com2.CommandText = sqlString;
Com2.Connection = conn;
OleDbDataReader objDataReader2 = null;
objDataReader2 = Com2.ExecuteReader();
if (objDataReader2 == null)
{
return;
}
while (objDataReader2.Read())
{
findResult.SetEntries(objDataReader2["Entries"].ToString());
findResult.SetAddress(objDataReader2["Addresses"].ToString() + "\t\t" + objDataReader2["DateOfAddresses"].ToString());
findResult.SetEducation(objDataReader2["Universities"].ToString() + "\t\t" + objDataReader2["Degrees"].ToString() + "\t\t" + objDataReader2["FieldOfStudies"].ToString());
findResult.SetTechnology(objDataReader2["Technologies"].ToString());
findResult.SetBusinesses(objDataReader2["PlaceOfBiz"].ToString() + "\t\t" + objDataReader2["RoleInBiz"].ToString());
findResult.SetAssociates(objDataReader2["Associates"].ToString());
findResult.SetLinks(objDataReader2["Links"].ToString());
}
objDataReader2.Close();
}
else
{
MessageBox.Show("Search Returned 0 matches.\nEnter * in LastName field to see all entries.");
}
}
void Button1Click(object sender, EventArgs e)
{
string fromRichTextBox;
dbResult localResult = new dbResult();
//connect to database
ConnectToAccess3(localDataBasePath);
//find the last number added
string sqlString = "SELECT MyNumber, ID FROM tblPeeps WHERE (ID=(SELECT MAX(ID) FROM tblPeeps))";
string myNumber = "33";
OleDbCommand Com = new OleDbCommand();
Com.CommandText = sqlString;
Com.Connection = conn3;
OleDbDataReader objDataReader = null;
objDataReader = Com.ExecuteReader();
if (objDataReader == null)
{
return;
}
while (objDataReader.Read())
{
myNumber = objDataReader["MyNumber"].ToString();
int num = int.Parse(myNumber);
myNumber = (++num).ToString();
}
objDataReader.Close();
//fill query object with values from form
localResult.SetName(textBox13.Text);
localResult.SetLastName(textBox1.Text);
localResult.SetFirstName(textBox2.Text);
localResult.SetMiddleName(textBox3.Text);
localResult.SetCountry(textBox6.Text);
localResult.SetPassport(textBox5.Text);
localResult.SetPics(textBox14.Text);
localResult.SetEyecolor(textBox10.Text);
localResult.SetHaircolor(textBox9.Text);
localResult.SetHeight(textBox8.Text);
localResult.SetWeight(textBox7.Text);
localResult.SetEthnicity(textBox4.Text);
localResult.SetNotes(richTextBox1.Text);
localResult.SetPOB(textBox11.Text);
localResult.SetDOB(textBox12.Text);
//for the second table, take each row and add to localResult separating by :
fromRichTextBox = richTextBox8.Text;
string[] words = fromRichTextBox.Split('\n');
foreach (string word in words)
{
//if (word.Contains()
localResult.SetEntries(word);
}
fromRichTextBox = richTextBox2.Text;
words = fromRichTextBox.Split('\n');
string[] words2 = richTextBox12.Text.Split('\n');
int i = 0;
foreach (string word in words)
{
//if (!word.Contains(""))
localResult.SetAddress(word + ":" + words2[i++]);
}
fromRichTextBox = richTextBox3.Text;
words = fromRichTextBox.Split('\n');
words2 = richTextBox9.Text.Split('\n');
string[] words3 = richTextBox10.Text.Split('\n');
i = 0;
foreach (string word in words)
{
//if (!word.Contains(""))
localResult.SetEducation(word + ":" + words2[i] + ":" + words3[i++]);
}
fromRichTextBox = richTextBox4.Text;
words = fromRichTextBox.Split('\n');
words2 = richTextBox11.Text.Split('\n');
i = 0;
foreach (string word in words)
{
//if (!word.Contains(""))
localResult.SetBusinesses(word + ":" + words2[i++]);
}
fromRichTextBox = richTextBox5.Text;
words = fromRichTextBox.Split('\n');
foreach (string word in words)
{
//if (!word.Contains(""))
localResult.SetTechnology(word);
}
fromRichTextBox = richTextBox6.Text;
words = fromRichTextBox.Split('\n');
foreach (string word in words)
{
//if (!word.Contains(""))
localResult.SetAssociates(word);
}
fromRichTextBox = richTextBox7.Text;
words = fromRichTextBox.Split('\n');
foreach (string word in words)
{
//if (word != "")
localResult.SetLinks(word);
}
//insert object values into database
insertDatabase(localResult, myNumber, conn3);
MessageBox.Show("Successfully added to database.");
}
void filterResume(dbResult filterResults)
{
//get the name and address and remove newlines
string temp = richTextBox1.Text.Replace('\n', ' ');
temp = temp.Replace('.', ' ');
temp = temp.Replace(',', ' ');
//use regex to check for address, get index of start of address and then assume name precedes it
//looking for format 5503 West Seminary Rd #1897 falls church VA 22041
Regex exp = new Regex(@"\d+\s+\w+(\s+\w+)?\s+[a-z]+(\s+\D+\d+)?\s+[a-z]+(\s+[a-z]+)?\s+[a-z]+\s+\d\d\d\d\d",
RegexOptions.IgnoreCase);
MatchCollection MatchList = exp.Matches(temp);
if (MatchList.Count == 1)
{
Match FirstMatch = MatchList[0];
filterResults.SetAddress(FirstMatch.Value + ":");
string nameTemp = temp.Substring(0, temp.IndexOf(FirstMatch.Value) - 1);
string[] nameArray = nameTemp.Split(' ');
int count = 0;
//there are better ways to do this, but I needed an easy way to get rid of hidden spaces
foreach (string name in nameArray)
{
if (name != "")
{
count++;
}
}
if (count >= 3)
{
filterResults.SetFirstName(nameArray[0]);
filterResults.SetMiddleName(nameArray[1]);
filterResults.SetLastName(nameArray[2]);
}
else if (count >= 2)
{
filterResults.SetFirstName(nameArray[0]);
filterResults.SetLastName(nameArray[1]);
}
}
//now work on education portion of form
string educText = richTextBox2.Text;
//find degrees, then colleges, then fields of study
//first two can be found by using regex to filter keywords
Regex educExp = new Regex(@"((((bachelor(s)?(,|\s+of)\s+)|(b\.?))|((master(s)?(,|\s+of)\s+)|(m\.?))|((doctorate(,|\s+of)\s+)|(d\.?)))(((s\.?(\s+|,))|(science(,|\s+)))|((e\.?(\s+|,))|(engineering(,|\s+)))|((a\.?(\s+|,))|(art(,|\s+)))|((t\.?(\s+|,))|(tech[a-z]+(,|\s+)))))|((doctorate\s+of\s+philosophy(,|\s+))|(p\.?h\.?d\.?(,|\s+)))",
RegexOptions.IgnoreCase);
MatchCollection MatchListEduc = educExp.Matches(educText);
Regex univExp = new Regex(@"((University\s+of\s+([A-Z][a-z]+(\s+|,))+(\s+|,)?)|(([A-Z][a-z]+\s+)+University(\s+|,)?))|((College\s+of\s+([A-Z][a-z]+(\s+|,))+(\s+|,)?)|(([A-Z][a-z]+\s+)+College(\s+|,)?))|((Institute\s+of\s+([A-Z][a-z]+(\s+|,))+(\s+|,)?)|(([A-Z][a-z]+\s+)+Institute(\s+|,)?))");
MatchCollection MatchListUniv = univExp.Matches(educText);
//need to use degree data to split into substrings
//then use regex to find field of study in substring for each degree
//Regex fosExp = new Regex(@"",
// RegexOptions.IgnoreCase);
//MatchCollection MatchListFos = fosExp.Matches(educText);
//foreach (Match FirstMatchEduc in MatchListUniv)
// filterResults.SetEducation(FirstMatchEduc.Value+":"+"university"+":"+"fos");
//format <university>:<degree>:<field of study> sent to object to get passed to extranctionform
for (int i = 0; i < MatchListEduc.Count; i++)
{
filterResults.SetEducation(MatchListUniv[i].Value + ":" + MatchListEduc[i].Value + ":" + "fos");
}
//now get associates info from teh box; go for the low hanging fruit first. :)
string[] assArray = richTextBox4.Text.Split('\n');
foreach (string assoc in assArray)
{
filterResults.SetAssociates(assoc);
}
//now get technologies from education and emplyment history; once again, low hanging fruit. :)
//open keyword file and pull keywords inot array; then look through education and employment text for keywords
string textIn = File.ReadAllText("techWords.cfg");
string [] textArray = textIn.Split(new string [] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string keyword in textArray)
{
if (richTextBox3.Text.Contains(keyword) || richTextBox2.Text.Contains(keyword))
{
filterResults.SetTechnology(keyword);
}
}
}
