def get_userinfo(id, getpw=False):
'''
get user info from db
:param getpw: True=return passwdmd5
'''
flist = [{
'fn': 'id',
"type": 'str'
}, {
'fn': 'username',
'type': 'b64str'
}, {
'fn': 'email',
'type': 'b64str'
}, {
'fn': 'role',
'type': 'str'
}]
if getpw:
flist.append({'fn': 'passwdmd5', 'type': 'str'})
wstr = 'where id={0}'.format(str(id))
try:
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
data = funcs.get_datas(cur, 'user', flist, wstr=wstr, logger=gv.logger)
return data
except:
gv.logger.error(traceback.format_exc())
res = ee.DBERR()
return res
def get_child_ids(pid):
'''
get child ids of parent
'''
fl = [{'fn': 'child_ids', 'type': 'str'}]
try:
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
wstr = 'where id={}'.format(str(pid))
res = funcs.get_datas(cur, 'parent', fl, wstr=wstr, logger=gv.logger)
cur.close()
conn.close()
ids = set()
if len(res['data']) > 0:
for _line in res['data']:
if _line['child_ids'] is not None and \
_line['child_ids']!='':
_lids = _line['child_ids'].split(',')
ids.update(_lids)
resdata = []
for id in ids:
if id != '':
resdata.append({'id': int(id)})
res['count'] = len(resdata)
res['data'] = resdata
return res
except:
estr = traceback.format_exc()
gv.logger.error('database error!\n' + estr)
return ee.DBERR()
return ee.UNKNOWN()
def _get_simple_child_info_by_cid(cid):
'''
get simple child info by id,
info: name,name_chs,alias
'''
flist = [{
'fn': 'name',
'type': 'b64str'
}, {
'fn': 'name_chs',
'type': 'b64str'
}, {
'fn': 'alias',
'type': 'b64str'
}]
try:
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
wstr = 'where id={cid}'.format(cid=str(cid))
res = funcs.get_datas(cur, 'child', flist, wstr=wstr, logger=gv.logger)
cur.close()
conn.close()
return res
except:
estr = traceback.format_exc()
gv.logger.error('database error!\n' + estr)
return ee.DBERR()
return ee.UNKNOWN()
def get_status_by_id(stype, sid):
'''
get status by id
'''
tdef = copy.deepcopy(sttb[stype])
cidi = get_index(tdef['flist'], 'child_id')
if cidi is not None:
tdef['flist'][cidi]['type'] = 'str'
wstr = 'where id={0}'.format(str(sid))
try:
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
datas = funcs.get_datas(cur,
tdef['tbn'],
tdef['flist'],
wstr=wstr,
logger=gv.logger)
if stype != 'temperature':
for line in datas['data']:
if line['status'] is not None:
line['status'] = gv.enum_selector[tptbn[stype]][int(
line['status'])]
cur.close()
conn.close()
return datas
except:
estr = traceback.format_exc()
gv.logger.error('database error!\n' + estr)
return ee.DBERR()
return ee.UNKNOWN()
def get_file_info(fid):
'''
get file list from database
:param fid: file id
'''
wstr = 'where id={0}'.format(str(fid))
fl = deepcopy(flist)
fl[1]['type'] = 'str'
try:
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
datas = funcs.get_datas(cur,
'files',
flist,
wstr=wstr,
logger=gv.logger)
cur.close()
conn.close()
return datas
except:
estr = traceback.format_exc()
gv.logger.error('database error!\n' + estr)
return ee.DBERR()
return ee.UNKNOWN()
def get_file_list(page=0, limit=0, cid=None):
'''
get file list from database
:param page:
:param limit:
:param cid: child id, 空则不限制
'''
fl = deepcopy(flist)
fl[1]['type'] = 'str'
del fl[2]
fl[2]['as'] = 'filename'
wstr = ''
if cid is not None:
wstr = 'where child_id={0}'.format(str(cid))
try:
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
datas = funcs.get_datas(cur,
'files',
fl,
page=page,
limit=limit,
wstr=wstr,
logger=gv.logger)
cur.close()
conn.close()
return datas
except:
estr = traceback.format_exc()
gv.logger.error('database error!\n' + estr)
return ee.DBERR()
return ee.UNKNOWN()
def get_child_ids(pid):
'''
get child ids of parent
'''
fl = [{'fn': 'child_ids', 'type': 'str'}]
try:
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
wstr = 'where id={}'.format(str(pid))
res = funcs.get_datas(cur, 'parent', fl, wstr=wstr, logger=gv.logger)
cur.close()
conn.close()
return res
except:
estr = traceback.format_exc()
gv.logger.error('database error!\n' + estr)
return ee.DBERR()
return ee.UNKNOWN()
def get_parents(page, limit, simple=False):
'''
get parents from database
:param page: page, 0 based
:param limit: count per page, 0 = no limit
:param simple: simple mode
:return : Unified format json
'''
flist_t = [{
'fn': "t1`.`id",
'type': 'str'
}, {
'fn': "username",
'type': 'b64str'
}, {
'fn': "name",
'type': 'b64str'
}, {
'fn': "name_chs",
'type': 'b64str'
}, {
'fn': "child_ids",
'type': 'str'
}, {
'fn': "edu",
'type': 'b64str'
}, {
'fn': "occupation",
'type': 'b64str'
}, {
'fn': "tel",
'type': 'str'
}]
flist_p = [{
'fn': "id",
'type': 'int'
}, {
'fn': "name",
'type': 'b64str'
}, {
'fn': "name_chs",
'type': 'b64str'
}, {
'fn': "child_ids",
'type': 'str'
}, {
'fn': "edu",
'type': 'b64str'
}, {
'fn': "occupation",
'type': 'b64str'
}, {
'fn': "tel",
'type': 'str'
}]
limitstr = ''
if page < 0:
page = 0
if limit > 0:
limitstr = ' limit {0},{1}'.format(str(page * limit), str(limit))
if simple:
flist_t = flist_t[:4]
flist_p = flist_p[:3]
kstr_p = funcs.build_field_str(flist_p)
wstr = 'inner join (select {kstr} from parent {limit}) as t1'\
' on `user`.id=t1.id'.format(kstr=kstr_p, limit=limitstr)
try:
conn = gv.dbpool.connection()
cur = conn.cursor()
res = funcs.get_datas(cur,
'user',
flist_t,
wstr=wstr,
logger=gv.logger)
cur.close()
conn.close()
if res['code'] == 0 and len(res['data']) > 0:
# 更改id字段名
for line in res['data']:
line['id'] = line['t1`.`id']
del line['t1`.`id']
return res
except:
gv.logger.error(traceback.format_exc())
res = ee.DBERR()
return res
def get_children_info(cid, sk):
"""
从数据库分页获取孩子信息
:param cid: child id
:param sk: secret key
:return : unity formated json
"""
_id = cid
pid = gv.logged[sk]['id']
role = gv.logged[sk]['role']
flist_c = [{
'fn': 't1`.`id',
'type': 'str',
'as': 'id'
}, {
'fn': 't1`.`name',
'type': 'b64str',
'as': 'name'
}, {
'fn': 't1`.`name_chs',
'type': 'b64str',
'as': 'name_chs'
}, {
'fn': 'alias',
'type': 'b64str'
}, {
'fn': 'gender',
'type': 'str'
}, {
'fn': 'religion',
'type': 'b64str'
}, {
'fn': 'born_day',
'type': 'str'
}, {
'fn': 'birth_cert_no',
'type': 'str'
}, {
'fn': 'place_birth',
'type': 'b64str'
}, {
'fn': 'address',
'type': 'b64str'
}, {
'fn': 'date_in',
'type': 'str',
'sorted': 1
}, {
'fn': 't1`.`tel',
'type': 'str',
'as': 'tel'
}, {
'fn': 'email',
'type': 'b64str'
}, {
'fn': 'caregiver',
'type': 'str',
'as': 'caregiver_id'
}, {
'fn': 'parent`.`name',
'type': 'b64str',
'as': 'caregiver_name'
}, {
'fn': 'parent`.`name_chs',
'type': 'b64str',
'as': 'caregiver_name_chs'
}, {
'fn': 'lang',
'type': 'b64str'
}, {
'fn': 'group_id',
'type': 'int'
}]
try:
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
# 判断，如果是家长，只可以看自己的孩子
allow = True
if role == 'parent':
allow = False
s1 = 'select child_ids from parent where id={0};'.format(str(pid))
cur.execute(s1.encode("utf-8", "ignore"))
cidsres = cur.fetchall()
if cidsres is not None and len(cidsres) > 0:
if cidsres[0][0] is not None and len(cidsres[0][0]) > 0:
_tmp = cidsres[0][0].split(',')
if str(_id) in _tmp:
allow = True
if not allow:
cur.close()
conn.close()
return ee.NOAUTHORITY(msg='Parents can only view the '
'information of their own children')
wstr = 'right join (select * from child where id={cid}) as t1'\
' on `parent`.id=t1.caregiver'.format(cid=_id)
res = funcs.get_datas(cur,
'parent',
flist_c,
wstr=wstr,
logger=gv.logger)
res['count'] = len(res['data'])
cur.close()
conn.close()
return res
except:
estr = traceback.format_exc()
gv.logger.error('database error!\n' + estr)
return ee.DBERR()
return ee.UNKNOWN()
def get_children_infos(page=0, limit=0, simple=False):
"""
从数据库分页获取孩子信息
:param page: 页码,0 based
:param limit: 每页数量，设为0时，不限，直接返回所有数据
:param simple: simple模式
:return : unity formated json
"""
tbn = 'child'
flist = [{
'fn': 'id',
'type': 'str'
}, {
'fn': 'name',
'type': 'b64str'
}, {
'fn': 'name_chs',
'type': 'b64str'
}, {
'fn': 'alias',
'type': 'b64str'
}, {
'fn': 'gender',
'type': 'str'
}, {
'fn': 'born_day',
'type': 'str'
}, {
'fn': 'place_birth',
'type': 'b64str'
}, {
'fn': 'date_in',
'type': 'str',
'sorted': 1
}, {
'fn': 'tel',
'type': 'str'
}, {
'fn': 'caregiver_id',
'type': 'str'
}, {
'fn': 'caregiver_name',
'type': 'b64str'
}, {
'fn': 'caregiver_name_chs',
'type': 'b64str'
}, {
'fn': 'lang',
'type': 'b64str'
}, {
'fn': 'group_id',
'type': 'int'
}]
flist_p = [{
'fn': 'id` as `caregiver_id',
'type': 'str'
}, {
'fn': 'name` as `caregiver_name',
'type': 'b64str'
}, {
'fn': 'name_chs` as `caregiver_name_chs',
'type': 'b64str'
}]
if simple:
flist = flist[:4]
try:
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
wstr = ''
if not simple:
kstr_p = funcs.build_field_str(flist_p)
wstr = 'left join (select {kstr} from parent) as t1'\
' on `child`.caregiver=caregiver_id'.format(kstr=kstr_p)
res = funcs.get_datas(cur,
tbn,
flist,
page=page,
limit=limit,
wstr=wstr,
logger=gv.logger)
cur.close()
conn.close()
return res
except:
estr = traceback.format_exc()
gv.logger.error('database error!\n' + estr)
return ee.DBERR()
return ee.UNKNOWN()
def get_status(tp, page=0, limit=0, cid=None):
'''
从数据库分页获得status信息
:param tp: status type, 指定需要哪种状态信息
:param page: 页码,0 based
:param limit: 每页数量，设为0时，不限，直接返回所有数据
:param cid: child id，不设置时，返回所有孩子的
:return : unity formated json
'''
startt = request.args.get('startt')
endt = request.args.get('endt')
if type(startt) == type(''):
try:
# check
datetime.datetime.strptime(startt, '%Y-%m-%d')
except:
return ee.PARAMERR(app=': startt')
elif startt is not None:
return ee.PARAMERR(app=': startt')
if type(endt) == type(''):
try:
# check
datetime.datetime.strptime(endt, '%Y-%m-%d')
except:
return ee.PARAMERR(app=': endt')
elif endt is not None:
return ee.PARAMERR(app=': endt')
tdef = deepcopy(sttb[tp])
cidi = get_index(tdef['flist'], 'child_id')
if cidi is not None:
tdef['flist'][cidi]['type'] = 'str'
wstr = ''
if cid is not None:
wstr += ' child_id={0}'.format(str(cid))
if startt is not None:
if cid is not None:
wstr = wstr + ' and'
wstr = wstr + " `time`>='{0}'".format(startt)
if endt is not None:
if startt is not None:
wstr = wstr + ' and'
wstr = wstr + " `time`<='{0} 23:59:59.999'".format(endt)
if wstr != '':
wstr = 'where' + wstr
try:
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
datas = funcs.get_datas(cur,
tdef['tbn'],
tdef['flist'],
page=page,
limit=limit,
wstr=wstr,
logger=gv.logger)
if tp != 'temperature':
for line in datas['data']:
line['status'] = gv.enum_selector[tptbn[tp]][int(
line['status'])]
cur.close()
conn.close()
return datas
except:
estr = traceback.format_exc()
gv.logger.error('database error!\n' + estr)
return ee.DBERR()
return ee.UNKNOWN()
def get_status_by_cid(cid, startt, endt, sk):
"""
获取指定child id 对应的status信息
:param cid: child id
:param startt: start time
:param endt: end time
:param sk: secret key
"""
if type(startt) == type(''):
try:
# check
datetime.datetime.strptime(startt, '%Y-%m-%d')
except:
return ee.PARAMERR(app=': startt')
elif startt is not None:
return ee.PARAMERR(app=': startt')
if type(endt) == type(''):
try:
# check
datetime.datetime.strptime(endt, '%Y-%m-%d')
except:
return ee.PARAMERR(app=': endt')
elif endt is not None:
return ee.PARAMERR(app=': endt')
wstr = 'where child_id={0}'.format(str(cid))
if startt is not None:
wstr = wstr + " and `time`>='{0}'".format(startt)
if endt is not None:
wstr = wstr + " and `time`<='{0} 23:59:59.999'".format(endt)
try:
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
# 判断，如果是家长，只可以看自己的孩子
role = gv.logged[sk]['role']
allow = True
if role == 'parent':
pid = gv.logged[sk]['id']
allow = False
s1 = 'select child_ids from parent where id={0};'.format(str(pid))
cur.execute(s1.encode("utf-8", "ignore"))
cidsres = cur.fetchall()
if cidsres is not None and len(cidsres) > 0:
if cidsres[0][0] is not None and len(cidsres[0][0]) > 0:
_tmp = cidsres[0][0].split(',')
if str(cid) in _tmp:
allow = True
if not allow:
cur.close()
conn.close()
return ee.NOAUTHORITY(msg='Parents can only view the '
'information of their own children')
res = ee.NORMAL()
_sttb = deepcopy(sttb)
for tbn, fl in _sttb.items():
cidi = get_index(fl['flist'], 'child_id')
if cidi is not None:
fl['flist'][cidi]['type'] = 'str'
datas = funcs.get_datas(cur,
tbn,
fl['flist'],
wstr=wstr,
logger=gv.logger)
for line in datas['data']:
if tbn != 'temperature':
line['status'] = gv.enum_selector[tptbn[tbn]][int(
line['status'])]
line['type'] = tbn
res['data'] = res['data'] + datas['data']
# 按时间排序
res['data'] = sorted(res['data'],
key=lambda x: x['time'],
reverse=True)
res['count'] = len(res['data'])
cur.close()
conn.close()
return res
except:
estr = traceback.format_exc()
gv.logger.error('database error!\n' + estr)
return ee.DBERR()
return ee.UNKNOWN()