def update_subject(group_ids, row,update_subject_, insert_time,data_from_):
for data_from in group_ids:
group = Group.query.filter(Group.id == data_from).first()
update_time = Update_time.query.filter(Update_time.group_id == group.id).first()
#根据update_time有无数据来判断是否为首次更新
if update_time:
# if str(insert_time) > update_time.update_time:
result = update_subject_(row,group,data_from_)
update_time.update_time = str(insert_time)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
raise DBError(message=e.message)
else:
log.info('{}{} 首次进行增量更新'.format(group.category.encode('utf-8'),group.gender.encode('utf-8')))
result = add_subject(row,group,data_from_)
update_time = Update_time(
group_id = group.id,
update_time = insert_time
)
db.session.add(update_time)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
raise DBError(message=e.message)
def getuserid(username):
likename = "%" + username + "%"
users = db.session.query(User).filter(User.username.like(likename)).all()
user_ids = []
for user in users:
log.info("%d : %s: %d" % (user.id, user.username, user.inc_id))
user_ids.append(user.id)
return user_ids
def incremental_update(inc):
hour_now = time.strftime('%H', time.localtime(time.time()))
if int(hour_now) >= SPECIFY_UPDATE_TIME_AFTER :
db_conn = cx_Oracle.connect(DB_LINK)
orc_cursor = db_conn.cursor()
orc_cursor.execute(query)
row = orc_cursor.fetchone()
while row:
data_from, insert_time = row[8], row[10] #data_from 是一个长度为256的字符串，例：'01000101010'
group_ids_to_add,group_ids_to_del = data_from_to_group_id(data_from,row) #根据oracle的照片来源判断group_id
if group_ids_to_add:
update_subject(group_ids_to_add, row,add_subject, insert_time,data_from)
if group_ids_to_del:
update_subject(group_ids_to_del, row,del_subject,insert_time,data_from)
try:
row = orc_cursor.fetchone()
except :
pass
log.info('All is well')
schedule.enter(inc, 0, incremental_update, (inc,))
def back_up_mongo_data():
from config import Config
import datetime
log.info("back up mongo db")
dbinfo = Config.MONGOINFO
databaseip = dbinfo['ip']
dbport = dbinfo['port']
authdb = dbinfo['authdb']
database = dbinfo['db']
username, password = dbinfo['username'], dbinfo['password']
databaseip = databaseip if databaseip else "127.0.0.1"
t = datetime.datetime.now()
seven_day_ago = t - datetime.timedelta(days=4)
unpackdate = t.strftime('%Y%m%d-%H%M') + "-m"
unpackdate_seven_ago = seven_day_ago.strftime('%Y%m%d-%H%M')
backup_path = "/backup/mongo/" + unpackdate
mongo_dump_command = "/usr/bin/mongodump -h %s --port %s --authenticationDatabase %s -u %s -p %s -o %s" % (
databaseip, dbport, authdb, username, password, backup_path)
log.info(mongo_dump_command)
res = os.system(mongo_dump_command)
log.info(res)
remove_command = "rm -rf " + "/backup/mongo/" + unpackdate_seven_ago
res = os.system(remove_command)
log.info(res)
def mv_all_recordfile():
user_ids = getuserid("")
for user_id in user_ids:
mv_recordfile(user_id)
log.info("move user " + str(user_id) + " complete")
def delete_unused_data():
keep_time = int(time()) - 120 * 24 * 60 * 60 # 75天
delete_time = int(time()) - 20 * 24 * 60 * 60 # 20天
phoneplan_count = phoneplan.objects(
current_phoneres__lastime__lte=int(keep_time),
current_phoneres__lastime__ne=None).count()
log.info("total deleted phoneplan count: " + str(phoneplan_count))
if prompt_bool("Are you sure ? You will delete %d of datas!" %
phoneplan_count):
phoneplan.objects(current_phoneres__lastime__lte=int(keep_time),
current_phoneres__lastime__ne=None).delete()
phonesinfos = phoneresinfo.objects(
lastime__lte=int(keep_time)).order_by('id')[0:5000]
all_count = 0
last_id = None
while phonesinfos:
phoneinfo_ids = [phonesinfo.id for phonesinfo in phonesinfos]
plan_ids = [phonesinfo.planid for phonesinfo in phonesinfos]
plan_ids_map = {}
for phonesinfo in phonesinfos:
plan_ids_map[phonesinfo.planid] = phonesinfo.id
need_plan_ids = json.loads(
phoneplan.objects(id__in=plan_ids,
is_delete=False).only("id").to_json())
need_plan_ids = [x['_id']['$oid'] for x in need_plan_ids]
log.info("need plan " + str(len(need_plan_ids)))
unused_id = set(plan_ids) - set(need_plan_ids)
log.info("unused_id plan " + str(len(unused_id)))
unused_phones_ids = [plan_ids_map[x] for x in unused_id]
new_phonesinfos = phoneresinfo.objects(id__in=unused_phones_ids)
micons = [phonesinfo.micon for phonesinfo in new_phonesinfos]
del_id_count = len(unused_phones_ids)
all_count = all_count + del_id_count
log.info("delete " + str(del_id_count))
phoneresinfo.objects(id__in=unused_phones_ids).delete()
calltime.objects(resid__in=unused_phones_ids).delete()
recordfileinfo.objects(micon__in=micons).delete()
last_id = phoneinfo_ids[:-1][0]
log.info("last id: " + str(last_id))
phonesinfos = phoneresinfo.objects(id__gt=last_id,
lastime__lte=int(keep_time))[0:5000]
log.info("total deleted phonesinfos count: " + str(all_count))
def recover_dialtime(recover_startime, recover_endtime):
recover_startdate = datetime.datetime.fromtimestamp(recover_startime)
recover_endate = datetime.datetime.fromtimestamp(recover_endtime)
from app.logic.sub_table_logic import get_dialtask_model
user_list = User.query.filter(User.is_delete == False).all()
dt = (recover_startdate + datetime.timedelta(days=1)).date()
log.info('dt_manage_: ' + str(dt))
log.info('recover_startdate: ' + str(recover_startdate))
log.info('recover_endate: ' + str(recover_endate))
for user in user_list:
res, dialtime_5, dialtime_10, dialtime_30, dialtime_sum = classified_statistic(user, recover_startdate,
recover_endate)
log.info('user_id: ' + str(user.id) + ' res: ' + str(res))
for callphone, label_ in res.items():
for label, code_ in res[callphone].items():
for code, total_count in res[callphone][label].items():
code_sum = ResultSum.query.filter(ResultSum.sum_date == dt, ResultSum.user_id == user.id,
ResultSum.callphone == callphone, ResultSum.tag == label,
ResultSum.code == code).first()
if code_sum:
code_sum.number = total_count
else:
new_code_sum = ResultSum(sum_date=dt, user_id=user.id, callphone=callphone,
tag=label, code=code, number=total_count)
db.session.add(new_code_sum)
during_type_5 = ResultSum.query.filter(ResultSum.user_id == user.id,
ResultSum.dail_during_type == DailDuringType.DAILDURING_BIG5,
ResultSum.sum_date == dt).first()
if not during_type_5:
new_during_type_5 = ResultSum(sum_date=dt, user_id=user.id, dail_during_type=DailDuringType.DAILDURING_BIG5,
number=dialtime_5)
db.session.add(new_during_type_5)
else:
during_type_5.number = dialtime_5
during_type_10 = ResultSum.query.filter(ResultSum.user_id == user.id,
ResultSum.dail_during_type == DailDuringType.DAILDURING_BIG10,
ResultSum.sum_date == dt).first()
if not during_type_10:
new_during_type_10 = ResultSum(sum_date=dt, user_id=user.id,
dail_during_type=DailDuringType.DAILDURING_BIG10, number=dialtime_10)
db.session.add(new_during_type_10)
else:
during_type_10.number = dialtime_10
during_type_30 = ResultSum.query.filter(ResultSum.user_id == user.id,
ResultSum.dail_during_type == DailDuringType.DAILDURING_BIG30,
ResultSum.sum_date == dt).first()
if not during_type_30:
new_during_type_30 = ResultSum(sum_date=dt, user_id=user.id,
dail_during_type=DailDuringType.DAILDURING_BIG30, number=dialtime_30)
db.session.add(new_during_type_30)
else:
during_type_30.number = dialtime_30
dial__time_sum = ResultSum.query.filter(ResultSum.sum_date == dt, ResultSum.user_id == user.id,
ResultSum.dial_time_sum != None).first()
if not dial__time_sum:
new_dialtime_sum = ResultSum(sum_date=dt, user_id=user.id, dial_time_sum=dialtime_sum)
db.session.add(new_dialtime_sum)
else:
dial__time_sum.dial_time_sum = dialtime_sum
log.info('user_id: ' + str(user.id) + ' dialtime_sum: ' + str(dialtime_sum))
db.session.commit()