dispositiontools/craft-commerceinsights

Get insights from Craft Commerce

craft-commerceinsights's Issues

Purchases by date: SQLSTATE[42000]: Syntax error or access violation

Hi @dispositiontools we're getting the following error when running any products by date (products/purchases) report.

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'my_db_name.cvc.title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

This is on Craft 3 and the plugin is up to date. Thanks.

Missing billing address causes error

I have an order that for some reason has no billing address. This causes Commerce Insights (v3 branch) to throw an error.

It looks like the issue is in the Products Service, where there's a check that the Shipping address exists but then data is pulled from the Billing address which may not:

if($shippingAddress){
  $addressShippingDetails = [
    'shippingBusinessName' => $billingAddress->businessName ,
     'shippingFirstName' => $billingAddress->firstName ,
     'shippingLastName' => $billingAddress->lastName ,
     'shippingAddress1' => $billingAddress->address1 ,
     'shippingAddress2' => $billingAddress->address2 ,
     'shippingAddress3' => $billingAddress->address3 ,
     'shippingCity' => $billingAddress->city ,
     'shippingPostcode' => $billingAddress->zipCode ,
   ];
}

I assume that's just a typo.

MySQL only_full_group_by error on getCustomers

Hi,

We get this message on the getCustomers method in the CustomersService. Looks like this query is made pre MySQL 5.7.5 due to the 'only_full_group_by' error.

Syntax error or access violation: 1055 Expression #15 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'craft.co.email' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Tested it locally and made this change to make it work on line 126 of src/services/Customers.php

->groupBy(['co.customerId'])

to

->groupBy(['co.customerId', 'co.email'])

If this is fixed that we can start using it in our production enviroments on MySQL 8. Thanks

Craft 4

Is a Craft 4 compatible version planned?

Bug: Active Tab is not highlighted correctly

Hi there,

Using Craft CMS 4.3.6.1 and Commerce Insights 4.0.1.

If you click 'Customers' or 'Transactions' in the left hand side nav, 'Products' still remains highlighted.

See attached screenshot:

Error when using "Find best selling products"

Hello,

I'm getting an error when I use "Find best selling products" functionality.

Probably because it is trying to get a purchasable that has been deleted. The docs mention this scenario here: https://craftcms.com/docs/commerce/3.x/purchasables.html#purchasables-and-line-items

Is it possible that you can change the code so it uses the snapshot of the lineItem instead of the getPurchasable() method?
Because the getPurchasable() method is returning null if the purchasable is deleted.

Thanks in advance!

Bug: All lists do not show current day's transactions

Hi there, sorry for the barrage of tickets!

By default, the date picker on all the report views is pre-filled with: From: 1st of current month -> To: Today's date.

But no transactions from today are shown, you have to manually change the date picker to 1 day higher, i.e. tomorrow.

(e.g. today is the 12th, but I have to select 13th to see today's transactions).

Could you take a look into this please when you have a second?

Thanks,

Rob

DivisionByZeroError on empty store.

Hello,

I just installed this plugin on a new and empty store and received a division by zero error on the admin/commerceinsights/customers page.

Error best selling products

I get an error when searching for the best selling products:
Undefined index: product

Best wishes

Database error on PostgreSQL

Trying to view any of the reports on my site gives the error:

Database Exception – yii\db\Exception

SQLSTATE[42703]: Undefined column: 7 ERROR: column cli.orderid does not exist
LINE 3: LEFT JOIN "commerce_orders" "co" ON co.id = cli.orderId
^
HINT: Perhaps you meant to reference the column "cli.orderId".

It looks like query is case sensitive, would this be due to me using PostgreSQL rather than MySQL?

Feature Request: Currency display

In our store, we only have one currency. In the CMS, the reports show non currency numbers.

It would be great if we could either select this in the Insights options or it pulled it dynamically through from Commerce settings.