public static List <BibleSearchResult> Search(List <string> searchTerms)
{
if (searchTerms.Count < 1)
{
throw (new ApplicationException("No search terms"));
}
// Double check sql
string p = ",./<>?[]\\{}|!@#$%^&*()-=_+:;'\"";
foreach (string str in searchTerms)
{
if (str.IndexOfAny(p.ToCharArray()) != -1)
{
throw (new ApplicationException("Malformed sql passed to Search"));
}
}
// Build the query
string query = "SELECT \"VERSION\", \"REFBOOK\", \"REFCHAPTER\", \"REFVERSE\", \"DATA\", \"BOOK\", \"CHAPTER\", \"VERSE\" FROM \"BIBLEVERSES\" WHERE ";
foreach (string s in searchTerms)
{
query += "(\"DATA\" LIKE '%" + s + "%') AND ";
}
query = query.Remove(query.Length - 4);
// Query the database
List <BibleSearchResult> lResults = new List <BibleSearchResult>();
using (FBirdTask t = new FBirdTask())
{
t.CommandText = query;
t.ExecuteReader();
int cutoff = 250;
while (t.DR.Read() && cutoff > 0)
{
cutoff--;
BibleSearchResult r = new BibleSearchResult();
BibleVerse bv = new BibleVerse();
bv.RefVersion = t.GetString(0);
bv.RefBook = t.GetString(1);
bv.RefChapter = t.GetInt32(2);
bv.RefVerse = t.GetInt32(3);
bv.Text = t.GetString(4);
bv.SecondaryBook = t.GetString(5);
bv.SecondaryChapter = t.GetInt32(6);
bv.SecondaryVerse = t.GetInt32(7);
r.bibVerse = bv;
r.searchResult = bv.Text;
lResults.Add(r);
}
}
return(lResults);
}
public static void LoadVerses(List <SongVerse> listToLoad, int autoNumber, bool removeFormatting)
{
listToLoad.Clear();
using (FBirdTask t = new FBirdTask())
{
t.CommandText =
"SELECT [AutoNumber], [IsChorus], [Verse], [OrderNum] " +
"FROM [SongVerses] " +
"WHERE [AutoNumber] = @AutoNumber " +
"ORDER BY [OrderNum]";
t.Parameters.Add("@AutoNumber", FbDbType.Integer);
t.Parameters["@AutoNumber"].Value = autoNumber;
t.ExecuteReader();
int verseNumber = 1;
while (t.DR.Read())
{
bool isChorus = t.GetInt32(1) == 1;
int vnum = isChorus ? -1 : verseNumber;
string verseData = t.GetString(2);
if (removeFormatting)
{
verseData = RemoveVerseFormatting(verseData);
}
SongVerse sv = new SongVerse(isChorus, verseData, vnum);
listToLoad.Add(sv);
if (!isChorus)
{
verseNumber++;
}
}
}
}
private void UpdateVerseList()
{
if (lbBibleBook == null || lbBibleBook.SelectedIndex == -1 || lbBibleChapter.SelectedIndex == -1)
{
return;
}
// Get the data from db & load into the list
lbBibleVerse.SuspendLayout();
lbBibleVerse.Items.Clear();
using (FBirdTask t = new FBirdTask())
{
t.CommandText = "SELECT \"DATA\" FROM \"BIBLEVERSES\" WHERE " +
"\"VERSION\" = '" + Program.ConfigHelper.BiblePrimaryTranslation + "' AND " +
"\"REFBOOK\" = '" + ((BookData)lbBibleBook.SelectedItem).mappingName + "' AND " +
"\"REFCHAPTER\" = " + lbBibleChapter.SelectedItem.ToString() + " " +
"ORDER BY \"REFBOOK\", \"REFCHAPTER\", \"REFVERSE\"";
t.ExecuteReader();
int i = 0;
while (t.DR.Read())
{
i++;
VerseData vd = new VerseData(t.GetString(0), i);
lbBibleVerse.Items.Add(vd);
}
}
lbBibleVerse.ResumeLayout();
}
public static List <SongSearchResult> Search(List <string> searchTerms)
{
if (searchTerms.Count < 1)
{
throw (new ApplicationException("No search terms"));
}
// Double check sql
string p = ",./<>?[]\\{}|!@#$%^&*()-=_+:;'\"";
foreach (string str in searchTerms)
{
if (str.IndexOfAny(p.ToCharArray()) != -1)
{
throw (new ApplicationException("Malformed sql passed to Search"));
}
}
// Build the query
string query = "SELECT \"SongVerses\".\"AutoNumber\", \"Songs\".\"Number\", \"SongVerses\".\"Verse\", \"SongVerses\".\"OrderNum\", \"SongVerses\".\"IsChorus\" " +
"FROM \"SongVerses\" INNER JOIN \"Songs\" ON \"SongVerses\".\"AutoNumber\" = \"Songs\".\"AutoNumber\" WHERE ";
foreach (string s in searchTerms)
{
query += "(\"Verse\" LIKE '%" + s + "%') AND ";
}
query = query.Remove(query.Length - 4);
query += "ORDER BY \"Number\"";
// Query the database
List <SongSearchResult> lResult = new List <SongSearchResult>();
using (FBirdTask t = new FBirdTask())
{
t.CommandText = query;
t.ExecuteReader();
int cutoff = 100;
while (t.DR.Read() && cutoff > 0)
{
cutoff--;
SongSearchResult ssr = new SongSearchResult();
ssr.autoNumber = t.GetInt32(0);
ssr.songNumber = t.GetInt32(1);
ssr.verseData = t.GetString(2);
ssr.isAtStart = t.GetInt32(3) == 0;
ssr.isChorus = t.GetBoolean(4);
ssr.verseData = SongProject.RemoveVerseFormatting(ssr.verseData);
ssr.searchResult = ssr.verseData;
lResult.Add(ssr);
}
}
return(lResult);
}
private void LoadSongNote(int songNumber)
{
dt = new PresenterDataset.SongNotesDataTable();
SNdataView = new DataView();
this.Controls.Add(SndataGrid);
BindingSource source = new BindingSource();
source.DataSource = SNdataView;
SndataGrid.DataSource = SNdataView;
int SongId = 5;
using (FBirdTask t = new FBirdTask())
{
t.CommandText = "SELECT [SongNotes].[Number], [SongNotes].[Note], [SongNotes].[IdSong] FROM [SongNotes]" +
" where [SongNotes].[IdSong]=" + songNumber;
t.ExecuteReader();
if (t.DR != null)
{
int rowNumber = 1;
while (t.DR.Read())
{
sRow = dt.NewSongNotesRow();
sRow.Number = t.GetInt32(0); // rowNumber;
sRow.Note = t.GetString(1);
// sRow.SongNumber= t.GetInt32(2);
dt.AddSongNotesRow(sRow);
rowNumber++;
}
t.DR.Close();
SNdataView.BeginInit();
SNdataView.Table = dt;
SNdataView.EndInit();
}
else
{
sRow = dt.NewSongNotesRow();
sRow.Note = "";
dt.AddSongNotesRow(sRow);
}
rowCount = dt.Rows.Count;
}
}
private void InternalLoadSongFromDB(PresenterDataset.SongsRow row, bool isNew)
{
dt = new PresenterDataset.SongVersesDataTable();
dt.Columns["AutoNumber"].DefaultValue = row.AutoNumber;
if (!isNew)
{
using (FBirdTask t = new FBirdTask())
{
t.CommandText =
"SELECT [AutoNumber], [IsChorus], [Verse], [OrderNum] " +
"FROM [SongVerses] " +
"WHERE [AutoNumber] = @AutoNumber " +
"ORDER BY [OrderNum]";
t.Parameters.Add("@AutoNumber", FbDbType.Integer);
t.Parameters["@AutoNumber"].Value = row.AutoNumber;
PresenterDataset.SongVersesRow r;
t.ExecuteReader();
while (t.DR.Read())
{
r = dt.NewSongVersesRow();
r.AutoNumber = t.GetInt32(0);
r.IsChorus = t.GetInt32(1) == 1 ? true : false;
r.Verse = t.GetString(2).Replace("\r\n", "\n").Replace("\n", "\r\n");
r.OrderNum = t.GetInt32(3);
dt.AddSongVersesRow(r);
}
}
}
dt.AcceptChanges();
if (dt.Count == 0)
{
btnSave.Enabled = false;
}
dataView1.BeginInit();
dataView1.Table = dt;
dataView1.EndInit();
dataGrid1.TableStyles[0].MappingName = dt.TableName;
}
// Database support
public static PresenterFont GetFontFromDatabase(int id)
{
PresenterFont font = new PresenterFont();
using (FBirdTask t = new FBirdTask())
{
t.CommandText =
"SELECT [FONTNAME], [SIZEINPOINTS], [COLOR], [VERTICALALIGNMENT], [HORIZONTALALIGNMENT], [OUTLINE], [SHADOW], [OUTLINECOLOR], [SHADOWCOLOR], [ITALIC], [BOLD], [DOUBLESPACE] " +
"FROM [PPTFONT] " +
"WHERE [AUTONUMBER] = @AUTONUMBER";
t.AddParameter("@AUTONUMBER", id);
t.ExecuteReader();
if (t.DR.Read())
{
font.FontName = t.GetString(0);
font.SizeInPoints = t.GetInt32(1);
font.Color = t.GetColor(2, Color.Black);
switch (t.GetInt32(3))
{
case 0:
font.VerticalAlignment = VerticalAlignment.Top;
break;
case 1:
font.VerticalAlignment = VerticalAlignment.Middle;
break;
case 2:
font.VerticalAlignment = VerticalAlignment.Bottom;
break;
default:
break;
}
switch (t.GetInt32(4))
{
case 0:
font.HorizontalAlignment = HorizontalAlignment.Left;
break;
case 2:
font.HorizontalAlignment = HorizontalAlignment.Center;
break;
case 1:
font.HorizontalAlignment = HorizontalAlignment.Right;
break;
default:
break;
}
font.Outline = t.GetBoolean(5);
font.Shadow = t.GetBoolean(6);
font.OutlineColor = t.GetColor(7, Color.White);
font.ShadowColor = t.GetColor(8, Color.Gray);
font.Italic = t.GetBoolean(9);
font.Bold = t.GetBoolean(10);
font.DoubleSpace = t.GetBoolean(11);
}
return(font);
}
}
private void LoadBibDat(BibleVerse verse)
{
// Understanding the databse
// SELECT FIRST 10 * FROM BIBLEVERSES
// SELECT DISTINCT VERSION FROM BIBLEVERSES
// SELECT DISTINCT BOOK FROM BIBLEVERSES
// Verses are queried in the number scheme of the primary translation
// KJV Psalms 23 == RST Psalms 22
// SELECT FIRST 10 * FROM BIBLEVERSES AS PRI WHERE PRI.VERSION = 'RST' AND PRI.BOOK = 'Psalms'
// Old implementation of the Bibleverse table
// KJV | Ref book = original KJV numbering | Book = RST equivalent numbering
// RST | Ref book = original RST numbering | Book = KJV equivalent numbering
// One advantage of the old method is that ability to preload all the version of a chapter relative to the primary translation
// PENDING: This code needs to be refactored to use a universal numbering scheme
// https://www.biblegateway.com/passage/?search=Psalm+23&version=RUSV
// KJV | Ref book = KJV numbering | Book = KJV numbering
// RST | Ref book = KJV numbering | Book = RST numbering
List <string> translations = TranslationList();
List <BibleVerse> verses = new List <BibleVerse>();
bibVerses.Clear();
currentVerseNum = verse.RefVerse;
using (FBirdTask t = new FBirdTask())
{
string select = "SELECT ";
for (int tnum = 1; tnum <= translations.Count; tnum++)
{
if (tnum > 1)
{
select += ", ";
}
select += string.Format("T{0}.REFBOOK, T{0}.REFCHAPTER, T{0}.REFVERSE, T{0}.DATA", tnum);
}
string from = " FROM BIBLEVERSES AS T1";
for (int transIx = 1; transIx < translations.Count; transIx++)
{
bool doMaping = translations[0] == "KJV" || translations[transIx] == "KJV";
if (doMaping)
{
from += string.Format(" LEFT JOIN BIBLEVERSES AS T{0} ON T1.BOOK = T{0}.REFBOOK AND T1.CHAPTER = T{0}.REFCHAPTER AND T1.VERSE = T{0}.REFVERSE", (transIx + 1));
}
else
{
from += string.Format(" LEFT JOIN BIBLEVERSES AS T{0} ON T1.REFBOOK = T{0}.REFBOOK AND T1.REFCHAPTER = T{0}.REFCHAPTER AND T1.REFVERSE = T{0}.REFVERSE", (transIx + 1));
}
}
string where = " WHERE T1.VERSION = @Version1 AND T1.REFBOOK = @Book AND T1.REFCHAPTER = @Chapter";
for (int transIx = 1; transIx < translations.Count; transIx++)
{
where += string.Format(" AND T{0}.VERSION = @Version{0}", (transIx + 1));
}
string order = " ORDER BY T1.REFBOOK, T1.REFCHAPTER, T1.REFVERSE, T1.VERSE";
t.CommandText = select + from + where + order;
t.Command.Parameters.Add("@Book", FbDbType.VarChar, 50).Value = verse.RefBook;
t.Command.Parameters.Add("@Chapter", FbDbType.Integer).Value = verse.RefChapter;
for (int tnum = 1; tnum <= translations.Count; tnum++)
{
t.Command.Parameters.Add("@Version" + tnum, FbDbType.VarChar, 10).Value = translations[tnum - 1];
}
t.ExecuteReader();
while (t.DR.Read())
{
int ix = 0;
BibleVerse bVerse = new BibleVerse();
bVerse.RefVersion = translations[0];
bVerse.RefBook = t.GetString(ix++);
bVerse.RefChapter = t.GetInt32(ix++);
bVerse.RefVerse = t.GetInt32(ix++);
bVerse.Text = t.GetString(ix++);
if (translations.Count > 1)
{
bVerse.SecondaryVersion = translations[1];
bVerse.SecondaryBook = t.GetString(ix++);
bVerse.SecondaryChapter = t.GetInt32(ix++);
bVerse.SecondaryVerse = t.GetInt32(ix++);
bVerse.SecondaryText = t.GetString(ix++);
}
if (translations.Count > 2)
{
bVerse.TertiaryVersion = translations[2];
bVerse.TertiaryBook = t.GetString(ix++);
bVerse.TertiaryChapter = t.GetInt32(ix++);
bVerse.TertiaryVerse = t.GetInt32(ix++);
bVerse.TertiaryText = t.GetString(ix++);
}
verses.Add(bVerse);
}
}
verses.ForEach(v => bibVerses.Add(v.RefVerse, v));
}