/// <summary>
/// Gets rows from the table fulfilling the criteria.
/// </summary>
/// <param name="wFilter">A filter to use as where condition</param>
/// <returns>All rows fulfilling the criteria</returns>
public IEnumerable <BuchungRow> GetRowsByFilter(BuchungFilter wFilter)
{
return(mTable.GetRowsByFilter(wFilter));
}
/// <summary>
/// Gets rows from the table fulfilling the criteria.
/// </summary>
/// <param name="wFilter">A filter to use as where condition</param>
/// <returns>All rows fulfilling the criteria</returns>
public IEnumerable <BuchungRow> GetRowsByFilter(BuchungFilter wFilter)
{
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "SELECT [ID],[IDBudget],[IDDauerauftrag],[Bezeichnung],[Datum],[Betrag],[Bemerkung] FROM [Buchung]";
StringBuilder sbWhere = new StringBuilder();
StringBuilder sbColumnWhere;
string wComboWord = (wFilter.ComboMode == OryxFilterCombinationEnum.And ? " AND " : " OR ");
sbColumnWhere = new StringBuilder();
if (wFilter.IDBudget != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_IDBudget + "=@wIDBudget");
command.Parameters.AddWithValue("wIDBudget", wFilter.IDBudget);
}
if (sbColumnWhere.Length > 0)
{
if (sbWhere.Length > 0)
{
sbWhere.Append(wComboWord);
}
sbWhere.Append(sbColumnWhere.ToString());
}
sbColumnWhere = new StringBuilder();
if (wFilter.IDDauerauftrag != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_IDDauerauftrag + "=@wIDDauerauftrag");
command.Parameters.AddWithValue("wIDDauerauftrag", wFilter.IDDauerauftrag);
}
if (wFilter.IDDauerauftragMinWo != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_IDDauerauftrag + ">@wIDDauerauftragMinWo");
command.Parameters.AddWithValue("wIDDauerauftragMinWo", wFilter.IDDauerauftragMinWo);
}
if (sbColumnWhere.Length > 0)
{
if (sbWhere.Length > 0)
{
sbWhere.Append(wComboWord);
}
sbWhere.Append(sbColumnWhere.ToString());
}
sbColumnWhere = new StringBuilder();
if (wFilter.Bezeichnung != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Bezeichnung + "=@wBezeichnung");
command.Parameters.AddWithValue("wBezeichnung", wFilter.Bezeichnung);
}
if (wFilter.BezeichnungPattern != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Bezeichnung + " LIKE @wBezeichnungPattern");
command.Parameters.AddWithValue("wBezeichnungPattern", wFilter.BezeichnungPattern);
}
if (sbColumnWhere.Length > 0)
{
if (sbWhere.Length > 0)
{
sbWhere.Append(wComboWord);
}
sbWhere.Append(sbColumnWhere.ToString());
}
sbColumnWhere = new StringBuilder();
if (wFilter.Datum != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Datum + "=@wDatum");
command.Parameters.AddWithValue("wDatum", this.GetAccessDateTime(wFilter.Datum.Value));
}
if (wFilter.DatumMaxWo != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Datum + "<@wDatumMaxWo");
command.Parameters.AddWithValue("wDatumMaxWo", this.GetAccessDateTime(wFilter.DatumMaxWo.Value));
}
if (wFilter.DatumMinWo != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Datum + ">@wDatumMinWo");
command.Parameters.AddWithValue("wDatumMinWo", this.GetAccessDateTime(wFilter.DatumMinWo.Value));
}
if (wFilter.DatumMax != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Datum + "<=@wDatumMax");
command.Parameters.AddWithValue("wDatumMax", this.GetAccessDateTime(wFilter.DatumMax.Value));
}
if (wFilter.DatumMin != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Datum + ">=@wDatumMin");
command.Parameters.AddWithValue("wDatumMin", this.GetAccessDateTime(wFilter.DatumMin.Value));
}
if (sbColumnWhere.Length > 0)
{
if (sbWhere.Length > 0)
{
sbWhere.Append(wComboWord);
}
sbWhere.Append(sbColumnWhere.ToString());
}
sbColumnWhere = new StringBuilder();
if (wFilter.Betrag != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Betrag + "=@wBetrag");
command.Parameters.AddWithValue("wBetrag", wFilter.Betrag);
}
if (wFilter.BetragMaxWo != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Betrag + "<@wBetragMaxWo");
command.Parameters.AddWithValue("wBetragMaxWo", wFilter.BetragMaxWo);
}
if (wFilter.BetragMinWo != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Betrag + ">@wBetragMinWo");
command.Parameters.AddWithValue("wBetragMinWo", wFilter.BetragMinWo);
}
if (wFilter.BetragMax != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Betrag + "<=@wBetragMax");
command.Parameters.AddWithValue("wBetragMax", wFilter.BetragMax);
}
if (wFilter.BetragMin != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Betrag + ">=@wBetragMin");
command.Parameters.AddWithValue("wBetragMin", wFilter.BetragMin);
}
if (sbColumnWhere.Length > 0)
{
if (sbWhere.Length > 0)
{
sbWhere.Append(wComboWord);
}
sbWhere.Append(sbColumnWhere.ToString());
}
sbColumnWhere = new StringBuilder();
if (wFilter.Bemerkung != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Bemerkung + "=@wBemerkung");
command.Parameters.AddWithValue("wBemerkung", wFilter.Bemerkung);
}
if (wFilter.BemerkungPattern != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Bemerkung + " LIKE @wBemerkungPattern");
command.Parameters.AddWithValue("wBemerkungPattern", wFilter.BemerkungPattern);
}
if (wFilter.BemerkungIsNull != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Insert(0, "((");
sbColumnWhere.Append(string.Format(") OR {0} IS {1}NULL)", SQL_COL_Bemerkung, (wFilter.BemerkungIsNull.HasValue && wFilter.BemerkungIsNull.Value ? "" : "NOT ")));
}
else
{
sbColumnWhere.Append(string.Format("{0} IS {1}NULL", SQL_COL_Bemerkung, (wFilter.BemerkungIsNull.HasValue && wFilter.BemerkungIsNull.Value ? "" : "NOT ")));
}
}
if (sbColumnWhere.Length > 0)
{
if (sbWhere.Length > 0)
{
sbWhere.Append(wComboWord);
}
sbWhere.Append(sbColumnWhere.ToString());
}
// Add the WHERE term
if (sbWhere.Length > 0)
{
command.CommandText += (" WHERE " + sbWhere.ToString());
}
command.CommandText += ";";
DataTable resultTable = base.ExecuteCommand(command);
List <BuchungRow> result = new List <BuchungRow>(resultTable.Rows.Count);
foreach (DataRow oneRow in resultTable.Rows)
{
result.Add(RowToDto(oneRow));
}
return(result);
}
}
