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