/// <summary>
/// Protokolliert die Weiterleitung einer Nachricht
/// </summary>
/// <param name="recMsgId">Id der Nachricht, die weitergeleitet wurde</param>
/// <param name="sentToId">Id des Kontakts, an den die Nachricht gesendet wurde</param>
/// <param name="way">Sendeweg (SMS, Email)</param>
public int InsertLogSent(int recMsgId, int sentToId, SendToWay way)
{
try
{
using (var connection = new SqliteConnection(DataSource))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "INSERT INTO \"LogSent\" (\"LogRecievedId\", \"SentTime\", \"SentToId\", \"SentVia\") " +
"VALUES (@msgId, CURRENT_TIMESTAMP, @sentToId, @sendWay);" +
"SELECT Id FROM \"LogSent\" ORDER BY \"SentTime\" DESC LIMIT 1";
command.Parameters.AddWithValue("@msgId", recMsgId);
command.Parameters.AddWithValue("@sentToId", sentToId);
command.Parameters.AddWithValue("@sendWay", (int)way);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
//Lese Eintrag
if (int.TryParse(reader.GetString(0), out int sendId))
{
return(sendId);
}
}
}
}
}
catch (Exception)
{
throw new Exception("Sql-Fehler InsertLogSent()");
}
return(0);
}
/// <summary>
/// Ändert den Eintrag für einen Kontakt.
/// Übergebene Parameter mit Leerstring bzw. 0 werden nicht gegändert.
/// </summary>
/// <param name="contactId"></param>
/// <param name="sendWay">MelBoxSql.SendToWay Sendeweg</param>
/// <param name="name">Anzeigename</param>
/// <param name="companyId">Id der Firma</param>
/// <param name="email"></param>
/// <param name="phone"></param>
/// <param name="keyWord">Leerstring wird ignoriert</param>
public void UpdateContact(int contactId, SendToWay sendWay, string name = "", int companyId = 0, string email = "", ulong phone = 0, string keyWord = "")
{
//nicht schön: für jede Änderung ein eigener Schreibvorgang
try
{
using (var connection = new SqliteConnection(DataSource))
{
connection.Open();
var command = connection.CreateCommand();
if (sendWay != 0)
{
command.CommandText = "UPDATE \"Contact\" SET \"SendWay\" = @value WHERE \"Id\" = @contactId;";;
command.Parameters.AddWithValue("@contactId", contactId);
command.Parameters.AddWithValue("@value", sendWay);
command.ExecuteNonQuery();
}
if (name.Length > 3)
{
command.CommandText = "UPDATE \"Contact\" SET \"Name\" = @value WHERE \"Id\" = @contactId;";;
command.Parameters.AddWithValue("@contactId", contactId);
command.Parameters.AddWithValue("@value", name);
command.ExecuteNonQuery();
}
if (companyId > 0)
{
command.CommandText = "UPDATE \"Contact\" SET \"CompanyId\" = @value WHERE \"Id\" = @contactId;";;
command.Parameters.AddWithValue("@contactId", contactId);
command.Parameters.AddWithValue("@value", companyId);
command.ExecuteNonQuery();
}
if (email.Length > 3 && IsEmail(email))
{
command.CommandText = "UPDATE \"Contact\" SET \"Email\" = @value WHERE \"Id\" = @contactId;";;
command.Parameters.AddWithValue("@contactId", contactId);
command.Parameters.AddWithValue("@value", email);
command.ExecuteNonQuery();
}
if (phone > 0)
{
command.CommandText = "UPDATE \"Contact\" SET \"Phone\" = @value WHERE \"Id\" = @contactId;";;
command.Parameters.AddWithValue("@contactId", contactId);
command.Parameters.AddWithValue("@value", phone);
command.ExecuteNonQuery();
}
if (keyWord == null || keyWord.Length > 0) //Leerstring als KeyWord nicht zulässig, aber NULL
{
command.CommandText = "UPDATE \"Contact\" SET \"KeyWord\" = @value WHERE \"Id\" = @contactId;";;
command.Parameters.AddWithValue("@contactId", contactId);
command.Parameters.AddWithValue("@value", keyWord ?? "NULL"); //Wenn keyWord == null Dann string "NULL"
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw new Exception("Sql-Fehler UpdateContact()\r\n" + ex.Message);
}
}