hivedav

commit 704f5a88754fb4a1bf3c1a2e8e9c5cbfed1f7df3
parent 0fd55cea6e720d4de56f46d61a6b8dfb1230ff82
Author: Andreas Gruhler <andreas.gruhler@adfinis.com>
Date:   Sun, 25 Feb 2024 22:11:15 +0100

feat: simplify availability query

Diffstat:
M
README.md
 | 
38
++++++++++++++++++++++++++------------
M
server.go
 | 
34
++++++++++++++--------------------

2 files changed, 40 insertions(+), 32 deletions(-)
diff --git a/README.md b/README.md
@@ -148,44 +148,58 @@ stored in a local database for further processing.
 
 ## Availability Query
 
+- s: start time
+- e: end time
+- ?: sql query parameter (start/end)
+- |: time slot (1h)
+
+The (un)availability query returns true if an existing appointment is found
+(unavailable, no free time slot). It returns false, when there exists no
+existing calendar entry during the time slot (available).
+
 ```
 //  e ?   ?
 //  - |   |   false
-
+//
 //  s e   ?
 //  - +   |   false
-
+//
 //  s ? e ?
 //  - | - |   true (2)
-
+//
 //  s ?   e
 //  - |   +   true (2)
-
+//
 //  s ?   ? e
 //  - |   | - true (3)
-
+//
 //    s e ?
 //    + - |   true (1)
-
+//
 //    s   e
-//    +   +   true (2)
-
+//    +   +   true (3)
+//
 //    s   ? e
 //    +   | - true (1)
-
+//
 //    ? s e
 //    | - +   true (1)
-
+//
 //    ? s ? e
 //    | - | - true (1)
-
+//
 //    ?   s e
 //    |   + - false
 ```
 
+Note:
+The calendar does not show events marked with the
+(X-MICROSOFT-CDO-INTENDEDSTATUS") "FREE" status. This can be helpful to skip
+certain "reminders" in your calendar, which should not be considered as
+"blocked time" (e.g., full day events).
+
 TODO:
 - Time slots are 1h each (TODO: make it configurable)
-- Sqlite date comparison always inclusive?
 - Picture for the query
 
 ## Development & Contributions
diff --git a/server.go b/server.go
@@ -81,11 +81,9 @@ var create_availability_1 = `CREATE TABLE IF NOT EXISTS availability_1 (
 			     end DATETIME NOT NULL,
 			     recurring BOOL NOT NULL);`
 
-// BETWEEN is inclusive, we tweak start/time by 1 min to make the edge cases
-// 08:01, 09:59, etc. behave correctly
 var available = `SELECT id FROM availability WHERE
-		 (start BETWEEN ? AND ?) OR
-		 (end BETWEEN ? AND ?) OR
+		 (start >= ? AND start < ?) OR
+		 (end > ? AND end <= ?) OR
 		 (start <= ? AND end >= ?);`
 
 func (s *Server) NewServer(c *config.Config) (*Server, error) {
@@ -213,7 +211,7 @@ func (s *Server) Week(w http.ResponseWriter, req *http.Request, ps httprouter.Pa
 	log.Printf("Serving week '%v' of year '%v'\n", week, year)
 	log.Printf("Monday is '%v'\n", monday)
 
-	sqlDateFmt := "2006-01-02 15:04"
+	sqlDateFmt := "2006-01-02 15:04:00"
 
 	termRows := make([][]string, 9)
 	for i := range termRows {
@@ -266,11 +264,10 @@ func (s *Server) Week(w http.ResponseWriter, req *http.Request, ps httprouter.Pa
 		// Working days - Monday through Friday
 		//for d := 0; d < 5; d++ {
 		for _, d := range []int{1, 2, 3, 4, 5} {
-			// add/remove 1 minute to make the sql time query in available() below behave correctly
 			// convert to read/compare in UTC from db
-			startTime := timeIt.Add(time.Hour * time.Duration((d-1)*24+h)).Add(1 * time.Minute)
+			startTime := timeIt.Add(time.Hour * time.Duration((d-1)*24+h))
 			start := startTime.UTC().Format(sqlDateFmt)
-			endTime := timeIt.Add(time.Hour * time.Duration((d-1)*24+h+1)).Add(-1 * time.Minute)
+			endTime := timeIt.Add(time.Hour * time.Duration((d-1)*24+h+1))
 			end := endTime.UTC().Format(sqlDateFmt)
 			avi, err := s.available(start, end)
 			if err != nil {
@@ -438,7 +435,7 @@ func (s *Server) UpdateAvailability(calData []caldav.CalData) error {
 			}
 
 			// set new availability in temporary table availability_1
-			if _, err = s.db.Exec("INSERT INTO availability_1 VALUES(NULL, ?, ?, false);", startTime, endTime); err != nil {
+			if _, err = s.db.Exec("INSERT INTO availability_1 VALUES(NULL, DATETIME(?), DATETIME(?), false);", startTime, endTime); err != nil {
 				return err
 			}
 
@@ -466,7 +463,7 @@ func (s *Server) UpdateAvailability(calData []caldav.CalData) error {
 				// don't include the first event, handled outside the loop
 				rDates := set.Between(startTime, rrLimit, false)
 				for _, rrd := range rDates {
-					if _, err = s.db.Exec("INSERT INTO availability_1 VALUES(NULL, ?, ?, true);", rrd, rrd.Add(duration)); err != nil {
+					if _, err = s.db.Exec("INSERT INTO availability_1 VALUES(NULL, DATETIME(?), DATETIME(?), true);", rrd, rrd.Add(duration)); err != nil {
 						return err
 					}
 				}
@@ -551,7 +548,7 @@ func (s *Server) ListCubiclesInWeek(w http.ResponseWriter, req *http.Request, ps
 	log.Printf("Serving week '%v' of year '%v'\n", week, year)
 	log.Printf("Monday is '%v'\n", monday)
 
-	sqlDateFmt := "2006-01-02 15:04"
+	sqlDateFmt := "2006-01-02 15:04:00"
 
 	var rows [][]string
 
@@ -579,12 +576,11 @@ func (s *Server) ListCubiclesInWeek(w http.ResponseWriter, req *http.Request, ps
 	for d := 0; d < 5; d++ {
 		// 9 1hour time slots
 		for h := 8; h < 17; h++ {
-			// add/remove 1 minute to make the sql time query in available() below behave correctly
 			// convert to read/compare in UTC from db
 			startTime := timeIt.Add(time.Hour * time.Duration((d)*24+h))
 			endTime := timeIt.Add(time.Hour * time.Duration((d)*24+h+1))
-			start := startTime.Add(1 * time.Minute).UTC().Format(sqlDateFmt)
-			end := endTime.Add(-1 * time.Minute).UTC().Format(sqlDateFmt)
+			start := startTime.UTC().Format(sqlDateFmt)
+			end := endTime.UTC().Format(sqlDateFmt)
 			avi, err := s.available(start, end)
 			if err != nil {
 				log.Printf("Error getting availability on day '%d' hour '%d'\n", d, h)
@@ -728,12 +724,10 @@ func (s *Server) BookCubicle(w http.ResponseWriter, req *http.Request, ps httpro
 		return
 	}
 
-	// TODO: move this 1 minute logic + sqlDateFmt into available() function
-	// add/remove 1 minute to make the sql time query in available() below behave correctly
 	// convert to read/compare in UTC from db
-	sqlDateFmt := "2006-01-02 15:04"
-	start := dtstart.Add(1 * time.Minute).UTC().Format(sqlDateFmt)
-	end := dtstart.Add(time.Hour).Add(-1 * time.Minute).UTC().Format(sqlDateFmt)
+	sqlDateFmt := "2006-01-02 15:04:00"
+	start := dtstart.UTC().Format(sqlDateFmt)
+	end := dtstart.Add(time.Hour).UTC().Format(sqlDateFmt)
 	avi, err := s.available(start, end)
 	if err != nil {
 		log.Printf("Error getting availability on '%s'\n", start)
@@ -757,7 +751,7 @@ func (s *Server) BookCubicle(w http.ResponseWriter, req *http.Request, ps httpro
 	// update availability
 	startTimeUtc := dtstart.UTC()
 	endTimeUtc := dtstart.Add(time.Hour).UTC()
-	if _, err = s.db.Exec("INSERT INTO availability VALUES(NULL, ?, ?, false);", startTimeUtc, endTimeUtc); err != nil {
+	if _, err = s.db.Exec("INSERT INTO availability VALUES(NULL, DATETIME(?), DATETIME(?), false);", startTimeUtc, endTimeUtc); err != nil {
 		log.Printf("Error updating availability database with new booking: %v\n", err)
 		http.Error(w, err.Error(), http.StatusInternalServerError)
 		return