Simulate Database Update with Select

Question

How I could make a select in the table below (Mysql) to solve the method (Java)?

re         data        is_folga
10     01/01/2017         0
10     02/01/2017         0 
10     03/01/2017         0
10     04/01/2017         0
10     05/01/2017         1
10     06/01/2017         1
10     07/01/2017         0
10     08/01/2017         0 
10     09/01/2017         0
10     10/01/2017         0
10     11/01/2017         1
10     12/01/2017         1
20     01/01/2017         0
20     02/01/2017         0 
20     03/01/2017         0
20     04/01/2017         1
20     05/01/2017         1
20     06/01/2017         0
20     07/01/2017         0
20     08/01/2017         0 
20     09/01/2017         0
20     10/01/2017         1
20     11/01/2017         1
20     12/01/2017         0

public boolean trocaFolga(int re_A, int re_B, String data_A, String data_B){
boolean ok = false;

....
//fazer um select que verifique quantos dias o re_A trabalharia sem folga se a troca for feita 
//e o mesmo select para o re_B

  if(retorno do select < 7){
       ok=true;
    }
....

return ok;
}

Example:

I am the re 10 I want to change the day 04/01 by the day 04/01 of the re 20. If it is made the exchange, me or re 20 we will stay more than 7 days without slack?

Answer 1

First, I considered that the structure of the table is this:

CREATE TABLE tabela (
  re INTEGER NOT NULL,
  data DATE NOT NULL,
  is_folga BIT NOT NULL,
  PRIMARY KEY(re, data)
);

In particular, please note that data is the type DATE, and not VARCHAR. That is important.

I think the solution would be this:

private static final String URL = "jdbc:mysql://localhost/folgas";
private static final String USERNAME = "root";
private static final String SENHA = "root";

private static final String SQL_FOLGAS = ""
        "SELECT a.is_folga "
        "FROM tabela a "
        "WHERE a.re = ? "
        "AND a.data >= DATE_ADD(?, INTERVAL 6 DAY) "
        "AND a.data <= DATE_SUB(?, INTERVAL 6 DAY) "
        "ORDER BY a.data";

private Connection conectar() throws SQLException {
    return DriverManager.getConnection(URL, USERNAME, SENHA);
}

private static enum ResultadoFolga {
    NAO_PODE_TROCAR,
    VAI_FOLGAR,
    NAO_VAI_FOLGAR,
}

private ResultadoFolga trocaFolgas(Connection c, int re, LocalDate data)
        throws SQLException
{
    try (PreparedStatement ps = c.prepareStatement(SQL_FOLGAS)) {
        ps.setInt(1, re);
        ps.setDate(2, java.sql.Date.valueOf(data));
        ps.setDate(3, java.sql.Date.valueOf(data));
        int consecutivos = 0;
        int contador = 0;
        boolean folgandoNoDia = false;
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                boolean folga = rs.getInt(1) == 1;
                if (folga != (contador == 6)) {
                    consecutivos++;
                } else {
                    consecutivos = 0;
                }
                if (consecutivos == 7) return ResultadoFolga.NAO_PODE_TROCAR;
                if (contador == 6 && folga) folgandoNoDia = true;
                contador++;
            }
            return folgandoNoDia
                    ? ResultadoFolga.NAO_VAI_FOLGAR
                    : ResultadoFolga.VAI_FOLGAR;
        }
    }
}

public boolean trocaFolgas(int reA, int reB, LocalDate dataA, LocalDate dataB)
        throws SQLException
{
    try (Connection c = conectar()) {
        ResultadoFolga a = trocaFolgas(c, reA, dataA);
        ResultadoFolga b = trocaFolgas(c, reB, dataB);
        return a != ResultadoFolga.NAO_PODE_TROCAR
                && b != ResultadoFolga.NAO_PODE_TROCAR
                && a != b;
    }
}

If I understand your question correctly, no one can stay seven or more consecutive days without rest. That is, there cannot be 7 zeros in a row if the exchange is made.

The idea then is to check the employee when doing the SELECT 13-field is_folga of consecutive days, going from 6 days before the day off until 6 days later, and changing the value of the desired day, and see if there are 7 consecutive zeros. It is sufficient to check 6 backwards and 6 forwards, because if the first day of the 7 days off is the desired date, the last day would be 6 days later. Similarly, if the last day of the 7 days without leave is that date, then the first day would be 6 days before.

There is no need to go further because if there is a sequence of 7 or more days without leave before or after that period, it will already be far enough so that the exchange on the desired date cannot change that situation. If you ensure that the database will never contain 7 consecutive zeroes, this procedure will ensure that the exchange will not include this. But if you cannot guarantee this, the above procedure also cannot guarantee it.

This passage requires better explanations:

        int consecutivos = 0;
        int contador = 0;
        boolean folgandoNoDia = false;
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                boolean folga = rs.getInt(1) == 1;
                if (folga != (contador == 6)) {
                    consecutivos++;
                } else {
                    consecutivos = 0;
                }
                if (consecutivos == 7) return ResultadoFolga.NAO_PODE_TROCAR;
                if (contador == 6 && folga) folgandoNoDia = true;
                contador++;
            }
            return folgandoNoDia
                    ? ResultadoFolga.NAO_VAI_FOLGAR
                    : ResultadoFolga.VAI_FOLGAR;
        }

First, there are 3 possible results determined by Enum ResultadoFolga. This is because the results are three:

• You can’t make the trade;
• You can make the exchange and the re who would take the day off will no longer go and;
• You can make the exchange and the re who wouldn’t take the day off, now will.

What happens here is that we have the contador which will count the records read, going from 0 on the first record to 12 on the last. The contador == 6 will only be true on the seventh day (remember that the first is 0, then the seventh will be 6). That day will be just the day desired for the realization of the exchange.

The boolean folga = rs.getInt(1) == 1; causes the variable to folga receive true if the read value is equal to 1 and false if it’s not.

In the days that are not of the realization of the exchange, contador != 6 is assessed as true, and then folga == true is evaluated only as folga (if folga for false, this will result in false, and if folga for true, this will result in true). Already on the day of the exchange ,contador != 6 is assessed as false, and then folga == false will be equivalent to !folga, that is, it will be the inversion of the value read from the database. That is, this then evaluates whether the day is off according to the database, but reversing the value read on the desired day. Therefore, this determines whether after the reversal, it is a day off.

If after done the desired inversion, if this is a day off, consecutivos goes to zero, after all it is no longer a consecutive working day. Otherwise it counts as another consecutive working day, and therefore consecutivos++. When the value of consecutivos reach 7, we then have 7 consecutive days of work (if done the exchange), and therefore return ResultadoFolga.NAO_PODE_TROCAR, indicating that the exchange cannot be made.

If after all these 13 days and do not occur consecutivos reach 7 at no time, then the exchange will not introduce a sequence of 7 consecutive days without off. But we still need to know if re will work or not on that day. That’s where the variable comes in folgandoNoDia. It is initialized as false. If on the day contador == 6, it’s true, so it’s because according to the database, that would be a day off, and then folgandoNoDia becomes true.

In the end, after the 13 days, supposing that he did not find 7 consecutive days of work and fell in the ResultadoFolga.NAO_PODE_TROCAR, we return ResultadoFolga.VAI_FOLGAR or ResultadoFolga.NAO_VAI_FOLGAR depending on reverse of the value if the re is or is not take the day off. If it will merge, will no longer relax with the exchange. If it would not merge, then will play now.

Looking then at the case of two employees, we do this process for both separately (and we call the results of a and b) and then we check if the exchange is valid as follows: If any of them cannot exchange, then the exchange is not possible and return false. If both can exchange, then it is necessary for one to take the day off and the other not (otherwise it is not an exchange), and this is true when we have a != b.

To consult the database, I use the Try-with-Resources to manage the Connection, the PreparedStatement and the ResultSet. There’s only one Connection, but two queries are performed in the database (one for each re) and with that we have two PreparedStatements and two ResultSets. The fields URL, USERNAME and SENHA are the connection parameters with the database. The processing of any SQLException resulting (this means error when connecting or consulting the database) is on your own.

For dates, instead of using strings, I’m using a native and specialized type used to represent dates, the LocalDate. I do it because using strings to represent dates is bad programming practice. To learn more about the LocalDate, see that question.

It is possible to do all this directly in SQL, but the resulting SQL would be quite complicated, probably would have to be tied to Mysql/Mariadb (ie it would not be portable to other databases) and this would reduce little of the complexity on the Java side.

If the database table has any gaps on any date (for example, for re number 123, it jumps from day 14 to day 16), it will go wrong. It is important that you do not let the database have such gaps. In particular, the worst case is when the consulted date is not in the database. The code does not verify these cases. It’s not too difficult to add these checks, but it will make the code a little more complicated (basically, make sure that there are exactly 13 lines in the result and decide what to do if there is no).