public ILineItem NewLineItem()
{
MySqlLineItem item = new MySqlLineItem( this.Provider.lineItemsContext.NewRow() );
item.TransactionId = this.Id;
return item;
}
/// <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;
}