Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Recommended Posts

@@bruyndoncx

 

mine looks similar, added the comments to see the latest communication and then ends up

select  o.orders_id, max(osh.comments)
  from orders o join orders_status_history osh using (orders_id) 
where o.orders_status = 27 
  and osh.date_added <= date_sub(curdate(), interval 5 day)
  and osh.orders_status_id = 27
  group by orders_id

 

Carine...this query has come back to haunt me.  As I work with the iBar modules I've noticed that this query doesn't always show me that last comment and I realized that I need to ensure that it gets the record most recently added to the order status history table since there can be several records for the same order id.  I though this would be rather simple task but my sql skills don't seem to be up to the challenge....I tried googling around and found several suggestions most of which indicated that I needed to add a sub-query routine which I tried but it took far to long to execute.  I'm wondering if you might have any suggestions that won't cause the sever to take a big hit?

 

Dan

 

PS: So you don't need to dig for it there is a data_added field in the osh table that contains the date the comment was added...I thought the max function might help but it didn't seem to.

Link to comment
Share on other sites

  • Replies 144
  • Created
  • Last Reply

it only looks at the ones with status 27, is that limitation still what you want ?

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

please remind me again in plain english what you want

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

please remind me again in plain english what you want

 

Let me take another run at this first Carine...by brain was working as I slept last night and I realized that the order status table has a date_added field so I'm wondering if the query can be turned around so it gets the last record from the order status history table and then joins the order table on the order_id just to pick up the customer name.  I'm thinking something like that might be the better way forward.

 

Dan

Link to comment
Share on other sites

@@burt

 

What is it you are trying to do Dan ?

 

Get the most recent comment of the most recent order that has an order status = 27 ?

 

Here is my original post Gary that probably explains it as best as I can.

 

http://www.oscommerce.com/forums/topic/410593-dashboards/?p=1752221

 

Dan

Link to comment
Share on other sites

I dumbed down my query to try and figure out why I couldn't get it to work the way I wanted.  Here is my dumbed down query that gives me the records from the orders table that I want to now join with the orders_status_history table so I can pick up the most recent comments.

SELECT o.orders_id, date_added, osh.comments
FROM orders o
JOIN orders_status_history osh
USING ( orders_id )
WHERE o.orders_status =27
AND last_modified <= date_sub( curdate( ) , INTERVAL 5 DAY )
AND osh.orders_status_id =27
LIMIT 0 , 30

I'm coming to the conclusion that I don't have a unique way to join the data with the orders_status_history table since there are multiple records for the same order_id and order_status_id in that table.  The only way I can think to do that is to some how join it where both the order_id and order_status_id match AND it is the most recent record added for that order using the date_added field.  What I can't figure out is how to actually get the most recent record based on that date added. Does anyone know if this is possible and if so, how?

 

Dan

Link to comment
Share on other sites


SELECT o.orders_id, date_added, osh.comments

FROM orders o

JOIN orders_status_history osh

USING ( orders_id )

WHERE o.orders_status = 27

AND last_modified <= date_sub( curdate( ) , INTERVAL 5 DAY )

and osh.orders_status_history_id = (select osh2.orders_status_history_id from orders_status_history osh2

where osh2.orders_id = osh.orders_id order by osh2.orders_status_history_id desc limit 1)

This is what seems to give correct results on my system, no grouping needed as there is a descending ordering on id with limit 1 in the subquery

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

Wow...it is sure nice to have a database wizard around.   That works perfectly. (w00t) 

 

I would never have figured that out.  In fact even looking at the subquery has me baffled.  :wacko: 

 

Thanks Carine. I really appreciate it...it was driving me nuts. :thumbsup:  

 

Dan

Link to comment
Share on other sites

Thanks to an enthusiastic supporter of osCommerce, who shared this code with me, we now have an iBar Email Dashboard module that will show you the number of unread Gmail emails pending in your service or help desk email account.

 

It looks like this...

 

post-182953-0-38009000-1489931550_thumb.jpg

 

For those who want to take it for a test drive here it is...

 

iBar Emails.zip

 

If you have any problems with it just let me know.

 

Dan

Link to comment
Share on other sites

@@Dan Cole in the emails zip you have the language file in both places (module and language locations)...

 

Thanks Gary....I wish I could say it was the first time I've ever mixed code and language files up but I'd have an awful long nose if I did. :D

 

Hopefully I got this one right.

 

iBar Emails.zip

 

Dan

Link to comment
Share on other sites

Just noticed a logic issue in this module when there are no unread emails.   This update should sort it out.

 

iBar Emails.zip

 

Dan

Link to comment
Share on other sites

For reasons that escape me now I added an iBar module to show the number of Facebook likes that you have and provide a link to your Facebook page.  To use it you'll need to obtain a Facebook APP ID which you can do here...

 

https://developers.facebook.com/docs/apps/register

 

Here is the module.

 

iBar FaceBook Likes.zip

 

Dan

Link to comment
Share on other sites

  • 9 months later...

Has anyone developed any new IBar dashboards?  If so,  show us what you've done.    Personally I haven't done much with them lately but the ones I did develop are still in use.  I find the whole concept of having a dashboard to monitor things quite helpful and motivating.   Here is what my dashboard currently looks like. DASHBOARD.thumb.jpg.2044337d6a1d869b546e80be1adc5830.jpg

Link to comment
Share on other sites

  • 1 month later...

@Dan Cole

Nice Dashboard you got there.
Something like that would be helpful to have. So I started by installing your iBar e-mails module.
I added the necessary info for my gmail account, I think, but I get this error.

Fatal error: Call to undefined function imap_open() in C:\xampp\htdocs\XXXXXXXXXX\XXXXXXXX\includes\modules\dashboard\d_iBar_emails.php on line 33

I run a local test shop on XAMPP using php 5.6.21.
Using osC BS Edge with following module settings

Enter the HTML link to your service or help desk email provider.
smtp.gmail.com

The IMAP hostname of your email provider.
imap.gmail.com

This is usually your email address.
*my gmail email address*

 

Something else;

THIS

$inbox = imap_open(MODULE_ADMIN_DASHBOARD_EMAILS_HOSTNAME, MODULE_ADMIN_DASHBOARD_EMAILS_USERNAME, MODULE_ADMIN_DASHBOARD_EMAILS_PASSWORD, OP_READONLY) or die('Cannot connect to Gmail: ' . imap_last_error());

SHOULD BE

$inbox = imap_open(MODULE_ADMIN_DASHBOARD_IBAR_EMAILS_HOSTNAME, MODULE_ADMIN_DASHBOARD_IBAR_EMAILS_USERNAME, MODULE_ADMIN_DASHBOARD_IBAR_EMAILS_PASSWORD, OP_READONLY) or die('Cannot connect to Gmail: ' . imap_last_error());

 

Link to comment
Share on other sites

So it looks like I got it working... somewhat.

I needed to activate IMAP support on my XAMPP.

Quote

Where is the IMAP support for PHP?

As default, the IMAP support for PHP is deactivated in XAMPP due to some mysterious initialization errors with some home versions like Windows 98. If you work with NT systems, you can open the file "\xampp\php\php.ini" to activate the php exstension by removing the beginning semicolon at the line ";extension=php_imap.dll". It should be:

extension=php_imap.dll

Now restart Apache and IMAP should work. You can use the same steps for every extension, which is not enabled in the default configuration.

 

Do you use bootstrap for the admin area? Looks like the whole css styling is missing
 

Link to comment
Share on other sites

God damn it when the hell are we gonna be able to edit posts even 15 min are past!!?? :dry:

Here my correction regarding the module settings

Enter the HTML link to your service or help desk email provider.
*link to gmail site*

The IMAP hostname of your email provider.
{imap.gmail.com:993/imap/ssl/novalidate-cert}INBOX

This is usually your email address.
*my gmail email address*

Link to comment
Share on other sites

26 minutes ago, Tsimi said:

I needed to activate IMAP support on my XAMPP.

@Tsimi Hi Lambros....glad you're having a look.  I don't know much about XAMPP so I can't help any there.

31 minutes ago, Tsimi said:

Do you use bootstrap for the admin area? Looks like the whole css styling is missing.

I'm not sure what you installed but I uploaded a package to the add-ons area awhile ago and there are instructions in it regarding the changes needed to get bootstrap loaded and the css as well.  Perhaps you just installed a zip upload that added an additional module some time afterwards.   If you've loaded the package from the add-ons area and are still having trouble let me know and I'll do what I can to help.

Dan

 

Link to comment
Share on other sites

I only installed the iBar emails module which I downloaded from this topic. 

Didn't know you released a package. I will have a look at your package that might answer all my other questions.

The XAMPP issue is solved. See my quote post above. Just needed to activate IMAP support. 

Thanks and well done!

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...