public override void SetOnlineRecord( IOnlineRecord otrans )
{
if( otrans == null )
{
this.onlineRecord = null;
this.OnlineRecordId = null;
return;
}
if( !(otrans is XmlOnlineRecord) )
throw new ArgumentOutOfRangeException( "Must be an XmlOnlineRecord" );
this.OnlineRecordId = otrans.Id;
this.onlineRecord = (XmlOnlineRecord)otrans;
}
protected void Page_Load( object sender, EventArgs e )
{
this.otrans = WebGlobals.Provider.GetOnlineRecord( this.Id );
this.account = WebGlobals.Provider.GetAccountBySource( this.otrans.OnlineSource );
this.lblDate.Text = string.Format( "{0:M/d/yyyy}", this.otrans.Date );
this.lblAmount.Text = string.Format( "{0:N2}", this.otrans.Amount );
this.lblDescription.Text = this.otrans.Description;
DataHelper helper = new DataHelper( WebGlobals.Provider );
this.bestItem = helper.GetBestMatchingLineItem( this.account, this.otrans );
this.items.AddRange( WebGlobals.Provider.GetPotentialMatchingLineItems( this.account, this.otrans ) );
this.records = WebGlobals.Provider.GetRecordsByDescription( this.otrans.OnlineSource, this.otrans.Description );
}
public void SaveRecord( IOnlineRecord record )
{
SaveDataObject( (SqlRecord)record );
}
public IOnlineRecord FindRecord( IOnlineRecord record )
{
// CREATE UNIQUE NONCLUSTERED INDEX [IDX_Unique] ON [dbo].[Record]
// ( [Source], [Description], [PostDate], [Amount], [BankId], [Number] );
SqlRecord foundRecord = new SqlRecord();
foundRecord.OnlineSource = record.OnlineSource;
foundRecord.BankId = record.BankId;
foundRecord.Date = record.Date;
foundRecord.Description = record.Description;
foundRecord.Amount = record.Amount;
foundRecord.Number = record.Number;
foundRecord.SetParameters( command.Parameters );
foundRecord.FillParameters( command.Parameters );
SqlDataReader data = ExecuteReader( "SELECT ID, LineItemID FROM [{0}] WHERE [Source]=@source AND [Description]=@description AND [PostDate]=@date AND [Amount]=@amount AND [BankId]=@bankId AND [Number]=@number",
recordTemplate.TableName );
try
{
if( data.Read() )
{
foundRecord.SetSaved( true );
foundRecord.Id = Convert.ToInt32( data["ID"] );
foundRecord.LineItemId = Convert.ToInt32( data["LineItemID"] );
}
else
{
// No record found in the database.
foundRecord = null;
}
}
finally
{
data.Close();
}
return foundRecord;
}
public void SaveMatchingRecord( ILineItem item, IOnlineRecord record )
{
ExecuteNonQuery( "UPDATE [{0}] SET LineItemId={2} WHERE Id={1}",
recordTemplate.TableName, record.Id, item.Id );
}
/// <summary>
/// Gets a line item that matches the information in the given record.
/// </summary>
/// <param name="account">IAccount in which to search.</param>
/// <param name="record">IOnlineRecord with properties to match.</param>
/// <returns>
/// ILineItem object of best match or null if no match found.
/// </returns>
public IEnumerable<ILineItem> GetPotentialMatchingLineItem( IAccount account, IOnlineRecord record )
{
bool ignorePriorMatches = false;
// Determine whether to search for a credit or debit transaction.
string searchType;
if( account.AccountType == AccountType.Asset ||
account.AccountType == AccountType.Checking ||
account.AccountType == AccountType.Savings )
{
if( record.Amount < 0 ) searchType = "C";
else searchType = "D";
}
else if( account.AccountType == AccountType.Liability ||
account.AccountType == AccountType.CreditCard )
{
if( record.Amount < 0 ) searchType = "C";
else searchType = "D";
}
else
throw new ApplicationException( "Unsupported account type." );
// Enforce a minimum starting date.
// This is because SQL Server chokes on dates prior to 1753,
// so we can't just pass it DateTime.MinValue.
DateTime minDate = new DateTime( 1753, 1, 1 );
if( account.StartingDate != null )
minDate = account.StartingDate.Value;
// Get a short list of possible candidates.
// 1. Match must be for same account
// 2. Match must be on or before the post date
// 3. Match must be same amount and transaction type
List<ILineItem> matches = new List<ILineItem>();
using( MySqlCommand command = lineItemsContext.CreateCommand() )
{
string extraClause = string.Empty;
if( ignorePriorMatches ) extraClause = "r.ID IS NULL AND";
command.CommandText = string.Format(
"SELECT * FROM LineItem l" +
" INNER JOIN Account a ON (a.ID = l.AccountID)" +
" LEFT OUTER JOIN OnlineRecord r ON (r.LineItemID = l.ID)" +
" WHERE {0} a.OnlineSource=@source" +
" AND l.IsVoided=0" +
" AND l.TransactionType=@type" +
" AND l.TransactionDate>=@mindate" +
" AND l.TransactionDate<=@date" +
" AND l.Amount=@amount" +
" ORDER BY l.TransactionDate DESC", extraClause );
command.Parameters.AddWithValue( "@source", record.OnlineSource );
command.Parameters.Add( "@date", MySqlDbType.Date ).Value = record.Date.Date;
command.Parameters.Add( "@mindate", MySqlDbType.Date ).Value = minDate.Date;
command.Parameters.AddWithValue( "@type", searchType );
command.Parameters.AddWithValue( "@amount", Math.Abs( record.Amount ) );
DataTable table = this.lineItemsContext.GetRows( command );
foreach( DataRow row in table.Rows )
{
MySqlLineItem match = new MySqlLineItem( row );
matches.Add( match );
}
}
return matches;
}
public ILineItem FindMatchingLineItem( IAccount account, IOnlineRecord record, bool allowDuplicates )
{
// Determine whether to search for a credit or debit transaction.
string searchType;
if( account.AccountType == AccountType.Asset ||
account.AccountType == AccountType.Checking ||
account.AccountType == AccountType.Savings )
{
if( record.Amount < 0 ) searchType = "C";
else searchType = "D";
}
else if( account.AccountType == AccountType.Liability ||
account.AccountType == AccountType.CreditCard )
{
if( record.Amount < 0 ) searchType = "D";
else searchType = "C";
}
else
throw new ApplicationException( "Unsupported account type." );
// Enforce a minimum starting date of 1800.
// This is because SQL Server chokes on dates prior to 1753,
// so we can't just pass it DateTime.MinValue.
DateTime minDate = new DateTime( 1800, 1, 1 );
if( account.StartingDate != null )
minDate = account.StartingDate.Value;
// Get a short list of possible candidates.
// 1. Match must be for same account
// 2. Match must be on or before the post date
// 3. Match must be same amount and transaction type
command.Parameters.Clear();
command.Parameters.Add( "@source", SqlDbType.VarChar, 100 ).Value = record.OnlineSource;
command.Parameters.Add( "@date", SqlDbType.DateTime ).Value = record.Date;
command.Parameters.Add( "@mindate", SqlDbType.DateTime ).Value = minDate;
command.Parameters.Add( "@type", SqlDbType.Char, 1 ).Value = searchType;
command.Parameters.Add( "@amount", SqlDbType.Decimal, 2 ).Value = Math.Abs( record.Amount );
string dupeClause = string.Empty;
if( !allowDuplicates ) dupeClause = "r.ID IS NULL AND";
List<ILineItem> matches = new List<ILineItem>();
SqlDataReader data = ExecuteReader(
"SELECT * FROM [LineItem] l" +
" INNER JOIN [Account] a ON (a.ID = l.AccountID)" +
" LEFT JOIN [Record] r ON (r.LineItemID = l.ID)" +
" WHERE {0} a.Source=@source" +
" AND l.TransactionDate>=@mindate" +
" AND l.TransactionDate<=@date" +
" AND l.TransactionType=@type" +
" AND l.Amount=@amount" +
" ORDER BY l.[TransactionDate] DESC", dupeClause );
try
{
while( data.Read() )
{
SqlLineItem match = new SqlLineItem();
match.SetSaved( true );
match.Fill( data );
matches.Add( match as ILineItem );
}
}
finally
{
data.Close();
}
// Further tests.
if( matches.Count == 0 )
{
// No results, can just exit.
return null;
}
else if( matches.Count == 1 )
{
// The above filter will get most cases
return matches[0];
}
// Returned more than one possible match.
// Narrow it down to one.
SqlLineItem minLineItem = null;
int minDays = Int32.MaxValue;
foreach( SqlLineItem item in matches )
{
// If one of the matches has the same number, return it.
if( record.Number.Equals( item.Number, StringComparison.Ordinal ) )
return item;
// Otherwise pick the match closest to the record date.
TimeSpan span = record.Date - item.TransactionDate;
if( span.Days < minDays )
{
minDays = span.Days;
minLineItem = item;
}
}
return minLineItem;
}
public bool Contains( IOnlineRecord record )
{
foreach( IOnlineRecord otrans in this.Transactions )
{
if( otrans == record ) return true;
}
return false;
}
public void SetOnlineRecord( IOnlineRecord otrans )
{
if( otrans != null )
this.OnlineRecordId = otrans.Id;
else
this.OnlineRecordId = null;
}
public IOnlineRecord FindRecord( IOnlineRecord record )
{
throw new NotImplementedException();
}
public ILineItem GetMatchingLineItem( IAccount account, IOnlineRecord record )
{
throw new NotImplementedException();
}
public void SaveRecord( IOnlineRecord record )
{
Assert.PreCondition( ledger != null );
saveOnClose = true;
}
public void CreateMissingRecord( IOnlineRecord record )
{
throw new NotImplementedException();
}
public void SaveMatchingRecord( ILineItem item, IOnlineRecord record )
{
throw new NotImplementedException();
}
/// <summary>
/// Add a transaction to the list and generate an appropriate sequence
/// number for it. If the transaction takes place on the same day as
/// a previous transaction, a new sequence number is created.
/// Otherwise, the default sequence number of 0 is applied.
/// </summary>
/// <param name="otrans"></param>
public void AddWithSequence( IOnlineRecord otrans )
{
int sequence = 0;
// Sequence numbers are generated for transactions that occur on
// the same date to distinguish them. This assumes that the bank
// is always going to list their transactions in the same order.
string key = otrans.Date.ToString( "yyyyMMdd" );
if( this.keyCounts.ContainsKey( key ) )
sequence = this.keyCounts[key]++;
else
this.keyCounts.Add( key, 1 );
otrans.Sequence = sequence;
this.Transactions.Add( otrans );
}
public IOnlineRecord FindDuplicateByDescription( IOnlineRecord record )
{
foreach( IOnlineRecord otrans in this.Transactions )
{
if( otrans.Date == record.Date &&
otrans.Description == record.Description &&
otrans.Amount == record.Amount ) return otrans;
}
return null;
}
public ILineItemData FindMatchingLineItem( IOnlineRecord record )
{
return storage.FindMatchingLineItem( record );
}
/// <summary>
/// Gets an online record from the data store matching the criteria
/// in the given record.
/// </summary>
/// <param name="record">Online record with properties to match.</param>
/// <returns>IOnlineRecord object or null if not found.</returns>
public IOnlineRecord GetMatchingRecord( IOnlineRecord record )
{
List<XmlOnlineRecord> potentialMatches = new List<XmlOnlineRecord>();
foreach( XmlOnlineRecord otrans in this.records.Values )
{
if( otrans.OnlineSource == record.OnlineSource
&& otrans.Date == record.Date
&& otrans.Amount == record.Amount
&& otrans.Sequence == record.Sequence )
potentialMatches.Add( otrans );
}
if( potentialMatches.Count > 1 )
throw new DataProviderException(
"There are more than one potential online record matches for " + record.ToString() );
if( potentialMatches.Count == 1 )
return potentialMatches[0];
else
return null;
}
/// <summary>
/// Get the single best matching line item for the given record.
/// </summary>
/// <param name="account"></param>
/// <param name="record"></param>
/// <returns>An ILineItem or null if there were no matches.</returns>
public ILineItem GetBestMatchingLineItem( IAccount account, IOnlineRecord record )
{
List<ILineItem> matches = new List<ILineItem>(
this.Provider.GetPotentialMatchingLineItems( account, record ) );
foreach( ILineItem item in new List<ILineItem>( matches ) )
{
// If we have a Number, use it as the primary matching factor.
if( record.Number != null )
{
// If the line item doesn't have a number, it can never match.
if( item.Number == null )
matches.Remove( item );
// If the numbers don't match, remove it.
if( item.Number != record.Number )
matches.Remove( item );
}
if( record.Number == null && item.Number == null )
{
// As a precaution, cannot match non-checks
// to a transaction more than 30 days older.
TimeSpan span = record.Date - item.TransactionDate;
if( span.TotalDays > 30 ) matches.Remove( item );
}
}
if( matches.Count == 0 )
return null;
else if( matches.Count == 1 )
return matches[0];
// There are still multiple potential matches.
// At this point we need to try some more clever schemes.
int maxRelevance = 0;
ILineItem maxRelevanceItem = null;
// Attempt to match a line item with a similar description.
foreach( ILineItem item in new List<ILineItem>( matches ) )
{
ITransaction trans = this.Provider.GetTransaction( item.TransactionId );
int relevance = this.CompareDescriptions( trans.Payee, record.Description );
if( relevance >= maxRelevance )
{
maxRelevance = relevance;
maxRelevanceItem = item;
}
}
if( maxRelevanceItem != null )
return maxRelevanceItem;
// If there are STILL multiple potential matches,
// Pick the one with a date nearest to the online record.
ILineItem minItem = null;
int minDays = Int32.MaxValue;
foreach( ILineItem item in matches )
{
TimeSpan span = record.Date - item.TransactionDate;
if( span.Days <= minDays )
{
minDays = span.Days;
minItem = item;
}
}
return minItem;
}
public IEnumerable<ILineItem> GetPotentialMatchingLineItems( IAccount account, IOnlineRecord record )
{
throw new NotImplementedException();
}
public IOnlineRecord GetMatchingRecord( IOnlineRecord record )
{
throw new NotImplementedException();
}
public ILineItemData FindMatchingLineItem( IOnlineRecord record )
{
Assert.PreCondition( ledger != null );
return ledger.SearchForMatchingLineItem( (OnlineTransaction)record, true );
}
/// <summary>
/// Get all potential matching line items for the given record.
/// It is assumed the caller will prune down the list even more.
/// </summary>
/// <param name="account"></param>
/// <param name="record"></param>
/// <returns></returns>
public IEnumerable<ILineItem> GetPotentialMatchingLineItems( IAccount account, IOnlineRecord record )
{
TransactionType searchType;
if( account.IsAssetAccount() )
{
if( record.Amount < 0 ) searchType = TransactionType.Credit;
else searchType = TransactionType.Debit;
}
else if( account.IsLiabilityAccount() )
{
if( record.Amount < 0 ) searchType = TransactionType.Debit;
else searchType = TransactionType.Credit;
}
else
throw new DataProviderException( "Unsupported account type " + account.AccountType.ToString() );
List<ILineItem> matches = new List<ILineItem>();
foreach( XmlLineItem item in this.lineItems.Values )
{
// Ensure is from the right account
if( item.AccountId != account.Id ) continue;
// Don't match unless it is the right type of transaction.
if( item.TransactionType != searchType ) continue;
// Never match a reconciled line item
if( item.IsReconciled ) continue;
// Never match a voided line item
if( item.IsVoided ) continue;
// Don't match a line item which was already matched to something
if( item.OnlineRecordId != null ) continue;
// If amounts are the same, it could be a match.
if( item.Amount != Math.Abs( record.Amount ) ) continue;
// But...
// Online transaction must be later than or same date as transaction
if( record.Date < item.TransactionDate ) continue;
// If passed tests above, add to list of matches.
matches.Add( item );
}
return matches;
}
public ILineItem FindMatchingLineItem( IAccount account, IOnlineRecord record )
{
return FindMatchingLineItem( account, record, false );
}
public virtual void SetOnlineRecord( IOnlineRecord otrans )
{
if( otrans == null )
throw new ArgumentNullException( "An online record object must be provided" );
this.OnlineRecordId = otrans.Id;
}
/// <summary>
/// Gets an online record from the data store matching the criteria
/// in the given record.
/// </summary>
/// <param name="record">Online record with properties to match.</param>
/// <returns>
/// IOnlineRecord object or null if not found.
/// </returns>
public IOnlineRecord GetMatchingRecord( IOnlineRecord record )
{
// Uses the source/date/sequence/amount to identify the record.
using( MySqlCommand command = onlineRecordsContext.CreateCommand() )
{
command.CommandText = SQL.SelectMatchingRecord;
command.Parameters.AddWithValue( "@source", record.OnlineSource );
command.Parameters.AddWithValue( "@date", record.Date );
command.Parameters.AddWithValue( "@amount", record.Amount );
command.Parameters.AddWithValue( "@sequence", record.Sequence );
DataTable table = this.onlineRecordsContext.GetRows( command );
if( table.Rows.Count == 1 )
{
// If only one row found, return it.
return new MySqlOnlineRecord( table.Rows[0] );
}
}
// Otherwise if row was not found, return null.
return null;
}