Bounty: 50
I generate a list of Products based on two criterias Entity and Period.
The list retrieved contains all Products and the sum of all auctions sales for each. The example below shows a list of 4 products for Company XZY in November. Out of all the products listed only one product (sku 30) had two auction sales $180 and $220. The problem is the rest of products had no sales so the null value is not aligning properly with the product. I will need to still list those products and show $0 in sales.
Models.py
class Entity(models.Model):
entity = models.CharField(max_length=40)
class Period(models.Model):
period = models.CharField(max_length=7)
class Product(models.Model):
entity = models.ForeignKey(Entity, on_delete=models.CASCADE, default=None, blank=True, null=True)
period = models.ForeignKey(Period, on_delete=models.CASCADE, default=None, blank=True, null=True)
sku = models.CharField(max_length=40)
projectedsales = models.DecimalField(max_digits=11, decimal_places=2)
class Post(models.Model):
product = models.ForeignKey(Product, on_delete=models.CASCADE, default=None, blank=True, null=True)
auctionsale = models.DecimalField(max_digits=11, decimal_places=2)
Views.py
def get(self, request):
form = ProductsViewForm()
return render(request, "products_list.html", {'form': form})
def post(self, request):
products = None
posts = None
entities = None
periods = None
mylist = None
form = ProductsViewForm(request.POST)
if request.method == 'POST':
if form.is_valid():
entityquery = form.cleaned_data['entity']
periodquery = form.cleaned_data['period']
entities = Entity.objects.get(entity=entityquery)
periods = Period.objects.get(period=periodquery)
products = Product.objects.filter(entity=entityquery, period=periodquery).values('id', 'period', 'entity', 'sku', 'projectedsales')
posts = Post.objects.filter(product__sku__in=products.values('sku'), product__entity=entityquery, product__period=periodquery).annotate(total=Sum('auctionsale'))
mylist = list(itertools.zip_longest(products, posts, fillvalue='0'))
args = {'form': form, 'periods': periods, 'entities': entities, 'mylist': mylist}
return render(request, "products_list.html.html", args)
products_list.html
<div class="row">
<div class="col-12">
<form method="POST">
{% csrf_token %}
<div class="form-row">
<div class="form-group col-md-4">
{{ form.entity|add_class:"custom-select" }}
</div>
<div class="form-group col-md-4">
<input type="text" name="period" id="id_period" class="form-control" placeholder="mm/yyyyy" maxlength="7" minlength="7" required>
</div>
<div class="form-group col-md-4">
<input type="submit" value="Run Balance Sheet" class="btn btn-primary btn-block">
</div>
</div>
</form>
</div>
</div>
<div class="row">
<div class="col-12">
<br>
{% if entities %}
<center><h4>Auction Sales</h4></center>
<center><h5>Entity : {{entities.entity}}</h5></center>
{% endif %}
{% if periods %}
<center><h5>Period : {{periods.period}}</h5> </center>
{% endif %}
<br>
{% if mylist %}
<table class="table table-striped">
<thead>
<tr>
<th scope="col">Product</th>
<th scope="col">Projected Sales</th>
<th scope="col">Total Auction Sales</th>
</tr>
</thead>
<tbody>
{% for product, post in mylist %}
<tr>
<td>{{product.sku}}</td>
<td>{{product.projectedsales}}</td>
<td>{{post.total}}</td>
</tr>
{% endfor %}
</tbody>
</table>
{% endif %}
</div>
</div>
Query
Entity: Company XZY
Period: 11/2020
Expected results
Product -- Projected Auction Sales -- Total Auction Sales
sku 10 -- $100 -- $0
sku 20 -- $200 -- $0
sku 30 -- $600 -- $400
sku 40 -- $500 -- $0
The results i am getting
Product -- Projected Auction Sales -- Total Auction Sales
sku 10 -- $100 -- $400
sku 20 -- $200
sku 30 -- $600
sku 40 -- $500