Fixed RoDEX mail return not working correctly

8 files changed, 65 insertions, 28 deletions

diff --git a/sql-files/main.sql b/sql-files/main.sql
index 12607531b..5546c0271 100644
--- a/sql-files/main.sql
+++ b/sql-files/main.sql
@@ -889,6 +889,7 @@ INSERT IGNORE INTO `sql_updates` (`timestamp`) VALUES (1489588190); -- 2017-03-1
 INSERT IGNORE INTO `sql_updates` (`timestamp`) VALUES (1496588640); -- 2017-06-04--15-04.sql
 INSERT IGNORE INTO `sql_updates` (`timestamp`) VALUES (1496588700); -- 2017-06-04--15-05.sql
 INSERT IGNORE INTO `sql_updates` (`timestamp`) VALUES (1509835214); -- 2017-11-04--10-39.sql
+INSERT IGNORE INTO `sql_updates` (`timestamp`) VALUES (1519671456); -- 2018-02-26--15-57.sql
 --
 -- Table structure for table `storage`
 --
@@ -959,12 +960,13 @@ CREATE TABLE IF NOT EXISTS `rodex_mail` (
 	`sender_id` INT(11) NOT NULL,
 	`receiver_name` VARCHAR(30) NOT NULL,
 	`receiver_id` INT(11) NOT NULL,
-  `receiver_accountid` INT(11) NOT NULL,
+	`receiver_accountid` INT(11) NOT NULL,
 	`title` VARCHAR(50) NOT NULL,
 	`body` VARCHAR(510) NOT NULL,
 	`zeny` BIGINT(20) NOT NULL,
 	`type` TINYINT(8) UNSIGNED NOT NULL,
 	`is_read` TINYINT(8) NOT NULL,
+	`sender_read` TINYINT(2) NOT NULL,
 	`send_date` INT(11) NOT NULL,
 	`expire_date` INT(11) NOT NULL,
 	`weight` INT(11) NOT NULL,
diff --git a/sql-files/upgrades/2018-02-26--15-57.sql b/sql-files/upgrades/2018-02-26--15-57.sql
new file mode 100644
index 000000000..a2090bb3c
--- /dev/null
+++ b/sql-files/upgrades/2018-02-26--15-57.sql
@@ -0,0 +1,6 @@
+#1519671456
+
+ALTER TABLE `rodex_mail`
+	ADD COLUMN `sender_read` TINYINT(2) NOT NULL DEFAULT '0' AFTER `is_read`;
+
+INSERT INTO `sql_updates` (`timestamp`, `ignored`) VALUES (1519671456 , 'No');
diff --git a/sql-files/upgrades/index.txt b/sql-files/upgrades/index.txt
index 2b1409aba..a2d0c5171 100644
--- a/sql-files/upgrades/index.txt
+++ b/sql-files/upgrades/index.txt
@@ -43,3 +43,4 @@
 2017-06-04--15-04.sql
 2017-06-04--15-05.sql
 2017-11-04--10-39.sql
+2018-02-26--15-57.sql
diff --git a/src/char/int_rodex.c b/src/char/int_rodex.c
index b7314e726..127f7a213 100644
--- a/src/char/int_rodex.c
+++ b/src/char/int_rodex.c
@@ -55,7 +55,7 @@ static int inter_rodex_fromsql(int char_id, int account_id, int8 opentype, int64
 	case RODEX_OPENTYPE_MAIL:
 		if (SQL_ERROR == SQL->StmtPrepare(stmt,
 			"SELECT `mail_id`, `sender_name`, `sender_id`, `receiver_name`, `receiver_id`, `receiver_accountid`,"
-			"`title`, `body`, `zeny`, `type`, `is_read`, `send_date`, `expire_date`, `weight`"
+			"`title`, `body`, `zeny`, `type`, `is_read`, `sender_read`, `send_date`, `expire_date`, `weight`"
 			"FROM `%s` WHERE `expire_date` > '%d' AND `receiver_id` = '%d' AND `mail_id` > '%"PRId64"'"
 			"ORDER BY `mail_id` ASC", rodex_db, (int)time(NULL), char_id, mail_id)
 			) {
@@ -68,7 +68,7 @@ static int inter_rodex_fromsql(int char_id, int account_id, int8 opentype, int64
 	case RODEX_OPENTYPE_ACCOUNT:
 		if (SQL_ERROR == SQL->StmtPrepare(stmt,
 			"SELECT `mail_id`, `sender_name`, `sender_id`, `receiver_name`, `receiver_id`, `receiver_accountid`,"
-			"`title`, `body`, `zeny`, `type`, `is_read`, `send_date`, `expire_date`, `weight`"
+			"`title`, `body`, `zeny`, `type`, `is_read`, `sender_read`, `send_date`, `expire_date`, `weight`"
 			"FROM `%s` WHERE "
 			"`expire_date` > '%d' AND `receiver_accountid` = '%d' AND `mail_id` > '%"PRId64"'"
 			"ORDER BY `mail_id` ASC", rodex_db, (int)time(NULL), account_id, mail_id)
@@ -82,8 +82,8 @@ static int inter_rodex_fromsql(int char_id, int account_id, int8 opentype, int64
 	case RODEX_OPENTYPE_RETURN:
 		if (SQL_ERROR == SQL->StmtPrepare(stmt,
 			"SELECT `mail_id`, `sender_name`, `sender_id`, `receiver_name`, `receiver_id`, `receiver_accountid`,"
-			"`title`, `body`, `zeny`, `type`, `is_read`, `send_date`, `expire_date`, `weight`"
-			"FROM `%s` WHERE (`sender_id` = '%d' AND `expire_date` <= '%d' AND `send_date` + '%d' > '%d' AND `mail_id` > '%"PRId64"')"
+			"`title`, `body`, `zeny`, `type`, `is_read`, `sender_read`, `send_date`, `expire_date`, `weight`"
+			"FROM `%s` WHERE (`is_read` = 0 AND `sender_id` = '%d' AND `expire_date` <= '%d' AND `send_date` + '%d' > '%d' AND `mail_id` > '%"PRId64"')"
 			"ORDER BY `mail_id` ASC", rodex_db, char_id, (int)time(NULL), 2 * RODEX_EXPIRE, (int)time(NULL), mail_id)
 			) {
 			SqlStmt_ShowDebug(stmt);
@@ -95,9 +95,11 @@ static int inter_rodex_fromsql(int char_id, int account_id, int8 opentype, int64
 	case RODEX_OPENTYPE_UNSET:
 		if (SQL_ERROR == SQL->StmtPrepare(stmt,
 			"SELECT `mail_id`, `sender_name`, `sender_id`, `receiver_name`, `receiver_id`, `receiver_accountid`,"
-			"`title`, `body`, `zeny`, `type`, `is_read`, `send_date`, `expire_date`, `weight`"
-			"FROM `%s` WHERE `expire_date` > '%d' AND (`receiver_id` = '%d' or `receiver_accountid` = '%d') AND `mail_id` > '%"PRId64"'"
-			"ORDER BY `mail_id` ASC", rodex_db, (int)time(NULL), char_id, account_id, mail_id)
+			"`title`, `body`, `zeny`, `type`, `is_read`, `sender_read`, `send_date`, `expire_date`, `weight`"
+			"FROM `%s` WHERE "
+			"((`expire_date` > '%d' AND (`receiver_id` = '%d' OR `receiver_accountid` = '%d'))"
+			"OR (`is_read` = 0 AND `sender_id` = '%d' AND `expire_date` <= '%d' AND `send_date` + '%d' > '%d'))"
+			"ORDER BY `mail_id` ASC", rodex_db, (int)time(NULL), char_id, account_id, char_id, (int)time(NULL), 2 * RODEX_EXPIRE, (int)time(NULL))
 			) {
 			SqlStmt_ShowDebug(stmt);
 			SQL->StmtFree(stmt);
@@ -118,9 +120,10 @@ static int inter_rodex_fromsql(int char_id, int account_id, int8 opentype, int64
 	 || SQL_ERROR == SQL->StmtBindColumn(stmt, 8,  SQLDT_INT64,  &msg.zeny,               sizeof msg.zeny,               NULL, NULL)
 	 || SQL_ERROR == SQL->StmtBindColumn(stmt, 9,  SQLDT_UINT8,  &msg.type,               sizeof msg.type,               NULL, NULL)
 	 || SQL_ERROR == SQL->StmtBindColumn(stmt, 10, SQLDT_BOOL,   &msg.is_read,            sizeof msg.is_read,            NULL, NULL)
-	 || SQL_ERROR == SQL->StmtBindColumn(stmt, 11, SQLDT_INT,    &msg.send_date,          sizeof msg.send_date,          NULL, NULL)
-	 || SQL_ERROR == SQL->StmtBindColumn(stmt, 12, SQLDT_INT,    &msg.expire_date,        sizeof msg.expire_date,        NULL, NULL)
-	 || SQL_ERROR == SQL->StmtBindColumn(stmt, 13, SQLDT_INT,    &msg.weight,             sizeof msg.weight,             NULL, NULL)
+	 || SQL_ERROR == SQL->StmtBindColumn(stmt, 11, SQLDT_BOOL,   &msg.sender_read,        sizeof msg.sender_read,        NULL, NULL)
+	 || SQL_ERROR == SQL->StmtBindColumn(stmt, 12, SQLDT_INT,    &msg.send_date,          sizeof msg.send_date,          NULL, NULL)
+	 || SQL_ERROR == SQL->StmtBindColumn(stmt, 13, SQLDT_INT,    &msg.expire_date,        sizeof msg.expire_date,        NULL, NULL)
+	 || SQL_ERROR == SQL->StmtBindColumn(stmt, 14, SQLDT_INT,    &msg.weight,             sizeof msg.weight,             NULL, NULL)
 	) {
 		SqlStmt_ShowDebug(stmt);
 		SQL->StmtFree(stmt);
@@ -201,10 +204,12 @@ static int inter_rodex_fromsql(int char_id, int account_id, int8 opentype, int64
 
 #if PACKETVER >= 20170419
 			if (opentype == RODEX_OPENTYPE_UNSET) {
-				if (msg.receiver_id != 0)
-					msg.opentype = RODEX_OPENTYPE_MAIL;
-				else
+				if (msg.receiver_id == 0)
 					msg.opentype = RODEX_OPENTYPE_ACCOUNT;
+				else if (msg.expire_date < time(NULL))
+					msg.opentype = RODEX_OPENTYPE_RETURN;
+				else
+					msg.opentype = RODEX_OPENTYPE_MAIL;
 			} else {
 				msg.opentype = opentype;
 			}
@@ -243,8 +248,8 @@ static bool inter_rodex_hasnew(int char_id, int account_id)
 	if (SQL_ERROR == SQL->Query(inter->sql_handle,
 		"SELECT count(*) FROM `%s` WHERE ("
 		"(`expire_date` > '%d' AND (`receiver_id` = '%d' OR `receiver_accountid` = '%d')) OR"
-		"(`sender_id` = '%d' AND `expire_date` <= '%d' AND `send_date` + '%d' > '%d')"
-		") AND (`is_read` = 0 OR (`type` > 0 AND `type` != 8))",
+		"(`sender_id` = '%d' AND `expire_date` <= '%d' AND `send_date` + '%d' > '%d' AND `is_read` = 0)" // is_read is required in this line because of the OR in next condition
+		") AND ((`is_read` = 0 AND `sender_read` = 0) OR (`type` > 0 AND `type` != 8))",
 		rodex_db, (int)time(NULL), char_id, account_id,
 		char_id, (int)time(NULL), 2 * RODEX_EXPIRE, (int)time(NULL))
 		) {
@@ -309,10 +314,10 @@ int64 inter_rodex_savemessage(struct rodex_message* msg)
 	SQL->EscapeStringLen(inter->sql_handle, title, msg->title, strnlen(msg->title, RODEX_TITLE_LENGTH));
 
 	if (SQL_ERROR == SQL->Query(inter->sql_handle, "INSERT INTO `%s` (`sender_name`, `sender_id`, `receiver_name`, `receiver_id`, `receiver_accountid`, `title`, `body`,"
-		"`zeny`, `type`, `is_read`, `send_date`, `expire_date`, `weight`) VALUES "
-		"('%s', '%d', '%s', '%d', '%d', '%s', '%s', '%"PRId64"', '%d', '%d', '%d', '%d', '%d')",
+		"`zeny`, `type`, `is_read`, `sender_read`, `send_date`, `expire_date`, `weight`) VALUES "
+		"('%s', '%d', '%s', '%d', '%d', '%s', '%s', '%"PRId64"', '%d', '%d', '%d', '%d', '%d', '%d')",
 		rodex_db, sender_name, msg->sender_id, receiver_name, msg->receiver_id, msg->receiver_accountid,
-		title, body, msg->zeny, msg->type, msg->is_read == true ? 1 : 0, msg->send_date, msg->expire_date, msg->weight)) {
+		title, body, msg->zeny, msg->type, msg->is_read == true ? 1 : 0, msg->sender_read == true ? 1 : 0, msg->send_date, msg->expire_date, msg->weight)) {
 		Sql_ShowDebug(inter->sql_handle);
 		return 0;
 	}
@@ -443,7 +448,7 @@ void mapif_parse_rodex_updatemail(int fd)
 	int8 flag = RFIFOB(fd, 10);
 
 	Assert_retv(mail_id > 0);
-	Assert_retv(flag >= 0 && flag <= 3);
+	Assert_retv(flag >= 0 && flag <= 4);
 
 	switch (flag) {
 	case 0: // Read
@@ -469,6 +474,11 @@ void mapif_parse_rodex_updatemail(int fd)
 		if (SQL_ERROR == SQL->Query(inter->sql_handle, "DELETE FROM `%s` WHERE `mail_id` = '%"PRId64"'", rodex_item_db, mail_id))
 			Sql_ShowDebug(inter->sql_handle);
 		break;
+
+	case 4: // Sender Read
+		if (SQL_ERROR == SQL->Query(inter->sql_handle, "UPDATE `%s` SET `sender_read` = 1 WHERE `mail_id` = '%"PRId64"'", rodex_db, mail_id))
+			Sql_ShowDebug(inter->sql_handle);
+		break;
 	}
 }
 
diff --git a/src/common/mmo.h b/src/common/mmo.h
index 04daedca0..f9cbc57de 100644
--- a/src/common/mmo.h
+++ b/src/common/mmo.h
@@ -904,6 +904,7 @@ struct rodex_message {
 	uint8 type;
 	int8 opentype;
 	bool is_read;
+	bool sender_read;
 	bool is_deleted;
 	int send_date;
 	int expire_date;
diff --git a/src/map/clif.c b/src/map/clif.c
index 7c314b075..6b444553f 100644
--- a/src/map/clif.c
+++ b/src/map/clif.c
@@ -19720,7 +19720,7 @@ void clif_rodex_send_maillist(int fd, struct map_session_data *sd, int8 open_typ
 			continue;
 
 		inner->MailID = msg->id;
-		inner->Isread = msg->is_read == true ? 1 : 0;
+		inner->Isread = (msg->is_read == true || msg->sender_read == true) ? 1 : 0;
 		inner->type = msg->type;
 #if PACKETVER >= 20170419
 		inner->openType = msg->opentype;
@@ -19779,7 +19779,7 @@ void clif_rodex_send_mails_all(int fd, struct map_session_data *sd)
 			continue;
 
 		inner->MailID = msg->id;
-		inner->Isread = msg->is_read == true ? 1 : 0;
+		inner->Isread = (msg->is_read == true || msg->sender_read == true) ? 1 : 0;
 		inner->type = msg->type;
 		inner->openType = msg->opentype;
 		inner->expireDateTime = msg->expire_date - (int)time(NULL);
@@ -19846,7 +19846,7 @@ void clif_rodex_send_refresh(int fd, struct map_session_data *sd, int8 open_type
 			continue;
 
 		inner->MailID = msg->id;
-		inner->Isread = msg->is_read == true ? 1 : 0;
+		inner->Isread = (msg->is_read == true || msg->sender_read == true) ? 1 : 0;
 		inner->type = msg->type;
 #if PACKETVER >= 20170419
 		inner->openType = msg->opentype;
diff --git a/src/map/intif.c b/src/map/intif.c
index 0e5cd3db2..b8a0be37c 100644
--- a/src/map/intif.c
+++ b/src/map/intif.c
@@ -2599,10 +2599,11 @@ void intif_parse_RodexNotifications(int fd)
 
 /// Updates a mail
 /// flag:
-///     0 - user Read
+///     0 - receiver Read
 ///     1 - user got Zeny
 ///     2 - user got Items
 ///     3 - delete
+///     4 - sender Read (returned mail)
 int intif_rodex_updatemail(int64 mail_id, int8 flag)
 {
 	if (intif->CheckForCharServer())
diff --git a/src/map/rodex.c b/src/map/rodex.c
index d5a6064cf..e6bf7d216 100644
--- a/src/map/rodex.c
+++ b/src/map/rodex.c
@@ -364,15 +364,24 @@ struct rodex_message *rodex_get_mail(struct map_session_data *sd, int64 mail_id)
 {
 	int i;
 	struct rodex_message *msg;
+	int char_id;
 
 	nullpo_retr(NULL, sd);
 
-	ARR_FIND(0, VECTOR_LENGTH(sd->rodex.messages), i, VECTOR_INDEX(sd->rodex.messages, i).id == mail_id && VECTOR_INDEX(sd->rodex.messages, i).is_deleted != true);
+	ARR_FIND(0, VECTOR_LENGTH(sd->rodex.messages), i, VECTOR_INDEX(sd->rodex.messages, i).id == mail_id);
 	if (i == VECTOR_LENGTH(sd->rodex.messages))
 		return NULL;
 
 	msg = &VECTOR_INDEX(sd->rodex.messages, i);
 
+	char_id = sd->status.char_id;
+
+	if ((msg->is_deleted == true)
+		|| (msg->expire_date < time(NULL) && ((msg->receiver_accountid > 0) || (msg->receiver_id == char_id && msg->sender_id != char_id)))
+		|| ((msg->send_date + 2 * RODEX_EXPIRE) < time(NULL))
+		)
+		return NULL;
+
 	return msg;
 }
 
@@ -388,9 +397,16 @@ void rodex_read_mail(struct map_session_data *sd, int64 mail_id)
 	msg = rodex->get_mail(sd, mail_id);
 	nullpo_retv(msg);
 
-	if (msg->is_read == false) {
-		intif->rodex_updatemail(msg->id, 0);
-		msg->is_read = true;
+	if (msg->opentype == RODEX_OPENTYPE_RETURN) {
+		if (msg->sender_read == false) {
+			intif->rodex_updatemail(msg->id, 4);
+			msg->sender_read = true;
+		}
+	} else {
+		if (msg->is_read == false) {
+			intif->rodex_updatemail(msg->id, 0);
+			msg->is_read = true;
+		}
 	}
 
 	clif->rodex_read_mail(sd, msg->opentype, msg);