def resettoken(self, data):
     required_args = [{
         'name': '+account',
         'type': dict,
     }, {
         'name': '+passwd',
         'type': str,
     }]
     err = form_validation(data, required_args)
     if err: return (err, None)
     id = data['account']['id']
     token = data['account']['token']
     sql = self.gen_select_sql('users', ['account', 'passwd'])
     res = yield self.db.execute(sql + ' WHERE id = %s;', (id,))
     if res.rowcount == 0:
         return ('ID Not Exist', None)
     res = res.fetchone()
     hpasswd = res['passwd']
     account = res['account']
     if self.hash_pwd(data['passwd']) != hpasswd:
         return ((400, 'Passwd Error'), None)
     # self.rs.delete("[email protected]%s" % id)
     # self.rs.delete("[email protected]%s" % token)
     token = self.gen_token(account)
     sql, prama = self.gen_update_sql('users', {'token': token})
     res = yield self.db.execute(sql + ' WHERE id = %s;', prama + (id,))
     return (None, token)

    def get_problem_list(self, data={}):
        required_args = [{
            'name': '+group_id',
            'type': int,
        }, {
            'name': '+user_id',
            'type': int,
        }, {
            'name': '+page',
            'type': int,
        }, {
            'name': '+count',
            'type': int,
        }]
        err = form_validation(data, required_args)
        if err: return (err, None)
        sql = """
            SELECT 
            (SELECT COUNT(*) FROM (SELECT * FROM submissions WHERE problem_id=p.id AND user_id=%s AND verdict=9 limit 1) AS m) AS ac, 
            (SELECT COUNT(*) FROM (SELECT * FROM submissions WHERE problem_id=p.id AND user_id=%s limit 1) AS n) AS try, 
            p.id, p.title, p.source, p.visible, p.created_at,
            u.id as setter_user_id, u.account as setter_user,
            g.id as group_id, g.name as group_name
            FROM problems as p, users as u, groups as g
            WHERE u.id=p.setter_user_id AND g.id=p.group_id AND
            """
        sql += """ (p.group_id=%s) """
        sql += """ ORDER BY p.id limit %s OFFSET %s """

        res  = yield self.db.execute(sql, (data['user_id'], data['user_id'], data['group_id'], data['count'], (int(data["page"])-1)*int(data["count"]), ))
        return (None, res.fetchall())

    def signin(self, data, req): #need to set cookie
        '''
        data(dict): account(str), passwd(str)
        return id(str)
        '''
        ### check required arguemts
        required_args = [{
            'name': '+account',
            'type': str,
        }, {
            'name': '+passwd',
            'type': str,
        }]
        err = form_validation(data, required_args)
        if err: return (err, None)

        ### get hashed passwd
        col = ['passwd', 'id', 'token']
        sql = self.gen_select_sql('users', col)
        res = yield self.db.execute(sql+' WHERE account = %s;', (data['account'],))
        ### check account 
        if res.rowcount == 0:
            return ((404, 'User Not Exist'), None)
        res = res.fetchone()
        hpwd, id, token = res["passwd"], res["id"], res['token']
        ### check passwd
        if self.hash_pwd(data['passwd']) != hpwd:
            return ((400, 'Wrong Password'), None)
        req.set_secure_cookie('token', token)
        return (None, str(id))

    def get_problem(self, data={}):
        required_args = [{
            'name': '+id',
            'type': int,
        }]
        err = form_validation(data, required_args)
        if err: return (err, None)
        if int(data['id']) == 0:
            col = ["id", "title", "description", "input", "output", "sample_input", "sample_output", "hint", "source", "group_id", "setter_user_id", "visible", "interactive", "checker_id", "created_at", "updated_at","pdf", "score_type_id"]
            res = { x: "" for x in col }
            res['id'] = 0
            res['visible'] = 0
            res['verdict_id'] = 1
            res['score_type_id'] = 1
            return (None, res)

        # res = self.rs.get('[email protected]%s' % str(data['id']))
        res = None
        if not res:
            sql = "SELECT p.*, u.account as setter_user FROM problems as p, users as u WHERE p.setter_user_id=u.id AND p.id=%s"
            res = yield self.db.execute(sql, (data["id"], ))
            if res.rowcount == 0:
                return ('No problem id', None)
            res = res.fetchone()
            # self.rs.set('[email protected]%s' % str(data['id']), res)
        err, res['execute'] = yield from Service.Problem.get_problem_execute({'problem_id': data['id']})
        err, res['testdata'] = yield from Service.Testdata.get_testdata_list_by_problem({'problem_id': data['id']})
        return (None, res)

 def put_bulletin(self, data={}):
     required_args = [{
         'name': '+id',
         'type': int,
     }, {
         'name': '+group_id',
         'type': int,
     }, {
         'name': '+setter_user_id',
         'type': int,
     }, {
         'name': '+title',
         'type': str,
         'xss': True,
     }, {
         'name': '+content',
         'type': str,
         'xss': True,
     }]
     err = form_validation(data, required_args)
     if err: return (err, None)
     err, res = yield from self.get_bulletin(data)
     if err: return (err, None)
     data.pop('id')
     sql, parma = self.gen_update_sql("bulletins", data)
     yield self.db.execute("%s WHERE id=%%s AND group_id=%%s;"%sql, parma+(res['id'],res['group_id'],))
     return (None, None)

    def get_user_info_by_account_passwd(self, data={}):
        required_args = [{
            'name': '+account',
            'type': str,
        }, {
            'name': '+passwd',
            'type': str,
        }]
        err = form_validation(data, required_args)
        if err: return (err, None)

        ### get hashed passwd
        col = ['passwd', 'id', 'token']
        sql = self.gen_select_sql('users', col)
        res = yield self.db.execute(sql+' WHERE account = %s;', (data['account'],))
        ### check account 
        if res.rowcount == 0:
            return ((404, 'User Not Exist'), None)
        res = res.fetchone()
        hpwd, id, token = res["passwd"], res["id"], res['token']
        ### check passwd
        if self.hash_pwd(data['passwd']) != hpwd:
            return ((400, 'Wrong Password'), None)
        return (None, {
            'id': id,
            'token': token
        })

    def get_verdict(self, data={}):
        required_args = [{
            'name': '+id',
            'type': int,
        }]
        err = form_validation(data, required_args)
        if err: return (err, None)
        if int(data['id']) == 0:
            col = ['id', 'title', 'execute_type_id', 'execute_type_id', 'file_name', 'setter_user_id']
            res = {x: '' for x in col}
            res['id'] = 0
            return (None, res)
        # res = self.rs.get('[email protected]%s'%str(data['id']))
        # if res: return (None, res)
        res = yield self.db.execute('SELECT v.*, u.account as setter_user FROM verdicts as v, users as u WHERE v.id=%s AND v.setter_user_id=u.id;', (data['id'],))
        if res.rowcount == 0:
            return ((404, 'No Verdict ID'), None)
        res = res.fetchone()
        err, res['execute_type'] = yield from Service.Execute.get_execute({'id': res['execute_type_id']})

        folder = '%s/data/verdicts/%s/' % (config.DATAROOT, str(res['id']))
        file_path = '%s/%s' % (folder, res['file_name'])
        try: os.makedirs(folder)
        except: pass
        with open(file_path) as f:
            res['code'] = f.read()
        res['code_line'] = len(open(file_path).readlines())
        # self.rs.set('[email protected]%s'%(str(data['id'])), res)
        return (None, res)

    def get_contest(self, data={}):
        required_args = [{"name": "+id", "type": int}]
        err = form_validation(data, required_args)
        if err:
            return (err, None)
        ### new contest
        if int(data["id"]) == 0:
            col = ["id", "group_id", "visible", "title", "description", "setter_user_id", "type", "freeze"]
            res = {x: "" for x in col}
            col = ["register_start", "register_end", "start", "end"]
            res.update({x: datetime.datetime.now() for x in col})
            res["visible"] = 0
            res["id"] = 0
            res["problem"] = []
            return (None, res)

        # res = self.rs.get('[email protected]%s'%str(data['id']))
        res = None
        if not res:
            res = yield self.db.execute(
                "SELECT c.*, u.account as setter_user FROM contests as c, users as u WHERE c.setter_user_id=u.id AND c.id=%s;",
                (data["id"],),
            )
            if res.rowcount == 0:
                return ((404, "No Contest ID"), None)
            res = res.fetchone()
            # self.rs.set('[email protected]%s'%str(data['id']), res)
        err, res["problem"] = yield from self.get_contest_problem_list(data)
        err, res["user"] = yield from self.get_contest_user(data)
        return (None, res)

 def post_execute_priority(self, data={}):
     required_args = [{
         'name': '+priority',
         'type': dict,
     }] 
     err = form_validation(data, required_args)
     if err: return (err, None)
     priority = data['priority']
     err, execute_list = yield from self.get_execute_list()
     execute_list = [x['id'] for x in execute_list]
     max_priority = len(execute_list)
     for execute in execute_list:
         execute = int(execute)
         if execute not in priority:
             return ((400, 'priority of execute_type.%s not found'%(str(execute))), None)
     id = list(priority.keys())
     for execute in priority:
         execute = int(execute)
         if execute not in execute_list:
             return ((400, 'execute_type.%s not exist'%(str(execute))), None)
         if execute > max_priority:
             return ((400, 'priority of execute_type.%s error'%(str(execute))), None)
         if id.count(execute) > 1:
             return ((400, 'priority can not duplicate'), None)
     for id, pri in priority.items():
         yield self.db.execute('UPDATE execute_types SET priority=%s WHERE id=%s;', (pri, id,))
         # self.rs.delete('[email protected]%s'%(str(id)))
     # self.rs.delete('execute_list')
     return (None, None)

 def post_group_user(self, data={}):
     required_args = [{
         'name': '+user_id',
         'type': int,
     }, {
         'name': '+group_id',
         'type': int,
     }, {
         'name': 'force',
         'type': bool,
     }]
     err = form_validation(data, required_args)
     if err: return (err, None)
     if 'force' in data:
         force = data.pop('force')
     else: force = False
     err, group = yield from Service.Group.get_group({'id': data['group_id']})
     if err: return (err, None)
     if group['type'] == 0 or force == True: # public
         if force:
             self.db.execute('DELETE FROM map_inpublic_group_user WHERE group_id=%s AND user_id=%s;', (data['group_id'], data['user_id'],))
         sql, param = self.gen_insert_sql('map_group_user', data)
         try: res = yield self.db.execute(sql, param)
         except: return ((400, 'Already in'), None)
         id = res.fetchone()['id']
         return (None, id)
     elif group['type'] == 1: #inpublic
         sql, param = self.gen_insert_sql('map_inpublic_group_user', data)
         try: res = self.db.execute(sql, param)
         except: return ((400, 'Already registered'), None)
         return (None, None)
     elif group['type'] == -1: #private
         return ((403, 'This group is private'), None)

 def post_execute(self, data={}):
     required_args = [{
         'name': '+setter_user_id',
         'type': int,
     }, {
         'name': '+lang',
         'type': int,
     }, {
         'name': 'description',
         'type': str,
     }, {
         'name': 'command',
         'type': list,
     }, {
         'name': 'cm_mode',
         'type': str,
     }]
     err = form_validation(data, required_args)
     if err: return (err, None)
     # self.rs.delete('execute_list')
     command = data.pop('command')
     sql, parma = self.gen_insert_sql("execute_types", data)
     id = (yield self.db.execute(sql, parma)).fetchone()['id']
     yield self.db.execute("DELETE FROM execute_steps WHERE execute_type_id=%s", (id,))
     for x in command:
         meta = {}
         meta['command'] = x
         meta['execute_type_id'] = id
         sql, parma = self.gen_insert_sql("execute_steps", meta)
         yield self.db.execute(sql, parma)
     # self.rs.delete('[email protected]%s'%(str(id)))
     return (None, id)

 def get_execute(self, data={}):
     required_args = [{
         'name': '+id',
         'type': int,
     }]
     err = form_validation(data, required_args)
     if err: return (err, None)
     if int(data['id']) == 0:
         res = {}
         res['steps'] = []
         res['id'] = 0
         res['lang'] = 0
         res['description'] = ''
         res['cm_mode'] = ''
         return (None, res)
     # res = self.rs.get('[email protected]%s'%(str(data['id'])))
     # if res: return (None, res)
     sql = "SELECT e.*, u.account as setter_user FROM execute_types as e, users as u WHERE e.id=%s AND e.setter_user_id=u.id"
     res = yield self.db.execute(sql, (data["id"], ))
     if res.rowcount == 0:
         return ((404, 'Error execute id'), None)
     res = res.fetchone()
     res['steps'] = (yield self.db.execute("SELECT execute_steps.* FROM execute_steps WHERE execute_type_id=%s ORDER BY id", (res['id'],))).fetchall()
     for id, x in enumerate(res['steps']):
         x['step'] = id + 1
     # self.rs.set('[email protected]%s'%(str(data['id'])), res)
     return (None, res)

 def get_contest_submission_list(self, data={}):
     required_args = [
         {"name": "+id", "type": int},
         {"name": "+user_id", "type": int},
         {"name": "+current_group_power", "type": set},
         {"name": "+page", "type": int},
         {"name": "+count", "type": int},
     ]
     err = form_validation(data, required_args)
     if err:
         return (err, None)
     err, res = yield from self.get_contest(data)
     start = res["start"]
     end = res["end"]
     sql = """
     SELECT s.*, u.account as user, p.title as problem_title
     FROM submissions as s, users as u, map_contest_problem as mp, map_contest_user as mu, problems as p
     WHERE """
     if map_group_power["contest_manage"] not in data["current_group_power"]:
         sql += "u.id=%s AND " % (int(data["user_id"]))
     sql += """
     u.id=s.user_id AND mu.user_id=u.id 
     AND mu.contest_id=%s AND mp.contest_id=mu.contest_id  
     AND mp.problem_id=s.problem_id 
     AND mp.problem_id=p.id
     AND %s<=s.created_at AND s.created_at<=%s 
     ORDER BY s.id DESC LIMIT %s OFFSET %s;
     """
     submissions = yield self.db.execute(
         sql, (res["id"], start, end, data["count"], int(data["page"] - 1) * int(data["count"]))
     )
     return (None, submissions.fetchall())

 def get_contest_submission_list_count(self, data):
     required_args = [
         {"name": "+id", "type": int},
         {"name": "+user_id", "type": int},
         {"name": "+current_group_power", "type": set},
     ]
     err = form_validation(data, required_args)
     if err:
         return (err, None)
     err, res = yield from self.get_contest(data)
     start = res["start"]
     end = res["end"]
     sql = """
     SELECT count(*)
     FROM submissions as s, map_contest_problem as mp, map_contest_user as mu, problems as p
     WHERE """
     if map_group_power["contest_manage"] not in data["current_group_power"]:
         sql += "s.user_id=%s AND " % (int(data["user_id"]))
     sql += """
     mu.user_id=s.user_id 
     AND mu.contest_id=%s AND mp.contest_id=mu.contest_id  
     AND mp.problem_id=s.problem_id 
     AND mp.problem_id=p.id
     AND %s<=s.created_at AND s.created_at<=%s 
     """
     res = yield self.db.execute(sql, (res["id"], start, end))
     return (None, res.fetchone()["count"])

 def put_contest(self, data={}):
     required_args = [
         {"name": "+id", "type": int},
         {"name": "+group_id", "type": int},
         {"name": "+setter_user_id", "type": int},
         {"name": "+visible", "type": int},
         {"name": "+title", "type": str},
         {"name": "+description", "type": str, "xss": True},
         {"name": "+register_start", "type": datetime.datetime},
         {"name": "+register_end", "type": datetime.datetime},
         {"name": "+start", "type": datetime.datetime},
         {"name": "+end", "type": datetime.datetime},
         {"name": "+freeze", "type": int},
         {"name": "+type", "type": int},
     ]
     err = form_validation(data, required_args)
     if err:
         return (err, None)
     if not data["freeze"] or data["freeze"] == "":
         data["freeze"] = 0
     err, res = yield from self.get_contest(data)
     if err:
         return (err, None)
     data.pop("id")
     sql, param = self.gen_update_sql("contests", data)
     yield self.db.execute(sql + " WHERE id=%s AND group_id=%s;", param + (res["id"], res["group_id"]))
     # self.rs.delete('[email protected]%s'%str(res['id']))
     return (None, res["id"])

 def get_submission_list_count(self, data):
     required_args = [{
         'name': '+group_id',
         'type': int,
     }, {
         'name': 'problem_id',
         'type': int,
     }, {
         'name': 'account',
         'type': str,
     }, {
         'name': 'verdict',
         'type': int,
     }]
     err = form_validation(data, required_args)
     if err: return (err, None)
     sql = "SELECT count(*) FROM submissions as s, problems as p"
     sql += " WHERE s.problem_id = p.id AND p.group_id=%s"%(data['group_id'])
     if data['problem_id']:
         sql += " AND problem_id=%s " % (int(data['problem_id']))
     if data['account']:
         try:
             user_id = (yield self.db.execute("SELECT id FROM users WHERE account=%s", (data['account'],))).fetchone()['id']
         except:
             user_id = 0
         sql += " AND user_id=%s " % (user_id)
     if data['verdict']:
         sql += " AND s.verdict=%s" % (data['verdict'])
     res = yield self.db.execute(sql)
     return (None, res.fetchone()['count'])

 def post_verdict(self ,data={}):
     required_args = [{
         'name': '+title',
         'type': str,
     }, {
         'name': '+execute_type_id',
         'type': int,
     }, {
         'name': '+setter_user_id',
         'type': int,
     }, {
         'name': '+code_file',
     }]
     err = form_validation(data, required_args)
     if err: return (err, None)
     code_file = None
     if data['code_file'] is None:
         return ((400, 'No code file'), None)
     data['file_name'] = data['code_file']['filename']
     code_file = data.pop('code_file')
     sql, param = self.gen_insert_sql('verdicts', data)
     id = (yield self.db.execute(sql, param)).fetchone()['id']
     
     if code_file:
         folder = '%s/data/verdicts/%s/' % (config.DATAROOT, str(id))
         file_path = '%s/%s' % (folder, data['file_name'])
         try: shutil.rmtree(folder)
         except: pass
         try: os.makedirs(folder)
         except: pass
         with open(file_path, 'wb+') as f:
             f.write(code_file['body'])
     return (None, str(id))

    def put_testdata(self, data={}):
        required_args = [
            {"name": "+id", "type": int},
            {"name": "+problem_id", "type": int},
            {"name": "input"},
            {"name": "output"},
            {"name": "+score", "type": int},
            {"name": "+time_limit", "type": int},
            {"name": "+memory_limit", "type": int},
            {"name": "+output_limit", "type": int},
        ]
        err = form_validation(data, required_args)
        if err:
            return (err, None)
        # self.rs.delete('[email protected]%s'%(str(data['id'])))
        # self.rs.delete('[email protected]@%s'%(str(data['problem_id'])))
        required_args = ["time_limit", "memory_limit", "output_limit", "score"]
        meta = {x: data[x] for x in required_args}
        sql, parma = self.gen_update_sql("testdata", meta)
        yield self.db.execute("%s WHERE id=%s" % (sql, data["id"]), parma)

        """ create folder """
        folder = "%s/data/testdata/%s" % (config.DATAROOT, str(data["id"]))
        try:
            os.makedirs(folder)
        except Exception as e:
            print(e)
        """ save file and upload to ftp """
        for x in ["input", "output"]:
            if data[x] != None:
                file_path = "%s/%s" % (folder, x)
                with open(file_path, "wb+") as f:
                    f.write(data[x]["body"])
        return (None, data["id"])

 def post_submission(self, data):
     required_args = [{
         'name': '+problem_id',
         'type': int,
     }, {
         'name': '+execute_type_id',
         'type': int,
     }, {
         'name': '+user_id',
         'type': int,
     }, {
         'name': '+ip',
         'type': str,
     }]
     err = form_validation(data, required_args)
     if err: return(err, None)
     if data['code_file'] == None and len(data['plain_code']) == 0:
         return ((400, 'No code'), None)
     meta = { x['name']: data[x['name']] for x in required_args }
     ### check problem has execute_type
     res = yield self.db.execute("SELECT * FROM map_problem_execute WHERE problem_id=%s and execute_type_id=%s", (data['problem_id'], data['execute_type_id'],))
     if res.rowcount == 0:
         return ((400, 'No execute type'), None)
     err, data['execute'] = yield from Service.Execute.get_execute({'id': data['execute_type_id']})
     ### get file name and length
     if data['code_file']:
         meta['file_name'] = data['code_file']['filename']
         meta['length'] = len(data['code_file']['body'])
     else:
         if data['plain_file_name'] is None:
             data['plain_file_name'] = ''
         if re.match('[\w\.]*', data['plain_file_name']).group(0) != data['plain_file_name']:
             data['plain_file_name'] = ''
         if data['plain_file_name'] != '':
             meta['file_name'] = data['plain_file_name']
         else:
             meta['file_name'] = map_default_file_name[int(data['execute']['lang'])]
         meta['length'] = len(data['plain_code'])
     ### save to db
     sql, parma = self.gen_insert_sql("submissions", meta)
     id = (yield self.db.execute(sql, parma)).fetchone()['id']
     ### save file
     folder = '%s/data/submissions/%s/' % (config.DATAROOT, str(id))
     file_path = '%s/%s' % (folder, meta['file_name'])
     try: shutil.rmtree(folder)
     except: pass
     try: os.makedirs(folder)
     except: pass
     print(type(data['plain_code']))
     try: print(type(data['code_file']['body']))
     except: pass
     if data['code_file']:
         with open(file_path, 'wb+') as f:
             f.write(data['code_file']['body'])
     else:
         with open(file_path, 'w+') as f:
             f.write(data['plain_code'])
     yield self.db.execute('INSERT INTO wait_submissions (submission_id) VALUES(%s);', (id,))
     return (None, id) 

 def delete_testdata(self, data={}):
     required_args = [{"name": "+id", "type": int}]
     err = form_validation(data, required_args)
     if err:
         return (err, None)
     # self.rs.delete('[email protected]%s'%(str(data['id'])))
     # self.rs.delete('[email protected]@%s'%(data['problem_id']))
     yield self.db.execute("DELETE FROM testdata WHERE id=%s", (data["id"],))
     return (None, None)