models.py
from django.db import models
from datetime import date
class RocCompany(models.Model):
company_name = models.TextField(db_column='CompanyName', blank=True, null=True) # Field name made lowercase.
company_cin = models.CharField(db_column='CIN', primary_key=True, max_length=50) # Field name made lowercase.
incorporation = models.DateField(db_column='Incorporation', blank=True, null=True) # Field name made lowercase.
state = models.CharField(max_length=50,db_column='State', blank=True, null=True) # Field name made lowercase.
city = models.CharField(max_length=50,db_column='City', blank=True, null=True) # Field name made lowercase.
industry = models.CharField(max_length=50,db_column='Industry', blank=True, null=True) # Field name made lowercase.
sector = models.CharField(max_length=50,db_column='Sector', blank=True, null=True) # Field name made lowercase.
class Meta:
managed = False
db_table = 'cin_format'
ordering=('company_name','state','city','sector',)# needed for sorting
def __str__(self):
return self.company_cin #bcz nedded for filter filer cin mixup
from django.db import models
EXISTING_OLD_CHOICES = [
('existing', 'Existing'),
('old', 'Old'),
]
class RocCharge(models.Model):
id = models.IntegerField(primary_key=True)
srn_no = models.CharField(max_length=50,blank=True, null=True)
charge_id = models.IntegerField(blank=True, null=True)
charge_holder_name = models.CharField(max_length=50,blank=True, null=True,db_index=True)
date_of_creation = models.DateField(default=date.today)
date_of_modification = models.DateField(blank=True, null=True)
date_of_satisfaction = models.DateField(blank=True, null=True)
amount = models.BigIntegerField(blank=True, null=True)
address = models.TextField(blank=True, null=True)
existing_old = models.CharField(
max_length=8,
choices=EXISTING_OLD_CHOICES, # Use the choices parameter
)
cin = models.ForeignKey(RocCompany, on_delete=models.CASCADE, db_column='CIN', blank=True, null=True) # Field name made lowercase.
class Meta:
managed = False
db_table = 'roc_web'
ordering=('charge_holder_name','date_of_creation','amount',)
def __str__(self):
return self.charge_holder_name
,
my filters.py:
from bootstrap_datepicker_plus.widgets import DateTimePickerInput
import django_filters
from .models import RocCharge, RocCompany
from django import forms
# You can use a set comprehension to get unique values from RocCharge, loop for geting uniq val
unique_charge_holder_names = {i.charge_holder_name for i in RocCharge.objects.all()}
unique_company_names = {company.company_name for company in RocCompany.objects.all()}
class Orderfilter(django_filters.FilterSet):
# Specify the choices as a list of tuples
charge_holder_name = django_filters.MultipleChoiceFilter(
field_name='charge_holder_name',
lookup_expr='icontains',
label='Charge Holder Name',
choices=[(name, name) for name in unique_charge_holder_names]
)
#done but very difict one mixup thing field name cin but table from roccompny
company_cin = django_filters.ModelMultipleChoiceFilter(
field_name='cin',
lookup_expr='exact',
label='comapny Cin',
#in choices two args are required !
queryset=RocCompany.objects.all(),
)
#we cant send directly from model roccompany to filre we have to send through rochcgarge through cin__
company_name = django_filters.MultipleChoiceFilter(
field_name='cin__company_name',
lookup_expr='icontains',
label='Company Name',
#in choices two args are required !
choices=[(name, name) for name in unique_company_names]
)
amount = django_filters.NumberFilter(
field_name='amount',
label='Amount (equal to)',
lookup_expr='exact',
)
CHOICES = (
('ascending', 'Ascending'),
('descending', 'Descending')
)
sorting=django_filters.ChoiceFilter(label='Orderr',choices=CHOICES,method='filt')
sorting_amt=django_filters.ChoiceFilter(label='Ordering',choices=CHOICES,method='amt_filt')
sorting_date=django_filters.ChoiceFilter(label='Ordering',choices=CHOICES,method='dte_filt')
sorting_cmpny=django_filters.ChoiceFilter(label='Ordering',choices=CHOICES,method='cmp_filt')
sorting_city=django_filters.ChoiceFilter(label='Ordering',choices=CHOICES,method='cty_filt')
class Meta:
model = RocCharge
fields = {
'existing_old': ['exact'],
'amount': ['lt', 'gt'],
'date_of_creation': ['gte', 'lte'],
}
def filt(self,queryset,name,value):
expres='charge_holder_name' if value=='ascending' else '-charge_holder_name'
return queryset.order_by(expres)
def amt_filt(self,queryset,name,value):
expres='amount' if value=='ascending' else '-amount'
return queryset.order_by(expres)
def dte_filt(self,queryset,name,value):
expres='date_of_creation' if value=='ascending' else '-date_of_creation'
return queryset.order_by(expres)
#secnd models sort fields.........
def cmp_filt(self,queryset,name,value):
expres='cin__company_name' if value=='ascending' else '-cin__company_name'
return queryset.order_by(expres)
def cty_filt(self,queryset,name,value):
expres='cin__city' if value=='ascending' else '-cin__city'
return queryset.order_by(expres) ,
my views.py:
def lenders(request):
myfilter = Orderfilter(request.GET, queryset=RocCharge.objects.all().select_related('cin'))
# Logic for clearing all filter buttons
if request.method == 'GET' and 'remove_filter' in request.GET:
filter_name = request.GET['remove_filter']
myfilter.form[filter_name].initial = None
elif 'remove_all_filters' in request.GET:
for filter_name in myfilter.form.fields:
myfilter.form[filter_name].initial = None
combined_data = myfilter.qs.annotate(
month=ExtractMonth('date_of_creation'),
year=ExtractYear('date_of_creation')
).values('charge_holder_name', 'month', 'year', 'cin__company_name','date_of_creation', 'date_of_modification',
'date_of_satisfaction', 'amount','existing_old','cin', # Include 'cin' in the values method
'cin__state', 'cin__city', 'cin__sector', 'cin__industry').annotate(
total_charges=Count('charge_id'),
total_amount=Sum('amount'),
new_clients_count=Count('cin__company_cin', distinct=True)
).order_by('year', 'month')
from_date = request.GET.get('fromdate')
to_date = request.GET.get('Todate')
if from_date and to_date:
combined_data = combined_data.filter(date_of_creation__gte=from_date, date_of_creation__lte=to_date)
# Convert month numbers to month names
for data in combined_data:
data['month'] = calendar.month_name[data['month']]
# Create a set with unique month and year combinations
unique_data = {}
vald=[]
for data in combined_data:
key = (data['month'], data['year'])
if key not in unique_data:
unique_data[key] = {
'total_charges': 0,
'total_amount': 0,
'new_company_name_count': 0,
'new_amt_sum': 0,
'unique_company_names':[], # Initialize an empty set for unique company names
'charge_holder_names':[],
'dos':[],
'dom':[],
'doc':[],
'amt':[],
'e_o':[],
'state':[],
'city':[],
'industry':[],
'sector':[],
}
unique_data[key]['total_charges'] += data['total_charges']
unique_data[key]['total_amount'] += float(format_in_crores(data['total_amount']))
new_value = data['cin'] # Assuming 'cin' is the value you want to add
# Check if it's a new company
if new_value not in vald:
unique_data[key]['new_company_name_count'] += data['new_clients_count']
unique_data[key]['new_amt_sum'] += (format_in_crores(data['amount']))
if data['new_clients_count'] > 0:
# Add the new company name to the set
unique_data[key]['unique_company_names'].append(data['cin__company_name'])
unique_data[key]['charge_holder_names'].append(data['charge_holder_name'])
unique_data[key]['dos'].append(data['date_of_satisfaction'])
unique_data[key]['dom'].append(data['date_of_modification'])
unique_data[key]['doc'].append(data['date_of_creation'])
unique_data[key]['amt'].append(data['amount'])
unique_data[key]['e_o'].append(data['existing_old'])
unique_data[key]['state'].append(data['cin__state'])
unique_data[key]['city'].append(data['cin__city'])
unique_data[key]['industry'].append(data['cin__industry'])
unique_data[key]['sector'].append(data['cin__sector'])
vald.append(new_value)
# Calculate the total sum of total_amount in unique_data
total_amount_sum = sum(totals['total_amount'] for totals in unique_data.values())
total_count = sum(totals['total_charges'] for totals in unique_data.values())
context = {
'myfilter': myfilter.form,
'unique_data': unique_data,
'applied_filters': request.GET.dict(),
'combined_data': combined_data,
'total_count': total_count,
'total_amount_sum': total_amount_sum,
'filtered_companies': myfilter.qs, # Add the filtered companies to the context
}
return render(request, "central/lenders_chrgs.html", context)
want to filter my data into new obj let ‘new_combined_data’ , like it will filter data, condition is that where “cin__company_name” does not have any relationship or does not have any records with that specific “charge holder name” which i give as a input from input box or using django char multiple select input filter.
dummy example
data before applying any filters:
| company_name | month | year | charge_holder_name |
|---|---|---|---|
| c A | Jan | 1889 | sbi |
| c W | Jan | 1995 | hdfc |
| c X | Feb | 2000 | kotak |
| c F | Feb | 1999 | gbs |
| c C | Feb | 2023 | kotak |
| c C | Feb | 2011 | unc |
| … etc | … | … | |
| c A | nov | 2020 | kotak |
| … etc | … | … |
now i applied filter on data “charge holder name” is =“kotak”
| company_name | month | year | charge_holder_name |
|---|---|---|---|
| c A | Jan | 2020 | kotak |
| c B | Jan | 2020 | kotak |
| c C | Feb | 2023 | kotak |
now we found and know that “c A” has also connected with “charge holder name”=“sbi” in database in records , so i give input ‘sbi’ on another filter “excude_chrg_name_data” filter and when i submit it will return output like this:
| company_name | month | year | charge_holder_name |
|---|---|---|---|
| c B | Jan | 2020 | kotak |
| c C | Feb | 2023 | kotak |
see company name c A entry removed
like i want to exclude all that cin_company_name (company_name) or fields data or whole row which were connected or have any relationship (because there is two models in models.py connect with ‘cin’ foreign key) or any records with that spefic charge holder name which i typed or give it as input from filter or input box.