DEV Community: Morten Hartvig

Creating WordPress plugin options based on a JSON structure

Morten Hartvig — Mon, 09 Dec 2024 15:34:59 +0000

The other day I was wondering how I could let WordPress plugin options be controlled by a JSON file, allowing one to more easily add additional settings in the future without having to adjust the code itself.

This article provides an example of an extremely simple WordPress plugin with a single settings pages consisting of 2 sections and 3 fields / options.

The full code can be found on Github.

Setting up the base

The plugin initially consists of 3 files.

adventures.json
adventures.php
class.adventures.php

adventures.php with a basic plugin registration:

<?php
/*
Plugin Name: Adventures
Plugin URI: https://mortenhartvig.dk
Description: Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed pharetra nisi eu varius pellentesque. Aenean posuere, velit mollis sodales convallis, ipsum lectus feugiat nunc, ac auctor sapien enim eu metus.
Version: 1
Requires at least: 6.1
Requires PHP: 8.3
Author: Morten Hartvig
Author URI: https://mortenhartvig.dk
License: Do whatever you want
*/

define('ADV__PLUGIN_DIR', plugin_dir_path(__FILE__));
define('ADV__PLUGIN_VIEW', ADV__PLUGIN_DIR . 'views');
define('ADV__PLUGIN_SLUG', 'adv');

require_once ADV__PLUGIN_DIR . 'class.adventures.php';

(new Adventures());

class.adventures.php with an empty class:

<?php

class Adventures {
    public function __construct() {

    }
}

adventures.json contains the JSON structure of the plugin settings:

{
    "settings": {
        "pages": [
            {
                "title": "Adventures",
                "capability": "manage_options",
                "slug": "adv"
            }
        ],
        "sections": [
            {
                "id": "portal_base",
                "title": "Base configuration",
                "description": "Lorem 1, ipsum dolor sit amet consectetur adipisicing elit. Cumque nulla in officiis. Laborum quisquam illo eaque, deserunt facere mollitia sint doloremque maiores, obcaecati reiciendis voluptate itaque iure fugiat quia architecto!",
                "view": "section"
            },
            {
                "id": "portal_appearance",
                "title": "Appearance",
                "description": "Lorem 2, ipsum dolor sit amet consectetur adipisicing elit. Cumque nulla in officiis. Laborum quisquam illo eaque, deserunt facere mollitia sint doloremque maiores, obcaecati reiciendis voluptate itaque iure fugiat quia architecto!",
                "view": "section"
            }
        ],
        "fields": [
            {
                "id": "adv_portal_key",
                "title": "Portal Key",
                "section": "portal_base",
                "type": "text",
                "placeholder": "Enter your portal key",
                "view": "field.text"
            },
            {
                "id": "adv_api_host",
                "title": "Host API",
                "section": "portal_base",
                "type": "text",
                "placeholder": "Enter API host",
                "default": "https://api.mortenhartvig.dk",
                "view": "field.text"
            },
            {
                "id": "adv_portal_theme",
                "title": "Theme",
                "section": "portal_appearance",
                "type": "select",
                "options": {
                    "rounded.v1": "Round (V1)",
                    "squared.v1": "Square (V1)",
                    "standard": "Standard"
                },
                "default": "standard",
                "view": "field.select"
            }
        ]
    }
}

Reading JSON data

Create a property for your settings and call set_settings() in ´class.adventures.php´:

private $settings;

public function __construct() {
    $this->set_settings();
}

Create set_settings() and get_json_data():


private function set_settings() {
    $data = $this->get_json_data();

    $this->settings = $data['settings'];
}

private function get_json_data() {
    $file = ADV__PLUGIN_DIR . 'adventures.json';

    if (!file_exists($file)) {
        die('adventures.json not found');
    }

    return json_decode(file_get_contents($file), true);
}

If you add a die(print_r($this->settings)) to your __construct you can confirm that you indeed have your settings loaded.

Settings page

Call init_hooks() from the constructor:

public function __construct() {
    $this->set_settings();
    $this->init_hooks();
}

Create init_hooks():

private function init_hooks() {
    add_action('admin_menu', [$this, 'register_settings_pages']);
}

Create register_settings_pages() and settings_page_callback().

public function register_settings_pages() {
    foreach ($this->settings['pages'] as $page) {
        add_options_page($page['title'], $page['title'], $page['capability'], $page['slug'], [$this, 'settings_page_callback']);    
    }
}

public function settings_page_callback() {
    $this->render('settings.php');
}

Create render():

private function render($filename, $args) {
    if (is_array($args)) {
        $value = get_option($args['id']);

        if (empty($value) && isset($args['default'])) {
            $value = $args['default'];
        }

        $args = array_merge($args, ['value' => $value]);
    }

    $file = ADV__PLUGIN_VIEW . $filename;

    if (!str_ends_with($file,'.php')) {
        $file .= '.php';
    }

    if(!file_exists($file)) {
        die('File not found ' . $filename);
    }

    require $file;
}

Create views/settings.php:

<div class="wrap">
    <h1>Adventures</h1>
</div>

Sections

Add another action in init_hooks:

private function init_hooks() {
    add_action('admin_menu', [$this, 'register_settings_pages']);
    add_action('admin_init', [$this, 'register_settings_sections']);
}

Create register_settings_sections() and settings_section_callback():

public function register_settings_sections() {
    foreach ($this->settings['sections'] as $section) {
        add_settings_section($section['id'], $section['title'], [$this, 'settings_section_callback'], ADV__PLUGIN_SLUG, $section);
    }
}

public function settings_section_callback($args) {
    $this->render($args['view'], $args);
}

Create views/section.php:

<p>
    <?php echo $args['description']; ?>
</p>

Fields

Add a third action in init_hooks:

private function init_hooks() {
    add_action('admin_menu', [$this, 'register_settings_pages']);
    add_action('admin_init', [$this, 'register_settings_sections']);
    add_action('admin_init', [$this, 'register_settings_fields']);
}

Create register_settings_fields() and settings_field_callback():

public function register_settings_fields() {
    foreach ($this->settings['fields'] as $field) {
        add_settings_field($field['id'], $field['title'], [$this, 'settings_field_callback'], ADV__PLUGIN_SLUG, $field['section'], $field);

        register_setting(ADV__PLUGIN_SLUG, $field['id']);
    }
}

public function settings_field_callback($args) {
    $this->render($args['view'], $args);
}

Create views/field.select.php:

<?php

$html_options = '';

foreach ($args['options'] as $key => $val) {
    $html_options .= sprintf('<option value="%s" %s >%s</option>', $key, selected($args['value'], $key, false), $val);
}

printf('<select name="%s" id="%s">%s</select>', $args['id'], $args['id'], $html_options);

Create views/field.text.php:

<?php

printf('<input name="%s" id="%s" type="text" value="%s" placeholder="%s" />', 
$args['id'], $args['id'], $args['value'], $args['placeholder']);

Saving

To view and save your options add a form to settings.php:

<div class="wrap">
    <h1>Adventures</h1>

    <form action="options.php" method="post">
        <?php 
        settings_fields(ADV__PLUGIN_SLUG);
        do_settings_sections(ADV__PLUGIN_SLUG);
        submit_button();
        ?>
    </form>
</div>

Change an option and attempt to Save. The save should be successful.

Your settings are now saved and can be accessed throughout the site via:

<?php
echo get_option('adv_portal_theme'); // squared.v1

Adding new fields

Add the JSON below to adventures.json:

{
    "id": "adv_api_token",
    "title": "Host API Token",
    "section": "portal_base",
    "type": "text",
    "placeholder": "Enter API host token",
    "default": "",
    "view": "field.text"
}

It will automatically be added to your settings:

Local environment for Shopify theme development

Morten Hartvig — Mon, 21 Oct 2024 10:19:28 +0000

Making code changes directly on your published theme in production is seldom a good idea as it introduces the risk of downtime in case of errors and typos. Luckily, using the Shopify CLI, getting a local instance of your theme up and running has never been easier.

Shopify CLI is a command-line tool that allows you to work with- and generate themes, apps and storefronts using a simple command-line interface.

Depending on the project and its complexity you might have various CI/CD solutions in place. This article will not cover those kinds of setup - merely the CLI to help you get started with local development.

Prerequisites

Cf. Shopify's own documentation, in order to use the CLI you will need to have installed:

Node 18.20+, 20.20 or higher
npm (although Yarn and pnpm is also supported)
Git

Setting up a local environment

Firstly install Shopfy CLI globally using your package manager of choice (in this case npm).

npm install -g @shopify/cli @shopify/theme

Create a development folder for your theme.

Pull your theme using the command below.

shopify theme pull --store=your-store-name.myshopify.com

Note that if this is the first time using the CLI for this particular store you will be promoted to authenticate.

Start developing by running the command:

shopify theme dev

... which will start up a local instance:

Shopify will provide you with three different URLs:

[1] View your theme locally
[2] Make changes to your development theme in the Theme Editor
[3] Share a preview to your development theme

Especially [3] can be useful if you want to live demo a local, pending change to your client.

If you visit 127.0.0.1:9292 you will be able to see a 1:1 copy of your published, production theme:

Publishing a change

Now that you have a copy of your theme running locally open the project folder created earlier in your editor of choice, such as Visual Studio Code.

As a humble example, try and add the current year next to your stores logo:

127.0.0.1:9292 will automatically refresh and display the change.

To implement the change in production simply run:

shopify theme push

Visit your production environment and you will see the change.

Having your theme locally, you can also push it to your favourite Git platform allowing you to - among other things - create pull requests for your colleagues to review before deployment to production.

Oh.. and did you know that Shopify has its own Github integration?

Routing in Umbraco Part 2: Content Finders

Morten Hartvig — Mon, 03 Jun 2024 17:39:42 +0000

The previous article in this series demoed how to adjust segments for your Umbraco nodes using an IUrlSegmentProvider. When an altered segment is returned in an IUrlSegmentProvider it becomes a native segment that Umbraco can handle out of the box and no further URL manipulation/rewrite is required.

But.. what if you wanted to change the path? Say, for example, you have a blog and you want to automatically have the year of the blog post's creation inserted in the URL, such as /blog/hello-world should become /blog/2024/hello-world. This is where an IContentFinder comes in.

When you alter part of a node's path and not simply the segment, Umbraco will need some help mapping the inbound request to the node, as the new path assigned to the node is not natively known by Umbraco.

This article will show how to provide a new path for a node and how to return that node for the inbound request.

Changing the path

Much like an IUrlSegmentProvider Umbraco has an IUrlProvider that can be implemented for change the URL for your nodes.

The interface has two methods: GetUrl() and GetOtherUrls(). The latter will let your return other URLs to display in the Backoffice on the node's Info Content App.

Below is an example for GetUrl().

public class BlogPostUrlProvider : IUrlProvider
{
    public UrlInfo? GetUrl(IPublishedContent content, UrlMode mode, string? culture, Uri current)
    {

    }

    public IEnumerable<UrlInfo> GetOtherUrls(int id, Uri current)
    {

    }
}

However, instead of implementing IUrlProvider, Umbraco ships with a DefaultUrlProvider with a virtual method for both GetOtherUrls() and GetUrl() that can be overwritten.

public class BlogPostUrlProvider : DefaultUrlProvider
{
    public BlogPostUrlProvider(IOptionsMonitor<RequestHandlerSettings> requestSettings, 
        ILogger<DefaultUrlProvider> logger, 
        ISiteDomainMapper siteDomainMapper, 
        IUmbracoContextAccessor umbracoContextAccessor, 
        UriUtility uriUtility, 
        ILocalizationService localizationService) : base(requestSettings, logger, siteDomainMapper, umbracoContextAccessor, uriUtility, localizationService)
    { }
}

Note that ILocalizationService is removed in V15. Unfortunately DefaultUrlProvider does not yet have a constructor that accepts ILanguageService. If you want to skip changing the injected interface as part of a V15 upgrade, consider doing a full IUrlProvider implementation. Inspiration can be found on Github.

Change the blog post's UrlInfo in GetUrl() like so:

public override UrlInfo? GetUrl(IPublishedContent content, UrlMode mode, string? culture, Uri current)
{
    if (content.ContentType.Alias != "blogPost") return null;

    var segment = content.UrlSegment;
    if (segment is null) return null;

    var currentUrlInfo = base.GetUrl(content, mode, culture, current);
    if (currentUrlInfo is null) return null;

    // To add the post's created year one could 
    // simply replace the post's segment in the 
    // URL with year/segment.
    var currentUrl = currentUrlInfo.Text;
    var customUrl = currentUrl.Replace(segment,
        $"{content.CreateDate.Year}/{segment}");

    return new UrlInfo(customUrl, true, culture);
}

public class BlogPostComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.UrlProviders().Insert<BlogPostUrlProvider>();
    }
}

Alternatively, if your solution has multiple providers, the provider could be inserted before a specific provider:

builder.UrlProviders().InsertBefore<DefaultUrlProvider, BlogPostUrlProvider>();

The following methods are available for registering your provider. The same goes for ContentFinders():

builder.UrlProviders().Append<T>();
builder.UrlProviders().Insert<T>(int index = 0);
builder.UrlProviders().Remove<T>();
builder.UrlProviders().InsertAfter<TAfter,T>();
builder.UrlProviders().InsertBefore<TBefore, T>();

If you attempt to view the URL in the Backoffice you will notice that it is not working. This is because we have yet to change the inbound request.

Handling the inbound request

Umbraco will go through all registered IContentFinders until one of them returns true, after which no further IContentFinders are executed.

The goal of an IContentFinder is therefore to set an IPublishedContent for the request and return true, so Umbraco can load the node and stop executing the rest of the IContentFinders.

Create a BlogPostContentFinder that implements IContentFinder and inject IUmbracoContextAccessor.

public class BlogPostContentFinder : IContentFinder
{
    private readonly IUmbracoContextAccessor _contextAccessor;

    public BlogPostContentFinder(IUmbracoContextAccessor contextAccessor)
    {
        _contextAccessor = contextAccessor;
    }

    public Task<bool> TryFindContent(IPublishedRequestBuilder request)
    { 

    }
}

In TryFindContent() attempt to find a content node by (in this case) the route. Provided there is a match, set it as the published content and return true.

public Task<bool> TryFindContent(IPublishedRequestBuilder request)
{ 
    // Get the path for the current request
    var path = request.Uri.GetAbsolutePathDecoded();

    // In this example site there will not be other URLs using four digits
    // between two slashes, so they can easily be removed safely like so.
    // E.g. /blog/2024/hello-world -> /blog/hello-world
    var url = Regex.Replace(path, "/[0-9]+/", "/");

    if (!_contextAccessor.TryGetUmbracoContext(out var context)) return Task.FromResult(false);

    // Attempt to find the content
    var content = context.Content?.GetByRoute(url);
    if (content is null) return Task.FromResult(false);

    // Set it as the published content for the request
    request.SetPublishedContent(content);

    // Return true to let Umbraco know a match was found
    return Task.FromResult(true);
}

public class BlogPostComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.UrlProviders().Insert<BlogPostUrlProvider>();
        builder.ContentFinders().Append<BlogPostContentFinder>();
    }
}

Build your solution and publish a blog post and everything should work:

Routing in Umbraco Part 1: URL segments

Morten Hartvig — Thu, 30 May 2024 17:19:44 +0000

If you have ever paid attention to the URL structure of nodes in Umbraco while going hard at your various Umbraco endeavours, you might have noticed that URLs are generated based on a node's placement in the content tree followed by its name. Usually this poses no issue but what if you wanted to change how the URL is made?

Say, for example, that you have a blog and you would like the create date (year) of each post shown in the URL. How would you do this?

You could use one of the built-in property type aliases such as umbracoUrlName, as Umbraco will use that property instead of the node's name for the URL segment:

Textstring property with umbracoUrlName as the alias:

New URL:

However, who wants to do this for each blog posts?

Luckily Umbraco has some tricks up its sleeve and allows you to easily change how URLs behave by using your own URL providers.

Creating your own URL segment provider

Create a class that implements IUrlSegmentProvider. All the magic will take place in GetUrlSegment():

using Umbraco.Cms.Core.Models;
using Umbraco.Cms.Core.Strings;

namespace Adventures;

public class BlogPostUrlSegmentProvider : IUrlSegmentProvider
{
    private readonly IUrlSegmentProvider _segmentProvider;

    public BlogPostUrlSegmentProvider(IShortStringHelper shortStringHelper)
    {
        _segmentProvider = new DefaultUrlSegmentProvider(shortStringHelper);
    }

    public string? GetUrlSegment(IContentBase content, string? culture = null)
    {
        // Do code
    }
}

By using _segmentProvider.GetUrlSegment(content, culture) we can get the current URL segment for the given node and its culture.

In order to prepend the year of the blog post to the URL, all you have to do is make sure content.CreateDate.Year is part the string that is returned.

CreateDate is used for simplicity's sake. Typically you would want a separate DateTime property to assign a publish date. Otherwise, a post that you started in December of a given year but is not published until January will have the previous year in the URL - and you might not want that :-)

public string? GetUrlSegment(IContentBase content, string? culture = null)
{
    if (content.ContentType.Alias != "blogPost")
        return null;

    var segment = _segmentProvider.GetUrlSegment(content, culture);

    return $"{content.CreateDate.Year}-{segment}";
}

With the segment provider in place you can now register it:

using Umbraco.Cms.Core.Composing;

namespace Adventures;

public class BlogPostUrlComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.UrlSegmentProviders()
            .Insert<BlogPostUrlSegmentProvider>();
    }
}

If you have previously worked with custom URLs you might have experience with your own Content Finders. These are not required when only the segment is changed.

Build your solution and republish a relevant node in Umbraco and you will have an updated URL:

oh.. and if the node has previously been published, Umbraco will automatically create a redirect. Neat :-)

Custom components in CompuSoft's Composer

Morten Hartvig — Wed, 29 May 2024 10:43:15 +0000

Composer is CompuSoft's product for easily creating emails related to bookings, invoices and whatever else you might have used and/or is currently using emails for.

Composer is included in your subscription and can be access in CompuSuite by going to Settings -> Document Templates -> Composer.

The article will assume you have some knowledge of Composer or similar email builders.

Components to the rescue

Once you get started (and hooked) on using the Composer you will quickly find that some items are often repeated in your emails, such various branding visuals, buttons and text.

To make it easier for you to reuse items the Composer allows you to create custom components. Components are essentially just "blocks" that can be dragged and dropped into your other emails*, thus giving you the option for better reusability.

* The template/markup for your email in Composer is often referred to as a "form".

Creating components for better reusability

The example below will use a Branding header as an example component. When inserted into Composer forms it will display a company logo along with the customer's name (the customer being the individual eventually receiving an email using this form).

1 Go to Composer in CompuSuite and click + Create new

2 You will have the option to choose from a template. Templates will be covered in a later post. For now simply click on Empty document

3 You will now have a new, unsaved Composer form. Click on Settings in the top left and provide a name for the new component

4 Insert an image and text block by clicking on them and provide whichever information your would like

Notice how the text on the image says {{ "Hej" | translate:6249 }} {{ Customer.Name }}. Composer supports a template language called Liquid, which will be covered in depth in another post.

{{ "Hej" | translate:6249 }} will insert the translation item with the given id (6249) from the CompuSuite translator library, making sure the text is always translated correctly for the customer's local language.

{{ Customer.Name }} will insert the name of the customer when the email is sent.

5 Click Save and Close in the bottom right, set the type to Component and click Save

6 Components can be found under Components on the Composer page, including built-in components provided by CompuSoft.

Note that you can only edit your own components.

Using your custom component in an email

With a component created it is time to insert it into an email.

1 Go to the Composer page in CompuSuite and create a new empty form just like in the previous step 1

2 Looking at the components available to the right you will see Branding - Header, which can be inserted by clicking on it

3 You can expand the component to see the contents of it by toggling the arrow

Note that some information (such as {{ Customer.Name }}) will not be visible in the editor as the information is not available at this level.

4 Clicking the three dots followed by Preview will allow you to preview the form

5 On the right-hand side you can select a customer to preview the form as

Notice how the customer's name is inserted correctly in the component.

Your new component can be inserted into any new Composer email and will make it a lot easier to reuse the (in this case) branding for the header of the email :)

Changing the PublishedContent for the current request in Umbraco

Morten Hartvig — Tue, 28 May 2024 15:44:56 +0000

Using Umbraco's notifications handlers it is possible to override the PublishedContent being loaded for the current request.

The example below assumes you have a maintenance mode you can toggle in you Backoffice. When enabled, every page request to your website should display the maintenance page.

Start by creating a MaintenanceHandler.cs file:

using Umbraco.Cms.Core.Events;
using Umbraco.Cms.Core.Models.PublishedContent;
using Umbraco.Cms.Core.Notifications;

namespace Adventures
{
    public class MaintenanceHandler : INotificationHandler<RoutingRequestNotification>
    {
        public void Handle(RoutingRequestNotification notification)
        {
            var request = notification.RequestBuilder;

            var website = request.PublishedContent?.Root();
            if (website == null) return;

            var isInMaintenanceMode = website.Value<bool>("maintenanceMode");
            if (!isInMaintenanceMode) return;

            var maintinancePage = website.Value<IPublishedContent>("maintenancePage");
            if (maintinancePage != null)
            { 
                // If maintinance is enabled and the page exists 
                // we set the content for the request and change the template
                request.SetPublishedContent(maintinancePage);
                request.TrySetTemplate(maintinancePage.GetTemplateAlias());
            }
        }
    }
}

... and then register it in Program.cs:

builder.CreateUmbracoBuilder()
    .AddBackOffice()
    .AddWebsite()
    .AddDeliveryApi()
    .AddComposers()
    .AddNotificationHandler<RoutingRequestNotification, MaintenanceHandler>()
    .Build();

Visiting the website before toggling maintenance mode:

Visiting the website after toggling maintenance mode:

Disable preview in Umbraco

Morten Hartvig — Sun, 06 Aug 2023 16:37:28 +0000

When creating Umbraco projects you often end up making document types for content such as office locations, categories or employees where the content type does not have a template and/or is unable to be previewed directly.

Some clients might request to be redirected to the home page (or some related page) while others might request that the preview button is removed to avoid confusion. Below is an example of doing the latter.

Create a SendingContentNotificationHandler.cs to listen for content notifications and disable the preview for nodes without templates:

using Umbraco.Cms.Core.Events;
using Umbraco.Cms.Core.Notifications;

namespace Adventures
{
    public class SendingContentNotificationHandler : INotificationHandler<SendingContentNotification>
    {
        public SendingContentNotificationHandler() { }

        public void Handle(SendingContentNotification notification)
        {
            if (notification.Content.AllowedTemplates?.Count == 0)
            {
                notification.Content.AllowPreview = false;
            }
        }
    }
}

public void ConfigureServices(IServiceCollection services)
{
    services.AddUmbraco(_env, _config)
        .AddBackOffice()
        .AddWebsite()
        .AddComposers()
        .AddNotificationHandler<SendingContentNotification, SendingContentNotificationHandler>()
        .Build();
}

Displaying Umbraco Heartcore data in Excel using Office Script and GraphQL

Morten Hartvig — Mon, 12 Jun 2023 19:23:31 +0000

Recently I noticed that Office Script is a thing. It enhances what you could previously do using VBA by making your enhancements Cloud-based and cross-platform. If you have ever used Google's Apps Script you will find it quite similar.

When I discovered Apps Script a few years ago I tested it by inserting Umbraco Heartcore data using the Content Delivery API.. this time around I wanted to test the GraphQL capabilities of Umbraco Heartcore instead of doing a copy-paste from my Google Drive.

Please note that access to Office Script requires Office 365 Business or higher https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel#requirements.

An Office Script can be created by going to Automate in the ribbon and clicking New Script, which will open a code editor in your Excel window.

In this case the goal is to display products from an Umbraco Heartcore project where the stock property on the Product nodes is <= 3. As such, the first step is to add the necessary headers for the table.

Add the following to the main function.

  let sheet = workbook.getActiveWorksheet();

  let headers = [['SKU', 'Name', 'Price', 'Stock']];
  let headersRange = sheet.getRange('B2:E2');
  headersRange.setValues(headers);
  headersRange.getFormat().getFill().setColor('#4472C4');
  headersRange.getFormat().getFont().setColor('white');

Executing the code above will render the cells with the given header texts and colours.

The next step is to create the interfaces needed for parsing the Umbraco Heartcore response.

interface Response {
  data: Data
}

interface Data {
  allProduct: Connection
}

interface Connection {
  edges: Edge[]
}

interface Edge {
  node: Node
}

interface Node {
  sku: string,
  name: string,
  price: number,
  stock: number,
}

Create a function for getting the product data.

async function getProductData() {
  const query = `
  {
    allProduct(
      where: {
        stock_lte: 3,
      },
      orderBy: name_ASC
    ) {
      edges {
        node {
          sku
          name
          price
          stock
        }
      }
    }
  }
  `;

  let result = await fetch('https://graphql.umbraco.io', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'Umb-Project-Alias': 'umbraco-heartcore-alias-here'
    },
    body: JSON.stringify({
      query: query
    })
  });

  let data = [] = [];

  let response: Response = await result.json();
  response.data.allProduct.edges.forEach(e => {
    let node = e.node;

    data.push([
      node.sku,
      node.name,
      node.price,
      node.stock
    ])
  });

  return data;
}

Make the main function async and fetch the products.

async function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();

  let headers = [['SKU', 'Name', 'Price', 'Stock']];
  let headersRange = sheet.getRange('B2:E2');
  headersRange.setValues(headers);
  headersRange.getFormat().getFill().setColor('#4472C4');
  headersRange.getFormat().getFont().setColor('white');

  let data = await getProductData();
  let dataRangeLength = data.length + 2;

  sheet.getRange('B3:E' + dataRangeLength).setValues(data);

  let stockRange = sheet.getRange('E3:E' + dataRangeLength);
  let stockValues = stockRange.getValues() as number[][];

  for (let i = 0; i < stockValues.length; i++) {
    if (stockValues[i][0] == 0) {
      stockRange.getCell(i, 0).getFormat().getFill().setColor('red');
      }
    }
}

If a product's stuck is zero the cell is marked with red. Microsoft recommends limiting requests to the worksheet in loops (sounds like a good idea), which is why all the values are fetched prior to the loop, significantly reducing the amount of requests.

Complete example.

async function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();

  let headers = [['SKU', 'Name', 'Price', 'Stock']];
  let headersRange = sheet.getRange('B2:E2');
  headersRange.setValues(headers);
  headersRange.getFormat().getFill().setColor('#4472C4');
  headersRange.getFormat().getFont().setColor('white');

  let data = await getProductData();
  let dataRangeLength = data.length + 2;

  sheet.getRange('B3:E' + dataRangeLength).setValues(data);

  let stockRange = sheet.getRange('E3:E' + dataRangeLength);
  let stockValues = stockRange.getValues() as number[][];

  for (let i = 0; i < stockValues.length; i++) {
    if (stockValues[i][0] == 0) {
      stockRange.getCell(i, 0).getFormat().getFill().setColor('red');
      }
    }
}

async function getProductData() {
  const query = `
  {
    allProduct(
      where: {
        stock_lte: 3,
      },
      orderBy: name_ASC
    ) {
      edges {
        node {
          sku
          name
          price
          stock
        }
      }
    }
  }
  `;

  let result = await fetch('https://graphql.umbraco.io', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'Umb-Project-Alias': 'umbraco-heartcore-alias-here'
    },
    body: JSON.stringify({
      query: query
    })
  });

  let data = [] = [];

  let response: Response = await result.json();
  response.data.allProduct.edges.forEach(e => {
    let node = e.node;

    data.push([
      node.sku,
      node.name,
      node.price,
      node.stock
    ])
  });

  return data;
}


interface Response {
  data: Data
}

interface Data {
  allProduct: Connection
}

interface Connection {
  edges: Edge[]
}

interface Edge {
  node: Node
}

interface Node {
  sku: string,
  name: string,
  price: number,
  stock: number,
}

Creating database tables in Umbraco

Morten Hartvig — Mon, 21 Nov 2022 07:06:19 +0000

One of the most beloved things about Umbraco is its flexibility. No matter the challenge you face at work (or at home), there is always a way to solve it using Umbraco's features and APIs.

This includes - but is definitely not limited to - storing and displaying data that is not handled by the CMS and its Backoffice.

In the following post you will see how easy it is to create custom database tables in Umbraco (11).

Creating a model

First step is to create a model to represent your database entity. To keep it simple this post will assume you need a database table for a set of (non-Umbraco) users.

It is also assumed the users are retrieved from some kind of CRM-like system in which they have an id, email, first- and lastname.

[TableName("adventuresUsers")]
[PrimaryKey("Id", AutoIncrement = false)]
public class User
{
    public int Id { get; set; }

    public required string Email { get; set; }

    public required string FirstName { get; set; }

    public required string LastName { get; set; }
}

You can use various attributes on your properties to control how the columns should be created in your database. As an example, you could add [Column("Firstname")] to your FirstName property to indicate the column should be Firstname instead of FirstName in the database, which it would otherwise be called.

Refer to NPoco and Umbraco's database annotations for more info.

Creating a migration

Your migration will inherit Umbraco's MigrationBase. However, seeing as you might have to create additional tables at a later stage, you might consider enhancing this with some useful helper methods.

public abstract class AdventuresMigrationBase : MigtgtrationBase
{
    protected AdventuresMigrationBase(IMigrationContext context) : base(context) { }

    protected void CreateTableIfNotExists<TEntity>()
    {
        if (TableExists(Database.PocoDataFactory.ForType(typeof(TEntity)).TableInfo.TableName) == false)
        {
            Create.Table<TEntity>().Do();
        }
    }
}

With the migration base in place you can create a migration for creating the users table.

public class AddUsersTable : AdventuresMigrationBase
{
    public AddUsersTable(IMigrationContext context) : base(context) { }

    protected override void Migrate()
    {
        CreateTableIfNotExists<User>();
    }
}

Should you need to create other tables in the future for, say, podcasts or jobs, you can reuse your own migration base and easily create tables with CreateTableIfNotExists<Podcast>() and CreateTableIfNotExists<Job>() respectively.

In order to run the migration you need a migration plan in which you will keep track of and control the steps for our migration(s).

Seeing as this is the first migration in the plan, you will be going from an empty source state to a defined target state - in this case AddUsersTable.

// If you plan (no pun intended) to create multiple tables
// you might want more than one plan... and therefore be 
// a bit more explicit in your naming :-)
public class AdventuresMigrationPlan : MigrationPlan
{
    public AdventuresMigrationPlan() : base("AdventuresMigration")
    {
        From(string.Empty)
            .To<AddUsersTable>("AddUsersTable");
    }
}

Next step is to execute the migration plan at startup. This can be done by hooking into the UmbracoApplicationStartingNotification notification.

public class AdventuresMigration : INotificationHandler<UmbracoApplicationStartingNotification>
{
    private readonly ICoreScopeProvider _scopeProvider;
    private readonly IMigrationPlanExecutor _migrationPlanExecutor;
    private readonly IKeyValueService _keyValueService;
    private readonly IRuntimeState _runtimeState;

    public AdventuresMigration(ICoreScopeProvider scopeProvider,
        IMigrationPlanExecutor migrationPlanExecutor,
        IKeyValueService keyValueService,
        IRuntimeState runtimeState)
    {
        _migrationPlanExecutor = migrationPlanExecutor;
        _scopeProvider = scopeProvider;
        _keyValueService = keyValueService;
        _runtimeState = runtimeState;
    }

    public void Handle(UmbracoApplicationStartingNotification notification)
    {
        if (_runtimeState.Level < RuntimeLevel.Run) return;

        var migrationPlan = new AdventuresMigrationPlan();

        var upgrader = new Upgrader(migrationPlan);
        upgrader.Execute(_migrationPlanExecutor, _scopeProvider, _keyValueService);
    }
}

Lastly the migration notifications handler will have to be registered in Startup.cs using the AddNotificationHandler extension.

public void ConfigureServices(IServiceCollection services)
{
    services.AddUmbraco(_env, _config)
        .AddBackOffice()
        .AddWebsite()
        .AddComposers()
   .AddNotificationHandler<UmbracoApplicationStartingNotification, AdventuresMigration>()
        .Build();
}

If you wish to keep your Startup clean, you can add an extension method yourself ...

public static class UmbracoBuilderExtensions
{
    public static IUmbracoBuilder AddMigrations(this IUmbracoBuilder builder)
    {
        builder.AddNotificationHandler<UmbracoApplicationStartingNotification, AdventuresMigration>();

        return builder;
    }
}

... and use that ... :-)

public void ConfigureServices(IServiceCollection services)
{
    services.AddUmbraco(_env, _config)
        .AddBackOffice()
        .AddWebsite()
        .AddComposers()
        .AddMigrations()
        .Build();
}

Note Umbraco recommends using composers for registering your notification handler if you are creating a package.

If you compile and wait for Umbraco to boot, you should now have an adventuresUsers table your the database.

Running the following SQL query will give you an empty result (which is expected).. but the table is there ;-)

select * from adventuresUsers;

Id  Email   FirstName   LastName

The migration state is stored in the umbracoKeyValue table.

select * from umbracoKeyValue where [key] = 'Umbraco.Core.Upgrader.State+AdventuresMigration'

key value   updated
Umbraco.Core.Upgrader.State+AdventuresMigration AddUsersTable 2022-11-18 10:12:52.193

Adjustments

Not five minutes has passed since you deployed the code above and you are told that the users also have an optional nickname. Instead of dropping the table and starting over, you can easily add new migrations and increase the migration state.

Start by adding an optional NickName property to your User model.

[NullSetting]
public string? NickName { get; set; }

Next.. remember that custom migration base you created? You can add another helpful method for ease of use through your project's development cycle when you need string columns.

public abstract class AdventuresMigrationBase : MigrationBase
{
    protected AdventuresMigrationBase(IMigrationContext context) : base(context) { }

    ...

    protected void EnsureStringColumn(string tableName, string columnName)
    {
        if (ColumnExists(tableName, columnName) == false)
        {
            Alter.Table(tableName).AddColumn(columnName).AsString().Do();
        }
    }
}

Create a new migration...

public class AddNickNameColumnToUsersTable : AdventuresMigrationBase
{
    public AddNickNameColumnToUsersTable(IMigrationContext context) : base(context) { }

    protected override void Migrate()
    {
        var tableName = Database.PocoDataFactory.ForType(typeof(User)).TableInfo.TableName;
        var columnName = nameof(User.Nickname);

        EnsureStringColumn(tableName, columnName);
    }
}

... and increment the migration state in your MigrationPlan.

From(string.Empty)
    .To<AddUsersTable>("AddUsersTable")
    .To<AddNickNameColumnToUsersTable>("AddUsersNickName");

Compile and run your SQL query again. It will still show an empty result... but you have a NickName column :-)

select * from adventuresUsers;

Id  Email   FirstName   LastName    NickName

Also, the migration state for AdventuresMigration has been updated to the latest migration.

select * from umbracoKeyValue where [key] = 'Umbraco.Core.Upgrader.State+AdventuresMigration'

key value   updated
Umbraco.Core.Upgrader.State+AdventuresMigration AddUsersNickName 2022-11-18 10:20:52.132

That is it! As you can see, creating tables in Umbraco can be done with ease :-)

This post definitely did not covered everything there is to managing tables so it is highly recommend that you dive into Umbraco's documentation (including the API Docs) - which has only gotten better over the last few years.

Replacing placeholders in Umbraco's dictionary items

Morten Hartvig — Tue, 11 May 2021 07:24:25 +0000

Umbraco's dictionary feature is really cool as it allows editors to control the text you have on buttons, in form labels and other places on your website, removing the need to involve a developer in order to change simple sentences.

Today we ran into a scenario where we needed to make some text that was defined in a dictionary item a bit more dynamic. Instead of completely replacing the dictionary item with text directly in the template, we decided to look into replacing values in the dictionary item. This would allow editors to still control the majority of the text displayed.

An easy fix would be to simply perform some kind of replacement on the string returned by the specific dictionary item in whatever template you have. That is, however, not very scalable. Therefore I looked into further improving the UmbracoHelper's GetDictionaryValue method with two extension methods:

public static string GetDictionaryValueWithReplacement(this UmbracoHelper umbraco, string key, string pattern, string replacement)
{
    return GetDictionaryValueWithReplacement(umbraco, key, string.Empty, pattern, replacement);
}

public static string GetDictionaryValueWithReplacement(this UmbracoHelper umbraco, string key, string altText, string pattern, string replacement)
{
    return Regex.Replace(umbraco.GetDictionaryValue(key, altText), pattern, replacement);
}

This gives us the possibility to easily reuse the functionality anywhere on the website.

Now, given a dictionary item with the key Some.Dictionary.Item and text This message is super personal for you, %name%. Hope you like it., you can use the extension method above in, for example, a template like so:

@Umbraco.GetDictionaryValueWithReplacement("Some.Dictionary.Item", "%name%", Members.GetCurrentMember().Name)

Which will display the following (in my browser, at least):

This message is super personal for you, Morten Hartvig. Hope you like it.

One could of course make additional methods in order to work with more than one placeholder.. you know, something like:

public static string GetDictionaryValueWithReplacement(this UmbracoHelper umbraco, string key, IDictionary<string, string> replacements)
{
    var value = umbraco.GetDictionaryValue(key);

    foreach (var replacement in replacements)
    {
        value = Regex.Replace(value, replacement.Key, replacement.Value);
    }

    return value;
}

... but that is beyond the scope of what we need so I'll let you play around with that.