Macro Scripting - Sample Code
Email Feedback request to Amazon and eBay customers who's order has been processed over 10 days ago
This sample code will get all orders processed from eBay and Amazon that have been processed over 2 weeks ago. We then email please leave feedback email address to the customer and record the email sent in the audit trail. The initial selection will not include the orders where "Please Leave Feedback" email has already been sent.
/*Email Client configuration. Here you will need to specify Server, user name and password for your SMTP server, and also testing tag*/
string SMTPServer = "smtp.gmail.com";
int SMTPPort = 25;
string UserName = "someuser@linnsystems.com";
string Password = "somepassword";
string FromEmail = "someuser@linnsystems.com";
string FromName = "Some user";
//email subject and email body
string Subject = "Please Leave feedback for your [{Source}] order from SUPER COOL STORE";
string eBayEmailBody = "Hello, [{Name}].\r\n\r\nPlease leave feedback for the items you have purchase from us\r\n\r\n[{ItemList}]\r\nThanks a bunch";
/*query syntax. Here we will select all orders processed 10 days ago but not older than 1 month, that don't have FEEDBACK email sent
and order item table. Two queries in one request
*/
string query =@"SELECT pkOrderId,cEmailAddress, cFullName, [Source]
FROM [Order] o
LEFT OUTER JOIN Order_LifeHistory ls on ls.fkOrderId = o.pkOrderID and ls.fkOrderHistoryTypeId='EMAIL_SENT' and ls.Tag='FEEDBACK'
WHERE
o.bProcessed = 1 AND o.HoldOrCancel=0 AND o.dProcessedOn < DATEADD(D,-10,GETDATE()) AND ls.sid_history is null
AND o.Source IN ('DIRECT','EBAY','AMAZON');
SELECT pkOrderId,ItemNumber = oi.ItemNumber,ItemTitle = sis.cItemName
FROM [Order] o
INNER JOIN [OrderItem] oi on oi.fkOrderID = o.pkOrderID
INNER JOIN [StockItems] sis on sis.pkStockID = oi.fkStockID
LEFT OUTER JOIN Order_LifeHistory ls on ls.fkOrderId = o.pkOrderID and ls.fkOrderHistoryTypeId='EMAIL_SENT' and ls.Tag='FEEDBACK'
WHERE
o.bProcessed = 1 AND o.HoldOrCancel=0 AND o.dProcessedOn < DATEADD(D,-10,GETDATE()) AND ls.sid_history is null
AND o.Source IN ('DIRECT','EBAY','AMAZON');";
//list of order ids - here we will put all orderids for which emails have been successfully sent
List<Guid> emailSent = new List<Guid>();
//list of order ids - here we will put all orderids for which email address was invalid
List<Guid> invalidEmail = new List<Guid>();
//exectue data query and get two tables [order] and [orderitems]
List<CodeHelper.DataQueryParameter> par = new List<CodeHelper.DataQueryParameter>();
DataSet ds = CodeHelper.ExecuteDataQuerySet(query, par,new string[]{"order","orderitems"},ActiveConnection);
int count = 0;
//iterate through all orders in the table
foreach(DataRow row in ds.Tables["order"].Rows){
count+=1;
debug.Progress((int)((double)count/(double)ds.Tables["order"].Rows.Count * 100),"Sending Feedback request email to "+ row["cEmailAddress"].ToString());
//check if it has valid email address
if (CodeHelper.IsValidEmail(row["cEmailAddress"].ToString())){
//lets build orderitems text by itterating through all order items and find all items belonging to the selected order
string orderitems = "";
foreach(DataRow itemRow in ds.Tables["orderitems"].Rows)
if ((Guid)itemRow["pkOrderId"]==(Guid)row["pkOrderId"]) // check the order item row belongs to the order
{
// add order item text to the string
orderitems+=(orderitems!="" ? "\r\n":"")+itemRow["ItemNumber"].ToString()+" - " + itemRow["ItemTitle"].ToString();
}
// do a bit of tag replacing
string replacedeBayEmailBody= eBayEmailBody.Replace("[{Name}]",row["cFullName"].ToString());
replacedeBayEmailBody= replacedeBayEmailBody.Replace("[{ItemList}]",orderitems);
string replacedSubject = Subject.Replace("[{Source}]",row["Source"].ToString());
// just a debuging line to see what we are outputting
debug.AddEntry(row["cEmailAddress"].ToString()+" "+ replacedeBayEmailBody);
try{
//Send email using SendEmailNow command in the commondata.
// UNCOMMENT WHEN YOU ARE READY TO SEND EMAILS
//Email.SendEmailNow(SMTPServer,SMTPPort,UserName,Password,true,FromEmail,FromName,row["cEmailAddress"].ToString(),row["cFullName"].ToString(), replacedSubject,replacedeBayEmailBody);
//add order id to the list of successfuly sent emails
emailSent.Add((Guid)row["pkOrderId"]);
}catch(Exception ex){
}
}else{
// put order id into the list of invalid email addresses
invalidEmail.Add((Guid)row["pkOrderId"]);
debug.AddEntry("Invalid email " + row["cEmailAddress"].ToString());
}
}
/*We now sent out all the emails and need to log the fact that the emails have now been sent for all these orders.
This will prevent the script resending emails again. We do this by adding Audit trail tag EMAIL_SENT
This needs to be done in a batch to limit the number of queries we do on the server.
OrderData.AddOrderLifeHistoryBatch - does exactly that
*/
debug.Progress(100,"Logging EMAIL_SENT");
debug.AddEntry("Batch history update for EMAIL_SENT successfully for " + emailSent.Count.ToString());
if (emailSent.Count>0){
OrderData.AddOrderLifeHistoryBatch(ActiveConnection,emailSent.ToArray(),"EMAIL_SENT","Feedback request email sent","","FEEDBACK","Macro Script");
}
debug.AddEntry("Batch history update for EMAIL_SENT unsuccessful for " + invalidEmail.Count.ToString());
if (invalidEmail.Count>0){
OrderData.AddOrderLifeHistoryBatch(ActiveConnection,invalidEmail.ToArray(),"EMAIL_SENT","Feedback: Email is invalid","","FEEDBACK","Macro Script");
}
Email Low Stock Script
The following script will go and get all inventory items where the current stock level + the number in Open Orders is below the minimum level. The products are then ordered by the sales from the past 2 weeks. This is then placed into an email and then fired off to the specified email address.
// Email Configuration - Specify server, username and password details in here.
string SMTPServer = "";
int SMTPPort = 0;
string Username = "";
string Password = "";
string FromEmail = "";
string FromName = "";
bool ssl = true;
string ToEmail = "";
string ToName = "";
string Subject = "Items to Reorder"; // Email Subject
string body = "Find below all products that need reordering: \r\n\r\n"; // Body of the email
// Query to Get all Items below minimum level, ordered by sales from past week.
string query = @"SELECT SKU = si.ItemNumber , si.ItemTitle, StockLevel = sl.Quantity, Due = sl.OnOrder, sl.MinimumLevel, LastWeeksSales = ISNULL(weekSales.Sales, 0)
FROM StockLevel sl
INNER JOIN StockItem si on si.pkStockItemID = sl.fkStockItemId
LEFT OUTER JOIN ( SELECT Sales = COUNT(*), fkStockItemId_processed
FROM [Order] o
INNER JOIN OrderItem oi on oi.fkOrderID = o.pkOrderID
WHERE o.dProcessedOn BETWEEN DATEADD(d, -14,GETDATE()) and GETDATE()
GROUP BY fkStockItemId_processed ) weekSales on weekSales.fkStockItemId_processed = si.pkStockItemID
WHERE sl.Quantity + sl.OnOrder < sl.MinimumLevel
ORDER BY weekSales.Sales desc";
// string to store the list of items that need ordering
string resultItems = "";
// Get the last run time of the script
DateTime td = linnworks.finaware.CommonData.CodeHelper.GetGlobalDateSetting("EMAIL","EMAILTIME",ActiveConnection);
// Check the difference between now and the last time the script was run in hours, if more than 24...
if (DateTime.Now.Subtract(td).TotalHours>24)
{
// Execute data query and place into data table
DataTable dt = CodeHelper.ExecuteDataQuery(query, new List<CodeHelper.DataQueryParameter>(), ActiveConnection);
// Iterate through rows,
foreach(DataRow dr in dt.Rows){
// For every row, output the relevant item information
resultItems += dr[0].ToString() + " - " + dr[1].ToString() + "\r\n" + "Level: " + dr[2].ToString() + " Due: " + dr[3].ToString() + " Minimum Level: " + dr[4].ToString() + " Last weeks sales: " + dr[5].ToString() + "\r\n" + "\r\n"; }
// Add the list of items to the body of the email
body += resultItems;
// Send the email using the settings at the top of the script, remove the two forward-slashes from the beginning of the next line when you are ready to send
//Email.SendEmailNow(SMTPServer, SMTPPort, Username, Password, ssl, FromEmail, FromName, ToEmail, ToName, Subject, body);
// Update the time that the email was sent. linnworks.finaware.CommonData.CodeHelper.SetGlobalDateSetting("EMAIL","EMAILTIME",DateTime.Now,ActiveConnection);
}
linnworks.finaware.CommonData.CodeHelper.SetGlobalDateSetting("EMAIL","EMAILTIME",DateTime.Now,ActiveConnection);
Email customers who's order is unprocessed and is 3 days old or older
This sample code will get all unprocessed orders that are 3 days old or older. We then email the customer to inform then that the order is late and record the email sent in the audit trail. The initial selection will not include the orders where "LATE3DAYS" email has already been sent.
/*Email Client configuration. Here you will need to specify Server, user name and password for your SMTP server, and also testing tag*/
string SMTPServer = "smtp.gmail.com";
int SMTPPort = 25;
string UserName = "someuser@linnsystems.com";
string Password = "somepassword";
string FromEmail = "someuser@linnsystems.com";
string FromName = "Some user";
//email subject and email body
string Subject = "Your order is running late for your [{Source}] order from SUPER COOL STORE";
string eBayEmailBody = "Hello, [{Name}].\r\n\r\nWe regret to inform you that the items you have purchased from us are currently late\r\n\r\n[{ItemList}]\r\nWe will contact your shortly when we are able to fully process your order";
/*query syntax.Here we will select all open orders 3 days, that don't have LATE3DAYS email sent
and order item table. Two queries in one request
*/
string query =@"SELECT pkOrderId,cEmailAddress, cFullName, [Source]
FROM [Order] o
LEFT OUTER JOIN Order_LifeHistory ls on ls.fkOrderId = o.pkOrderID AND ls.fkOrderHistoryTypeId='EMAIL_SENT' AND ls.Tag='LATE3DAYS'
WHERE
o.bProcessed = 0 AND o.HoldOrCancel=0 AND o.dReceievedDate <= DATEADD(d,-3,GETDATE()) AND ls.sid_history is null;
SELECT pkOrderId,ItemNumber = oi.ItemNumber,ItemTitle = sis.cItemName
FROM [Order] o
INNER JOIN [OrderItem] oi on oi.fkOrderID = o.pkOrderID
INNER JOIN [StockItems] sis on sis.pkStockID = oi.fkStockID
LEFT OUTER JOIN Order_LifeHistory ls on ls.fkOrderId = o.pkOrderID AND ls.fkOrderHistoryTypeId='EMAIL_SENT' AND ls.Tag='LATE3DAYS'
WHERE
o.bProcessed = 0 AND o.HoldOrCancel=0 AND o.dReceievedDate <= DATEADD(d,-3,GETDATE()) AND ls.sid_history is null";
//list of order ids - here we will put all orderids for which emails have been successfully sent
List emailSent=new List();
//list of order ids - here we will put all orderids for which email address was invalid
List invalidEmail =new List();
//exectue data query and get two tables [order] and [orderitems]
DataSet ds = CodeHelper.ExecuteDataQuerySet(query,new List(),new string[]{"order","orderitems"},ActiveConnection);
int count = 0;
//iterate through all orders in the table
foreach(DataRow row in ds.Tables["order"].Rows){
count+=1;
debug.Progress((int)((double)count/(double)ds.Tables["order"].Rows.Count * 100),"Sending 3 Day Overdue email to "+ row["cEmailAddress"].ToString());
//check if it has valid email address
if (CodeHelper.IsValidEmail(row["cEmailAddress"].ToString())){
//lets build orderitems text by itterating through all order items and find all items belonging to the selected order
string orderitems = "";
foreach(DataRow itemRow in ds.Tables["orderitems"].Rows)
if ((Guid)itemRow["pkOrderId"]==(Guid)row["pkOrderId"]) // check the order item row belongs to the order
{
// add order item text to the string
orderitems+=(orderitems!="" ? "\r\n":"")+itemRow["ItemNumber"].ToString()+" - " + itemRow["ItemTitle"].ToString();
}
// do a bit of tag replacing
string replacedeBayEmailBody= eBayEmailBody.Replace("[{Name}]",row["cFullName"].ToString());
replacedeBayEmailBody= replacedeBayEmailBody.Replace("[{ItemList}]",orderitems);
string replacedSubject = Subject.Replace("[{Source}]",row["Source"].ToString());
// just a debuging line to see what we are outputting
debug.AddEntry(row["cEmailAddress"].ToString()+" "+ replacedeBayEmailBody);
try{
//Send email using SendEmailNow command in the commondata.
// UNCOMMENT WHEN YOU ARE READY TO SEND EMAILS
//Email.SendEmailNow(SMTPServer,SMTPPort,UserName,Password,true,FromEmail,FromName,row["cEmailAddress"].ToString(),row["cFullName"].ToString(), replacedSubject,replacedeBayEmailBody);
//add order id to the list of successfuly sent emails
emailSent.Add((Guid)row["pkOrderId"]);
}catch(Exception ex){
}
}else{
// put order id into the list of invalid email addresses
invalidEmail.Add((Guid)row["pkOrderId"]);
debug.AddEntry("Invalid email " + row["cEmailAddress"].ToString());
}
}
/*We now sent out all the emails and need to log the fact that the emails have now been sent for all these orders.
This will prevent the script resending emails again. We do this by adding Audit trail tag EMAIL_SENT
This needs to be done in a batch to limit the number of queries we do on the server.
OrderData.AddOrderLifeHistoryBatch - does exactly that
*/
debug.Progress(100,"Logging EMAIL_SENT");
debug.AddEntry("Batch history update for EMAIL_SENT successfully for " + emailSent.Count.ToString());
if (emailSent.Count>0){
OrderData.AddOrderLifeHistoryBatch(ActiveConnection,emailSent.ToArray(),"EMAIL_SENT","Order 3 Days Late Email sent","","LATE3DAYS","Macro Script");
}
debug.AddEntry("Batch history update for EMAIL_SENT unsuccessful for " + invalidEmail.Count.ToString());
if (invalidEmail.Count>0){
OrderData.AddOrderLifeHistoryBatch(ActiveConnection,invalidEmail.ToArray(),"EMAIL_SENT","Order 3 Days Late: Email is invalid","","LATE3DAYS","Macro Script");
}
Import Stock Levels from supplier FTP
This sample code will download an inventory file from FTP and update stock levels. It checks the modified date of the last downloaded version with the modified date of the current version on the Ftp. If Ftp version is newer then download and update will be actioned
Select Settings | Macros | Add New
Use the sample code below and update the settings to suit your FTP
Give the script a name eg DownloadInventoryLevelsFromFtp
Click Save
Click Save again
The macro will run on Sync, but only download when required.
namespace linnworks.finaware.CommonData // leave untouched
{ // leave untouched
public class ScriptMacroClass : linnworks.scripting.core.IMacroScript // leave untouched
{
public void Initialize(linnworks.scripting.core.Debugger debug, System.Data.SqlClient.SqlConnection ActiveConnection) // leave untouched
{ // leave untouched
// this macro will download an inventory file from FTP and update stock levels
// it checks the modified date of the last downloaded version with the modified date of the current version on the Ftp
// if Ftp version is newer then download and update will be actioned
// there are various settings that need to be set to make this macro work
// Each of these has the following Wrapper to make them easier to find
//******* User Settings Required ~ Start
//******* User Settings Required ~ Finish
// create a query to retrieve the time stamp based on the last time we imported
// check first to see if the setting exists and if not add it
string query = @"IF NOT EXISTS (SELECT top 1 1 FROM AppSettings WHERE settingCategory = 'TIMESTAMP' and settingname = 'INVENTORY_FILE_CREATED_TIME')
BEGIN
INSERT INTO AppSettings(settingCategory, settingname, settingValue)
VALUES ('TIMESTAMP','INVENTORY_FILE_CREATED_TIME',GETDATE()-30)
END
SELECT settingValue
FROM AppSettings
WHERE settingCategory = 'TIMESTAMP' and settingname = 'INVENTORY_FILE_CREATED_TIME'";
// use active connection to the Linnworks database
using (ActiveConnection)
{
// run the query
debug.AddEntry("Run query to get date");
SqlCommand cmdGetDate = new SqlCommand(query, ActiveConnection);
object objLastModified = cmdGetDate.ExecuteScalar();
// make sure we have something returned from the query
if (objLastModified != null)
{
debug.AddEntry("Query worked");
debug.AddEntry("Last downloaded file date = " + objLastModified.ToString());
// assign object retrun value to a local datetime variable
DateTime date = Convert.ToDateTime (objLastModified);
//******* User Settings Required ~ Start
// set FTP variables, edit these to suit your FTP server settings
debug.AddEntry("Set Ftp params");
string ftpPath = "*******************"; // enter full path eg ftp://My.Supplier.com/EndOfDayReports/
string ftpUser = "*******************"; // enter ftp User Name
string ftpPassword = "*******************"; // enter ftp password
string ftpFilename = "*******************"; // enter file name eg "inventory.csv";
// Linnworks location,
string sLocation = "Default"; // enter Linnworks Location to suit where Stock Levels need to be updated
//******* User Settings Required ~ Finish
// retrieve the modified date for the file on the ftp
debug.AddEntry("Check modified date");
DateTime modifiedDate = CheckModifiedDateTime(ftpPath, ftpUser, ftpPassword, false, ftpFilename);
debug.AddEntry(" File modified Date = " + modifiedDate);
// compare the date in the database with the modified date
if (modifiedDate > date)
{
debug.AddEntry("File modified, download required");
// create a list to contain the items to update
List items = new List();
// download the file from the Ftp server
debug.AddEntry("Start Download of file " + ftpFilename );
string file = DownloadFileFromFTP(ftpPath, ftpUser, ftpPassword, false, ftpFilename);
debug.AddEntry(" Download Complete" );
// initialise the settings for the format of the flat file
// these must be set to match the format of the file being downloaded
debug.AddEntry("Initialise Flat File settings" );
linnworks.finaware.CommonData.Classes.Generic.FlatFileSettings settings = new linnworks.finaware.CommonData.Classes.Generic.FlatFileSettings();
settings.ColumnHeaders = true;
settings.delimiter = ",";
settings.textseparator = "\"";
settings.UTF8 = false;
// convert the downloaded file to a database table
debug.AddEntry("Start Convert flat file to table");
DataTable tbl = linnworks.finaware.CommonData.FlatFileWorker.FlatFileToTable(settings, file);
debug.AddEntry(" Convert Complete");
// iterate each row of the table and read the SKU/quantity values
debug.AddEntry("Start adding SKU's/Quantities to List");
foreach (DataRow row in tbl.Rows)
{
string sku = row["SKU"].ToString();
int quantity = 0;
try
{
quantity = Convert.ToInt32(row["quantity"].ToString());
}
catch
{
quantity = int.Parse(row["quantity"].ToString());
}
if (sku != "")
{
// if we have sku add it to the list for updating linnworks
items.Add(new linnworks.finaware.CommonData.FulfilmentCenter.BatchStockLevelUpdateItem(sku, quantity));
}
}
debug.AddEntry(" Finish adding SKU's/Quantities");
// find guid for required Location
Guid locationId = linnworks.finaware.CommonData.Locations.GetLocationIdByName(ActiveConnection, sLocation);
// now do the actual stock update
debug.AddEntry("Start update");
linnworks.finaware.CommonData.FulfilmentCenter.BatchStockLevelUpdate(ActiveConnection, locationId, items);
debug.AddEntry(" Finish update");
// update the date in App Settings to match the modified date of the import file
debug.AddEntry("Start AppSettings update");
debug.AddEntry("Update TIMESTAMP / INVENTORY_FILE_CREATED_TIME to " + modifiedDate.ToString());
string updateQuery = @" update AppSettings
SET settingValue = '" + modifiedDate.ToString() + @"'
WHERE settingCategory = 'TIMESTAMP' and settingname = 'INVENTORY_FILE_CREATED_TIME'";
SqlCommand cmdUpdateDate = new SqlCommand(updateQuery, ActiveConnection);
cmdUpdateDate.ExecuteNonQuery();
debug.AddEntry(" Finish AppSettings update");
}
else
{
debug.AddEntry("File NOT modified, no download required");
}
}
}
} // leave untouched
// check the modified Date/Time of a file on an Ftp server
static DateTime CheckModifiedDateTime(string FTPPath, string Username, string Password, bool Active, string filename)
{
DateTime ret = DateTime.Now;
try
{
System.Net.FtpWebRequest requestDown = (System.Net.FtpWebRequest)System.Net.WebRequest.Create(FTPPath + "/" + filename);
requestDown.Method = System.Net.WebRequestMethods.Ftp.GetDateTimestamp;
requestDown.Credentials = new System.Net.NetworkCredential(Username, Password);
using (System.Net.FtpWebResponse resp = (System.Net.FtpWebResponse)requestDown.GetResponse())
{
ret = resp.LastModified;
}
}
catch (Exception ex)
{
throw new Exception("CheckModifiedDateTime " + ex.Message);
}
return ret;
}
// download a file from an Ftp server
public static string DownloadFileFromFTP(string FTPPath, string Username, string Password, bool Active, string filename)
{
string step = "";
try
{
step = "Create Download Request " + FTPPath + "/" + filename;
System.Net.FtpWebRequest requestDown = (System.Net.FtpWebRequest)System.Net.WebRequest.Create(FTPPath + "/" + filename);
requestDown.Method = System.Net.WebRequestMethods.Ftp.DownloadFile;
requestDown.Credentials = new System.Net.NetworkCredential(Username, Password);
requestDown.UsePassive = !Active;
step = "GetResponse Download " + FTPPath + "/" + filename;
System.Net.FtpWebResponse responseDownload = (System.Net.FtpWebResponse)requestDown.GetResponse();
step = "Open Download stream " + FTPPath + "/" + filename;
System.IO.Stream responseStream = responseDownload.GetResponseStream();
step = "Read file " + FTPPath + "/" + filename;
System.IO.StreamReader reader = new System.IO.StreamReader(responseStream);
string downloadedFile = reader.ReadToEnd();
try
{
reader.Close();
responseStream.Close();
responseDownload.Close();
}
catch { }
return downloadedFile;
}
catch (Exception ex)
{
throw new Exception("Download file from FTP " + ex.Message);
}
}
} // leave untouched
} // leave untouched
|
