public static List <ListDisplay.ListItem> LoadAllByDate(string path)
{
string commandString = "SELECT * FROM CardInfo ORDER BY date";
SQLiteConnection database = new SQLiteConnection("Data Source=" + path + ";Version=3;");
SQLiteCommand command = new SQLiteCommand(commandString, database);
List <ListDisplay.ListItem> output = new List <ListDisplay.ListItem>();
SQLiteDataReader rowData;
database.Open();
rowData = command.ExecuteReader();
/*Populate collection*/
while (rowData.Read())
{
output.Add(ListDisplay.FormListItem(rowData));
}
database.Close();
return(output);
}
public static List <ListDisplay.ListItem> PerformFilter(DateTime?fromDate, DateTime?toDate,
string filterCard, string ignoreCard, string path)
{
string commandString;
SQLiteConnection database = new SQLiteConnection("Data Source=" + path + ";Version=3;");
SQLiteCommand command;
List <ListDisplay.ListItem> output = new List <ListDisplay.ListItem>();
SQLiteDataReader rowData;
bool flagDate = false, flagOne = false;
//Form command
commandString = "SELECT * FROM CardInfo WHERE ";
//Date filtering
if (fromDate != null && toDate != null)
{
commandString += "Date BETWEEN \"" + Utilities.FormSQLDateFormat(fromDate.Value, true)
+ "\" AND \"" + Utilities.FormSQLDateFormat(toDate.Value, false) + "\" ";
flagDate = true;
}
else if (fromDate == null && toDate != null)
{
commandString += "Date < \"" + Utilities.FormSQLDateFormat(toDate.Value, true) + "\" ";
flagDate = true;
}
else if (toDate == null && fromDate != null)
{
commandString += "Date > \"" + Utilities.FormSQLDateFormat(fromDate.Value, false) + "\" ";
flagDate = true;
}
//Special snowflake
if (filterCard != "Kod karty" && filterCard != null && filterCard != "")
{
if (flagDate)
{
commandString += "AND ";
}
commandString += "AccountID = \"" + filterCard + "\" ";
flagOne = true;
}
//Ignore card
if (ignoreCard != "Kod karty" && ignoreCard != null && ignoreCard != "")
{
if (flagDate || flagOne)
{
commandString += "AND ";
}
commandString += "AccountID != \"" + ignoreCard + "\" ";
}
commandString += "ORDER BY date";
command = new SQLiteCommand(commandString, database);
database.Open();
try
{
rowData = command.ExecuteReader();
}
catch (System.Data.SQLite.SQLiteException)
{
return(null);
}
/*Populate collection*/
while (rowData.Read())
{
output.Add(ListDisplay.FormListItem(rowData));
}
database.Close();
return(output);
}
