Dashboards

[♥bruyndoncx]

i can only think of the 3 monkeys, looking good !

[Dan Cole]

@@bruyndoncx  Thanks Carine...I think. :D

 

Dan

[Dan Cole]

I pulled the new products iBar together and posted it on my live site only to discover that the query wasn't working as well as I thought.   Seems it need a LEFT JOIN rather than just a JOIN and I also discovered I could FORMAT the Total_Sales data within MYSQL as well, so the final (I think) query ended up as follows:

SELECT COUNT(*) AS New_Products,
FORMAT(SUM( op.final_price * op.products_quantity ),2) AS Total_Sales
FROM products p
LEFT JOIN orders_products op using (products_id)
WHERE p.products_date_added >= ( date_sub(now(), interval 30 day))

Dan

[♥bruyndoncx]

I pulled the new products iBar together and posted it on my live site only to discover that the query wasn't working as well as I thought.   Seems it need a LEFT JOIN rather than just a JOIN and I also discovered I could FORMAT the Total_Sales data within MYSQL as well, so the final (I think) query ended up as follows:

Still looking good, it all depends on what you exactly wanted to see, but I think you got the hang of it :thumbsup:

[♥Gyakutsuki]

I think your indicators could be developped also on other page like products / customers / orders. Hooks could be interesting in this case;

[Dan Cole]

@@Gyakutsuki

 

I think your indicators could be developped also on other page like products / customers / orders. Hooks could be interesting in this case;

 

Hi Loic...I agree that should be possible since they are simply modules.  Personally I like seeing these all in one place (at least for the module that have been developed so far) but it might be useful to develop specific modules for the pages you indicated.  Getting specific warning indicators could prove quite interesting and useful.

 

Dan

[Dan Cole]

One of the things I've noticed over the years is the importance of continually adding new products.   It not only helps to keep your store fresh and up-to-date but it also helps to increase sales and attract new customers.

 

To ensure that this gets the focus it needs I'd like to add another iBar module to show me the number of new products added and the total sales generated from those products during a specified period...say monthly, quarterly or whatever.

 

This module is attached.  I want to add a couple of order status style tables to it but it is ready for some tire kicking.

 

If you have any trouble with it or have any questions please let me know.   In due course I'll post it to the Addons thread.

 

More Modules.zip

 

Dan

[Dan Cole]

The iBar New Products module seems to have succeed in getting me to add new products to my shop.  I set a monthly goal and have been spending my spare time trying to achieve it. I still have products to add but I'm already seeing sales as a result.  I find the iBar module very motivating and I think it is one that everyone should be using.

 

As those sales started adding up I wanted to see which products were selling so I pulled together an iBar New Products Sales table.   That module is included in the attached zip file as well as a couple of other updates to tie some of the other modules together a little better.

 

More iBar Modules.zip

 

If you download and install the modules please let me know how it goes, one way or the other.  If they are trouble free I will add them to the original iBar package.

 

Dan

[Dan Cole]

I have made my own Bootstrap admin index page with a few key things I like to like at and/or have quick access to.

 

dashboard.png

 

@@frankl Frank I was looking at your Dashboard again and noticed that you have an item described at "Update Website".  It looks like a todo list.  Is that what it is? Could you elaborate a bit on it.  Is it a hard coded list or are you drawing items from a database.  How exactly does that work?  I also see that you have an alert for product questions and help desk emails?  What is the source for that information?  Are you using an internal CMS?

 

Interesting stuff...

 

Dan

[Dan Cole]

It seems that my new product query needed another tweak.  As I started to see multiple sales, from some of the new products I was adding, I realized I was counting the number of products sold rather than the number of new products.  I added a DISTINCT clause to my COUNT function and that seems to have sorted it out.  Here is what the new products query looks like now. 

SELECT COUNT(DISTINCT (p.products_id)) AS New_Products, 
FORMAT(SUM( op.final_price * op.products_quantity ),2) AS Total_Sales
FROM products p
LEFT JOIN orders_products op using (products_id)
WHERE p.products_date_added >= ( date_sub(now(), interval " . MODULE_ADMIN_DASHBOARD_IBAR_NEW_PRODUCTS_PERIOD . ") )

I'll update the iBar module in due course.

 

Dan

[frankl]

@@Dan Cole The "Update website" is hardcoded with links (I have several sites and this is where I put regular maintenance items which vary from site to site).

 

I have product questions addon installed, so the number of unanswered questions are highlighted in that module.

 

The help desk emails module connects to our email account with imap_open and checks for unread mails, nothing else (just a heads up to go to the email program and answer some emails lol).

[burt]

The help desk emails module connects to our email account with imap_open and checks for unread mails, nothing else (just a heads up to go to the email program and answer some emails lol).

 

That is the best thing I have read on this forum in a long time.  Intrigued enough to make a dashboard...

 

And whilst making it, discovered some unread emails  :blush:

 

[Dan Cole]

@@frankl

 

The help desk emails module connects to our email account with imap_open and checks for unread mails, nothing else (just a heads up to go to the email program and answer some emails lol).

 

Very interesting Frank and like Gary I didn't know that was even possible.   I think this would make for a awesome iBar module....would you be willing to share the code?  I know you have it hard coded currently but maybe we could turn it into an iBar module.   A little TODO LIST module would be pretty cool too but I'll leave that for another day.

 

Dan

[Dan Cole]

Got a little side tracked and started to actually work on my business.  I guess the iBar modules are having the desired affect. :thumbsup:

 

Here is what my Dashboard looks like at the moment.  

 

 

Now that I have achieved my new products goal I'm going to spend a little time sending out requests for reviews.  I'm using Gary's Key System which is all set up to go and I have a couple of iBar review modules on the Dashboard.  I can't run a cronjob on the server where my site is hosted so it's a manual process but one that shouldn't take up too much time -- it just needs some focus.  

 

I also want to spend some time setting up "specials" on a regular basis so there will be an iBar module for that in the not to distant future.

 

What does your Dashboard look like?

 

Dan

[Jack_mcs]

@@Dan Cole I haven't been following this thread so please excuse me if this has been mentioned or done, but I think it might be helpful if the boxes were collapsible. There are addons that add items to the dashboard so it could get pretty crowded there over time with those and others that may come out. Just a suggestion.

 

I like what you've done, by the way. :)

[Dan Cole]

@@Dan Cole I haven't been following this thread so please excuse me if this has been mentioned or done, but I think it might be helpful if the boxes were collapsible. There are addons that add items to the dashboard so it could get pretty crowded there over time with those and others that may come out. Just a suggestion.

 

I like what you've done, by the way. :)

 

@@Jack_mcs  That is an excellent suggestion Jack especially for the table like dashboards.  It would free up a lot of space and perhaps it can be set up so that the queries aren't executed until the tables are expanded.  That would speed up/minimize loading times as more and more modules are added to the dashboard.   Good idea Jack...I like it. 

 

Has anyone seen a module that works in this way? :)

 

Dan

[Jack_mcs]

If the dashboard is just for the BS version, you can use the collapse option. See an example here.

[Dan Cole]

@@Jack_mcs  Thanks again Jack....I did that on a Mini Marketing Site I built when we were just exploring BootStrap....I guess there is no reason to reinvent the wheel.

 

Dan

[Dan Cole]

I had a bit of time this afternoon so I thought I'd do a little work on the iBar Specials Modules and see if I could come up with a couple of queries to get the value of the products sold that were featured as "specials" and secondly produce a list of those products, showing the name of the products sold, the quantity sold as well as the value.   Here is what I came up with...

 

To get the details....

SELECT sp.products_id, prd.products_name, IFNULL( SUM( orp.products_quantity ) , 0 ) AS Qty, IFNULL( SUM( orp.final_price ) , 0 ) AS Sales
FROM specials sp
INNER JOIN products_description prd ON sp.products_id = prd.products_id
LEFT OUTER JOIN orders_products orp ON sp.products_id = orp.products_id
INNER JOIN orders ord ON orp.orders_id = ord.orders_id
WHERE ord.date_purchased >= '2017-02-02T00:00:00-05:00'
AND ord.date_purchased <= '2017-03-04T23:59:59-05:00'
AND (
sp.expires_date >= '2017-03-04T23:59:59-05:00'
OR sp.expires_date = '0000-00-00 00:00:00'
OR sp.expires_date IS NULL
OR sp.expires_date = ''
)
GROUP BY sp.products_id, prd.products_name
ORDER BY prd.products_name

and to get the total

SELECT SUM( Sales ) AS TotalSales
FROM (

SELECT sp.products_id, prd.products_name, IFNULL( SUM( orp.products_quantity ) , 0 ) AS Qty, IFNULL( SUM( orp.final_price ) , 0 ) AS Sales
FROM specials sp
INNER JOIN products_description prd ON sp.products_id = prd.products_id
LEFT OUTER JOIN orders_products orp ON sp.products_id = orp.products_id
INNER JOIN orders ord ON orp.orders_id = ord.orders_id
WHERE ord.date_purchased >= '2017-02-02T00:00:00-05:00'
AND ord.date_purchased <= '2017-03-04T23:59:59-05:00'
   AND ( sp.expires_date >= '2017-03-04T23:59:59-05:00'
       OR sp.expires_date = '0000-00-00 00:00:00'
       OR sp.expires_date IS NULL
       OR sp.expires_date = ''
         )
GROUP BY sp.products_id, prd.products_name
ORDER BY prd.products_name

) AS qry

While these queries seem to work as intended I'm wondering if there is a more efficient way to write them?   Secondly and while I don't need to do this I'm curious to know if it is possible to combine them so there is just one query.   It won't help to have just one query since they are going in different modules but I wanted to see if I could do that and wasn't able to figure it out.  I'm stubborn and need to know.  (w00t)

 

Dan

[Dan Cole]

If the dashboard is just for the BS version, you can use the collapse option. See an example here.

 

I gave this a go on my new iBar_specials_table and while it collapses and expands nicely it just leaves me with "white space" below the collapsed bar since the module in the other column doesn't change.  I assume if I add the collapsible option to the table in the opposite column it'll make a difference but we'll see.   I also played around a bit with the bootstrap card columns but I couldn't seem to get it formatted the way I wanted.  I put that aside for now but will no doubt come back to it as the collapsible option is added to the other iBar tables. 

 

Dan

[burt]

Collapsing things is straightforward until they are side by side.

 

If you have 4 boxes as so, in 2 column and 2 rows;

1 | 2
1 | 2
  | 2
3 | 4
3 | 4

Box 1 is 2 units deep.  2 is 3 units deep.  This means that Row A (contains box 1 and 2) is 3 units deep [to match box 2].

 

If you now collapse Box 2 to nothing...the row will collapse to match the depth of Box 1 (ie 2 units).

Reset box 2 to 3 units.

If you now collapse Box 1 to nothing...the row wont change at all as it is still 3 deep (due to box 2).

Rest box 1.

If you now collapse BOTH box 1 and box 2 to nothing, the row will also collapse to nothing.

 

Does that help?

 

What most developers do to get around is to have the modules laid out as so:

 

1

1

2

2

2

3

3

4

4

 

and use an accordion

[Dan Cole]

 @@burt

 

Does that help?

 

I haven't had enough coffee yet for that explanation :D but I'll have another look at it some where along the way.  I'm to stubborn not to.   :wacko:

 

I thought the card columns option was the best long term solution but I just couldn't get it to work the way I thought it should.  I'm sure it was just me though so I'll have another go at that too.  I got so caught up with the card columns I never thought of using an accordion at all, although I have used it before.

 

Thanks Gary.

 

Dan

[Jack_mcs]

I gave this a go on my new iBar_specials_table and while it collapses and expands nicely it just leaves me with "white space" below the collapsed bar since the module in the other column doesn't change.  I assume if I add the collapsible option to the table in the opposite column

That's correct. Though I think the better way to do it would be to wrap each row (pair of sections) in its own div. Then the collapse would work for the whole row.

[Dan Cole]

That's correct. Though I think the better way to do it would be to wrap each row (pair of sections) in its own div. Then the collapse would work for the whole row.

@@Jack_mcs

 

It doesn't look like you even need to wrap them in a "row" div.  As it turns out I had one of the other iBar tables beside my new Specials table and the white space disappeared as my opposite table got shorter and shorter so this should work okay until I decide on something better. :)

 

Dan

[Dan Cole]

For those who have been following along and want to help test new modules as they are developed here is a new update.  If the modules pass mustard I'll add them to the full package in the add-ons area in due course.   The package includes...

 

1.  An iBar review requested module.   This dashboard module requires and works with Gary's Key System as described here.

 

2. A Specials iBar.  The one shows you the number of new of items purchased that were on "special" as well as the dollar value of those purchases.

 

3. A Specials iBar Table supporting the above module.

 

4. An iBar Spacer module so you can add a bit of blank space for formatting purposes.  My modules spilled over to a second row and messed with the formatting of the rest of the dashboard modules so I needed a way to fix that without resizing the rest of the iBar modules....I remembered a module that @@kymation Jim developed when we were working on the modular shopping cart so I borrowed his idea and added this spacer module.

 

I spent a fair bit of time working on the heading for the iBar Specials Table and if it hangs together okay it will become the template that I'll be using for all other table modules.  I would welcome your input on that in particular...does it work well, do you like it etc?

 

More iBar Modules - r2.zip

 

Dan