Code:
create function canprivatemessage(my_id integer, their_name citext)
returns TABLE(target_id integer, result boolean)
language plpgsql
as $$
DECLARE their_id INTEGER;
BEGIN
SELECT id
INTO their_id
FROM accounts
WHERE displayname ILIKE their_name;
RETURN QUERY SELECT their_id as target_id, (CASE
--Couldn't find player
WHEN (their_id IS NULL)
THEN FALSE
--They aren't even online
WHEN (SELECT count(*)
FROM online_characters oc
WHERE oc.account_id = their_id) <= 0
THEN FALSE
--On their ignore list
WHEN (SELECT count(*)
FROM ignores
WHERE account_id = their_id AND friend_id = my_id) > 0
THEN FALSE
-- PMStatus Offline
WHEN (SELECT pmstatus
FROM characters c
WHERE c.account_id = their_id AND c.id = (SELECT oc.character_id
FROM online_characters oc
WHERE oc.account_id = their_id)) =
2
THEN FALSE
-- PMStatus Friends only
WHEN (SELECT pmstatus
FROM characters c
WHERE c.account_id = their_id AND c.id = (SELECT oc.character_id
FROM online_characters oc
WHERE oc.account_id = their_id)) =
1
THEN (CASE WHEN (SELECT count(*)
FROM friends f2
WHERE f2.account_id = their_id AND f2.friend_id = my_id) > 0
THEN TRUE
ELSE FALSE END)
-- Otherwise we can pm them :)
ELSE TRUE
END) AS result;
END;
$$;
create function getfriendsfor(my_id integer)
returns TABLE(accout_id integer, friend_id integer, world_id integer, service_id integer, character_id integer, displayname character varying, lastname character varying, rights smallint, clanrank integer)
language plpgsql
as $$
BEGIN
RETURN QUERY SELECT
f1.account_id,
f1.friend_id,
(CASE
WHEN ch.pmstatus = 2 --Offline
THEN 0
WHEN ch.pmstatus = 1 --Friends only
THEN (CASE WHEN (SELECT count(*)
FROM friends f2
WHERE f2.account_id = f1.friend_id AND f2.friend_id = f1.account_id) > 0
THEN oc.world_id
ELSE 0 END)
ELSE oc.world_id --Normal
END) AS world_id,
oc.service_id,
oc.character_id,
a.displayname,
a.lastname,
a.rights,
f1.clanrank
FROM friends AS f1
LEFT JOIN online_characters AS oc
ON oc.account_id = f1.friend_id
LEFT JOIN accounts AS a
ON f1.friend_id = a.id
LEFT JOIN characters AS ch
ON ch.id = oc.character_id
WHERE f1.account_id = my_id;
END;
$$;
create function getfriendsfor(my_id integer, pmstatus integer)
returns TABLE(accout_id integer, friend_id integer, world_id integer, service_id integer, character_id integer, displayname character varying, lastname character varying, rights smallint, clanrank integer)
language plpgsql
as $$
BEGIN
RETURN QUERY SELECT
f1.account_id,
f1.friend_id,
CASE
WHEN oc.world_id IS NULL OR oc.world_id = 0
THEN oc.world_id --If their world is null or 0 they are offline already
WHEN pmstatus = 1 AND (SELECT count(*)
FROM friends AS f2
WHERE f2.friend_id = f1.account_id AND f2.account_id = f1.friend_id) > 0
THEN oc.world_id --If other person is friends only and we are on their friends list then show the real world
WHEN pmstatus != 1
THEN oc.world_id --Other person isn't on friends only, we all good :)
ELSE 0 --Other person is friends only and we are no on their friends list. make them appear offline
END AS world_id,
oc.service_id,
oc.character_id,
a.displayname,
a.lastname,
a.rights,
f1.clanrank
FROM friends AS f1
LEFT JOIN online_characters AS oc
ON oc.account_id = f1.friend_id
LEFT JOIN accounts AS a
ON f1.friend_id = a.id
WHERE f1.account_id = my_id;
END;
$$;