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_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_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 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