def write_query_result(date_suffix, sql_filename, project, dataset, table,
write_disposition):
if write_disposition == 'WRITE_APPEND_ONE':
write_disposition = 'WRITE_APPEND'
else:
table += date_suffix
table_ref = client.dataset(dataset, project=project).table(table)
job_config = bigquery.QueryJobConfig()
job_config.destination = table_ref
job_config.write_disposition = write_disposition
query_job = client.query(get_query(sql_filename,
params={'date': date_suffix}),
job_config=job_config)
query_job.result()
print(f'Query written to table: {project}.{dataset}.{table} \n')
def search_new(request):
extra = []
query_string = ''
found_entries = None
if 'key' in request.GET:
query_string = request.GET['key'].strip()
entry_query = get_query(query_string, ['title'])
found_entries = Question.objects.filter(entry_query).order_by(
'-date_update')
for i in found_entries:
extra.append(UserProfile.objects.filter(user=i.user_id_id))
final = zip(extra, found_entries)
return render_to_response('search.html', {
'query_string': query_string,
'found_entries': found_entries,
'extra': extra,
'final': final
},
context_instance=RequestContext(request))
def apply_filter(self,request):
"""
Filtering according to the search term entered.
"""
self.results= []
i = 0
buyer_id = []
purchase_order = []
self.entry_query= get_query(self.title,self.fields_list)
if 'Client' in request.GET:
self.found_entries = User.objects.filter(self.entry_query)
for entries in self.found_entries:
self.temp = []
for value in self.fields_list:
obj = User.objects.filter(id=entries.id).values(
value)
for temp_result in obj:
self.temp.append(temp_result)
self.results.append(self.temp)
if 'Order' in request.GET:
if 'suspense' in request.GET:
self.found_entries = VoucherId.objects.filter\
(purchase_order_of_session=self.title)
suspenseorder = SuspenseOrder.objects.values\
('purchase_order_id')
for entries in self.found_entries:
self.temp = []
for value in self.fields_list:
self.obj = VoucherId.objects.values(value).\
filter(id=entries.id)
for temp_result in self.obj:
self.temp.append(temp_result)
if self.temp[-1] in suspenseorder and\
self.temp[-1] not in purchase_order:
purchase_order.append(self.temp[-1])
self.results.append(self.temp)
elif 'proforma' in request.GET:
self.found_entries = QuotedOrderofSession.objects.filter\
(quoted_order_session=self.title)
for entries in self.found_entries:
self.temp = []
for value in self.fields_list:
self.obj = QuotedOrderofSession.objects.values(value).\
filter(id=entries.id)
for temp_result in self.obj:
self.temp.append(temp_result)
self.results.append(self.temp)
else:
self.found_entries = VoucherId.objects.filter\
(purchase_order_of_session=self.title)
for entries in self.found_entries:
self.temp = []
for value in self.fields_list:
self.obj = VoucherId.objects.values(value).\
filter(id=entries.id)
for temp_result in self.obj:
self.temp.append(temp_result)
if self.temp[-1] in purchase_order:
pass
else:
purchase_order.append(self.temp[-1])
self.results.append(self.temp)
return self.view_results(request)
def apply_filter(self, request):
"""
Filtering according to the search term entered.
"""
self.results = []
i = 0
buyer_id = []
purchase_order = []
self.entry_query = get_query(self.title, self.fields_list)
if 'Client' in request.GET:
self.found_entries = User.objects.filter(self.entry_query)
for entries in self.found_entries:
self.temp = []
for value in self.fields_list:
obj = User.objects.filter(id=entries.id).values(value)
for temp_result in obj:
self.temp.append(temp_result)
self.results.append(self.temp)
if 'Order' in request.GET:
if 'suspense' in request.GET:
self.found_entries = VoucherId.objects.filter\
(purchase_order_of_session=self.title)
suspenseorder = SuspenseOrder.objects.values\
('purchase_order_id')
for entries in self.found_entries:
self.temp = []
for value in self.fields_list:
self.obj = VoucherId.objects.values(value).\
filter(id=entries.id)
for temp_result in self.obj:
self.temp.append(temp_result)
if self.temp[-1] in suspenseorder and\
self.temp[-1] not in purchase_order:
purchase_order.append(self.temp[-1])
self.results.append(self.temp)
elif 'proforma' in request.GET:
self.found_entries = QuotedOrderofSession.objects.filter\
(quoted_order_session=self.title)
for entries in self.found_entries:
self.temp = []
for value in self.fields_list:
self.obj = QuotedOrderofSession.objects.values(value).\
filter(id=entries.id)
for temp_result in self.obj:
self.temp.append(temp_result)
self.results.append(self.temp)
else:
self.found_entries = VoucherId.objects.filter\
(purchase_order_of_session=self.title)
for entries in self.found_entries:
self.temp = []
for value in self.fields_list:
self.obj = VoucherId.objects.values(value).\
filter(id=entries.id)
for temp_result in self.obj:
self.temp.append(temp_result)
if self.temp[-1] in purchase_order:
pass
else:
purchase_order.append(self.temp[-1])
self.results.append(self.temp)
return self.view_results(request)
def search_result(request):
"""
searches and displays the results for the query entered according
to the checkboxes selected
"""
title = "Search"
results = []
result_fields = []
purchase_order_id = ""
selected_fields_client = request.GET.getlist("client_fields")
selected_fields_order = request.GET.getlist("order")
selected_fields_constraints = request.GET.getlist("additional_constraints")
avail_list_dict_client = {
"name": "user__username",
"city": "address__city",
"phone": "telephone",
"joining date": "date_joined",
"company": "company",
}
avail_list_dict_order = {
"quantity": "qty",
"unit price": "item__price_per_unit",
"item": "item__name",
"discount": "purchase_order__total_discount",
"debit": "purchase_order__is_debit",
"total price": "price",
}
avail_list = []
avail_list2 = []
start_date = request.GET["start_date"]
end_date = request.GET["end_date"]
amt_g = request.GET["amount_greater_than"]
amt_l = request.GET["amount_less_than"]
"""
For client Search
"""
if "Client" in request.GET:
result_fields.append(selected_fields_client)
search_fields = []
for value in selected_fields_client:
search_value = avail_list_dict_client[value]
search_fields.append(search_value)
query_string = ""
found_entries = None
if ("search" in request.GET) and request.GET["search"].strip():
query_string = request.GET["search"]
entry_query = get_query(query_string, search_fields)
found_entries = Customer.objects.filter(entry_query)
for entries in found_entries:
temp = []
if "date" in selected_fields_constraints:
for value in search_fields:
obj = Customer.objects.filter(id=entries.id, date_joined__range=(start_date, end_date)).values(
value
)
for temp_result in obj:
temp.append(temp_result)
else:
for value in search_fields:
obj = Customer.objects.filter(id=entries.id).values(value)
for temp_result in obj:
temp.append(temp_result)
results.append(temp)
"""
For Order Search
"""
if "Order" in request.GET:
result_fields.append(selected_fields_order)
search_fields = []
for value in selected_fields_order:
search_value = avail_list_dict_order[value]
search_fields.append(search_value)
query_string = ""
found_entries = ""
search_fields.append("purchase_order__id")
purchase_order_id = "enable"
if ("search" in request.GET) and request.GET["search"].strip():
query_string = request.GET["search"]
entry_query = get_query(query_string, search_fields)
found_entries = PurchasedItem.objects.filter(entry_query)
for entries in found_entries:
temp = []
"""
if Debit, Date, 'Amount greater than'(gt),
'Amount lesser than'(lt) checkboxes are selected
"""
if (
"debit" in selected_fields_order
and "date" in selected_fields_constraints
and "gt" in selected_fields_constraints
and "lt" in selected_fields_constraints
):
for value in search_fields:
obj = PurchasedItem.objects.filter(
id=entries.id,
purchase_order__is_debit=1,
purchase_order__date_time__range=(start_date, end_date),
price__gt=amt_g,
price__lt=amt_l,
).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Date, 'Amount greater than'(gt), 'Amount lesser than'
(lt) checkboxes are selected
"""
if (
"date"
and "gt" in selected_fields_constraints
and "lt" in selected_fields_constraints
and not "debit" in selected_fields_order
):
for value in search_fields:
obj = PurchasedItem.objects.filter(
id=entries.id,
purchase_order__date_time__range=(start_date, end_date),
price__gt=amt_g,
price__lt=amt_l,
).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, 'Amount greater than'(gt), 'Amount lesser than'
(lt) checkboxes are selected
"""
if (
"debit" in selected_fields_order
and not "date" in selected_fields_constraints
and "gt" in selected_fields_constraints
and "lt" in selected_fields_constraints
):
for value in search_fields:
obj = PurchasedItem.objects.filter(
id=entries.id, purchase_order__is_debit=1, price__gt=amt_g, price__lt=amt_l
).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, Date, 'Amount lesser than'(lt) checkboxes are
selected
"""
if (
"debit" in selected_fields_order
and "date" in selected_fields_constraints
and "lt" in selected_fields_constraints
and not "gt" in selected_fields_constraints
):
for value in search_fields:
obj = PurchasedItem.objects.filter(
id=entries.id, purchase_order__is_debit=1, price__lt=amt_l
).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, Date, 'Amount greater than'(gt) checkboxes are
selected
"""
if (
"debit" in selected_fields_order
and "date" in selected_fields_constraints
and "gt" in selected_fields_constraints
and not "lt" in selected_fields_constraints
):
for value in search_fields:
obj = PurchasedItem.objects.filter(
id=entries.id,
purchase_order__is_debit=1,
purchase_order__date_time__range=(start_date, end_date),
price__gt=amt_g,
).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if 'Amount greater than'(gt), 'Amount lesser than'(lt)
checkboxes are selected
"""
if (
not "debit" in selected_fields_order
and not "date" in selected_fields_constraints
and "gt" in selected_fields_constraints
and "lt" in selected_fields_constraints
):
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id, price__gt=amt_g, price__lt=amt_l).values(
value
)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, 'Amount lesser than'(lt) checkboxes are
selected
"""
if (
"debit" in selected_fields_order
and not "date" in selected_fields_constraints
and not "gt" in selected_fields_constraints
and "lt" in selected_fields_constraints
):
for value in search_fields:
obj = PurchasedItem.objects.filter(
id=entries.id, purchase_order__is_debit=1, price_lt=amt_l
).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, 'Amount greater than'(gt), checkboxes are
selected
"""
if (
"debit" in selected_fields_order
and not "date" in selected_fields_constraints
and not "lt" in selected_fields_constraints
and "gt" in selected_fields_constraints
):
for value in search_fields:
obj = PurchasedItem.objects.filter(
id=entries.id, purchase_order__is_debit=1, price__gt=amt_g
).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Date, 'Amount greater than'(gt) checkboxes are
selected
"""
if (
not "debit" in selected_fields_order
and not "lt" in selected_fields_constraints
and "date" in selected_fields_constraints
and "gt" in selected_fields_constraints
):
for value in search_fields:
obj = PurchasedItem.objects.filter(
id=entries.id, purchase_order__date_time__range=(start_date, end_date), price__gt=amt_g
).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Date, 'Amount lesser than'(lt) checkboxes are
selected
"""
if (
not "debit" in selected_fields_order
and not "gt" in selected_fields_constraints
and "date" in selected_fields_constraints
and "lt" in selected_fields_constraints
):
for value in search_fields:
obj = PurchasedItem.objects.filter(
id=entries.id, purchase_order__date_time__range=(start_date, end_date), price__lt=amt_l
).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, Date checkboxes are selected
"""
if (
"debit" in selected_fields_order
and "date" in selected_fields_constraints
and not "gt" in selected_fields_constraints
and not "lt" in selected_fields_constraints
):
for value in search_fields:
obj = PurchasedItem.objects.filter(
id=entries.id,
purchase_order__is_debit=1,
purchase_order__date_time__range=(start_date, end_date),
).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit checkbox is selected
"""
if (
"debit" in selected_fields_order
and not "date" in selected_fields_constraints
and not "gt" in selected_fields_constraints
and not "lt" in selected_fields_constraints
):
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id, purchase_order__is_debit=1).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Date checkbox is selected
"""
if (
not "debit" in selected_fields_order
and "date" in selected_fields_constraints
and not "gt" in selected_fields_constraints
and not "lt" in selected_fields_constraints
):
for value in search_fields:
obj = PurchasedItem.objects.filter(
id=entries.id, purchase_order__date_time__range=(start_date, end_date)
).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if 'Amount greater than'(gt) checkboxes is selected
"""
if (
not "debit" in selected_fields_order
and "gt" in selected_fields_constraints
and not "date" in selected_fields_constraints
and not "lt" in selected_fields_constraints
):
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id, price__gt=amt_g).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if 'Amount lesser than'(lt) checkbox is selected
"""
if (
not "debit" in selected_fields_order
and "lt" in selected_fields_constraints
and not "date" in selected_fields_constraints
and not "gt" in selected_fields_constraints
):
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id, price__lt=amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if none of the checkboxes - Debit, Date, 'Amount
greater than'(gt), 'Amount lesser than'(lt) are
selected
"""
if (
not "debit" in selected_fields_order
and not "date" in selected_fields_constraints
and not "gt" in selected_fields_constraints
and not "lt" in selected_fields_constraints
):
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id).values(value)
for temp_result in obj:
temp.append(temp_result)
results.append(temp)
if "search" in request.GET:
title = request.GET["search"]
return render(
request,
"reports/search_result.html",
{"results": results, "title": title, "result_fields": result_fields, "order_id": purchase_order_id},
)
def search_result(request):
"""
searches and displays the results for the query entered according
to the checkboxes selected
"""
title = 'Search'
results=[]
result_fields = []
purchase_order_id = ''
selected_fields_client = request.GET.getlist('client_fields')
selected_fields_order = request.GET.getlist('order')
selected_fields_constraints = request.GET.getlist('additional_constraints')
avail_list_dict_client = {'name':'user__username', 'city':'address__city',
'phone':'telephone','joining date':'date_joined',
'company':'company'}
avail_list_dict_order = {'quantity':'qty','unit price':'item__price_per_unit',
'item':'item__name','discount':'purchase_order__total_discount','debit':
'purchase_order__is_debit', 'total price':'price'}
avail_list = []
avail_list2=[]
start_date = request.GET['start_date']
end_date = request.GET['end_date']
amt_g = request.GET['amount_greater_than']
amt_l = request.GET['amount_less_than']
"""
For client Search
"""
if 'Client' in request.GET:
result_fields.append(selected_fields_client)
search_fields = []
for value in selected_fields_client:
search_value = avail_list_dict_client[value]
search_fields.append(search_value)
query_string = ''
found_entries = None
if ('search' in request.GET) and request.GET['search'].strip():
query_string = request.GET['search']
entry_query = get_query(query_string, search_fields)
found_entries = Customer.objects.filter(entry_query)
for entries in found_entries:
temp = []
if 'date' in selected_fields_constraints:
for value in search_fields:
obj = Customer.objects.filter(id=entries.id,
date_joined__range=(start_date,end_date)).values(value)
for temp_result in obj:
temp.append(temp_result)
else:
for value in search_fields:
obj = Customer.objects.filter(id=entries.id).values(
value)
for temp_result in obj:
temp.append(temp_result)
results.append(temp)
"""
For Order Search
"""
if 'Order' in request.GET:
result_fields.append(selected_fields_order)
search_fields = []
for value in selected_fields_order:
search_value = avail_list_dict_order[value]
search_fields.append(search_value)
query_string = ''
found_entries = ''
search_fields.append('purchase_order__id')
purchase_order_id = 'enable'
if ('search' in request.GET) and request.GET['search'].strip():
query_string = request.GET['search']
entry_query = get_query(query_string,search_fields)
found_entries = PurchasedItem.objects.filter(entry_query)
for entries in found_entries:
temp=[]
"""
if Debit, Date, 'Amount greater than'(gt),
'Amount lesser than'(lt) checkboxes are selected
"""
if 'debit' in selected_fields_order and 'date' in selected_fields_constraints and 'gt' in selected_fields_constraints and 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(
id=entries.id,purchase_order__is_debit=1,
purchase_order__date_time__range=(start_date,
end_date), price__gt= amt_g, price__lt=
amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Date, 'Amount greater than'(gt), 'Amount lesser than'
(lt) checkboxes are selected
"""
if 'date' and 'gt' in selected_fields_constraints and 'lt' in selected_fields_constraints and not 'debit' in selected_fields_order:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,
purchase_order__date_time__range=(start_date,
end_date),price__gt=amt_g, price__lt=
amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, 'Amount greater than'(gt), 'Amount lesser than'
(lt) checkboxes are selected
"""
if 'debit' in selected_fields_order and not 'date' in selected_fields_constraints and 'gt' in selected_fields_constraints and 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,
purchase_order__is_debit = 1,price__gt=amt_g,
price__lt=amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, Date, 'Amount lesser than'(lt) checkboxes are
selected
"""
if 'debit' in selected_fields_order and 'date' in selected_fields_constraints and 'lt' in selected_fields_constraints and not 'gt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,
purchase_order__is_debit = 1, price__lt=
amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, Date, 'Amount greater than'(gt) checkboxes are
selected
"""
if 'debit' in selected_fields_order and 'date' in selected_fields_constraints and 'gt' in selected_fields_constraints and not 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,
purchase_order__is_debit=1,
purchase_order__date_time__range=(start_date,
end_date), price__gt=amt_g).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if 'Amount greater than'(gt), 'Amount lesser than'(lt)
checkboxes are selected
"""
if not 'debit' in selected_fields_order and not 'date' in selected_fields_constraints and 'gt' in selected_fields_constraints and 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,
price__gt=amt_g,price__lt=amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, 'Amount lesser than'(lt) checkboxes are
selected
"""
if 'debit' in selected_fields_order and not 'date' in selected_fields_constraints and not 'gt' in selected_fields_constraints and 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,
purchase_order__is_debit = 1,price_lt=
amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, 'Amount greater than'(gt), checkboxes are
selected
"""
if 'debit' in selected_fields_order and not 'date' in selected_fields_constraints and not 'lt' in selected_fields_constraints and 'gt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,
purchase_order__is_debit = 1, price__gt=
amt_g).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Date, 'Amount greater than'(gt) checkboxes are
selected
"""
if not 'debit' in selected_fields_order and not 'lt' in selected_fields_constraints and 'date' in selected_fields_constraints and 'gt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,
purchase_order__date_time__range=(start_date,
end_date),price__gt=amt_g).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Date, 'Amount lesser than'(lt) checkboxes are
selected
"""
if not 'debit' in selected_fields_order and not 'gt' in selected_fields_constraints and 'date' in selected_fields_constraints and 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,
purchase_order__date_time__range=(start_date,
end_date), price__lt=amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, Date checkboxes are selected
"""
if 'debit' in selected_fields_order and 'date' in selected_fields_constraints and not 'gt' in selected_fields_constraints and not 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,
purchase_order__is_debit = 1,
purchase_order__date_time__range=(start_date,
end_date)).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit checkbox is selected
"""
if 'debit' in selected_fields_order and not 'date' in selected_fields_constraints and not 'gt' in selected_fields_constraints and not 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,
purchase_order__is_debit = 1).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Date checkbox is selected
"""
if not 'debit' in selected_fields_order and 'date' in selected_fields_constraints and not 'gt' in selected_fields_constraints and not 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,
purchase_order__date_time__range=(start_date,
end_date)).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if 'Amount greater than'(gt) checkboxes is selected
"""
if not 'debit' in selected_fields_order and 'gt' in selected_fields_constraints and not 'date' in selected_fields_constraints and not 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,
price__gt=amt_g).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if 'Amount lesser than'(lt) checkbox is selected
"""
if not 'debit' in selected_fields_order and 'lt' in selected_fields_constraints and not 'date' in selected_fields_constraints and not 'gt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,
price__lt=amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if none of the checkboxes - Debit, Date, 'Amount
greater than'(gt), 'Amount lesser than'(lt) are
selected
"""
if not 'debit' in selected_fields_order and not 'date' in selected_fields_constraints and not 'gt' in selected_fields_constraints and not 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=
entries.id).values(value)
for temp_result in obj:
temp.append(temp_result)
results.append(temp)
if 'search' in request.GET:
title = request.GET['search']
return render(request, 'reports/search_result.html', {'results':
results,'title': title,'result_fields':result_fields,
'order_id':purchase_order_id})
def search_result(request):
"""
searches and displays the results for the query entered according to the checkboxes selected
"""
title = 'Search'
results=[]
result_fields = []
selected_fields_client = request.GET.getlist('client_fields')
selected_fields_order = request.GET.getlist('order')
selected_fields_constraints = request.GET.getlist('additional_constraints')
avail_list_dict_client = {'name':'user__username', 'city':'address__city',
'phone':'telephone','joining date':'date_joined','company':'company'}
avail_list_dict_order = {'quantity':'qty','unit price':'item__price',
'item':'item__name','discount':'discount','debit':'purchase_order__is_debit',
'total price':'price'}
avail_list = []
avail_list2=[]
start_date = request.GET['start_date']
end_date = request.GET['end_date']
amt_g = request.GET['amount_greater_than']
amt_l = request.GET['amount_less_than']
"""
For client Search
"""
if 'Client' in request.GET:
result_fields.append(selected_fields_client)
search_fields = []
for value in selected_fields_client:
search_value = avail_list_dict_client[value]
search_fields.append(search_value)
query_string = ''
found_entries = None
if ('search' in request.GET) and request.GET['search'].strip():
query_string = request.GET['search']
entry_query = get_query(query_string, search_fields)
found_entries = Customer.objects.filter(entry_query)
for entries in found_entries:
temp = []
if 'date' in selected_fields_constraints:
for value in search_fields:
obj = Customer.objects.filter(id=entries.id, date_joined__range=(start_date,end_date) ).values(value)
for temp_result in obj:
temp.append(temp_result)
else:
for value in search_fields:
obj = Customer.objects.filter(id=entries.id).values(value)
for temp_result in obj:
temp.append(temp_result)
results.append(temp)
"""
For Order Search
"""
if 'Order' in request.GET:
result_fields.append(selected_fields_order)
search_fields = []
for value in selected_fields_order:
search_value = avail_list_dict_order[value]
search_fields.append(search_value)
query_string = ''
found_entries = None
if ('search' in request.GET) and request.GET['search'].strip():
query_string= request.GET['search']
entry_query = get_query(query_string,search_fields)
found_entries = PurchasedItem.objects.filter(entry_query)
for entries in found_entries:
temp=[]
"""
if Debit, Date, 'Amount greater than'(gt), 'Amount lesser than'(lt) checkboxes are selected
"""
if 'debit' in selected_fields_order and 'date' in selected_fields_constraints and 'gt' in selected_fields_constraints and 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,purchase_order__is_debit = 1,purchase_order__date_time__range=(start_date,end_date), price__gt= amt_g, price__lt = amt_l ).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Date, 'Amount greater than'(gt), 'Amount lesser than'(lt) checkboxes are selected
"""
if 'date' and 'gt' in selected_fields_constraints and 'lt' in selected_fields_constraints and not 'debit' in selected_fields_order:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,purchase_order__date_time__range=(start_date,end_date),price__gt=amt_g,price__lt=amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, 'Amount greater than'(gt), 'Amount lesser than'(lt) checkboxes are selected
"""
if 'debit' in selected_fields_order and not 'date' in selected_fields_constraints and 'gt' in selected_fields_constraints and 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,purchase_order__is_debit = 1,price__gt=amt_g,price__lt=amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, Date, 'Amount lesser than'(lt) checkboxes are selected
"""
if 'debit' in selected_fields_order and 'date' in selected_fields_constraints and 'lt' in selected_fields_constraints and not 'gt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,purchase_order__is_debit = 1, price__lt=amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, Date, 'Amount greater than'(gt) checkboxes are selected
"""
if 'debit' in selected_fields_order and 'date' in selected_fields_constraints and 'gt' in selected_fields_constraints and not 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,purchase_order__is_debit = 1,purchase_order__date_time__range=(start_date,end_date),price__gt=amt_g).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if 'Amount greater than'(gt), 'Amount lesser than'(lt) checkboxes are selected
"""
if not 'debit' in selected_fields_order and not 'date' in selected_fields_constraints and 'gt' in selected_fields_constraints and 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,price__gt=amt_g,price__lt=amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, 'Amount lesser than'(lt) checkboxes are selected
"""
if 'debit' in selected_fields_order and not 'date' in selected_fields_constraints and not 'gt' in selected_fields_constraints and 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,purchase_order__is_debit = 1,price_lt = amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, 'Amount greater than'(gt), checkboxes are selected
"""
if 'debit' in selected_fields_order and not 'date' in selected_fields_constraints and not 'lt' in selected_fields_constraints and 'gt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,purchase_order__is_debit = 1, price__gt= amt_g).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Date, 'Amount greater than'(gt) checkboxes are selected
"""
if not 'debit' in selected_fields_order and not 'lt' in selected_fields_constraints and 'date' in selected_fields_constraints and 'gt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,purchase_order__date_time__range=(start_date,end_date),price__gt=amt_g).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Date, 'Amount lesser than'(lt) checkboxes are selected
"""
if not 'debit' in selected_fields_order and not 'gt' in selected_fields_constraints and 'date' in selected_fields_constraints and 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,purchase_order__date_time__range=(start_date,end_date), price__lt=amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit, Date checkboxes are selected
"""
if 'debit' in selected_fields_order and 'date' in selected_fields_constraints and not 'gt' in selected_fields_constraints and not 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,purchase_order__is_debit = 1,purchase_order__date_time__range=(start_date,end_date)).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Debit checkbox is selected
"""
if 'debit' in selected_fields_order and not 'date' in selected_fields_constraints and not 'gt' in selected_fields_constraints and not 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,purchase_order__is_debit = 1).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if Date checkbox is selected
"""
if not 'debit' in selected_fields_order and 'date' in selected_fields_constraints and not 'gt' in selected_fields_constraints and not 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,purchase_order__date_time__range=(start_date,end_date)).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if 'Amount greater than'(gt) checkboxes is selected
"""
if not 'debit' in selected_fields_order and 'gt' in selected_fields_constraints and not 'date' in selected_fields_constraints and not 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id, price__gt=amt_g).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if 'Amount lesser than'(lt) checkbox is selected
"""
if not 'debit' in selected_fields_order and 'lt' in selected_fields_constraints and not 'date' in selected_fields_constraints and not 'gt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id,price__lt=amt_l).values(value)
for temp_result in obj:
temp.append(temp_result)
"""
if none of the checkboxes - Debit, Date, 'Amount greater than'(gt), 'Amount lesser than'(lt) are selected
"""
if not 'debit' in selected_fields_order and not 'date' in selected_fields_constraints and not 'gt'in selected_fields_constraints and not 'lt' in selected_fields_constraints:
for value in search_fields:
obj = PurchasedItem.objects.filter(id=entries.id).values(value)
for temp_result in obj:
temp.append(temp_result)
results.append(temp)
if 'search' in request.GET:
title = request.GET['search']
return render(request, 'search_result.html', {'results':results,'title': title,'result_fields':result_fields, 'a':search_fields,'b':search_value})
