GetPayrollElementsQueryHandler.execute - Code Metrics - Inspection of "Add payroll elements generator" - fairnesscoop/permacoop - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#220)

unknown

created 2021-11-22 12:14 UTC

GetPayrollElementsQueryHandler.execute B

↳ Parent: GetPayrollElementsQueryHandler

Complexity

Conditions

Size

Total Lines	118
Code Lines	41

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	41
dl	0
loc	118
c	0
b	0
f	0
rs	8.896
cc	1

How to fix Long Method

import { Inject } from '@nestjs/common';
import { IQueryBus, QueryHandler } from '@nestjs/cqrs';
import { InjectEntityManager } from '@nestjs/typeorm';
import { EntityManager } from 'typeorm';
import { IUserRepository } from 'src/Domain/HumanResource/User/Repository/IUserRepository';
import { IUserAdministrativeRepository } from 'src/Domain/HumanResource/User/Repository/IUserAdministrativeRepository';
import { LeaveView, PayrollElementsView } from '../View/PayrollElementsView';
import { GetPayrollElementsQuery } from './GetPayrollElementsQuery';
import { MealTicketSummaryView } from '../../MealTicket/Views/MealTicketSummaryView';
import { CountMealTicketPerMonthQuery } from '../../MealTicket/Query/CountMealTicketPerMonthQuery';

@QueryHandler(GetPayrollElementsQuery)
export class GetPayrollElementsQueryHandler {
  constructor(
    @InjectEntityManager()
    private readonly manager: EntityManager,
    @Inject('IQueryBus')
    private readonly queryBus: IQueryBus,
    @Inject('IUserRepository')
    private readonly userRepository: IUserRepository,
    @Inject('IUserAdministrativeRepository')
    private readonly userAdministrativeRepository: IUserAdministrativeRepository
  ) {}

  private async getUserMealTickets(): Promise<Map<string, number>> {
    const currentDate = new Date();

    // NOTE: this code suffers from the "N+1 queries" problem, as we fetch the meal tickets count
    // for each user. But we wanted to reuse the existing meal ticket calculation logic,
    // which exists in JavaScript code.
    // Ideally we would move the meal ticket calculation logic to the database.

    const mealTicketsByUserId = new Map<string, number>();

    const _users = await this.userRepository.findUsers(false);

    for (let user of _users) {
      const userAdministrative = await this.userAdministrativeRepository.findOneByUserId(
        user.getId()
      );

      if (userAdministrative.getLeavingDate() !== null) {
        // User is not working here anymore, don't include them in the payroll elements.
        continue;
      }

      const monthSummaries: MealTicketSummaryView[] = await this.queryBus.execute(
        new CountMealTicketPerMonthQuery(user, currentDate)
      );

      const thisMonthSummary = monthSummaries.find(
        view => view.month === currentDate.getMonth()
      );

      mealTicketsByUserId.set(user.getId(), thisMonthSummary.total);
    }

    return mealTicketsByUserId;
  }

  public async execute(
    _: GetPayrollElementsQuery
  ): Promise<PayrollElementsView[]> {
    const sql = `
SELECT
  "user".id AS "userId",
  "firstName",
  "lastName",
  "executivePosition",
  "joiningDate",
  "leavingDate",
  "annualEarnings",
  ("annualEarnings" / 12) AS "monthlyEarnings",
  "workingTime",
  "transportFee",
  "healthInsurance",
  (
    SELECT
      COALESCE(SUM(leave.time / 60.0 / 7.0), 0)
    FROM leave_request
    INNER JOIN leave ON leave."leaveRequestId" = leave_request.id
    INNER JOIN "user" AS u ON u.id = leave_request."userId" AND u.id = "user".id
    WHERE leave_request.status = 'accepted'
    AND leave_request.type = 'paid'
    AND u.id = "user".id
  ) AS "totalPaidLeaves",
  (
    SELECT
      COALESCE(SUM(leave.time / 60.0 / 7.0), 0)
    FROM leave_request
    INNER JOIN leave ON leave."leaveRequestId" = leave_request.id
    INNER JOIN "user" AS u ON u.id = leave_request."userId" AND u.id = "user".id
    WHERE leave_request.status = 'accepted'
    AND leave_request.type = 'unpaid'
  ) AS "totalUnpaidLeaves",
  (
    SELECT
      COALESCE(SUM(leave.time / 60.0 / 7.0), 0)
    FROM leave_request
    INNER JOIN leave ON leave."leaveRequestId" = leave_request.id
    INNER JOIN "user" AS u ON u.id = leave_request."userId" AND u.id = "user".id
    WHERE leave_request.status = 'accepted'
    AND leave_request.type = 'medical'
  ) AS "totalMedicalLeaves",
  (
    SELECT
      COALESCE(SUM(leave.time / 60.0 / 7.0), 0)
    FROM leave_request
    INNER JOIN leave ON leave."leaveRequestId" = leave_request.id
    INNER JOIN "user" AS u ON u.id = leave_request."userId" AND u.id = "user".id
    WHERE leave_request.status = 'accepted'
    AND leave_request.type = 'special'
  ) AS "totalSpecialLeaves"
FROM "user"
INNER JOIN user_administrative AS user_a ON "user"."userAdministrativeId" = user_a.id
WHERE user_a."leavingDate" IS NULL;
    `;

    const rows = await this.manager.query(sql);

    const leaveSql = `
SELECT
  "user".id AS "userId",
  "startDate",
  "endDate"
FROM leave_request
INNER JOIN "user" ON "user".id = leave_request."userId"
WHERE leave_request.status = 'accepted'
ORDER BY "startDate";
    `;

    const leaveRows = await this.manager.query(leaveSql);

    const mealTicketsByUserId = await this.getUserMealTickets();

    return rows.map(
      ({
        userId,
        firstName,
        lastName,
        executivePosition,
        joiningDate,
        leavingDate,
        annualEarnings,
        monthlyEarnings,
        workingTime,
        transportFee,
        healthInsurance,
        totalPaidLeaves,
        totalUnpaidLeaves,
        totalMedicalLeaves,
        totalSpecialLeaves
      }) => {
        const leaves = leaveRows
          .filter(({ userId: leaveUserId }) => leaveUserId === userId)
          .map(({ startDate, endDate }) => new LeaveView(startDate, endDate));

        const mealTickets = mealTicketsByUserId.get(userId);

        return new PayrollElementsView(
          userId,
          firstName,
          lastName,
          executivePosition,
          joiningDate,
          leavingDate,
          +annualEarnings * 0.01,
          +monthlyEarnings * 0.01,
          workingTime,
          +transportFee * 0.01,
          mealTickets,
          healthInsurance,
          +totalPaidLeaves,
          +totalUnpaidLeaves,
          +totalMedicalLeaves,
          +totalSpecialLeaves,
          leaves
        );
      }
    );
  }
}


1			import { Inject } from '@nestjs/common';
2			import { IQueryBus, QueryHandler } from '@nestjs/cqrs';
3			import { InjectEntityManager } from '@nestjs/typeorm';
4			import { EntityManager } from 'typeorm';
5			import { IUserRepository } from 'src/Domain/HumanResource/User/Repository/IUserRepository';
6			import { IUserAdministrativeRepository } from 'src/Domain/HumanResource/User/Repository/IUserAdministrativeRepository';
7			import { LeaveView, PayrollElementsView } from '../View/PayrollElementsView';
8			import { GetPayrollElementsQuery } from './GetPayrollElementsQuery';
9			import { MealTicketSummaryView } from '../../MealTicket/Views/MealTicketSummaryView';
10			import { CountMealTicketPerMonthQuery } from '../../MealTicket/Query/CountMealTicketPerMonthQuery';
11
12			@QueryHandler(GetPayrollElementsQuery)
13			export class GetPayrollElementsQueryHandler {
14			constructor(
15			@InjectEntityManager()
16			private readonly manager: EntityManager,
17			@Inject('IQueryBus')
18			private readonly queryBus: IQueryBus,
19			@Inject('IUserRepository')
20			private readonly userRepository: IUserRepository,
21			@Inject('IUserAdministrativeRepository')
22			private readonly userAdministrativeRepository: IUserAdministrativeRepository
23			) {}
24
25			private async getUserMealTickets(): Promise<Map<string, number>> {
26			const currentDate = new Date();
27
28			// NOTE: this code suffers from the "N+1 queries" problem, as we fetch the meal tickets count
29			// for each user. But we wanted to reuse the existing meal ticket calculation logic,
30			// which exists in JavaScript code.
31			// Ideally we would move the meal ticket calculation logic to the database.
32
33			const mealTicketsByUserId = new Map<string, number>();
34
35			const _users = await this.userRepository.findUsers(false);
36
37			for (let user of _users) {
38			const userAdministrative = await this.userAdministrativeRepository.findOneByUserId(
39			user.getId()
40			);
41
42			if (userAdministrative.getLeavingDate() !== null) {
43			// User is not working here anymore, don't include them in the payroll elements.
44			continue;
45			}
46
47			const monthSummaries: MealTicketSummaryView[] = await this.queryBus.execute(
48			new CountMealTicketPerMonthQuery(user, currentDate)
49			);
50
51			const thisMonthSummary = monthSummaries.find(
52			view => view.month === currentDate.getMonth()
53			);
54
55			mealTicketsByUserId.set(user.getId(), thisMonthSummary.total);
56			}
57
58			return mealTicketsByUserId;
59			}
60
61			public async execute(
62			_: GetPayrollElementsQuery
63			): Promise<PayrollElementsView[]> {
64			const sql = `
65			SELECT
66			"user".id AS "userId",
67			"firstName",
68			"lastName",
69			"executivePosition",
70			"joiningDate",
71			"leavingDate",
72			"annualEarnings",
73			("annualEarnings" / 12) AS "monthlyEarnings",
74			"workingTime",
75			"transportFee",
76			"healthInsurance",
77			(
78			SELECT
79			COALESCE(SUM(leave.time / 60.0 / 7.0), 0)
80			FROM leave_request
81			INNER JOIN leave ON leave."leaveRequestId" = leave_request.id
82			INNER JOIN "user" AS u ON u.id = leave_request."userId" AND u.id = "user".id
83			WHERE leave_request.status = 'accepted'
84			AND leave_request.type = 'paid'
85			AND u.id = "user".id
86			) AS "totalPaidLeaves",
87			(
88			SELECT
89			COALESCE(SUM(leave.time / 60.0 / 7.0), 0)
90			FROM leave_request
91			INNER JOIN leave ON leave."leaveRequestId" = leave_request.id
92			INNER JOIN "user" AS u ON u.id = leave_request."userId" AND u.id = "user".id
93			WHERE leave_request.status = 'accepted'
94			AND leave_request.type = 'unpaid'
95			) AS "totalUnpaidLeaves",
96			(
97			SELECT
98			COALESCE(SUM(leave.time / 60.0 / 7.0), 0)
99			FROM leave_request
100			INNER JOIN leave ON leave."leaveRequestId" = leave_request.id
101			INNER JOIN "user" AS u ON u.id = leave_request."userId" AND u.id = "user".id
102			WHERE leave_request.status = 'accepted'
103			AND leave_request.type = 'medical'
104			) AS "totalMedicalLeaves",
105			(
106			SELECT
107			COALESCE(SUM(leave.time / 60.0 / 7.0), 0)
108			FROM leave_request
109			INNER JOIN leave ON leave."leaveRequestId" = leave_request.id
110			INNER JOIN "user" AS u ON u.id = leave_request."userId" AND u.id = "user".id
111			WHERE leave_request.status = 'accepted'
112			AND leave_request.type = 'special'
113			) AS "totalSpecialLeaves"
114			FROM "user"
115			INNER JOIN user_administrative AS user_a ON "user"."userAdministrativeId" = user_a.id
116			WHERE user_a."leavingDate" IS NULL;
117			`;
118
119			const rows = await this.manager.query(sql);
120
121			const leaveSql = `
122			SELECT
123			"user".id AS "userId",
124			"startDate",
125			"endDate"
126			FROM leave_request
127			INNER JOIN "user" ON "user".id = leave_request."userId"
128			WHERE leave_request.status = 'accepted'
129			ORDER BY "startDate";
130			`;
131
132			const leaveRows = await this.manager.query(leaveSql);
133
134			const mealTicketsByUserId = await this.getUserMealTickets();
135
136			return rows.map(
137			({
138			userId,
139			firstName,
140			lastName,
141			executivePosition,
142			joiningDate,
143			leavingDate,
144			annualEarnings,
145			monthlyEarnings,
146			workingTime,
147			transportFee,
148			healthInsurance,
149			totalPaidLeaves,
150			totalUnpaidLeaves,
151			totalMedicalLeaves,
152			totalSpecialLeaves
153			}) => {
154			const leaves = leaveRows
155			.filter(({ userId: leaveUserId }) => leaveUserId === userId)
156			.map(({ startDate, endDate }) => new LeaveView(startDate, endDate));
157
158			const mealTickets = mealTicketsByUserId.get(userId);
159
160			return new PayrollElementsView(
161			userId,
162			firstName,
163			lastName,
164			executivePosition,
165			joiningDate,
166			leavingDate,
167			+annualEarnings * 0.01,
168			+monthlyEarnings * 0.01,
169			workingTime,
170			+transportFee * 0.01,
171			mealTickets,
172			healthInsurance,
173			+totalPaidLeaves,
174			+totalUnpaidLeaves,
175			+totalMedicalLeaves,
176			+totalSpecialLeaves,
177			leaves
178			);
179			}
180			);
181			}
182			}
183

fairnesscoop / permacoop

Pull Request — master (#220)

GetPayrollElementsQueryHandler.execute B

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like