Example #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;
Example #2
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();
Example #3
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();
Example #4
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;
Example #5
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);
Example #6
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;
Example #7
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);
Example #8
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;
Example #9
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);
Example #10
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;
Example #11
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);
Example #12
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;