import { forwardRef, Inject, Injectable } from '@nestjs/common'; import { EventEmitter2 } from '@nestjs/event-emitter'; import { InjectDataSource } from '@nestjs/typeorm'; import axios from 'axios'; const Razorpay = require('razorpay'); import { DataSource } from 'typeorm'; import { customAlphabet } from 'nanoid'; import { PostAppointment, StatusChangePatch, UpdateAppointment, UpdateAppointmentcharge, CancelAppointment, AddAppointmentPayment, } from './entities/op-hub-appointment.entity'; import { upcomingApptCountResponseDto } from './dto/create-op-hub-appointment.dto'; import { FaceAuthService } from 'src/face-auth/face-auth.service'; import { TransactionSplitService } from 'src/transaction_split/transaction_split.service'; import { NewBillingService } from 'src/new-billing/new-billing.service'; import { DeductDiscountDto } from './dto/deduct_discount.dto'; @Injectable() export class OpHubAppointmentService { constructor( private readonly dynamicConnection: DataSource, @InjectDataSource('AdminConnection') private readonly connection: DataSource, private readonly eventEmitter: EventEmitter2, @Inject(forwardRef(() => FaceAuthService)) private readonly addAppointmentService: FaceAuthService, @Inject(forwardRef(() => TransactionSplitService)) private readonly TransactionShareService: TransactionSplitService, private readonly newBillingService: NewBillingService, ) { } async TransferToSubmerchant( payment_id, getPaymentGatewayDetails, hospital_id, patient_aayush_id, admin_transaction_id, hos_transaction_id, ) { try { const razorpay = new Razorpay({ key_id: process.env.RAZORPAY_KEY_ID, key_secret: process.env.RAZORPAY_KEY_SECRET, }); const SubMerchantdetails = JSON.parse( JSON.stringify(getPaymentGatewayDetails.gateway_account_details), ); const submerchantAccountId = SubMerchantdetails.id; const paymentDetails = await 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, }, ], }; console.log('adfasdfadfadf', transferPayload); await razorpay.payments.transfer(payment_id, transferPayload); const overallamount = paymentDetails.amount; const razorpayAmount = paymentDetails.amount * 0.02; const organisation_share = paymentDetails.amount - refndAmt - razorpayAmount; const split_req_body = { hospital_id: hospital_id, patient_aayush_id: patient_aayush_id, admin_transaction_id: admin_transaction_id, hos_transaction_id: hos_transaction_id, hospital_share: refndAmt, organisation_share: organisation_share, overallpaid: overallamount, razorpay_share: razorpayAmount, created_at: new Date(), }; console.log(split_req_body, 'split_req_body'); const result = await this.TransactionShareService.create(split_req_body); } catch (error) { console.log(error, 'haraeee ohh sambo'); } } async create(AppointmentEntity: PostAppointment) { if (!AppointmentEntity.Hospital_id) { return { status: 'failed', message: 'Enter Hospital_id to book appointment', }; } if (!AppointmentEntity.txn_id) { AppointmentEntity.txn_id = 'NA'; } if (!AppointmentEntity.bank_ref_id) { AppointmentEntity.bank_ref_id = 'NA'; } if (!AppointmentEntity.pg_ref_id) { AppointmentEntity.pg_ref_id = 'NA'; } if (AppointmentEntity.time) { try { const timeString = AppointmentEntity.time; const startTime = timeString.split(' - ')[0]; AppointmentEntity.time = startTime; } catch (error) { AppointmentEntity.time = AppointmentEntity.time; } } const currentDate = new Date(); const formattedDate = currentDate.toISOString().split('T')[0]; let position; let apptDetails: any; try { const [check_duplicate] = await this.dynamicConnection.query( `select * from appointment where patient_id = ? and doctor = ? and shift_id = ? and date = ? and appointment_status_id <> 4`, [ AppointmentEntity.patient_id, AppointmentEntity.doctor, AppointmentEntity.shift_id, AppointmentEntity.date, ], ); if (!check_duplicate) { let appointment_status; let appointment_status_id; if (AppointmentEntity.date == formattedDate) { appointment_status_id = 3; appointment_status = 'Approved'; } else { appointment_status_id = 2; appointment_status = 'Reserved'; } const HosPatient = await this.dynamicConnection.query( `select * from patients where id = ?`, [AppointmentEntity.patient_id], ); const patientInAdmin = await this.connection.query( `select * from patients where aayush_unique_id = ?`, [HosPatient[0].aayush_unique_id], ); let AdminPatientId; if (patientInAdmin[0]) { AdminPatientId = patientInAdmin[0].id; } else { let timestamp: any; if (HosPatient[0].dob) { const dateString = HosPatient[0].dob; const dateObject = new Date(dateString); timestamp = new Date( dateObject.getFullYear(), dateObject.getMonth(), dateObject.getDate(), ); } let faceID = null; if (HosPatient[0].image && HosPatient[0].image.trim() != '') { const getFaceId = await this.addAppointmentService.getfaceID( HosPatient[0].image, ); faceID = getFaceId?.faceID; } const createPatient = await this.connection.query( `insert into patients ( patient_name, dob, image, faceId, mobileno, email, gender, address, ABHA_number, dial_code, blood_bank_product_id, aayush_unique_id ) values (?,?,?,?,?,?,?,?,?,?,?,?)`, [ HosPatient[0].patient_name, timestamp, HosPatient[0].image, faceID, HosPatient[0].mobileno, HosPatient[0].email, HosPatient[0].gender, HosPatient[0].address, HosPatient[0].ABHA_number, HosPatient[0].dial_code, HosPatient[0].blood_bank_product_id, HosPatient[0].aayush_unique_id, ], ); AdminPatientId = createPatient.insertId; } const [staffEmailHos] = await this.dynamicConnection.query( `select email from staff where id = ?`, [AppointmentEntity.doctor], ); const [AdminStaff] = await this.connection.query( `select id from staff where email = ?`, [staffEmailHos.email], ); let AdminStaffId = AdminStaff.id; let paymentStatus; if ( AppointmentEntity.payment_mode == 'Paylater' || AppointmentEntity.payment_mode == 'Offline' || AppointmentEntity.payment_mode == 'cheque' || AppointmentEntity.payment_mode == 'offline' ) { paymentStatus = 'unpaid'; } else { paymentStatus = 'paid'; } // if (AppointmentEntity.payment_mode) { // if ( // AppointmentEntity.payment_mode.toLocaleLowerCase() != 'cash' && // AppointmentEntity.payment_mode.toLocaleLowerCase() != 'cheque' && // AppointmentEntity.payment_mode.toLocaleLowerCase() != 'offline' // ) { // if (!AppointmentEntity.payment_gateway) { // return { // status: 'failed', // message: 'enter payment gateway to book appointment', // }; // } // if ( // AppointmentEntity.payment_gateway.toLocaleLowerCase() == // 'razorpay' // ) { // const razorpay = new Razorpay({ // key_id: process.env.RAZORPAY_KEY_ID, // key_secret: process.env.RAZORPAY_KEY_SECRET, // }); // try { // const [getPaymentGatewayDetails] = await this.connection.query( // `select * from hospital_payment_gateway_details where hospital_id = ? and payment_gateway = ?`, // [AppointmentEntity.Hospital_id, 'razorpay'], // ); // const SubMerchantdetails = JSON.parse( // JSON.stringify( // getPaymentGatewayDetails.gateway_account_details, // ), // ); // const submerchantAccountId = SubMerchantdetails.id; // const paymentDetails = await razorpay.payments.fetch( // AppointmentEntity.payment_id, // ); // let refndAmt; // if (paymentDetails.amount) { // if (paymentDetails.amount <= 100) { // refndAmt = Math.round(await paymentDetails.amount); // } else { // refndAmt = Math.round( // (await paymentDetails.amount) - // paymentDetails.amount * 0.052, // ); // } // } // const transferPayload = { // transfers: [ // { // account: submerchantAccountId, // amount: await refndAmt, // currency: 'INR', // notes: { // name: 'Gaurav Kumar', // roll_no: 'IEC2011025', // }, // linked_account_notes: ['roll_no'], // on_hold: false, // }, // ], // }; // await razorpay.payments.transfer( // AppointmentEntity.payment_id, // transferPayload, // ); // } catch (error) { // return error; // } // if ( // !AppointmentEntity.payment_reference_number || // !AppointmentEntity.payment_id // ) { // if (!AppointmentEntity.payment_reference_number) { // AppointmentEntity.payment_reference_number = 'NA'; // } // if (!AppointmentEntity.payment_id) { // AppointmentEntity.payment_id = 'NA'; // } // return { // status: 'failed', // message: // 'enter the payment reference number and payment_id received from razorpay for booking appointment', // }; // } // } // } // } let HOStransaction_id: number; const HOScaseRef = await this.dynamicConnection.query( 'INSERT INTO case_references values(default,default)', ); const HOSopdCreate = await this.dynamicConnection.query( `insert into opd_details (case_reference_id,patient_id) values (?,?)`, [HOScaseRef.insertId, AppointmentEntity.patient_id], ); const HOScharge = await this.dynamicConnection.query( 'select charge_id from shift_details where shift_details.staff_id = ?', [AppointmentEntity.doctor], ); let HOScharge_id = await HOScharge[0].charge_id; const HOSamount = await this.dynamicConnection.query( ` select charges.standard_charge,tax_category.percentage tax_percentage, 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 = ?`, [HOScharge_id], ); const Patient_charges_insert = await this.dynamicConnection.query( `insert into patient_charges( date, opd_id, qty, charge_id, standard_charge, tax, apply_charge, amount, total, payment_status, patient_id,balance ) values(?,?,?,?,?,?,?,?,?,?,?,?)`, [ AppointmentEntity.date + ' ' + AppointmentEntity.time, HOSopdCreate.insertId ?? null, 1, HOScharge_id, HOSamount[0].standard_charge, HOSamount[0].tax_percentage, HOSamount[0].standard_charge, HOSamount[0].amount, HOSamount[0].amount, paymentStatus, AppointmentEntity.patient_id, -HOSamount[0].amount, ], ); const HOSvisitInsert = await this.dynamicConnection.query( ` insert into visit_details( opd_details_id, patient_charge_id, transaction_id, case_type, cons_doctor, appointment_date, live_consult, payment_mode ) values (?,?,?,?,?,?,?,?)`, [ HOSopdCreate.insertId, Patient_charges_insert.insertId, HOStransaction_id, '', AppointmentEntity.doctor, AppointmentEntity.date + ' ' + AppointmentEntity.time, AppointmentEntity.live_consult, AppointmentEntity.payment_mode, ], ); let hos_appointment_id; const [AdminGlobalShiftId] = await this.connection.query( `select * from global_shift where hospital_global_shift_id = ? and hospital_id = ?`, [AppointmentEntity.global_shift_id, AppointmentEntity.Hospital_id], ); const [AdminShiftId] = await this.connection.query( `select * from doctor_shift where hospital_doctor_shift_id = ? and Hospital_id = ?`, [AppointmentEntity.shift_id, AppointmentEntity.Hospital_id], ); try { const HOSbookAppnt = await this.dynamicConnection.query( `insert into appointment( patient_id, case_reference_id, visit_details_id, date, time, doctor, source, global_shift_id, shift_id, live_consult, amount, appointment_status, appointment_status_id ) values(?,?,?,?,?,?,?,?,?,?,?,?,?)`, [ AppointmentEntity.patient_id, HOScaseRef.insertId, HOSvisitInsert.insertId, AppointmentEntity.date, AppointmentEntity.time, AppointmentEntity.doctor, 'Offline', AppointmentEntity.global_shift_id, AppointmentEntity.shift_id, AppointmentEntity.live_consult, HOSamount[0].amount, appointment_status, appointment_status_id, ], ); hos_appointment_id = HOSbookAppnt.insertId; if (AppointmentEntity.date == formattedDate) { const getLastPosition = await this.dynamicConnection.query( `select position from appointment_queue where date(date) = ? and staff_id = ? and shift_id = ? ORDER BY position DESC `, [ AppointmentEntity.date, AppointmentEntity.doctor, AppointmentEntity.shift_id, ], ); if (getLastPosition.length !== 0) { position = getLastPosition[0].position + 1; } else { position = 1; } } else { position = null; } await this.dynamicConnection.query( `insert into appointment_queue( appointment_id, staff_id, shift_id, position, date ) values (?,?,?,?,?)`, [ hos_appointment_id, AppointmentEntity.doctor, AppointmentEntity.shift_id, position, AppointmentEntity.date, ], ); if (HOStransaction_id) { await this.dynamicConnection.query( `update transactions set transactions.appointment_id = ? where transactions.id = ?`, [HOSbookAppnt.insertId, HOStransaction_id], ); } let payment_type; if (AppointmentEntity.payment_mode == `cash`) { payment_type = `Offline`; } else { payment_type = `Online`; } let hosTransId; if (HOStransaction_id) { hosTransId = HOStransaction_id; } await this.dynamicConnection.query( `insert into appointment_payment (appointment_id, charge_id, paid_amount, payment_mode, payment_type, transaction_id, date) values (?,?,?,?,?,?,?)`, [ hos_appointment_id, HOScharge_id, HOSamount[0].amount, AppointmentEntity.payment_mode, payment_type, hosTransId, AppointmentEntity.date + ' ' + AppointmentEntity.time, ], ); apptDetails = await this.dynamicConnection.query( `SELECT CONCAT( CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END, appointment.id ) id, patients.id patient_id, patients.aayush_unique_id, CONCAT("PT",patients.id) plenome_patient_id, patients.patient_name,patients.gender,patients.age, patients.mobileno,opd_details.id opd_id, patients.dial_code,patients.email,patients.image, patients.abha_address,patients.ABHA_number, CASE WHEN appointment.source = 'Online' THEN 'Online Consultation ' ELSE 'Offline Consultation' END AS consultingType, CONCAT("Dr. ",staff.name," ",staff.surname) doctorName, staff.id doctor_id, COALESCE(GROUP_CONCAT(DISTINCT specialist.specialist_name),"-") AS doctorSpecialist, appointment.source,appointment.global_shift_id,appointment.shift_id, appointment.module, CONCAT(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.id) appointment_id, DATE_FORMAT(appointment.date, '%D %b %Y') appointmentDate, appointment.date, DATE_FORMAT(appointment.time, '%h:%i %p') appointmentTime, TIME(appointment.time) time, CONCAT(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) slot, appointment_status.status appointment_status, appointment.appointment_status_id, appointment.is_token_verified, appointment.is_consultation_closed, appointment_status.color_code, appointment_queue.position tokenNumber, appointment.message, CASE WHEN appointment.doctor IS NULL THEN patient_charges.temp_standard_charge ELSE patient_charges.standard_charge END consultFees, CASE WHEN appointment.doctor IS NULL THEN patient_charges.temp_tax ELSE patient_charges.tax END taxPercentage, CAST(patient_charges.additional_charge AS DECIMAL(10,2)) as additional_charge,patient_charges.additional_charge_note, patient_charges.discount_percentage,CAST(patient_charges.discount_amount AS DECIMAL(10,2)) as discount_amount, patient_charges.id patientChargeId, CASE WHEN appointment.doctor IS NULL THEN FORMAT((((patient_charges.temp_standard_charge + patient_charges.additional_charge - patient_charges.discount_amount) * patient_charges.temp_tax)/100),2) ELSE FORMAT((((patient_charges.standard_charge + patient_charges.additional_charge - patient_charges.discount_amount) * patient_charges.tax)/100),2) END taxAmount, CAST(patient_charges.balance AS DECIMAL(10,2)) netAmount, CAST(patient_charges.balance AS DECIMAL(10,2)) balanceAmount, CAST(patient_charges.temp_amount AS DECIMAL(10,2)) hospital_amount, patient_charges.charge_id chargesId, CAST(patient_charges.temp_standard_charge AS DECIMAL(10,2)) hospital_standard_charge, patient_charges.temp_tax hospital_tax, CAST(patient_charges.temp_apply_charge AS DECIMAL(10,2)) hospital_apply_charge, CONCAT("TRID",transactions.id) transactionID, transactions.id trnId, transactions.partial_payment_mode temp_payment_mode, transactions.payment_mode,transactions.payment_date,transactions.payment_method, transactions.card_division,transactions.card_type,transactions.card_transaction_id, transactions.card_bank_name,transactions.net_banking_division, transactions.net_banking_transaction_id,transactions.upi_id, transactions.upi_bank_name,transactions.upi_transaction_id, transactions.cash_transaction_id, transactions.actual_paid_amount, transactions.wallet_paid_amount, transactions.temp_actual_paid_amount, transactions.temp_wallet_paid_amount, CONCAT( COALESCE(transactions.net_banking_transaction_id,""), COALESCE(transactions.card_transaction_id,""), COALESCE(transactions.upi_transaction_id,""), COALESCE(transactions.payment_reference_number,""), COALESCE(transactions.cash_transaction_id,"") ) payment_transaction_id, CASE WHEN patient_charges.payment_status = 'paid' THEN 'Payment done.' WHEN patient_charges.payment_status = 'partially_paid' and appointment.doctor IS NULL THEN 'Partially Paid.' ELSE 'Need payment.' END AS payment_status FROM appointment LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN opd_details ON opd_details.case_reference_id = appointment.case_reference_id LEFT JOIN appointment_status ON appointment_status.id = appointment.appointment_status_id LEFT JOIN staff ON staff.id = appointment.doctor LEFT JOIN ( SELECT pc.* FROM ( SELECT * FROM patient_charges ORDER BY date ASC ) pc GROUP BY pc.opd_id ) AS patient_charges ON patient_charges.opd_id = opd_details.id LEFT JOIN transactions ON transactions.id = patient_charges.transaction_id LEFT JOIN doctor_shift ON doctor_shift.id = appointment.shift_id LEFT JOIN appointment_queue ON appointment.id = appointment_queue.appointment_id LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1 WHERE appointment.id = ? GROUP BY patient_id,patient_name,gender,age,mobileno,email,ABHA_number,consultingType,opd_id, doctorName,doctor_id,source,appointment_id,appointmentDate,appointmentTime,slot, abha_address,appointment_status,appointment_status_id,color_code,tokenNumber, message,consultFees,taxPercentage,taxAmount,netAmount,transactionID,payment_mode, payment_date,balanceAmount,payment_status,date`, [hos_appointment_id], ); } catch (error) { return 'errhos_appor in Appointment insert :' + error; } let transaction_id: number; const caseRef = await this.connection.query( 'INSERT INTO case_references values(default,default)', ); const opdCreate = await this.connection.query( ` insert into opd_details (case_reference_id,patient_id,Hospital_id,hos_opd_id) values (?,?,?,?)`, [ caseRef.insertId, AdminPatientId, AppointmentEntity.Hospital_id, HOSopdCreate.insertId, ], ); const getAdminChargeId = await this.connection.query( `select id from charges where Hospital_id = ? and hospital_charges_id = ?`, [AppointmentEntity.Hospital_id, HOScharge_id], ); const patient_charges = await this.connection.query( `insert into patient_charges( date, opd_id, qty, charge_id, standard_charge, tax, apply_charge, amount, Hospital_id, hos_patient_charges_id, total, payment_status, patient_id,balance ) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)`, [ AppointmentEntity.date + ' ' + AppointmentEntity.time, opdCreate.insertId, 1, getAdminChargeId[0].id, HOSamount[0].standard_charge, HOSamount[0].tax_percentage, HOSamount[0].standard_charge, HOSamount[0].amount, AppointmentEntity.Hospital_id, Patient_charges_insert.insertId, HOSamount[0].amount, paymentStatus, AdminPatientId, -HOSamount[0].amount, ], ); try { if ( AppointmentEntity.payment_mode.toLocaleLowerCase() != 'paylater' && AppointmentEntity.payment_mode.toLocaleLowerCase() != 'offline' ) { const HOStransactions = await this.dynamicConnection.query( ` insert into transactions ( type, section, patient_id, case_reference_id, amount, payment_mode, payment_date,txn_id,pg_ref_id,bank_ref_id,patient_charges_id,received_by_name, actual_paid_amount, wallet_paid_amount ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)`, [ 'payment', 'Appointment', AppointmentEntity.patient_id, HOScaseRef.insertId, HOSamount[0].amount, AppointmentEntity.payment_mode, AppointmentEntity.payment_date, AppointmentEntity.txn_id, AppointmentEntity.pg_ref_id, AppointmentEntity.bank_ref_id, Patient_charges_insert.insertId, AppointmentEntity.received_by_name, AppointmentEntity.actual_amount_paid, AppointmentEntity.amount_from_coins, ], ); HOStransaction_id = await HOStransactions.insertId; await this.dynamicConnection.query( `update patient_charges set transaction_id = ?,balance = 0 where id = ?`, [HOStransactions.insertId, Patient_charges_insert.insertId], ); const transactions = await this.connection.query( ` insert into transactions ( type, section, patient_id, case_reference_id, amount, payment_mode, payment_date,Hospital_id, hos_transaction_id,txn_id,pg_ref_id,bank_ref_id,patient_charges_id,received_by_name, actual_paid_amount, wallet_paid_amount ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`, [ 'payment', 'Appointment', AdminPatientId, await caseRef.insertId, await HOSamount[0].amount, AppointmentEntity.payment_mode, AppointmentEntity.payment_date, AppointmentEntity.Hospital_id, HOStransaction_id, AppointmentEntity.txn_id, AppointmentEntity.pg_ref_id, AppointmentEntity.bank_ref_id, patient_charges.insertId, AppointmentEntity.received_by_name, AppointmentEntity.actual_amount_paid, AppointmentEntity.amount_from_coins, ], ); transaction_id = await transactions.insertId; await this.connection.query( `update patient_charges set transaction_id = ?,balance = 0 where id = ?`, [transactions.insertId, patient_charges.insertId], ); try { this.newBillingService.addtransactionInfo( transactions.insertId.toString(), ); } catch (error) { console.log('Error while adding transaction info to mangodb'); } if (AppointmentEntity.payment_mode) { if ( AppointmentEntity.payment_mode.toLocaleLowerCase() != 'cash' && AppointmentEntity.payment_mode.toLocaleLowerCase() != 'cheque' && AppointmentEntity.payment_mode.toLocaleLowerCase() != 'offline' ) { if (!AppointmentEntity.payment_gateway) { return { status: 'failed', message: 'enter payment gateway to book appointment', }; } if ( AppointmentEntity.payment_gateway.toLocaleLowerCase() == 'razorpay' ) { const [getPaymentGatewayDetails] = await this.connection.query( `select * from hospital_payment_gateway_details where hospital_id = ? and payment_gateway = ?`, [AppointmentEntity.Hospital_id, 'razorpay'], ); const get_aayush_unique_id = HosPatient[0].aayush_unique_id; // await this.connection.query( // `select aayush_unique_id from patients where id = ?`, // [HosPatient[0].aayush_unique_id], // ); this.TransferToSubmerchant( AppointmentEntity.payment_id, getPaymentGatewayDetails, AppointmentEntity.Hospital_id, get_aayush_unique_id, HOStransactions.insertId, transactions.insertId, ); if ( !AppointmentEntity.payment_reference_number || !AppointmentEntity.payment_id ) { if (!AppointmentEntity.payment_reference_number) { AppointmentEntity.payment_reference_number = 'NA'; } if (!AppointmentEntity.payment_id) { AppointmentEntity.payment_id = 'NA'; } return { status: 'failed', message: 'enter the payment reference number and payment_id received from razorpay for booking appointment', }; } } } } } } catch (error) { return 'error in Admin transaction insert'; } const visitInsert = await this.connection.query( ` insert into visit_details( opd_details_id, patient_charge_id, transaction_id, case_type, cons_doctor, appointment_date, live_consult, payment_mode,Hospital_id, hos_visit_id ) values (?,?,?,?,?,?,?,?,?,?)`, [ await opdCreate.insertId, await patient_charges.insertId, transaction_id, '', AdminStaffId, AppointmentEntity.date + ' ' + AppointmentEntity.time, AppointmentEntity.live_consult, AppointmentEntity.payment_mode, AppointmentEntity.Hospital_id, await HOSvisitInsert.insertId, ], ); try { const bookAppnt = await this.connection.query( `insert into appointment( patient_id, case_reference_id, visit_details_id, date, time, doctor, source, global_shift_id, shift_id, live_consult, Hospital_id,hos_appointment_id,amount, appointment_status, appointment_status_id ) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`, [ AdminPatientId, await caseRef.insertId, await visitInsert.insertId, AppointmentEntity.date, AppointmentEntity.time, AdminStaffId, 'Offline', AdminGlobalShiftId.id, AdminShiftId.id, AppointmentEntity.live_consult, AppointmentEntity.Hospital_id, hos_appointment_id, await HOSamount[0].amount, appointment_status, appointment_status_id, ], ); await this.connection.query( `insert into appointment_queue( appointment_id, staff_id, shift_id, position, date ) values (?,?,?,?,?)`, [ bookAppnt.insertId, AdminStaffId, AdminShiftId.id, position, AppointmentEntity.date, ], ); let payment_type; if (AppointmentEntity.payment_mode == `cash`) { payment_type = `Offline`; } else { payment_type = `Online`; } if (transaction_id) { await this.connection.query( `update transactions set transactions.appointment_id = ? where transactions.id = ?`, [bookAppnt.insertId, transaction_id], ); } await this.connection.query( `insert into appointment_payment (appointment_id, charge_id, paid_amount, payment_mode, payment_type, transaction_id, date) values (?,?,?,?,?,?,?)`, [ bookAppnt.insertId, getAdminChargeId[0].id, HOSamount[0].amount, AppointmentEntity.payment_mode, payment_type, transaction_id, AppointmentEntity.date + ' ' + AppointmentEntity.time, ], ); if (!position) { position = ' - '; } const verifyData = { mobilenumber: '91' + apptDetails[0].mobileno, Patname: ' ' + apptDetails[0].patient_name, Date: apptDetails[0].appointmentDate + ' With ' + apptDetails[0].doctorName, DocName: 'AppointmentNo : ' + apptDetails[0].id, }; const emailData = { email: apptDetails[0].email, name: ' ' + apptDetails[0].patient_name, drname: 'Dr.' + apptDetails[0].doctorName, date: apptDetails[0].appointmentDate, time: apptDetails[0].appointmentTime, HosName: apptDetails[0].hospital_name, location: apptDetails[0].hospital_address, }; axios.post('https://notifications.plenome.com/sms', verifyData); axios.post( 'https://notifications.plenome.com/email-appointment-booked', emailData, ); try { const notifydata_URL = 'http://13.200.35.19:7000/send-notification/to-profile'; const notifyaddress_data = { patient_id: await AdminPatientId, title: 'Regarding Your Appointment Booking', body: 'Your appointment has been booked successfully!!', module: 'Appointment', }; axios.post(notifydata_URL, notifyaddress_data); } catch (error) { console.error('Error while sending notification:', error); } return [ { status: 'success', messege: 'Appointment booked successfully', inserted_details: apptDetails[0], }, ]; } catch (error) { throw new Error('error is : ' + error); } } else { return [ { status: 'failed', messege: 'cannot book duplicate Appointment', }, ]; } } catch (error) { throw new Error('error is : ' + error); } } async findAll( fromDate: string, toDate: string, doctorId: number, appointStatus: string, hospital_id: number, paymentStatus: string, ) { if (hospital_id) { try { let query = ` SELECT patients.patient_name, patients.id AS patient_id, concat("PT",patients.id) plenome_patient_id, CONCAT(DATE_FORMAT(appointment.date, '%D %b %Y'), ",", DATE_FORMAT(appointment.time, '%h:%i %p')) AS appointment_date, CONCAT(date(appointment.date), " ", time(appointment.time)) AS comp, CONCAT( patients.mobileno) AS Mobile, coalesce( GROUP_CONCAT(DISTINCT specialist.specialist_name) ,"-")AS doctorSpecialist, patients.dial_code, opd_details.id opd_id, appointment.doctor, coalesce(patients.salutation,"") salutation, CONCAT("Dr. ",staff.name, " ", staff.surname) AS consultant, appointment_status.status appointment_status, appointment.appointment_status_id, appointment.module, appointment_status.color_code, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.id) appointment_id, patient_charges.payment_status, patient_charges.total apptFees, CASE WHEN appointment_queue.position IS NOT NULL THEN appointment_queue.position ELSE CONCAT(" ", "- ") END AS appointment_token FROM appointment LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN staff ON staff.id = appointment.doctor LEFT JOIN appointment_queue ON appointment_queue.appointment_id = appointment.id left join appointment_status on appointment_status.id = appointment.appointment_status_id LEFT JOIN visit_details ON visit_details.id = appointment.visit_details_id left join opd_details on opd_details.id = visit_details.opd_details_id left join patient_charges on patient_charges.id = visit_details.patient_charge_id LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1 `; let date; let values = []; if (fromDate && toDate) { date = ` date(appointment.date) >= date( '` + fromDate + `' ) and date(appointment.date) <= date( '` + toDate + `' ) `; } else if (fromDate) { date = ` date(appointment.date) >= date( '` + fromDate + `' ) `; } else if (toDate) { date = ` date(appointment.date) <= date( '` + toDate + `' ) `; } else { date = ` appointment.date > DATE(NOW()) `; } let where = `WHERE ` + date; if (doctorId) { where += ` and appointment.doctor = ?`; values.push(doctorId); } if (appointStatus) { where += ` and appointment.appointment_status_id = ?`; values.push(appointStatus); } else { where += ` and (appointment.appointment_status_id <> 6 and appointment.appointment_status_id <> 4)`; } if (paymentStatus) { where += ` and patient_charges.payment_status = ?`; values.push(paymentStatus); } let order = `ORDER BY date(appointment.date) ASC, time(appointment.date) ASC `; let group = ` GROUP BY patients.patient_name, patients.id, appointment.date, appointment.time, patients.mobileno, patients.dial_code, appointment.doctor, staff.name, staff.surname, appointment_status.status, appointment.appointment_status_id, appointment_status.color_code, appointment.id, apptFees, opd_id, patient_charges.payment_status, appointment_queue.position `; let final = query + where + group + order; const GetTodayAppointment = await this.dynamicConnection.query( final, values, ); return GetTodayAppointment; } catch (error) { throw new Error(error); } } else { return { status: 'failed', message: 'Enter hospital_id to get the values', }; } } async Today( fromDate: string, toDate: string, doctorId: number, appointStatus: string, hospital_id: number, paymentStatus: string, ) { if (hospital_id) { try { let query = ` SELECT patients.patient_name, patients.id AS patient_id, concat("PT",patients.id) plenome_patient_id, CONCAT(DATE_FORMAT(appointment.date, '%D %b %Y'), ",", DATE_FORMAT(appointment.time, '%h:%i %p')) AS appointment_date, CONCAT(date(appointment.date), " ", time(appointment.time)) AS comp, CONCAT( patients.mobileno) AS Mobile, coalesce( GROUP_CONCAT(DISTINCT specialist.specialist_name) ,"-")AS doctorSpecialist, patients.dial_code, opd_details.id opd_id, appointment.doctor, coalesce(patients.salutation,"") salutation, CONCAT("Dr. ",staff.name, " ", staff.surname) AS consultant, appointment_status.status appointment_status, appointment.module, appointment.appointment_status_id, appointment_status.color_code, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.id) appointment_id, patient_charges.payment_status, patient_charges.total apptFees, CASE WHEN appointment_queue.position IS NOT NULL THEN appointment_queue.position ELSE CONCAT(" ", "- ") END AS appointment_token FROM appointment LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN staff ON staff.id = appointment.doctor LEFT JOIN appointment_queue ON appointment_queue.appointment_id = appointment.id left join appointment_status on appointment_status.id = appointment.appointment_status_id LEFT JOIN visit_details ON visit_details.id = appointment.visit_details_id left join opd_details on opd_details.id = visit_details.opd_details_id left join patient_charges on patient_charges.id = visit_details.patient_charge_id LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1 `; let date; let values = []; if (fromDate && toDate) { date = ` date(appointment.date) >= date( '` + fromDate + `' ) and date(appointment.date) <= date( '` + toDate + `' ) `; } else if (fromDate) { date = ` date(appointment.date) >= date( '` + fromDate + `' ) `; } else if (toDate) { date = ` date(appointment.date) <= date( '` + toDate + `' ) `; } else { date = ` appointment.date = DATE(NOW()) `; } let where = `WHERE ` + date; if (doctorId) { where += ` and appointment.doctor = ?`; values.push(doctorId); } if (appointStatus) { where += ` and appointment.appointment_status_id = ?`; values.push(appointStatus); } else { where += ` and ((appointment.appointment_status_id <> 6) and (appointment.appointment_status_id <> 4))`; } if (paymentStatus) { where += ` and patient_charges.payment_status = ?`; values.push(paymentStatus); } let order = `ORDER BY date(appointment.date) ASC, time(appointment.time) ASC `; let group = ` GROUP BY patients.patient_name, patients.id, appointment.date, appointment.time, patients.mobileno, patients.dial_code, appointment.doctor, staff.name, staff.surname, appointment_status.status, appointment.appointment_status_id, appointment_status.color_code, appointment.id, apptFees, opd_id, patient_charges.payment_status, appointment_queue.position `; let final = query + where + group + order; const GetTodayAppointment = await this.dynamicConnection.query( final, values, ); return GetTodayAppointment; } catch (error) { throw new Error(error); } } else { return { status: 'failed', message: 'Enter hospital_id to get the values', }; } } async findAllHistory( fromDate: string, toDate: string, doctorId: number, appointStatus: string, hospital_id: number, payment_status: string, ) { if (hospital_id) { try { let query = `SELECT patients.patient_name, patients.id AS patient_id, concat("PT",patients.id) plenome_patient_id, CONCAT(DATE_FORMAT(appointment.date, '%D %b %Y'), ",", DATE_FORMAT(appointment.time, '%h:%i %p')) AS appointment_date, CONCAT(date(appointment.date), " ", time(appointment.time)) AS comp, CONCAT( patients.mobileno) AS Mobile, coalesce( GROUP_CONCAT(DISTINCT specialist.specialist_name) ,"-")AS doctorSpecialist, patients.dial_code, appointment.doctor, coalesce(patients.salutation,"") salutation, CONCAT("Dr. ",staff.name, " ", staff.surname) AS consultant, appointment_status.status appointment_status, appointment.appointment_status_id, appointment_status.color_code, appointment.module, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.id) appointment_id, patient_charges.payment_status, patient_charges.total apptFees, CASE WHEN appointment_queue.position IS NOT NULL THEN appointment_queue.position ELSE CONCAT(" ", "- ") END AS appointment_token FROM appointment LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN staff ON staff.id = appointment.doctor LEFT JOIN appointment_queue ON appointment_queue.appointment_id = appointment.id left join appointment_status on appointment_status.id = appointment.appointment_status_id LEFT JOIN visit_details ON visit_details.id = appointment.visit_details_id left join patient_charges on patient_charges.id = visit_details.patient_charge_id LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1 `; let date; let values = []; if (fromDate && toDate) { date = ` (date(appointment.date) >= date( '` + fromDate + `' ) and date(appointment.date) <= date( '` + toDate + `' ) or (appointment.appointment_status_id = 6 or appointment.appointment_status_id = 4))`; } else if (fromDate) { date = ` (date(appointment.date) >= date( '` + fromDate + `' ) or (appointment.appointment_status_id = 6 or appointment.appointment_status_id = 4))`; } else if (toDate) { date = ` (date(appointment.date) <= date( '` + toDate + `' ) or (appointment.appointment_status_id = 6 or appointment.appointment_status_id = 4)) `; } else { date = ` (appointment.date < DATE(NOW()) or (appointment.appointment_status_id = 6 or appointment.appointment_status_id = 4)) `; } let where = ` WHERE ` + date; if (doctorId) { where += ` and appointment.doctor = ?`; values.push(doctorId); } if (appointStatus) { where += ` and appointment.appointment_status_id = ?`; values.push(appointStatus); } if (payment_status) { where += ` and patient_charges.payment_status = ?`; values.push(payment_status); } let order = `ORDER BY appointment.date DESC, appointment.time ASC `; let group = ` GROUP BY patients.patient_name, patients.id, appointment.date, appointment.time, patients.mobileno, patients.dial_code, appointment.doctor, staff.name, apptFees, staff.surname, appointment_status.status, appointment.appointment_status_id, appointment_status.color_code, appointment.id, patient_charges.payment_status, appointment_queue.position `; let final = query + where + group + order; const GetTodayAppointment = await this.dynamicConnection.query( final, values, ); return GetTodayAppointment; } catch (err) { return err; } } else { return { status: 'failed', message: 'Enter hospital_id to get the values', }; } } async findOne(token: string, hospitalId: number) { if (!hospitalId) { return { status: 'failed', message: 'Enter hospital_id to get the appointment information', }; } // Extract appointment type + number const apptType = token.replace(/[0-9]/g, ''); const appointmentNumber = parseInt(token.replace(/[a-zA-Z]/g, ''), 10); if (isNaN(appointmentNumber)) { return { status: 'failed', message: 'Invalid appointment token' }; } if (apptType !== 'APPN' && apptType !== 'TEMP') { return { status: 'failed', message: 'enter appointment_id to get the values', }; } try { // Validate appointment exists in hospital system const [phrAppt] = await this.connection.query( `SELECT * FROM appointment WHERE Hospital_id = ? AND hos_appointment_id = ?`, [hospitalId, appointmentNumber], ); if (!phrAppt) { return { status: 'failed', message: 'appointment not found.' }; } // Fetch appointment details from dynamic connection const appointmentQuery = this.getAppointmentQuery(); const [appointment] = await this.dynamicConnection.query( appointmentQuery, [appointmentNumber], ); console.log(appointment, 'appointment'); if (!appointment) { return { status: 'failed', message: 'appointment not found.' }; } let isDentist = false; if (appointment.doctorSpecialist) { isDentist = appointment.doctorSpecialist .split(',') .some((item: any) => item.trim().toLowerCase() === 'dentist'); } // Fix slot if missing if (!appointment.slot) { const [timings] = await this.connection.query( `SELECT CONCAT( DATE_FORMAT(TIME(hospitals.hospital_opening_timing), '%h:%i %p'), " - ", DATE_FORMAT(TIME(hospitals.hospital_closing_timing), '%h:%i %p') ) AS time FROM hospitals WHERE plenome_id = ?`, [hospitalId], ); appointment.slot = timings?.time || null; } appointment.phr_appointment_id = phrAppt.id; // Attach payment details (if any) if (appointment.transactionID) { await this.attachPaymentDetails(appointment, hospitalId); } if (appointment.partial_payment_mode) { // ans.temp_payment_transaction_id = await ans.temp_appt_payment_reference_number appointment.temp_hos_transaction_id = (await 'TEMP') + appointment.trnId; const [getPlenomeTransacitonId] = await this.connection.query( `select id from transactions where hospital_id = ? and hos_transaction_id = ?`, [hospitalId, appointment.trnId], ); appointment.temp_plenome_transaction_id = (await 'TEMP') + getPlenomeTransacitonId.id; } if ( appointment.partial_payment_mode?.toLocaleLowerCase() == 'aayush_coins' ) { appointment.temp_payment_transaction_id = 'aayush_coins'; } if (appointment.payment_mode?.toLocaleLowerCase() == 'aayush_coins') { appointment.payment_transaction_id = 'aayush_coins'; } if ( appointment.doctor_id !== null && appointment.payment_status === 'Need payment.' ) { console.log('asd'); appointment.netAmount = Number(appointment.netAmount) * -1; } else { console.log('asad'); appointment.netAmount = Number(appointment.netAmount) * -1; } // if (appointment.discount_amount > 0) // appointment.netAmount = Number(appointment.netAmount) - Number(appointment.discount_amount); // appointment.actual_paid_amount = // Number(appointment.netAmount) + Number(appointment.balanceAmount); if (appointment.payment_status == 'Partially Paid.') { console.log( parseFloat(appointment.total), parseFloat(appointment.balanceAmount), parseFloat(appointment.discount_amount), 'log', ); appointment.total_amount_paid = parseFloat(appointment.hospital_amount); } else if (appointment.payment_status == 'Payment done.') { console.log(2); appointment.total_amount_paid = Number( ( parseFloat(appointment.apply_charge || 0) - parseFloat(appointment.discount_amount || 0) + parseFloat(appointment.taxAmount || 0) ).toFixed(2) ); } else { console.log(3); appointment.total_amount_paid = parseFloat('0'); } appointment.isDentist = isDentist; if (appointment.balanceAmount <= 0) { appointment.balanceAmount = Number(appointment.balanceAmount) * -1; } else { appointment.balanceAmount = 0; } let paymentData: any = { total_paid: 0, total_due: 0, status: null }; try { const transData = await this.get_all_transaction_charges(appointmentNumber, hospitalId); appointment.paid_charges_data = transData?.paid_data ?? []; appointment.due_charges_data = transData?.unpaid_data ?? []; appointment.transaction_data = transData?.transaction_data ?? []; paymentData = { ...paymentData, ...(transData?.billing_summary ?? {}) }; if (paymentData.total_paid === 0 && paymentData.total_due > 0) { paymentData.status = 'due'; } else if (paymentData.total_paid > 0 && paymentData.total_due > 0) { paymentData.status = 'partially_paid'; } else if (paymentData.total_due === 0 && paymentData.total_paid > 0) { paymentData.status = 'paid'; } else { paymentData.status = null; } } catch (error) { console.error('error at all_patient_charges:', error); } appointment.total_payment = paymentData; return { QR_Type_ID: 3, QR_Type: 'Appointment_QR', Appointment_details: appointment, }; } catch (error) { console.error('findOne error:', error); return { status: 'failed', message: 'Internal server error', error }; } } /** * Extracted: SQL query for appointment details */ private getAppointmentQuery(): string { return ` SELECT CONCAT( CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END, appointment.id ) id, patients.id patient_id, patients.aayush_unique_id, CONCAT("PT",patients.id) plenome_patient_id, patients.patient_name,patients.gender,patients.age, patients.mobileno,opd_details.id opd_id, patients.dial_code,patients.email,patients.image, patients.abha_address,patients.ABHA_number, CASE WHEN appointment.source = 'Online' THEN 'Online Consultation ' ELSE 'Offline Consultation' END AS consultingType, CONCAT("Dr. ",staff.name," ",staff.surname) doctorName, staff.id doctor_id, COALESCE(GROUP_CONCAT(DISTINCT specialist.specialist_name),"-") AS doctorSpecialist, appointment.source,appointment.global_shift_id,appointment.shift_id, appointment.module, CONCAT(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.id) appointment_id, DATE_FORMAT(appointment.date, '%D %b %Y') appointmentDate, appointment.date, DATE_FORMAT(appointment.time, '%h:%i %p') appointmentTime, TIME(appointment.time) time, CONCAT(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) slot, appointment_status.status appointment_status, appointment.appointment_status_id, appointment.is_token_verified, appointment.is_consultation_closed, appointment_status.color_code, appointment_queue.position tokenNumber, appointment.message, CASE WHEN appointment.doctor IS NULL THEN patient_charges.temp_standard_charge ELSE patient_charges.standard_charge END consultFees, CASE WHEN appointment.doctor IS NULL THEN patient_charges.temp_tax ELSE patient_charges.tax END taxPercentage, CAST(patient_charges.additional_charge AS DECIMAL(10,2)) as additional_charge,patient_charges.additional_charge_note, patient_charges.discount_percentage,CAST(patient_charges.discount_amount AS DECIMAL(10,2)) as discount_amount, patient_charges.id patientChargeId, CASE WHEN appointment.doctor IS NULL THEN CAST( ( ( patient_charges.temp_apply_charge + patient_charges.additional_charge - patient_charges.discount_amount ) * patient_charges.temp_tax ) / 100 AS DECIMAL(10,2)) ELSE CAST( ( ( patient_charges.apply_charge + patient_charges.additional_charge - patient_charges.discount_amount ) * patient_charges.tax ) / 100 AS DECIMAL(10,2)) END AS taxAmount, CAST(patient_charges.balance AS DECIMAL(10,2)) netAmount, patient_charges.total, CAST(patient_charges.balance AS DECIMAL(10,2)) balanceAmount, CAST(patient_charges.temp_amount AS DECIMAL(10,2)) hospital_amount, patient_charges.apply_charge, patient_charges.charge_id chargesId, CAST(patient_charges.temp_standard_charge AS DECIMAL(10,2)) hospital_standard_charge, patient_charges.temp_tax hospital_tax, CAST(patient_charges.temp_apply_charge AS DECIMAL(10,2)) hospital_apply_charge, CONCAT("TRID",transactions.id) transactionID, transactions.id trnId, transactions.partial_payment_mode temp_payment_mode, transactions.payment_mode,transactions.payment_date,transactions.payment_method, transactions.card_division,transactions.card_type,transactions.card_transaction_id, transactions.card_bank_name,transactions.net_banking_division, transactions.net_banking_transaction_id,transactions.upi_id, transactions.upi_bank_name,transactions.upi_transaction_id, transactions.cash_transaction_id, transactions.actual_paid_amount, transactions.wallet_paid_amount, transactions.temp_actual_paid_amount, transactions.temp_wallet_paid_amount, CONCAT( COALESCE(transactions.net_banking_transaction_id,""), COALESCE(transactions.card_transaction_id,""), COALESCE(transactions.upi_transaction_id,""), COALESCE(transactions.payment_reference_number,""), COALESCE(transactions.cash_transaction_id,"") ) payment_transaction_id, CASE WHEN patient_charges.payment_status = 'paid' THEN 'Payment done.' WHEN patient_charges.payment_status = 'partially_paid' and appointment.doctor IS NULL THEN 'Partially Paid.' ELSE 'Need payment.' END AS payment_status FROM appointment LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN opd_details ON opd_details.case_reference_id = appointment.case_reference_id LEFT JOIN appointment_status ON appointment_status.id = appointment.appointment_status_id LEFT JOIN staff ON staff.id = appointment.doctor LEFT JOIN ( SELECT pc.* FROM ( SELECT * FROM patient_charges ORDER BY date ASC ) pc GROUP BY pc.opd_id ) AS patient_charges ON patient_charges.opd_id = opd_details.id LEFT JOIN transactions ON transactions.id = patient_charges.transaction_id LEFT JOIN doctor_shift ON doctor_shift.id = appointment.shift_id LEFT JOIN appointment_queue ON appointment.id = appointment_queue.appointment_id LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1 WHERE appointment.id = ? GROUP BY patient_id,patient_name,gender,age,mobileno,email,ABHA_number,consultingType,opd_id, doctorName,doctor_id,source,appointment_id,appointmentDate,appointmentTime,slot, abha_address,appointment_status,appointment_status_id,color_code,tokenNumber, message,consultFees,taxPercentage,taxAmount,netAmount,transactionID,payment_mode, payment_date,balanceAmount,payment_status,date `; } /** * Extracted: Attach Razorpay / transaction details */ private async attachPaymentDetails(appointment: any, hospitalId: number) { const razorpay = new Razorpay({ key_id: process.env.RAZORPAY_KEY_ID, key_secret: process.env.RAZORPAY_KEY_SECRET, }); const trnId = appointment.transactionID.replace(/[a-zA-Z]/g, ''); const [plenomeTransaction] = await this.connection.query( `SELECT id FROM transactions WHERE Hospital_id = ? AND hos_transaction_id = ?`, [hospitalId, trnId], ); if (plenomeTransaction) { appointment.plenome_transaction_id = 'PLE' + plenomeTransaction.id; } const [trx] = await this.dynamicConnection.query( `SELECT * FROM transactions WHERE id = ?`, [trnId], ); if (!trx) return; // Normal payment gateway if (trx.payment_gateway?.toLowerCase() === 'razorpay') { try { appointment.paymentDetails = await razorpay.payments.fetch( trx.payment_id, ); const refunds = await razorpay.refunds.all({ payment_id: trx.payment_id, }); if (refunds.count > 0) appointment.refundDetails = refunds.items; } catch (err) { console.error('razorpay error:', err); } } // Temp appointment payments if (trx.temp_appt_payment_gateway?.toLowerCase() === 'razorpay') { try { appointment.tempAppointmentPaymentDetails = await razorpay.payments.fetch(trx.temp_appt_payment_id); const refunds = await razorpay.refunds.all({ payment_id: trx.temp_appt_payment_id, }); if (refunds.count > 0) appointment.tempAppointmentRefundDetails = refunds.items; } catch (err) { console.error('razorpay temp error:', err); } } } async findQR(token: string, hospital_id: number) { if (hospital_id) { let ApptType = token.replace(/[0-9]/g, ''); let numb; try { numb = token.replace(/[a-zA-Z]/g, ''); } catch (err) { return err; } try { if (ApptType == 'APPN' || ApptType == 'TEMP') { let query = `select concat("APPN",appointment.id) hos_appointment_id, patients.id patient_id, patients.mobileno, patients.aayush_unique_id, staff.id doctor_id, concat("APPN",appointment.id) appointment_id, appointment_queue.position tokenNumber from appointment left join patients on patients.id = appointment.patient_id left join staff on staff.id = appointment.doctor left join appointment_queue on appointment.id = appointment_queue.appointment_id where appointment.id = ? `; let values = [numb]; const [phrApptID] = await this.connection.query( `select * from appointment where Hospital_id = ? and hos_appointment_id = ?`, [hospital_id, numb], ); let [ans] = await this.dynamicConnection.query(query, values); ans.phr_appointment_id = phrApptID.id; ans.Hospital_id = phrApptID.Hospital_id; if (!ans.aayush_unique_id) { ans.aayush_unique_id = 'NA'; } return { QR_Type_ID: 3, QR_Type: 'Appointment_QR', Appointment_details: ans, }; } else { return { status: 'failed', message: 'enter appointment_id to get the values', }; } } catch (error) { return [error]; } } else { return [ { status: 'failed', message: 'Enter hospital_id to get the appointment information', }, ]; } } async updateStatus(id: string, Entity: StatusChangePatch) { let numb: number; try { numb = parseInt(id.replace(/[a-zA-Z]/g, '')); } catch (error) { numb = parseInt(id); } if (Entity.Hospital_id) { try { await this.dynamicConnection.query( `update appointment set appointment_status = ?, appointment_status_id = ? where id = ?`, [Entity.appointment_status, Entity.appointment_status_id, numb], ); const [adminApptId] = await this.connection.query( `select id from appointment where Hospital_id = ? and hos_appointment_id = ?`, [Entity.Hospital_id, numb], ); await this.connection.query( `update appointment set appointment_status = ?, appointment_status_id = ? where id = ?`, [ Entity.appointment_status, Entity.appointment_status_id, adminApptId.id, ], ); return [ { status: 'success', message: 'Appointment Status Updated successfully', }, ]; } catch (error) { return [error]; } } else { return [ { status: 'failed', message: 'Enter hospital_id to update the appointment ', }, ]; } } async reshedule(id: string, Entity: UpdateAppointment) { if (Entity.Hospital_id) { let numb: number; let moduleNumb; try { numb = parseInt(id.replace(/[a-zA-Z]/g, '')); moduleNumb = numb; } catch (error) { numb = parseInt(id); moduleNumb = 'APPN' + numb; } try { const [getstaff_id] = await this.dynamicConnection.query( `select * from appointment where id = ?`, [numb], ); let adminApptStatusId = Entity.appointment_status_id; const doc_id = getstaff_id.doctor; const currentDate = new Date(); const datePart = new Date( currentDate.getFullYear(), currentDate.getMonth(), currentDate.getDate(), ); const formattedDate = currentDate.toISOString().split('T')[0]; if (!doc_id) { const [getPatId] = await this.dynamicConnection.query( `select patient_id from appointment where id = ?`, [numb], ); const [checkForDuplicate] = await this.dynamicConnection.query( `select id from appointment where shift_id = ? and date = ? and doctor = ? and patient_id = ? and (appointment_status_id <> 4 or appointment_status_id <> 6 )`, [Entity.shift_id, Entity.date, doc_id, await getPatId.patient_id], ); if (checkForDuplicate) { return { status: 'failed', message: 'Appointment already exists for this date and time', }; } } if (doc_id) { console.log("doctor irukku"); if (doc_id == Entity.doctor) { console.log("rendu doctor um onnu tha"); try { const HosVisitDetailsId = await getstaff_id.visit_details_id; const [HosPatientCharges] = await this.dynamicConnection.query( `select patient_charge_id from visit_details where id = ?`, [HosVisitDetailsId], ); console.log("HosPatientCharges starting", HosPatientCharges); const [HosAppointQueue] = await this.dynamicConnection.query( `select id from appointment_queue where appointment_id = ?`, [numb], ); const [HosAppointPayment] = await this.dynamicConnection.query( `select id from appointment_payment where appointment_id = ?`, [numb], ); const [getHosPAtCid] = await this.dynamicConnection.query( `select payment_status from patient_charges where id = ?`, [HosPatientCharges.patient_charge_id], ); let payment_status: string; if ( Entity.payment_mode == 'Paylater' || Entity.payment_mode == 'Offline' || Entity.payment_mode == 'cheque' || Entity.payment_mode == 'offline' || !Entity.payment_mode ) { payment_status = getHosPAtCid.payment_status; } else { payment_status = 'paid'; } this.dynamicConnection.query( `update patient_charges set date= ?,payment_status = ? where id = ?`, [ Entity.date + ' ' + Entity.time, payment_status, HosPatientCharges.patient_charge_id, ], ); this.dynamicConnection.query( `update visit_details set appointment_date = ?, live_consult = ? where id = ?`, [ Entity.date + ' ' + Entity.time, Entity.live_consult, HosVisitDetailsId, ], ); this.dynamicConnection.query( `update appointment set date = ?,time = ?, global_shift_id = ?,shift_id = ?,live_consult = ?,appointment_status_id = ?,appointment_status = ? where id = ?`, [ Entity.date, Entity.time, Entity.global_shift_id, Entity.shift_id, Entity.live_consult, Entity.appointment_status_id, Entity.appointment_status, numb, ], ); let position: number; if (Entity.date != formattedDate) { position = null; this.dynamicConnection.query( `update appointment_queue set shift_id = ?,date = ?,position = ? where id = ?`, [Entity.shift_id, Entity.date, position, HosAppointQueue.id], ); } else { const [checkPreviousToken] = await this.dynamicConnection.query( `select position,staff_id, shift_id, date from appointment_queue where id = ?`, [HosAppointQueue.id], ); if ( checkPreviousToken.position && checkPreviousToken.staff_id == Entity.doctor && checkPreviousToken.shift_id == Entity.shift_id && checkPreviousToken.date == Entity.date ) { position = checkPreviousToken.position; await this.dynamicConnection.query( `update appointment_queue set shift_id = ?,position = ?,date = ? where id = ?`, [ Entity.shift_id, position, Entity.date, HosAppointQueue.id, ], ); } else { const [getLastPosition] = await this.dynamicConnection.query( `select position from appointment_queue where date(date) = ? and staff_id = ? and shift_id = ? ORDER BY position DESC `, [Entity.date, Entity.doctor, Entity.shift_id], ); if (getLastPosition) { position = getLastPosition.position + 1; } else { position = 1; } this.dynamicConnection.query( `update appointment_queue set shift_id = ?,position = ?,date = ? where id = ?`, [ Entity.shift_id, position, Entity.date, HosAppointQueue.id, ], ); } } this.dynamicConnection.query( `update appointment_payment set date = ? where id = ?`, [Entity.date, HosAppointPayment.id], ); const [getAdminAppointdetails] = await this.connection.query( `select * from appointment where hos_appointment_id = ? and Hospital_id = ?`, [numb, Entity.Hospital_id], ); const AdminAppointmentId = await getAdminAppointdetails.id; const AdminPatId = await getAdminAppointdetails.patient_id; const AdminVisitDetailsId = await getAdminAppointdetails.visit_details_id; const [AdminPatientChargeId] = await this.connection.query( `select * from visit_details where id = ?`, [AdminVisitDetailsId], ); const [AdminAppointmentQueue] = await this.connection.query( `select * from appointment_queue where appointment_id = ?`, [AdminAppointmentId], ); const [AdminAppointmentPayment] = await this.connection.query( `select * from appointment_payment where appointment_id = ?`, [AdminAppointmentId], ); const [getPatientChargesAdmin] = await this.connection.query( `select * from patient_charges where id = ?`, [AdminPatientChargeId.patient_charge_id], ); await this.connection.query( `update patient_charges set date= ?,payment_status = ? where id = ?`, [ Entity.date + ' ' + Entity.time, payment_status, AdminPatientChargeId.patient_charge_id, ], ); const [getPatientCharges] = await this.dynamicConnection.query( `select * from patient_charges where id = ?`, [getPatientChargesAdmin.hos_patient_charges_id], ); if (!Entity.txn_id) { Entity.txn_id = 'NA'; } if (!Entity.bank_ref_id) { Entity.bank_ref_id = 'NA'; } if (!Entity.pg_ref_id) { Entity.pg_ref_id = 'NA'; } if ( payment_status == 'paid' && getPatientChargesAdmin.payment_status == 'unpaid' ) { const [getHosApptDetails] = await this.dynamicConnection.query( `select * from appointment where id = ?`, [numb], ); const insert_transactionsHos = await this.dynamicConnection.query( `insert into transactions ( txn_id, pg_ref_id, bank_ref_id, type, section, patient_id, case_reference_id, patient_charges_id, appointment_id, amount, payment_mode, payment_date,received_by_name, actual_paid_amount, wallet_paid_amount) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) `, [ Entity.txn_id, Entity.pg_ref_id, Entity.bank_ref_id, 'payment', 'Appointment', getHosApptDetails.patient_id, getHosApptDetails.case_reference_id, HosPatientCharges.patient_charge_id, getHosApptDetails.id, getPatientCharges.balance * -1, Entity.payment_mode, Entity.payment_date, Entity.received_by_name, Entity.actual_amount_paid, Entity.amount_from_coins, ], ); await this.dynamicConnection.query( `update patient_charges set balance = ?, transaction_id = ? where id = ?`, [ 0, insert_transactionsHos.insertId, HosPatientCharges.patient_charge_id, ], ); const insert_transactions = await this.connection.query( `insert into transactions ( txn_id, pg_ref_id, bank_ref_id, type, section, patient_id, case_reference_id, patient_charges_id, appointment_id, amount, payment_mode, payment_date, Hospital_id, hos_transaction_id,received_by_name, actual_paid_amount, wallet_paid_amount) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) `, [ Entity.txn_id, Entity.pg_ref_id, Entity.bank_ref_id, 'payment', 'Appointment', getAdminAppointdetails.patient_id, getAdminAppointdetails.case_reference_id, getAdminAppointdetails.patient_charge_id, getAdminAppointdetails.id, getPatientCharges.balance * -1, Entity.payment_mode, Entity.payment_date, Entity.Hospital_id, insert_transactionsHos.insertId, Entity.received_by_name, Entity.actual_amount_paid, Entity.amount_from_coins, ], ); await this.connection.query( `update patient_charges set balance = ?, transaction_id = ? where id = ?`, [ 0, insert_transactions.insertId, AdminPatientChargeId.patient_charge_id, ], ); try { console.log(insert_transactions.insertId, "insert_transactions.insertId"); console.log(insert_transactions.insertId.toString(), "insert_transactions.insertId.toString()"); this.newBillingService.addtransactionInfo( insert_transactions.insertId.toString(), ); } catch (error) { console.log('Error while adding transaction info to mangodb'); } } else if ( payment_status == 'paid' && getPatientChargesAdmin.payment_status == 'partially_paid' ) { await this.dynamicConnection.query( `UPDATE transactions SET amount = ?, received_by_name = ?, payment_mode = ?, payment_date = ?, actual_paid_amount = ?, wallet_paid_amount = ? WHERE appointment_id = ?`, [ getPatientCharges.balance * -1, Entity.received_by_name, Entity.payment_mode, Entity.payment_date, Entity.actual_amount_paid, Entity.amount_from_coins, numb, ], ); await this.connection.query( `UPDATE transactions SET amount = ?, received_by_name = ?, payment_mode = ?, payment_date = ?, actual_paid_amount = ?, wallet_paid_amount = ? WHERE appointment_id = ?`, [ getPatientCharges.balance * -1, Entity.received_by_name, Entity.payment_mode, Entity.payment_date, Entity.actual_amount_paid, Entity.amount_from_coins, AdminAppointmentId, ], ); try { const [getAdminTransactionId] = await this.connection.query(`select id from transactions where appointment_id = ? order by created_at asc limit 1`, [AdminAppointmentId]) this.newBillingService.addtransactionInfo( getAdminTransactionId?.id.toString(), ); } catch (error) { console.log('Error while adding transaction info to mangodb'); } } await this.connection.query( `update visit_details set appointment_date = ?, live_consult = ? where id = ?`, [ Entity.date + ' ' + Entity.time, Entity.live_consult, AdminVisitDetailsId, ], ); const [AdminGlobalShiftdetails] = await this.connection.query( `select * from global_shift where hospital_global_shift_id = ?`, [Entity.global_shift_id], ); const [AdminShiftDetails] = await this.connection.query( `select * from doctor_shift where hospital_doctor_shift_id = ?`, [Entity.shift_id], ); await this.connection.query( `update appointment set date = ?,time = ?, global_shift_id = ?,shift_id = ?,live_consult = ?,appointment_status_id = ?,appointment_status = ? where id = ?`, [ Entity.date, Entity.time, AdminGlobalShiftdetails.id, AdminShiftDetails.id, Entity.live_consult, adminApptStatusId, Entity.appointment_status, AdminAppointmentId, ], ); if (Entity.date != formattedDate) { position = null; await this.connection.query( `update appointment_queue set shift_id = ?,date = ?,position = ? where id = ?`, [ AdminShiftDetails.id, Entity.date, position, AdminAppointmentQueue.id, ], ); } else { await this.connection.query( `update appointment_queue set shift_id = ?,position = ?,date = ? where id = ?`, [ AdminShiftDetails.id, position, Entity.date, AdminAppointmentQueue.id, ], ); } await this.connection.query( `update appointment_payment set date = ? where id = ?`, [Entity.date, AdminAppointmentPayment.id], ); const [result] = await this.dynamicConnection.query( `SELECT CONCAT( CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END, appointment.id ) id, patients.id patient_id, patients.aayush_unique_id, CONCAT("PT",patients.id) plenome_patient_id, patients.patient_name,patients.gender,patients.age, patients.mobileno,opd_details.id opd_id, patients.dial_code,patients.email,patients.image, patients.abha_address,patients.ABHA_number, CASE WHEN appointment.source = 'Online' THEN 'Online Consultation ' ELSE 'Offline Consultation' END AS consultingType, CONCAT("Dr. ",staff.name," ",staff.surname) doctorName, staff.id doctor_id, COALESCE(GROUP_CONCAT(DISTINCT specialist.specialist_name),"-") AS doctorSpecialist, appointment.source,appointment.global_shift_id,appointment.shift_id, appointment.module, CONCAT(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.id) appointment_id, DATE_FORMAT(appointment.date, '%D %b %Y') appointmentDate, appointment.date, DATE_FORMAT(appointment.time, '%h:%i %p') appointmentTime, TIME(appointment.time) time, CONCAT(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) slot, appointment_status.status appointment_status, appointment.appointment_status_id, appointment.is_token_verified, appointment.is_consultation_closed, appointment_status.color_code, appointment_queue.position tokenNumber, appointment.message, CASE WHEN appointment.doctor IS NULL THEN patient_charges.temp_standard_charge ELSE patient_charges.standard_charge END consultFees, CASE WHEN appointment.doctor IS NULL THEN patient_charges.temp_tax ELSE patient_charges.tax END taxPercentage, CAST(patient_charges.additional_charge AS DECIMAL(10,2)) as additional_charge,patient_charges.additional_charge_note, patient_charges.discount_percentage,CAST(patient_charges.discount_amount AS DECIMAL(10,2)) as discount_amount, patient_charges.id patientChargeId, CASE WHEN appointment.doctor IS NULL THEN FORMAT((((patient_charges.temp_standard_charge + patient_charges.additional_charge - patient_charges.discount_amount) * patient_charges.temp_tax)/100),2) ELSE FORMAT((((patient_charges.standard_charge + patient_charges.additional_charge - patient_charges.discount_amount) * patient_charges.tax)/100),2) END taxAmount, CAST(patient_charges.balance AS DECIMAL(10,2)) netAmount, CAST(patient_charges.balance AS DECIMAL(10,2)) balanceAmount, CAST(patient_charges.temp_amount AS DECIMAL(10,2)) hospital_amount, patient_charges.charge_id chargesId, CAST(patient_charges.temp_standard_charge AS DECIMAL(10,2)) hospital_standard_charge, patient_charges.temp_tax hospital_tax, CAST(patient_charges.temp_apply_charge AS DECIMAL(10,2)) hospital_apply_charge, CONCAT("TRID",transactions.id) transactionID, transactions.id trnId, transactions.partial_payment_mode temp_payment_mode, transactions.payment_mode,transactions.payment_date,transactions.payment_method, transactions.card_division,transactions.card_type,transactions.card_transaction_id, transactions.card_bank_name,transactions.net_banking_division, transactions.net_banking_transaction_id,transactions.upi_id, transactions.upi_bank_name,transactions.upi_transaction_id, transactions.cash_transaction_id, transactions.actual_paid_amount, transactions.wallet_paid_amount, transactions.temp_actual_paid_amount, transactions.temp_wallet_paid_amount, CONCAT( COALESCE(transactions.net_banking_transaction_id,""), COALESCE(transactions.card_transaction_id,""), COALESCE(transactions.upi_transaction_id,""), COALESCE(transactions.payment_reference_number,""), COALESCE(transactions.cash_transaction_id,"") ) payment_transaction_id, CASE WHEN patient_charges.payment_status = 'paid' THEN 'Payment done.' WHEN patient_charges.payment_status = 'partially_paid' and appointment.doctor IS NULL THEN 'Partially Paid.' ELSE 'Need payment.' END AS payment_status FROM appointment LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN opd_details ON opd_details.case_reference_id = appointment.case_reference_id LEFT JOIN appointment_status ON appointment_status.id = appointment.appointment_status_id LEFT JOIN staff ON staff.id = appointment.doctor LEFT JOIN ( SELECT pc.* FROM ( SELECT * FROM patient_charges ORDER BY date ASC ) pc GROUP BY pc.opd_id ) AS patient_charges ON patient_charges.opd_id = opd_details.id LEFT JOIN transactions ON transactions.id = patient_charges.transaction_id LEFT JOIN doctor_shift ON doctor_shift.id = appointment.shift_id LEFT JOIN appointment_queue ON appointment.id = appointment_queue.appointment_id LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1 WHERE appointment.id = ? GROUP BY patient_id,patient_name,gender,age,mobileno,email,ABHA_number,consultingType,opd_id, doctorName,doctor_id,source,appointment_id,appointmentDate,appointmentTime,slot, abha_address,appointment_status,appointment_status_id,color_code,tokenNumber, message,consultFees,taxPercentage,taxAmount,netAmount,transactionID,payment_mode, payment_date,balanceAmount,payment_status,date `, [numb], ); const verifyData = { mobilenumber: '91' + result.mobileno, patname: ' ' + result.patient_name, date: result.appointmentDate, doctor: result.doctorName, }; const emailData = { email: result.email, name: ' ' + result.patient_name, drname: 'Dr.' + result.doctorName, date: result.appointmentDate, time: result.appointmentTime, HosName: result.hospital_name, location: result.hospital_address, }; axios.post( 'https://notifications.plenome.com/sending-sms-appointment-reschedule', verifyData, ); axios.post( 'https://notifications.plenome.com/email-appointment-rescheduled', emailData, ); try { const notifydata_URL = 'http://13.200.35.19:7000/send-notification/to-profile'; const notifyaddress_data = { patient_id: AdminPatId, title: 'Regarding Your Appointment Reschedule', body: 'Your appointment has been booked successfully!!', module: 'Appointment', }; axios.post(notifydata_URL, notifyaddress_data); } catch (error) { console.error('Error while sending notification:', error); } console.log(await this.dynamicConnection.query(`select * from patient_charges where id = ?`, [HosPatientCharges.patient_charge_id])); return [ { status: 'success', message: 'Appointment updated successfully.', transactionId: result.transactionID ? result.transactionID.toString() : null, updated_value: result, }, ]; } catch (error) { throw new Error('error is : ' + error); } } else { return { status: 'failed', message: 'cannot change doctor.', }; } } else { console.log("doctor illa"); if (Entity.doctor) { console.log("doctor pudhusu irukku"); const [getHosStaffdetails] = await this.dynamicConnection.query( `select * from staff where id = ?`, [Entity.doctor], ); const [HosChargeId] = await this.dynamicConnection.query( `select * from shift_details where staff_id = ?`, [Entity.doctor], ); const [getAmountDetails] = await this.dynamicConnection.query( ` select charges.standard_charge,tax_category.percentage tax_percentage, 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 = ?`, [HosChargeId.charge_id], ); const HosssVisitDetailsId = await getstaff_id.visit_details_id; const [HossPatientCharges] = await this.dynamicConnection.query( `select * from visit_details where id = ?`, [HosssVisitDetailsId], ); const [HossAppointQueue] = await this.dynamicConnection.query( `select * from appointment_queue where appointment_id = ?`, [numb], ); const [HossAppointPayment] = await this.dynamicConnection.query( `select * from appointment_payment where appointment_id = ?`, [numb], ); const [HossssPatientCharges] = await this.dynamicConnection.query( `select * from patient_charges where id = ?`, [HossPatientCharges.patient_charge_id], ); let payment_status; if ( Entity.payment_mode == 'Paylater' || Entity.payment_mode == 'Offline' || Entity.payment_mode == 'cheque' || Entity.payment_mode == 'offline' || !Entity.payment_mode ) { payment_status = HossssPatientCharges.payment_status; } else { payment_status = 'paid'; } const total: number = Number(getAmountDetails.amount); let Balance: number = HossssPatientCharges.balance; if ( HossssPatientCharges?.payment_status?.toLocaleLowerCase() == 'partially_paid' ) { Balance = (total - Number(HossssPatientCharges.temp_amount)) * -1; } else { Balance = total * -1; } // let Balance = 0; await this.dynamicConnection.query( `update patient_charges set date= ?, charge_id=?,standard_charge=?, tax=?,apply_charge=?,amount=?, total = ?, balance = ?, payment_status = ? where id = ?`, [ Entity.date + ' ' + Entity.time, HosChargeId.charge_id, getAmountDetails.standard_charge, getAmountDetails.tax_percentage, getAmountDetails.standard_charge, getAmountDetails.amount, total, Balance, payment_status, HossPatientCharges.patient_charge_id, ], ); await this.dynamicConnection.query( `update visit_details set appointment_date = ?,cons_doctor = ?, live_consult = ? where id = ?`, [ Entity.date + ' ' + Entity.time, Entity.doctor, Entity.live_consult, HosssVisitDetailsId, ], ); await this.dynamicConnection.query( `update appointment set date = ?,time = ?,doctor=?, global_shift_id = ?,shift_id = ?,live_consult = ?,amount=?, appointment_status_id = ?,appointment_status = ? where id = ?`, [ Entity.date, Entity.time, Entity.doctor, Entity.global_shift_id, Entity.shift_id, Entity.live_consult, getAmountDetails.amount, 2, 'Reserved', numb, ], ); let position: number; if (Entity.date != formattedDate) { position = null; await this.dynamicConnection.query( `update appointment_queue set shift_id = ?, date = ?,position = ?,staff_id = ? where id = ?`, [ Entity.shift_id, Entity.date, position, Entity.doctor, HossAppointQueue.id, ], ); } else { const [getlastpsn] = await this.dynamicConnection.query( `SELECT * FROM appointment_queue where staff_id = ? and date = ? and shift_id = ? order by position desc limit 1 `, [Entity.doctor, Entity.date, Entity.shift_id], ); if (getlastpsn) { position = getlastpsn.position + 1; } else { position = 1; } await this.dynamicConnection.query( `update appointment_queue set shift_id = ?, date = ?,staff_id = ?,position = ? where id = ?`, [ Entity.shift_id, Entity.date, Entity.doctor, position, HossAppointQueue.id, ], ); } await this.dynamicConnection.query( `update appointment_payment set date = ? where id = ?`, [Entity.date, HossAppointPayment.id], ); const [getAdminnAppointdetails] = await this.connection.query( `select * from appointment where hos_appointment_id = ? and Hospital_id = ?`, [numb, Entity.Hospital_id], ); const AdminnnAppointmentId = await getAdminnAppointdetails.id; const [getAdminStaffdetails] = await this.connection.query( `select * from staff where email = ?`, [getHosStaffdetails.email], ); const [getAdminnGlobalShiftId] = await this.connection.query( `select * from global_shift where hospital_global_shift_id = ?`, [Entity.global_shift_id], ); const [getAdminnShiftId] = await this.connection.query( `select * from doctor_shift where hospital_doctor_shift_id = ?`, [Entity.shift_id], ); const [getAdminchargeId] = await this.connection.query( `select * from shift_details where staff_id = ?`, [getAdminStaffdetails.id], ); const [getAdminAmountDetails] = await this.connection.query( ` select charges.standard_charge,tax_category.percentage tax_percentage, 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 = ?`, [getAdminchargeId.charge_id], ); const AdminnVisitDetailsId = await getAdminnAppointdetails.visit_details_id; const [AdminnPatientCharges] = await this.connection.query( `select * from visit_details where id = ?`, [AdminnVisitDetailsId], ); const [AdminnAppointQueue] = await this.connection.query( `select * from appointment_queue where appointment_id = ?`, [AdminnnAppointmentId], ); const [AdminnAppointPayment] = await this.connection.query( `select * from appointment_payment where appointment_id = ?`, [AdminnnAppointmentId], ); if (Balance > 0) { const [getCoinValue] = await this.connection.query( `select coin_amount from coin_value order by created_at desc limit 1`, ); const coins = (Balance / getCoinValue.coin_amount).toFixed(2); const [getPatDetails] = await this.dynamicConnection.query( `select mobileno from patients where id = ?`, [getstaff_id.patient_id], ); if (getPatDetails.mobileno.length > 10) { getPatDetails.mobileno = await getPatDetails.mobileno.slice(-10); } const [getUserId] = await this.connection.query( `select id from users where username = ? `, ['91' + getPatDetails.mobileno], ); const coins_expiry_time = new Date(); coins_expiry_time.setFullYear( coins_expiry_time.getFullYear() + 100, ); await this.connection.query( `insert into aayush_coins (user_id,total_coins_remaining, total_coins_issued,coins_expiry_time,module) values (?,?,?,?,?)`, [ await getUserId.id, coins, coins, coins_expiry_time, moduleNumb, ], ); } await this.connection.query( `update patient_charges set date= ?,charge_id=?,standard_charge=?, tax=?,apply_charge=?,amount=?, total = ?, balance = ?, payment_status = ? where id = ?`, [ Entity.date + ' ' + Entity.time, getAdminchargeId.charge_id, getAdminAmountDetails.standard_charge, getAdminAmountDetails.tax_percentage, getAdminAmountDetails.standard_charge, getAdminAmountDetails.amount, total, Balance, payment_status, AdminnPatientCharges.patient_charge_id, ], ); if (!Entity.txn_id) { Entity.txn_id = 'NA'; } if (!Entity.bank_ref_id) { Entity.bank_ref_id = 'NA'; } if (!Entity.pg_ref_id) { Entity.pg_ref_id = 'NA'; } if ( HossssPatientCharges.payment_status == 'partially_paid' && payment_status == 'paid' ) { const [getAdmintransactionDetails] = await this.connection.query( `select * from transactions where appointment_id = ? `, [getAdminnAppointdetails.id], ); const [getHosTrdID] = await this.dynamicConnection.query( `select * from transactions where appointment_id = ?`, [numb], ); await this.connection.query( `UPDATE transactions SET amount = ?, received_by_name = ?, payment_mode = ?, actual_paid_amount = ?, wallet_paid_amount = ? WHERE id = ?`, [ Balance * -1, Entity.received_by_name, Entity.payment_mode, Entity.actual_amount_paid, Entity.amount_from_coins, getAdmintransactionDetails.id, ], ); await this.dynamicConnection.query( `UPDATE transactions SET amount = ?, received_by_name = ?, payment_mode = ?, actual_paid_amount = ?, wallet_paid_amount = ? WHERE id = ?`, [ Balance * -1, Entity.received_by_name, Entity.payment_mode, Entity.actual_amount_paid, Entity.amount_from_coins, getHosTrdID.id, ], ); try { this.newBillingService.addtransactionInfo( getAdmintransactionDetails.id.toString(), ); } catch (error) { console.log('Error while adding transaction info to mangodb'); } } if ( payment_status == 'paid' && HossssPatientCharges.payment_status == 'unpaid' ) { const [getHosApptDetails] = await this.dynamicConnection.query( `select * from appointment where id = ?`, [numb], ); const insert_transactionsHos = await this.dynamicConnection.query( `insert into transactions ( txn_id, pg_ref_id, bank_ref_id, type, section, patient_id, case_reference_id, patient_charges_id, appointment_id, amount, payment_mode, payment_date, received_by_name, actual_paid_amount, wallet_paid_amount) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) `, [ Entity.txn_id, Entity.pg_ref_id, Entity.bank_ref_id, 'payment', 'Appointment', getHosApptDetails.patient_id, getHosApptDetails.case_reference_id, HossssPatientCharges.id, getHosApptDetails.id, Balance * -1, Entity.payment_mode, Entity.payment_date, Entity.received_by_name, Entity.actual_amount_paid, Entity.amount_from_coins, ], ); await this.dynamicConnection.query( `update patient_charges set transaction_id = ? where id = ?`, [ insert_transactionsHos.insertId, HossPatientCharges.patient_charge_id, ], ); const insert_transactions = await this.connection.query( `insert into transactions ( txn_id, pg_ref_id, bank_ref_id, type, section, patient_id, case_reference_id, patient_charges_id, appointment_id, amount, payment_mode, payment_date, Hospital_id, hos_transaction_id,received_by_name, actual_paid_amount, wallet_paid_amount) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) `, [ Entity.txn_id, Entity.pg_ref_id, Entity.bank_ref_id, 'payment', 'Appointment', getAdminnAppointdetails.patient_id, getAdminnAppointdetails.case_reference_id, AdminnPatientCharges.patient_charge_id, getAdminnAppointdetails.id, Balance * -1, Entity.payment_mode, Entity.payment_date, Entity.Hospital_id, insert_transactionsHos.insertId, Entity.received_by_name, Entity.actual_amount_paid, Entity.amount_from_coins, ], ); await this.dynamicConnection.query( `update patient_charges set transaction_id = ? where id = ?`, [ insert_transactions.insertId, AdminnPatientCharges.patient_charge_id, ], ); try { this.newBillingService.addtransactionInfo( insert_transactions.insertId.toString(), ); } catch (error) { console.log('Error while adding transaction info to mangodb'); } } await this.connection.query( `update visit_details set appointment_date = ?,cons_doctor = ?, live_consult = ? where id = ?`, [ Entity.date + ' ' + Entity.time, getAdminStaffdetails.id, Entity.live_consult, AdminnVisitDetailsId, ], ); await this.connection.query( `update appointment set date = ?,time = ?,doctor=?, global_shift_id = ?,shift_id = ?,live_consult = ?,amount=?,appointment_status_id = ?,appointment_status = ? where id = ?`, [ Entity.date, Entity.time, getAdminStaffdetails.id, getAdminnGlobalShiftId.id, getAdminnShiftId.id, Entity.live_consult, getAdminAmountDetails.amount, 2, 'Reserved', AdminnnAppointmentId, ], ); if (Entity.date != datePart) { position = null; await this.connection.query( `update appointment_queue set shift_id = ?, date = ?,position = ?,staff_id = ? where id = ?`, [ getAdminnShiftId.id, Entity.date, position, getAdminStaffdetails.id, AdminnAppointQueue.id, ], ); } else { const [getlastpsn] = await this.connection.query( `SELECT * FROM appointment_queue where staff_id = ? and date = ? and shift_id = ? order by position desc limit 1 `, [getAdminStaffdetails.id, Entity.date, getAdminnShiftId.id], ); if (getlastpsn) { position = getlastpsn.position + 1; } else { position = 1; } await this.connection.query( `update appointment_queue set shift_id = ?, date = ?,staff_id = ?, position = ? where id = ?`, [ getAdminnShiftId.id, Entity.date, getAdminStaffdetails.id, position, AdminnAppointQueue.id, ], ); } await this.connection.query( `update appointment_payment set date = ? where id = ?`, [Entity.date, AdminnAppointPayment.id], ); const [reslt] = await this.dynamicConnection.query( `select patients.id patient_id, concat("PT",patients.id) plenome_patient_id, patients.patient_name,patients.gender,patients.age, patients.mobileno, patients.email, patients.ABHA_number, CASE WHEN appointment.live_consult = 'yes' THEN 'Online Consultation ' ELSE 'Offline Consultation' END AS consultingType, concat("Dr. ",staff.name," ",staff.surname) doctorName, staff.id doctor_id, coalesce( GROUP_CONCAT(DISTINCT specialist.specialist_name) ,"-")AS doctorSpecialist, appointment.source, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.id) appointment_id, DATE_FORMAT(appointment.date, '%D %b %Y') appointmentDate, date(appointment.date) date, DATE_FORMAT(appointment.time, '%h:%i %p') appointmentTime, concat(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) slot, appointment_status.status appointment_status, appointment.appointment_status_id, appointment_status.color_code, appointment_queue.position tokenNumber, appointment.message, patient_charges.standard_charge consultFees, patient_charges.tax taxPercentage, case when appointment.doctor is null then format((((patient_charges.temp_standard_charge + patient_charges.additional_charge - patient_charges.discount_amount) * patient_charges.temp_tax)/100 ),2) else format((((patient_charges.standard_charge + patient_charges.additional_charge - patient_charges.discount_amount) * patient_charges.tax)/100 ),2) end taxAmount, patient_charges.total netAmount, patient_charges.balance balanceAmount, transactions.id transactionID, transactions.payment_mode, transactions.payment_date, CASE WHEN patient_charges.payment_status = 'paid' THEN 'Payment done.' ELSE 'Need payment.' END AS payment_status from appointment left join patients on patients.id = appointment.patient_id left join appointment_status on appointment_status.id = appointment.appointment_status_id left join staff on staff.id = appointment.doctor left join transactions on transactions.appointment_id = appointment.id left join doctor_shift on doctor_shift.id = appointment.shift_id left join opd_details on opd_details.case_reference_id = appointment.case_reference_id left join patient_charges on patient_charges.opd_id = opd_details.id left join appointment_queue on appointment.id = appointment_queue.appointment_id LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1 where appointment.id = ? group by patient_id,patient_name,gender,age,mobileno,email,ABHA_number,consultingType, doctorName,doctor_id,source,appointment_id,appointmentDate,appointmentTime,slot, appointment_status,appointment_status_id,color_code,tokenNumber,message,consultFees,taxPercentage, taxAmount,netAmount,transactionID,payment_mode,payment_date,balanceAmount,payment_status,date `, [numb], ); const verifyData = { mobilenumber: '91' + reslt.mobileno, patname: ' ' + reslt.patient_name, date: reslt.appointmentDate, doctor: reslt.doctorName, }; const emailData = { email: reslt.email, name: ' ' + reslt.patient_name, drname: 'Dr.' + reslt.doctorName, date: reslt.appointmentDate, time: reslt.appointmentTime, HosName: reslt.hospital_name, location: reslt.hospital_address, }; axios.post( 'https://notifications.plenome.com/sending-sms-appointment-reschedule', verifyData, ); axios.post( 'https://notifications.plenome.com/email-appointment-rescheduled', emailData, ); try { const notifydata_URL = 'http://13.200.35.19:7000/send-notification/to-profile'; const notifyaddress_data = { patient_id: getAdminnAppointdetails.patient_id, title: 'Regarding Your Appointment Reschedule', body: 'Your appointment has been booked successfully!!', module: 'Appointment', }; axios.post(notifydata_URL, notifyaddress_data); } catch (error) { console.error('Error while sending notification:', error); } let trans_id: any; if (reslt.trans_id) { trans_id = reslt.transactionID.toString(); } await this.connection.query( `update patient_charges set date= ?,payment_status = ?, balance = ? where id = ?`, [ Entity.date + ' ' + Entity.time, payment_status, 0, AdminnPatientCharges.patient_charge_id, ], ); return [ { status: 'success', message: 'Appointment Updated successfully', transactionId: trans_id, updated_value: reslt, }, ]; } else { console.log("dotor illa"); const [getAPPDetails] = await this.dynamicConnection.query( `select * from appointment where id = ?`, [numb], ); const HOSSVisitDetailsId = await getAPPDetails.visit_details_id; const [HOSSPatientCharges] = await this.dynamicConnection.query( `select * from visit_details where id = ?`, [HOSSVisitDetailsId], ); const [HOSSAppointQueue] = await this.dynamicConnection.query( `select * from appointment_queue where appointment_id = ?`, [numb], ); const [HOSSAppointPayment] = await this.dynamicConnection.query( `select * from appointment_payment where appointment_id = ?`, [numb], ); await this.dynamicConnection.query( `update patient_charges set date= ? where id = ?`, [Entity.date + ' ' + Entity.time, HOSSPatientCharges.patient_charge_id], ); await this.dynamicConnection.query( `update visit_details set appointment_date = ?, live_consult = ? where id = ?`, [ Entity.date + ' ' + Entity.time, Entity.live_consult, HOSSVisitDetailsId, ], ); await this.dynamicConnection.query( `update appointment set date = ?,time = ?, live_consult = ?,appointment_status_id = ? ,appointment_status = ? where id = ?`, [ Entity.date, Entity.time, Entity.live_consult, Entity.appointment_status_id, Entity.appointment_status, numb, ], ); await this.dynamicConnection.query( `update appointment_queue set date = ? where id = ?`, [Entity.date, HOSSAppointQueue.id], ); await this.dynamicConnection.query( `update appointment_payment set date = ? where id = ?`, [Entity.date, HOSSAppointPayment.id], ); const [getADMINAPPDetails] = await this.connection.query( `select * from appointment where hos_appointment_id = ? and Hospital_id = ?`, [numb, Entity.Hospital_id], ); const ADMINVisitDetailsId = await getADMINAPPDetails.visit_details_id; const [ADMINPatientCharges] = await this.connection.query( `select * from visit_details where id = ?`, [ADMINVisitDetailsId], ); const [ADMINAppointQueue] = await this.connection.query( `select * from appointment_queue where appointment_id = ?`, [getADMINAPPDetails.id], ); const [ADMINAppointPayment] = await this.connection.query( `select * from appointment_payment where appointment_id = ?`, [getADMINAPPDetails.id], ); await this.connection.query( `update patient_charges set date= ? , balance = ? where id = ?`, [Entity.date + ' ' + Entity.time, 0, ADMINPatientCharges.patient_charge_id], ); await this.connection.query( `update visit_details set appointment_date = ?, live_consult = ? where id = ?`, [ Entity.date + ' ' + Entity.time, Entity.live_consult, ADMINVisitDetailsId, ], ); await this.connection.query( `update appointment set date = ?,time = ?,live_consult = ?,appointment_status_id = ?,appointment_status = ? where id = ?`, [ Entity.date, Entity.time, Entity.live_consult, adminApptStatusId, Entity.appointment_status, getADMINAPPDetails.id, ], ); await this.dynamicConnection.query( `update appointment_queue set date = ? where id = ?`, [Entity.date, ADMINAppointQueue.id], ); await this.dynamicConnection.query( `update appointment_payment set date = ? where id = ?`, [Entity.date, ADMINAppointPayment.id], ); const [reslt] = await this.dynamicConnection.query( `select patients.id patient_id, concat("PT",patients.id) plenome_patient_id, patients.patient_name,patients.gender,patients.age, patients.mobileno, patients.email, patients.ABHA_number, CASE WHEN appointment.live_consult = 'yes' THEN 'Online Consultation ' ELSE 'Offline Consultation' END AS consultingType, concat("Dr. ",staff.name," ",staff.surname) doctorName, staff.id doctor_id, coalesce( GROUP_CONCAT(DISTINCT specialist.specialist_name) ,"-")AS doctorSpecialist, appointment.source, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.id) appointment_id, DATE_FORMAT(appointment.date, '%D %b %Y') appointmentDate, date(appointment.date) date, DATE_FORMAT(appointment.time, '%h:%i %p') appointmentTime, concat(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) slot, appointment_status.status appointment_status, appointment.appointment_status_id, appointment_status.color_code, appointment_queue.position tokenNumber, appointment.message, patient_charges.standard_charge consultFees, patient_charges.tax taxPercentage, case when appointment.doctor is null then format((((patient_charges.temp_standard_charge + patient_charges.additional_charge - patient_charges.discount_amount) * patient_charges.temp_tax)/100 ),2) else format((((patient_charges.standard_charge + patient_charges.additional_charge - patient_charges.discount_amount) * patient_charges.tax)/100 ),2) end taxAmount, patient_charges.total netAmount, patient_charges.balance balanceAmount, transactions.id transactionID, transactions.payment_mode, transactions.payment_date, CASE WHEN patient_charges.payment_status = 'paid' THEN 'Payment done.' ELSE 'Need payment.' END AS payment_status from appointment left join patients on patients.id = appointment.patient_id left join appointment_status on appointment_status.id = appointment.appointment_status_id left join staff on staff.id = appointment.doctor left join transactions on transactions.appointment_id = appointment.id left join doctor_shift on doctor_shift.id = appointment.shift_id left join opd_details on opd_details.case_reference_id = appointment.case_reference_id left join patient_charges on patient_charges.opd_id = opd_details.id left join appointment_queue on appointment.id = appointment_queue.appointment_id LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1 where appointment.id = ? group by patient_id,patient_name,gender,age,mobileno,email,ABHA_number,consultingType, doctorName,doctor_id,source,appointment_id,appointmentDate,appointmentTime,slot, appointment_status,appointment_status_id,color_code,tokenNumber,message,consultFees,taxPercentage, taxAmount,netAmount,transactionID,payment_mode,payment_date,balanceAmount,payment_status,date `, [numb], ); const verifyData = { mobilenumber: '91' + reslt.mobileno, patname: ' ' + reslt.patient_name, date: reslt.appointmentDate, doctor: reslt.doctorName, }; const emailData = { email: reslt.email, name: ' ' + reslt.patient_name, drname: 'Dr.' + reslt.doctorName, date: reslt.appointmentDate, time: reslt.appointmentTime, HosName: reslt.hospital_name, location: reslt.hospital_address, }; axios.post( 'https://notifications.plenome.com/sending-sms-appointment-reschedule', verifyData, ); axios.post( 'https://notifications.plenome.com/email-appointment-rescheduled', emailData, ); try { const notifydata_URL = 'http://13.200.35.19:7000/send-notification/to-profile'; const notifyaddress_data = { patient_id: getAPPDetails.patient_id, title: 'Regarding Your Appointment Reschedule', body: 'Your appointment has been booked successfully!!', module: 'Appointment', }; axios.post(notifydata_URL, notifyaddress_data); } catch (error) { console.error('Error while sending notification:', error); } return [ { status: 'success', message: 'Appointment Updated successfully', transactionId: reslt.transactionID.toString(), updated_values: reslt, }, ]; } } } catch (error) { return [{ error: error }]; } } else { return [ { status: 'failed', message: 'Enter hospital_id to update the appointment ', }, ]; } } async updateChargeDetails(id: number, Entity: UpdateAppointmentcharge) { console.log(Entity, 'Entity', id); if (Entity.Hospital_id) { try { const [getAdminPatChargeDetails] = await this.connection.query( `select * from patient_charges where Hospital_id = ? and hos_patient_charges_id = ?`, [Entity.Hospital_id, id], ); console.log( getAdminPatChargeDetails.balance + (Entity.additional_charge || 0) - (Entity.discount_amount || 0), ); console.log( getAdminPatChargeDetails.balance, Entity.additional_charge, Entity.discount_amount, ); // const balance = await getAdminPatChargeDetails.balance - (Entity.additional_charge || 0) + (Entity.discount_amount || 0); const balance = Entity.total * -1; await this.dynamicConnection.query( `update patient_charges set additional_charge = ?, additional_charge_note = ?, discount_percentage = ?, discount_amount = ?, balance = ?, total = ? where id = ?`, [ Entity.additional_charge, Entity.additional_charge_note, Entity.discount_percentage, Entity.discount_amount, balance, Entity.total, id, ], ); await this.connection.query( `update patient_charges set additional_charge = ?, additional_charge_note = ?, discount_percentage = ?, discount_amount = ?, balance = ?, total = ? where id = ?`, [ Entity.additional_charge, Entity.additional_charge_note, Entity.discount_percentage, Entity.discount_amount, balance, Entity.total, getAdminPatChargeDetails.id, ], ); console.log( await this.dynamicConnection.query( `select * from patient_charges where id = ?`, [id], ), 'resp', ); return [ { status: 'success', message: 'charges modified successfully', }, ]; } catch (error) { throw new Error(error); } } else { return [ { status: 'failed', message: 'Enter hospital_id to get the appointment information', }, ]; } } async cancelAppointment(id: string, Entity: CancelAppointment) { let numb = id.replace(/[a-zA-Z]/g, ''); if (Entity.Hospital_id) { try { await this.dynamicConnection.query( `update appointment set appointment_status = ?, appointment_status_id = ?,appointment_cancellation_reason = ? where id = ?`, ['Cancelled', 4, Entity.cancellationReason, numb], ); // await this.dynamicConnection.query( // `update appointment_queue set position = ? where appointment_id = ?`, // [null, numb], // ); const [adminApptId] = await this.connection.query( `select id, patient_id,doctor from appointment where Hospital_id = ? and hos_appointment_id = ?`, [Entity.Hospital_id, numb], ); await this.connection.query( `update appointment set appointment_status = ?, appointment_status_id = ?,appointment_cancellation_reason = ? where id = ?`, ['Cancelled', 4, Entity.cancellationReason, adminApptId.id], ); // await this.connection.query( // `update appointment_queue set position = null where appointment_id = ?`, // [adminApptId.id], // ); const [getHosName] = await this.connection.query( `select * from hospitals where plenome_id = ?`, [Entity.Hospital_id], ); const HosName = await getHosName.hospital_name; const HosAddress = await getHosName.address; const [getTransactionIdUsingApptNo] = await this.dynamicConnection.query( `select * from transactions where appointment_id = ?`, [numb], ); if (getTransactionIdUsingApptNo) { let [pat_char] = await this.dynamicConnection.query( `select * from patient_charges where transaction_id = ? order by created_at asc`, [getTransactionIdUsingApptNo.id], ); if (getTransactionIdUsingApptNo) { if (getTransactionIdUsingApptNo.payment_gateway) { if ( getTransactionIdUsingApptNo.payment_gateway.toLocaleLowerCase() == 'razorpay' ) { const [getPatientChargesDetails] = await this.dynamicConnection.query( `select * from patient_charges where transaction_id = ?`, [getTransactionIdUsingApptNo.id], ); const razorpay = new Razorpay({ key_id: process.env.RAZORPAY_KEY_ID, key_secret: process.env.RAZORPAY_KEY_SECRET, }); const refundData: any = {}; // Default full refund const paymentDetails = await razorpay.payments.fetch( getTransactionIdUsingApptNo.payment_id, ); if (paymentDetails.amount) { let refndAmt; if (paymentDetails.amount <= 100) { refndAmt = paymentDetails.amount; } else { refndAmt = paymentDetails.amount - paymentDetails.amount * 0.052; } refundData.amount = refndAmt; } await razorpay.payments.refund( getTransactionIdUsingApptNo.payment_id, refundData, ); await this.dynamicConnection.query( `update patient_charges set payment_status = 'refunded' where id = ?`, [getPatientChargesDetails.id], ); const [getTransactionIdUsingApptNoHMS] = await this.dynamicConnection.query( `select * from transactions where appointment_id = ?`, [numb], ); const [getTransactionIdUsingApptNoADMIN] = await this.connection.query( `select * from transactions where hos_transaction_id = ? and Hospital_id = ?`, [getTransactionIdUsingApptNoHMS.id, Entity.Hospital_id], ); const [getPatientChargesDetailsADMIN] = await this.connection.query( `select * from patient_charges where transaction_id = ?`, [getTransactionIdUsingApptNoADMIN.id], ); await this.connection.query( `update patient_charges set payment_status = 'refunded' where id = ?`, [getPatientChargesDetailsADMIN.id], ); } } else { const [getCoinValue] = await this.connection.query( `select coin_amount from coin_value order by created_at desc limit 1`, ); let Balance = 0; if (pat_char.payment_status == 'paid') { Balance = Number(pat_char.total); } else if ( pat_char.payment_status == 'partially_paid' && !getTransactionIdUsingApptNo.temp_appt_payment_gateway ) { Balance = Number(pat_char.temp_amount); } const coins = (Balance / getCoinValue.coin_amount).toFixed(2); const coins_expiry_time = new Date(); coins_expiry_time.setFullYear( coins_expiry_time.getFullYear() + 100, ); let moduleNumb; let numb; try { numb = parseInt(id.replace(/[a-zA-Z]/g, '')); moduleNumb = numb; } catch (error) { numb = parseInt(id); moduleNumb = 'APPN' + numb; } const [getPatDetails] = await this.dynamicConnection.query( `select mobileno from patients where id = ?`, [pat_char.patient_id], ); if (getPatDetails.mobileno.length > 10) { getPatDetails.mobileno = await getPatDetails.mobileno.slice(-10); } const [getUserId] = await this.connection.query( `select id from users where username = ? `, ['91' + getPatDetails.mobileno], ); if (parseFloat(coins) > 0.0) { await this.connection.query( `insert into aayush_coins (user_id,total_coins_remaining, total_coins_issued,coins_expiry_time,module) values (?,?,?,?,?)`, [ await getUserId.id, coins, coins, coins_expiry_time, moduleNumb, ], ); } await this.dynamicConnection.query( `update patient_charges set payment_status = 'refunded' where id = ?`, [pat_char.id], ); await this.connection.query( `update patient_charges set payment_status = 'refunded' where hos_patient_charges_id = ? and Hospital_id = ?`, [pat_char.id, Entity.Hospital_id], ); } if ( getTransactionIdUsingApptNo.temp_appt_payment_gateway && pat_char.payment_status == 'partially_paid' ) { if ( getTransactionIdUsingApptNo.temp_appt_payment_gateway.toLocaleLowerCase() == 'razorpay' ) { const [getPatientChargesDetails] = await this.dynamicConnection.query( `select * from patient_charges where transaction_id = ?`, [getTransactionIdUsingApptNo.id], ); const razorpay = new Razorpay({ key_id: process.env.RAZORPAY_KEY_ID, key_secret: process.env.RAZORPAY_KEY_SECRET, }); const refundData: any = {}; // Default full refund const paymentDetails = await razorpay.payments.fetch( getTransactionIdUsingApptNo.temp_appt_payment_id, ); if (paymentDetails.amount) { let refndAmt; if (paymentDetails.amount <= 100) { refndAmt = paymentDetails.amount; } else { refndAmt = paymentDetails.amount - paymentDetails.amount * 0.052; } refundData.amount = refndAmt; } await razorpay.payments.refund( getTransactionIdUsingApptNo.temp_appt_payment_id, refundData, ); await this.dynamicConnection.query( `update patient_charges set temp_payment_status = 'refunded' where id = ?`, [getPatientChargesDetails.id], ); const [getTransactionIdUsingApptNoHMS] = await this.dynamicConnection.query( `select * from transactions where appointment_id = ?`, [numb], ); const [getTransactionIdUsingApptNoADMIN] = await this.connection.query( `select * from transactions where hos_transaction_id = ? and Hospital_id = ?`, [getTransactionIdUsingApptNoHMS.id, Entity.Hospital_id], ); const [getPatientChargesDetailsADMIN] = await this.connection.query( `select * from patient_charges where transaction_id = ?`, [getTransactionIdUsingApptNoADMIN.id], ); await this.connection.query( `update patient_charges set temp_payment_status = 'refunded' where id = ?`, [getPatientChargesDetailsADMIN.id], ); } } } } const reslt = await this.dynamicConnection.query( `select patients.id, concat("PT",patients.id) plenome_patient_id, patients.patient_name,patients.gender,patients.age, patients.mobileno, patients.email, patients.ABHA_number, concat("Dr. ",staff.name," ",staff.surname) doctorName, staff.id doctor_id, appointment.source, concat("APPN",appointment.id) appointment_id, DATE_FORMAT(appointment.date, '%D %b %Y') appointmentDate, DATE_FORMAT(appointment.time, '%h:%i %p') appointmentTime, concat(DATE_FORMAT(doctor_shift.start_time, '%h:%i %p')," - ",DATE_FORMAT(doctor_shift.end_time, '%h:%i %p')) slot, appointment.appointment_status, appointment_queue.position, CASE WHEN appointment.live_consult = 'yes' THEN 'online' ELSE 'offline' END AS consultingType, appointment.message, patient_charges.standard_charge consultFees, patient_charges.tax taxPercentage, case when appointment.doctor is null then format((((patient_charges.temp_standard_charge + patient_charges.additional_charge - patient_charges.discount_amount) * patient_charges.temp_tax)/100 ),2) else format((((patient_charges.standard_charge + patient_charges.additional_charge - patient_charges.discount_amount) * patient_charges.tax)/100 ),2) end taxAmount, patient_charges.amount netAmount, transactions.id transactionID, transactions.payment_mode, transactions.payment_date, CASE WHEN transactions.id THEN 'Payment done.' ELSE 'Need payment.' END AS payment_status from appointment left join patients on patients.id = appointment.patient_id left join staff on staff.id = appointment.doctor left join transactions on transactions.appointment_id = appointment.id left join doctor_shift on doctor_shift.id = appointment.shift_id left join opd_details on opd_details.case_reference_id = appointment.case_reference_id left join patient_charges on patient_charges.opd_id = opd_details.id left join appointment_queue on appointment.id = appointment_queue.appointment_id where appointment.id = ?`, [numb], ); reslt[0].hospital_name = HosName; reslt[0].hospital_address = HosAddress; const verifyData = { mobileNumber: '91' + reslt[0].mobileno, name: ' ' + reslt[0].patient_name, date: reslt[0].appointmentDate + ' With ' + reslt[0].doctorName, reason: Entity.cancellationReason, }; const emailData = { email: reslt[0].email, name: ' ' + reslt[0].patient_name, date: reslt[0].appointmentDate + ' ' + reslt[0].appointmentDate, reason: Entity.cancellationReason, }; axios.post( 'https://notifications.plenome.com/sending-sms-appointment-cancelled', verifyData, ); axios.post( 'https://notifications.plenome.com/email-appointment-cancelled', emailData, ); try { const notifydata_URL = 'http://13.200.35.19:7000/send-notification/to-profile'; const notifyaddress_data = { patient_id: await adminApptId.patient_id, title: 'Appointment Cancellation', body: 'Your appointment has been Cancelled!!', module: 'Appointment', }; axios.post(notifydata_URL, notifyaddress_data); } catch (error) { console.error('Error while sending notification:', error); } try { await this.remove_summery_amount_from_cancel_app( numb, Entity.Hospital_id, ); } catch (error) { console.log('Error while deducting summery from the patients'); } return [ { status: 'success', message: 'Appointment Cancelled successfully', }, ]; } catch (error) { console.log(error, 'err'); return [error]; } } else { return [ { status: 'failed', message: 'Enter hospital_id to update the appointment ', }, ]; } } async makepayment(paymentDetails: PostAppointment, transaction_id: any) { if (paymentDetails.Hospital_id) { let numb: number; try { numb = transaction_id.replace(/[a-zA-Z]/g, ''); } catch (err) { numb = transaction_id; } try { // if (paymentDetails.payment_gateway) { // if ( // paymentDetails.payment_gateway.toLocaleLowerCase() == 'razorpay' // ) { // const razorpay = new Razorpay({ // key_id: process.env.RAZORPAY_KEY_ID, // key_secret: process.env.RAZORPAY_KEY_SECRET, // }); // try { // const [getPaymentGatewayDetails] = await this.connection.query( // `select * from hospital_payment_gateway_details where hospital_id = ? and payment_gateway = ?`, // [paymentDetails.Hospital_id, 'razorpay'], // ); // const SubMerchantdetails = JSON.parse( // JSON.stringify( // getPaymentGatewayDetails.gateway_account_details, // ), // ); // const submerchantAccountId = SubMerchantdetails.id; // const paymentDetail = await razorpay.payments.fetch( // paymentDetails.payment_id, // ); // let routeAmount = await paymentDetail.amount; // const transferPayload = { // transfers: [ // { // account: submerchantAccountId, // amount: Math.round(routeAmount - routeAmount * 0.052), // currency: 'INR', // notes: { // name: 'Gaurav Kumar', // roll_no: 'IEC2011025', // }, // linked_account_notes: ['roll_no'], // on_hold: false, // }, // ], // }; // const makeRoute = await razorpay.payments.transfer( // paymentDetails.payment_id, // transferPayload, // ); // } catch (error) { // console.log('error : ', error); // } // if ( // !paymentDetails.payment_id || // !paymentDetails.payment_reference_number // ) { // return { // status: 'failed', // message: // 'enter payment_id and payment_reference_number received form razorpay to update values', // }; // } // } // } await this.dynamicConnection.query( `update transactions set payment_reference_number = ?, received_by_name = ?, payment_id = ?, payment_gateway = ? where id = ?`, [ paymentDetails.payment_reference_number, paymentDetails.received_by_name, paymentDetails.payment_id, paymentDetails.payment_gateway, numb, ], ); const [getAdminTransactionId] = await this.connection.query( `select id from transactions where Hospital_id = ? and hos_transaction_id = ?`, [paymentDetails.Hospital_id, numb], ); await this.connection.query( `update transactions set payment_reference_number = ?, received_by_name = ?, payment_id = ?, payment_gateway = ? where id = ?`, [ paymentDetails.payment_reference_number, paymentDetails.received_by_name, paymentDetails.payment_id, paymentDetails.payment_gateway, getAdminTransactionId.id, ], ); if (!paymentDetails.payment_gateway) { return { status: 'failed', message: 'enter payment gateway to book appointment', }; } if (paymentDetails.payment_gateway.toLocaleLowerCase() == 'razorpay') { const [getPaymentGatewayDetails] = await this.connection.query( `select * from hospital_payment_gateway_details where hospital_id = ? and payment_gateway = ?`, [paymentDetails.Hospital_id, 'razorpay'], ); const [patient_id] = await this.connection.query( `select patient_id from transactions where id = ?`, [getAdminTransactionId.id], ); console.log(patient_id, 'patient_id'); const [get_aayush_unique_id] = await this.connection.query( `select aayush_unique_id from patients where id = ?`, [patient_id.patient_id], ); console.log(get_aayush_unique_id, 'get_aayush_unique_id'); console.log( paymentDetails.payment_id, getPaymentGatewayDetails, paymentDetails.Hospital_id, get_aayush_unique_id.aayush_unique_id, numb, getAdminTransactionId.id, 'sssssss', ); this.TransferToSubmerchant( paymentDetails.payment_id, getPaymentGatewayDetails, paymentDetails.Hospital_id, get_aayush_unique_id.aayush_unique_id, numb, getAdminTransactionId.id, ); if ( !paymentDetails.payment_reference_number || !paymentDetails.payment_id ) { if (!paymentDetails.payment_reference_number) { paymentDetails.payment_reference_number = 'NA'; } if (!paymentDetails.payment_id) { paymentDetails.payment_id = 'NA'; } return { status: 'failed', message: 'enter the payment reference number and payment_id received from razorpay for booking appointment', }; } } return { status: 'success', message: 'updated payment details successfully', }; } catch (error) { return { success: 'failed', message: 'unable to update payment details', }; } } else { return { status: 'failed', message: 'enter Hospital_id to get update transaction details', }; } } async V2findAllUpcoming( fromDate: string, toDate: string, doctorId: number, appointStatus: string, hospital_id: number, paymentStatus: string, limit: number, page: number, ): Promise { const offset = limit * (page - 1); try { let query = ` SELECT patients.patient_name, patients.id AS patient_id, concat("PT",patients.id) plenome_patient_id, CONCAT(DATE_FORMAT(appointment.date, '%D %b %Y'), ",", DATE_FORMAT(appointment.time, '%h:%i %p')) AS appointment_date, CONCAT(date(appointment.date), " ", time(appointment.time)) AS comp, CONCAT( patients.mobileno) AS Mobile, coalesce( GROUP_CONCAT(DISTINCT specialist.specialist_name) ,"-")AS doctorSpecialist, patients.dial_code, opd_details.id opd_id, appointment.doctor, coalesce(patients.salutation,"") salutation, CONCAT("Dr. ",staff.name, " ", staff.surname) AS consultant, appointment_status.status appointment_status, appointment.appointment_status_id, appointment.module, appointment_status.color_code, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.id) appointment_id, patient_charges.payment_status, patient_charges.total apptFees, CASE WHEN appointment_queue.position IS NOT NULL THEN appointment_queue.position ELSE CONCAT(" ", "- ") END AS appointment_token FROM appointment LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN staff ON staff.id = appointment.doctor LEFT JOIN appointment_queue ON appointment_queue.appointment_id = appointment.id left join appointment_status on appointment_status.id = appointment.appointment_status_id LEFT JOIN visit_details ON visit_details.id = appointment.visit_details_id left join opd_details on opd_details.id = visit_details.opd_details_id left join patient_charges on patient_charges.id = visit_details.patient_charge_id LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1 `; let countQuery = `SELECT COUNT(*) as totalCount from appointment left join visit_details on visit_details.id = appointment.visit_details_id left join patient_charges on patient_charges.id = visit_details.patient_charge_id `; let date; let values = []; if (fromDate && toDate) { date = ` date(appointment.date) >= date( '` + fromDate + `' ) and date(appointment.date) <= date( '` + toDate + `' ) `; } else if (fromDate) { date = ` date(appointment.date) >= date( '` + fromDate + `' ) `; } else if (toDate) { date = ` date(appointment.date) <= date( '` + toDate + `' ) `; } else { date = ` appointment.date > DATE(NOW()) `; } let where = `WHERE ` + date; if (doctorId) { where += ` and appointment.doctor = ?`; values.push(doctorId); } if (appointStatus) { where += ` and appointment.appointment_status_id = ?`; values.push(appointStatus); } else { where += ` and (appointment.appointment_status_id <> 6 and appointment.appointment_status_id <> 4)`; } if (paymentStatus) { where += ` and patient_charges.payment_status = ?`; values.push(paymentStatus); } let order = ` ORDER BY date(appointment.date) DESC, time(appointment.date) ASC limit ${limit} offset ${offset} `; let group = ` GROUP BY patients.patient_name, patients.id, appointment.date, appointment.time, patients.mobileno, patients.dial_code, appointment.doctor, staff.name, staff.surname, appointment_status.status, appointment.appointment_status_id, appointment_status.color_code, appointment.id, apptFees, opd_id, patient_charges.payment_status, appointment_queue.position `; let final = query + where + group + order; let finalCount = countQuery + where; const GetTodayAppointment = await this.dynamicConnection.query( final, values, ); const [getCount] = await this.dynamicConnection.query(finalCount, values); let output = { details: GetTodayAppointment, count: getCount.totalCount, }; return output; } catch (error) { throw new Error(error); } } async V2findAllHistory( fromDate: string, toDate: string, doctorId: number, appointStatus: string, hospital_id: number, paymentStatus: string, limit: number, page: number, ): Promise { const offset = limit * (page - 1); console.log(fromDate, toDate, 'aaa'); try { let query = ` SELECT patients.patient_name, patients.id AS patient_id, concat("PT",patients.id) plenome_patient_id, CONCAT(DATE_FORMAT(appointment.date, '%D %b %Y'), ",", DATE_FORMAT(appointment.time, '%h:%i %p')) AS appointment_date, CONCAT(date(appointment.date), " ", time(appointment.time)) AS comp, CONCAT( patients.mobileno) AS Mobile, coalesce( GROUP_CONCAT(DISTINCT specialist.specialist_name) ,"-")AS doctorSpecialist, patients.dial_code, opd_details.id opd_id, appointment.doctor, coalesce(patients.salutation,"") salutation, CONCAT("Dr. ",staff.name, " ", staff.surname) AS consultant, appointment_status.status appointment_status, appointment.appointment_status_id, appointment.module, appointment_status.color_code, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.id) appointment_id, patient_charges.payment_status, patient_charges.total apptFees, CASE WHEN appointment_queue.position IS NOT NULL THEN appointment_queue.position ELSE CONCAT(" ", "- ") END AS appointment_token FROM appointment LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN staff ON staff.id = appointment.doctor LEFT JOIN appointment_queue ON appointment_queue.appointment_id = appointment.id left join appointment_status on appointment_status.id = appointment.appointment_status_id LEFT JOIN visit_details ON visit_details.id = appointment.visit_details_id left join opd_details on opd_details.id = visit_details.opd_details_id left join patient_charges on patient_charges.id = visit_details.patient_charge_id LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1 `; let countQuery = `SELECT COUNT(*) as totalCount from appointment left join visit_details on visit_details.id = appointment.visit_details_id left join patient_charges on patient_charges.id = visit_details.patient_charge_id `; let date; let values = []; if (fromDate && toDate) { date = `( (date(appointment.date) >= date('${fromDate}') and date(appointment.date) <= date('${toDate}')) and (appointment.appointment_status_id = 6 or appointment.appointment_status_id = 4) )`; } else if (fromDate) { date = `( date(appointment.date) >= date('${fromDate}') and (appointment.appointment_status_id = 6 or appointment.appointment_status_id = 4) )`; } else if (toDate) { console.log('inga thaa vandhurukku'); date = `( date(appointment.date) <= date('${toDate}') and (appointment.appointment_status_id = 6 or appointment.appointment_status_id = 4) )`; } else { date = `( appointment.date < DATE(NOW()) or (appointment.appointment_status_id = 6 or appointment.appointment_status_id = 4) )`; } let where = `WHERE ` + date; if (doctorId) { where += ` and appointment.doctor = ?`; values.push(doctorId); } if (appointStatus) { where += ` and appointment.appointment_status_id = ?`; values.push(appointStatus); } if (paymentStatus) { where += ` and patient_charges.payment_status = ?`; values.push(paymentStatus); } let order = `ORDER BY date(appointment.date) DESC, time(appointment.date) ASC limit ${limit} offset ${offset} `; let group = ` GROUP BY patients.patient_name, patients.id, appointment.date, appointment.time, patients.mobileno, patients.dial_code, appointment.doctor, staff.name, staff.surname, appointment_status.status, appointment.appointment_status_id, appointment_status.color_code, appointment.id, apptFees, opd_id, patient_charges.payment_status, appointment_queue.position `; let final = query + where + group + order; let finalCount = countQuery + where; const GetTodayAppointment = await this.dynamicConnection.query( final, values, ); const [getCount] = await this.dynamicConnection.query(finalCount, values); let output = { details: GetTodayAppointment, count: getCount.totalCount, }; return output; } catch (error) { throw new Error(error); } } async V2findAllToday( fromDate: string, toDate: string, doctorId: number, appointStatus: string, hospital_id: number, paymentStatus: string, limit: number, page: number, ): Promise { const offset = limit * (page - 1); try { let query = ` SELECT patients.patient_name, patients.id AS patient_id, concat("PT",patients.id) plenome_patient_id, CONCAT(DATE_FORMAT(appointment.date, '%D %b %Y'), ",", DATE_FORMAT(appointment.time, '%h:%i %p')) AS appointment_date, CONCAT(date(appointment.date), " ", time(appointment.time)) AS comp, CONCAT( patients.mobileno) AS Mobile, coalesce( GROUP_CONCAT(DISTINCT specialist.specialist_name) ,"-")AS doctorSpecialist, patients.dial_code, opd_details.id opd_id, appointment.doctor, coalesce(patients.salutation,"") salutation, CONCAT("Dr. ",staff.name, " ", staff.surname) AS consultant, appointment_status.status appointment_status, appointment.appointment_status_id, appointment.module, appointment_status.color_code, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.id) appointment_id, patient_charges.payment_status, patient_charges.total apptFees, CASE WHEN appointment_queue.position IS NOT NULL THEN appointment_queue.position ELSE CONCAT(" ", "- ") END AS appointment_token FROM appointment LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN staff ON staff.id = appointment.doctor LEFT JOIN appointment_queue ON appointment_queue.appointment_id = appointment.id left join appointment_status on appointment_status.id = appointment.appointment_status_id LEFT JOIN visit_details ON visit_details.id = appointment.visit_details_id left join opd_details on opd_details.id = visit_details.opd_details_id left join patient_charges on patient_charges.id = visit_details.patient_charge_id LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1 `; let countQuery = `SELECT COUNT(*) as totalCount from appointment left join visit_details on visit_details.id = appointment.visit_details_id left join patient_charges on patient_charges.id = visit_details.patient_charge_id `; let date; let values = []; if (fromDate && toDate) { date = ` date(appointment.date) >= date( '` + fromDate + `' ) and date(appointment.date) <= date( '` + toDate + `' ) `; } else if (fromDate) { date = ` date(appointment.date) >= date( '` + fromDate + `' ) `; } else if (toDate) { date = ` date(appointment.date) <= date( '` + toDate + `' ) `; } else { date = ` appointment.date = DATE(NOW()) `; } let where = `WHERE ` + date; if (doctorId) { where += ` and appointment.doctor = ?`; values.push(doctorId); } if (appointStatus) { where += ` and appointment.appointment_status_id = ?`; values.push(appointStatus); } else { where += ` and (appointment.appointment_status_id <> 6 and appointment.appointment_status_id <> 4)`; } if (paymentStatus) { where += ` and patient_charges.payment_status = ?`; values.push(paymentStatus); } let order = `ORDER BY date(appointment.date) DESC, time(appointment.date) ASC limit ${limit} offset ${offset} `; let group = ` GROUP BY patients.patient_name, patients.id, appointment.date, appointment.time, patients.mobileno, patients.dial_code, appointment.doctor, staff.name, staff.surname, appointment_status.status, appointment.appointment_status_id, appointment_status.color_code, appointment.id, apptFees, opd_id, patient_charges.payment_status, appointment_queue.position `; let final = query + where + group + order; let finalCount = countQuery + where; const GetTodayAppointment = await this.dynamicConnection.query( final, values, ); const [getCount] = await this.dynamicConnection.query(finalCount, values); let output = { details: GetTodayAppointment, count: getCount.totalCount, }; return output; } catch (error) { throw new Error(error); } } async V3findAllUpcoming( fromDate: string, toDate: string, doctorId: number, appointStatus: string, hospital_id: number, paymentStatus: string, limit: number, page: number, ) { const offset = limit * (page - 1); let query = `SELECT id, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.id) appointment_id, date, time, doctor, appointment_status, appointment_status_id, module, patient_id, visit_details_id FROM appointment ORDER BY date(appointment.date) DESC, time(appointment.date) ASC `; let date; let values = []; if (fromDate && toDate) { date = ` date(appointment.date) >= date( '` + fromDate + `' ) and date(appointment.date) <= date( '` + toDate + `' ) `; } else if (fromDate) { date = ` date(appointment.date) >= date( '` + fromDate + `' ) `; } else if (toDate) { date = ` date(appointment.date) <= date( '` + toDate + `' ) `; } else { date = ` appointment.date < DATE(NOW()) `; } let where = `WHERE ` + date; if (doctorId) { where += ` and appointment.doctor = ?`; values.push(doctorId); } if (appointStatus) { where += ` and appointment.appointment_status_id = ?`; values.push(appointStatus); } else { where += ` and (appointment.appointment_status_id <> 6 and appointment.appointment_status_id <> 4)`; } // if (paymentStatus) { // where += ` and patient_charges.payment_status = ?`; // values.push(paymentStatus); // } try { const app_list = await this.dynamicConnection.query( query + where + ` LIMIT ${limit} OFFSET ${offset}`, values, ); const [count] = await this.dynamicConnection.query( `select count(*) as totalCount from appointment ` + where, values, ); if (app_list.length > 0) { const patientIds = app_list.map((app) => app.patient_id); const doctorIds = app_list.map((app) => app.doctor); const visitIds = app_list.map((app) => app.visit_details_id); const apptIds = app_list.map((app) => app.id); const statusIds = app_list.map((app) => app.appointment_status_id); const [patients, staffs, queues, statuses, opds, charges] = await Promise.all([ this.dynamicConnection.query( `SELECT id, patient_name, mobileno, dial_code, salutation FROM patients WHERE id IN (?)`, [patientIds], ), this.dynamicConnection.query( `SELECT id, name, surname, specialist FROM staff WHERE id IN (?)`, [doctorIds], ), this.dynamicConnection.query( `SELECT appointment_id, position FROM appointment_queue WHERE appointment_id IN (?)`, [apptIds], ), this.dynamicConnection.query( `SELECT id, status, color_code FROM appointment_status WHERE id IN (?)`, [statusIds], ), this.dynamicConnection.query( ` SELECT opd_details.id AS opd_id, visit_details.id AS visit_id FROM opd_details LEFT JOIN visit_details ON visit_details.opd_details_id = opd_details.id WHERE visit_details.id IN (?)`, [visitIds], ), this.dynamicConnection.query( ` SELECT payment_status, total, visit_details.id AS visit_id FROM patient_charges LEFT JOIN visit_details ON visit_details.patient_charge_id = patient_charges.id WHERE visit_details.id IN (?)`, [visitIds], ), ]); const patientMap = new Map(patients.map((p) => [p.id, p])); const staffMap = new Map(staffs.map((s) => [s.id, s])); const queueMap = new Map(); queues.forEach((q) => queueMap.set(q.appointment_id, q)); const statusMap = new Map(statuses.map((s) => [s.id, s])); const opdMap = new Map(opds.map((o) => [o.visit_id, o.opd_id])); const chargeMap = new Map(charges.map((c) => [c.visit_id, c])); await Promise.all( app_list.map(async (app) => { app.patient_details = patientMap.get(app.patient_id); const staff_details: any = staffMap.get(app.doctor); if (staff_details?.specialist) { const specIds = staff_details.specialist.filter( (id) => typeof id === 'number', ); const specNames = await Promise.all( specIds.map(async (id) => { try { const [row] = await this.dynamicConnection.query( `SELECT specialist_name FROM specialist WHERE id = ?`, [id], ); return row?.specialist_name || id; } catch (err) { return id; } }), ); staff_details.specialist = specNames; } app.staff_details = staff_details; app.appointment_token_details = queueMap.get(app.id); app.app_status = statusMap.get(Number(app.appointment_status_id)); app.opd_details = opdMap.get(app.visit_details_id); app.payment_details = chargeMap.get(app.visit_details_id); }), ); let out = { details: app_list, count: count.totalCount, }; return out; } else { return { details: [], count: 0, }; } } catch (error) { throw new Error(error); } } async V3findAllHistory( fromDate: string, toDate: string, doctorId: number, appointStatus: string, hospital_id: number, paymentStatus: string, limit: number, page: number, ) { const offset = limit * (page - 1); let query = `SELECT id, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.id) appointment_id, date, time, doctor, appointment_status, appointment_status_id, module, patient_id, visit_details_id FROM appointment ORDER BY date(appointment.date) DESC, time(appointment.date) ASC `; let date; let values = []; if (fromDate && toDate) { date = ` date(appointment.date) >= date( '` + fromDate + `' ) and date(appointment.date) <= date( '` + toDate + `' ) `; } else if (fromDate) { date = ` date(appointment.date) >= date( '` + fromDate + `' ) `; } else if (toDate) { date = ` date(appointment.date) <= date( '` + toDate + `' ) `; } else { date = ` appointment.date < DATE(NOW()) `; } let where = `WHERE ` + date; if (doctorId) { where += ` and appointment.doctor = ?`; values.push(doctorId); } if (appointStatus) { where += ` and appointment.appointment_status_id = ?`; values.push(appointStatus); } else { where += ` and (appointment.appointment_status_id <> 6 and appointment.appointment_status_id <> 4)`; } // if (paymentStatus) { // where += ` and patient_charges.payment_status = ?`; // values.push(paymentStatus); // } try { const app_list = await this.dynamicConnection.query( query + where + ` LIMIT ${limit} OFFSET ${offset}`, values, ); const [count] = await this.dynamicConnection.query( `select count(*) as totalCount from appointment ` + where, values, ); if (app_list.length > 0) { const patientIds = app_list.map((app) => app.patient_id); const doctorIds = app_list.map((app) => app.doctor); const visitIds = app_list.map((app) => app.visit_details_id); const apptIds = app_list.map((app) => app.id); const statusIds = app_list.map((app) => app.appointment_status_id); const [patients, staffs, queues, statuses, opds, charges] = await Promise.all([ this.dynamicConnection.query( `SELECT id, patient_name, mobileno, dial_code, salutation FROM patients WHERE id IN (?)`, [patientIds], ), this.dynamicConnection.query( `SELECT id, name, surname, specialist FROM staff WHERE id IN (?)`, [doctorIds], ), this.dynamicConnection.query( `SELECT appointment_id, position FROM appointment_queue WHERE appointment_id IN (?)`, [apptIds], ), this.dynamicConnection.query( `SELECT id, status, color_code FROM appointment_status WHERE id IN (?)`, [statusIds], ), this.dynamicConnection.query( ` SELECT opd_details.id AS opd_id, visit_details.id AS visit_id FROM opd_details LEFT JOIN visit_details ON visit_details.opd_details_id = opd_details.id WHERE visit_details.id IN (?)`, [visitIds], ), this.dynamicConnection.query( ` SELECT payment_status, total, visit_details.id AS visit_id FROM patient_charges LEFT JOIN visit_details ON visit_details.patient_charge_id = patient_charges.id WHERE visit_details.id IN (?)`, [visitIds], ), ]); const patientMap = new Map(patients.map((p) => [p.id, p])); const staffMap = new Map(staffs.map((s) => [s.id, s])); const queueMap = new Map(); queues.forEach((q) => queueMap.set(q.appointment_id, q)); const statusMap = new Map(statuses.map((s) => [s.id, s])); const opdMap = new Map(opds.map((o) => [o.visit_id, o.opd_id])); const chargeMap = new Map(charges.map((c) => [c.visit_id, c])); await Promise.all( app_list.map(async (app) => { app.patient_details = patientMap.get(app.patient_id); const staff_details: any = staffMap.get(app.doctor); if (staff_details?.specialist) { const specIds = staff_details.specialist.filter( (id) => typeof id === 'number', ); const specNames = await Promise.all( specIds.map(async (id) => { try { const [row] = await this.dynamicConnection.query( `SELECT specialist_name FROM specialist WHERE id = ?`, [id], ); return row?.specialist_name || id; } catch (err) { return id; } }), ); staff_details.specialist = specNames; } app.staff_details = staff_details; app.appointment_token_details = queueMap.get(app.id); app.app_status = statusMap.get(Number(app.appointment_status_id)); app.opd_details = opdMap.get(app.visit_details_id); app.payment_details = chargeMap.get(app.visit_details_id); }), ); let out = { details: app_list, count: count.totalCount, }; return out; } else { return { details: [], count: 0, }; } } catch (error) { throw new Error(error); } } async V3findAllToday( fromDate: string, toDate: string, doctorId: number, appointStatus: string, hospital_id: number, paymentStatus: string, limit: number, page: number, ) { const offset = limit * (page - 1); let query = `SELECT id, concat(CASE WHEN appointment.doctor IS NOT NULL THEN 'APPN' ELSE 'TEMP' END,appointment.id) appointment_id, date, time, doctor, appointment_status, appointment_status_id, module, patient_id, visit_details_id FROM appointment ORDER BY date(appointment.date) DESC, time(appointment.date) ASC `; let date; let values = []; if (fromDate && toDate) { date = ` date(appointment.date) >= date( '` + fromDate + `' ) and date(appointment.date) <= date( '` + toDate + `' ) `; } else if (fromDate) { date = ` date(appointment.date) >= date( '` + fromDate + `' ) `; } else if (toDate) { date = ` date(appointment.date) <= date( '` + toDate + `' ) `; } else { date = ` appointment.date < DATE(NOW()) `; } let where = `WHERE ` + date; if (doctorId) { where += ` and appointment.doctor = ?`; values.push(doctorId); } if (appointStatus) { where += ` and appointment.appointment_status_id = ?`; values.push(appointStatus); } else { where += ` and (appointment.appointment_status_id <> 6 and appointment.appointment_status_id <> 4)`; } // if (paymentStatus) { // where += ` and patient_charges.payment_status = ?`; // values.push(paymentStatus); // } try { const app_list = await this.dynamicConnection.query( query + where + ` LIMIT ${limit} OFFSET ${offset}`, values, ); const [count] = await this.dynamicConnection.query( `select count(*) as totalCount from appointment ` + where, values, ); if (app_list.length > 0) { const patientIds = app_list.map((app) => app.patient_id); const doctorIds = app_list.map((app) => app.doctor); const visitIds = app_list.map((app) => app.visit_details_id); const apptIds = app_list.map((app) => app.id); const statusIds = app_list.map((app) => app.appointment_status_id); const [patients, staffs, queues, statuses, opds, charges] = await Promise.all([ this.dynamicConnection.query( `SELECT id, patient_name, mobileno, dial_code, salutation FROM patients WHERE id IN (?)`, [patientIds], ), this.dynamicConnection.query( `SELECT id, name, surname, specialist FROM staff WHERE id IN (?)`, [doctorIds], ), this.dynamicConnection.query( `SELECT appointment_id, position FROM appointment_queue WHERE appointment_id IN (?)`, [apptIds], ), this.dynamicConnection.query( `SELECT id, status, color_code FROM appointment_status WHERE id IN (?)`, [statusIds], ), this.dynamicConnection.query( ` SELECT opd_details.id AS opd_id, visit_details.id AS visit_id FROM opd_details LEFT JOIN visit_details ON visit_details.opd_details_id = opd_details.id WHERE visit_details.id IN (?)`, [visitIds], ), this.dynamicConnection.query( ` SELECT payment_status, total, visit_details.id AS visit_id FROM patient_charges LEFT JOIN visit_details ON visit_details.patient_charge_id = patient_charges.id WHERE visit_details.id IN (?)`, [visitIds], ), ]); const patientMap = new Map(patients.map((p) => [p.id, p])); const staffMap = new Map(staffs.map((s) => [s.id, s])); const queueMap = new Map(); queues.forEach((q) => queueMap.set(q.appointment_id, q)); const statusMap = new Map(statuses.map((s) => [s.id, s])); const opdMap = new Map(opds.map((o) => [o.visit_id, o.opd_id])); const chargeMap = new Map(charges.map((c) => [c.visit_id, c])); await Promise.all( app_list.map(async (app) => { app.patient_details = patientMap.get(app.patient_id); const staff_details: any = staffMap.get(app.doctor); if (staff_details?.specialist) { const specIds = staff_details.specialist.filter( (id) => typeof id === 'number', ); const specNames = await Promise.all( specIds.map(async (id) => { try { const [row] = await this.dynamicConnection.query( `SELECT specialist_name FROM specialist WHERE id = ?`, [id], ); return row?.specialist_name || id; } catch (err) { return id; } }), ); staff_details.specialist = specNames; } app.staff_details = staff_details; app.appointment_token_details = queueMap.get(app.id); app.app_status = statusMap.get(Number(app.appointment_status_id)); app.opd_details = opdMap.get(app.visit_details_id); app.payment_details = chargeMap.get(app.visit_details_id); }), ); let out = { details: app_list, count: count.totalCount, }; return out; } else { return { details: [], count: 0, }; } } catch (error) { throw new Error(error); } } async updatePaymentDetails( appointment_id: string, Entity: AddAppointmentPayment, ) { if (Entity.payment_mode.toLocaleLowerCase() == 'cash') { const generateUpperAlphaNumId = customAlphabet( '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', 10, ); Entity.cash_transaction_id = generateUpperAlphaNumId(); } if (!Entity.amount_from_coins) { Entity.amount_from_coins = '0'; } if (!Entity.actual_amount_paid) { Entity.actual_amount_paid = '0'; } try { const [getTransactionDetails] = await this.dynamicConnection.query( ` select id, amount, temp_appt_amount from transactions where appointment_id = ?`, [appointment_id], ); if (!getTransactionDetails) { return { status_code: 400, status: 'Failed', message: 'Transaction details not found for the appointment', }; } const [getAdminTransactionDetails] = await this.connection.query( `select id,amount, temp_appt_amount from transactions where hos_transaction_id = ? and Hospital_id = ?`, [getTransactionDetails.id, Entity.Hospital_id], ); if (!getTransactionDetails && getAdminTransactionDetails) { return { status_code: 400, status: 'Failed', message: 'Transaction details not found for the appointment', }; } const updateTransaction = await this.dynamicConnection.query( `update transactions set payment_method = ?, card_division = ?, card_bank_name = ?, card_type = ?, card_transaction_id = ?, net_banking_division = ?, net_banking_transaction_id = ?, upi_id = ?, upi_transaction_id = ?, cash_transaction_id = ?, upi_bank_name = ?,actual_paid_amount = ?,wallet_paid_amount = ? where id = ?`, [ Entity.payment_method, Entity.card_division, Entity.card_bank_name, Entity.card_type, Entity.card_transaction_id, Entity.net_banking_division, Entity.net_banking_transaction_id, Entity.upi_id, Entity.upi_transaction_id, Entity.cash_transaction_id, Entity.upi_bank_name, Entity.actual_amount_paid, Entity.amount_from_coins, getTransactionDetails.id, ], ); const updateAdminTransaction = await this.connection.query( `update transactions set payment_method = ?, card_division = ?, card_bank_name = ?, card_type = ?, card_transaction_id = ?, net_banking_division = ?, net_banking_transaction_id = ?, upi_id = ?, upi_transaction_id = ?, cash_transaction_id = ?, upi_bank_name = ?,actual_paid_amount = ?,wallet_paid_amount = ? where id = ?`, [ Entity.payment_method, Entity.card_division, Entity.card_bank_name, Entity.card_type, Entity.card_transaction_id, Entity.net_banking_division, Entity.net_banking_transaction_id, Entity.upi_id, Entity.upi_transaction_id, Entity.cash_transaction_id, Entity.upi_bank_name, Entity.actual_amount_paid, Entity.amount_from_coins, getAdminTransactionDetails.id, ], ); const [adminpatchargeid] = await this.connection.query( `select id from patient_charges where transaction_id = ?`, [getAdminTransactionDetails.id], ); console.log(getTransactionDetails.id, 'getTransactionDetails.id'); const [hospatchargeid] = await this.dynamicConnection.query( `select id from patient_charges where transaction_id = ?`, [getTransactionDetails.id], ); console.log(hospatchargeid.id, 'hospatchargeid.id'); const [HossssPatientCharges] = await this.dynamicConnection.query( `select * from patient_charges where id = ?`, [hospatchargeid.id], ); const [doctor_id] = await this.dynamicConnection.query( `select doctor,patient_id from appointment where id = ?`, [appointment_id], ); const [HosChargeId] = await this.dynamicConnection.query( `select * from shift_details where staff_id = ?`, [doctor_id.doctor], ); const [getAmountDetails] = await this.dynamicConnection.query( ` select charges.standard_charge,tax_category.percentage tax_percentage, 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 = ?`, [HosChargeId.charge_id], ); const [admingetAmountDetails] = await this.connection.query( ` select id from charges where Hospital_id = ? and hospital_charges_id = ?`, [Entity.Hospital_id, HosChargeId.charge_id], ); console.log(HossssPatientCharges, 'HossssPatientCharges'); const total: number = Number(getAmountDetails.amount) + Number(HossssPatientCharges.additional_charge); console.log(getTransactionDetails, 'getTransactionDetails'); let Balance: number; console.log( getTransactionDetails.temp_appt_amount, 't', ); Balance = Number(getTransactionDetails.temp_appt_amount) + Number(getTransactionDetails.amount) - Number(total); Balance = 0; let payment_status; if ( Entity.payment_mode == 'Paylater' || Entity.payment_mode == 'Offline' || Entity.payment_mode == 'cheque' || Entity.payment_mode == 'offline' || !Entity.payment_mode ) { payment_status = HossssPatientCharges.payment_status; } else { payment_status = 'paid'; } if (Balance > 0) { const [getCoinValue] = await this.connection.query( `select coin_amount from coin_value order by created_at desc limit 1`, ); const coins = (Balance / getCoinValue.coin_amount).toFixed(2); const [getPatDetails] = await this.dynamicConnection.query( `select mobileno from patients where id = ?`, [doctor_id.patient_id], ); if (getPatDetails.mobileno.length > 10) { getPatDetails.mobileno = await getPatDetails.mobileno.slice(-10); } const [getUserId] = await this.connection.query( `select id from users where username = ? `, ['91' + getPatDetails.mobileno], ); const coins_expiry_time = new Date(); coins_expiry_time.setFullYear(coins_expiry_time.getFullYear() + 100); let moduleNumb = 'APPN' + appointment_id; // await this.connection.query( // `insert into aayush_coins (user_id,total_coins_remaining, // total_coins_issued,coins_expiry_time,module) values (?,?,?,?,?)`, // [await getUserId.id, coins, coins, coins_expiry_time, moduleNumb], // ); } // await this.connection.query( // `update patient_charges set charge_id=?,standard_charge=?, // tax=?,apply_charge=?,amount=?, total = ?, balance = ?, payment_status = ? where id = ?`, // [ // admingetAmountDetails.id, // getAmountDetails.standard_charge, // getAmountDetails.tax_percentage, // getAmountDetails.standard_charge, // getAmountDetails.amount, // total, // Balance, // payment_status, // adminpatchargeid.id, // ], // ); // await this.dynamicConnection.query( // `update patient_charges set charge_id=?,standard_charge=?, // tax=?,apply_charge=?,amount=?, total = ?, balance = ?, payment_status = ? where id = ?`, // [ // HosChargeId.charge_id, // getAmountDetails.standard_charge, // getAmountDetails.tax_percentage, // getAmountDetails.standard_charge, // getAmountDetails.amount, // total, // Balance, // payment_status, // hospatchargeid.id, // ], // ); // if ( // updateTransaction.affectedRows > 0 && // updateAdminTransaction.affectedRows > 0 // ) { return { status_code: 200, status: 'Success', message: 'Payment details updated successfully', }; // } return Promise.resolve([ { status_code: 400, status: 'Failed', message: 'Failed to update payment details', }, ]); } catch (error) { console.log(error, 'er'); return { status_code: 500, status: 'Error', message: 'API SERVICE TEMPORARILY UNAVAILABLE,CHECK BACK LATER', }; } } async specialityList() { try { const specialityList = await this.dynamicConnection.query( `select id,specialist_name from specialist where is_active = 'yes'`, ); if (specialityList.length == 0) { return { status_code: 404, status: 'Failed', message: 'No speciality found', }; } return { status_code: 200, status: 'Success', message: 'Speciality list fetched successfully', data: specialityList, }; } catch (error) { return { status_code: 500, status: 'Error', message: 'API SERVICE TEMPORARILY UNAVAILABLE,CHECK BACK LATER', }; } } async get_all_transaction_charges(appointment_id: number, hospital_id: number) { const rows: any[] = await this.dynamicConnection.query( `SELECT appointment.*, patient_charges.*, appointment.doctor AS doctor_id, transactions.amount, patient_charges.created_at as billed_date, transactions.payment_date as paid_date, transactions.payment_method, transactions.payment_mode, CONCAT( COALESCE(transactions.net_banking_transaction_id, ""), COALESCE(transactions.card_transaction_id, ""), COALESCE(transactions.upi_transaction_id, ""), COALESCE(transactions.payment_reference_number, ""), COALESCE(transactions.cash_transaction_id, "") ) AS payment_transaction_id, CONCAT("TRID", transactions.id) AS transactionID, transactions.id AS transId FROM appointment LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN opd_details ON opd_details.case_reference_id = appointment.case_reference_id LEFT JOIN appointment_status ON appointment_status.id = appointment.appointment_status_id LEFT JOIN staff ON staff.id = appointment.doctor LEFT JOIN patient_charges ON patient_charges.opd_id = opd_details.id LEFT JOIN transactions ON transactions.id = patient_charges.transaction_id WHERE appointment.id = ? ORDER BY patient_charges.id ASC `, [appointment_id] ); let totalPaid = 0; let totalDue = 0; let isConsultationCharge = true; const resultData: any[] = []; const transIdSet = new Set(); for (const res of rows) { if (res.transId) transIdSet.add(res.transId); const totalBill = Number(res.total ?? 0); const oldBalance = Math.abs(Number(res.balance ?? 0)); let paid = 0; let due = 0; switch (res.payment_status) { case 'paid': paid = totalBill; break; case 'partially_paid': paid = totalBill - oldBalance; due = oldBalance; break; case 'unpaid': due = totalBill; break; } totalPaid += paid; totalDue += due; resultData.push({ ...res, isConsultationCharge }); isConsultationCharge = false; } const transIds = Array.from(transIdSet); let plenomeMap = new Map(); if (transIds.length > 0) { const plenomeRows: any[] = await this.connection.query( `SELECT id, hos_transaction_id FROM transactions WHERE Hospital_id = ? AND hos_transaction_id IN (?) `, [hospital_id, transIds] ); plenomeMap = new Map( plenomeRows.map((x: any) => [x.hos_transaction_id, x]) ); } const finalPatientData = resultData.map((res) => { const plenome = plenomeMap.get(res.transId); return { ...res, plenome_transaction_id: plenome?.id ? `PLE${plenome.id}` : null, hos_transaction_id: plenome?.hos_transaction_id ?? null }; }); const paid_filter_data = finalPatientData.filter((res) => res.payment_status == 'paid') const unpaid_filter_data = finalPatientData.filter((res) => res.payment_status == 'partially_paid' || res.payment_status == 'unpaid') const transaction_filter_data = []; paid_filter_data.forEach(element => { const isData = transaction_filter_data.find((da) => da.hos_transaction_id === element.hos_transaction_id) if (!isData) { transaction_filter_data.push(element) } }); return { paid_data: paid_filter_data, unpaid_data: unpaid_filter_data, transaction_data: transaction_filter_data, billing_summary: { total_paid: Number(totalPaid.toFixed(2)), total_due: Number(totalDue.toFixed(2)) } }; } async deduct_discount(deductDiscountDto: DeductDiscountDto) { const [charge] = await this.dynamicConnection.query( `SELECT * FROM patient_charges WHERE id = ?`, [deductDiscountDto.patient_charges_id], ); const [appointment] = await this.dynamicConnection.query( `SELECT * FROM appointment WHERE id = ?`, [deductDiscountDto.appointment_id], ); if ( deductDiscountDto.before_discount === deductDiscountDto.after_discount ) { return { status: 'success', message: 'No discount change', }; } const baseAmount = Number(charge.apply_charge) + Number(charge.additional_charge); const taxPercent = appointment.doctor ? Number(charge.tax) : Number(charge.temp_tax); const afterDiscountPercent = Number(deductDiscountDto.after_discount); const afterDiscountAmount = (baseAmount * afterDiscountPercent) / 100; const subtotalAfter = baseAmount - afterDiscountAmount; const taxAfter = (subtotalAfter * taxPercent) / 100; const finalAfter = subtotalAfter + taxAfter; const beforeDiscountPercent = Number(deductDiscountDto.before_discount); const beforeDiscountAmount = (baseAmount * beforeDiscountPercent) / 100; const subtotalBefore = baseAmount - beforeDiscountAmount; const taxBefore = (subtotalBefore * taxPercent) / 100; const finalBefore = subtotalBefore + taxBefore; const finalDiff = finalBefore - finalAfter; const subtotalDiff = subtotalBefore - subtotalAfter; const taxDiff = taxBefore - taxAfter; const [patientDue] = await this.dynamicConnection.query( `SELECT * FROM patient_payment_summary WHERE patient_id = ?`, [appointment.patient_id], ); const updatedDueAmount = Number(patientDue?.due_amount || 0) - finalDiff; const updatedBilledDueAmount = Number(patientDue?.total_billed_amount || 0) - finalDiff; const updatedSubTotalAmount = Number(patientDue?.overall_subtotal_amount || 0) - subtotalDiff; const updatedTaxAmount = Number(patientDue?.total_tax_amount || 0) - taxDiff; await this.dynamicConnection.query( ` UPDATE patient_payment_summary SET due_amount = ?, overall_subtotal_amount = ?, total_tax_amount = ?, total_billed_amount = ? WHERE patient_id = ? `, [ updatedDueAmount, updatedSubTotalAmount, updatedTaxAmount, updatedBilledDueAmount, appointment.patient_id, ], ); const [admin_appointment] = await this.connection.query( ` SELECT id, patient_id FROM appointment WHERE hos_appointment_id = ? AND Hospital_id = ? `, [deductDiscountDto.appointment_id, deductDiscountDto.hospital_id], ); const [admin_patientDue] = await this.connection.query( `SELECT * FROM patient_payment_summary WHERE patient_id = ?`, [admin_appointment.patient_id], ); const admin_updatedDueAmount = Number(admin_patientDue?.due_amount || 0) - finalDiff; const admin_updatedBilledDueAmount = Number(admin_patientDue?.total_billed_amount || 0) - finalDiff; const admin_updatedSubTotalAmount = Number(admin_patientDue?.overall_subtotal_amount || 0) - subtotalDiff; const admin_updatedTaxAmount = Number(admin_patientDue?.total_tax_amount || 0) - taxDiff; await this.connection.query( ` UPDATE patient_payment_summary SET due_amount = ?, overall_subtotal_amount = ?, total_tax_amount = ?, total_billed_amount = ? WHERE patient_id = ? `, [ admin_updatedDueAmount, admin_updatedSubTotalAmount, admin_updatedTaxAmount, admin_updatedBilledDueAmount, admin_appointment.patient_id, ], ); const [hospitalDue] = await this.connection.query( `SELECT * FROM hospital_payment_summary WHERE hospital_id = ?`, [deductDiscountDto.hospital_id], ); const hospital_updatedDueAmount = Number(hospitalDue?.due_amount || 0) - finalDiff; const hospital_updatedBilledDueAmount = Number(hospitalDue?.total_billed_amount || 0) - finalDiff; const hospital_updatedSubTotalAmount = Number(hospitalDue?.overall_subtotal_amount || 0) - subtotalDiff; const hospital_updatedTaxAmount = Number(hospitalDue?.total_tax_amount || 0) - taxDiff; await this.connection.query( ` UPDATE hospital_payment_summary SET due_amount = ?, overall_subtotal_amount = ?, total_tax_amount = ?, total_billed_amount = ? WHERE hospital_id = ? `, [ hospital_updatedDueAmount, hospital_updatedSubTotalAmount, hospital_updatedTaxAmount, hospital_updatedBilledDueAmount, deductDiscountDto.hospital_id, ], ); const [user_patient] = await this.connection.query( `SELECT * FROM patients WHERE id = ?`, [admin_appointment.patient_id], ); const [admin_user_id] = await this.connection.query( `select id,user_id from users where username = ?`, [91 + user_patient.mobileno], ); console.log(admin_user_id, 'admin_user_id'); const [user_Due] = await this.connection.query( `SELECT * FROM user_payment_summary WHERE user_id = ?`, [admin_user_id.id], ); const user_updatedDueAmount = Number(user_Due?.due_amount || 0) - finalDiff; const user_updatedSubTotalAmount = Number(user_Due?.overall_subtotal_amount || 0) - subtotalDiff; const user_updatedTaxAmount = Number(user_Due?.total_tax_amount || 0) - taxDiff; await this.connection.query( ` UPDATE user_payment_summary SET due_amount = ?, overall_subtotal_amount = ?, total_tax_amount = ? WHERE user_id = ? `, [ user_updatedDueAmount, user_updatedSubTotalAmount, user_updatedTaxAmount, user_Due.user_id, ], ); return { status: 'success', message: 'Discount updated successfully', }; } async remove_summery_amount_from_cancel_app( appointment_id: any, hospital_id: number, ) { const charges_data = await this.dynamicConnection.query( ` SELECT patient_charges.id FROM appointment LEFT JOIN patients ON patients.id = appointment.patient_id LEFT JOIN staff ON staff.id = appointment.doctor LEFT JOIN transactions ON transactions.appointment_id = appointment.id LEFT JOIN doctor_shift ON doctor_shift.id = appointment.shift_id LEFT JOIN opd_details ON opd_details.case_reference_id = appointment.case_reference_id LEFT JOIN patient_charges ON patient_charges.opd_id = opd_details.id AND patient_charges.payment_status IN ('unpaid', 'partially_paid','paid','refunded') LEFT JOIN appointment_queue ON appointment.id = appointment_queue.appointment_id WHERE appointment.id = ? AND patient_charges.id IS NOT NULL `, [appointment_id], ); for (const res of charges_data) { await this.newBillingService.deduct_amount_from_summery( res.id, hospital_id, null, null, appointment_id, ); } return true; } }