Refund & Exchange & Split Order Script

introduction this script is designed to send emails to clients when they request a refund or exchange it also send emasils to clients whose orders has been split, and will arrrive at different times it first makes a sql query to linnworks to fetch data about the requested refund or exchange once it has the necessary data, the script sends an email to the client using a professional email template the script is written in python and uses the smtplib library to send emails installation clone the repository and install the dependencies git clone https //github com/e mps co uk/refund mail cd refund mail python3 m venv venv source venv/bin/activate pip install r requirements txt create a env file and add the get the keys from the linnworks developer account https //developer linnworks com/ applicatonid=xxxxxxxx xxxx xxxx xxxx xxxxxxxxxxxx applicationsecret=xxxxxxxx xxxx xxxx xxxx xxxxxxxxxxxx token=xxxxxxxx xxxx xxxx xxxx xxxxxxxxxxxx running the script python3 main py code structure script structure ├── license ├── readme md ├── refund │ ├── init py │ ├── config py │ ├── logger py │ ├── refund script py │ ├── sql scripts │ │ ├── init py │ │ ├── exchange script py │ │ ├── refund script py │ │ └── split script py │ ├── templates │ │ ├── email template stock html │ │ ├── email template exchange html │ │ ├── email template exchange international html │ │ ├── email template oos html │ │ ├── email template refund html │ │ └── email template split order html │ └── utils py ├── config ini ├── main py ├── pyproject toml ├── requirements txt ├── tests │ ├── init py │ ├── test refund script py │ └── test utils py ├── towerlondonrefunds db └── tox ini code overview database the database is created automatically when you run the code, if the database doesn’t already exist it is used to store order data and retrieve processed order data on demand the database used in this script is sqlite conn = sqlite3 connect('towerlondonrefunds db') cursor = conn cursor() cursor execute(return table query) conn commit() the return table query also creates the table if does not already exist create table if not exists refunds( id integer primary key autoincrement, customername text) refund script module the linnapi class is designed to interact with the linnworks api https //apidocs linnworks net/reference for fetching and processing split, exchange and refund orders data token management this method is responsible for obtaining a new authentication token to interact with the linnworks api it ensures that a valid token is always available def get new token(self) > none response = requests post(self token url,params=self refresh data, timeout=30) if response status code != 200 raise httperror("failed to get new token status code " f"{response status code}") response data = response json() new access token = response data\['token'] self headers\["authorization"] = new access token the method is used to fetch the access token and add it to the headers if the token has expired and has not been refreshed, it is fetched again using the get data from api method data retrieval this method fetches data from the linnworks api using a post request and returns the json response it also handles token refresh if the current token is invalid it mostly acts as a helper function to fetch data def get data from api(self, api url str, script str, headers dict\[str, str]) > dict\[str, any] r = requests post(api url, data={"script" script}, headers=headers, timeout=60) if r status code != 200 self get new token() r = requests post(api url, data={"script" script}, headers=headers, timeout=60) response dict\[str, any] = r json() return response insert data into db this method inserts data into an sqlite database using the provided cursor it is used to store refund and exchange data for more info on the sqlite python documentation visit this link https //docs python org/3/library/sqlite3 html process data the process data function is a part of a data pipeline that extracts, transforms, and loads (etl) data into a sqlite database extracts data it uses a custom script query (data query) to retrieve data from an external source via an api the api url is constructed using the base url (self apiurl) and the endpoint (‘/dashboards/executecustomscriptquery’) transforms data the retrieved data is transformed into a pandas dataframe for easier manipulation it then iterates over each row of the dataframe, checking if the record already exists in the database if not, it inserts the new record into the database loads data the transformed data is loaded into a sqlite database (towerlondonrefunds db) the function creates a table in the database using the provided sql query (table query), and inserts data into the specified table (table name) email notifications after loading the data into the database, it retrieves all unprocessed records (where processed = 0) from the table and triggers email notifications for each record using the provided email function (email func) the code snippet shows how the function works data = self get data from api(api url, script, self headers) df = pd dataframe(data\['results']) for index, row in df iterrows() cursor execute( f"select from {table name} where orderid=?", (row\['orderid'],)) if cursor fetchone() is none values = \[row\[col] for col in columns] self insert data into db(cursor, table name, columns, values) main log info('%s orders inserted for table %s', len(df), table name) conn commit() existing data = pd read sql( f"select from {table name} where processed = 0", conn) main log info('orders to be processed for table name %s are %s', table name, len(existing data)) for index, row in existing data iterrows() email func(row\ to dict(), cursor, conn) conn close() refund data , exchange data , and split order each of these methods processes a specific type of order (refunds, exchanges, and split orders respectively) by calling the process data function with parameters specific to each order type these parameters include the sql queries for creating the table and retrieving data, the table name, column names, and a function for sending emails specific to the order type (send refund mail, send exchange mail, or send split mail) utils module this python module provides a set of helper methods for sending emails it is designed to streamline the process of sending various types of emails, including refund notifications and order exchange notifications send email template this function sends an email using a provided template file and dynamic data it handles the process of replacing placeholders in the template with the dynamic values and sending the email parameters are template file (str) path to the template file name (str) recipient's name email (str) recipient's email address subject (str) email subject bcc (str) bcc email address data (dict) dictionary containing dynamic data for the template caller function calling the send email template function the code snippet shoes how data is filled to the template file, and then an email is sent to the client \# read in the template file with open(template file, "r", encoding='utf 8') as f template = f read() \# replace placeholders with dynamic values for key, value in data items() template = template replace(f"{{{key}}}", str(value)) \# create the email message msg = mimemultipart() msg\["from"] = config client email msg\["to"] = email msg\["subject"] = subject msg\["bcc"] = bcc msg attach(mimetext(template, 'html')) \# send the email server = smtplib smtp(config client host, int(config client port)) server starttls() server login(config client email, config client password) server send message(msg) main log info("sending %s email to %s", subject, 'iandan') server quit() create email data this function is designed to prepare the data needed for sending an email it takes a dictionary of row data (row dict) and a template file path (template file) as inputs update order status this function is used update the orderid status in the database after an e mail has been sent processed is changed from 0 to 1 prepare email this function is a utility function used to prepare and send an email using data from a sqlite database and a specified template file here’s a brief description of its functionality and parameters functionality the function first calls the create email data function to prepare the email data using the provided row data and template file it then sends the email using the send email function, and updates the order status in the database using the update order status function if any error occurs during this process, it logs the error using main log error code snippet template file, data = create email data(row dict, template file) send email(template file, data\['full name'], data\['email address'], subject, f'{config bcc email1},{config bcc email2},{config bcc email3}', data, function name) update order status(cursor, conn, table name, row dict\['orderid']) send refund email the function is used to send an email notification based on a refund or out of stock reason send exchange mail the function is used to send an email notification for order exchange send split mail the function is used to send an email notification for an order that has been split they are different but serve the same purpose example of the send exchange mail if row dict\['country'] == 'united kingdom' template file = config template exchange else template file = config template exchange international prepare email(row dict, cursor, conn, template file, "order exchange notification", "exchanges", "send exchange mail") config module the configuration module serves as a centralized location for various essential parameters and credentials it includes file paths paths to files that the application needs to access api keys keys required to authenticate with various apis email credentials credentials needed to send emails from the application logfile paths locations of log files for application debugging and record keeping function configurations settings and parameters for various functions within the application templates the template files are stored in the templates folder the template files are in html format and contain placeholders for dynamic values the placeholders are replaced with dynamic values before the email is sent the template files are passed to the send email template function as parameters the function then replaces the placeholders with the dynamic values and sends the email to the client tests the test suite in this script has been written with pytest to run the tests run pytest tests/ the schedule process this script run as a pm2 process on the server the process is scheduled to run as specified below i use the schedule library to schedule the process def main() > none linn = linnapi() parser = argparse argumentparser(description='run scheduled tasks ') parser add argument(' getrefund data', type=int, default=40, help='interval (in minutes) for running the' 'refund function') parser add argument(' getexchange data', type=int, default=50, help='interval (in minutes) for running the' 'exchange data function') args = parser parse args() schedule every(args getrefund data) minutes do(linn return data) schedule every(args getexchange data) minutes do(linn exchange data) while true schedule run pending() time sleep(1) if name == " main " main() this is a general overview of the refund, exchange & split order mail with the information provided you will be able to run the script seamlessly