from rest_framework.views import APIView from rest_framework.response import Response from visitors_app.models import appointment,department,user,company,website_setting from visitors_app.views import date_time from django.db import connection, IntegrityError from visitors_app.context_processors import my_constants from django.core.mail import EmailMultiAlternatives from django.template.loader import render_to_string from django.utils.html import strip_tags from datetime import datetime from gate_keeper_app.visitor_reject_whatsapp_notification import visitor_reject_whatsapp_notification from gate_keeper_app.visitor_request_accepted_whatsapp_notification import visitor_accepted_whatsapp_notification from django.core import signing class visitore_listingView(APIView): def post(self, request): constants = my_constants(request) employee_id = request.data.get('employee_id', '') page = request.data.get('page', 1) type = request.data.get('type', '') search_term = request.data.get('search', '') page = int(page) database_name = constants['database_name'] if not employee_id: return Response({"status": "false", "message": "Employee ID is required."}) # Define the number of results per page results_per_page = 10 base_image_url = constants['IMAGEPATH'] placeholder_image_url = constants['DEFAULT_IMAGE'] offset = (page - 1) * results_per_page with connection.cursor() as cursor: # Construct the base query base_query = f""" SELECT {database_name}.appointment.*, {database_name}.users.first_name as visitors_first_name, {database_name}.users.email as visitors_email, {database_name}.users.mobile as visitors_mobile, {database_name}.users.image as visitors_image FROM {database_name}.appointment INNER JOIN {database_name}.users ON {database_name}.users.id = {database_name}.appointment.visitors_id WHERE {database_name}.appointment.employee_id = %s """ # Prepare the parameters for the main query params = [employee_id] # Handle search term if provided if search_term: base_query += " AND (" base_query += f"{database_name}.users.first_name LIKE %s OR " base_query += f"{database_name}.users.email LIKE %s OR " base_query += f"{database_name}.users.mobile LIKE %s OR " # Fix applied here base_query += f"{database_name}.appointment.status LIKE %s" base_query += ")" params.extend([f"%{search_term}%", f"%{search_term}%", f"%{search_term}%", f"%{search_term}%"]) # Handle different values for `type` if type == 'all': count_query = f""" SELECT COUNT(*) FROM {database_name}.appointment INNER JOIN {database_name}.users ON {database_name}.users.id = {database_name}.appointment.visitors_id WHERE {database_name}.appointment.employee_id = %s AND {database_name}.appointment.status != 'pending' """ if search_term: count_query += " AND (users.first_name LIKE %s OR users.email LIKE %s OR users.mobile LIKE %s OR appointment.status LIKE %s)" # Fix applied here query = base_query + f""" AND {database_name}.appointment.status != 'pending' ORDER BY {database_name}.appointment.id DESC LIMIT %s OFFSET %s """ params.append(results_per_page) params.append(offset) elif type == 'pending': count_query = f""" SELECT COUNT(*) FROM {database_name}.appointment INNER JOIN {database_name}.users ON {database_name}.users.id = {database_name}.appointment.visitors_id WHERE {database_name}.appointment.employee_id = %s AND {database_name}.appointment.status = 'pending' """ if search_term: count_query += " AND (users.first_name LIKE %s OR users.email LIKE %s OR users.mobile LIKE %s OR appointment.status LIKE %s)" # Fix applied here query = base_query + f""" AND {database_name}.appointment.status = 'pending' LIMIT %s OFFSET %s """ params.append(results_per_page) params.append(offset) else: return Response({"status": "false", "message": "Invalid type value. Allowed values are 'all' or 'pending'."}) try: # Execute the count query if search_term: cursor.execute(count_query, [employee_id] + [f"%{search_term}%"] * 4) else: cursor.execute(count_query, [employee_id]) total_items_result = cursor.fetchone() total_items = total_items_result[0] if total_items_result else 0 # Calculate total pages total_pages = (total_items // results_per_page) + (1 if total_items % results_per_page else 0) # Execute the main query with pagination cursor.execute(query, params) columns = [col[0] for col in cursor.description] results = [ dict(zip(columns, row)) for row in cursor.fetchall() ] for result in results: result['visitors_image'] = ( base_image_url + result['visitors_image'] if result.get('visitors_image') else placeholder_image_url ) except Exception as e: print(f"Error: {e}") return Response({"status": "false", "message": f"An error occurred: {e}"}) pagination = { 'current_page': page, 'per_page_count': results_per_page, 'total': total_items, 'last_page': total_pages, 'search_term': search_term } if results: return Response({ "status": "true", "data": results, "pagination": pagination, }) else: pagination['total'] = 0 return Response({ "status": "false", "message": "No data found.", "data": results, "pagination": pagination, }) class status_changesView(APIView): def post(self, request): constants = my_constants(request) database_name = constants['database_name'] from_email = constants['From_Email'] employee_id = request.data.get('appointment_id', '') status = request.data.get('status', '') reason = request.data.get('reason', '') date = request.data.get('date', '') time = request.data.get('time', '') # Input validation if not employee_id: return Response({"status": "false", "message": "Employee ID is required."}) if status not in ['accepted', 'rejected']: return Response({"status": "false", "message": "Invalid status provided."}) appointment_exist = appointment.objects.filter(id=employee_id).first() if not appointment_exist: return Response({"status": "false", "message": "Appointment not found."}) if status == 'rejected' and (not reason or not date or not time): return Response({"status": "false", "message": "Reason, date, and time are required for rejected status."}) try: with connection.cursor() as cursor: # Generate current timestamp for updated_at current_timestamp = datetime.now().strftime('%y-%m-%d %H:%M:%S') # Update the status in vms.appointment cursor.execute(f""" UPDATE {database_name}.appointment SET status = %s, employee_approval = %s, updated_at = %s WHERE id = %s """, [status, status, current_timestamp, employee_id]) # If status is 'rejected', insert into appointment_reject if status == 'rejected': cursor.execute(f""" INSERT INTO {database_name}.appointment_reject (appointment_id, reason, date, time, created_at, updated_at) VALUES (%s, %s, %s, %s, %s,%s) """, [employee_id, reason, date, time, current_timestamp,current_timestamp]) # Fetch appointment details for email cursor.execute(f""" SELECT {database_name}.appointment.*, visitors.first_name AS visitors_first_name, visitors.last_name AS visitors_last_name, visitors.email AS visitors_email, visitors.mobile AS visitors_mobile, visitors.image AS visitors_image, employees.first_name AS employee_first_name, employees.last_name AS last_name, employees.email AS employees_email, employees.mobile AS employees_mobile, employees.image AS employees_image FROM {database_name}.appointment INNER JOIN {database_name}.users AS visitors ON visitors.id = {database_name}.appointment.visitors_id INNER JOIN {database_name}.users AS employees ON employees.id = {database_name}.appointment.employee_id WHERE {database_name}.appointment.id = %s """, [employee_id]) database_all_data = cursor.fetchone() if not database_all_data: return Response({"status": "false", "message": "Failed to retrieve appointment details."}) columns = [col[0] for col in cursor.description] all_data = dict(zip(columns, database_all_data)) # Send email if recipient_email exists recipient_email = all_data.get('visitors_email', '') appointment_id = all_data.get('id','') visitors_first_name = all_data.get('visitors_first_name') visitors_last_name = all_data.get('visitors_last_name') employee_first_name = all_data.get('employee_first_name') employee_last_name = all_data.get('last_name') visit_schedule = f"{all_data.get('date')} {all_data.get('time')}" employee_obj = user.objects.filter(email=all_data.get('employees_email')).first() dept_obj = department.objects.filter(id=employee_obj.department_id).first() if employee_obj else None department_name = dept_obj.department_name if dept_obj else "" company_obj = company.objects.filter(id=employee_obj.company_id).first() company_name = company_obj.company_name if company_obj else "" request_id = all_data.get('request_id','') context = { 'company_name': company_name, 'visitor_name': f"{visitors_first_name} {visitors_last_name}", 'person_name': f"{employee_first_name} {employee_last_name}", 'person_departments': f"{department_name}", 'visit_schedule': visit_schedule, 'request_id':request_id, } if status == 'accepted': template_name = 'dashboard/email_templates/visitor_request_approved.html' subject = f'Visitor Request Approved – {company_name}' else: # rejected template_name = 'dashboard/email_templates/visitor_request_rejection.html' subject = f'Visitor Request Update – {company_name}' if recipient_email: message = render_to_string(template_name, context) plain_message = strip_tags(message) mail = EmailMultiAlternatives(subject, plain_message, from_email, [recipient_email]) mail.attach_alternative(message, "text/html") mail.send() if status == 'rejected': visitor_reject_whatsapp_notification( request=request, visitor_mobile=f"91{all_data.get('visitors_mobile')}", visitor_name=f"{visitors_first_name} {visitors_last_name}", employee_name=f"{employee_first_name} {employee_last_name}", company_name=company_name ) if status == 'accepted': company_website = website_setting.objects.first() visitor_accepted_whatsapp_notification( request=request, visitor_mobile=f"91{all_data.get('visitors_mobile')}", visitor_name=f"{visitors_first_name} {visitors_last_name}", employee_name=f"{employee_first_name} {employee_last_name}", company_name=company_name, qr_code=request.build_absolute_uri( f'/gate_keeper/qr_code_visitor_details/{signing.dumps(int(appointment_id))}'), company_website=company_website.company_domain ) return Response({"status": "true", "message": "Status changed successfully."}) except IntegrityError as e: return Response({"status": "false", "message": f"Database integrity error: {str(e)}"}) except Exception as e: return Response({"status": "false", "message": f"An error occurred: {str(e)}"}) class visitore_detailView(APIView): def post(self, request): constants = my_constants(request) database_name = constants['database_name'] appointment_id = request.data.get('appointment_id', '') if not appointment_id: return Response({"status": "false", "message": "Appointment ID is required."}) try: appointment_id = int(appointment_id) except ValueError: return Response({"status": "false", "message": "Appointment ID must be a number."}) base_image_url = constants['IMAGEPATH'] # Define a placeholder image URL placeholder_image_url = constants['DEFAULT_IMAGE'] appointment_exist = appointment.objects.filter(id=appointment_id) if appointment_exist: with connection.cursor() as cursor: cursor.execute(f""" SELECT {database_name}.appointment.*, visitors.first_name AS visitor_first_name, visitors.email AS visitor_email, visitors.mobile AS visitor_mobile, visitors.image as visitors_image, employees.first_name AS employee_first_name, employees.email AS employee_email, employees.mobile AS employee_mobile FROM {database_name}.appointment INNER JOIN {database_name}.users AS visitors ON visitors.id = {database_name}.appointment.visitors_id INNER JOIN {database_name}.users AS employees ON employees.id = {database_name}.appointment.employee_id WHERE {database_name}.appointment.id = %s; ; """, [appointment_id]) columns = [col[0] for col in cursor.description] results = [ dict(zip(columns, row)) for row in cursor.fetchall() ] for result in results: if 'visitors_image' in result and result['visitors_image']: # If the image URL is not empty or invalid, prepend the base URL result['visitors_image'] = base_image_url + result['visitors_image'] else: # Use the placeholder image URL if no image is provided result['visitors_image'] = placeholder_image_url with connection.cursor() as cursor: cursor.execute(f""" SELECT {database_name}.appointment.*, appointmentes.reason AS appointment_reason, appointmentes.date AS appointment_date, appointmentes.time AS appointment_time, appointmentes.created_at AS appointment_created_at, appointmentes.updated_at AS appointment_updated_at FROM {database_name}.appointment INNER JOIN {database_name}.appointment_reject AS appointmentes ON appointment_id = {database_name}.appointment.id WHERE {database_name}.appointment.id = %s; ; """, [appointment_id]) columns = [col[0] for col in cursor.description] appointment_reason = [ dict(zip(columns, row)) for row in cursor.fetchall() ] return Response({ "status": "true", "data": results, "appointment_reason":appointment_reason }) else: return Response({"status": "false", "message": "Appointment not found."})