def delete_file(fid): ''' delete file from database and disk ''' finfo = get_file_info(fid) if finfo['code'] != 0: return finfo if len(finfo['data']) == 0: return ee.IDERR(msg='file with this id not found') finfo = finfo['data'][0] _fn = finfo['filename'] _fpath = os.path.join(filedir, _fn) if os.path.exists(_fpath): try: os.remove(_fpath) except: return ee.UNKNOWN(msg='error occured when deleting file') # delete info in db sqlstr = 'delete from files where id={};'.format(str(fid)) 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 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_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_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 passwd_put(): _un = request.form.get('username') _pw = request.form.get('passwd_md5') _vc = request.form.get('vcode') if None in [_un, _pw, _vc]: return ee.PARAMERR() if _pw == '': return ee.PARAMERR(msg='invalid password') if _un not in gv.vcode: return ee.UNKNOWN() _ovc = gv.vcode[_un]['vcode'] _t = gv.vcode[_un]['time'] if int(time.time()) - _t > 1800: return ee.TIMEOUT(msg='verification code time out (more than 30min)') if str(_ovc) != str(_vc).zfill(8): return ee.VCODEERR() _id = gv.vcode[_un]['id'] sqlstr = "update `user` set passwdmd5='{0}' where id={1};".format(_pw, _id) 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() del gv.vcode[_un] return ee.NORMAL() except: emsg = 'DB error in update pw.\n' + traceback.format_exc() gv.logger.error(emsg) 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 insert_file_info(cid, filename, originfn): ''' insert file info into database ''' fl = deepcopy(flist) del fl[0] fl[0]['val'] = cid fl[1]['val'] = filename fl[2]['val'] = originfn try: conn = gv.dbpool.connection() cur = conn.cursor() cur.execute("SET NAMES UTF8mb4;") res = funcs.insert_if_not_exists(cur, 'files', fl, 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()
def child_exists(cid): ''' 判断孩子id是否存在 ''' sqlstr = "select 1 from child where id={cid};".format(cid=str(cid)) try: conn = gv.dbpool.connection() cur = conn.cursor() cur.execute("SET NAMES UTF8mb4;") cur.execute(sqlstr.encode("utf-8", "ignore")) res = cur.fetchall() if res is None: _ex = ee.DBERR(app=(': when check child id')) elif len(res) > 0: _ex = ee.NORMAL() _ex['data'].append({'exists': True}) else: _ex = ee.NORMAL() _ex['data'].append({'exists': False}) cur.close() conn.close() return _ex 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 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 delete_status(stbn, sid): ''' 从数据库删除状态信息 :param stbn: 表名 :param sid: 状态信息id :return : ''' try: conn = gv.dbpool.connection() cur = conn.cursor() cur.execute("SET NAMES UTF8mb4;") sqlstr = 'delete from `{0}` where id={1};'.format(stbn, str(sid)) 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 send_vcode(): # test username, email, time username = request.args.get('username') email = request.args.get('email') if username is None or username == '': return ee.PARAMERR(app=': username') if email is None or email == '': return ee.PARAMERR(app=': email') if username in gv.vcode: _t = int(time.time()) if _t - gv.vcode[username]['time'] < 25: return ee.FREQ(app=': less than 25s') _user = get_id_by_username(username) if _user['code'] != 0: return ee.DBERR(': chk un') if len(_user['data']) == 0: return ee.IDERR(app=': username is incorrect') if _user['data'][0]['email'] != email: return ee.IDERR(app=': email is incorrect') gv.vcode[username] = { 'email': email, 'id': _user['data'][0]['id'], 'time': int(time.time()) } try: vcode = random.randint(0, 99999999) vcode = str(vcode).zfill(8) gv.vcode[username]['vcode'] = vcode _html = render_template('mail_vcode.html', vcode=vcode) app = current_app._get_current_object() thr = Thread(target=send_email, args=[app, username, _html]) thr.start() except: emsg = 'Error when sending email\n' + traceback.format_exc() gv.logger.error(emsg) return ee.UNKNOWN() return ee.NORMAL()
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 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 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 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()