def sql_select_class(content_class_name=None,school_class_id=None): #
session = DBSession()
class_sql_obj = None
if content_class_name:
class_sql_obj = dao_find.find_school_class_by_class_name(content_class_name)
elif school_class_id:
class_sql_obj = dao_find.find_school_class_by_id(school_class_id)
session.close()
return class_sql_obj
def find_school_class_user_by_user_id(user_id):
"""
通过发起者的user_id查询返回用户信息
:param user_id:
:return: object
"""
son = DBSession()
ob = son.query(Edu_School_Class_User).filter_by(user_id=user_id).first()
son.close()
return ob
def find_class_by_group_id(group_id):
"""
通过班级group_id查询返回所有当前班级信息
:param group_id:
:return: object
"""
son = DBSession()
ob = son.query(Edu_School_Class).filter_by(group_id=group_id).first()
son.close()
return ob
def find_school_class_by_org_id(org_id):
"""
通过org_id 查询返回班级信息
:param org_id:
:return:object
"""
son = DBSession()
ob = son.query(Edu_School_Class).filter_by(org_id=org_id).first()
son.close()
return ob
def find_edu_robot_by_uin(robot_uin):
"""
通过机器人uin查询返回机器人信息
:param robot_uin:
:return: object
"""
son = DBSession()
ob = son.query(EduWxRobot).filter_by(robot_uin=robot_uin).first()
son.close()
return ob
def find_school_class_by_uin(uin):
"""
通过uin查询返回班级信息
:param uin:
:return: object
"""
son = DBSession()
ob = son.query(Edu_School_Class).filter_by(uin=uin).first()
son.close()
return ob
def find_room_by_room_uin(room_uin):
"""
通过room_uin查询返回群信息
:param user_name:
:param school_class_id:
:return: object
"""
son = DBSession()
ob = son.query(EduWxRobotChatRoom).filter_by(room_uin=room_uin).first()
son.close()
return ob
def find_class_user_by_id(school_class_id):
"""
通过班级id查询返回所有当前班级用户信息
:param school_class_id:
:return: objects
"""
son = DBSession()
obs = son.query(Edu_School_Class_User).filter_by(
school_class_id=school_class_id).all()
son.close()
return obs
def find_class_user_by_weixin(weixin):
"""
通过用户微信号查询返回当前班级用户信息
:param weixin:
:return: objects
"""
son = DBSession()
obs = son.query(Edu_School_Class_User).filter_by(weixin=weixin).order_by(
Edu_School_Class_User.school_class_id.desc()).all()
son.close()
return obs
def find_school_class_by_id(school_class_id):
"""
通过班级id查询返回当前班级信息
:param school_class_id:
:return: object
"""
son = DBSession()
ob = son.query(Edu_School_Class).filter_by(
school_class_id=school_class_id).first()
son.close()
return ob
def find_school_class_user_by_weixin_and_class_id(weixin, school_class_id):
"""
通过微信号班级id查询返回单个用户信息
:param weixin:
:param school_class_id:
:return:object
"""
son = DBSession()
ob = son.query(Edu_School_Class_User).filter_by(
weixin=weixin, school_class_id=school_class_id).first()
son.close()
return ob
def find_class_user_by_user_name_and_class_id(user_name, school_class_id):
"""
通过姓名和班级id查询返回用户信息
:param user_name:
:param school_class_id:
:return: object
"""
son = DBSession()
ob = son.query(Edu_School_Class_User).filter_by(
school_class_id=school_class_id, user_name=user_name).first()
son.close()
return ob
def find_class_user_by_user_name(user_name):
"""
通过用户名称查询返回所有当前班级用户信息
:param user_name:
:return: objects
"""
son = DBSession()
obs = son.query(Edu_School_Class_User).filter_by(
user_name=user_name).order_by(
Edu_School_Class_User.school_class_id.desc()).all()
son.close()
return obs
def find_school_class_by_name(school_class_name):
"""
:param school_class_name:
:return:
"""
son = DBSession()
obs = son.query(Edu_School_Class).filter_by(
school_class_name=school_class_name).order_by(
Edu_School_Class.school_class_id.desc()).all()
son.close()
return obs
def find_school_class_by_class_name(school_class_name):
"""
通过班级名称查询返回班级信息
:param schoo_class_name:
:return:object
"""
son = DBSession()
ob = son.query(Edu_School_Class).filter_by(
school_class_name=school_class_name).order_by(
Edu_School_Class.school_class_id.desc()).first()
son.close()
return ob
def find_school_class_user_by_nick_name(nick_name):
"""
通过用户的个人昵称查询返回当前班级的信息
:param nick_name:
:return:object
"""
son = DBSession()
obs = son.query(Edu_School_Class_User).filter_by(
nick_name=nick_name).order_by(
Edu_School_Class_User.school_class_id.desc()).all()
son.close()
return obs
def find_school_class_by_org_id_nick_name(org_id, school_class_name):
"""
通过org_id,nick_name查询返回班级信息
:param org_id:
:param nick_name:
:return:object
"""
son = DBSession()
ob = son.query(Edu_School_Class).filter_by(
org_id=org_id, school_class_name=school_class_name).first()
son.close()
return ob
def find_school_class_user_by_nick_name_and_id(nick_name, school_class_id):
"""
通过用户nick_name 和所在班级的id返回用户信息
:param nick_name:
:param school_class_id:
:return:object
"""
son = DBSession()
obs = son.query(Edu_School_Class_User).filter_by(
nick_name=nick_name, school_class_id=school_class_id).order_by(
Edu_School_Class_User.school_class_id.desc()).all()
son.close()
return obs
def find_school_class_user_by_displayname_and_class_id(displayname,
school_class_id):
"""
通过微信号班级id查询返回单个用户信息
:param displayname:
:param school_class_id:
:return:object
"""
son = DBSession()
ob = son.query(Edu_School_Class_User).filter_by(
displayname=displayname, school_class_id=school_class_id).first()
son.close()
return ob
def update_data(record, record_id, attrs):
"""
更新记录的attrs字段，不允许为空
:param record: 已经赋值的对象
:param record_id: 需要更新的对象主键
:param attrs: 需要更新的字段名的 list， 如["product_count","product_name"]
:return:
"""
son = DBSession()
# record_id = getattr(record, get_pk(record))
print "========update_data record_id===========", record_id
if int(record_id) > 0:
if len(attrs) == 0:
return
update_columns = {}
for a in attrs:
update_columns[a] = getattr(record, a)
if update_columns:
son.query(record.__class__).filter_by(**{
get_pk(record): record_id
}).update(update_columns)
son.commit()
son.close()
return record_id
else:
return False
def find_data_by_sql(sql):
"""
通过sql查询并删除
:param sql:
:return:
"""
son = DBSession()
son.execute(sql)
son.commit()
son.close()
return
def into_edu_school_notice(content, actual_nick_name, actual_user_name, q_username, meg_type):
session = DBSession()
esc = dao_find.find_class_by_group_id(q_username)
escu = dao_find.find_school_class_user_by_user_id(actual_user_name)
esc_id, escu_id = 0, 0
if esc:
esc_id = esc.school_class_id
if escu:
escu_id = escu.user_id
esn = Edu_School_Notice(from_id=escu_id, school_class_id=esc_id, content=content, msg_type="Text")
session.add(esn)
session.commit()
esn_id = esn.notice_id
session.close()
return esn_id
def insert_data(record):
"""
插入记录，并返回id
:param record: 初始化对象
:return: id
:example:
def add_course_dynamic(data)
dynamic = EduCourseDynamic()
set_data_to_record(dynamic, data)
return insert_data(dynamic)
"""
son = DBSession()
son.add(record)
son.commit()
record_id = getattr(record, get_pk(record))
son.close()
# flog.info("insert a record --" + str(record) + "id--: " + str(record_id))
print "===========record_id================="
print record_id
print "====================================="
return record_id
def into_friend_data_text2(jqr, content, to_user_name, search_friend, msg_type, created_time=None):
session = DBSession()
if search_friend["Alias"]:
Alias = search_friend["Alias"]
else:
Alias = search_friend["Uin"]
type_list = [u'Picture', u'Recording', u'Attachment', u'Video']
if msg_type in type_list:
msg_type = msg_type
else:
msg_type = "text"
data = dict()
data["robot_uin"] = jqr["jqr_uin"]
data["robot_user_name"] = to_user_name
data["content"] = content
data["robot_nick_name"] = jqr["jqr_name"]
data["friend_user_name"] = Alias
data["friend_uin"] = Alias
data["friend_nick_name"] = Alias
data["msg_type"] = msg_type
data["created_time"] = created_time
return dao_add.add_friend_chat_data(data)
def into_chat_room_data(data):
session = DBSession()
room_uin = data["room_uin"]
room_id = 0
if room_uin:
print "if room-uin===================="
print "=room-uin=", room_uin
room = dao_find.find_room_by_room_uin(room_uin)
print "====room===="
print room
if room:
print "room_id=", room.id
room_id = room.id
# else:
# room_id = dao_add.add_edu_robot_room_data(data)
elif data["room_nick_name"]:
print "if room_nick_name====================", data["room_nick_name"]
room = dao_find.find_room_by_room_nick_name(data["room_nick_name"])
print room
if room:
room_id = room.id
# else:
# room_id = dao_add.add_edu_robot_room_data(data)
print "=============群聊天信息存入数据库============="
dataa = dict()
dataa["room_id"] = room_id
dataa["room_uin"] = room_uin
dataa["room_user_name"] = data["room_user_name"]
dataa["content"] = data["content"]
dataa["msg_type"] = data["msg_type"]
dataa["room_nick_name"] = data["room_nick_name"]
dataa["from_user_name"] = data["from_user_name"]
dataa["from_nick_name"] = data["from_nick_name"]
dataa["head_img"] = data["head_img"]
robot_id = dao_add.add_edu_robot_room_chat_data(dataa)
def into_q_memberlist(new_instance_b, memberlist):
print "##memberlist#", memberlist
no_alias_list = [] # 没有微信号用户列表
# invite_code = uuid.uuid1()
own_user_Alias=""
own_user_AttrStatus=""
own_user_NickName=""
user_list = memberlist["MemberList"]
q_username = memberlist["UserName"]
uin = memberlist["Uin"]
EncryChatRoomId = memberlist["EncryChatRoomId"]
"""获取当前群信息"""
xcv = new_instance_b.update_chatroom(userName=EncryChatRoomId)
KeyWord=xcv["KeyWord"]
print "########KeyWord#######",KeyWord
KeyWord = KeyWord
print "###qun_uin###", uin
self_user = memberlist["self"]
chatroomowner = ""
if "ChatRoomOwner" in memberlist:
print "%%%%%%%%%%%%%%@@@@@@@@@@@@@", memberlist["ChatRoomOwner"]
chatroomowner = memberlist["ChatRoomOwner"]
"""获取群创建者信息"""
own_user = new_instance_b.search_friends(userName=chatroomowner)
print "own_userown_userown_user@@@@@@@@@@@", own_user["Alias"], own_user["AttrStatus"], own_user["NickName"]
own_user_Alias=own_user["Alias"]
own_user_AttrStatus=own_user["AttrStatus"]
own_user_NickName=own_user["NickName"]
else:
print "%%%%%%%%%%%%%%@@@@@@@@@@@@@"
# chatroomowner = memberlist["ChatRoomOwner"] # 房间创建者的user_name_id
# 房间创建者的user_name_id
q_nickname = memberlist["NickName"]
if uin and uin != 0 and uin != "0":
q_ = dao_find.find_school_class_by_uin(uin)
else:
q_ = dao_find.find_school_class_by_group_id(q_username)
if not q_:
# q_ = dao_base.find_school_class_by_org_id(KeyWord)
q_ = dao_find.find_school_class_by_org_id_nick_name(KeyWord, q_nickname)
session = DBSession()
if not q_:
data = dict()
data['group_id'] = q_username
data['uin'] = uin
data['chatroomowner'] = chatroomowner
data['org_id'] = KeyWord
data['school_class_name'] = q_nickname
data['invite_code'] = json.dumps({"Alias": own_user_Alias, "AttrStatus": own_user_AttrStatus,
"NickName": own_user_NickName})
q_ = Edu_School_Class(group_id=q_username, uin=uin, chatroomowner=chatroomowner, org_id=KeyWord,
school_class_name=q_nickname,
invite_code=json.dumps({"Alias": own_user_Alias,
"AttrStatus": own_user_AttrStatus,
"NickName": own_user_NickName}))
session.add(q_)
session.commit()
if q_username:
q_.group_id = q_username
if uin and len(uin) >= 3:
q_.uin = uin
if chatroomowner:
q_.chatroomowner = chatroomowner
if q_nickname:
q_.school_class_name = q_nickname
q_.invite_code = json.dumps({"Alias": own_user_Alias, "AttrStatus": own_user_AttrStatus, "NickName": own_user_NickName})
q_.org_id = KeyWord
q_.chatroomowner = chatroomowner
session.add(q_)
session.commit()
school_class_id = q_.school_class_id
session.close()
for i in user_list:
if i["Alias"]:
print "####Alias####", i["Alias"]
else:
print "####notAlias ####", i
q_user = None
username = i["UserName"] # userid
nickname = i["NickName"] # 昵称
displayname = i["DisplayName"] # 群昵称
alias = i["Alias"] # 微信号
province = i["Province"] # 省份
city = i["City"] # 城市
sex = i["Sex"] # 性别
signature = i["Signature"] # 签名
KeyWord = i["KeyWord"] # 关键字
#headimgurl = i["HeadImgUrl"] # 头像地址
headimgurl = "" # 头像地址
from robot.util.oss import itchat_upload_images_to_oss
if username:
"""获取微信头像存入数据库"""
avatar = new_instance_b.get_head_img(userName=username)
headimgurl = itchat_upload_images_to_oss(avatar)
session = DBSession()
if alias:
q_user = session.query(Edu_School_Class_User).filter_by(school_class_id=school_class_id).filter_by(weixin=alias).first()
#q_user = dao_base.find_school_class_user_by_weixin_and_class_id(alias, school_class_id)
if q_user:
q_user.displayname = displayname
q_user.nickname = nickname
q_user.username = username
q_user.headimgurl = headimgurl
q_user.signature = signature
session.add(q_user)
session.commit()
elif displayname:
q_user = session.query(Edu_School_Class_User).filter_by(school_class_id=school_class_id).filter_by(displayname=displayname).first()
#q_user = dao_base.find_school_class_user_by_displayname_and_class_id(displayname, school_class_id)
if q_user:
q_user.weixin = alias
q_user.nickname = nickname
q_user.username = username
q_user.headimgurl = headimgurl
q_user.signature = signature
session.add(q_user)
session.commit()
else:
#当群用户没修改群昵称时
q_user = Edu_School_Class_User(user_name=username, headimgurl=headimgurl, displayname=displayname,
weixin=alias, signature=signature, school_class_id=school_class_id,
province=province, city=city, sex=sex, nick_name=nickname)
session.add(q_user)
session.commit()
elif displayname:
q_user = session.query(Edu_School_Class_User).filter_by(school_class_id=school_class_id).filter_by(displayname=displayname).first()
#q_user = dao_base.find_school_class_user_by_displayname_and_class_id(displayname, school_class_id)
if q_user:
if KeyWord:
q_user.weixin = KeyWord
q_user.username = username
q_user.headimgurl = headimgurl
q_user.signature = signature
session.add(q_user)
session.commit()
else:
q_user = None # 没有设置微信号
no_alias_list.append({"username": username, "nickname": nickname, "displayname": displayname})
if not q_user and displayname:
q_user = Edu_School_Class_User(user_name=username, headimgurl=headimgurl, displayname=displayname,
weixin=alias, signature=signature, school_class_id=school_class_id,
province=province, city=city, sex=sex, nick_name=nickname)
session.add(q_user)
session.commit()
if q_user and q_user != "error":
q_user.username = username
q_user.nickname = nickname
q_user.headimgurl = headimgurl
q_user.displayname = displayname
q_user.alias = alias
q_user.province = province
q_user.sex = sex
q_user.headimgurl = ""
q_user.signature = signature
session.add(q_user)
session.commit()
session.close()
return school_class_id
def sql_search_user_role(school_class_id):
session = DBSession()
#user_list = session.query(Edu_School_Class_User).filter_by(school_class_id=school_class_id).all()
user_list = dao_find.find_class_user_by_id(school_class_id)
session.close()
if not user_list:
user_list = []
else:
for u in user_list:
print "=============uuuuuuu================"
print u
if u.displayname != None:
print "====u.displayname!=None======"
print u.displayname
if u'老师' in u.displayname:
#user_role = u.user_role
if u.user_role != 1:
u.user_role = 1
session.add(u)
session.commit()
for p in PARENTS:
if p in u.displayname:
student_name = (u.displayname).split(p)[0]
#student_user = session.query(Edu_School_Class_User).filter_by(school_class_id=school_class_id, user_name=student_name).first()
student_user = dao_find.find_class_user_by_user_name_and_class_id(
student_name, school_class_id)
if student_user:
student_id = student_user.user_id
else:
student = Edu_School_Class_User(
user_name=student_name,
school_class_id=school_class_id,
user_role=3)
session.add(student) #存储学生信息
session.commit()
student_id = student.user_id
u.student_id = student_id #父母存储学生id
if u.user_role != 2:
u.user_role = 2
session.add(u)
session.commit()
return user_list
def get_class_id(alias, nick_name, from_user_name, content,content_name):
error_info = []
session = DBSession()
q_class_id_list = []
q_class_user_name_list = []
q_class_list = []
select_class_id_list = None
ewrcrd2_notice_id = ""
if content_name: # 是否制定班级名称
select_class_id_list = dao_find.find_school_class_by_name(content_name)
if select_class_id_list:
school_class_id = select_class_id_list[0].school_class_id
if alias:
print "####alias####", alias
q_user_all = dao_find.find_school_class_user_by_weixin_and_school_class_id(alias, school_class_id)
# q_user_all = session.query(Edu_School_Class_User).filter_by(weixin=alias).filter_by(weixin=alias,school_class_id=school_class_id).order_by(Edu_School_Class_User.school_class_id.desc()).all()
elif from_user_name:
#q_user_all = session.query(Edu_School_Class_User).filter_by(user_name=from_user_name,school_class_id=school_class_id).order_by(Edu_School_Class_User.school_class_id.desc()).all()
q_user_all = dao_find.find_school_class_user_by_user_name_and_id(from_user_name, school_class_id)
if not q_user_all:
#q_user_all = session.query(Edu_School_Class_User).filter_by(nick_name=nick_name,school_class_id=school_class_id).order_by(Edu_School_Class_User.school_class_id.desc()).all()
q_user_all = dao_find.find_school_class_user_by_nick_name_and_id(nick_name, school_class_id)
elif nick_name:
#q_user_all = session.query(Edu_School_Class_User).filter_by(nick_name=nick_name,school_class_id=school_class_id).order_by(Edu_School_Class_User.school_class_id.desc()).all()
q_user_all = dao_find.find_school_class_user_by_nick_name_and_id(nick_name, school_class_id)
else:
if alias:
print "####alias####", alias
#q_user_all = session.query(Edu_School_Class_User).filter_by(weixin=alias).order_by(Edu_School_Class_User.school_class_id.desc()).all()
q_user_all = dao_find.find_class_user_by_weixin(alias)
elif from_user_name:
#q_user_all = session.query(Edu_School_Class_User).filter_by(user_name=from_user_name).order_by(Edu_School_Class_User.school_class_id.desc()).all()
q_user_all = dao_find.find_class_user_by_user_name(from_user_name)
if not q_user_all:
#q_user_all = session.query(Edu_School_Class_User).filter_by(nick_name=nick_name).order_by(Edu_School_Class_User.school_class_id.desc()).all()
q_user_all = dao_find.find_school_class_user_by_nick_name(nick_name)
elif nick_name:
#q_user_all = session.query(Edu_School_Class_User).filter_by(nick_name=nick_name).order_by(Edu_School_Class_User.school_class_id.desc()).all()
q_user_all = dao_find.find_school_class_user_by_nick_name(nick_name)
if q_user_all:
for q_user in q_user_all:
q_user_displayname = q_user.displayname
if q_user_displayname[-2:] == u"老师":
print u"q_user是老师", q_user_displayname, q_user.school_class_id
q_class_list.append(q_user)
q_class_id_list.append(q_user.school_class_id)
#q_class = session.query(Edu_School_Class).filter_by(school_class_id=q_user.school_class_id).first()
q_class = dao_find.find_school_class_by_id(q_user.school_class_id)
if q_class:
print "##q_class.school_class_name####", q_class.school_class_name
q_class_info = itchat.search_chatrooms(name=q_class.school_class_name)
if q_class_info:
q_class_info = q_class_info[0]
q_class_user_name_list.append(q_class_info["UserName"])
q_class.user_name = q_class_info["UserName"]
ewrcrd2 = Edu_School_Notice(from_id=q_user.user_id, school_class_id=q_user.school_class_id,
content=content, msg_type=0)
session.add(q_class)
session.add(ewrcrd2)
session.commit()
ewrcrd2_notice_id = ewrcrd2.notice_id
else:
error_info.append("q_class_info_error")
# print "q_class_info####", q_class_info
session.close()
print "######q_class_id_list######",q_class_id_list
return q_class_id_list, q_class_list, q_class_user_name_list, ewrcrd2_notice_id, error_info
def index_class_update_sys(uin, nick_name, user_name, KeyWord):
session = DBSession()
q_class = dao_find.find_school_class_by_uin(uin)
if q_class:
q_class.uin = uin
q_class.nick_name = nick_name
q_class.user_name = user_name
q_class.org_id = KeyWord
session.add(q_class)
session.commit()
else:
q_class = dao_find.find_school_class_by_org_id_nick_name(KeyWord, nick_name)
if q_class:
if not q_class.uin:
q_class.uin = uin
q_class.nick_name = nick_name
q_class.user_name = user_name
q_class.org_id = KeyWord
session.add(q_class)
session.commit()
else:
q_class = Edu_School_Class(group_id=user_name, uin=uin, chatroomowner="", org_id=KeyWord,
school_class_name=nick_name)
session.add(q_class)
session.commit()
session.close()
def friend_update_sys(uin, nick_name, alias, user_name):
session = DBSession()
friend_all = dao_find.find_class_user_by_weixin(str(alias))
if friend_all:
print "friend_all###", friend_all
for friend in friend_all:
friend.uin = uin
friend.nick_name = nick_name
friend.user_name = user_name
session.add(friend)
session.commit()
else:
print "alias", alias
friend = Edu_School_Class_User(user_name=user_name, headimgurl="", displayname="", uid=uin, weixin=alias,
signature="", school_class_id=0, province="", city="", sex=0,
nick_name=nick_name)
session.add(friend)
session.commit()
session.close()
