From 2933603ee0940395a80a91bb23e50cbe8ba9baa5 Mon Sep 17 00:00:00 2001
From: cloudroam <cloudroam>
Date: 星期三, 05 三月 2025 10:03:16 +0800
Subject: [PATCH] fix: 2
---
app/src/main/java/com/example/firstapp/database/dao/CodeDao.kt | 66 ++++++++++++++++++++++++++++++++
1 files changed, 65 insertions(+), 1 deletions(-)
diff --git a/app/src/main/java/com/example/firstapp/database/dao/CodeDao.kt b/app/src/main/java/com/example/firstapp/database/dao/CodeDao.kt
index a3c0bca..3f5d7ae 100644
--- a/app/src/main/java/com/example/firstapp/database/dao/CodeDao.kt
+++ b/app/src/main/java/com/example/firstapp/database/dao/CodeDao.kt
@@ -7,7 +7,10 @@
import androidx.room.Update
import com.example.firstapp.database.entity.Code
import com.example.firstapp.database.entity.Msg
+import com.example.firstapp.model.CourierStat
+import com.example.firstapp.model.DailyStat
import io.reactivex.Completable
+import kotlinx.coroutines.flow.Flow
@Dao
interface CodeDao {
@@ -45,10 +48,71 @@
@Query("""
SELECT * FROM Code
WHERE type LIKE '%' || :keyword || '%'
+ AND pickup = '0'
ORDER BY time DESC
""")
fun getByKeyword(keyword: String): List<Code>
- @Query("SELECT * FROM Code WHERE type = :content and code= :code and overtime = :dateString LIMIT 1")
+ @Query("SELECT * FROM Code WHERE type = :content and code= :code and createtime = :dateString LIMIT 1")
fun queryByTypeAndCodeAndDate(content: String, code: String, dateString: String): Code
+
+
+ @Query("update Code set pickup = '1' , pickuptime = CURRENT_TIMESTAMP where id=:id")
+ fun pickup(id: Long)
+
+ //查询当天包裹信息
+ @Query("SELECT * FROM code WHERE date(createtime) = date(:date/1000, 'unixepoch', 'localtime') ORDER BY createtime DESC")
+ fun getNewPackagesByDay(date: Long): List<Code>
+
+ @Query("""
+ SELECT * FROM code
+ WHERE substr(createtime, 1, 10) =
+ date(:date/1000, 'unixepoch', 'localtime')
+ ORDER BY createtime DESC
+ """)
+ fun getPackagesByDay(date: Long): Flow<List<Code>>
+
+ @Query("""
+ SELECT type as courierName, COUNT(*) as count
+ FROM code
+ WHERE strftime('%Y-%W', substr(createtime, 1, 10)) =
+ strftime('%Y-%W', datetime(:date/1000, 'unixepoch', 'localtime'))
+ GROUP BY type
+ ORDER BY count DESC
+ """)
+ fun getCourierStatsByWeek(date: Long): Flow<List<CourierStat>>
+
+ @Query("""
+ WITH RECURSIVE weeks(week_start, week_end) AS (
+ -- 从选定的日期开始计算周
+ SELECT
+ date(datetime(:date/1000, 'unixepoch', 'localtime'), 'weekday 1', '-28 days') as week_start,
+ date(datetime(:date/1000, 'unixepoch', 'localtime'), 'weekday 0', '-28 days') as week_end
+ UNION ALL
+ SELECT
+ date(week_start, '+7 days'),
+ date(week_end, '+7 days')
+ FROM weeks
+ WHERE date(week_start) <= date(datetime(:date/1000, 'unixepoch', 'localtime'))
+ LIMIT 5
+ )
+ SELECT
+ CAST(strftime('%W', week_start) AS INTEGER) as date,
+ COUNT(code.id) as count,
+ week_start as week_start
+ FROM weeks
+ LEFT JOIN code ON datetime(code.createtime) BETWEEN datetime(weeks.week_start)
+ AND datetime(weeks.week_end, '+23 hours', '+59 minutes', '+59 seconds')
+ GROUP BY weeks.week_start
+ ORDER BY weeks.week_start ASC
+ """)
+ fun getDailyStatsByWeek(date: Long): Flow<List<DailyStat>>
+
+ @Query("""
+ SELECT * FROM code
+ WHERE substr(createtime, 1, 10) =
+ date(:date/1000, 'unixepoch', 'localtime')
+ ORDER BY createtime DESC
+ """)
+ fun getPackagesByWeek(date: Long): Flow<List<Code>>
}
--
Gitblit v1.9.3