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

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

export default class OcrChannelController {
    private paginationService = new PaginationService(OcrChannel)

    public async index({ request, bouncer }: HttpContextContract) {
        return await this.paginationService.paginate(request.all())
    }

    public async store({ request, bouncer }: HttpContextContract) {
        await request.validate({
            schema: schema.create({
                name: schema.string.optional(),
                deviceNum: schema.number(),
                recordNum: schema.number(),
                scanNum: schema.number()
            })
        })
        return await OcrChannel.create(request.all())
    }

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

    public async update({ params, request, bouncer }: HttpContextContract) {
        await bouncer.authorize('admin')
        const ocrChannel = await OcrChannel.findOrFail(params.id)
        const data = await request.validate({
            schema: schema.create({
                name: schema.string.optional(),
                deviceNum: schema.number.optional(),
                recordNum: schema.number.optional(),
                scanNum: schema.number.optional()
            })
        })
        return await ocrChannel.merge(data).save()
    }

    public async findApiChannel({ auth, response }: HttpContextContract) {
        if (!auth.user) {
            return response.unauthorized({ message: 'unauthorized' })
        }
        return await OcrChannel.findBy('name', auth.user.username)
    }

    public async plusDeviceNum({ request, response }: HttpContextContract) {
        try {
            const ocrChannel = await OcrChannel.findBy('id', request.param('id'))
            if (!ocrChannel) {
                return response.notFound({ message: `未找到ID为 ${request.param('id')} 的OCR渠道` })
            }

            ocrChannel.deviceNum += 1
            await ocrChannel.save()
            return ocrChannel
        } catch (error) {
            return response.internalServerError({
                message: '更新设备数量时发生错误',
                error: error.message
            })
        }
    }

    public async plusRecordNum({ request, response }: HttpContextContract) {
        try {
            const ocrChannel = await OcrChannel.findBy('id', request.param('id'))
            if (!ocrChannel) {
                return response.notFound({ message: `未找到ID为 ${request.param('id')} 的OCR渠道` })
            }
            ocrChannel.recordNum += 1
            await ocrChannel.save()
            return ocrChannel
        } catch (error) {
            return response.internalServerError({
                message: '更新记录数量时发生错误',
                error: error.message
            })
        }
    }

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

        try {
            const ocrChannel = await OcrChannel.findBy('id', request.param('id'))
            if (!ocrChannel) {
                return response.notFound({
                    message: `未找到 ID 为 ${request.param('id')} 的 OCR 渠道`
                })
            }

            ocrChannel.scanNum = (Number(ocrChannel.scanNum) || 0) + scanCount
            await ocrChannel.save()

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

    public async getStatistics({ request, response }: HttpContextContract) {
        try {
            const name = request.input('name')
            let query = Database.from('ocr_channels')

            if (name) {
                query = query.where('name', name)
            }

            const sevenDaysAgo = DateTime.now().minus({ days: 7 }).startOf('day').toSQL()
            const data = await query
                .where('created_at', '>=', sevenDaysAgo)
                .orderBy('created_at', 'asc')
                .select('created_at', 'device_num as deviceNum', 'record_num as recordNum', 'scan_num as scanNum')

            const result = {
                dates: data.map((item) => DateTime.fromISO(item.created_at).toFormat('yyyy-MM-dd')),
                deviceNum: data.map((item) => item.deviceNum),
                recordNum: data.map((item) => item.recordNum),
                scanNum: data.map((item) => item.scanNum)
            }

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

    public async getChannelNames({ auth, response }: HttpContextContract) {
        try {
            const user = auth.user
            const role = user?.$attributes?.role

            // 验证管理员或操作员权限
            if (role !== 'admin' && role !== 'operator') {
                return response.forbidden({
                    message: 'unauthorized'
                })
            }

            // 获取所有渠道名称
            const channels = await OcrChannel.query().select('name').orderBy('name', 'asc')

            return response.ok({
                data: channels.map((channel) => channel.name)
            })
        } catch (error) {
            return response.internalServerError({
                message: '获取渠道名称列表时发生错误',
                error: error.message
            })
        }
    }

    public async updateStatistics({ response }: HttpContextContract) {
        try {
            // 获取所有渠道
            const channels = await OcrChannel.all()
            const results: any[] = []

            for (const channel of channels) {
                // 使用Database查询生成器直接执行SQL查询

                // 获取设备数量
                const deviceCountResult = await Database.from('ocr_devices')
                    .where('channel', channel.name)
                    .count('* as total')
                console.log('deviceCountResult', deviceCountResult)

                // 获取记录数量
                const recordCountResult = await Database.from('ocr_records')
                    .where('channel', channel.name)
                    .count('* as total')

                // 获取扫描数量总和
                const scanSumResult = await Database.from('ocr_devices')
                    .where('channel', channel.name)
                    .sum('scanned as total')
                console.log('scanSumResult', scanSumResult)

                // 确保数值正确转换为数字
                const deviceNum = parseInt(deviceCountResult[0].total || '0', 10)
                const recordNum = parseInt(recordCountResult[0].total || '0', 10)
                const scanNum = parseInt(scanSumResult[0].total || '0', 10)

                // 更新渠道数据
                channel.deviceNum = deviceNum
                channel.recordNum = recordNum
                channel.scanNum = scanNum

                await channel.save()
                results.push({
                    id: channel.id,
                    name: channel.name,
                    deviceNum: channel.deviceNum,
                    recordNum: channel.recordNum,
                    scanNum: channel.scanNum
                })
            }

            return response.ok({
                message: '所有渠道统计数据已更新',
                data: results
            })
        } catch (error) {
            return response.internalServerError({
                message: '更新所有渠道统计数据时发生错误',
                error: error.message
            })
        }
    }
}