private BluecoinDatabase LoadBluecoinDb(IDbConnection connection)
{
var result = new BluecoinDatabase();
var defaultCurrencySetting = connection.Get <SETTINGSTABLE>(1);
if (defaultCurrencySetting == null || string.IsNullOrWhiteSpace(defaultCurrencySetting.defaultSettings) || defaultCurrencySetting.defaultSettings.Length != 3)
{
throw new Exception("Could not find default currency setting.");
}
result.DefaultCurrency = defaultCurrencySetting.defaultSettings;
var accountGroupAssets = connection.Query <ACCOUNTINGGROUPTABLE>(
"SELECT * FROM ACCOUNTINGGROUPTABLE WHERE accountGroupName = 'Assets'").Single();
result.AccountTypeCashID = connection.Query <ACCOUNTTYPETABLE>(
"SELECT * FROM ACCOUNTTYPETABLE WHERE accountTypeName = 'Cash' AND accountingGroupID = @gid",
new { gid = accountGroupAssets.accountingGroupTableID }).Single().accountTypeTableID;
result.SpecialCategories = LoadSpecialCategories(connection);
result.TransactionTypes = LoadTransactionTypes(connection);
return(result);
}
private void ImportAccounts(IDbConnection connection, Database database, BluecoinDatabase bluecoinDatabase)
{
bluecoinDatabase.Accounts = database.Accounts.Select(a => new ACCOUNTSTABLE
{
MmmAccount = a,
accountName = a.Name,
accountCurrency = a.CurrencyCode,
accountTypeID = bluecoinDatabase.AccountTypeCashID,
accountConversionRateNew = _currencyExchange.GetRate(bluecoinDatabase.DefaultCurrency, a.CurrencyCode)
}).ToList();
foreach (var account in bluecoinDatabase.Accounts)
{
var existingAccount = connection.Query <ACCOUNTSTABLE>(
"SELECT * FROM ACCOUNTSTABLE WHERE accountName = @accountName AND accountTypeID = @accountTypeID", account).SingleOrDefault();
if (existingAccount == null)
{
account.accountsTableID = connection.NextID <ACCOUNTSTABLE>();
connection.Insert(account);
}
else
{
if (existingAccount.accountCurrency != account.accountCurrency)
{
throw new Exception($"Currency clash for account {account.accountName}. Source currency: {account.accountCurrency}. Target currency = {existingAccount.accountCurrency}.");
}
account.accountsTableID = existingAccount.accountsTableID;
}
}
}
private void ImportCategories(IDbConnection connection, Database database, BluecoinDatabase bluecoinDatabase)
{
var parentCategories = database.Categories
.Where(c => c.Parent == null && database.Categories.Select(cc => cc.Parent).Contains(c))
.Select(c => new PARENTCATEGORYTABLE
{
categoryGroupId = ResolveParentCategoryGroup(c, database, bluecoinDatabase.SpecialCategories),
parentCategoryName = c.Name,
MmmCategory = c
})
.ToList();
foreach (var category in parentCategories)
{
var existingCategory = connection.Query <PARENTCATEGORYTABLE>(
"SELECT * FROM PARENTCATEGORYTABLE WHERE parentCategoryName = @parentCategoryName", category).SingleOrDefault();
if (existingCategory == null)
{
category.parentCategoryTableId = connection.NextID <PARENTCATEGORYTABLE>();
connection.Insert(category);
}
else
{
category.parentCategoryTableId = existingCategory.parentCategoryTableId;
}
}
bluecoinDatabase.ChildCategories = database.Categories
.Where(c => c.Parent != null || !database.Categories.Select(cc => cc.Parent).Contains(c))
.Select(c => new CHILDCATEGORYTABLE
{
parentCategoryId = ResolveParentCategory(c, database, bluecoinDatabase.SpecialCategories) ?? parentCategories.Where(pc => pc.MmmCategory == c.Parent).Single().parentCategoryTableId,
childCategoryName = c.Name,
childCategoryIcon = "xxx_more_horiz_black_24dp",
MmmCategory = c
})
.ToList();
//Bluecoin does not support using parent categories in transactions
var parentWithTransactions = parentCategories
.Where(c => database.Transactions.Select(t => t.Category).Contains(c.MmmCategory));
foreach (var c in parentWithTransactions)
{
var child = new CHILDCATEGORYTABLE
{
parentCategoryId = c.parentCategoryTableId,
childCategoryName = c.parentCategoryName,
childCategoryIcon = "xxx_more_horiz_black_24dp",
MmmCategory = c.MmmCategory
};
bluecoinDatabase.ChildCategories.Add(child);
}
foreach (var category in bluecoinDatabase.ChildCategories)
{
var existingCategory = connection.Query <CHILDCATEGORYTABLE>(
"SELECT * FROM CHILDCATEGORYTABLE WHERE childCategoryName = @childCategoryName", category).SingleOrDefault();
if (existingCategory == null)
{
category.categoryTableID = connection.NextID <CHILDCATEGORYTABLE>();
connection.Insert(category);
}
else
{
category.categoryTableID = existingCategory.categoryTableID;
}
}
}
private void ImportTransactions(IDbConnection connection, Database database, BluecoinDatabase bluecoinDatabase)
{
foreach (var transaction in database.Transactions)
{
if (transaction.Type == TransactionType.Adjustment)
{
var item = new ITEMTABLE {
itemTableID = connection.NextID <ITEMTABLE>(), itemName = transaction.FromAccount.Name, MmmTransaction = transaction
};
connection.Insert(item);
var tran = new TRANSACTIONSTABLE
{
itemID = item.itemTableID,
transactionTypeID = bluecoinDatabase.TransactionTypes.NewAccount,
MmmTransaction = transaction,
categoryID = bluecoinDatabase.SpecialCategories.NewAccountCatID,
accountID = bluecoinDatabase.Accounts.Single(a => a.MmmAccount == transaction.FromAccount).accountsTableID,
accountPairID = bluecoinDatabase.Accounts.Single(a => a.MmmAccount == transaction.FromAccount).accountsTableID,
notes = transaction.Notes,
status = 2, //TODO: meaning is not clear
deletedTransaction = 6, //TODO: meaning is not clear
date = transaction.Date,
accountReference = 3,
};
SetAmount(tran, transaction.FromAmount, transaction.FromAccount.CurrencyCode, bluecoinDatabase.DefaultCurrency);
tran.transactionsTableID = tran.uidPairID = connection.NextID <TRANSACTIONSTABLE>();
connection.Insert(tran);
}
if (transaction.Type == TransactionType.Transfer)
{
var item = new ITEMTABLE {
itemName = transaction.FromAccount.Name, MmmTransaction = transaction
};
var tran = new TRANSACTIONSTABLE
{
transactionTypeID = bluecoinDatabase.TransactionTypes.Transfer,
MmmTransaction = transaction,
categoryID = bluecoinDatabase.SpecialCategories.TransferCatID,
accountID = bluecoinDatabase.Accounts.Single(a => a.MmmAccount == transaction.FromAccount).accountsTableID,
accountPairID = bluecoinDatabase.Accounts.Single(a => a.MmmAccount == transaction.ToAccount).accountsTableID,
notes = transaction.Notes,
status = 0, //TODO: meaning is not clear
deletedTransaction = 6, //TODO: meaning is not clear
date = transaction.Date,
accountReference = 2 //TODO
};
SetAmount(tran, -transaction.FromAmount, transaction.FromAccount.CurrencyCode, bluecoinDatabase.DefaultCurrency);
tran.itemID = item.itemTableID = connection.NextID <ITEMTABLE>();
connection.Insert(item);
tran.transactionsTableID = tran.transferGroupID = connection.NextID <TRANSACTIONSTABLE>();
connection.Insert(tran);
var tran2 = new TRANSACTIONSTABLE
{
itemID = item.itemTableID,
transactionTypeID = bluecoinDatabase.TransactionTypes.Transfer,
MmmTransaction = transaction,
categoryID = bluecoinDatabase.SpecialCategories.TransferCatID,
accountID = bluecoinDatabase.Accounts.Single(a => a.MmmAccount == transaction.ToAccount).accountsTableID,
accountPairID = bluecoinDatabase.Accounts.Single(a => a.MmmAccount == transaction.FromAccount).accountsTableID,
notes = transaction.Notes,
status = 0, //TODO: meaning is not clear
deletedTransaction = 6, //TODO: meaning is not clear
uidPairID = tran.transactionsTableID,
transferGroupID = tran.transactionsTableID,
date = transaction.Date,
accountReference = 2 //TODO
};
SetAmount(tran2, transaction.ToAmount, transaction.ToAccount.CurrencyCode, bluecoinDatabase.DefaultCurrency);
tran2.transactionsTableID = tran.uidPairID = connection.NextID <TRANSACTIONSTABLE>();
connection.Insert(tran2);
connection.Update <TRANSACTIONSTABLE>(tran);
}
if (transaction.Type == TransactionType.Income)
{
var item = new ITEMTABLE {
itemName = transaction.Notes, MmmTransaction = transaction
};
var tran = new TRANSACTIONSTABLE
{
transactionTypeID = bluecoinDatabase.TransactionTypes.Income,
MmmTransaction = transaction,
categoryID = bluecoinDatabase.ChildCategories.Single(c => c.MmmCategory == transaction.Category).categoryTableID,
accountID = bluecoinDatabase.Accounts.Single(a => a.MmmAccount == transaction.ToAccount).accountsTableID,
accountPairID = bluecoinDatabase.Accounts.Single(a => a.MmmAccount == transaction.ToAccount).accountsTableID,
notes = transaction.Notes,
status = 0, //TODO: meaning is not clear
deletedTransaction = 6, //TODO: meaning is not clear
date = transaction.Date,
accountReference = 1 //TODO
};
SetAmount(tran, transaction.ToAmount, transaction.ToAccount.CurrencyCode, bluecoinDatabase.DefaultCurrency);
tran.itemID = item.itemTableID = connection.NextID <ITEMTABLE>();
connection.Insert(item);
tran.transactionsTableID = tran.uidPairID = connection.NextID <TRANSACTIONSTABLE>();
connection.Insert(tran);
}
if (transaction.Type == TransactionType.Expense)
{
var item = new ITEMTABLE {
itemName = transaction.Notes, MmmTransaction = transaction
};
var tran = new TRANSACTIONSTABLE
{
transactionTypeID = bluecoinDatabase.TransactionTypes.Expense,
MmmTransaction = transaction,
categoryID = bluecoinDatabase.ChildCategories.Single(c => c.MmmCategory == transaction.Category).categoryTableID,
accountID = bluecoinDatabase.Accounts.Single(a => a.MmmAccount == transaction.FromAccount).accountsTableID,
accountPairID = bluecoinDatabase.Accounts.Single(a => a.MmmAccount == transaction.FromAccount).accountsTableID,
notes = transaction.Notes,
status = 0, //TODO: meaning is not clear
deletedTransaction = 6, //TODO: meaning is not clear
date = transaction.Date,
accountReference = 1 //TODO
};
SetAmount(tran, -transaction.FromAmount, transaction.FromAccount.CurrencyCode, bluecoinDatabase.DefaultCurrency);
tran.itemID = item.itemTableID = connection.NextID <ITEMTABLE>();
connection.Insert(item);
tran.transactionsTableID = tran.uidPairID = connection.NextID <TRANSACTIONSTABLE>();
connection.Insert(tran);
}
}
}
