//Verses 3 : lambat baget, jangan pake
public static ObservableCollection <AyahData> getVerses3(int Surah, int LangId = 11, int verseSize = 30)
{
ObservableCollection <AyahData> SelAyah = new ObservableCollection <AyahData>();
try
{
var lng = getLanguage(LangId);
FungsiDB.KoneksiStr = Conn;
string Qry = string.Format(@"select a.sura,a.aya,a.text,b.content as translit, c.{0} as translate from quran_text a left join transliteration b on a.sura=b.surahidx and a.aya=b.ayahidx
left join quran c on a.aya=c.ayah_id and a.sura=c.surah_id
where a.sura={1} and b.langid={2}", lng.lang, Surah, LangId);
System.Data.DataTable dt = FungsiDB.RetrieveData(Qry);
//dt.TableName = "data";
foreach (System.Data.DataRow dr in dt.Rows)
{
SelAyah.Add(new AyahData()
{
idx = Convert.ToInt32(dr["aya"]), ayah = AyahInArabic[Convert.ToInt32(dr["aya"])], translation = dr["translate"].ToString(), transliteration = dr["translit"].ToString(), content = dr["text"].ToString(), VerseSize = verseSize
});
}
}
catch
{
throw;
}
return(SelAyah);
}
public static Dictionary <int, string> getTranslationDictionary(int LangId, int SurahId)
{
Dictionary <int, string> Data = new Dictionary <int, string>();
try
{
var lng = getLanguage(LangId);
if (lng != null)
{
FungsiDB.KoneksiStr = Conn;
string Qry = string.Format("select surah_id, ayah_id, {0} from quran where surah_id = {1}", lng.lang, SurahId);
System.Data.DataTable dt = FungsiDB.RetrieveData(Qry);
//dt.TableName = "data";
if (dt != null)
{
foreach (System.Data.DataRow dr in dt.Rows)
{
TranslationData item = new TranslationData()
{
ayah_id = Convert.ToInt32(dr["ayah_id"]), surah_id = Convert.ToInt32(dr["surah_id"]), content = dr[lng.lang].ToString()
};
Data.Add(item.ayah_id, item.content);
}
}
}
}
catch
{
throw;
}
return(Data);
}
public static void updateVerse(int Surah, int Ayah, string LangName, string Content)
{
try
{
FungsiDB.KoneksiStr = Conn;
//update
Content = Content.Replace("'", "''");
string Qry = string.Format("update quran set {0}='{1}' where surah_id = {2} and ayah_id= {3}", LangName, Content, Surah, Ayah);
FungsiDB.ExecuteNonQuery(Qry);
/*
* var connection = new SQLiteConnection(Conn);
* using (var context = new DataContext(connection))
* {
* Content = Content.Replace("'","''");
*
* var sel = (from a in context.GetTable<quran>()
* where a.surah_id == Surah && a.ayah_id == Ayah
* select a).ToList();
* if (sel.Count > 0)
* {
* //update
* string Qry = string.Format("update quran set {0}='{1}' where surah_id = {2} and ayah_id= {3}",LangName,Content,Surah,Ayah);
* FungsiDB.ExecuteNonQuery(Qry);
* }
* else
* {
* //insert
*
* string Qry = string.Format("insert into quran (surah_id,ayah_id,{0}) values ({1},{2},'{3}')", LangName, Surah, Ayah,Content);
* FungsiDB.ExecuteNonQuery(Qry);
* }
*
* }
* connection.Close();*/
}
catch
{
throw;
}
}
public static void ReadQuranWithFungsiDB()
{
List <quran_text> data = new List <quran_text>();
try
{
FungsiDB.KoneksiStr = Conn;
string Qry = string.Format("select * from quran_text where sura=2");
System.Data.DataTable dt = FungsiDB.RetrieveData(Qry);
dt.TableName = "data";
foreach (System.Data.DataRow dr in dt.Rows)
{
data.Add(new quran_text()
{
aya = Convert.ToInt32(dr["aya"]), sura = Convert.ToInt32(dr["sura"]), index = Convert.ToInt32(dr["index"]), text = dr["text"].ToString()
});
}
}
catch
{
throw;
}
}
public static ObservableCollection <SearchItem> searchByKeyword(string Keyword, int HadithId, int PageNo, int ChapterNo, int Limit = 100)
{
ObservableCollection <SearchItem> data = null;
try
{
var connection = new SQLiteConnection(Conn);
//get all hadith with existing chapter
using (var context = new DataContext(connection))
{
string AddQuery = HadithId == -999 ? "" : string.Format(" and a.hadithid = {0}", HadithId);
AddQuery += PageNo == -999 ? "" : string.Format(" and a.pageno = {0}", PageNo);
AddQuery += ChapterNo == -999 ? "" : string.Format(" and a.chapterno = {0}", ChapterNo);
FungsiDB.KoneksiStr = Conn;
string Qry = string.Format(@"select a.ChapterNo, d.Title as ChapterTitle, a.ContentID, b.HadithID, a.HadithOrder, b.Title as HadithTitle, c.PageNo, c.Title as PageTitle, a.Reference, c.TitleArabic as PageArabic from hadithcontent a
left outer join hadith b on a.hadithid = b.hadithid
left outer join hadithpage c on a.hadithid = c.hadithid and a.pageno = c.pageno
left outer join hadithchapter d on a.hadithid = d.hadithid and a.pageno = d.pageno and a.chapterno = d.chapterno
where ( a.ContentEnglish like '%{0}%' or a.ContentIndonesia like '%{0}%' or a.ContentUrdu like '%{0}%' or a.ContentArabic like '%{0}%'){2} limit {1}", Keyword, Limit, AddQuery);
System.Data.DataTable dt = FungsiDB.RetrieveData(Qry);
//var xx = dt.Rows.Count;
dt.TableName = "data";
if (dt != null && dt.Rows.Count > 0)
{
int counter = 0;
data = (from a in dt.AsEnumerable()
select new SearchItem
{
ChapterNo = (int)a.Field <long>("ChapterNo"),
HadithOrder = (int)a.Field <long>("HadithOrder"),
PageNo = (int)a.Field <long>("PageNo"),
Reference = a.Field <string>("Reference"),
ChapterTitle = a.Field <string>("ChapterTitle"),
ContentId = (int)a.Field <long>("ContentId"),
HadithId = (int)a.Field <long>("HadithId"),
HadithTitle = a.Field <string>("HadithTitle"),
IndexNo = ++counter,
PageArabic = a.Field <string>("PageArabic"),
PageTitle = a.Field <string>("PageTitle")
}).ToObservableCollection();
/*
* if (items != null)
* {
* int counter = 0;
* data = (from a in items
* join b in context.GetTable<hadith>().DefaultIfEmpty() on a.HadithID equals b.HadithID
* join c in context.GetTable<hadithpage>().DefaultIfEmpty() on new { a.HadithID, a.PageNo } equals new { c.HadithID, c.PageNo }
* join d in context.GetTable<hadithchapter>().DefaultIfEmpty() on new { a.HadithID, a.PageNo, a.ChapterNo } equals new { d.HadithID, d.PageNo, d.ChapterNo }
* select new SearchItem { ChapterNo = d.ChapterNo, ChapterTitle = d.Title.Trim(), ContentId = a.ContentID, HadithId = b.HadithID, HadithOrder = a.HadithOrder, HadithTitle = b.Title, PageNo = c.PageNo, PageTitle = c.Title, IndexNo = ++counter, Reference=a.Reference, PageArabic=c.TitleArabic }).ToObservableCollection();
* }*/
}
}
}
catch (Exception ex)
{
Logs.WriteLog(ex.Message + " - " + ex.StackTrace);
}
return(data);
}
