DB duplicate key error when deleting and shifting services

[tbar0970]

Database Error: _doQuery: [Error message: Could not execute statement]
[Last executed query: UPDATE service
                SET date = DATE_ADD(date, INTERVAL -7 DAY)
                WHERE date >= '2016-06-18'
                AND congregationid IN (10)
                ORDER BY date ASC]
[Native code: 1062]
[Native message: Duplicate entry '2016-06-12-10' for key 'datecong']

Line 54 of /home/jethro/code/2.13.2/include/general.php

Request: Array
(
   [view] => services__service_program
   [editing] => 1
   [congregations] => Array
       (
           [0] => 10
       )

   [start_date_d] => 1
   [start_date_m] => 6
   [start_date_y] => 2016
   [end_date_d] => 31
   [end_date_m] => 8
   [end_date_y] => 2016
   [program_submitted] => 1
   [delete_single] => 
   [delete_all_date] => 2016-06-18


   [new_service_date_d] => Array
       (
           [0] => 26
       )

   [new_service_date_m] => Array
       (
           [0] => 6
       )

   [new_service_date_y] => Array
       (
           [0] => 2016
       )

   [shift_after_delete] => Yes
)

[tbar0970]

We're deleting the services on the 18th June
and Jethro is shifting all the folloing services back by 7 days
but that means a service on the 19th June is being shifted back to the 12th June
which happens to have a service on it already.

May need to change the logic to
"shift all the services that are >= 7 days after back by 7 days."

[tbar0970]

@edwhiting maybe you could have a look at fixing that UPDATE query as mentioned above...

[tbar0970]

+1

[tbar0970]

Again

REQUEST: 
Array
(
    [view] => services__list_all
    [congregations] => Array
        (
            [0] => 1
            [1] => 2
        )

    [start_date_d] => 28
    [start_date_m] => 11
    [start_date_y] => 2018
    [end_date_d] => 28
    [end_date_m] => 4
    [end_date_y] => 2019
    [editing] => Edit Schedule
    [program_submitted] => 1
    [delete_single] => 
    [delete_all_date] => 2019-01-08
    [topic_title] => Array
...
    [shift_after_delete] => Yes
)

[tbar0970]

+1

[tbar0970]

Another one.

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2021-03-28-2' for key 'datecong'
Line 220 of /home/jethro/code/2.30.0/db_objects/service.class.php

Following a request to delete-all services on a given date and YES move following services to close the gap.

0] => Array
        (
            [file] => /home/jethro/code/2.30.0/db_objects/service.class.php
            [line] => 220
            [function] => query
            [class] => PDO
            [type] => ->
            [args] => Array
                (
                    [0] => UPDATE service
                                SET date = DATE_ADD(date, INTERVAL -7 DAY)
                                WHERE date >= '2021-04-02'
                                AND congregationid IN ('1', '2')
                                ORDER BY date ASC
                )

[tbar0970]

I can reproduce this when I create a mid-week service then delete it.

[tbar0970]

If I have services on Sunday 13th, Wednesday 16th and Sunday 20th
and I delete the services on Wednesday 16th,
Jethro tries to shift everything after 16th back by 7 days.
This creates a clash when it tries to move Sunday 20th's services back to the 13th, where there are already services.

The "move everything back" logic should only be available if there is 7 days between this service, the previous service, and the next service. Otherwise it should just delete the service and leave everything else alone.