import { Injectable, Inject, forwardRef } from "@nestjs/common"; import { DataSource } from "typeorm"; import { Appointment } from "./entities/appointment.entity"; import * as crypto from "crypto"; import * as fs from "fs"; import { DynamicDatabaseService } from "src/dynamic_db.service"; import axios from "axios"; const Razorpay = require("razorpay"); const moment = require("moment"); import { EventEmitter2, OnEvent } from "@nestjs/event-emitter"; import { CountDto, UpcomingCountDto, PastCountDto, } from "./dto/appointment.dto"; import { CryptoService } from "src/qr-encrpyt/qr-encrpyt.service"; import * as jwt from "jsonwebtoken"; @Injectable() export class AppointmentService { constructor( private readonly connection: DataSource, private readonly eventEmitter: EventEmitter2, @Inject(forwardRef(() => DynamicDatabaseService)) private readonly dynamicDbService: DynamicDatabaseService, @Inject(forwardRef(() => CryptoService)) private readonly EncryptedService: CryptoService ) { } private privateKey: string; private decrypt(encryptedData: Buffer, privateKey: string): string { const decryptedBuffer = crypto.privateDecrypt( { key: privateKey, padding: crypto.constants.RSA_PKCS1_OAEP_PADDING, oaepHash: "sha256", }, encryptedData ); return decryptedBuffer.toString("utf-8"); } @OnEvent("send-email-sms-appointment-create") handleOrderCreatedEvent(smsData, emailData) { axios.post(process.env.APPT_CREATE_SMS_SEND_API, smsData); axios.post( process.env.APPT_CREATE_EMAIL_SEND_API, emailData ); } @OnEvent("send-email-sms-appointment-update") sendnotificationApptUpdateEvent(verifyData, emailData) { axios.post( process.env.APPT_UPDATE_SMS_SEND_API, verifyData ); axios.post( process.env.APPT_UPDATE_EMAIL_SEND_API, emailData ); } @OnEvent("send-email-sms-appointment-cancel") sendnotificationApptDeleteEvent(verifyData, emailData) { axios.post( process.env.APPT_CANCEL_SMS_SEND_API, verifyData ); axios.post( process.env.APPT_CANCEL_EMAIL_SEND_API, emailData ); } @OnEvent("send-email-sms-temp-appointment-create") sendnotificationTempApptCreateEvent(verifyData, emailData) { axios.post( process.env.TEMP_APPT_SEND_SMS_API, verifyData ); axios.post( process.env.TEMP_APPT_SEND_EMAIL_API, emailData ); } @OnEvent("transfer-razorpay-to-sub-merchant") TransferToSubmerchant(payment_id, getPaymentGatewayDetails) { const razorpay = new Razorpay({ key_id: process.env.APPT_REAZORPAY_KEY_ID, key_secret: process.env.APPT_RAZORPAY_SECRET }); const SubMerchantdetails = JSON.parse( JSON.stringify(getPaymentGatewayDetails.gateway_account_details) ); const submerchantAccountId = SubMerchantdetails.id; const paymentDetails = razorpay.payments.fetch(payment_id); let refndAmt; if (paymentDetails.amount) { if (paymentDetails.amount <= 100) { refndAmt = Math.round(paymentDetails.amount); } else { refndAmt = Math.round( paymentDetails.amount - paymentDetails.amount * 0.052 ); } } const transferPayload = { transfers: [ { account: submerchantAccountId, amount: refndAmt, currency: "INR", notes: { name: "Gaurav Kumar", roll_no: "IEC2011025", }, linked_account_notes: ["roll_no"], on_hold: false, }, ], }; razorpay.payments.transfer(payment_id, transferPayload); } async findAllPast(patient_id: number) { const pastAppoint = await this.connection.query( ` SELECT appointment.id, appointment.module, CASE WHEN appointment.priority = 1 THEN 'Normal' WHEN appointment.priority = 2 THEN 'Urgent' WHEN appointment.priority = 3 THEN 'Emergency' ELSE 'Low' END AS priority, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.hos_appointment_id) appointment_id, staff.id AS doctor_id, CONCAT(staff.name, ' ', staff.surname) AS doctor_name, staff.gender doctor_gender, staff.image, coalesce(visit_details.case_sheet_document,"-")case_sheet_document , hospitals.plenome_id AS hospital_id, hospitals.lattitude, hospitals.longitude, hospitals.hospital_name, hospitals.address, DATE_FORMAT(appointment.date, '%D %b %Y') date, CASE WHEN appointment.shift_id IS NOT NULL AND appointment.module = 'APPOINTMENT' THEN concat(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) WHEN appointment.shift_id IS NULL AND appointment.module = 'APPOINTMENT' THEN concat(DATE_FORMAT(time(hospitals.hospital_opening_timing), '%h:%i %p')," - ",DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) WHEN appointment.module = 'IPD' THEN concat(DATE_FORMAT(time(hospitals.hospital_opening_timing), '%h:%i %p')," - ",DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) WHEN appointment.module = 'OPD' THEN concat(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) ELSE DATE_FORMAT(appointment.time, '%h:%i %p') END time, appointment.appointment_status, appointment.appointment_status_id, appointment_status.color_code, patients.id AS patient_id, GROUP_CONCAT(specialist.specialist_name) AS specialist_names FROM appointment LEFT JOIN staff ON staff.id = appointment.doctor left join doctor_shift on doctor_shift.id = appointment.shift_id LEFT JOIN hospitals on hospitals.plenome_id = appointment.Hospital_id left join visit_details on visit_details.id = appointment.visit_details_id LEFT JOIN hospital_staffs ON hospital_staffs.staff_id = staff.id LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN appointment_status ON appointment_status.id = appointment.appointment_status_id LEFT JOIN specialist ON IF( JSON_VALID(staff.specialist) AND JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON)), 1, 0 ) WHERE (patients.id = ? and (appointment.date < date(now()) or (appointment.appointment_status_id = 4 or appointment.appointment_status_id = 6))) and appointment.is_deleted = 0 GROUP BY id, doctor_id, doctor_name,case_sheet_document,hospital_id, hospital_name, date, time, appointment_status, patient_id,appointment_id,doctor_gender;`, [patient_id] ); return pastAppoint; } async findAllUpcoming(patient_id: number) { try { const pastAppoint = await this.connection.query( ` SELECT appointment.id, appointment.module, CASE WHEN appointment.priority = 1 THEN 'Normal' WHEN appointment.priority = 2 THEN 'Urgent' WHEN appointment.priority = 3 THEN 'Emergency' ELSE 'Low' END AS priority, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.hos_appointment_id) appointment_id, staff.id AS doctor_id, staff.image, CONCAT(staff.name, ' ', staff.surname) AS doctor_name, staff.gender doctor_gender, hospitals.plenome_id AS hospital_id, hospitals.lattitude, hospitals.longitude, hospitals.hospital_name, hospitals.address, DATE_FORMAT(appointment.date, '%D %b %Y') date, CASE WHEN appointment.shift_id IS NOT NULL AND appointment.module = 'APPOINTMENT' THEN concat(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) WHEN appointment.shift_id IS NULL AND appointment.module = 'APPOINTMENT' THEN concat(DATE_FORMAT(time(hospitals.hospital_opening_timing), '%h:%i %p')," - ",DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) WHEN appointment.module = 'IPD' THEN concat(DATE_FORMAT(time(hospitals.hospital_opening_timing), '%h:%i %p')," - ",DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) WHEN appointment.module = 'OPD' THEN concat(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) ELSE DATE_FORMAT(appointment.time, '%h:%i %p') END time, appointment.appointment_status, appointment.appointment_status_id, appointment_status.color_code, patients.id AS patient_id, appointment.live_consult, appointment.global_shift_id, appointment.shift_id, GROUP_CONCAT(specialist.specialist_name) AS specialist_names FROM appointment LEFT JOIN staff ON staff.id = appointment.doctor LEFT JOIN hospitals on hospitals.plenome_id = appointment.Hospital_id left join doctor_shift on doctor_shift.id = appointment.shift_id LEFT JOIN hospital_staffs ON hospital_staffs.staff_id = staff.id LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN appointment_status ON appointment_status.id = appointment.appointment_status_id LEFT JOIN specialist ON IF( JSON_VALID(staff.specialist) AND JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON)), 1, 0 ) WHERE patients.id = ? and appointment.date >= date(now()) and appointment.is_deleted = 0 and (appointment.appointment_status_id <> 4 and appointment.appointment_status_id <> 6) GROUP BY id, doctor_id,doctor_gender, doctor_name, hospital_id, hospital_name, date, time,appointment_id, appointment_status, patient_id ORDER BY appointment.id ASC;`, [patient_id] ); return pastAppoint; } catch (error) { return error; } } async findDoctors(search: string) { let query = `SELECT distinct CONCAT("Dr. ",staff.name, " ", staff.surname) AS doctor_name, staff.id AS doctor_id, staff.image, hospitals.plenome_id AS hospital_id, hospitals.hospital_name, hospitals.lattitude, hospitals.longitude, CONCAT(hospitals.address, ", ", hospitals.district, ", ", hospitals.state, " - ", hospitals.pincode) AS address, coalesce( staff.work_exp,"-") AS experience, coalesce( staff.qualification,"-") qualification, staff_designation.designation AS doctor_designation, COALESCE( GROUP_CONCAT(DISTINCT languages.language),"-") AS languages_known, coalesce( ROUND(((ROUND((SELECT AVG(staff_rating.rating) FROM staff_rating WHERE staff_rating.staff_id = staff.id), 1) / 5) * 5), 0),"-" )AS rating, coalesce( GROUP_CONCAT(DISTINCT specialist.specialist_name) ,"-")AS specialist_names, concat( DATE_FORMAT(time(hospitals.hospital_opening_timing), '%h:%i %p')," - " , DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) timings_shift_id, (SELECT ROUND((charges.standard_charge + (charges.standard_charge * ((tax_category.percentage) / 100))), 2) amount FROM charges JOIN tax_category ON charges.tax_category_id = tax_category.id WHERE charges.id = shift_details.charge_id) amount FROM staff LEFT JOIN languages ON JSON_CONTAINS(staff.languagesKnown, CAST(languages.id AS JSON), '$') = 1 LEFT JOIN staff_roles ON staff.id = staff_roles.staff_id LEFT JOIN doctor_shift ON doctor_shift.staff_id = staff.id left join hospital_staffs on hospital_staffs.staff_id = staff.id left join hospitals on hospitals.plenome_id = hospital_staffs.hospital_id LEFT JOIN shift_details on shift_details.staff_id = staff.id left join charges on charges.id = shift_details.charge_id left join tax_category on charges.tax_category_id = tax_category.id LEFT JOIN staff_designation ON staff_designation.id = staff.staff_designation_id LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1 WHERE staff_roles.role_id = 3 and staff.is_active = 1 and staff.is_deleted = 0 `; const values = []; if (search) { query += ` AND (staff.name LIKE ? OR hospitals.hospital_name LIKE ? OR specialist.specialist_name like ? OR languages.language like ?) GROUP BY doctor_id, doctor_name, doctor_shift.day, doctor_shift.global_shift_id,charge_id,hospital_name, hospital_opening_timing,qualification,hospital_id `; values.push("%" + search + "%"); values.push("%" + search + "%"); values.push("%" + search + "%"); values.push("%" + search + "%"); } else { query += `GROUP BY doctor_id, doctor_name, doctor_shift.day, doctor_shift.global_shift_id,charge_id,hospital_name, hospital_opening_timing,qualification,hospital_id`; } try { const oneDoctors = await this.connection.query(query, values); return oneDoctors; } catch (error) { return error; } } async findOneDoctors(id: number) { let query = `SELECT distinct CONCAT("Dr. ",staff.name, " ", staff.surname," (",staff.employee_id,")") AS doctor_name, staff.id AS doctor_id, staff.image, hospitals.plenome_id AS hospital_id, hospitals.lattitude, hospitals.longitude, hospitals.hospital_name, CONCAT(hospitals.address, ", ", hospitals.district, ", ", hospitals.state, " - ", hospitals.pincode) AS address, coalesce( staff.work_exp,"-") AS experience, coalesce( staff.qualification,"-") qualification, staff_designation.designation AS doctor_designation, COALESCE( GROUP_CONCAT(DISTINCT languages.language),"-") AS languages_known, coalesce( ROUND(((ROUND((SELECT AVG(staff_rating.rating) FROM staff_rating WHERE staff_rating.staff_id = staff.id), 1) / 5) * 5), 0),"-" )AS rating, coalesce( GROUP_CONCAT(DISTINCT specialist.specialist_name) ,"-")AS specialist_names, concat( DATE_FORMAT(time(hospitals.hospital_opening_timing), '%h:%i %p')," - " , DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) timings_shift_id, charges.standard_charge, round((charges.standard_charge * ((tax_category.percentage) / 100)),2) tax, concat(tax_category.percentage,"%") taxPercentage, (SELECT ROUND((charges.standard_charge + (charges.standard_charge * ((tax_category.percentage) / 100))), 2) amount FROM charges JOIN tax_category ON charges.tax_category_id = tax_category.id WHERE charges.id = shift_details.charge_id) amount FROM staff LEFT JOIN languages ON JSON_CONTAINS(staff.languagesKnown, CAST(languages.id AS JSON), '$') = 1 LEFT JOIN staff_roles ON staff.id = staff_roles.staff_id LEFT JOIN doctor_shift ON doctor_shift.staff_id = staff.id left join hospital_staffs on hospital_staffs.staff_id = staff.id left join hospitals on hospitals.plenome_id = hospital_staffs.hospital_id LEFT JOIN shift_details on shift_details.staff_id = staff.id left join charges on charges.id = shift_details.charge_id left join tax_category on charges.tax_category_id = tax_category.id LEFT JOIN staff_designation ON staff_designation.id = staff.staff_designation_id LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1 WHERE staff_roles.role_id = 3 and staff.id = ? GROUP BY doctor_id, doctor_name, doctor_shift.day, doctor_shift.global_shift_id,charge_id,hospital_name,standard_charge, tax, hospital_opening_timing,qualification,hospital_id `; const values = [id]; try { const oneDoctors = await this.connection.query(query, values); return oneDoctors; } catch (error) { return error; } } async convertTo12HourFormat(time) { return moment(time, "HH:mm:ss").format("h:mm A"); } async findOne(appointment_id: number, auth_token: any) { const decoded_value = jwt.decode(auth_token); const token_id = decoded_value.id; const [user_list] = await this.connection.query( "select username from users where id =?", [token_id] ); const user_id = user_list.username; let num = user_id; if (user_id.length === 12) { num = user_id.slice(2); } const get_pat_list = await this.connection.query( `select id from patients where mobileno = ?`, [num] ); const [appoint_patid] = await this.connection.query( `select patient_id from appointment where id = ?`, [appointment_id] ); const patientIds = get_pat_list.map((p: any) => p.id); if (!patientIds.includes(appoint_patid?.patient_id)) { return [ { status: process.env.FAILED_STATUS_ABHA_ADDRESS_MAPPING, message: process.env.APPT_NOT_BELONGED_TO_PATIENT, }, ]; } if (appointment_id) { const [adminHosAppt_id] = await this.connection.query( `select Hospital_id from appointment where id = ?`, [appointment_id] ); try { const [get_base_url] = await this.connection.query( `select phr_api_base_url from hospitals where plenome_id = ?`, [adminHosAppt_id.Hospital_id] ); const response = await axios.get( `${get_base_url.phr_api_base_url}/phr-appointment/getOne/${appointment_id}` ); return response.data; } catch (error) { return error; } } else { return [ { status: process.env.FAILED_STATUS_ABHA_ADDRESS_MAPPING, message: process.env.APPT_ID_MISSING_ERROR, }, ]; } } async findOneQR(appointment_id: string) { if (appointment_id) { const [adminHosAppt_id] = await this.connection.query( `select Hospital_id from appointment where id = ?`, [appointment_id] ); try { const [get_base_url] = await this.connection.query( `select phr_api_base_url from hospitals where plenome_id = ?`, [adminHosAppt_id.Hospital_id] ); const response = await axios.get( `${get_base_url.phr_api_base_url}/phr-appointment/getQR/${appointment_id}` ); return response.data; } catch (error) { return error; } } else { return [ { status: process.env.FAILED_STATUS_ABHA_ADDRESS_MAPPING, message: process.env.APPT_ID_MISSING_ERROR, }, ]; } } async EncryptfindOneQR(appointment_id: string) { if (appointment_id) { const [adminHosAppt_id] = await this.connection.query( `select Hospital_id from appointment where id = ?`, [appointment_id] ); try { const [get_base_url] = await this.connection.query( `select phr_api_base_url from hospitals where plenome_id = ?`, [adminHosAppt_id.Hospital_id] ); const response = await axios.get( `${get_base_url.phr_api_base_url}/phr-appointment/getQR/${appointment_id}` ); delete response.data?.Appointment_details?.patient_name; delete response.data?.Appointment_details?.mobileno; delete response.data?.Appointment_details?.email; delete response.data?.Appointment_details?.ABHA_number; let a = response.data?.Appointment_details; (a.QR_Type_ID = 3), (a.QR_Type = "Appointment_QR"); const encrypt_apicall = await this.EncryptedService.encrypt( JSON.stringify(a), process.env.encryption_key, process.env.encryption_iv ); return encrypt_apicall; } catch (error) { return error; } } else { return [ { status: process.env.FAILED_STATUS_ABHA_ADDRESS_MAPPING, message: process.env.APPT_ID_MISSING_ERROR, }, ]; } } async V2EncryptfindOneQR(appointment_id: string) { if (appointment_id) { const [adminHosAppt_id] = await this.connection.query( `select Hospital_id from appointment where id = ?`, [appointment_id] ); try { const [get_base_url] = await this.connection.query( `select phr_api_base_url from hospitals where plenome_id = ?`, [adminHosAppt_id.Hospital_id] ); const response = await axios.get( `${get_base_url.phr_api_base_url}/phr-appointment/getQR/${appointment_id}` ); delete response.data?.Appointment_details?.patient_name; delete response.data?.Appointment_details?.mobileno; delete response.data?.Appointment_details?.email; delete response.data?.Appointment_details?.ABHA_number; delete response.data?.Appointment_details?.hos_appointment_id; delete response.data?.Appointment_details?.aayush_unique_id; delete response.data?.Appointment_details?.Hospital_id; delete response.data?.Appointment_details?.appointment_id; delete response.data?.Appointment_details?.patient_id; let a = response.data?.Appointment_details; (a.QR_Type_ID = 3), (a.QR_Type = "Appointment_QR"); a.id = a.phr_appointment_id; delete a.phr_appointment_id; const encrypt_apicall = await this.EncryptedService.encrypt( JSON.stringify(a), process.env.encryption_key, process.env.encryption_iv ); return encrypt_apicall; } catch (error) { return error; } } else { return [ { status: process.env.FAILED_STATUS_ABHA_ADDRESS_MAPPING, message: process.env.APPT_ID_MISSING_ERROR, }, ]; } } async update(id: number, AppointmentEntity: Appointment) { const [hos_details] = await this.connection.query( `select phr_api_base_url from hospitals where plenome_id = ?`, [AppointmentEntity.Hospital_id] ); const appointment_booking = await axios.post( `${hos_details.phr_api_base_url}/phr-appointment/${id}`, AppointmentEntity ); return appointment_booking.data; } async remove(id: number) { const [getHosAppt_id] = await this.connection.query( `select Hospital_id,hos_appointment_id,visit_details_id from appointment where id = ?`, [id] ); try { const [get_base_url] = await this.connection.query( `select phr_api_base_url from hospitals where id = ?`, [getHosAppt_id.Hospital_id] ); const response = await axios.delete( `${get_base_url.phr_api_base_url}/phr-appointment/${id}` ); return response.data; } catch (error) { return error; } } async updateCancelApp(id: number, AppointmentEntity: Appointment) { const [hos_details] = await this.connection.query( `select phr_api_base_url from hospitals where plenome_id = ?`, [AppointmentEntity.Hospital_id] ); const appointment_booking = await axios.patch( `${hos_details.phr_api_base_url}/phr-appointment/cancelAppointment/${id}`, AppointmentEntity ); return appointment_booking.data; } async create(AppointmentEntity: Appointment) { const [hos_details] = await this.connection.query( `select phr_api_base_url from hospitals where plenome_id = ?`, [AppointmentEntity.Hospital_id] ); const appointment_booking = await axios.post( `${hos_details.phr_api_base_url}/phr-appointment`, AppointmentEntity ); return appointment_booking.data; } ////////////////////////////////////////////////paagination async findAllPastlist( patient_id: number, limit: number, page: number, filters: string, dateRange: string ): Promise { try { const offset = limit * (page - 1); let query = `SELECT appointment.id, appointment.module, CASE WHEN appointment.priority = 1 THEN 'Normal' WHEN appointment.priority = 2 THEN 'Urgent' WHEN appointment.priority = 3 THEN 'Emergency' ELSE 'Low' END AS priority, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.hos_appointment_id) appointment_id, staff.id AS doctor_id, CONCAT(staff.name, ' ', staff.surname) AS doctor_name, staff.gender doctor_gender, staff.image, coalesce(visit_details.case_sheet_document,"-")case_sheet_document , hospitals.plenome_id AS hospital_id, hospitals.hospital_name, hospitals.lattitude, hospitals.longitude, hospitals.address, DATE_FORMAT(appointment.date, '%D %b %Y') date, CASE WHEN appointment.shift_id IS NOT NULL AND appointment.module = 'APPOINTMENT' THEN concat(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) WHEN appointment.shift_id IS NULL AND appointment.module = 'APPOINTMENT' THEN concat(DATE_FORMAT(time(hospitals.hospital_opening_timing), '%h:%i %p')," - ",DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) WHEN appointment.module = 'IPD' THEN concat(DATE_FORMAT(time(hospitals.hospital_opening_timing), '%h:%i %p')," - ",DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) WHEN appointment.module = 'OPD' THEN concat(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) ELSE DATE_FORMAT(appointment.time, '%h:%i %p') END time, appointment.appointment_status, appointment.appointment_status_id, appointment.live_consult, appointment_status.color_code, patients.id AS patient_id, GROUP_CONCAT(specialist.specialist_name) AS specialist_names FROM appointment LEFT JOIN staff ON staff.id = appointment.doctor left join doctor_shift on doctor_shift.id = appointment.shift_id LEFT JOIN hospitals on hospitals.plenome_id = appointment.Hospital_id left join visit_details on visit_details.id = appointment.visit_details_id LEFT JOIN hospital_staffs ON hospital_staffs.staff_id = staff.id LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN appointment_status ON appointment_status.id = appointment.appointment_status_id LEFT JOIN specialist ON IF( JSON_VALID(staff.specialist) AND JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON)), 1, 0 ) WHERE (patients.id = ${patient_id} and (appointment.date < date(now()) or (appointment.appointment_status_id = 4 or appointment.appointment_status_id = 6))) and appointment.is_deleted = 0 `; if (filters) { const filterArray = filters.split("|"); for (const filterItem of filterArray) { const [key, value] = filterItem.split(":"); if (key.trim() == "appointment_status_id") { const ids = value .split(",") .map((id) => id.trim()) .filter(Boolean); if (ids.length > 0) { const conditions = ids .map((id) => ` appointment.appointment_status_id = ${id} `) .join(" OR "); query += ` AND (${conditions}) `; } } if (key.trim() == "visit_type") { if (value.trim() == "at_home") { query += ` AND appointment.live_consult = 'yes' `; } else if (value.trim() == "at_clinic") { query += ` AND appointment.live_consult <> 'yes' `; } } } } if (dateRange) { const filterArray = dateRange.split("to"); if (filterArray.length > 1) { const startDate = filterArray[0].trim(); const endDate = filterArray[1].trim(); query += ` AND DATE(appointment.date) BETWEEN DATE('${startDate}') AND DATE('${endDate}') `; } else if (filterArray.length == 1) { const startDate = filterArray[0].trim(); query += ` AND DATE(appointment.date) = DATE('${startDate}') `; } } let groupBy = ` GROUP BY id, doctor_id, doctor_name,case_sheet_document,hospital_id, hospital_name, date, time, appointment_status, patient_id,appointment_id,doctor_gender ORDER BY appointment.date DESC LIMIT ${limit} OFFSET ${offset} `; let finalQuery = query + groupBy; const pastAppoint = await this.connection.query(finalQuery); let [total] = await this.connection .query(`select count(id) as total from appointment where (appointment.patient_id = ${patient_id} and (appointment.date < date(now()) or (appointment.appointment_status_id = 4 or appointment.appointment_status_id = 6))) and appointment.is_deleted = 0 `); let past_out = { details: pastAppoint, total: total.total, }; return past_out; } catch (error) { return error; } } async findAllUpcominglist( patient_id: number, limit: number, page: number, filters: string, dateRange: string ): Promise { try { const offset = limit * (page - 1); let query = `SELECT appointment.id, appointment.module, CASE WHEN appointment.priority = 1 THEN 'Normal' WHEN appointment.priority = 2 THEN 'Urgent' WHEN appointment.priority = 3 THEN 'Emergency' ELSE 'Low' END AS priority, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.hos_appointment_id) appointment_id, staff.id AS doctor_id, staff.image, CONCAT(staff.name, ' ', staff.surname) AS doctor_name, staff.gender doctor_gender, hospitals.plenome_id AS hospital_id, hospitals.hospital_name, hospitals.address, hospitals.lattitude, hospitals.longitude, DATE_FORMAT(appointment.date, '%D %b %Y') date, CASE WHEN appointment.shift_id IS NOT NULL AND appointment.module = 'APPOINTMENT' THEN concat(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) WHEN appointment.shift_id IS NULL AND appointment.module = 'APPOINTMENT' THEN concat(DATE_FORMAT(time(hospitals.hospital_opening_timing), '%h:%i %p')," - ",DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) WHEN appointment.module = 'IPD' THEN concat(DATE_FORMAT(time(hospitals.hospital_opening_timing), '%h:%i %p')," - ",DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) WHEN appointment.module = 'OPD' THEN concat(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) ELSE DATE_FORMAT(appointment.time, '%h:%i %p') END time, appointment.appointment_status, appointment.appointment_status_id, appointment_status.color_code, patients.id AS patient_id, appointment.live_consult, appointment.global_shift_id, appointment.shift_id, GROUP_CONCAT(specialist.specialist_name) AS specialist_names FROM appointment LEFT JOIN staff ON staff.id = appointment.doctor LEFT JOIN hospitals on hospitals.plenome_id = appointment.Hospital_id left join doctor_shift on doctor_shift.id = appointment.shift_id LEFT JOIN hospital_staffs ON hospital_staffs.staff_id = staff.id LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN appointment_status ON appointment_status.id = appointment.appointment_status_id LEFT JOIN specialist ON IF( JSON_VALID(staff.specialist) AND JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON)), 1, 0 ) WHERE patients.id = ${patient_id} and appointment.date >= date(now()) and appointment.is_deleted = 0 and (appointment.appointment_status_id <> 4 and appointment.appointment_status_id <> 6) `; if (filters) { const filterArray = filters.split("|"); for (const filterItem of filterArray) { const [key, value] = filterItem.split(":"); if (key.trim() == "appointment_status_id") { const ids = value .split(",") .map((id) => id.trim()) .filter(Boolean); if (ids.length > 0) { const conditions = ids .map((id) => `appointment.appointment_status_id = ${id}`) .join(" OR "); query += ` AND (${conditions}) `; } } if (key.trim() == "visit_type") { if (value.trim() == "at_home") { query += ` AND appointment.live_consult = 'yes' `; } else if (value.trim() == "at_clinic") { query += ` AND appointment.live_consult <> 'yes' `; } } } } if (dateRange) { const filterArray = dateRange.split("to"); if (filterArray.length > 1) { const startDate = filterArray[0].trim(); const endDate = filterArray[1].trim(); query += ` AND DATE(appointment.date) BETWEEN DATE('${startDate}') AND DATE('${endDate}') `; } else if (filterArray.length == 1) { const startDate = filterArray[0].trim(); query += ` AND DATE(appointment.date) = DATE('${startDate}') `; } } let groupBy = ` GROUP BY id, doctor_id,doctor_gender, doctor_name, hospital_id, hospital_name, date, time,appointment_id, appointment_status, patient_id ORDER BY appointment.date ASC LIMIT ${limit} OFFSET ${offset} `; let finalQuery = query + groupBy; const upcoming_list = await this.connection.query(finalQuery); let [total_list] = await this.connection .query(` select count(id) as total from appointment where appointment.patient_id = ${patient_id} and appointment.date >= date(now()) and appointment.is_deleted = 0 and (appointment.appointment_status_id <> 4 and appointment.appointment_status_id <> 6) `); let result = { details: upcoming_list, total: total_list.total, }; return result; } catch (error) { return error; } } async findDoctorslist( search: string, limit: number, page: number ): Promise { const offset = limit * (page - 1); let query = `SELECT distinct CONCAT("Dr. ",staff.name, " ", staff.surname) AS doctor_name, staff.id AS doctor_id, staff.image, hospitals.plenome_id AS hospital_id, hospitals.hospital_name, CONCAT(hospitals.address, ", ", hospitals.district, ", ", hospitals.state, " - ", hospitals.pincode) AS address, coalesce( staff.work_exp,"-") AS experience, coalesce( staff.qualification,"-") qualification, staff_designation.designation AS doctor_designation, COALESCE( GROUP_CONCAT(DISTINCT languages.language),"-") AS languages_known, coalesce( ROUND(((ROUND((SELECT AVG(staff_rating.rating) FROM staff_rating WHERE staff_rating.staff_id = staff.id), 1) / 5) * 5), 0),"-" )AS rating, coalesce( GROUP_CONCAT(DISTINCT specialist.specialist_name) ,"-")AS specialist_names, concat( DATE_FORMAT(time(hospitals.hospital_opening_timing), '%h:%i %p')," - " , DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) timings_shift_id, (SELECT ROUND((charges.standard_charge + (charges.standard_charge * ((tax_category.percentage) / 100))), 2) amount FROM charges JOIN tax_category ON charges.tax_category_id = tax_category.id WHERE charges.id = shift_details.charge_id) amount FROM staff LEFT JOIN languages ON JSON_CONTAINS(staff.languagesKnown, CAST(languages.id AS JSON), '$') = 1 LEFT JOIN staff_roles ON staff.id = staff_roles.staff_id LEFT JOIN doctor_shift ON doctor_shift.staff_id = staff.id left join hospital_staffs on hospital_staffs.staff_id = staff.id left join hospitals on hospitals.plenome_id = hospital_staffs.hospital_id LEFT JOIN shift_details on shift_details.staff_id = staff.id left join charges on charges.id = shift_details.charge_id left join tax_category on charges.tax_category_id = tax_category.id LEFT JOIN staff_designation ON staff_designation.id = staff.staff_designation_id LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1 WHERE staff_roles.role_id = 3 and staff.is_active = 1 and staff.is_deleted = 0 `; const values = []; if (search) { query += ` AND (staff.name LIKE ? OR hospitals.hospital_name LIKE ? OR specialist.specialist_name like ? OR languages.language like ?) GROUP BY doctor_id, doctor_name, doctor_shift.day, doctor_shift.global_shift_id,charge_id,hospital_name, hospital_opening_timing,qualification,hospital_id limit ? offset ? `; values.push("%" + search + "%"); values.push("%" + search + "%"); values.push("%" + search + "%"); values.push("%" + search + "%"); values.push(limit, offset); } else { query += `GROUP BY doctor_id, doctor_name, doctor_shift.day, doctor_shift.global_shift_id,charge_id,hospital_name, hospital_opening_timing,qualification,hospital_id limit ? offset ?`; values.push(limit, offset); } try { const oneDoctors = await this.connection.query(query, values); let total_query = `select staff.name,count(distinct staff.id) as total from staff LEFT JOIN staff_roles ON staff.id = staff_roles.staff_id left join hospital_staffs on hospital_staffs.staff_id = staff.id left join hospitals on hospitals.plenome_id = hospital_staffs.hospital_id LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1 LEFT JOIN languages ON JSON_CONTAINS(staff.languagesKnown, CAST(languages.id AS JSON), '$') = 1 where staff_roles.role_id = 3 and staff.is_active = 1 and staff.is_deleted = 0 `; if (search) { total_query += `AND (staff.name LIKE ? OR hospitals.hospital_name LIKE ? OR specialist.specialist_name like ? OR languages.language like ?)`; } let [total_hos] = await this.connection.query(total_query, [ "%" + search + "%", "%" + search + "%", "%" + search + "%", "%" + search + "%", ]); let out = { details: oneDoctors, total: total_hos.total, }; return out; } catch (error) { return error; } } async findLastOneAppointment( patient_id: number, limit: number, page: number ): Promise { try { const offset = limit * (page - 1); let query = `SELECT appointment.id, appointment.module, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.hos_appointment_id) appointment_id, staff.id AS doctor_id, CONCAT(staff.name, ' ', staff.surname) AS doctor_name, staff.gender doctor_gender, staff.image, coalesce(visit_details.case_sheet_document,"-")case_sheet_document , hospitals.plenome_id AS hospital_id, hospitals.hospital_name, hospitals.address, DATE_FORMAT(appointment.date, '%D %b %Y') date, CASE WHEN appointment.shift_id IS NOT NULL AND appointment.module = 'APPOINTMENT' THEN concat(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) WHEN appointment.shift_id IS NULL AND appointment.module = 'APPOINTMENT' THEN concat(DATE_FORMAT(time(hospitals.hospital_opening_timing), '%h:%i %p')," - ",DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) WHEN appointment.module = 'IPD' THEN concat(DATE_FORMAT(time(hospitals.hospital_opening_timing), '%h:%i %p')," - ",DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) WHEN appointment.module = 'OPD' THEN concat(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) ELSE DATE_FORMAT(appointment.time, '%h:%i %p') END time, appointment.appointment_status, appointment.appointment_status_id, appointment_status.color_code, patients.id AS patient_id, GROUP_CONCAT(specialist.specialist_name) AS specialist_names FROM appointment LEFT JOIN staff ON staff.id = appointment.doctor left join doctor_shift on doctor_shift.id = appointment.shift_id LEFT JOIN hospitals on hospitals.plenome_id = appointment.Hospital_id left join visit_details on visit_details.id = appointment.visit_details_id LEFT JOIN hospital_staffs ON hospital_staffs.staff_id = staff.id LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN appointment_status ON appointment_status.id = appointment.appointment_status_id LEFT JOIN specialist ON IF( JSON_VALID(staff.specialist) AND JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON)), 1, 0 ) WHERE patients.id = ${patient_id} and appointment.is_deleted = 0 `; let groupBy = ` GROUP BY id, doctor_id, doctor_name,case_sheet_document,hospital_id, hospital_name, date, time, appointment_status, patient_id,appointment_id,doctor_gender ORDER BY appointment.id DESC LIMIT ${limit} OFFSET ${offset} `; let finalQuery = query + groupBy; const pastAppoint = await this.connection.query(finalQuery); let [total] = await this.connection .query(`select count(id) as total from appointment where (appointment.patient_id = ${patient_id} and (appointment.date < date(now()) or (appointment.appointment_status_id = 4 or appointment.appointment_status_id = 6))) and appointment.is_deleted = 0 `); let past_out = { details: pastAppoint, total: total.total, }; return past_out; } catch (error) { return error; } } }