def stock1(request):
queryset = product_items.objects.annotate(
purchase=Sum(
ExpressionWrapper(
F('purchase_trans_item__qty'),
output_field=DecimalField()
)
),
sales=Sum(
ExpressionWrapper(
F('sales_trans_item__qty') ,
output_field=DecimalField()
)
)
)
context={
'filter':queryset,
}
return render (request,'stock.html',context)
Side Note: When posting code here, enclose the code between lines of three backtick - ` characters. This means you’ll have a line of ```, then your code, then another line of ```. This forces the forum software to keep your code properly formatted. (Since this is your first post, I’ve taken the liberty of correcting it for you. Please remember to do this in the future.)
Please provide a more complete description of the issue in your post. Identify what isn’t working as expected, or post the complete traceback and error message of any error you are receiving.
It is also likely to be helpful if you posted the models that are involved here.
Thanks for Advising,
actually I require stock calculation from purchase_transaction and sales_transaction both model connected product_items model though foreign key
product_items
id product_name
1 product 1
2 product 2
purchase_transaction
product_name qty
1 2
1 3
2 2
sales_transaction
product_name qty
1 1
2 1
result require
product_name stock
1 4
2 1
So go ahead and post the code for those models here.
class product_items(models.Model):
product_name=models.ForeignKey(product_master,null=True,on_delete=models.CASCADE)
mode=models.CharField(max_length=200,default='')
unit=models.ForeignKey(unit_master,related_name='sub_unit',null=True,on_delete=models.SET_NULL)
conversion=models.FloatField(default=1)
cost= models.FloatField(default=0)
sale_price= models.FloatField(default=0)
class Meta:
db_table = 'product_items'
def __str__(self):
return str(self.product_name)
class purchase_transaction(models.Model):
barcode= models.ForeignKey(product_items,null=True, on_delete=models.SET_NULL,related_name='purchase_trans_item')
exp_date=models.DateTimeField(null=True,blank=True)
batch_no=models.CharField(max_length=200,null=True)
um=models.CharField(max_length=200,null=True)
qty= models.DecimalField(max_digits=16, decimal_places=7,default=0)
price= models.DecimalField(max_digits=16, decimal_places=7,default=0)
total= models.DecimalField(max_digits=16, decimal_places=7,default=0)
disc_type=models.CharField(max_length=50,null=True)
item_discount= models.DecimalField(max_digits=16, decimal_places=7,default=0)
total_aft_disc= models.DecimalField(max_digits=16, decimal_places=7,default=0)
tax_percetage= models.DecimalField(max_digits=16, decimal_places=7,default=0)
tax_amt= models.DecimalField(max_digits=16, decimal_places=7,default=0)
Item_total= models.DecimalField(max_digits=16, decimal_places=7,default=0)
store = models.ForeignKey(store_master,null=True, on_delete=models.SET_NULL)
status= models.IntegerField(default=0)
class Meta:
db_table = 'purchase_transaction'
def __str__(self):
return self.voucher_no
class sales_transaction(models.Model):
barcode= models.ForeignKey(product_items,null=True, on_delete=models.SET_NULL,related_name='sales_trans_item')
exp_date=models.DateTimeField(null=True,blank=True)
batch_no=models.CharField(max_length=200,null=True)
um=models.CharField(max_length=200,null=True)
qty= models.DecimalField(max_digits=16, decimal_places=7,default=0)
price= models.DecimalField(max_digits=16, decimal_places=7,default=0)
total= models.DecimalField(max_digits=16, decimal_places=7,default=0)
disc_type=models.CharField(max_length=50,null=True)
item_discount= models.DecimalField(max_digits=16, decimal_places=7,default=0)
total_aft_disc= models.DecimalField(max_digits=16, decimal_places=7,default=0)
tax_percetage= models.DecimalField(max_digits=16, decimal_places=7,default=0)
tax_amt= models.DecimalField(max_digits=16, decimal_places=7,default=0)
Item_total= models.DecimalField(max_digits=16, decimal_places=7,default=0)
store = models.ForeignKey(store_master,null=True, on_delete=models.SET_NULL)
status= models.IntegerField(default=0)
class Meta:
db_table = 'sales_transaction'
def __str__(self):
return self.voucher_no
Side note: You need to use the backtick - ` and not the apostrophe - '. Also, it’s a line that only has the ``` before and after the block of code. The three backticks must not be part of a line with any other text.
Example:
# The line above this is ```
def function():
return
# The line after this is ```
Thanks for posting the code.
So, what is the issue you are having? If you are getting an error, please post the complete traceback with the error. If you’re getting results that you don’t expect, please post the complete test case with enough data to demonstrate that what you’re getting is not what you’re expecting to see.
def stock1(request):
queryset = product_items.objects.annotate(
purchase=Sum(
ExpressionWrapper(
F('purchase_trans_item__qty'),
output_field=DecimalField()
)
),
sales=Sum(
ExpressionWrapper(
F('sales_trans_item__qty') ,
output_field=DecimalField()
)
)
)
for order in queryset:
purchaseqty=order.purchase
salesqty=order.sales
print(purchaseqty, salesqty)
context={
'filter':queryset,
}
return render (request,'stock.html',context)
result
Product3 4.0000000 4.0000000
Product6 None None
actually product 3 purchased two time
product name qty
product3 1
product3 1
and product3 sales two time
product name qty
product3 1
product3 1
so it should be result
product3 2 2 but showing 4 4
For testing purposes, what happens if you split these into two separate expressions?
e.g.:
queryset_1 = product_items.objects.annotate(
purchase=Sum(
ExpressionWrapper(
F('purchase_trans_item__qty'),
output_field=DecimalField()
)
)
)
queryset_2 = product_items.objects.annotate(
sales=Sum(
ExpressionWrapper(
F('sales_trans_item__qty') ,
output_field=DecimalField()
)
)
)
If this gives you the proper results, then see the docs for Combining multiple aggregations.
result require
ProductName Purchase Sales Stock
product3 2 2 0