Press n or j to go to the next uncovered block, b, p or k for the previous block.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 | 1x 1x 1x 1x 1x 1x 19x 19x 19x 19x 19x 19x 19x 19x 19x | import { Getter, inject } from '@loopback/core';
import { BelongsToAccessor, Options, repository } from '@loopback/repository';
import { BaseCrudRepository, CompanyRepository, LicenceRepository, UserRepository } from '.';
import { LogChainDataSource } from '../datasources';
import { Company, CompanyLicence, CompanyLicenceRelations, Licence, User } from '../models';
import { CompanyLicenceDto } from '@logchain/dtos';
import { AnyObject } from '@logchain/types';
export class CompanyLicenceRepository extends BaseCrudRepository<
CompanyLicence,
typeof CompanyLicence.prototype.id,
CompanyLicenceRelations
> {
public readonly creator: BelongsToAccessor<User, typeof User.prototype.id>;
public readonly modifier: BelongsToAccessor<User, typeof User.prototype.id>;
public readonly company: BelongsToAccessor<Company, typeof Company.prototype.id>;
public readonly referrer_company: BelongsToAccessor<Company, typeof Company.prototype.id>;
public readonly licence: BelongsToAccessor<Licence, typeof Licence.prototype.id>;
constructor(
@inject('datasources.logchain') dataSource: LogChainDataSource,
@repository.getter('UserRepository')
protected userRepoGetter: Getter<UserRepository>,
@repository.getter('CompanyRepository')
protected companyRepoGetter: Getter<CompanyRepository>,
@repository.getter('LicenceRepository')
protected licenceRepositoryGetter: Getter<LicenceRepository>,
) {
super(CompanyLicence, dataSource);
// register creator relation
this.creator = this.createBelongsToAccessor('creator', userRepoGetter);
this.modifier = this.createBelongsToAccessor('modifier', userRepoGetter);
this.company = this.createBelongsToAccessor('company', companyRepoGetter);
this.referrer_company = this.createBelongsToAccessor('referrer_company', companyRepoGetter);
this.licence = this.createBelongsToAccessor('licence', licenceRepositoryGetter);
}
async getLicencesHistory(options: any): Promise<CompanyLicenceDto[]> {
const sql = `SELECT
li.id AS licence_id,
li."name" AS licence_name,
cl.licence_rate,
CASE
WHEN (cl.licence_rate = 0) THEN ''::text
WHEN (cl.licence_rate = 1) THEN 'Monthly'::text
ELSE 'Yearly'::text
END AS licence_rate_name,
cl.start_date,
cl.end_date,
cl.status,
CASE
WHEN (cl.status = 1) THEN 'Active'::text
WHEN (cl.status = 2) THEN 'Inactive'::text
ELSE 'Expired'::text
END AS status_name,
uv.name AS uploaded_by,
uv.company_name AS uploaded_by_company,
li.company_referred,
COALESCE(cl.white_collar_users, li.white_collar_users) AS white_collar_users,
COALESCE(cl.blue_collar_users, li.blue_collar_users) AS blue_collar_users,
li.company_referred_white_collar_users,
li.company_referred_blue_collar_users,
li.monthly_rate,
li.yearly_rate,
cl.referrer_company_id
FROM company_licences cl
INNER JOIN companies co ON co.id = cl.company_id
INNER JOIN licences li ON li.id = cl.licence_id
INNER JOIN users_view uv ON uv.id = cl.modified_by
WHERE cl.company_id = $1
ORDER BY cl.id DESC`;
const params = [options.companyId];
return this.dataSource.execute(sql, params, options);
}
/**
* Retrieves a list of company party details along with user status counts for a given company.
*
* @param company_id - The ID of the company to retrieve details for.
* @param options - Optional. Additional options for query execution.
* @returns A promise that resolves to an array of objects, each containing the company party ID,
* company party name, and counts of users with different statuses and collar types.
*/
async getListForCompanyLicenceDetail(company_id: number, options?: Options): Promise<AnyObject[]> {
const rsUsers = await this.execute(
`
SELECT co.id AS company_referred_id, co."name" AS company_referred_name,
SUM(CASE WHEN users.status = 0 THEN 1 ELSE 0 END) AS invite,
SUM(CASE WHEN users.status = 1 THEN 1 ELSE 0 END) AS inactive,
SUM(CASE WHEN users.status = 2 THEN 1 ELSE 0 END) AS active,
SUM(CASE WHEN users.status = 3 THEN 1 ELSE 0 END) AS deleted,
COUNT(users.id) AS total,
SUM(CASE WHEN users.status = 1 AND roles.collar_type = 1 THEN 1 ELSE 0 END) AS blue_inactived,
SUM(CASE WHEN users.status = 2 AND roles.collar_type = 1 THEN 1 ELSE 0 END) AS blue_actived,
SUM(CASE WHEN users.status IN (1,2) AND roles.collar_type = 1 THEN 1 ELSE 0 END) AS blue_total,
SUM(CASE WHEN users.status = 1 AND roles.collar_type = 2 THEN 1 ELSE 0 END) AS white_inactived,
SUM(CASE WHEN users.status = 2 AND roles.collar_type = 2 THEN 1 ELSE 0 END) AS white_actived,
SUM(CASE WHEN users.status IN (1,2) AND roles.collar_type = 2 THEN 1 ELSE 0 END) AS white_total,
MAX(cl.white_collar_users) AS allowed_white_collar_users,
MAX(cl.blue_collar_users) AS allowed_blue_collar_users
FROM users
INNER JOIN roles ON roles.id = users.role_id
INNER JOIN companies_licences_view cl ON cl.company_id = users.company_id
INNER JOIN companies co ON co.id = cl.company_id
WHERE cl.referrer_company_id = $1 AND cl.status = 1
GROUP BY co.id, co."name"
ORDER BY co."name";`, [company_id], options
);
return rsUsers?.length ? rsUsers as AnyObject[] : [];
}
}
|