#!/usr/bin/env python # -*- coding: utf-8 -*- from data_fixture.mysql_db import DB from data_fixture import config_data as Data from datetime import datetime, date, timedelta import time from data_fixture.config_data import HOST_BOSS import requests import calendar import uuid db = DB() ## ********************************************************************************************************************* ## 验证码 ## ********************************************************************************************************************* def fet_authCode(mobile): url = HOST_BOSS + "/ozing/timer/user/fetchAuthCode" headers = {'Accept': '*/*'} postData = {'mobile': mobile, 'type': 'general'} r = requests.post(url, headers=headers, data=postData) result = r.json() if result['status'] == 100: return result['jsessionid'] else : raise FetchException('fetch auth code Error!') class FetchException(Exception): pass ## ********************************************************************************************************************* ## 保卡 ## ********************************************************************************************************************* # 保卡数据 -- 新建保卡 def pre_elecCard(device_cus_bind='0', device_sam='0', device_cus_unbind='0'): if device_cus_bind != '0': # 客机，已绑定保卡 select_customermachine = "select * from acornuser.ozing_customermachine where deviceNumber = '{}' ".format(device_cus_bind) select_machine_cus = "SELECT * FROM acornuser.ozing_machine where deviceNumber = '{}' ".format(device_cus_bind) insert_customermachine_tabel = 'acornuser.ozing_customermachine' insert_customermachine_data = { 'deviceNumber': device_cus_bind, 'userId': Data.USER_ID, 'customerName':'测试customer', 'customerAddress':'内蒙古巴彦淖尔市', 'customerPhone':'13822222222', 'buyTime': datetime.now().strftime("%Y-%m-%d %H:%M:%S"), 'buyAddress':'内蒙古巴彦淖尔市', 'alterSaleCall':'51518888', 'cmstate': '1', 'createTime': datetime.now().strftime("%Y-%m-%d %H:%M:%S") } insert_machine_cus_table = 'acornuser.ozing_machine' insert_machine_cus_data = {'productModel': Data.MODEL, 'deviceNumber': device_cus_bind, 'macAddress': Data.MAC_CUS_BIND, 'state': '1' } if db.select(select_customermachine): pass else: db.insert(insert_customermachine_tabel, insert_customermachine_data) if db.select(select_machine_cus): pass else: db.insert(insert_machine_cus_table, insert_machine_cus_data) # 样机 if device_sam !='0': select_samplemachine = "SELECT * FROM acornuser.ozing_samplemachine where deviceNumber = '{}' ".format(device_sam) select_machine_sam = "SELECT * FROM acornuser.ozing_machine where deviceNumber = '{}' ".format(device_sam) insert_samplemachine_tabel = 'acornuser.ozing_samplemachine' insert_samplemachine_data= {'deviceNumber': device_sam, 'userId': Data.USER_ID, 'terminalAddress': "内蒙古巴彦淖尔市", 'distributor': "新华书店", 'saleClerk':"张三", 'mobilePhone': "18622222222", 'photo':'[{"photoUrl":"static/upload/online_api/samplePhoto/2017-12-13/b6480129-e720-4109-a455-6130fd640f16.jpg"},{"photoUrl":"static/upload/online_api/samplePhoto/2017-12-13/b8317fa4-cfa5-4ea4-91d4-3ca020e06bca.jpg"},{"photoUrl":"static/upload/online_api/samplePhoto/2017-12-13/110dc30b-34f1-4e0b-88e0-a030b8ab4af7.jpg"}]', 'smstate': '1', 'createTime': datetime.now().strftime('%Y-%m-%d %H:%M:%S') } insert_machine_sam_tabel = 'acornuser.ozing_machine' insert_machine_sam_data = {'productModel': Data.MODEL, 'deviceNumber': device_sam, 'macAddress': Data.MAC_SAM, 'state': '0'} if db.select(select_samplemachine): pass else: db.insert(insert_samplemachine_tabel, insert_samplemachine_data) if db.select(select_machine_sam): pass else: db.insert(insert_machine_sam_tabel, insert_machine_sam_data) # 未绑定 -- 客机 if device_cus_unbind != '0': select_customermachine_unbind = "select * from acornuser.ozing_customermachine where deviceNumber = '{}' " \ .format(device_cus_unbind) update_customermachine_unbind_tabel = 'acornuser.ozing_customermachine' update_customermachine_unbind_set = {'deviceNumber':str(time.time())} update_customermachine_unbind_where = {'deviceNumber': device_cus_unbind} select_machine_cus_1 = "SELECT * FROM acornuser.ozing_machine where deviceNumber = '{}' ".format(device_cus_unbind) select_machine_cus_2 = "SELECT * FROM acornuser.ozing_machine where deviceNumber = '{}' and state = '1' ".format(device_cus_unbind) update_machine_cus_unbind_table = 'acornuser.ozing_machine' update_machine_cus_unbind_set = {'state': '1'} update_machine_cus_unbind_where = {'deviceNumber': device_cus_unbind} # 保卡表中有数据 if db.select(select_customermachine_unbind): db.update(update_customermachine_unbind_tabel, update_customermachine_unbind_set, update_customermachine_unbind_where) # 机器表中有数据，并且状态是1 if db.select(select_machine_cus_1): if db.select(select_machine_cus_2): pass else: # update state =1 db.update(update_machine_cus_unbind_table, update_machine_cus_unbind_set, update_machine_cus_unbind_where) # 机器表中没数据 else: pass # #添加客机保卡 def pre_SetUpElecCard(): select_customermachine_new = "select * from acornuser.ozing_customermachine where deviceNumber = '{}' ".format(Data.DEVICE_NUMBER_NEW) update_customermachine_new_tabel = 'acornuser.ozing_customermachine' update_customermachine_new_set = {'deviceNumber':str(time.time())} update_customermachine_new_where = {'deviceNumber': Data.DEVICE_NUMBER_NEW} select_machine_new = "select * from acornuser.ozing_machine where deviceNumber = '{}' ".format(Data.DEVICE_NUMBER_NEW) update_machine_new_tabel = 'acornuser.ozing_machine' update_machine_new_set = {'deviceNumber': str(time.time())} update_machine_new_where = {'deviceNumber': Data.DEVICE_NUMBER_NEW} if db.select(select_customermachine_new): db.update(update_customermachine_new_tabel, update_customermachine_new_set, update_customermachine_new_where) if db.select(select_machine_new): db.update(update_machine_new_tabel, update_machine_new_set, update_machine_new_where) ## ********************************************************************************************************************* ## 子账户 ## ********************************************************************************************************************* def pre_subAccount(parent_id, sub_account_id, status, deviceNumber=Data.DEVICE_NUMBER_CUS_BIND): #status: child status # 子账户数据 TABEL_CHILD_USER = 'acornuser.child_user' if status == 1 : # make sure only 1 sub account's status marked as '1' update_all_0_sub = "update acornuser.child_user set status = 0 where parent_id = '{}' and deviceNumber = '{}'" \ .format(parent_id, deviceNumber) db.update_(update_all_0_sub) select_sub_acc = "select * from acornuser.child_user where parent_id = '{}' and subAccountId = '{}' ".format(parent_id, sub_account_id) select_sub_acc_status = "select * from acornuser.child_user where parent_id = '{}' and subAccountId = '{}' " \ "and status = {} ".format(parent_id, sub_account_id, status) update_set = {'status': status} update_where = {'subAccountId': sub_account_id } insert_data = {'parent_id':parent_id, 'image': Data.SUB_ACC_IMAGE, 'name' : '测试sub', 'grade_id':'6', 'school_id': Data.SUB_ACC_SCHOOL_ID_2, 'region_id': Data.SUB_ACC_REGION_ID_2, 'status':status, 'region_name': Data.SUB_ACC_REGION_NAME_2, 'deviceNumber':Data.DEVICE_NUMBER_CUS_BIND, 'subAccountId':sub_account_id } if db.select(select_sub_acc_status): pass elif db.select(select_sub_acc): db.update(TABEL_CHILD_USER, update_set, update_where) else: db.insert(TABEL_CHILD_USER, insert_data) #主账户数据 select_acc_1 = "select * from acornuser.acorn_user_status where userId = '{}' and deviceNumber = '{}' " \ .format(parent_id, deviceNumber) if status == 1: # if child status == 1, parent status should be 0 select_acc_2 = "select * from acornuser.acorn_user_status where userId = '{}' and deviceNumber = '{}' and status = 0 " \ .format(parent_id, deviceNumber) insert_data_parent_0 = {'userId': parent_id, 'status': 0, 'deviceNumber': Data.DEVICE_NUMBER_CUS_BIND } set_0 = {'status': 0} where = {'userId': parent_id, 'deviceNumber': Data.DEVICE_NUMBER_CUS_BIND } if db.select(select_acc_2): pass elif db.select(select_acc_1): db.update("acornuser.acorn_user_status", set_0, where) else: db.insert("acornuser.acorn_user_status", insert_data_parent_0) else: # if child status == 0, parent status should be 1 select_acc_3 = "select * from acornuser.acorn_user_status where userId = '{}' and deviceNumber = '{}' and status = 1 " \ .format(parent_id, deviceNumber) set_1 = {'status': 1} where = {'userId': parent_id, 'deviceNumber': Data.DEVICE_NUMBER_CUS_BIND } insert_data_parent_1 = {'userId': parent_id, 'status': 1, 'deviceNumber': Data.DEVICE_NUMBER_CUS_BIND } if db.select(select_acc_3): pass elif db.select(select_acc_1): db.update("acornuser.acorn_user_status", set_1, where) else: db.insert("acornuser.acorn_user_status", insert_data_parent_1) # 删除对应的parentID的所有数据 便于验证添加成功 def pre_AddSubAccount(parent_Id): where_data = {'parent_Id': parent_Id} set_data = {'parent_Id': calendar.timegm(time.gmtime())} if db.select_('acornuser.child_user', where_data): db.update('acornuser.child_user', set_data, where_data) #检查signature存在 def checkSignatureExists(userId, type): #主账户 if type == 1: sql_1 = 'select * from acornuser.acorn_user_extra where user_id = {} '.format(userId) sql_2 = 'select * from acornuser.acorn_user_extra where user_id = {} and signature is not NULL'.format(userId) if db.select(sql_2): pass elif db.select(sql_1): set = {'signature': '聪明的波利'} where = {'user_id': userId} db.update('acornuser.acorn_user_extra', set, where) else: insert = {'user_id': userId, 'signature': '聪明的波利'} db.insert('acornuser.acorn_user_extra', insert) # 子账户 if type == 2: sql_1 = "select * from acornuser.subAccount_user_extra where sub_account_id = '{}' ".format(userId) sql_2 = "select * from acornuser.subAccount_user_extra where sub_account_id = '{}'and signature is not NULL".format(userId) if db.select(sql_2): pass elif db.select(sql_1): set = {'signature': '聪明的波利 sub~'} where = {'sub_account_id': userId} db.update('acornuser.subAccount_user_extra', set, where) else: insert = {'sub_account_id': userId, 'signature': '聪明的波利 sub~'} db.insert('acornuser.subAccount_user_extra', insert) ## ********************************************************************************************************************* ## 教材版本 ## ********************************************************************************************************************* # 用户版本信息 def pre_GetUserPressInfo(userId): set = {'chinese': '北京师范大学出版社'} where = {'user_id': userId} db.update('acornuser.user_press', set, where) ## ********************************************************************************************************************* ## 登录注册 ## ********************************************************************************************************************* # 检查用户注册信息是否完整 def pre_register_extrainfo_check(user_id, complete): #不完整 if complete == False: sql = "select * from acornuser.ozing_student where user_id = {} and region_id is NULL and school_id is NULL".format(user_id) if db.select(sql): pass else: update_sql = "update acornuser.ozing_student set region_id = NULL, school_id = NULL where user_id = {}".format(user_id) db.update_(update_sql) # 完整 else: sql = "select * from acornuser.ozing_student where user_id = {} and region_id is not NULL and school_id is not NULL".format( user_id) if db.select(sql): pass else: set = {'region_id': '140600', 'school_id': '496299', 'region_name':'江苏苏州吴中'} where = {'user_id': user_id} db.update('acornuser.ozing_student', set, where) ## ********************************************************************************************************************* ## 最近观看视频 ## ********************************************************************************************************************* def pre_getRecentVideo(user_id, status): # status=0 novideo, status=1 has video where = {'user_id': user_id} if status == 0 : set = {'user_id': str(time.time())} if db.select_('acornuser.user_video_watch', where): db.update('acornuser.user_video_watch', set, where) if status == 1 : if db.select_('acornuser.user_video_watch', where): pass else: insert_data = {'user_id': user_id, 'data_id': 486600, 'data_name':'人教7上_1 春_程诗尧.mpc', 'play_online_url':'http://fd.xuexiao100.com/mp4/黄冈视频/初中语文/7年级上 人民教育出版社_2017版/人教7上_1 春_程诗尧.mp4?k=e8f8a7429a42aff00cb96faa6f48821e', 'cover_url': 'http://hjxprodbucket.oss.aliyuncs.com/static/upload/boss_api/announcement/2017-10-18/34cfe338-2305-4aa0-96d8-c952be4dd800.jpg', 'app_unique_name': 'famous-teacher', 'created_time': '2017-12-27 14:52:08', 'modified_time': '2017-12-27 14:52:08' } db.insert('acornuser.user_video_watch', insert_data) ## ********************************************************************************************************************* ## 手机号重复验证 ## ********************************************************************************************************************* def pre_phoneUsedCheck(phone, used): #used = True :used where = {'username': phone} if used: if db.select_('acornuser.acorn_user', where): pass else: max_id = db.select('select max(id) from acornuser.acorn_user')[0]['max(id)'] update_where = {'id': max_id - 150} count = db.select_('acornuser.acorn_user', update_where) db.update('acornuser.acorn_user', where, update_where) else: if db.select_('acornuser.acorn_user', where): update_set = {'username': str(time.time())} db.update('acornuser.acorn_user', update_set, where) ## ********************************************************************************************************************* ## 家长控制 密码 ## ********************************************************************************************************************* def get_parentSpace_password(device_number): sql = "select password from acornuser.parents_space_pass where deviceNumber = '{}'".format(device_number) result = db.select(sql) if result: return result[0]['password'] else: return '123456' ## ********************************************************************************************************************* ## 家长控制 app使用统计 ## ********************************************************************************************************************* # "now" format: timestamp , create app using data about this week, month, year, last year(according 'now' time) def create_app_use_record(now, user_id, device_number): today = date.fromtimestamp(now) year_start_time = int(str(time.mktime(date(today.year, 1, 1).timetuple())).split('.')[0]) where_equal = {'user_id':user_id, 'device_number':device_number} where_unequal = ' time_end > {} '.format(year_start_time) if db.select_('analytics.app_record', where_equal, where_unequal): # data existing update_data = "update analytics.app_record set device_number = '{}' where user_id = '{}' and device_number = '{}' " \ .format(str(time.time()), user_id, device_number) db.update_(update_data) study_apps = [{'app_name':'百度英语资料大全', 'app_pid':'com.sailang.EnglishBook','category_id':'25', 'source_id':'1', \ 'time_spent': 100, 'user_id': user_id, 'device_number': device_number}, {'app_name': '开心大学士', 'app_pid': 'com.ksense.study','category_id':'26', 'source_id':'1', \ 'time_spent': 200, 'user_id': user_id, 'device_number': device_number}, {'app_name': '驾考宝典', 'app_pid': 'com.handsgo.jiakao.android' ,'category_id':'27', 'source_id':'1', \ 'time_spent': 300, 'user_id': user_id, 'device_number': device_number}, {'app_name': '我爱汉字', 'app_pid': 'com.cronlygames.hanzi' ,'category_id':'28', 'source_id':'1',\ 'time_spent': 400, 'user_id': user_id, 'device_number': device_number}, {'app_name': '拖拖乐3', 'app_pid': 'cn.com.wiisoft.tuotuo' ,'category_id':'57', 'source_id':'1', \ 'time_spent': 100, 'user_id': user_id, 'device_number': device_number}, {'app_name': '幼儿数字算数学习', 'app_pid': 'com.syhrobert1991.infantlearning' ,'category_id':'25', \ 'source_id':'1', 'time_spent': 200, 'user_id': user_id, 'device_number': device_number}, {'app_name': '轻松背单词之初中英语', 'app_pid': 'petpestzx.wordroid.model' ,'category_id':'26', \ 'source_id':'2', 'time_spent': 300, 'user_id': user_id, 'device_number': device_number}, {'app_name': '有谱－爱学习（数理化）', 'app_pid': 'com.emingren.youpu' ,'category_id':'27', 'source_id':'2', \ 'time_spent': 400, 'user_id': user_id, 'device_number': device_number}, {'app_name': '疯狂音标', 'app_pid': 'com.neo.crazyphonetic' ,'category_id':'28', 'source_id':'2', \ 'time_spent': 100, 'user_id': user_id, 'device_number': device_number}, {'app_name': '互动作业V3.18.6', 'app_pid': 'com.v.study' ,'category_id':'57', 'source_id':'2', \ 'time_spent': 250, 'user_id': user_id, 'device_number': device_number}, {'app_name': '发音背单词', 'app_pid': 'org.liberty.android.fantastischmemo' ,'category_id':'57', \ 'source_id':'2', 'time_spent': 350, 'user_id': user_id, 'device_number': device_number}, {'app_name': '语文100', 'app_pid': 'com.kk.kkyuwen' ,'category_id':'57', 'source_id':'2', \ 'time_spent': 450, 'user_id': user_id, 'device_number': device_number} ] game_apps = [{'app_name': '小伴龙新', 'app_pid': 'com.xiaobanlong.main' ,'category_id':'37', 'source_id':'1',\ 'time_spent': 100, 'user_id': user_id, 'device_number': device_number}, {'app_name': '三国群英传', 'app_pid': 'com.tencent.tmgp.sgqyz' ,'category_id':'38', 'source_id':'1', \ 'time_spent': 200, 'user_id': user_id, 'device_number': device_number}, {'app_name': '童言童语', 'app_pid': 'com.lingshi.kids' ,'category_id':'39', 'source_id':'2', \ 'time_spent': 300, 'user_id': user_id, 'device_number': device_number}, {'app_name': '从前啊', 'app_pid': 'com.mojie.longlongago' ,'category_id':'37', 'source_id':'2', \ 'time_spent': 400, 'user_id': user_id, 'device_number': device_number}, {'app_name': '永恒纪元', 'app_pid': 'com.m37.dtszj.uc' ,'category_id':'38', 'source_id':'2', \ 'time_spent': 100, 'user_id': user_id, 'device_number': device_number}, {'app_name': '我的世界新', 'app_pid': 'com.netease.mc.aligames' ,'category_id':'39', 'source_id':'2', \ 'time_spent': 200, 'user_id': user_id, 'device_number': device_number}, {'app_name': '球球大作战', 'app_pid': 'com.ztgame.bob', 'category_id': '37', 'source_id': '2', \ 'time_spent': 300, 'user_id': user_id, 'device_number': device_number}, {'app_name': '葫芦侠我的世界', 'app_pid': 'com.huluxia.mctool', 'category_id': '38', 'source_id': '2',\ 'time_spent': 400, 'user_id': user_id, 'device_number': device_number}, {'app_name': 'QQ游戏V6.8.7', 'app_pid': 'com.tencent.qqgame', 'category_id': '39', 'source_id': '2',\ 'time_spent': 100, 'user_id': user_id, 'device_number': device_number}, {'app_name': '99围棋最新', 'app_pid': 'com.r99weiqi.dvd', 'category_id': '37', 'source_id': '2', \ 'time_spent': 50, 'user_id': user_id, 'device_number': device_number}, {'app_name': '凯蒂环球之旅', 'app_pid': 'com.tencent.HelloKitty', 'category_id': '37', 'source_id': '2',\ 'time_spent': 150, 'user_id': user_id, 'device_number': device_number}, {'app_name': '贪吃蛇大作战', 'app_pid': 'com.wepie.snake.qihoo', 'category_id': '100', 'source_id': '2',\ 'time_spent': 250, 'user_id': user_id, 'device_number': device_number} ] # get date of the last 7 days(include today) days = [] # the day should insert app records if today.month == 1: # the first month of this year if today.day <= 7: # when today < 7th day of this month , 7 records chould cover this week ,this month, this year for i in range(0, 7): day_delta = timedelta(days=i) days.append(str(time.mktime((today - day_delta).timetuple())).split('.')[0]) else: # when today > 7th day of this month , should create 8 records to cover this week ,this month, this year for i in range(0, 8): day_delta = timedelta(days=i) days.append(str(time.mktime((today - day_delta).timetuple())).split('.')[0]) else: # create january data to cover this year for i in range(0, 8): day_delta = timedelta(days=i) days.append(str(time.mktime((today - day_delta).timetuple())).split('.')[0]) days.append(str(time.mktime(date(today.year, 1, 1).timetuple())).split('.')[0]) app_rec_to_insert = [] for day in days: for i in range(0, 12): time_end_1 = {'time_end': int(day) + 28800 + i * 1000, 'id':str(uuid.uuid4()).replace('-', '')} time_end_2 = {'time_end': int(day) + 29800 + i * 500, 'id':str(uuid.uuid4()).replace('-', '')} study_app = study_apps[i].copy() study_app.update(time_end_1) game_app = game_apps[i].copy() game_app.update(time_end_2) app_rec_to_insert.append(study_app) app_rec_to_insert.append(game_app) for rec in app_rec_to_insert: db.insert('analytics.app_record', rec)