/*
* PatientView
*
* Copyright (c) Worth Solutions Limited 2004-2013
*
* This file is part of PatientView.
*
* PatientView 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.
* PatientView 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 PatientView in a file
* titled COPYING. If not, see <http://www.gnu.org/licenses/>.
*
* @package PatientView
* @link http://www.patientview.org
* @author PatientView <info@patientview.org>
* @copyright Copyright (c) 2004-2013, Worth Solutions Limited
* @license http://www.gnu.org/licenses/gpl-3.0.html The GNU General Public License V3.0
*/
package org.patientview.radar.util;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* Stand alone utility class for converting radar excel data file into sql file for import into the db.
* creates sql for:
* 1. prd codes
* 2. working groups
* 3. mapping between prd codes and working groups
*/
public class RadarPhase2ExcelDataToSqlMapper {
private static final Logger LOGGER = LoggerFactory.getLogger(RadarPhase2ExcelDataToSqlMapper.class);
private static final String BASE_PATH = "/radarphase2dataimport/";
public static final int FIRST_DATA_ROW = 2;
public static final int LAST_DATA_ROW = 287;
public static final int FIRST_BOOLEAN_FIELD = 2;
public static final int LAST_BOOLEAN_FIELD = 10;
public static final int FIRST_WORKING_GROUP_INDEX = 24;
public static final int LAST_WORKING_GROUP_INDEX = 68;
public static void main(String params[]) {
InputStream inp = null;
try {
/************** 1. first create the prd codes sql ***************/
// this is the file from radar originally called ERA_EDTA_new_PRD_codes_27042012_def for NDT_RADAR
inp = new FileInputStream(BASE_PATH + "input/prd_codes_and_working_group.xls");
Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
// each list item represents a row to insert
List<List<String>> dataList = new ArrayList<List<String>>();
//iterate through the rows in excel file
for (Row row : sheet) {
// ignore non data rows
if (row.getRowNum() < FIRST_DATA_ROW) {
continue;
} else if (row.getRowNum() > LAST_DATA_ROW) {
break;
}
List<String> values = new ArrayList<String>();
// iterate through cells
for (int cn = 0; cn < row.getLastCellNum(); cn++) {
Cell cell = row.getCell(cn);
// ignore non data cells
if (cn > 23) {
break;
}
String value = "";
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
value = cell.getStringCellValue();
}
// convert x values to 1 which means true, or blank to 0 which means false
if ((cn >= FIRST_BOOLEAN_FIELD && cn <= LAST_BOOLEAN_FIELD)) {
value = value.equals("x") ? "1" : "0";
}
values.add(value);
}
dataList.add(values);
}
StringBuilder outputText = new StringBuilder();
StringBuilder prdSql = new StringBuilder();
String sqlBaseInsert = "INSERT INTO rdr_prd_code(ERA_EDTA_PRD_code, ERA_EDTA_primaryRenalDiagnosisTerm, " +
"histology, clinicalHistory, familyHistory, clinicalExam, biochemistry, immunology, " +
"urineAnalysis, " + "imaging, geneTest, otherCriteriaAndNotes, " +
"SNOMED_CT_conceptIdentifierForFocusConcept, " + "SNOMED_CT_fullySpecifiedName, " +
"SNOMED_CT_expressionConstraint, majorHeading, mappingToOldPRDCode, " +
"mappingToOldPRDTerm, ERA_EDTA_defaultSortOrder, geneticsHomeReferenceLink, " +
"nationalCenterForBiotechnologyLink, ICD_10_code, ICD10_rubricTerm, alternativesearchTerms) " +
"VALUES (";
int index = 0;
// for each row in the data list create an sql insert statement
for (List<String> row : dataList) {
String sqlInsert = sqlBaseInsert;
int valueIndex = 0;
for (String value : row) {
value = value.replace("'", "").replace("\"", "");
sqlInsert += "'" + value + "'" + (valueIndex != row.size() - 1 ? "," : "");
valueIndex++;
}
sqlInsert += ");" + System.getProperty("line.separator");
prdSql.append(sqlInsert);
index++;
}
// append to output text - output text will eventually be written to a file
outputText.append(prdSql + System.getProperty("line.separator"));
/************** 2. create the working groups sql ***************/
Row row = sheet.getRow(1);
List<String> workingGroups = new ArrayList<String>();
// iterate through all working groups
for (Cell cell : row) {
if (cell.getColumnIndex() < FIRST_WORKING_GROUP_INDEX) {
continue;
} else if (cell.getColumnIndex() > LAST_WORKING_GROUP_INDEX) {
break;
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String value = cell.getStringCellValue();
value = value.replace("'", "\\'");
workingGroups.add(value);
}
// create sql for working groups sql insert
String workingGroupSql = "" +
System.getProperty("line.separator");
int workingGroupIndex = 0;
for (String workingGroup : workingGroups) {
String unitCode = workingGroup.split(" ")[0]+workingGroupIndex; // this is id, has to be unique
workingGroupSql += "INSERT INTO unit(unitcode, name, shortName, sourceType) VALUES('"+unitCode+"', '"+
workingGroup + "', '','radargroup');" + System.getProperty("line.separator");
workingGroupIndex++;
}
// append to output text - output text will eventually be written to a file
outputText.append(workingGroupSql + System.getProperty("line.separator"));
/************** 3. create the mapping table sql - this is the tricky bit! ***************/
List<List<String>> mappingData = new ArrayList<List<String>>();
// for each working group collect mapping values to working group
for (int columnIndex = FIRST_WORKING_GROUP_INDEX; columnIndex < LAST_WORKING_GROUP_INDEX;
columnIndex++) {
List<String> list = new ArrayList<String>();
for (int rowIndex = FIRST_DATA_ROW; rowIndex <= LAST_DATA_ROW; rowIndex++) {
Row mappingRow = sheet.getRow(rowIndex);
Cell cell = mappingRow.getCell(columnIndex);
String value = "0";
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
value = cell.getStringCellValue();
}
list.add(value);
}
mappingData.add(list);
}
// create list of prd ids
List<String> prdIds = new ArrayList<String>();
for (int i = FIRST_DATA_ROW; i <= LAST_DATA_ROW; i++) {
Row aRow = sheet.getRow(i);
Cell cell = aRow.getCell(0);
cell.setCellType(Cell.CELL_TYPE_STRING);
String value = cell.getStringCellValue();
prdIds.add(value);
}
// create sql insert statements based on where working group and disease intersect
String mappingSql = "";
String baseSql = "INSERT INTO rdr_diagnosis_mapping(workingGroup, PRDCode, ordering) VALUES(";
for (int i = 0; i < mappingData.size(); i++) {
String sql = "";
List<String> list = mappingData.get(i);
for (int j = 0; j < list.size(); j++) {
sql = baseSql;
String value = list.get(j);
if (!value.equals("0")) {
sql += "'" + (workingGroups.get(i).split(" ")[0] + i) + "', '" + prdIds.get(j) + "','"
+ value + "');";
if (!sql.equals(baseSql)) {
mappingSql += sql + System.getProperty("line.separator");
}
}
}
}
outputText.append(mappingSql);
// output all sql stuff to file
FileWriter fileWriter = new FileWriter(BASE_PATH + "output/phase2Data.sql");
BufferedWriter bufferedWriter = new BufferedWriter(fileWriter);
bufferedWriter.write(outputText.toString());
//Close the output stream
bufferedWriter.close();
} catch (Exception e) {
//To change body of catch statement use File | Settings | File Templates.
LOGGER.error(e.getMessage());
LOGGER.debug(e.getMessage(), e);
}
}
}