void Button2Click(object sender, System.EventArgs e)
{
int myIndex = comboBox1.SelectedIndex;
string selectedIndex = "";
string selectedName = "";
dbResult localResult = new dbResult();
selectedIndex = localIndex[myIndex];
selectedName = localName[myIndex];
localResult.queryDatabase("WHERE ((MyNumber='" + selectedIndex + "')", ")", localResult, conn2);
localResult.ClearIndex();
localResult.ClearName();
for (int i = 0; i < localIndex.Count; i++)
{
localResult.SetIndex(localIndex[i]);
localResult.SetName(localName[i]);
}
//delete the entry we're going to edit
localResult.deleteFromDatabase(localIndex[myIndex], conn2);
//set up the extraction form and show
ExtractionForm editForm = new ExtractionForm(myDBPath, localResult);
editForm.Show();
}
void ComboBox1SelectedIndexChanged(object sender, EventArgs e)
{
//take the new index of the combobox and look up the index of the local list.
//use the local list value to search the database user IDs.
//then repopulate the form.
//access connections have already been set up and everything.
//TODO:after quert, point editResult to the localResult for use when button2 is pressed
string selectedIndex = "";
string selectedName = "";
int myIndex = comboBox1.SelectedIndex;
comboIndex = myIndex;
dbResult localResult = new dbResult();
selectedIndex = localIndex[myIndex];
selectedName = localName[myIndex];
localResult.queryDatabase("WHERE ((MyNumber='" + selectedIndex + "')", ")", localResult, conn2);
localResult.ClearIndex();
localResult.ClearName();
for (int i = 0; i < localIndex.Count; i++)
{
localResult.SetIndex(localIndex[i]);
localResult.SetName(localName[i]);
}
//editResult = localResult;
clearForm();
populateForm(localResult, myIndex);
//localResult = null;
}
void populateForm(dbResult findResult, int nameBoxSel)
{
textBox1.Text = findResult.GetLastName();
textBox2.Text = findResult.GetFirstName();
textBox3.Text = findResult.GetMiddleName();
textBox5.Text = findResult.GetPassport();
textBox6.Text = findResult.GetCountry();
textBox9.Text = findResult.GetHaircolor();
textBox10.Text = findResult.GetEyecolor();
textBox8.Text = findResult.GetHeight();
textBox7.Text = findResult.GetWeight();
textBox4.Text = findResult.GetEthnicity();
richTextBox1.Text = findResult.GetNotes();
textBox12.Text = findResult.GetDOB();
textBox11.Text = findResult.GetPOB();
pictureBox1.ImageLocation = findResult.GetPics();
for (int i = 0; i < findResult.GetNameCount(); i++)
{
comboBox1.Items.Add(findResult.GetName(i));
}
comboBox1.SelectedIndex = nameBoxSel;
for (int i = 0; i < findResult.GetEntriesCount(); i++)
{
comboBox2.Items.Add(findResult.GetEntries(i));
}
comboBox2.SelectedIndex = findResult.GetEntriesCount() - 1;
for (int i = 0; i < findResult.GetAddressCount(); i++)
{
comboBox3.Items.Add(findResult.GetAddress(i));
}
comboBox3.SelectedIndex = findResult.GetAddressCount() - 1;
for (int i = 0; i < findResult.GetTechnologyCount(); i++)
{
comboBox6.Items.Add(findResult.GetTechnology(i));
}
comboBox6.SelectedIndex = findResult.GetTechnologyCount() - 1;
for (int i = 0; i < findResult.GetBusinessesCount(); i++)
{
comboBox5.Items.Add(findResult.GetBusinesses(i));
}
comboBox5.SelectedIndex = findResult.GetBusinessesCount() - 1;
for (int i = 0; i < findResult.GetAssociatesCount(); i++)
{
comboBox7.Items.Add(findResult.GetAssociates(i));
}
comboBox7.SelectedIndex = findResult.GetAssociatesCount() - 1;
for (int i = 0; i < findResult.GetLinksCount(); i++)
{
comboBox8.Items.Add(findResult.GetLinks(i));
}
comboBox8.SelectedIndex = findResult.GetLinksCount() - 1;
for (int i = 0; i < findResult.GetEducationCount(); i++)
{
comboBox4.Items.Add(findResult.GetEducation(i));
}
comboBox4.SelectedIndex = findResult.GetEducationCount() - 1;
}
void Button1Click(object sender, EventArgs e)
{
//create new database object
dbResult resumeFilter = new dbResult();
//send object to filter to fill in values
filterResume(resumeFilter);
//show add person form
ExtractionForm extractForm = new ExtractionForm(localDataBasePath, resumeFilter);
extractForm.Show();
}
void FindButtonClick(object sender, System.EventArgs e)
{
//create the form to show results, create teh SQL string, run the command and then show results on form
dbResult findResult = new dbResult();
string queryCriteria = "";
string queryEnd = "";
if (!isConnected)
{
MessageBox.Show("Please connect to database prior to searching.");
}
else
{
//last name must be entered prior to search. entering * is a wildcard, and entering nothing actually searches for a blank criteria
if (lastName.Text != "")
{
if (lastName.Text != "*")
{
queryEnd = ")";
queryCriteria = "WHERE ((LastName='" + lastName.Text.ToString() + "')";
if (firstName.Text != "")
{
queryCriteria = queryCriteria + "AND " + "(FirstName='" + firstName.Text.ToString() + "')";
}
if (middleName.Text != "")
{
queryCriteria = queryCriteria + "AND " + "(middleName='" + middleName.Text.ToString() + "')";
}
}
findResult.queryDatabase(queryCriteria, queryEnd, findResult, conn);
//set up the form and show
dbForm findForm = new dbForm(findResult, dataBasePath);
findForm.Show();
}
}
}
public dbForm(dbResult findResult, string dataBasePath)
{
//
// The InitializeComponent() call is required for Windows Forms designer support.
//
InitializeComponent();
//
// TODO: Add constructor code after the InitializeComponent() call.
//
this.localIndex.Clear();
this.localName.Clear();
for (int i = 0; i < findResult.GetIndexCount(); i++)
{
this.localIndex.Add(findResult.GetIndex(i));
this.localName.Add(findResult.GetName(i));
}
myDBPath = dataBasePath;
myResult = findResult;
ConnectToAccess2(dataBasePath);
populateForm(findResult, findResult.GetNameCount() - 1);
}
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.");
}
public ExtractionForm(string dataBasePath, dbResult initWindow)
{
//
// The InitializeComponent() call is required for Windows Forms designer support.
//
InitializeComponent();
//
// TODO: Add constructor code after the InitializeComponent() call.
//
localDataBasePath = dataBasePath;
//fill in textbox values from the initWindow object
// textBox13.Text = initWindow.GetName(0); //never uncomment this again. it breaks the form.
textBox1.Text = initWindow.GetLastName();
textBox2.Text = initWindow.GetFirstName();
textBox3.Text = initWindow.GetMiddleName();
textBox14.Text = initWindow.GetPics();
textBox6.Text = initWindow.GetCountry();
textBox5.Text = initWindow.GetPassport();
richTextBox1.Text = initWindow.GetNotes();
textBox10.Text = initWindow.GetEyecolor();
textBox9.Text = initWindow.GetHaircolor();
textBox8.Text = initWindow.GetHeight();
textBox7.Text = initWindow.GetWeight();
textBox4.Text = initWindow.GetEthnicity();
textBox12.Text = initWindow.GetDOB();
textBox11.Text = initWindow.GetPOB();
string entriesString = "";
for (int i = 0; i < initWindow.GetEntriesCount(); i++)
{
entriesString = entriesString + initWindow.GetEntries(i) + "\n";
}
richTextBox8.Text = entriesString;
string linksString = "";
for (int i = 0; i < initWindow.GetLinksCount(); i++)
{
linksString = linksString + initWindow.GetLinks(i) + "\n";
}
richTextBox7.Text = linksString;
string tempString = "";
for (int i = 0; i < initWindow.GetAddressCount(); i++)
{
tempString = tempString + initWindow.GetAddress(i) + "\n";
}
richTextBox2.Text = tempString;
tempString = "";
for (int i = 0; i < initWindow.GetTechnologyCount(); i++)
{
tempString = tempString + initWindow.GetTechnology(i) + "\n";
}
richTextBox5.Text = tempString;
string[] educString = new String[3];
string substring = "";
for (int i = 0; i < initWindow.GetEducationCount(); i++)
{
//since adding and editing a person's info creates different delimiters in the object, replace
//with common delimiter before working string
substring = initWindow.GetEducation(i).Replace("\t\t", ":");
string[] temp = substring.Split(':');
for (int j = 0; j < 3; j++)
{
educString[j] = educString[j] + temp[j] + "\n";
}
}
richTextBox3.Text = educString[0];
richTextBox9.Text = educString[1];
richTextBox10.Text = educString[2];
string[] busString = new String[2];
for (int i = 0; i < initWindow.GetBusinessesCount(); i++)
{
//since adding and editing a person's info creates different delimiters in the object, replace
//with common delimiter before working string
substring = initWindow.GetBusinesses(i).Replace("\t\t", ":");
string[] temp = substring.Split(':');
for (int j = 0; j < 2; j++)
{
busString[j] = busString[j] + temp[j] + "\n";
}
}
richTextBox4.Text = busString[0];
richTextBox11.Text = busString[1];
string[] addyString = new String[2];
for (int i = 0; i < initWindow.GetAddressCount(); i++)
{
//since adding and editing a person's info creates different delimiters in the object, replace
//with common delimiter before working string
substring = initWindow.GetAddress(i).Replace("\t\t", ":");
string[] temp = substring.Split(':');
for (int j = 0; j < 2; j++)
{
addyString[j] = addyString[j] + temp[j] + "\n";
}
}
richTextBox2.Text = addyString[0];
richTextBox12.Text = addyString[1];
string assocString = "";
for (int i = 0; i < initWindow.GetAssociatesCount(); i++)
{
assocString = assocString + initWindow.GetAssociates(i) + "\n";
}
richTextBox6.Text = assocString;
string techString = "";
for (int i = 0; i < initWindow.GetTechnologyCount(); i++)
{
techString = techString + initWindow.GetTechnology(i) + "\n";
}
richTextBox5.Text = techString;
}
public void insertDatabase(dbResult findResult, string myIndex, System.Data.OleDb.OleDbConnection conn)
{
//create INSERT command
string insertCols1 = "PersonName, LastName, FirstName, MiddleName, Country, Photo, PlaceOfBirth, DateOfBirth, Passport, Notes, EyeColor, HairColor, Height, Weight, Ethnicity, MyNumber";
string sqlString = "INSERT INTO tblPeeps (" + insertCols1 + ") VALUES (";
string insertVals1 = "'" + findResult.GetName(0) + "', '" + findResult.GetLastName() + "', '" + findResult.GetFirstName() + "', '" +
findResult.GetMiddleName() + "', '" + findResult.GetCountry() + "', '" + findResult.GetPics() + "', '" +
findResult.GetPOB() + "', '" + findResult.GetDOB() + "', '" + findResult.GetPassport() + "', '" +
findResult.GetNotes() + "', '" + findResult.GetEyecolor() + "', '" + findResult.GetHaircolor() + "', '" +
findResult.GetHeight() + "', '" + findResult.GetWeight() + "', '" + findResult.GetEthnicity() + "', '" + myIndex + "'";
sqlString = sqlString + insertVals1 + ")";
//set up connection and run command for 1st table
OleDbCommand Com = new OleDbCommand();
Com.CommandText = sqlString;
Com.Connection = conn;
OleDbDataReader objDataReader = null;
objDataReader = Com.ExecuteReader();
objDataReader.Close();
//set up sql strings for each insertion to the second table
int maxLines = findResult.GetEntriesCount();
if (findResult.GetAddressCount() > maxLines)
{
maxLines = findResult.GetAddressCount();
}
if (findResult.GetAssociatesCount() > maxLines)
{
maxLines = findResult.GetAssociatesCount();
}
if (findResult.GetBusinessesCount() > maxLines)
{
maxLines = findResult.GetBusinessesCount();
}
if (findResult.GetEducationCount() > maxLines)
{
maxLines = findResult.GetEducationCount();
}
if (findResult.GetEntriesCount() > maxLines)
{
maxLines = findResult.GetEntriesCount();
}
if (findResult.GetLinksCount() > maxLines)
{
maxLines = findResult.GetLinksCount();
}
if (findResult.GetTechnologyCount() > maxLines)
{
maxLines = findResult.GetTechnologyCount();
}
//set up sql command, connection and run command for second table
OleDbCommand Com2 = new OleDbCommand();
Com2.Connection = conn;
OleDbDataReader objDataReader2 = null;
string insertCols2 = "Entries, Addresses, DateOfAddresses, Technologies, " +
"Associates, Universities, Degrees, FieldOfStudies, PlaceOfBiz, RoleInBiz, Links, PersonID";
string valueString = "";
//create individual rows and send them one at a time to DB
for (int i = 0; i < maxLines; i++)
{
//create strings using values from form
valueString = "";
if (i < findResult.GetEntriesCount())
{
valueString = valueString + "'" + findResult.GetEntries(i) + "', ";
}
else
{
valueString = valueString + "'', ";
}
string[] words = findResult.GetAddress(0).Split(':');
if (i < findResult.GetAddressCount())
{
words = findResult.GetAddress(i).Split(':');
valueString = valueString + "'" + words[0] + "', '" + words[1] + "', ";
}
else
{
valueString = valueString + "'', '', ";
}
if (i < findResult.GetTechnologyCount())
{
valueString = valueString + "'" + findResult.GetTechnology(i) + "', ";
}
else
{
valueString = valueString + "'', ";
}
if (i < findResult.GetAssociatesCount())
{
valueString = valueString + "'" + findResult.GetAssociates(i) + "', ";
}
else
{
valueString = valueString + "'', ";
}
if (i < findResult.GetEducationCount())
{
words = findResult.GetEducation(i).Split(':');
valueString = valueString + "'" + words[0] + "', '" + words[1] + "', '" + words[2] + "', ";
}
else
{
valueString = valueString + "'', '', '', ";
}
if (i < findResult.GetBusinessesCount())
{
words = findResult.GetBusinesses(i).Split(':');
valueString = valueString + "'" + words[0] + "', '" + words[1] + "', ";
}
else
{
valueString = valueString + "'', '', ";
}
if (i < findResult.GetLinksCount())
{
valueString = valueString + "'" + findResult.GetLinks(i) + "', ";
}
else
{
valueString = valueString + "'', ";
}
valueString = valueString + "'" + myIndex + "'";
//put together SQL string and send to database
sqlString = "INSERT INTO tblOtherInfo (" + insertCols2 + ") VALUES (" + valueString + ")";
Com2.CommandText = sqlString;
objDataReader2 = Com2.ExecuteReader();
objDataReader2.Close();
objDataReader2 = null;
}
}
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)
{
//TODO: creat afunction; need a new structure to get results into and pass to display sindow
string sqlSimpleQuery = "";
string sqlJoinQuery = "";
string whereClause = "";
string totalWheres = "";
bool needAnd = false;
customQuery analyze = new customQuery();
dbResult test = new dbResult();
//access connection has already been made
//look over checkboxes and use combobox selections to create SQL statement
if (checkBox1.Checked)
{
string[] comboArray = comboBox4.Text.ToString().Split(new string[] { "\t" }, StringSplitOptions.RemoveEmptyEntries);
whereClause = "(Universities='" + comboArray[0] + "')";
totalWheres = totalWheres + whereClause + "\n";
sqlSimpleQuery = "SELECT DISTINCT PersonID FROM tblOtherInfo WHERE " + whereClause;
needAnd = true;
sqlJoinQuery = "SELECT PersonID, Universities FROM tblOtherInfo WHERE " + whereClause;
}
if (checkBox2.Checked)
{
string[] comboArray = comboBox4.Text.ToString().Split(new string[] { "\t" }, StringSplitOptions.RemoveEmptyEntries);
whereClause = "(Degrees='" + comboArray[1] + "')";
totalWheres = totalWheres + whereClause + "\n";
sqlSimpleQuery = "SELECT DISTINCT PersonID FROM tblOtherInfo WHERE " + whereClause;
if (needAnd == false)
{
needAnd = true;
sqlJoinQuery = "SELECT PersonID, Degrees FROM tblOtherInfo WHERE " + whereClause;
}
else
{
sqlJoinQuery = "SELECT DegreesJoinPrev.PersonID FROM (" + sqlJoinQuery + ")DegreesJoinPrev INNER JOIN (" + sqlSimpleQuery + ")DegreesJoin ON DegreesJoinPrev.PersonID=DegreesJoin.PersonID";
}
}
if (checkBox3.Checked)
{
string[] comboArray = comboBox4.Text.ToString().Split(new string[] { "\t" }, StringSplitOptions.RemoveEmptyEntries);
whereClause = "(FieldOfStudies='" + comboArray[2] + "')";
totalWheres = totalWheres + whereClause + "\n";
sqlSimpleQuery = "SELECT DISTINCT PersonID FROM tblOtherInfo WHERE " + whereClause;
if (needAnd == false)
{
needAnd = true;
sqlJoinQuery = "SELECT PersonID, FieldOfStudies FROM tblOtherInfo WHERE " + whereClause;
}
else
{
sqlJoinQuery = "SELECT FOSJoinPrev.PersonID FROM (" + sqlJoinQuery + ")FOSJoinPrev INNER JOIN (" + sqlSimpleQuery + ")FOSJoin ON FOSJoinPrev.PersonID=FOSJoin.PersonID";
}
}
if (checkBox4.Checked)
{
string[] comboArray = comboBox5.Text.ToString().Split(new string[] { "\t" }, StringSplitOptions.RemoveEmptyEntries);
whereClause = "(PlaceOfBiz='" + comboArray[0] + "')";
totalWheres = totalWheres + whereClause + "\n";
sqlSimpleQuery = "SELECT DISTINCT PersonID FROM tblOtherInfo WHERE " + whereClause;
if (needAnd == false)
{
needAnd = true;
sqlJoinQuery = "SELECT PersonID, PlaceOfBiz FROM tblOtherInfo WHERE " + whereClause;
}
else
{
sqlJoinQuery = "SELECT POBJoinPrev.PersonID FROM (" + sqlJoinQuery + ")POBJoinPrev INNER JOIN (" + sqlSimpleQuery + ")POBJoin ON POBJoinPrev.PersonID=POBJoin.PersonID";
}
}
if (checkBox5.Checked)
{
string[] comboArray = comboBox5.Text.ToString().Split(new string[] { "\t" }, StringSplitOptions.RemoveEmptyEntries);
whereClause = "(RoleInBiz='" + comboArray[1] + "')";
totalWheres = totalWheres + whereClause + "\n";
sqlSimpleQuery = "SELECT DISTINCT PersonID FROM tblOtherInfo WHERE " + whereClause;
if (needAnd == false)
{
needAnd = true;
sqlJoinQuery = "SELECT PersonID, RoleInBiz FROM tblOtherInfo WHERE " + whereClause;
}
else
{
sqlJoinQuery = "SELECT RIBJoinPrev.PersonID FROM (" + sqlJoinQuery + ")RIBJoinPrev INNER JOIN (" + sqlSimpleQuery + ")RIBJoin ON RIBJoinPrev.PersonID=RIBJoin.PersonID";
}
}
if (checkBox6.Checked)
{
whereClause = "(Technologies='" + comboBox6.Text.ToString() + "')";
totalWheres = totalWheres + whereClause + "\n";
sqlSimpleQuery = "SELECT DISTINCT PersonID FROM tblOtherInfo WHERE " + whereClause;
if (needAnd == false)
{
needAnd = true;
sqlJoinQuery = "SELECT PersonID, Technologies FROM tblOtherInfo WHERE " + whereClause;
}
else
{
sqlJoinQuery = "SELECT TechJoinPrev.PersonID FROM (" + sqlJoinQuery + ")TechJoinPrev INNER JOIN (" + sqlSimpleQuery + ")TechJoin ON TechJoinPrev.PersonID=TechJoin.PersonID";
}
}
if (checkBox7.Checked)
{
whereClause = "(Associates='" + comboBox7.Text.ToString() + "')";
totalWheres = totalWheres + whereClause + "\n";
sqlSimpleQuery = "SELECT DISTINCT PersonID FROM tblOtherInfo WHERE " + whereClause;
if (needAnd == false)
{
needAnd = true;
sqlJoinQuery = "SELECT PersonID, Associates FROM tblOtherInfo WHERE " + whereClause;
}
else
{
sqlJoinQuery = "SELECT AssJoinPrev.PersonID FROM (" + sqlJoinQuery + ")AssJoinPrev INNER JOIN (" + sqlSimpleQuery + ")AssJoin ON AssJoinPrev.PersonID=AssJoin.PersonID";
}
}
//end query
//send statement to database and get results
analyze.queryDatabase(sqlJoinQuery, analyze, conn2);
//set up the form and show with results
displayForm analyzeDisplay = new displayForm(analyze, totalWheres);
analyzeDisplay.Show();
}
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);
}
}
}
