public bool LoadCheckReconcile(string accountId)
{
SaveRecord();
m_registerRecs.Clear();
textDate.Text = "";
comboType.SelectedIndex = -1;
textCheckNumber.Text = "";
comboDescription.Text = "";
comboDescription.SelectedIndex = -1;
comboDescription.Items.Clear();
textAmount.Text = "";
checkCleared.Checked = false;
checkArchived.Checked = false;
m_accountId = accountId;
string sql = string.Format("select description from vendor_names where account_id = '{0}'", accountId);
OleDbCommand selectCmd = new OleDbCommand(sql, m_dbConnection);
OleDbDataReader dataReader = selectCmd.ExecuteReader();
while(dataReader.Read())
{
comboDescription.Items.Add(dataReader[0].ToString());
}
dataReader.Close();
sql = string.Format("select * from check_register where account_id = '{0}' and cleared = 0 order by trans_date", accountId);
selectCmd = new OleDbCommand(sql, m_dbConnection);
dataReader = selectCmd.ExecuteReader();
while(dataReader.Read())
{
CheckRegister rec = new CheckRegister();
rec.Load(dataReader);
m_registerRecs.Add(rec);
}
dataReader.Close();
sql = string.Format("select * from bank_transactions where account_id = '{0}' and check_register_id is null", accountId);
selectCmd = new OleDbCommand(sql, m_dbConnection);
dataReader = selectCmd.ExecuteReader();
listTransactions.Items.Clear();
while(dataReader.Read())
{
BankTransaction tran = new BankTransaction();
tran.Load(dataReader);
ListViewItem item = new ListViewItem();
item.Tag = tran;
tran.Tag = item;
item.Text = tran.TransType;
item.SubItems.Add(tran.CheckNumber);
if("" == tran.TransName)
{
item.SubItems.Add(tran.Description);
}
else
{
item.SubItems.Add(string.Format("{0} - {1}", tran.TransName, tran.Description));
}
item.SubItems.Add(tran.TransDate.ToShortDateString());
item.SubItems.Add(tran.TransAmount.ToString("C"));
listTransactions.Items.Add(item);
}
dataReader.Close();
UpdateBalances();
if(m_registerRecs.Count > 0)
{
CheckRegister rec = (CheckRegister)m_registerRecs[0];
DisplayRegisterRec(rec);
}
return true;
}
private void ImportTransactions(string fileName)
{
Cursor c = Cursor;
Cursor = Cursors.WaitCursor;
Account account = null;
ImportData import = new ImportData();
StreamReader reader = null;
bool showReconcileScreen = false;
try
{
reader = new StreamReader(fileName);
import.ImportDate = DateTime.Now;
import.Data = reader.ReadToEnd();
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show(this, string.Format("The following error occurred while reading the import file {0}\n\n{1)\n\nImport failed!", fileName, ex.Message), "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
try
{
reader = new StreamReader(fileName);
string tag = "";
BankTransaction tran = null;
while ("" != (tag = FindNextTag(reader)))
{
switch (tag)
{
case "<ACCTID>":
{
string accountNum = GetTagValue(reader);
account = m_accountRecs[accountNum] as Account;
if (account == null)
{
MessageBox.Show(this, string.Format("There is no account with Account Number '{0}'. Import failed!"), "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
DialogResult dr = MessageBox.Show(this, string.Format("Import bank transactions into {0}?", account.AccountName), "Import?", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.No)
{
return;
}
showReconcileScreen = true;
account.LastImport = DateTime.Today.ToString("d");
account.Store(m_dbConnection);
import.AccountId = account.Id;
import.Store(m_dbConnection);
}
break;
case "<STMTTRN>":
if (account == null)
{
MessageBox.Show(this, "Invalid import file! Import failed!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
tran = new BankTransaction();
tran.AccountId = account.Id;
break;
case "</STMTTRN>":
if (tran == null)
{
MessageBox.Show(this, "Invalid import file! Import failed!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
try
{
tran.Store(m_dbConnection);
}
catch (OleDbException dbEx)
{
if (-2147467259 != dbEx.ErrorCode)
{
//this is not a duplicate primary key error, so display it
string msg = string.Format("The following error occurred while saving a bank transaction record:\n\n{0}\n\nThe record was not saved. Click OK to continue with the next record, or Cancel.", dbEx.Message);
if (DialogResult.Cancel == MessageBox.Show(this, msg, "Error!", MessageBoxButtons.OKCancel, MessageBoxIcon.Error))
{
reader.Close();
return;
}
}
}
catch (Exception ex)
{
string msg = string.Format("The following error occurred while saving a bank transaction record:\n\n{0}\n\nThe record was not saved. Click OK to continue with the next record, or Cancel.", ex.Message);
if (DialogResult.Cancel == MessageBox.Show(this, msg, "Error!", MessageBoxButtons.OKCancel, MessageBoxIcon.Error))
{
reader.Close();
Cursor = c;
return;
}
}
break;
case "<TRNTYPE>":
//this trans type is a generic name for the transaction.
//the <NAME> field provides a nicer descripton for the
//trans type
if (tran == null)
{
MessageBox.Show(this, "Invalid import file! Import failed!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
tran.TransType = GetTagValue(reader);
break;
case "<DTPOSTED>":
{
if (tran == null)
{
MessageBox.Show(this, "Invalid import file! Import failed!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string transDate = GetTagValue(reader);
string dateFormatted = string.Format("{0}/{1}/{2}", transDate.Substring(4, 2), transDate.Substring(6, 2), transDate.Substring(0, 4));
tran.TransDate = DateTime.Parse(dateFormatted);
}
break;
case "<TRNAMT>":
{
if (tran == null)
{
MessageBox.Show(this, "Invalid import file! Import failed!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string amt = GetTagValue(reader);
tran.TransAmount = Convert.ToDouble(amt);
}
break;
case "<FITID>":
if (tran == null)
{
MessageBox.Show(this, "Invalid import file! Import failed!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
tran.TransactionId = GetTagValue(reader);
break;
case "<CHECKNUM>":
{
if (tran == null)
{
MessageBox.Show(this, "Invalid import file! Import failed!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string checkNum = GetTagValue(reader);
if ("000000" != checkNum)
{
checkNum = checkNum.TrimStart(new char[] { '0' });
tran.CheckNumber = checkNum;
}
}
break;
case "<NAME>":
if (tran == null)
{
MessageBox.Show(this, "Invalid import file! Import failed!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
tran.TransName = GetTagValue(reader);
break;
case "<MEMO>":
if (tran == null)
{
MessageBox.Show(this, "Invalid import file! Import failed!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
tran.Description = GetTagValue(reader);
break;
}
}
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if(reader != null) reader.Close();
if (showReconcileScreen)
{
foreach (TreeNode node in treeManagement.Nodes)
{
if (node.Tag.ToString() == account.AccountNumber)
{
if (treeManagement.SelectedNode != node.Nodes[1])
{
treeManagement.SelectedNode = node.Nodes[1];
}
else
{
//force a refresh
m_checkReconcile.LoadCheckReconcile(account.Id);
m_checkReconcile.SetDefaultButton();
}
}
}
}
Cursor = c;
}
}
private void FindMatches()
{
listMatches.Items.Clear();
CheckRegister rec = (CheckRegister)Tag;
string sql = string.Format("select * from bank_transactions where account_id = '{0}' and (trans_amount = {1} or trans_amount = -{1}) and check_register_id is null", rec.AccountId, rec.Amount);
OleDbCommand selectCmd = new OleDbCommand(sql, m_dbConnection);
OleDbDataReader dataReader = selectCmd.ExecuteReader();
ListViewItem item = null;
while(dataReader.Read())
{
BankTransaction tran = new BankTransaction();
tran.Load(dataReader);
item = new ListViewItem();
item.Tag = tran;
tran.Tag = item;
item.Text = tran.TransType;
item.SubItems.Add(tran.CheckNumber);
if("" != tran.TransName)
{
item.SubItems.Add(tran.TransName);
}
else
{
item.SubItems.Add(tran.Description);
}
item.SubItems.Add(tran.TransDate.ToShortDateString());
item.SubItems.Add(tran.TransAmount.ToString("C"));
listMatches.Items.Add(item);
}
dataReader.Close();
}
