aboutsummaryrefslogtreecommitdiff
path: root/server/src
diff options
context:
space:
mode:
Diffstat (limited to 'server/src')
-rw-r--r--server/src/Job/WeeklyReport.hs2
-rw-r--r--server/src/Persistence/Payment.hs160
-rw-r--r--server/src/Persistence/Util.hs11
3 files changed, 106 insertions, 67 deletions
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')"
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-23 01:28:54 +0000