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)