from django.core.management.base import BaseCommand from django.db import connection from datetime import datetime, date, timedelta from stamps.models import Stamp from worktimeservice.models import WorkBalance class Command(BaseCommand): help = 'Debug why monthly_balance endpoint shows 00:00 even after recalculation' def add_arguments(self, parser): parser.add_argument('--user-id', type=str, required=True, help='User ID to debug') parser.add_argument('--date', type=str, required=True, help='Date to check (YYYY-MM-DD)') parser.add_argument('--start-date', type=str, help='Start date for range (YYYY-MM-DD)') parser.add_argument('--end-date', type=str, help='End date for range (YYYY-MM-DD)') def handle(self, *args, **options): user_id = options['user_id'] check_date_str = options['date'] try: check_date = datetime.strptime(check_date_str, '%Y-%m-%d').date() except ValueError: self.stdout.write(self.style.ERROR(f"Invalid date format: {check_date_str}. Use YYYY-MM-DD")) return self.stdout.write("=" * 80) self.stdout.write("DEBUGGING MONTHLY_BALANCE ENDPOINT") self.stdout.write("=" * 80) self.stdout.write(f"User ID: {user_id}") self.stdout.write(f"Date: {check_date}") self.stdout.write("") # 1. Check if WorkBalance exists in database self.stdout.write("1. CHECKING WORKBALANCE IN DATABASE") self.stdout.write("-" * 80) work_balance = WorkBalance.objects.filter( user_id=str(user_id), date=check_date ).first() if work_balance: self.stdout.write(self.style.SUCCESS("✓ WorkBalance record found")) self.stdout.write(f" - ID: {work_balance.id}") self.stdout.write(f" - Date: {work_balance.date}") self.stdout.write(f" - User ID: {work_balance.user_id}") self.stdout.write(f" - regular_work_seconds: {work_balance.regular_work_seconds} ({work_balance.regular_work_seconds / 3600:.2f} hours)") self.stdout.write(f" - total_work_seconds: {work_balance.total_work_seconds} ({work_balance.total_work_seconds / 3600:.2f} hours)") self.stdout.write(f" - daily_break_seconds: {work_balance.daily_break_seconds} ({work_balance.daily_break_seconds / 3600:.2f} hours)") self.stdout.write(f" - net_work_seconds: {work_balance.net_work_seconds} ({work_balance.net_work_seconds / 3600:.2f} hours)") self.stdout.write(f" - overtime_seconds: {work_balance.overtime_seconds} ({work_balance.overtime_seconds / 3600:.2f} hours)") self.stdout.write(f" - flex_seconds: {work_balance.flex_seconds} ({work_balance.flex_seconds / 3600:.2f} hours)") self.stdout.write(f" - created_at: {work_balance.created_at}") self.stdout.write(f" - updated_at: {work_balance.updated_at}") else: self.stdout.write(self.style.ERROR("✗ No WorkBalance record found")) self.stdout.write("") # 2. Check raw SQL query self.stdout.write("2. CHECKING RAW SQL QUERY") self.stdout.write("-" * 80) with connection.cursor() as cursor: cursor.execute( "SELECT * FROM workbalances WHERE user_id = %s AND date = %s", [str(user_id), check_date] ) row = cursor.fetchone() if row: self.stdout.write(self.style.SUCCESS("✓ Record found via raw SQL")) # Get column names columns = [col[0] for col in cursor.description] for i, col in enumerate(columns): if i < len(row): self.stdout.write(f" - {col}: {row[i]}") else: self.stdout.write(self.style.ERROR("✗ No record found via raw SQL")) self.stdout.write("") # 3. Check if query with str() conversion works self.stdout.write("3. CHECKING QUERY WITH STR() CONVERSION (as in endpoint)") self.stdout.write("-" * 80) work_balance_str = WorkBalance.objects.filter( user_id=str(user_id), date=check_date ).first() if work_balance_str: self.stdout.write(self.style.SUCCESS("✓ Query with str(user_id) works")) self.stdout.write(f" - Found: {work_balance_str.id}") else: self.stdout.write(self.style.ERROR("✗ Query with str(user_id) returns None")) # Try without str() work_balance_no_str = WorkBalance.objects.filter( user_id=user_id, date=check_date ).first() if work_balance_no_str: self.stdout.write(self.style.WARNING("⚠ But query without str() works!")) self.stdout.write(f" - Found: {work_balance_no_str.id}") self.stdout.write(f" - user_id type in DB: {type(work_balance_no_str.user_id)}") self.stdout.write(f" - user_id value in DB: '{work_balance_no_str.user_id}'") self.stdout.write(f" - user_id type in query: {type(user_id)}") self.stdout.write(f" - user_id value in query: '{user_id}'") self.stdout.write("") # 4. Check date format matching self.stdout.write("4. CHECKING DATE FORMAT MATCHING") self.stdout.write("-" * 80) all_balances = WorkBalance.objects.filter(user_id=str(user_id)).order_by('date')[:10] if all_balances.exists(): self.stdout.write("Sample WorkBalance dates for this user:") for wb in all_balances: date_match = "✓" if wb.date == check_date else "✗" self.stdout.write(f" {date_match} {wb.date} (type: {type(wb.date)}) vs {check_date} (type: {type(check_date)})") else: self.stdout.write(self.style.WARNING("No WorkBalance records found for this user")) self.stdout.write("") # 5. Simulate the endpoint query logic self.stdout.write("5. SIMULATING ENDPOINT QUERY LOGIC") self.stdout.write("-" * 80) # Get start and end dates if options.get('start_date') and options.get('end_date'): start_date_obj = datetime.strptime(options['start_date'], '%Y-%m-%d').date() end_date_obj = datetime.strptime(options['end_date'], '%Y-%m-%d').date() else: # Default to checking the single date start_date_obj = check_date end_date_obj = check_date # Simulate the endpoint's work_balances queryset work_balances = WorkBalance.objects.filter( user_id=str(user_id), date__range=[start_date_obj, end_date_obj] ).order_by('date') self.stdout.write(f"Queryset filter: user_id=str('{user_id}'), date__range=[{start_date_obj}, {end_date_obj}]") self.stdout.write(f"Queryset count: {work_balances.count()}") if work_balances.exists(): self.stdout.write("Records in queryset:") for wb in work_balances: self.stdout.write(f" - {wb.date}: total_work_seconds={wb.total_work_seconds}") # Simulate the endpoint's date loop current_date = start_date_obj while current_date <= end_date_obj: # Simulate: work_balance = next((wb for wb in work_balances if wb.date == current_date), None) work_balance_old = next((wb for wb in work_balances if wb.date == current_date), None) # Simulate: work_balance = WorkBalance.objects.filter(user_id=str(user_id), date=current_date).first() work_balance_new = WorkBalance.objects.filter( user_id=str(user_id), date=current_date ).first() if current_date == check_date: self.stdout.write(f"\nFor date {current_date}:") if work_balance_old: self.stdout.write(self.style.SUCCESS(f" ✓ Old method (next()): Found - total_work_seconds={work_balance_old.total_work_seconds}")) else: self.stdout.write(self.style.ERROR(f" ✗ Old method (next()): Not found")) if work_balance_new: self.stdout.write(self.style.SUCCESS(f" ✓ New method (direct query): Found - total_work_seconds={work_balance_new.total_work_seconds}")) else: self.stdout.write(self.style.ERROR(f" ✗ New method (direct query): Not found")) current_date = (datetime.combine(current_date, datetime.min.time()) + timedelta(days=1)).date() self.stdout.write("") # 6. Check for stamps on this date self.stdout.write("6. CHECKING STAMPS FOR THIS DATE") self.stdout.write("-" * 80) stamps = Stamp.objects.filter(user_id=user_id, date=check_date).order_by('time') self.stdout.write(f"Stamps found: {stamps.count()}") for stamp in stamps: self.stdout.write(f" - {stamp.stamp_function} at {stamp.time}") self.stdout.write("") # 7. Check database connection and transaction state self.stdout.write("7. CHECKING DATABASE STATE") self.stdout.write("-" * 80) self.stdout.write(f"Database: {connection.settings_dict['NAME']}") self.stdout.write(f"In transaction: {connection.in_atomic_block}") self.stdout.write("") # 8. Summary self.stdout.write("=" * 80) self.stdout.write("SUMMARY") self.stdout.write("=" * 80) if work_balance: if work_balance.total_work_seconds > 0: self.stdout.write(self.style.SUCCESS("✓ WorkBalance exists with non-zero values")) self.stdout.write(self.style.WARNING("⚠ But endpoint shows 00:00 - possible causes:")) self.stdout.write(" 1. Query cache issue") self.stdout.write(" 2. Date format mismatch") self.stdout.write(" 3. User ID type mismatch (str vs non-str)") self.stdout.write(" 4. Transaction not committed") self.stdout.write(" 5. Different database connection") else: self.stdout.write(self.style.WARNING("⚠ WorkBalance exists but values are 0")) self.stdout.write(" This suggests the calculation didn't work correctly") else: self.stdout.write(self.style.ERROR("✗ No WorkBalance record found")) self.stdout.write(" This suggests the calculation didn't create/update the record")