public void Insert(List <MailContentEntity> entities)
{
foreach (var mail in entities)
{
if (!_mailContentEntity.Exists(x => x.Uid == mail.Uid))
{
_mailContentEntity.Insert(mail);
// create index
_mailContentEntity.EnsureIndex(x => x.Uid);;
}
}
}
public async void AddNova_Categoria(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtNome.Text) == true)
{
await DisplayAlert("Atenção!", "Valor inválido", "Cancelar");
return;
}
if (Categorias.Exists(X => X.Nome == txtNome.Text))
{
await DisplayAlert("Atenção!", "Já existe uma categoria com este nome registrado: " + txtNome.Text, "Cancelar");
return;
}
int idCategoria = Categorias.Count() == 0 ? 1 : (int)(Categorias.Max(x => x.CategoriaId) + 1);
Categoria categoria = new Categoria
{
CategoriaId = idCategoria,
Nome = txtNome.Text,
};
Categorias.Insert(categoria);
await Navigation.PopAsync();
}
public async void AddNova_Noticia(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtTitulo.Text) == true)
{
await DisplayAlert("Atenção!", "Valor inválido", "Cancelar");
return;
}
if (Noticias.Exists(X => X.Titulo == txtTitulo.Text))
{
await DisplayAlert("Atenção!", "Já existe uma notícia com este título registrado: " + txtTitulo.Text, "Cancelar");
return;
}
int idNoticia = Noticias.Count() == 0 ? 1 : (int)(Noticias.Max(x => x.NoticiaId) + 1);
Noticia noticia = new Noticia
{
NoticiaId = idNoticia,
categoria = categoriaSelect,
Titulo = txtTitulo.Text,
Descricao = edtDescricao.Text,
DtNoticia = lblDtNoticia.Text,
};
Noticias.Insert(noticia);
await Navigation.PopAsync();
}
public override void ForkBlockIndexes(
Guid sourceChainId,
Guid destinationChainId,
BlockHash branchpoint)
{
LiteCollection <HashDoc> srcColl = IndexCollection(sourceChainId);
if (!srcColl.Exists(_ => true))
{
throw new ChainIdNotFoundException(
sourceChainId,
$"No such chain ID: {sourceChainId}."
);
}
LiteCollection <HashDoc> destColl = IndexCollection(destinationChainId);
BlockHash?genesisHash = IterateIndexes(sourceChainId, 0, 1)
.Cast <BlockHash?>()
.FirstOrDefault();
if (genesisHash is null || branchpoint.Equals(genesisHash))
{
return;
}
destColl.Delete(Query.All());
destColl.InsertBulk(srcColl.FindAll().TakeWhile(i => !i.Hash.Equals(branchpoint)));
AppendIndex(destinationChainId, branchpoint);
}
public static void SyncDatabase(DiscordSocketClient client)
{
_client = client;
_client.JoinedGuild += (guild) => AddGuildToDb(guild.Id);
_client.LeftGuild += (guild) => RemoveGuildFromDb(guild.Id);
Console.WriteLine("Syncing Database...");
// check that database contains all existing guilds
foreach (var guild in _client.Guilds)
{
if (!GuildDatabase.Exists(x => x.Id == guild.Id))
{
Console.WriteLine("found missing guild");
AddGuildToDb(guild.Id);
}
}
// check if the database contains guilds that no longer exist
foreach (var guild in GuildDatabase.FindAll())
{
if (!_client.Guilds.Any(g => g.Id == guild.Id))
{
Console.WriteLine("found non-existent guild");
RemoveGuildFromDb(guild.Id);
}
}
} // end SyncDatabase
public void UpdateTrack(Mediafile file)
{
if (file != null && tracks.Exists(t => t.Path == file.Path))
{
tracks.Update(file);
}
}
/// <summary>
/// A private method to process transactions from API and store as ledgers in local data store.
/// </summary>
/// <param name="txs">Transactions from API</param>
/// <returns>None.</returns>
private async Task ProcessingLedger(ApiTransactionCollections txs)
{
if (txs.txs.Count() > 0)
{
foreach (var tx in txs.txs)
{
tx.blockheight = await api.GetBlockHeight(tx.blockhash);
var record = tx.ToLedger(contract, contractService);
if (record != null && !ledger.Exists(Query.EQ("TxId", record.TxId)))
{
record.Status = ProcessStatus.NotProcessed;
ledger.Upsert(record);
}
}
ledger.EnsureIndex(l => l.TxId);
ledger.EnsureIndex(l => l.Blockheight);
ledger.EnsureIndex(l => l.Operation);
ledger.EnsureIndex(l => l.TokenSenderHash);
ledger.EnsureIndex(l => l.TokenReceiverHash);
ledger.EnsureIndex(l => l.Time);
contract.LastSyncedBlock = txs.txs.Max(t => t.blockheight);
contractService.UpdateContract(contract);
}
}
/// <inheritdoc/>
public override void ForkStateReferences <T>(
Guid sourceChainId,
Guid destinationChainId,
Block <T> branchPoint)
{
string srcCollId = StateRefId(sourceChainId);
string dstCollId = StateRefId(destinationChainId);
LiteCollection <StateRefDoc> srcColl = _db.GetCollection <StateRefDoc>(srcCollId),
dstColl = _db.GetCollection <StateRefDoc>(dstCollId);
Query srcQuery = Query.And(
Query.GT("BlockIndex", 0),
Query.LTE("BlockIndex", branchPoint.Index)
);
IEnumerable <StateRefDoc> srcStateRefs = srcColl.Find(srcQuery);
dstColl.InsertBulk(srcStateRefs);
if (!dstColl.Exists(_ => true) && CountIndex(sourceChainId) < 1)
{
throw new ChainIdNotFoundException(
sourceChainId,
"The source chain to be forked does not exist."
);
}
dstColl.EnsureIndex("AddressString");
dstColl.EnsureIndex("BlockIndex");
_lastStateRefCaches.Remove(destinationChainId);
}
/// <inheritdoc/>
public override void ForkStateReferences <T>(
Guid sourceChainId,
Guid destinationChainId,
Block <T> branchPoint)
{
string srcCollId = StateRefId(sourceChainId);
string dstCollId = StateRefId(destinationChainId);
LiteCollection <StateRefDoc> srcColl = _liteDb.GetCollection <StateRefDoc>(srcCollId),
dstColl = _liteDb.GetCollection <StateRefDoc>(dstCollId);
dstColl.InsertBulk(srcColl.Find(Query.LTE("BlockIndex", branchPoint.Index)));
if (!dstColl.Exists(_ => true) && CountIndex(sourceChainId) < 1)
{
throw new ChainIdNotFoundException(
sourceChainId,
"The source chain to be forked does not exist."
);
}
dstColl.EnsureIndex(nameof(StateRefDoc.StateKey));
dstColl.EnsureIndex(nameof(StateRefDoc.BlockIndex));
_lastStateRefCaches.Remove(destinationChainId);
}
public bool ConfigurationAlertExists(AlertType type)
{
using (LiteDatabase db = new LiteDatabase(connectionString))
{
LiteCollection <ConfigurationAlert> col = db.GetCollection <ConfigurationAlert>(configurationAlertsCollectionName);
return(col.Exists(Query.EQ("AlertType", type.ToString())));
}
}
public void Insert(List <MailHeaderEntity> entities)
{
foreach (var mail in entities)
{
if (!_mailFromEntity.Exists(x => x.Address == mail.MailFromEntity.Address))
{
_mailFromEntity.Insert(mail.MailFromEntity);
}
if (!_mailHeaderEntity.Exists(x => x.Uid == mail.Uid))
{
_mailHeaderEntity.Insert(mail);
// create index
_mailHeaderEntity.EnsureIndex(x => x.Uid);;
}
}
}
private void InitDbEntry(string key, BsonValue value)
{
if (!optionsCollection.Exists(x => x.ContainsKey(key)))
{
optionsCollection.Insert(new BsonDocument {
[key] = value
});
}
}
internal void set(string key, string value)
{
bool exist = _items.Exists(i => i.Key == key);
if (exist)
{
var item = _items.FindOne(i => i.Key == key);
item.Value = value;
_items.Update(item);
}
else
{
_items.Insert(new DBItem()
{
Key = key, Value = value
});
}
}
/// <summary>
/// Метод проверяет существует ли такой контрагент в базе
/// </summary>
/// <param name="_inn"></param>
/// <param name="_kpp"></param>
/// <returns></returns>
public bool CheckExist(string _inn, string _kpp)
{
LiteCollection <Contractor> collection = database.GetCollection <Contractor>("contractors");
collection.EnsureIndex(a => a.INN);
collection.EnsureIndex(a => a.KPP);
bool result = collection.Exists(a => a.INN == _inn && a.KPP == _kpp);
return(result);
}
public void AddPokemonTimestamp(Int64 ts)
{
if (!pokemonTimestampCollection.Exists(s => s.Timestamp == ts))
{
var stat = new PokemonTimestamp {
Timestamp = ts
};
pokemonTimestampCollection.Insert(stat);
}
}
public bool AddUser(User user)
{
//if the guid exists or email address is allready in the system then dont add
if (mUsers.Exists((n) => n.Guid == user.Guid || n.EmailAddress == user.EmailAddress))
{
return(false);
}
mUsers.Insert(user);
return(true);
}
public bool Register(string usr, string pwd, string pwd2)
{
if (pwd != pwd2)
{
return(false);
}
if (_users.Exists(u => u.Username == usr))
{
return(false);
}
var user = new User
{
Username = usr,
Password = BCrypt.Net.BCrypt.HashPassword(pwd)
};
_users.Insert(user);
return(true);
}
public bool UserPrincipalNameExists(string upn)
{
LiteCollection <AuthenticablePrincipal> col = db.GetCollection <AuthenticablePrincipal>(authenticablePrincipalCollectionName);
Query query = Query.Or(
Query.In("AlternativeNames", upn),
Query.EQ("Name", upn)
);
return(col.Exists(query));
}
public int CreateUser(User user)
{
if (usersRepository.Exists(u => u.UserName == user.UserName))
{
var exception = new InvalidOperationException("User already exists.");
exception.Data["UserName"] = user.UserName;
throw exception;
}
var newUserBson = usersRepository.Insert(user);
return(newUserBson.AsInt32);
}
private static void CreateAdminUser(LiteCollection <User> collection, User user, bool update)
{
if (!collection.Exists(u => u.Username == user.Username))
{
collection.Insert(user);
}
else
{
if (update)
{
var oldUser = collection.FindOne(u => u.Username == user.Username);
user.Id = oldUser.Id;
collection.Update(user);
}
}
}
public override void ForkTxNonces(Guid sourceChainId, Guid destinationChainId)
{
LiteCollection <BsonDocument> srcColl = TxNonceCollection(sourceChainId);
if (!srcColl.Exists(_ => true))
{
throw new ChainIdNotFoundException(
sourceChainId,
$"No such chain ID: {sourceChainId}."
);
}
LiteCollection <BsonDocument> destColl = TxNonceCollection(destinationChainId);
destColl.InsertBulk(srcColl.FindAll());
}
public void Update(Mediafile file, bool updateWithMatch = false)
{
if (file != null && tracks.Exists(t => t.Path == file.Path))
{
if (updateWithMatch == false)
{
tracks.Update(file);
}
else
{
Mediafile mp3 = tracks.FindOne(t => t.Path == file.Path);
mp3.Playlists.Clear();
mp3.Playlists.AddRange(file.Playlists);
tracks.Update(mp3);
}
}
}
/// <inheritdoc/>
public override void StoreStateReference(
Guid chainId,
IImmutableSet <Address> addresses,
HashDigest <SHA256> blockHash,
long blockIndex)
{
string collId = StateRefId(chainId);
LiteCollection <StateRefDoc> coll = _db.GetCollection <StateRefDoc>(collId);
IEnumerable <StateRefDoc> stateRefDocs = addresses
.Select(addr => new StateRefDoc
{
Address = addr,
BlockIndex = blockIndex,
BlockHash = blockHash,
})
.Where(doc => !coll.Exists(d => d.Id == doc.Id));
coll.InsertBulk(stateRefDocs);
coll.EnsureIndex("AddressString");
coll.EnsureIndex("BlockIndex");
if (!_lastStateRefCaches.ContainsKey(chainId))
{
_lastStateRefCaches[chainId] =
new LruCache <Address, Tuple <HashDigest <SHA256>, long> >();
}
LruCache <Address, Tuple <HashDigest <SHA256>, long> > stateRefCache =
_lastStateRefCaches[chainId];
foreach (Address address in addresses)
{
_logger.Debug($"Try to set cache {address}");
if (!stateRefCache.TryGetValue(address, out Tuple <HashDigest <SHA256>, long> cache) ||
cache.Item2 < blockIndex)
{
stateRefCache[address] =
new Tuple <HashDigest <SHA256>, long>(blockHash, blockIndex);
}
}
}
/// <inheritdoc/>
public override void StoreStateReference(
Guid chainId,
IImmutableSet <string> keys,
HashDigest <SHA256> blockHash,
long blockIndex)
{
string collId = StateRefId(chainId);
LiteCollection <StateRefDoc> coll = _liteDb.GetCollection <StateRefDoc>(collId);
IEnumerable <StateRefDoc> stateRefDocs = keys
.Select(key => new StateRefDoc
{
StateKey = key,
BlockIndex = blockIndex,
BlockHash = blockHash,
})
.Where(doc => !coll.Exists(d => d.Id == doc.Id));
coll.InsertBulk(stateRefDocs);
coll.EnsureIndex(nameof(StateRefDoc.StateKey));
coll.EnsureIndex(nameof(StateRefDoc.BlockIndex));
if (!_lastStateRefCaches.ContainsKey(chainId))
{
_lastStateRefCaches[chainId] =
new LruCache <string, Tuple <HashDigest <SHA256>, long> >();
}
LruCache <string, Tuple <HashDigest <SHA256>, long> > stateRefCache =
_lastStateRefCaches[chainId];
foreach (string key in keys)
{
_logger.Debug($"Try to set cache {key}");
if (!stateRefCache.TryGetValue(key, out Tuple <HashDigest <SHA256>, long> cache) ||
cache.Item2 < blockIndex)
{
stateRefCache[key] =
new Tuple <HashDigest <SHA256>, long>(blockHash, blockIndex);
}
}
}
/// <inheritdoc/>
public override void StoreStateReference(
Guid chainId,
IImmutableSet <Address> addresses,
HashDigest <SHA256> hash,
long index)
{
string collId = StateRefId(chainId);
LiteCollection <StateRefDoc> coll = _db.GetCollection <StateRefDoc>(collId);
IEnumerable <StateRefDoc> stateRefDocs = addresses
.Select(addr => new StateRefDoc
{
Address = addr,
BlockIndex = index,
BlockHash = hash,
})
.Where(doc => !coll.Exists(d => d.Id == doc.Id));
coll.InsertBulk(stateRefDocs);
coll.EnsureIndex("AddressString");
coll.EnsureIndex("BlockIndex");
}
public bool Exists(string field, string value)
{
return(_db.Exists(Query.EQ(field, value)));
}
public Handlers(string serverBaseFolder)
{
_serverBaseFolder = serverBaseFolder;
_serializer = new JavaScriptSerializer();
_helpers = new Helpers();
_actions = new Dictionary <string, Action>()
{
{ "getDbFiles", () => {
try {
ArrayList dbFilePaths = new ArrayList();
ArrayList dbFileNames = new ArrayList();
FolderBrowserDialog fbd = new FolderBrowserDialog();
Invoker invoker = new Invoker(fbd);
if (DialogResult.OK == invoker.Invoke())
{
string[] files = Directory.GetFiles(fbd.SelectedPath);
foreach (string filepath in files)
{
FileInfo fileInfo = new FileInfo(filepath);
if (fileInfo.Extension.Equals(".db"))
{
dbFilePaths.Add(filepath);
dbFileNames.Add(fileInfo.Name);
}
}
}
Dictionary <string, object> responseDict = new Dictionary <string, object>()
{
{ "dbfolder", fbd.SelectedPath },
{ "dbfilepaths", dbFilePaths },
{ "dbfilenames", dbFileNames }
};
string responseStr = _serializer.Serialize(responseDict);;
_helpers.SendHttpTextResponse(_httpDetails.Response, responseStr);
} catch (Exception ex) {
_helpers.SendHttpResponse(500, ex.Message, new byte[0], "text/html", "MoneyTracker Server", _httpDetails.Response);
}
} },
{ "selectdb", () => {
try {
string dbpath = (string)_requestDictionary["dbpath"];
LiteDatabase db = new LiteDatabase(dbpath);
_yearCollection = db.GetCollection <TransYear>("YearCollection");
_categoryCollection = db.GetCollection <TransCategory>("CategoryCollection");
_transactionCollection = db.GetCollection <Transaction>("TransactionCollection");
//get categories and year for client selection from this database
Dictionary <string, object> responseDict = new Dictionary <string, object>()
{
{ "categorynames", GetCategoryNames() },
{ "years", GetYears() }
};
string responseStr = _serializer.Serialize(responseDict);
_helpers.SendHttpTextResponse(_httpDetails.Response, responseStr);
} catch (Exception ex) {
_helpers.SendHttpResponse(500, ex.Message, new byte[0], "text/html", "MoneyTracker Server", _httpDetails.Response);
}
} },
{ "filterTransactions", () => {
try {
if (_transactionCollection != null)
{
Dictionary <string, object> selectionDict = (Dictionary <string, object>)_requestDictionary["selection"];
string type = (string)selectionDict["type"];
string category = (string)selectionDict["category"];
string year = (string)selectionDict["year"];
ArrayList typeList = new ArrayList();
if (type == "all")
{
typeList.Add("income");
typeList.Add("expense");
}
else
{
typeList.Add(type);
}
ArrayList categoryList = null;
if (category == "all")
{
categoryList = GetCategoryNames();
}
else
{
categoryList = new ArrayList();
categoryList.Add(category);
}
ArrayList yearList = null;
if (year == "all")
{
yearList = GetYears();
}
else
{
yearList = new ArrayList();
yearList.Add(Int32.Parse(year));
}
IEnumerable <Transaction> transactions = _transactionCollection.Find(x => typeList.Contains(x.TransType) && categoryList.Contains(x.CategoryName) && yearList.Contains(x.Year));
ArrayList transactionList = new ArrayList();
foreach (Transaction trans in transactions)
{
transactionList.Add(trans.TransactionDict());
}
string responseStr = _serializer.Serialize(transactionList);
_helpers.SendHttpTextResponse(_httpDetails.Response, responseStr);
}
} catch (Exception ex) {
_helpers.SendHttpResponse(500, ex.Message, new byte[0], "text/html", "MoneyTracker Server", _httpDetails.Response);
}
} },
{ "addTransaction", () => {
try {
if (_transactionCollection != null)
{
Transaction newTransaction = new Transaction();
Dictionary <string, object> transactionDict = (Dictionary <string, object>)_requestDictionary["transaction"];
string categoryName = (string)transactionDict["CategoryName"];
string[] dateParts = ((string)transactionDict["TransDate"]).Split('-');
newTransaction.TransDate = new DateTime(Int32.Parse(dateParts[0]), Int32.Parse(dateParts[1]),
Int32.Parse(dateParts[2]));
int year = Int32.Parse(dateParts[0]);
int categoryId = GetCategoryId(categoryName);
int yearId = GetYearId(year);
newTransaction.Year = year;
newTransaction.YearId = yearId;
newTransaction.CategoryName = categoryName;
newTransaction.CategoryId = categoryId;
newTransaction.TransType = (string)transactionDict["TransType"];
newTransaction.Amount = decimal.Parse((string)transactionDict["Amount"]);
newTransaction.Description = (string)transactionDict["Description"];
newTransaction.Source = (string)transactionDict["Source"];
newTransaction.Id = _transactionCollection.Insert(newTransaction);
//return a list of category names, years, and the new transaction
Dictionary <string, object> responseDict = new Dictionary <string, object>()
{
{ "categorynames", GetCategoryNames() },
{ "years", GetYears() },
{ "transaction", newTransaction.TransactionDict() }
};
string responseStr = _serializer.Serialize(responseDict);
_helpers.SendHttpTextResponse(_httpDetails.Response, responseStr);
}
else
{
_helpers.SendHttpResponse(400, "Database has not been specified", new byte[0], "text/html", "MoneyTracker Server", _httpDetails.Response);
}
} catch (Exception ex) {
_helpers.SendHttpResponse(500, ex.Message, new byte[0], "text/html", "MoneyTracker Server", _httpDetails.Response);
}
} },
{ "updateTransaction", () => {
try {
if (_transactionCollection != null)
{
Dictionary <string, object> transactionDict = (Dictionary <string, object>)_requestDictionary["transaction"];
//get the current transaction for backup
int id = Convert.ToInt32(transactionDict["Id"]);
Transaction backupTransaction = _transactionCollection.FindById(id);
//build the new updated transaction
Transaction updateTransaction = new Transaction();
string categoryName = (string)transactionDict["CategoryName"];
string[] dateParts = ((string)transactionDict["TransDate"]).Split('-');
updateTransaction.TransDate = new DateTime(Int32.Parse(dateParts[0]), Int32.Parse(dateParts[1]),
Int32.Parse(dateParts[2]));
int year = Int32.Parse(dateParts[0]);
int categoryId = GetCategoryId(categoryName);
int yearId = GetYearId(year);
updateTransaction.Id = id;
updateTransaction.Year = year;
updateTransaction.YearId = yearId;
updateTransaction.CategoryName = categoryName;
updateTransaction.CategoryId = categoryId;
updateTransaction.TransType = (string)transactionDict["TransType"];
//string fullName = transactionDict["Amount"].GetType().FullName;
updateTransaction.Amount = decimal.Parse((string)transactionDict["Amount"]);
updateTransaction.Description = (string)transactionDict["Description"];
updateTransaction.Source = (string)transactionDict["Source"];
bool found = _transactionCollection.Update(updateTransaction);
if (found)
{
//return a list of category names, years, and the backup transaction
Dictionary <string, object> responseDict = new Dictionary <string, object>()
{
{ "categorynames", GetCategoryNames() },
{ "years", GetYears() },
{ "backup", backupTransaction.TransactionDict() }
};
string responseStr = _serializer.Serialize(responseDict);
_helpers.SendHttpTextResponse(_httpDetails.Response, responseStr);
}
else
{
_helpers.SendHttpResponse(400, "Transaction was not located for update", new byte[0], "text/html", "MoneyTracker Server", _httpDetails.Response);
}
}
else
{
_helpers.SendHttpResponse(400, "Database has not been specified", new byte[0], "text/html", "MoneyTracker Server", _httpDetails.Response);
}
} catch (Exception ex) {
_helpers.SendHttpResponse(500, ex.Message, new byte[0], "text/html", "MoneyTracker Server", _httpDetails.Response);
}
} },
{ "deleteTransaction", () => {
try {
if (_transactionCollection != null)
{
Dictionary <string, object> transactionDict = (Dictionary <string, object>)_requestDictionary["transaction"];
//get the current transaction for backup
int id = Convert.ToInt32(transactionDict["Id"]);
Transaction backupTransaction = _transactionCollection.FindById(id);
//get the current transaction's category id and year id
int categoryId = backupTransaction.CategoryId;
int yearId = backupTransaction.YearId;
//delete the transaction
bool found = _transactionCollection.Delete(id);
if (found)
{
//was this the last transaction with the category?
if (!_transactionCollection.Exists(Query.EQ("CategoryId", categoryId)))
{
//if category does not exist among transactons then delete it
_categoryCollection.Delete(categoryId);
}
//was this the last transaction with the year?
if (!_transactionCollection.Exists(Query.EQ("YearId", yearId)))
{
_yearCollection.Delete(yearId);
}
//return a list of category names, years, and the backup transaction
Dictionary <string, object> responseDict = new Dictionary <string, object>()
{
{ "categorynames", GetCategoryNames() },
{ "years", GetYears() },
{ "backup", backupTransaction.TransactionDict() }
};
string responseStr = _serializer.Serialize(responseDict);
_helpers.SendHttpTextResponse(_httpDetails.Response, responseStr);
}
else
{
_helpers.SendHttpResponse(400, "Transaction was not located for delete", new byte[0], "text/html", "MoneyTracker Server", _httpDetails.Response);
}
}
} catch (Exception ex) {
_helpers.SendHttpResponse(500, ex.Message, new byte[0], "text/html", "MoneyTracker Server", _httpDetails.Response);
}
} },
{ "excelSheet", () => {
try {
float totalExpense = 0;
float totalIncome = 0;
string year = (string)_requestDictionary["year"];
ArrayList rows = (ArrayList)_requestDictionary["rows"];
Dictionary <int, object> expenseDictionary = new Dictionary <int, object>();
Dictionary <string, float> expenseTotals = new Dictionary <string, float>();
Dictionary <int, object> incomeDictionary = new Dictionary <int, object>();
Dictionary <string, float> incomeTotals = new Dictionary <string, float>();
foreach (ArrayList row in rows)
{
string category = (string)row[2];
string date = (string)row[3];
string amount = row[4].ToString();
string source = (string)row[5];
string description = (string)row[6];
float amountFloat = float.Parse(amount);
if (row[1].Equals("expense"))
{
totalExpense += amountFloat;
expenseDictionary.Add((int)row[0], new List <string>()
{
amount, category, date, source, description
});
if (!expenseTotals.ContainsKey(category))
{
expenseTotals.Add(category, amountFloat);
}
else
{
expenseTotals[category] = expenseTotals[category] + amountFloat;
}
}
else if (row[1].Equals("income"))
{
totalIncome += amountFloat;
incomeDictionary.Add((int)row[0], new List <string>()
{
amount, category, date, source, description
});
if (!incomeTotals.ContainsKey(category))
{
incomeTotals.Add(category, amountFloat);
}
else
{
incomeTotals[category] = incomeTotals[category] + amountFloat;
}
}
}
//Create Excel App
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = true;
Excel.Workbook workbook = excelApp.Workbooks.Add(Missing.Value);
/*
* //Create Excel workbook at file location
* string file_location = Path.Combine(_databasePath, "moneytracker.xls");
* excelApp.Workbooks.Open(file_location, 0, false, 5, "", "",
* false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
*/
string amtcol = ColumnNumberToName(2);
string catcol = ColumnNumberToName(3);
string datcol = ColumnNumberToName(4);
string soucol = ColumnNumberToName(5);
string descol = ColumnNumberToName(6);
//Create expense worksheet
if (expenseDictionary.Count() > 0)
{
//expenseSheet = (Excel.Worksheet)sheets.get_Item(1);
Excel.Worksheet expenseSheet = (Excel.Worksheet)workbook.ActiveSheet;
expenseSheet.Name = "Expenses";
//create expense title cell
expenseSheet.Cells[2, 2] = "Expenses for " + year;
expenseSheet.get_Range(amtcol + 2, catcol + 2).Font.Bold = true;
expenseSheet.get_Range(amtcol + 2, catcol + 2).Font.Size = 22;
//set column widths for headings, value cells
expenseSheet.get_Range(amtcol + 3, amtcol + 200).ColumnWidth = 8;
expenseSheet.get_Range(catcol + 3, catcol + 200).ColumnWidth = 20;
expenseSheet.get_Range(datcol + 3, datcol + 200).ColumnWidth = 12;
expenseSheet.get_Range(soucol + 3, soucol + 200).ColumnWidth = 20;
expenseSheet.get_Range(descol + 3, descol + 200).ColumnWidth = 45;
//some twiks
expenseSheet.get_Range(amtcol + 3, descol + 200).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
expenseSheet.get_Range(amtcol + 3, datcol + 200).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
expenseSheet.get_Range(descol + 3, descol + 200).WrapText = true;
//create transaction heading cells
expenseSheet.get_Range(amtcol + 4, descol + 4).Font.Bold = true;
expenseSheet.get_Range(amtcol + 4, descol + 4).Font.Size = 12;
expenseSheet.Cells[4, 2] = "Amount";
expenseSheet.Cells[4, 3] = "Category";
expenseSheet.Cells[4, 4] = "Date";
expenseSheet.Cells[4, 5] = "Source";
expenseSheet.Cells[4, 6] = "Description";
//assign values
for (int idx = 0; idx < expenseDictionary.Count; idx++)
{
var item = expenseDictionary.ElementAt(idx);
var val = (List <string>)item.Value;
int rowI = idx + 5;
string rowS1 = amtcol + rowI;
expenseSheet.get_Range(rowS1, rowS1).Formula = "=Fixed(" + val[0] + ",2,TRUE)";
expenseSheet.Cells[rowI, 3] = val[1];
expenseSheet.Cells[rowI, 4] = val[2];
expenseSheet.Cells[rowI, 5] = val[3];
expenseSheet.Cells[rowI, 6] = val[4];
}
//create cell for total expense
int offset = expenseDictionary.Count + 6;
expenseSheet.get_Range(amtcol + offset, amtcol + offset).Font.Bold = true;
expenseSheet.get_Range(amtcol + offset, amtcol + offset).Font.Size = 16;
expenseSheet.get_Range(amtcol + offset, amtcol + offset).Formula = "=Fixed(" + totalExpense + ",2,TRUE)";
}
//Create income worksheet at end
if (incomeDictionary.Count() > 0)
{
Excel.Worksheet incomeSheet = (Excel.Worksheet)workbook.Sheets.Add(Type.Missing, workbook.Sheets[workbook.Sheets.Count],
1, Excel.XlSheetType.xlWorksheet);
incomeSheet.Name = "Income";
//create income title cell
incomeSheet.Cells[2, 2] = "Income for " + year;
incomeSheet.get_Range(amtcol + 2, catcol + 2).Font.Bold = true;
incomeSheet.get_Range(amtcol + 2, catcol + 2).Font.Size = 22;
//set column widths for headings, value cells
incomeSheet.get_Range(amtcol + 3, amtcol + 200).ColumnWidth = 8;
incomeSheet.get_Range(catcol + 3, catcol + 200).ColumnWidth = 20;
incomeSheet.get_Range(datcol + 3, datcol + 200).ColumnWidth = 12;
incomeSheet.get_Range(soucol + 3, soucol + 200).ColumnWidth = 20;
incomeSheet.get_Range(descol + 3, descol + 200).ColumnWidth = 45;
//some twiks
incomeSheet.get_Range(amtcol + 3, descol + 200).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
incomeSheet.get_Range(amtcol + 3, datcol + 200).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
incomeSheet.get_Range(descol + 3, descol + 200).WrapText = true;
//create transaction heading cells
incomeSheet.get_Range(amtcol + 4, descol + 4).Font.Bold = true;
incomeSheet.get_Range(amtcol + 4, descol + 4).Font.Size = 12;
incomeSheet.Cells[4, 2] = "Amount";
incomeSheet.Cells[4, 3] = "Category";
incomeSheet.Cells[4, 4] = "Date";
incomeSheet.Cells[4, 5] = "Source";
incomeSheet.Cells[4, 6] = "Description";
//assign values
for (int idx = 0; idx < incomeDictionary.Count; idx++)
{
var item = incomeDictionary.ElementAt(idx);
var val = (List <string>)item.Value;
int rowI = idx + 5;
string rowS1 = amtcol + rowI;
incomeSheet.get_Range(rowS1, rowS1).Formula = "=Fixed(" + val[0] + ",2,TRUE)";
incomeSheet.Cells[rowI, 3] = val[1];
incomeSheet.Cells[rowI, 4] = val[2];
incomeSheet.Cells[rowI, 5] = val[3];
incomeSheet.Cells[rowI, 6] = val[4];
}
//create cell for total income
int offset = incomeDictionary.Count + 6;
incomeSheet.get_Range(amtcol + offset, amtcol + offset).Font.Bold = true;
incomeSheet.get_Range(amtcol + offset, amtcol + offset).Font.Size = 16;
incomeSheet.get_Range(amtcol + offset, amtcol + offset).Formula = "=Fixed(" + totalIncome + ",2,TRUE)";
}
//create a summary worksheet at the end
Excel.Worksheet summarySheet = (Excel.Worksheet)workbook.Sheets.Add(Type.Missing, workbook.Sheets[workbook.Sheets.Count],
1, Excel.XlSheetType.xlWorksheet);
summarySheet.Name = "Summary";
//create expense summary
if (expenseDictionary.Count() > 0)
{
//create cells for summary category totals
string sumcatcol = ColumnNumberToName(4);
string sumtotcol = ColumnNumberToName(5);
//create summary expense title
summarySheet.Cells[2, 4] = "Expense Summary";
summarySheet.get_Range(sumcatcol + 2, sumtotcol + 2).Font.Bold = true;
summarySheet.get_Range(sumcatcol + 2, sumtotcol + 2).Font.Size = 22;
//set column widths for headings, value cells
summarySheet.get_Range(sumcatcol + 3, sumcatcol + 200).ColumnWidth = 24;
summarySheet.get_Range(sumtotcol + 3, sumtotcol + 200).ColumnWidth = 12;
//create category total heading cells
summarySheet.get_Range(sumcatcol + 4, sumtotcol + 4).Font.Bold = true;
summarySheet.get_Range(sumcatcol + 4, sumtotcol + 4).Font.Size = 16;
summarySheet.Cells[4, 4] = "Category";
summarySheet.Cells[4, 5] = "Total";
//assign values
for (int idx = 0; idx < expenseTotals.Count; idx++)
{
var item = expenseTotals.ElementAt(idx);
int rowI = idx + 5;
string rowS2 = sumtotcol + rowI;
summarySheet.Cells[rowI, 4] = item.Key;
summarySheet.get_Range(rowS2, rowS2).Formula = "=Fixed(" + item.Value + ",2,TRUE)";
}
//create cell for total expense
int offset = expenseTotals.Count + 6;
summarySheet.get_Range(sumtotcol + offset, sumtotcol + offset).Font.Bold = true;
summarySheet.get_Range(sumtotcol + offset, sumtotcol + offset).Font.Size = 16;
summarySheet.get_Range(sumtotcol + offset, sumtotcol + offset).Formula = "=Fixed(" + totalExpense + ",2,TRUE)";
}
//create income summary
if (incomeDictionary.Count() > 0)
{
//create cells for summary category totals
string sumcatcol = ColumnNumberToName(9);
string sumtotcol = ColumnNumberToName(10);
//create income header
summarySheet.Cells[2, 9] = "Income Summary";
summarySheet.get_Range(sumcatcol + 2, sumtotcol + 2).Font.Bold = true;
summarySheet.get_Range(sumcatcol + 2, sumtotcol + 2).Font.Size = 22;
//set column widths for headings, value cells
summarySheet.get_Range(sumcatcol + 3, sumcatcol + 200).ColumnWidth = 24;
summarySheet.get_Range(sumtotcol + 3, sumtotcol + 200).ColumnWidth = 12;
//create category total heading cells
summarySheet.get_Range(sumcatcol + 4, sumtotcol + 4).Font.Bold = true;
summarySheet.get_Range(sumcatcol + 4, sumtotcol + 4).Font.Size = 16;
summarySheet.Cells[4, 9] = "Category";
summarySheet.Cells[4, 10] = "Total";
//assign values
for (int idx = 0; idx < incomeTotals.Count; idx++)
{
var item = incomeTotals.ElementAt(idx);
int rowI = idx + 5;
string rowS2 = sumtotcol + rowI;
summarySheet.Cells[rowI, 9] = item.Key;
summarySheet.get_Range(rowS2, rowS2).Formula = "=Fixed(" + item.Value + ",2,TRUE)";
}
//create cell for total income
int offset = incomeTotals.Count + 6;
summarySheet.get_Range(sumtotcol + offset, sumtotcol + offset).Font.Bold = true;
summarySheet.get_Range(sumtotcol + offset, sumtotcol + offset).Font.Size = 16;
summarySheet.get_Range(sumtotcol + offset, sumtotcol + offset).Formula = "=Fixed(" + totalIncome + ",2,TRUE)";
}
//save changes and close workbook
workbook.Close(true, Type.Missing, Type.Missing);
//close Excel server
excelApp.Quit();
_helpers.SendHttpTextResponse(_httpDetails.Response, "Completed Excel Workbook");
} catch (Exception ex) {
_helpers.SendHttpResponse(500, ex.Message, new byte[0], "text/html",
"MoneyTracker Server", _httpDetails.Response);
}
} }
};
}
public bool Exists(System.Linq.Expressions.Expression <Func <T, bool> > predicate)
{
return(_collection.Exists(predicate));
}
public virtual bool Any(Expression <Func <T, bool> > where)
{
return(collection.Exists(where));
}
public bool Any(Func <TItem, bool> predicate) => _collection.Exists(x => predicate(x));