from rest_framework.views import APIView from rest_framework.response import Response from django.contrib.auth.hashers import check_password,make_password from visitors_app.models import user,roles,countries,states,cities,location,areas,appointment from visitors_app.context_processors import my_constants import os from django.conf import settings from django.core.files.storage import default_storage from visitors_app.views import date_time from django.db import connection from django.core.mail import send_mail from django.db import connection, IntegrityError, DatabaseError from visitors_app.context_processors import my_constants import random import string from django.core.mail import send_mail, EmailMultiAlternatives from django.template.loader import render_to_string from django.utils.html import strip_tags import datetime # class dashboard(APIView): # def post(self, request): # constants = my_constants(request) # database_name = constants['database_name'] # employee_id = request.data.get('employee_id', '') # year = datetime.datetime.now().strftime("%Y")[:2] # today_date = date_time().split(' ')[0] # total_visitors_date = f'{year}{today_date}' # sql_query_check_in_count = """ # SELECT COUNT(*) AS total_count # FROM {database_name}.appointment # WHERE date = %s AND check_in_time IS NOT NULL AND check_in_time != '' AND check_out_time = ''; # """.format(database_name=f'{database_name}') # sql_query_check_out_count = """ # SELECT COUNT(*) AS total_count # FROM {database_name}.appointment # WHERE date = %s AND check_out_time IS NOT NULL AND check_out_time != ''; # """.format(database_name=f'{database_name}') # sql_query_total_visitors = """ # SELECT COUNT(*) AS total_count # FROM {database_name}.appointment # WHERE date = %s; # """.format(database_name=f'{database_name}') # sql_query_pending = """ # SELECT COUNT(*) AS total_count # FROM {database_name}.appointment # WHERE date = %s AND check_in_time = '' AND check_out_time = ''; # """.format(database_name=f'{database_name}') # with connection.cursor() as cursor: # cursor.execute(sql_query_check_in_count, [total_visitors_date]) # check_in_count = cursor.fetchone()[0] # cursor.execute(sql_query_check_out_count, [total_visitors_date]) # check_out_count = cursor.fetchone()[0] # cursor.execute(sql_query_total_visitors, [total_visitors_date]) # total_visitors_count = cursor.fetchone()[0] # cursor.execute(sql_query_pending, [total_visitors_date]) # pending_count = cursor.fetchone()[0] # # Return JSON response # return Response({ # 'pending_count':pending_count, # 'check_in_count': check_in_count, # 'check_out_count': check_out_count, # 'total_visitors_count': total_visitors_count # }) class dashboard(APIView): def post(self, request): constants = my_constants(request) database_name = constants['database_name'] employee_id = request.data.get('employee_id', '') year = datetime.datetime.now().strftime("%Y")[:2] today_date = date_time().split(' ')[0] total_visitors_date = f'{year}{today_date}' sql_query_check_in_count = f""" SELECT COUNT(*) AS total_count FROM {database_name}.appointment INNER JOIN {database_name}.users AS visitors ON visitors.id = appointment.visitors_id INNER JOIN {database_name}.users AS employees ON employees.id = appointment.employee_id INNER JOIN {database_name}.users AS created_by ON created_by.id = appointment.created_by WHERE date = %s AND check_in_time IS NOT NULL AND check_in_time != '' AND check_out_time = '' AND employee_id = %s; """ sql_query_check_out_count = f""" SELECT COUNT(*) AS total_count FROM {database_name}.appointment INNER JOIN {database_name}.users AS visitors ON visitors.id = appointment.visitors_id INNER JOIN {database_name}.users AS employees ON employees.id = appointment.employee_id INNER JOIN {database_name}.users AS created_by ON created_by.id = appointment.created_by WHERE date = %s AND check_out_time IS NOT NULL AND check_out_time != '' AND employee_id = %s; """ sql_query_total_visitors = f""" SELECT COUNT(*) AS total_count FROM {database_name}.appointment INNER JOIN {database_name}.users AS visitors ON visitors.id = appointment.visitors_id INNER JOIN {database_name}.users AS employees ON employees.id = appointment.employee_id INNER JOIN {database_name}.users AS created_by ON created_by.id = appointment.created_by WHERE date = %s AND employee_id = %s; """ # sql_query_pending = f""" # SELECT COUNT(*) FROM {database_name}.appointment # WHERE date = %s AND check_in_time = '' AND check_out_time = ''; # """ sql_query_pending = f""" SELECT COUNT(*) AS total_count FROM {database_name}.appointment INNER JOIN {database_name}.users AS visitors ON visitors.id = appointment.visitors_id INNER JOIN {database_name}.users AS employees ON employees.id = appointment.employee_id INNER JOIN {database_name}.users AS created_by ON created_by.id = appointment.created_by WHERE date = %s AND check_in_time = '' AND check_out_time = '' AND employee_id = %s; """ with connection.cursor() as cursor: cursor.execute(sql_query_check_in_count, [total_visitors_date,employee_id]) check_in_data = cursor.fetchone() check_in_count = check_in_data[0] if check_in_data else 0 cursor.execute(sql_query_check_out_count, [total_visitors_date,employee_id]) check_out_data = cursor.fetchone() check_out_count = check_out_data[0] if check_out_data else 0 cursor.execute(sql_query_total_visitors, [total_visitors_date,employee_id]) total_visitors_data = cursor.fetchone() total_visitors_count = total_visitors_data[0] if total_visitors_data else 0 cursor.execute(sql_query_pending, [total_visitors_date,employee_id]) pending_data = cursor.fetchone() pending_count = pending_data[0] if pending_data else 0 base_url = request.build_absolute_uri('/')[:-1] context = [ {"name": "Today Appointment", "number": total_visitors_count,"image":f"{base_url}/images/calendardash.png"}, {"name": "Today Pending", "number": pending_count,"image":f"{base_url}/images/pendingdash.png","title": "pending"}, {"name": "Total Check In", "number": check_in_count,"image":f"{base_url}/images/signindash.png","title": "check in"}, {"name": "Total Check Out", "number": check_out_count,"image":f"{base_url}/images/signoutdash.png","title": "check out"}, ] return Response({ "status": "true", "data": context }) # class dashboard_pending(APIView): # def post(self, request): # constants = my_constants(request) # database_name = constants['database_name'] # year = datetime.datetime.now().strftime("%Y")[:2] # today_date = date_time().split(' ')[0] # # total_visitors_date = f'{year}{today_date}' # total_visitors_date = '2024-09-30' # employee_id = request.data.get('employee_id', '') # total = request.data.get('total_request', '') # page = request.data.get('page_no', 1) # page_size = 15 # offset = (int(page) - 1) * page_size # # Base SQL query # base_query = f""" # SELECT # appointment.*, # visitors.first_name AS visitors_name, # visitors.last_name AS visitors_last_name, # visitors.uni_id AS visitors_uni_id, # visitors.image AS visitors_image, # visitors.email AS visitors_email, # visitors.mobile AS visitors_mobile, # employees.first_name AS employee_name, # employees.last_name AS employee_last_name, # employees.uni_id AS employee_uni_id, # appointment.check_in_time AS start_time, # appointment.check_out_time AS stop_time, # created_by.first_name AS created_by_first_name, # created_by.last_name AS created_by_last_name # FROM # {database_name}.appointment # INNER JOIN # {database_name}.users AS visitors ON visitors.id = appointment.visitors_id # INNER JOIN # {database_name}.users AS employees ON employees.id = appointment.employee_id # INNER JOIN # {database_name}.users AS created_by ON created_by.id = appointment.created_by # WHERE # date = %s AND employee_id = %s # """ # if total == 'pending': # condition = "AND check_in_time = '' AND check_out_time = ''" # elif total == 'check in': # condition = "AND check_out_time IS NOT NULL AND check_out_time != '' AND check_out_time = ''" # elif total == 'check out': # condition = "AND check_in_time IS NOT NULL AND check_in_time != '' AND check_out_time = ''" # else: # return Response({"status": "false", "message": "Invalid request value"}) # # Add condition, ordering, limit, and offset # sql_query = f"{base_query} {condition} ORDER BY visitors.id DESC LIMIT %s OFFSET %s;" # with connection.cursor() as cursor: # cursor.execute(sql_query, [total_visitors_date, employee_id, page_size, offset]) # database_all_data = cursor.fetchall() # columns = [col[0] for col in cursor.description] # result_list = [dict(zip(columns, row)) for row in database_all_data] # return Response({"status": "true", "data": result_list}) # class dashboard_pending(APIView): # def post(self, request): # constants = my_constants(request) # database_name = constants['database_name'] # year = datetime.datetime.now().strftime("%Y")[:2] # today_date = date_time().split(' ')[0] # total_visitors_date = f'{year}{today_date}' # employee_id = request.data.get('employee_id', '') # total = request.data.get('total_request', '') # page = request.data.get('page_no', '') # if employee_id == '' and total == '': # return Response({ # "status": "false", # "message": "All fields are required", # }) # page_size = 15 # offset = (int(page) - 1) * page_size # # Base SQL query for fetching records # base_query = f""" # SELECT # appointment.*, # visitors.first_name AS visitors_name, # visitors.last_name AS visitors_last_name, # visitors.uni_id AS visitors_uni_id, # visitors.image AS visitors_image, # visitors.email AS visitors_email, # visitors.mobile AS visitors_mobile, # employees.first_name AS employee_name, # employees.last_name AS employee_last_name, # employees.uni_id AS employee_uni_id, # appointment.check_in_time AS start_time, # appointment.check_out_time AS stop_time, # created_by.first_name AS created_by_first_name, # created_by.last_name AS created_by_last_name # FROM # {database_name}.appointment # INNER JOIN # {database_name}.users AS visitors ON visitors.id = appointment.visitors_id # INNER JOIN # {database_name}.users AS employees ON employees.id = appointment.employee_id # INNER JOIN # {database_name}.users AS created_by ON created_by.id = appointment.created_by # WHERE # date = %s AND employee_id = %s # """ # if total == 'pending': # condition = "AND check_in_time = '' AND check_out_time = ''" # elif total == 'check in': # condition = "AND check_in_time IS NOT NULL AND check_in_time != '' AND check_out_time = ''" # elif total == 'check out': # condition = "AND check_in_time IS NOT NULL AND check_in_time != '' AND check_out_time IS NOT NULL" # else: # return Response({"status": "false", "message": "Invalid request value"}) # # Complete SQL query with condition, ordering, limit, and offset # sql_query = f"{base_query} {condition} ORDER BY visitors.id DESC LIMIT %s OFFSET %s;" # # Query to get the total number of records (without LIMIT and OFFSET) # count_query = f"SELECT COUNT(*) FROM {database_name}.appointment WHERE date = %s AND employee_id = %s {condition};" # with connection.cursor() as cursor: # # Execute count query to get the total record count # cursor.execute(count_query, [total_visitors_date, employee_id]) # total_records = cursor.fetchone()[0] # # Calculate total number of pages # total_pages = (total_records + page_size - 1) // page_size # # Check if the requested page is valid # if int(page) > total_pages: # return Response({ # "status": "false", # "message": "No more records available", # "data": [], # "pagination": { # "current_page": page, # "page_size": page_size, # "total_records": total_records, # "total_pages": total_pages # } # }) # # Execute the main query with pagination # cursor.execute(sql_query, [total_visitors_date, employee_id, page_size, offset]) # database_all_data = cursor.fetchall() # columns = [col[0] for col in cursor.description] # result_list = [dict(zip(columns, row)) for row in database_all_data] # return Response({ # "status": "true", # "data": result_list, # "pagination": { # "current_page": page, # "page_size": page_size, # "total_records": total_records, # "total_pages": total_pages, # } # }) class dashboard_pending(APIView): def post(self, request): constants = my_constants(request) database_name = constants['database_name'] base_image_url = constants['IMAGEPATH'] placeholder_image_url = constants['DEFAULT_IMAGE'] year = datetime.datetime.now().strftime("%Y")[:2] today_date = date_time().split(' ')[0] total_visitors_date = f'{year}{today_date}' employee_id = request.data.get('employee_id', '') total = request.data.get('total_request', '') page = request.data.get('page_no', '') if employee_id == '' and total == '': return Response({ "status": "false", "message": "All fields are required", }) page_size = 15 offset = (int(page) - 1) * page_size # Base SQL query for fetching records base_query = f""" SELECT appointment.*, visitors.first_name AS visitors_first_name, visitors.last_name AS visitors_last_name, visitors.uni_id AS visitors_uni_id, visitors.image AS visitors_image, visitors.email AS visitors_email, visitors.mobile AS visitors_mobile, employees.first_name AS employee_name, employees.last_name AS employee_last_name, employees.uni_id AS employee_uni_id, appointment.check_in_time AS start_time, appointment.check_out_time AS stop_time, created_by.first_name AS created_by_first_name, created_by.last_name AS created_by_last_name FROM {database_name}.appointment INNER JOIN {database_name}.users AS visitors ON visitors.id = appointment.visitors_id INNER JOIN {database_name}.users AS employees ON employees.id = appointment.employee_id INNER JOIN {database_name}.users AS created_by ON created_by.id = appointment.created_by WHERE date = %s AND employee_id = %s """ if total == 'pending': condition = "AND check_in_time = '' AND check_out_time = ''" elif total == 'check in': condition = "AND check_in_time IS NOT NULL AND check_in_time != '' AND check_out_time = ''" elif total == 'check out': condition = "AND check_in_time IS NOT NULL AND check_in_time != '' AND check_out_time IS NOT NULL" else: return Response({"status": "false", "message": "Invalid request value"}) # Complete SQL query with condition, ordering, limit, and offset sql_query = f"{base_query} {condition} ORDER BY visitors.id DESC LIMIT %s OFFSET %s;" # Query to get the total number of records (without LIMIT and OFFSET) count_query = f"SELECT COUNT(*) FROM {database_name}.appointment WHERE date = %s AND employee_id = %s {condition};" with connection.cursor() as cursor: # Execute count query to get the total record count cursor.execute(count_query, [total_visitors_date, employee_id]) total_records = cursor.fetchone()[0] # Calculate total number of pages total_pages = (total_records + page_size - 1) // page_size # Check if the requested page is valid if int(page) > total_pages: return Response({ "status": "false", "message": "No more records available", "data": [], "pagination": { "current_page": page, "page_size": page_size, "total_records": total_records, "total_pages": total_pages } }) # Execute the main query with pagination cursor.execute(sql_query, [total_visitors_date, employee_id, page_size, offset]) database_all_data = cursor.fetchall() # columns = [col[0] for col in cursor.description] # result_list = [dict(zip(columns, row)) for row in database_all_data] columns = [col[0] for col in cursor.description] all_visitor_data = [] # Append base_image_url to the image field for row in database_all_data: visitor_data = dict(zip(columns, row)) # Update image URL if image is present, otherwise use placeholder if visitor_data.get('visitors_image'): visitor_data['visitors_image'] = f"{base_image_url}{visitor_data['visitors_image']}" else: visitor_data['visitors_image'] = placeholder_image_url all_visitor_data.append(visitor_data) return Response({ "status": "true", "data": all_visitor_data, "pagination": { "current_page": page, "page_size": page_size, "total_records": total_records, "total_pages": total_pages, } })