shorts-api/app/Controllers/Http/OcrDevicesController.ts

import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'
import PaginationService from 'App/Services/PaginationService'
import { schema } from '@ioc:Adonis/Core/Validator'
import OcrDevice from 'App/Models/OcrDevice'
import OcrRecord from 'App/Models/OcrRecord'
import OcrChannel from 'App/Models/OcrChannel'
import { DateTime } from 'luxon'
import * as console from 'node:console'
import Database from '@ioc:Adonis/Lucid/Database'

export default class OcrDevicesController {
    private paginationService = new PaginationService(OcrDevice)

    public async index({ request, auth }: HttpContextContract) {
        const user = auth.user
        const isApiUser = user?.$attributes?.role === 'api'

        const requestData = request.all()
        if (isApiUser) {
            requestData.channel = user.username
        }
        return await this.paginationService.paginate(request.all())
    }

    public async store({ request, bouncer }: HttpContextContract) {
        // await bouncer.authorize('admin')
        const data = await request.validate({
            schema: schema.create({
                id: schema.string(),
                platform: schema.string(),
                channel: schema.string(),
                deviceInfo: schema.string.optional(),
                total: schema.number(),
                scanned: schema.number(),
                ipAddress: schema.string.optional()
            })
        })
        const clientIp = request.ip()
        if (!data.ipAddress) {
            data.ipAddress = clientIp
        }
        const device = await OcrDevice.findBy('id', data.id)
        let ocrDevice: OcrDevice
        if (device) {
            device.merge(data)
            ocrDevice = await device.save()
        } else {
            ocrDevice = await OcrDevice.create(data)
        }
        // 更新渠道统计数据
        await this.updateNums(data.channel)
        return ocrDevice
    }

    private async updateNums(channel: string) {
        if (channel) {
            const ocrChannel = await OcrChannel.findBy('name', channel)
            if (ocrChannel) {
                const deviceCount = await OcrDevice.query()
                    .where('channel', channel)
                    .count('* as total')
                const recordCount = await OcrRecord.query()
                    .where('channel', channel)
                    .count('* as total')
                const scanSum = await OcrDevice.query()
                    .where('channel', channel)
                    .sum('scanned as total')

                ocrChannel.deviceNum = Number(deviceCount[0].$extras.total || 0)
                ocrChannel.recordNum = Number(recordCount[0].$extras.total || 0)
                ocrChannel.scanNum = Number(scanSum[0].$extras.total || 0)

                await ocrChannel.save()
            }
        }
    }

    public async show({ params, bouncer }: HttpContextContract) {
        await bouncer.authorize('admin')
        return await OcrDevice.findOrFail(params.id)
    }

    public async plusTotal({ request, response }: HttpContextContract) {
        try {
            const device = await OcrDevice.findBy('id', request.param('id'))
            if (!device) {
                return response.notFound({ message: `未找到ID为 ${request.param('id')} 的OCR设备` })
            }
            device.total += 1
            await device.save()
            return response.ok(device)
        } catch (error) {
            return response.internalServerError({
                message: '更新设备记录数量时发生错误',
                error: error.message
            })
        }
    }

    public async plusScanned({ request, response }: HttpContextContract) {
        const scanCount = Number(request.param('scanCount'))
        if (isNaN(scanCount)) {
            return response.badRequest({ message: 'scanCount 参数必须是有效数字' })
        }

        try {
            const device = await OcrDevice.findBy('id', request.param('id'))
            if (!device) {
                return response.notFound({
                    message: `未找到 ID 为 ${request.param('id')} 的 OCR 设备`
                })
            }

            device.scanned += scanCount
            await device.save()

            return response.ok(device)
        } catch (error) {
            return response.internalServerError({
                message: '更新设备扫描数量时发生错误',
                error: error.message
            })
        }
    }

    public async getStatistics({ request, response, auth }: HttpContextContract) {
        try {
            const user = auth.user
            const isApiUser = user?.$attributes?.role === 'api'

            // 获取开始日期和结束日期，默认为不包括今天的最近七天
            let startDate = request.input(
                'startDate',
                DateTime.now().minus({ days: 7 }).startOf('day').toFormat('yyyy-MM-dd HH:mm:ss')
            )
            let endDate = request.input(
                'endDate',
                DateTime.now().minus({ days: 1 }).endOf('day').toFormat('yyyy-MM-dd HH:mm:ss')
            )

            // 处理可能的不同日期格式
            let startDateTime: DateTime
            let endDateTime: DateTime

            // 尝试解析开始日期
            if (startDate.includes(' ')) {
                // 如果包含空格，假设是完整的日期时间格式
                startDateTime = DateTime.fromFormat(startDate, 'yyyy-MM-dd HH:mm:ss')
                if (!startDateTime.isValid) {
                    // 尝试其他可能的格式
                    startDateTime = DateTime.fromISO(startDate)
                }
            } else {
                // 只有日期部分
                startDateTime = DateTime.fromFormat(startDate, 'yyyy-MM-dd').startOf('day')
                startDate = startDateTime.toFormat('yyyy-MM-dd HH:mm:ss')
            }

            // 尝试解析结束日期
            if (endDate.includes(' ')) {
                // 如果包含空格，假设是完整的日期时间格式
                endDateTime = DateTime.fromFormat(endDate, 'yyyy-MM-dd HH:mm:ss')
                if (!endDateTime.isValid) {
                    // 尝试其他可能的格式
                    endDateTime = DateTime.fromISO(endDate)
                }
            } else {
                // 只有日期部分，设置为当天结束
                endDateTime = DateTime.fromFormat(endDate, 'yyyy-MM-dd').endOf('day')
                endDate = endDateTime.toFormat('yyyy-MM-dd HH:mm:ss')
            }

            // 验证日期是否有效
            if (!startDateTime.isValid || !endDateTime.isValid) {
                return response.badRequest({
                    message: '无效的日期格式',
                    details: {
                        startDate: startDateTime.isValid ? '有效' : '无效',
                        endDate: endDateTime.isValid ? '有效' : '无效'
                    }
                })
            }

            // 计算日期之间的天数差
            const diffInDays = Math.max(1, Math.ceil(endDateTime.diff(startDateTime, 'days').days))

            console.log('日期调试信息:', {
                startDate,
                endDate,
                startDateTime: startDateTime.toISO(),
                endDateTime: endDateTime.toISO(),
                diffInDays
            })

            // 生成日期数组
            const dates: string[] = []
            for (let i = 0; i < diffInDays; i++) {
                const date = startDateTime.plus({ days: i }).toFormat('yyyy-MM-dd')
                dates.push(date)
            }

            // 准备查询条件
            let channelCondition = {}
            if (isApiUser) {
                channelCondition = { channel: user.username }
            } else if (request.input('channel')) {
                channelCondition = { channel: request.input('channel') }
            }

            // 使用SQL直接获取每日设备统计数据
            const deviceStatsQuery = Database.from('ocr_devices')
                .select(
                    Database.raw("DATE_FORMAT(created_at, '%Y-%m-%d') as date"),
                    Database.raw('COUNT(id) as device_count'),
                    Database.raw('SUM(scanned) as scanned_count')
                )
                .whereBetween('created_at', [startDate, endDate])
                .groupBy('date')
                .orderBy('date', 'asc')

            // 添加渠道条件
            if (Object.keys(channelCondition).length > 0) {
                deviceStatsQuery.where(channelCondition)
            }

            const deviceStats = await deviceStatsQuery

            // 使用SQL直接获取每日记录统计数据
            const recordStatsQuery = Database.from('ocr_records')
                .select(
                    Database.raw("DATE_FORMAT(created_at, '%Y-%m-%d') as date"),
                    Database.raw('COUNT(id) as record_count')
                )
                .whereBetween('created_at', [startDate, endDate])
                .groupBy('date')
                .orderBy('date', 'asc')

            // 添加渠道条件
            if (Object.keys(channelCondition).length > 0) {
                recordStatsQuery.where(channelCondition)
            }

            const recordStats = await recordStatsQuery

            // 合并结果
            const dailyStats = {}

            // 初始化所有日期的统计数据
            dates.forEach((date) => {
                dailyStats[date] = {
                    total: 0,
                    scanned: 0,
                    deviceCount: 0
                }
            })

            // 填充设备统计数据
            deviceStats.forEach((stat) => {
                if (dailyStats[stat.date]) {
                    dailyStats[stat.date].scanned = Number(stat.scanned_count) || 0
                    dailyStats[stat.date].deviceCount = Number(stat.device_count) || 0
                }
            })

            // 填充记录统计数据
            recordStats.forEach((stat) => {
                if (dailyStats[stat.date]) {
                    dailyStats[stat.date].total = Number(stat.record_count) || 0
                }
            })

            // 转换为前端需要的数组格式
            const totals = dates.map((date) => dailyStats[date].total)
            const scanned = dates.map((date) => dailyStats[date].scanned)
            const deviceCounts = dates.map((date) => dailyStats[date].deviceCount)

            return response.ok({
                dates,
                total: totals,
                scanned: scanned,
                deviceCount: deviceCounts
            })
        } catch (error) {
            return response.internalServerError({
                message: '获取设备统计数据时发生错误',
                error: error.message
            })
        }
    }

    public async getTodayStatistics({ request, response, auth }: HttpContextContract) {
        try {
            const user = auth.user
            const isApiUser = user?.$attributes?.role === 'api'
            const deviceQuery = OcrDevice.query()

            // 如果是API用户，强制使用其username作为channel
            if (isApiUser) {
                deviceQuery.where('channel', user.username)
            } else {
                // 如果不是API用户，则使用请求中的channel参数
                const channel = request.input('channel')
                if (channel) {
                    deviceQuery.where('channel', channel)
                }
            }

            // 获取指定日期的数据，默认为今天
            const targetDate = request.input('date', DateTime.now().toFormat('yyyy-MM-dd'))
            const dayStart = DateTime.fromFormat(targetDate, 'yyyy-MM-dd').startOf('day').toSQL()
            const dayEnd = DateTime.fromFormat(targetDate, 'yyyy-MM-dd').endOf('day').toSQL()

            // 获取设备数据
            const deviceData = await deviceQuery
                .where('createdAt', '>=', dayStart)
                .where('createdAt', '<=', dayEnd)
                .select('scanned')

            // 获取OcrRecord数据
            const recordCount = await Database.from('ocr_records')
                .where('created_at', '>=', dayStart)
                .where('created_at', '<=', dayEnd)
                .where(function (query) {
                    if (isApiUser) {
                        query.where('channel', user.username)
                    } else {
                        const channel = request.input('channel')
                        if (channel) {
                            query.where('channel', channel)
                        }
                    }
                })
                .count('* as total')

            // 计算统计数据
            const scanned = deviceData.reduce((acc, item) => acc + item.scanned, 0)
            const total = Number(recordCount[0].total) || 0
            const deviceCount = deviceData.length

            return response.ok({
                date: targetDate,
                total,
                scanned,
                deviceCount
            })
        } catch (error) {
            return response.internalServerError({
                message: '获取统计数据时发生错误',
                error: error.message
            })
        }
    }
}