Triggering OrderShipped email with SQL query if possible

Hi,

my python script changes Order status, and Shipping status when orders are prepared and shipped. Now my questions is how can I trigger OrderShipped email to be sent at the same time via SQL query, if that is possible at all, thanks

You could insert all the relevant fields into the QueuedEmail table, then the schedule task would pick it up.  I'm not immediately sure if there are other dependencies, but it wouldn't be hard to setup a test case.

I think that is it :) thanks I am going to try it.

Well I looked at Queued Emails table and I can get all the info for all Columns except the Body Column, How do I connect the body of an email with particular order order?

Messages can have HTML.  Below is an example of an order paid email from the sample store.  Note that there is are 'lines' to show each product purchased.  That may not be so easy for you to do in SQL.   Also, consider that 3rd party plugins, if you have any, may be handling Order Paid (or Placed) events, and your SQL update of those fields directly won't be seen by them.

<p>
<a href="http://localhost:5000/">Your store name</a>
<br />
<br />
Hello John Smith,
<br />
Thanks for buying from <a href="http://localhost:5000/">Your store name</a>. Below is the summary of the order.
<br />
<br />
Order Number: 6
<br />
Order Details: <a target="_blank" href="http://localhost:5000/orderdetails/6">http://localhost:5000/orderdetails/6</a>
<br />
Date Ordered: Wednesday, September 11, 2019
<br />
<br />
<br />
<br />
Billing Address
<br />
John Smith
<br />
21 West 52nd Street
<br />
New York 10021
<br />
New York United States
<br />
<br />
<br />
<br />
Shipping Address
<br />
John Smith
<br />

<br />
New York 10021
<br />
New York United States
<br />
<br />
Shipping Method: My Ground
<br />
<br />
  <table border="0" style="width:100%;">
<tr style="background-color:#b9babe;text-align:center;">
<th>Name</th>
<th>Price</th>
<th>Quantity</th>
<th>Total</th>
</tr>
<tr style="background-color: #ebecee;text-align: center;">
<td style="padding: 0.6em 0.4em;text-align: left;">HTC One M8 Android L 5.0 Lollipop
<br />
SKU: M8_HTC_5L
</td>
<td style="padding: 0.6em 0.4em;text-align: right;">$245.00</td>
<td style="padding: 0.6em 0.4em;text-align: center;">1</td>
<td style="padding: 0.6em 0.4em;text-align: right;">$245.00</td>
</tr>
<tr><td style="text-align:right;" colspan="1">&nbsp;</td><td colspan="3" style="text-align:right">
Gift wrapping: No
</td></tr>
<tr style="text-align:right;"><td>&nbsp;</td><td colspan="2" style="background-color: #dde2e6;padding:0.6em 0.4 em;"><strong>Sub-Total:</strong></td> <td style="background-color: #dde2e6;padding:0.6em 0.4 em;"><strong>$245.00</strong></td></tr>
<tr style="text-align:right;"><td>&nbsp;</td><td colspan="2" style="background-color: #dde2e6;padding:0.6em 0.4 em;"><strong>Shipping:</strong></td> <td style="background-color: #dde2e6;padding:0.6em 0.4 em;"><strong>$5.00</strong></td></tr>
<tr style="text-align:right;"><td>&nbsp;</td><td colspan="2" style="background-color: #dde2e6;padding:0.6em 0.4 em;"><strong>Tax:</strong></td> <td style="background-color: #dde2e6;padding:0.6em 0.4 em;"><strong>$0.00</strong></td></tr>
<tr style="text-align:right;"><td>&nbsp;</td><td colspan="2" style="background-color: #dde2e6;padding:0.6em 0.4 em;"><strong>Order Total:</strong></td> <td style="background-color: #dde2e6;padding:0.6em 0.4 em;"><strong>$250.00</strong></td></tr>
</table>

</p>

[/b][b][u][/u]OK I get it, so I resorted to triggering my own unique email which I managed by inserting a row into [dbo].[QueuedEmail] and it worked fine.

Now I ran into a small syntax problem. I want to use some variables in the query to fill out the name and the tracking number of the person who placed the order. Now those variables are the ones used in the my java script that fills out the shipping label at our shipping providers webpage.

This is the query I want to run with python

cursor.execute("INSERT INTO [dbo].[QueuedEmail](Priorityid, [From], FromName, [To], ToName, [Subject], Body, CreatedOnUtc, SentTries, EmailAccountId, AttachedDownloadId) VALUES (5, '[email protected]', 'Web Shop Name', '%s', '%s','<p>Web Shop Name<br /><br />Poštovani/na '%s'!, <br />Dobre vijesti! Vaša narudžba ? je poslana.<br />Način dostave: GLS dostavna služba</p>  <p>Broj za praćenje pošiljke: '?'<br /><br />Vašu narudžbu možete pratiti ovdje: https://gls-group.eu/HR/hr/pracenje-posiljke</p>  <p>Napomena: Nekad je potrebno i do 24 sata za vašu pošiljku da se pojavi u sustavu za praćenje.<br /><br /></p>', SYSUTCDATETIME(), 0, 7, 0)", (consig_email, consig_matchcode, consig_matchcode, OrderNumber, TrackingNumber))

Does that makes sense? I did get an error running it.  
It was good when I did not have %s and ? for the variables.

I would put my SQL insert query into a stored procedure with the necessary variables, then pass them as parameters in the python call.  The '%s' looks like string formatting in python, you would use the variable names instead in however it's done in your sql python library, E.G. for pyodbc: https://stackoverflow.com/questions/34296845/python-pyodbc-execute-stored-procedure-with-parameters

If you've written your own unique html email, then another option with its own complexities is to just put it all into a stored procedure that runs on a job schedule.  That goes outside of nopCommerce support territory, but tutorials are easy to find in google.

Thanks I will look into that and get back with the result.

Solved the problem by using f() variable substitution in python to create query!