public void FirstAfter(DateTime timestamp)
{
using (NpgsqlConnection conn = Connection ()) {
conn.Open ();
Console.WriteLine ("time i'm shooting for: " + timestamp.ToString ());
string timestampStr = timestamp.ToString (dbTimeFormat);
bool forward = true;
bool back = false;
Screenshot screenshot = GetNextPrevNote (conn, timestampStr, forward, timeCheckTypeFilters);
if (screenshot == null) {
Console.WriteLine ("time i'm shooting for: -> note is null");
screenshot = GetNextPrevNote (conn, timestampStr, back, timeCheckTypeFilters);
} else {
Console.WriteLine ("time i'm shooting for: -> note not null");
}
if (screenshot != null) {
currentScreenshot = screenshot;
setNoteDataFileIfAvailible (conn, currentScreenshot);
sendTimeToTimeblockAdder (currentScreenshot);
} else {
Console.WriteLine ("time i'm shooting for: -> note null again");
}
conn.Close ();
}
}
private void setNoteDataFileIfAvailible(NpgsqlConnection conn, Screenshot note)
{
ScreenshotData screenshotData = GetNoteDatafile (conn, currentScreenshot.NoteId);
if (screenshotData != null) {
if (screenshotData.DataType == "jpeg") {
nvv.setImage (screenshotData.Filename);
} else {
nvv.setImage ("");
}
} else {
nvv.setImage ("");
}
}
private void sendTimeToTimeblockAdder(Screenshot note)
{
DateTime dt;
bool okay = DateTime.TryParse (note.DateTime, out dt);
tpm.recvNoteTime (dt);
}
private void JumpToNote(string timestamp, bool forward, LinkedList<string> tcFilters)
{
using (NpgsqlConnection conn = Connection ()) {
conn.Open ();
Screenshot newScreenshot = GetNextPrevNote (conn, timestamp, forward, tcFilters);
if (newScreenshot != null) {
currentScreenshot = newScreenshot;
setNoteDataFileIfAvailible (conn, currentScreenshot);
sendTimeToTimeblockAdder (currentScreenshot);
} else {
// TODO message "no more notes"
}
conn.Close ();
}
}
private Screenshot GetNextPrevNote(NpgsqlConnection conn, string beforeOrAfterTime, bool forward, LinkedList<string> tcFilters)
{
string timeCheckFiltersSqlExpr = this.timeCheckFiltersSqlExpr (tcFilters);
Screenshot note = null;
string forwardOrBack;
string maxOrMin;
if (forward) {
forwardOrBack = " > ";
maxOrMin = "min";
} else {
forwardOrBack = " < ";
maxOrMin = "max";
}
String query = "with" + " next_tc_timestamp as" + " (select " + maxOrMin + "(time_check_timestamp) as time_check_timestamp " + " from time_checks " + " where time_check_timestamp " + forwardOrBack + " CAST(:dt as timestamp without time zone) " + " and time_check_id not in (select time_check_id from time_block_time_checks) and " + timeCheckFiltersSqlExpr + " )," + " next_tc_id as" + " (select min(time_check_id) as time_check_id from time_checks natural join next_tc_timestamp)" + "select time_check_id,CAST(time_check_timestamp as text),time_check_text from time_checks natural join next_tc_id";
using (NpgsqlCommand command = new NpgsqlCommand (query, conn)) {
command.Parameters.Add (new NpgsqlParameter ("dt", NpgsqlDbType.Text));
command.Prepare ();
// Now, add a value to it and later execute the command as usual.
command.Parameters[0].Value = beforeOrAfterTime;
using (NpgsqlDataReader dr = command.ExecuteReader ()) {
if (dr.Read ()) {
note = new Screenshot ();
note.NoteId = (Int64)dr[0];
note.DateTime = (string)dr[1];
}
}
}
return note;
}
private Screenshot GetFirstBeforeOrAfter(NpgsqlConnection conn, DateTime timestamp, LinkedList<string> tcFilters, bool forward)
{
string forwardOrBackOp;
string agg;
if (forward) {
agg = "min";
forwardOrBackOp = " > ";
} else {
agg = "max";
Console.WriteLine ("time i'm shooting for: -> note not null");
forwardOrBackOp = " < ";
}
Screenshot note = null;
string timeCheckFiltersSqlExpr = this.timeCheckFiltersSqlExpr (tcFilters);
String query = "" + "with" + " next_timestamp(time_check_timestamp) as" + " (select min(time_check_timestamp) as time_check_timestamp " + " from " + " time_checks " + " where " + " time_check_timestamp " + forwardOrBackOp + " CAST(:time as timestamp without time zone) and " + " time_check_id not in (select time_check_id from time_block_time_checks) and " + timeCheckFiltersSqlExpr + " )," + " next_tc_id(time_check_id) as" + " (select " + agg + " (time_check_id) as time_check_id from time_checks natural join next_timestamp) " + "select time_check_id,CAST(time_check_timestamp as text),time_check_text " + "from time_checks natural join next_tc_id;";
using (NpgsqlCommand command = new NpgsqlCommand (query, conn)) {
command.Parameters.Add (new NpgsqlParameter ("time", NpgsqlDbType.Text));
command.Prepare ();
// Now, add a value to it and later execute the command as usual.
command.Parameters[0].Value = timestamp;
using (NpgsqlDataReader dr = command.ExecuteReader ()) {
if (dr.Read ()) {
note = new Screenshot ();
note.NoteId = (Int64)dr[0];
note.DateTime = (string)dr[1];
}
}
}
return note;
}