#StackBounty: #python #python-3.x #django #django-views #django-templates Django Python – Itertools is not aligning products with sum o…

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


Get this bounty!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.