def inser_staff(staff_id, name, moblie, email, password, last_login_ip, last_login_time, create_uid, modify_uid, create_time, modify_time, user_status):
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
print(staff_id, name, moblie, email, password, last_login_ip, last_login_time, create_uid, modify_uid, create_time, modify_time, user_status)
sql = "insert into `staff`(`staff_id`, `name`, `moblie`, `email`, `password`, `last_login_ip`, last_login_time, `create_uid`, `modify_uid`, `create_time`, `modify_time`, `status`)" \
" values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (staff_id,name,moblie,email,password,last_login_ip, last_login_time,create_uid,modify_uid,create_time,modify_time,user_status);
print(sql)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
cursor.execute(sql)
# 使用fetall()获取全部数据
mysql_db.commit()
cursor.close()
def admin_resource():
for user in read_line:
print(user[1])
if (user[0] != '_id'):
sql = "SELECT user_login from user where user_id= "+ user[1] + " or staff_id=" +user[1]+" limit 1"
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
print(sql)
cursor.execute(sql)
data = cursor.fetchall()
print(data[0][0])
userName = data[0][0];
try:
print(bson.int64.Int64(user[1]))
if(len(data)>0):
collection.update_many({"create_uid":bson.int64.Int64(user[1])},{"$set":{"create_uname":userName}})
#data = collection.find({"create_uid":bin(user[1])})
#print(data[0])
except Exception as e:
print(e)
mysql_db.commit()
cursor.close()
def admin_resource():
sql = "SELECT distinct st.staff_id from staff st left join `user` u on st.moblie= u.mobile where u.user_type =1 or u.user_login ='******'"
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
# print(sql)
cursor.execute(sql)
# 使用fetall()获取全部数据
data = cursor.fetchall()
print(data)
mysql_db.commit()
cursor.close()
s = generator(10, 10)
for d in data:
# id生成
staff_manger_resource_id = s.__next__()
#print(staff_manger_resource_id)
staff_id = d[0]
print(staff_id)
try:
inser_staff_manger_resource(staff_manger_resource_id, staff_id, 8)
inser_staff_manger_resource(staff_manger_resource_id, staff_id, 9)
except Exception as e:
print(e)
def flush_staff_resource_ems():
for mobile in read_mobile_line:
print(mobile[0])
if (mobile[0] != 'mobile'):
sql = "SELECT st.staff_id from staff st where st.moblie= '" + mobile[
0] + "'"
print(sql)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
# print(sql)
cursor.execute(sql)
# 使用fetall()获取全部数据
data = cursor.fetchall()
print(data)
mysql_db.commit()
cursor.close()
s = generator(10, 10)
for d in data:
# id生成
staff_manger_resource_id = s.__next__()
# print(staff_manger_resource_id)
staff_id = d[0]
print(staff_id)
try:
inser_staff_manger_resource(staff_manger_resource_id,
staff_id, 9)
except Exception as e:
print(e)
def update_chapter_class_status(chapter_class_id):
sql = "update chapter_class set settlement_status=1 where chapter_class_id=%d" % (
chapter_class_id)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
cursor.execute(sql)
# 使用fetall()获取全部数据
mysql_db.commit()
cursor.close()
def class_student_clear():
sql = "update class_model set student_num=0"
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
# print(sql)
cursor.execute(sql)
# 使用fetall()获取全部数据
mysql_db.commit()
cursor.close()
def inser_staff_manger_resource(staff_manger_resource_id,staff_id,manger_resource_id):
sql = "insert into `staff_manger_resource`(staff_manger_resource_id,manger_resource_id,staff_id,create_uid,modify_uid,create_time,modify_time) " \
"VALUES (%d,%d,%d,%d,%d,%d,%d)" % (staff_manger_resource_id, manger_resource_id, staff_id, staff_id, staff_id, time.time(), time.time())
print(sql)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
cursor.execute(sql)
# 使用fetall()获取全部数据
mysql_db.commit()
cursor.close()
def update_product(course_id, img):
sql = "update product set product_app_img='%s',product_pc_img='%s',product_other_img='%s' where product_id=%d" % (
img, img, img, course_id)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
# print(sql)
cursor.execute(sql)
# 使用fetall()获取全部数据
mysql_db.commit()
cursor.close()
def update_class_student_num(student_num, class_id):
sql = "update class_model set student_num=%d where class_id=%d" % (
student_num, class_id)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
# print(sql)
cursor.execute(sql)
# 使用fetall()获取全部数据
mysql_db.commit()
cursor.close()
def update_product(product_id, product_service):
sql = "update product set product_service='%s' where product_id=%d" % (
product_service, product_id)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
# print(sql)
cursor.execute(sql)
# 使用fetall()获取全部数据
mysql_db.commit()
cursor.close()
def update_class_student(class_student_id, chapter_id):
sql = "update class_student set chapter_ids=CONCAT(chapter_ids,'%s,') where class_student_id=%d" % (
chapter_id, class_student_id)
#print("追加SQL:"+sql)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
cursor.execute(sql)
# 使用fetall()获取全部数据
mysql_db.commit()
cursor.close()
def get_chapter_info():
sql = "select chapter_id,start_time,end_time,course_id,class_id,chapter_class_id from chapter_class where start_time<unix_timestamp(NOW()) and `status`=1 and settlement_status=0 order by start_time asc"
print("查询未结转的课:" + sql)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
cursor.execute(sql)
# 使用fetall()获取全部数据
data = cursor.fetchall()
mysql_db.commit()
cursor.close()
return data
def get_class_student(class_id):
sql = "select product_id,order_id,student_id,class_student_id,chapter_ids from class_student where class_id=%d and `status`=1" % (
class_id)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
#print("查询class_student: " + sql)
cursor.execute(sql)
# 使用fetall()获取全部数据
data = cursor.fetchall()
if not data:
return None
return data
def course_num(class_id):
sql = "select count(*) from chapter_class where class_id=%d" % (class_id)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
cursor.execute(sql)
# 使用fetall()获取全部数据
data = cursor.fetchone()
mysql_db.commit()
cursor.close()
if not data:
return None
return data[0]
def get_order_info(order_id):
sql = "select pay_tag_type,pay_type from `order` where order_id=%d" % (
order_id)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
cursor.execute(sql)
# 使用fetall()获取全部数据
data = cursor.fetchone()
mysql_db.commit()
cursor.close()
if not data:
return None
return data
def get_course_info(course_id):
sql = "select course_name,quarter_mode,part_mode,grade_mode,subject_mode,class_mode,course_mode from course where course_id=%d" % (
course_id)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
cursor.execute(sql)
# 使用fetall()获取全部数据
data = cursor.fetchone()
mysql_db.commit()
cursor.close()
if not data:
return None
return data
def get_chapter_count(class_id, start_time):
sql = "select * from chapter_class where start_time<%d and class_id=%d and `status`=1 order by start_time asc" % (
start_time, class_id)
print(sql)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
cursor.execute(sql)
# 使用fetall()获取全部数据
data = cursor.fetchall()
mysql_db.commit()
cursor.close()
return len(data)
def get_settlement_count(student_id, chapter_class_id):
sql = "select * from settlement where student_id=%d and chapter_class_id=%d " % (
student_id, chapter_class_id)
print(sql)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
cursor.execute(sql)
# 使用fetall()获取全部数据
data = cursor.fetchall()
mysql_db.commit()
cursor.close()
return len(data)
def get_student_info(student_id):
sql = "select stu_name,stu_mobile from student where student_id=%d" % (
student_id)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
cursor.execute(sql)
# 使用fetall()获取全部数据
data = cursor.fetchone()
mysql_db.commit()
cursor.close()
if not data:
return None
return data
def order_detail_price(order_id, product_id):
sql = "select price,order_detail_id,product_name,teacher_name,order_id,assistant_code from order_detail where `status`=1 and product_id=%d and order_id=%d" % (
product_id, order_id)
print("查询订单详情:" + sql)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
cursor.execute(sql)
# 使用fetall()获取全部数据
data = cursor.fetchone()
mysql_db.commit()
cursor.close()
if not data:
return None
return data
def class_student_teacher():
sql = "select class_id_main,count(*) as num from class_student group by class_id_main"
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
# print(sql)
cursor.execute(sql)
# 使用fetall()获取全部数据
data = cursor.fetchall()
mysql_db.commit()
cursor.close()
for d in data:
update_class_student_num(d[1], d[0])
print(d[1], d[0])
def get_assistant_info(assistant_code):
sql = "select u.user_nickname,cm.stage from class_model cm,user u where cm.assistant_id=u.user_id and assistant_code='%s'" % (
assistant_code)
print("查询助教名称:" + sql)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
cursor.execute(sql)
# 使用fetall()获取全部数据
data = cursor.fetchone()
mysql_db.commit()
cursor.close()
if not data:
return None
return data
def insert_settlement(settlement_id, student_id, product_id, order_id,
order_detail_id, settlement_num, amount, start_time,
end_time, month, remark, student_name, student_mobile,
year_detail, quarter_mode, grade_mode, subject_mode,
product_name, course_mode, teacher_main_name, pay_type,
pay_os, chapter_class_id, assistant_name, stage):
sql="insert into `settlement`(settlement_id,student_id,product_id,order_id,order_detail_id,settlement_num,amount,settlement_mode,course_start_time,course_end_time,create_time,`month`,remark,student_name,stu_mobile,year_detail,quarter_mode,grade_mode,subject_mode,product_name,course_mode,teacher_main_name,pay_type,pay_os,chapter_class_id,assistant_name,stage)" \
" VALUES(%d,%d,%d,%d,%d,%d,%.2f,%d,%d,%d,%d,'%s','%s','%s','%s',%d,'%s','%s','%s','%s','%s','%s','%s','%s',%d,'%s','%s')" % (settlement_id,student_id,product_id,order_id,order_detail_id,settlement_num,amount,1,start_time,end_time,int(time.time()),str(month),str(remark),student_name,student_mobile,year_detail,quarter_mode,grade_mode,subject_mode,product_name,course_mode,teacher_main_name,pay_type,pay_os,chapter_class_id,assistant_name,stage)
print(sql)
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
cursor.execute(sql)
# 使用fetall()获取全部数据
mysql_db.commit()
cursor.close()
def class_model():
sql = "SELECT product_id,product_service FROM `product` where product_service is not null and LENGTH(product_service)>3"
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
# print(sql)
cursor.execute(sql)
# 使用fetall()获取全部数据
data = cursor.fetchall()
mysql_db.commit()
cursor.close()
for d in data:
str_service = d[1]
if str_service[0] == ',':
print(str_service)
str_service = str_service[1:]
update_product(d[0], str_service)
print(str_service)
def product():
sql = "select product_id,product_app_img from product where product_app_img like '%coursemanage%'"
cursor = mysql_db.cursor()
mysql_db.ping(reconnect=True)
# print(sql)
cursor.execute(sql)
# 使用fetall()获取全部数据
data = cursor.fetchall()
mysql_db.commit()
cursor.close()
for d in data:
img = str(d[1]).split('/')
my_img = ''
if len(img) > 0:
my_img = 'https://resource.puxinwangxiao.com/' + img[len(img) - 1]
update_product(d[0], my_img)
print(d[0], my_img)
