From 33b85b7f12798f5762d940ed5c30f775cdd7b751 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 28 Jan 2018 12:13:09 +0100
Subject: WIP

---
 server/src/Persistence/Income.hs | 88 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 88 insertions(+)
 create mode 100644 server/src/Persistence/Income.hs

(limited to 'server/src/Persistence/Income.hs')

diff --git a/server/src/Persistence/Income.hs b/server/src/Persistence/Income.hs
new file mode 100644
index 0000000..a863f85
--- /dev/null
+++ b/server/src/Persistence/Income.hs
@@ -0,0 +1,88 @@
+module Persistence.Income
+  ( list
+  , create
+  , editOwn
+  , deleteOwn
+  ) where
+
+import           Data.Maybe             (listToMaybe)
+import           Data.Time.Calendar     (Day)
+import           Data.Time.Clock        (getCurrentTime)
+import           Database.SQLite.Simple (FromRow (fromRow), Only (Only))
+import qualified Database.SQLite.Simple as SQLite
+import           Prelude                hiding (id)
+
+import           Common.Model           (Income (..), IncomeId, User (..),
+                                         UserId)
+
+import           Model.Query            (Query (Query))
+
+newtype Row = Row Income
+
+instance FromRow Row where
+  fromRow = Row <$> (Income <$>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field)
+
+list :: Query [Income]
+list =
+  Query (\conn ->
+    map (\(Row i) -> i) <$>
+      SQLite.query_ conn "SELECT * FROM income WHERE deleted_at IS NULL"
+  )
+
+create :: UserId -> Day -> Int -> Query IncomeId
+create incomeUserId incomeDate incomeAmount =
+  Query (\conn -> do
+    now <- getCurrentTime
+    SQLite.execute
+      conn
+      "INSERT INTO income (user_id, date, amount, created_at) VALUES (?, ?, ?, ?)"
+      (incomeUserId, incomeDate, incomeAmount, now)
+    SQLite.lastInsertRowId conn
+  )
+
+editOwn :: UserId -> IncomeId -> Day -> Int -> Query Bool
+editOwn incomeUserId incomeId incomeDate incomeAmount =
+  Query (\conn -> do
+    mbIncome <- fmap (\(Row i) -> i) . listToMaybe <$>
+      SQLite.query conn "SELECT * FROM income WHERE id = ?" (Only incomeId)
+    case mbIncome of
+      Just income ->
+        if _income_userId income == incomeUserId
+          then do
+            now <- getCurrentTime
+            SQLite.execute
+              conn
+              "UPDATE income SET edited_at = ?, date = ?, amount = ? WHERE id = ?"
+              (now, incomeDate, incomeAmount, incomeId)
+            return True
+          else
+            return False
+      Nothing ->
+        return False
+  )
+
+deleteOwn :: User -> IncomeId -> Query Bool
+deleteOwn user incomeId =
+  Query (\conn -> do
+    mbIncome <-
+      fmap (\(Row i) -> i) . listToMaybe <$>
+        SQLite.query conn "SELECT * FROM income WHERE id = ?" (Only incomeId)
+    case mbIncome of
+      Just income ->
+        if _income_userId income == _user_id user
+          then do
+            now <- getCurrentTime
+            SQLite.execute conn "UPDATE income SET deleted_at = ? WHERE id = ?" (now, incomeId)
+            return True
+          else
+            return False
+      Nothing ->
+        return False
+  )
-- 
cgit v1.2.3


From fb8f0fe577e28dae69903413b761da50586e0099 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sat, 10 Aug 2019 14:53:41 +0200
Subject: Remove payment category if unused after a payment is deleted

---
 server/src/Persistence/Income.hs | 34 ++++++++++++----------------------
 1 file changed, 12 insertions(+), 22 deletions(-)

(limited to 'server/src/Persistence/Income.hs')

diff --git a/server/src/Persistence/Income.hs b/server/src/Persistence/Income.hs
index a863f85..cee9892 100644
--- a/server/src/Persistence/Income.hs
+++ b/server/src/Persistence/Income.hs
@@ -1,8 +1,8 @@
 module Persistence.Income
   ( list
   , create
-  , editOwn
-  , deleteOwn
+  , edit
+  , delete
   ) where
 
 import           Data.Maybe             (listToMaybe)
@@ -12,7 +12,7 @@ import           Database.SQLite.Simple (FromRow (fromRow), Only (Only))
 import qualified Database.SQLite.Simple as SQLite
 import           Prelude                hiding (id)
 
-import           Common.Model           (Income (..), IncomeId, User (..),
+import           Common.Model           (Income (..), IncomeId, PaymentId,
                                          UserId)
 
 import           Model.Query            (Query (Query))
@@ -47,8 +47,8 @@ create incomeUserId incomeDate incomeAmount =
     SQLite.lastInsertRowId conn
   )
 
-editOwn :: UserId -> IncomeId -> Day -> Int -> Query Bool
-editOwn incomeUserId incomeId incomeDate incomeAmount =
+edit :: UserId -> IncomeId -> Day -> Int -> Query Bool
+edit incomeUserId incomeId incomeDate incomeAmount =
   Query (\conn -> do
     mbIncome <- fmap (\(Row i) -> i) . listToMaybe <$>
       SQLite.query conn "SELECT * FROM income WHERE id = ?" (Only incomeId)
@@ -68,21 +68,11 @@ editOwn incomeUserId incomeId incomeDate incomeAmount =
         return False
   )
 
-deleteOwn :: User -> IncomeId -> Query Bool
-deleteOwn user incomeId =
-  Query (\conn -> do
-    mbIncome <-
-      fmap (\(Row i) -> i) . listToMaybe <$>
-        SQLite.query conn "SELECT * FROM income WHERE id = ?" (Only incomeId)
-    case mbIncome of
-      Just income ->
-        if _income_userId income == _user_id user
-          then do
-            now <- getCurrentTime
-            SQLite.execute conn "UPDATE income SET deleted_at = ? WHERE id = ?" (now, incomeId)
-            return True
-          else
-            return False
-      Nothing ->
-        return False
+delete :: UserId -> PaymentId -> Query ()
+delete userId paymentId =
+  Query (\conn ->
+    SQLite.execute
+      conn
+      "UPDATE income SET deleted_at = datetime('now') WHERE id = ? AND user_id = ?"
+      (paymentId, userId)
   )
-- 
cgit v1.2.3


From 7aadcc97f9df0e2daccbe8a8726d8bc6c63d67f4 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 20 Oct 2019 12:02:21 +0200
Subject: Add income

---
 server/src/Persistence/Income.hs | 19 ++++++++++++++-----
 1 file changed, 14 insertions(+), 5 deletions(-)

(limited to 'server/src/Persistence/Income.hs')

diff --git a/server/src/Persistence/Income.hs b/server/src/Persistence/Income.hs
index cee9892..a0c3bbf 100644
--- a/server/src/Persistence/Income.hs
+++ b/server/src/Persistence/Income.hs
@@ -36,15 +36,24 @@ list =
       SQLite.query_ conn "SELECT * FROM income WHERE deleted_at IS NULL"
   )
 
-create :: UserId -> Day -> Int -> Query IncomeId
-create incomeUserId incomeDate incomeAmount =
+create :: UserId -> Day -> Int -> Query Income
+create userId date amount =
   Query (\conn -> do
-    now <- getCurrentTime
+    createdAt <- getCurrentTime
     SQLite.execute
       conn
       "INSERT INTO income (user_id, date, amount, created_at) VALUES (?, ?, ?, ?)"
-      (incomeUserId, incomeDate, incomeAmount, now)
-    SQLite.lastInsertRowId conn
+      (userId, date, amount, createdAt)
+    incomeId <- SQLite.lastInsertRowId conn
+    return $ Income
+      { _income_id        = incomeId
+      , _income_userId    = userId
+      , _income_date      = date
+      , _income_amount    = amount
+      , _income_createdAt = createdAt
+      , _income_editedAt  = Nothing
+      , _income_deletedAt = Nothing
+      }
   )
 
 edit :: UserId -> IncomeId -> Day -> Int -> Query Bool
-- 
cgit v1.2.3


From e4b32ce15f8c92f3b477d3f3d4d301ba08f9b5e3 Mon Sep 17 00:00:00 2001
From: Joris
Date: Wed, 23 Oct 2019 22:35:27 +0200
Subject: Edit an income

---
 server/src/Persistence/Income.hs | 31 ++++++++++++++++++-------------
 1 file changed, 18 insertions(+), 13 deletions(-)

(limited to 'server/src/Persistence/Income.hs')

diff --git a/server/src/Persistence/Income.hs b/server/src/Persistence/Income.hs
index a0c3bbf..2b9bf0c 100644
--- a/server/src/Persistence/Income.hs
+++ b/server/src/Persistence/Income.hs
@@ -56,25 +56,30 @@ create userId date amount =
       }
   )
 
-edit :: UserId -> IncomeId -> Day -> Int -> Query Bool
-edit incomeUserId incomeId incomeDate incomeAmount =
+edit :: UserId -> IncomeId -> Day -> Int -> Query (Maybe Income)
+edit userId incomeId incomeDate incomeAmount =
   Query (\conn -> do
     mbIncome <- fmap (\(Row i) -> i) . listToMaybe <$>
       SQLite.query conn "SELECT * FROM income WHERE id = ?" (Only incomeId)
     case mbIncome of
       Just income ->
-        if _income_userId income == incomeUserId
-          then do
-            now <- getCurrentTime
-            SQLite.execute
-              conn
-              "UPDATE income SET edited_at = ?, date = ?, amount = ? WHERE id = ?"
-              (now, incomeDate, incomeAmount, incomeId)
-            return True
-          else
-            return False
+        do
+          currentTime <- getCurrentTime
+          SQLite.execute
+            conn
+            "UPDATE income SET edited_at = ?, date = ?, amount = ? WHERE id = ? AND user_id = ?"
+            (currentTime, incomeDate, incomeAmount, incomeId, userId)
+          return . Just $ Income
+            { _income_id        = incomeId
+            , _income_userId    = userId
+            , _income_date      = incomeDate
+            , _income_amount    = incomeAmount
+            , _income_createdAt = _income_createdAt income
+            , _income_editedAt  = Just currentTime
+            , _income_deletedAt = Nothing
+            }
       Nothing ->
-        return False
+        return Nothing
   )
 
 delete :: UserId -> PaymentId -> Query ()
-- 
cgit v1.2.3


From b97ad942495352c3fc1e0c820cfba82a9693ac7a Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 27 Oct 2019 20:26:29 +0100
Subject: WIP Set up server side paging for incomes

---
 server/src/Persistence/Income.hs | 26 +++++++++++++++++++++++++-
 1 file changed, 25 insertions(+), 1 deletion(-)

(limited to 'server/src/Persistence/Income.hs')

diff --git a/server/src/Persistence/Income.hs b/server/src/Persistence/Income.hs
index 2b9bf0c..de55a18 100644
--- a/server/src/Persistence/Income.hs
+++ b/server/src/Persistence/Income.hs
@@ -1,5 +1,7 @@
 module Persistence.Income
-  ( list
+  ( count
+  , list
+  , listv2
   , create
   , edit
   , delete
@@ -29,6 +31,18 @@ instance FromRow Row where
     SQLite.field <*>
     SQLite.field)
 
+data Count = Count Int
+
+instance FromRow Count where
+  fromRow = Count <$> SQLite.field
+
+count :: Query Int
+count =
+  Query (\conn ->
+    (\[Count n] -> n) <$>
+      SQLite.query_ conn "SELECT COUNT(*) FROM income WHERE deleted_at IS NULL"
+  )
+
 list :: Query [Income]
 list =
   Query (\conn ->
@@ -36,6 +50,16 @@ list =
       SQLite.query_ conn "SELECT * FROM income WHERE deleted_at IS NULL"
   )
 
+listv2 :: Int -> Int -> Query [Income]
+listv2 page perPage =
+  Query (\conn ->
+    map (\(Row i) -> i) <$>
+      SQLite.query
+        conn
+        "SELECT * FROM income WHERE deleted_at IS NULL ORDER BY date DESC LIMIT ? OFFSET ?"
+        (perPage, (page - 1) * perPage)
+  )
+
 create :: UserId -> Day -> Int -> Query Income
 create userId date amount =
   Query (\conn -> do
-- 
cgit v1.2.3


From 9dbb4e6f7c2f0edc1126626e2ff498144c6b9947 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 3 Nov 2019 11:28:42 +0100
Subject: Show income header

---
 server/src/Persistence/Income.hs | 23 +++++++++++++----------
 1 file changed, 13 insertions(+), 10 deletions(-)

(limited to 'server/src/Persistence/Income.hs')

diff --git a/server/src/Persistence/Income.hs b/server/src/Persistence/Income.hs
index de55a18..4ae3228 100644
--- a/server/src/Persistence/Income.hs
+++ b/server/src/Persistence/Income.hs
@@ -1,7 +1,7 @@
 module Persistence.Income
   ( count
   , list
-  , listv2
+  , listAll
   , create
   , edit
   , delete
@@ -43,15 +43,8 @@ count =
       SQLite.query_ conn "SELECT COUNT(*) FROM income WHERE deleted_at IS NULL"
   )
 
-list :: Query [Income]
-list =
-  Query (\conn ->
-    map (\(Row i) -> i) <$>
-      SQLite.query_ conn "SELECT * FROM income WHERE deleted_at IS NULL"
-  )
-
-listv2 :: Int -> Int -> Query [Income]
-listv2 page perPage =
+list :: Int -> Int -> Query [Income]
+list page perPage =
   Query (\conn ->
     map (\(Row i) -> i) <$>
       SQLite.query
@@ -60,6 +53,16 @@ listv2 page perPage =
         (perPage, (page - 1) * perPage)
   )
 
+listAll :: Query [Income]
+listAll =
+  Query (\conn ->
+    map (\(Row i) -> i) <$>
+      SQLite.query_ conn "SELECT * FROM income WHERE deleted_at IS NULL"
+  )
+
+-- firstIncomeByUser
+-- SELECT user_id, MIN(date) FROM income WHERE deleted_at IS NULL GROUP BY user_id;
+
 create :: UserId -> Day -> Int -> Query Income
 create userId date amount =
   Query (\conn -> do
-- 
cgit v1.2.3


From f4f24158a46d8c0975f1b8813bbdbbeebad8c108 Mon Sep 17 00:00:00 2001
From: Joris
Date: Wed, 6 Nov 2019 19:44:15 +0100
Subject: Show the payment table with server side paging

---
 server/src/Persistence/Income.hs | 3 ---
 1 file changed, 3 deletions(-)

(limited to 'server/src/Persistence/Income.hs')

diff --git a/server/src/Persistence/Income.hs b/server/src/Persistence/Income.hs
index 4ae3228..cb2ef10 100644
--- a/server/src/Persistence/Income.hs
+++ b/server/src/Persistence/Income.hs
@@ -60,9 +60,6 @@ listAll =
       SQLite.query_ conn "SELECT * FROM income WHERE deleted_at IS NULL"
   )
 
--- firstIncomeByUser
--- SELECT user_id, MIN(date) FROM income WHERE deleted_at IS NULL GROUP BY user_id;
-
 create :: UserId -> Day -> Int -> Query Income
 create userId date amount =
   Query (\conn -> do
-- 
cgit v1.2.3


From c0ea63f8c1a8c7123b78798cec99726b113fb1f3 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 17 Nov 2019 18:08:28 +0100
Subject: Optimize and refactor payments

---
 server/src/Persistence/Income.hs | 59 +++++++++++++++++++++++++++++++++++-----
 1 file changed, 52 insertions(+), 7 deletions(-)

(limited to 'server/src/Persistence/Income.hs')

diff --git a/server/src/Persistence/Income.hs b/server/src/Persistence/Income.hs
index cb2ef10..ba7ad19 100644
--- a/server/src/Persistence/Income.hs
+++ b/server/src/Persistence/Income.hs
@@ -2,17 +2,22 @@ module Persistence.Income
   ( count
   , list
   , listAll
+  , listModifiedSince
   , create
   , edit
   , delete
+  , definedForAll
   ) where
 
-import           Data.Maybe             (listToMaybe)
+import qualified Data.List              as L
+import qualified Data.Maybe             as Maybe
+import qualified Data.Text              as T
 import           Data.Time.Calendar     (Day)
+import           Data.Time.Clock        (UTCTime)
 import           Data.Time.Clock        (getCurrentTime)
 import           Database.SQLite.Simple (FromRow (fromRow), Only (Only))
 import qualified Database.SQLite.Simple as SQLite
-import           Prelude                hiding (id)
+import           Prelude                hiding (id, until)
 
 import           Common.Model           (Income (..), IncomeId, PaymentId,
                                          UserId)
@@ -31,15 +36,15 @@ instance FromRow Row where
     SQLite.field <*>
     SQLite.field)
 
-data Count = Count Int
+data CountRow = CountRow Int
 
-instance FromRow Count where
-  fromRow = Count <$> SQLite.field
+instance FromRow CountRow where
+  fromRow = CountRow <$> SQLite.field
 
 count :: Query Int
 count =
   Query (\conn ->
-    (\[Count n] -> n) <$>
+    (Maybe.fromMaybe 0 . fmap (\(CountRow n) -> n) . Maybe.listToMaybe) <$>
       SQLite.query_ conn "SELECT COUNT(*) FROM income WHERE deleted_at IS NULL"
   )
 
@@ -60,6 +65,23 @@ listAll =
       SQLite.query_ conn "SELECT * FROM income WHERE deleted_at IS NULL"
   )
 
+listModifiedSince :: UTCTime -> Query [Income]
+listModifiedSince since =
+  Query (\conn ->
+    map (\(Row i) -> i) <$>
+      SQLite.query
+        conn
+        (SQLite.Query . T.intercalate " " $
+          [ "SELECT *"
+          , "FROM income"
+          , "WHERE"
+          ,   "created_at >= ?"
+          ,   "OR edited_at >= ?"
+          ,   "OR deleted_at >= ?"
+          ])
+        (Only since)
+  )
+
 create :: UserId -> Day -> Int -> Query Income
 create userId date amount =
   Query (\conn -> do
@@ -83,7 +105,7 @@ create userId date amount =
 edit :: UserId -> IncomeId -> Day -> Int -> Query (Maybe Income)
 edit userId incomeId incomeDate incomeAmount =
   Query (\conn -> do
-    mbIncome <- fmap (\(Row i) -> i) . listToMaybe <$>
+    mbIncome <- fmap (\(Row i) -> i) . Maybe.listToMaybe <$>
       SQLite.query conn "SELECT * FROM income WHERE id = ?" (Only incomeId)
     case mbIncome of
       Just income ->
@@ -114,3 +136,26 @@ delete userId paymentId =
       "UPDATE income SET deleted_at = datetime('now') WHERE id = ? AND user_id = ?"
       (paymentId, userId)
   )
+
+data UserDayRow = UserDayRow (UserId, Day)
+
+instance FromRow UserDayRow where
+  fromRow = do
+    user <- SQLite.field
+    day <- SQLite.field
+    return $ UserDayRow (user, day)
+
+definedForAll :: [UserId] -> Query (Maybe Day)
+definedForAll users =
+  Query (\conn ->
+    (fromRows . fmap (\(UserDayRow (user, day)) -> (user, day))) <$>
+      SQLite.query_
+        conn
+        "SELECT user_id, MIN(date) FROM income WHERE deleted_at IS NULL GROUP BY user_id;"
+  )
+  where
+    fromRows rows =
+      if L.sort users == L.sort (map fst rows) then
+        Maybe.listToMaybe . L.sort . map snd $ rows
+      else
+        Nothing
-- 
cgit v1.2.3


From 54628c70cb33de5e4309c35b9f6b57bbe9f7a07b Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 24 Nov 2019 16:19:53 +0100
Subject: Compute cumulative income with a DB query

---
 server/src/Persistence/Income.hs | 58 ++++++++++++++++++++++++++++++++--------
 1 file changed, 47 insertions(+), 11 deletions(-)

(limited to 'server/src/Persistence/Income.hs')

diff --git a/server/src/Persistence/Income.hs b/server/src/Persistence/Income.hs
index ba7ad19..e689505 100644
--- a/server/src/Persistence/Income.hs
+++ b/server/src/Persistence/Income.hs
@@ -1,21 +1,24 @@
 module Persistence.Income
   ( count
   , list
-  , listAll
   , listModifiedSince
   , create
   , edit
   , delete
   , definedForAll
+  , getCumulativeIncome
   ) where
 
 import qualified Data.List              as L
+import           Data.Map               (Map)
+import qualified Data.Map               as M
 import qualified Data.Maybe             as Maybe
 import qualified Data.Text              as T
 import           Data.Time.Calendar     (Day)
 import           Data.Time.Clock        (UTCTime)
 import           Data.Time.Clock        (getCurrentTime)
-import           Database.SQLite.Simple (FromRow (fromRow), Only (Only))
+import           Database.SQLite.Simple (FromRow (fromRow), NamedParam ((:=)),
+                                         Only (Only))
 import qualified Database.SQLite.Simple as SQLite
 import           Prelude                hiding (id, until)
 
@@ -58,13 +61,6 @@ list page perPage =
         (perPage, (page - 1) * perPage)
   )
 
-listAll :: Query [Income]
-listAll =
-  Query (\conn ->
-    map (\(Row i) -> i) <$>
-      SQLite.query_ conn "SELECT * FROM income WHERE deleted_at IS NULL"
-  )
-
 listModifiedSince :: UTCTime -> Query [Income]
 listModifiedSince since =
   Query (\conn ->
@@ -79,7 +75,7 @@ listModifiedSince since =
           ,   "OR edited_at >= ?"
           ,   "OR deleted_at >= ?"
           ])
-        (Only since)
+        (since, since, since)
   )
 
 create :: UserId -> Day -> Int -> Query Income
@@ -156,6 +152,46 @@ definedForAll users =
   where
     fromRows rows =
       if L.sort users == L.sort (map fst rows) then
-        Maybe.listToMaybe . L.sort . map snd $ rows
+        Maybe.listToMaybe . reverse . L.sort . map snd $ rows
       else
         Nothing
+
+getCumulativeIncome :: Day -> Day -> Query (Map UserId Int)
+getCumulativeIncome start end =
+  Query (\conn -> M.fromList <$> SQLite.queryNamed conn (SQLite.Query query) parameters)
+  where
+    query =
+      T.intercalate "\n" $
+        [ "SELECT user_id, CAST(ROUND(SUM(count)) AS INTEGER) FROM ("
+        , "  SELECT"
+        , "    I1.user_id,"
+        , "    ((JULIANDAY(MIN(I2.date)) - JULIANDAY(I1.date)) * I1.amount * 12 / 365) AS count"
+        , "  FROM (" <> (selectBoundedIncomes ">" ":start") <> ") AS I1"
+        , "  INNER JOIN (" <> (selectBoundedIncomes "<" ":end") <> ") AS I2"
+        , "  ON I2.date > I1.date AND I2.user_id == I1.user_id"
+        , "  GROUP BY I1.date, I1.user_id"
+        , ") GROUP BY user_id"
+        ]
+
+    selectBoundedIncomes op param =
+      T.intercalate "\n" $
+        [ "    SELECT user_id, date, amount FROM ("
+        , "      SELECT"
+        , "        i.user_id, " <> param <> " AS date, i.amount"
+        , "      FROM"
+        , "        (SELECT id, MAX(date) AS max_date"
+        , "        FROM income"
+        , "        WHERE date <= " <> param <> " AND deleted_at IS NULL"
+        , "        GROUP BY user_id) AS m"
+        , "      INNER JOIN income AS i"
+        , "      ON i.id = m.id AND i.date = m.max_date"
+        , "    ) UNION"
+        , "    SELECT user_id, date, amount"
+        , "    FROM income"
+        , "    WHERE date " <> op <> " " <> param <> " AND deleted_at IS NULL"
+        ]
+
+    parameters =
+      [ ":start" := start
+      , ":end" := end
+      ]
-- 
cgit v1.2.3


From 316bda10c6bec8b5ccc9e23f1f677c076205f046 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 8 Dec 2019 11:39:37 +0100
Subject: Add category page

---
 server/src/Persistence/Income.hs | 45 ++++++++++++----------------------------
 1 file changed, 13 insertions(+), 32 deletions(-)

(limited to 'server/src/Persistence/Income.hs')

diff --git a/server/src/Persistence/Income.hs b/server/src/Persistence/Income.hs
index e689505..cd98814 100644
--- a/server/src/Persistence/Income.hs
+++ b/server/src/Persistence/Income.hs
@@ -78,7 +78,7 @@ listModifiedSince since =
         (since, since, since)
   )
 
-create :: UserId -> Day -> Int -> Query Income
+create :: UserId -> Day -> Int -> Query ()
 create userId date amount =
   Query (\conn -> do
     createdAt <- getCurrentTime
@@ -86,42 +86,23 @@ create userId date amount =
       conn
       "INSERT INTO income (user_id, date, amount, created_at) VALUES (?, ?, ?, ?)"
       (userId, date, amount, createdAt)
-    incomeId <- SQLite.lastInsertRowId conn
-    return $ Income
-      { _income_id        = incomeId
-      , _income_userId    = userId
-      , _income_date      = date
-      , _income_amount    = amount
-      , _income_createdAt = createdAt
-      , _income_editedAt  = Nothing
-      , _income_deletedAt = Nothing
-      }
   )
 
-edit :: UserId -> IncomeId -> Day -> Int -> Query (Maybe Income)
+edit :: UserId -> IncomeId -> Day -> Int -> Query Bool
 edit userId incomeId incomeDate incomeAmount =
   Query (\conn -> do
-    mbIncome <- fmap (\(Row i) -> i) . Maybe.listToMaybe <$>
+    income <- fmap (\(Row i) -> i) . Maybe.listToMaybe <$>
       SQLite.query conn "SELECT * FROM income WHERE id = ?" (Only incomeId)
-    case mbIncome of
-      Just income ->
-        do
-          currentTime <- getCurrentTime
-          SQLite.execute
-            conn
-            "UPDATE income SET edited_at = ?, date = ?, amount = ? WHERE id = ? AND user_id = ?"
-            (currentTime, incomeDate, incomeAmount, incomeId, userId)
-          return . Just $ Income
-            { _income_id        = incomeId
-            , _income_userId    = userId
-            , _income_date      = incomeDate
-            , _income_amount    = incomeAmount
-            , _income_createdAt = _income_createdAt income
-            , _income_editedAt  = Just currentTime
-            , _income_deletedAt = Nothing
-            }
-      Nothing ->
-        return Nothing
+    if Maybe.isJust income then
+      do
+        currentTime <- getCurrentTime
+        SQLite.execute
+          conn
+          "UPDATE income SET edited_at = ?, date = ?, amount = ? WHERE id = ? AND user_id = ?"
+          (currentTime, incomeDate, incomeAmount, incomeId, userId)
+        return True
+    else
+      return False
   )
 
 delete :: UserId -> PaymentId -> Query ()
-- 
cgit v1.2.3


From fff99e6fb1c03235e219a94ce52acf5a50d3fb62 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 5 Jan 2020 16:03:48 +0100
Subject: Use named parameters instead of positional parameters in persistence
 queries

---
 server/src/Persistence/Income.hs | 56 ++++++++++++++++++++++++----------------
 1 file changed, 34 insertions(+), 22 deletions(-)

(limited to 'server/src/Persistence/Income.hs')

diff --git a/server/src/Persistence/Income.hs b/server/src/Persistence/Income.hs
index cd98814..76cb952 100644
--- a/server/src/Persistence/Income.hs
+++ b/server/src/Persistence/Income.hs
@@ -17,8 +17,7 @@ import qualified Data.Text              as T
 import           Data.Time.Calendar     (Day)
 import           Data.Time.Clock        (UTCTime)
 import           Data.Time.Clock        (getCurrentTime)
-import           Database.SQLite.Simple (FromRow (fromRow), NamedParam ((:=)),
-                                         Only (Only))
+import           Database.SQLite.Simple (FromRow (fromRow), NamedParam ((:=)))
 import qualified Database.SQLite.Simple as SQLite
 import           Prelude                hiding (id, until)
 
@@ -55,63 +54,76 @@ list :: Int -> Int -> Query [Income]
 list page perPage =
   Query (\conn ->
     map (\(Row i) -> i) <$>
-      SQLite.query
+      SQLite.queryNamed
         conn
-        "SELECT * FROM income WHERE deleted_at IS NULL ORDER BY date DESC LIMIT ? OFFSET ?"
-        (perPage, (page - 1) * perPage)
+        "SELECT * FROM income WHERE deleted_at IS NULL ORDER BY date DESC LIMIT :limit OFFSET :offset"
+        [ ":limit" := perPage
+        , ":offset" := (page - 1) * perPage
+        ]
   )
 
 listModifiedSince :: UTCTime -> Query [Income]
 listModifiedSince since =
   Query (\conn ->
     map (\(Row i) -> i) <$>
-      SQLite.query
+      SQLite.queryNamed
         conn
         (SQLite.Query . T.intercalate " " $
           [ "SELECT *"
           , "FROM income"
           , "WHERE"
-          ,   "created_at >= ?"
-          ,   "OR edited_at >= ?"
-          ,   "OR deleted_at >= ?"
+          ,   "created_at >= :since"
+          ,   "OR edited_at >= :since"
+          ,   "OR deleted_at >= :since"
           ])
-        (since, since, since)
+        [ ":since" := since ]
   )
 
 create :: UserId -> Day -> Int -> Query ()
 create userId date amount =
   Query (\conn -> do
     createdAt <- getCurrentTime
-    SQLite.execute
+    SQLite.executeNamed
       conn
-      "INSERT INTO income (user_id, date, amount, created_at) VALUES (?, ?, ?, ?)"
-      (userId, date, amount, createdAt)
+      "INSERT INTO income (user_id, date, amount, created_at) VALUES (:userId, :date, :amount, :createdAt)"
+      [ ":userId" := userId
+      , ":date" := date
+      , ":amount" := amount
+      , ":createdAt" := createdAt
+      ]
   )
 
 edit :: UserId -> IncomeId -> Day -> Int -> Query Bool
-edit userId incomeId incomeDate incomeAmount =
+edit userId id date amount =
   Query (\conn -> do
     income <- fmap (\(Row i) -> i) . Maybe.listToMaybe <$>
-      SQLite.query conn "SELECT * FROM income WHERE id = ?" (Only incomeId)
+      SQLite.queryNamed conn "SELECT * FROM income WHERE id = :id" [ ":id" := id ]
     if Maybe.isJust income then
       do
         currentTime <- getCurrentTime
-        SQLite.execute
+        SQLite.executeNamed
           conn
-          "UPDATE income SET edited_at = ?, date = ?, amount = ? WHERE id = ? AND user_id = ?"
-          (currentTime, incomeDate, incomeAmount, incomeId, userId)
+          "UPDATE income SET edited_at = :editedAt, date = :date, amount = :amount WHERE id = :id AND user_id = :userId"
+          [ ":editedAt" := currentTime
+          , ":date" := date
+          , ":amount" := amount
+          , ":id" := id
+          , ":userId" := userId
+          ]
         return True
     else
       return False
   )
 
 delete :: UserId -> PaymentId -> Query ()
-delete userId paymentId =
+delete userId id =
   Query (\conn ->
-    SQLite.execute
+    SQLite.executeNamed
       conn
-      "UPDATE income SET deleted_at = datetime('now') WHERE id = ? AND user_id = ?"
-      (paymentId, userId)
+      "UPDATE income SET deleted_at = datetime('now') WHERE id = :id AND user_id = :userId"
+      [ ":id" := id
+      , ":userId" := userId
+      ]
   )
 
 data UserDayRow = UserDayRow (UserId, Day)
-- 
cgit v1.2.3


From 79e1d8b0099d61b580a499311f1714b1b7eb07b5 Mon Sep 17 00:00:00 2001
From: Joris
Date: Mon, 27 Jan 2020 22:07:18 +0100
Subject: Show total incom by month in statistics

---
 server/src/Persistence/Income.hs | 13 ++++++++++++-
 1 file changed, 12 insertions(+), 1 deletion(-)

(limited to 'server/src/Persistence/Income.hs')

diff --git a/server/src/Persistence/Income.hs b/server/src/Persistence/Income.hs
index 76cb952..1b5364c 100644
--- a/server/src/Persistence/Income.hs
+++ b/server/src/Persistence/Income.hs
@@ -1,5 +1,6 @@
 module Persistence.Income
-  ( count
+  ( listAll
+  , count
   , list
   , listModifiedSince
   , create
@@ -43,6 +44,16 @@ data CountRow = CountRow Int
 instance FromRow CountRow where
   fromRow = CountRow <$> SQLite.field
 
+listAll :: Query [Income]
+listAll =
+  Query (\conn ->
+    map (\(Row i) -> i) <$>
+      SQLite.query_
+        conn
+        "SELECT * FROM income WHERE deleted_at IS NULL ORDER BY date DESC"
+  )
+
+
 count :: Query Int
 count =
   Query (\conn ->
-- 
cgit v1.2.3