refactor: implement query runners for database operations in multiple services

libs/common/src/firebase/devices-status/services/devices-status.service.ts

@@ -24,6 +24,7 @@ import { PowerClampEnergyEnum } from '@app/common/constants/power.clamp.enargy.e
 import { PresenceSensorEnum } from '@app/common/constants/presence.sensor.enum';
 import { OccupancyService } from '@app/common/helper/services/occupancy.service';
 import { AqiDataService } from '@app/common/helper/services/aqi.data.service';
+import { DataSource, QueryRunner } from 'typeorm';
 @Injectable()
 export class DeviceStatusFirebaseService {
   private tuya: TuyaContext;
@@ -35,6 +36,7 @@ export class DeviceStatusFirebaseService {
     private readonly occupancyService: OccupancyService,
     private readonly aqiDataService: AqiDataService,
     private deviceStatusLogRepository: DeviceStatusLogRepository,
+    private readonly dataSource: DataSource,
   ) {
     const accessKey = this.configService.get<string>('auth-config.ACCESS_KEY');
     const secretKey = this.configService.get<string>('auth-config.SECRET_KEY');
@@ -79,28 +81,46 @@ export class DeviceStatusFirebaseService {
   async addDeviceStatusToFirebase(
     addDeviceStatusDto: AddDeviceStatusDto,
   ): Promise<AddDeviceStatusDto | null> {
+    const queryRunner = this.dataSource.createQueryRunner();
+    await queryRunner.connect();
+    await queryRunner.startTransaction();
     try {
       const device = await this.getDeviceByDeviceTuyaUuid(
         addDeviceStatusDto.deviceTuyaUuid,
+        queryRunner,
       );
 
       if (device?.uuid) {
-        return await this.createDeviceStatusFirebase({
+        const result = await this.createDeviceStatusFirebase(
+          {
             deviceUuid: device.uuid,
             ...addDeviceStatusDto,
             productType: device.productDevice.prodType,
-        });
+          },
+          queryRunner,
+        );
+        await queryRunner.commitTransaction();
+        return result;
       }
       // Return null if device not found or no UUID
+      await queryRunner.rollbackTransaction();
       return null;
     } catch (error) {
-      // Handle the error silently, perhaps log it internally or ignore it
+      await queryRunner.rollbackTransaction();
       return null;
+    } finally {
+      await queryRunner.release();
     }
   }
+  async getDeviceByDeviceTuyaUuid(
+    deviceTuyaUuid: string,
+    queryRunner?: QueryRunner,
+  ) {
+    const repo = queryRunner
+      ? queryRunner.manager.getRepository(this.deviceRepository.target)
+      : this.deviceRepository;
 
-  async getDeviceByDeviceTuyaUuid(deviceTuyaUuid: string) {
-    return await this.deviceRepository.findOne({
+    return await repo.findOne({
       where: {
         deviceTuyaUuid,
         isActive: true,
@@ -108,6 +128,7 @@ export class DeviceStatusFirebaseService {
       relations: ['productDevice'],
     });
   }
+
   async getDevicesInstructionStatus(deviceUuid: string) {
     try {
       const deviceDetails = await this.getDeviceByDeviceUuid(deviceUuid);
@@ -153,9 +174,14 @@ export class DeviceStatusFirebaseService {
   }
   async getDeviceByDeviceUuid(
     deviceUuid: string,
-    withProductDevice: boolean = true,
+    withProductDevice = true,
+    queryRunner?: QueryRunner,
   ) {
-    return await this.deviceRepository.findOne({
+    const repo = queryRunner
+      ? queryRunner.manager.getRepository(this.deviceRepository.target)
+      : this.deviceRepository;
+
+    return await repo.findOne({
       where: {
         uuid: deviceUuid,
         isActive: true,
@@ -163,21 +189,20 @@ export class DeviceStatusFirebaseService {
       ...(withProductDevice && { relations: ['productDevice'] }),
     });
   }
+
   async createDeviceStatusFirebase(
     addDeviceStatusDto: AddDeviceStatusDto,
+    queryRunner?: QueryRunner,
   ): Promise<any> {
     const dataRef = ref(
       this.firebaseDb,
       `device-status/${addDeviceStatusDto.deviceUuid}`,
     );
 
-    // Use a transaction to handle concurrent updates
+    // Step 1: Update Firebase Realtime Database
     await runTransaction(dataRef, (existingData) => {
-      if (!existingData) {
-        existingData = {};
-      }
+      if (!existingData) existingData = {};
 
-      // Assign default values if fields are not present
       if (!existingData.deviceTuyaUuid) {
         existingData.deviceTuyaUuid = addDeviceStatusDto.deviceTuyaUuid;
       }
@@ -191,18 +216,15 @@ export class DeviceStatusFirebaseService {
         existingData.status = [];
       }
 
-      // Create a map to track existing status codes
+      // Merge incoming status with existing status
       const statusMap = new Map(
         existingData.status.map((item) => [item.code, item.value]),
       );
 
-      // Update or add status codes
-
       for (const statusItem of addDeviceStatusDto.status) {
         statusMap.set(statusItem.code, statusItem.value);
       }
 
-      // Convert the map back to an array format
       existingData.status = Array.from(statusMap, ([code, value]) => ({
         code,
         value,
@@ -211,9 +233,9 @@ export class DeviceStatusFirebaseService {
       return existingData;
     });
 
-    // Save logs to your repository
-    const newLogs = addDeviceStatusDto.log.properties.map((property) => {
-      return this.deviceStatusLogRepository.create({
+    // Step 2: Save device status log entries
+    const newLogs = addDeviceStatusDto.log.properties.map((property) =>
+      this.deviceStatusLogRepository.create({
         deviceId: addDeviceStatusDto.deviceUuid,
         deviceTuyaId: addDeviceStatusDto.deviceTuyaUuid,
         productId: addDeviceStatusDto.log.productId,
@@ -222,10 +244,19 @@ export class DeviceStatusFirebaseService {
         value: property.value,
         eventId: addDeviceStatusDto.log.dataId,
         eventTime: new Date(property.time).toISOString(),
-      });
-    });
-    await this.deviceStatusLogRepository.save(newLogs);
+      }),
+    );
 
+    if (queryRunner) {
+      const repo = queryRunner.manager.getRepository(
+        this.deviceStatusLogRepository.target,
+      );
+      await repo.save(newLogs);
+    } else {
+      await this.deviceStatusLogRepository.save(newLogs);
+    }
+
+    // Step 3: Trigger additional data services
     if (addDeviceStatusDto.productType === ProductType.PC) {
       const energyCodes = new Set([
         PowerClampEnergyEnum.ENERGY_CONSUMED,
@@ -269,7 +300,8 @@ export class DeviceStatusFirebaseService {
         addDeviceStatusDto.deviceUuid,
       );
     }
-    // Return the updated data
+
+    // Step 4: Return updated Firebase status
     const snapshot: DataSnapshot = await get(dataRef);
     return snapshot.val();
   }

libs/common/src/helper/services/aqi.data.service.ts

@@ -36,9 +36,18 @@ export class AqiDataService {
     procedureFileName: string,
     params: (string | number | null)[],
   ): Promise<void> {
+    const queryRunner = this.dataSource.createQueryRunner();
+    await queryRunner.connect();
+    try {
       const query = this.loadQuery(procedureFolderName, procedureFileName);
-    await this.dataSource.query(query, params);
+      await queryRunner.query(query, params);
       console.log(`Procedure ${procedureFileName} executed successfully.`);
+    } catch (err) {
+      console.error(`Failed to execute procedure ${procedureFileName}:`, err);
+      throw err;
+    } finally {
+      await queryRunner.release();
+    }
   }
 
   private loadQuery(folderName: string, fileName: string): string {

libs/common/src/helper/services/occupancy.service.ts

@@ -57,9 +57,18 @@ export class OccupancyService {
     procedureFileName: string,
     params: (string | number | null)[],
   ): Promise<void> {
+    const queryRunner = this.dataSource.createQueryRunner();
+    await queryRunner.connect();
+    try {
       const query = this.loadQuery(procedureFolderName, procedureFileName);
-    await this.dataSource.query(query, params);
+      await queryRunner.query(query, params);
       console.log(`Procedure ${procedureFileName} executed successfully.`);
+    } catch (err) {
+      console.error(`Failed to execute procedure ${procedureFileName}:`, err);
+      throw err;
+    } finally {
+      await queryRunner.release();
+    }
   }
 
   private loadQuery(folderName: string, fileName: string): string {

libs/common/src/helper/services/power.clamp.service.ts

@@ -46,12 +46,21 @@ export class PowerClampService {
     procedureFileName: string,
     params: (string | number | null)[],
   ): Promise<void> {
+    const queryRunner = this.dataSource.createQueryRunner();
+    await queryRunner.connect();
+    try {
       const query = this.loadQuery(
         'fact_device_energy_consumed',
         procedureFileName,
       );
-    await this.dataSource.query(query, params);
+      await queryRunner.query(query, params);
       console.log(`Procedure ${procedureFileName} executed successfully.`);
+    } catch (err) {
+      console.error(`Failed to execute procedure ${procedureFileName}:`, err);
+      throw err;
+    } finally {
+      await queryRunner.release();
+    }
   }
 
   private loadQuery(folderName: string, fileName: string): string {

src/community/services/community.service.ts

@@ -190,25 +190,26 @@ export class CommunityService {
         .distinct(true);
 
       if (includeSpaces) {
-        qb.leftJoinAndSelect('c.spaces', 'space', 'space.disabled = false')
+        qb.leftJoinAndSelect(
+          'c.spaces',
+          'space',
+          'space.disabled = :disabled AND space.spaceName != :orphanSpaceName',
+          { disabled: false, orphanSpaceName: ORPHAN_SPACE_NAME },
+        )
           .leftJoinAndSelect('space.parent', 'parent')
           .leftJoinAndSelect(
             'space.children',
             'children',
             'children.disabled = :disabled',
             { disabled: false },
-          )
+          );
         // .leftJoinAndSelect('space.spaceModel', 'spaceModel')
-          .andWhere('space.spaceName != :orphanSpaceName', {
-            orphanSpaceName: ORPHAN_SPACE_NAME,
-          })
-          .andWhere('space.disabled = :disabled', { disabled: false });
       }
 
       if (search) {
         qb.andWhere(
           `c.name ILIKE :search ${includeSpaces ? 'OR space.space_name ILIKE :search' : ''}`,
-          { search: `%${search}%` },
+          { search },
         );
       }
 
@@ -216,7 +217,6 @@ export class CommunityService {
 
       const { baseResponseDto, paginationResponseDto } =
         await customModel.findAll({ ...pageable, modelName: 'community' }, qb);
-
       if (includeSpaces) {
         baseResponseDto.data = baseResponseDto.data.map((community) => ({
           ...community,

src/device/controllers/device-project.controller.ts

@@ -1,11 +1,11 @@
-import { DeviceService } from '../services/device.service';
-import { Controller, Get, Param, Query, UseGuards } from '@nestjs/common';
-import { ApiTags, ApiBearerAuth, ApiOperation } from '@nestjs/swagger';
-import { EnableDisableStatusEnum } from '@app/common/constants/days.enum';
 import { ControllerRoute } from '@app/common/constants/controller-route';
-import { PermissionsGuard } from 'src/guards/permissions.guard';
+import { EnableDisableStatusEnum } from '@app/common/constants/days.enum';
+import { Controller, Get, Param, Query, UseGuards } from '@nestjs/common';
+import { ApiBearerAuth, ApiOperation, ApiTags } from '@nestjs/swagger';
 import { Permissions } from 'src/decorators/permissions.decorator';
-import { GetDoorLockDevices, ProjectParam } from '../dtos';
+import { PermissionsGuard } from 'src/guards/permissions.guard';
+import { GetDevicesFilterDto, ProjectParam } from '../dtos';
+import { DeviceService } from '../services/device.service';
 
 @ApiTags('Device Module')
 @Controller({
@@ -25,7 +25,7 @@ export class DeviceProjectController {
   })
   async getAllDevices(
     @Param() param: ProjectParam,
-    @Query() query: GetDoorLockDevices,
+    @Query() query: GetDevicesFilterDto,
   ) {
     return await this.deviceService.getAllDevices(param, query);
   }

src/device/dtos/get.device.dto.ts

@@ -1,6 +1,7 @@
 import { DeviceTypeEnum } from '@app/common/constants/device-type.enum';
 import { ApiProperty } from '@nestjs/swagger';
 import {
+  IsArray,
   IsEnum,
   IsNotEmpty,
   IsOptional,
@@ -41,16 +42,7 @@ export class GetDeviceLogsDto {
   @IsOptional()
   public endTime: string;
 }
-export class GetDoorLockDevices {
-  @ApiProperty({
-    description: 'Device Type',
-    enum: DeviceTypeEnum,
-    required: false,
-  })
-  @IsEnum(DeviceTypeEnum)
-  @IsOptional()
-  public deviceType: DeviceTypeEnum;
-}
+
 export class GetDevicesBySpaceOrCommunityDto {
   @ApiProperty({
     description: 'Device Product Type',
@@ -72,3 +64,23 @@ export class GetDevicesBySpaceOrCommunityDto {
   @IsNotEmpty({ message: 'Either spaceUuid or communityUuid must be provided' })
   requireEither?: never; // This ensures at least one of them is provided
 }
+
+export class GetDevicesFilterDto {
+  @ApiProperty({
+    description: 'Device Type',
+    enum: DeviceTypeEnum,
+    required: false,
+  })
+  @IsEnum(DeviceTypeEnum)
+  @IsOptional()
+  public deviceType: DeviceTypeEnum;
+  @ApiProperty({
+    description: 'List of Space IDs to filter devices',
+    required: false,
+    example: ['60d21b4667d0d8992e610c85', '60d21b4967d0d8992e610c86'],
+  })
+  @IsOptional()
+  @IsArray()
+  @IsUUID('4', { each: true })
+  public spaces?: string[];
+}

src/device/services/device.service.ts

@@ -53,7 +53,7 @@ import { DeviceSceneParamDto } from '../dtos/device.param.dto';
 import {
   GetDeviceLogsDto,
   GetDevicesBySpaceOrCommunityDto,
-  GetDoorLockDevices,
+  GetDevicesFilterDto,
 } from '../dtos/get.device.dto';
 import {
   controlDeviceInterface,
@@ -955,19 +955,20 @@ export class DeviceService {
 
   async getAllDevices(
     param: ProjectParam,
-    query: GetDoorLockDevices,
+    { deviceType, spaces }: GetDevicesFilterDto,
   ): Promise<BaseResponseDto> {
     try {
       await this.validateProject(param.projectUuid);
-      if (query.deviceType === DeviceTypeEnum.DOOR_LOCK) {
-        return await this.getDoorLockDevices(param.projectUuid);
-      } else if (!query.deviceType) {
+      if (deviceType === DeviceTypeEnum.DOOR_LOCK) {
+        return await this.getDoorLockDevices(param.projectUuid, spaces);
+      } else if (!deviceType) {
         const devices = await this.deviceRepository.find({
           where: {
             isActive: true,
             spaceDevice: {
-              community: { project: { uuid: param.projectUuid } },
+              uuid: spaces && spaces.length ? In(spaces) : undefined,
               spaceName: Not(ORPHAN_SPACE_NAME),
+              community: { project: { uuid: param.projectUuid } },
             },
           },
           relations: [
@@ -1563,7 +1564,7 @@ export class DeviceService {
     }
   }
 
-  async getDoorLockDevices(projectUuid: string) {
+  async getDoorLockDevices(projectUuid: string, spaces?: string[]) {
     await this.validateProject(projectUuid);
 
     const devices = await this.deviceRepository.find({
@@ -1573,6 +1574,7 @@ export class DeviceService {
         },
         spaceDevice: {
           spaceName: Not(ORPHAN_SPACE_NAME),
+          uuid: spaces && spaces.length ? In(spaces) : undefined,
           community: {
             project: {
               uuid: projectUuid,