Get A Suspended Service That Has A Invoice Due Date In Future

[Blesta Addons]

Hello , i have this code that is working perfectly .

$this->Record = $this->getServices(null, "suspended");
 
  return $this->Record->
   innerJoin("invoice_lines", "invoice_lines.service_id", "=", "services.id", false)->
   innerJoin("invoices", "invoices.id", "=", "invoice_lines.invoice_id", false)->
   where("invoices.status", "in", array("active", "proforma"))->
   where("invoices.date_closed", "=", null)->
   where("invoices.date_due", ">=", $this->dateToUtc(date("c")))->  
   // where("services.date_suspended", ">=", "invoices.date_billed" )->
   where("client_groups.id", "=", $client_group_id)->
   group(array("services.id"))->
   fetchAll();

but i want to add a condition to check if exist another invoice that has due date in the past .

 

 

let say i have service wish have a two invoice , 1 has a future due date, and other has a old past due date .

 

in my query i don't want to be in the result .

[Tyson]

You're updating the services model?

 

I mentioned in your other thread that a check on suspended services with past due invoices will be a very slow operation.

 

You can write a subquery to fetch all service IDs for invoices that are past due and add a where condition to your existing query to fetch services that are not in that list. The subquery will be very slow, so you should limit the size of the result set as much as possible.

[Blesta Addons]

i want to make my own cronjob . v2.5 was thing that we can't live without it now , from it this case . i don't know how it work in v2.5  but it has a logic/code that do the work for us . example

 

service A , has invoice AA due date is today . invoice AB due date next week .

service B . has invoice BB due date today .

service C . has no invoices

 

in the cron job at 00:00 , the service A, B are suspended .

 

after 2 days  .

we change the invoice AA due date to next mounth .

we suspend the service C manually for sending spam .

 

in cron job at 00:00 , service A are unsuspended .

 

after 7 days

invoice CA has been regenerated auto for renewal for service C .

invoice AB has due date today .

in cron job at 00:00 , service C is still suspended , service A suspended .

 

in V3 some of this actions are not working

 

like the service C is unsuspended after regenerate a invoice for it !!!

 

really i don't matter if the slow query , but my concern is to get what it should do for me .

 

i will make/watch more cases that can happen in v3 .

 

NOTE : i'm talking about a system that has totally imported from v2 .

 

what i feel is that the log_services is empty after importing, maybe that is the case for that trouble we have .

[Tyson]

in V3 some of this actions are not working

 

like the service C is unsuspended after regenerate a invoice for it !!!

 

A service is unsuspended after an invoice is generated for it? If there are no past due invoices for it, then it could be unsuspended.

 

 

what i feel is that the log_services is empty after importing, maybe that is the case for that trouble we have .

 

Blesta does look at the `log_services` table when unsuspending a service, and the service would need to be in that table. Perhaps that is an issue for you from the import.

[Blesta Addons]

Blesta does look at the `log_services` table when unsuspending a service, and the service would need to be in that table. Perhaps that is an issue for you from the import.

 

but is not just my case, maybe anyone import from other system like whmc* can have this issue , so i think it worth a look from your part .

from my part , the import has been run, the move has been done definitely , and no option to back .