Search payments by cost too

4 files changed, 107 insertions, 67 deletions

diff --git a/server/server.cabal b/server/server.cabal
index cc6172d..d38949d 100644
--- a/server/server.cabal
+++ b/server/server.cabal
@@ -113,6 +113,7 @@ Executable server
     Persistence.Income
     Persistence.Payment
     Persistence.User
+    Persistence.Util
     Resource
     Secure
     SendMail
diff --git a/server/src/Job/WeeklyReport.hs b/server/src/Job/WeeklyReport.hs
index 16be396..ff80ddf 100644
--- a/server/src/Job/WeeklyReport.hs
+++ b/server/src/Job/WeeklyReport.hs
@@ -35,7 +35,7 @@ weeklyReport conf mbLastExecution = do
 
             _ ->
               return M.empty
-        weekPayments <- PaymentPersistence.listModifiedSince lastExecution
+        weekPayments <- PaymentPersistence.listModifiedPunctualSince lastExecution
         weekIncomes <- IncomePersistence.listModifiedSince lastExecution
         (preIncomeRepartition, postIncomeRepartition) <-
           PaymentPersistence.getPreAndPostPaymentRepartition paymentRange users
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')"