/// <summary>
/// Copies the names in the "prijmeni.xls" spreadsheet, converting them to proper casing
/// (only the first character upper-cased) and writes the output into a "prijmeni lowercase.xls" file
/// </summary>
/// <param name="sender">originator of the event</param>
/// <param name="e">additional details on the event</param>
private void prijmeniConversionToolStripMenuItem_Click(object sender, EventArgs e)
{
string sPrijmeniPath;
if (DialogResult.OK != Utilities.CheckForDataFile("prijmeni.xls", out sPrijmeniPath))
return;
var fNames = new ExcelFile(sPrijmeniPath);
var fNamesOut = new ExcelFile();
var pTarg = new Point(5, 1);
var pHtml = new Point(4, 1);
int iBlanks, iNames, iRow;
for (iBlanks = 0, iNames = 0, iRow = 2; iBlanks < 5; ++iRow)
{
string sValue = string.Empty;
string sHtml = string.Empty;
foreach (var iNext in new[] { 1, 2, 3, 4, 5, 6 })
{
var pNext = new Point(iNext, iRow);
string sNextValue = fNames.ValueAt(pNext);
if (iNext == 4)
sHtml = sNextValue;
if (iNext == 5)
sValue = sNextValue;
string sValueOut = sNextValue;
if ( ( iNext >= 2 ) && ( iNext <= 5 ) )
sValueOut = WebQuery.ConvertToProperCase( sNextValue );
fNamesOut.SetValueAt(pNext, sValueOut);
}
if ((iRow % 100) == 0)
{
txtLastName.Text = sValue;
txtFirstName.Text = iRow.ToString(CultureInfo.InvariantCulture);
Application.DoEvents();
}
pTarg.Y = iRow; pHtml.Y = iRow;
if (string.IsNullOrEmpty(sValue))
++iBlanks;
else
{
if (sValue.ToCharArray().Any(cNext => (cNext < 0x20) || (cNext > 127)))
{
MessageBox.Show(@"Name with special character (" + sValue + @")" + Environment.NewLine +
@"found in row " + iRow, @"Special character detected", MessageBoxButtons.OK, MessageBoxIcon.Hand);
}
if (sHtml.ToCharArray().Any(cNext => (cNext < 0x20) || (cNext > 127)))
{
MessageBox.Show(@"Name with special character (" + sHtml + @")" + Environment.NewLine +
@"found in HTML string at row " + iRow, @"Special character detected", MessageBoxButtons.OK, MessageBoxIcon.Hand);
}
++iNames;
iBlanks = 0;
}
}
MessageBox.Show(iNames + @" names found in spreadsheet", @"Prijmeni scan", MessageBoxButtons.OK, MessageBoxIcon.Information);
fNames.Close();
fNamesOut.SaveAs(System.IO.Path.Combine(Utilities.DataFilesFolder, "prijmeni lowercased.xls"));
fNamesOut.Close();
}
/// <summary>
/// Event handler for the given names transliteration menu item.
/// No longer sure just what this did when it was last used, to be frank.
/// </summary>
/// <param name="sender">originator of the event</param>
/// <param name="e">additional details on the event</param>
/// <remarks>Looks like this was used in a combination of two operations:
/// Transliterating Czech names (with diacriticals) to English (without),
/// and obtaining counts of the frequency of each name.</remarks>
private void givenNamesTranslitToolStripMenuItem_Click(object sender, EventArgs e)
{
string sGivenNames;
if (DialogResult.OK != Utilities.CheckForDataFile("GivenNamesMaleTransliterated.xls", out sGivenNames))
return;
var fNames = new ExcelFile(sGivenNames);
int iRow = 2, iBlankRows = 0, iModified = 0, iNew = 0;
txtResponse.Clear();
while (iBlankRows < 1)
{
string sId = fNames.ValueAt(new Point(1, iRow)).Trim();
String sNative = fNames.ValueAt(new Point(2, iRow)).Trim();
string sWeb = fNames.ValueAt(new Point(3, iRow)).Trim();
string sPlainText = fNames.ValueAt(new Point(4, iRow)).Trim();
string sCount = fNames.ValueAt(new Point(5, iRow)).Trim();
//if ((iRow % 100) == 0)
//{
// txtResponse.AppendText("Row " + iRow.ToString() +
// " (Id: " + sId + ") - " + iNew.ToString() + " records added, " +
// iModified.ToString() + " records modified (Current name \"" +
// sNative + "\", " + sCount + " individuals with this name)" + Environment.NewLine);
// Application.DoEvents();
//}
if (string.IsNullOrEmpty(sId))
++iBlankRows;
else if ( !string.IsNullOrEmpty(sNative) )
{
int iCount, iIndex;
int.TryParse(sCount, out iCount);
int.TryParse(sId, out iIndex);
//if (!int.TryParse(sCount, out iCount))
// MessageBox.Show("Invalid count at row " + iRow.ToString() + ": " + sCount, "Not a valid integer", MessageBoxButtons.OK, MessageBoxIcon.Error);
var q = (from jmNext in _mdB.KrestniJmenas
where jmNext.CodePage == sNative
select jmNext).FirstOrDefault();
if (q != null)
{
++iModified;
txtResponse.AppendText("Modifying existing entry \"" + sNative + "\" with count of " + sCount + Environment.NewLine);
Application.DoEvents();
}
else
{
if ((iCount > 1) && !sNative.Contains(' '))
{
++iNew;
txtResponse.AppendText("Adding new entry \"" + sNative + "\"" + Environment.NewLine);
//var jmNew = new KrestniJmena();
//jmNew.CodePage = sNative;
//jmNew.Web = sWeb;
//jmNew.PlainText = sPlainText;
//mdB.KrestniJmenas.InsertOnSubmit(jmNew);
Application.DoEvents();
}
}
}
++iRow;
}
txtResponse.AppendText(string.Format(">>> Totals: {0} rows, {1} records added, {2} records modified <<<{3}", iRow, iNew, iModified, Environment.NewLine));
// fNames.SaveAs(System.IO.Path.Combine(Utilities.DataFilesFolder, "GivenNamesFemaleTransliterated.xls"));
fNames.Close();
}
/// <summary>
/// Reads in a spreadsheet containing given (first) names
/// and adds the names to the given names database table if they don't already exist there.
/// </summary>
/// <param name="sender">originator of the event</param>
/// <param name="e">additional details on the event</param>
private void givenNamesMergeToolStripMenuItem_Click(object sender, EventArgs e)
{
string sGivenNames;
string sGender = "Female"; // "Male"
if (DialogResult.OK != Utilities.CheckForDataFile("GivenNames" + sGender + "Transliterated-2.xls", out sGivenNames))
return;
var fNames = new ExcelFile(sGivenNames);
txtResponse.Clear();
int iBlanks, iNames, iNew, iModified, iRow;
for (iBlanks = 0, iNames = 0, iNew = 0, iModified = 0, iRow = 2; iBlanks < 5; ++iRow)
{
string sId = fNames.ValueAt(new Point(1, iRow)).Trim();
string sNative = fNames.ValueAt(new Point(2, iRow)).Trim();
string sHtml = fNames.ValueAt(new Point(3, iRow)).Trim();
string sPlainText = fNames.ValueAt(new Point(4, iRow)).Trim();
string sCount = fNames.ValueAt(new Point(5, iRow)).Trim();
if ((iRow % 1000) == 0)
{
txtResponse.AppendText("Row " + iRow + ": Name \"" + sNative + "\"" + Environment.NewLine);
Application.DoEvents();
// break;
}
if (string.IsNullOrEmpty(sPlainText))
++iBlanks;
else
{
int iCount, iId;
if (!int.TryParse(sCount, out iCount))
txtResponse.AppendText(string.Format("Invalid count \"{0}\" found at row {1}{2}", sCount, iRow, Environment.NewLine));
if (!int.TryParse(sId, out iId ))
txtResponse.AppendText( string.Format("Invalid ID \"{0}\" found at row {1}{2}", sId, iRow, Environment.NewLine));
KrestniJmena q = (from jmNext in _mdB.KrestniJmenas
where jmNext.CodePage == sNative
select jmNext).FirstOrDefault();
bool bIsNewRecord = false;
if (q != null)
{
++iModified;
//q.CodePage = sNative;
//q.Web = sHtml;
//q.PlainText = sPlainText;
// txtResponse.AppendText("Modifying existing entry \"" + sNative + "\" with ID=" + sId + " and Count=" + sCount + Environment.NewLine);
// Application.DoEvents();
}
else
{
bIsNewRecord = true;
if ((iCount > 1) || !sNative.Contains(' '))
{
++iNew;
// txtResponse.AppendText("Adding new entry \"" + sNative + "\" with ID=" + sId + " and Count=" + sCount + Environment.NewLine);
q = new KrestniJmena {CodePage = sNative, Web = sHtml, PlainText = sPlainText};
// Application.DoEvents();
}
}
if (q != null)
{
switch (sGender)
{
case "Male":
q.MaleIndex = iId;
q.MaleCount = iCount;
break;
case "Female":
q.FemaleIndex = iId;
q.FemaleCount = iCount;
break;
}
if ( bIsNewRecord )
_mdB.KrestniJmenas.InsertOnSubmit(q);
try
{
_mdB.SubmitChanges();
}
catch (Exception ex)
{
txtResponse.AppendText(string.Format("Database error at row {0}: {1}{2}", iRow, ex, Environment.NewLine));
}
}
++iNames;
iBlanks = 0;
}
}
txtResponse.AppendText(iNames + " names found in spreadsheet" + Environment.NewLine);
txtResponse.AppendText(iNew + " records added" + Environment.NewLine);
txtResponse.AppendText(iModified + " records modified" + Environment.NewLine);
fNames.Close();
}
/// <summary>
/// Reads through the rank-sorted Czech family names list "prijmeni rank-sorted.xls",
/// displaying every 100-th name in the user interface, and flagging any duplicate entries
/// </summary>
/// <param name="sender">originator of the event</param>
/// <param name="e">additional details on the event</param>
private void prijmeniRankCheckToolStripMenuItem_Click(object sender, EventArgs e)
{
string sPrijmeniPath;
if (DialogResult.OK != Utilities.CheckForDataFile("prijmeni rank-sorted.xls", out sPrijmeniPath))
return;
var fNames = new ExcelFile(sPrijmeniPath);
int iBlanks, iRow;
var lRanks = new List<int>();
for (iBlanks = 0, iRow = 2; iBlanks < 5; ++iRow)
{
var pTarg = new Point(1, iRow);
var pName = new Point(5, iRow);
string sRank = fNames.ValueAt(pTarg);
string sName = fNames.ValueAt(pName);
if ((iRow % 100) == 0)
{
txtLastName.Text = sName;
txtFirstName.Text = iRow.ToString(CultureInfo.InvariantCulture);
Application.DoEvents();
}
if (string.IsNullOrEmpty(sRank))
++iBlanks;
else
{
int iRank = int.Parse(sRank);
if (lRanks.Contains(iRank))
MessageBox.Show(string.Format(Resources.DuplicateRankCaption, sRank, sName), Resources.DuplicateKeyLabel, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
else
lRanks.Add(iRank);
iBlanks = 0;
}
}
fNames.Close();
}
/// <summary>
/// Scans through a spreadsheet of given (first) names and looks for
/// invalid instance counts and non-printable characters
/// </summary>
/// <param name="sender">originator of the event</param>
/// <param name="e">additional details on the event</param>
private void givenNamesCheckToolStripMenuItem_Click(object sender, EventArgs e)
{
string sGivenNames;
if (DialogResult.OK != Utilities.CheckForDataFile("GivenNamesMaleTransliterated-2.xls", out sGivenNames))
return;
var fNames = new ExcelFile(sGivenNames);
var lSpecialChars = new List<char>();
var lBogusCounts = new List<int>();
txtResponse.Clear();
int iBlanks, iNames, iRow;
for (iBlanks = 0, iNames = 0, iRow = 2; iBlanks < 5; ++iRow)
{
// string sId = fNames.ValueAt( new Point( 1, iRow ) ).Trim();
string sNative = fNames.ValueAt(new Point(2, iRow)).Trim();
string sHtml = fNames.ValueAt(new Point(3, iRow)).Trim();
string sPlainText = fNames.ValueAt(new Point(4, iRow)).Trim();
string sCount = fNames.ValueAt(new Point(5, iRow)).Trim();
if ((iRow % 1000) == 0)
{
txtResponse.AppendText("Row " + iRow + ": Name \"" + sNative + "\"" + Environment.NewLine);
Application.DoEvents();
}
if (string.IsNullOrEmpty(sPlainText))
++iBlanks;
else
{
int iCount;
if (!int.TryParse(sCount, out iCount))
{
txtResponse.AppendText("Invalid count \"" + sCount + "\" found at row " + iRow + Environment.NewLine);
lBogusCounts.Add(iRow);
}
bool bFoundSpecialChar = false;
foreach (char cNext in sPlainText + sHtml)
{
if ((cNext < 0x20) || (cNext > 127))
{
bFoundSpecialChar = true;
if (!lSpecialChars.Contains(cNext))
lSpecialChars.Add(cNext);
}
}
if (bFoundSpecialChar)
{
txtResponse.AppendText("Special char at row " + iRow + ": Web=\"" + sHtml + "\", PlainText=\"" + sPlainText + "\"" + Environment.NewLine);
//txtResponse.AppendText("Web=\"" + sHtml + "\", PlainText=\"" + sPlainText + "\" -> ");
//CharEquivalents.Transliterate(sNative, out sHtml, out sPlainText);
//fNames.SetValueAt(new Point(3, iRow), sHtml);
//fNames.SetValueAt(new Point(4, iRow), sPlainText);
//txtResponse.AppendText("Web=\"" + sHtml + "\", PlainText=\"" + sPlainText + "\"" + Environment.NewLine);
}
//foreach (char cNext in sPlainText.ToCharArray())
//{
// if ((cNext < 0x20) || (cNext > 127))
// {
// if (!lSpecialChars.Contains(cNext))
// lSpecialChars.Add(cNext);
// txtResponse.AppendText("Name with special character (" + sPlainText + ")" +
// "found in plain text string at row " + iRow.ToString() + Environment.NewLine);
// }
//}
//foreach (char cNext in sHtml.ToCharArray())
//{
// if ((cNext < 0x20) || (cNext > 127))
// {
// if (!lSpecialChars.Contains(cNext))
// lSpecialChars.Add(cNext);
// txtResponse.AppendText("Name with special character (" + sHtml + ")" +
// "found in HTML string at row " + iRow.ToString() + Environment.NewLine);
// }
//}
++iNames;
iBlanks = 0;
}
}
txtResponse.AppendText(iNames + " names found in spreadsheet" + Environment.NewLine);
foreach (char cNext in lSpecialChars)
{
txtResponse.AppendText("new TransChar( \"" + cNext + "\", \"&#" + ((int)cNext) + ";\", \"\" )," + Environment.NewLine);
}
foreach (int iNextRow in lBogusCounts)
{
txtResponse.AppendText("Bogus count at row " + iNextRow + Environment.NewLine);
}
// fNames.SaveAs(System.IO.Path.Combine(Utilities.DataFilesFolder, "GivenNamesFemaleTransliterated-2.xls"));
fNames.Close();
}