def get_finance_car_series_county_info(city_id=None, car_series_id=None):
"""
根据城市ID和车系ID返回销售对应车系的经销商所在的
每个县区的经销商数量和信息
参数
----
city_id : int
城市ID
car_series_id : int
车系ID
返回值
------
result_dict : dict
返回每个县区的经销商数量和信息，还有所有符合条件的经销商总数
"""
city_id = common_utils.to_int(city_id)
car_series_id = common_utils.to_int(car_series_id)
dealer_id_list = dealer_utils.get_dealer_id_list(
city_id=city_id, car_series_id=car_series_id)
if not dealer_id_list:
return None
price_sql_template = '''
SELECT
b.county_id AS county_id, b.county_name,
count(a.id) AS dealer_count
FROM
t_base_dealer AS a,
t_base_county AS b
WHERE
a.id in ({{dealer_id_list}})
AND a.county_id=b.county_id
AND a.is_enable=1
AND a.is_frozen=0
AND b.is_enable=1
AND b.is_show=1
GROUP BY
a.county_id
ORDER BY
a.county_id
'''
price_param = {
'dealer_id_list': common_utils.int_list_to_str(dealer_id_list),
}
price_sql = Template(price_sql_template).render(price_param)
cursor = connection().cursor()
cursor.execute(price_sql, price_param)
county_list = dictfetchall(cursor)
if not county_list:
return None
result_dict = {
'county_list': county_list,
}
county_count = 0
for county in county_list:
county_count += county['dealer_count']
result_dict['county_count'] = county_count
return result_dict
def get_finance_county_brand_dealer(county_id=None,
car_brand_id=None,
per_page=10,
page=1):
"""
根据县区ID和品牌ID返回经销商信息
先按VIP排序再按评分排序
参数
----
county_id : int
县区ID
car_brand_id : int
品牌ID
per_page : int
当前页返回的数据数量
page : int
页码
返回值
------
result_list : list
返回经销商数组
"""
(offset, count) = common_utils.offset_count(per_page=per_page, page=page)
county_id = common_utils.to_int(county_id)
car_brand_id = common_utils.to_int(car_brand_id)
dealer_id_list = dealer_utils.get_dealer_id_list(county_id=county_id,
car_brand_id=car_brand_id)
if not dealer_id_list:
return None
dealer_sql_template = '''
SELECT
id, (pre_sales_score+after_sales_score)/2 as sales_score
FROM
t_base_dealer
WHERE
is_enable=1
AND is_frozen=0
AND id in ({{dealer_id_list}})
ORDER BY
is_vip desc, sales_score desc
LIMIT
%(offset)s, %(count)s
'''
param = {
'dealer_id_list': common_utils.int_list_to_str(dealer_id_list),
'offset': offset,
'count': count,
}
dealer_sql = Template(dealer_sql_template).render(param)
cursor = connection().cursor()
cursor.execute(dealer_sql, param)
dealers = dictfetchall(cursor)
result_list = []
for dealer in dealers:
dealer = dealer_utils.get_dealer_by_id(dealer_id=dealer['id'])
result_list.append(dealer)
return result_list
def get_city_car_type_dealer_count(city_id=None, car_type_id=None):
"""
PC端车型页面
返回对应城市ID和车型ID在各个县区的经销商的数量
参数
----
city_id : int
城市ID
car_type_id : int
车型ID
返回值
------
result_list : list
返回县区的ID，名字和各个县区的经销商数量
"""
city_id = common_utils.to_int(city_id)
car_type_id = common_utils.to_int(car_type_id)
car_type = car_type_utils.get_cartype_by_id(car_type_id=car_type_id)
if not car_type:
return None
dealer_id_list = dealer_utils.get_dealer_id_list(
city_id=city_id, car_series_id=car_type['car_series_id'])
if not dealer_id_list:
return None
price_sql_template = '''
SELECT
b.county_id AS county_id, b.county_name,
count(a.id) AS dealer_count
FROM
t_base_dealer AS a,
t_base_county AS b
WHERE
a.county_id=b.county_id
AND a.is_enable=1
AND a.is_frozen=0
AND b.is_enable=1
AND b.is_show=1
AND a.id in ({{dealer_id_list}})
GROUP BY
a.county_id
'''
price_param = {
'dealer_id_list': common_utils.int_list_to_str(dealer_id_list),
}
price_sql = Template(price_sql_template).render(price_param)
cursor = connection().cursor()
cursor.execute(price_sql, price_param)
price_list = dictfetchall(cursor)
if not price_list:
return None
return price_list
def get_finance_dealer_county(city_id=None):
"""
通过城市ID获取所有包含经销商的县区的数据
参数
----
city_id : int
城市ID
返回值
------
result_list : list
所有县区的数据的数组
"""
city_id = common_utils.to_int(city_id)
dealer_id_list = dealer_utils.get_dealer_id_list(city_id=city_id)
if not dealer_id_list:
return None
dealer_sql_template = '''
SELECT
DISTINCT county_id
FROM
t_base_dealer
WHERE
is_enable=1
AND is_frozen=0
AND id in ({{dealer_id_list}})
ORDER BY
county_id
'''
param = {
'dealer_id_list': common_utils.int_list_to_str(dealer_id_list),
}
dealer_sql = Template(dealer_sql_template).render(param)
cursor = connection().cursor()
cursor.execute(dealer_sql, param)
dealers = dictfetchall(cursor)
if not dealers:
return None
result_list = []
for dealer in dealers:
county_info = place_utils.get_county_by_id(
county_id=dealer['county_id'])
if county_info:
result_list.append(county_info)
return result_list
def get_offerprice_by_series_city(car_series_id=None, city_id=None):
car_series_id = common_utils.to_int(car_series_id)
city_id = common_utils.to_int(city_id)
try:
offer_price = list_objs(
model=ModelName.T_BASE_OFFER_PRICE,
orderby=['public_offer_price', '-created_date'],
is_enable=1,
car_series_id=car_series_id,
city_id=city_id)[0]
except IndexError:
dealer_id_list = dealer_utils.get_dealer_id_list(
city_id=city_id, car_series_id=car_series_id)
if not dealer_id_list:
return get_offerprice_none_series(car_series_id=car_series_id)
return get_offerprice_by_series_dealer_list(
car_series_id=car_series_id, dealer_id_list=dealer_id_list)
offer_price_dict = get_offerprice_by_model(offer_price=offer_price)
return offer_price_dict
def get_finance_car_type_dealer(city_id=None,
county_id=None,
car_type_id=None,
per_page=10,
page=1):
"""
任性贷车型页面，PC和WAP通用
根据城市ID、县区ID和车型ID返回车型的经销商和报价
排序按 堡垒店-》价格
如果当前城市没有经销商，就返回最近的售全省的经销商的报价
参数
----
city_id : int
城市ID
county_id : int, 可选
县区ID
car_type_id : int
车型ID
per_page : int
当前页返回的数据数量
page : int
页码
返回值
------
result_list : list
返回车型的经销商和报价
"""
(offset, count) = common_utils.offset_count(per_page=per_page, page=page)
city_id = common_utils.to_int(city_id)
county_id = common_utils.to_int(county_id)
car_type_id = common_utils.to_int(car_type_id)
car_type = car_type_utils.get_cartype_by_id(car_type_id=car_type_id)
if not car_type:
return None
dealer_id_list = dealer_utils.get_dealer_id_list(
city_id=city_id,
county_id=county_id,
car_series_id=car_type['car_series_id'])
if not dealer_id_list:
return None
result_list = []
for dealer_id in dealer_id_list:
tmp_dealer = dealer_utils.get_dealer_by_id(dealer_id=dealer_id)
if not tmp_dealer:
continue
tmp_offer_price = offer_price_utils.get_offerprice(
car_type_id=car_type_id, dealer_id=dealer_id)
car_type_dict = {
'dealer': tmp_dealer,
'offer_price': tmp_offer_price,
}
result_list.append(car_type_dict)
if result_list:
result_list = sorted(result_list,
key=lambda tmp_car_type:
(-tmp_car_type['dealer']['is_vip'], tmp_car_type[
'offer_price']['public_offer_price']))
result_list = result_list[offset:(offset + count)]
return result_list
def get_car_series_dealer(city_id=None,
county_id=None,
car_series_id=None,
per_page=10,
page=1,
orderby='price',
descending=False):
"""
WAP端车系页面
返回对应城市、县区和车系的经销商
参数
----
city_id : int
城市ID
county_id : int, 可选
县区ID
car_series_id : int
车系ID
per_page : int
当前页返回的数据数量
page : int
页码
orderby : {'price', 'discount'}
price按报价排序，discount按优惠排序
descending : {0, 1}
0按升序，1按降序
返回值
------
result_list : list
返回经销商数组，包括对应车系的促销活动
"""
(offset, count) = common_utils.offset_count(per_page=per_page, page=page)
city_id = common_utils.to_int(city_id)
county_id = common_utils.to_int(county_id)
car_series_id = common_utils.to_int(car_series_id)
orderby_price = True
if orderby == 'discount':
orderby_price = False
else:
orderby_price = True
if descending:
if isinstance(descending, str) and (descending.upper() != 'TRUE'):
descending = False
dealer_id_list = dealer_utils.get_dealer_id_list(
city_id=city_id, county_id=county_id, car_series_id=car_series_id)
if not dealer_id_list:
return None
result_list = []
for dealer_id in dealer_id_list:
tmp_dealer = dealer_utils.get_dealer_by_id(dealer_id=dealer_id)
if not tmp_dealer:
continue
tmp_dealer['offer_price'] = offer_price_utils.get_offerprice(
city_id=city_id,
dealer_id=tmp_dealer['id'],
car_series_id=car_series_id)
activity_list = activity_utils.get_activity_list_by_dealer_series(
dealer_id=tmp_dealer['id'], car_series_id=car_series_id)
tmp_dealer['activity_list'] = activity_list
result_list.append(tmp_dealer)
if result_list:
result_list = offer_price_utils.sorted_offer_price_list(
offer_price_list=result_list,
orderby_price=orderby_price,
descending=descending)
result_list = result_list[offset:(offset + count)]
return result_list
def utils_dealer_city_series_types(city_id=None, car_series_id=None, count=2):
"""
根据城市ID和车系ID返回当前城市的经销商
排序按照 堡垒店-》评分
"""
city_id = common_utils.to_int(city_id)
car_series_id = common_utils.to_int(car_series_id)
count = common_utils.max_int(count)
dealer_id_list = dealer_utils.get_dealer_id_list(
city_id=city_id, car_series_id=car_series_id)
if not dealer_id_list:
return None
dealer_sql_template = '''
SELECT
id, (pre_sales_score+after_sales_score)/2 as sales_score
FROM
t_base_dealer
WHERE
is_enable=1
AND is_frozen=0
AND id in ({{dealer_id_list}})
ORDER BY
is_vip desc, sales_score desc
LIMIT
%(count)s
'''
param = {
'dealer_id_list': common_utils.int_list_to_str(dealer_id_list),
'count': count,
}
dealer_sql = Template(dealer_sql_template).render(param)
cursor = connection().cursor()
cursor.execute(dealer_sql, param)
dealers = dictfetchall(cursor)
dealer_list = []
for tmp_dealer in dealers:
tmp_dealer = dealer_utils.get_dealer_by_id(dealer_id=tmp_dealer['id'])
# 获取当前经销商的在售车型和报价
price_sql_template = '''
SELECT
id, car_type_id
FROM
t_base_offer_price
WHERE
dealer_id=%(dealer_id)s
AND car_series_id=%(car_series_id)s
AND is_enable = 1
ORDER BY
public_offer_price
LIMIT
%(count)s
'''
param = {
'dealer_id': tmp_dealer['id'],
'car_series_id': car_series_id,
'count': 4,
}
price_sql = Template(price_sql_template).render(param)
cursor = connection().cursor()
cursor.execute(price_sql, param)
price_list = dictfetchall(cursor)
car_type_list = []
for price in price_list:
car_type = car_type_utils.get_cartype_by_id(
car_type_id=price['car_type_id'])
if not car_type:
continue
offer_price = offer_price_utils.get_offerprice_by_id(
offer_price_id=price['id'])
if not offer_price:
continue
car_type['offer_price'] = offer_price
car_type_list.append(car_type)
tmp_dealer['car_type_list'] = car_type_list
dealer_list.append(tmp_dealer)
return dealer_list
def get_dealer_by_default(city_id=None,
county_id=None,
brand_id=None,
series_id=None,
per_page=10,
page=1,
newest_activity=None):
"""
PC端找好店首页
WAP端找好店首页-默认
先按VIP排序再按评分排序
参数
----
city_id : int
城市ID
county_id : int, 可选
县区ID
brand_id : int, 可选
品牌ID
series_id : int, 可选
车系ID
per_page : int
当前页返回的数据数量
page : int
页码
newest_activity : int
是否返回最新促销活动数组 activity_list，1返回，0不返回。
会覆盖车系ID对应的促销活动数组 activity_list
返回值
------
result_dict : dict
返回当前城市的经销商数量和过滤后的经销商数量和经销商数组
"""
(offset, count) = common_utils.offset_count(per_page=per_page, page=page)
city_id = common_utils.to_int(city_id)
county_id = common_utils.to_int(county_id)
brand_id = common_utils.to_int(brand_id)
series_id = common_utils.to_int(series_id)
newest_activity = common_utils.to_int(newest_activity)
dealer_id_list = dealer_utils.get_dealer_id_list(city_id=city_id,
county_id=county_id,
car_brand_id=brand_id,
car_series_id=series_id)
if not dealer_id_list:
return None
dealer_sql_template = '''
SELECT
id, (pre_sales_score+after_sales_score)/2 as sales_score
FROM
t_base_dealer
WHERE
is_enable=1
AND is_frozen=0
AND id in ({{dealer_id_list}})
ORDER BY
is_vip desc, sales_score desc
LIMIT
%(offset)s, %(count)s
'''
param = {
'dealer_id_list': common_utils.int_list_to_str(dealer_id_list),
'offset': offset,
'count': count,
}
dealer_sql = Template(dealer_sql_template).render(param)
cursor = connection().cursor()
cursor.execute(dealer_sql, param)
dealers = dictfetchall(cursor)
dealer_list = []
for dealer in dealers:
dealer = dealer_utils.get_dealer_by_id(dealer_id=dealer['id'])
if newest_activity:
tmp_activity_list = activity_utils.get_activity_list_by_dealer(
dealer_id=dealer['id'],
activity_type=1,
)
dealer['activity_list'] = tmp_activity_list
elif series_id:
activity_list = activity_utils.get_activity_list_by_dealer_series(
dealer_id=dealer['id'], car_series_id=series_id)
dealer['activity_list'] = activity_list
dealer_list.append(dealer)
filter_count = len(dealer_id_list)
# city count
city_count = dealer_utils.get_dealer_count(city_id=city_id)
result_dict = {
'city_count': city_count,
'filter_count': filter_count,
'dealer_list': dealer_list,
'page_count': int((filter_count + count - 1) / count),
}
# filter car series
if series_id:
result_dict['car_series_list'] = [
car_series_utils.get_carseries_by_id(car_series_id=series_id)
]
else:
all_dealer_list = dealer_utils.get_dealer_list_by_id_list(
dealer_id_list=dealer_id_list)
tmp_dict = {}
for tmp_dealer in all_dealer_list:
if tmp_dealer['car_series_ids']:
for tmp_car_series_id in tmp_dealer['car_series_ids']:
tmp_dict[tmp_car_series_id] = 1
tmp_car_series_id_list = tmp_dict.keys()
car_series_list = []
for tmp_car_series_id in tmp_car_series_id_list:
car_series_info = car_series_utils.get_carseries_by_id(
car_series_id=tmp_car_series_id)
if car_series_info:
if brand_id:
if car_series_info['car_brand_id'] == brand_id:
car_series_list.append(car_series_info)
else:
car_series_list.append(car_series_info)
if car_series_list:
car_series_list = sorted(
car_series_list,
key=lambda tmp_car_series: tmp_car_series['order_no'])
result_dict['car_series_list'] = car_series_list
return result_dict
def get_dealer_by_distance(city_id=None,
county_id=None,
brand_id=None,
series_id=None,
longitude=None,
latitude=None,
per_page=10,
page=1,
newest_activity=None):
"""
WAP端找好店首页-离我最近
按经纬度距离排序
参数
----
city_id : int
城市ID
county_id : int, 可选
县区ID
brand_id : int, 可选
品牌ID
series_id : int, 可选
车系ID
longitude : float
经度
latitude : float
纬度
per_page : int
当前页返回的数据数量
page : int
页码
newest_activity : int
是否返回最新促销活动数组 activity_list，1返回，0不返回。
会覆盖车系ID对应的促销活动数组 activity_list
返回值
------
result_dict : dict
返回当前城市的经销商数量和过滤后的经销商数量和经销商数组
"""
(offset, count) = common_utils.offset_count(per_page=per_page, page=page)
city_id = common_utils.to_int(city_id)
county_id = common_utils.to_int(county_id)
brand_id = common_utils.to_int(brand_id)
series_id = common_utils.to_int(series_id)
longitude = common_utils.to_float(longitude)
latitude = common_utils.to_float(latitude)
newest_activity = common_utils.to_int(newest_activity)
dealer_id_list = dealer_utils.get_dealer_id_list(city_id=city_id,
county_id=county_id,
car_brand_id=brand_id,
car_series_id=series_id)
if not dealer_id_list:
return None
dealer_sql_template = '''
SELECT
id,
(
(longitude-%(longitude)s)*(longitude-%(longitude)s)
+(latitude-%(latitude)s)*(latitude-%(latitude)s)
) AS dealer_distance
FROM
t_base_dealer
WHERE
is_enable=1
AND is_frozen=0
AND id in ({{dealer_id_list}})
ORDER BY
dealer_distance
LIMIT
%(offset)s, %(count)s
'''
param = {
'dealer_id_list': common_utils.int_list_to_str(dealer_id_list),
'longitude': longitude,
'latitude': latitude,
'offset': offset,
'count': count,
}
dealer_sql = Template(dealer_sql_template).render(param)
cursor = connection().cursor()
cursor.execute(dealer_sql, param)
dealers = dictfetchall(cursor)
dealer_list = []
for dealer in dealers:
dealer = dealer_utils.get_dealer_by_id(dealer_id=dealer['id'])
dealer['distance'] = common_utils.geo_distance(latitude, longitude,
dealer['latitude'],
dealer['longitude'])
if newest_activity:
tmp_activity_list = activity_utils.get_activity_list_by_dealer(
dealer_id=dealer['id'],
activity_type=1,
)
dealer['activity_list'] = tmp_activity_list
elif series_id:
activity_list = activity_utils.get_activity_list_by_dealer_series(
dealer_id=dealer['id'], car_series_id=series_id)
if activity_list:
dealer['activity_list'] = activity_list
dealer_list.append(dealer)
filter_count = len(dealer_id_list)
# city count
city_count = dealer_utils.get_dealer_count(city_id=city_id)
result_dict = {
'city_count': city_count,
'filter_count': filter_count,
'dealer_list': dealer_list,
'page_count': int((filter_count + count - 1) / count),
}
return result_dict
