https://pastein.ru/t/m6M
Загрузка данных
package org.example.service;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.example.model.CsvRow;
import org.example.model.OldProfileData;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
@Slf4j
@Service
public class ExcelReportService {
@Autowired
private UriNormalizer uriNormalizer;
private static final String DOC_FOLDER = "/home/retb_test/PPRB_OTP/Tools/GeneratorProfile" + "/DocumentForTheScript";
private static final String TEMPLATE_FILE = "Анкета ЕФС web и mobile.xlsx";
private static final String STATIC_OPERATION_ID = "UC00_StaticJS_1_WEB";
private static final String GET_CONTACTS_OPERATION_ID = "UC29_GET_contacts_WEB";
// Индексы колонок для WEB (8 колонок)
private static final int COL_SCRIPT = 0;
private static final int COL_SERVICE_WEB = 1;
private static final int COL_TPH_WEB = 2;
private static final int COL_SLA_WEB = 3;
private static final int COL_LOGIN_WEB = 4;
private static final int COL_THREADS_WEB = 5;
private static final int COL_PACING_WEB = 6;
private static final int COL_ITERATIONS_WEB = 7;
// Индексы колонок для MOBILE (8 колонок)
private static final int COL_SCRIPT_MOB = 0;
private static final int COL_SERVICE_MOB = 1;
private static final int COL_TPH_MOB = 2;
private static final int COL_SLA_MOB = 3;
private static final int COL_LOGIN_MOB = 4;
private static final int COL_THREADS_MOB = 5;
private static final int COL_PACING_MOB = 6;
private static final int COL_ITERATIONS_MOB = 7;
public void generateExcelReport(List<CsvRow> newRows, String projectType,
Map<String, Integer> oldIntensityByService,
Set<String> oldServices,
Set<String> extraInScript,
Map<String, OldProfileData> lastProfileData) {
try {
File templateFile = new File(DOC_FOLDER, TEMPLATE_FILE);
String pagePrefix = "WEB".equals(projectType) ? "WEB" : "MOB";
Workbook workbook;
if (templateFile.exists()) {
workbook = new XSSFWorkbook(new FileInputStream(templateFile));
} else {
workbook = new XSSFWorkbook();
}
// Удаляем пустые страницы
for (int i = workbook.getNumberOfSheets() - 1; i >= 0; i--) {
String sheetName = workbook.getSheetName(i);
if (sheetName.startsWith(pagePrefix) && workbook.getSheetAt(i).getLastRowNum() == 0) {
workbook.removeSheetAt(i);
}
}
Sheet lastSheet = findLastSheetByPrefix(workbook, pagePrefix);
int nextReleaseNumber;
String newSheetName;
if (lastSheet == null) {
nextReleaseNumber = 5038;
newSheetName = pagePrefix + " 5.038";
} else {
int lastReleaseNumber = extractReleaseNumber(lastSheet.getSheetName());
nextReleaseNumber = lastReleaseNumber + 1;
newSheetName = pagePrefix + " " + formatReleaseNumber(nextReleaseNumber);
}
Sheet newSheet = workbook.createSheet(newSheetName);
if (lastSheet != null) {
copySheetStructure(lastSheet, newSheet);
removeDelayAndFixHeaders(newSheet, projectType);
removeHeaderRowIfExists(newSheet);
} else {
createHeaderRow(newSheet, projectType);
}
fillSheetWithData(newSheet, newRows, oldIntensityByService, oldServices, projectType, extraInScript, lastProfileData);
removeHeaderRowIfExists(newSheet);
try (FileOutputStream fos = new FileOutputStream(templateFile)) {
workbook.write(fos);
}
workbook.close();
log.info(" Excel отчёт обновлён: добавлена страница {}", newSheetName);
} catch (IOException e) {
log.error("Ошибка при создании Excel отчёта: {}", e.getMessage());
}
}
private void removeDelayAndFixHeaders(Sheet sheet, String projectType) {
Row oldHeaderRow = sheet.getRow(0);
if (oldHeaderRow != null) {
sheet.removeRow(oldHeaderRow);
}
Row newHeaderRow = sheet.createRow(0);
String[] headers;
if ("MOBILE".equals(projectType)) {
headers = new String[]{"Скрипт", "Вызываемый сервис", "TPH", "SLA", "Логинов в час",
"Количество потоков", "Pacing", "Количество итераций"};
} else {
headers = new String[]{"Скрипт", "Вызываемый сервис", "TPH", "SLA", "Логинов в час",
"Количество потоков", "Pacing", "Количество итераций"};
}
for (int i = 0; i < headers.length; i++) {
Cell cell = newHeaderRow.createCell(i);
cell.setCellValue(headers[i]);
sheet.setColumnWidth(i, (i == 7 || i == 8) ? 8000 : 5000);
}
Font headerFont = sheet.getWorkbook().createFont();
headerFont.setBold(true);
CellStyle headerStyle = sheet.getWorkbook().createCellStyle();
headerStyle.setFont(headerFont);
setBorders(headerStyle);
for (int i = 0; i < headers.length; i++) {
newHeaderRow.getCell(i).setCellStyle(headerStyle);
}
int maxCol = headers.length;
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row != null) {
for (int c = row.getLastCellNum() - 1; c >= maxCol; c--) {
Cell cell = row.getCell(c);
if (cell != null) {
row.removeCell(cell);
}
}
}
}
}
private void removeHeaderRowIfExists(Sheet sheet) {
for (int r = sheet.getLastRowNum(); r >= 0; r--) {
Row row = sheet.getRow(r);
if (row == null) continue;
for (int c = 0; c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null) {
String cellValue = null;
try {
cellValue = cell.getStringCellValue();
} catch (IllegalStateException e) {
continue;
}
if (cellValue != null && cellValue.contains("Наименование операции")) {
sheet.shiftRows(r + 1, sheet.getLastRowNum(), -1);
log.info(" Удалена строка с содержимым: {}", cellValue);
break;
}
}
}
}
}
private Sheet findLastSheetByPrefix(Workbook workbook, String prefix) {
Sheet lastSheet = null;
int maxRelease = -1;
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
String sheetName = workbook.getSheetName(i);
if (sheetName.startsWith(prefix)) {
int releaseNum = extractReleaseNumber(sheetName);
if (releaseNum > maxRelease) {
maxRelease = releaseNum;
lastSheet = workbook.getSheetAt(i);
}
}
}
return lastSheet;
}
private int extractReleaseNumber(String sheetName) {
Pattern pattern = Pattern.compile("(\\d+)\\.(\\d+)");
Matcher matcher = pattern.matcher(sheetName);
if (matcher.find()) {
int major = Integer.parseInt(matcher.group(1));
int minor = Integer.parseInt(matcher.group(2));
return major * 1000 + minor;
}
return 0;
}
private String formatReleaseNumber(int releaseNumber) {
int major = releaseNumber / 1000;
int minor = releaseNumber % 1000;
return major + "." + String.format("%03d", minor);
}
private void createHeaderRow(Sheet sheet, String projectType) {
Row headerRow = sheet.createRow(0);
String[] headers;
if ("MOBILE".equals(projectType)) {
headers = new String[]{"Скрипт", "Вызываемый сервис", "TPH", "SLA", "Логинов в час",
"Количество потоков", "Pacing", "Количество итераций"};
} else {
headers = new String[]{"Скрипт", "Вызываемый сервис", "TPH", "SLA", "Логинов в час",
"Количество потоков", "Pacing", "Количество итераций"};
}
for (int i = 0; i < headers.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(headers[i]);
sheet.setColumnWidth(i, (i == 7 || i == 8) ? 8000 : 5000);
}
Font headerFont = sheet.getWorkbook().createFont();
headerFont.setBold(true);
CellStyle headerStyle = sheet.getWorkbook().createCellStyle();
headerStyle.setFont(headerFont);
setBorders(headerStyle);
for (int i = 0; i < headers.length; i++) {
headerRow.getCell(i).setCellStyle(headerStyle);
}
}
private void copySheetStructure(Sheet source, Sheet target) {
if (source.getRow(0) != null) {
for (int i = 0; i < source.getRow(0).getLastCellNum(); i++) {
target.setColumnWidth(i, source.getColumnWidth(i));
}
}
for (int r = 0; r <= source.getLastRowNum(); r++) {
Row sourceRow = source.getRow(r);
if (sourceRow != null) {
Row targetRow = target.createRow(r);
for (int c = 0; c < sourceRow.getLastCellNum(); c++) {
Cell sourceCell = sourceRow.getCell(c);
if (sourceCell != null) {
Cell targetCell = targetRow.createCell(c);
switch (sourceCell.getCellType()) {
case STRING:
targetCell.setCellValue(sourceCell.getStringCellValue());
break;
case NUMERIC:
targetCell.setCellValue(sourceCell.getNumericCellValue());
break;
case BOOLEAN:
targetCell.setCellValue(sourceCell.getBooleanCellValue());
break;
case FORMULA:
targetCell.setCellFormula(sourceCell.getCellFormula());
break;
default:
targetCell.setCellValue("");
}
CellStyle newStyle = target.getWorkbook().createCellStyle();
newStyle.cloneStyleFrom(sourceCell.getCellStyle());
targetCell.setCellStyle(newStyle);
}
}
}
}
}
private void fillSheetWithData(Sheet sheet, List<CsvRow> newRows,
Map<String, Integer> oldIntensityByService,
Set<String> oldServices,
String projectType,
Set<String> extraInScript,
Map<String, OldProfileData> lastProfileData) {
// ========== СИНХРОНИЗАЦИЯ СТАТИКИ С UC29 (ДЛЯ WEB) ==========
if ("WEB".equals(projectType)) {
CsvRow contactsRow = null;
CsvRow staticRow = null;
int contactsIndex = -1;
int staticIndex = -1;
for (int i = 0; i < newRows.size(); i++) {
CsvRow row = newRows.get(i);
if (GET_CONTACTS_OPERATION_ID.equals(row.getName())) {
contactsRow = row;
contactsIndex = i;
} else if (STATIC_OPERATION_ID.equals(row.getName())) {
staticRow = row;
staticIndex = i;
}
}
if (contactsRow != null && staticRow != null) {
int contactsIntensity = contactsRow.getIntensity();
if (contactsIntensity != staticRow.getIntensity()) {
CsvRow updatedStaticRow = new CsvRow(
staticRow.getName(),
staticRow.getUri(),
staticRow.getMethod(),
contactsIntensity,
staticRow.getSla(),
staticRow.getTransactionName(),
staticRow.getRequirement(),
staticRow.getComment()
);
newRows.set(staticIndex, updatedStaticRow);
log.info(" Excel: синхронизирована интенсивность {} -> {} (как у {})",
STATIC_OPERATION_ID, contactsIntensity, GET_CONTACTS_OPERATION_ID);
}
}
}
// ========== ДЛЯ MOBILE: полная перезапись страницы ==========
if ("MOBILE".equals(projectType)) {
for (int r = sheet.getLastRowNum(); r >= 1; r--) {
Row row = sheet.getRow(r);
if (row != null) {
sheet.removeRow(row);
}
}
Row row2 = sheet.getRow(2);
if (row2 != null) {
boolean isEmpty = true;
for (int c = 0; c < row2.getLastCellNum(); c++) {
Cell cell = row2.getCell(c);
if (cell != null && cell.getCellType() != CellType.BLANK) {
isEmpty = false;
break;
}
}
if (isEmpty) {
if (sheet.getLastRowNum() >= 2) {
sheet.shiftRows(3, sheet.getLastRowNum(), -1);
}
}
}
log.info(" MOBILE страница очищена, будет создано {} новых строк", newRows.size());
Workbook workbook = sheet.getWorkbook();
CellStyle defaultStyle = workbook.createCellStyle();
setBorders(defaultStyle);
CellStyle goodStyle = workbook.createCellStyle();
goodStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
goodStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
setBorders(goodStyle);
CellStyle badStyle = workbook.createCellStyle();
badStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
badStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
setBorders(badStyle);
int currentRow = 1;
for (CsvRow csvRow : newRows) {
Row dataRow = sheet.createRow(currentRow);
String redKey = csvRow.getMethod() + "|" + csvRow.getUri();
if (csvRow.getIntensity() == 0) {
log.info("MOBILE: найдена операция с 0 - метод={}, uri={}, redKey={}",
csvRow.getMethod(), csvRow.getUri(), redKey);
log.info("MOBILE: extraInScript.contains(redKey) = {}",
extraInScript != null && extraInScript.contains(redKey));
log.info("MOBILE: oldServices.contains(serviceKey) = {}",
oldServices != null && oldServices.contains(
uriNormalizer.normalizeService(csvRow.getMethod(), csvRow.getUri(), projectType)));
}
boolean shouldBeRed = csvRow.getIntensity() == 0
&& extraInScript != null
&& extraInScript.contains(redKey);
if (csvRow.getIntensity() == 0) {
log.debug("MOBILE: shouldBeRed = {}", shouldBeRed);
}
if (shouldBeRed) {
log.info(" MOBILE: красим красным {} {} (интенсивность=0)",
csvRow.getMethod(), csvRow.getUri());
}
String serviceKey = uriNormalizer.normalizeService(csvRow.getMethod(), csvRow.getUri(), projectType);
boolean isNew = oldServices != null && !oldServices.contains(serviceKey)
&& (lastProfileData == null || !lastProfileData.containsKey(csvRow.getName()));
Integer oldTph = oldIntensityByService != null ? oldIntensityByService.get(serviceKey) : null;
boolean isIntensityChanged = oldTph != null && !oldTph.equals(csvRow.getIntensity()) && csvRow.getIntensity() > 0;
int currentTph = csvRow.getIntensity();
int iterations = shouldBeRed ? 1 : calculateIterations(currentTph);
int threads = shouldBeRed ? 1 : calculateThreads(currentTph);
CellStyle rowStyle;
if (shouldBeRed) {
rowStyle = badStyle;
} else if (isNew) {
rowStyle = goodStyle;
} else {
rowStyle = defaultStyle;
}
Cell cellScript = dataRow.createCell(COL_SCRIPT_MOB);
cellScript.setCellValue(csvRow.getName());
cellScript.setCellStyle(rowStyle);
Cell cellService = dataRow.createCell(COL_SERVICE_MOB);
cellService.setCellValue(csvRow.getMethod() + " " + csvRow.getUri());
cellService.setCellStyle(rowStyle);
Cell cellTph = dataRow.createCell(COL_TPH_MOB);
cellTph.setCellValue(currentTph);
cellTph.setCellStyle(rowStyle);
Cell cellSla = dataRow.createCell(COL_SLA_MOB);
cellSla.setCellValue(csvRow.getSla());
cellSla.setCellStyle(rowStyle);
Cell cellIterations = dataRow.createCell(COL_ITERATIONS_MOB);
cellIterations.setCellValue(iterations);
cellIterations.setCellStyle(rowStyle);
Cell cellThreads = dataRow.createCell(COL_THREADS_MOB);
cellThreads.setCellValue(threads);
cellThreads.setCellStyle(rowStyle);
Cell cellLogin = dataRow.createCell(COL_LOGIN_MOB);
if (shouldBeRed) {
cellLogin.setCellValue(0);
} else {
String tphColLetter = getColumnLetter(COL_TPH_MOB);
String iterColLetter = getColumnLetter(COL_ITERATIONS_MOB);
String formula = String.format("ROUNDUP(%s%d/%s%d,0)",
tphColLetter, currentRow + 1, iterColLetter, currentRow + 1);
cellLogin.setCellFormula(formula);
}
cellLogin.setCellStyle(rowStyle);
Cell cellPacing = dataRow.createCell(COL_PACING_MOB);
if (shouldBeRed) {
cellPacing.setCellValue(0);
} else {
int pacing = 0;
if (iterations > 0 && threads > 0) {
pacing = (int) Math.round(3600.0 / (((double) currentTph / iterations) / threads));
}
if (pacing > 300 && iterations > 1) {
int[] validIterations = {1, 5, 10, 20, 40, 60};
int newIterations = iterations;
int newPacing = pacing;
for (int i = validIterations.length - 1; i >= 0; i--) {
int candidateIterations = validIterations[i];
if (candidateIterations < iterations) {
int candidatePacing = (int) Math.round(3600.0 / (((double) currentTph / candidateIterations) / threads));
if (candidatePacing <= 300) {
newIterations = candidateIterations;
newPacing = candidatePacing;
break;
}
}
}
Cell iterCell = dataRow.getCell(COL_ITERATIONS_MOB);
if (iterCell != null && newIterations != iterations) {
iterCell.setCellValue(newIterations);
log.debug(" MOBILE: скорректированы итерации: {} -> {} (пейсинг стал {})",
iterations, newIterations, newPacing);
}
}
String loginColLetter = getColumnLetter(COL_LOGIN_MOB);
String threadsColLetter = getColumnLetter(COL_THREADS_MOB);
String formula = String.format("ROUND(3600/(%s%d/%s%d),0)",
loginColLetter, currentRow + 1, threadsColLetter, currentRow + 1);
cellPacing.setCellFormula(formula);
}
cellPacing.setCellStyle(rowStyle);
if (!shouldBeRed && !isNew && isIntensityChanged) {
cellTph.setCellStyle(goodStyle);
log.info(" MOBILE: изменилась интенсивность {} {}: {} -> {}",
csvRow.getMethod(), csvRow.getUri(), oldTph, currentTph);
}
currentRow++;
}
int totalRowNum = currentRow;
Row totalRow = sheet.createRow(totalRowNum);
Cell totalLabel = totalRow.createCell(COL_SCRIPT_MOB);
totalLabel.setCellValue("ИТОГО");
totalLabel.setCellStyle(defaultStyle);
int lastDataRow = currentRow;
if (lastDataRow < 2) lastDataRow = 2;
String tphColLetter = getColumnLetter(COL_TPH_MOB);
String loginColLetter = getColumnLetter(COL_LOGIN_MOB);
String threadsColLetter = getColumnLetter(COL_THREADS_MOB);
Cell tphFormulaCell = totalRow.createCell(COL_TPH_MOB);
tphFormulaCell.setCellFormula(String.format("SUM(%s2:%s%d)", tphColLetter, tphColLetter, lastDataRow));
tphFormulaCell.setCellStyle(defaultStyle);
Cell loginFormulaCell = totalRow.createCell(COL_LOGIN_MOB);
loginFormulaCell.setCellFormula(String.format("SUM(%s2:%s%d)", loginColLetter, loginColLetter, lastDataRow));
loginFormulaCell.setCellStyle(defaultStyle);
Cell threadsFormulaCell = totalRow.createCell(COL_THREADS_MOB);
threadsFormulaCell.setCellFormula(String.format("SUM(%s2:%s%d)", threadsColLetter, threadsColLetter, lastDataRow));
threadsFormulaCell.setCellStyle(defaultStyle);
log.info(" MOBILE страница создана, {} операций", newRows.size());
return;
}
// ========== ДЛЯ WEB ==========
for (int r = sheet.getLastRowNum(); r >= 1; r--) {
Row row = sheet.getRow(r);
if (row != null) {
sheet.removeRow(row);
}
}
Row row2 = sheet.getRow(2);
if (row2 != null) {
boolean isEmpty = true;
for (int c = 0; c < row2.getLastCellNum(); c++) {
Cell cell = row2.getCell(c);
if (cell != null && cell.getCellType() != CellType.BLANK) {
if (cell.getCellType() == CellType.STRING && !cell.getStringCellValue().isEmpty()) {
isEmpty = false;
break;
} else if (cell.getCellType() == CellType.NUMERIC) {
isEmpty = false;
break;
}
}
}
if (isEmpty && sheet.getLastRowNum() >= 3) {
sheet.shiftRows(3, sheet.getLastRowNum(), -1);
}
}
Workbook workbook = sheet.getWorkbook();
CellStyle goodStyle = workbook.createCellStyle();
goodStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
goodStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
setBorders(goodStyle);
CellStyle badStyle = workbook.createCellStyle();
badStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
badStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
setBorders(badStyle);
CellStyle defaultStyle = workbook.createCellStyle();
setBorders(defaultStyle);
int colScript = COL_SCRIPT;
int colService = COL_SERVICE_WEB;
int colTph = COL_TPH_WEB;
int colSla = COL_SLA_WEB;
int colLogin = COL_LOGIN_WEB;
int colThreads = COL_THREADS_WEB;
int colPacing = COL_PACING_WEB;
int colIterations = COL_ITERATIONS_WEB;
Set<String> newServiceKeys = newRows.stream()
.map(row -> uriNormalizer.normalizeService(row.getMethod(), row.getUri(), projectType))
.collect(Collectors.toSet());
Set<String> deletedServiceKeys = new HashSet<>(oldServices);
deletedServiceKeys.removeAll(newServiceKeys);
int maxCol = colIterations;
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row != null && colService >= 0) {
Cell serviceCell = row.getCell(colService);
if (serviceCell != null) {
try {
String fullService = serviceCell.getStringCellValue();
String method = fullService.substring(0, fullService.indexOf(' '));
String uri = fullService.substring(fullService.indexOf(' ') + 1);
String serviceKey = uriNormalizer.normalizeService(method, uri, projectType);
if (deletedServiceKeys.contains(serviceKey)) {
for (int c = 0; c <= maxCol; c++) {
Cell cell = row.getCell(c);
if (cell == null) cell = row.createCell(c);
cell.setCellStyle(badStyle);
}
log.info(" Покрашена (удалена из профиля): {}", serviceKey);
}
} catch (Exception e) {
log.warn(" Ошибка при обработке строки {}: {}", r, e.getMessage());
continue;
}
}
}
}
CsvRow staticRow = null;
Iterator<CsvRow> iterator = newRows.iterator();
while (iterator.hasNext()) {
CsvRow row = iterator.next();
if ("UC00_StaticJS_1_WEB".equals(row.getName())) {
staticRow = row;
iterator.remove();
break;
}
}
if (staticRow != null) {
Integer staticRowNum = null;
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row != null && colScript >= 0) {
Cell scriptCell = row.getCell(colScript);
if (scriptCell != null) {
String scriptValue = null;
try {
scriptValue = scriptCell.getStringCellValue();
} catch (IllegalStateException e) {
continue;
}
if ("UC00_StaticJS_1_WEB".equals(scriptValue)) {
staticRowNum = r;
break;
}
}
}
}
Row staticRowExcel;
int staticRowIndex;
if (staticRowNum == null) {
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum >= 1) {
sheet.shiftRows(1, lastRowNum, 1);
}
staticRowExcel = sheet.createRow(1);
staticRowIndex = 1;
staticRowExcel.createCell(colScript).setCellValue(staticRow.getName());
staticRowExcel.createCell(colService).setCellValue(staticRow.getMethod() + " " + staticRow.getUri());
log.info(" Добавлена строка со статическим запросом UC00_StaticJS_1_WEB");
} else {
staticRowExcel = sheet.getRow(staticRowNum);
staticRowIndex = staticRowNum;
log.info(" Обновлена статическая операция UC00_StaticJS_1_WEB");
}
String serviceKey = uriNormalizer.normalizeService(staticRow.getMethod(), staticRow.getUri(), projectType);
boolean isNew = oldServices != null && !oldServices.contains(serviceKey)
&& (lastProfileData == null || !lastProfileData.containsKey(staticRow.getName()));
Integer oldTph = oldIntensityByService != null ? oldIntensityByService.get(serviceKey) : null;
boolean isChanged = oldTph != null && !oldTph.equals(staticRow.getIntensity());
CellStyle staticStyle = (isNew || isChanged) ? goodStyle : defaultStyle;
if (isChanged) {
log.info(" Статика {} изменила интенсивность: {} -> {}",
STATIC_OPERATION_ID, oldTph, staticRow.getIntensity());
}
Cell tphCell = staticRowExcel.getCell(colTph);
if (tphCell == null) tphCell = staticRowExcel.createCell(colTph);
tphCell.setCellValue(staticRow.getIntensity());
tphCell.setCellStyle(staticStyle);
Cell slaCell = staticRowExcel.getCell(colSla);
if (slaCell == null) slaCell = staticRowExcel.createCell(colSla);
slaCell.setCellValue(staticRow.getSla());
slaCell.setCellStyle(defaultStyle);
int iterations = calculateIterations(staticRow.getIntensity());
Cell iterCell = staticRowExcel.getCell(colIterations);
if (iterCell == null) iterCell = staticRowExcel.createCell(colIterations);
iterCell.setCellValue(iterations);
iterCell.setCellStyle(defaultStyle);
int threads = calculateThreads(staticRow.getIntensity());
Cell threadsCell = staticRowExcel.getCell(colThreads);
if (threadsCell == null) threadsCell = staticRowExcel.createCell(colThreads);
threadsCell.setCellValue(threads);
threadsCell.setCellStyle(defaultStyle);
Cell loginCell = staticRowExcel.getCell(colLogin);
if (loginCell == null) loginCell = staticRowExcel.createCell(colLogin);
loginCell.setCellValue(0);
loginCell.setCellStyle(defaultStyle);
Cell pacingCell = staticRowExcel.getCell(colPacing);
if (pacingCell == null) pacingCell = staticRowExcel.createCell(colPacing);
String tphColLetter = getColumnLetter(colTph);
String threadsColLetter = getColumnLetter(colThreads);
String pacingFormula = String.format("ROUND(3600/(%s%d/%s%d),0)",
tphColLetter, staticRowIndex + 1, threadsColLetter, staticRowIndex + 1);
pacingCell.setCellFormula(pacingFormula);
pacingCell.setCellStyle(defaultStyle);
}
Map<String, Integer> existingRowsMap = new HashMap<>();
int maxRowNum = 1;
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row != null) {
Cell serviceCell = row.getCell(colService);
if (serviceCell != null) {
try {
String fullService = serviceCell.getStringCellValue();
String method = fullService.substring(0, fullService.indexOf(' '));
String uri = fullService.substring(fullService.indexOf(' ') + 1);
String serviceKey = uriNormalizer.normalizeService(method, uri, projectType);
existingRowsMap.put(serviceKey, r);
if (r > maxRowNum) {
maxRowNum = r;
}
} catch (Exception e) {
continue;
}
}
}
}
for (int r = sheet.getLastRowNum(); r >= 0; r--) {
Row row = sheet.getRow(r);
if (row != null) {
Cell firstCell = row.getCell(0);
if (firstCell != null) {
String value = null;
try {
value = firstCell.getStringCellValue();
} catch (IllegalStateException e) {
continue;
}
if ("ИТОГО".equals(value)) {
sheet.removeRow(row);
}
}
}
}
Set<String> processedNames = new HashSet<>();
for (CsvRow row : newRows) {
String serviceKey = uriNormalizer.normalizeService(row.getMethod(), row.getUri(), projectType);
String redKey = row.getMethod() + "|" + row.getUri();
boolean shouldBeRed = row.getIntensity() == 0
&& extraInScript != null
&& extraInScript.contains(redKey);
if (shouldBeRed) {
log.info(" WEB: красим красным {} {} (интенсивность=0)",
row.getMethod(), row.getUri());
}
if (processedNames.contains(row.getName())) {
log.warn("Пропущен дубликат операции в newRows: {}", row.getName());
continue;
}
boolean isNew = oldServices != null && !oldServices.contains(serviceKey)
&& (lastProfileData == null || !lastProfileData.containsKey(row.getName()));
Integer oldTph = oldIntensityByService != null ? oldIntensityByService.get(serviceKey) : null;
boolean isIntensityChanged = oldTph != null && !oldTph.equals(row.getIntensity()) && row.getIntensity() > 0;
Integer existingRowNum = existingRowsMap.get(serviceKey);
Row dataRow;
int currentRowNum;
if (existingRowNum != null) {
dataRow = sheet.getRow(existingRowNum);
currentRowNum = existingRowNum;
if (existingRowNum > maxRowNum) maxRowNum = existingRowNum;
} else {
maxRowNum++;
currentRowNum = maxRowNum;
dataRow = sheet.createRow(maxRowNum);
if (colScript >= 0) {
dataRow.createCell(colScript).setCellValue(row.getName());
}
dataRow.createCell(colService).setCellValue(row.getMethod() + " " + row.getUri());
}
processedNames.add(row.getName());
if (shouldBeRed) {
for (int c = 0; c <= colIterations; c++) {
Cell cell = dataRow.getCell(c);
if (cell == null) cell = dataRow.createCell(c);
cell.setCellStyle(badStyle);
}
fillRowDataWeb(dataRow, row, currentRowNum, colScript, colService, colTph, colSla,
colIterations, colThreads, colLogin, colPacing, badStyle, false);
continue;
}
if (isNew) {
for (int c = 0; c <= colIterations; c++) {
Cell cell = dataRow.getCell(c);
if (cell == null) cell = dataRow.createCell(c);
cell.setCellStyle(goodStyle);
}
fillRowDataWeb(dataRow, row, currentRowNum, colScript, colService, colTph, colSla,
colIterations, colThreads, colLogin, colPacing, goodStyle, false);
continue;
}
fillRowDataWeb(dataRow, row, currentRowNum, colScript, colService, colTph, colSla,
colIterations, colThreads, colLogin, colPacing, defaultStyle, false);
if (isIntensityChanged) {
Cell tphCell = dataRow.getCell(colTph);
if (tphCell != null) {
tphCell.setCellStyle(goodStyle);
log.info(" Изменилась интенсивность: {} {}: {} -> {}",
row.getMethod(), row.getUri(), oldTph, row.getIntensity());
}
}
}
for (int r = sheet.getLastRowNum(); r > maxRowNum; r--) {
Row row = sheet.getRow(r);
if (row != null) {
sheet.removeRow(row);
}
}
int totalRowNum = maxRowNum + 1;
Row totalRow = sheet.createRow(totalRowNum);
int lastDataRow = maxRowNum;
if (lastDataRow < 2) lastDataRow = 2;
String tphColLetter = getColumnLetter(colTph);
String loginColLetter = getColumnLetter(colLogin);
String threadsColLetter = getColumnLetter(colThreads);
Cell tphFormulaCell = totalRow.createCell(colTph);
tphFormulaCell.setCellFormula("SUM(" + tphColLetter + "2:" + tphColLetter + (lastDataRow + 1) + ")");
tphFormulaCell.setCellStyle(defaultStyle);
Cell loginFormulaCell = totalRow.createCell(colLogin);
loginFormulaCell.setCellFormula("SUM(" + loginColLetter + "2:" + loginColLetter + (lastDataRow + 1) + ")");
loginFormulaCell.setCellStyle(defaultStyle);
Cell threadsFormulaCell = totalRow.createCell(colThreads);
threadsFormulaCell.setCellFormula("SUM(" + threadsColLetter + "2:" + threadsColLetter + (lastDataRow + 1) + ")");
threadsFormulaCell.setCellStyle(defaultStyle);
Cell totalLabel = totalRow.createCell(colScript >= 0 ? colScript : colService);
totalLabel.setCellValue("ИТОГО");
totalLabel.setCellStyle(defaultStyle);
}
private void fillRowDataWeb(Row dataRow, CsvRow row, int rowNum,
int colScript, int colService, int colTph, int colSla,
int colIterations, int colThreads, int colLogin, int colPacing,
CellStyle style, boolean isStatic) {
int currentTph = row.getIntensity();
int iterations = calculateIterations(currentTph);
int threads = calculateThreads(currentTph);
if (colScript >= 0) {
Cell cell = dataRow.getCell(colScript);
if (cell == null) cell = dataRow.createCell(colScript);
cell.setCellValue(row.getName());
cell.setCellStyle(style);
}
Cell serviceCell = dataRow.getCell(colService);
if (serviceCell == null) serviceCell = dataRow.createCell(colService);
serviceCell.setCellValue(row.getMethod() + " " + row.getUri());
serviceCell.setCellStyle(style);
Cell tphCell = dataRow.getCell(colTph);
if (tphCell == null) tphCell = dataRow.createCell(colTph);
tphCell.setCellValue(currentTph);
tphCell.setCellStyle(style);
Cell slaCell = dataRow.getCell(colSla);
if (slaCell == null) slaCell = dataRow.createCell(colSla);
slaCell.setCellValue(row.getSla());
slaCell.setCellStyle(style);
Cell iterCell = dataRow.getCell(colIterations);
if (iterCell == null) iterCell = dataRow.createCell(colIterations);
iterCell.setCellValue(iterations);
iterCell.setCellStyle(style);
Cell threadsCell = dataRow.getCell(colThreads);
if (threadsCell == null) threadsCell = dataRow.createCell(colThreads);
threadsCell.setCellValue(threads);
threadsCell.setCellStyle(style);
Cell loginCell = dataRow.getCell(colLogin);
if (loginCell == null) loginCell = dataRow.createCell(colLogin);
if (isStatic) {
loginCell.setCellValue(0);
} else {
String tphColLetter = getColumnLetter(colTph);
String iterColLetter = getColumnLetter(colIterations);
String formula = String.format("ROUNDUP(%s%d/%s%d,0)",
tphColLetter, rowNum + 1, iterColLetter, rowNum + 1);
loginCell.setCellFormula(formula);
}
loginCell.setCellStyle(style);
Cell pacingCell = dataRow.getCell(colPacing);
if (pacingCell == null) pacingCell = dataRow.createCell(colPacing);
if (isStatic) {
String tphColLetter = getColumnLetter(colTph);
String threadsColLetter = getColumnLetter(colThreads);
String formula = String.format("ROUND(3600/(%s%d/%s%d),0)",
tphColLetter, rowNum + 1, threadsColLetter, rowNum + 1);
pacingCell.setCellFormula(formula);
} else {
int pacing = (int) Math.round(3600.0 / (((double) currentTph / iterations) / threads));
if (pacing > 300 && iterations > 1) {
int[] validIterations = {1, 5, 10, 20, 40, 60};
int newIterations = iterations;
int newPacing = pacing;
for (int i = validIterations.length - 1; i >= 0; i--) {
int candidateIterations = validIterations[i];
if (candidateIterations < iterations) {
int candidatePacing = (int) Math.round(3600.0 / (((double) currentTph / candidateIterations) / threads));
if (candidatePacing <= 300) {
newIterations = candidateIterations;
newPacing = candidatePacing;
break;
}
}
}
if (iterCell != null && newIterations != iterations) {
iterCell.setCellValue(newIterations);
log.debug(" Скорректированы итерации: {} -> {} (пейсинг стал {})",
iterations, newIterations, newPacing);
}
String loginColLetter = getColumnLetter(colLogin);
String threadsColLetter = getColumnLetter(colThreads);
String formula = String.format("ROUND(3600/(%s%d/%s%d),0)",
loginColLetter, rowNum + 1, threadsColLetter, rowNum + 1);
pacingCell.setCellFormula(formula);
} else {
String loginColLetter = getColumnLetter(colLogin);
String threadsColLetter = getColumnLetter(colThreads);
String formula = String.format("ROUND(3600/(%s%d/%s%d),0)",
loginColLetter, rowNum + 1, threadsColLetter, rowNum + 1);
pacingCell.setCellFormula(formula);
}
}
pacingCell.setCellStyle(style);
}
private void setBorders(CellStyle style) {
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
}
private int calculateThreads(int tph) {
if (tph <= 100) return 3;
if (tph <= 200) return 6;
if (tph <= 500) return 8;
if (tph <= 1000) return 10;
if (tph <= 10000) return 20;
if (tph <= 15000) return 24;
if (tph <= 20000) return 28;
if (tph <= 25000) return 32;
if (tph <= 30000) return 36;
if (tph <= 40000) return 40;
if (tph <= 50000) return 44;
return 60;
}
private int calculateIterations(int tph) {
if (tph <= 900) return 1;
if (tph <= 1000) return 10;
if (tph <= 20000) return 20;
if (tph <= 40000) return 40;
return 60;
}
private String getColumnLetter(int colIndex) {
StringBuilder letter = new StringBuilder();
while (colIndex >= 0) {
letter.insert(0, (char) ('A' + (colIndex % 26)));
colIndex = colIndex / 26 - 1;
}
return letter.toString();
}
private String cleanUriFromParams(String uri) {
if (uri == null) return null;
String cleaned = uri.replaceAll("[\\p{Cf}]", "");
int queryIndex = cleaned.indexOf('?');
if (queryIndex != -1) {
return cleaned.substring(0, queryIndex);
}
return cleaned;
}
public void generatePprbExcelReport(List<CsvRow> newRows, String projectType,
Map<String, Integer> oldIntensityByService,
Set<String> oldServices,
Set<String> extraInScript,
Map<String, OldProfileData> lastProfileData) {
List<CsvRow> filteredRows = new ArrayList<>();
for (CsvRow row : newRows) {
String normalizedUri = uriNormalizer.normalizeUri(row.getUri(), "PPRB");
String redKey = row.getMethod() + "|" + normalizedUri;
boolean shouldSkip = row.getIntensity() == 0
&& extraInScript != null
&& !extraInScript.contains(redKey);
if (shouldSkip) {
log.info(" ППРБ: пропускаем операцию {} {} (интенсивность=0, нет в скрипте)",
row.getMethod(), row.getUri());
continue;
}
filteredRows.add(row);
}
try {
File templateFile = new File(DOC_FOLDER, TEMPLATE_FILE);
String pagePrefix = "PPRB";
Workbook workbook;
if (templateFile.exists()) {
workbook = new XSSFWorkbook(new FileInputStream(templateFile));
} else {
workbook = new XSSFWorkbook();
}
for (int i = workbook.getNumberOfSheets() - 1; i >= 0; i--) {
String sheetName = workbook.getSheetName(i);
if (sheetName.startsWith(pagePrefix) && workbook.getSheetAt(i).getLastRowNum() == 0) {
workbook.removeSheetAt(i);
}
}
Sheet lastSheet = findLastSheetByPrefix(workbook, pagePrefix);
int nextReleaseNumber;
String newSheetName;
if (lastSheet == null) {
nextReleaseNumber = 6026;
newSheetName = pagePrefix + " 6.026";
} else {
int lastReleaseNumber = extractReleaseNumber(lastSheet.getSheetName());
nextReleaseNumber = lastReleaseNumber + 1;
newSheetName = pagePrefix + " " + formatReleaseNumber(nextReleaseNumber);
}
Sheet newSheet = workbook.createSheet(newSheetName);
if (lastSheet != null) {
copySheetStructure(lastSheet, newSheet);
removeDelayAndFixHeadersPprb(newSheet);
removeHeaderRowIfExists(newSheet);
} else {
createHeaderRowPprb(newSheet);
}
fillSheetWithDataPprb(newSheet, filteredRows, oldIntensityByService, oldServices, extraInScript, lastProfileData);
removeHeaderRowIfExists(newSheet);
try (FileOutputStream fos = new FileOutputStream(templateFile)) {
workbook.write(fos);
}
workbook.close();
log.info(" ППРБ Excel отчёт обновлён: добавлена страница {}", newSheetName);
} catch (IOException e) {
log.error("Ошибка при создании ППРБ Excel отчёта: {}", e.getMessage());
}
}
private void createHeaderRowPprb(Sheet sheet) {
Row headerRow = sheet.createRow(0);
String[] headers = {"Скрипт", "Вызываемый сервис", "TPH", "SLA", "Количество потоков", "Pacing"};
for (int i = 0; i < headers.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(headers[i]);
sheet.setColumnWidth(i, 5000);
}
Font headerFont = sheet.getWorkbook().createFont();
headerFont.setBold(true);
CellStyle headerStyle = sheet.getWorkbook().createCellStyle();
headerStyle.setFont(headerFont);
setBorders(headerStyle);
for (int i = 0; i < headers.length; i++) {
headerRow.getCell(i).setCellStyle(headerStyle);
}
}
private void removeDelayAndFixHeadersPprb(Sheet sheet) {
Row oldHeaderRow = sheet.getRow(0);
if (oldHeaderRow != null) {
sheet.removeRow(oldHeaderRow);
}
createHeaderRowPprb(sheet);
}
private void fillSheetWithDataPprb(Sheet sheet, List<CsvRow> newRows,
Map<String, Integer> oldIntensityByService,
Set<String> oldServices,
Set<String> extraInScript,
Map<String, OldProfileData> lastProfileData) {
for (int r = sheet.getLastRowNum(); r >= 1; r--) {
Row row = sheet.getRow(r);
if (row != null) {
sheet.removeRow(row);
}
}
Row row2 = sheet.getRow(2);
if (row2 != null) {
boolean isEmpty = true;
for (int c = 0; c < row2.getLastCellNum(); c++) {
Cell cell = row2.getCell(c);
if (cell != null && cell.getCellType() != CellType.BLANK) {
if (cell.getCellType() == CellType.STRING && !cell.getStringCellValue().isEmpty()) {
isEmpty = false;
break;
} else if (cell.getCellType() == CellType.NUMERIC) {
isEmpty = false;
break;
}
}
}
if (isEmpty && sheet.getLastRowNum() >= 3) {
sheet.shiftRows(3, sheet.getLastRowNum(), -1);
}
}
log.info(" ППРБ страница очищена, будет создано {} новых строк", newRows.size());
Workbook workbook = sheet.getWorkbook();
CellStyle goodStyle = workbook.createCellStyle();
goodStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
goodStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
setBorders(goodStyle);
CellStyle badStyle = workbook.createCellStyle();
badStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
badStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
setBorders(badStyle);
CellStyle defaultStyle = workbook.createCellStyle();
setBorders(defaultStyle);
int colScript = 0;
int colService = 1;
int colTph = 2;
int colSla = 3;
int colThreads = 4;
int colPacing = 5;
int currentRow = 1;
for (CsvRow row : newRows) {
String normalizedUri = uriNormalizer.normalizeUri(row.getUri(), "PPRB");
String serviceKey = uriNormalizer.normalizeService(row.getMethod(), row.getUri(), "PPRB");
String redKey = row.getMethod() + "|" + normalizedUri;
boolean shouldBeRed = row.getIntensity() == 0
&& extraInScript != null
&& extraInScript.contains(redKey);
boolean isNew = (oldServices == null || !oldServices.contains(serviceKey))
&& (lastProfileData == null || !lastProfileData.containsKey(row.getName()));
Integer oldTph = oldIntensityByService != null ? oldIntensityByService.get(serviceKey) : null;
boolean isIntensityChanged = oldTph != null && !oldTph.equals(row.getIntensity()) && row.getIntensity() > 0;
Row dataRow = sheet.createRow(currentRow);
CellStyle rowStyle;
if (shouldBeRed) {
rowStyle = badStyle;
log.info(" ППРБ: красим красным {} {} (интенсивность=0)",
row.getMethod(), row.getUri());
} else if (isNew) {
rowStyle = goodStyle;
log.info(" ППРБ: новая операция {} {}", row.getMethod(), row.getUri());
} else {
rowStyle = defaultStyle;
}
int currentTph = row.getIntensity();
int threads = calculateThreads(currentTph);
Cell cellScript = dataRow.createCell(colScript);
cellScript.setCellValue(row.getName());
cellScript.setCellStyle(rowStyle);
Cell cellService = dataRow.createCell(colService);
cellService.setCellValue(row.getMethod() + " " + row.getUri());
cellService.setCellStyle(rowStyle);
Cell cellTph = dataRow.createCell(colTph);
cellTph.setCellValue(currentTph);
cellTph.setCellStyle(rowStyle);
Cell cellSla = dataRow.createCell(colSla);
cellSla.setCellValue(row.getSla());
cellSla.setCellStyle(rowStyle);
Cell cellThreads = dataRow.createCell(colThreads);
cellThreads.setCellValue(threads);
cellThreads.setCellStyle(rowStyle);
Cell cellPacing = dataRow.createCell(colPacing);
if (shouldBeRed) {
cellPacing.setCellValue(0);
} else {
String tphColLetter = getColumnLetter(colTph);
String threadsColLetter = getColumnLetter(colThreads);
String formula = String.format("MIN(ROUND(3600/(%s%d/%s%d),0), 300)",
tphColLetter, currentRow + 1, threadsColLetter, currentRow + 1);
cellPacing.setCellFormula(formula);
}
cellPacing.setCellStyle(rowStyle);
if (!shouldBeRed && !isNew && isIntensityChanged) {
cellTph.setCellStyle(goodStyle);
log.info(" ППРБ: изменилась интенсивность {} {}: {} -> {}",
row.getMethod(), row.getUri(), oldTph, currentTph);
}
currentRow++;
}
int totalRowNum = currentRow;
Row totalRow = sheet.createRow(totalRowNum);
Cell totalLabel = totalRow.createCell(colScript);
totalLabel.setCellValue("ИТОГО");
totalLabel.setCellStyle(defaultStyle);
int lastDataRow = currentRow;
if (lastDataRow < 2) lastDataRow = 2;
String tphColLetter = getColumnLetter(colTph);
String threadsColLetter = getColumnLetter(colThreads);
Cell tphFormulaCell = totalRow.createCell(colTph);
tphFormulaCell.setCellFormula("SUM(" + tphColLetter + "2:" + tphColLetter + lastDataRow + ")");
tphFormulaCell.setCellStyle(defaultStyle);
Cell threadsFormulaCell = totalRow.createCell(colThreads);
threadsFormulaCell.setCellFormula("SUM(" + threadsColLetter + "2:" + threadsColLetter + lastDataRow + ")");
threadsFormulaCell.setCellStyle(defaultStyle);
log.info(" ППРБ страница создана, {} операций", newRows.size());
}
}
