TimemanVersion19Converter::updateRecords

private function updateRecords(array $records)
{
	$utcOffsets = [];
	foreach ($records as $record)
	{
		$utcOffsets[date('Z', strtotime($record['DATE_START']))][] = (int)$record['ID'];
	}

	foreach ($utcOffsets as $offsetSeconds => $entriesIds)
	{
		$reportIds = array_column(
			WorktimeReportTable::query()
				->registerRuntimeField(new ExpressionField('RID', 'MIN(ID)'))
				->addSelect('RID')
				->whereIn('REPORT_TYPE', ['ERR_OPEN', 'ERR_CLOSE'])
				->whereIn('ENTRY_ID', $entriesIds)
				->addGroup('REPORT_TYPE')
				->addGroup('ENTRY_ID')
				->exec()
				->fetchAll(),
			'RID'
		);
		$connection = Application::getConnection();
		$offsetWithLeadZero = TimeHelper::getInstance()->getFormattedOffset($offsetSeconds);
		$dateFormat = '"%Y-%m-%dT%H:%i:%s' . $offsetWithLeadZero . '"';
		$reportLike = '"%;%' . $offsetWithLeadZero . ';%"';
		$connection->query('SET time_zone = "' . TimeHelper::getInstance()->getFormattedOffset($offsetSeconds, false) . '";');
		if (empty($reportIds))
		{
			$connection->query('
				UPDATE `b_timeman_entries` e
				SET 
					e.TIMESTAMP_X = e.TIMESTAMP_X,

					e.RECORDED_START_TIMESTAMP = IF(
						e.RECORDED_START_TIMESTAMP != 0, 
						e.RECORDED_START_TIMESTAMP, 
						UNIX_TIMESTAMP(e.DATE_START)
					),
					e.ACTUAL_START_TIMESTAMP = IF(
						e.ACTUAL_START_TIMESTAMP != 0, 
						e.ACTUAL_START_TIMESTAMP, 
						UNIX_TIMESTAMP(e.DATE_START)
					),
	
					e.RECORDED_STOP_TIMESTAMP = IF(
						e.RECORDED_STOP_TIMESTAMP != 0, 
						e.RECORDED_STOP_TIMESTAMP, 
						IF(e.CURRENT_STATUS = "CLOSED", UNIX_TIMESTAMP(e.DATE_FINISH), 0)
					),
					e.ACTUAL_STOP_TIMESTAMP = IF(
						e.ACTUAL_STOP_TIMESTAMP != 0, 
						e.ACTUAL_STOP_TIMESTAMP, 
						IF(e.CURRENT_STATUS = "CLOSED", UNIX_TIMESTAMP(e.DATE_FINISH), 0)
					),
					e.RECORDED_DURATION = IF(
						e.RECORDED_DURATION != 0, 
						e.RECORDED_DURATION, 
						IF(e.CURRENT_STATUS = "PAUSED", UNIX_TIMESTAMP(e.DATE_FINISH) - UNIX_TIMESTAMP(e.DATE_START), 0)
					)
				WHERE 
					e.ID IN (' . implode(', ', $entriesIds) . ');
			');
		}
		else
		{
			$connection->query('
				UPDATE `b_timeman_entries` e
				LEFT JOIN `b_timeman_reports` rstart ON rstart.ENTRY_ID = e.id AND rstart.REPORT_TYPE = "ERR_OPEN" 
				LEFT JOIN `b_timeman_reports` rend ON rend.ENTRY_ID = e.id AND rend.REPORT_TYPE ="ERR_CLOSE"
				SET
					e.TIMESTAMP_X = e.TIMESTAMP_X,
					e.RECORDED_START_TIMESTAMP = IF(
						e.RECORDED_START_TIMESTAMP != 0, 
						e.RECORDED_START_TIMESTAMP, 
						UNIX_TIMESTAMP(e.DATE_START)
					),
					e.ACTUAL_START_TIMESTAMP = IF(
						e.ACTUAL_START_TIMESTAMP != 0, 
						e.ACTUAL_START_TIMESTAMP, 
						IF(
							rstart.REPORT IS NOT NULL AND rstart.REPORT LIKE ' . $reportLike . ', 
							UNIX_TIMESTAMP(
								STR_TO_DATE(
									SUBSTRING_INDEX(SUBSTRING_INDEX(rstart.REPORT, ';', 2), ';', -1)
									, ' . $dateFormat . '
								)
							), 
							UNIX_TIMESTAMP(e.DATE_START) 
						)
					),
	
					e.RECORDED_STOP_TIMESTAMP = IF(
						e.RECORDED_STOP_TIMESTAMP != 0, 
						e.RECORDED_STOP_TIMESTAMP, 
						IF(e.CURRENT_STATUS = "CLOSED", UNIX_TIMESTAMP(e.DATE_FINISH), 0)
					),
					e.ACTUAL_STOP_TIMESTAMP = IF(
						e.ACTUAL_STOP_TIMESTAMP != 0, 
						e.ACTUAL_STOP_TIMESTAMP, 
						IF(e.CURRENT_STATUS = "CLOSED", IF(
							rend.REPORT IS NOT NULL AND rend.REPORT LIKE ' . $reportLike . ', 
							UNIX_TIMESTAMP(
								STR_TO_DATE(
									SUBSTRING_INDEX(SUBSTRING_INDEX(rend.REPORT, ';', 2), ';', -1)
									, ' . $dateFormat . '
								)
							), 
							UNIX_TIMESTAMP(e.DATE_FINISH) 
						), 0)
					),
					e.RECORDED_DURATION = IF(
						e.RECORDED_DURATION != 0, 
						e.RECORDED_DURATION, 
						IF(e.CURRENT_STATUS = "PAUSED", UNIX_TIMESTAMP(e.DATE_FINISH) - UNIX_TIMESTAMP(e.DATE_START), 0)
					)

				WHERE 
					e.ID IN (' . implode(', ', $entriesIds) . ')
					AND (rstart.ID IS NULL OR rstart.ID IN (' . implode(', ', $reportIds) . '))
					AND (rend.ID  IS NULL OR rend.ID IN (' . implode(', ', $reportIds) . '));
			');
		}
	}
}