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;
}
