Calculate the stock count

Hi, this is my first post.
I’m building a bike rental app and I’m having a hard time writing logic to calculate the stock count.

Ingredients:
the total stock of bikes category wise.
active bookings.

recipe:
1.i need to consider the start_date and end_date to query the booking model and pull all the active bookings matching the dates.
2.count number of bikes present in the booking with the quantity, category wise.(i have 6 category of bikes).
3. deduct the count from the total stock of bikes and display the result.

i understand using aggregate would be the best solution.

i tried it this way, but couldn’t finish it.

#booking logic
booking = Booking.objects.filter(Q(start_date__lte = start_date) & Q(end_date__gte = start_date) | (Q(end_date__lte = end_date) & Q(start_date__gte = end_date)))
booking= booking.values_list(‘cart’, flat=True)
cart = CartItem.objects.filter(cart__in = booking)

        sporty=0
        step= 0
        fat= 0
        tricycle =0
        scoo=0
        triscoo=0 

        for item in cart:
            if item.vehiclecategory.title == 'Sporty':
                sporty += item.quantity
            
            if item.vehiclecategory.title == 'Step-Through':
                step += item.quantity

            if item.vehiclecategory.title == 'Fatbike':
                fat += item.quantity

            if item.vehiclecategory.title == 'Tri-Cycle':
                tricycle += item.quantity

            if item.vehiclecategory.title == 'Scooter':
                scoo += item.quantity

            if item.vehiclecategory.title == 'Tri-Wheel Scooter':
                triscoo += item.quantity

Any help would be appreciated.

(Side note: When posting code, please enclose it all between lines consisting only of three backtick (`) characters - so you would have one line that is just ```, followed by your code, followed by another line of ```. Also note, be sure to use the backtick character and not the apostrophe - ')

What do your models look like? It might be helpful to see the minimal implementation of those.

For clarification, do you have an object representing a vehicle, with an attribute identifying its type? What do Cart and Booking represent?

This isn’t the only problem (it’s probably most helpful if you can provide the information KenWhitesell suggested), but I think what you want for this filter is instead:

booking = Booking.objects.filter(Q(start_date__lte = start_date) & Q(end_date__gte = start_date) | (Q(start_date__lte = start_date) & Q(end_date__gte = end_date)))

I think this is what you want, because (Q(end_date__lte = end_date) & Q(start_date__gte = end_date)) doesn’t seem to make much sense - these conditions would only return True if the “start” and “end” date have the same value. You can’t have the “end date” occur before a date, while the “start date” occurs later than the same date. Unless I’m getting things wrong? It’s easy to get confused about those things, especially when defining and using four Q objects in one line.

thank you for replying.
MODELS.PY


class Vehicle(models.Model):

    STATUS_CHOICES=(
        ('KIM', 'KIM'),
        ('Rented Out', 'Rented Out'),
        ('Available Online', 'Available Online'),
        ('Available Offline', 'Available Offline'),
        ('Retired', 'Retired'),
        ('Under Repair', 'Under Repair'),
        ('In Store', 'In Store')
    )

    

    serial_number = models.CharField(max_length= 100, unique=True)  
    slug =models.SlugField(max_length=200, db_index=True)
    vehicle_category = models.ForeignKey(VehicleCategory,on_delete=models.SET_NULL,related_name='category', null=True)
    vehicle_brand = models.ForeignKey(VehicleBrand, on_delete=models.SET_NULL, null=True)
    date_of_purchase = models.DateField(blank=True)
    date_of_retiring = models.DateField(blank=True, null=True)
    purchase_price = models.DecimalField(max_digits=10, decimal_places=2, blank=True, null=True)
    vehicle_status = models.CharField(max_length=100, choices=STATUS_CHOICES, default='Available Online')
    whether_rented_out = models.BooleanField("If Rented Out", default=False)
    whether_reserved = models.BooleanField("Reserved", default=False)
    qr_code = models.ImageField(
        null=True,
        blank=True,
        width_field="width_field",
        height_field= "height_field",
        upload_to='qrCode')
    created = models.DateTimeField(auto_now_add=True)
    updated = models.DateTimeField(auto_now= True)
    objects = models.Manager()#default manager
    active = ActiveManager() # custom manager
   


    def __str__(self):
        return '%s - %s - %s' % (self.vehicle_category, self.serial_number, self.vehicle_status)

class VehicleCategory(models.Model):

    CATEGORY_CHOICES=(
        ('E-Cycle', 'E-Cycle'),
        ('E-Scooter', 'E-Scooter')
    )
    
    main_category = models.CharField(max_length=15, choices= CATEGORY_CHOICES)
    title = models.CharField(unique=True, max_length=200)
    image = models.ImageField(
        null=True,
        blank=True,
        width_field="width_field",
        height_field= "height_field",
        default= 'e-bike.png',
        upload_to='category')
    width_field = models.IntegerField(default=250)
    height_field = models.IntegerField(default=250) 
    slug =models.SlugField(max_length=200, db_index=True, unique=True)
    objects= models.Manager() # the default manager
    active = ActiveManager() # custom manager


class Booking(models.Model):
    help_text= "Ensure no more than 2 digits after decimal"
    BOOKING_CHOICES = [
        ('Online Booking', 'Online Booking'),
        ('Offline Booking', 'Offline Booking'),
        ('Other', 'Other')
        ]
    PAYMENT_CHOICES = [
        ('Paid', 'Paid'),
        ('UnPaid', 'UnPaid')    
    ]

    PAYMENT_TYPE_CHOICES = [
        ('Auro Account', 'Auro Account'),
        ('Auro Card', 'Auro Card'),
        ('RazorPay', 'RazorPay'),
        ('Cash', 'Cash'),
        ('Other', 'Other')   
    ]


    booking_id = models.AutoField(primary_key=True)
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.SET_NULL, blank=True, null=True)
    customer = models.ForeignKey(Customer, on_delete=models.SET_NULL, null=True, blank=True)
    start_date =  models.DateField(blank=True, null=True)
    end_date =  models.DateField(blank=True, null=True)
    booking_type = models.CharField(max_length=50, choices=BOOKING_CHOICES, default='Online Booking')
    payment_status = models.CharField(max_length=10, choices=PAYMENT_CHOICES, default='UnPaid')
    amount_paid = models.DecimalField(help_text=help_text,max_digits=7, decimal_places=2, default=0, blank=True)
    discount = models.DecimalField(help_text=help_text,max_digits=5, decimal_places=2, default=0, blank=True)
    payment_type = models.CharField(max_length=50, choices=PAYMENT_TYPE_CHOICES,  null=True, blank=True)
    comments = models.TextField(blank=True, null=True)
    invoice_number = models.CharField(max_length=100, blank=True, null=True)
    total_price = models.IntegerField(blank=True, null=True)
    vehicle = models.ManyToManyField(Vehicle, blank=True)
    accessory = models.ManyToManyField(Inventory, blank=True)
    cart = models.ForeignKey(Cart, on_delete=models.CASCADE)
    booked =  models.BooleanField(default=False)
    transaction_id = models.CharField(max_length=200, null=True, blank=True)
    booking_on = models.DateTimeField(auto_now_add=True)
    
    def clean(self):
        if self.start_date > self.end_date:
            raise ValidationError('Rental ended before start.')

    def __str__(self):
        return str(self.booking_id)

class CartItem(models.Model):
    cart = models.ForeignKey(Cart, on_delete=models.CASCADE, blank=True)
    vehiclecategory =  models.ForeignKey(VehicleCategory, on_delete=models.CASCADE, related_name='vehiclecategory')
    quantity = models.IntegerField(default=1)
    accessory =  models.ManyToManyField(Inventory,related_name='accessory',blank=True)
    accessory_quantity = models.IntegerField(default=0)

  class Cart(models.Model):
    vehiclecategory = models.ManyToManyField(VehicleCategory,blank=True)
    active = models.BooleanField(default=True)


HI,

when a user enters a start_date and end_date, I want to check to see if the start or end or both dates are in the range of bookings start and end date.

if any of the dates are present in booking then I pull all those bookings and calculate the count of vehicles booked category wise, then deduct the count with the Total stock, and show how many vehicles are available.

for a demo you can got to Kinisi.co.in that’s the web app im building

when a user enters a start_date and end_date, I want to check to see if the start or end or both dates are in the range of bookings start and end date.

I’m a bit confused by this, but if the logic you’ve written does actually do what you want to then great :slight_smile:

The site’s layout looks great, though I only looked at it very briefly. I just wanted to reply quickly to recommend that you change your admin path. I tried going to https://kinisi.co.in/admin/ and I did indeed get to the admin login. This is a serious security issue AFAIK. You can change the admin path to anything you want, e. g. ‘my-great-fantastic-admin’, just as long as it’s not the default ‘admin’. There are countless bots scouring the web and trying to hit URL’s by adding /admin, precisely because it’s known that that’s the default for Django (and it also indicates that the web developer might be fairly new and not have other security measures in place either).

im aware of the security risk involved with admin, but its still in the testing phase so didn’t really work on it. the filter works as expected.
however im stuck with the code after filter, one I think lopping through the model and adding the count is not very effective, moreover I still don’t know how to deduct the count with the default quantity.

First, to clarify what you’re looking for in the date range:

User entered start date: 07 June 2020, end date 14 June 2020

Bookings

Start date End date Include?
05 June 08 June Yes
12 June 15 June Yes
08 June 10 June Yes
05 June 15 June Yes
03 June 05 June No
16 June 20 June No

Am I understanding the conditions correctly?

Also, you reference a “total stock count”, but I don’t see that defined above. What is the formula for calculating that? (Can it be described as simply Vehicle.objects.count()? Or are there other conditions involved?)

You’ve got what appears to me to be a very unusual relationship set between Cart, CartItem, and VehicleCategory. Can you describe what these objects represent?

It appears to me that a Booking is a reservation for one or more vehicles over a range of dates. Is that correct?

So a Booking has a M2M relationship with Vehicle which means a customer can reserve multiple vehicles at one time. Each Vehicle has a M2O relationship with a VehicleCategory, which means that for each Vehicle, you can determine its category.

But, a Booking also has a M2O relationship with a Cart. The Cart has a M2M relationship with VehicleCategory - which may (or may not) have any relationship to the Vehicles previously associated with the Booking. The Cart also has a O2M “back relationship” to a CartItem, which then has a M2O relationship to another VehicleCategory - which may or may not have any specific relationship to a VehicleCategory referenced by the Cart.

From all this I would guess that a Cart might be a “standard” reservation for a type of Vehicle. (Like a “favorites” list.) A CartItem might be intended to be the individual Vehicles that comprise the Cart?

(Also, I see a reference to an Inventory model, where does that fit into this picture?)

My first reaction to seeing this is that these models and relationships are needlessly complex.

75% of the problem is properly designing your data representation such that your models facilitate and assist with calculations needing to be performed rather than hindering them. If it’s not possible to accurately and effectively describe the purpose and relationships of each and every models, that’s a possible indication of a suboptimal implementation. I would think that if you re-evaluate your models, you’ll find that the types of calculations you’re looking to perform are going to work out to be a lot easier.

Ken

yes you got the dates right.
that’s exactly how I want it and that’s exactly how its working now.
the stock is calculated on category basis.
this is the calculation of stock in the vehicle model.

@property
    def online(self):
        return Vehicle.objects.filter(vehicle_status= "Available Online", vehicle_category__title= self.title).count()

    @property
    def offline(self):
        return Vehicle.objects.filter(vehicle_status= "Available Offline", vehicle_category__title= self.title).count()

    @property
    def kim(self):
        return Vehicle.objects.filter(vehicle_status= "KIM", vehicle_category__title= self.title).count()

    @property
    def retired(self):
        return Vehicle.objects.filter(vehicle_status= "Retired", vehicle_category__title= self.title).count()

basically a user can book multiple vehicles in an order, when he adds a vehicle to cart a cart is created and a cart item is created.

my app is working completely fine, however I have 2 issues.

  1. showing the available stock which I got it working but its mostly hardcoded, can u help me to simplify this.

this is my logic.

def search(request):
    template_name = 'app/vehicles.html' 
    if request.method == 'GET':
        form = DateForm(request.GET)
        if form.is_valid():
            start_date =  form.cleaned_data['start_date']
            end_date =  form.cleaned_data['end_date']


            
            start = start_date.strftime("%d/%m/%Y")
            end = end_date.strftime("%d/%m/%Y")

            request.session['start_date'] = start
            request.session['end_date'] = end
        

            duration = (end_date - start_date).days +1
           
            request.session['duration'] = duration

            #booking logic
            
            booking = Booking.objects.filter(Q(start_date__lte = start_date) & Q(end_date__gte = start_date) | (Q(start_date__lte = end_date) & Q(end_date__gte = end_date)))
            
            booking= booking.values_list('cart', flat=True)

            

            cart = CartItem.objects.filter(cart__in = booking)

            sporty=0
            step= 0
            fat= 0
            tricycle =0
            scoo=0
            triscoo=0 

            for item in cart:
                if item.vehiclecategory.title == 'Sporty':
                    sporty += item.quantity
                
                if item.vehiclecategory.title == 'Step-Through':
                    step += item.quantity

                if item.vehiclecategory.title == 'Fatbike':
                    fat += item.quantity

                if item.vehiclecategory.title == 'Tri-Cycle':
                    tricycle += item.quantity

                if item.vehiclecategory.title == 'Scooter':
                    scoo += item.quantity

                if item.vehiclecategory.title == 'Tri-Wheel Scooter':
                    triscoo += item.quantity
           
            vehiclecategory = VehicleCategory.objects.all()
            spo =  vehiclecategory.get(title='Sporty')
            sporty_count = spo.online - sporty

            spo =  vehiclecategory.get(title='Step-Through')
            step = spo.online - step

            spo =  vehiclecategory.get(title='Fatbike')
            fatbike = spo.online - fat

            spo =  vehiclecategory.get(title='Tri-Cycle')
            tricycle = spo.online - tricycle

            spo =  vehiclecategory.get(title='Scooter')
            scoo = spo.online - scoo

            spo =  vehiclecategory.get(title='Tri-Wheel Scooter')
            triscoo = spo.online - triscoo
        
            context = {
                'vehiclecategory1': vehiclecategory.filter(main_category= 'E-Cycle'),
                'vehiclecategory2': vehiclecategory.filter(main_category= 'E-Scooter'),
                'form':CartQuantityForm(),
                'dateform': DateForm(initial={'start_date': start_date, 'end_date':end_date}),
                'vehicleprice': VehiclePrice.objects.all(),

                'sporty_count':sporty_count,
                'step':step,
                'fatbike': fatbike,
                'tricycle': tricycle,
                'scooter': scoo,
                'triscooter': triscoo,
                
                
            }
            
            return render(request, template_name, context)
    messages.warning(request,form.errors)
    return render(request, template_name, {'dateform': DateForm()})

I believe this is not very effective but it does work.

this is how the template looks.

<div class="row">
                    {% for item in vehiclecategory1 %}
                    <div class="col-md-4 mb-3 disable">
                        <!-- Card Narrower -->
                        <div class="card h-100 z-depth-0" style="border: 1px solid #E6E6E6;">
                            <!-- Card image -->
                            <div class="view overlay ">
                                <img class="card-img-top" src="{{item.image.url}}"
                                alt="{{item.title}}">
                                <a>
                                <div class="mask rgba-white-slight"></div>
                                </a>
                            </div>
                            <!-- Card content -->
                            <div class="card-body card-body-cascade">
                                <!-- Label -->
                            
                                <!-- Title -->
                                <h4 class="card-title">{{item.title}}</h4>
                                {% for items in vehicleprice %}
                                    {% if items.vehicle_category.title == item.title and request.session.duration >= items.slab.start and request.session.duration <= items.slab.end %}
                                    <h5 class="pink-text" ><i class="fas fa-rupee-sign"></i>{{items.total_price}}/day</h5>
                                    {% endif %}
                                {% endfor %}
                                <br>
                                Stock:{% if item.title == 'Sporty' %}
                                        {{sporty_count}}
                                    {% elif item.title == 'Step-Through' %}
                                        {{step}}
                                    {% elif item.title == 'Fatbike' %}
                                        {{fatbike}}
                                    {% else %}
                                        {{tricycle}}
                                    {% endif%}
                                <div class="row" id="row">      
                                    <div class="col-md-12" >
                                        <form action="{% url 'cart:add-to-cart' item.id %}" method="POST">
                                            {% csrf_token %}
                                            <div class="row">
                                                <div class="col-md-7 col-sm-7 col-xs-7"> 
                                                Quantity: <span>{{form.quantity}}</span>
                                                </div>
                                                <div class="col-md-5 col-sm-5 col-xs-5">
                                                    <button id="update" class="btn btn-success pt-2 pb-2 z-depth-0" type="submit">Add<i class=" fa fa-shopping-cart"></i></button>
                                                </div>
                                            </div>
                                            </form>
                                    </div>
                                    
                                </div>
                            </div>
                        </div>

my major issue is showing output in a loop… I tried with aggregate but couldn’t get it right.

  1. second issues is the price .

the price is calculated based on the duration, duration is calculated from the start and end date entered by the user while searching for the bikes.

this is how the price is calculated on the vehicle category model.

def duration(self):
        return 1

    @property
    def get_price(self):
        for item in VehiclePrice.objects.all():
            if item.vehicle_category.title == self.title and (self.duration() >= item.slab.start and self.duration() <= item.slab.end):
                return item.total_price 

if you notice the duration is hardcoded for now to test the rest of the code, but how can I pass the duration in the model function ???

if you see my views.py above you will see I convert the date range into number of days.

stock count is not a priority, but this price is .

I can’t seem to find a way around it.

please help.

I would like to help, but I don’t understand the relationships among your data - and in order to provide you with an appropriate query to perform the calculations you desire, I need to understand the data model and what it all represents.

So I will ask again - what are these objects and what do they represent in this process?

let me explain.

MODELS.PY

class VehicleCategory(models.Model):

    CATEGORY_CHOICES=(
        ('E-Cycle', 'E-Cycle'),
        ('E-Scooter', 'E-Scooter')
    )
    
    main_category = models.CharField(max_length=15, choices= CATEGORY_CHOICES)
    title = models.CharField(unique=True, max_length=200)
    image = models.ImageField(
        null=True,
        blank=True,
        width_field="width_field",
        height_field= "height_field",
        default= 'e-bike.png',
        upload_to='category')
    width_field = models.IntegerField(default=250)
    height_field = models.IntegerField(default=250) 
    slug =models.SlugField(max_length=200, db_index=True, unique=True)
    objects= models.Manager() # the default manager
    active = ActiveManager() # custom manager


    
    def __str__(self):
        return self.title

    @property
    def online(self):
        return Vehicle.objects.filter(vehicle_status= "Available Online", vehicle_category__title= self.title).count()

    @property
    def offline(self):
        return Vehicle.objects.filter(vehicle_status= "Available Offline", vehicle_category__title= self.title).count()

    @property
    def kim(self):
        return Vehicle.objects.filter(vehicle_status= "KIM", vehicle_category__title= self.title).count()

    @property
    def retired(self):
        return Vehicle.objects.filter(vehicle_status= "Retired", vehicle_category__title= self.title).count()
    
    

    def duration(self):
        return 1

    @property
    def get_price(self):
        for item in VehiclePrice.objects.all():
            if item.vehicle_category.title == self.title and (self.duration() >= item.slab.start and self.duration() <= item.slab.end):
                return item.total_price 
        
    class Meta():   
        verbose_name = "Vehicle Category"
        verbose_name_plural = "Vehicle Categories"




class PriceSlab(models.Model):
    start = models.IntegerField()
    end = models.IntegerField()
    timestamp = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return '%s - %s ' % (self.start, self.end)



class VehiclePrice(CustomerStatus):
    help_text= "Ensure no more than 2 digits after decimal"
    vehicle_category = models.ForeignKey(VehicleCategory, on_delete= models.SET_NULL, null=True, related_name='vehicle_category_price')
    slab = models.ForeignKey(PriceSlab, on_delete=models.CASCADE)
    net_price = models.DecimalField(help_text= help_text, max_digits=5, decimal_places=2)
    tax_percent = models.DecimalField(help_text=help_text, max_digits=4, decimal_places=2, default=18.00)
    discount_percent = models.DecimalField(help_text=help_text,max_digits=4, decimal_places=2, default=0, blank=True)
    
    @property
    def total_discount(self):
        discount = (self.net_price * self.discount_percent)/100
        return discount

    @property
    def get_price(self):
        total = self.net_price  - self.total_discount
        return total 

    @property
    def total_tax(self):    
        tax = (self.get_price * self.tax_percent)/100
        return tax
    

    @property
    def total_price(self):
        total = self.get_price + self.total_tax
        
        return round(total)

    class Meta():
        unique_together=('customer_status','vehicle_category' ,'slab')

    def __str__(self):
        return '%s - %s - %s' % (self.customer_status, self.vehicle_category, self.slab)

As u can see I have 3 models. vehiclecategory, vehicle price and slab.

slab model is number of days.

Screenshot 2020-06-23 at 9.48.05 PM

1 to 3 days or 4 to 7 days.

when a user enters start and end date this range is converted in days to see which slabs it falls in.

I can’t have more than one image so this is the continuation.


this is how the price model look like.


a user chooses the start and end date.

based on the dates selected I convert that in to days.

Screenshot 2020-06-23 at 9.54.06 PM

based on the selected dates I should get this result.

the calculation of price is done in the vehiclecategory model, (if u go back and look at the vehiclecategory model I have a @property which calculates the price. however one of the conditions to calculate the price is the duration, and the duration is received in the frontend via the search form.

how will I pass the duration in the model function to display the price???

I don’t know if I can be any more clearer.

please help!

Addressing on the price calculation question -

I’m not sure why you’re flagging get_price as a property - I don’t see what advantage it provides.

If you just make get_price a standard function, it can accept duration as a parameter and perform the calculations accordingly.

If there is some reason why get_price needs to be a property, you could create another method that accepts duration as a parameter, and then calls get_price to perform the calculation.

Ken

I can still keep get price as property and make the duration as function which accepts a parameter.
But how will I pass the duration from my views to models ???

If your view is just executing a query on the model in a manner like:
price_list = VehiclePrice.objects.all(), then what you can do is annotate the duration on to the queryset similar to this:

price_list = VehiclePrice.objects.annotate(duration=duration_from_form)

This will add a member attribute to each row in the queryset named “duration” having the value that you’re passing in from the form.

In that case, you don’t need a separate method to accept the duration - you can just calculate price using the self.duration member variable on the object.

Ken

I don’t know how that works, I tried your way and it gave me an error, I’ve sent you all my Code, I don’t have a field duration on any model. im writing a model function to get the duration.
can u write a model function which accepts a parameter and then use .annotate in the views? cos this way I tried but doesn’t work

The purpose of the annotate method is to inject a new field into the model being returned from the query, so it becomes a new field dynamically added as part of the query.