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/Payment.hs | 169 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 169 insertions(+)
 create mode 100644 server/src/Persistence/Payment.hs

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

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
+  )
-- 
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/Payment.hs')

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 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/Payment.hs | 34 +++++++++++-----------------------
 1 file changed, 11 insertions(+), 23 deletions(-)

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

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)
   )
-- 
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/Payment.hs')

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 +++---
 1 file changed, 3 insertions(+), 3 deletions(-)

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

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
-- 
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/Payment.hs')

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/Payment.hs | 25 ++++++++++++++++++++++++-
 1 file changed, 24 insertions(+), 1 deletion(-)

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

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/Payment.hs')

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/Payment.hs | 214 ++++++++++++++++++++++++++++----------
 1 file changed, 161 insertions(+), 53 deletions(-)

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

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)
-- 
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/Payment.hs | 12 +++++++++---
 1 file changed, 9 insertions(+), 3 deletions(-)

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

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/Payment.hs | 48 ++++++++++-----------------------------
 1 file changed, 12 insertions(+), 36 deletions(-)

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

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 ++++++++++++++++++++++----------------
 1 file changed, 94 insertions(+), 66 deletions(-)

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

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)
-- 
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/Payment.hs | 14 ++++++++++++++
 1 file changed, 14 insertions(+)

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

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 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/Payment.hs')

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