package com.example.firstapp.database.dao
|
|
import androidx.room.Dao
|
import androidx.room.Delete
|
import androidx.room.Insert
|
import androidx.room.Query
|
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 {
|
|
@Insert
|
fun insert(code: Code): Long
|
|
@Update
|
fun update(code: Code)
|
|
@Delete
|
fun delete(msg: Msg): Completable
|
|
@Query("DELETE FROM Code where id=:id")
|
fun delete(id: Long)
|
|
|
@Query("SELECT * FROM Code WHERE id = :id LIMIT 1")
|
fun getCodeById(id: Long): Code?
|
|
@Query("SELECT * FROM Code")
|
fun getAllCodes(): List<Code>
|
|
@Query("SELECT * FROM Code WHERE type = :type")
|
fun getCodesByType(type: String): List<Code>
|
|
|
|
@Query("DELETE FROM Code WHERE id = :id")
|
fun deleteCodeById(id: Long)
|
|
@Query("SELECT * FROM Code order by time desc")
|
abstract fun getAllCodesDesc(): List<Code>
|
|
@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 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>>
|
}
|
