def view_notifications(request): current_shop = get_current_shop(request) if not request.user.is_superuser: user_profile = Profile.objects.get(user=request.user) if user_profile.user_type == 'admin': instances = Notification.objects.filter(is_deleted=False, shop=current_shop) else: instances = Notification.objects.filter(is_deleted=False, shop=current_shop, is_cheque=False) else: instances = Notification.objects.filter(is_deleted=False, shop=current_shop, is_cheque=False) title = "Notifications" #filter by query query = request.GET.get("q") if query: title = "Notifications (%s)" % query instances = instances.filter( Q(message__icontains=query) | Q(product__icontains=query)) context = { 'title': title, "instances": instances, } return render(request, 'app/view_notifications.html', context)
def delete_purchase(request, pk): current_shop = get_current_shop(request) instance = get_object_or_404( Purchase.objects.filter(pk=pk, is_deleted=False, shop=current_shop)) purchase_products = PurchaseProduct.objects.filter(purchase=instance, is_deleted=False) #update product stock for purchase_product in purchase_products: stock = Product.objects.get(pk=purchase_product.product.pk, shop=current_shop).stock Product.objects.filter( pk=purchase_product.product.pk, shop=current_shop).update(stock=stock - purchase_product.quantity) #update vendor balance if instance.vendor: balance = Vendor.objects.get(pk=instance.vendor.pk, shop=current_shop, is_deleted=False).balance Vendor.objects.filter(pk=instance.vendor.pk, shop=current_shop, is_deleted=False).update(balance=balance - instance.balance) Purchase.objects.filter(pk=pk, shop=current_shop).update(is_deleted=True) request.session['message'] = 'Successfully Deleted' return HttpResponseRedirect(reverse('purchases:view_purchases'))
def print_sale_report(request): #get current shop current_shop = get_current_shop(request) try: #getting report from session variable report = request.session[str(current_shop)+'_report'] total_report = request.session[str(current_shop)+'_total_report'] #delete session del request.session[str(current_shop)+'_report'] del request.session[str(current_shop)+'_total_report'] except: report = None total_report = None context = { "title" : "Sales Report", 'report' : report, 'total_report' : total_report, } return render(request,'reports/print_sale_report.html',context)
def get_queryset(self): current_shop = get_current_shop(self.request) if not self.request.user.is_authenticated(): return Vendor.objects.none() qs = Vendor.objects.filter(is_deleted=False, shop=current_shop) if self.q: qs = qs.filter(name__istartswith=self.q) return qs
def create_customer(request): if request.method == "POST": form = CustomerForm(request.POST, request=request) if form.is_valid(): #get current shop shop = get_current_shop(request) #create customer_id customer_id = 1 customer_obj = Customer.objects.filter( shop=shop).order_by("-date_added")[:1] if customer_obj: for customer in customer_obj: customer_id = customer.customer_id + 1 #create farmer data = form.save(commit=False) data.creator = request.user data.updater = request.user data.customer_id = customer_id data.shop = shop data.save() request.session['message'] = 'Form Submitted successfully' return HttpResponseRedirect( reverse('customers:view_customer', kwargs={'pk': data.pk})) else: errors = generate_form_errors(form, formset=False) context = { "form": form, "title": "Error", "errors": errors, "customers_active": "active" } return render(request, 'customers/entry_customer.html', context) else: form = CustomerForm() context = { "form": form, "title": "Create Customer", "url": reverse('customers:create_customer'), "redirect": True, "customers_active": "active" } return render(request, 'customers/entry_customer.html', context)
def create_vendor(request): if request.method == "POST": form = VendorForm(request.POST,request=request) current_shop = get_current_shop(request) if form.is_valid(): #generate auto id vendor_id = 1 vendor_obj= Vendor.objects.filter(shop=current_shop).order_by("-date_added")[:1] if vendor_obj: for vendor in vendor_obj: vendor_id = vendor.vendor_id+1 #create vendor data = form.save(commit=False) data.creator = request.user data.updater = request.user data.vendor_id = vendor_id data.shop = current_shop data.save() request.session['message'] = 'Form Submitted Successfully' return HttpResponseRedirect(reverse('vendors:view_vendor',kwargs = {'pk' : data.pk})) else: errors =generate_form_errors(form,formset=False) context = { "form" : form, "title" : "Create Vendor", "errors" : errors, "vendors_active":"active" } return render(request,'vendors/entry_vendor.html',context) else: form = VendorForm() context = { "form" : form, "title" : "Create Vendor", "url" : reverse('vendors:create_vendor'), "vendors_active":"active" } return render(request,'vendors/entry_vendor.html',context)
def view_purchases(request): current_shop = get_current_shop(request) instances = Purchase.objects.filter(is_deleted=False, shop=current_shop) try: message = request.session['message'] del request.session['message'] except KeyError: message = None title = "Purchases" #filter by query query = request.GET.get("q") if query: title = "Purchases (%s)" % query try: date_query = datetime.datetime.strptime(query, "%d-%m-%y") except: date_query = query instances = instances.filter( Q(vendor__name__icontains=query) | Q(purchase_id__icontains=query) | Q(date__icontains=date_query)) #code for page nation starts here paginator = Paginator(instances, 100) page = request.GET.get('page') try: instances = paginator.page(page) except PageNotAnInteger: # If page is not an integer, deliver first page. instances = paginator.page(1) except EmptyPage: # If page is out of range (e.g. 9999), deliver last page of results. instances = paginator.page(paginator.num_pages) context = { 'title': title, "instances": instances, 'query': query, "message": message, "purchases_active": "active" } return render(request, 'purchases/view_purchases.html', context)
def delete_sale(request, pk): current_shop = get_current_shop(request) instance = get_object_or_404( Sale.objects.filter(pk=pk, is_deleted=False, shop=current_shop)) sale_products = SaleProduct.objects.filter(sale=instance, is_deleted=False) #update product stock for sale_product in sale_products: stock = Product.objects.get(pk=sale_product.product.pk, shop=current_shop).stock Product.objects.filter(pk=sale_product.product.pk, shop=current_shop).update(stock=stock + sale_product.quantity) Sale.objects.filter(pk=pk, shop=current_shop).update(is_deleted=True) sale_products.update(is_deleted=True) request.session['message'] = 'Successfully Deleted' return HttpResponseRedirect(reverse('sales:view_sales'))
def clean_name(self): name = self.cleaned_data['name'] request = self.request edit = self.edit #get current shop current_shop = get_current_shop(request) customer = Customer.objects.filter(name=name, shop=current_shop, is_deleted=False) customer_count = customer.count() if edit and customer_count == 1: pass elif customer.exists(): raise forms.ValidationError(_("Customer already Exists")) return self.cleaned_data['name']
def create_cheque(request): if request.method == "POST": form = ChequeForm(request.POST) if form.is_valid(): #get current shop shop = get_current_shop(request) #create cheque data = form.save(commit=False) data.creator = request.user data.updater = request.user data.shop = shop data.save() request.session['message'] = 'Form Submitted successfully' return HttpResponseRedirect(reverse('cheques:view_cheque', kwargs = {'pk' : data.pk})) else: errors =generate_form_errors(form,formset=False) context = { "form" : form, "title" : "Error", "errors":errors, "cheques_active":"active" } return render(request, 'cheques/entry_cheque.html', context) else: form = ChequeForm() context = { "form" : form, "title" : "Create Cheque", "url" : reverse('cheques:create_cheque'), "redirect" : True, "cheques_active":"active" } return render(request, 'cheques/entry_cheque.html', context)
def create_customer_popup(request): if request.method == "POST": #get current shop shop = get_current_shop(request) form = CustomerForm(request.POST, request=request) if form.is_valid(): #create customer_id customer_id = 1 customer_obj = Customer.objects.filter( shop=shop).order_by("-date_added")[:1] if customer_obj: for customer in customer_obj: customer_id = customer.customer_id + 1 #create farmer data = form.save(commit=False) data.creator = request.user data.updater = request.user data.customer_id = customer_id data.shop = shop data.save() response_data = { 'status': 'true', 'message': "Customer %s Created Successfully" % (data.name), } response = HttpResponse(json.dumps(response_data), content_type='application/javascript') return response else: errors = generate_form_errors(form, formset=False) response_data = { 'status': 'false', 'message': errors, } response = HttpResponse(json.dumps(response_data), content_type='application/javascript') return response
def clean_email(self): email = self.request.POST.get('email', None) request = self.request edit = self.edit #get current shop current_shop = get_current_shop(request) if email: customer = Customer.objects.filter(email=email, shop=current_shop, is_deleted=False) customer_count = customer.count() if edit and customer_count == 1: pass elif customer.exists(): raise forms.ValidationError(_("Email already Exists")) return self.cleaned_data['email']
def view_customers(request): current_shop = get_current_shop(request) instances = Customer.objects.filter(shop=current_shop, is_deleted=False) try: message = request.session['message'] del request.session['message'] except KeyError: message = None title = "Customers" #filter by query query = request.GET.get("q") if query: title = "Customers (%s)" % query instances = instances.filter( Q(name__icontains=query) | Q(customer_id__icontains=query) | Q(phone__icontains=query) | Q(email__icontains=query)) #code for page nation starts here paginator = Paginator(instances, 100) page = request.GET.get('page') try: instances = paginator.page(page) except PageNotAnInteger: # If page is not an integer, deliver first page. instances = paginator.page(1) except EmptyPage: # If page is out of range (e.g. 9999), deliver last page of results. instances = paginator.page(paginator.num_pages) context = { 'title': title, "instances": instances, 'query': query, "message": message, "customers_active": "active" } return render(request, 'customers/view_customers.html', context)
def search_result(request): current_shop = get_current_shop(request) product_instances = None vendor_instances = None customer_instances = None title1 = '' title2 = '' title3 = '' instances1 = Product.objects.filter(is_deleted=False, shop=current_shop) instances2 = Vendor.objects.filter(is_deleted=False, shop=current_shop) instances3 = Customer.objects.filter(is_deleted=False, shop=current_shop) query = request.GET.get("q") title = "Search Result- (%s)" % query if query: title1 = "Products (%s)" % query title2 = "Vendors (%s)" % query title3 = "Customers (%s)" % query product_instances = instances1.filter( Q(name__icontains=query) | Q(product_id__icontains=query) | Q(unit_price__icontains=query)) vendor_instances = instances2.filter( Q(name__icontains=query) | Q(phone__icontains=query) | Q(email__icontains=query) | Q(address__icontains=query)) customer_instances = instances3.filter( Q(name__icontains=query) | Q(customer_id__icontains=query) | Q(phone__icontains=query) | Q(email__icontains=query) | Q(details__icontains=query)) context = { "title": title, 'title1': title1, "title2": title2, "title3": title3, "product_instances": product_instances, "vendor_instances": vendor_instances, "customer_instances": customer_instances } return render(request, 'app/search_result.html', context)
def clean_phone(self): phone = self.request.POST.get('phone', None) request = self.request edit = self.edit #get current shop current_shop = get_current_shop(request) if phone: vendor = Vendor.objects.filter(phone=phone, shop=current_shop, is_deleted=False) vendor_count = vendor.count() if edit and vendor_count == 1: pass elif vendor.exists(): raise forms.ValidationError(_("Phone number already Exists")) return self.cleaned_data['phone']
def create_vendor_popup(request): if request.method == "POST": form = VendorForm(request.POST,request=request) current_shop = get_current_shop(request) if form.is_valid(): #generate auto id vendor_id = 1 vendor_obj= Vendor.objects.filter(shop=current_shop).order_by("-date_added")[:1] if vendor_obj: for vendor in vendor_obj: vendor_id = vendor.vendor_id+1 #create vendor data = form.save(commit=False) data.creator = request.user data.updater = request.user data.vendor_id = vendor_id data.shop = current_shop data.save() response_data = { 'status':'true', 'message': "Vendor %s Created Successfully" %(data.name), } response = HttpResponse(json.dumps(response_data), content_type='application/javascript') return response else: errors =generate_form_errors(form,formset=False) response_data = { 'status':'false', 'message': errors, } response = HttpResponse(json.dumps(response_data), content_type='application/javascript') return response
def clean_phone(self): phone = self.request.POST.get('phone', None) request = self.request edit = self.edit print str(phone) + "jaseemm" #get current shop current_shop = get_current_shop(request) if phone: customer = Customer.objects.filter(phone=phone, shop=current_shop, is_deleted=False) customer_count = customer.count() if edit and customer_count == 1: pass elif customer.exists(): raise forms.ValidationError(_("Phone number already Exists")) return self.cleaned_data['phone']
def print_vat(request): #get current shop current_shop = get_current_shop(request) try: sales_report = request.session[str(current_shop)+'_sales_report'] purchases_report = request.session[str(current_shop)+'_purchases_report'] total_sales_tax = request.session[str(current_shop)+'_total_sales_tax'] total_purchases_tax = request.session[str(current_shop)+'_total_purchases_tax'] except: sales_report = None purchases_report = None context = { "title" : "Print VAT", "sales_report": sales_report, "purchases_report":purchases_report, "total_sales_tax":total_sales_tax, "total_purchases_tax":total_purchases_tax, } return render(request,'reports/print_vat.html',context)
def middleware(request): if not request.user.is_superuser: current_shop = get_current_shop(request) if Cheque.objects.filter(is_notified=False, is_deleted=False, shop=current_shop).exists(): instances = Cheque.objects.filter(is_notified=False, is_deleted=False, shop=current_shop) for instance in instances: date = instance.date today = datetime.datetime.now() limit_date = date - today if limit_date <= timedelta(days=4): create_cheque_notification(instance, current_shop) Cheque.objects.filter(is_deleted=False).update( is_notified=True) response = get_response(request) return response
def view_excel_report(request): #get current shop current_shop = get_current_shop(request) from_date = request.GET.get('from_date') to_date = request.GET.get('to_date') #filter by date if from_date and to_date: start_date = datetime.strptime(from_date, '%d-%m-%Y') end_date = datetime.strptime(to_date, '%d-%m-%Y').replace(hour=23,minute=59,second=59,microsecond=999999) elif from_date and not to_date: start_date = datetime.strptime(from_date, '%d-%m-%Y') end_date = datetime.today() elif not from_date and to_date: start_date = datetime.today().replace(day=1,hour=0,minute=0,second=0,microsecond=0) end_date = datetime.strptime(to_date, '%d-%m-%Y').replace(hour=23,minute=59,second=59,microsecond=999999) else: start_date = datetime.today().replace(day=1,hour=0,minute=0,second=0,microsecond=0) end_date = datetime.today() request.session[str(current_shop)+'_start_date'] = datetime.strftime(start_date,'%d-%m-%Y') request.session[str(current_shop)+'_end_date'] = datetime.strftime(end_date,'%d-%m-%Y') context = { "start_date":start_date, "end_date": end_date, "title" : "Excel Report", } return render(request,'reports/view_excel_report.html',context)
def export_xlsx(request): #get current shop current_shop = get_current_shop(request) try: sales_report = request.session[str(current_shop)+'_sales_report'] purchases_report = request.session[str(current_shop)+'_purchases_report'] total_sales_tax = request.session[str(current_shop)+'_total_sales_tax'] total_purchases_tax = request.session[str(current_shop)+'_total_purchases_tax'] except: sales_report = None purchases_report = None buffer = BytesIO() workbook = xlsxwriter.Workbook(buffer) bold_small_pink= workbook.add_format({'font_size':10,'bold': True,'bg_color':'#FFB6F1','font_name':'arial','right':1}) small_arial = workbook.add_format({'font_size':10,'font_name':'arial'}) worksheet = workbook.add_worksheet('Purchases') worksheet.set_column(0,2,15) worksheet.set_column(3,4,25) worksheet.set_column(5,7,15) worksheet.set_row(0,40) worksheet.write(0,0, "Invoice No\n(Mandatory)",bold_small_pink) worksheet.write(0,1, "Invoice Date\n(DD-MM-YYYY)\n(Mandatory)",bold_small_pink) worksheet.write(0,2, "Seller Registration No\n(Mandatory)",bold_small_pink) worksheet.write(0,3, "Seller Dealer Name\n(Mandatory for Unregistered)",bold_small_pink) worksheet.write(0,4, "Seller Dealer Address\n(Mandatory for Unregistered)",bold_small_pink) worksheet.write(0,5, "Value of Goods\n(Mandatory)",bold_small_pink) worksheet.write(0,6, "Vat Amount Paid\n(Mandatory)",bold_small_pink) worksheet.write(0,7, "Cess Amount\n(Mandatory)",bold_small_pink) row = 1 for invoice_no,invoice_date,tin,vendor,address,value_of_goods,vat_amount in purchases_report: worksheet.write(row,0, invoice_no,small_arial) worksheet.write(row,1, invoice_date,small_arial) worksheet.write(row,2, tin,small_arial) worksheet.write(row,3, vendor,small_arial) worksheet.write(row,4, address,small_arial) worksheet.write(row,5, value_of_goods,small_arial) worksheet.write(row,6, vat_amount,small_arial) row = row +1 worksheet1 = workbook.add_worksheet('Sales') worksheet1.set_column(0,2,15) worksheet1.set_column(3,4,25) worksheet1.set_column(5,7,15) worksheet1.set_row(0,40) worksheet1.write(0,0, "Invoice No\n(Mandatory)",bold_small_pink) worksheet1.write(0,1, "Invoice Date\n(DD-MM-YYYY)\n(Mandatory)",bold_small_pink) worksheet1.write(0,2, "Buyer Registration No\n(Mandatory)",bold_small_pink) worksheet1.write(0,3, "Buyer Dealer Name\n(Mandatory for Unregistered)",bold_small_pink) worksheet1.write(0,4, "Buyer Dealer Address\n(Mandatory for Unregistered)",bold_small_pink) worksheet1.write(0,5, "Value of Goods\n(Mandatory)",bold_small_pink) worksheet1.write(0,6, "Vat Amount Paid\n(Mandatory)",bold_small_pink) worksheet1.write(0,7, "Cess Amount\n(Mandatory)",bold_small_pink) row = 1 for invoice_no,invoice_date,tin,customer,address,value_of_goods,vat_amount in sales_report: worksheet1.write(row,0, invoice_no,small_arial) worksheet1.write(row,1, invoice_date,small_arial) worksheet1.write(row,2, tin,small_arial) worksheet1.write(row,3, customer,small_arial) worksheet1.write(row,4, address,small_arial) worksheet1.write(row,5, value_of_goods,small_arial) worksheet1.write(row,6, vat_amount,small_arial) row = row + 1 workbook.close() xlsx = buffer.getvalue() buffer.close() response = HttpResponse(xlsx,content_type='application/ms-excel') response['Content-Disposition'] = 'attachment; filename="'+str(current_shop)+'_purchase_sale.xlsx"' return response
def dashboard(request): if request.user.is_superuser: total_sale = Decimal('0.00') total_purchase = Decimal('0.00') paid_purchase = Decimal('0.00') balance_purchase = Decimal('0.00') total_expense = Decimal('0.00') else: current_shop = get_current_shop(request) user_profile = Profile.objects.get(user=request.user) start_date = datetime.datetime.today().replace(hour=0, minute=0, second=0, microsecond=0) end_date = datetime.datetime.now() #dashboard for sale details sale_list = Sale.objects.filter(is_deleted=False, shop=current_shop, date__range=[start_date, end_date]) if user_profile.tax_only: sale_product_list = SaleProduct.objects.filter( sale__in=sale_list, is_deleted=False, product__untaxed=False) else: sale_product_list = SaleProduct.objects.filter(sale__in=sale_list, is_deleted=False) total_sale = sale_product_list.aggregate( Sum('total_amount'))['total_amount__sum'] total_sale = total_sale if total_sale else Decimal('0.00') #dashboard for purchase details purchase_list = Purchase.objects.filter( is_deleted=False, shop=current_shop, date__range=[start_date, end_date]) if user_profile.tax_only: purchase_product_list = PurchaseProduct.objects.filter( is_deleted=False, purchase__in=purchase_list, product__untaxed=False) else: purchase_product_list = PurchaseProduct.objects.filter( is_deleted=False, purchase__in=purchase_list) total_purchase = purchase_product_list.aggregate( Sum('total_amount'))['total_amount__sum'] total_purchase = total_purchase if total_purchase else Decimal('0.00') paid_purchase = purchase_list.aggregate(Sum('paid'))['paid__sum'] paid_purchase = paid_purchase if paid_purchase else Decimal('0.00') balance_purchase = purchase_list.aggregate( Sum('balance'))['balance__sum'] balance_purchase = balance_purchase if balance_purchase else Decimal( '0.00') expense_list = Expense.objects.filter( is_deleted=False, shop=current_shop, date__range=[start_date, end_date]) total_expense = expense_list.aggregate(Sum('amount'))['amount__sum'] total_expense = total_expense if total_expense else Decimal('0.00') context = { 'title': 'Dashboard', 'total_sale': total_sale, 'total_purchase': total_purchase, 'total_expense': total_expense, 'paid_purchase': paid_purchase, 'balance_purchase': balance_purchase, 'dashboard_active': 'active' } return render(request, 'base.html', context)
def edit_sale(request, pk): current_shop = get_current_shop(request) instance = get_object_or_404(Sale.objects.filter(pk=pk, is_deleted=False)) customer_form = CustomerForm() SaleProductFormset = inlineformset_factory( Sale, SaleProduct, can_delete=True, extra=0, min_num=1, validate_min=True, exclude=('shop', 'sale'), widgets={ 'product': autocomplete.ModelSelect2(url='products:product-autocomplete', attrs={ 'data-placeholder': '*Select Product', 'data-minimum-input-length': 1 }), 'quantity': TextInput(attrs={ 'placeholder': '*Enter quantity', 'class': 'required form-control' }), 'unit_price': TextInput( attrs={ 'placeholder': 'Enter unit price(inc. tax)', 'label': '*Unit price', 'class': 'required form-control' }), 'output_gst': TextInput(attrs={ 'placeholder': 'Output GST', 'class': 'required form-control' }), 'offer': TextInput(attrs={ 'placeholder': 'Enter Discount', 'class': 'required form-control' }), 'amount': TextInput(attrs={ 'placeholder': 'Enter amount', 'class': 'required form-control' }), 'tax_amount': TextInput(attrs={ 'placeholder': 'Tax amount', 'class': 'required form-control' }), 'total_amount': TextInput( attrs={ 'placeholder': 'Enter total amount', 'class': 'required form-control' }), }, error_messages={ 'product': { 'required': _("Product field is required."), }, 'unit_price': { 'required': _("Unit Price field is required."), }, 'quantity': { 'required': _("Quantity field is required."), }, }) if request.method == "POST": form = SaleForm(request.POST, instance=instance) sale_product_formset = SaleProductFormset( request.POST, prefix='sale_product_formset', instance=instance) error_messages = '' if form.is_valid() and sale_product_formset.is_valid(): #check product availability for f in sale_product_formset: product = f.cleaned_data['product'] quantity = f.cleaned_data['quantity'] # previous sale quantity taken if SaleProduct.objects.filter(pk=f.instance.pk).exists(): saleform = SaleProduct.objects.get(pk=f.instance.pk) pre_product = saleform.product pre_quantity = saleform.quantity if pre_product == product: available_stock = Product.objects.get( shop=current_shop, pk=product.pk).stock new_stock = available_stock + pre_quantity if new_stock < quantity: error_messages += "%s is out of stock. Only %s unit(s) exists. </br >" % ( product, available_stock) else: available_stock = Product.objects.get( shop=current_shop, pk=product.pk).stock if available_stock < quantity: error_messages += "%s is out of stock. Only %s unit(s) exists. </br >" % ( product, available_stock) else: available_stock = 0 #check product availability if product: available_stock = Product.objects.get( shop=current_shop, pk=product.pk).stock if available_stock < quantity: error_messages += "%s is out of stock. Only %s unit(s) exists. </br >" % ( product, available_stock) if not error_messages: #update item data = form.save(commit=False) data.updater = request.user data.date_updated = datetime.datetime.now() data.save() if sale_product_formset.deleted_forms: for deleted_form in sale_product_formset.deleted_forms: new_stock = deleted_form.instance.product.stock + deleted_form.instance.quantity Product.objects.filter( pk=deleted_form.instance.product.pk, is_deleted=False).update(stock=new_stock) #save SaleProduct for form in sale_product_formset: product = form.cleaned_data['product'] unit_price = form.cleaned_data['unit_price'] quantity = form.cleaned_data['quantity'] offer = form.cleaned_data['offer'] output_gst = form.cleaned_data['output_gst'] tax_amount = form.cleaned_data['tax_amount'] amount = form.cleaned_data['amount'] # previous sale quantity taken if SaleProduct.objects.filter( pk=form.instance.pk).exists(): saleform = SaleProduct.objects.get(pk=form.instance.pk) pre_product = saleform.product pre_quantity = saleform.quantity if pre_product == product: current_stock = Product.objects.get( pk=product.pk, shop=current_shop).stock Product.objects.filter(pk=product.pk, shop=current_shop).update( stock=current_stock + pre_quantity - quantity) else: current_stock = pre_product.stock Product.objects.filter( pk=pre_product.pk, shop=current_shop).update(stock=current_stock + pre_quantity) current_product_stock = product.stock product.stock = current_product_stock - quantity product.save() else: current_stock = Product.objects.get( pk=product.pk, shop=current_shop).stock Product.objects.filter( pk=product.pk, shop=current_shop).update(stock=current_stock - quantity) #update sale product fomset forms sale_product_formset.save() request.session['message'] = 'Form Submitted successfully' return HttpResponseRedirect( reverse('sales:view_sale', kwargs={'pk': data.pk})) else: context = { "form": form, "customer_form": customer_form, "title": "Edit Sale : " + instance.sale_id, "sale_product_formset": sale_product_formset, "errors": error_messages, "sales_active": "active" } return render(request, 'sales/entry_sale.html', context) else: error_messages = generate_form_errors(form, formset=False) error_messages += generate_form_errors(sale_product_formset, formset=True) context = { "form": form, "customer_form": customer_form, "title": "Edit Sale : " + instance.sale_id, "sale_product_formset": sale_product_formset, "errors": error_messages, "sales_active": "active" } return render(request, 'sales/entry_sale.html', context) else: form = SaleForm(instance=instance) sale_product_formset = SaleProductFormset( prefix='sale_product_formset', instance=instance) for field in sale_product_formset: field.fields['product'].queryset = Product.objects.filter( shop=current_shop, is_deleted=False) context = { "form": form, "customer_form": customer_form, "sale_product_formset": sale_product_formset, "title": "Edit Sale : " + instance.sale_id, "url": reverse('sales:edit_sale', kwargs={'pk': instance.pk}), "sales_active": "active" } return render(request, 'sales/entry_sale.html', context)
def create_sale(request): SaleProductFormset = formset_factory(SaleProductForm, min_num=1, validate_min=True, extra=0) current_shop = get_current_shop(request) customer_form = CustomerForm() if request.method == "POST": form = SaleForm(request.POST) sale_product_formset = SaleProductFormset( request.POST, prefix='sale_product_formset') for field in sale_product_formset: field.fields['product'].queryset = Product.objects.filter( shop=current_shop, is_deleted=False) if form.is_valid() and sale_product_formset.is_valid(): sale_id = 'OFF/1' sale_obj = Sale.objects.filter( shop=current_shop).order_by("-date_added")[:1] if sale_obj: for sale in sale_obj: sale_id = 'OFF/' + str( int(re.findall(r'\d+', sale.sale_id)[0]) + 1) error_messages = '' #check product availability for f in sale_product_formset: product = f.cleaned_data['product'] quantity = f.cleaned_data['quantity'] available_stock = 0 #check product availability if Product.objects.filter(shop=current_shop, pk=product.pk).exists(): available_stock = Product.objects.get(shop=current_shop, pk=product.pk).stock if available_stock < quantity: error_messages += "%s is out of stock. Only %s unit(s) exists in %s . </br >" % ( product, available_stock, current_shop) if not error_messages: customer = form.cleaned_data['customer'] #create sale data = form.save(commit=False) data.creator = request.user data.updater = request.user data.sale_id = sale_id data.shop = current_shop data.date_added = datetime.datetime.now() data.save() #save SaleProduct for form in sale_product_formset: product = form.cleaned_data['product'] unit_price = form.cleaned_data['unit_price'] quantity = form.cleaned_data['quantity'] offer = form.cleaned_data['offer'] output_gst = form.cleaned_data['output_gst'] tax_amount = form.cleaned_data['tax_amount'] amount = form.cleaned_data['amount'] total_amount = form.cleaned_data['total_amount'] SaleProduct(product=product, sale=data, unit_price=unit_price, quantity=quantity, output_gst=output_gst, offer=offer, tax_amount=tax_amount, amount=amount, total_amount=total_amount).save() current_stock = Product.objects.get( pk=product.pk, shop=current_shop).stock updated_stock = current_stock - quantity Product.objects.filter( pk=product.pk, shop=current_shop).update(stock=updated_stock) if updated_stock == 0: create_notification(product, current_shop) request.session['message'] = 'Form Submitted successfully' return HttpResponseRedirect( reverse('sales:view_sale', kwargs={'pk': data.pk})) else: context = { "form": form, "customer_form": customer_form, "title": "Create Sale", "sale_product_formset": sale_product_formset, "errors": error_messages, "sales_active": "active" } return render(request, 'sales/entry_sale.html', context) else: errors = generate_form_errors(form, formset=False) errors += generate_form_errors(sale_product_formset, formset=True) context = { "form": form, "customer_form": customer_form, "title": "Create Sale", "sale_product_formset": sale_product_formset, "errors": errors, "sales_active": "active" } return render(request, 'sales/entry_sale.html', context) else: form = SaleForm() sale_product_formset = SaleProductFormset( prefix='sale_product_formset') context = { "form": form, "customer_form": customer_form, "title": "Create Sale", "sale_product_formset": sale_product_formset, "url": reverse('sales:create_sale'), "sales_active": "active" } return render(request, 'sales/entry_sale.html', context)
def view_sale_report(request): #get current shop current_shop = get_current_shop(request) user_profile = Profile.objects.get(user=request.user) from_date = request.GET.get('from_date',False) to_date = request.GET.get('to_date',False) #filter by date if from_date and to_date: start_date = datetime.strptime(from_date, '%d-%m-%Y') end_date = datetime.strptime(to_date, '%d-%m-%Y').replace(hour=23,minute=59,second=59,microsecond=999999) elif from_date and not to_date: start_date = datetime.strptime(from_date, '%d-%m-%Y') end_date = datetime.today() elif not from_date and to_date: start_date = datetime.today().replace(day=1,hour=0,minute=0,second=0,microsecond=0) end_date = datetime.strptime(to_date, '%d-%m-%Y').replace(hour=23,minute=59,second=59,microsecond=999999) else: start_date = datetime.today().replace(day=1,hour=0,minute=0,second=0,microsecond=0) end_date = datetime.today() day_count = (end_date-start_date).days + 1 total_sales = Sale.objects.filter(shop=current_shop,is_deleted=False,date__range=[start_date,end_date]) if user_profile.tax_only: total_income = SaleProduct.objects.filter(sale__in=total_sales,is_deleted=False,product__untaxed=False).aggregate(Sum('total_amount'))['total_amount__sum'] else: total_income = SaleProduct.objects.filter(sale__in=total_sales,is_deleted=False).aggregate(Sum('total_amount'))['total_amount__sum'] total_income = total_income if total_income else 0.00 report =[] for date in (start_date+timedelta(days=days) for days in range(day_count)): day_sales = Sale.objects.filter(shop=current_shop,is_deleted=False,date=date) if user_profile.tax_only: income = SaleProduct.objects.filter(sale__in=day_sales,is_deleted=False,product__untaxed=False).aggregate(Sum('total_amount'))['total_amount__sum'] else: income = SaleProduct.objects.filter(sale__in=day_sales,is_deleted=False).aggregate(Sum('total_amount'))['total_amount__sum'] income = income if income else '0.00' report.append([datetime.strftime(date,'%d-%m-%Y'),str(income)]) request.session[str(current_shop)+'_report'] = report request.session[str(current_shop)+'_total_report'] = [str(total_income)] total_expense = Expense.objects.filter(shop=current_shop,is_deleted=False,date__range=[start_date,end_date]).aggregate(Sum('amount'))['amount__sum'] total_expense = total_expense if total_expense else 0.00 request.session[str(current_shop)+'_total_expense'] = [str(total_expense)] context = { 'title' : "Sales Report", 'report' : report, 'start_date':start_date, 'end_date': end_date, 'total_income':total_income, 'total_expense' : total_expense, "reports_active":"active" } return render(request,'reports/view_sale_report.html',context)
def view_vat(request): #get current shop current_shop = get_current_shop(request) from_date = request.GET.get('from_date') to_date = request.GET.get('to_date') #filter by date if from_date and to_date: start_date = datetime.strptime(from_date, '%d-%m-%Y') end_date = datetime.strptime(to_date, '%d-%m-%Y').replace(hour=23,minute=59,second=59,microsecond=999999) elif from_date and not to_date: start_date = datetime.strptime(from_date, '%d-%m-%Y') end_date = datetime.today() elif not from_date and to_date: start_date = datetime.today().replace(day=1,hour=0,minute=0,second=0,microsecond=0) end_date = datetime.strptime(to_date, '%d-%m-%Y').replace(hour=23,minute=59,second=59,microsecond=999999) else: start_date = datetime.today().replace(day=1,hour=0,minute=0,second=0,microsecond=0) end_date = datetime.today() sales = Sale.objects.filter(shop=current_shop,is_deleted=False,date__range=[start_date,end_date]).order_by('date_added') sales_report =[] total_sales_tax = Decimal('0.00') for sale in sales: products = SaleProduct.objects.filter(sale=sale,is_deleted=False,product__untaxed=False) total_sale_tax = products.aggregate(Sum('tax_amount'))['tax_amount__sum'] total_untaxed_amount = products.aggregate(Sum('amount'))['amount__sum'] if sale.customer: tin = sale.customer.tin customer_name = sale.customer.name customer_address = sale.customer.details else: tin = None customer_name = None customer_address = None if products.exists(): sale_report = [sale.sale_id,datetime.strftime(sale.date,'%d-%m-%Y'),tin,customer_name,customer_address,str(round(total_untaxed_amount, 2)),str(round(total_sale_tax, 2))] total_sales_tax = total_sales_tax + total_sale_tax if sales.exists() and products.exists(): sales_report.append(sale_report) purchases = Purchase.objects.filter(shop=current_shop,is_deleted=False,date__range=[start_date,end_date]).order_by('date_added') purchases_report =[] total_purchases_tax = Decimal('0.00') for purchase in purchases: products = PurchaseProduct.objects.filter(purchase=purchase,is_deleted=False,product__untaxed=False) total_purchase_tax = products.aggregate(Sum('tax_amount'))['tax_amount__sum'] total_untaxed_amount = products.aggregate(Sum('amount'))['amount__sum'] if purchase.vendor: tin = purchase.vendor.tin vendor_name = purchase.vendor.name vendor_address = purchase.vendor.address else: tin = None vendor_name = None vendor_address = None if products.exists(): purchase_report = [purchase.purchase_id,datetime.strftime(purchase.date,'%d-%m-%Y'),tin,vendor_name,vendor_address,str(round(total_untaxed_amount, 2)),str(round(total_purchase_tax, 2))] total_purchases_tax = total_purchases_tax + total_purchase_tax if purchases.exists() and products.exists(): purchases_report.append(purchase_report) request.session[str(current_shop)+'_sales_report'] = sales_report request.session[str(current_shop)+'_total_sales_tax'] = str(round(total_sales_tax,2)) request.session[str(current_shop)+'_purchases_report'] = purchases_report request.session[str(current_shop)+'_total_purchases_tax'] = str(round(total_purchases_tax,2)) context = { "sales_report": sales_report, "total_sales_tax":total_sales_tax, "purchases_report":purchases_report, "total_purchases_tax":total_purchases_tax, "start_date":start_date, "end_date": end_date, "title" : "VAT Report", "reports_active":"active" } return render(request,'reports/view_vat.html',context)
def create_purchase(request): PurchaseProductFormset = formset_factory(PurchaseProductForm, min_num=1, validate_min=True, extra=0) current_shop = get_current_shop(request) vendor_form = VendorForm() if request.method == "POST": form = PurchaseForm(request.POST) purchase_product_formset = PurchaseProductFormset( request.POST, prefix='purchase_product_formset') for field in purchase_product_formset: field.fields['product'].queryset = Product.objects.filter( shop=current_shop, is_deleted=False) if form.is_valid() and purchase_product_formset.is_valid(): purchase_id = 'P1' purchase_obj = Purchase.objects.filter( shop=current_shop).order_by("-date_added")[:1] if purchase_obj: for purchase in purchase_obj: purchase_id = 'P' + str( int(re.findall(r'\d+', purchase.purchase_id)[0]) + 1) balance = form.cleaned_data['balance'] vendor = form.cleaned_data['vendor'] #create purchase data = form.save(commit=False) data.creator = request.user data.updater = request.user data.purchase_id = purchase_id data.shop = current_shop data.date_added = datetime.datetime.now() data.save() #save PurchaseProduct for form in purchase_product_formset: product = form.cleaned_data['product'] unit_cost = form.cleaned_data['unit_cost'] input_gst = form.cleaned_data['input_gst'] quantity = form.cleaned_data['quantity'] offer = form.cleaned_data['offer'] tax_amount = form.cleaned_data['tax_amount'] amount = form.cleaned_data['amount'] total_amount = form.cleaned_data['total_amount'] PurchaseProduct(product=product, purchase=data, unit_cost=unit_cost, input_gst=input_gst, quantity=quantity, offer=offer, tax_amount=tax_amount, amount=amount, total_amount=total_amount).save() if Product.objects.filter(pk=product.pk, shop=current_shop).exists(): current_stock = Product.objects.get( pk=product.pk, shop=current_shop).stock Product.objects.filter(pk=product.pk, shop=current_shop).update( stock=quantity + current_stock) #update balance of vendor if vendor: current_balance = Vendor.objects.get(pk=vendor.pk, shop=current_shop, is_deleted=False).balance Vendor.objects.filter( pk=vendor.pk, shop=current_shop, is_deleted=False).update(balance=current_balance + balance) request.session['message'] = 'Form Submitted successfully' return HttpResponseRedirect( reverse('purchases:view_purchase', kwargs={'pk': data.pk})) else: errors = generate_form_errors(form, formset=False) errors += generate_form_errors(purchase_product_formset, formset=True) context = { "form": form, "vendor_form": vendor_form, "title": "Create Purchase", "purchase_product_formset": purchase_product_formset, "errors": errors, "purchases_active": "active" } return render(request, 'purchases/entry_purchase.html', context) else: form = PurchaseForm() purchase_product_formset = PurchaseProductFormset( prefix='purchase_product_formset') for field in purchase_product_formset: field.fields['product'].queryset = Product.objects.filter( shop=current_shop, is_deleted=False) context = { "form": form, "vendor_form": vendor_form, "purchase_product_formset": purchase_product_formset, "title": "Create Purchase", "url": reverse('purchases:create_purchase'), "purchases_active": "active" } return render(request, 'purchases/entry_purchase.html', context)
def edit_purchase(request, pk): current_shop = get_current_shop(request) purchase_instance = get_object_or_404( Purchase.objects.filter(pk=pk, is_deleted=False, shop=current_shop)) vendor_form = VendorForm() PurchaseProductFormset = inlineformset_factory( Purchase, PurchaseProduct, can_delete=True, extra=0, min_num=1, validate_min=True, exclude=('shop', 'purchase'), widgets={ 'product': autocomplete.ModelSelect2(url='products:product-autocomplete', attrs={ 'data-placeholder': 'Select Product', 'data-minimum-input-length': 1 }), 'quantity': TextInput(attrs={ 'placeholder': 'Enter quantity', 'class': 'required form-control' }), 'unit_cost': TextInput( attrs={ 'placeholder': 'Enter unit cost(inc. tax)', 'label': 'Unit cost', 'class': 'required form-control' }), 'input_gst': TextInput(attrs={ 'placeholder': 'Input Tax', 'class': 'required form-control' }), 'offer': TextInput(attrs={ 'placeholder': 'Enter offer', 'class': 'required form-control' }), 'amount': TextInput(attrs={ 'placeholder': 'Enter amount', 'class': 'required form-control' }), 'tax_amount': TextInput(attrs={ 'placeholder': 'Tax amount', 'class': 'required form-control' }), 'total_amount': TextInput( attrs={ 'placeholder': 'Enter total amount', 'class': 'required form-control' }), }, error_messages={ 'product': { 'required': _("product field is required."), }, 'unit_cost': { 'required': _("Unit Cost field is required."), }, 'quantity': { 'required': _("Quantity field is required."), }, }) if request.method == "POST": response_data = {} form = PurchaseForm(request.POST, instance=purchase_instance) purchase_product_formset = PurchaseProductFormset( request.POST, prefix='purchase_product_formset', instance=purchase_instance) error = '' # checking quantity and stock of deleted product to ensure quantity is less than stock if purchase_product_formset.deleted_forms: for deleted_form in purchase_product_formset.deleted_forms: if deleted_form.instance.product.stock <= deleted_form.instance.quantity: error = "Invalid operation, Product can not removed because of incorrect quantity" #tacking vendor and vendor balance in variables vendor_balance = purchase_instance.balance vendor = purchase_instance.vendor if form.is_valid() and purchase_product_formset.is_valid(): #remove previous Purchase Product purchase_product = PurchaseProduct.objects.filter( purchase=purchase_instance.pk, is_deleted=False) for purchase_product_form in purchase_product_formset: new_product = purchase_product_form.cleaned_data['product'] new_quantity = purchase_product_form.cleaned_data['quantity'] new_unit_cost = purchase_product_form.cleaned_data['unit_cost'] new_input_gst = purchase_product_form.cleaned_data['input_gst'] new_offer = purchase_product_form.cleaned_data['offer'] new_amount = purchase_product_form.cleaned_data['amount'] pre_quantity = 0 pre_product = None if PurchaseProduct.objects.filter( pk=purchase_product_form.instance.pk).exists(): purchaseform = PurchaseProduct.objects.get( pk=purchase_product_form.instance.pk) pre_quantity = purchaseform.quantity pre_purchase_product = PurchaseProduct.objects.get( pk=purchase_product_form.instance.pk) pre_product = pre_purchase_product.product current_stock = new_product.stock new_stock = 0 if pre_product == new_product: new_stock = current_stock - pre_quantity + new_quantity else: if pre_product: pre_product_stock_defference = pre_product.stock - pre_purchase_product.quantity if pre_product_stock_defference < 0: error += "You can not change product because you already sold some unit %s belong to this purchase" % pre_product.name if new_quantity < 0: error += "Can't change the quantity as you entered. Please re enter the quantity of %s" % ( new_product) if new_stock < 0: error += "You can not change product because you already sold some unit %s belong to this purchase" % pre_product.name if not error: #take edited vendor and balance edited_balance = form.cleaned_data['balance'] edited_vendor = form.cleaned_data['vendor'] #removing deleted form from formset group and reducing count of product in stock count if purchase_product_formset.deleted_forms: for deleted_form in purchase_product_formset.deleted_forms: if deleted_form.instance.product.stock >= deleted_form.instance.quantity: new_stock = deleted_form.instance.product.stock - deleted_form.instance.quantity Product.objects.filter( pk=deleted_form.instance.product.pk, is_deleted=False).update(stock=new_stock) #update item data = form.save(commit=False) data.updater = request.user data.date_updated = datetime.datetime.now() data.save() #update PurchaseProduct for form in purchase_product_formset: product = form.cleaned_data['product'] unit_cost = form.cleaned_data['unit_cost'] input_gst = form.cleaned_data['input_gst'] quantity = form.cleaned_data['quantity'] offer = form.cleaned_data['offer'] amount = form.cleaned_data['amount'] #update product stock pre_quantity = 0 pre_product = None if PurchaseProduct.objects.filter( pk=form.instance.pk).exists(): purchaseform_instance = PurchaseProduct.objects.get( pk=form.instance.pk) pre_quantity = purchaseform_instance.quantity pre_purchase_product = purchaseform_instance pre_product = pre_purchase_product.product """update product stock if previous product is current product or previous product""" if pre_product == product: new_stock = pre_product.stock - pre_quantity + quantity Product.objects.filter( pk=product.pk, shop=current_shop).update(stock=new_stock) elif pre_product: #reducing entire quantity from product prev_product_stock = pre_product.stock - pre_quantity #updating previous product stock Product.objects.filter( pk=pre_product.pk, shop=current_shop).update(stock=prev_product_stock) #adding newly entered quantity to product stock new_stock = product.stock + quantity #updating new stock Product.objects.filter( pk=product.pk, shop=current_shop).update(stock=new_stock) """if there is no previous product then modifing product stock""" else: #adding new quantity to stock new_stock = product.stock + quantity #updating new quantity Product.objects.filter( pk=product.pk, shop=current_shop).update(stock=new_stock) #update purchase product fomset forms purchase_product_formset.save() #update vendor balance if vendor == edited_vendor: if vendor: current_balance = Vendor.objects.get( pk=vendor.pk, shop=current_shop).balance Vendor.objects.filter( pk=vendor.pk, shop=current_shop).update(balance=current_balance - vendor_balance + edited_balance) else: #update new vendor balance if edited_vendor: current_balance = Vendor.objects.get( pk=edited_vendor.pk, shop=current_shop).balance Vendor.objects.filter( pk=edited_vendor.pk, shop=current_shop).update(balance=current_balance + edited_balance) #remove previous vendor balance if vendor: pre_balance = Vendor.objects.get( pk=vendor.pk, shop=current_shop).balance Vendor.objects.filter( pk=vendor.pk, shop=current_shop).update(balance=pre_balance - vendor_balance) else: context = { "form": form, "vendor_form": vendor_form, "errors": error, "purchase_product_formset": purchase_product_formset, "title": "Edit Purchase : " + purchase_instance.purchase_id, "purchase_active": "active" } return render(request, 'purchases/entry_purchase.html', context) request.session['message'] = 'Form Submitted successfully' return HttpResponseRedirect( reverse('purchases:view_purchase', kwargs={'pk': data.pk})) else: errors = generate_form_errors(form, formset=False) errors += generate_form_errors(purchase_product_formset, formset=True) context = { "form": form, "vendor_form": vendor_form, "errors": errors, "purchase_product_formset": purchase_product_formset, "title": "Edit Purchase : " + purchase_instance.purchase_id, "purchase_active": "active" } return render(request, 'purchases/entry_purchase.html', context) else: form = PurchaseForm(instance=purchase_instance) purchase_product_formset = PurchaseProductFormset( prefix='purchase_product_formset', instance=purchase_instance) for field in purchase_product_formset: field.fields['product'].queryset = Product.objects.filter( shop=current_shop, is_deleted=False) context = { "form": form, "vendor_form": vendor_form, "purchase_product_formset": purchase_product_formset, "title": "Edit Purchase : " + purchase_instance.purchase_id, "url": reverse('purchases:edit_purchase', kwargs={'pk': purchase_instance.pk}), "purchases_active": "active" } return render(request, 'purchases/entry_purchase.html', context)
def export_excel(request): #get current shop current_shop = get_current_shop(request) start_date = request.session[str(current_shop)+'_start_date'] end_date = request.session[str(current_shop)+'_end_date'] start_date_str = datetime.strptime(start_date,'%d-%m-%Y') end_date_str = datetime.strptime(end_date,'%d-%m-%Y') buffer = BytesIO() workbook = xlsxwriter.Workbook(buffer) worksheet = workbook.add_worksheet('Purchases') format = workbook.add_format() bold = workbook.add_format({'bold': True,'bg_color': '#5BCF64'}) center = workbook.add_format() center.set_align('center') center.set_align('vcenter') format.set_bg_color('#6FACFC') format.set_align('center') format.set_align('vcenter') format.set_border(2) worksheet.set_row(0, 25) worksheet.set_column('A:S', 10) worksheet.write(0,0, 'Invoice No',format) worksheet.write(0,1, 'Invoice Date',format) worksheet.write(0,2, 'Vendor',format) worksheet.write(0,3, 'GSTIN',format) worksheet.write(0,4, 'State',format) worksheet.write(0,5, 'State Code',format) worksheet.write(0,6, 'Product',format) worksheet.write(0,7, 'HSN code',format) worksheet.write(0,8, 'Unit Cost',format) worksheet.write(0,9, 'Quantity',format) worksheet.write(0,10, 'Discount',format) worksheet.write(0,11, 'Taxable Value',format) worksheet.write(0,12, 'CGST%',format) worksheet.write(0,13, 'CGST',format) worksheet.write(0,14, 'SGST%',format) worksheet.write(0,15, 'SGST',format) worksheet.write(0,16, 'Totoal GST',format) worksheet.write(0,17, 'Grand Total',format) row = 1 purchases = Purchase.objects.filter(is_deleted=False,date__range=[start_date_str,end_date_str]).order_by('purchase_id') starting_row = 1 merged_row = 0 for purchase in purchases: purchase_products = PurchaseProduct.objects.filter(is_deleted=False,purchase=purchase) total_gst = PurchaseProduct.objects.filter(is_deleted=False,purchase=purchase).aggregate(Sum('tax_amount'))['tax_amount__sum'] total_net_amount = PurchaseProduct.objects.filter(is_deleted=False,purchase=purchase).aggregate(Sum('amount'))['amount__sum'] final_total = PurchaseProduct.objects.filter(is_deleted=False,purchase=purchase).aggregate(Sum('total_amount'))['total_amount__sum'] row_count = purchase_products.count() for purchase_product in purchase_products: product = purchase_product.product unit_cost = purchase_product.unit_cost cgst = purchase_product.input_gst/2 cgst_amount = purchase_product.tax_amount/2 quantity = purchase_product.quantity tax_amount = purchase_product.tax_amount amount = purchase_product.amount offer = purchase_product.offer total_amount = purchase_product.total_amount worksheet.write(row,6, product.name) worksheet.write(row,7, product.hsn_code) worksheet.write(row,8, unit_cost) worksheet.write(row,9, quantity) worksheet.write(row,10, offer) worksheet.write(row,11, amount) worksheet.write(row+1,11, total_net_amount ,bold) worksheet.write(row,12, cgst) worksheet.write(row,13, cgst_amount) worksheet.write(row,14, cgst) worksheet.write(row,15, cgst_amount) worksheet.write(row,16, tax_amount) worksheet.write(row+1,16, total_gst , bold) worksheet.write(row,17, total_amount) worksheet.write(row+1,17, final_total , bold) row = row +1 merged_row = merged_row + row_count +1 worksheet.merge_range(starting_row,0,merged_row,0, purchase.purchase_id,center) worksheet.merge_range(starting_row,1,merged_row,1, datetime.strftime(purchase.date,'%d-%m-%Y'),center) if purchase.vendor: worksheet.merge_range(starting_row,2,merged_row,2, purchase.vendor.name,center) worksheet.merge_range(starting_row,3,merged_row,3, purchase.vendor.gstin,center) worksheet.merge_range(starting_row,4,merged_row,4, purchase.vendor.state,center) worksheet.merge_range(starting_row,5,merged_row,5, purchase.vendor.get_state_display(),center) else: worksheet.merge_range(starting_row,2,merged_row,2, "") worksheet.merge_range(starting_row,3,merged_row,3, "") worksheet.merge_range(starting_row,4,merged_row,4, "") worksheet.merge_range(starting_row,5,merged_row,5, "") starting_row =merged_row + 1 row = row +1 worksheet1 = workbook.add_worksheet('Sales') worksheet1.set_row(0, 25) worksheet1.set_column('A:S', 10) worksheet1.write(0,0, 'Sale Id',format) worksheet1.write(0,1, 'Sale Date',format) worksheet1.write(0,2, 'Customer',format) worksheet1.write(0,3, 'Details',format) worksheet1.write(0,4, 'GSTIN',format) worksheet1.write(0,5, 'State',format) worksheet1.write(0,6, 'State Code',format) worksheet1.write(0,7, 'Product',format) worksheet1.write(0,8, 'HSN code',format) worksheet1.write(0,9, 'Unit Price',format) worksheet1.write(0,10, 'Quantity',format) worksheet1.write(0,11, 'Discount',format) worksheet1.write(0,12, 'Taxable Value',format) worksheet1.write(0,13, 'CGST%',format) worksheet1.write(0,14, 'CGST',format) worksheet1.write(0,15, 'SGST%',format) worksheet1.write(0,16, 'SGST',format) worksheet1.write(0,17, 'Total GST',format) worksheet1.write(0,18, 'Grant Total',format) row = 1 starting_row = 1 merged_row = 0 sales = Sale.objects.filter(is_deleted=False,date__range=[start_date_str,end_date_str]).order_by('sale_id') for sale in sales: sale_products = SaleProduct.objects.filter(is_deleted=False,sale=sale) total_gst = SaleProduct.objects.filter(is_deleted=False,sale=sale).aggregate(Sum('tax_amount'))['tax_amount__sum'] total = SaleProduct.objects.filter(is_deleted=False,sale=sale).aggregate(Sum('total_amount'))['total_amount__sum'] total_net_amount = SaleProduct.objects.filter(is_deleted=False,sale=sale).aggregate(Sum('amount'))['amount__sum'] row_count = sale_products.count() for sale_product in sale_products: product = sale_product.product unit_price = sale_product.unit_price output_gst = sale_product.output_gst quantity = sale_product.quantity tax_amount = sale_product.tax_amount amount = sale_product.amount offer = sale_product.offer cgst = sale_product.output_gst/2 cgst_amount = sale_product.tax_amount/2 total_amount = sale_product.total_amount worksheet1.write(row,7, product.name) worksheet1.write(row,8, product.hsn_code) worksheet1.write(row,9, unit_price) worksheet1.write(row,10, quantity) worksheet1.write(row,11, offer) worksheet1.write(row,12, amount) worksheet1.write(row+1,12, total_net_amount , bold) worksheet1.write(row,13, cgst) worksheet1.write(row,14, cgst_amount) worksheet1.write(row,15, cgst) worksheet1.write(row,16, cgst_amount) worksheet1.write(row,17, tax_amount) worksheet1.write(row+1,17, total_gst , bold) worksheet1.write(row,18, total_amount) worksheet1.write(row+1,18, total, bold) row = row +1 merged_row = merged_row + row_count +1 worksheet1.merge_range(starting_row,0,merged_row,0, sale.sale_id,center) worksheet1.merge_range(starting_row,1,merged_row,1, datetime.strftime(sale.date,'%d-%m-%Y'),center) if sale.customer: worksheet1.merge_range(starting_row,2,merged_row,2, sale.customer.name,center) worksheet1.merge_range(starting_row,3,merged_row,3, sale.customer.details,center) worksheet1.merge_range(starting_row,4,merged_row,4, sale.customer.gstin,center) worksheet1.merge_range(starting_row,5,merged_row,5, sale.customer.state,center) worksheet1.merge_range(starting_row,6,merged_row,6, sale.customer.get_state_display(),center) else: worksheet1.merge_range(starting_row,2,merged_row,2, "") worksheet1.merge_range(starting_row,3,merged_row,3, "") worksheet1.merge_range(starting_row,4,merged_row,4, "") worksheet1.merge_range(starting_row,5,merged_row,5, "") worksheet1.merge_range(starting_row,6,merged_row,6, "") starting_row =merged_row + 1 row = row +1 workbook.close() xlsx = buffer.getvalue() buffer.close() response = HttpResponse(xlsx,content_type='application/ms-excel') response['Content-Disposition'] = 'attachment; filename="'+str(current_shop)+'_purchase_sale.xlsx"' return response