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/Category.hs        |  79 ++++++++++++++
 server/src/Persistence/Frequency.hs       |  23 ++++
 server/src/Persistence/Income.hs          |  88 ++++++++++++++++
 server/src/Persistence/Init.hs            |  25 +++++
 server/src/Persistence/Payment.hs         | 169 ++++++++++++++++++++++++++++++
 server/src/Persistence/PaymentCategory.hs |  66 ++++++++++++
 server/src/Persistence/User.hs            |  37 +++++++
 7 files changed, 487 insertions(+)
 create mode 100644 server/src/Persistence/Category.hs
 create mode 100644 server/src/Persistence/Frequency.hs
 create mode 100644 server/src/Persistence/Income.hs
 create mode 100644 server/src/Persistence/Init.hs
 create mode 100644 server/src/Persistence/Payment.hs
 create mode 100644 server/src/Persistence/PaymentCategory.hs
 create mode 100644 server/src/Persistence/User.hs

(limited to 'server/src/Persistence')

diff --git a/server/src/Persistence/Category.hs b/server/src/Persistence/Category.hs
new file mode 100644
index 0000000..2afe5db
--- /dev/null
+++ b/server/src/Persistence/Category.hs
@@ -0,0 +1,79 @@
+module Persistence.Category
+  ( list
+  , create
+  , edit
+  , delete
+  ) where
+
+import           Data.Maybe             (isJust, listToMaybe)
+import           Data.Text              (Text)
+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           (Category (..), CategoryId)
+
+import           Model.Query            (Query (Query))
+
+newtype Row = Row Category
+
+instance FromRow Row where
+  fromRow = Row <$> (Category <$>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field)
+
+list :: Query [Category]
+list =
+  Query (\conn ->
+    map (\(Row c) -> c) <$>
+      SQLite.query_ conn "SELECT * FROM category WHERE deleted_at IS NULL"
+  )
+
+create :: Text -> Text -> Query CategoryId
+create categoryName categoryColor =
+  Query (\conn -> do
+    now <- getCurrentTime
+    SQLite.execute
+      conn
+      "INSERT INTO category (name, color, created_at) VALUES (?, ?, ?)"
+      (categoryName, categoryColor, now)
+    SQLite.lastInsertRowId conn
+  )
+
+edit :: CategoryId -> Text -> Text -> Query Bool
+edit categoryId categoryName categoryColor =
+  Query (\conn -> do
+    mbCategory <- fmap (\(Row c) -> c) . listToMaybe <$>
+      (SQLite.query conn "SELECT * FROM category WHERE id = ?" (Only categoryId))
+    if isJust mbCategory
+      then do
+        now <- getCurrentTime
+        SQLite.execute
+          conn
+          "UPDATE category SET edited_at = ?, name = ?, color = ? WHERE id = ?"
+          (now, categoryName, categoryColor, categoryId)
+        return True
+      else
+        return False
+  )
+
+delete :: CategoryId -> Query Bool
+delete categoryId =
+  Query (\conn -> do
+    mbCategory <- fmap (\(Row c) -> c) . listToMaybe <$>
+      (SQLite.query conn "SELECT * FROM category WHERE id = ?" (Only categoryId))
+    if isJust mbCategory
+      then do
+        now <- getCurrentTime
+        SQLite.execute
+          conn
+          "UPDATE category SET deleted_at = ? WHERE id = ?" (now, categoryId)
+        return True
+      else
+        return False
+  )
diff --git a/server/src/Persistence/Frequency.hs b/server/src/Persistence/Frequency.hs
new file mode 100644
index 0000000..edaa844
--- /dev/null
+++ b/server/src/Persistence/Frequency.hs
@@ -0,0 +1,23 @@
+module Persistence.Frequency
+  ( FrequencyField(..)
+  ) where
+
+import qualified Data.Text                        as T
+import           Database.SQLite.Simple           (SQLData (SQLText))
+import           Database.SQLite.Simple.FromField (FromField (fromField),
+                                                   fieldData)
+import           Database.SQLite.Simple.Ok        (Ok (Errors, Ok))
+import           Database.SQLite.Simple.ToField   (ToField (toField))
+
+import           Common.Model                     (Frequency)
+
+newtype FrequencyField = FrequencyField Frequency
+
+instance FromField FrequencyField where
+  fromField field =
+    case fieldData field of
+      SQLText text -> Ok (FrequencyField (read (T.unpack text) :: Frequency))
+      _            -> Errors [error "SQLText field required for frequency"]
+
+instance ToField FrequencyField where
+  toField (FrequencyField f) = SQLText . T.pack . show $ f
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
+  )
diff --git a/server/src/Persistence/Init.hs b/server/src/Persistence/Init.hs
new file mode 100644
index 0000000..74d9172
--- /dev/null
+++ b/server/src/Persistence/Init.hs
@@ -0,0 +1,25 @@
+module Persistence.Init
+  ( getInit
+  ) where
+
+import           Common.Model                (Init (Init), User (..))
+
+import           Conf                        (Conf)
+import qualified Conf
+import           Model.Query                 (Query)
+import qualified Persistence.Category        as CategoryPersistence
+import qualified Persistence.Income          as IncomePersistence
+import qualified Persistence.Payment         as PaymentPersistence
+import qualified Persistence.PaymentCategory as PaymentCategoryPersistence
+import qualified Persistence.User            as UserPersistence
+
+getInit :: User -> Conf -> Query Init
+getInit user conf =
+  Init <$>
+    UserPersistence.list <*>
+    (return . _user_id $ user) <*>
+    PaymentPersistence.listActive <*>
+    IncomePersistence.list <*>
+    CategoryPersistence.list <*>
+    PaymentCategoryPersistence.list <*>
+    (return . Conf.currency $ conf)
diff --git a/server/src/Persistence/Payment.hs b/server/src/Persistence/Payment.hs
new file mode 100644
index 0000000..32600d7
--- /dev/null
+++ b/server/src/Persistence/Payment.hs
@@ -0,0 +1,169 @@
+module Persistence.Payment
+  ( Payment(..)
+  , find
+  , listActive
+  , listPunctual
+  , listActiveMonthlyOrderedByName
+  , create
+  , createMany
+  , editOwn
+  , deleteOwn
+  ) where
+
+import           Data.Maybe                     (listToMaybe)
+import           Data.Text                      (Text)
+import qualified Data.Text                      as T
+import           Data.Time.Calendar             (Day)
+import           Data.Time.Clock                (getCurrentTime)
+import           Database.SQLite.Simple         (FromRow (fromRow), Only (Only),
+                                                 ToRow)
+import qualified Database.SQLite.Simple         as SQLite
+import           Database.SQLite.Simple.ToField (ToField (toField))
+import           Prelude                        hiding (id)
+
+import           Common.Model                   (Frequency (..), Payment (..),
+                                                 PaymentId, UserId)
+
+import           Model.Query                    (Query (Query))
+import           Persistence.Frequency          (FrequencyField (..))
+
+newtype Row = Row Payment
+
+instance FromRow Row where
+  fromRow = Row <$> (Payment <$>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field <*>
+    (fmap (\(FrequencyField f) -> f) $ SQLite.field) <*>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field)
+
+newtype InsertRow = InsertRow Payment
+
+instance ToRow InsertRow where
+  toRow (InsertRow p) =
+    [ toField (_payment_user p)
+    , toField (_payment_name p)
+    , toField (_payment_cost p)
+    , toField (_payment_date p)
+    , toField (FrequencyField (_payment_frequency p))
+    , toField (_payment_createdAt p)
+    ]
+
+find :: PaymentId -> Query (Maybe Payment)
+find paymentId =
+  Query (\conn -> do
+    fmap (\(Row p) -> p) . listToMaybe <$>
+      SQLite.query conn "SELECT * FROM payment WHERE id = ?" (Only paymentId)
+  )
+
+listActive :: Query [Payment]
+listActive =
+  Query (\conn -> do
+    map (\(Row p) -> p) <$>
+      SQLite.query_ conn "SELECT * FROM payment WHERE deleted_at IS NULL"
+  )
+
+listPunctual :: Query [Payment]
+listPunctual =
+  Query (\conn -> do
+    map (\(Row p) -> p) <$>
+      SQLite.query
+        conn
+        (SQLite.Query "SELECT * FROM payment WHERE frequency = ?")
+        (Only (FrequencyField Punctual))
+  )
+
+listActiveMonthlyOrderedByName :: Query [Payment]
+listActiveMonthlyOrderedByName =
+  Query (\conn -> do
+    map (\(Row p) -> p) <$>
+      SQLite.query
+        conn
+        (SQLite.Query $ T.intercalate " "
+          [ "SELECT *"
+          , "FROM payment"
+          , "WHERE deleted_at IS NULL AND frequency = ?"
+          , "ORDER BY name DESC"
+          ])
+        (Only (FrequencyField Monthly))
+  )
+
+create :: UserId -> Text -> Int -> Day -> Frequency -> Query PaymentId
+create userId paymentName paymentCost paymentDate paymentFrequency =
+  Query (\conn -> do
+    now <- getCurrentTime
+    SQLite.execute
+      conn
+      (SQLite.Query $ T.intercalate " "
+        [ "INSERT INTO payment (user_id, name, cost, date, frequency, created_at)"
+        , "VALUES (?, ?, ?, ?, ?, ?)"
+        ])
+      (userId, paymentName, paymentCost, paymentDate, FrequencyField paymentFrequency, now)
+    SQLite.lastInsertRowId conn
+  )
+
+createMany :: [Payment] -> Query ()
+createMany payments =
+  Query (\conn ->
+    SQLite.executeMany
+      conn
+      (SQLite.Query $ T.intercalate ""
+        [ "INSERT INTO payment (user_id, name, cost, date, frequency, created_at)"
+        , "VALUES (?, ?, ?, ?, ?, ?)"
+        ])
+      (map InsertRow payments)
+  )
+
+editOwn :: UserId -> PaymentId -> Text -> Int -> Day -> Frequency -> Query Bool
+editOwn userId paymentId paymentName paymentCost paymentDate paymentFrequency =
+  Query (\conn -> do
+    mbPayment <- fmap (\(Row p) -> p) . listToMaybe <$>
+      SQLite.query conn "SELECT * FROM payment WHERE id = ?" (Only paymentId)
+    case mbPayment of
+      Just payment ->
+        if _payment_user payment == userId
+          then do
+            now <- getCurrentTime
+            SQLite.execute
+              conn
+              (SQLite.Query $ T.intercalate " "
+                [ "UPDATE payment"
+                , "SET edited_at = ?,"
+                , "    name = ?,"
+                , "    cost = ?,"
+                , "    date = ?,"
+                , "    frequency = ?"
+                , "WHERE id = ?"
+                ])
+              (now, paymentName, paymentCost, paymentDate, FrequencyField paymentFrequency, paymentId)
+            return True
+          else
+            return False
+      Nothing ->
+        return False
+  )
+
+deleteOwn :: UserId -> PaymentId -> Query Bool
+deleteOwn userId paymentId =
+  Query (\conn -> do
+    mbPayment <- listToMaybe <$>
+      SQLite.query conn "SELECT * FROM payment WHERE id = ?" (Only paymentId)
+    case mbPayment of
+      Just (Row payment) ->
+        if _payment_user payment == userId
+          then do
+            now <- getCurrentTime
+            SQLite.execute
+              conn
+              "UPDATE payment SET deleted_at = ? WHERE id = ?"
+              (now, paymentId)
+            return True
+          else
+            return False
+      Nothing ->
+        return False
+  )
diff --git a/server/src/Persistence/PaymentCategory.hs b/server/src/Persistence/PaymentCategory.hs
new file mode 100644
index 0000000..1e377b1
--- /dev/null
+++ b/server/src/Persistence/PaymentCategory.hs
@@ -0,0 +1,66 @@
+module Persistence.PaymentCategory
+  ( list
+  , listByCategory
+  , save
+  ) where
+
+import           Data.Maybe             (isJust, listToMaybe)
+import           Data.Text              (Text)
+import qualified Data.Text              as T
+import           Data.Time.Clock        (getCurrentTime)
+import           Database.SQLite.Simple (FromRow (fromRow), Only (Only))
+import qualified Database.SQLite.Simple as SQLite
+
+import           Common.Model           (CategoryId, PaymentCategory (..))
+import qualified Common.Util.Text       as T
+
+import           Model.Query            (Query (Query))
+
+newtype Row = Row PaymentCategory
+
+instance FromRow Row where
+  fromRow = Row <$> (PaymentCategory <$>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field)
+
+list :: Query [PaymentCategory]
+list =
+  Query (\conn -> do
+    map (\(Row pc) -> pc) <$>
+      SQLite.query_ conn "SELECT * from payment_category"
+  )
+
+listByCategory :: CategoryId -> Query [PaymentCategory]
+listByCategory cat =
+  Query (\conn -> do
+    map (\(Row pc) -> pc) <$>
+      SQLite.query conn "SELECT * FROM payment_category WHERE category = ?" (Only cat)
+  )
+
+save :: Text -> CategoryId -> Query ()
+save newName categoryId =
+  Query (\conn -> do
+    now <- getCurrentTime
+    hasPaymentCategory <- isJust <$> listToMaybe <$>
+      (SQLite.query
+        conn
+        "SELECT * FROM payment_category WHERE name = ?"
+        (Only (formatPaymentName newName)) :: IO [Row])
+    if hasPaymentCategory
+      then
+        SQLite.execute
+          conn
+          "UPDATE payment_category SET category = ?, edited_at = ? WHERE name = ?"
+          (categoryId, now, formatPaymentName newName)
+      else do
+        SQLite.execute
+          conn
+          "INSERT INTO payment_category (name, category, created_at) VALUES (?, ?, ?)"
+          (formatPaymentName newName, categoryId, now)
+  )
+  where
+    formatPaymentName :: Text -> Text
+    formatPaymentName = T.unaccent . T.toLower
diff --git a/server/src/Persistence/User.hs b/server/src/Persistence/User.hs
new file mode 100644
index 0000000..4ec2dcf
--- /dev/null
+++ b/server/src/Persistence/User.hs
@@ -0,0 +1,37 @@
+module Persistence.User
+  ( list
+  , get
+  ) where
+
+import           Data.Maybe             (listToMaybe)
+import           Data.Text              (Text)
+import           Database.SQLite.Simple (FromRow (fromRow), Only (Only))
+import qualified Database.SQLite.Simple as SQLite
+import           Prelude                hiding (id)
+
+import           Common.Model           (User (..))
+
+import           Model.Query            (Query (Query))
+
+newtype Row = Row User
+
+instance FromRow Row where
+  fromRow = Row <$> (User <$>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field <*>
+    SQLite.field)
+
+list :: Query [User]
+list =
+  Query (\conn -> do
+    map (\(Row u) -> u) <$>
+      SQLite.query_ conn "SELECT * from user ORDER BY creation DESC"
+  )
+
+get :: Text -> Query (Maybe User)
+get userEmail =
+  Query (\conn -> do
+    fmap (\(Row u) -> u) . listToMaybe <$>
+      SQLite.query conn "SELECT * FROM user WHERE email = ? LIMIT 1" (Only userEmail)
+  )
-- 
cgit v1.2.3


From 50fb8fa48d1c4881da20b4ecf6d68a772301e713 Mon Sep 17 00:00:00 2001
From: Joris
Date: Tue, 30 Oct 2018 18:04:58 +0100
Subject: Update table when adding or removing a payment

---
 server/src/Persistence/Payment.hs | 21 ++++++++++++++++-----
 1 file changed, 16 insertions(+), 5 deletions(-)

(limited to 'server/src/Persistence')

diff --git a/server/src/Persistence/Payment.hs b/server/src/Persistence/Payment.hs
index 32600d7..272cd39 100644
--- a/server/src/Persistence/Payment.hs
+++ b/server/src/Persistence/Payment.hs
@@ -92,18 +92,29 @@ listActiveMonthlyOrderedByName =
         (Only (FrequencyField Monthly))
   )
 
-create :: UserId -> Text -> Int -> Day -> Frequency -> Query PaymentId
-create userId paymentName paymentCost paymentDate paymentFrequency =
+create :: UserId -> Text -> Int -> Day -> Frequency -> Query Payment
+create userId name cost date frequency =
   Query (\conn -> do
-    now <- getCurrentTime
+    time <- getCurrentTime
     SQLite.execute
       conn
       (SQLite.Query $ T.intercalate " "
         [ "INSERT INTO payment (user_id, name, cost, date, frequency, created_at)"
         , "VALUES (?, ?, ?, ?, ?, ?)"
         ])
-      (userId, paymentName, paymentCost, paymentDate, FrequencyField paymentFrequency, now)
-    SQLite.lastInsertRowId conn
+      (userId, name, cost, date, FrequencyField frequency, time)
+    paymentId <- SQLite.lastInsertRowId conn
+    return $ Payment
+      { _payment_id        = paymentId
+      , _payment_user      = userId
+      , _payment_name      = name
+      , _payment_cost      = cost
+      , _payment_date      = date
+      , _payment_frequency = frequency
+      , _payment_createdAt = time
+      , _payment_editedAt  = Nothing
+      , _payment_deletedAt = Nothing
+      }
   )
 
 createMany :: [Payment] -> Query ()
-- 
cgit v1.2.3


From bc81084933f8ec1bfe6c2834defd12243117fdd9 Mon Sep 17 00:00:00 2001
From: Joris
Date: Mon, 5 Aug 2019 21:53:30 +0200
Subject: Use updated payment categories from payment add in payment’s table

---
 server/src/Persistence/PaymentCategory.hs | 48 ++++++++++++++++++++-----------
 1 file changed, 31 insertions(+), 17 deletions(-)

(limited to 'server/src/Persistence')

diff --git a/server/src/Persistence/PaymentCategory.hs b/server/src/Persistence/PaymentCategory.hs
index 1e377b1..1cfd702 100644
--- a/server/src/Persistence/PaymentCategory.hs
+++ b/server/src/Persistence/PaymentCategory.hs
@@ -4,7 +4,7 @@ module Persistence.PaymentCategory
   , save
   ) where
 
-import           Data.Maybe             (isJust, listToMaybe)
+import qualified Data.Maybe             as Maybe
 import           Data.Text              (Text)
 import qualified Data.Text              as T
 import           Data.Time.Clock        (getCurrentTime)
@@ -40,27 +40,41 @@ listByCategory cat =
       SQLite.query conn "SELECT * FROM payment_category WHERE category = ?" (Only cat)
   )
 
-save :: Text -> CategoryId -> Query ()
+save :: Text -> CategoryId -> Query PaymentCategory
 save newName categoryId =
   Query (\conn -> do
     now <- getCurrentTime
-    hasPaymentCategory <- isJust <$> listToMaybe <$>
+    paymentCategory <- fmap (\(Row pc) -> pc) . Maybe.listToMaybe <$>
       (SQLite.query
         conn
         "SELECT * FROM payment_category WHERE name = ?"
-        (Only (formatPaymentName newName)) :: IO [Row])
-    if hasPaymentCategory
-      then
-        SQLite.execute
-          conn
-          "UPDATE payment_category SET category = ?, edited_at = ? WHERE name = ?"
-          (categoryId, now, formatPaymentName newName)
-      else do
-        SQLite.execute
-          conn
-          "INSERT INTO payment_category (name, category, created_at) VALUES (?, ?, ?)"
-          (formatPaymentName newName, categoryId, now)
+        (Only formattedNewName))
+    case paymentCategory of
+      Just pc ->
+        do
+          SQLite.execute
+            conn
+            "UPDATE payment_category SET category = ?, edited_at = ? WHERE name = ?"
+            (categoryId, now, formattedNewName)
+          return $ PaymentCategory
+            (_paymentCategory_id pc)
+            formattedNewName
+            categoryId
+            (_paymentCategory_createdAt pc)
+            (Just now)
+      Nothing ->
+        do
+          SQLite.execute
+            conn
+            "INSERT INTO payment_category (name, category, created_at) VALUES (?, ?, ?)"
+            (formattedNewName, categoryId, now)
+          paymentCategoryId <- SQLite.lastInsertRowId conn
+          return $ PaymentCategory
+            paymentCategoryId
+            formattedNewName
+            categoryId
+            now
+            Nothing
   )
   where
-    formatPaymentName :: Text -> Text
-    formatPaymentName = T.unaccent . T.toLower
+    formattedNewName = T.unaccent . T.toLower $ newName
-- 
cgit v1.2.3


From fc8be14dd0089eb12b78af7aaaecd8ed57896677 Mon Sep 17 00:00:00 2001
From: Joris
Date: Wed, 7 Aug 2019 21:27:59 +0200
Subject: Update category according to payment in add overlay

---
 server/src/Persistence/PaymentCategory.hs | 3 +--
 1 file changed, 1 insertion(+), 2 deletions(-)

(limited to 'server/src/Persistence')

diff --git a/server/src/Persistence/PaymentCategory.hs b/server/src/Persistence/PaymentCategory.hs
index 1cfd702..5fd035a 100644
--- a/server/src/Persistence/PaymentCategory.hs
+++ b/server/src/Persistence/PaymentCategory.hs
@@ -6,7 +6,6 @@ module Persistence.PaymentCategory
 
 import qualified Data.Maybe             as Maybe
 import           Data.Text              (Text)
-import qualified Data.Text              as T
 import           Data.Time.Clock        (getCurrentTime)
 import           Database.SQLite.Simple (FromRow (fromRow), Only (Only))
 import qualified Database.SQLite.Simple as SQLite
@@ -77,4 +76,4 @@ save newName categoryId =
             Nothing
   )
   where
-    formattedNewName = T.unaccent . T.toLower $ newName
+    formattedNewName = T.formatSearch newName
-- 
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 +++++++++++--------------------
 server/src/Persistence/Payment.hs         | 34 ++++++++++---------------------
 server/src/Persistence/PaymentCategory.hs | 14 +++++++++++--
 3 files changed, 35 insertions(+), 47 deletions(-)

(limited to 'server/src/Persistence')

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)
   )
diff --git a/server/src/Persistence/Payment.hs b/server/src/Persistence/Payment.hs
index 272cd39..3d8f129 100644
--- a/server/src/Persistence/Payment.hs
+++ b/server/src/Persistence/Payment.hs
@@ -6,8 +6,8 @@ module Persistence.Payment
   , listActiveMonthlyOrderedByName
   , create
   , createMany
-  , editOwn
-  , deleteOwn
+  , edit
+  , delete
   ) where
 
 import           Data.Maybe                     (listToMaybe)
@@ -129,8 +129,8 @@ createMany payments =
       (map InsertRow payments)
   )
 
-editOwn :: UserId -> PaymentId -> Text -> Int -> Day -> Frequency -> Query Bool
-editOwn userId paymentId paymentName paymentCost paymentDate paymentFrequency =
+edit :: UserId -> PaymentId -> Text -> Int -> Day -> Frequency -> Query Bool
+edit userId paymentId paymentName paymentCost paymentDate paymentFrequency =
   Query (\conn -> do
     mbPayment <- fmap (\(Row p) -> p) . listToMaybe <$>
       SQLite.query conn "SELECT * FROM payment WHERE id = ?" (Only paymentId)
@@ -158,23 +158,11 @@ editOwn userId paymentId paymentName paymentCost paymentDate paymentFrequency =
         return False
   )
 
-deleteOwn :: UserId -> PaymentId -> Query Bool
-deleteOwn userId paymentId =
-  Query (\conn -> do
-    mbPayment <- listToMaybe <$>
-      SQLite.query conn "SELECT * FROM payment WHERE id = ?" (Only paymentId)
-    case mbPayment of
-      Just (Row payment) ->
-        if _payment_user payment == userId
-          then do
-            now <- getCurrentTime
-            SQLite.execute
-              conn
-              "UPDATE payment SET deleted_at = ? WHERE id = ?"
-              (now, paymentId)
-            return True
-          else
-            return False
-      Nothing ->
-        return False
+delete :: UserId -> PaymentId -> Query ()
+delete userId paymentId =
+  Query (\conn ->
+    SQLite.execute
+      conn
+      "UPDATE payment SET deleted_at = datetime('now') WHERE id = ? AND user_id = ?"
+      (paymentId, userId)
   )
diff --git a/server/src/Persistence/PaymentCategory.hs b/server/src/Persistence/PaymentCategory.hs
index 5fd035a..7dc363c 100644
--- a/server/src/Persistence/PaymentCategory.hs
+++ b/server/src/Persistence/PaymentCategory.hs
@@ -2,16 +2,17 @@ module Persistence.PaymentCategory
   ( list
   , listByCategory
   , save
+  , deleteIfUnused
   ) where
 
 import qualified Data.Maybe             as Maybe
 import           Data.Text              (Text)
+import qualified Data.Text              as T
 import           Data.Time.Clock        (getCurrentTime)
 import           Database.SQLite.Simple (FromRow (fromRow), Only (Only))
 import qualified Database.SQLite.Simple as SQLite
 
 import           Common.Model           (CategoryId, PaymentCategory (..))
-import qualified Common.Util.Text       as T
 
 import           Model.Query            (Query (Query))
 
@@ -76,4 +77,13 @@ save newName categoryId =
             Nothing
   )
   where
-    formattedNewName = T.formatSearch newName
+    formattedNewName = T.toLower newName
+
+deleteIfUnused :: Text -> Query ()
+deleteIfUnused name =
+  Query (\conn ->
+    SQLite.execute
+      conn
+      "DELETE FROM payment_category WHERE name = lower(?) AND name IN (SELECT DISTINCT lower(name) FROM payment WHERE name = lower(?) AND deleted_at IS NOT NULL)"
+      (name, name)
+  ) >> return ()
-- 
cgit v1.2.3


From 2d79ab0e0a11f55255fc21a5dfab1598d3beeba3 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 11 Aug 2019 22:40:09 +0200
Subject: Add payment clone

---
 server/src/Persistence/Payment.hs | 66 +++++++++++++++++++++++++--------------
 1 file changed, 43 insertions(+), 23 deletions(-)

(limited to 'server/src/Persistence')

diff --git a/server/src/Persistence/Payment.hs b/server/src/Persistence/Payment.hs
index 3d8f129..b3f2b2e 100644
--- a/server/src/Persistence/Payment.hs
+++ b/server/src/Persistence/Payment.hs
@@ -129,33 +129,53 @@ createMany payments =
       (map InsertRow payments)
   )
 
-edit :: UserId -> PaymentId -> Text -> Int -> Day -> Frequency -> Query Bool
-edit userId paymentId paymentName paymentCost paymentDate paymentFrequency =
+edit :: UserId -> PaymentId -> Text -> Int -> Day -> Frequency -> Query (Maybe Payment)
+edit userId paymentId name cost date frequency =
   Query (\conn -> do
     mbPayment <- fmap (\(Row p) -> p) . listToMaybe <$>
-      SQLite.query conn "SELECT * FROM payment WHERE id = ?" (Only paymentId)
+      SQLite.query
+        conn
+        "SELECT * FROM payment WHERE id = ? and userId = ?"
+        (paymentId, userId)
     case mbPayment of
-      Just payment ->
-        if _payment_user payment == userId
-          then do
-            now <- getCurrentTime
-            SQLite.execute
-              conn
-              (SQLite.Query $ T.intercalate " "
-                [ "UPDATE payment"
-                , "SET edited_at = ?,"
-                , "    name = ?,"
-                , "    cost = ?,"
-                , "    date = ?,"
-                , "    frequency = ?"
-                , "WHERE id = ?"
-                ])
-              (now, paymentName, paymentCost, paymentDate, FrequencyField paymentFrequency, paymentId)
-            return True
-          else
-            return False
+      Just payment -> do
+        now <- getCurrentTime
+        SQLite.execute
+          conn
+          (SQLite.Query $ T.intercalate " "
+            [ "UPDATE"
+            , "  payment"
+            , "SET"
+            , "  edited_at = ?,"
+            , "  name = ?,"
+            , "  cost = ?,"
+            , "  date = ?,"
+            , "  frequency = ?"
+            , "WHERE"
+            , "  id = ?"
+            , "  AND user_id = ?"
+            ])
+          ( now
+          , name
+          , cost
+          , date
+          , FrequencyField frequency
+          , paymentId
+          , userId
+          )
+        return . Just $ Payment
+          { _payment_id        = paymentId
+          , _payment_user      = userId
+          , _payment_name      = name
+          , _payment_cost      = cost
+          , _payment_date      = date
+          , _payment_frequency = frequency
+          , _payment_createdAt = _payment_createdAt payment
+          , _payment_editedAt  = Just now
+          , _payment_deletedAt = Nothing
+          }
       Nothing ->
-        return False
+        return Nothing
   )
 
 delete :: UserId -> PaymentId -> Query ()
-- 
cgit v1.2.3


From f4c5df9e1b1afddeb5a482d4fbe654d0b321159c Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 6 Oct 2019 19:28:54 +0200
Subject: Make payment edition to work on the frontend

---
 server/src/Persistence/Payment.hs         | 6 +++---
 server/src/Persistence/PaymentCategory.hs | 2 +-
 2 files changed, 4 insertions(+), 4 deletions(-)

(limited to 'server/src/Persistence')

diff --git a/server/src/Persistence/Payment.hs b/server/src/Persistence/Payment.hs
index b3f2b2e..bcd7eb8 100644
--- a/server/src/Persistence/Payment.hs
+++ b/server/src/Persistence/Payment.hs
@@ -129,13 +129,13 @@ createMany payments =
       (map InsertRow payments)
   )
 
-edit :: UserId -> PaymentId -> Text -> Int -> Day -> Frequency -> Query (Maybe Payment)
+edit :: UserId -> PaymentId -> Text -> Int -> Day -> Frequency -> Query (Maybe (Payment, Payment))
 edit userId paymentId name cost date frequency =
   Query (\conn -> do
     mbPayment <- fmap (\(Row p) -> p) . listToMaybe <$>
       SQLite.query
         conn
-        "SELECT * FROM payment WHERE id = ? and userId = ?"
+        "SELECT * FROM payment WHERE id = ? and user_id = ?"
         (paymentId, userId)
     case mbPayment of
       Just payment -> do
@@ -163,7 +163,7 @@ edit userId paymentId name cost date frequency =
           , paymentId
           , userId
           )
-        return . Just $ Payment
+        return . Just . (,) payment $ Payment
           { _payment_id        = paymentId
           , _payment_user      = userId
           , _payment_name      = name
diff --git a/server/src/Persistence/PaymentCategory.hs b/server/src/Persistence/PaymentCategory.hs
index 7dc363c..46be7f5 100644
--- a/server/src/Persistence/PaymentCategory.hs
+++ b/server/src/Persistence/PaymentCategory.hs
@@ -84,6 +84,6 @@ deleteIfUnused name =
   Query (\conn ->
     SQLite.execute
       conn
-      "DELETE FROM payment_category WHERE name = lower(?) AND name IN (SELECT DISTINCT lower(name) FROM payment WHERE name = lower(?) AND deleted_at IS NOT NULL)"
+      "DELETE FROM payment_category WHERE name = lower(?) AND name NOT IN (SELECT DISTINCT lower(name) FROM payment WHERE lower(name) = lower(?) AND deleted_at IS NULL)"
       (name, name)
   ) >> return ()
-- 
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')

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 602c52acfcfa494b07fec05c20b317b60ea8a6f3 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 20 Oct 2019 21:31:57 +0200
Subject: Load init data per page with AJAX

---
 server/src/Persistence/Init.hs | 25 -------------------------
 1 file changed, 25 deletions(-)
 delete mode 100644 server/src/Persistence/Init.hs

(limited to 'server/src/Persistence')

diff --git a/server/src/Persistence/Init.hs b/server/src/Persistence/Init.hs
deleted file mode 100644
index 74d9172..0000000
--- a/server/src/Persistence/Init.hs
+++ /dev/null
@@ -1,25 +0,0 @@
-module Persistence.Init
-  ( getInit
-  ) where
-
-import           Common.Model                (Init (Init), User (..))
-
-import           Conf                        (Conf)
-import qualified Conf
-import           Model.Query                 (Query)
-import qualified Persistence.Category        as CategoryPersistence
-import qualified Persistence.Income          as IncomePersistence
-import qualified Persistence.Payment         as PaymentPersistence
-import qualified Persistence.PaymentCategory as PaymentCategoryPersistence
-import qualified Persistence.User            as UserPersistence
-
-getInit :: User -> Conf -> Query Init
-getInit user conf =
-  Init <$>
-    UserPersistence.list <*>
-    (return . _user_id $ user) <*>
-    PaymentPersistence.listActive <*>
-    IncomePersistence.list <*>
-    CategoryPersistence.list <*>
-    PaymentCategoryPersistence.list <*>
-    (return . Conf.currency $ conf)
-- 
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')

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')

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')

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 0f85cbd8ee736b1996e3966bac1f5e47ed7d27a9 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 3 Nov 2019 15:47:11 +0100
Subject: Fetch the first payment date instead of every payment to get
 cumulative income

---
 server/src/Persistence/Payment.hs | 16 ++++++++++++++++
 1 file changed, 16 insertions(+)

(limited to 'server/src/Persistence')

diff --git a/server/src/Persistence/Payment.hs b/server/src/Persistence/Payment.hs
index bcd7eb8..eb238d4 100644
--- a/server/src/Persistence/Payment.hs
+++ b/server/src/Persistence/Payment.hs
@@ -1,6 +1,7 @@
 module Persistence.Payment
   ( Payment(..)
   , find
+  , firstPunctualDay
   , listActive
   , listPunctual
   , listActiveMonthlyOrderedByName
@@ -60,6 +61,21 @@ find paymentId =
       SQLite.query conn "SELECT * FROM payment WHERE id = ?" (Only paymentId)
   )
 
+data DayRow = DayRow Day
+
+instance FromRow DayRow where
+  fromRow = DayRow <$> SQLite.field
+
+firstPunctualDay :: Query (Maybe Day)
+firstPunctualDay =
+  Query (\conn -> do
+    fmap (\(DayRow d) -> d) . listToMaybe <$>
+      SQLite.query
+        conn
+        "SELECT date FROM payment WHERE frequency = ? AND deleted_at IS NULL ORDER BY date LIMIT 1"
+        (Only (FrequencyField Punctual))
+  )
+
 listActive :: Query [Payment]
 listActive =
   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 ---
 server/src/Persistence/Payment.hs | 25 ++++++++++++++++++++++++-
 2 files changed, 24 insertions(+), 4 deletions(-)

(limited to 'server/src/Persistence')

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
diff --git a/server/src/Persistence/Payment.hs b/server/src/Persistence/Payment.hs
index eb238d4..e01753f 100644
--- a/server/src/Persistence/Payment.hs
+++ b/server/src/Persistence/Payment.hs
@@ -1,8 +1,9 @@
 module Persistence.Payment
-  ( Payment(..)
+  ( count
   , find
   , firstPunctualDay
   , listActive
+  , listActivePage
   , listPunctual
   , listActiveMonthlyOrderedByName
   , create
@@ -54,6 +55,18 @@ instance ToRow InsertRow where
     , toField (_payment_createdAt p)
     ]
 
+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 payment WHERE deleted_at IS NULL"
+  )
+
 find :: PaymentId -> Query (Maybe Payment)
 find paymentId =
   Query (\conn -> do
@@ -83,6 +96,16 @@ listActive =
       SQLite.query_ conn "SELECT * FROM payment WHERE deleted_at IS NULL"
   )
 
+listActivePage :: Int -> Int -> Query [Payment]
+listActivePage page perPage =
+  Query (\conn ->
+    map (\(Row p) -> p) <$>
+      SQLite.query
+        conn
+        "SELECT * FROM payment WHERE deleted_at IS NULL ORDER BY date DESC LIMIT ? OFFSET ?"
+        (perPage, (page - 1) * perPage)
+  )
+
 listPunctual :: Query [Payment]
 listPunctual =
   Query (\conn -> do
-- 
cgit v1.2.3


From 4dc84dbda7ba3ea60d13e6f81eeec556974b7c72 Mon Sep 17 00:00:00 2001
From: Joris
Date: Thu, 7 Nov 2019 07:59:41 +0100
Subject: Show payment header infos

---
 server/src/Persistence/Payment.hs | 21 ++++++++++++++++-----
 1 file changed, 16 insertions(+), 5 deletions(-)

(limited to 'server/src/Persistence')

diff --git a/server/src/Persistence/Payment.hs b/server/src/Persistence/Payment.hs
index e01753f..7835c98 100644
--- a/server/src/Persistence/Payment.hs
+++ b/server/src/Persistence/Payment.hs
@@ -89,11 +89,14 @@ firstPunctualDay =
         (Only (FrequencyField Punctual))
   )
 
-listActive :: Query [Payment]
-listActive =
+listActive :: Frequency -> Query [Payment]
+listActive frequency =
   Query (\conn -> do
     map (\(Row p) -> p) <$>
-      SQLite.query_ conn "SELECT * FROM payment WHERE deleted_at IS NULL"
+      SQLite.query
+        conn
+        "SELECT * FROM payment WHERE deleted_at IS NULL AND frequency = ?"
+        (Only (FrequencyField frequency))
   )
 
 listActivePage :: Int -> Int -> Query [Payment]
@@ -102,8 +105,16 @@ listActivePage page perPage =
     map (\(Row p) -> p) <$>
       SQLite.query
         conn
-        "SELECT * FROM payment WHERE deleted_at IS NULL ORDER BY date DESC LIMIT ? OFFSET ?"
-        (perPage, (page - 1) * perPage)
+        (SQLite.Query $ T.intercalate " "
+          [ "SELECT *"
+          , "FROM payment"
+          , "WHERE deleted_at IS NULL AND frequency = ?"
+          , "ORDER BY date DESC"
+          , "LIMIT ?"
+          , "OFFSET ?"
+          ]
+        )
+        (FrequencyField Punctual, perPage, (page - 1) * perPage)
   )
 
 listPunctual :: Query [Payment]
-- 
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/Category.hs        |  10 +-
 server/src/Persistence/Income.hs          |  59 +++++++-
 server/src/Persistence/Payment.hs         | 214 ++++++++++++++++++++++--------
 server/src/Persistence/PaymentCategory.hs |  89 -------------
 server/src/Persistence/User.hs            |   4 +-
 5 files changed, 220 insertions(+), 156 deletions(-)
 delete mode 100644 server/src/Persistence/PaymentCategory.hs

(limited to 'server/src/Persistence')

diff --git a/server/src/Persistence/Category.hs b/server/src/Persistence/Category.hs
index 2afe5db..00cf0a5 100644
--- a/server/src/Persistence/Category.hs
+++ b/server/src/Persistence/Category.hs
@@ -5,7 +5,7 @@ module Persistence.Category
   , delete
   ) where
 
-import           Data.Maybe             (isJust, listToMaybe)
+import qualified Data.Maybe             as Maybe
 import           Data.Text              (Text)
 import           Data.Time.Clock        (getCurrentTime)
 import           Database.SQLite.Simple (FromRow (fromRow), Only (Only))
@@ -48,9 +48,9 @@ create categoryName categoryColor =
 edit :: CategoryId -> Text -> Text -> Query Bool
 edit categoryId categoryName categoryColor =
   Query (\conn -> do
-    mbCategory <- fmap (\(Row c) -> c) . listToMaybe <$>
+    mbCategory <- fmap (\(Row c) -> c) . Maybe.listToMaybe <$>
       (SQLite.query conn "SELECT * FROM category WHERE id = ?" (Only categoryId))
-    if isJust mbCategory
+    if Maybe.isJust mbCategory
       then do
         now <- getCurrentTime
         SQLite.execute
@@ -65,9 +65,9 @@ edit categoryId categoryName categoryColor =
 delete :: CategoryId -> Query Bool
 delete categoryId =
   Query (\conn -> do
-    mbCategory <- fmap (\(Row c) -> c) . listToMaybe <$>
+    mbCategory <- fmap (\(Row c) -> c) . Maybe.listToMaybe <$>
       (SQLite.query conn "SELECT * FROM category WHERE id = ?" (Only categoryId))
-    if isJust mbCategory
+    if Maybe.isJust mbCategory
       then do
         now <- getCurrentTime
         SQLite.execute
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
diff --git a/server/src/Persistence/Payment.hs b/server/src/Persistence/Payment.hs
index 7835c98..f75925d 100644
--- a/server/src/Persistence/Payment.hs
+++ b/server/src/Persistence/Payment.hs
@@ -1,33 +1,57 @@
 module Persistence.Payment
   ( count
   , find
-  , firstPunctualDay
-  , listActive
+  , getRange
   , listActivePage
-  , listPunctual
+  , listModifiedSince
   , listActiveMonthlyOrderedByName
   , create
   , createMany
   , edit
   , delete
+  , searchCategory
+  , repartition
+  , getPreAndPostPaymentRepartition
   ) where
 
-import           Data.Maybe                     (listToMaybe)
+import           Data.Map                       (Map)
+import qualified Data.Map                       as M
+import qualified Data.Maybe                     as Maybe
 import           Data.Text                      (Text)
 import qualified Data.Text                      as T
 import           Data.Time.Calendar             (Day)
+import qualified Data.Time.Calendar             as Calendar
+import           Data.Time.Clock                (UTCTime)
 import           Data.Time.Clock                (getCurrentTime)
 import           Database.SQLite.Simple         (FromRow (fromRow), Only (Only),
                                                  ToRow)
 import qualified Database.SQLite.Simple         as SQLite
 import           Database.SQLite.Simple.ToField (ToField (toField))
-import           Prelude                        hiding (id)
+import           Prelude                        hiding (id, until)
 
-import           Common.Model                   (Frequency (..), Payment (..),
-                                                 PaymentId, UserId)
+import           Common.Model                   (CategoryId, Frequency (..),
+                                                 Payment (..), PaymentId,
+                                                 User (..), UserId)
 
 import           Model.Query                    (Query (Query))
 import           Persistence.Frequency          (FrequencyField (..))
+import qualified Persistence.Income             as IncomePersistence
+
+
+
+fields :: Text
+fields = T.intercalate "," $
+  [ "id"
+  , "user_id"
+  , "name"
+  , "cost"
+  , "date"
+  , "category"
+  , "frequency"
+  , "created_at"
+  , "edited_at"
+  , "deleted_at"
+  ]
 
 newtype Row = Row Payment
 
@@ -38,6 +62,7 @@ instance FromRow Row where
     SQLite.field <*>
     SQLite.field <*>
     SQLite.field <*>
+    SQLite.field <*>
     (fmap (\(FrequencyField f) -> f) $ SQLite.field) <*>
     SQLite.field <*>
     SQLite.field <*>
@@ -51,6 +76,7 @@ instance ToRow InsertRow where
     , toField (_payment_name p)
     , toField (_payment_cost p)
     , toField (_payment_date p)
+    , toField (_payment_category p)
     , toField (FrequencyField (_payment_frequency p))
     , toField (_payment_createdAt p)
     ]
@@ -60,73 +86,94 @@ data Count = Count Int
 instance FromRow Count where
   fromRow = Count <$> SQLite.field
 
-count :: Query Int
-count =
+count :: Frequency -> Text -> Query Int
+count frequency search =
   Query (\conn ->
     (\[Count n] -> n) <$>
-      SQLite.query_ conn "SELECT COUNT(*) FROM payment WHERE deleted_at IS NULL"
+      SQLite.query
+        conn
+        (SQLite.Query $ T.intercalate " "
+          [ "SELECT COUNT(*)"
+          , "FROM payment"
+          , "WHERE"
+          ,   "deleted_at IS NULL"
+          ,   "AND frequency = ?"
+          ,   "AND name LIKE ?"
+          ])
+        (FrequencyField frequency, "%" <> search <> "%")
   )
 
 find :: PaymentId -> Query (Maybe Payment)
 find paymentId =
   Query (\conn -> do
-    fmap (\(Row p) -> p) . listToMaybe <$>
-      SQLite.query conn "SELECT * FROM payment WHERE id = ?" (Only paymentId)
+    fmap (\(Row p) -> p) . Maybe.listToMaybe <$>
+      SQLite.query
+        conn
+        (SQLite.Query $ "SELECT " <> fields <> " FROM payment WHERE id = ?")
+        (Only paymentId)
   )
 
-data DayRow = DayRow Day
+data RangeRow = RangeRow (Day, Day)
 
-instance FromRow DayRow where
-  fromRow = DayRow <$> SQLite.field
+instance FromRow RangeRow where
+  fromRow = (\f t -> RangeRow (f, t)) <$> SQLite.field <*> SQLite.field
 
-firstPunctualDay :: Query (Maybe Day)
-firstPunctualDay =
+getRange :: Query (Maybe (Day, Day))
+getRange =
   Query (\conn -> do
-    fmap (\(DayRow d) -> d) . listToMaybe <$>
+    fmap (\(RangeRow (f, t)) -> (f, t)) . Maybe.listToMaybe <$>
       SQLite.query
         conn
-        "SELECT date FROM payment WHERE frequency = ? AND deleted_at IS NULL ORDER BY date LIMIT 1"
+        (SQLite.Query $ T.intercalate " "
+          [ "SELECT MIN(date), MAX(date)"
+          , "FROM payment"
+          , "WHERE"
+          ,   "frequency = ?"
+          ,   "AND deleted_at IS NULL"
+          ])
         (Only (FrequencyField Punctual))
   )
 
-listActive :: Frequency -> Query [Payment]
-listActive frequency =
-  Query (\conn -> do
-    map (\(Row p) -> p) <$>
-      SQLite.query
-        conn
-        "SELECT * FROM payment WHERE deleted_at IS NULL AND frequency = ?"
-        (Only (FrequencyField frequency))
-  )
-
-listActivePage :: Int -> Int -> Query [Payment]
-listActivePage page perPage =
+listActivePage :: Frequency -> Int -> Int -> Text -> Query [Payment]
+listActivePage frequency page perPage search =
   Query (\conn ->
     map (\(Row p) -> p) <$>
       SQLite.query
         conn
         (SQLite.Query $ T.intercalate " "
-          [ "SELECT *"
+          [ "SELECT"
+          , fields
           , "FROM payment"
-          , "WHERE deleted_at IS NULL AND frequency = ?"
+          , "WHERE"
+          ,   "deleted_at IS NULL"
+          ,   "AND frequency = ?"
+          ,   "AND name LIKE ?"
           , "ORDER BY date DESC"
           , "LIMIT ?"
           , "OFFSET ?"
           ]
         )
-        (FrequencyField Punctual, perPage, (page - 1) * perPage)
+        (FrequencyField frequency, "%" <> search <> "%", perPage, (page - 1) * perPage)
   )
 
-listPunctual :: Query [Payment]
-listPunctual =
-  Query (\conn -> do
-    map (\(Row p) -> p) <$>
+listModifiedSince :: UTCTime -> Query [Payment]
+listModifiedSince since =
+  Query (\conn ->
+    map (\(Row i) -> i) <$>
       SQLite.query
         conn
-        (SQLite.Query "SELECT * FROM payment WHERE frequency = ?")
-        (Only (FrequencyField Punctual))
+        (SQLite.Query . T.intercalate " " $
+          [ "SELECT *"
+          , "FROM payment"
+          , "WHERE"
+          ,   "created_at >= ?"
+          ,   "OR edited_at >= ?"
+          ,   "OR deleted_at >= ?"
+          ])
+        (Only since)
   )
 
+
 listActiveMonthlyOrderedByName :: Query [Payment]
 listActiveMonthlyOrderedByName =
   Query (\conn -> do
@@ -134,7 +181,8 @@ listActiveMonthlyOrderedByName =
       SQLite.query
         conn
         (SQLite.Query $ T.intercalate " "
-          [ "SELECT *"
+          [ "SELECT"
+          , fields
           , "FROM payment"
           , "WHERE deleted_at IS NULL AND frequency = ?"
           , "ORDER BY name DESC"
@@ -142,17 +190,17 @@ listActiveMonthlyOrderedByName =
         (Only (FrequencyField Monthly))
   )
 
-create :: UserId -> Text -> Int -> Day -> Frequency -> Query Payment
-create userId name cost date frequency =
+create :: UserId -> Text -> Int -> Day -> CategoryId -> Frequency -> Query Payment
+create userId name cost date category frequency =
   Query (\conn -> do
     time <- getCurrentTime
     SQLite.execute
       conn
       (SQLite.Query $ T.intercalate " "
-        [ "INSERT INTO payment (user_id, name, cost, date, frequency, created_at)"
-        , "VALUES (?, ?, ?, ?, ?, ?)"
+        [ "INSERT INTO payment (user_id, name, cost, date, category, frequency, created_at)"
+        , "VALUES (?, ?, ?, ?, ?, ?, ?)"
         ])
-      (userId, name, cost, date, FrequencyField frequency, time)
+      (userId, name, cost, date, category, FrequencyField frequency, time)
     paymentId <- SQLite.lastInsertRowId conn
     return $ Payment
       { _payment_id        = paymentId
@@ -160,6 +208,7 @@ create userId name cost date frequency =
       , _payment_name      = name
       , _payment_cost      = cost
       , _payment_date      = date
+      , _payment_category  = category
       , _payment_frequency = frequency
       , _payment_createdAt = time
       , _payment_editedAt  = Nothing
@@ -173,19 +222,19 @@ createMany payments =
     SQLite.executeMany
       conn
       (SQLite.Query $ T.intercalate ""
-        [ "INSERT INTO payment (user_id, name, cost, date, frequency, created_at)"
-        , "VALUES (?, ?, ?, ?, ?, ?)"
+        [ "INSERT INTO payment (user_id, name, cost, date, category, frequency, created_at)"
+        , "VALUES (?, ?, ?, ?, ?, ?, ?)"
         ])
       (map InsertRow payments)
   )
 
-edit :: UserId -> PaymentId -> Text -> Int -> Day -> Frequency -> Query (Maybe (Payment, Payment))
-edit userId paymentId name cost date frequency =
+edit :: UserId -> PaymentId -> Text -> Int -> Day -> CategoryId -> Frequency -> Query (Maybe Payment)
+edit userId paymentId name cost date category frequency =
   Query (\conn -> do
-    mbPayment <- fmap (\(Row p) -> p) . listToMaybe <$>
+    mbPayment <- fmap (\(Row p) -> p) . Maybe.listToMaybe <$>
       SQLite.query
         conn
-        "SELECT * FROM payment WHERE id = ? and user_id = ?"
+        (SQLite.Query $ "SELECT " <> fields <> " FROM payment WHERE id = ? and user_id = ?")
         (paymentId, userId)
     case mbPayment of
       Just payment -> do
@@ -200,6 +249,7 @@ edit userId paymentId name cost date frequency =
             , "  name = ?,"
             , "  cost = ?,"
             , "  date = ?,"
+            , "  category = ?,"
             , "  frequency = ?"
             , "WHERE"
             , "  id = ?"
@@ -209,16 +259,18 @@ edit userId paymentId name cost date frequency =
           , name
           , cost
           , date
+          , category
           , FrequencyField frequency
           , paymentId
           , userId
           )
-        return . Just . (,) payment $ Payment
+        return . Just $ Payment
           { _payment_id        = paymentId
           , _payment_user      = userId
           , _payment_name      = name
           , _payment_cost      = cost
           , _payment_date      = date
+          , _payment_category  = category
           , _payment_frequency = frequency
           , _payment_createdAt = _payment_createdAt payment
           , _payment_editedAt  = Just now
@@ -236,3 +288,59 @@ delete userId paymentId =
       "UPDATE payment SET deleted_at = datetime('now') WHERE id = ? AND user_id = ?"
       (paymentId, userId)
   )
+
+data CategoryIdRow = CategoryIdRow CategoryId
+
+instance FromRow CategoryIdRow where
+  fromRow = CategoryIdRow <$> SQLite.field
+
+searchCategory :: Text -> Query (Maybe CategoryId)
+searchCategory paymentName =
+  Query (\conn ->
+    fmap (\(CategoryIdRow d) -> d) . Maybe.listToMaybe <$>
+      SQLite.query
+        conn
+        "SELECT category FROM payment WHERE name LIKE ? LIMIT 1"
+        (Only $ "%" <> paymentName <> "%")
+  )
+
+data UserCostRow = UserCostRow (UserId, Int)
+
+instance FromRow UserCostRow where
+  fromRow = do
+    user <- SQLite.field
+    cost <- SQLite.field
+    return $ UserCostRow (user, cost)
+
+repartition :: Frequency -> Text -> Day -> Day -> Query (Map UserId Int)
+repartition frequency search from to =
+  Query (\conn ->
+    M.fromList . fmap (\(UserCostRow r) -> r) <$> SQLite.query
+      conn
+      (SQLite.Query . T.intercalate " " $
+        [ "SELECT user_id, SUM(cost)"
+        , "FROM payment"
+        , "WHERE"
+        ,   "deleted_at IS NULL"
+        ,   "AND frequency = ?"
+        ,   "AND name LIKE ?"
+        ,   "AND date >= ?"
+        ,   "AND date < ?"
+        , "GROUP BY user_id"
+        ])
+      (FrequencyField frequency, "%" <> search <> "%", from, to)
+  )
+
+getPreAndPostPaymentRepartition :: Maybe (Day, Day) -> [User] -> Query (Map UserId Int, Map UserId Int)
+getPreAndPostPaymentRepartition paymentRange users = do
+  case paymentRange of
+    Just (from, to) -> do
+      incomeDefinedForAll <- IncomePersistence.definedForAll (_user_id <$> users)
+      (,)
+        <$> (repartition Punctual "" from (Maybe.fromMaybe (Calendar.addDays 1 to) incomeDefinedForAll))
+        <*> (case incomeDefinedForAll of
+          Just d  -> repartition Punctual "" d (Calendar.addDays 1 to)
+          Nothing -> return M.empty)
+
+    Nothing ->
+      return (M.empty, M.empty)
diff --git a/server/src/Persistence/PaymentCategory.hs b/server/src/Persistence/PaymentCategory.hs
deleted file mode 100644
index 46be7f5..0000000
--- a/server/src/Persistence/PaymentCategory.hs
+++ /dev/null
@@ -1,89 +0,0 @@
-module Persistence.PaymentCategory
-  ( list
-  , listByCategory
-  , save
-  , deleteIfUnused
-  ) where
-
-import qualified Data.Maybe             as Maybe
-import           Data.Text              (Text)
-import qualified Data.Text              as T
-import           Data.Time.Clock        (getCurrentTime)
-import           Database.SQLite.Simple (FromRow (fromRow), Only (Only))
-import qualified Database.SQLite.Simple as SQLite
-
-import           Common.Model           (CategoryId, PaymentCategory (..))
-
-import           Model.Query            (Query (Query))
-
-newtype Row = Row PaymentCategory
-
-instance FromRow Row where
-  fromRow = Row <$> (PaymentCategory <$>
-    SQLite.field <*>
-    SQLite.field <*>
-    SQLite.field <*>
-    SQLite.field <*>
-    SQLite.field)
-
-list :: Query [PaymentCategory]
-list =
-  Query (\conn -> do
-    map (\(Row pc) -> pc) <$>
-      SQLite.query_ conn "SELECT * from payment_category"
-  )
-
-listByCategory :: CategoryId -> Query [PaymentCategory]
-listByCategory cat =
-  Query (\conn -> do
-    map (\(Row pc) -> pc) <$>
-      SQLite.query conn "SELECT * FROM payment_category WHERE category = ?" (Only cat)
-  )
-
-save :: Text -> CategoryId -> Query PaymentCategory
-save newName categoryId =
-  Query (\conn -> do
-    now <- getCurrentTime
-    paymentCategory <- fmap (\(Row pc) -> pc) . Maybe.listToMaybe <$>
-      (SQLite.query
-        conn
-        "SELECT * FROM payment_category WHERE name = ?"
-        (Only formattedNewName))
-    case paymentCategory of
-      Just pc ->
-        do
-          SQLite.execute
-            conn
-            "UPDATE payment_category SET category = ?, edited_at = ? WHERE name = ?"
-            (categoryId, now, formattedNewName)
-          return $ PaymentCategory
-            (_paymentCategory_id pc)
-            formattedNewName
-            categoryId
-            (_paymentCategory_createdAt pc)
-            (Just now)
-      Nothing ->
-        do
-          SQLite.execute
-            conn
-            "INSERT INTO payment_category (name, category, created_at) VALUES (?, ?, ?)"
-            (formattedNewName, categoryId, now)
-          paymentCategoryId <- SQLite.lastInsertRowId conn
-          return $ PaymentCategory
-            paymentCategoryId
-            formattedNewName
-            categoryId
-            now
-            Nothing
-  )
-  where
-    formattedNewName = T.toLower newName
-
-deleteIfUnused :: Text -> Query ()
-deleteIfUnused name =
-  Query (\conn ->
-    SQLite.execute
-      conn
-      "DELETE FROM payment_category WHERE name = lower(?) AND name NOT IN (SELECT DISTINCT lower(name) FROM payment WHERE lower(name) = lower(?) AND deleted_at IS NULL)"
-      (name, name)
-  ) >> return ()
diff --git a/server/src/Persistence/User.hs b/server/src/Persistence/User.hs
index 4ec2dcf..3c3a2b1 100644
--- a/server/src/Persistence/User.hs
+++ b/server/src/Persistence/User.hs
@@ -3,7 +3,7 @@ module Persistence.User
   , get
   ) where
 
-import           Data.Maybe             (listToMaybe)
+import qualified Data.Maybe             as Maybe
 import           Data.Text              (Text)
 import           Database.SQLite.Simple (FromRow (fromRow), Only (Only))
 import qualified Database.SQLite.Simple as SQLite
@@ -32,6 +32,6 @@ list =
 get :: Text -> Query (Maybe User)
 get userEmail =
   Query (\conn -> do
-    fmap (\(Row u) -> u) . listToMaybe <$>
+    fmap (\(Row u) -> u) . Maybe.listToMaybe <$>
       SQLite.query conn "SELECT * FROM user WHERE email = ? LIMIT 1" (Only userEmail)
   )
-- 
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 +++++++++++++++++++++++++++++++--------
 server/src/Persistence/Payment.hs | 12 ++++++--
 2 files changed, 56 insertions(+), 14 deletions(-)

(limited to 'server/src/Persistence')

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
+      ]
diff --git a/server/src/Persistence/Payment.hs b/server/src/Persistence/Payment.hs
index f75925d..953f0ae 100644
--- a/server/src/Persistence/Payment.hs
+++ b/server/src/Persistence/Payment.hs
@@ -163,14 +163,14 @@ listModifiedSince since =
       SQLite.query
         conn
         (SQLite.Query . T.intercalate " " $
-          [ "SELECT *"
+          [ "SELECT " <> fields
           , "FROM payment"
           , "WHERE"
           ,   "created_at >= ?"
           ,   "OR edited_at >= ?"
           ,   "OR deleted_at >= ?"
           ])
-        (Only since)
+        (since, since, since)
   )
 
 
@@ -300,7 +300,13 @@ searchCategory paymentName =
     fmap (\(CategoryIdRow d) -> d) . Maybe.listToMaybe <$>
       SQLite.query
         conn
-        "SELECT category FROM payment WHERE name LIKE ? LIMIT 1"
+        (SQLite.Query . T.intercalate " " $
+          [ "SELECT category"
+          , "FROM payment"
+          , "WHERE deleted_at is NULL AND name LIKE ?"
+          , "ORDER BY edited_at, created_at"
+          , "LIMIT 1"
+          ])
         (Only $ "%" <> paymentName <> "%")
   )
 
-- 
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/Category.hs | 34 +++++++++++++++++++++++----
 server/src/Persistence/Income.hs   | 45 +++++++++++------------------------
 server/src/Persistence/Payment.hs  | 48 ++++++++++----------------------------
 3 files changed, 54 insertions(+), 73 deletions(-)

(limited to 'server/src/Persistence')

diff --git a/server/src/Persistence/Category.hs b/server/src/Persistence/Category.hs
index 00cf0a5..2934b28 100644
--- a/server/src/Persistence/Category.hs
+++ b/server/src/Persistence/Category.hs
@@ -1,5 +1,7 @@
 module Persistence.Category
-  ( list
+  ( count
+  , list
+  , listAll
   , create
   , edit
   , delete
@@ -27,14 +29,37 @@ instance FromRow Row where
     SQLite.field <*>
     SQLite.field)
 
-list :: Query [Category]
-list =
+data CountRow = CountRow Int
+
+instance FromRow CountRow where
+  fromRow = CountRow <$> SQLite.field
+
+count :: Query Int
+count =
+  Query (\conn ->
+    (Maybe.fromMaybe 0 . fmap (\(CountRow n) -> n) . Maybe.listToMaybe) <$>
+      SQLite.query_ conn "SELECT COUNT(*) FROM category WHERE deleted_at IS NULL"
+  )
+
+
+list :: Int -> Int -> Query [Category]
+list page perPage =
+  Query (\conn ->
+    map (\(Row c) -> c) <$>
+      SQLite.query
+          conn
+          "SELECT * FROM category WHERE deleted_at IS NULL ORDER BY edited_at, created_at DESC LIMIT ? OFFSET ?"
+          (perPage, (page - 1) * perPage)
+  )
+
+listAll :: Query [Category]
+listAll =
   Query (\conn ->
     map (\(Row c) -> c) <$>
       SQLite.query_ conn "SELECT * FROM category WHERE deleted_at IS NULL"
   )
 
-create :: Text -> Text -> Query CategoryId
+create :: Text -> Text -> Query ()
 create categoryName categoryColor =
   Query (\conn -> do
     now <- getCurrentTime
@@ -42,7 +67,6 @@ create categoryName categoryColor =
       conn
       "INSERT INTO category (name, color, created_at) VALUES (?, ?, ?)"
       (categoryName, categoryColor, now)
-    SQLite.lastInsertRowId conn
   )
 
 edit :: CategoryId -> Text -> Text -> Query Bool
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 ()
diff --git a/server/src/Persistence/Payment.hs b/server/src/Persistence/Payment.hs
index 953f0ae..da877ff 100644
--- a/server/src/Persistence/Payment.hs
+++ b/server/src/Persistence/Payment.hs
@@ -190,30 +190,17 @@ listActiveMonthlyOrderedByName =
         (Only (FrequencyField Monthly))
   )
 
-create :: UserId -> Text -> Int -> Day -> CategoryId -> Frequency -> Query Payment
+create :: UserId -> Text -> Int -> Day -> CategoryId -> Frequency -> Query ()
 create userId name cost date category frequency =
   Query (\conn -> do
-    time <- getCurrentTime
+    currentTime <- getCurrentTime
     SQLite.execute
       conn
       (SQLite.Query $ T.intercalate " "
         [ "INSERT INTO payment (user_id, name, cost, date, category, frequency, created_at)"
         , "VALUES (?, ?, ?, ?, ?, ?, ?)"
         ])
-      (userId, name, cost, date, category, FrequencyField frequency, time)
-    paymentId <- SQLite.lastInsertRowId conn
-    return $ Payment
-      { _payment_id        = paymentId
-      , _payment_user      = userId
-      , _payment_name      = name
-      , _payment_cost      = cost
-      , _payment_date      = date
-      , _payment_category  = category
-      , _payment_frequency = frequency
-      , _payment_createdAt = time
-      , _payment_editedAt  = Nothing
-      , _payment_deletedAt = Nothing
-      }
+      (userId, name, cost, date, category, FrequencyField frequency, currentTime)
   )
 
 createMany :: [Payment] -> Query ()
@@ -228,17 +215,17 @@ createMany payments =
       (map InsertRow payments)
   )
 
-edit :: UserId -> PaymentId -> Text -> Int -> Day -> CategoryId -> Frequency -> Query (Maybe Payment)
+edit :: UserId -> PaymentId -> Text -> Int -> Day -> CategoryId -> Frequency -> Query Bool
 edit userId paymentId name cost date category frequency =
   Query (\conn -> do
-    mbPayment <- fmap (\(Row p) -> p) . Maybe.listToMaybe <$>
+    payment <- fmap (\(Row p) -> p) . Maybe.listToMaybe <$>
       SQLite.query
         conn
         (SQLite.Query $ "SELECT " <> fields <> " FROM payment WHERE id = ? and user_id = ?")
         (paymentId, userId)
-    case mbPayment of
-      Just payment -> do
-        now <- getCurrentTime
+    if Maybe.isJust payment then
+      do
+        currentTime <- getCurrentTime
         SQLite.execute
           conn
           (SQLite.Query $ T.intercalate " "
@@ -255,7 +242,7 @@ edit userId paymentId name cost date category frequency =
             , "  id = ?"
             , "  AND user_id = ?"
             ])
-          ( now
+          ( currentTime
           , name
           , cost
           , date
@@ -264,20 +251,9 @@ edit userId paymentId name cost date category frequency =
           , paymentId
           , userId
           )
-        return . Just $ Payment
-          { _payment_id        = paymentId
-          , _payment_user      = userId
-          , _payment_name      = name
-          , _payment_cost      = cost
-          , _payment_date      = date
-          , _payment_category  = category
-          , _payment_frequency = frequency
-          , _payment_createdAt = _payment_createdAt payment
-          , _payment_editedAt  = Just now
-          , _payment_deletedAt = Nothing
-          }
-      Nothing ->
-        return Nothing
+        return True
+    else
+      return False
   )
 
 delete :: UserId -> PaymentId -> Query ()
-- 
cgit v1.2.3


From 1dfb85d3fd56d163fc854a8b3cf659d0ac39f639 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sat, 4 Jan 2020 17:25:29 +0100
Subject: Search payments by cost too

---
 server/src/Persistence/Payment.hs | 160 ++++++++++++++++++++++----------------
 server/src/Persistence/Util.hs    |  11 +++
 2 files changed, 105 insertions(+), 66 deletions(-)
 create mode 100644 server/src/Persistence/Util.hs

(limited to 'server/src/Persistence')

diff --git a/server/src/Persistence/Payment.hs b/server/src/Persistence/Payment.hs
index da877ff..a0cd580 100644
--- a/server/src/Persistence/Payment.hs
+++ b/server/src/Persistence/Payment.hs
@@ -3,7 +3,7 @@ module Persistence.Payment
   , find
   , getRange
   , listActivePage
-  , listModifiedSince
+  , listModifiedPunctualSince
   , listActiveMonthlyOrderedByName
   , create
   , createMany
@@ -23,8 +23,8 @@ import           Data.Time.Calendar             (Day)
 import qualified Data.Time.Calendar             as Calendar
 import           Data.Time.Clock                (UTCTime)
 import           Data.Time.Clock                (getCurrentTime)
-import           Database.SQLite.Simple         (FromRow (fromRow), Only (Only),
-                                                 ToRow)
+import           Database.SQLite.Simple         (FromRow (fromRow),
+                                                 NamedParam ((:=)), ToRow)
 import qualified Database.SQLite.Simple         as SQLite
 import           Database.SQLite.Simple.ToField (ToField (toField))
 import           Prelude                        hiding (id, until)
@@ -32,11 +32,12 @@ import           Prelude                        hiding (id, until)
 import           Common.Model                   (CategoryId, Frequency (..),
                                                  Payment (..), PaymentId,
                                                  User (..), UserId)
+import qualified Common.Util.Text               as TextUtil
 
 import           Model.Query                    (Query (Query))
 import           Persistence.Frequency          (FrequencyField (..))
 import qualified Persistence.Income             as IncomePersistence
-
+import qualified Persistence.Util               as PersistenceUtil
 
 
 fields :: Text
@@ -90,27 +91,30 @@ count :: Frequency -> Text -> Query Int
 count frequency search =
   Query (\conn ->
     (\[Count n] -> n) <$>
-      SQLite.query
+      SQLite.queryNamed
         conn
         (SQLite.Query $ T.intercalate " "
           [ "SELECT COUNT(*)"
           , "FROM payment"
           , "WHERE"
           ,   "deleted_at IS NULL"
-          ,   "AND frequency = ?"
-          ,   "AND name LIKE ?"
+          ,   "AND frequency = :frequency"
+          ,   "AND (" <> PersistenceUtil.formatKeyForSearch "name" <> " LIKE :search OR cost LIKE :search)"
           ])
-        (FrequencyField frequency, "%" <> search <> "%")
+        [ ":frequency" := FrequencyField frequency
+        , ":search" := "%" <> TextUtil.formatSearch search <> "%"
+        ]
   )
 
 find :: PaymentId -> Query (Maybe Payment)
 find paymentId =
   Query (\conn -> do
     fmap (\(Row p) -> p) . Maybe.listToMaybe <$>
-      SQLite.query
+      SQLite.queryNamed
         conn
-        (SQLite.Query $ "SELECT " <> fields <> " FROM payment WHERE id = ?")
-        (Only paymentId)
+        (SQLite.Query $ "SELECT " <> fields <> " FROM payment WHERE id = :id")
+        [ "id" := paymentId
+        ]
   )
 
 data RangeRow = RangeRow (Day, Day)
@@ -122,23 +126,24 @@ getRange :: Query (Maybe (Day, Day))
 getRange =
   Query (\conn -> do
     fmap (\(RangeRow (f, t)) -> (f, t)) . Maybe.listToMaybe <$>
-      SQLite.query
+      SQLite.queryNamed
         conn
         (SQLite.Query $ T.intercalate " "
           [ "SELECT MIN(date), MAX(date)"
           , "FROM payment"
           , "WHERE"
-          ,   "frequency = ?"
+          ,   "frequency = :frequency"
           ,   "AND deleted_at IS NULL"
           ])
-        (Only (FrequencyField Punctual))
+        [ ":frequency" := FrequencyField Punctual
+        ]
   )
 
 listActivePage :: Frequency -> Int -> Int -> Text -> Query [Payment]
 listActivePage frequency page perPage search =
   Query (\conn ->
     map (\(Row p) -> p) <$>
-      SQLite.query
+      SQLite.queryNamed
         conn
         (SQLite.Query $ T.intercalate " "
           [ "SELECT"
@@ -146,31 +151,36 @@ listActivePage frequency page perPage search =
           , "FROM payment"
           , "WHERE"
           ,   "deleted_at IS NULL"
-          ,   "AND frequency = ?"
-          ,   "AND name LIKE ?"
+          ,   "AND frequency = :frequency"
+          ,   "AND (" <> PersistenceUtil.formatKeyForSearch "name" <> " LIKE :search OR cost LIKE :search)"
           , "ORDER BY date DESC"
-          , "LIMIT ?"
-          , "OFFSET ?"
+          , "LIMIT :limit"
+          , "OFFSET :offset"
           ]
         )
-        (FrequencyField frequency, "%" <> search <> "%", perPage, (page - 1) * perPage)
+        [ ":frequency" := FrequencyField frequency
+        , ":search" := "%" <> TextUtil.formatSearch search <> "%"
+        , ":limit" := perPage
+        , ":offset" := (page - 1) * perPage
+        ]
   )
 
-listModifiedSince :: UTCTime -> Query [Payment]
-listModifiedSince since =
+listModifiedPunctualSince :: UTCTime -> Query [Payment]
+listModifiedPunctualSince since =
   Query (\conn ->
     map (\(Row i) -> i) <$>
-      SQLite.query
+      SQLite.queryNamed
         conn
         (SQLite.Query . T.intercalate " " $
           [ "SELECT " <> fields
           , "FROM payment"
           , "WHERE"
-          ,   "created_at >= ?"
-          ,   "OR edited_at >= ?"
-          ,   "OR deleted_at >= ?"
+          ,   "frequency = :frequency"
+          ,   "AND (created_at >= :since OR edited_at >= :since OR deleted_at >= :since)"
           ])
-        (since, since, since)
+        [ ":frequency" := FrequencyField Punctual
+        , ":since" := since
+        ]
   )
 
 
@@ -178,29 +188,37 @@ listActiveMonthlyOrderedByName :: Query [Payment]
 listActiveMonthlyOrderedByName =
   Query (\conn -> do
     map (\(Row p) -> p) <$>
-      SQLite.query
+      SQLite.queryNamed
         conn
         (SQLite.Query $ T.intercalate " "
           [ "SELECT"
           , fields
           , "FROM payment"
-          , "WHERE deleted_at IS NULL AND frequency = ?"
+          , "WHERE deleted_at IS NULL AND frequency = :frequency"
           , "ORDER BY name DESC"
           ])
-        (Only (FrequencyField Monthly))
+        [ ":frequency" := FrequencyField Monthly
+        ]
   )
 
 create :: UserId -> Text -> Int -> Day -> CategoryId -> Frequency -> Query ()
 create userId name cost date category frequency =
   Query (\conn -> do
     currentTime <- getCurrentTime
-    SQLite.execute
+    SQLite.executeNamed
       conn
       (SQLite.Query $ T.intercalate " "
         [ "INSERT INTO payment (user_id, name, cost, date, category, frequency, created_at)"
-        , "VALUES (?, ?, ?, ?, ?, ?, ?)"
+        , "VALUES (:userId, :name, :cost, :date, :category, :frequency, :currentTime)"
         ])
-      (userId, name, cost, date, category, FrequencyField frequency, currentTime)
+      [ ":userId" := userId
+      , ":name" := name
+      , ":cost" := cost
+      , ":date" := date
+      , ":category" := category
+      , ":frequency" := FrequencyField frequency
+      , ":currentTime" := currentTime
+      ]
   )
 
 createMany :: [Payment] -> Query ()
@@ -219,38 +237,41 @@ edit :: UserId -> PaymentId -> Text -> Int -> Day -> CategoryId -> Frequency ->
 edit userId paymentId name cost date category frequency =
   Query (\conn -> do
     payment <- fmap (\(Row p) -> p) . Maybe.listToMaybe <$>
-      SQLite.query
+      SQLite.queryNamed
         conn
-        (SQLite.Query $ "SELECT " <> fields <> " FROM payment WHERE id = ? and user_id = ?")
-        (paymentId, userId)
+        (SQLite.Query $
+          "SELECT " <> fields <> " FROM payment WHERE id = :paymentId and user_id = :userId")
+        [ ":paymentId" := paymentId
+        , ":userId" := userId
+        ]
     if Maybe.isJust payment then
       do
         currentTime <- getCurrentTime
-        SQLite.execute
+        SQLite.executeNamed
           conn
           (SQLite.Query $ T.intercalate " "
             [ "UPDATE"
             , "  payment"
             , "SET"
-            , "  edited_at = ?,"
-            , "  name = ?,"
-            , "  cost = ?,"
-            , "  date = ?,"
-            , "  category = ?,"
-            , "  frequency = ?"
+            , "  edited_at = :editedAt,"
+            , "  name = :name,"
+            , "  cost = :cost,"
+            , "  date = :date,"
+            , "  category = :category,"
+            , "  frequency = :frequency"
             , "WHERE"
-            , "  id = ?"
-            , "  AND user_id = ?"
+            , "  id = :id"
+            , "  AND user_id = :userId"
             ])
-          ( currentTime
-          , name
-          , cost
-          , date
-          , category
-          , FrequencyField frequency
-          , paymentId
-          , userId
-          )
+          [ ":editedAt" := currentTime
+          , ":name" := name
+          , ":cost" := cost
+          , ":date" := date
+          , ":category" := category
+          , ":frequency" := FrequencyField frequency
+          , ":id" := paymentId
+          , ":userId" := userId
+          ]
         return True
     else
       return False
@@ -259,10 +280,12 @@ edit userId paymentId name cost date category frequency =
 delete :: UserId -> PaymentId -> Query ()
 delete userId paymentId =
   Query (\conn ->
-    SQLite.execute
+    SQLite.executeNamed
       conn
-      "UPDATE payment SET deleted_at = datetime('now') WHERE id = ? AND user_id = ?"
-      (paymentId, userId)
+      "UPDATE payment SET deleted_at = datetime('now') WHERE id = :id AND user_id = :userId"
+      [ ":id" := paymentId
+      , ":userId" := userId
+      ]
   )
 
 data CategoryIdRow = CategoryIdRow CategoryId
@@ -274,16 +297,17 @@ searchCategory :: Text -> Query (Maybe CategoryId)
 searchCategory paymentName =
   Query (\conn ->
     fmap (\(CategoryIdRow d) -> d) . Maybe.listToMaybe <$>
-      SQLite.query
+      SQLite.queryNamed
         conn
         (SQLite.Query . T.intercalate " " $
           [ "SELECT category"
           , "FROM payment"
-          , "WHERE deleted_at is NULL AND name LIKE ?"
+          , "WHERE deleted_at is NULL AND name LIKE :name"
           , "ORDER BY edited_at, created_at"
           , "LIMIT 1"
           ])
-        (Only $ "%" <> paymentName <> "%")
+        [ ":name" := "%" <> paymentName <> "%"
+        ]
   )
 
 data UserCostRow = UserCostRow (UserId, Int)
@@ -297,20 +321,24 @@ instance FromRow UserCostRow where
 repartition :: Frequency -> Text -> Day -> Day -> Query (Map UserId Int)
 repartition frequency search from to =
   Query (\conn ->
-    M.fromList . fmap (\(UserCostRow r) -> r) <$> SQLite.query
+    M.fromList . fmap (\(UserCostRow r) -> r) <$> SQLite.queryNamed
       conn
       (SQLite.Query . T.intercalate " " $
         [ "SELECT user_id, SUM(cost)"
         , "FROM payment"
         , "WHERE"
         ,   "deleted_at IS NULL"
-        ,   "AND frequency = ?"
-        ,   "AND name LIKE ?"
-        ,   "AND date >= ?"
-        ,   "AND date < ?"
+        ,   "AND frequency = :frequency"
+        ,   "AND (" <> PersistenceUtil.formatKeyForSearch "name" <> " LIKE :search OR cost LIKE :search)"
+        ,   "AND date >= :from"
+        ,   "AND date < :to"
         , "GROUP BY user_id"
         ])
-      (FrequencyField frequency, "%" <> search <> "%", from, to)
+      [ ":frequency" := FrequencyField frequency
+      , ":search" := "%" <> TextUtil.formatSearch search <> "%"
+      , ":from" := from
+      , ":to" := to
+      ]
   )
 
 getPreAndPostPaymentRepartition :: Maybe (Day, Day) -> [User] -> Query (Map UserId Int, Map UserId Int)
diff --git a/server/src/Persistence/Util.hs b/server/src/Persistence/Util.hs
new file mode 100644
index 0000000..b7496c6
--- /dev/null
+++ b/server/src/Persistence/Util.hs
@@ -0,0 +1,11 @@
+module Persistence.Util
+  ( formatKeyForSearch
+  ) where
+
+import           Data.Text (Text)
+
+formatKeyForSearch :: Text -> Text
+formatKeyForSearch key =
+  "replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower("
+  <> key
+  <> "), 'à', 'a'), 'â', 'a'), 'ç', 'c'), 'è', 'e'), 'é', 'e'), 'ê', 'e'), 'ë', 'e'), 'î', 'i'), 'ï', 'i'), 'ô', 'o'), 'ù', 'u'), 'û', 'u'), 'ü', 'u')"
-- 
cgit v1.2.3


From da2a0c13aa89705c65fdb9df2f496fb4eea29654 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sat, 4 Jan 2020 19:22:45 +0100
Subject: Allow to remove only unused categories

---
 server/src/Persistence/Category.hs | 64 +++++++++++++++++++++++++-------------
 server/src/Persistence/Payment.hs  | 14 +++++++++
 2 files changed, 56 insertions(+), 22 deletions(-)

(limited to 'server/src/Persistence')

diff --git a/server/src/Persistence/Category.hs b/server/src/Persistence/Category.hs
index 2934b28..b0a6fca 100644
--- a/server/src/Persistence/Category.hs
+++ b/server/src/Persistence/Category.hs
@@ -10,7 +10,7 @@ module Persistence.Category
 import qualified Data.Maybe             as Maybe
 import           Data.Text              (Text)
 import           Data.Time.Clock        (getCurrentTime)
-import           Database.SQLite.Simple (FromRow (fromRow), Only (Only))
+import           Database.SQLite.Simple (FromRow (fromRow), NamedParam ((:=)))
 import qualified Database.SQLite.Simple as SQLite
 import           Prelude                hiding (id)
 
@@ -46,10 +46,12 @@ list :: Int -> Int -> Query [Category]
 list page perPage =
   Query (\conn ->
     map (\(Row c) -> c) <$>
-      SQLite.query
+      SQLite.queryNamed
           conn
-          "SELECT * FROM category WHERE deleted_at IS NULL ORDER BY edited_at, created_at DESC LIMIT ? OFFSET ?"
-          (perPage, (page - 1) * perPage)
+          "SELECT * FROM category WHERE deleted_at IS NULL ORDER BY name LIMIT :limit OFFSET :offset"
+          [ ":limit" := perPage
+          , ":offset" := (page - 1) * perPage
+          ]
   )
 
 listAll :: Query [Category]
@@ -60,43 +62,61 @@ listAll =
   )
 
 create :: Text -> Text -> Query ()
-create categoryName categoryColor =
+create name color =
   Query (\conn -> do
-    now <- getCurrentTime
-    SQLite.execute
+    currentTime <- getCurrentTime
+    SQLite.executeNamed
       conn
-      "INSERT INTO category (name, color, created_at) VALUES (?, ?, ?)"
-      (categoryName, categoryColor, now)
+      "INSERT INTO category (name, color, created_at) VALUES (:name, :color, :created_at)"
+      [ ":name" := name
+      , ":color" := color
+      , ":created_at" := currentTime
+      ]
   )
 
 edit :: CategoryId -> Text -> Text -> Query Bool
-edit categoryId categoryName categoryColor =
+edit id name color =
   Query (\conn -> do
     mbCategory <- fmap (\(Row c) -> c) . Maybe.listToMaybe <$>
-      (SQLite.query conn "SELECT * FROM category WHERE id = ?" (Only categoryId))
+      (SQLite.queryNamed conn "SELECT * FROM category WHERE id = :id" [ ":id" := id ])
     if Maybe.isJust mbCategory
       then do
-        now <- getCurrentTime
-        SQLite.execute
+        currentTime <- getCurrentTime
+        SQLite.executeNamed
           conn
-          "UPDATE category SET edited_at = ?, name = ?, color = ? WHERE id = ?"
-          (now, categoryName, categoryColor, categoryId)
+          "UPDATE category SET edited_at = :editedAt, name = :name, color = :color WHERE id = :id"
+          [ ":editedAt" := currentTime
+          , ":name" := name
+          , ":color" := color
+          , ":id" := id
+          ]
         return True
       else
         return False
   )
 
+data BoolRow = BoolRow Int
+
+instance FromRow BoolRow where
+  fromRow = BoolRow <$> SQLite.field
+
 delete :: CategoryId -> Query Bool
-delete categoryId =
+delete id =
   Query (\conn -> do
-    mbCategory <- fmap (\(Row c) -> c) . Maybe.listToMaybe <$>
-      (SQLite.query conn "SELECT * FROM category WHERE id = ?" (Only categoryId))
-    if Maybe.isJust mbCategory
+    mbPayment <- (fmap (\(BoolRow b) -> b) . Maybe.listToMaybe) <$>
+      (SQLite.queryNamed
+        conn
+        "SELECT true FROM payment WHERE category = :id AND deleted_at IS NULL"
+        [ ":id" := id ])
+    if Maybe.isNothing mbPayment
       then do
-        now <- getCurrentTime
-        SQLite.execute
+        currentTime <- getCurrentTime
+        SQLite.executeNamed
           conn
-          "UPDATE category SET deleted_at = ? WHERE id = ?" (now, categoryId)
+          "UPDATE category SET deleted_at = :deletedAt WHERE id = :id AND deleted_at IS NULL"
+          [ ":deletedAt" := currentTime
+          , ":id" := id
+          ]
         return True
       else
         return False
diff --git a/server/src/Persistence/Payment.hs b/server/src/Persistence/Payment.hs
index a0cd580..b3eb141 100644
--- a/server/src/Persistence/Payment.hs
+++ b/server/src/Persistence/Payment.hs
@@ -12,6 +12,7 @@ module Persistence.Payment
   , searchCategory
   , repartition
   , getPreAndPostPaymentRepartition
+  , usedCategories
   ) where
 
 import           Data.Map                       (Map)
@@ -310,6 +311,19 @@ searchCategory paymentName =
         ]
   )
 
+usedCategories :: Query [CategoryId]
+usedCategories =
+  Query (\conn -> do
+    map (\(CategoryIdRow p) -> p) <$>
+      SQLite.query_
+        conn
+        (SQLite.Query $ T.intercalate " "
+          [ "SELECT DISTINCT category"
+          , "FROM payment"
+          , "WHERE deleted_at IS NULL"
+          ])
+  )
+
 data UserCostRow = UserCostRow (UserId, Int)
 
 instance FromRow UserCostRow where
-- 
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 ++++++++++++++++++++++++----------------
 server/src/Persistence/User.hs   |  9 ++++---
 2 files changed, 40 insertions(+), 25 deletions(-)

(limited to 'server/src/Persistence')

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)
diff --git a/server/src/Persistence/User.hs b/server/src/Persistence/User.hs
index 3c3a2b1..89eb57d 100644
--- a/server/src/Persistence/User.hs
+++ b/server/src/Persistence/User.hs
@@ -5,7 +5,7 @@ module Persistence.User
 
 import qualified Data.Maybe             as Maybe
 import           Data.Text              (Text)
-import           Database.SQLite.Simple (FromRow (fromRow), Only (Only))
+import           Database.SQLite.Simple (FromRow (fromRow), NamedParam ((:=)))
 import qualified Database.SQLite.Simple as SQLite
 import           Prelude                hiding (id)
 
@@ -30,8 +30,11 @@ list =
   )
 
 get :: Text -> Query (Maybe User)
-get userEmail =
+get email =
   Query (\conn -> do
     fmap (\(Row u) -> u) . Maybe.listToMaybe <$>
-      SQLite.query conn "SELECT * FROM user WHERE email = ? LIMIT 1" (Only userEmail)
+      SQLite.queryNamed
+        conn
+        "SELECT * FROM user WHERE email = :email LIMIT 1"
+        [ ":email" := email ]
   )
-- 
cgit v1.2.3


From af8353c6164aaaaa836bfed181f883ac86bb76a5 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 19 Jan 2020 14:03:31 +0100
Subject: Sign in with email and password

---
 server/src/Persistence/User.hs | 48 +++++++++++++++++++++++++++++++++++++-----
 1 file changed, 43 insertions(+), 5 deletions(-)

(limited to 'server/src/Persistence')

diff --git a/server/src/Persistence/User.hs b/server/src/Persistence/User.hs
index 89eb57d..12145ac 100644
--- a/server/src/Persistence/User.hs
+++ b/server/src/Persistence/User.hs
@@ -1,17 +1,21 @@
 module Persistence.User
   ( list
   , get
+  , checkPassword
+  , createSignInToken
   ) where
 
 import qualified Data.Maybe             as Maybe
 import           Data.Text              (Text)
 import           Database.SQLite.Simple (FromRow (fromRow), NamedParam ((:=)))
 import qualified Database.SQLite.Simple as SQLite
-import           Prelude                hiding (id)
 
-import           Common.Model           (User (..))
+import           Common.Model           (Email (..), Password (..), User (..))
 
+import           Model.HashedPassword   (HashedPassword (..))
+import qualified Model.HashedPassword   as HashedPassword
 import           Model.Query            (Query (Query))
+import qualified Model.UUID             as UUID
 
 newtype Row = Row User
 
@@ -26,15 +30,49 @@ list :: Query [User]
 list =
   Query (\conn -> do
     map (\(Row u) -> u) <$>
-      SQLite.query_ conn "SELECT * from user ORDER BY creation DESC"
+      SQLite.query_ conn "SELECT id, creation, email, name from user ORDER BY creation DESC"
   )
 
 get :: Text -> Query (Maybe User)
-get email =
+get token =
   Query (\conn -> do
     fmap (\(Row u) -> u) . Maybe.listToMaybe <$>
       SQLite.queryNamed
         conn
-        "SELECT * FROM user WHERE email = :email LIMIT 1"
+        "SELECT id, creation, email, name FROM user WHERE sign_in_token = :sign_in_token LIMIT 1"
+        [ ":sign_in_token" := token ]
+  )
+
+data HashedPasswordRow = HashedPasswordRow HashedPassword
+
+instance FromRow HashedPasswordRow where
+  fromRow = HashedPasswordRow <$> (HashedPassword <$> SQLite.field)
+
+checkPassword :: Email -> Password -> Query Bool
+checkPassword (Email email) password =
+  Query (\conn -> do
+    hashedPassword <- fmap (\(HashedPasswordRow p) -> p) . Maybe.listToMaybe <$>
+      SQLite.queryNamed
+        conn
+        "SELECT password FROM user WHERE email = :email LIMIT 1"
         [ ":email" := email ]
+    case hashedPassword of
+      Just h ->
+        return (HashedPassword.check password h)
+
+      Nothing ->
+        return False
+  )
+
+createSignInToken :: Email -> Query Text
+createSignInToken (Email email) =
+  Query (\conn -> do
+    token <- UUID.generateUUID
+    SQLite.executeNamed
+      conn
+      "UPDATE user SET sign_in_token = :sign_in_token WHERE email = :email"
+      [ ":sign_in_token" := token
+      , ":email" := email
+      ]
+    return token
   )
-- 
cgit v1.2.3


From 47c2a4d6b68c54eed5f7b45671b1ccaf8c0db200 Mon Sep 17 00:00:00 2001
From: Joris
Date: Mon, 20 Jan 2020 19:47:23 +0100
Subject: Show payment stats

---
 server/src/Persistence/Payment.hs | 19 +++++++++++++++++++
 1 file changed, 19 insertions(+)

(limited to 'server/src/Persistence')

diff --git a/server/src/Persistence/Payment.hs b/server/src/Persistence/Payment.hs
index b3eb141..573d57f 100644
--- a/server/src/Persistence/Payment.hs
+++ b/server/src/Persistence/Payment.hs
@@ -2,6 +2,7 @@ module Persistence.Payment
   ( count
   , find
   , getRange
+  , listAllPunctual
   , listActivePage
   , listModifiedPunctualSince
   , listActiveMonthlyOrderedByName
@@ -140,6 +141,24 @@ getRange =
         ]
   )
 
+listAllPunctual :: Query [Payment]
+listAllPunctual =
+  Query (\conn ->
+    map (\(Row p) -> p) <$>
+      SQLite.queryNamed
+        conn
+        (SQLite.Query $ T.intercalate " "
+          [ "SELECT"
+          , fields
+          , "FROM payment"
+          , "WHERE deleted_at IS NULL AND frequency = :frequency"
+          , "ORDER BY date"
+          ])
+        [ ":frequency" := FrequencyField Punctual
+        ]
+  )
+
+
 listActivePage :: Frequency -> Int -> Int -> Text -> Query [Payment]
 listActivePage frequency page perPage search =
   Query (\conn ->
-- 
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')

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