update mysql attribute according to a day (12/12/2019) cakephp, mysql

Question

I have the following problem:

If a client does not pay a certain portion, he would like the system (mysql+cakephp) to place the client with the debtor status. would have some way to automate this, without the employee manually changing the customer’s status?

I would like the mysql itself daily to make a query in the bank and change the status of clients who did not pay until that given day to (late) there when the employee entered the system would already come clients who are in arrears. the part of the application is quiet for me my doubt is as I do to automate this logic of the bank

Answer 1

You can create an event.

An event is a task executed based on a predefined schedule, so it is sometimes referred to as a scheduled event.

A procedure is executed only when it is called directly, a trigger is executed when an event associated with a table occurs, such as an insertion, update or deletion event. An event can be run at regular time intervals, one or more times. The event is also known as "temporal trigger" because it is triggered by time.

An event is similar to a cron activity on UNIX or a task scheduler on Windows.

You can use events in many cases, such as optimizing database tables, clearing logs, archiving data, or generating complex reports outside peak hours.

Starting the event scheduler in Mysql.

By default, the event scheduler is not enabled. To enable and start scheduling events, you must execute the following command:

SET GLOBAL event_scheduler = ON;

To disable the event scheduler run the command:

SET GLOBAL event_scheduler = OFF;

Creating new Mysql events

Creating an event is similar to creating other database objects, such as procedures or triggers.

An event is a named object that contains SQL statements.

Syntax.

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    nome_do_evento
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO corpo_do_event;

schedule:
    AT timestamp [+ INTERVAL intervalo] ...
  | EVERY intervalo
    [STARTS timestamp [+ INTERVAL intervalo] ...]
    [ENDS timestamp [+ INTERVAL intervalo] ...]

intervalo:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

CREATE EVENT requires the privilege EVENT for the scheme in which the event should be created.

The clause ON SCHEDULE determines when and how often and for how long the defined action in defined corpo_do_event repeats. This clause takes one of two forms:

• AT timestamp which is used for a single event. It specifies that the event is executed only once on the date and time provided by timestamp or must be an expression that is resolved to a date and time value. You can use a value of type DATETIME or TIMESTAMPfor that purpose.
• EVERY which is used to repeat actions in a regular interval. The clause EVERY may contain an `STARTS clause followed by a timestamp indicating when the action should begin to repeat. It may also contain an ENDS clause followed by a timestamp that tells Mysql when the event should stop repeating.

Minimum requirements for making a declaration CREATE EVENT valid are the following:

• The keywords CREATE EVENT plus a unique event name, which uniquely identifies the event in a database schema.
• A clause ON SCHEDULE determining when and how often the event runs.
• A clause DO which contains the SQL statement to be executed by event.

To discard the event use the statement DROP EVENT:

DROP EVENT [IF EXISTS] nome_do_evento

For more details about the syntax of CREATE EVENT https://dev.mysql.com/doc/refman/8.0/en/create-event.html

For more details about the syntax of DROP EVENT
https://dev.mysql.com/doc/refman/8.0/en/drop-event.html

Answer

Applying your question what I can offer is a superficial example of an event that every day checks the customer table and if the payment is 0 it changes the status of the customer to debtor as no specific data were presented on the format of the data in your question:

CREATE EVENT verificarEstatus
    ON SCHEDULE EVERY DAY STARTS CURRENT_TIMESTAMP
    DO
      UPDATE alugueis.clientes SET status = "DEVEDOR" WHERE pagamento = 0;

This event starts the verification on 12/12/2019:

CREATE EVENT verificarEstatus
    ON SCHEDULE EVERY DAY STARTS TIMESTAMP('2019-12-12')
    DO
      UPDATE alugueis.clientes SET status = "DEVEDOR" WHERE pagamento = 0;

This event starts today the check and ends on 12/12/2019:

CREATE EVENT verificarEstatus
    ON SCHEDULE EVERY DAY STARTS CURRENT_TIMESTAMP
                ENDS TIMESTAMP('2019-12-12')
    DO
      UPDATE alugueis.clientes SET status = "DEVEDOR" WHERE pagamento = 0;