from datetime import datetime, date from sqlalchemy.orm import joinedload from core.config import get_db from core.security.exceptions import GenericError from models import Participant, Payment, Course, Cart, Parent, Term,PaymentDetails from models.wallet_history import WalletHistory from schema.wallet import RefundResponseSchema class WalletService: def search_student(self, request): with get_db() as db: participant_list = [] participant_query = ( db.query( Participant.id.label("participant_id"), Participant.first_name, Participant.last_name, Participant.dob, Payment.id.label("payment_id"), PaymentDetails.id.label("payment_details_id"), Participant.mobile_number, Participant.email, Course.name.label("course_name"), Payment.total_paid_amount.label("paid_fee"), PaymentDetails.final_amount.label("final_amount"), Payment.is_wallet_used, Payment.transaction_batch_id, Term.name.label("course_term") ) .join(PaymentDetails, PaymentDetails.purchased_for_id == Participant.id) .join(Payment, Payment.id == PaymentDetails.payment_id) .join(Course, Course.id == PaymentDetails.course_id) .join(Term, Course.term_id == Term.id) .filter(Payment.is_payment_completed == True) ) if request.name: participant_query = participant_query.filter(Participant.first_name.icontains(request.name)) if request.mobile_number: participant_query = participant_query.filter(Participant.mobile_number == request.mobile_number) if request.email: participant_query = participant_query.filter(Participant.email == request.email) if request.age_group_id: # age_group_instance=db.query(AgeGroup).get(request.age_group_id) # print(age_group_instance.age_start) participant_query = participant_query.filter(Course.age_group_id == request.age_group_id) if request.academic_year_id: participant_query = participant_query.filter(Course.academic_year_id == request.academic_year_id) if request.course_id: participant_query = participant_query.filter(Course.id == request.course_id) if request.term_id: participant_query = participant_query.filter(Course.term_id == request.term_id) for row in participant_query.all(): is_bursary_applied = db.query(Cart).filter(Cart.transaction_batch_id == row.transaction_batch_id, Cart.is_discount_applied == True).count() participant_list.append( { "participant_id": row.participant_id, "first_name": row.first_name, "last_name": row.last_name, "full_name": f"{row.first_name} {row.last_name}", "payment_id": row.payment_id, "final_amount": row.final_amount, "total_paid_fee": row.paid_fee, "payment_details_id":row.payment_details_id, "mobile_number": row.mobile_number, "is_wallet_used": row.is_wallet_used, "course": row.course_name, "dob": row.dob, "email": row.email, "age": self.age(row.dob), "transaction_batch_id": row.transaction_batch_id, "course_term": row.course_term, "is_bursary_applied": is_bursary_applied > 0 }) return participant_list def age(self, dob): if dob: today = date.today() age = today.year - dob.year - ((today.month, today.day) < (dob.month, dob.day)) return age else: return 0 def initiate_refund(self, request): with get_db() as db: for row in request.refunds: # Payment.is_refund_completed==False payment_details_instance = db.query(PaymentDetails).filter(PaymentDetails.id == row.payment_detail_id).first() if not payment_details_instance: raise GenericError(status_code=404, exc="Payment not found or Refund already initiated") if row.refund_amount > payment_details_instance.final_amount: raise GenericError(status_code=400, exc="Refund amount cannot be greater than payment amount") payment_details_instance.refund_amount = row.refund_amount payment_details_instance.refund_status = "completed" payment_details_instance.refund_date = datetime.now() payment_details_instance.refund_created_on = datetime.now() payment_details_instance.refund_completed_on = datetime.now() payment_details_instance.refund_note = row.note payment_details_instance.is_refund_completed = True payment_details_instance.is_refund_requested = True if payment_details_instance.payment.user_type == 'parent': db.query(Parent).filter( Parent.id == payment_details_instance.payment.created_by ).update({ Parent.wallet_amount: Parent.wallet_amount + row.refund_amount }) else: db.query(Participant).filter( Participant.id == payment_details_instance.payment.created_by ).update({ Participant.wallet_amount: Participant.wallet_amount + row.refund_amount }) db.add(WalletHistory( payment_details_id=payment_details_instance.id, user_type=payment_details_instance.payment.user_type, refund_for_id=payment_details_instance.payment.created_by, amount=row.refund_amount, refund_type=row.refund_type, transaction_id=payment_details_instance.payment.transaction_batch_id, note=row.note, is_credit=False, message=f"Refund amount {row.refund_amount} credited against course :{payment_details_instance.course.name}", )) db.commit() def fetch_all_refund(self): return "use payment history api" with get_db() as db: records = db.query(Refund).where(Refund.deleted_at.is_(None)).all() return [RefundResponseSchema.model_validate(record).model_dump() for record in records] def refund_detail(self, refund_id: int): return "use payment history api" # with get_db() as db: # record = db.query(Refund).where(Refund.id == refund_id, Refund.deleted_at.is_(None)).first() # # if not record: # raise GenericError(status_code=404, exc="Refund not found") # return RefundResponseSchema.model_validate(record).model_dump() def get_wallet_history(self,request, user): with get_db() as db: data = [] # Start query query = db.query(WalletHistory) \ .join(WalletHistory.payment_details) \ .join(PaymentDetails.purchased_participant) \ .options( joinedload(WalletHistory.payment_details).joinedload(PaymentDetails.purchased_participant) ) # Apply user type filter if user['user_type'] != 'admin': query = query.filter( WalletHistory.refund_for_id == user['id'], WalletHistory.user_type == user['user_type'] ) # Apply participant name search if request.participant_name: query = query.filter( Participant.first_name.ilike(f"%{request.participant_name}%") ) if request.email: query=query.filter( Participant.email==request.email ) if request.mobile_number: query = query.filter( Participant.mobile_number == request.mobile_number ) # Now get all records records = query.all() # Preload guardian information (bulk load if needed) for row in records: if row.user_type == 'parent': guardian = db.query(Parent).get(row.refund_for_id) else: guardian = db.query(Participant).get(row.refund_for_id) data.append({ "id": row.id, "message": row.message, "amount": row.amount, "transaction_id": row.transaction_id, "refund_for": row.refund_for_id, "payment_details_id": row.payment_details_id, "created_at": row.created_at, "note": row.note, "refund_type":row.refund_type, "parent_or_participant_details": { "first_name": guardian.first_name if guardian else None, "last_name": guardian.last_name if guardian else None }, "participant_details": { "id": row.payment_details.purchased_participant.id , "first_name": row.payment_details.purchased_participant.first_name , "last_name": row.payment_details.purchased_participant.last_name, "email":row.payment_details.purchased_participant.email, "mobile_number":row.payment_details.purchased_participant.mobile_number, } }) return data