public static int InsertIntoOringalTransaction(BankAccount bankAccount, Transaction transaction, string categoryName) { string cmdText = "INSERT INTO tblOrginalTransaction("; cmdText += "Verified, TransactionID, TransactionDate, TransactionAmount, Merchant, BankMemo, BankAccountId, TransactionType"; if (!string.IsNullOrEmpty(transaction.CheckNumber)) cmdText += ", CheckNumber"; if (categoryName != "") cmdText += ", CategoryName"; cmdText += ") Values("; cmdText += "false"; cmdText += ", '" + transaction.TransactionID + "'"; cmdText += ", #" + Convert.ToString(transaction.TransactionDate) + "#"; cmdText += ", " + Convert.ToString(transaction.TransactionAmount); cmdText += ", '" + transaction.MerchantName.Replace(@"'", "''") + "'"; cmdText += ", '" + transaction.BankMemo.Replace(@"'", "''") + "'"; cmdText += ", " + Convert.ToString(bankAccount.BankAccountID); cmdText += ", '" + transaction.TransactionType + "'"; if (!string.IsNullOrEmpty(transaction.CheckNumber)) cmdText += ", '" + transaction.CheckNumber + "'"; if (categoryName != "") cmdText += ", '" + categoryName + "'"; cmdText += ")"; string identity = " SELECT @@Identity"; int orginalTransactionID; using (OleDbConnection myConnection = new OleDbConnection( ConfigurationManager.ConnectionStrings["BeanCounterDB"].ToString())) { myConnection.Open(); using (OleDbCommand myCommand = new OleDbCommand(cmdText, myConnection)) myCommand.ExecuteNonQuery(); using (OleDbCommand myCommand = new OleDbCommand(identity, myConnection)) orginalTransactionID = Convert.ToInt32(myCommand.ExecuteScalar().ToString()); } return orginalTransactionID; }
public static string FindCategory(Transaction transaction, List<Merchant> merchants) { string categoryName = ""; if (transaction.MerchantName != "?") { categoryName = Merchant.NationalMerchant(transaction.MerchantName,merchants); if (categoryName == "") categoryName = Merchant.LocalMerchant(transaction.MerchantName, true); if (categoryName != "") categoryName = CheckSpecials(categoryName, transaction.TransactionAmount); } return categoryName; }
public static void InsertIntoSplitTransction(string categoryName, int transactionID, Transaction transaction) { string cmdText = "INSERT INTO tblSplitTransaction("; cmdText += "OrginalTransactionID, TransactionAmount"; if (!string.IsNullOrEmpty(categoryName)) cmdText += ", CategoryName"; cmdText += ") Values(" + transactionID; cmdText += ", " + Convert.ToString(transaction.TransactionAmount); if (!string.IsNullOrEmpty(categoryName)) cmdText += ", '" + categoryName + "'"; cmdText += ")"; using (OleDbConnection myConnection = new OleDbConnection( ConfigurationManager.ConnectionStrings["BeanCounterDB"].ToString())) { myConnection.Open(); using (OleDbCommand myCommand = new OleDbCommand(cmdText, myConnection)) myCommand.ExecuteNonQuery().ToString(); } }
public static bool TransactionDoesNotExist(Transaction transaction, int bankAccountID) { bool transactionDoesNotExist = false; string cmdText = "select * from tblOrginalTransaction where TransactionID = '" + transaction.TransactionID + "' and BankAccountId = " + Convert.ToString(bankAccountID) + " and TransactionAmount = " + Convert.ToString(transaction.TransactionAmount); using (OleDbConnection myConnection = new OleDbConnection( ConfigurationManager.ConnectionStrings["BeanCounterDB"].ToString())) { using (OleDbCommand myCommand = new OleDbCommand(cmdText, myConnection)) { myConnection.Open(); using (OleDbDataReader myDataReader = myCommand.ExecuteReader()) { if (!myDataReader.Read()) transactionDoesNotExist = true; } } } return transactionDoesNotExist; }
public FrmSplitTransaction(Transaction transaction, int bankAccountID) { Transaction = transaction; BankAccountID = bankAccountID; InitializeComponent(); }
private static List<Transaction> GetTransactions(string fileContents) { List<Transaction> transactions = new List<Transaction>(); int currentPosition = fileContents.IndexOf("<TRNTYPE>") - 1; int endPosition = fileContents.LastIndexOf("<TRNTYPE>") - 1; int transactionNumber = 0; while (transactionNumber != NumberOfTransactions(fileContents)) { Transaction transaction = new Transaction(); transaction.TransactionType = ExtractText(fileContents, "<TRNTYPE>", currentPosition); if (transaction.TransactionType.ToUpper() == "CHECK") transaction.CheckNumber = ExtractText(fileContents, "<CHECKNUM>", currentPosition); transaction.TransactionDate = ExtractDate(fileContents, "<DTPOSTED>", currentPosition); transaction.TransactionAmount = Convert.ToDecimal(ExtractText(fileContents, "<TRNAMT>", currentPosition)); transaction.TransactionID = ExtractText(fileContents, "<FITID>", currentPosition); string merchantName = ExtractText(fileContents, "<NAME>", currentPosition); merchantName = merchantName.Replace("&", ""); transaction.MerchantName = merchantName.Trim(); string bankMemo = ""; if (fileContents.Contains("<MEMO>")) bankMemo = ExtractText(fileContents, "<MEMO>", currentPosition).Replace("&", "").Trim(); transaction.BankMemo = bankMemo; currentPosition = fileContents.IndexOf("<NAME>", currentPosition); currentPosition = fileContents.IndexOf("<TRNTYPE>", currentPosition) - 1; transactions.Add(transaction); transactionNumber += 1; } return transactions; }
public static List<Transaction> TransactionsByCategory(string categoryName, string dateRange) { string cmdText = "select " + "tblOrginalTransaction.TransactionDate, tblOrginalTransaction.Merchant, tblSplitTransaction.TransactionAmount, " + "tblOrginalTransaction.BankMemo, tblOrginalTransaction.CheckNumber, tblOrginalTransaction.TransactionType, " + "tblOrginalTransaction.UserMemo " + "FROM tblOrginaltransaction left join tblSplitTransaction " + "on tblSplitTransaction.OrginalTransactionID = tblorginaltransaction.OrginalTransactionID " + "WHERE tblSplitTransaction.CategoryName = '" + categoryName + "'"; cmdText += AddDateRange(dateRange); cmdText += "order by tblOrginalTransaction.TransactionDate desc"; List<Transaction> transactions = new List<Transaction>(); using (OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection( System.Configuration.ConfigurationManager.ConnectionStrings["BeanCounterDB"].ToString())) { myConnection.Open(); using (OleDbCommand myCommand = new OleDbCommand(cmdText, myConnection)) { using (OleDbDataReader myDataReader = myCommand.ExecuteReader()) { while (myDataReader.Read()) { Transaction transaction = new Transaction(); transaction.TransactionDate = Convert.ToDateTime(myDataReader["TransactionDate"].ToString()); transaction.MerchantName = myDataReader["Merchant"].ToString(); transaction.TransactionAmount = Convert.ToDecimal(myDataReader["TransactionAmount"].ToString()); transaction.BankMemo = myDataReader["BankMemo"].ToString(); transaction.UserMemo = myDataReader["UserMemo"].ToString(); transaction.CheckNumber = myDataReader["CheckNumber"].ToString(); transaction.TransactionType = myDataReader["TransactionType"].ToString(); transactions.Add(transaction); } } } return transactions; } }
public static IEnumerable<Transaction> Transactions(int BankAccountID, bool showOnlyUnverfied, string orderBy) { string cmdText = "SELECT Verified, TransactionDate, Merchant" + ", TransactionAmount, CategoryName, BankMemo" + ", CheckNumber, TransactionType" + ", OrginalTransactionID, UserMemo" + " FROM tblOrginalTransaction" + " WHERE (BankAccountId = " + Convert.ToString(BankAccountID) + ")"; if (showOnlyUnverfied) cmdText += " AND (Verified = No)"; cmdText += orderBy; List<Transaction> transactions = new List<Transaction>(); using (OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection( System.Configuration.ConfigurationManager.ConnectionStrings["BeanCounterDB"].ToString())) { myConnection.Open(); using (OleDbCommand myCommand = new OleDbCommand(cmdText, myConnection)) { using (OleDbDataReader myDataReader = myCommand.ExecuteReader()) { while (myDataReader.Read()) { Transaction transaction = new Transaction( Convert.ToBoolean(myDataReader["Verified"].ToString()), Convert.ToDateTime(myDataReader["TransactionDate"].ToString()), myDataReader["Merchant"].ToString(), Convert.ToDecimal(myDataReader["TransactionAmount"].ToString()), myDataReader["CategoryName"].ToString(), myDataReader["BankMemo"].ToString(), Convert.ToInt32(myDataReader["OrginalTransactionID"].ToString()), myDataReader["UserMemo"].ToString(), myDataReader["CheckNumber"].ToString(), myDataReader["TransactionType"].ToString() ); transactions.Add(transaction); } } } return transactions; } }
public static Transaction CheckBankTricks(Transaction transaction, BankAccount bankAccount) { if (transaction.TransactionType.ToLower() == "check") { transaction.MerchantName = "?"; transaction.BankMemo = ""; } else { string merchantName = ""; string bankMemo = ""; if (bankAccount.ReverseFields) { merchantName = transaction.BankMemo; bankMemo = transaction.MerchantName; } else { merchantName = transaction.MerchantName; bankMemo = transaction.BankMemo; } transaction.MerchantName = merchantName; transaction.BankMemo = bankMemo; switch (bankAccount.RemoveFromMerchant) { case "[Remove Merchant Name]": transaction.MerchantName = RemovePartialMerchantName(transaction.MerchantName, transaction.BankMemo.Replace(bankAccount.RemoveFromBankMemo, "")).Trim(); break; case "[Everything]": transaction.MerchantName = ""; break; default: if (!string.IsNullOrEmpty(bankAccount.RemoveFromMerchant)) transaction.MerchantName = transaction.MerchantName.Replace(bankAccount.RemoveFromMerchant, "").Trim(); break; } switch (bankAccount.RemoveFromBankMemo) { case "[Remove Merchant Name]": transaction.BankMemo = RemovePartialMerchantName(transaction.BankMemo, transaction.MerchantName.Replace(bankAccount.RemoveFromMerchant, "")).Trim(); break; case "[Everything]": transaction.BankMemo = ""; break; default: if (!string.IsNullOrEmpty(bankAccount.RemoveFromBankMemo)) transaction.BankMemo = transaction.BankMemo.Replace(bankAccount.RemoveFromBankMemo, "").Trim(); break; } } return transaction; }