Search the Community

When you have a blesta setup with relatively high client volume, you may want to periodically check if everything is OK, or clean up things that should not be there (i.e: data that is not automatically removed by blesta/plugins). I've come up to this queries which I use periodically: - Show users that have at least 1 active service and more than 3 unpaid invoices: This may happen when a provision plugin is not suspending a service, or just human mistake (useful to find customers that are not paying because of not having their services suspended) SELECT many_invoices_customers.id 'customer_id', services.id_value 'service_id' from (SELECT i.client_id 'id', count(*) 'c' FROM invoices i, clients c WHERE c.id = i.client_id AND i.status != 'void' and i.paid != i.total GROUP BY i.client_id HAVING c > 3) many_invoices_customers, services WHERE services.client_id = many_invoices_customers.id AND services.status = 'active'; - Show users that have more than 3 unpaid invoices and at least 1 suspended service: let's you clean up users that don't pay anymore (and manually cancel them) SELECT many_invoices_customers.id 'customer_id', services.id_value 'service_id', many_invoices_customers.c 'invoices_number' from (SELECT i.client_id 'id', count(*) 'c' FROM invoices i, clients c WHERE c.id = i.client_id AND i.status != 'void' and i.paid != i.total GROUP BY i.client_id HAVING c > 3) many_invoices_customers, services WHERE services.client_id = many_invoices_customers.id AND services.status = 'suspended' ORDER BY many_invoices_customers.c DESC; I haven't implemented auto service cancel yet, but maybe is useful to someone here *Those queries are meant to be executed directly into your blesta MySQL DB.