Sql For Pending Service With A Paid Invoice

[drolfe]

Hi,

 

I'm looking for an sql query that will show me pending services that have a paid invoice.

 

This is for some custom alarming, basically we don't want to get alerted when a new order comes through, only once the new order/service is paid for.

 

Any help on a sql query for this would be most appreciated.

 

Regards, Daniel

[Michael]

  On 4/12/2015 at 3:13 PM, drolfe said:

Hi,

 

I'm looking for an sql query that will show me pending services that have a paid invoice.

 

This is for some custom alarming, basically we don't want to get alerted when a new order comes through, only once the new order/service is paid for.

 

Any help on a sql query for this would be most appreciated.

 

Regards, Daniel

 

If you have the Billing Order widget that shows you.

 

Billing > Manage Widgets > Order widget

 

On the Pending if it's a green tick, the invoice has been paid.

 

You could use the order email to get an order and then an email when a payment is made, that's what I do.

 

Payments made => My info > Notices > Payment received.

[drolfe]

  On 4/12/2015 at 3:27 PM, Licensecart said:

If you have the Billing Order widget that shows you.

 

Billing > Manage Widgets > Order widget

 

On the Pending if it's a green tick, the invoice has been paid.

 

You could use the order email to get an order and then an email when a payment is made, that's what I do.

 

Payments made => My info > Notices > Payment received.

 

Hi Thanks for that, I didn't know about the order widget.

 

I still need the sql for some SMS notifications, I'll look into the DB a little more

 

Regards, Daniel

[drolfe]

Ok , SQL isn't my thing so this almost melted my brain but I think I have something with this one

SELECT COUNT(*) FROM orders INNER JOIN invoices ON orders.invoice_id = invoices.id WHERE orders.status = "pending" AND invoices.date_closed IS NOT NULL;

If anyone else has anything better or knows more about SQL ?

 

Regards, Daniel

[serge]

I think if you want to get data result, :

- you can remove "COUNT(*)", because if not your query will just give you the number of line that match to your query (just the number like "3")

- you will need to fletch the result, and Blesta do have maybe his own data base communication abstraction to do it, you could check in the code how Blesta is doing it