Custom report for Emerald

Quote

Total posts: 425
19 Aug 2014 17:14

Hi Sergey,

I need a special page, where I can see:
daily number of sales and daily total amount of sales grouped by last 15 days (two columns) monthly number of sales and monthly total amount of sales group by last 6 months (two columns).

I also need some more customizations to this report but for the moment this is enough.

Is it possible that I can get this page without login if I adding the cronjob word at the end of the link as a parameter.

This page does not even need to be n the joomla template. It can be a standalone page which will show me these numbers.

Is this possible, would you like to add this to the current system? If not, can you send me a piece of SQL code so I can create a standalone page where I can gather this information?

Thanks a lot. Hakan

Last Modified: 25 Aug 2014

Quote

hakana

Total posts: 425
19 Aug 2014 17:15

I will also add this to future requests for emerald. I am thinking to create a mobile application which will connect to joomla and get this report. If you can create this mobile application, I will buy it for sure with extra cost. You can consider building something like this.

Quote

Sergey

Total posts: 13,748
20 Aug 2014 11:03

I will not consider this roght now anyway. Too much work already. But may be in the future.

Quote

hakana

Total posts: 425
20 Aug 2014 17:05

Hi Sergey,

If you can give me the sqls for these two queries, I will get this application developed and present it to you so that you can sell it.

daily number of sales and daily total amount of sales grouped by last 15 days (two columns)
monthly number of sales and monthly total amount of sales group by last 6 months (two columns).

Have a nice day, Hakan

Quote

Sergey

Total posts: 13,748
22 Aug 2014 11:45

hakana If you can give me the sqls for these two queries, I will get this application developed and present it to you so that you can sell it.

If you develop it, you can add it to depot and distribute it for free for other users.

hakana daily number of sales and daily total amount of sales grouped by last 15 days (two columns)

SELECT sum(price), count(id) 
  FROM `#__emerald_subscriptions` 
 WHERE ctime > NOW() - INTERVAL 15 DAY 
 GROUP BY DATE(ctime);

hakana monthly number of sales and monthly total amount of sales group by last 6 months (two columns).

SELECT sum(price), count(id) 
  FROM `#__emerald_subscriptions` 
 WHERE ctime > NOW() - INTERVAL 6 MONTH 
 GROUP BY DATE_FORMAT(ctime, '%Y-%m');

Quote

Sergey

Total posts: 13,748
25 Aug 2014 02:33

pepperstreet Why can't I use "GROUP BY" separately?

This will work perfectly fine.

SELECT id 
  FROM `#__emerald_subscriptions` 
 WHERE ctime > NOW() - INTERVAL 15 DAY 
 GROUP BY DATE(ctime);

It is not GROUP BY that require agreagators function, it is agrigator functions that require GROUP BY.

Login (copy)