// BoxDBManager.ets import Box from '../bean/Box'; import relationalStore from '@ohos.data.relationalStore'; import common from '@ohos.app.ability.common'; import emitter from '@ohos.events.emitter'; import { Record } from '../bean/Record'; import { BusinessError } from '@ohos.base'; import { Logger } from './Logger'; import { uartUtils } from './UartUtils'; import { Member } from '../bean/Member'; // 定义锁信息接口 interface LockInfo { lockBoard: number; lockNumber: number; } interface SizeLockMap { size: string; start: number; } interface SizeLockMigrationMap { sizeChar: string; fullSize: string; start: number; } interface MemberInfo { id: number; name: string; phone: string; password: string; } const TAG = "UartUtils"; class BoxDBManager { private static instance: BoxDBManager; private rdbStore: relationalStore.RdbStore | null = null; private readonly DB_NAME = 'BoxDB.db'; private readonly TABLE_NAME = 'BOX'; public readonly DATA_CHANGED_EVENT_ID = 1001; private readonly DB_CONFIG: relationalStore.StoreConfig = { name: this.DB_NAME, securityLevel: relationalStore.SecurityLevel.S1 }; private readonly CREATE_TABLE_SQL = ` CREATE TABLE IF NOT EXISTS ${this.TABLE_NAME} ( ID INTEGER PRIMARY KEY AUTOINCREMENT, PHONE TEXT DEFAULT '', PASSWORD TEXT DEFAULT '', BOX_ID TEXT UNIQUE, SIZE TEXT, STATUS INTEGER DEFAULT 0, ENABLED INTEGER DEFAULT 1, LOCK_BOARD INTEGER DEFAULT 1, LOCK_NUMBER INTEGER DEFAULT 1 )`; private readonly CREATE_LOG_TABLE_SQL = ` CREATE TABLE IF NOT EXISTS BOX_OPERATION_LOG ( ID INTEGER PRIMARY KEY AUTOINCREMENT, BOX_ID INTEGER DEFAULT 0, OPERATION_TIME TEXT NOT NULL, OPERATOR TEXT NOT NULL, ACTION_TYPE TEXT NOT NULL, ACCESS_METHOD TEXT NOT NULL )`; private readonly CREATE_MEMBER_TABLE_SQL = ` CREATE TABLE IF NOT EXISTS MEMBER ( ID INTEGER PRIMARY KEY AUTOINCREMENT, MEMBER_ID TEXT NOT NULL, NAME TEXT NOT NULL, PASSWORD TEXT NOT NULL )`; private constructor() {} public static getInstance(): BoxDBManager { if (!BoxDBManager.instance) { BoxDBManager.instance = new BoxDBManager(); } return BoxDBManager.instance; } // 初始化数据库（使用Promise） async initDatabase(context: common.Context): Promise<boolean> { try { this.rdbStore = await relationalStore.getRdbStore(context, this.DB_CONFIG); await this.rdbStore.executeSql(this.CREATE_TABLE_SQL); await this.rdbStore.executeSql(this.CREATE_LOG_TABLE_SQL); await this.rdbStore.executeSql(this.CREATE_MEMBER_TABLE_SQL); await this.initDefaultBoxes(); console.info('Database initialized successfully'); return true; } catch (err) { console.error(`Database init failed: ${JSON.stringify(err)}`); return false; } } // 新增：添加会员（使用 Member 类） async addMember(member: Member): Promise<boolean> { if (!this.rdbStore){return false} try { const valueBucket: relationalStore.ValuesBucket = { 'MEMBER_ID': member.id, // 会员编号（字符串） 'NAME': member.name, // 姓名 'PASSWORD': member.password // 密码（已拼接好的完整密码） }; // 插入会员表 await this.rdbStore.insert('MEMBER', valueBucket); console.info(`会员 ${member.name} 添加成功`); return true; } catch (err) { console.error(`添加会员失败: ${JSON.stringify(err)}`); return false; } } // 验证会员（通过密码） async verifyMember(password: string): Promise<boolean> { if (!this.rdbStore) return false; try { // 使用RegExp构造函数替代正则表达式字面量 const passwordRegex = new RegExp('^\\d{8}$'); if (!passwordRegex.test(password)) { console.error("密码格式错误，必须是8位数字"); return false; } const predicates = new relationalStore.RdbPredicates('MEMBER'); predicates.equalTo('PASSWORD', password); const columns = ['ID']; const resultSet = await this.rdbStore.query(predicates, columns); const isMember = resultSet.rowCount > 0; resultSet.close(); return isMember; } catch (err) { console.error(`验证会员失败: ${JSON.stringify(err)}`); return false; } } // 删除会员 async deleteMember(memberId: string): Promise<boolean> { if (!this.rdbStore) return false; try { const predicates = new relationalStore.RdbPredicates('MEMBER'); predicates.equalTo('MEMBER_ID', memberId); const rowsDeleted = await this.rdbStore.delete(predicates); console.info(`删除会员成功，ID: ${memberId}，删除行数: ${rowsDeleted}`); this.notifyDataChanged(); // 通知数据变化 return rowsDeleted > 0; } catch (err) { console.error(`删除会员失败: ${JSON.stringify(err)}`); return false; } } // 获取所有会员（用于后台管理页面展示） async getAllMembers(): Promise<Member[]> { if (!this.rdbStore) return []; try { const predicates = new relationalStore.RdbPredicates('MEMBER'); const columns = ['MEMBER_ID', 'NAME', 'PASSWORD']; const resultSet = await this.rdbStore.query(predicates, columns); const members: Member[] = []; while (resultSet.goToNextRow()) { members.push(new Member( resultSet.getLong(resultSet.getColumnIndex('MEMBER_ID')).toString(), resultSet.getString(resultSet.getColumnIndex('NAME')), resultSet.getString(resultSet.getColumnIndex('PASSWORD')) // 返回存储的密码 )); } resultSet.close(); return members; } catch (err) { console.error(`查询会员列表失败: ${JSON.stringify(err)}`); return []; } } // 在BoxDBManager类中添加以下方法 // 更新会员信息（支持ID修改） async updateMember(oldId: string, newMember: Member): Promise<boolean> { if (!this.rdbStore) return false; try { // 检查新ID是否已存在（如果修改了ID） if (oldId !== newMember.id) { // 直接查询新ID是否已存在 const checkPredicates = new relationalStore.RdbPredicates('MEMBER'); checkPredicates.equalTo('MEMBER_ID', newMember.id); const resultSet = await this.rdbStore.query(checkPredicates, ['MEMBER_ID']); const exists = resultSet.rowCount > 0; resultSet.close(); if (exists) { console.error(`更新失败：会员编号 ${newMember.id} 已存在`); return false; } } // 构建更新条件 const predicates = new relationalStore.RdbPredicates('MEMBER'); predicates.equalTo('MEMBER_ID', oldId); // 构建更新内容 const valueBucket: relationalStore.ValuesBucket = { 'MEMBER_ID': newMember.id, 'NAME': newMember.name, 'PASSWORD': newMember.password }; // 执行更新 const rowsUpdated = await this.rdbStore.update(valueBucket, predicates); if (rowsUpdated === 0) { console.error(`未找到会员编号 ${oldId} 的记录`); return false; } console.info(`更新会员成功，从ID: ${oldId} 更新为 ${newMember.id}`); this.notifyDataChanged(); // 通知数据变化 return true; } catch (err) { console.error(`更新会员失败: ${JSON.stringify(err)}`); return false; } } // 初始化默认箱子 private async initDefaultBoxes(): Promise<void> { const count = await this.getBoxCount(); if (count > 0) return; const sizeLockMapping:SizeLockMap[] = [ { size: "特大", start: 1 }, { size: "大", start: 4 }, { size: "中", start: 7 }, { size: "小", start: 10 } ]; const boxes: relationalStore.ValuesBucket[] = []; for (const mapping of sizeLockMapping) { for (let i = 1; i <= 3; i++) { boxes.push({ 'BOX_ID': `${mapping.size.charAt(0)}${i}`, 'SIZE': mapping.size, 'STATUS': 0, 'ENABLED': 1, 'LOCK_BOARD': 1, 'LOCK_NUMBER': mapping.start + (i - 1) // 计算正确的锁号 }); } } await this.rdbStore?.batchInsert(this.TABLE_NAME, boxes); console.info(`${boxes.length} default boxes initialized`); } async migrateLockNumbers(): Promise<void> { if (!this.rdbStore) return; try { // 获取所有箱子 const allBoxes = await this.getAllBoxes(); // 使用类型安全的映射定义 const sizeLockMapping:SizeLockMigrationMap[] = [ { sizeChar: "特", fullSize: "特大", start: 1 }, { sizeChar: "大", fullSize: "大", start: 4 }, { sizeChar: "中", fullSize: "中", start: 7 }, { sizeChar: "小", fullSize: "小", start: 10 } ]; for (const box of allBoxes) { // 提取尺寸和序号 const regex = new RegExp("^(\\D+)(\\d+)$"); const match = regex.exec(box.boxId); if (!match) continue; const sizeChar = match[1]; const index = parseInt(match[2]); // 查找匹配的尺寸映射 const mapping = sizeLockMapping.find(m => m.sizeChar === sizeChar); if (!mapping) continue; // 计算新锁号 const newLockNumber = mapping.start + (index - 1); // 更新数据库 const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME); predicates.equalTo('ID', box.id); const valueBucket: relationalStore.ValuesBucket = { 'LOCK_NUMBER': newLockNumber }; await this.rdbStore.update(valueBucket, predicates); } console.info("Lock numbers migrated successfully"); } catch (err) { console.error("Lock number migration failed:", err); } } // 获取箱子数量 private async getBoxCount(): Promise<number> { if (!this.rdbStore) return 0; const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME); const result = await this.rdbStore.query(predicates, ['count(*) as count']); if (result.rowCount > 0 && result.goToFirstRow()) { return result.getLong(result.getColumnIndex('count')); } return 0; } // 获取所有箱子 async getAllBoxes(): Promise<Box[]> { if (!this.rdbStore) return []; try { const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME); const columns = ['ID', 'PHONE', 'PASSWORD', 'BOX_ID', 'SIZE', 'STATUS', 'ENABLED', 'LOCK_BOARD', 'LOCK_NUMBER']; // 直接使用await获取结果集 const resultSet = await this.rdbStore.query(predicates, columns); const boxes: Box[] = []; if (resultSet.rowCount > 0) { while (resultSet.goToNextRow()) { const box = new Box( resultSet.getString(resultSet.getColumnIndex('BOX_ID')), resultSet.getString(resultSet.getColumnIndex('SIZE')), resultSet.getLong(resultSet.getColumnIndex('STATUS')), resultSet.getLong(resultSet.getColumnIndex('ENABLED')) === 1, resultSet.getLong(resultSet.getColumnIndex('LOCK_BOARD')), resultSet.getLong(resultSet.getColumnIndex('LOCK_NUMBER')) ); box.id = resultSet.getLong(resultSet.getColumnIndex('ID')); box.phone = resultSet.getString(resultSet.getColumnIndex('PHONE')); box.password = resultSet.getString(resultSet.getColumnIndex('PASSWORD')); boxes.push(box); } } resultSet.close(); return boxes; } catch (err) { console.error(`Query failed: ${JSON.stringify(err)}`); return []; } } // 更新箱子状态 async updateBoxStatus(id: number, status: number): Promise<boolean> { if (!this.rdbStore) return false; const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME); predicates.equalTo('ID', id); const valueBucket: relationalStore.ValuesBucket = { 'STATUS': status }; await this.rdbStore.update(valueBucket, predicates); this.notifyDataChanged(); return true; } // 占用箱子 async occupyBox(boxId: string, phone: string, password: string): Promise<boolean> { if (!this.rdbStore) return false; const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME); predicates.equalTo('BOX_ID', boxId); predicates.equalTo('STATUS', 0); const valueBucket: relationalStore.ValuesBucket = { 'PHONE': phone, 'PASSWORD': password, 'STATUS': 1 }; await this.rdbStore.update(valueBucket, predicates); this.notifyDataChanged(); return true; } // 释放箱子 async releaseBox(phoneSuffix: string, password: string): Promise<string[]> { if (!this.rdbStore) return []; // 查询匹配的箱子 const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME); predicates.endsWith('PHONE', phoneSuffix); predicates.equalTo('PASSWORD', password); predicates.equalTo('STATUS', 1); predicates.equalTo('ENABLED', 1); const result = await this.rdbStore.query(predicates, ['ID', 'BOX_ID']); const boxIds: string[] = []; while (result.goToNextRow()) { boxIds.push(result.getString(result.getColumnIndex('BOX_ID'))); } result.close(); if (boxIds.length === 0) return []; // 更新匹配的箱子 const updatePredicates = new relationalStore.RdbPredicates(this.TABLE_NAME); updatePredicates.endsWith('PHONE', phoneSuffix); updatePredicates.equalTo('PASSWORD', password); updatePredicates.equalTo('STATUS', 1); updatePredicates.equalTo('ENABLED', 1); const valueBucket: relationalStore.ValuesBucket = { 'PHONE': '', 'PASSWORD': '', 'STATUS': 0 }; await this.rdbStore.update(valueBucket, updatePredicates); this.notifyDataChanged(); return boxIds; } // 更新箱子信息 public async updateBoxInfo( id: number, boxId: string, size: string, status: number, // 新增状态参数 enabled: boolean, lockBoard: number, lockNumber: number ): Promise<boolean> { if (!this.rdbStore) return false; try { const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME); predicates.equalTo('ID', id); const valueBucket: relationalStore.ValuesBucket = { 'BOX_ID': boxId, 'SIZE': size, 'STATUS': status, // 保存状态值 'ENABLED': enabled ? 1 : 0, 'LOCK_BOARD': lockBoard, 'LOCK_NUMBER': lockNumber }; const changedRows = await this.rdbStore.update(valueBucket, predicates); console.info(`Updated ${changedRows} rows for box ID: ${id}, new status: ${status}`); this.notifyDataChanged(); return changedRows > 0; } catch (err) { console.error(`Update box info failed: ${JSON.stringify(err)}`); return false; } } // 删除箱子 public async deleteBox(id: number): Promise<number> { if (!this.rdbStore) return 0; try { const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME); predicates.equalTo('ID', id); const rows = await this.rdbStore.delete(predicates); this.notifyDataChanged(); return rows; } catch (err) { console.error(`Delete box failed: ${JSON.stringify(err)}`); return 0; } } // 添加新箱子 public async addBox(box: Box): Promise<number> { if (!this.rdbStore) return -1; try { const valueBucket: relationalStore.ValuesBucket = { 'BOX_ID': box.boxId, 'SIZE': box.size, 'ENABLED': box.enabled ? 1 : 0, 'LOCK_BOARD': box.lockBoard, 'LOCK_NUMBER': box.lockNumber }; const id = await this.rdbStore.insert(this.TABLE_NAME, valueBucket); this.notifyDataChanged(); return id; } catch (err) { console.error(`Add box failed: ${JSON.stringify(err)}`); return -1; } } // 清空箱子的手机号和密码 public async clearBoxCredentials(id: number): Promise<boolean> { if (!this.rdbStore) return false; try { const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME); predicates.equalTo('ID', id); const valueBucket: relationalStore.ValuesBucket = { 'PHONE': '', 'PASSWORD': '' }; await this.rdbStore.update(valueBucket, predicates); return true; } catch (err) { console.error(`Clear credentials failed: ${JSON.stringify(err)}`); return false; } } // 通知数据变化 private notifyDataChanged() { try { console.info("Emitting DATA_CHANGED event"); emitter.emit({ eventId: this.DATA_CHANGED_EVENT_ID }, { data: { action: "DATA_CHANGED" } }); } catch (err) { console.error(`Emit event failed: ${err.message}`); } } addOperationLog(record:Record, callback: (success: boolean) => void) { const rdbStore = this.rdbStore; if (!rdbStore) { console.error('RdbStore is not initialized'); callback(false); return; } const valueBucket: relationalStore.ValuesBucket = { 'BOX_ID': record.Box_ID, 'OPERATION_TIME': record.Operation_Time, // 当前时间戳 'OPERATOR': record.Operator, 'ACTION_TYPE': record.Action_Type, 'ACCESS_METHOD': record.Access_Method }; rdbStore.insert('BOX_OPERATION_LOG', valueBucket, (err: BusinessError) => { if (err) { console.error(`Insert log failed. Code:${err.code}, message:${err.message}`); callback(false); return; } callback(true); }); } getLogsByTime(time: string, callback: (logs: Record[]) => void) { const rdbStore = this.rdbStore; if (!rdbStore) { console.error('RdbStore is not initialized'); callback([]); return; } const predicates = new relationalStore.RdbPredicates('BOX_OPERATION_LOG'); // 只查询Record类需要的字段 const columns = ['BOX_ID', 'OPERATION_TIME', 'OPERATOR', 'ACTION_TYPE', 'ACCESS_METHOD']; // 构建时间查询条件 if (time.length === 10) { // YYYY-MM-DD 格式 predicates.greaterThanOrEqualTo('OPERATION_TIME', `${time}T00:00:00Z`); predicates.lessThan('OPERATION_TIME', `${time}T23:59:59.999Z`); } else if (time.length === 7) { // YYYY-MM 格式 predicates.like('OPERATION_TIME', `${time}-%`); } else { console.error('Invalid time format. Use YYYY-MM or YYYY-MM-DD'); callback([]); return; } rdbStore.query(predicates, columns, (err: BusinessError, resultSet: relationalStore.ResultSet) => { if (err) { console.error(`Query failed. Code:${err.code}, message:${err.message}`); callback([]); return; } const logs: Record[] = []; if (resultSet.rowCount > 0) { while (resultSet.goToNextRow()) { logs.push(new Record( resultSet.getLong(resultSet.getColumnIndex('BOX_ID')), resultSet.getString(resultSet.getColumnIndex('OPERATION_TIME')), resultSet.getString(resultSet.getColumnIndex('OPERATOR')), resultSet.getString(resultSet.getColumnIndex('ACTION_TYPE')), resultSet.getString(resultSet.getColumnIndex('ACCESS_METHOD')) )); } } resultSet.close(); callback(logs); }); } // 新增方法：根据箱子ID获取锁板编号和锁编号 public async getLockInfoById(id: number): Promise<LockInfo | null> { if (!this.rdbStore) return null; try { const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME); predicates.equalTo('ID', id); const columns = ['LOCK_BOARD', 'LOCK_NUMBER']; const resultSet = await this.rdbStore.query(predicates, columns); if (resultSet.rowCount > 0 && resultSet.goToFirstRow()) { return { lockBoard: resultSet.getLong(resultSet.getColumnIndex('LOCK_BOARD')), lockNumber: resultSet.getLong(resultSet.getColumnIndex('LOCK_NUMBER')) }; } return null; } catch (err) { console.error(`Get lock info failed: ${JSON.stringify(err)}`); return null; } } // 新增方法：根据箱子ID直接开锁 public async openLockById(id: number): Promise<boolean> { try { // 获取锁信息 const lockInfo = await this.getLockInfoById(id); if (!lockInfo) { Logger.e(TAG, `No lock info found for box ID: ${id}`); return false; } // 检查锁编号是否在有效范围内 (1-12) if (lockInfo.lockNumber < 1 || lockInfo.lockNumber > 12) { Logger.e(TAG, `Invalid lock number ${lockInfo.lockNumber} for box ID: ${id}`); return false; } // 开锁 uartUtils.openSpecificLock(lockInfo.lockNumber); return true; } catch (error) { Logger.e(TAG, `Failed to open lock for box ID ${id}: ${error.message}`); return false; } } } export default BoxDBManager.getInstance(); 根据box类将上面的代码修改一下 deveco studio4.1版本API11// Box.ets export default class Box { id: number = 0; phone: string = ''; password: string = ''; boxId: string = ''; size: string = ''; status: number = 0; // 0: 空闲, 1: 占用, 2: 打开 enabled: boolean = true; // 新增：能用状态 lockBoard: number = 1; // 新增：锁板 lockNumber: number = 1; // 新增：锁号 constructor( boxId: string, size: string, status: number, enabled: boolean = true, lockBoard: number = 1, lockNumber: number = 1 ) { this.boxId = boxId; this.size = size; this.status = status; this.enabled = enabled; this.lockBoard = lockBoard; this.lockNumber = lockNumber; } }