When adding the Subscriptions widget to the dashboard, it no longer loads, and presents the following error:

SQLSTATE[42703]: Undefined column: 7 ERROR: column subscriptions.planid does not exist

Unfortunately, I don't know enough about joins to understand why this isn't working (planId is indeed a column in the commerce_subscriptions table…), so my fussing with the source didn't unearth an explanation.

This is in version 2.0.9, on Craft 3.0.28, and Commerce 2.0.0-beta.12.1.

Let me know if you need any other info!

Hello! Lovely app - any plans to make it Craft v4 compatible? Thanks!

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'primalgourmetsc.orders.customerId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/db/Command.php:1258
Stack trace:
#0 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/db/Command.php(1258): PDOStatement->execute()
#1 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/db/Command.php(1148): yii\db\Command->internalExecute('SELECT count(*)...')
#2 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/db/Command.php(399): yii\db\Command->queryInternal('fetchAll', NULL)
#3 /home/forge/primal-gourmet.kickstandstudio.com/vendor/bymayo/commerce-widgets/src/widgets/TopCustomers.php(83): yii\db\Command->queryAll()
#4 /home/forge/primal-gourmet.kickstandstudio.com/vendor/bymayo/commerce-widgets/src/widgets/TopCustomers.php(136): bymayo\commercewidgets\widgets\TopCustomers->getCustomers()
#5 /home/forge/primal-gourmet.kickstandstudio.com/vendor/craftcms/cms/src/controllers/DashboardController.php(517): bymayo\commercewidgets\widgets\TopCustomers->getBodyHtml()
#6 /home/forge/primal-gourmet.kickstandstudio.com/vendor/craftcms/cms/src/controllers/DashboardController.php(98): craft\controllers\DashboardController->_getWidgetInfo(Object(bymayo\commercewidgets\widgets\TopCustomers))
#7 [internal function]: craft\controllers\DashboardController->actionIndex()
#8 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#9 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#10 /home/forge/primal-gourmet.kickstandstudio.com/vendor/craftcms/cms/src/web/Controller.php(104): yii\base\Controller->runAction('index', Array)
#11 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('index', Array)
#12 /home/forge/primal-gourmet.kickstandstudio.com/vendor/craftcms/cms/src/web/Application.php(282): yii\base\Module->runAction('dashboard/index', Array)
#13 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/web/Application.php(103): craft\web\Application->runAction('dashboard/index', Array)
#14 /home/forge/primal-gourmet.kickstandstudio.com/vendor/craftcms/cms/src/web/Application.php(271): yii\web\Application->handleRequest(Object(craft\web\Request))
#15 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#16 /home/forge/primal-gourmet.kickstandstudio.com/web/index.php(42): yii\base\Application->run()
#17 {main}

Next yii\db\Exception: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'primalgourmetsc.orders.customerId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
The SQL being executed was: SELECT count(*) as totalOrders, SUM(orders.totalPrice) as totalRevenue, `orders`.`email`, `orders`.`customerId`
FROM `commerce_orders` `orders`
WHERE `orders`.`isCompleted`=1
GROUP BY `orders`.`email`
ORDER BY `totalOrders` DESC
LIMIT 5 in /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/db/Schema.php:664
Stack trace:
#0 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/db/Command.php(1263): yii\db\Schema->convertException(Object(PDOException), 'SELECT count(*)...')
#1 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/db/Command.php(1148): yii\db\Command->internalExecute('SELECT count(*)...')
#2 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/db/Command.php(399): yii\db\Command->queryInternal('fetchAll', NULL)
#3 /home/forge/primal-gourmet.kickstandstudio.com/vendor/bymayo/commerce-widgets/src/widgets/TopCustomers.php(83): yii\db\Command->queryAll()
#4 /home/forge/primal-gourmet.kickstandstudio.com/vendor/bymayo/commerce-widgets/src/widgets/TopCustomers.php(136): bymayo\commercewidgets\widgets\TopCustomers->getCustomers()
#5 /home/forge/primal-gourmet.kickstandstudio.com/vendor/craftcms/cms/src/controllers/DashboardController.php(517): bymayo\commercewidgets\widgets\TopCustomers->getBodyHtml()
#6 /home/forge/primal-gourmet.kickstandstudio.com/vendor/craftcms/cms/src/controllers/DashboardController.php(98): craft\controllers\DashboardController->_getWidgetInfo(Object(bymayo\commercewidgets\widgets\TopCustomers))
#7 [internal function]: craft\controllers\DashboardController->actionIndex()
#8 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#9 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#10 /home/forge/primal-gourmet.kickstandstudio.com/vendor/craftcms/cms/src/web/Controller.php(104): yii\base\Controller->runAction('index', Array)
#11 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('index', Array)
#12 /home/forge/primal-gourmet.kickstandstudio.com/vendor/craftcms/cms/src/web/Application.php(282): yii\base\Module->runAction('dashboard/index', Array)
#13 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/web/Application.php(103): craft\web\Application->runAction('dashboard/index', Array)
#14 /home/forge/primal-gourmet.kickstandstudio.com/vendor/craftcms/cms/src/web/Application.php(271): yii\web\Application->handleRequest(Object(craft\web\Request))
#15 /home/forge/primal-gourmet.kickstandstudio.com/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#16 /home/forge/primal-gourmet.kickstandstudio.com/web/index.php(42): yii\base\Application->run()
#17 {main}
Additional Information:
Array
(
    [0] => 42000
    [1] => 1055
    [2] => Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'primalgourmetsc.orders.customerId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
)

Seems like there was a similar issue here

Hi there, would it be possible to take into account Craft's timezone setting when fetching data for the various widgets?

I recently took the plunge and built a custom widget that reported on different order types (specific to a particular CC site). I found that all dates are stored in UTC time, so to get the correct output for my timezone (Pacific/Auckland), I needed to do something similar to a fix implemented in CC earlier in the year: craftcms/commerce@b36ad11.

Just for reference, this is what I'm currently seeing with the goal widget vs. Craft Commerce:

... and this is from my custom widget:

If I get time I might have a crack and raise a PR, but can't promise anything!

Hi there!
Great plugin! We love it, would you happen to know why the timestamps are off from what our craft install is set at? Is there somewhere to set the timezone to EST?

Thank you

I have noticed that on the Cart Abandonment Widget the total for successful (completed) carts never goes above 100.

@bymayo I'm using postgresql and the columns are case sensitive, for example this query

SELECT COALESCE(sum(orders.totalPrice), 0) AS "totalRevenue", COALESCE(count(orders.id), 0) AS "totalOrders" FROM "commerce_orders" "orders" INNER JOIN "elements" "elements" ON elements.id = orders.id

It doesn't work, but if I change it this way it works

SELECT COALESCE(sum(orders."totalPrice"), 0) AS "totalRevenue", COALESCE(count(orders."id"), 0) AS "totalOrders" FROM "commerce_orders" "orders" INNER JOIN "elements" "elements" ON elements.id = orders.id

https://stackoverflow.com/questions/21796446/postgres-case-sensitivity

So no widget works for me

is it possible to create a function so it is possible to let clients see there revenue of an order without taxes and shipping. To make a start for this just get the revenue amount of an order without all applied adjusters from tax and shipping.

Examples:

• Per user
• Per usergroup
• Per product

Add global setting to use fiscal year instead of calendar year. E.g. April - April not Jan - Dec.

When using Verbb's Gift Voucher plugin, Vouchers are available in commerce_purchasables, but are not in commerce_variants. As such, the query returns NULL for the ID and SKU. This really messes up the widget.

My solution right now is to just exclude any item the returns a NULL ID. This actually gives me what I want the most anyway, as I just want to know what the actual top selling products are and exclude Gift Vouchers from that display.

Change Line 84 of ProductsTop.php from

->where(['orders.orderStatusId' => $this->orderStatusId]);

to

->andWhere(['orders.orderStatusId' => $this->orderStatusId])
->andWhere(['not', ['variants.productId' => null]]);

I could see this also being an issue with Digital Products, as those products don't use Variants either, although I haven't tested it.

Both of these widgets don't take in to account trashed elements

Description

After installing the plugin and enabling a widget the control panel throws "An unknown error occurred" notification. After refreshing we get the database exception.

Steps to reproduce

1. Install Plugin
2. Enable a widget
3. Refresh on dashboard

Additional info

• Craft version: 3.0.27.1
• PHP version: 7.2.8
• Database driver & version: MySQL 5.7.23
• Plugins & versions:
  Contact Form 2.2.2
  Craft Commerce 2.0.0-beta.11
  Logs 3.0.0
  Redactor 2.1.6
  SEO 3.4.4
  Stripe for Craft Commerce 1.0.7
  Super Table 2.0.10
  Table Maker 2.0.1

I have noticed that the Total Revenue & Orders widget on the dashboard seems to show the same totals for the Year item as the month item. I believe the month is correct and the year is incorrect.

I'm definitely not sure, but it looks like the Total Ordered or Total Revenue value is showing the number of times the product appears in orders rather than data unique to that item.

For example, for a given product on the widget we see:

• 909 Total Ordered
• £19,599 Total Revenue

We know the product in question is £7 so if the total ordered is correct the total revenue should be £6,363.

If the total revenue is correct, the data must be made of multiple sources, since 19599 isn't divisible by 7 (and I'm pretty sure there are no historic discounts that would affect data).

Bit of a strange one - I know there's a discrepancy but I'm really not sure where 🧐

When the widget is set to yearly, it causes memory leaks.

Sorry, me again!

Getting a couple of weird issues with the Abandoned Cart widget:

1. y axis values appear to be getting bunched up on the left
2. graph appears to have a max width when being used at a wider setting on the dashboard.

Cheers!

Hello,

I recently installed Commerce Widgets 2.0.21 on Craft 3.5.15.1 running Commerce 3.2.12 and the settings screen is blank.

Screenshot:

It appears blank with or without a commerce-widgets.php config file.

Thanks for you help!

Feature Request

It would be awesome if there was a setting to exclude specific email addresses from all the widgets.

For example the client and our team often make test orders which can through out totals like abandoned carts etc.

The Cart Abandonment widget seems to show the same value for completed carts and abandoned carts. I believe the abounded is showing the incorrect value and this is in-fact showing the completed number instead.

I assume a completed cart is a completed order? So should this figure show the same as the order totals for that period? As this seems to differ too. In the instance I have. I have a total of 18 orders for the month but the completed cart shows 15 and the abandoned cart shows 15 too.

It seems all totals for revenue etc are including orders that have been trashed.

We had a lot of test orders which were trashed rather than deleted from the database so totals are way out.

Would be great to add to the Total Revenue & Orders widget the total orders over all time.
Currently shows the year, month, week, yesterday and today. A bottom figure for ALL time would be great.

There's an issue with the number being reported for completed carts.

The method currently uses the dateCreated attribute in the query to get the completed carts. But it's possible for a cart to be created on say a Monday but not ordered (and thus completed) until the Tuesday.

In order to get an accurate figure for completed carts it's necessary to look at the dateOrdered attribute instead but still use the dateCreated for getting the abandoned carts figure.

If a cache config setting is set, then show a label to explain when the values were last updated. Some customers/clients getting confused between values in Stripe and the stats on the dashboard.