Course Specialization

Task 1: Create SpecializationController

  import { SpecializationService } from './specialization.service';
  import { Body, Controller, Delete, Get, HttpCode, Param, Post, Put, Query, Request, UseGuards } from '@nestjs/common';
  import { JwtAuthGuard } from 'src/guard/jwt-auth.guard';
  import { MSpecializationService } from './mspecialization.service';


  @Controller('api/v1/specializations')
  export class SpecializationController {

      constructor(private specializationService: MSpecializationService) {

      }

      @Get()
      @HttpCode(200)
      async getSpecializationsList(@Query('orgId') orgId: string) {
          return this.specializationService.getAllSpecializations(orgId);
      }

      @UseGuards(JwtAuthGuard)
      @Post()
      @HttpCode(200)
      async addSpecialization(@Request() request, @Body() specialization: any) {
          const orgId = request.user.orgId;
          specialization.orgId = orgId;
          return this.specializationService.addSpecialization(specialization);
      }


      @UseGuards(JwtAuthGuard)
      @Put(':id/courses/:courseId')
      @HttpCode(200)
      async updateCourse(@Param("id") id: string, @Param("courseId") courseId: string) {
          return this.specializationService.assignCourse(id, courseId);
      }

      @UseGuards(JwtAuthGuard)
      @Delete(':id/courses/:courseId')
      @HttpCode(200)
      async removeCourse(@Param("id") id: string, @Param("courseId") courseId: string) {
          return this.specializationService.removeCourse(id, courseId);
      }

      @Delete(':id')
      @HttpCode(200)
      async deleteSpecialization(@Param("id") id: string) {
          return this.specializationService.deleteSpecialization(id);
      }


      @Get('users/:userId')
      @HttpCode(200)
      async getSpecializationsUserList(@Param('userId') userId: string, @Query('orgId') orgId: string) {
          return this.specializationService.getUserSpecializations(orgId, userId);
      }

      @UseGuards(JwtAuthGuard)
      @Get('publish')
      @HttpCode(200)
      async publish(@Request() request) {
          const orgId = request.user.orgId;
          return this.specializationService.publish(orgId);
      }

      @Get(':id')
      @HttpCode(200)
      async getSpecializationList(@Param('id') id: string, @Query('userId') userId: string) {
          return this.specializationService.getSpecialization(id);
      }


  }

Task 2: Create SpecializationService

  import { CourseService } from './../course/course.service';
  import { Injectable } from "@nestjs/common";
  import { SpecializationDAO } from "src/course/specialization.dao";
  import { MUserCourseDAO } from "src/usercourse/musercourse.dao";
  import { UserCourseDAO } from "src/usercourse/usercourse.dao";
  import { MSpecializationDAO } from 'src/course/mspecialization.dao';

  @Injectable()
  export class SpecializationService {
    constructor(
      private specializationDAO: SpecializationDAO,
      private userCourseDAO: UserCourseDAO,
      private muserCourseDAO: MUserCourseDAO,
      private courseService: CourseService
    ) { }



    async getAllSpecializations(orgId: string) {
      const specializations = await this.specializationDAO.findAllWithCourses(orgId);
      return specializations;
    }

    async getUserSpecializations(orgId: string, userId: string) {
      const specializations = await this.specializationDAO.findAll(orgId);
      console.table(specializations);
      let completionData = await this.specializationDAO.findCompletionReport(
        userId
      );

      if (completionData) {
        specializations.forEach((s) => {
          let data = completionData.find((obj: any) => obj.id == s.id);

          s.percentage = data != null ? data["percentage"] : 0;
        });
      }

      return specializations;
    }

    async addSpecialization(specialization: any) {
      const result = await this.specializationDAO.save(
        specialization
      );
      return result;
    }



    async deleteSpecialization(id: any) {
      const result = await this.specializationDAO.delete(
        id
      );
      return result;
    }

    async assignCourse(id: any, specializationCourse: any) {

      specializationCourse.specializationId = id;
      const result = await this.specializationDAO.addCourse(
        specializationCourse
      );
      return result;
    }

    async removeCourse(id: any, courseId: string) {

      const result = await this.specializationDAO.removeCourse(id, courseId);
      return result;
    }

    async getSpecialization(specializationId: number, userId: string) {
      const specialization = await this.specializationDAO.findOne(
        specializationId
      );

      let courses: any;
      if (specialization) {

        let specializationCourses = await this.specializationDAO.findCourses(specializationId);

        const courseIds = specializationCourses.map(obj => obj.course_id);//await this.specializationDAO.findSpecializationCourseIds(specializationId);
        console.log(courseIds);

        courses = await this.courseService.getCoursesForIds(courseIds);



        //1. get completion percentage
        // let completionData = await this.specializationDAO.findCompletionReportOne( 
        //     specializationId, userId);


        //2. get enrolled courses
        let totalTopicsCompleted = 0;
        if (userId) {

          let userCourses = await this.muserCourseDAO.findCoursePercentage(userId, courseIds)
          console.log("UserCoursePercentage:", userCourses);

          if (!courses) {
            specialization.percentage = 0;//courses != null ? courses["percentage"] : 0;
          }
          else {





            //courses = await this.specializationDAO.findCourseTopicsCompletionReport(specializationId, userId);

            courses.forEach((c: any) => {

              let userCourse = userCourses.find(obj => obj.courseCode == c.code);
              if (userCourse) {
                c.noOfTopics = userCourse.noOfTopics;
                c.noOfTopicsCompleted = userCourse.noOfTopicsCompleted;
                totalTopicsCompleted += parseInt(c.noOfTopicsCompleted);
                c.percentage = Math.round(100 * parseInt(c.noOfTopicsCompleted) / c.noOfTopics);
              }
            })
            specialization.topicsCompleted = totalTopicsCompleted;
          }
        } else {

        }
        specialization.courses = courses ?? [];
      }

      return specialization;
    }

    async publish(orgId: string) {
      const specializations = await this.specializationDAO.findAll(orgId);
      const courses = await this.specializationDAO.findAllCourses(orgId);

      // console.table(courses);
      specializations.forEach((s) => {
        s.courses = courses.filter((obj: any) => obj.specialization_id == s.id);
        let total = 0;
        courses.forEach(obj => {
          total += obj.no_of_topics;
        });
        s.total_topics = total;
      });

      return specializations;
    }

    async updateTopics() { }

    async updateUserTopics() { }
  }

Task 3: Create SpecializationDAO

  import { Injectable, Logger } from "@nestjs/common";
  import { Connection } from "typeorm";

  @Injectable()
  export class SpecializationDAO {

    private readonly logger = new Logger(SpecializationDAO.name);

    constructor(private connection: Connection) { }



    baseQuery = 'select * from specializations';
    baseQuery1 = 'select s.*, (select count(*) from specialization_courses where specialization_id = s.id) as no_of_courses from specializations s';


    async save(specialization: any) {
      const sql =
        "insert into specializations ( name,category,org_id) values ( ?,?,?)";
      const results = await this.connection.query(sql, [
        specialization.name,
        specialization.category,
        specialization.orgId
      ]);
      return results.insertId;
    }



    async addCourse(specializationCourse: any) {
      const sql =
        "insert into specialization_courses ( specialization_id, course_id, no_of_topics) values ( ?,?,?)";
      const results = await this.connection.query(sql, [
        specializationCourse.specializationId,
        specializationCourse.course.code,
        specializationCourse.course.noOfTopics
      ]);
      return results.insertId;
    }

    async removeCourse(specializationId: any, courseId: string) {
      const sql =
        "delete from specialization_courses where specialization_id = ? and course_id = ?";
      const results = await this.connection.query(sql, [specializationId, courseId]);
      return results.affectedRows;
    }


    async delete(id: any) {
      const sql =
        "delete from specializations where id = ? ";
      const results = await this.connection.query(sql, [
        id
      ]);
      return results.affectedRows;
    }


    async findAll(orgId: string) {
      const sql =
        this.baseQuery + " where org_id = ? order by display_order";
      const results = await this.connection.query(sql, [orgId]);
      return results;
    }


    async findAllWithCourses(orgId: string) {
      const sql =
        this.baseQuery1 + " where org_id =?  order by display_order";
      const results = await this.connection.query(sql, [orgId]);
      return results;
    }

    async findOne(id: number) {
      const sql =
        this.baseQuery + " where id = ?";
      const results = await this.connection.query(sql, [id]);
      return results.length > 0 ? results[0] : null;
    }


    async findSpecializationCourseIds(id: number) {
      const sql = "select course_id from specialization_courses where specialization_id = ?";
      const results = await this.connection.query(sql, [id]);
      return results.map(obj => obj.course_id);
    }


    async findAllCourses(orgId: string) {
      const sql =
        'select sc.*,ct.title as course_title,( select name from ct_categories where id = ct.category) as category,ct.no_of_topics from specializations s, ct_courses ct, specialization_courses sc where s.id = sc.specialization_id and s.org_id =? and ct.code = sc.course_id order by s.display_order, sc.display_order';
      const results = await this.connection.query(sql, [orgId]);
      return results;
    }

    async findCoursesCount(orgId: string) {
      const sql =
        'select specialization_id, count(*) as no_of_courses from specialization_courses where specialization_id in (select id from specializations where org_id = ?) group by specialization_id ';
      const results = await this.connection.query(sql, [orgId]);
      return results;
    }


    async findCourses1(specializationId: number) {
      const sql =
        'select sc.*,ct.title as course_title, ( select name from ct_categories where id = ct.category) as category,ct.no_of_topics from specializations s, ct_courses ct, specialization_courses sc where s.id = sc.specialization_id and ct.code = sc.course_id and sc.specialization_id = ? order by sc.display_order';
      const results = await this.connection.query(sql, [specializationId]);
      return results;
    }

    async findCourses(specializationId: number) {
      const sql =
        'select * from specializations s, specialization_courses sc where s.id = sc.specialization_id and sc.specialization_id = ? order by sc.display_order';
      const results = await this.connection.query(sql, [specializationId]);
      return results;
    }

    async findCompletionReport(userId: string) {
      const sql = 'select s.id, get_specialization_percentage(?,s.id) as percentage from specializations s';
      const results = await this.connection.query(sql, [userId]);
      return results;
    }

    async findCompletionReportOne(id: number, userId: string) {
      const sql = 'select s.id, get_specialization_percentage(?,s.id) as percentage from specializations s where id = ?';
      const results = await this.connection.query(sql, [userId, id]);
      return results.length > 0 ? results[0] : null;
    }

    async findCourseTopicsCompletionReport(id: number, userId: string) {
      const sql = " SELECT ct.code AS course_id,ct.title AS course_title,ct.no_of_topics, IFNULL(uce.no_of_topics_completed,0) AS topics_completed, ( select name from ct_categories where id = ct.category) as category  FROM ct_courses ct LEFT OUTER JOIN user_course_enrollment uce ON (ct.code = uce.course_id AND uce.user_id = ?) where ct.code in (select course_id from specialization_courses where specialization_id = ?) order by ct.display_order"
      const results = await this.connection.query(sql, [userId, id]);
      return results;
    }


    async mfindCourseTopicsCompletionReport(id: number, userId: string) {
      const sql = " SELECT ct.code AS course_id,ct.title AS course_title,ct.no_of_topics, IFNULL(uce.no_of_topics_completed,0) AS topics_completed, ( select name from ct_categories where id = ct.category) as category  FROM ct_courses ct LEFT OUTER JOIN user_course_enrollment uce ON (ct.code = uce.course_id AND uce.user_id = ?) where ct.code in (select course_id from specialization_courses where specialization_id = ?) order by ct.display_order"
      const results = await this.connection.query(sql, [userId, id]);
      return results;
    }


    async updateTopics() {
      const sql = 'update specializations s set total_topics = IFNULL( SELECT SUM(no_of_topics) FROM ct_courses WHERE CODE IN ( SELECT course_id FROM specialization_courses WHERE specialization_id = s.id )),0)';
      const results = await this.connection.query(sql);
      return results;
    }

    async updateUserTopics() {
      const sql = 'update specializations s set total_topics = ( SELECT SUM(no_of_topics) FROM ct_courses WHERE CODE IN ( SELECT course_id FROM specialization_courses WHERE specialization_id = s.id ))';
      const results = await this.connection.query(sql);
      return results;
    }



  }

Task 4: Create MSpecializationDAO

  import { specializationDoc } from './../schema/specialization.schema';
  import { Injectable, Logger } from "@nestjs/common";
  import { InjectModel } from "@nestjs/mongoose";
  import { Model } from "mongoose";
  import { courseModuleDoc } from "src/schema/course-module.schema";
  import {
      courseTopicDoc,
      CourseTopicSchema,
  } from "src/schema/course-topic.schema";
  import { Course, courseDoc } from "src/schema/course.schema";
  import { Connection } from "typeorm";

  @Injectable()
  export class MSpecializationDAO {
      private readonly logger = new Logger(MSpecializationDAO.name);

      constructor(
          private connection: Connection,
          @InjectModel("Course") private Course: Model<courseDoc>,
          @InjectModel("Specialization") private Specialization: Model<specializationDoc>,
      ) { }

      async save(specializationDTO: any) {
          const specialization = new this.Specialization(specializationDTO);
          return specialization.save();
      }

      async findAll(orgId: string) {
          let specializations: any = await this.Specialization.find({
              orgId: orgId
          }).exec();
          return specializations;
      }

      async delete(id: string) {
          const result = await this.Specialization.deleteOne({ _id: id }).exec();
          return result.deletedCount;
      }

      async findOne(id: string) {
          const result = await this.Specialization.findOne({ _id: id }).exec();
          return result;
      }

      async findByName(name: string) {
          const result = await this.Specialization.findOne({ name: name }).exec();
          return result;
      }

  }