Website Analytics With Matomo: Prerequisites

Useful WebsitesThis post is part of the where I am taking a look at Matomo which bills itself as a Google Analytics alternative that protects your data and your customers' privacy.

The prerequisites for installing the on-premise version of Matomo are quite straightforward:

  • Matomo can be run on any operating system such as Linux (Ubuntu, RedHat, CentOS, Raspberry Pi OS, etc.), Windows, macOS Server or FreeBSD.
  • A webserver such as Apache, Nginx, IIS, LiteSpeed, etc.
  • PHP version 7.2.5 or greater (the previous major vrsion of Matomo will run on PHP version 5.5.9 through to PHP 7x).
  • MySQL version 5.5 or greater or MariaDB
  • PHP extension pdo and pdo_mysql or the mysqli extension.
  • A mySQL/MariaDB database.
  • A mySQL/MariaDB user with permissions to create/alter tables in the database.

If you're running a high traffic site, there are some resources available covering recommended setup.

Full requirements are available here.

Website Analytics With Matomo: Download Software

Useful WebsitesThis post is part of the where I am taking a look at Matomo which bills itself as a Google Analytics alternative that protects your data and your customers' privacy.

You can download the on-remise version of Matomo by clicking the large green button on the < href='https://matomo.org/matomo-on-premise/'>Matomo On-Premise page:

Matomo software download link

On the next page, click the large green Download Matomo button and save the zip file.

Website Analytics With Matomo: What is Matomo?

Microsoft Dynamics GPThis post is part of the where I am taking a look at Matomo which bills itself as a Google Analytics alternative that protects your data and your customers' privacy.

Matomo says you can "[t]ake back control with Matomo – a powerful web analytics platform that gives you 100% data ownership". It is a GPL licensed web analytics software platform giving you detailed reports on your website(s) and visitors, including search engines and keywords used as wel as their language and pages they visit or files downloaded.

Matomo is open source software which can be used via the Matomo cloud or an "on-premise" installation where you have it installed on your own servers. In this series I will only be looking at the on-premise version of Matomo. Using on-premise Matomo means all of the software is installed on your servers and all of the data is on your database server.

This is a major difference to using Google Analytics, which I did for a short time, where your tracking data is owned and controlled by Google and used for whatever they decide. With Matomo the data is on your server and you decide how it can be used and by who; you can therefore ensure you are fully GDPR and CCPA compliant (I am based in the UK where GDPR applies but my websites are hosted in the US where most of my visitors come from).

There is also the capability to extend with plugins with more than 70 plugins available on the Marketplace; some of the plugins are free, but others are paid premium plugins.

Tracking is enabled on websites by way of a JavaScript tag added to the bottom of each page. Matomo then records activity and aggregates the data to provide detailed web analytics reports about your visitors, page views, referrer information, search engine keywords and so on.

Website Analytics With Matomo: Series Index

Useful WebsitesI've used a few different tools for tracking visits to my sites for a while, moving on from one tool when they made it less useful or when it stopped working. More recently I have started using Matomo which bills itself as a Google Analytics alternative that protects your data and your customers' privacy.

In this series I am going to take a look at what Matomo is and how it is implemented and used. If you're reading this post directly on azurecurve|Rambings of a Dynamics GP Consultant it will automatically update, otherwise you will need to .

ClassicPress Plugin Development: Develop for Accessibility

ClassicPress PluginsThis post is part of the ClassicPress Plugin Development series n which I am going to look at both best practice for developing plugins and how I approach some requirements as well as some of the functions I commonly use.

in the last post, I discussed how plugins should be developed for them to be translated (internationalization); you should also develop for accessibility (often abbreviated as a11y as there are 11 letters between the letters l and n.

The A11y Project website has a lot of good resources which can help get you started with developing for accessibility and as they say, on their about page, "[a]ccessibility is incredibly important, yet often overlooked in traditional digital design and development education...".

Unfortunately, this post is more of a "do as I say, not as I do" because I have nt been developing with accessibility in mind. There are certain ways of coding which I have picked up in the past whic do fit the accessibility patterns, but this is more fortuitous happenstance than deliberate design. It recently came up on the ClassicPress Forum when Marco Zehe joined the community.

Reading an interview he did with Deborah Edwards-Onoro of Lireo Designs this reiterated that accessibility should not just be a nice to have, but a requirement as without it, sites will not be accessible to many users. One of the reasons he gave for looking at ClassicPress is the forced introduction of the block editor (formerly Gutenburg editor) into WordPress which has many series accessibility problems.

Plugins form a major part of the ClassicPress, and WordPress, ecosystems with many of them outputting content to users and this should always be done in an accessible way; it's not just the front end content which needs to be accessible, it is also the settings pages in the admin dashboard.

As with internationalization, adding accessibility to an existing plugin will take a lot more effort than designing and coding the plugin for accessibility from the start. This is a task which I have added to my development list to look at as soon as possible; unfortunately with a lot of ClassicPress plugins this is not going to be a small task.

The A11Y Project website has links to a lot of resources to help with accessibility design including a checklist.

ClassicPress Plugin Development: Develop for Translation

ClassicPress PluginsThis post is part of the ClassicPress Plugin Development series in which I am going to look at both best practice for developing plugins and how I approach some requirements as well as some of the functions I commonly use.

When developing plugins for ClassicPress, you should plan and develop for the plugin to be translatable. There are many posts on translation of plugins for WordPress which will also apply to ClassicPress. I did a series in late 2019 on ClassicPress Plugin Development where I covered the why and how of developing plugins so they can be translated.

Two key terms in this process are internationalization (often abbreviated as i18n as there are 18 letters between the letters i and n) which is the process of developing a plugin so it can easily be translated into other languages and localization (often abbreviated as l10n as there are 10 letters between the letters l and n) is the process of translating an internationalized plugin.

Developing your plugins using the principles of internationalization, allows other to localize your plugin by translating it into their language. Plugins I develop are all in English, as that is my primary language, but having internationalized them during developed, anyone can now translate them into the language used by their site without needing to modify any code.

As I noted in this post, when I started developing plugins I did not internationalize any of the strings and then had to go back and find every single string in all the plugins, but new plugins I internationalize from the start and this takes far less effort overall.

Internationalizing your plugins will widen the market for your plugin to countries outside of those which use your language.

Excel Snippets: AND/OR Operators

Microsoft ExcelI might not post many Excel snippets, but I'm collecting them into a small Excel Snippets series to make them easy to find.

I've been doing work with quite a few clients with Jet Reports recently and one of the common requirements it to hide a row when values are blank. While the clients I am working with are experienced with Excel, they often are very good with VLOOKUP and other functions, but not so much with AND/OR operators.

This can be done using an IF statement combined with an AND operator. In Excel the and statement format is different to programming/scripting languages.

The below is an example AND checking the value in cell A2 and B2 and returning true if they are both 1 otherwise returning false:

=IF(AND(A2=1,B2=1),"True","False")

Just for completeness, the below is an example OR checking the value in cell A2 and B2 and returning true if they either cell is 1 otherwise returning false:

=IF(OR(A2=1,B2=1),"True","False")

SQL View to Return Sales Invoice Lines

Microsoft Dynamics GPI've been organising a demo of software which can be used to produce documentation using data from Microsoft Dynamics GP and integrate it with data from other systems or network shares. I thought about a few ways of getting the Dynamics GP data and decided that a custom SQL view returning only the required information was the best way to go.

This view returns sales invoice lines and related fields such as the sales user defined.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_SalesInvoiceLines', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesInvoiceLines
GO
-- create view
CREATE VIEW [dbo].[uv_AZRCRV_SalesInvoiceLines] AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT RTRIM(['Sales Transaction Work'].SOPNUMBE) AS 'Invoice Number' ,['Sales Transaction Amounts Work'].LNITMSEQ AS 'Line Number' ,RTRIM(ISNULL(['Sales Customer Item Cross Reference'].CUSTITEMNMBR, ['Sales Transaction Amounts Work'].ITEMNMBR)) AS 'Item Number' ,RTRIM(ISNULL(['Sales Customer Item Cross Reference'].CUSTITEMDESC, ['Sales Transaction Amounts Work'].ITEMDESC)) AS 'Item Description' ,RTRIM(['Item Master'].TCC) AS 'Tax Commodity Code' ,'UK' AS 'Country of Origin' ,RTRIM(['Sales Transaction Amounts Work'].UOFM) AS 'Unit of Measure' ,['Sales Transaction Amounts Work'].QUANTITY AS 'Quantity' ,CONVERT(DECIMAL(10,2),['Sales Transaction Amounts Work'].ORUNTPRC) AS 'Originating Unit Price' ,CONVERT(DECIMAL(10,2),['Sales Transaction Amounts Work'].OREXTCST) AS 'Originating Extended Price' ,RTRIM(['Sales Transaction Amounts Work'].LOCNCODE) AS 'Site' FROM SOP10100 AS ['Sales Transaction Work'] -- Sales Transaction Work (SOP10100) INNER JOIN SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200) ON ['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE AND ['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE INNER JOIN IV00101 AS ['Item Master'] -- Item Master (IV00101) ON ['Item Master'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR LEFT JOIN SOP60300 AS ['Sales Customer Item Cross Reference'] -- Sales Customer Item Cross Reference (SOP60300) ON ['Sales Customer Item Cross Reference'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR AND ['Sales Customer Item Cross Reference'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR WHERE ['Sales Transaction Amounts Work'].SOPTYPE = 3 GO GRANT SELECT ON uv_AZRCRV_SalesInvoiceLines TO DYNGRP GO

[/postcode]

SQL View to Return Sales Invoice Headers

Microsoft Dynamics GPI've been organising a demo of software which can be used to produce documentation using data from Microsoft Dynamics GP and integrate it with data from other systems or network shares. I thought about a few ways of getting the Dynamics GP data and decided that a custom SQL view returning only the required information was the best way to go.

This view returns sales invoice headers and related fields such as the sales user defined.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_SalesInvoiceHeaders', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesInvoiceHeaders
GO
-- create view
CREATE VIEW [dbo].[uv_AZRCRV_SalesInvoiceHeaders] AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT RTRIM(['Sales Transaction Work'].SOPNUMBE) AS 'Invoice Number' ,['Sales Transaction Work'].DOCID AS 'Invoice Type' ,FORMAT(['Sales Transaction Work'].DOCDATE, 'dd-MM-yyyy') AS 'Document Date' ,FORMAT(['Sales Transaction Work'].INVODATE, 'dd-MM-yyyy') AS 'Invoice Date' ,FORMAT(['Sales Transaction Work'].INVODATE, 'yyyy-MM-dd') AS 'Invoice Date Sortable' ,FORMAT(['Sales Transaction Work'].DUEDATE, 'dd-MM-yyyy') AS 'Due Date' ,FORMAT(['Sales Transaction Work'].ReqShipDate, 'dd-MM-yyyy') AS 'Requested Ship Date' ,RTRIM(['Company Master'].ADRCNTCT) AS 'Shipper Contact Person' ,RTRIM(['Company Master'].ADDRESS1) AS 'Shipper Address 1' ,RTRIM(['Company Master'].ADDRESS2) AS 'Shipper Address 2' ,RTRIM(['Company Master'].ADDRESS3) AS 'Shipper Address 3' ,RTRIM(['Company Master'].CITY) AS 'Shipper City' ,RTRIM(['Company Master'].STATE) AS 'Shipper State' ,RTRIM(['Company Master'].ZIPCODE) AS 'Shipper Zip Code' ,RTRIM(['Company Master'].COUNTY) AS 'Shipper Country' ,RTRIM(['Company Master'].PHONE1) AS 'Shipper Phone 1' ,RTRIM(['Sales Transaction Work'].CUSTNMBR) AS 'Customer Number' ,RTRIM(['Sales Transaction Work'].CUSTNAME) AS 'Customer Name' ,RTRIM(['RM Customer Master'].TXRGNNUM) AS 'Tax Registration Number' ,RTRIM(['Sales Transaction Work'].PRSTADCD) AS 'Ship To Address Code' ,RTRIM(['Sales Transaction Work'].CNTCPRSN) AS 'Contact Person' ,RTRIM(['Sales Transaction Work'].ShipToName) AS 'Ship To Name' ,RTRIM(['Sales Transaction Work'].ADDRESS1) AS 'Address 1' ,RTRIM(['Sales Transaction Work'].ADDRESS2) AS 'Address 2' ,RTRIM(['Sales Transaction Work'].ADDRESS3) AS 'Address 3' ,RTRIM(['Sales Transaction Work'].CITY) AS 'City' ,RTRIM(['Sales Transaction Work'].STATE) AS 'State' ,RTRIM(['Sales Transaction Work'].ZIPCODE) AS 'Zip Code' ,RTRIM(['Sales Transaction Work'].COUNTRY) AS 'Country' ,RTRIM(['Sales Transaction Work'].PHNUMBR1) AS 'Phone 1' ,RTRIM(['Sales Transaction Work'].CURNCYID) AS 'Currency ID' ,( SELECT CONVERT(DECIMAL(10,0), SUM(QUANTITY)) FROM SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200) WHERE ['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE AND ['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE ) AS 'Number Of Pieces' ,( SELECT CONVERT(DECIMAL(10,2), SUM(QUANTITY) * SUM(['Item Master'].ITEMSHWT)) FROM SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200) INNER JOIN IV00101 AS ['Item Master'] -- Item Master (IV00101) ON ['Item Master'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR WHERE ['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE AND ['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE ) AS 'Total Weight' ,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].SUBTOTAL) AS 'Sub Total' ,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].TAXAMNT) AS 'Tax Amount' ,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].FRTAMNT) AS 'Freight Amount' ,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].DOCAMNT) AS 'Document Amount' FROM SOP10100 AS ['Sales Transaction Work'] -- Sales Transaction Work (SOP10100) LEFT JOIN RM00101 AS ['RM Customer Master'] -- RM Customer MSTR (RM00101) ON ['RM Customer Master'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR LEFT JOIN DYNAMICS..SY01500 AS ['Company Master'] -- Company Master (SY01500) ON ['Company Master'].INTERID = DB_NAME() WHERE ['Sales Transaction Work'].SOPTYPE = 3 GO GRANT SELECT ON uv_AZRCRV_SalesInvoiceHeaders TO DYNGRP GO

[/postcode]

SQL View to Return Sales Quote Lines

Microsoft Dynamics GPI've been organising a demo of software which can be used to produce documentation using data from Microsoft Dynamics GP and integrate it with data from other systems or network shares. I thought about a few ways of getting the Dynamics GP data and decided that a custom SQL view returning only the required information was the best way to go.

This view returns sales quote lines and related fields such as the sales user defined.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_SalesQuoteLines', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesQuoteLines
GO
-- create view
CREATE VIEW [dbo].[uv_AZRCRV_SalesQuoteLines] AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT RTRIM(['Sales Transaction Work'].SOPNUMBE) AS 'Quote Number' ,['Sales Transaction Amounts Work'].LNITMSEQ AS 'Line Number' ,RTRIM(ISNULL(['Sales Customer Item Cross Reference'].CUSTITEMNMBR, ['Sales Transaction Amounts Work'].ITEMNMBR)) AS 'Item Number' ,RTRIM(ISNULL(['Sales Customer Item Cross Reference'].CUSTITEMDESC, ['Sales Transaction Amounts Work'].ITEMDESC)) AS 'Item Description' ,RTRIM(['Sales Transaction Amounts Work'].UOFM) AS 'Unit of Measure' ,['Sales Transaction Amounts Work'].QUANTITY AS 'Quantity' ,CONVERT(DECIMAL(10,2),['Sales Transaction Amounts Work'].ORUNTPRC) AS 'Originating Unit Price' ,CONVERT(DECIMAL(10,2),['Sales Transaction Amounts Work'].OREXTCST) AS 'Originating Extended Price' ,RTRIM(['Sales Transaction Amounts Work'].LOCNCODE) AS 'Site' FROM SOP10100 AS ['Sales Transaction Work'] -- Sales Transaction Work (SOP10100) INNER JOIN SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200) ON ['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE AND ['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE LEFT JOIN SOP60300 AS ['Sales Customer Item Cross Reference'] -- Sales Customer Item Cross Reference (SOP60300) ON ['Sales Customer Item Cross Reference'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR AND ['Sales Customer Item Cross Reference'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR WHERE ['Sales Transaction Amounts Work'].SOPTYPE = 1 GO GRANT SELECT ON uv_AZRCRV_SalesQuoteLines TO DYNGRP GO