from django.shortcuts import render, redirect from django.contrib import messages from visitors_app.models import location, company, department from visitors_app.context_processors import my_constants from django.shortcuts import get_object_or_404 from django.http import JsonResponse from visitors_app.views import date_time from django.db import connection from django.views.decorators.csrf import csrf_exempt from django.db import transaction import csv import io import base64 from openpyxl import Workbook from openpyxl.styles import PatternFill, Font from visitors_app.check_subscription import check_subscription @check_subscription def admin_department_add(request): username = request.session.get('admin') if not username: return redirect('admin') locationes = location.objects.filter(status=1) companyes = company.objects.filter(status=1) if request.method == 'POST': department_name = request.POST['department_name'] department_code = request.POST['department_code'] company_id = request.POST['company_id'] location_id = request.POST['location_id'] department_color_code = request.POST['department_color_code'] created_at = date_time() departmente = department(department_name=department_name, department_code=department_code,department_color_code=department_color_code, company_id=company_id, location_id=location_id, created_at=created_at) departmente.save() messages.success(request, "Department successfully created.", extra_tags="success") return redirect('admin_department') return render(request, 'dashboard/admin_dashboard/admin_department/admin_department_add.html', {'locationes': locationes, 'companyes': companyes,'function_name': 'Admin','username':username}) @check_subscription def admin_department(request): username = request.session.get('admin') if not username: return redirect('admin') return render(request, 'dashboard/admin_dashboard/admin_department/admin_department.html',{'function_name': 'Admin','username':username}) @check_subscription def admin_department_ajax_page_ajax(request): constants = my_constants(request) username = request.session.get('admin') if not username: return redirect('admin') database_name = constants['database_name'] start = request.POST.get('start', 0) length = request.POST.get('length', 10) search_value = request.POST.get('search[value]', '') search = '' if search_value != '': # search = f'WHERE visitors.first_name LIKE "%{search_value}%" or employees.first_name LIKE "%{search_value}%" or appointment.id LIKE "%{search_value}%" or appointment.date LIKE "%{search_value}%" or appointment.time LIKE "%{search_value}%" or appointment.purpose LIKE "%{search_value}%"' search = f'AND ( department_name LIKE "%{search_value}%" OR department_code LIKE "%{search_value}%" OR location_name LIKE "%{search_value}%" OR company_name LIKE "%{search_value}%")' # First SQL query to get the count of appointments sql_query = f""" SELECT COUNT(*) AS department_count FROM {database_name}.department INNER JOIN {database_name}.location ON {database_name}.department.location_id = {database_name}.location.id INNER JOIN {database_name}.company ON {database_name}.department.company_id = {database_name}.company.id where 1=1 {search}; """ with connection.cursor() as cursor: cursor.execute(sql_query) database_all_data = cursor.fetchall() # Check if database_all_data is not empty before accessing its elements if database_all_data: recordsTotal = database_all_data[0][0] else: recordsTotal = 0 # Second SQL query to get detailed appointment data sql_query = f""" select {database_name}.department.*,{database_name}.location.location_name,{database_name}.company.company_name from {database_name}.department inner join {database_name}.location on {database_name}.department.location_id = {database_name}.location.id inner join {database_name}.company on {database_name}.department.company_id = {database_name}.company.id where 1=1 {search} ORDER BY department.id DESC LIMIT {length} OFFSET {start}; """ # Execute the second SQL query with connection.cursor() as cursor: cursor.execute(sql_query) database_all_data = cursor.fetchall() columns = [col[0] for col in cursor.description] all_coupon_dtl = [dict(zip(columns, row)) for row in database_all_data] return JsonResponse({"recordsTotal": recordsTotal, "recordsFiltered": recordsTotal, 'data': all_coupon_dtl}) @check_subscription def admin_department_delete(request,id): username = request.session.get('admin') if not username : return redirect('admin') all_department = get_object_or_404(department,id=id) all_department.delete() data = { 'status': 1, 'message': f'Department Deleted Successfully.', } messages.success(request, f"successfully Department Deleted!", extra_tags="success") return JsonResponse(data) @check_subscription def admin_department_edit(request, id): constants = my_constants(request) username = request.session.get('admin') if not username: return redirect('admin') user_data = constants.get('admin_data', {}) user_id = user_data.get('id') all_department= get_object_or_404(department, id=id) all_location = location.objects.filter(status=1) all_company= company.objects.filter(status=1) # state = states.objects.filter(id=all_location.state_id).first() # citie = cities.objects.filter(id=all_location.city_id).first() if request.method == "POST": all_department.department_name = request.POST['department_name'] all_department.department_code = request.POST['department_code'] all_department.department_color_code = request.POST['department_color_code'] all_department.company_id = request.POST['company_id'] all_department.location_id = request.POST['location_id'] all_department.updated_at = date_time() all_department.save() messages.success(request, "Department successfully updated.", extra_tags="success") return redirect('admin_department') return render(request, 'dashboard/admin_dashboard/admin_department/all_department_edit.html', { 'all_department': all_department, 'locationes': all_location, 'companyes':all_company, 'function_name': 'Admin', 'username': username }) @csrf_exempt def admin_department_csv_upload(request): constants = my_constants(request) username = request.session.get('admin') if not username: return redirect('admin') user_data = constants.get('admin_data', {}) user_id = user_data.get('id') if not user_id: return JsonResponse({'status': 'error', 'message': 'Invalid user data'}) if request.method == 'POST': failed_rows = [] success_count = 0 try: if 'csv_file' not in request.FILES: return JsonResponse({'status': 'error', 'message': 'No CSV file uploaded'}) csv_file = request.FILES['csv_file'] if not csv_file.name.endswith('.csv'): return JsonResponse({'status': 'error', 'message': 'File is not a CSV'}) if csv_file.size > 5 * 1024 * 1024: return JsonResponse({'status': 'error', 'message': 'File size exceeds 5MB limit'}) decoded_file = csv_file.read().decode('utf-8').splitlines() reader = csv.DictReader(decoded_file) fieldnames = reader.fieldnames + ['Error'] if reader.fieldnames else [] required_columns = {'Department_Name', 'Department_Code', 'Company_Name', 'Location_Name'} if not required_columns.issubset(reader.fieldnames or []): missing = required_columns - set(reader.fieldnames or []) return JsonResponse({'status': 'error', 'message': f'Missing columns: {", ".join(missing)}'}) with transaction.atomic(): for row_num, row in enumerate(reader, start=2): try: row = {k: v.strip() for k, v in row.items()} department_name = row['Department_Name'] department_code = row['Department_Code'] company_name = row['Company_Name'] location_name = row['Location_Name'] company_obj = company.objects.filter(company_name__iexact=company_name).first() location_obj = location.objects.filter(location_name__iexact=location_name).first() if not company_obj or not location_obj: missing = [] if not company_obj: missing.append('Invalid Company') if not location_obj: missing.append('Invalid Location') row['Error'] = ', '.join(missing) failed_rows.append(row) continue # Check for duplicate department code or name errors = [] if department.objects.filter(department_code=department_code).exists(): errors.append('Duplicate Department Code') if department.objects.filter(department_name__iexact=department_name).exists(): errors.append('Duplicate Department Name') if errors: row['Error'] = ', '.join(errors) failed_rows.append(row) continue department.objects.create( department_name=department_name, department_code=department_code, company_id=company_obj.id, location_id=location_obj.id, created_at=date_time() ) success_count += 1 except Exception as e: row['Error'] = f"Unexpected error: {str(e)}" failed_rows.append(row) continue if failed_rows: wb = Workbook() ws = wb.active ws.title = "Failed Uploads" ws.append(fieldnames) error_col_index = fieldnames.index('Error') + 1 ws.cell(row=1, column=error_col_index).fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid") ws.cell(row=1, column=error_col_index).font = Font(color="FFFFFF") for row_data in failed_rows: row_values = [row_data.get(field, '') for field in fieldnames] ws.append(row_values) cell = ws.cell(row=ws.max_row, column=error_col_index) cell.fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid") cell.font = Font(color="FFFFFF") output = io.BytesIO() wb.save(output) output.seek(0) encoded_excel = base64.b64encode(output.getvalue()).decode('utf-8') return JsonResponse({ 'status': 'success', 'message': f'{success_count} departments added successfully', 'count': success_count, 'failed_rows_excel': encoded_excel, 'failed_rows_filename': 'failed_departments.xlsx' }) return JsonResponse({ 'status': 'success', 'message': f'{success_count} departments added successfully', 'count': success_count }) except Exception as e: return JsonResponse({'status': 'error', 'message': f'Processing error: {str(e)}'}) return JsonResponse({'status': 'error', 'message': 'Invalid request method'})