def modify_info(req): """ 修改信息 :param req: :return: """ ret = ResModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 return JsonResponse(ret.to_dic()) # 中间件校验token后赋值USER_ID user_id = req.META.get("USER_ID") if not user_id: return JsonResponse(ResModelToLogin().to_dic()) # 校验用户信息 with connection() as con: user_rs = con.execute_sql( """select {table11_id} from {table11} where {table11_id}=%(uid)s""".format( **SQL_DIC_USERINFO), {"uid": user_id}) if (not user_rs) or (not user_rs[0]): # 按理说不会走这个if的,若走了,肯定有问题,不必友好提示 return JsonResponse(ResModelToLogin().to_dic()) # 获取参数,不取默认值是为了保持数据库的default值 sex_type = req.POST.get('sexType', DrUserInfo.SEX_TYPE[0][0]) head_img = req.POST.get('headImg', '') user_introduce = req.POST.get('userIntro', '') user_notes = req.POST.get('userNotes', '') occupation = req.POST.get('userOcc', '') user_location = req.POST.get('userLocal', '') with connection() as con: con.execute_sql( """update {table11} set {table11_sex}=%(sex)s,{table11_himg}=%(himg)s,{table11_introduce}=%(intro)s, {table11_notes}=%(notes)s,{table11_occupation}=%(occ)s,{table11_local}=%(local)s where {table11_id}=%(uid)s""".format(**SQL_DIC_USERINFO), { "uid": user_id, "sex": sex_type, "himg": head_img, "intro": user_introduce, "notes": user_notes, "occ": occupation, "local": user_location }) ret.msg = '已修改' ret.code = ret.ResCode.succ return JsonResponse(ret.to_dic())
def get_info(req): """ 查询信息 :param req: :return: """ ret = ResModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 return JsonResponse(ret.to_dic()) # 中间件校验token后赋值USER_ID user_id = req.META.get("USER_ID") if user_id: # 查询用户信息 with connection() as con: user_rs = con.execute_sql(""" select a.{table1_uname},a.{table1_phone},a.{table1_email},a.{table1_emailsucc}, a.{table1_wx},a.{table1_qq},to_char(a.{table1_atime}, 'yyyy-mm-dd hh24:mi:ss') as {table1_atime}, b.{table11_sex},b.{table11_himg},b.{table11_introduce},b.{table11_notes},b.{table11_occupation}, b.{table11_local} from {table1} a,{table11} b where a.{table1_id}=b.{table11_id} and a.{table1_id}=%(uid)s""" .format(**{ **SQL_DIC_USER, **SQL_DIC_USERINFO }), {"uid": user_id}, dicorobj='dict') if user_rs and user_rs[0]: ret.data = user_rs[0] ret.code = ret.ResCode.succ ret.msg = "" return JsonResponse(ret.to_dic())
def content_detail(req, id): """ 查询blog明细 :param req: request :param id: id :return: """ ret = ResModel() ret.msg = req_invalid_check(req) v_cnt = 0 if not req.GET.get("forManage"): # 不 通过管理端查询 # 浏览者信息登记 try: ip = get_ip(req) v_cnt = add_visitor(ip, apps.get_app_config('app_blog').name, 'read', id) except: from traceback import format_exc flog.error("记录访客信息失败:%s" % format_exc()) if ret.msg: # 请求合法性校验不通过 ret.code = ret.ResCode.fail ret.data = {} return JsonResponse(ret.to_dic()) ret.code = ret.ResCode.succ # todo 权限level的控制 # 为了统一,驼峰命名,采用原生sql with connection() as con: # where 条件 and {table1_rlevel}>=0 暂时先屏蔽 rs = con.execute_sql( """select {table1_id},{table1_title},{table1_content},{table1_rcnt}, to_char({table1_atime}, 'yyyy-mm-dd hh24:mi:ss') as {table1_atime}, {table1_notes},{table1_bgurl},{table1_rlevel},{table1_type_id},{table1_tags} from {table1} where {table1_id}=%(id)s """.format(**SQL_DIC_BLOG), {"id": id}, dicorobj="dict") if rs: ret.data = rs[0] if v_cnt == 1: con.execute_sql( "update {table1} set {table1_rcnt}={table1_rcnt}+1 where {table1_id}=%(id)s" .format(**SQL_DIC_BLOG), {"id": id}) else: ret.data = {} return JsonResponse(ret.to_dic())
def save_menulist_role(req, role_id): """ 保存角色下的菜单 :param req: :param role_id: :return: """ ret = ResModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 return JsonResponse(ret.to_dic()) # 中间件校验token后赋值USER_ID mod_user_id = req.META.get("USER_ID") mids = req.POST.get('ids', '') # 先删除要保存或修改的角色菜单,然后再添加 sql_ins = """insert into {tablerm}({tablerm_id},{tablerm_rid},{tablerm_mid},{tablerm_upduid}, {tablerm_atime}) values(%(rmid)s, %(rid)s, %(mid)s, %(uid)s, %(atime)s) """ now_date = fmt_date() # 开启事务 with connection() as con: con.execute_sql( "delete from {tablerm} where {tablerm_rid}=%(rid)s".format( **SQL_DIC_ROLEMENU), {"rid": role_id}) if mids: for mid in mids.split(","): con.execute_sql( sql_ins.format(**SQL_DIC_ROLEMENU), { "rmid": str(uuid1()).replace('-', ''), "rid": role_id, "mid": mid, "uid": mod_user_id, "atime": now_date }) ret.msg = '已保存' ret.code = ret.ResCode.succ return JsonResponse(ret.to_dic())
def type_list(req): """ 查询blog类别 :param req: request :return: """ ret = ResModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 ret.code = ret.ResCode.fail ret.data = [] return JsonResponse(ret.to_dic()) ret.code = ret.ResCode.succ with connection() as con: ret.data = con.execute_sql( """select {table2_type_id}, {table2_type_name} from {table2} order by convert_to({table2_type_name} , 'GB18030') asc""" .format(**SQL_DIC_TYPE), dicorobj="dict") return JsonResponse(ret.to_dic())
def title_value(req): """ 查询副标题参数 :param req: request :return: """ ret = ResModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 ret.code = ret.ResCode.fail ret.data = {} return JsonResponse(ret.to_dic()) ret.code = ret.ResCode.succ with connection() as con: rs = con.execute_sql( "select {table1_value} from {table1} where {table1_code}=%(p)s ". format(**SQL_DIC_PARAM), {'p': SET_TITLE_CMD}, dicorobj="dict") ret.data = rs[0] if rs else {} return JsonResponse(ret.to_dic())
def get_display_list(req): """ 角色列表-带pid的树形 :param req: :return: """ ret = ResModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 ret.code = ret.ResCode.fail return JsonResponse(ret.to_dic()) with connection() as con: ret.data = con.execute_sql( """select a.{tablem_id} as id,a.{tablem_aname} as name,a.{tablem_pid} as p_id from {tablem} a where 1=1 order by a.{tablem_orderno} asc,a.{tablem_id} asc""" .format(**SQL_DIC_MENU), dicorobj="dict") ret.code = ret.ResCode.succ ret.msg = "" return JsonResponse(ret.to_dic())
def modify_pwd(req): """ 修改密码 :param req: :return: """ ret = ResModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 return JsonResponse(ret.to_dic()) # 中间件校验token后赋值USER_ID user_id = req.META.get("USER_ID") if not user_id: return JsonResponse(ResModelToLogin().to_dic()) old_pwd = req.POST.get('oldPwd', '').upper() new_pwd = req.POST.get('newPwd', '').upper() if (not old_pwd) or (not new_pwd): ret.msg = "请求参数缺失" return JsonResponse(ret.to_dic()) # db前校验 r = RedisCtrl() if r.get_one(REDIS_KEY_PRE_PWDERR + user_id + "_ERR"): ret.msg = "原密码错误,请" + str( r.get_ex_time(REDIS_KEY_PRE_PWDERR + user_id + "_ERR")) + "秒后再尝试" return JsonResponse(ret.to_dic()) # 校验用户信息 with connection() as con: user_rs = con.execute_sql( """select {table1_pwd},{table1_salt} from {table1} where {table1_id}=%(uid)s""".format( **SQL_DIC_USER), {"uid": user_id}) if (not user_rs) or (not user_rs[0]): # 按理说不会走这个if的,若走了,肯定有问题,不必友好提示 return JsonResponse(ResModelToLogin().to_dic()) user_rs = user_rs[0] # 密码校验 p = PwdCtrl() if user_rs.pwdValue.upper() != p.create_md5(src_str=old_pwd, salt=user_rs.pwdSalt): # 密码校验不通过 ret.msg = '原密码错误' # 错误次数,5分钟内,不可大于等于10次 err_cnt = r.get_one(REDIS_KEY_PRE_PWDERR + user_id) if not err_cnt: err_cnt = 1 r.set_one(REDIS_KEY_PRE_PWDERR + user_id, str(err_cnt), expt=60 * 5) else: err_cnt = int(err_cnt) + 1 r.set_one(REDIS_KEY_PRE_PWDERR + user_id, str(err_cnt), expt=60 * 5) if int(err_cnt) >= 10: r.del_one(REDIS_KEY_PRE_PWDERR + user_id) # 同一个手机号验证码尝试次数过多 flog.warning(f"修改密码时ID[{user_id}]用户的原密码尝试次数过多") r.set_one(REDIS_KEY_PRE_PWDERR + user_id + "_ERR", "ERR", expt=60 * 5) ret.msg = '原密码错误,请重新加载' return JsonResponse(ret.to_dic()) # 修改 salt = p.get_salt() pwd = p.create_md5(new_pwd, salt=salt) with connection() as con: con.execute_sql( """update {table1} set {table1_pwd}=%(pwd)s,{table1_salt}=%(salt)s where {table1_id}=%(uid)s""".format(**SQL_DIC_USER), { "uid": user_id, "pwd": pwd, "salt": salt }) ret.msg = '已修改' ret.code = ret.ResCode.succ return JsonResponse(ret.to_dic())
def log_in_openid(req, yw_name, openid): """ 根据openid登录 1.查询redis是否存在该openid用户: 1.1.存在则直接return 1.2.不存在则查询db: a.获取用户信息存入redis后return b.无用户信息则return空,可走注册流程 :param req: :param yw_name: :param openid: :return: """ ret = ResModel() app_idsecret = MINI_PROGRAM_APP_CONF.get(yw_name) if not app_idsecret: ret.msg = '未配置的应用名称' return JsonResponse(ret.to_dic()) ret.msg = '' ret.code = ret.ResCode.succ app_id, app_secret = app_idsecret user_obj = None r = RedisCtrl() token = r.get_one(app_id + openid) if token: user_obj = r.get_one(token) else: with connection() as con: sql = """select a.{table1_id}, a.{table1_uname}, a.{table1_pwd}, a.{table1_salt}, a.{table1_wx}, a.{table1_qq}, a.{table1_phone}, a.{table1_email} from {table1} a, {tableo} b where a.{table1_id}=b.{tableo_uid} and b.{tableo_aid}=%(appid)s and b.{tableo_oid}=%(oid)s """.format(**{**SQL_DIC_USER, **SQL_DIC_USEROPENID}) user_rs = con.execute_sql(sql, {"appid": app_id, "oid": openid}) if user_rs and user_rs[0]: user_obj = user_rs[0] else: ret.msg = '用户信息不存在' if user_obj: # 记录登录日志 add_visitor(get_ip(req), apps.get_app_config('app_mf').name, 'login', user_obj.id) # 组织可见的user_info user_info = {"id": user_obj.id, "username": user_obj.userName} # 生成登录token # 存入缓存格式: token:user_info token_tmp = create_token(user_info, expt=-1) # 存入缓存格式: appid+openid:token r.set_one(app_id + openid, token_tmp) ret.data = {"token": token_tmp, "userInfo": user_info} else: ret.data = {} return JsonResponse(ret.to_dic())
def reset_phone_email(req, code_way, sid): """ 更换/添加手机号、email :param req: :param code_way: smscode.短信验证码;emailcode.email验证码 :param sid: :return: """ ret = ResModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 return JsonResponse(ret.to_dic()) # 中间件校验token后赋值USER_ID user_id = req.META.get("USER_ID") if not user_id: ret.msg = "用户信息不存在" return JsonResponse(ret.to_dic()) # 图形验证码or短信验证码 auth_code = req.POST.get('authCode', '').upper() # 新phone或者新email new_aim = req.POST.get('newAim', '') if not auth_code: ret.msg = '验证码不可空' return JsonResponse(ret.to_dic()) if not new_aim: ret.msg = '参数异常' return JsonResponse(ret.to_dic()) # 获取redis缓存数据 r = RedisCtrl() verify_code = r.get_one(REDIS_KEY_PRE_SID + sid) if not verify_code: ret.msg = '验证码已过期' return JsonResponse(ret.to_dic()) if code_way == 'emailcode': # 登录方式.emailcode.email验证码 user_code = r.get_one(REDIS_KEY_PRE_EMAILCODE + new_aim) ret.msg = '该email验证码已过期' sub_sql = "{table1_email}=%(new)s,{table1_emailsucc}='" + DrUser.EMAIL_SUCC[ 1][0] + "'," elif code_way == 'smscode': # 登录方式.smscode.短信验证码 user_code = r.get_one(REDIS_KEY_PRE_SMSCODE + new_aim) ret.msg = '该手机号验证码已过期' sub_sql = "{table1_phone}=%(new)s," else: # 其他code_way,反馈失败 return JsonResponse(ret.to_dic()) # 校验验证码 if not user_code: # 验证码不存在 return JsonResponse(ret.to_dic()) if user_code != auth_code: # 校验验证码 ret.msg = '验证码错误' # 记录错误次数,多次失败则需重新请求sid err_cnt = r.get_one(REDIS_KEY_PRE_CODEERR + user_id) if not err_cnt: err_cnt = 1 r.set_one(REDIS_KEY_PRE_CODEERR + user_id, str(err_cnt), expt=60 * 5) else: err_cnt = int(err_cnt) + 1 r.set_one(REDIS_KEY_PRE_CODEERR + user_id, str(err_cnt), expt=60 * 5) if int(err_cnt) >= 10: # 尝试次数大于10次,则需重新发送sms r.del_one(REDIS_KEY_PRE_SMSCODE + user_id) r.del_one(REDIS_KEY_PRE_EMAILCODE + user_id) r.del_one(REDIS_KEY_PRE_CODEERR + user_id) # 同一个验证码尝试次数过多 flog.warning(f"[{user_id}]用户的{code_way}验证码尝试次数过多") ret.msg = '验证码错误,请重新加载' return JsonResponse(ret.to_dic()) # 设置 with connection() as con: con.execute_sql( "update {table1} set " + sub_sql + ",{table1_utime}=%(ut)s where {table1_id}=%(uid)s".format( **SQL_DIC_USER), { "new": new_aim, "uid": user_id, "ut": fmt_date() }) ret.msg = '已修改' ret.code = ret.ResCode.succ return JsonResponse(ret.to_dic())
def email_reset_email(req, sid, vcode): """ 发送email验证码-更换/添加email :param req: :param sid: :param vcode: :return: """ ret = ResModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 return JsonResponse(ret.to_dic()) # 中间件校验token后赋值USER_ID user_id = req.META.get("USER_ID") if not user_id: ret.msg = "用户信息不存在" return JsonResponse(ret.to_dic()) email = req.POST.get("email") if not email: ret.msg = "请输入新EMAIL" return JsonResponse(ret.to_dic()) # 校验 r = RedisCtrl() verify_code = r.get_one(REDIS_KEY_PRE_SID + sid) if not verify_code: ret.msg = '验证码已过期' return JsonResponse(ret.to_dic()) if verify_code.upper() != vcode.upper(): ret.msg = '验证码错误' # 记录错误次数,多次失败则需重新请求sid err_cnt = r.get_one(REDIS_KEY_PRE_SIDERR + sid) if not err_cnt: err_cnt = 1 r.set_one(REDIS_KEY_PRE_SIDERR + sid, str(err_cnt), expt=60 * 5) else: err_cnt = int(err_cnt) + 1 r.set_one(REDIS_KEY_PRE_SIDERR + sid, str(err_cnt), expt=60 * 5) if int(err_cnt) >= 10: # 尝试次数大于10次,则需重新请求sid r.del_one(REDIS_KEY_PRE_SID + sid) r.del_one(REDIS_KEY_PRE_SIDERR + sid) return JsonResponse(ret.to_dic()) # 查询用户信息 with connection() as con: user_rs = con.execute_sql( "select {table1_id} from {table1} where {table1_id}=%(uid)s". format(**SQL_DIC_USER), {"uid": user_id}) if not user_rs: ret.msg = "用户信息不存在" return JsonResponse(ret.to_dic()) # 新email重复性校验 user_rs2 = con.execute_sql( "select {table1_id} from {table1} where {table1_email}=%(email)s". format(**SQL_DIC_USER), {"email": email}) if user_rs2: ret.msg = "该email已被使用" return JsonResponse(ret.to_dic()) # email验证码发送 email_code = "%06d" % randint(0, 999999) r.set_one(REDIS_KEY_PRE_EMAILCODE + email, email_code, expt=60 * 6) send_ret = email_send([email], EMAIL_MODPHONE_TITLE, EMAIL_MODPHONE_CONT % email_code) flog.debug(email + f"重置密码时发送email验证码err:{send_ret}") ret.msg = '已发送' ret.code = ret.ResCode.succ return JsonResponse(ret.to_dic())
def sms_reset_phone(req, sid, vcode): """ 发送短信验证码-更换/添加手机号 :param req: :param sid: :param vcode: :return: """ ret = ResModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 return JsonResponse(ret.to_dic()) # 中间件校验token后赋值USER_ID user_id = req.META.get("USER_ID") if not user_id: ret.msg = "用户信息不存在" return JsonResponse(ret.to_dic()) phone = req.POST.get("phone") if not phone: ret.msg = "请输入新手机号" return JsonResponse(ret.to_dic()) # 校验 r = RedisCtrl() verify_code = r.get_one(REDIS_KEY_PRE_SID + sid) if not verify_code: ret.msg = '验证码已过期' return JsonResponse(ret.to_dic()) if verify_code.upper() != vcode.upper(): ret.msg = '验证码错误' # 记录错误次数,多次失败则需重新请求sid err_cnt = r.get_one(REDIS_KEY_PRE_SIDERR + sid) if not err_cnt: err_cnt = 1 r.set_one(REDIS_KEY_PRE_SIDERR + sid, str(err_cnt), expt=60 * 5) else: err_cnt = int(err_cnt) + 1 r.set_one(REDIS_KEY_PRE_SIDERR + sid, str(err_cnt), expt=60 * 5) if int(err_cnt) >= 10: # 尝试次数大于10次,则需重新请求sid r.del_one(REDIS_KEY_PRE_SID + sid) r.del_one(REDIS_KEY_PRE_SIDERR + sid) return JsonResponse(ret.to_dic()) # 查询用户信息 with connection() as con: user_rs = con.execute_sql( "select {table1_id} from {table1} where {table1_id}=%(uid)s". format(**SQL_DIC_USER), {"uid": user_id}) if not user_rs: ret.msg = "用户信息不存在" return JsonResponse(ret.to_dic()) # 新手机号重复性校验 user_rs2 = con.execute_sql( "select {table1_id} from {table1} where {table1_phone}=%(phone)s". format(**SQL_DIC_USER), {"phone": phone}) if user_rs2: ret.msg = "该手机号已被使用" return JsonResponse(ret.to_dic()) # 短信验证码发送 sms_code = "%06d" % randint(0, 999999) r.set_one(REDIS_KEY_PRE_SMSCODE + phone, sms_code, expt=60 * 5) sms_ret = sms_send(phone, {"code": sms_code}, ALI_SMS_TMPL_RESET_PHONE, "修改手机号验证码") flog.debug(f"修改手机号验证码发送:{sms_ret}") ret.msg = '已发送' ret.code = ret.ResCode.succ return JsonResponse(ret.to_dic())
def get_list(req): """ 用户列表 :param req: :return: """ ret = ResPageModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 ret.code = ret.ResCode.fail return JsonResponse(ret.to_dic()) ret.page = req.GET.get('page', '1') ret.limit = req.GET.get('limit', PAGE_DEFAULT_LIMIT) order_field = hump2underline(req.GET.get('orderField', '')) order_type = req.GET.get('orderType', '') role_id = req.GET.get('roleId', '') uid = req.GET.get('id', '') acc = req.GET.get('account', '') name = req.GET.get('name', '') # 查询条件 par_dic = {} sub_sql_role = "" if role_id: sub_sql_role = ' and r.{tabler_id}=%(role_id)s' par_dic['role_id'] = role_id # 数据查询sql sql = """select u.{table1_id} as id,u.{table1_account} as account,u.{table1_uname} as user_name,u.{table1_phone} as phone, u.{table1_email} as email,u.{table1_emailsucc} as emailsucc, ui.{table11_sex} as sex_type,ui.{table11_himg} as head_img, ui.{table11_introduce} as user_introduce, ui.{table11_occupation} as occupation, aa.role_ids, aa.role_names, to_char(u.{table1_atime}, 'yyyy-mm-dd hh24:mi:ss') as add_time from {table1} as u inner join {table11} ui on ui.{table11_id}=u.{table1_id} inner join ( select urn.user_id, string_agg(urn.role_name, ',') role_names, string_agg(urn.role_id, ',') role_ids from (select ur.{table3_uid} as user_id,r.{tabler_id} as role_id,r.{tabler_name} as role_name from {tabler} r,{table3} ur where ur.{table3_rid}=r.{tabler_id} %s ) urn group by urn.user_id ) aa on aa.user_id=u.{table1_id} where 1=1 """ % sub_sql_role sql_count = """select count(1) as cnt from {table1} as u inner join {table11} ui on ui.{table11_id}=u.{table1_id} inner join ( select urn.user_id, string_agg(urn.role_name, ',') role_names, string_agg(urn.role_id, ',') role_ids from (select ur.{table3_uid} as user_id,r.{tabler_id} as role_id,r.{tabler_name} as role_name from {tabler} r,{table3} ur where ur.{table3_rid}=r.{tabler_id} %s ) urn group by urn.user_id ) aa on aa.user_id=u.{table1_id} where 1=1 """ % sub_sql_role # 查询条件 if uid: sql += " and u.{table1_id}=%(uid)s" sql_count += " and u.{table1_id}=%(uid)s" par_dic['uid'] = uid if acc: sql += " and u.{table1_account} like %(acc)s" sql_count += " and u.{table1_account} like %(acc)s" par_dic['acc'] = f"%{acc}%" if name: sql += " and u.{table1_uname} like %(name)s" sql_count += " and u.{table1_uname} like %(name)s" par_dic['name'] = f"%{name}%" # 排序 if order_field: sql += f" order by {order_field} {order_type}" else: sql += " order by u.{table1_uname} asc " with connection() as con: rs = con.execute_sql( sql_count.format( **{ **SQL_DIC_USER, **SQL_DIC_USERINFO, **SQL_DIC_ROLES, **SQL_DIC_USERROLE }), par_dic) ret.rsCount = rs[0].cnt # dicorobj需为dict ret.data = con.execute_sql(sql.format( **{ **SQL_DIC_USER, **SQL_DIC_USERINFO, **SQL_DIC_ROLES, **SQL_DIC_USERROLE }), par_dic, dicorobj="dict", page=ret.page, limit=ret.limit) ret.code = ret.ResCode.succ ret.msg = "" return JsonResponse(ret.to_dic())
def reset_pwd(req, code_way, sid): """ 重置密码 :param req: :param code_way: smscode.短信验证码;emailcode.email验证码 :param sid: :return: """ ret = ResModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 return JsonResponse(ret.to_dic()) # 中间件校验token后赋值USER_ID user_id = req.META.get("USER_ID") if not user_id: ret.msg = "用户信息不存在" return JsonResponse(ret.to_dic()) # 图形验证码or短信验证码 auth_code = req.POST.get('authCode', '').upper() new_pwd = req.POST.get('newPwd', '').upper() if not auth_code: ret.msg = '验证码不可空' return JsonResponse(ret.to_dic()) if not new_pwd: ret.msg = '新密码不可空' return JsonResponse(ret.to_dic()) # 获取redis缓存数据 r = RedisCtrl() verify_code = r.get_one(REDIS_KEY_PRE_SID + sid) if not verify_code: ret.msg = '验证码已过期' return JsonResponse(ret.to_dic()) if code_way == 'emailcode': # 登录方式.emailcode.email验证码 user_code = r.get_one(REDIS_KEY_PRE_EMAILCODE + user_id) elif code_way == 'smscode': # 登录方式.smscode.短信验证码 user_code = r.get_one(REDIS_KEY_PRE_SMSCODE + user_id) else: # 其他code_way,反馈失败 return JsonResponse(ret.to_dic()) # 校验验证码 if not user_code: # 短信验证码不存在 ret.msg = '验证码已过期' return JsonResponse(ret.to_dic()) if user_code != auth_code: # 校验验证码 ret.msg = '验证码错误' # 记录错误次数,多次失败则需重新请求sid err_cnt = r.get_one(REDIS_KEY_PRE_CODEERR + user_id) if not err_cnt: err_cnt = 1 r.set_one(REDIS_KEY_PRE_CODEERR + user_id, str(err_cnt), expt=60 * 5) else: err_cnt = int(err_cnt) + 1 r.set_one(REDIS_KEY_PRE_CODEERR + user_id, str(err_cnt), expt=60 * 5) if int(err_cnt) >= 10: # 尝试次数大于10次,则需重新发送sms r.del_one(REDIS_KEY_PRE_SMSCODE + user_id) r.del_one(REDIS_KEY_PRE_EMAILCODE + user_id) r.del_one(REDIS_KEY_PRE_CODEERR + user_id) # 同一个验证码尝试次数过多 flog.warning(f"重置密码时[{user_id}]用户的验证码尝试次数过多") ret.msg = '验证码错误,请重新加载' return JsonResponse(ret.to_dic()) # 设置密码 p = PwdCtrl() salt = p.get_salt() pwd = p.create_md5(src_str=new_pwd, salt=salt) with connection() as con: con.execute_sql( """update {table1} set {table1_pwd}=%(pwd)s,{table1_salt}=%(salt)s ,{table1_utime}=%(ut)s where {table1_id}=%(uid)s""".format(**SQL_DIC_USER), { "pwd": pwd, "uid": user_id, "salt": salt, "ut": fmt_date() }) ret.msg = '已修改密码' ret.code = ret.ResCode.succ return JsonResponse(ret.to_dic())
def email_reset_pwd(req, sid, vcode): """ 修改密码-发送email验证码 :param req: :param sid: :param vcode: :return: """ ret = ResModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 return JsonResponse(ret.to_dic()) # 中间件校验token后赋值USER_ID user_id = req.META.get("USER_ID") if not user_id: ret.msg = "用户信息不存在" return JsonResponse(ret.to_dic()) # 校验 r = RedisCtrl() verify_code = r.get_one(REDIS_KEY_PRE_SID + sid) if not verify_code: ret.msg = '验证码已过期' return JsonResponse(ret.to_dic()) if verify_code.upper() != vcode.upper(): ret.msg = '验证码错误' # 记录错误次数,多次失败则需重新请求sid err_cnt = r.get_one(REDIS_KEY_PRE_SIDERR + sid) if not err_cnt: err_cnt = 1 r.set_one(REDIS_KEY_PRE_SIDERR + sid, str(err_cnt), expt=60 * 5) else: err_cnt = int(err_cnt) + 1 r.set_one(REDIS_KEY_PRE_SIDERR + sid, str(err_cnt), expt=60 * 5) if int(err_cnt) >= 10: # 尝试次数大于10次,则需重新请求sid r.del_one(REDIS_KEY_PRE_SID + sid) r.del_one(REDIS_KEY_PRE_SIDERR + sid) return JsonResponse(ret.to_dic()) # 查询用户信息 with connection() as con: user_rs = con.execute_sql( "select {table1_id}, {table1_email} from {table1} where {table1_id}=%(uid)s" .format(**SQL_DIC_USER), {"uid": user_id}) if user_rs and user_rs[0]: user_obj = user_rs[0] if not user_obj.email: ret.msg = "您未绑定email地址" return JsonResponse(ret.to_dic()) else: ret.msg = "用户信息不存在" return JsonResponse(ret.to_dic()) # email验证码发送 email_code = "%06d" % randint(0, 999999) r.set_one(REDIS_KEY_PRE_EMAILCODE + user_obj.id, email_code, expt=60 * 6) email_send([user_obj.email], EMAIL_MODPWD_TITLE, EMAIL_MODPWD_CONT % email_code) ret.msg = '已发送' ret.code = ret.ResCode.succ return JsonResponse(ret.to_dic())
def log_in(req, code_way, sid): """ log_in :param req: :param code_way: smscode.短信验证码;piccode.图形验证码 :param sid: 验证码会话sid :return: """ ret = ResModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 ret.code = ret.ResCode.fail return JsonResponse(ret.to_dic()) # 获取redis缓存数据 r = RedisCtrl() verify_code = r.get_one(REDIS_KEY_PRE_SID + sid) if not verify_code: ret.msg = '验证码已过期' return JsonResponse(ret.to_dic()) # 图形验证码or短信验证码 auth_code = req.POST.get('authCode', '').upper() # 用户账户,若为短信方式,则该字段为手机号;图形验证码的话,该字段可以是account、email、phone user_acc = req.POST.get('userAcc', '') # 密码为为md5加密的值 pwd = req.POST.get('pwd', '').upper() # 是否需校验密码 need_check_pwd = False if code_way == 'piccode': # 登录方式.piccode.图形验证码 # 校验验证码 if (not settings.DEBUG) and (verify_code.upper() != auth_code): # 是否为debug模式,非debug模式,需校验验证码 ret.msg = '验证码错误' return JsonResponse(ret.to_dic()) need_check_pwd = True elif code_way == 'smscode': # 登录方式.smscode.短信验证码 sms_code = r.get_one(REDIS_KEY_PRE_SMSCODE + user_acc) if not sms_code: # 短信验证码不存在 ret.msg = '验证码已过期' return JsonResponse(ret.to_dic()) if (not settings.DEBUG) and (sms_code != auth_code): # 是否为debug模式,非debug模式,需校验验证码 ret.msg = '短信验证码错误' # 记录错误次数,多次失败则需重新请求sid err_cnt = r.get_one(REDIS_KEY_PRE_CODEERR + user_acc) if not err_cnt: err_cnt = 1 r.set_one(REDIS_KEY_PRE_CODEERR + user_acc, str(err_cnt), expt=60 * 5) else: err_cnt = int(err_cnt) + 1 r.set_one(REDIS_KEY_PRE_CODEERR + user_acc, str(err_cnt), expt=60 * 5) if int(err_cnt) >= 10: # 尝试次数大于10次,则需重新发送sms r.del_one(REDIS_KEY_PRE_SMSCODE + user_acc) r.del_one(REDIS_KEY_PRE_CODEERR + user_acc) # 同一个手机号验证码尝试次数过多 flog.warning(f"短信验证码登录时[{user_acc}]用户的短信验证码尝试次数过多") ret.msg = '短信验证码错误,请重新加载' return JsonResponse(ret.to_dic()) else: # 其他code_way,反馈失败 return JsonResponse(ret.to_dic()) # 查询用户信息 with connection() as con: user_rs = con.execute_sql( """select {table1_id}, {table1_account}, {table1_uname}, {table1_pwd}, {table1_salt}, {table1_wx}, {table1_qq}, {table1_phone}, {table1_email} from {table1} where ( {table1_account}=%(user_acc)s or {table1_phone}=%(user_acc)s or {table1_email}=%(user_acc)s)""".format( **SQL_DIC_USER), {"user_acc": user_acc} ) if user_rs and user_rs[0]: user_obj = user_rs[0] # 获取用户角色 role_rs = con.execute_sql( """select a.{tabler_id} as id from {tabler} a, {table3} b where a.{tabler_id}=b.{table3_rid} and b.{table3_uid}=%(uid)s""".format(**{**SQL_DIC_ROLES, **SQL_DIC_USERROLE}), {"uid": user_obj.id}) roles = ','.join([i.id for i in role_rs]) else: # 需校验密码的话,提示语要隐晦一些 ret.msg = '用户名或密码错误' if need_check_pwd else '用户信息不存在' return JsonResponse(ret.to_dic()) if need_check_pwd: # 接下来校验account-pwd 或 phone-pwd 或 email-pwd db_pwd = user_obj.pwdValue or '' db_salt = user_obj.pwdSalt # 密码校验 p = PwdCtrl() if db_pwd.upper() != p.create_md5(src_str=pwd, salt=db_salt): # 密码校验不通过 ret.msg = '用户名或密码错误' return JsonResponse(ret.to_dic()) # 记录登录日志 add_visitor(get_ip(req), apps.get_app_config('app_dr').name, 'login', user_obj.id) # 组织可见的user_info user_info = {"id": user_obj.id, "username": user_obj.userName, "account": user_obj.account} # 生成登录token # redis中额外放入元素roles等;但是不必返回前端 user_info_redis = {**user_info} user_info_redis["roles"] = roles token = create_token(user_info_redis) ret.data = {"token": token, "referer": req.META.get("HTTP_REFERER"), "userInfo": user_info} ret.msg = f'欢迎{user_obj.userName}' ret.code = ret.ResCode.succ return JsonResponse(ret.to_dic())
def sms_log_in(req, sid, vcode): """ 登录-短信验证码发送 短信验证码有效期5分钟 :param req: :param sid: :param vcode: :return: """ ret = ResModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 return JsonResponse(ret.to_dic()) phone = req.POST.get('phone', '') if not phone: ret.msg = '参数异常' return JsonResponse(ret.to_dic()) # 校验 r = RedisCtrl() verify_code = r.get_one(REDIS_KEY_PRE_SID + sid) if not verify_code: ret.msg = '验证码已过期' return JsonResponse(ret.to_dic()) if verify_code.upper() != vcode.upper(): ret.msg = '验证码错误' # 记录错误次数,多次失败则需重新请求sid err_cnt = r.get_one(REDIS_KEY_PRE_SIDERR + sid) if not err_cnt: err_cnt = 1 r.set_one(REDIS_KEY_PRE_SIDERR + sid, str(err_cnt), expt=60 * 5) else: err_cnt = int(err_cnt) + 1 r.set_one(REDIS_KEY_PRE_SIDERR + sid, str(err_cnt), expt=60 * 5) if int(err_cnt) >= 10: # 尝试次数大于10次,则需重新请求sid r.del_one(REDIS_KEY_PRE_SID + sid) r.del_one(REDIS_KEY_PRE_SIDERR + sid) return JsonResponse(ret.to_dic()) # 校验用户是否存在 with connection() as con: user_rs = con.execute_sql( """select {table1_id}, {table1_uname}, {table1_pwd}, {table1_salt}, {table1_wx}, {table1_qq}, {table1_phone}, {table1_email} from {table1} where {table1_phone}=%(phone)s """.format(**SQL_DIC_USER), {"phone": phone} ) if (not user_rs) or (not user_rs[0]): ret.msg = '当前用户信息不存在' return JsonResponse(ret.to_dic()) # 短信验证码发送 sms_code = "%06d" % randint(0, 999999) r.set_one(REDIS_KEY_PRE_SMSCODE + phone, sms_code, expt=60 * 5) sms_ret = sms_send(phone, {"code": sms_code}, ALI_SMS_TMPL_COMMON_CODE, "登录验证码") flog.debug(f"登录验证码发送:{sms_ret}") ret.msg = '已发送' ret.code = ret.ResCode.succ return JsonResponse(ret.to_dic())
def get_allmenu2list(req): """ get_allmenu2list :param req: :return: """ ret = ResModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 return JsonResponse(ret.to_dic()) # 中间件校验token后赋值USER_ID user_id = req.META.get("USER_ID") sql = """select * from (select prt.{tablem_id} {tablem_id}, prt.{tablem_sname}, prt.{tablem_aname}, prt.{tablem_href}, prt.{tablem_icon}, prt.{tablem_orderno} {tablem_orderno}, chd.{tablem_id} child_id, chd.{tablem_sname} child_show_name, chd.{tablem_aname} child_alias_name, chd.{tablem_href} child_href, chd.{tablem_icon} child_icon, chd.{tablem_orderno} child_order_no, chd.{tablem_pid} child_parent_id from {tablem} prt left join {tablem} chd on chd.{tablem_pid} = prt.{tablem_id} where prt.{tablem_pid} is null or prt.{tablem_pid} = '') menu where menu.child_id in ( select rm.{tablerm_mid} from {table1} u, {table3} ur, {tablerm} rm where u.{table1_id} = ur.{table3_uid} and ur.{table3_rid} = rm.{tablerm_rid} and u.{table1_id} = %(uid)s) or (menu.{tablem_id} in ( select rm.{tablerm_mid} from {table1} u, {table3} ur, {tablerm} rm where u.{table1_id} = ur.{table3_uid} and ur.{table3_rid} = rm.{tablerm_rid} and u.{table1_id} = %(uid)s) and (menu.child_id is null or menu.child_id = '')) order by menu.{tablem_orderno}, menu.child_order_no """ ret.data = [] menuIdLst = [] with connection() as con: menu_list = con.execute_sql( sql.format( **{ **SQL_DIC_MENU, **SQL_DIC_ROLEMENU, **SQL_DIC_USER, **SQL_DIC_USERROLE }), {"uid": user_id}) for row in menu_list: if row.id in menuIdLst: continue menuIdLst.append(row.id) menuObj = { "menuId": row.id, "showName": row.showName, "aliasName": row.aliasName, "href": row.href, "icon": row.icon, "orderNo": row.orderNo } if row.childId: menuObj["hasChildren"] = True parentId = row.id children = [] for child in menu_list: if parentId == child.childParentId: childObj = { "menuId": child.childId, "showName": child.childShowName, "aliasName": child.childAliasName, "href": child.childHref, "icon": child.childIcon, "orderNo": child.childOrderNo } children.append(childObj) menuObj["children"] = children else: menuObj["hasChildren"] = False ret.data.append(menuObj) ret.msg = '' ret.code = ret.ResCode.succ return JsonResponse(ret.to_dic())
def add_visitor(ip, app_type, visitor_type, link_id, bz=None): """ 添加到访客记录 :param ip: 同ip,5分钟内,只记录一次 :param app_type: :param visitor_type: :param link_id: 关联的id,可以不关联到业务表,用于记录访客随便输入的id :param bz:备注 :return: """ with connection() as con: rs = con.execute_sql( "SELECT count(1) as cnt FROM {table1} " \ "WHERE {table1_ip} = %(ip)s and {table1_atime}>=%(time)s and {table1_linkid}=%(link_id)s".format( **SQL_DIC_VISITOR), {'ip': ip, 'time': after_seconds(seconds=-1 * 5 * 60), 'link_id': link_id}) if not rs or rs[0].cnt == 0: # 若登记过该ip,则获取它的信息 cnt_ipstack = 0 cnt_iptaobao = 0 visitor_lat = '' visitor_lng = '' visitor_city = '' visitor_addr = '' visitor_isp = '' if is_internal_ip(ip): # 内网ip,免解析 cnt_ipstack = -1 else: rs_over = con.execute_sql( """SELECT {table1_lat},{table1_lng},{table1_city},{table1_addr},{table1_isp} FROM {table1} WHERE {table1_ip} = %(ip)s and ( {table1_ipstack}=-1 or {table1_iptaobao}=-1 ) limit 1""".format(**SQL_DIC_VISITOR), {'ip': ip}, hump=False) if rs_over and rs_over[0]: cnt_ipstack = -1 cnt_iptaobao = -1 visitor_lat = rs_over[0][SQL_DIC_VISITOR["table1_lat"]] visitor_lng = rs_over[0][SQL_DIC_VISITOR["table1_lng"]] visitor_city = rs_over[0][SQL_DIC_VISITOR["table1_city"]] visitor_addr = rs_over[0][SQL_DIC_VISITOR["table1_addr"]] visitor_isp = rs_over[0][SQL_DIC_VISITOR["table1_isp"]] # 登记信息 DrVisitorInfo.objects.create(id=str(uuid1()).replace('-', ''), visitor_ip=ip, add_time=timezone.now(), app_name=app_type, visitor_type=visitor_type, link_id=link_id, cnt_ipstack=cnt_ipstack, visitor_lat=visitor_lat, visitor_lng=visitor_lng, visitor_city=visitor_city, visitor_addr=visitor_addr, visitor_isp=visitor_isp, cnt_iptaobao=cnt_iptaobao, bz=bz) return 1 return 0
def get_list(req): """ 角色列表 :param req: :return: """ ret = ResPageModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 ret.code = ret.ResCode.fail return JsonResponse(ret.to_dic()) ret.page = req.GET.get('page', '1') ret.limit = req.GET.get('limit', PAGE_DEFAULT_LIMIT) order_field = hump2underline(req.GET.get('orderField', '')) order_type = req.GET.get('orderType', '') name = req.GET.get('name', '') rid = req.GET.get('rid', '') no_id = req.GET.get('noId', '') pid = req.GET.get('pId', '') # 数据查询sql sql = """select a.{tablem_id}, a.{tablem_pid}, a.{tablem_sname}, a.{tablem_aname}, a.{tablem_href}, a.{tablem_icon}, a.{tablem_orderno}, to_char(a.{tablem_atime}, 'yyyy-mm-dd hh24:mi:ss') as {tablem_atime}, b.{tablem_sname} as parent_name from {tablem} a left join {tablem} b on a.{tablem_pid}=b.{tablem_id} where 1=1 """ sql_count = """select count(1) as cnt from {tablem} a left join {tablem} b on a.{tablem_pid}=b.{tablem_id} where 1=1 """ # 查询条件 par_dic = {} if name: sql += " and (a.{tablem_sname} like %(name)s or a.{tablem_aname} like %(name)s)" sql_count += " and (a.{tablem_sname} like %(name)s or a.{tablem_aname} like %(name)s)" par_dic['name'] = f"%{name}%" if rid: sql += " and a.{tablem_id} = %(mid)s" sql_count += " and a.{tablem_id} = %(mid)s" par_dic['mid'] = rid if pid: sql += " and( a.{tablem_id} = %(pid)s or a.{tablem_pid} = %(pid)s)" sql_count += " and( a.{tablem_id} = %(pid)s or a.{tablem_pid} = %(pid)s)" par_dic['pid'] = pid if no_id: sql += " and a.{tablem_id} != %(nid)s" sql_count += " and a.{tablem_id} != %(nid)s" par_dic['nid'] = no_id # 排序 if order_field: sql += f" order by {order_field} {order_type}" else: sql += " order by a.{tabler_orderno} asc " with connection() as con: rs = con.execute_sql(sql_count.format(**SQL_DIC_MENU), par_dic) ret.rsCount = rs[0].cnt # dicorobj需为dict ret.data = con.execute_sql(sql.format(**SQL_DIC_MENU), par_dic, dicorobj="dict", page=ret.page, limit=ret.limit) ret.code = ret.ResCode.succ ret.msg = "" return JsonResponse(ret.to_dic())
def index_list(req): """ 2019/08/30 fls 查询blog列表 :param req: request :return: """ ret = ResPageModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 ret.code = ret.ResCode.fail ret.data = [] return JsonResponse(ret.to_dic()) blog_type = req.GET.get('type', '') title = req.GET.get('title', '') rlevel = req.GET.get('rlevel', '0') ret.page = req.GET.get('page', '1') ret.limit = req.GET.get('limit', PAGE_DEFAULT_LIMIT) # todo 权限level的控制 # 采用源生sql,方便where条件和分页,后期计划加入用户表的关联查询 sql = """select a.{table1_id},a.{table1_title},b.{table2_type_name},a.{table1_tags},a.{table1_notes}, a.{table1_aname}, to_char(a.{table1_atime}, 'yyyy-mm-dd hh24:mi:ss') as {table1_atime}, a.{table1_rlevel},a.{table1_rcnt},a.{table1_type_id} from {table1} a, {table2} b where a.{table1_type_id}=b.{table2_type_id} and a.{table1_rlevel}>=%(rlevel)s""".format( **SQL_DIC_BLOG) sql_count = """select count(1) as cnt from {table1} a, {table2} b where a.{table1_type_id}=b.{table2_type_id} and {table1_rlevel}>=%(rlevel)s""".format( **SQL_DIC_BLOG) # 查询条件 par_dic = {"rlevel": rlevel} if blog_type: # 类型 sql += " and a.{table1_type_id}=%(blog_type)s".format(**SQL_DIC_BLOG) sql_count += " and a.{table1_type_id}=%(blog_type)s".format( **SQL_DIC_BLOG) par_dic['blog_type'] = blog_type if title: sql += " and (a.{table1_title} like %(title)s or a.{table1_notes} like %(title)s)" sql_count += " and (a.{table1_title} like %(title)s or a.{table1_notes} like %(title)s)" par_dic['title'] = f"%{title}%" # 排序 sql += " order by a.{table1_atime} desc ".format(**SQL_DIC_BLOG) with connection() as con: rs = con.execute_sql(sql_count, par_dic) ret.rsCount = rs[0].cnt # dicorobj需为dict ret.data = con.execute_sql(sql, par_dic, dicorobj="dict", page=ret.page, limit=ret.limit) ret.code = ret.ResCode.succ return JsonResponse(ret.to_dic())
def get_list(req): """ 角色列表 :param req: :return: """ ret = ResPageModel() ret.msg = req_invalid_check(req) if ret.msg: # 请求合法性校验不通过 ret.code = ret.ResCode.fail return JsonResponse(ret.to_dic()) ret.page = req.GET.get('page', '1') ret.limit = req.GET.get('limit', PAGE_DEFAULT_LIMIT) order_field = hump2underline(req.GET.get('orderField', '')) order_type = req.GET.get('orderType', '') name = req.GET.get('name', '') rid = req.GET.get('id', '') # 查询用户角色,若为超管,则不限制visible条件 filter_visible = True token = req.META.get('HTTP_TOKEN') r = RedisCtrl() user_info = r.get_one(REDIS_KEY_PRE_TOKEN + token) if user_info: user_info = loads(user_info) if DrRoles.type_sa in user_info['roles'].split(","): # 超管,不需限制visible条件 filter_visible = False # 数据查询sql sql = """select {tabler_id}, {tabler_name}, {tabler_level}, {tabler_orderno}, {tabler_note}, {tabler_visible}, to_char({tabler_atime}, 'yyyy-mm-dd hh24:mi:ss') as {tabler_atime} from {tabler} a where 1=1 """ sql_count = """select count(1) as cnt from {tabler} a where 1=1 """ # 查询条件 par_dic = {} if filter_visible: sql += " and a.{tabler_visible}=%(visible)s" sql_count += " and a.{tabler_visible}=%(visible)s" par_dic['visible'] = '1' if name: sql += " and {tabler_name} like %(name)s" sql_count += " and {tabler_name} like %(name)s" par_dic['name'] = f"%{name}%" if rid: sql += " and {tabler_id} = %(rid)s" sql_count += " and {tabler_id} = %(rid)s" par_dic['rid'] = rid # 排序 if order_field: sql += f" order by {order_field} {order_type}" else: sql += " order by a.{tabler_orderno} asc " with connection() as con: rs = con.execute_sql(sql_count.format(**SQL_DIC_ROLES), par_dic) ret.rsCount = rs[0].cnt # dicorobj需为dict ret.data = con.execute_sql(sql.format(**SQL_DIC_ROLES), par_dic, dicorobj="dict", page=ret.page, limit=ret.limit) ret.code = ret.ResCode.succ ret.msg = "" return JsonResponse(ret.to_dic())