def get_queryset(self):
queryset = self.queryset
#--------- Filter by distance-------------------#
ip = self.request.QUERY_PARAMS.get('ipNumber', '')
distance = self.request.QUERY_PARAMS.get('distance', '')
if ip and distance:
result = []
zip_code_data=[]
zip_all=[]
result =sql_select("select latitude,longitude from ip2location where "+ip+" between ip_from and ip_to limit 1")
if result:
zip_code_data =sql_select("select zip_code from ip2location where (3959 * acos(cos(radians("+result[0]['latitude']+")) * cos(radians(latitude)) * cos( radians(longitude) - radians("+result[0]['longitude']+")) + sin(radians("+result[0]['latitude']+")) * sin(radians(latitude)))) < "+distance+" group by zip_code")
if zip_code_data:
for zip in zip_code_data:
zip_all.append(zip['zip_code'])
queryset = queryset.filter(zip_code__in=zip_all)[:10]
elif ip is not None:
queryset = queryset.filter(ip_from__lte=ip,ip_to__gte=ip)
else:
queryset = ip2locationInfo.objects.none()
return queryset
def get_queryset(self):
queryset = self.queryset
status = self.request.QUERY_PARAMS.get('status_id',None)
if status is not None:
queryset = queryset.filter(status_id=status)
queryset_new = queryset
#-- Filter by ID ---
id = self.request.QUERY_PARAMS.get('id', '')
params = self.request.QUERY_PARAMS.get('params',None)
zip_codes = self.request.QUERY_PARAMS.get('zip_code',None)
if id!="":
queryset = queryset.filter(id=id)
#-------------------------#
#-- Search on avg_rating --- #
avg_rating = self.request.QUERY_PARAMS.get('avg_rating', None)
try:
avg_rating=int(avg_rating)
avg_rating = 'round_rating == '+str(avg_rating)
except:
try:
avg_rating = [int(x) for x in filter(None,avg_rating.split(","))]
string = []
for item in avg_rating:
string.append("round_rating == "+str(item))
avg_rating = ' or '.join(string)
except:
avg_rating=0
if avg_rating is not None and avg_rating!=0 and avg_rating!="":
result = Rate.objects.values('users_id').annotate(rate=Avg('rate'))
user_ids = []
for item in result:
round_rating = int(round(item['rate']))
if eval(avg_rating):
user_ids.append(item['users_id'])
queryset = queryset.filter(id__in=user_ids)
#-----------------------------#
#-- Search on service_id --- #
service_id = self.request.QUERY_PARAMS.get('service_id', None)
if service_id is not None and service_id!="":
try:
service_id=int(service_id)
service_id =[service_id]
except:
try:
service_id = [int(x) for x in filter(None,service_id.split(","))]
except:
service_id=0
if service_id is not 0:
result = SpUserService.objects.values('user_id','service_id').annotate(Sum('price')).filter(service_id__in=service_id)
user_ids = []
for item in result:
user_ids.append(item['user_id'])
queryset = queryset.filter(id__in=user_ids)
#-----------------------------#
#-- Search on auth_to_issue_insurence_rem_receipt --- #
auth_to_issue_insurence_rem_receipt = self.request.QUERY_PARAMS.get('auth_to_issue_insurence_rem_receipt', None)
try:
auth_to_issue_insurence_rem_receipt=str(auth_to_issue_insurence_rem_receipt)
except:
auth_to_issue_insurence_rem_receipt=None
if auth_to_issue_insurence_rem_receipt is not None:
if auth_to_issue_insurence_rem_receipt == '1':
queryset = queryset.filter(details__auth_to_issue_insurence_rem_receipt=1)
elif auth_to_issue_insurence_rem_receipt == '0':
queryset = queryset.exclude(details__auth_to_issue_insurence_rem_receipt=1)
#-----------------------------------------------------#
#-- Search on days_id --- #
days_id = self.request.QUERY_PARAMS.get('days_id', None)
if days_id is not None and days_id!="":
try:
days_id=int(days_id)
days_id =[days_id]
except:
try:
days_id = [int(x) for x in filter(None,days_id.split(","))]
except:
days_id=0 # When we assign 0,It wont check the condition
if days_id is not 0:
queryset = queryset.filter(availability_days__days_id__in=days_id).distinct()
#-------------------------#
#-- Search on practitioners_name --- #
practitioners_name = self.request.QUERY_PARAMS.get('practitioners_name', None)
if practitioners_name is not None and practitioners_name!="":
queryset_temp = queryset
## explode the string
practitioners_name=[str(x) for x in filter(None,practitioners_name.strip().split(' '))]
try:
if practitioners_name[0] and practitioners_name[1]:
queryset_temp = queryset_temp.filter(first_name__icontains=practitioners_name[0],last_name__icontains=practitioners_name[1])
except:
queryset_temp = queryset_temp.filter(first_name__icontains=practitioners_name[0])
#-- Search on company name --- #
if queryset_temp.exists()== False:
queryset = queryset.filter(details__company_name__contains=practitioners_name[0])
else:
queryset = queryset_temp
#-------------------------#
#-- Filter by city ---
#city = self.request.QUERY_PARAMS.get('city', '')
#if city!="":
# queryset = queryset.filter(Q(work_address__city__icontains=city)|Q(address__city__icontains=city)).distinct()
#-------------------------#
#-- Filter by State ---
state_id = self.request.QUERY_PARAMS.get('state_id', '')
if state_id!="":
queryset = queryset.filter(work_address__state_id=state_id).distinct()
#-------------------------#
#-- Filter by Country ---
country_id = self.request.QUERY_PARAMS.get('country_id', '')
if country_id!="":
queryset = queryset.filter(work_address__country_id=country_id).distinct()
#-------------------------#
#-- Filter by Zip Code ---
#zip_code = self.request.QUERY_PARAMS.get('zip_code', '')
#if zip_code!="":
# queryset = queryset.filter(Q(work_address__zip_code=zip_code)|Q(address__zip_code=zip_code))
#-------------------------#
#-- Lets add parameter for city name province name and country name ---
params = self.request.QUERY_PARAMS.get("params",None)
if params is not None:
#queryset = queryset.filter(Q(work_address__country__country_name__icontains=params)|Q(address__country__country_name__icontains=params)|Q(work_address__state__state_name__icontains=params)|Q(address__state__state_name__icontains=params)|Q(work_address__city__icontains=params)|Q(address__city__icontains=params)).distinct()
queryset = queryset.filter(Q(work_address__country__country_name__icontains=params)|Q(work_address__state__state_name__icontains=params)|Q(work_address__city__icontains=params)).distinct()
#-------------------------#
#-- Lets add parameter for country name for the same ---
country_name = self.request.QUERY_PARAMS.get("country_name",'')
if country_name!='':
#queryset = queryset.filter(Q(work_address__country__country_name__icontains=country_name)|Q(address__country__country_name__icontains=country_name)).distinct()
queryset = queryset.filter(Q(work_address__country__country_name__icontains=country_name)).distinct()
#-------------------------#
#-- Lets add parameter for province name for the same ---
state_name = self.request.QUERY_PARAMS.get("state_name", '')
if state_name!='':
#queryset = queryset.filter(Q(work_address__state__state_name__icontains=state_name)|Q(address__state__state_name__icontains=state_name)).distinct()
queryset = queryset.filter(Q(work_address__state__state_name__icontains=state_name)).distinct()
#-------------------------#
#-- Location type filter go here ---#
loc_type = self.request.QUERY_PARAMS.get("location_type",'')
if loc_type:
location_types = str(loc_type)
location_type = location_types.split(',')
if '4' in location_type:
queryset = queryset.filter(details__offering_at_home='1')
location_type.remove('4')
if '5' in location_type:
queryset = queryset.filter(details__offering_at_work_office='1')
location_type.remove('5')
if location_type:
queryset = queryset.filter(work_address__location_type_id__in=location_type)
#--------- Filter by distance-------------------#
city = self.request.QUERY_PARAMS.get('city', '')
zip_code = self.request.QUERY_PARAMS.get('zip_code', '')
ip = self.request.QUERY_PARAMS.get('ipNumber', '')
distance = self.request.QUERY_PARAMS.get('distance', '')
if city!="" and zip_code!="":
result = []
zip_code_data=[]
zip_all=[]
result =sql_select("select Latitude,Longitude from myips where PostalCode like '%"+zip_code+"%' limit 1")
if result and distance!='':
zip_code_data =sql_select("select DISTINCT PostalCode from myips inner join address on address.zip_code = myips.PostalCode WHERE round(3959 * acos(cos(radians("+result[0]['Latitude']+")) * cos(radians(Latitude)) * cos( radians(Longitude) - radians("+result[0]['Longitude']+")) + sin(radians("+result[0]['Latitude']+")) * sin(radians(Latitude)))) <= "+distance+" order by (3959 * acos(cos(radians("+result[0]['Latitude']+")) * cos(radians(Latitude)) * cos( radians(Longitude) - radians("+result[0]['Longitude']+")) + sin(radians("+result[0]['Latitude']+")) * sin(radians(Latitude)))) ASC limit 20")
elif result:
zip_code_data =sql_select("select DISTINCT PostalCode from myips inner join address on address.zip_code = myips.PostalCode order by (3959 * acos(cos(radians("+result[0]['Latitude']+")) * cos(radians(Latitude)) * cos( radians(Longitude) - radians("+result[0]['Longitude']+")) + sin(radians("+result[0]['Latitude']+")) * sin(radians(Latitude)))) ASC limit 20")
if zip_code_data:
for zip in zip_code_data:
zip_all.append(zip['PostalCode'])
#assert False, zip_all
if distance !='':
queryset = queryset.filter(Q(work_address__zip_code__in=zip_all))
else:
queryset = queryset.filter(Q(work_address__city__icontains=city)|Q(work_address__zip_code__in=zip_all))
#assert False, queryset.query
#-----------------------------------------------#
#------------- Filter by association_member --------------#
association_member = self.request.QUERY_PARAMS.get('association_member', None)
if association_member and association_member is not None:
queryset_org=PractitionerOrganizationLookup.objects.filter(organization__status_id =1)
user_ids_all = []
for item_all in queryset_org:
user_ids_all.append(item_all.practitioner_id)
if user_ids_all:
if association_member == '1':
queryset = queryset.filter(id__in=user_ids_all)
elif association_member == '0':
queryset = queryset.exclude(id__in=user_ids_all)
#------------------ End -------------------------#
#------------- Filter by Price Range ------------#
minPrice = self.request.QUERY_PARAMS.get('minPrice', 0)
maxPrice = self.request.QUERY_PARAMS.get('maxPrice', 0)
if maxPrice and maxPrice != '0':
queryset = queryset.filter(spusersservice__price__range=[minPrice,maxPrice]).distinct()
#------------------------------------------------#
#------------- Filter by Price Range ------------#
treatmentLength = self.request.QUERY_PARAMS.get('treatmentLength', 0)
if treatmentLength and treatmentLength != '0':
queryset = queryset.filter(spusersservice__duration__range=[0,treatmentLength]).distinct()
#------------- Filter by treatment for physically disabled person ------------#
treatment = self.request.QUERY_PARAMS.get('treatment_for_physically_disabled_person', 0)
if treatment and treatment != '0':
queryset = queryset.filter(details__treatment_for_physically_disabled_person=treatment).distinct()
# --------------- Filter by sp_availability --------------------------#
sp_availability = self.request.QUERY_PARAMS.get('sp_availability', '')
date_range_filter = self.request.QUERY_PARAMS.get('date_range_filter', '')
if sp_availability == '' and date_range_filter == '1' :
return queryset.none()
if sp_availability and sp_availability != '':
queryset = queryset.filter(id__in=[int(x) for x in filter(None,sp_availability.split(","))])
#---------------- End ------------------------------------------------#
exclude_user = self.request.QUERY_PARAMS.get('exclude_user', '')
if exclude_user:
queryset = queryset.exclude(id=exclude_user)
#-- Order by price ---
price = self.request.QUERY_PARAMS.get('price', None)
if price=='1':
queryset = queryset.annotate(min_price=Min('spusersservice__price')).exclude(spusersservice__price__isnull=True).order_by('-min_price') # Higher to lower
elif price=='2':
queryset = queryset.annotate(min_price=Min('spusersservice__price')).exclude(spusersservice__price__isnull=True).order_by('min_price') # lower to Higher
#-------------------------#
#-- Order by booking_no ---
booking_no = self.request.QUERY_PARAMS.get('booking_no', '')
if booking_no == '1' or booking_no == '2':
total_booking = Booking.objects.values('service_provider_id').annotate(total_booking=Count('service_provider')).filter(user_id__isnull=False).order_by('total_booking')
#total_booking = BookingSuggestionHistory.objects.values('user').annotate(total_booking=Count('user')).filter(booking_status=4,user__isnull=False).order_by('total_booking')
#print total_booking.query
user_ids_all = []
for item_all in queryset:
user_ids_all.append(item_all.id)
user_ids = []
for item in total_booking:
#user_ids.append(item['user'])
user_ids.append(item['service_provider_id'])
ordering = 'FIELD(`id`, %s)' % ','.join(str(id) for id in user_ids)
if booking_no == '1':
queryset = queryset_new.filter(id__in=user_ids_all).extra(select={'ordering': ordering}, order_by=('-ordering',))
else:
queryset = queryset_new.filter(id__in=user_ids_all).extra(select={'ordering': ordering}, order_by=('ordering',))
#-------------------------#
#-- Order by feedback_filter ---
feedback_filter = self.request.QUERY_PARAMS.get('feedback_filter', '')
if feedback_filter == '1' or feedback_filter == '2':
total_feedback = Feedback.objects.values('users_id').annotate(total_feedback=Count('users_id')).filter(status_id=9).order_by('total_feedback')
user_ids_all = []
for item_all in queryset:
user_ids_all.append(item_all.id)
user_ids = []
for item in total_feedback:
user_ids.append(item['users_id'])
ordering = 'FIELD(`id`, %s)' % ','.join(str(id) for id in user_ids)
if feedback_filter == '1':
queryset = queryset_new.filter(id__in=user_ids_all).extra(select={'ordering': ordering}, order_by=('-ordering',))
else:
queryset = queryset_new.filter(id__in=user_ids_all).extra(select={'ordering': ordering}, order_by=('ordering',))
#-------------------------#
#assert False, queryset.query
queryset = queryset.distinct()
return queryset