def update_breadcrumbs_without_dist(self):
# set breadcrumbs to default empty array
RawSql(
'''update entity_urls eu set breadcrumb=json_build_array() where entity_type='Hospital' and is_valid = True
and sitemap_identifier in ('HOSPITALS_LOCALITY_CITY', 'HOSPITALS_CITY' , 'HOSPITAL_PAGE') ''',
[]).execute()
RawSql(
'''update entity_urls tu set breadcrumb=(select json_build_array(json_build_object('title', concat(locality_value, ' Hospitals ') , 'url', url, 'link_title', concat(locality_value, ' Hospitals')))
from entity_urls where sitemap_identifier ='HOSPITALS_CITY' and is_valid=True and lower(locality_value)=lower(tu.locality_value)
limit 1) where sitemap_identifier ='HOSPITALS_LOCALITY_CITY' and is_valid=True ''',
[]).execute()
# update for hospital profile from city if null
RawSql(
'''update entity_urls tu set breadcrumb = (select breadcrumb ||
jsonb_build_array(jsonb_build_object('title', concat(sublocality_value), 'url', url,
'link_title', concat('Hospitals in ', sublocality_value ,', ', locality_value) ))
from entity_urls where sitemap_identifier ='HOSPITALS_LOCALITY_CITY' and is_valid=True and lower(locality_value)=lower(tu.locality_value)
and lower(sublocality_value)=lower(tu.sublocality_value)
order by st_distance(location, tu.location) asc limit 1)
where sitemap_identifier ='HOSPITAL_PAGE' and is_valid=True ''',
[]).execute()
RawSql(
'''update entity_urls tu set breadcrumb = (select jsonb_build_array(jsonb_build_object('title', concat(locality_value, ' Hospitals '), 'url', url,
'link_title', concat(locality_value, ' Hospitals') ))
from entity_urls where sitemap_identifier ='HOSPITALS_CITY' and is_valid=True and lower(locality_value)=lower(tu.locality_value)
order by st_distance(location, tu.location) asc limit 1)
where sitemap_identifier ='HOSPITAL_PAGE' and is_valid=True and breadcrumb is null ''',
[]).execute()
RawSql(
'''update entity_urls tu set breadcrumb=json_build_array() where breadcrumb is null and is_valid=True and entity_type='Hospital' ''',
[]).execute()
def map_lab_location_urls():
query = '''select nextval('entity_url_version_seq') as inc'''
seq = RawSql(query, []).fetch_all()
sequence = seq[0]['inc']
cache = PageUrlCache(EntityUrls.SitemapIdentifier.LAB_PAGE)
to_create = []
all_labs = Lab.objects.filter(is_live=True).all().annotate(
distance=Distance(
'location', Point(float(77.0694707), float(28.4502948),
srid=4326))).order_by('distance')
for lab in all_labs:
to_create.append(LabPageUrl.create_page_urls(lab, sequence, cache))
EntityUrls.objects.bulk_create(to_create)
print("lab page urls created")
update_query = '''update entity_urls set is_valid=false where sitemap_identifier
in ('LAB_PAGE') and sequence< %d''' % sequence
RawSql(update_query, []).execute()
cleanup = '''delete from entity_urls where id in (select id from
(select eu.*, row_number() over(partition by url order by is_valid desc, sequence desc) rownum from entity_urls eu
)x where rownum>1
) '''
RawSql(cleanup, []).execute()
print("success")
return True
def update_breadcrumbs(self):
# set breadcrumbs to default empty array
RawSql('''update temp_url tu set breadcrumb=json_build_array()''',
[]).execute()
RawSql(
'''update temp_url tu set breadcrumb=(select json_build_array(json_build_object('title', concat(locality_value, ' Hospitals ') , 'url', url, 'link_title', concat(locality_value, ' Hospitals')))
from temp_url where sitemap_identifier ='HOSPITALS_CITY' and lower(locality_value)=lower(tu.locality_value)
and st_dwithin(location::geography, tu.location::geography, 20000) order by st_distance(location, tu.location) asc limit 1)
where sitemap_identifier ='HOSPITALS_LOCALITY_CITY' ''',
[]).execute()
# update for hospital profile from city if null
RawSql(
'''update temp_url tu set breadcrumb = (select breadcrumb ||
jsonb_build_array(jsonb_build_object('title', concat(sublocality_value), 'url', url,
'link_title', concat('Hospitals in ', sublocality_value ,', ', locality_value) ))
from temp_url where sitemap_identifier ='HOSPITALS_LOCALITY_CITY' and lower(locality_value)=lower(tu.locality_value)
and lower(sublocality_value)=lower(tu.sublocality_value)
and st_dwithin(location::geography, tu.location::geography, 10000) order by st_distance(location, tu.location) asc limit 1)
where sitemap_identifier ='HOSPITAL_PAGE' ''', []).execute()
RawSql(
'''update temp_url tu set breadcrumb=json_build_array() where breadcrumb is null ''',
[]).execute()
def calculate_centroid():
RawSql("update entity_address set centroid=null", []).execute()
max_order = RawSql('select max("order") from entity_address', []).fetch_all()
if max_order:
max_order = max_order[0]['max']
print(max_order)
else:
print("error")
return
# for all addresses with no child
RawSql("update entity_address set centroid=abs_centroid, child_count=1 where id not in (select parent_id from \
entity_address where parent_id is not null)", []).execute()
current_order = max_order
while current_order >= 1:
print("running for " + str(current_order))
RawSql("update entity_address ea set centroid = (select ST_Centroid(ST_Union(centroid::geometry)) "
"from entity_address where parent_id=ea.id and centroid is not null), "
"child_count = (select sum(child_count)+1 from entity_address where parent_id=ea.id) " \
"where ea.order=%s and (select count(*) from entity_address where parent_id=ea.id and " \
"centroid is not null)>0", [current_order]).execute()
current_order -= 1
RawSql('''update entity_address e set centroid = ( select
st_setsrid(st_point(cl.longitude, cl.latitude),4326)::geography as city_centroid
from city_lat_long cl where lower(e.alternative_value) = lower(cl.city) ) where e.id in (select ea.id
from entity_address ea inner join city_lat_long cll
on lower(ea.alternative_value) = lower(cll.city) and ea.type = 'LOCALITY' )''', []).execute()
def __init__(self):
query = '''select nextval('entity_url_version_seq') as inc;'''
seq = RawSql(query, []).fetch_all()
self.sequence = seq[0]['inc']
RawSql('truncate table temp_url', []).execute()
ea_limit = RawSql(
'select min(id) min, max(id) max from entity_address',
[]).fetch_all()
self.min_ea = ea_limit[0]['min']
self.max_ea = ea_limit[0]['max']
self.step = 2000
def update_doctors_count(cls):
RawSql('''update practice_specialization ps set doctor_count=
coalesce((select doctor_count from
(select ps.id specialization_id, count(distinct d.id) as doctor_count from doctor d inner join
doctor_practice_specialization dps on d.id = dps.doctor_id
inner join practice_specialization ps on ps.id = dps.specialization_id
group by ps.id)x where x.specialization_id = ps.id),0)
''', []).execute()
return "success"
def map_doctor_urls():
query = '''select nextval('entity_url_version_seq') as inc'''
seq = RawSql(query, []).fetch_all()
if seq:
sequence = seq[0]['inc'] if seq[0]['inc'] else 0
else:
sequence = 0
all_doctors = Doctor.objects.filter(
is_live=True, is_test_doctor=False).order_by('-id').all()
for doctor in all_doctors:
try:
dp = DoctorPageURL(doctor, sequence)
dp.create()
except Exception as e:
print(str(e))
def map_hospital_geocoding_results():
#hospital_object = Hospital.objects.first()
query = "select id, st_x(location::geometry)::text lng , st_y(location::geometry)::text lat from hospital where location is not null and is_live=true order by id desc"
all_hospitals = RawSql(query, []).fetch_all()
print("Attempting for hospital. ", len(all_hospitals))
for hospital in all_hospitals:
response = GeocodingResults.create_results(
latitude=hospital.get('lat'),
longitude=hospital.get('lng'),
id=hospital.get('id'),
type='hospital')
def map_lab_geocoding_results():
# lab_object = Lab.objects.first()
query = "select id, st_x(location::geometry)::text lng , st_y(location::geometry)::text lat from lab where location is not null and is_live=true order by id desc"
all_labs = RawSql(query, []).fetch_all()
print("Attempting for lab.", len(all_labs))
for lab in all_labs:
print(lab)
print(
GeocodingResults.create_results(latitude=lab.get('lat'),
longitude=lab.get('lng'),
id=lab.get('id'),
type='lab'))
def map_lab_location_urls():
query = '''select nextval('entity_url_version_seq') as inc'''
seq = RawSql(query, []).fetch_all()
if seq:
sequence = seq[0]['inc'] if seq[0]['inc'] else 0
else:
sequence = 0
all_labs = Lab.objects.filter(is_live=True).all().annotate(
distance=Distance(
'location', Point(float(77.0694707), float(28.4502948),
srid=4326))).order_by('distance')[:5000]
for lab in all_labs:
try:
lp = LabPageUrl(lab, sequence)
lp.create()
except Exception as e:
print(str(e))
def map_lab_location_urls():
content_type = ContentType.objects.get(model='lab')
if content_type:
query = '''select distinct(l.id) from lab l inner join entity_location_relations elr on l.id = elr.object_id
and elr.content_type_id=%s and l.is_live=True and (ST_Distance(l.location, elr.entity_geo_location)>0
or elr.entity_geo_location is null) order by l.id'''
result = RawSql(query, [content_type.id]).fetch_all()
all_labs_id = list(map(lambda h: h.get('id', 0), result))
all_labs = Lab.objects.filter(id__in=all_labs_id)
for lab in all_labs:
if lab.location:
success = EntityLocationRelationship.create(
latitude=lab.location.y,
longitude=lab.location.x,
content_object=lab)
if success:
print("Successfull for labid ", lab.id)
else:
print("Failed for labid ", lab.id)
def map_hospital_locations():
content_type = ContentType.objects.get(model='hospital')
if content_type:
query = '''select distinct(h.id) from hospital h left join entity_location_relations elr on h.id = elr.object_id
and elr.content_type_id=%s and h.is_live=True and (ST_Distance(h.location, elr.entity_geo_location)>0
or elr.entity_geo_location is null) order by h.id'''
result = RawSql(query, [content_type.id]).fetch_all()
all_hospitals_id = list(map(lambda h: h.get('id', 0), result))
all_hospitals = Hospital.objects.filter(id__in=all_hospitals_id)
print("Attempting for hospital. ", len(all_hospitals))
for hospital in all_hospitals:
if hospital.location:
success = EntityLocationRelationship.create(
latitude=hospital.location.y,
longitude=hospital.location.x,
content_object=hospital)
if success:
print("Successfull for hospital id ", hospital.id)
else:
print("Failed for hospital id ", hospital.id)
def update_seo_urls():
from ondoc.doctor.models import Doctor, Hospital
from ondoc.diagnostic.models import Lab
from ondoc.procedure.models import IpdProcedure
from ondoc.api.v1.utils import RawSql
# update doctor seo urls
Doctor.update_doctors_seo_urls()
# update hospital seo urls
Hospital.update_hospital_seo_urls()
# update lab seo urls()
# Lab.update_labs_seo_urls()
# update ipd_procedure urls
IpdProcedure.update_ipd_seo_urls()
# update labs, doctors and hospitals profile urls
from ondoc.location.models import UrlsModel
UrlsModel.update_profile_urls()
# Truncate temp_url table
RawSql('truncate table temp_url', []).execute()
return True
def create_hosp_search_urls_new(self):
hosp_search_query = ''' select hospital_id, sublocality_value,
locality_value, count, entity_type, url_type, is_valid, bookable_doctors_count from (
select ROW_NUMBER() OVER (PARTITION BY hospital_id) AS row_num, hospital_id, sublocality_value,
locality_value, count, entity_type, url_type, is_valid,
json_build_object('bookable_doctors_count',bookable_doctors_count,'bookable_doctors_2km',bookable_doctors_2km)
as bookable_doctors_count
from (
select max(h.id) as hospital_id, max(h.locality) as sublocality_value, max(h.city) as locality_value, count(distinct d.id) count,
COUNT(DISTINCT CASE WHEN d.enabled_for_online_booking=True and dc.enabled_for_online_booking=True
and h.enabled_for_online_booking=True then d.id else null END) as bookable_doctors_count,
COUNT(DISTINCT CASE WHEN d.enabled_for_online_booking=True and dc.enabled_for_online_booking=True
and h.enabled_for_online_booking=True
and ST_DWithin(ea.centroid::geography,h.location::geography,2000)then d.id else null end) as bookable_doctors_2km,
'Hospital' as entity_type, 'SEARCHURL' url_type, True as is_valid
from entity_address ea inner join hospital h on ((ea.type = 'LOCALITY' and lower(h.city)=lower(ea.alternative_value) and
ST_DWithin(ea.centroid::geography,h.location::geography,h.search_url_locality_radius)) OR
(ea.type = 'SUBLOCALITY' and lower(h.locality)=lower(ea.alternative_value) and
ST_DWithin(ea.centroid::geography,h.location::geography,h.search_url_sublocality_radius))) and h.is_live=true
and ea.type IN ('SUBLOCALITY' , 'LOCALITY') and ea.use_in_url=true
inner join doctor_clinic dc on dc.hospital_id = h.id
and dc.enabled=true inner join doctor d on dc.doctor_id= d.id and d.is_live=true
where ea.id>=%d and ea.id<%d
group by ea.id)a )b where row_num=1'''
start = self.min_ea
to_create = []
while start < self.max_ea:
query = hosp_search_query % (start, start + self.step)
hosp_data = RawSql(query, []).fetch_all()
if hosp_data:
hospital_ids = (data.get('hospital_id') for data in hosp_data)
hosp_loc_obj = Hospital.objects.filter(
id__in=hospital_ids).values('id', 'location')
hosp_loc_dict = dict()
for data in hosp_loc_obj:
if not hosp_loc_dict.get(data.get('id')):
hosp_loc_dict[data.get('id')] = data.get('location')
for data in hosp_data:
location = hosp_loc_dict[data.get(
'hospital_id')] if hosp_loc_dict.get(
data.get('hospital_id')) else None
if location:
url_data = {}
url_data['is_valid'] = data.get('is_valid')
url_data['url_type'] = data.get('url_type')
url_data['entity_type'] = data.get('entity_type')
url_data['count'] = data.get('count')
url_data['bookable_doctors_count'] = data.get(
'bookable_doctors_count')
url_data['sitemap_identifier'] = 'HOSPITALS_CITY'
url_data['locality_latitude'] = location.y
url_data['locality_longitude'] = location.x
url_data['location'] = location
url_data['locality_location'] = location
url_data['locality_value'] = data.get('locality_value')
url_data['url'] = slugify('hospitals-in-' +
data.get('locality_value') +
'-hspcit')
to_create.append(TempURL(**url_data))
url_data[
'sitemap_identifier'] = 'HOSPITALS_LOCALITY_CITY'
url_data['sublocality_value'] = data.get(
'sublocality_value')
url_data['sublocality_latitude'] = location.y
url_data['sublocality_longitude'] = location.x
url_data['sublocality_location'] = location
url_data['url'] = slugify(
'hospitals-in-' + data.get('sublocality_value') +
'-' + data.get('locality_value') + '-hsplitcit')
to_create.append(TempURL(**url_data))
start = start + self.step
TempURL.objects.bulk_create(to_create)
update_extras_query = '''update temp_url
set extras = case when sitemap_identifier='HOSPITALS_CITY' then
json_build_object('location_json',json_build_object('locality_id',locality_id,'locality_value',locality_value,
'locality_latitude',locality_latitude,'locality_longitude',locality_longitude))
else json_build_object('location_json',
json_build_object('sublocality_id', sublocality_id,'sublocality_value', sublocality_value,
'locality_id', locality_id, 'locality_value', locality_value,'breadcrum_url',slugify_url('hospitals-in-' || locality_value ||'-hspcit'),
'sublocality_latitude',sublocality_latitude, 'sublocality_longitude',sublocality_longitude, 'locality_latitude',locality_latitude,
'locality_longitude',locality_longitude)) end
'''
update_extras = RawSql(update_extras_query, []).execute()
# clean up duplicate urls
RawSql(
'''delete from temp_url where id in (select id from
(select eu.*, row_number() over(partition by url ) rownum from temp_url eu
)x where rownum>1
) ''', []).execute()
return 'success'
def create_doctor_score(self):
RawSql(
'''update doctor d set search_score=(select final_score from search_score ss where ss.doctor_id=d.id)''',
[]).execute()
return "success"
def delete_search_score(self):
RawSql('''delete from search_score''', []).execute()
return "success"
def list(self, request):
type = request.query_params.get('type')
if type not in ('doctor','lab'):
type = None
latitude = request.query_params.get('latitude')
longitude = request.query_params.get('longitude')
starts_with = request.query_params.get('starts_with')
search = request.query_params.get('search')
if (not type or not latitude or not longitude) or \
(not starts_with and type == 'doctor'):
return Response({'count':0,'total_count':0, 'results':[]})
if not starts_with:
params = dict()
params['latitude'] = latitude
params['longitude'] = longitude
result = list()
labs_query = '''select l.network_id,l.name, 'lab' as type,eu.url,l.city,l.id,
st_distance(l.location,st_setsrid(st_point((%(longitude)s),(%(latitude)s)), 4326))/1000 distance
from lab_network ln inner join lab l on l.network_id = ln.id
inner join entity_urls eu on l.id = eu.entity_id and eu.sitemap_identifier='LAB_PAGE' and eu.is_valid=true
where l.is_live=true and l.is_test_lab=false and ln.id in (43, 18, 65, 22) and St_dwithin( St_setsrid(St_point((%(longitude)s), (%(latitude)s)), 4326),l.location, 15000)
order by ST_Distance(l.location, St_setsrid(St_point((%(longitude)s), (%(latitude)s)), 4326)) '''
labs = RawSql(labs_query, params).fetch_all()
temp_dict = set()
for lab in labs:
if len(temp_dict) == 4:
break
if not lab.get('network_id') in temp_dict:
result.append(lab)
temp_dict.add(lab.get('network_id'))
total_count_query= "select count(distinct entity_id) from insurance_covered_entity where type= %(type)s"
total_count = RawSql(total_count_query, {'type':type}).fetch_all()[0].get('count')
data_list = []
for r in result:
data_list.append(
{'name': r.get('name'), 'distance': math.ceil(r.get('distance')), 'id': r.get('id'), \
'type': r.get('type'), 'url': r.get('url'), 'city': r.get('city')})
resp = dict()
resp["starts_with"] = None
resp["count"] = len(result)
resp["total_count"] = total_count
resp["distance_count"] = len(result)
resp["results"] = data_list
return Response(resp)
else:
starts_with = starts_with.lower()
params = {'type':type,'latitude':latitude,'longitude':longitude,'starts_with':starts_with+'%'}
query_string = "select * from (select x.*, rank() over(partition by entity_id order by distance) "\
" rnk from (select mt.*, st_distance(location,st_setsrid(st_point((%(longitude)s),(%(latitude)s)), 4326))/1000 "\
" distance from insurance_covered_entity mt where type=(%(type)s) and "
if type =='doctor' and search == 'specialization':
params['comma_separated_starts_with'] = '%,' + starts_with+'%'
query_string += " ((specialization_search_key like (%(starts_with)s)) or (specialization_search_key like (%(comma_separated_starts_with)s) )) and "
# query_string += " ((specialization_search_key like (%(starts_with)s)) or (specialization_search_key like concat('%,',(%(starts_with)s))) ) and "
else:
query_string += ' search_key like %(starts_with)s and '
query_string += " st_dwithin(location,st_setsrid(st_point((%(longitude)s),(%(latitude)s)), 4326),15000) "\
" )x )y where rnk=1 order by distance"
results = RawSql(query_string, params).fetch_all()
distance_count_query = "select count(distinct entity_id) from insurance_covered_entity where type= %(type)s "\
" and st_dwithin(location,st_setsrid(st_point((%(longitude)s),(%(latitude)s)), 4326),15000)"
distance_count = RawSql(distance_count_query, {'type':type,'latitude':latitude,'longitude':longitude}).fetch_all()[0].get('count')
total_count_query= "select count(distinct entity_id) from insurance_covered_entity where type= %(type)s"
total_count = RawSql(total_count_query, {'type':type}).fetch_all()[0].get('count')
data_list = []
for r in results:
data_list.append({'name':r.get('name'), 'distance':math.ceil(r.get('distance')), 'id':r.get('entity_id'),\
'type':r.get('type'), 'city':r.get('data',{}).get('city'),'url':r.get('data',{}).get('url'),\
'specializations':r.get('data',{}).get('specializations')})
resp = dict()
resp["starts_with"] = starts_with
resp["count"] = len(data_list)
resp["total_count"] = total_count
resp["distance_count"] = distance_count
resp["results"] = data_list
return Response(resp)
def insert_search_urls(self):
seq = self.sequence
insert_page_urls_query = '''insert into entity_urls(sequence,extras, sitemap_identifier, url, count, entity_type,
url_type, created_at,
updated_at, sublocality_latitude, sublocality_longitude, locality_latitude,
locality_longitude, locality_id, sublocality_id,
locality_value, sublocality_value, is_valid, locality_location, sublocality_location, location, entity_id,
specialization_id, specialization, breadcrumb, bookable_doctors_count)
select %d as sequence ,a.extras, a.sitemap_identifier,getslug(a.url) as url, a.count, a.entity_type,
a.url_type, now() as created_at, now() as updated_at,
a.sublocality_latitude, a.sublocality_longitude, a.locality_latitude, a.locality_longitude,
a.locality_id, a.sublocality_id, a.locality_value, a.sublocality_value, a.is_valid,
a.locality_location, a.sublocality_location, a.location, entity_id,
specialization_id, specialization, breadcrumb, bookable_doctors_count from temp_url a
where sitemap_identifier='HOSPITAL_PAGE' and (a.entity_id not in (57640, 40669, 4918, 19239, 57604, 3429, 3751, 3063, 56822, 3496, 3513, 3898, 55231,
56841, 56840, 4160, 30525, 24401, 2142, 3465, 38362, 2337, 56620, 56621, 27536, 31211, 56618, 33188, 56835,
56827, 56829, 31369, 56832, 56826, 33327, 3627, 3191, 5999, 56848, 6170, 5423, 14713, 3293, 56849, 56850,
56851, 56853, 6586, 57641, 19303, 23181, 8584, 1297, 57596, 22430, 18489, 55377, 32282, 57610, 56856,
56859, 56860, 3240, 52779, 2348, 2703, 227, 3068, 5347, 3380, 1980, 3560, 56071, 2861, 57639 )) ''' % seq
# where ((a.sitemap_identifier='HOSPITAL_PAGE' and a.entity_id not in (select entity_id from entity_urls where
# sitemap_identifier='HOSPITAL_PAGE' and is_valid=True)) OR (a.sitemap_identifier!='HOSPITAL_PAGE' ))
# ''' %seq
insert_search_urls_query = '''insert into entity_urls(sequence,extras, sitemap_identifier, url, count, entity_type,
url_type, created_at,
updated_at, sublocality_latitude, sublocality_longitude, locality_latitude,
locality_longitude, locality_id, sublocality_id,
locality_value, sublocality_value, is_valid, locality_location, sublocality_location, location, entity_id,
specialization_id, specialization, breadcrumb, bookable_doctors_count)
select %d as sequence ,a.extras, a.sitemap_identifier,getslug(a.url) as url, a.count, a.entity_type,
a.url_type, now() as created_at, now() as updated_at,
a.sublocality_latitude, a.sublocality_longitude, a.locality_latitude, a.locality_longitude,
a.locality_id, a.sublocality_id, a.locality_value, a.sublocality_value, a.is_valid,
a.locality_location, a.sublocality_location, a.location, entity_id,
specialization_id, specialization, breadcrumb, bookable_doctors_count from temp_url a
where sitemap_identifier in ('HOSPITALS_LOCALITY_CITY', 'HOSPITALS_CITY') ''' % seq
# update_seq_query = '''update entity_urls set sequence = %d where sitemap_identifier = 'HOSPITAL_PAGE' and
# is_valid = True''' % seq
update_query = '''update entity_urls set is_valid=false where sitemap_identifier
in ('HOSPITALS_LOCALITY_CITY', 'HOSPITALS_CITY', 'HOSPITAL_PAGE') and sequence< %d
and entity_id not in (57640, 40669, 4918, 19239, 57604, 3429, 3751, 3063, 56822, 3496, 3513, 3898, 55231,
56841, 56840, 4160, 30525, 24401, 2142, 3465, 38362, 2337, 56620, 56621, 27536, 31211, 56618, 33188, 56835,
56827, 56829, 31369, 56832, 56826, 33327, 3627, 3191, 5999, 56848, 6170, 5423, 14713, 3293, 56849, 56850,
56851, 56853, 6586, 57641, 19303, 23181, 8584, 1297, 57596, 22430, 18489, 55377, 32282, 57610, 56856,
56859, 56860, 3240, 52779, 2348, 2703, 227, 3068, 5347, 3380, 1980, 3560, 56071, 2861, 57639) ''' % seq
cleanup = '''delete from entity_urls where id in (select id from
(select eu.*, row_number() over(partition by url order by is_valid desc, sequence desc) rownum from entity_urls eu
)x where rownum>1
) '''
update_duplicate_entities = ''' update entity_urls set is_valid=false where id in ( select id from
(select eu.*, row_number() over(partition by entity_id order by is_valid desc, sequence desc )
rownum from entity_urls eu where sitemap_identifier='HOSPITAL_PAGE')x
where sitemap_identifier='HOSPITAL_PAGE' and rownum >1) '''
# for urls which we are not creating over here
update_locality_value = ''' update entity_urls set locality_value=(select city from hospital h
where h.id=entity_urls.entity_id
and entity_urls.sitemap_identifier='HOSPITAL_PAGE'
and entity_urls.is_valid=True) where sitemap_identifier='HOSPITAL_PAGE' and entity_id in (57640, 40669, 4918, 19239, 57604, 3429, 3751, 3063, 56822, 3496, 3513, 3898, 55231,
56841, 56840, 4160, 30525, 24401, 2142, 3465, 38362, 2337, 56620, 56621, 27536, 31211, 56618, 33188, 56835,
56827, 56829, 31369, 56832, 56826, 33327, 3627, 3191, 5999, 56848, 6170, 5423, 14713, 3293, 56849, 56850,
56851, 56853, 6586, 57641, 19303, 23181, 8584, 1297, 57596, 22430, 18489, 55377, 32282, 57610, 56856,
56859, 56860, 3240, 52779, 2348, 2703, 227, 3068, 5347, 3380, 1980, 3560, 56071, 2861, 57639) '''
update_sublocality_value = ''' update entity_urls set sublocality_value=(select locality from hospital h
where h.id=entity_urls.entity_id
and entity_urls.sitemap_identifier='HOSPITAL_PAGE'
and entity_urls.is_valid=True) where sitemap_identifier='HOSPITAL_PAGE' and entity_id in (57640, 40669, 4918, 19239, 57604, 3429, 3751, 3063, 56822, 3496, 3513, 3898, 55231,
56841, 56840, 4160, 30525, 24401, 2142, 3465, 38362, 2337, 56620, 56621, 27536, 31211, 56618, 33188, 56835,
56827, 56829, 31369, 56832, 56826, 33327, 3627, 3191, 5999, 56848, 6170, 5423, 14713, 3293, 56849, 56850,
56851, 56853, 6586, 57641, 19303, 23181, 8584, 1297, 57596, 22430, 18489, 55377, 32282, 57610, 56856,
56859, 56860, 3240, 52779, 2348, 2703, 227, 3068, 5347, 3380, 1980, 3560, 56071, 2861, 57639) '''
RawSql(insert_page_urls_query, []).execute()
RawSql(insert_search_urls_query, []).execute()
# RawSql(update_seq_query, []).execute()
RawSql(update_query, []).execute()
RawSql(cleanup, []).execute()
RawSql(update_duplicate_entities, []).execute()
RawSql(update_locality_value, []).execute()
RawSql(update_sublocality_value, []).execute()
return True
def create_hosp_search_urls(self):
q1 = '''insert into temp_url (search_slug, count, sublocality_id, locality_id,
sitemap_identifier, entity_type, url_type, is_valid, created_at, updated_at, bookable_doctors_count )
select search_slug, count, sublocality_id,locality_id,sitemap_identifier,
entity_type, url_type, is_valid, now(), now(),
json_build_object('bookable_doctors_count',bookable_doctors_count,'bookable_doctors_2km',bookable_doctors_2km)
as bookable_doctors_count
from (
select search_slug, count(distinct d.id) count,
COUNT(DISTINCT CASE WHEN d.enabled_for_online_booking=True and dc.enabled_for_online_booking=True
and h.enabled_for_online_booking=True then d.id else null END) as bookable_doctors_count,
COUNT(DISTINCT CASE WHEN d.enabled_for_online_booking=True and dc.enabled_for_online_booking=True
and h.enabled_for_online_booking=True
and ST_DWithin(ea.centroid::geography,h.location::geography,2000)then d.id else null end) as bookable_doctors_2km,
case when ea.type = 'SUBLOCALITY' then ea.id end as sublocality_id,
case when ea.type = 'LOCALITY' then ea.id end as locality_id,
case when ea.type = 'LOCALITY' then 'HOSPITALS_CITY' else 'HOSPITALS_LOCALITY_CITY' end as sitemap_identifier,
'Hospital' as entity_type, 'SEARCHURL' url_type, True as is_valid, now(), now()
from entity_address ea inner join hospital h on ((ea.type = 'LOCALITY' and
ST_DWithin(ea.centroid::geography,h.location::geography,15000)) OR
(ea.type = 'SUBLOCALITY' and ST_DWithin(ea.centroid::geography,h.location::geography,5000))) and h.is_live=true
and ea.type IN ('SUBLOCALITY' , 'LOCALITY') and ea.use_in_url=true
inner join doctor_clinic dc on dc.hospital_id = h.id
and dc.enabled=true inner join doctor d on dc.doctor_id= d.id and d.is_live=true
where ea.id>=%d and ea.id<%d
group by ea.id)a '''
start = self.min_ea
while start < self.max_ea:
query = q1 % (start, start + self.step)
RawSql(query, []).execute()
start = start + self.step
RawSql(
'''update temp_url tu set locality_id = (select parent_id from entity_address where id = tu.sublocality_id)
where locality_id is null and sublocality_id is not null''',
[]).execute()
RawSql('''delete from temp_url where locality_id is null''',
[]).execute()
RawSql(
'''delete from temp_url where locality_id in (select id from entity_address where use_in_url is false)''',
[]).execute()
RawSql(
'''delete from temp_url where sublocality_id in (select id from entity_address where use_in_url is false)''',
[]).execute()
RawSql(
'''UPDATE temp_url
SET locality_latitude = st_y(centroid::geometry), locality_longitude = st_x(centroid::geometry),
locality_value = ea.alternative_value, locality_location = centroid
FROM entity_address ea
WHERE locality_id = ea.id ''', []).execute()
RawSql(
'''UPDATE temp_url
SET sublocality_latitude = st_y(centroid::geometry), sublocality_longitude = st_x(centroid::geometry),
sublocality_value = ea.alternative_value, sublocality_location = centroid
FROM entity_address ea
WHERE sublocality_id = ea.id
''', []).execute()
RawSql(
'''update temp_url set location = sublocality_location where sublocality_location is not null''',
[]).execute()
RawSql(
'''update temp_url set location = locality_location where location is null''',
[]).execute()
update_urls_query = '''update temp_url set url = case when sitemap_identifier = 'HOSPITALS_LOCALITY_CITY' then
slugify_url(concat('hospitals-in-', search_slug, '-hsplitcit'))
else slugify_url(concat('hospitals-in-', search_slug, '-hspcit'))
end '''
update_urls = RawSql(update_urls_query, []).execute()
update_extras_query = '''update temp_url
set extras = case when sitemap_identifier='HOSPITALS_CITY' then
json_build_object('location_json',json_build_object('locality_id',locality_id,'locality_value',locality_value,
'locality_latitude',locality_latitude,'locality_longitude',locality_longitude))
else json_build_object('location_json',
json_build_object('sublocality_id', sublocality_id,'sublocality_value', sublocality_value,
'locality_id', locality_id, 'locality_value', locality_value,'breadcrum_url',slugify_url('hospitals-in-' || locality_value ||'-hspcit'),
'sublocality_latitude',sublocality_latitude, 'sublocality_longitude',sublocality_longitude, 'locality_latitude',locality_latitude,
'locality_longitude',locality_longitude)) end
'''
update_extras = RawSql(update_extras_query, []).execute()
# clean up duplicate urls
RawSql(
'''delete from temp_url where id in (select id from (select tu.*,
row_number() over(partition by tu.url order by ea.child_count desc nulls last, tu.count desc nulls last) rownum
from temp_url tu inner join entity_address ea on
case when tu.sublocality_id is not null then tu.sublocality_id else tu.locality_id end = ea.id
)x where rownum>1)
''', []).execute()
return 'success'
