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/server.cabal | 1 + server/src/Job/WeeklyReport.hs | 2 +- server/src/Persistence/Payment.hs | 160 ++++++++++++++++++++++---------------- server/src/Persistence/Util.hs | 11 +++ 4 files changed, 107 insertions(+), 67 deletions(-) create mode 100644 server/src/Persistence/Util.hs 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')" -- cgit v1.2.3