def get_purchasetax(price=0, car_type_id=0): ''' flag 0:正常,1:购置税减半 ''' sql = """ select count(1) as cnt from T_BASE_CAR_TYPE where characteristic_activity like %(saving_half)s and id = %(car_type_id)s and is_enable=1; """ params = {} params['car_type_id'] = car_type_id params['saving_half'] = '%,24,%' #购置税减半为24 tmp = db.fetchone(sql, params) rtn = { 'purchasetax': round(to_float(price) / (1 + 0.17) * 0.1, 2), 'flag': 0 } if tmp and tmp['cnt'] > 0: rtn['purchasetax'] = round(rtn['purchasetax'] / 2, 2) rtn['flag'] = 1 return rtn
def get_cartype_tax_insurance(car_type_id=0): # 获取排量 displacement = 1600 sql = """ SELECT property_value FROM e4sdb_data.t_base_car_type_property where property_id in (SELECT id FROM e4sdb_data.t_base_car_type_property_template where property_key in ('pailiang_cc')) and car_type_id = %(car_type_id)s; """ params = {} params['car_type_id'] = car_type_id item = db.fetchone(sql, params) if item and item['property_value']: displacement = to_int(item['property_value']) rtn = {'travel_tax': 0, 'travel_insurance': 950, 'busi_insurance': 2065} if displacement in range(1, 1001): rtn['travel_tax'] = 180 elif displacement in range(1001, 1601): rtn['travel_tax'] = 360 elif displacement in range(1601, 2001): rtn['travel_tax'] = 420 elif displacement in range(2001, 2501): rtn['travel_tax'] = 720 elif displacement in range(3001, 4001): rtn['travel_tax'] = 3000 elif displacement > 4000: rtn['travel_tax'] = 4500 return rtn
def get_article_detail_with_series(article_id=0): ''' 根据ID获取一篇文章的详细内容,包含车系ID @param article_id 文章ID @return 返回一条文章的详细内容以及车系详情 ''' if not article_id or int(article_id) <= 0: return None sql = """ SELECT id, title, keywords, front_img_path, source, description, content, created_date, '' series FROM t_cms_article WHERE `status` = 'PUBLISH' AND id = %(article_id)s LIMIT 1 """ params = {} params['article_id'] = article_id tmp = db.fetchone(sql, params) if not tmp or len(tmp) == 0: return None sql = """ SELECT a.object_id, b.CAR_SERIES_CN, b.START_GUIDEPRICE, b.WAP_THUMBNAIL, b.PC_THUMBNAIL FROM t_cms_article_rela a LEFT JOIN t_base_car_series b ON a.object_id = b.ID WHERE a.article_id = %(article_id)s AND a.status != 'DEL' GROUP BY a.object_id """ tmp['series'] = db.fetch(sql, params) return tmp
def get_service_data(self, request, context, **kwargs): sql = """ select * from T_BASE_MEDIA_ACTIVITY where date_format(created_date, '%%Y%%m')=%(yyyymm)s and id=%(activity_id)s and activity_type=%(activity_type)s """ activity = db.fetchone(sql, kwargs) if not activity: raise Http404 context['activity'] = activity_utils.get_activity_by_id( activity_id=activity['ID']) return context
def get_lowest_summary(price=0, city_id=None, series_id=None): ''' 获取最低的成本数值 @param price 经销商报价,输入0即取官方指导价 @param city_id 城市编码 @param series_id 车系编码 @return 返回首付比率、金额,贷款期限、金额,总成本、月供 ''' tmp = get_lowest_downpay_monthly(city_id=city_id, series_id=series_id) sql = """ SELECT b.FINAL_PAYMENT_SCALE, MIN(a.SKU_ITEM) SKU_ITEM, a.SKU_RATE FROM t_base_financial_pro_sku a LEFT JOIN t_base_financial_product b ON a.FINACIAL_PRODUCT_ID=b.ID WHERE a.FINACIAL_PRODUCT_ID=%(product_id)s """ params = {} params['product_id'] = tmp['downpay_id'] tmp = db.fetchone(sql, params) resultdict = {} resultdict['downpay_scale'] = tmp['FINAL_PAYMENT_SCALE'] if not tmp['FINAL_PAYMENT_SCALE'] or float( tmp['FINAL_PAYMENT_SCALE']) <= 0: tmp['FINAL_PAYMENT_SCALE'] = 0 resultdict['downpay_money'] = round( float(tmp['FINAL_PAYMENT_SCALE']) * float(price) / 100, 2) resultdict['period'] = tmp['SKU_ITEM'] resultdict['loan_money'] = round( float(price) - float(resultdict['downpay_money']), 2) resultdict['total_cost'] = round( float(resultdict['downpay_money']) * float(tmp['SKU_RATE']) / 100 + float(price), 2) resultdict['monthly'] = round( resultdict['total_cost'] / resultdict['period'], 2) return resultdict
def get_lowest_downpay_monthly(city_id=None, series_id=None): ''' 获取当前城市指定车系最低的首付和月供 @param city_id 城市编码 @param series_id 车系编码 @return 返回首付和月供两个参数,以及相应的最低首付和最低月供的金融方案ID ''' if not city_id or not series_id: logger.error("no city or series") resultdict = {} params = {} params['city_id'] = city_id params['series_id'] = series_id sql = """ SELECT p.ID, MIN(fpp.FIRST_PAY_PERCENT * op.PUBLIC_OFFER_PRICE/100) downpay FROM t_base_financial_first_pay_percent_rel fpp, t_base_financial_product p, t_base_financial_dlr_rel fd, t_base_financial_series_rel fs, t_base_dealer d, t_base_offer_price op WHERE fpp.FINACIAL_PRODUCT_ID = p.ID AND p.ID = fd.FINANCIAL_PRODUCT_ID AND p.ID = fs.FINANCIAL_PRODUCT_ID AND fd.DLR_CODE = d.DLR_CODE AND fs.CAR_SERIES_ID = op.CAR_SERIES_ID AND d.ID = op.DEALER_ID AND d.CITY_ID = %(city_id)s AND fs.CAR_SERIES_ID = %(series_id)s """ tmp = db.fetchone(sql, params) resultdict['downpay_id'] = tmp['ID'] resultdict['downpay'] = 0 if tmp['downpay']: resultdict['downpay'] = round(tmp['downpay'], 2) sql = """ SELECT p.ID, MIN((op.PUBLIC_OFFER_PRICE - fpp.FIRST_PAY_PERCENT * op.PUBLIC_OFFER_PRICE/100) / sku.SKU_ITEM) monthly FROM t_base_financial_first_pay_percent_rel fpp, t_base_financial_pro_sku sku, t_base_financial_product p, t_base_financial_dlr_rel fd, t_base_financial_series_rel fs, t_base_dealer d, t_base_offer_price op WHERE fpp.FINACIAL_PRODUCT_ID = p.ID AND sku.FINACIAL_PRODUCT_ID = p.id AND p.ID = fd.FINANCIAL_PRODUCT_ID AND p.ID = fs.FINANCIAL_PRODUCT_ID AND fd.DLR_CODE = d.DLR_CODE AND fs.CAR_SERIES_ID = op.CAR_SERIES_ID AND d.ID = op.DEALER_ID AND d.CITY_ID = %(city_id)s AND fs.CAR_SERIES_ID = %(series_id)s """ tmp = db.fetchone(sql, params) resultdict['monthly_id'] = tmp['ID'] resultdict['monthly'] = 0 if tmp['monthly']: resultdict['monthly'] = round(tmp['monthly'], 2) return resultdict
def get_lowest_monthly_payment(city_id=None, car_series_id=None, car_type_id=None, dealer_id=None, finacial_product_id=None, price=None): ''' 根据城市、车系,获取最低月供的金融方案 ''' offer_price = { "public_offer_price": price, "car_series_id": car_series_id, "car_type_id": car_type_id, "dealer_id": dealer_id } if not price: kw = {} if car_series_id: kw['car_series_id'] = car_series_id if car_type_id: kw['car_type_id'] = car_type_id if dealer_id: kw['dealer_id'] = dealer_id _offer_price = offer_price_utils.get_offerprice(**kw) offer_price['public_offer_price'] = _offer_price['public_offer_price'] sql = """ select * from ( select p.id as product_id, p.is_final_payment, p.final_payment_scale, p.repayment_type, fpp.id as first_percent_id, fpp.FIRST_PAY_PERCENT as first_pay_percent, sku.id as sku_id, sku.SKU_ITEM as sku_item, sku.sku_rate as sku_rate, first_pay_percent * sku_item as less_monthly_flag FROM t_base_financial_product p, t_base_financial_first_pay_percent_rel fpp, t_base_financial_pro_sku sku, t_base_financial_series_rel fs where p.id = fpp.FINACIAL_PRODUCT_ID and p.id = sku.FINACIAL_PRODUCT_ID and p.id = fs.FINANCIAL_PRODUCT_ID and p.EFFECT_START_DATE <= now() and p.EFFECT_END_DATE >= now() and p.is_enable = 1 and fs.CAR_SERIES_ID = %(car_series_id)s {% if finacial_product_id -%} and p.id = %(finacial_product_id)s {%- endif %} ) xxx order by xxx.less_monthly_flag desc limit 1; """ kw = { 'car_series_id': car_series_id, 'finacial_product_id': finacial_product_id } finance_data = db.fetchone(sql, kw) if not finance_data: return {} finance_data.update(offer_price) rtn_data = calc_financial(**finance_data) finance_data.update(rtn_data) return finance_data