public DataRow getImageById(long id, long offset, string sortByColumn, SortOrder sortByDirection)
{
string tableName = this.filterReady();
SQLiteCommand command;
DataRow dr;
if (offset == 0) {
command = new SQLiteCommand("SELECT * FROM `" + tableName + "` WHERE (id = @id) LIMIT 1;", this.dbConnector.connection);
command.Parameters.AddWithValue("@id", id);
dr = DBConnector.executeReaderFirstDataRow(command);
if (dr == null) {
return this.getFirstImage(sortByColumn, sortByDirection);
}
} else {
if (sortByColumn == null) sortByColumn = "path";
if (sortByDirection == null) sortByDirection = new SortOrder(SortOrder.Direction.ASC);
string than = "<";
if (sortByDirection.isDESC()) offset *= -1;
if (offset < 0) {
than = "<";
sortByDirection.setDESC();
} else {
than = ">";
sortByDirection.setASC();
}
string sqlValue = "(SELECT " + sortByColumn + " FROM `" + tableName + "` WHERE `" + tableName + "`.id = @id)";
string sql = "FROM `" + tableName + "` WHERE (" + sortByColumn + " " + than + " " + sqlValue + ") " +
" OR ((" + sortByColumn + " = " + sqlValue + " AND id " + than + " @id)) " +
"ORDER BY " + sortByColumn + " " + sortByDirection.ToString() + ", id " + sortByDirection.ToString();
command = new SQLiteCommand(@"SELECT * " + sql + " LIMIT 1 OFFSET " + (Math.Abs(offset)-1) + ";", this.dbConnector.connection);
command.Parameters.AddWithValue("@id", id);
dr = DBConnector.executeReaderFirstDataRow(command);
if (dr == null) {
command = new SQLiteCommand(@"SELECT COUNT(`" + tableName + "`.id) " + sql, this.dbConnector.connection);
command.Parameters.AddWithValue("@id", id);
dr = DBConnector.executeReaderFirstDataRow(command);
if (dr != null) {
if (offset < 0) offset += Convert.ToInt32(dr[0])+1;
else offset -= Convert.ToInt32(dr[0])+1;
if (this.nrImagesFilter() > 0) {
offset = offset % this.nrImagesFilter();
}
dr = getFirstImage(sortByColumn, sortByDirection, offset);
}
}
}
return dr;
}
public DataRow getFirstImage(string orderBy, SortOrder direction, long offset)
{
string tableName = this.filterReady();
SQLiteCommand command;
if (orderBy == null) orderBy = "created";
if (direction == null) direction = new SortOrder(SortOrder.Direction.ASC);
command = new SQLiteCommand("SELECT * FROM `" + tableName + "` ORDER BY " + orderBy + " " + direction.ToString() + ", id " + direction.ToString() + " LIMIT 1 OFFSET " + Math.Abs(offset) + ";", this.dbConnector.connection);
command.Parameters.AddWithValue("@orderBy", orderBy);
return DBConnector.executeReaderFirstDataRow(command);
}
public DataRow getFirstImage(string orderBy, SortOrder direction)
{
return getFirstImage(orderBy, direction, 0);
}
public DataRow getSequentialImage(long seedId, int offset, bool readOnly)
{
DataRow currentImage = null;
string sortByColumn = this.screensaver.config.getPersistantString("sortBy");
SortOrder sortDirection = new SortOrder(this.screensaver.config.getPersistantString("sortDirection"));
if (seedId == -1) {
try {
long imageId;
imageId = Convert.ToInt32(this.config.getPersistant("sequentialStartImageId"));
currentImage = this.fileDatabase.getImageById(imageId, (this.screensaver.monitors.Length - 1) * -1, sortByColumn, sortDirection);
} catch (Exception e) {
Debug.WriteLine("getSequentialImage " + e.Message);
currentImage = this.fileDatabase.getFirstImage(sortByColumn, sortDirection);
}
} else {
currentImage = fileDatabase.getImageById(seedId, offset, sortByColumn, sortDirection);
}
if (!readOnly && currentImage != null) {
this.currentSequentialSeedId = Convert.ToInt32(currentImage["id"]);
}
return currentImage;
}
public DataRow getImageById(long id, long offset)
{
if (offset == 0) {
return fileDatabase.getImageById(id, offset);
} else {
string sortByColumn = this.screensaver.config.getPersistantString("sortBy");
SortOrder sortDirection = new SortOrder(this.screensaver.config.getPersistantString("sortDirection"));
return fileDatabase.getImageById(id, offset, sortByColumn, sortDirection);
}
}
public void debugMonitorInfo(int m, SortOrder d, int o, DataRow dr, string s)
{
if (dr == null) this.screensaver.monitors[m].showInfoOnMonitor("getSequentialImage(monitor " + m + ", direction " + d.ToString() + ", offset "+o+") ["+s+"]: null");
else this.screensaver.monitors[m].showInfoOnMonitor("getSequentialImage(monitor " + m + ", direction " + d.ToString() + ", offset " + o + ") [" + s + "]: " + dr["id"]);
}
