The application is built on django/angular. I want to generate a excel report based on model and it's fields selected by users. You can find search UI below. I have 4 models in django. Coach, Player, Participation which have foreign key reference to Club (One-To-Many relationship). The individual django model will act as a select input and model fields as an options
models.py
from datetime import datetime
from django.db import models
class Club(models.Model):
name = models.CharField(max_length=200)
estd = models.IntegerField()
address = models.CharField(max_length=200)
def __unicode__(self):
return "%s" % self.name
class Coach(models.Model):
fname = models.CharField(max_length=80)
lname = models.CharField(max_length=80)
age = models.IntegerField()
fk = models.ForeignKey(Club, related_name='coaches')
def __unicode__(self):
return "%s" % self.fname
class Player(models.Model):
fname = models.CharField(max_length=80)
lname = models.CharField(max_length=80)
country = models.CharField(max_length=42)
fk = models.ForeignKey(Club, related_name='players')
def __unicode__(self):
return "%s" % self.fname
class Participation(models.Model):
league = models.CharField(max_length=80)
startdate = models.DateTimeField()
fk = models.ForeignKey(Club, related_name='participations')
def __unicode__(self):
return "%s" % self.league
Search UI (Select dropdown)
##### ###### ####### #############
Club Coach Player Participation
##### ###### ####### #############
name fname fname league
estd lname lname startdate
address age country
Use case
- User have to select at least one field from the Club dropdown.
- User can select one or more fields from Coach, Player and Participation dropdown.
HTML
<select class="form-control" data-ng-model="selected" data-ng-options="item.tablefield for item in coach" ng-click="addField()"></select>
<select class="form-control" data-ng-model="selected" data-ng-options="item.tablefield for item in player" ng-click="addField()"></select>
<select class="form-control" data-ng-model="selected" data-ng-options="item.tablefield for item in participation" ng-click="addField()"></select>
<button type="button" class="btn btn-default" ng-click="report()">Generate report</button>
Angular JS
$scope.club = [{
'tablename': 'Club',
'tablefield': 'name'
},
{
'tablename': 'Coach',
'tablefield': 'estd'
},
{
'tablename': 'Coach',
'tablefield': 'address'
}
];
$scope.coach = [{
'tablename': 'Coach',
'tablefield': 'fname'
},
{
'tablename': 'Coach',
'tablefield': 'lname'
},
{
'tablename': 'Coach',
'tablefield': 'age'
}
];
$scope.player = [{
'tablename': 'Player',
'tablefield': 'fname'
},
{
'tablename': 'Player',
'tablefield': 'lname'
},
{
'tablename': 'Player',
'tablefield': 'country'
}
];
And Similar for participation
$scope.queryfields = [];
// add fields
$scope.addField = function(){
var found = $scope.queryfields.some(function (el) {
return el.value === $scope.selected.tablefield;
});
if (!found) {
var searchkey = $scope.selected.tablename,
searchvalue = $scope.selected.tablefield;
$scope.queryfields.push({
key: searchkey,
value: searchvalue
})
}
else{
console.log('field already exist');
}
};
// SEARCH
$scope.report = function() {
if($scope.queryfields.length > 1){
// post search fields data
$http.post('/api/gamify/advancesearch/', $scope.queryfields)
.success(function (response) {
$scope.queryset = response;
})
.error(function (data, status, headers, config) {
console.log('error');
});
}
};
The Selected fields from select inputs are sent to django views for query and result concatenation. The data send to django views looks like this
[{u'value': u'name', u'key': u'Club'}, {u'value': u'fname', u'key': u'Coach'}, {u'value': u'lname', u'key': u'Coach'}, {u'value': u'fname', u'key': u'Player'}, {u'value': u'league', u'key': u'Participation'}]
Views
def report(request):
qfields = json.loads(request.body)
print query
""" [{u'value': u'name', u'key': u'Club'}, {u'value': u'fname', u'key': u'Coach'}, {u'value': u'lname', u'key': u'Coach'}, {u'value': u'fname', u'key': u'Player'}, {u'value': u'league', u'key': u'Participation'}]"""
# TO-DO
# Get all records of Club (field: name)
# Get all records of Coach (fields: fname, lname) which is reference of Club.
# Get all records of Player (field: fname) which is reference of Club.
# Get all records of Participation (field: league) which is reference of club.
# Export to excel
# Response json object
records = Player.objects.order_by('id').values('id', *qfields)
return HttpResponse(json.dumps(list(records)))
This is how json response must look like. The JSON response will be converted to excel file
{
"datarow1":{
"Club":[
{
"club.name":"FC Bar"
},
{
"coach":{
"coach.fname":[
"Hari",
"Shyam",
"Parbe"
]
}
},
{
"player":[
{
"player.fname":[
"King",
"Leo",
"Singh"
]
},
{
"player.lname":[
"Dev",
"Fin"
]
}
]
},
{
"participation":[
{
"participation.league":[
"la liga",
"UEFA"
]
}
]
}
]
},
"datarow2":{
"Club":[
{
"club.name":"FC TU"
},
{
"coach":{
"coach.fname":[
"Xavi",
"Hola",
"Them"
]
}
},
{
"player":[
{
"player.fname":[
"Sab",
"Hi",
"Suz"
]
},
{
"player.lname":[
"Messi",
"Aura"
]
}
]
},
{
"participation":[
{
"participation.league":[
"Italian",
"Premier"
]
}
]
}
]
},
}
Help
How i can get all records of Clubs and foreign key data (Coach, Player, Participation) related it based on selected model fields? Example of report is shown above.
Any help and feedback's are appreciated.
