Skip to content
GitLab
Select Git revision
  • getStudentByRollnumber
  • main default
Find file BlameHistoryPermalink
app.service.ts 4.31 KiB
1 
import { Injectable } from '@nestjs/common';
2 
import * as CryptoJS from 'crypto';
3 
import { dbConnector } from './db/db.module';
4 
import { ConnectionPool } from 'mssql';
5 
@Injectable()
6 
export class AppService {
7 
  dbConnection: ConnectionPool = null;
8 
  constructor(private conn: dbConnector) {
9 
    console.log(
10 
      process.env.MSSQL_USER,
11 
      process.env.MSSQL_SERVER,
12 
      process.env.MSSQL_DATABASE,
13 
      process.env.MSSQL_PASSWORD,
14 
      process.env.MSSQL_PORT,
15 
    );
16 
    this.dbConnection = conn.getConnectionPool(
17 
      process.env.MSSQL_USER,
18 
      process.env.MSSQL_SERVER,
19 
      process.env.MSSQL_DATABASE,
20 
      process.env.MSSQL_PASSWORD,
21 
      parseInt(process.env.MSSQL_PORT),
22 
    );
23 
  }
24
25 
  // async mysqlCallStoredProcedure(msg: string) {
26 
  //returns a promise that resolves to a result set on success
27 
  //   const execSql = (statement) => {
28 
  //     const p = new Promise((res, rej) => {
29 
  //       this.conn.query(statement, function (err, result) {
30 
  //         if (err) rej(err);
31 
  //         else res(result);
32 
  //       });
33 
  //     });
34 
  //     return p;
35 
  //   };
36 
  //   const res = await execSql('call getTest()');
37 
  //   return res;
38 
  // }
39
40 
  async mssqlCallStoredProcedure(username: string) {
41 
    //returns a promise that resolves to a result set on success
42 
    const db = await this.dbConnection.connect();
43 
    const res = await db.query(`EXEC get_user  @Username = N'${username}'`);
44 
    console.log(res);
45 
    return { pass: res['recordset'][0]['Password'] };
46 
  }
47
48 
  parseQueryResponse(res) {
49 
    const nres = [];
50 
    res.recordset.forEach((element) => {
51 
      delete element.Password;
52 
      delete element.IsActive;
53 
      delete element.CreatedBy;
54 
      delete element.LastModifiedBy;
55 
      delete element.LastModifiedOn;
56 
      delete element.UserKey;
57 
      delete element.Code;
58 
      delete element.UserType;
59 
      delete element.CreatedOn;
60 
      delete element.PasswordIsModified;
61
62 
      const [firstName, lastName] = element.Name.split(' ');
63 
      element.FirstName = firstName;
64 
      element.lastName = lastName;
65
66 
      const lowerCase = (str) => str[0].toLowerCase() + str.slice(1);
67 
      const nelement = Object.fromEntries(
68 
        Object.entries(element).map(([k, v]) => [lowerCase(k), v]),
69 
      );
70 
      nelement.roles = ['USER'];
7172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140
nres.push(nelement); }); return nres; } async getUsers(search: string, first: number, max: number) { const db = await this.dbConnection.connect(); const res = this.parseQueryResponse( await db.query(`SELECT * FROM upsmfac_casa.dbo.Master_user`), ); return res; } async getUsersCount() { const db = await this.dbConnection.connect(); const res = await db.query( `SELECT COUNT(UserKey) as count FROM Master_user`, ); return res.recordset[0].count; } async getUserById(username: string) { const db = await this.dbConnection.connect(); const res = this.parseQueryResponse( await db.query( `SELECT * FROM upsmfac_casa.dbo.Master_user where username = N'${username}'`, ), ); return res[0]; } async getUserCredentials(username: string) { const db = await this.dbConnection.connect(); const res = await db.query( `SELECT * FROM upsmfac_casa.dbo.Master_user where username = N'${username}'`, ); const saltRounds = 1000; const cred_res = []; res.recordset.forEach((ele) => { // const salt = bcrypt.genSaltSync(saltRounds); const salt = CryptoJS.randomBytes(32); const hash = CryptoJS.pbkdf2Sync( ele.Password, salt, saltRounds, 64, 'sha256', ); // const hash = bcrypt.hashSync(ele.Password, salt); cred_res.push({ value: hash.toString('base64'), salt: salt.toString('base64'), algorithm: 'pbkdf2-sha256', iterations: saltRounds, type: 'password', }); }); return cred_res[0]; } async getTutorById(id: string) { const db = await this.dbConnection.connect(); const res = await db.query( `Select * from upsmfac_casa.dbo.Master_Tutor_Basic_Info where TutorKey='${id}'`, ); return res.recordset[0]; } async getStudentById(id: string) { const db = await this.dbConnection.connect();
141142143144145146147
const res = await db.query( `Select * from upsmfac_casa.dbo.Master_StudentProfile where StudentProfileKey='${id}'`, ); return res.recordset[0]; } }