Beispiel #1
0
def increment_marshmallows_num (goal_id):
	with connection.cursor() as cursor:
		sql = """UPDATE goals SET 
					marshmallows_num  = (
						SELECT marshmallows_num + 1 FROM (
							SELECT * FROM goals
						) AS c1 WHERE c1.id = %s
					),
					last_update = NOW() 
					WHERE id = %s
					AND active = 1 
					AND 1 <= DATEDIFF(
						NOW(), 
						(
							SELECT last_update FROM (
								SELECT * FROM goals
							) AS c2 WHERE c2.id = %s
						)
					);"""

		execution_result = cursor.execute(sql,(goal_id, goal_id, goal_id));
		connection.commit();

	if execution_result:
		with connection.cursor() as cursor:
			sql = """INSERT INTO activity_date (goal_id, date)
				VALUES (%s, NOW());"""

			cursor.execute(sql,(goal_id));
			connection.commit();

	return execution_result;
Beispiel #2
0
def is_valid_jwt (str_jwt):
	state = True;
	reflesh_jwt = "";
	user_id = "";
	try:
		# change str type to byte type;
		encoded_jwt = str_jwt.encode("utf-8");
		decoded = jwt.decode(encoded_jwt, secret, verify=True);
		user_id = decoded["user_id"];

		with connection.cursor() as cursor:
			sql = """SELECT jwt
						FROM use_jwt
						WHERE users_id = %s;"""

			cursor.execute(sql,(user_id));
			results = cursor.fetchone();
			use_jwt = results["jwt"];

	except Exception as e:
		print("JwtError:", str(e))

		return;

	try:
		if str_jwt != use_jwt:
			raise UnauthenticationError(str_jwt);
			
	except UnauthenticationError as e:
		print("UnauthenticationError",str(e));

		return;

	return state, user_id;
Beispiel #3
0
def delete_goal (goal_id):
	with connection.cursor() as cursor:
		sql = """DELETE FROM goals
					WHERE id = %s;"""

		cursor.execute(sql,(goal_id));
		connection.commit();
Beispiel #4
0
def update_goal (goal_id, value):
	with connection.cursor() as cursor:
		sql = """UPDATE goals SET value = %s 
					WHERE id = %s"""

		cursor.execute(sql,(value, goal_id));
		connection.commit();
Beispiel #5
0
def update_todo (todo_id, value):
	with connection.cursor() as cursor:
		sql = """UPDATE todos SET value = %s 
					WHERE id = %s;"""

		cursor.execute(sql,(value, todo_id));
		connection.commit();

	return;
Beispiel #6
0
def delete_todo (todo_id):
	with connection.cursor() as cursor:
		sql = """DELETE FROM todos 
					WHERE id = %s;"""

		cursor.execute(sql, (todo_id));
		connection.commit();

	return;
Beispiel #7
0
def achieve_toggle_todo (todo_id):
	with connection.cursor() as cursor:
		sql = """SELECT achieve FROM todos
					WHERE id = %s;""";

		cursor.execute(sql, (todo_id));
		result = cursor.fetchone();

	int_bool = 0 if result["achieve"] else 1

	with connection.cursor() as cursor:
		sql = """UPDATE todos SET achieve = %s
					WHERE id = %s;""" 

		cursor.execute(sql, (int_bool, todo_id));
		connection.commit();

	return;
Beispiel #8
0
def get_user_info (user_id):
	with connection.cursor() as cursor:
		sql = """SELECT user_name FROM users 
					WHERE user_id = %s"""

		cursor.execute(sql,(user_id));
		result = cursor.fetchone();

	return result;
Beispiel #9
0
def create_goal (user_id, value):
	with connection.cursor() as cursor:
		sql = """INSERT INTO goals (user_id, value, create_date) 
					VALUES (%s, %s, NOW());"""

		cursor.execute(sql,(user_id, value));
		connection.commit();

	return get_active_goal(user_id);
Beispiel #10
0
def create_todo (goal_id, value):
	with connection.cursor() as cursor:
		sql = """INSERT INTO todos(goals_id, value)
					VALUES (%s, %s);"""

		cursor.execute(sql,(goal_id, value));
		connection.commit();

	return read_todo(goal_id);
Beispiel #11
0
def read_todo (goal_id):
	with connection.cursor() as cursor:
		sql = """SELECT id, value, achieve FROM todos
					WHERE goals_id = %s
					ORDER BY id DESC;"""

		__result = cursor.execute(sql, (goal_id));
		result = cursor.fetchall();

	return result;
Beispiel #12
0
def twitter_callback():
    session = request.environ.get("beaker.session");

    oauth_verifier = request.query['oauth_verifier']

    access_token, access_token_secret = twitter.get_access_token(
        session["twitter"]["request_oauth_token"],
    	session["twitter"]["request_oauth_token_secret"],
        oauth_verifier)

    twitter_user = twitter.verify_credentials(access_token, access_token_secret)
    int(twitter_user.id);

    with connection.cursor() as cursor:
    	sql = """SELECT user_id FROM users
    				WHERE user_id = %s"""

    	cursor.execute(sql,(twitter_user.id));
    	results = cursor.fetchone();
       	
    	if results is None:
    		with connection.cursor() as cursor:
		    	sql = """INSERT INTO users (user_id, user_name) 
		    				SELECT %s,%s
		    				FROM dual 
		    				WHERE NOT EXISTS (
		    					SELECT * 
		    					FROM users 
		    					WHERE user_id = %s
		    				)"""

		    	cursor.execute(sql,(twitter_user.id,twitter_user.screen_name,twitter_user.id))
		    	# connection.commit(twitter_user.id);
		    	connection.commit();
		    	
		    	jwt = generate_jwt(twitter_user.id);
    	else:
    		user_id = results["user_id"];
    		jwt = generate_jwt(user_id);

    redirect(root.SERVER_URL +"?jwt="+ jwt);
Beispiel #13
0
def generate_jwt(user_id):
	exp = datetime.datetime.utcnow() + datetime.timedelta(days = 7);
	encoded = jwt.encode({"exp": exp,"user_id": user_id}, secret, algorithm="HS256").decode('utf-8');

	with connection.cursor() as cursor:
		sql = """INSERT INTO use_jwt(jwt, users_id)
					VALUES (%s, %s)
					ON DUPLICATE KEY UPDATE jwt= %s"""
		
		cursor.execute(sql, (encoded, user_id, encoded));
		connection.commit();

	return encoded;
Beispiel #14
0
	def wrapper():
		payload = request.json["payload"];
		__jwt = payload["jwt"];
		
		try:
			status, user_id = jwt.is_valid_jwt(__jwt);
		except:
			json_data = {
				"api":{"status": "UnAuthentication", "message": "failure authentication", "data": None}
			}
			parsed_json = json.dumps(json_data);
			r = HTTPResponse(status=301, body=parsed_json, content_type = "application/json");
			return r;

		if status:
			# last_update確認して日にちをまたいでいたらtodoのachiveをFalseにする
			result_goal_data = get_active_goal(user_id);
			if result_goal_data is not None:
				goals_id = result_goal_data["id"];

				# NOW()時間がずれている
				with connection.cursor() as cursor:
					sql = """UPDATE todos SET achieve = 0
								WHERE goals_id = %s
								AND 1 <= DATEDIFF(NOW(),(SELECT last_update FROM users WHERE user_id = %s))"""
					cursor.execute(sql,(goals_id, user_id));
					connection.commit();
					
				with connection.cursor() as cursor:
					sql = """UPDATE users SET last_update = NOW()
								WHERE user_id = %s"""

					cursor.execute(sql,(user_id));
					connection.commit();

			result = func(user_id);

		return result;
Beispiel #15
0
def get_active_goal (user_id):
	with connection.cursor() as cursor:
		sql = """SELECT id, value, marshmallows_num, create_date FROM goals 
					WHERE user_id = %s and active = 1"""

		__result = cursor.execute(sql,(user_id));
		result = cursor.fetchone();

		if result is None:
			return result;
			
		result["create_date"] = result["create_date"].strftime("%Y-%m-%d");

	return result;
Beispiel #16
0
def sign_out ():
	payload = request.json["payload"];
	__jwt = payload["jwt"];
	result = jwt.is_valid_jwt(__jwt);
	user_id = result[1];

	with connection.cursor() as cursor:
		sql = """INSERT INTO use_jwt(jwt, users_id)
					VALUES ("", %s)
					ON DUPLICATE KEY UPDATE jwt = ""
					FROM users
					WHERE user_id = %s;"""

		cursor.execute(sql, (user_id));
		connection.commit();

	redirect(root.SERVER_URL);
Beispiel #17
0
def get_actived_goal (goal_id):
	with connection.cursor() as cursor:
		sql = """SELECT date FROM activity_date
					WHERE goal_id = %s""";

		cursor.execute(sql, (goal_id));
		result = cursor.fetchall();

	date_list = [];
	for li in result:
		now = datetime.datetime.now();
		diff = now - li["date"]
		diff_date = diff.days;
		if diff_date <= 91:
			date_str = li["date"].strftime("%Y/%m/%d");
			date_list.append(date_str);
	
	return date_list;
Beispiel #18
0
def get_achive_goal (user_id):
	with connection.cursor() as cursor:
		sql = """SELECT id, value, marshmallows_num FROM goals 
					WHERE user_id = %s and active = 0 and achieve = 1"""

		result = cursor.execute(sql,(user_id));
Beispiel #19
0
def achieve_goal (goal_id):
	with connection.cursor() as cursor:
		sql = """UPDATE goals SET achieve = 1, active = 0;
					WHERE goal_id = %s""";

		cursor.execute(sql,(goal_id));