/// <summary>
/// Removes command from buffer.
/// </summary>
/// <param name="command"></param>
public void Remove(SerilizeSqlCommand command)
{
lock (ReadWrite)
{
_buffer.Remove(command);
}
}
public void InsertTime(Time time)
{
if (!SqlEnabled)
{
return;
}
Debug.WriteLine("Insert time");
if (time == null)
{
return;
}
try
{
using (SerilizeSqlCommand command = new SerilizeSqlCommand($"INSERT INTO {ToTimeTableName(CurrentUserName)} VALUES(@Date, @TimeIn, @TimeOut, @Details)"))
{
command.AddParameter(new SqlParameter("Date", time.TimeIn.Date));
command.AddParameter(new SqlParameter("TimeIn", time.TimeIn.TimeOfDay));
command.AddParameter(new SqlParameter("TimeOut", time.TimeOut.TimeOfDay));
command.AddParameter(new SqlParameter("Details", ""));
AddCommand(command);
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
throw;
}
}
/// <summary>
/// Adds serializable command to buffer.
/// </summary>
/// <param name="cmd"></param>
public void Add(SerilizeSqlCommand cmd)
{
lock (ReadWrite)
{
_buffer.Add(cmd);
}
}
/// <summary>
/// Inserts day into data base
/// </summary>
/// <param name="day"></param>
public void InsertDay(Day day)
{
Debug.WriteLine("Insert day");
if (day == null)
{
return;
}
try
{
using (SerilizeSqlCommand command = new SerilizeSqlCommand($"INSERT INTO {ToTimeTableName(CurrentUserName)} VALUES(@Date, @TimeIn, @TimeOut, @Details)"))
{
command.AddParameter(new SqlParameter("Date", day.Date));
command.AddParameter(new SqlParameter("TimeIn", new DateTime().TimeOfDay));
command.AddParameter(new SqlParameter("TimeOut", new DateTime().TimeOfDay));
command.AddParameter(new SqlParameter("Details", day.Details));
AddCommand(command);
}
using (SerilizeSqlCommand command = new SerilizeSqlCommand($"INSERT INTO {ToDayTableName(CurrentUserName)} VALUES(@Date, @TimeIn, @TimeOut, @Details)"))
{
command.AddParameter(new SqlParameter("Date", day.Date));
command.AddParameter(new SqlParameter("TimeIn", new DateTime().TimeOfDay));
command.AddParameter(new SqlParameter("TimeOut", new DateTime().TimeOfDay));
command.AddParameter(new SqlParameter("Details", day.Details));
AddCommand(command);
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
throw;
}
}
private void AddToPump(MySqlCommand command)
{
SerilizeSqlCommand serializedSqlCommand = new SerilizeSqlCommand(command.CommandText);
serializedSqlCommand.Type = SerilizeSqlCommand.SqlType.MySql;
foreach (MySqlParameter commandParameter in command.Parameters)
{
serializedSqlCommand.AddParameter(commandParameter);
}
_buffer.Add(serializedSqlCommand);
UpdateChangedEvent?.Invoke(_buffer.Buffer());
if (ServerState == State.Connected)
{
if (UpdateMode == UpdateModes.Async)
{
PumpSqlAsync(_buffer.Buffer());
}
else
{
PumpSql(_buffer.Buffer());
}
}
}
public void RemoveTime(Time time)
{
if (!SqlEnabled)
{
return;
}
Debug.WriteLine("Remove time");
SerilizeSqlCommand command = new SerilizeSqlCommand($@"DELETE FROM {ToTimeTableName(CurrentUserName)} WHERE( Date = '{time.TimeIn.Date}' AND TimeIn = '{time.TimeIn.TimeOfDay}' AND TimeOut = '{time.TimeOut.TimeOfDay}')");
AddCommand(command);
}
/// <summary>
/// Removes all times in this day as well!
/// </summary>
/// <param name="date"></param>
public void RemoveDay(DateTime date)
{
if (!SqlEnabled)
{
return;
}
Debug.WriteLine("Remove day");
var command = new SerilizeSqlCommand($@"DELETE FROM {ToDayTableName(CurrentUserName)} WHERE( Date = '{date}' )");
AddCommand(command);
}
public void SqlUpdateTime(Time prev, Time upd)
{
if (!SqlEnabled)
{
return;
}
Debug.WriteLine("Update time");
using (SerilizeSqlCommand cmd = new SerilizeSqlCommand($"UPDATE {ToTimeTableName(CurrentUserName)} SET Date = '{upd.TimeIn.Date}', TimeIn = '{upd.TimeIn.TimeOfDay}', TimeOut = '{upd.TimeOut.TimeOfDay}' WHERE( Date = '" + prev.TimeIn.Date + "' AND TimeIn = '" + prev.TimeIn.TimeOfDay + "' AND TimeOut = '" + prev.TimeOut.TimeOfDay + "')"))
{
AddCommand(cmd);
}
}
/// <summary>
/// Add command to list and trys to flush commands
/// </summary>
/// <param name="command"></param>
private void AddCommand(SerilizeSqlCommand command)
{
command.CommandAddTime = DateTime.Now;
new Thread(() =>
{
lock (SqlServerLock) // Moving the lock here as keeps the SerilizableCommands list from changing while flushing. THE ONLY PLACE FLUSHCOMMANDS IS CALLED!
{
Commands.Add(command);
UpdateChangedEvent?.Invoke(Commands);
}
}).Start();
}
public void UpdateDetails(Day day)
{
if (!SqlEnabled)
{
return;
}
Debug.WriteLine("Update details");
if (day.Details == "" && day.Times.Count == 0) // day is removed in local data if no time and details == ""
{
RemoveDay(day.Date);
}
else
{
using (SerilizeSqlCommand cmd = new SerilizeSqlCommand($"UPDATE {ToDayTableName(CurrentUserName)} SET Details = @Details WHERE( Date = '" + day.Date + "' AND TimeIn = '" + new TimeSpan() + "')"))
{
cmd.AddParameter(new SqlParameter("Details", day.Details));
AddCommand(cmd);
}
}
}