Django pdf template count severity per row-CodePudding

I am using Django but I am stuck with the PDF report. There are three severity which are "Damage to Property", "Fatal" and "Non-Fatal". Download of PDF is working but but in my PDF report, I would want for every distinct address, it will count each severity (the number of Damage to Property, Fatal or Non-fatal). How can I achieve this?

This is what the table will look like:

Model

class IncidentGeneral(SoftDeleteModel):
PENDING = 1
APPROVED = 2
REJECTED = 3
STATUS = (
    (PENDING, 'Pending'),
    (APPROVED, 'Approved'),
    (REJECTED, 'Rejected')
)

IF_DUPLICATE = (
    ('Duplicate', 'Duplicate'),
    ('Possible Duplicate', 'Possible Duplicate'),
    ('Not Duplicate', 'Not Duplicate')
)

WEATHER = (
    ('Clear Night', 'Clear Night'),
    ('Cloudy', 'Cloudy'),
    ('Day', 'Day'),
    ('Fog', 'Fog'),
    ('Hail', 'Hail'),
    ('Partially cloudy day', 'Partially cloudy day'),
    ('Partially cloudy night', 'Partially cloudy night'),
    ('Rain', 'Rain'),
    ('Rain', 'Rain'),
    ('Wind', 'Wind'),
)

LIGHT = (
    ('Dawn', 'Dawn'),
    ('Day', 'Day'),
    ('Dusk', 'Dusk'),
    ('Night', 'Night'),
)

SEVERITY = (
    ('Damage to Property', 'Damage to Property'),
    ('Fatal', 'Fatal'),
    ('Non-Fatal', 'Non-Fatal'),
)

user = models.ForeignKey(User, on_delete=models.CASCADE, editable=False, null=True, blank=True)
generalid = models.CharField(max_length=250, unique=True, null=True, blank=True)
upload_id = models.ForeignKey(UploadFile, on_delete=models.CASCADE, editable=False, null=True, blank=True)
description = models.TextField(max_length=250, blank=True)
address = models.CharField(max_length=250)
country = models.CharField(max_length=50, blank=True, null=True)
state = models.CharField(max_length=250, blank=True, null=True)
city = models.CharField(max_length=50, blank=True, null=True)
pin_code = models.CharField(max_length=6, blank=True, null=True)
latitude = models.FloatField(max_length=20, blank=True, null=True)
longitude = models.FloatField(max_length=20, blank=True, null=True)
geo_location = gismodels.PointField(blank=True, null=True, srid=4326) # New field
upload_photovideo = models.FileField(upload_to=incident_image_upload_path, blank=True, null=True)
date = models.DateField(auto_now_add=False, auto_now=False)
time = models.TimeField(auto_now_add=False, auto_now=False)
status = models.PositiveSmallIntegerField(choices=STATUS, blank=True, null=True)
duplicate =  models.CharField(choices=IF_DUPLICATE,max_length=250, blank=True, null=True)
duplicate_general =  models.ForeignKey('incidentreport.IncidentGeneral', on_delete=models.CASCADE, null=True, blank=True)
accident_factor = models.ForeignKey(AccidentCausation, on_delete=models.SET_NULL, blank=True, null=True)
collision_type = models.ForeignKey(CollisionType, on_delete=models.SET_NULL, blank=True, null=True)
crash_type = models.ForeignKey(CrashType, on_delete=models.SET_NULL, blank=True, null=True)
weather =  models.CharField(choices=WEATHER, max_length=250,blank=True, null=True)
light =  models.CharField(choices=LIGHT,max_length=250, blank=True, null=True)
severity = models.CharField(choices=SEVERITY, max_length=250, blank=True, null=True)
movement_code = models.CharField(max_length=250, blank=True, null=True)
created_at = models.DateTimeField(auto_now_add=True)
updated_at = models.DateTimeField(auto_now=True)

VIEWS

def GenerateInvoiceLocation(request):
try:
    if request.method == 'POST':
        fromdate = request.POST.get('fromdate')
        todate = request.POST.get('todate')
        report = GenerateReport(user=request.user, fromdate=fromdate, todate=todate, report="Location PDF")
        report.save()
        
    print(fromdate, todate)

    # incident_general_accident = IncidentGeneral.objects.filter(user_report__status = 2).values('accident_factor__category').annotate(Count('severity'), filter=Q(severity='Damage to Property'))
    incident_general_accident = IncidentGeneral.objects.filter(status = 2, date__range=[fromdate, todate ]).distinct('address', 'latitude', 'longitude')
    incident_general_accident1 = IncidentGeneral.objects.filter(status = 2,severity='Fatal', date__range=[fromdate, todate ] ).annotate(Count('severity'))
    incident_general_accident2 = IncidentGeneral.objects.filter(status = 2,severity='Damage to Property', date__range=[fromdate, todate ] ).annotate(Count('severity'))
    incident_general_accident3 = IncidentGeneral.objects.filter(status = 2,severity='Non-Fatal', date__range=[fromdate, todate ] ).annotate(Count('severity'))
    incident_general_classification = IncidentGeneral.objects.filter(status = 2, severity="Damage to Property", date__range=[fromdate, todate ]).distinct('address', 'latitude', 'longitude')
    incident_general_collision = IncidentGeneral.objects.filter(status = 2, severity="Damage to Property", date__range=[fromdate, todate ]).distinct('address', 'latitude', 'longitude') #you can filter using order_id as well
    
    
    report1 = GenerateReport.objects.all().order_by('-created_at')[:1]
except:
    return HttpResponse("505 Not Found")
data = {
    'incident_general_accident': incident_general_accident,
    'incident_general_classification': incident_general_classification,
    'incident_general_collision': incident_general_collision,
    'incident_general_accident1': incident_general_accident1,
    'incident_general_accident2': incident_general_accident2,
    'incident_general_accident3': incident_general_accident3,
    'report1':report1
        # 'amount': order_db.total_amount,
}
pdf = render_to_pdf('pages/generate_report_pdf_location.html', data)
    #return HttpResponse(pdf, content_type='application/pdf')

    # force download
if pdf:
    response = HttpResponse(pdf, content_type='application/pdf')
    filename = "Location.pdf" #%(data['incident_general.id'])
    content = "inline; filename='%s'" %(filename)
        #download = request.GET.get("download")
        #if download:
    content = "filename=%s" %(filename)
    response['Content-Disposition'] = content
    return response
    # return HttpResponse("Not found")
return render(request, 'pages/generate_report_sa.html')

HTML

<table  style="width:100%">
  <h3>{{report1.fromdate}} {{report1.todate}}</h3>
      <thead>
          <tr>
            <th scope="col">DATE</th>
            <th scope="col">TIME</th>
            <th scope="col">ADDRESS</th>
              
              {% comment %} <th scope="col">ACCIDENT CAUSATION SUB-CATEGORY</th> {% endcomment %}
              <th scope="col">DAMAGE TO PROPERTY</th>
              <th scope="col">FATAL</th>
              <th scope="col">NON-FATAL</th>
          </tr>
      </thead>
      <tbody>
          {% for i in incident_general_accident %}
        
          <tr>
            <td scope="row">{{i.date}}</td>
            <td scope="row">{{i.time}}</td>
            <td scope="row">{{i.address}}</td>
              {% comment %} <td scope="row">{{i.accident_subcategory.sub_category}}</td> {% endcomment %}
              {% if severity == "Damage to Property"%}
              <td>{{i.severity | length}}</td>
              {% endif %}
              {% if severity == "Fatal"%}
              <td>{{i.severity | length}}</td>
              {% endif %}
              {% if severity == "Non-Fatal"%}
              <td>{{i.severity | length}}</td>
              {% endif %}
             
       
          </tr>
          {% endfor %}

          <tr>
              <td scope="row"></td>
              <td>TOTAL: </td>
              <td>{{incident_general_accident | length}}</td>
              
          </tr>
      </tbody>
  </table>

CodePudding user response：

you can of course do it by using Django annotate and Q Djagno aggregation

But, first of all, it is a good behaviour to use related_name in your ForeignKey field :

user = models.ForeignKey(User,related_name='incident_generals' on_delete=models.CASCADE, editable=False, null=True, blank=True)

By this way you can retrieve all related IncidentGeneral for a user by doing : user.incident_generals.all(). It is possible without that but it is more suitable.

Next: If you want to retrieve all IncidentGeneral for a user with the count off severity status then you do :

from django.db.models import Count, Q
count_damage_to_property = Count('IncidentGeneral', filter=Q(severity='Damage to Property'))
count_fatal = Count('IncidentGeneral', filter=Q(severity='Fatal'))
count_non_fatal = Count('IncidentGeneral', filter=Q(severity='Non-Fatal'))
user_incident_generals = user.incident_generals.annotate(damage_to_property=count_damage_to_property).annotate(fatal=count_fatal).annotate(non_fatal=count_non_fatal)

You can now acces each user IncidentGeneral count by severity level like this

for user_incident in user_incident_generals:
    nb_fatal = user_incident.fatal
    nb_non_fatal = user_incident.non_fatal

You can now do what you whant. Hope it will help you