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 update_status(stype, sid): ''' ''' tdef = copy.deepcopy(sttb[stype]) del tdef['flist'][get_index(tdef['flist'], 'id')] # get and check params for fd in tdef['flist']: if fd['fn'] == 'staff_id': # 直接获取当前登录id,不需取值 _sk = request.form.get('secret_key') fd['val'] = gv.logged[_sk]['id'] continue val = request.form.get(fd['fn']) if fd['type'] == 'int': # 检验是否是int if type(val) == type(''): if not str.isdigit(val): return ee.PARAMERR(app=': ' + fd['fn']) # child id 需要检验存在性 if fd['fn'] == 'child_id': if val is None: return ee.PARAMERR(app=': ' + fd['fn'] + ' is needed') _ex = child_exists(val) if _ex['code'] != 0: return _ex if not _ex['data'][0]['exists']: return ee.IDERR(app=': child id incorrect') elif fd['fn'] == 'time': if type(val) == type(''): try: # check datetime.datetime.strptime(val, '%Y-%m-%d %H:%M:%S') except: return ee.PARAMERR(app=': ' + fd['fn']) elif val is not None: return ee.PARAMERR(app=': ' + fd['fn']) fd['val'] = val sstr = funcs.build_set_str(tdef['flist']) if sstr is None: return ee.PARAMERR() if sstr == '': return ee.PARAMERR(app=': no values to update') sqlstr = 'update `{tbn}` set {sstr} where id={sid};'.format( tbn=tdef['tbn'], sstr=sstr, sid=str(sid)) # update try: conn = gv.dbpool.connection() cur = conn.cursor() cur.execute("SET NAMES UTF8mb4;") cur.execute(sqlstr.encode("utf-8", "ignore")) conn.commit() cur.close() conn.close() return ee.NORMAL() except: estr = traceback.format_exc() gv.logger.error('database error!\n' + estr) return ee.DBERR() return ee.UNKNOWN()
def insert_child(): """ insert child info into database """ uid = funcs.get_random_int64() tbn = 'child' alias = request.form.get('alias') flist = [{ 'fn': 'id', 'type': 'int', 'val': uid }, { 'fn': 'name', 'type': 'b64str', 'val': request.form.get('name') }, { 'fn': 'name_chs', 'type': 'b64str', 'val': request.form.get('name_chs') }, { 'fn': 'alias', 'type': 'b64str', 'val': alias }, { 'fn': 'gender', 'type': 'str', 'val': request.form.get('gender') }, { 'fn': 'religion', 'type': 'b64str', 'val': request.form.get('religion') }, { 'fn': 'born_day', 'type': 'str', 'val': request.form.get('born_day') }, { 'fn': 'birth_cert_no', 'type': 'str', 'val': request.form.get('birth_cert_no') }, { 'fn': 'place_birth', 'type': 'b64str', 'val': request.form.get('place_birth') }, { 'fn': 'address', 'type': 'b64str', 'val': request.form.get('address') }, { 'fn': 'date_in', 'type': 'str', 'val': request.form.get('date_in') }, { 'fn': 'tel', 'type': 'str', 'val': request.form.get('tel') }, { 'fn': 'email', 'type': 'b64str', 'val': request.form.get('email') }, { 'fn': 'caregiver', 'type': 'int', 'val': request.form.get('caregiver') }, { 'fn': 'lang', 'type': 'b64str', 'val': request.form.get('lang') }, { 'fn': 'group_id', 'type': 'int', 'val': request.form.get('group_id') }] # check parameters # gender gi = get_index(flist, 'gender') if type(flist[gi]['val']) == type(''): if flist[gi]['val'].lower() == 'female': flist[gi]['val'] = 'f' elif flist[gi]['val'].lower() == 'male': flist[gi]['val'] = 'm' elif flist[gi]['val'].lower() == 'boy': flist[gi]['val'] = 'm' elif flist[gi]['val'].lower() == 'girl': flist[gi]['val'] = 'f' elif flist[gi]['val'].lower() == 'man': flist[gi]['val'] = 'm' # alias if alias is None: return ee.PARAMERR(app=': alias is needed') _cidfa = get_cid_by_alias(alias) if _cidfa['code'] != 0: return _cidfa if len(_cidfa['data']) > 0: return ee.UN_EXISTS(app=': alias already exists') # born_day bdi = get_index(flist, 'born_day') if type(flist[bdi]['val']) == type(''): try: # check bd = datetime.datetime.strptime(flist[bdi]['val'], '%Y-%m-%d').date() # get group id today = datetime.date.today() tdd = (today - bd).days tdm = tdd // 30 if tdd % 30 != 0: tdm += 1 for g in gv.groups: if tdm >= g['smon'] and tdm <= g['emon']: flist[-1]['val'] = g['id'] except: return ee.PARAMERR(app=': born_day') # date_in di = get_index(flist, 'date_in') if flist[di]['val'] is None or flist[di]['val'] == '': flist[di]['val'] = time.strftime('%Y-%m-%d', time.localtime(time.time())) else: try: time.strptime(flist[di]['val'], '%Y-%m-%d') except: return ee.PARAMERR(app='date_in') # parent cid = None if flist[-3]['val'] is not None: _pun = flist[-3]['val'] pid = get_id_by_username(_pun) if pid['code'] != 0: return pid if pid['count'] == 0 or len(pid['data']) == 0: return ee.IDERR(msg='username of caregiver not exists') cid = int(pid['data'][0]['id']) flist[-3]['val'] = cid try: conn = gv.dbpool.connection() cur = conn.cursor() cur.execute("SET NAMES UTF8mb4;") res = funcs.insert_if_not_exists(cur, tbn, flist, chkfn=None, logger=gv.logger) if res['code'] != 0: cur.close() conn.close() return res # add to parent if cid is not None: s1 = 'select child_ids from parent where id={0};'.format(str(cid)) cur.execute(s1.encode("utf-8", "ignore")) cidsres = cur.fetchall() cids = [] 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(',') for _t in _tmp: if len(_t) > 0: cids.append(str(_t)) cids.append(str(uid)) newcids = ','.join(cids) s2 = "update parent set child_ids='{0}' where id={1};".format( newcids, str(cid)) cur.execute(s2.encode("utf-8", "ignore")) conn.commit() cur.close() conn.close() return ee.NORMAL() 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()
def update_child(): """ update child info in database """ uid = request.form.get('id') alias = request.form.get('alias') if uid is None and alias is None: return ee.PARAMERR(app=": id or alias is needed") if type(uid) == type('') and not str.isdigit(uid): return ee.PARAMERR(app=': id') uidfa = None if alias is not None: uidfa = get_cid_by_alias(alias) if uid is not None and alias is not None: # 检查唯一性 if uidfa['code'] != 0: return uidfa if len(uidfa['data'])>0 and \ str(uidfa['data'][0]['id'])!=str(uid): return ee.UN_EXISTS(app=': alias already exists') if uid is None: if uidfa['code'] != 0: return uidfa if len(uidfa['data']) == 0: return ee.IDERR(app=': alias not exists') uid = uidfa['data'][0]['id'] tbn = 'child' flist = [{ 'fn': 'name', 'type': 'b64str', 'val': request.form.get('name') }, { 'fn': 'name_chs', 'type': 'b64str', 'val': request.form.get('name_chs') }, { 'fn': 'alias', 'type': 'b64str', 'val': alias }, { 'fn': 'gender', 'type': 'str', 'val': request.form.get('gender') }, { 'fn': 'religion', 'type': 'b64str', 'val': request.form.get('religion') }, { 'fn': 'born_day', 'type': 'str', 'val': request.form.get('born_day') }, { 'fn': 'birth_cert_no', 'type': 'str', 'val': request.form.get('birth_cert_no') }, { 'fn': 'place_birth', 'type': 'b64str', 'val': request.form.get('place_birth') }, { 'fn': 'address', 'type': 'b64str', 'val': request.form.get('address') }, { 'fn': 'date_in', 'type': 'str', 'val': request.form.get('date_in') }, { 'fn': 'tel', 'type': 'str', 'val': request.form.get('tel') }, { 'fn': 'email', 'type': 'b64str', 'val': request.form.get('email') }, { 'fn': 'caregiver', 'type': 'int', 'val': request.form.get('caregiver') }, { 'fn': 'lang', 'type': 'b64str', 'val': request.form.get('lang') }, { 'fn': 'group_id', 'type': 'int', 'val': request.form.get('group_id') }] # check parameters # gender gi = get_index(flist, 'gender') if type(flist[gi]['val']) == type(''): if flist[gi]['val'].lower() == 'female': flist[gi]['val'] = 'f' elif flist[gi]['val'].lower() == 'male': flist[gi]['val'] = 'm' elif flist[gi]['val'].lower() == 'boy': flist[gi]['val'] = 'm' elif flist[gi]['val'].lower() == 'girl': flist[gi]['val'] = 'f' elif flist[gi]['val'].lower() == 'man': flist[gi]['val'] = 'm' # born_day bdi = get_index(flist, 'born_day') if type(flist[bdi]['val']) == type(''): try: # check bd = datetime.datetime.strptime(flist[bdi]['val'], '%Y-%m-%d').date() # get group id today = datetime.date.today() tdd = (today - bd).days tdm = tdd // 30 if tdd % 30 != 0: tdm += 1 for g in gv.groups: if tdm >= g['smon'] and tdm <= g['emon']: flist[-1]['val'] = g['id'] except: return ee.PARAMERR(app='born_day') # date_in di = get_index(flist, 'date_in') if flist[di]['val'] is None or flist[di]['val'] == '': flist[di]['val'] = time.strftime('%Y-%m-%d', time.localtime(time.time())) else: try: time.strptime(flist[di]['val'], '%Y-%m-%d') except: return ee.PARAMERR(app='date_in') # parent cid = None if flist[-3]['val'] is not None: _pun = flist[-3]['val'] pid = get_id_by_username(_pun) if pid['code'] != 0: return pid if pid['count'] == 0 or len(pid['data']) == 0: return ee.IDERR(msg='username of caregiver not exists') cid = int(pid['data'][0]['id']) flist[-3]['val'] = cid try: conn = gv.dbpool.connection() cur = conn.cursor() cur.execute("SET NAMES UTF8mb4;") # get origin cid cidorg = None if cid is not None: sqlstr = 'select caregiver from child where id={0};'.format( str(uid)) cur.execute(sqlstr.encode("utf-8", "ignore")) cidorg = cur.fetchall() if cidorg is None or len(cidorg) == 0: cur.close() conn.close() return ee.IDERR(msg='child id is incorrect') cidorg = cidorg[0][0] vstr = funcs.build_set_str(flist) if vstr is None: cur.close() conn.close() return ee.PARAMERR() sqlstr = 'update `{tbn}` set {sstr} where id={cid};'.format(tbn=tbn, sstr=vstr, cid=uid) cur.execute(sqlstr.encode("utf-8", "ignore")) # add to parent if cid is not None and str(cidorg) != str(cid): # 抹除旧的 s1 = 'select child_ids from parent where id={0};'.format( str(cidorg)) 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: newcids = cidsres[0][0].replace(str(uid), '').replace(',,', ',') s2 = "update parent set child_ids='{0}' where id={1};".format( newcids, str(cidorg)) cur.execute(s2.encode("utf-8", "ignore")) # 设置新的 s1 = 'select child_ids from parent where id={0};'.format(str(cid)) cur.execute(s1.encode("utf-8", "ignore")) cidsres = cur.fetchall() cids = [] 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(',') for _t in _tmp: if len(_t) > 0: cids.append(str(_t)) cids.append(str(uid)) newcids = ','.join(cids) s2 = "update parent set child_ids='{0}' where id={1};".format( newcids, str(cid)) cur.execute(s2.encode("utf-8", "ignore")) conn.commit() cur.close() conn.close() return ee.NORMAL() except: estr = traceback.format_exc() gv.logger.error('database error!\n' + estr) return ee.DBERR() return ee.UNKNOWN()
def insert_status(stype): ''' ''' tdef = copy.deepcopy(sttb[stype]) del tdef['flist'][get_index(tdef['flist'], 'id')] # get and check params for fd in tdef['flist']: if fd['fn'] == 'staff_id': # 直接获取当前登录id,不需取值 _sk = request.form.get('secret_key') fd['val'] = gv.logged[_sk]['id'] continue val = request.form.get(fd['fn']) if fd['type'] == 'int': if type(val) == type(''): if not str.isdigit(val): return ee.PARAMERR(app=': ' + fd['fn']) if fd['fn'] == 'child_id': if val is None: # check alias _alias = request.form.get('alias') if _alias is None: return ee.PARAMERR(app=': child_id or alias is needed') _cid = get_cid_by_alias(_alias) if _cid['code'] != 0: return _cid if len(_cid['data']) == 0: return ee.IDERR(app=': alias not exists') val = _cid['data'][0]['id'] else: _ex = child_exists(val) if _ex['code'] != 0: return _ex if not _ex['data'][0]['exists']: return ee.IDERR(app=': child id incorrect') elif fd['fn'] == 'time': if type(val) == type(''): try: # check datetime.datetime.strptime(val, '%Y-%m-%d %H:%M:%S') except: return ee.PARAMERR(app=': ' + fd['fn']) elif val is not None: return ee.PARAMERR(app=': ' + fd['fn']) else: val = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()) fd['val'] = val # insert try: conn = gv.dbpool.connection() cur = conn.cursor() cur.execute("SET NAMES UTF8mb4;") res = funcs.insert_if_not_exists(cur, tdef['tbn'], tdef['flist'], chkfn=None, logger=gv.logger) conn.commit() cur.close() conn.close() return res except: estr = traceback.format_exc() gv.logger.error('database error!\n' + estr) return ee.DBERR() return ee.UNKNOWN()