diff options
Diffstat (limited to 'users/grfn/bbbg/src/bbbg/db')
-rw-r--r-- | users/grfn/bbbg/src/bbbg/db/attendee.clj | 85 | ||||
-rw-r--r-- | users/grfn/bbbg/src/bbbg/db/attendee_check.clj | 55 | ||||
-rw-r--r-- | users/grfn/bbbg/src/bbbg/db/event.clj | 94 | ||||
-rw-r--r-- | users/grfn/bbbg/src/bbbg/db/event_attendee.clj | 17 | ||||
-rw-r--r-- | users/grfn/bbbg/src/bbbg/db/user.clj | 19 |
5 files changed, 270 insertions, 0 deletions
diff --git a/users/grfn/bbbg/src/bbbg/db/attendee.clj b/users/grfn/bbbg/src/bbbg/db/attendee.clj new file mode 100644 index 000000000000..da5ee29321fb --- /dev/null +++ b/users/grfn/bbbg/src/bbbg/db/attendee.clj @@ -0,0 +1,85 @@ +(ns bbbg.db.attendee + (:require + [bbbg.attendee :as attendee] + [bbbg.db :as db] + [bbbg.util.sql :refer [count-where]] + honeysql-postgres.helpers + [honeysql.helpers + :refer + [merge-group-by merge-join merge-left-join merge-select merge-where]] + [bbbg.util.core :as u])) + +(defn search + ([q] (search {:select [:attendee.*] :from [:attendee]} q)) + ([db-or-query q] + (if (db/database? db-or-query) + (db/list db-or-query (search q)) + (cond-> db-or-query + q (merge-where + [:or + [:ilike :meetup_name (str "%" q "%")] + [:ilike :discord_name (str "%" q "%")]])))) + ([db query q] + (db/list db (search query q)))) + +(defn for-event + ([event-id] + (for-event {:select [:attendee.*] + :from [:attendee]} + event-id)) + ([db-or-query event-id] + (if (db/database? db-or-query) + (db/list db-or-query (for-event event-id)) + (-> db-or-query + (merge-select :event-attendee.*) + (merge-join :event_attendee [:= :attendee.id :event_attendee.attendee_id]) + (merge-where [:= :event_attendee.event_id event-id])))) + ([db query event-id] + (db/list db (for-event query event-id)))) + +(defn with-stats + ([] (with-stats {:select [:attendee.*] + :from [:attendee]})) + ([query] + (-> query + (merge-left-join :event_attendee [:= :attendee.id :event_attendee.attendee_id]) + (merge-group-by :attendee.id) + (merge-select + [(count-where :event_attendee.rsvpd_attending) :events-rsvpd] + [(count-where :event_attendee.attended) :events-attended] + [(count-where [:and + :event_attendee.rsvpd_attending + [:not :event_attendee.attended]]) + :no-shows])))) + +(defn upsert-all! + [db attendees] + (when (seq attendees) + (db/list + db + {:insert-into :attendee + :values (map #(->> % + (db/process-key-map :attendee) + (u/map-keys keyword)) + attendees) + :upsert {:on-conflict [:meetup-user-id] + :do-update-set [:meetup-name]} + :returning [:id :meetup-user-id]}))) + +(comment + (def db (:db bbbg.core/system)) + (db/database? db) + (search db "gri") + (db/insert! db :attendee {::attendee/meetup-name "Griffin Smith" + ::attendee/discord-name "grfn" + }) + + (search db (with-stats) "gri") + + (search (with-stats) "gri") + + (db/list db (with-stats)) + + (db/insert! db :attendee {::attendee/meetup-name "Rando Guy" + ::attendee/discord-name "rando"}) + ) diff --git a/users/grfn/bbbg/src/bbbg/db/attendee_check.clj b/users/grfn/bbbg/src/bbbg/db/attendee_check.clj new file mode 100644 index 000000000000..492f786bd660 --- /dev/null +++ b/users/grfn/bbbg/src/bbbg/db/attendee_check.clj @@ -0,0 +1,55 @@ +(ns bbbg.db.attendee-check + (:require + [bbbg.attendee :as attendee] + [bbbg.attendee-check :as attendee-check] + [bbbg.db :as db] + [bbbg.user :as user] + [bbbg.util.core :as u])) + +(defn create! [db params] + (db/insert! db :attendee-check + (select-keys params [::attendee/id + ::user/id + ::attendee-check/last-dose-at]))) + +(defn attendees-with-last-checks + [db attendees] + (when (seq attendees) + (let [ids (map ::attendee/id attendees) + checks + (db/list db {:select [:attendee-check.*] + :from [:attendee-check] + :join [[{:select [:%max.attendee-check.checked-at + :attendee-check.attendee-id] + :from [:attendee-check] + :group-by [:attendee-check.attendee-id] + :where [:in :attendee-check.attendee-id ids]} + :last-check] + [:= + :attendee-check.attendee-id + :last-check.attendee-id]]}) + users (if (seq checks) + (u/key-by + ::user/id + (db/list db {:select [:public.user.*] + :from [:public.user] + :where [:in :id (map ::user/id checks)]})) + {}) + checks (map #(assoc % :user (users (::user/id %))) checks) + attendee-id->check (u/key-by ::attendee/id checks)] + (map #(assoc % :last-check (attendee-id->check (::attendee/id %))) + attendees)))) + +(comment + (def db (:db bbbg.core/system)) + + (attendees-with-last-checks + db + (db/list db :attendee) + ) + + (db/insert! db :attendee-check + {::attendee/id #uuid "58bcd372-ff6e-49df-b280-23d24c5ba0f0" + ::user/id #uuid "303fb606-5ef0-4682-ad7d-6429c670cd78" + ::attendee-check/last-dose-at "2021-12-19"}) + ) diff --git a/users/grfn/bbbg/src/bbbg/db/event.clj b/users/grfn/bbbg/src/bbbg/db/event.clj new file mode 100644 index 000000000000..1b5a4e11ecd7 --- /dev/null +++ b/users/grfn/bbbg/src/bbbg/db/event.clj @@ -0,0 +1,94 @@ +(ns bbbg.db.event + (:require + [bbbg.attendee :as attendee] + [bbbg.db :as db] + [bbbg.event :as event] + [bbbg.util.sql :refer [count-where]] + [honeysql.helpers + :refer [merge-group-by merge-left-join merge-select merge-where]] + [java-time :refer [local-date local-date-time local-time]])) + +(defn create! [db event] + (db/insert! db :event (select-keys event [::event/date]))) + +(defn attended! + [db params] + (db/execute! + db + {:insert-into :event-attendee + :values [{:event_id (::event/id params) + :attendee_id (::attendee/id params) + :attended true}] + :upsert {:on-conflict [:event-id :attendee-id] + :do-update-set! {:attended true}}})) + +(defn on-day + ([day] {:select [:event.*] + :from [:event] + :where [:= :date (str day)]}) + ([db day] + (db/list db (on-day day)))) + + +(def end-of-day-hour + ;; 7am utc = 3am nyc + 7) + +(defn current-day + ([] (current-day (local-date-time))) + ([dt] + (if (<= 0 + (.getHour (local-time dt)) + end-of-day-hour) + (java-time/minus + (local-date dt) + (java-time/days 1)) + (local-date dt)))) + +(comment + (current-day + (local-date-time + 2022 5 1 + 1 13 0)) + ) + +(defn today + ([] (on-day (current-day))) + ([db] (db/list db (today)))) + +(defn upcoming + ([] (upcoming {:select [:event.*] :from [:event]})) + ([query] + (merge-where query [:>= :date (local-date)]))) + +(defn past + ([] (past {:select [:event.*] :from [:event]})) + ([query] + (merge-where query [:< :date (local-date)]))) + +(defn with-attendee-counts + [query] + (-> query + (merge-left-join :event_attendee [:= :event.id :event_attendee.event-id]) + (merge-select :%count.event_attendee.attendee_id) + (merge-group-by :event.id :event_attendee.event-id))) + +(defn with-stats + [query] + (-> query + (merge-left-join :event_attendee [:= :event.id :event_attendee.event-id]) + (merge-select + [(count-where :event-attendee.rsvpd_attending) :num-rsvps] + [(count-where :event-attendee.attended) :num-attendees]) + (merge-group-by :event.id))) + +(comment + (def db (:db bbbg.core/system)) + (db/list db (-> (today) (with-attendee-counts))) + + (honeysql.format/format + (honeysql-postgres.helpers/upsert {:insert-into :foo + :values {:bar 1}} + (-> (honeysql-postgres.helpers/on-conflict :did) + (honeysql-postgres.helpers/do-update-set! [:did true])))) + ) diff --git a/users/grfn/bbbg/src/bbbg/db/event_attendee.clj b/users/grfn/bbbg/src/bbbg/db/event_attendee.clj new file mode 100644 index 000000000000..31411e5d4504 --- /dev/null +++ b/users/grfn/bbbg/src/bbbg/db/event_attendee.clj @@ -0,0 +1,17 @@ +(ns bbbg.db.event-attendee + (:require honeysql-postgres.format + [bbbg.db :as db] + [bbbg.util.core :as u])) + +(defn upsert-all! + [db attendees] + (when (seq attendees) + (db/execute! + db + {:insert-into :event-attendee + :values (map #(->> % + (db/process-key-map :event-attendee) + (u/map-keys keyword)) + attendees) + :upsert {:on-conflict [:event-id :attendee-id] + :do-update-set [:rsvpd-attending]}}))) diff --git a/users/grfn/bbbg/src/bbbg/db/user.clj b/users/grfn/bbbg/src/bbbg/db/user.clj new file mode 100644 index 000000000000..700105ef6350 --- /dev/null +++ b/users/grfn/bbbg/src/bbbg/db/user.clj @@ -0,0 +1,19 @@ +(ns bbbg.db.user + (:require [bbbg.db :as db] + [bbbg.user :as user])) + +(defn create! [db attrs] + (db/insert! db + :public.user + (select-keys attrs [::user/id + ::user/username + ::user/discord-user-id]))) + +(defn find-or-create! [db attrs] + (or + (db/fetch db {:select [:*] + :from [:public.user] + :where [:= + :discord-user-id + (::user/discord-user-id attrs)]}) + (create! db attrs))) |