def save_admin(self, u):
# 事务…
try:
u['reg_datetime'] = str(datetime.datetime.now())[:19]
self.connect()
with self._conn.cursor() as cursor:
cursor.execute(
"""set session transaction isolation level REPEATABLE READ;"""
)
sql = """insert into t_admin (用户名, 密码, 姓名, 电子邮箱, 联系方式, 激活码, 激活, 注册时间) values
('{tel}', '{pwd}', '{name}', '{email}', '{tel}', '{id_code}', 'n', '{reg_datetime}');""".format(
**u)
cursor.execute(sql)
sql = """select * from t_admin where 编号 = (select max(编号) from t_admin);"""
cursor.execute(sql)
user_id = cursor.fetchall()[0][0]
u['user_id'] = user_id
self.commit()
except Exception as e:
u = None
logger.error(e)
self.rollback()
# send.email # new thread
finally:
self.disconnect()
return u
def _send_active_url_email(self, u):
subject = '用户激活_{name}'.format(**u)
u['activate_url'] = ACTIVATE_URL_PREFIX + \
'?user_id={user_id}&identify_code={id_code}'.format(**u)
images = []
try:
content = """
<b>用户激活</b><br/>
客户经理 {name}<br/>
登录手机 {tel}<br/>
电子邮箱 {email}<br/>
点击下面的链接激活（或复制到浏览器地址栏上访问即可）<br/>
{activate_url}
""".format(**u)
attachments = []
receivers = [u['email']]
logger.info('%d - 发送激活邮箱...' % u['user_id'])
send_email(receivers, subject, content, images, attachments)
logger.info('%d - 待激活邮箱发送成功' % u['user_id'])
except Exception as e:
logger.info('%d - 待激活邮箱发送失败' % u['user_id'])
logger.error(e)
def execute_query_by_sql(self, sql):
rs = None
try:
self.connect()
with self._conn.cursor() as cursor:
cursor.execute(sql)
rs = cursor.fetchall()
except Exception as e:
logger.error(sql)
logger.error(e)
finally:
self.disconnect()
return rs
def execute_update_by_sql(self, sql):
try:
self.connect()
with self._conn.cursor() as cursor:
cursor.execute(sql)
self.commit()
except Exception as e:
logger.error(sql)
logger.error(e)
self.rollback()
finally:
self.disconnect()
def execute_df_query_by_sql(self, sql, idx_col=None):
df = None
try:
self.connect()
if idx_col is None:
df = pd.read_sql(sql, self._conn)
else:
df = pd.read_sql(sql, self._conn, index_col=idx_col)
except Exception as e:
logger.error(sql)
logger.error(e)
finally:
self.disconnect()
return df
def execute_df_query_by_t_name(self, table_name, idx_col=None):
df = None
sql = """select * from {table_name}""".format(table_name=table_name)
try:
self.connect()
if idx_col is None:
df = pd.read_sql(sql, self._conn)
else:
df = pd.read_sql(sql, self._conn, index_col=idx_col)
except Exception as e:
logger.error(sql)
logger.error(e)
finally:
self.disconnect()
return df
def save_order(self, order_dict):
# 1. 保存至数据库
res_dict = self._order_dao.save_order(order_dict=order_dict)
res = res_dict.get('res', False) # 结果
if res:
# 2. 开启新线程，生成excel，发送邮件。主线程返回，以免等待时间过长…
order_id = res_dict.get('order_id')
order_info = res_dict.get('order_info')
t1 = threading.Thread(target=self._save_xlsx_send_mail,
args=(order_id, order_info))
t1.start()
else:
logger.error('订单保存失败')
return res_dict
def _save_xlsx_send_mail(self, order_id, order_info):
# 1. 生成对应的文件夹
try:
dt_str = pd.to_datetime(
order_info['order_datetime']).strftime('%Y%m%d')
old_folder_dict = {
int(x.split('_')[0]): x
for x in os.listdir(CONTRACT_DIR_PATH)
}
new_folder_path = CONTRACT_DIR_PATH + \
'{order_id}_{status}_{dt_str}_{organ}'.format(order_id=order_id, status='未处理',
organ=order_info['organization'],
dt_str=dt_str)
if order_id not in old_folder_dict:
os.mkdir(new_folder_path)
else:
if old_folder_dict[order_id] == new_folder_path:
pass
else:
os.rename(CONTRACT_DIR_PATH + old_folder_dict[order_id],
new_folder_path)
except Exception as e:
logger.info('新合同文件夹生成失败 %d' % order_id)
logger.error(e)
# 2. 生成Excel订单
try:
logger.info('正在保存订单excel文件')
self._save_xlsx(order_id)
logger.info('订单excel文件保存成功')
except Exception as e:
logger.info('订单excel文件保存失败 %d' % order_id)
logger.error(e)
# 3. 发送邮件
try:
logger.info('正在发送邮件')
self._send_email_order_xlsx(order_id, order_info)
except Exception as e:
logger.error('邮件发送失败 %d' % order_id)
logger.error(e)
def save_order(self, order_dict):
result = True
try:
self.connect()
with self._conn.cursor() as cursor:
cursor.execute(
"""set session transaction isolation level REPEATABLE READ;"""
)
params = order_dict
if len(order_dict['cart']) < 4: # 是3还是4？… -_-!
logger.error('购物车错误…')
raise ValueError('错误…')
params['total_price'] = order_dict['cart'][TOTAL_PRICE]
params['order_datetime'] = str(datetime.datetime.now())[:19]
params['admin_user'] = order_dict['admin_user']
params['admin_user_id'] = order_dict['admin_user']['编号']
sql = """
INSERT INTO t_order_info ( 订单状态, 订单价格, 联系人, 联系方式, 电子邮箱, 下单时间, 下单员编号,
单位名称, 备注 ) VALUES ( '未处理', {total_price}, '{name}', '{tel}', '{email}',
'{order_datetime}', {admin_user_id}, '{organization}', '{remark}') ;""".format(
**params)
cursor.execute(sql)
# 得到该订单的编号
sql = """select max(订单编号) from t_order_info;"""
cursor.execute(sql)
order_id = cursor.fetchall()[0][0]
params['order_id'] = order_id
# 数据存入订单详细表 t_order_component
cart = params['cart']
cart.pop(TOTAL_PRICE)
cart.pop(TOTAL_NUMBER)
cart.pop(TOTAL_CATEGORY_NUMBER)
order_cpnt_list = [(k, cart[k]) for k in sorted(cart.keys())]
prods_dict = ProductService().get_all_prods_dict()
for oc in order_cpnt_list:
prod_id = oc[0]
param2 = dict()
param2['order_id'] = order_id
param2['prod_id'] = prod_id
param2['price_unit'] = prods_dict[str(prod_id)]['报价单位']
param2['prod_measure_word'] = prods_dict[str(
prod_id)]['产品量词']
param2 = dict(param2, **json.loads(oc[1]))
sql = """ insert into t_order_component ( 订单编号, 产品编号, 预定数量, 预定年限, 投标报价, 报价单位,
产品量词, 单项价格 ) values ( {order_id}, {prod_id}, {quantity}, {year}, {unit_price},
'{price_unit}', '{prod_measure_word}', {product_price} ) ;""".format(
**param2)
cursor.execute(sql)
self.commit()
except Exception as e:
logger.error(e)
result = False
self.rollback()
finally:
self.disconnect()
return {'res': result, 'order_id': order_id, 'order_info': params}
def get(self, request):
path = request.path
logger.info('GET: ' + path)
# 查看产品列表
if '/mobile/all_products/' == path:
# t1 = datetime.datetime.now()
all_prods_df = self._prod_serv.get_all_prods_df()
# t2 = datetime.datetime.now()
prods_html_data = self._prod_serv.cvt_df_grp_type(all_prods_df)
# t3 = datetime.datetime.now()
prods_js_data = self._prod_serv.get_all_prods_dict()
# t4 = datetime.datetime.now()
# print(t2 - t1)
# print(t3 - t2)
# print(t4 - t3)
# print(t4 - t1)
try:
cart = get_session_cart(request)
js_data = {
'result': 1,
'cart': cart,
'prods_js_data': prods_js_data
}
html_data = {'result': 1, 'prods_html_data': prods_html_data}
except Exception as e:
logger.error(e)
return render(request, 'mobile/product_list.html', {
'js_data': json.dumps(js_data),
'html_data': html_data
})
# 手机上操作（增加、减少、删除、选中），此函数由ajax调用
elif '/mobile/update_cart/' == path:
try:
request.session.set_expiry(SESSION_EXPIER_TIME)
params = parse_get_params(request)
product_id = int(params['product_id'][0])
quantity = float(params['quantity'][0])
year = int(params['year'][0])
prod_df = self._prod_serv.get_all_prods_df(idx_col='产品编号')
cart = get_session_cart(request)
# if 数量为0，就把该商品从cart中去掉，并更新session
# else， 查询所有商品的价格，计算价格（单项 & 购物车中所有商品的价格）
if quantity > 0.001: # 数量大于零
px_dict = self._prod_serv.calc_prod_item_price(
prod_df, product_id, quantity, year)
item = {
'product_id': product_id,
'quantity': quantity,
'unit_price': px_dict.get('unit_price', 0),
'product_price': px_dict.get('product_price', 0),
'year': year,
}
cart[product_id] = json.dumps(item)
else: # 数量等于0
if product_id in cart: # cart中本来有这个商品
cart.pop(product_id) # remove
# 订单总价
# cart的key大于0的那些value，才是商品。有几个常量小于0。
total_price = sum([
json.loads(k2)['product_price']
for k2 in [cart[k] for k in cart.keys() if k > 0]
])
total_number = sum([
json.loads(k2)['quantity']
for k2 in [cart[k] for k in cart.keys() if k > 0]
])
cart[TOTAL_PRICE] = total_price
cart[TOTAL_NUMBER] = total_number
cart[TOTAL_CATEGORY_NUMBER] = max(len(cart) - 3,
0) # 减掉几个不是“已订商品”的key
request.session['cart'] = json.dumps(cart)
result_data = {'result': 1, 'cart': cart}
except Exception as e:
logger.error(e)
result_data = {'result': 0}
# 返回JSON，内含当前购物车中商品的id、数量、单价、总价等信息
return HttpResponse(json.dumps(result_data))
# 清空订单（此函数由ajax调用）
elif '/mobile/clear_cart/' == path:
try:
cart = INIT_CART
request.session['cart'] = json.dumps(cart) # 更新session的cart
result_data = {'result': 1, 'cart': cart}
except Exception as e:
logger.error(e)
result_data = {'result': 0}
return HttpResponse(json.dumps(result_data))
# 进入订单预览页面
elif '/mobile/preview_order/' == path:
# 全部商品
all_prods_df = self._prod_serv.get_all_prods_df()
all_prods_df.index = all_prods_df['产品编号']
# session 已购商品
cart = get_session_cart(request)
# cart是dict。但因为django的session比较弱，key和value只能存int或str
# 所以，以 prod_id 为key的那些value，本来是dict结果，但存在session中，只能存成str
# 因此，后面需要json.loads
ordered_prod_df = all_prods_df.loc[[
k for k in cart.keys() if k > 0
]]
ordered_prod_df['预订数量'] = ordered_prod_df['产品编号'].apply(
lambda x: json.loads(cart[x])['quantity'])
ordered_prod_df['预定年限'] = ordered_prod_df['产品编号'].apply(
lambda x: json.loads(cart[x])['year'])
ordered_prod_df['预定价格'] = ordered_prod_df['产品编号'].apply(
lambda x: json.loads(cart[x])['product_price'])
# 更新投标报价
ordered_prod_df['投标报价'] = ordered_prod_df['产品编号'].apply(
lambda x: json.loads(cart[x])['unit_price'])
ordered_prod_df_grp_type = self._prod_serv.cvt_df_grp_type(
ordered_prod_df)
cart['total_price'] = cart[TOTAL_PRICE]
cart['total_number'] = cart[TOTAL_NUMBER]
js_data = dict()
js_data['cart'] = cart
result_data = {
'cart': cart,
'prods_html_data': ordered_prod_df_grp_type,
'js_data': json.dumps(js_data)
}
return render(request, 'mobile/preview_order.html',
{'data': result_data})
# 进入填写客户信息的页面
elif '/mobile/input_customer_info/' == path:
return render(request, 'mobile/input_customer_info.html')
elif '/mobile/save_order/' == path:
logger.warning('GET提交，保存订单，可能是由于浏览器刷新所致') # 浏览器刷新！
return HttpResponseRedirect(NGINX_PREFIX +
'/mobile/all_products/') # 跳转到主界面
# 新用户输入信息注册
elif '/mobile/input_register/' == path:
return render(request, 'mobile/register.html')
# 选择登录 or 注册
elif '/mobile/index/' == path:
return render(request, 'mobile/index.html')
# 用户通过点击发送到邮箱的链接激活
elif '/mobile/activate/' == path:
params = parse_get_params(request)
user_id = int(params['user_id'][0])
identify_code = params['identify_code'][0]
u = self._admin_serv.activate({
'user_id': user_id,
'identify_code': identify_code
})
if u is not None:
logger.info('激活成功 ' + json.dumps(u))
return HttpResponse('<h1>激活成功！<h1>')
else:
return HttpResponse('用户激活失败，请联系廖润雪。')
# 网页上点击“登录”按钮后，先判断是否登录
# TODO: 已经用了filter对身份进行验证，这个分支是否可以考虑不写？
elif '/mobile/pre_login/' == path:
admin_user_str = request.session.get('admin_user', None)
if admin_user_str is None:
result = 0
else:
result = 1
return HttpResponse(json.dumps({'result': result}))
elif '/mobile/login/' == path:
return render(request, 'mobile/login.html')
elif '/mobile/all_orders/' == path:
admin_user_str = request.session['admin_user']
admin_user = json.loads(admin_user_str)
orders_dict = self._order_serv.get_all_orders_by_admin_id(
admin_user['编号'])
return render(request, 'mobile/order_list.html',
{'data': orders_dict})
elif '/mobile/order_result/' == path:
params = parse_get_params(request)
order_id = int(params['order_id'][0])
result_data = self._order_serv.get_order_result(order_id)
order_cpnt_df = result_data['order_cpnt_df']
order_cpnt_df_grp_type = self._prod_serv.cvt_df_grp_type(
order_cpnt_df)
result_data['order_cpnt_df'] = order_cpnt_df_grp_type
return render(request, 'mobile/order_result.html',
{'data': result_data})
elif '/mobile/my_info/' == path:
admin_user_str = request.session['admin_user']
admin_user = json.loads(admin_user_str)
return render(request, 'mobile/my_info.html', {'data': admin_user})
else:
logger.warning('404')
return HttpResponseRedirect(NGINX_PREFIX + '/mobile/index/')