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