54 lines
1.4 KiB
SQL
54 lines
1.4 KiB
SQL
DELIMITER $$
|
|
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`messageSendWithUser`(vSenderFK INT, vRecipient VARCHAR(255) CHARSET utf8, vMessage TEXT CHARSET utf8)
|
|
RETURNS int(11)
|
|
DETERMINISTIC
|
|
BEGIN
|
|
|
|
DECLARE vCount INT;
|
|
DECLARE vUuid VARCHAR(255);
|
|
DECLARE vSendDate DATETIME DEFAULT util.VN_NOW();
|
|
DECLARE vSender VARCHAR(255) CHARSET utf8;
|
|
|
|
SELECT `name` INTO vSender
|
|
FROM account.user WHERE id = vSenderFK;
|
|
|
|
SET vRecipient = LOWER(vRecipient);
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS tRecipients;
|
|
CREATE TEMPORARY TABLE tRecipients
|
|
SELECT u.name finalRecipient
|
|
FROM account.mailAlias a
|
|
JOIN account.mailAliasAccount aa ON aa.mailAlias = a.id
|
|
JOIN account.user u ON u.id = aa.account
|
|
WHERE a.alias = vRecipient COLLATE utf8_unicode_ci
|
|
AND u.name != vSender
|
|
AND u.active
|
|
UNION
|
|
SELECT u.name FROM account.user u
|
|
WHERE u.name = vRecipient
|
|
AND u.active;
|
|
|
|
SELECT COUNT(*) INTO vCount FROM tRecipients;
|
|
|
|
IF vCount = 0 THEN
|
|
RETURN vCount;
|
|
END IF;
|
|
|
|
SET vUuid = UUID();
|
|
|
|
INSERT INTO message
|
|
SET uuid = vUuid,
|
|
sender = vSender,
|
|
recipient = vRecipient,
|
|
message = vMessage,
|
|
sendDate = vSendDate;
|
|
|
|
INSERT INTO messageInbox (uuid, sender, recipient, finalRecipient, message, sendDate)
|
|
SELECT vUuid, vSender, vRecipient, finalRecipient, vMessage, vSendDate
|
|
FROM tRecipients;
|
|
|
|
DROP TEMPORARY TABLE tRecipients;
|
|
RETURN vCount;
|
|
END$$
|
|
DELIMITER ;
|