package org.activityinfo.server.endpoint.export;
/*
* #%L
* ActivityInfo Server
* %%
* Copyright (C) 2009 - 2013 UNICEF
* %%
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public
* License along with this program. If not, see
* <http://www.gnu.org/licenses/gpl-3.0.html>.
* #L%
*/
import com.bedatadriven.rebar.time.calendar.LocalDate;
import com.extjs.gxt.ui.client.Style.SortDir;
import com.extjs.gxt.ui.client.data.SortInfo;
import com.google.common.base.Strings;
import org.activityinfo.i18n.shared.I18N;
import org.activityinfo.legacy.shared.command.*;
import org.activityinfo.legacy.shared.command.result.SiteResult;
import org.activityinfo.legacy.shared.model.*;
import org.activityinfo.server.command.DispatcherSync;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.WorkbookUtil;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
* Exports sites in Excel format
*/
public class SiteExporter {
private static final Logger LOGGER = Logger.getLogger(SiteExporter.class.getName());
private static final int MAX_WORKSHEET_LENGTH = 31;
private static final short FONT_SIZE = 8;
private static final short TITLE_FONT_SIZE = 12;
private static final short DIAGONAL = 45;
private static final int COORD_COLUMN_WIDTH = 12;
private static final int ATTRIBUTE_COLUMN_WIDTH = 5;
private static final int INDICATOR_COLUMN_WIDTH = 16;
private static final int LOCATION_COLUMN_WIDTH = 20;
private static final int PARTNER_COLUMN_WIDTH = 16;
private static final int HEADER_CELL_HEIGHT = 75;
private static final int CHARACTERS_PER_WIDTH_UNIT = 255;
private static final int SITE_BATCH_SIZE = 100;
private final DispatcherSync dispatcher;
private final HSSFWorkbook book;
private final CreationHelper creationHelper;
private Map<String, Integer> sheetNames;
private CellStyle titleStyle;
private CellStyle dateStyle;
private CellStyle coordStyle;
private CellStyle indicatorValueStyle;
private CellStyle headerStyle;
private CellStyle headerStyleCenter;
private CellStyle headerStyleRight;
private CellStyle attribHeaderStyle;
private CellStyle indicatorHeaderStyle;
private CellStyle attribValueStyle;
private List<Integer> indicators;
private List<Integer> attributes;
private List<Integer> levels;
private HSSFCellStyle dateTimeStyle;
public SiteExporter(DispatcherSync dispatcher) {
this.dispatcher = dispatcher;
book = new HSSFWorkbook();
creationHelper = book.getCreationHelper();
sheetNames = new HashMap<String, Integer>();
declareStyles();
}
private void declareStyles() {
dateStyle = book.createCellStyle();
dateStyle.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy"));
dateTimeStyle = book.createCellStyle();
dateTimeStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-mm-dd HH:MM:SS"));
coordStyle = book.createCellStyle();
coordStyle.setDataFormat(creationHelper.createDataFormat().getFormat("0.000000"));
indicatorValueStyle = book.createCellStyle();
indicatorValueStyle.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0"));
Font headerFont = book.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
Font smallFont = book.createFont();
smallFont.setFontHeightInPoints(FONT_SIZE);
Font titleFont = book.createFont();
titleFont.setFontHeightInPoints(TITLE_FONT_SIZE);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
titleStyle = book.createCellStyle();
titleStyle.setFont(titleFont);
headerStyle = book.createCellStyle();
headerStyle.setFont(headerFont);
headerStyleCenter = book.createCellStyle();
headerStyleCenter.setFont(headerFont);
headerStyleCenter.setAlignment(CellStyle.ALIGN_CENTER);
headerStyleRight = book.createCellStyle();
headerStyleRight.setFont(headerFont);
headerStyleRight.setAlignment(CellStyle.ALIGN_RIGHT);
attribHeaderStyle = book.createCellStyle();
attribHeaderStyle.setFont(smallFont);
attribHeaderStyle.setRotation(DIAGONAL);
attribHeaderStyle.setWrapText(true);
indicatorHeaderStyle = book.createCellStyle();
indicatorHeaderStyle.setFont(smallFont);
indicatorHeaderStyle.setWrapText(true);
indicatorHeaderStyle.setAlignment(CellStyle.ALIGN_RIGHT);
attribValueStyle = book.createCellStyle();
attribValueStyle.setFont(smallFont);
}
public void export(ActivityFormDTO activity, Filter filter) {
HSSFSheet sheet = book.createSheet(composeUniqueSheetName(activity));
sheet.createFreezePane(4, 2);
// initConditionalFormatting(sheet);
createHeaders(activity, sheet);
createDataRows(activity, filter, sheet);
}
private String composeUniqueSheetName(ActivityFormDTO activity) {
String sheetName = activity.getDatabaseName() + " - " + activity.getName();
// to avoid conflict with our own disambiguation scheme, remove any trailing "(n)"
// from sheet names
sheetName = sheetName.replaceFirst("\\((\\d+)\\)$", "$1");
// shorten and translate the name to meet excel requirements
String safeName = WorkbookUtil.createSafeSheetName(sheetName);
// assure that the sheet name is unique
if (!sheetNames.containsKey(safeName)) {
sheetNames.put(safeName, 1);
return safeName;
} else {
int index = sheetNames.get(safeName) + 1;
sheetNames.put(safeName, index);
String disambiguatedNamed = safeName + " (" + index + ")";
if (disambiguatedNamed.length() > MAX_WORKSHEET_LENGTH) {
int toTrim = disambiguatedNamed.length() - MAX_WORKSHEET_LENGTH;
disambiguatedNamed = safeName.substring(0, safeName.length() - toTrim) + " (" + index + ")";
}
return disambiguatedNamed;
}
}
private void createHeaders(ActivityFormDTO activity, HSSFSheet sheet) {
// / The HEADER rows
Row headerRow1 = sheet.createRow(0);
Row headerRow2 = sheet.createRow(1);
headerRow2.setHeightInPoints(HEADER_CELL_HEIGHT);
// Create a title cell with the complete database + activity name
Cell titleCell = headerRow1.createCell(0);
titleCell.setCellValue(creationHelper.createRichTextString(
activity.getDatabaseName() + " - " + activity.getName()));
titleCell.setCellStyle(titleStyle);
int column = 0;
createHeaderCell(headerRow2, column++, "SiteId", CellStyle.ALIGN_LEFT);
createHeaderCell(headerRow2, column++, "DateCreated", CellStyle.ALIGN_RIGHT);
sheet.setColumnHidden(0, true);
sheet.setColumnHidden(1, true);
createHeaderCell(headerRow2, column++, "Date1", CellStyle.ALIGN_RIGHT);
createHeaderCell(headerRow2, column++, "Date2", CellStyle.ALIGN_RIGHT);
createHeaderCell(headerRow2, column, "Partner");
sheet.setColumnWidth(column, characters(PARTNER_COLUMN_WIDTH));
column++;
createHeaderCell(headerRow2, column, activity.getLocationType().getName());
sheet.setColumnWidth(column, characters(LOCATION_COLUMN_WIDTH));
column++;
createHeaderCell(headerRow2, column++, "Axe");
indicators = new ArrayList<Integer>(activity.getIndicators().size());
if (activity.getReportingFrequency() == ActivityFormDTO.REPORT_ONCE) {
for (IndicatorGroup group : activity.groupIndicators()) {
if (group.getName() != null) {
// create a merged cell on the top row spanning all members
// of the group
createHeaderCell(headerRow1, column, group.getName());
sheet.addMergedRegion(new CellRangeAddress(0,
0,
column,
column + group.getIndicators().size() - 1));
}
for (IndicatorDTO indicator : group.getIndicators()) {
indicators.add(indicator.getId());
createHeaderCell(headerRow2, column, indicator.getName(), indicatorHeaderStyle);
sheet.setColumnWidth(column, characters(INDICATOR_COLUMN_WIDTH));
column++;
}
}
}
attributes = new ArrayList<>();
for (AttributeGroupDTO group : activity.getAttributeGroups()) {
if (group.getAttributes().size() != 0) {
createHeaderCell(headerRow1, column, group.getName(), CellStyle.ALIGN_CENTER);
sheet.addMergedRegion(new CellRangeAddress(0, 0, column, column + group.getAttributes().size() - 1));
for (AttributeDTO attrib : group.getAttributes()) {
attributes.add(attrib.getId());
createHeaderCell(headerRow2, column, attrib.getName(), attribHeaderStyle);
sheet.setColumnWidth(column, characters(ATTRIBUTE_COLUMN_WIDTH));
column++;
}
}
}
levels = new ArrayList<>();
for (AdminLevelDTO level : activity.getAdminLevels()) {
createHeaderCell(headerRow2, column++, "Code " + level.getName());
createHeaderCell(headerRow2, column++, level.getName());
levels.add(level.getId());
}
int latColumn = column++;
int lngColumn = column++;
createHeaderCell(headerRow2, latColumn, I18N.CONSTANTS.longitude(), CellStyle.ALIGN_RIGHT);
createHeaderCell(headerRow2, lngColumn, I18N.CONSTANTS.latitude(), CellStyle.ALIGN_RIGHT);
sheet.setColumnWidth(lngColumn, characters(COORD_COLUMN_WIDTH));
sheet.setColumnWidth(latColumn, characters(COORD_COLUMN_WIDTH));
createHeaderCell(headerRow2, column++, I18N.CONSTANTS.comments());
}
private SiteResult querySites(ActivityFormDTO activity, Filter filter, int offset) {
Filter effectiveFilter = new Filter(filter);
effectiveFilter.addRestriction(DimensionType.Activity, activity.getId());
GetSites query = new GetSites();
query.setFilter(effectiveFilter);
query.setSortInfo(new SortInfo("date2", SortDir.DESC));
query.setOffset(offset);
query.setLimit(SITE_BATCH_SIZE);
return dispatcher.execute(query);
}
private void createDataRows(ActivityFormDTO activity, Filter filter, Sheet sheet) {
int rowIndex = 2;
// query in batches in order to avoid sinking MySQL
int offset = 0;
while (true) {
LOGGER.log(Level.INFO, "Fetching batching at offset " + offset);
SiteResult batch = querySites(activity, filter, offset);
if (batch.getData().isEmpty()) {
break; // break if no data
}
for (SiteDTO site : batch.getData()) {
addDataRow(sheet, rowIndex++, site);
}
offset += batch.getData().size();
if (offset >= batch.getTotalLength()) {
break;
}
}
}
private void addDataRow(Sheet sheet, int rowIndex, SiteDTO site) {
Row row = sheet.createRow(rowIndex);
int column = 0;
createCell(row, column++, Integer.toString(site.getId()));
createCell(row, column++, site.getDateCreated());
createCell(row, column++, site.getDate1());
createCell(row, column++, site.getDate2());
createCell(row, column++, site.getPartnerName());
createCell(row, column++, site.getLocationName());
createCell(row, column++, site.getLocationAxe());
for (Integer indicatorId : indicators) {
createIndicatorValueCell(row, column++, site.getIndicatorValue(indicatorId));
}
for (Integer attribId : attributes) {
boolean value = site.getAttributeValue(attribId);
Cell valueCell = createCell(row, column, value);
valueCell.setCellStyle(attribValueStyle);
column++;
}
for (Integer levelId : levels) {
AdminEntityDTO entity = site.getAdminEntity(levelId);
if (entity != null) {
createCell(row, column, "");
createCell(row, column + 1, entity.getName());
}
column += 2;
}
if (site.hasLatLong()) {
createCoordCell(row, column, site.getLongitude());
createCoordCell(row, column + 1, site.getLatitude());
}
column += 2;
if (!Strings.isNullOrEmpty(site.getComments())) {
createCell(row, column, site.getComments());
}
column++;
}
private Cell createHeaderCell(Row headerRow, int columnIndex, String text, CellStyle style) {
Cell cell = headerRow.createCell(columnIndex);
cell.setCellValue(creationHelper.createRichTextString(text));
cell.setCellStyle(style);
return cell;
}
private Cell createHeaderCell(Row headerRow, int columnIndex, String text) {
return createHeaderCell(headerRow, columnIndex, text, CellStyle.ALIGN_LEFT);
}
private Cell createHeaderCell(Row headerRow, int columnIndex, String text, int align) {
Cell cell = headerRow.createCell(columnIndex);
cell.setCellValue(creationHelper.createRichTextString(text));
switch (align) {
case CellStyle.ALIGN_LEFT:
cell.setCellStyle(headerStyle);
break;
case CellStyle.ALIGN_CENTER:
cell.setCellStyle(headerStyleCenter);
break;
case CellStyle.ALIGN_RIGHT:
cell.setCellStyle(headerStyleRight);
break;
}
return cell;
}
private Cell createCell(Row row, int columnIndex, String text) {
Cell cell = row.createCell(columnIndex);
cell.setCellValue(creationHelper.createRichTextString(text));
return cell;
}
private void createCell(Row row, int columnIndex, LocalDate date) {
Cell cell = row.createCell(columnIndex);
if (date != null) {
cell.setCellValue(date.atMidnightInMyTimezone());
}
cell.setCellStyle(dateStyle);
}
private void createCell(Row row, int columnIndex, Date date) {
Cell cell = row.createCell(columnIndex);
if (date != null) {
cell.setCellValue(date);
}
cell.setCellStyle(dateStyle);
}
private void createIndicatorValueCell(Row row, int columnIndex, Object value) {
if (value != null) {
Cell cell = row.createCell(columnIndex);
cell.setCellStyle(indicatorValueStyle);
if (value instanceof Double) {
cell.setCellValue((Double) value);
} else if (value instanceof String) {
cell.setCellValue((String) value);
} else if (value instanceof Date) {
cell.setCellValue((Date) value);
} else if (value instanceof LocalDate) {
cell.setCellValue(((LocalDate) value).atMidnightInMyTimezone());
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
}
}
}
private void createCoordCell(Row row, int columnIndex, double value) {
Cell cell = row.createCell(columnIndex);
cell.setCellValue(value);
cell.setCellStyle(coordStyle);
}
private Cell createCell(Row row, int columnIndex, boolean value) {
Cell cell = row.createCell(columnIndex);
cell.setCellValue(value);
return cell;
}
public HSSFWorkbook getBook() {
return book;
}
private int characters(int numberOfCharacters) {
return numberOfCharacters * CHARACTERS_PER_WIDTH_UNIT;
}
public void done() {
// an Excel workbook can't have zero sheets, so we need to
// add something here for it to be valid
if (book.getNumberOfSheets() == 0) {
HSSFSheet sheet = book.createSheet("Sheet1");
sheet.createRow(0).createCell(0).setCellValue("No matching sites.");
}
}
public SiteExporter buildExcelWorkbook(Filter filter) {
SchemaDTO schema = dispatcher.execute(new GetSchema());
for (UserDatabaseDTO db : schema.getDatabases()) {
if (!filter.isRestricted(DimensionType.Database) ||
filter.getRestrictions(DimensionType.Database).contains(db.getId())) {
for (ActivityDTO activity : db.getActivities()) {
if (!filter.isRestricted(DimensionType.Activity) ||
filter.getRestrictions(DimensionType.Activity).contains(activity.getId())) {
export(dispatcher.execute(new GetActivityForm(activity.getId())), filter);
}
}
}
}
done();
return this;
}
}