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