/// <summary>
/// CalendarInfoの新規IDを取得する
/// </summary>
/// <returns></returns>
internal int GetNewCalendarInfoId()
{
try
{
int result = 0;
OnExecute = (SQLiteConnection con, SQLiteTransaction transaction) => {
string sql = "SELECT CASE WHEN MAX(ID) IS NOT NULL THEN MAX(ID) + 1 ELSE 1 END FROM CALENDAR_INFO";
using (SQLiteCommand command = new SQLiteCommand(sql, con))
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
result = int.Parse("" + reader.GetValue(0));
}
}
}
};
Execute(false);
return(result);
}
catch (Exception ex)
{
throw Program.ThrowException(ex);
}
}
public async Task UpdateExpenseCategory_PostsNotification()
{
//Arrange
var messenger = new WeakReferenceMessenger();
var watcher = new MessageWatcher <DatabaseEvent <ExpenseCategory> >();
messenger.Register(watcher);
AutoMocker mocker = new();
using var factory = mocker.WithDbScope(messenger);
var expenseCategory = new ExpenseCategory
{
Account = new Account()
};
using var setupContext = factory.Create();
setupContext.ExpenseCategories.Add(expenseCategory);
await setupContext.SaveChangesAsync();
//Act
using var actContext = factory.Create();
var category = await actContext.ExpenseCategories.FindAsync(expenseCategory.ID);
category.CategoryName += "-Edited";
await actContext.SaveChangesAsync();
//Assert
DatabaseEvent <ExpenseCategory>?message = watcher.Messages.Last();
Assert.AreEqual(expenseCategory.ID, message.Item.ID);
Assert.AreEqual(EventType.Updated, message.Type);
}
private async void OnDatabaseEvent(IONDatabase database, DatabaseEvent de)
{
if (!de.table.Equals(typeof(JobRow)))
{
return;
}
var index = IndexOfJob(JobRecordWithId(de.id)?.data);
if (index < 0)
{
return;
}
switch (de.action)
{
case DatabaseEvent.EAction.Deleted:
records.RemoveAt(index);
NotifyItemRemoved(index);
break;
case DatabaseEvent.EAction.Inserted:
var job = await ion.database.QueryForAsync <JobRow>(de.id);
ion.PostToMain(() => {
records.Insert(index, new JobRecord(job));
NotifyItemInserted(index);
});
break;
case DatabaseEvent.EAction.Modified:
NotifyItemChanged(index);
break;
}
}
/// <summary>
/// UpdateIgnoreInfoを更新する
/// </summary>
/// <param name="version"></param>
internal void CreateUpdateIgnoreInfo(string version)
{
try
{
OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => {
string sql = "REPLACE INTO UPDATE_IGNORE_INFO (IGNOREVERSION) VALUES (@VERSION)";
using (SQLiteCommand command = new SQLiteCommand(sql, con))
{
try
{
command.Parameters.Add(new SQLiteParameter("@VERSION", version));
command.ExecuteNonQuery();
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
}
}
};
Execute(true);
}
catch (Exception ex)
{
throw Program.ThrowException(ex);
}
}
/// <summary>
/// CalendarInfoを削除する
/// </summary>
/// <param name="id"></param>
internal void DeleteCalendarInfo(int id)
{
try
{
OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => {
try
{
string sql = "DELETE FROM CALENDAR_INFO WHERE ID = @ID";
using (SQLiteCommand command = new SQLiteCommand(sql, con))
{
command.Parameters.Add(new SQLiteParameter("@ID", id));
command.ExecuteNonQuery();
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
};
Execute(true);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 設定値情報を取得する
/// </summary>
/// <returns></returns>
internal Dictionary <int, string> GetSettingInfoAll()
{
try
{
Dictionary <int, string> dic = new Dictionary <int, string>();
OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => {
string sql = "SELECT ID, VALUE FROM SETTING_INFO ORDER BY ID";
using (SQLiteCommand command = new SQLiteCommand(sql, con))
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int id = int.Parse("" + reader.GetValue(0));
string value = "" + reader.GetValue(1);
dic.Add(id, value);
}
}
}
};
Execute(false);
return(dic);
}
catch (Exception ex)
{
throw Program.ThrowException(ex);
}
}
/// <summary>
/// 設定値情報を作成する
/// </summary>
/// <param name="id"></param>
/// <param name="value"></param>
internal void CreateSettingInfo(int id, string value)
{
try
{
OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => {
try
{
string sql = "INSERT OR REPLACE INTO SETTING_INFO (ID, VALUE) VALUES(@ID,@VALUE)";
using (SQLiteCommand command = new SQLiteCommand(sql, con))
{
command.Parameters.Add(new SQLiteParameter("@ID", id));
command.Parameters.Add(new SQLiteParameter("@VALUE", value));
command.ExecuteNonQuery();
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw Program.ThrowException(ex);
}
};
Execute(true);
}
catch (Exception ex)
{
throw Program.ThrowException(ex);
}
}
/// <summary>
/// ExecuteLogを作成する
/// </summary>
/// <param name="calendarModel"></param>
internal void CreateExecuteLog(ExecuteLogModel execModel)
{
try
{
OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => {
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO EXECUTE_LOG (");
sql.Append("ID,");
sql.Append("CDATE,");
sql.Append("EXECID,");
sql.Append("EXECTYPE,");
sql.Append("EXECNAME,");
sql.Append("STARTTIME,");
sql.Append("ENDTIME,");
sql.Append("EXECTIME,");
sql.Append("DESCRIPTION,");
sql.Append("RESULT");
sql.Append(")VALUES(");
sql.Append("@ID,");
sql.Append("@CDATE,");
sql.Append("@EXECID,");
sql.Append("@EXECTYPE,");
sql.Append("@EXECNAME,");
sql.Append("@STARTTIME,");
sql.Append("@ENDTIME,");
sql.Append("@EXECTIME,");
sql.Append("@DESCRIPTION,");
sql.Append("@RESULT");
sql.Append(")");
using (SQLiteCommand command = new SQLiteCommand(sql.ToString(), con))
{
try
{
command.Parameters.Add(new SQLiteParameter("@ID", execModel.Id));
command.Parameters.Add(new SQLiteParameter("@CDATE", execModel.Cdate));
command.Parameters.Add(new SQLiteParameter("@EXECID", execModel.ExecId));
command.Parameters.Add(new SQLiteParameter("@EXECTYPE", execModel.ExecType));
command.Parameters.Add(new SQLiteParameter("@EXECNAME", execModel.ExecName));
command.Parameters.Add(new SQLiteParameter("@STARTTIME", execModel.StartTime));
command.Parameters.Add(new SQLiteParameter("@ENDTIME", execModel.EndTime));
command.Parameters.Add(new SQLiteParameter("@EXECTIME", execModel.ExecTime));
command.Parameters.Add(new SQLiteParameter("@DESCRIPTION", execModel.Description));
command.Parameters.Add(new SQLiteParameter("@RESULT", execModel.Result));
command.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
}
};
Execute(true);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task DeleteAccount_PostsNotification()
{
//Arrange
var mocker = new AutoMocker().WithMessenger();
var watcher = new MessageWatcher <DatabaseEvent <Account> >();
mocker.Get <IMessenger>().Register(watcher);
using var factory = mocker.WithDbScope();
var account1 = new Account();
using var setupContext = factory.Create();
setupContext.Accounts.Add(account1);
await setupContext.SaveChangesAsync();
//Act
using var actContext = factory.Create();
var account = await actContext.Accounts.FindAsync(account1.ID);
actContext.Accounts.Remove(account);
await actContext.SaveChangesAsync();
//Assert
DatabaseEvent <Account>?message = watcher.Messages.Last();
Assert.AreEqual(account1.ID, message.Item.ID);
Assert.AreEqual(EventType.Deleted, message.Type);
}
/// <summary>
/// データベース処理を実行する
/// </summary>
/// <param name="transaction"></param>
public void Execute(bool transaction)
{
try
{
using (SQLiteConnection con = new SQLiteConnection(DATABASE_PATH))
{
con.Open();
if (transaction)
{
using (SQLiteTransaction tran = con.BeginTransaction())
{
OnExecute?.Invoke(con, tran);
OnExecute = null;
}
}
else
{
OnExecute?.Invoke(con, null);
OnExecute = null;
}
}
}
catch (Exception ex)
{
throw Program.ThrowException(ex);
}
}
/// <summary>
/// 実行ログを削除する
/// </summary>
/// <param name="idList"></param>
internal void DeleteExecuteLog(List <string> idList)
{
try
{
OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => {
try
{
string sql = "DELETE FROM EXECUTE_LOG WHERE EXECID IN (";
int i = 0;
foreach (var id in idList)
{
i++;
if (i == 1)
{
sql += "@EXECID" + i;
}
else
{
sql += ",@EXECID" + i;
}
}
sql += ")";
using (SQLiteCommand command = new SQLiteCommand(sql, con))
{
i = 0;
foreach (var id in idList)
{
i++;
command.Parameters.Add(new SQLiteParameter("@EXECID" + i, id));
}
command.ExecuteNonQuery();
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
};
Execute(true);
}
catch (Exception ex)
{
throw ex;
}
}
private void OnDatabaseEvent(IONDatabase db, DatabaseEvent e)
{
switch (e.action)
{
case DatabaseEvent.EAction.Deleted:
if (e.table != typeof(JobRow))
{
return;
}
if ((int)e.id == ion.preferences.job.activeJob)
{
RemoveActiveJob();
}
break;
}
}
public void UpdateDataText(string newText)
{
var oldText = _databaseConnection.Data.Entity;
_databaseConnection.Data.Entity = newText;
var databaseEvent = new DatabaseEvent <TextUpdatedEvent>()
{
Event = new TextUpdatedEvent()
{
OldText = oldText,
NewText = newText
}
};
_eventPublisher.Publish(databaseEvent);
}
/// <summary>
/// カレンダーインフォを作成する
/// </summary>
/// <param name="id"></param>
/// <param name="name"></param>
/// <param name="model"></param>
/// <param name="cdate"></param>
/// <param name="calendarType"></param>
/// <param name="calendarInfoDic"></param>
internal void CreateCalendarInfo(int id, string name, CalendarModel model, DateTime cdate, CalendarType calendarType, Dictionary <int, object[]> calendarInfoDic)
{
try
{
OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => {
try
{
string sql = "INSERT OR REPLACE INTO CALENDAR_INFO (ID, CALENDARNAME, CALENDARVALUE, CDATE, CALENDARTYPE) VALUES(@ID,@CALENDARNAME, @CALENDARVALUE, @CDATE, @CALENDARTYPE)";
using (SQLiteCommand command = new SQLiteCommand(sql, con))
{
command.Parameters.Add(new SQLiteParameter("@ID", id));
command.Parameters.Add(new SQLiteParameter("@CALENDARNAME", name));
command.Parameters.Add(new SQLiteParameter("@CALENDARVALUE", JsonConvert.SerializeObject(model)));
command.Parameters.Add(new SQLiteParameter("@CDATE", cdate));
command.Parameters.Add(new SQLiteParameter("@CALENDARTYPE", calendarType));
command.ExecuteNonQuery();
if (calendarInfoDic.ContainsKey(id))
{
calendarInfoDic[id][0] = name;
calendarInfoDic[id][1] = model;
calendarInfoDic[id][2] = cdate;
calendarInfoDic[id][3] = calendarType;
}
else
{
calendarInfoDic.Add(id, new object[] { name, model, cdate, calendarType });
}
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw Program.ThrowException(ex);
}
};
Execute(true);
}
catch (Exception ex)
{
throw Program.ThrowException(ex);
}
}
internal List <string[]> Get_Project_Macro_InfoAll(ExecDataType execDataType)
{
try
{
List <string[]> resultList = new List <string[]>();
OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => {
StringBuilder sql = new StringBuilder();
string name = "";
switch (execDataType)
{
case ExecDataType.PROJECT:
name = "PROJECT";
break;
case ExecDataType.MACRO:
name = "MACRO";
break;
}
sql.Append("SELECT ID, " + name + "NAME, CDATE FROM " + name + "_INFO ORDER BY " + ((name != "PROJECT") ? name + "NAME" : "CDATE DESC"));
using (SQLiteCommand command = new SQLiteCommand(sql.ToString(), con))
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string id = "" + reader.GetValue(0);
string pName = "" + reader.GetValue(1);
string cdate = "" + reader.GetValue(2);
resultList.Add(new string[] { id, pName, cdate });
}
}
}
};
Execute(false);
return(resultList);
}
catch (Exception ex)
{
throw Program.ThrowException(ex);
}
}
/// <summary>
/// プロジェクト又はマクロを削除する
/// </summary>
/// <param name="type"></param>
/// <param name="projectId"></param>
internal void Delete_Project_Macro_Info(ExecDataType type, string projectId)
{
try
{
OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => {
try
{
StringBuilder sql = new StringBuilder();
string name = "";
switch (type)
{
case ExecDataType.PROJECT:
name = "PROJECT";
break;
case ExecDataType.MACRO:
name = "MACRO";
break;
}
sql.Append("DELETE FROM " + name + "_INFO WHERE ID = @ID");
using (SQLiteCommand command = new SQLiteCommand(sql.ToString(), con))
{
command.Parameters.Add(new SQLiteParameter("@ID", projectId));
command.ExecuteNonQuery();
tran.Commit();
}
}
catch (Exception ex)
{
tran.Rollback();
throw Program.ThrowException(ex);
}
};
Execute(true);
}
catch (Exception ex)
{
throw Program.ThrowException(ex);
}
}
/// <summary>
/// 指定したバージョンのUpdateIgnoreInfoが存在するかの判定
/// </summary>
/// <param name="currentVersion"></param>
/// <returns></returns>
internal bool UpdateIgnoreInfoExists(string currentVersion)
{
try
{
bool hasRow = false;
OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => {
string sql = "SELECT IGNOREVERSION FROM UPDATE_IGNORE_INFO WHERE IGNOREVERSION = @VERSION";
using (SQLiteCommand command = new SQLiteCommand(sql, con))
{
command.Parameters.Add(new SQLiteParameter("@VERSION", currentVersion));
using (SQLiteDataReader reader = command.ExecuteReader())
{
hasRow = reader.HasRows;
}
}
};
Execute(false);
return(hasRow);
}
catch (Exception ex)
{
throw Program.ThrowException(ex);
}
}
/// <summary>
/// プロジェクト又はマクロのValue値を取得する
/// </summary>
/// <param name="name"></param>
/// <param name="projectId"></param>
/// <returns></returns>
internal string Get_Project_Macro_Value(string name, string projectId)
{
try
{
string result = "";
OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => {
try
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT " + name + "VALUE FROM " + name + "_INFO WHERE ID = @ID");
using (SQLiteCommand command = new SQLiteCommand(sql.ToString(), con))
{
command.Parameters.Add(new SQLiteParameter("@ID", projectId));
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
result = "" + reader.GetValue(0);
}
}
}
}
catch (Exception ex)
{
tran.Rollback();
throw Program.ThrowException(ex);
}
};
Execute(false);
return(result);
}
catch (Exception ex)
{
throw Program.ThrowException(ex);
}
}
public void Receive(DatabaseEvent <ExpenseCategory> _) => LoadItemsAsync();
private Microsoft.VisualStudio.Modeling.ModelElement GetParentForDatabaseEvent(DatabaseEvent childElement)
{
return childElement.SubProcess;
}
/// <summary>
/// ProjectInfo又はMacroInfoを作成する
/// </summary>
/// <param name="model"></param>
internal void Create_Project_Module_Info(ProjectModel model)
{
try
{
OnExecute = (SQLiteConnection con, SQLiteTransaction tran) => {
try
{
string jsonString = JsonConvert.SerializeObject(model, new JsonSerializerSettings()
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore,
Formatting = Formatting.Indented
});
StringBuilder sql = new StringBuilder();
string name = "";
switch (model.ExecDataType)
{
case ExecDataType.PROJECT:
name = "PROJECT";
break;
case ExecDataType.MACRO:
name = "MACRO";
break;
}
sql.Append("DELETE FROM " + name + "_INFO WHERE ID = @ID");
using (SQLiteCommand command = new SQLiteCommand(sql.ToString(), con))
{
command.Parameters.Add(new SQLiteParameter("@ID", model.ProjectId));
command.ExecuteNonQuery();
}
sql.Clear();
sql.Append("INSERT INTO " + name + "_INFO(");
sql.Append("ID");
sql.Append("," + name + "NAME");
sql.Append("," + name + "VALUE");
sql.Append(",DESCRIPTION");
sql.Append(",CDATE");
sql.Append(")VALUES(");
sql.Append("@ID");
sql.Append(",@NAME");
sql.Append(",@VALUE");
sql.Append(",@DESCRIPTION");
sql.Append(",@CDATE");
sql.Append(")");
using (SQLiteCommand command = new SQLiteCommand(sql.ToString(), con))
{
command.Parameters.Add(new SQLiteParameter("@ID", model.ProjectId));
command.Parameters.Add(new SQLiteParameter("@NAME", model.Name));
command.Parameters.Add(new SQLiteParameter("@VALUE", jsonString));
command.Parameters.Add(new SQLiteParameter("@DESCRIPTION", model.Description));
command.Parameters.Add(new SQLiteParameter("@CDATE", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")));
command.ExecuteNonQuery();
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
};
Execute(true);
}
catch (Exception ex)
{
throw ex;
}
}
private void OnDatabaseEvent(IONDatabase db, DatabaseEvent e)
{
}
public void Receive(DatabaseEvent <Account> _) => LoadItemsAsync();
