Queued Emails i18n

[burt]

Queued Emails is a great feature of Phoenix.

It was brought to my attention that it is single language only.
In other words, imagine a shopowner running shop in German and English, but using Admin only in German.

There is only one input box for the Queued Email title/text, which means;

German only Title
German only Text

or

English only Title
English only Text

or

Putting German and English into the Title
Putting German and English into the Text

--

Putting two languages in an email is not unheard of. But it gets unwieldy the more languages a shopowner uses.

Therefore, it would be a lot nicer for the customer to receive a Queued Email in their own language, the language they use in the Shop Side.

Anyone got any thoughts, comments or opinion?

[burt]

Initial thoughts;

Amended to remove Title & Text
TABLE outgoing_tpl (
id int(11) NOT NULL AUTO_INCREMENT,
slug varchar(255) NOT NULL,
date_added datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_modified datetime DEFAULT NULL,
PRIMARY KEY (id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

New Table for i18n
TABLE outgoing_tpl_info (
id int NOT NULL,
languages_id int NOT NULL,
title varchar(255) NOT NULL,
text longtext NOT NULL,
PRIMARY KEY (id, languages_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ADMIN

- amend edit infobox for language display
- amend new infobox for language display
- amend save/edit action logic to make them save the correct bits to the tables
- when adding a language copy from (default)languages_id to (new)languages_id in table.outgoing_tpl_info

SHOP

- pick up session language
- craft email in that language
- push to Queue as normal

I -think- that's it. Am I missing anything?

[beerbee]

Hi,
this could be done without new tables but I don't know if it would be better but easier I guess by adding the language id from session to the slug name in outgoing_tpl db.

So you'd have single entries like order_thanks_1, order_thanks_2 and so on.

The original order_thanks could be left as it is as fallback and for already existing outgoings in the queue.

Only the modules on the shop side would have to be altered.

On the admin side it would be nice to have the following:

The listing could/should be ordered by slug name.
It should give an alert if there is a language variant missing for the installed languages offering to insert a copy of the original with the according slugname_language_id to avoid errors.

Kind regards
Christoph

[burt]

@BrockleyJohn I have soft-deleted your post as it adds confusion. TY.

Updating for i18n is quite complicated because the emails are not actually built until they are needing to be sent.
Shopowner can change the TPL of an email at any point prior to sending.

[burt]

On the admin side it would be nice to have the following:

The listing could/should be ordered by slug name.

The TPL listing on outgoing_tpl.php is ordered by slug name already, I think ?

The emails listing on outgoing.php is ordered by send_date.

[burt]

I think this updates Queued E-mails for internationalisation.
I am seeking feedback please of any errors, bugs etc.

I can't do all the coding & testing alone, as it's so easy to miss things.
TY in advance.

[beerbee]

Hi Gary,
got an error Unknown column 'languages_id' in 'field list', because languages_id was missing in the outgoing table.

Tested with all three included modules, works fine, thanks a lot!

Kind regards
Christoph

[burt]

ALTER TABLE outgoing ADD languages_id INT NOT NULL AFTER id;

In addition to the SQL in the Zip!

[burt]

Here's the full SQL;

Code: Select all

CREATE TABLE outgoing_tpl_info (
  id INT NOT NULL,
  languages_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  text LONGTEXT NOT NULL,
  PRIMARY KEY (id, languages_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

INSERT INTO outgoing_tpl_info (id, languages_id, title, text)
SELECT ot.id, l_all.languages_id, ot.title, ot.text
FROM outgoing_tpl ot
JOIN configuration c ON c.configuration_key = 'DEFAULT_LANGUAGE'
JOIN languages l_en ON l_en.code = c.configuration_value
JOIN languages l_all
LEFT JOIN outgoing_tpl_info oti
  ON oti.id = ot.id AND oti.languages_id = l_all.languages_id
WHERE oti.id IS NULL;

ALTER TABLE outgoing ADD languages_id INT NOT NULL AFTER id;

UPDATE outgoing o
JOIN configuration c ON c.configuration_key = 'DEFAULT_LANGUAGE'
JOIN languages l ON l.code = c.configuration_value
SET o.languages_id = l.languages_id;

ALTER TABLE outgoing_tpl
  DROP title,
  DROP text;
  

This does a number of things;

1. creates a new table for storage of translations of templates
2. populates that new table with translations (all of which will be in your shops default language)
3. adds languages_id to existing outgoing table
4. updates that languages_id to your shops default language (as all existing emails will be one language)
5. drops the two unnecessary columns from the existing outgoing_tpl table.

I think this SQL should be safe.
Only on a TEST SHOP, not a live shop.
TY in advance for any feedback.

[beerbee]

Tried. Works.

Kind regards
Christoph