public int DeletePhrase(Phrase item)
{
int r = 0;
lock (locker) {
if (item.id != 0) {
var connection = new SqliteConnection ("Data Source=" + path);
connection.Open ();
using (var command = connection.CreateCommand ()) {
command.CommandText = "Select * from [Phrase] where [id] = ?";
command.Parameters.Add (new SqliteParameter (DbType.String) { Value = item.id });
var result = command.ExecuteReader ();
if (result.HasRows) {
command.Dispose ();
command.CommandText = "Delete from [Phrase] where [id] = ?";
command.Parameters.Add(new SqliteParameter (DbType.String) { Value = item.categoryId });
r = command.ExecuteNonQuery ();
}
connection.Close ();
}
}
}
return r;
}
public int SavePhrase(Phrase item)
{
int r;
lock (locker) {
if (item.id != 0) {
var connection = new SqliteConnection ("Data Source=" + path);
connection.Open ();
using (var command = connection.CreateCommand ()) {
command.CommandText = "UPDATE [Phrase] SET [sourcePhrase] = ?, [targetPhrase] = ? WHERE [id] = ?";
command.Parameters.Add (new SqliteParameter (DbType.String) { Value = item.sourcePhrase });
command.Parameters.Add (new SqliteParameter (DbType.String) { Value = item.targetPhrase });
r = command.ExecuteNonQuery ();
}
connection.Close ();
return r;
} else {
var connection = new SqliteConnection ("Data Source=" + path);
connection.Open ();
using (var command = connection.CreateCommand ()) {
command.CommandText = "INSERT INTO [Phrase] ([collectorId], [speakerId], [categoryId], " +
"[sourcePhrase], [targetPhrase], [orthography], [audioURL], [notes]) VALUES (? ,? ,? ,? ,? ,? ,? ,?)";
command.Parameters.Add (new SqliteParameter (DbType.Int16) { Value = item.collectorId });
command.Parameters.Add (new SqliteParameter (DbType.Int16) { Value = item.speakerId });
command.Parameters.Add (new SqliteParameter (DbType.Int16) { Value = item.categoryId });
command.Parameters.Add (new SqliteParameter (DbType.String) { Value = item.sourcePhrase });
command.Parameters.Add (new SqliteParameter (DbType.String) { Value = item.targetPhrase });
command.Parameters.Add (new SqliteParameter (DbType.String) { Value = item.orthography });
command.Parameters.Add (new SqliteParameter (DbType.String) { Value = item.audioURL });
command.Parameters.Add (new SqliteParameter (DbType.String) { Value = item.notes });
r = command.ExecuteNonQuery ();
connection.Close ();
}
return r;
}
}
}
public static int DeletePhrase(Phrase item)
{
return PhraseRepository.DeletePhrase (item);
}
public static int UpdatePhrase(Phrase item)
{
return PhraseRepository.UpdatePhrase (item);
}
public static int SavePhrase(Phrase item)
{
return PhraseRepository.SavePhrase (item);
}
private void SaveNewPhrase()
{
UIView[] views = this.TableView.Subviews [0].Subviews;
Phrase newPhrase = new Phrase ();
for (int i = 0; i < views.Length; i++) {
if (views [i].GetType ().ToString ().Equals ("Ma.DataInput")) {
DataInput dataInputCell = (DataInput)views [i];
switch (dataInputCell.title.ToLower ()) {
case "english":
newPhrase.sourcePhrase = dataInputCell.GetInputValue ();
break;
case "mawng":
newPhrase.targetPhrase = dataInputCell.GetInputValue ();
break;
default:
break;
}
} else if (views [i].GetType ().ToString ().Equals ("Ma.AudioInput")) {
AudioInput audioInputCell = (AudioInput)views [i];
if (audioInputCell.audioFilePath == null) {
newPhrase.audioURL = string.Empty;
} else {
newPhrase.audioURL = audioInputCell.audioFilePath.RelativePath;
}
}
}
newPhrase.categoryId = this.categoryId;
newPhrase.collectorId = 1;
newPhrase.speakerId = 1;
if (string.IsNullOrEmpty (newPhrase.sourcePhrase) && string.IsNullOrEmpty (newPhrase.targetPhrase)) {
var okCancelAlertController = UIAlertController.Create ("Warning", "Please type in both Mawng phrase and English phrase", UIAlertControllerStyle.Alert);
okCancelAlertController.AddAction(UIAlertAction.Create("OK", UIAlertActionStyle.Default, null));
PresentViewController(okCancelAlertController, true, null);
} else {
PhraseManager.SavePhrase (newPhrase);
this.NavigationController.PopViewController (true);
}
}
public EditMyPhraseScreen(Phrase currentMyPhrase)
{
this.currentMyPhrase = currentMyPhrase;
}
private void UpdatePhrase()
{
UIView[] views = this.TableView.Subviews [0].Subviews;
Phrase newPhrase = new Phrase ();
for (int i = 0; i < views.Length; i++) {
if (views [i].GetType ().ToString ().Equals ("Ma.DataInput")) {
DataInput dataInputCell = (DataInput)views [i];
switch (dataInputCell.title.ToLower ()) {
case "english":
newPhrase.sourcePhrase = dataInputCell.GetInputValue ();
break;
case "mawng":
newPhrase.targetPhrase = dataInputCell.GetInputValue ();
break;
default:
break;
}
} else if (views [i].GetType ().ToString ().Equals ("Ma.AudioInput")) {
AudioInput audioInputCell = (AudioInput)views [i];
if (audioInputCell.audioFilePath == null) {
newPhrase.audioURL = string.Empty;
} else {
newPhrase.audioURL = audioInputCell.audioFilePath.RelativePath;
}
}
}
newPhrase.id = currentMyPhrase.id;
PhraseManager.UpdatePhrase (newPhrase);
this.NavigationController.PopViewController (true);
}
public static int UpdatePhrase(Phrase item)
{
return pq.UpdatePhrase (item);
}
public static int SavePhrase(Phrase item)
{
return pq.SavePhrase (item);
}
public static int DeletePhrase(Phrase item)
{
return pq.DeletePhrase (item);
}
private Phrase FromReader(SqliteDataReader r)
{
var c = new Phrase ();
c.id = Convert.ToInt32 (r ["id"]);
c.collectorId = Convert.ToInt32 (r ["collectorId"]);
c.speakerId = Convert.ToInt32 (r ["speakerId"]);
c.categoryId = Convert.ToInt32 (r ["categoryId"]);
c.sourcePhrase = r ["sourcePhrase"].ToString ();
c.targetPhrase = r ["targetPhrase"].ToString ();
c.orthography = r ["orthography"].ToString ();
c.audioURL = r ["audioURL"].ToString ();
c.notes = r ["notes"] == null ? string.Empty : r ["notes"].ToString ();
if (r ["savedDate"].ToString () == string.Empty) {
c.savedDate = null;
} else {
c.savedDate = Convert.ToDateTime (r ["savedDate"].ToString ());
}
if (r ["latitude"].ToString () == string.Empty) {
c.latitude = 0;
} else {
c.latitude = (float)Convert.ToDecimal(r ["latitude"]);
}
if (r ["longitude"].ToString () == string.Empty) {
c.longtitude = 0;
} else {
c.latitude = (float)Convert.ToDecimal(r ["longitude"]);
}
c.recordingDevice = r ["recordingDevice"].ToString ();
return c;
}
public int UpdatePhrase(Phrase item)
{
int r = 0;
lock(locker) {
if (item.id != 0) {
var connection = new SqliteConnection ("Data Source=" + path);
connection.Open ();
using (var command = connection.CreateCommand ()) {
command.CommandText = "Update [Phrase] Set [audioURL] = ?, [sourcePhrase] = ?, [targetPhrase] = ? where [id] = ?";
command.Parameters.Add (new SqliteParameter (DbType.String) { Value = item.audioURL });
command.Parameters.Add (new SqliteParameter (DbType.String) { Value = item.sourcePhrase });
command.Parameters.Add (new SqliteParameter (DbType.String) { Value = item.targetPhrase });
command.Parameters.Add (new SqliteParameter (DbType.String) { Value = item.id });
r = command.ExecuteNonQuery();
}
connection.Close();
}
}
return r;
}
