public void saveExitPageInfo() {
SqlRowSet s =
this.queryForRowSet(
" SELECT DISTINCT u.idurl,u.name,u.title,u.viewed,u.exit_rate "
+ " FROM log_url u,log_visit v "
+ " WHERE u.idurl = v.exit_url ;");
while (s.next()) {
ExitPageInfo exitPageInfo = new ExitPageInfo();
exitPageInfo.setName(s.getString("name"));
if (s.getString("title") == null) exitPageInfo.setTitle(" ");
else exitPageInfo.setTitle(s.getString("title"));
exitPageInfo.setVisit(s.getInt("viewed"));
exitPageInfo.setExitTimes((int) getVisitExitPage(s.getInt("idurl")));
exitPageInfo.setExitRate(s.getFloat("exit_rate"));
String sql =
"INSERT INTO exit_page_info(name,exit_times,visit,exit_rate,title) "
+ " VALUES ('"
+ exitPageInfo.getName()
+ "','"
+ exitPageInfo.getExitTimes()
+ "','"
+ exitPageInfo.getVisit()
+ "','"
+ exitPageInfo.getExitRate()
+ "','"
+ exitPageInfo.getTitle()
+ "');";
this.getJt().execute(sql);
}
}
@Override
public List<Module> getAllModule() {
// TODO Auto-generated method stub
String sqlStr =
"SELECT "
+ ID
+ ","
+ MODULE_NAME
+ ","
+ ENABLE
+ ","
+ PICTURE
+ ","
+ TARGET
+ " FROM "
+ TABLE_NAME;
List<Module> moduleList = new ArrayList<Module>();
SqlRowSet rs = jdbcTemplate.queryForRowSet(sqlStr);
while (rs.next()) {
Module module = new Module();
module.setEnable(rs.getInt(ENABLE));
module.setId(rs.getInt(ID));
module.setModuleName(rs.getString(MODULE_NAME));
module.setPicture(rs.getString(PICTURE));
module.setTarget(rs.getString(TARGET));
moduleList.add(module);
}
return moduleList;
}
public void saveEntryPageInfo() {
SqlRowSet s =
this.queryForRowSet(
" SELECT DISTINCT u.idurl,u.name,u.title,u.viewed,u.bounce_rate "
+ " FROM log_url u,log_visit v "
+ " WHERE u.idurl = v.entry_url ;");
while (s.next()) {
EntryPageInfo enttyPageInfo = new EntryPageInfo();
enttyPageInfo.setName(s.getString("name"));
if (s.getString("title") == null) enttyPageInfo.setTitle(" ");
else enttyPageInfo.setTitle(s.getString("title"));
enttyPageInfo.setEntrance(s.getInt("viewed"));
enttyPageInfo.setBounce((int) getVisitOnlyOnePage(s.getInt("idurl")));
enttyPageInfo.setBounceRate(s.getFloat("bounce_rate"));
String sql =
"INSERT INTO entry_page_info(name,entrance,bounce,bounce_rate,title) "
+ " VALUES ('"
+ enttyPageInfo.getName()
+ "','"
+ enttyPageInfo.getEntrance()
+ "','"
+ enttyPageInfo.getBounce()
+ "','"
+ enttyPageInfo.getBounceRate()
+ "','"
+ enttyPageInfo.getTitle()
+ "');";
this.getJt().execute(sql);
}
}
@Test
public void testLazyNameUpdate() {
Personne p = (Personne) getSession().get(Personne.class, 1000);
p.setLazyName("Arthur");
getSession().flush();
SqlRowSet rs = jdbcTemplate.queryForRowSet("select * from pers1 where id = ?", 1000);
rs.next();
assertEquals(rs.getString("LAZYNAME"), "Arthur", "Must have been updated");
assertEquals(rs.getString("NAME"), "Anybody", "Must not change");
}
public void saveVisitByMonth() {
SqlRowSet s =
this.queryForRowSet(
"SELECT month,year,COUNT(*) AS visit " + " FROM log_visit " + " GROUP BY month,year ;");
while (s.next()) {
VisitInfo visitInfo = new VisitInfo();
visitInfo.setName(s.getString("year") + "-" + s.getString("month") + "-00");
visitInfo.setVisitQuantity(s.getInt("visit"));
visitInfo.setType("month");
handleSaveVisitInfo(visitInfo);
}
}
@Test
public void testInsert() {
Personne p = new Personne();
p.setName("test");
p.setLazyName("testlazy");
getSession().saveOrUpdate(p);
getSession().flush(); // Test cheat
assertNotNull(p.getId());
SqlRowSet rs = jdbcTemplate.queryForRowSet("select * from pers1 where id = ?", p.getId());
rs.next();
assertEquals(rs.getString("NAME"), "test", "Must have been inserted");
assertEquals(rs.getString("LAZYNAME"), "testlazy", "Must have been inserted");
}
public LogUrl getLogUrl(String url) {
// TODO Auto-generated method stub
SqlRowSet s =
this.queryForRowSet("SELECT * " + " FROM log_url " + "WHERE name= '" + url + "';");
LogUrl l = new LogUrl();
while (s.next()) {
l.setIdUrl(s.getInt("idurl"));
l.setName(s.getString("name"));
l.setStatus(s.getString("status"));
l.setViewed(s.getInt("viewed"));
l.setUniqueView(s.getInt("unique_view"));
}
return l;
}
@Test
public void testRowSet() {
Object[] params = new Object[] {1};
SqlRowSet rowSet =
jdbcTemplate.queryForRowSet("SELECT * FROM regions WHERE RegionID > ?", params);
assertTrue(rowSet.next());
String regionId = rowSet.getString("RegionID");
assertTrue(regionId != null);
String regionName = rowSet.getString("RegionName");
assertTrue(regionName != null);
}
@Test
public void testRowSet2() {
Object[] params = new Object[] {"CA"};
SqlRowSet rowSet =
jdbcTemplate.queryForRowSet("SELECT * FROM countries WHERE CountryISOCode = ?", params);
assertTrue("Has next", rowSet.next());
String isoCode = rowSet.getString("CountryISOCode");
assertTrue(isoCode != null);
String name = rowSet.getString("CountryName");
assertTrue(name != null);
}
/**
* 根据SQL查询对应数据集合Map
*
* @param sql 返回Key-Value形式的SQL语句
* @return
*/
public Map<String, String> getSQLKeyValueMap(String sql) {
Map<String, String> dataMap = new LinkedHashMap<String, String>();
try {
if (StringUtils.isNotBlank(sql)) {
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
SqlRowSet row = jdbcTemplate.queryForRowSet(sql);
while (row.next()) {
dataMap.put(row.getString(1), row.getString(2));
}
}
} catch (Exception e) {
logger.error("SQL parse error: " + sql, e);
dataMap.put("ERROR", "[系统处理出现异常]");
}
return dataMap;
}
public void press() {
// get a list of all the tables and columns
logger.info("Starting MySQL to Mongo Conversion...");
logger.info("Preparing Tables...");
mySQLHandler.initialiseDatabase();
List<Table> tables = mySQLHandler.getTableList();
for (int i = 0; i < tables.size(); i++) {
Table table = tables.get(i);
List<Column> columns = table.getColumns();
List<DBObject> dboList = new ArrayList<DBObject>();
SqlRowSet rs = mySQLHandler.selectAllFromTable(table.getTableName());
logger.info("Creating objects for " + table.getTableName() + "...");
while (rs.next()) {
BasicDBObject dbo = new BasicDBObject();
for (int j = 0; j < columns.size(); j++) {
Column col = columns.get(j);
String colName = col.getColumnName();
switch (col.getType()) {
case Types.INTEGER:
case Types.BIGINT:
dbo.append(colName, rs.getInt(colName));
break;
case Types.DOUBLE:
dbo.append(colName, rs.getDouble(colName));
break;
case Types.DATE:
dbo.append(colName, rs.getDate(colName));
break;
default:
dbo.append(colName, rs.getString(colName));
break;
}
}
dboList.add(dbo);
}
// now insert it
logger.info(
"Inserting " + dboList.size() + " mongo rows into " + table.getTableName() + "...");
table.setNumOfRows(dboList.size());
try {
mongoHandler.createCollection(table.getTableName(), true);
mongoHandler.batchInsert(dboList, table.getTableName());
assert (mongoHandler.getNumObjectsInCollection(table.getTableName()) == dboList.size());
logger.info(table.getTableName() + " DONE!");
} catch (CollectionExistException e) {
e.printStackTrace();
}
}
logger.info(tables.size() + " collections added!");
// now check go get it from mongo and check if the data there is correct
logger.info("Checking mongo consistency...");
for (int i = 0; i < tables.size(); i++) {
Table table = tables.get(i);
assert (mongoHandler.getNumObjectsInCollection(table.getTableName()) == table.getNumOfRows());
logger.info(table.getTableName() + " consistent!");
}
logger.info("MySQL to Mongo Conversion Completed!!!!");
}
public void saveAvgVisitInfo() {
// TODO Auto-generated method stub
SqlRowSet s =
this.queryForRowSet(
"SELECT HOUR,AVG(TEMP.visit)AS visit "
+ "FROM(SELECT HOUR,visit_date,COUNT(*) AS visit "
+ "FROM log_visit "
+ "GROUP BY HOUR,visit_date)TEMP "
+ "GROUP BY HOUR;");
while (s.next()) {
AvgVisit avgVisit = new AvgVisit();
avgVisit.setName(s.getString("HOUR"));
avgVisit.setVisit(s.getFloat("visit"));
String sql =
"INSERT INTO avg_visit(name,visit,type) "
+ "VALUES ('"
+ avgVisit.getName()
+ "','"
+ avgVisit.getVisit()
+ "','hour')";
this.getJt().execute(sql);
}
SqlRowSet s1 =
this.queryForRowSet(
"SELECT TEMP.day,AVG(visit_quantity)AS visit "
+ "FROM (SELECT NAME,DAYNAME(NAME)AS DAY,visit_quantity "
+ " FROM visit_info "
+ " WHERE TYPE='date' "
+ " GROUP BY NAME)TEMP "
+ "GROUP BY TEMP.day;");
while (s1.next()) {
AvgVisit avgVisit = new AvgVisit();
avgVisit.setName(s1.getString("day"));
avgVisit.setVisit(s1.getFloat("visit"));
String sql =
"INSERT INTO avg_visit(name,visit,type) "
+ "VALUES ('"
+ avgVisit.getName()
+ "','"
+ avgVisit.getVisit()
+ "','day')";
this.getJt().execute(sql);
}
}
public String getCateNameById(int classifyId) throws Exception {
String sql = "select name from t_cy_classify where id=?";
Object[] o = new Object[] {classifyId};
SqlRowSet rs = commonDao.getRs(sql, o);
String name = "";
if (rs.next()) {
name = rs.getString(1);
}
return name;
}
void saveOsBrowserInfo() {
SqlRowSet s =
this.queryForRowSet(
"SELECT config_os,config_browser_name,COUNT(ip)AS visit,COUNT(DISTINCT ip)AS unique_visit,SUM(visit_total_time)AS total_time "
+ " FROM log_visit "
+ " GROUP BY config_os,config_browser_name ;");
while (s.next()) {
UserSettingInfo userSettingInfo = new UserSettingInfo();
userSettingInfo.setName(s.getString("config_os") + " " + s.getString("config_browser_name"));
userSettingInfo.setVisit(s.getInt("visit"));
userSettingInfo.setUniqueVisit(s.getInt("unique_visit"));
userSettingInfo.setAvgTime(s.getFloat("total_time") / userSettingInfo.getVisit());
SqlRowSet s1 =
this.queryForRowSet(
" SELECT COUNT(*) AS bounce "
+ " FROM log_visit "
+ " WHERE config_os='"
+ s.getString("config_os")
+ "'AND config_browser_name = '"
+ s.getString("config_browser_name")
+ "' AND visit_total_time =0 ;");
while (s1.next()) {
userSettingInfo.setBounceRate(s1.getFloat("bounce") / userSettingInfo.getVisit());
}
String sql =
"INSERT INTO user_setting_info(name,visit,unique_visit,avg_time,bounce_rate,type) "
+ " VALUES ('"
+ userSettingInfo.getName()
+ "','"
+ userSettingInfo.getVisit()
+ "','"
+ userSettingInfo.getUniqueVisit()
+ "','"
+ userSettingInfo.getAvgTime()
+ "','"
+ userSettingInfo.getBounceRate()
+ "','OB');";
this.getJt().execute(sql);
}
}
public void saveVisitByDate() {
SqlRowSet s =
this.queryForRowSet(
"SELECT visit_date,COUNT(*) AS visit " + " FROM log_visit " + " GROUP BY visit_date ;");
while (s.next()) {
VisitInfo visitInfo = new VisitInfo();
visitInfo.setName(s.getString("visit_date"));
visitInfo.setVisitQuantity(s.getInt("visit"));
visitInfo.setType("date");
handleSaveVisitInfo(visitInfo);
}
}
@Override
public LikelihoodData retrieve(final Long likelihoodId) {
final SqlRowSet likelihood = this._getLikelihood(likelihoodId);
likelihood.first();
return new LikelihoodData(
likelihood.getLong("id"),
likelihood.getString("name"),
likelihood.getString("code"),
likelihood.getLong("enabled"));
}
public static void testCase1() {
JdbcTemplate select = new JdbcTemplate();
select.setDataSource(DbTelescope.getDataSource());
SqlRowSet rowSet =
select.queryForRowSet(
"select tsp.get_container_ids(1318919, 'content_digital','C') from dual");
while (rowSet.next()) {
System.out.println("RECORD!");
System.out.println("Data: " + rowSet.getString(1));
}
}
@Override
public List<String> listDisabledContent(int groupId) {
List<String> ret = new LinkedList<String>();
SqlRowSet rs =
this.getJdbcTemplate()
.queryForRowSet(
"SELECT [content_id] FROM [t_group_lessoncontent] WHERE [group_id]=?",
new Object[] {groupId});
while (rs.next()) {
ret.add(rs.getString(1));
}
return ret;
}
@Override
public List<DatatableData> retrieveDatatableNames(final String appTable) {
String andClause;
if (appTable == null) {
andClause = "";
} else {
andClause = " and application_table_name = '" + appTable + "'";
}
// PERMITTED datatables
final String sql =
"select application_table_name, registered_table_name"
+ " from x_registered_table "
+ " where exists"
+ " (select 'f'"
+ " from m_appuser_role ur "
+ " join m_role r on r.id = ur.role_id"
+ " left join m_role_permission rp on rp.role_id = r.id"
+ " left join m_permission p on p.id = rp.permission_id"
+ " where ur.appuser_id = "
+ context.authenticatedUser().getId()
+ " and (p.code in ('ALL_FUNCTIONS', 'ALL_FUNCTIONS_READ') or p.code = concat('READ_', registered_table_name))) "
+ andClause
+ " order by application_table_name, registered_table_name";
final SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sql);
final List<DatatableData> datatables = new ArrayList<DatatableData>();
while (rs.next()) {
final String appTableName = rs.getString("application_table_name");
final String registeredDatatableName = rs.getString("registered_table_name");
datatables.add(DatatableData.create(appTableName, registeredDatatableName));
}
return datatables;
}
public List<List<Object>> getCateById(int id) throws Exception {
String sql = "select name,level from t_cy_classify where id=?";
Object[] o = new Object[id];
SqlRowSet rs = commonDao.getRs(sql, o);
List<List<Object>> list = new ArrayList<List<Object>>();
if (rs.next()) {
List<Object> innerList = new ArrayList<Object>();
String name = rs.getString(1);
int level = rs.getInt(2);
innerList.add(name);
innerList.add(level);
list.add(innerList);
}
return list;
}
@Override
public List<LikelihoodData> retrieveAll(final String ppiName) {
final SqlRowSet likelihood = this._getLikelihood(ppiName);
List<LikelihoodData> likelihoodDatas = new ArrayList<LikelihoodData>();
while (likelihood.next()) {
likelihoodDatas.add(
new LikelihoodData(
likelihood.getLong("id"),
likelihood.getString("name"),
likelihood.getString("code"),
likelihood.getLong("enabled")));
}
return likelihoodDatas;
}
private String queryForApplicationTableName(final String datatable) {
final String sql =
"SELECT application_table_name FROM x_registered_table where registered_table_name = '"
+ datatable
+ "'";
final SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sql);
String applicationTableName = null;
if (rs.next()) {
applicationTableName = rs.getString("application_table_name");
} else {
throw new DatatableNotFoundException(datatable);
}
return applicationTableName;
}
public void saveLinkInfo() {
SqlRowSet s =
this.queryForRowSet(
"SELECT idurl,name,title,viewed,unique_view "
+ " FROM log_url "
+ " WHERE STATUS='404';");
while (s.next()) {
LinkInfo linkInfo = new LinkInfo();
linkInfo.setName(s.getString("name"));
if (s.getString("title") == null) linkInfo.setTitle(" ");
else linkInfo.setTitle(s.getString("title"));
linkInfo.setVisit(s.getInt("viewed"));
linkInfo.setUniqueVisit(s.getInt("unique_view"));
String sql =
"INSERT INTO link_info(name,visit,unique_visit,type,title) "
+ " VALUES ('"
+ linkInfo.getName()
+ "','"
+ linkInfo.getVisit()
+ "','"
+ linkInfo.getUniqueVisit()
+ "','DL','"
+ linkInfo.getTitle()
+ "');";
this.getJt().execute(sql);
}
SqlRowSet s1 =
this.queryForRowSet(
"SELECT idurl,name,title,viewed,unique_view " + " FROM log_url " + " WHERE type='O';");
while (s1.next()) {
LinkInfo linkInfo = new LinkInfo();
linkInfo.setName(s1.getString("name"));
if (s1.getString("title") == null) linkInfo.setTitle(" ");
else linkInfo.setTitle(s1.getString("title"));
linkInfo.setVisit(s1.getInt("viewed"));
linkInfo.setUniqueVisit(s1.getInt("unique_view"));
String sql =
"INSERT INTO link_info(name,visit,unique_visit,type,title) "
+ " VALUES ('"
+ linkInfo.getName()
+ "','"
+ linkInfo.getVisit()
+ "','"
+ linkInfo.getUniqueVisit()
+ "','OL','"
+ linkInfo.getTitle()
+ "');";
this.getJt().execute(sql);
}
}
private List<ResultsetRowData> fillDatatableResultSetDataRows(final String sql) {
final SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sql);
final List<ResultsetRowData> resultsetDataRows = new ArrayList<ResultsetRowData>();
final SqlRowSetMetaData rsmd = rs.getMetaData();
while (rs.next()) {
final List<String> columnValues = new ArrayList<String>();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
final String columnName = rsmd.getColumnName(i + 1);
final String columnValue = rs.getString(columnName);
columnValues.add(columnValue);
}
final ResultsetRowData resultsetDataRow = ResultsetRowData.create(columnValues);
resultsetDataRows.add(resultsetDataRow);
}
return resultsetDataRows;
}
public void generateReport(
Program selProgram, List<OrganisationUnit> orgUnitList, Date sDate, Date eDate)
throws Exception {
String raFolderName = reportService.getRAFolderName();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
String query = "";
int rowStart = 3;
int colStart = 1;
int rowCount = rowStart;
int colCount = colStart;
// String outputReportPath = System.getenv( "DHIS2_HOME" ) + File.separator + raFolderName +
// File.separator + "output" + File.separator + UUID.randomUUID().toString() + ".xls";
String outputReportPath =
System.getenv("DHIS2_HOME") + File.separator + Configuration_IN.DEFAULT_TEMPFOLDER;
File newdir = new File(outputReportPath);
if (!newdir.exists()) {
newdir.mkdirs();
}
outputReportPath += File.separator + UUID.randomUUID().toString() + ".xls";
WritableWorkbook outputReportWorkbook = Workbook.createWorkbook(new File(outputReportPath));
WritableSheet sheet0 = outputReportWorkbook.createSheet(selProgram.getName(), 0);
try {
List<PatientIdentifierType> patientIdentifierTypes =
new ArrayList<PatientIdentifierType>(
patientIdentifierTypeService.getAllPatientIdentifierTypes());
Collections.sort(patientIdentifierTypes, new PatientIdentifierTypeComparator());
List<PatientAttribute> patientAttributes =
new ArrayList<PatientAttribute>(patientAttributeService.getAllPatientAttributes());
Collections.sort(patientAttributes, new PatientAttributeComparator());
List<ProgramStage> programStages = new ArrayList<ProgramStage>(selProgram.getProgramStages());
Collections.sort(programStages, new ProgramStageOrderComparator());
Map<ProgramStage, List<DataElement>> programStageDataElementMap =
new HashMap<ProgramStage, List<DataElement>>();
for (ProgramStage programStage : programStages) {
List<ProgramStageDataElement> programStageDataElements =
new ArrayList<ProgramStageDataElement>(programStage.getProgramStageDataElements());
List<DataElement> dataElements = new ArrayList<DataElement>();
for (ProgramStageDataElement programStageDataElement : programStageDataElements) {
dataElements.add(programStageDataElement.getDataElement());
}
Collections.sort(dataElements, new IdentifiableObjectNameComparator());
programStageDataElementMap.put(programStage, dataElements);
}
// Printing Header Information
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "OrgUnit Hierarchy", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "OrgUnit", getCellFormat1()));
colCount++;
for (PatientIdentifierType patientIdentifierType : patientIdentifierTypes) {
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(
new Label(colCount, rowCount - 1, patientIdentifierType.getName(), getCellFormat1()));
colCount++;
}
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Benificiary ID", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Benificiary Name", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Gender", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Age", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Data of Birth", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Blood Group", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Registration Date", getCellFormat1()));
colCount++;
for (PatientAttribute patientAttribute : patientAttributes) {
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(
new Label(colCount, rowCount - 1, patientAttribute.getName(), getCellFormat1()));
colCount++;
}
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Incident Date", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Enrollment Date", getCellFormat1()));
colCount++;
for (ProgramStage programStage : programStages) {
List<DataElement> dataElementList =
new ArrayList<DataElement>(programStageDataElementMap.get(programStage));
sheet0.mergeCells(
colCount, rowCount - 1, colCount + dataElementList.size() + 1, rowCount - 1);
sheet0.addCell(new Label(colCount, rowCount - 1, programStage.getName(), getCellFormat1()));
sheet0.addCell(new Label(colCount, rowCount, "Due Date", getCellFormat1()));
colCount++;
sheet0.addCell(new Label(colCount, rowCount, "Execution Date", getCellFormat1()));
colCount++;
for (DataElement dataElement : dataElementList) {
sheet0.addCell(
new Label(
colCount,
rowCount,
dataElement.getName() + "--" + dataElement.getType(),
getCellFormat1()));
colCount++;
}
}
rowCount++;
for (OrganisationUnit orgUnit : orgUnitList) {
if (sDate != null && eDate != null) {
query =
"SELECT patient.patientid, programinstance.programinstanceid,programinstance.dateofincident,programinstance.enrollmentdate FROM programinstance INNER JOIN patient "
+ " ON programinstance.patientid = patient.patientid "
+ " WHERE patient.organisationunitid = "
+ orgUnit.getId()
+ " AND programinstance.programid = "
+ selProgram.getId()
+ " AND patient.registrationdate >= '"
+ startDate
+ "'"
+ " AND patient.registrationdate <= '"
+ endDate
+ "' "
+ " AND enddate IS NULL";
} else {
query =
"SELECT patient.patientid, programinstance.programinstanceid,programinstance.dateofincident,programinstance.enrollmentdate FROM programinstance INNER JOIN patient "
+ " ON programinstance.patientid = patient.patientid "
+ " WHERE patient.organisationunitid = "
+ orgUnit.getId()
+ " AND programinstance.programid = "
+ selProgram.getId()
+ " AND enddate IS NULL";
}
SqlRowSet sqlResultSet = jdbcTemplate.queryForRowSet(query);
if (sqlResultSet != null) {
int count = 1;
String orgUnitBranch = "";
sqlResultSet.beforeFirst();
while (sqlResultSet.next()) {
colCount = colStart;
if (orgUnit.getParent() != null) {
orgUnitBranch = getOrgunitBranch(orgUnit.getParent());
} else {
orgUnitBranch = " ";
}
sheet0.addCell(new Label(colCount, rowCount, orgUnitBranch, getCellFormat2()));
colCount++;
sheet0.addCell(new Label(colCount, rowCount, orgUnit.getName(), getCellFormat2()));
colCount++;
int patientId = sqlResultSet.getInt(1);
int programInstanceId = sqlResultSet.getInt(2);
Date dateOfIncident = sqlResultSet.getDate(3);
Date dateOfEnrollment = sqlResultSet.getDate(4);
Patient patient = patientService.getPatient(patientId);
// Patient Identifier Details
for (PatientIdentifierType patientIdentifierType : patientIdentifierTypes) {
query =
"SELECT identifier from patientidentifier WHERE patientidentifiertypeid = "
+ patientIdentifierType.getId()
+ " AND patientid = "
+ patient.getId();
SqlRowSet sqlResultSet1 = jdbcTemplate.queryForRowSet(query);
if (sqlResultSet1 != null && sqlResultSet1.next()) {
String value = sqlResultSet1.getString(1);
if (value != null && !value.trim().equalsIgnoreCase("")) {
sheet0.addCell(new Label(colCount, rowCount, value, getCellFormat2()));
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
}
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
}
colCount++;
}
// Patient Properties
sheet0.addCell(
new Label(colCount, rowCount, patient.getId().toString(), getCellFormat2()));
colCount++;
sheet0.addCell(new Label(colCount, rowCount, patient.getFullName(), getCellFormat2()));
colCount++;
sheet0.addCell(
new Label(colCount, rowCount, patient.getTextGender(), getCellFormat2()));
colCount++;
sheet0.addCell(new Label(colCount, rowCount, patient.getAge(), getCellFormat2()));
colCount++;
sheet0.addCell(
new Label(
colCount,
rowCount,
simpleDateFormat.format(patient.getBirthDate()),
getCellFormat2()));
colCount++;
/**
* TODO BloodGroup is removed from Patient Object, so need to change this accordingly
*/
sheet0.addCell(
new Label(colCount, rowCount, "" /*patient.getBloodGroup()*/, getCellFormat2()));
colCount++;
sheet0.addCell(
new Label(
colCount,
rowCount,
simpleDateFormat.format(patient.getRegistrationDate()),
getCellFormat2()));
colCount++;
// Patient Attribute Values
for (PatientAttribute patientAttribute : patientAttributes) {
query =
"SELECT value from patientattributevalue WHERE patientid = "
+ patient.getId()
+ " AND patientattributeid = "
+ patientAttribute.getId();
SqlRowSet sqlResultSet1 = jdbcTemplate.queryForRowSet(query);
if (sqlResultSet1 != null && sqlResultSet1.next()) {
String value = sqlResultSet1.getString(1);
if (value != null && !value.trim().equalsIgnoreCase("")) {
sheet0.addCell(new Label(colCount, rowCount, value, getCellFormat2()));
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
}
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
}
colCount++;
}
// Program Enrollment Details
sheet0.addCell(
new Label(
colCount, rowCount, simpleDateFormat.format(dateOfIncident), getCellFormat2()));
colCount++;
sheet0.addCell(
new Label(
colCount,
rowCount,
simpleDateFormat.format(dateOfEnrollment),
getCellFormat2()));
colCount++;
// ProgramStage Values
for (ProgramStage programStage : programStages) {
query =
"SELECT programstageinstanceid,duedate,executiondate from programstageinstance "
+ " WHERE programinstanceid = "
+ programInstanceId
+ " AND programstageid = "
+ programStage.getId();
SqlRowSet sqlResultSet2 = jdbcTemplate.queryForRowSet(query);
Integer programStageInstanceId = 0;
if (sqlResultSet2 != null && sqlResultSet2.next()) {
programStageInstanceId = sqlResultSet2.getInt(1);
// ProgramStage DueDate and Execution Date
Date dueDate = sqlResultSet2.getDate(2);
Date exeDate = sqlResultSet2.getDate(3);
if (dueDate != null) {
String dueDateStr = simpleDateFormat.format(dueDate);
sheet0.addCell(new Label(colCount, rowCount, dueDateStr, getCellFormat3()));
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat3()));
}
colCount++;
if (exeDate != null) {
String exeDateStr = simpleDateFormat.format(exeDate);
sheet0.addCell(new Label(colCount, rowCount, exeDateStr, getCellFormat3()));
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat3()));
}
colCount++;
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat3()));
colCount++;
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat3()));
colCount++;
}
for (DataElement dataElement : programStageDataElementMap.get(programStage)) {
query =
"SELECT value from patientdatavalue WHERE programstageinstanceid = "
+ programStageInstanceId
+ " AND dataelementid = "
+ dataElement.getId();
// " AND organisationunitid = " + orgUnit.getId();
SqlRowSet sqlResultSet3 = jdbcTemplate.queryForRowSet(query);
if (sqlResultSet3 != null && sqlResultSet3.next()) {
String value = sqlResultSet3.getString(1);
if (dataElement.getType().equalsIgnoreCase(DataElement.VALUE_TYPE_BOOL)) {
if (value.equalsIgnoreCase("false")) value = "No";
else value = "Yes";
}
if (value != null && !value.trim().equalsIgnoreCase("")) {
sheet0.addCell(new Label(colCount, rowCount, value, getCellFormat2()));
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
}
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
}
colCount++;
}
}
rowCount++;
}
}
}
} catch (Exception e) {
System.out.println("Exception: " + e.getMessage());
e.printStackTrace();
}
outputReportWorkbook.write();
outputReportWorkbook.close();
fileName = selProgram.getName() + ".xls";
File outputReportFile = new File(outputReportPath);
inputStream = new BufferedInputStream(new FileInputStream(outputReportFile));
outputReportFile.deleteOnExit();
}
public List<TrackerItem> getTrackAll(
TrackerFilterEnum filter,
User currentUser,
Date startDate,
int topics,
int offset,
final int messagesInPage) {
MapSqlParameterSource parameter = new MapSqlParameterSource();
parameter.addValue("interval", startDate);
parameter.addValue("topics", topics);
parameter.addValue("offset", offset);
String partIgnored;
if (currentUser != null) {
partIgnored = queryPartIgnored + queryPartTagIgnored;
parameter.addValue("userid", currentUser.getId());
} else {
partIgnored = "";
}
String partFilter;
switch (filter) {
case ALL:
partFilter = "";
break;
case NOTALKS:
partFilter = queryPartNoTalks;
break;
case MAIN:
partFilter = queryPartMain;
break;
case TECH:
partFilter = queryPartTech;
break;
default:
partFilter = "";
}
boolean showUncommited =
currentUser != null && (currentUser.isModerator() || currentUser.isCorrector());
String partUncommited = showUncommited ? "" : noUncommited;
String query;
query =
String.format(
queryTrackerMain,
partUncommited,
partIgnored,
partFilter,
partUncommited,
partIgnored,
partFilter);
SqlRowSet resultSet = jdbcTemplate.queryForRowSet(query, parameter);
List<TrackerItem> res = new ArrayList<>(topics);
while (resultSet.next()) {
User author = userDao.getUserCached(resultSet.getInt("author"));
int msgid = resultSet.getInt("id");
Timestamp lastmod = resultSet.getTimestamp("lastmod");
int stat1 = resultSet.getInt("stat1");
int groupId = resultSet.getInt("gid");
String groupTitle = resultSet.getString("gtitle");
String title = StringUtil.makeTitle(resultSet.getString("title"));
int cid = resultSet.getInt("cid");
User lastCommentBy;
try {
int id = resultSet.getInt("last_comment_by");
if (id != 0) {
lastCommentBy = userDao.getUserCached(id);
} else {
lastCommentBy = null;
}
} catch (UserNotFoundException e) {
throw new RuntimeException(e);
}
boolean resolved = resultSet.getBoolean("resolved");
int section = resultSet.getInt("section");
String groupUrlName = resultSet.getString("urlname");
Timestamp postdate = resultSet.getTimestamp("postdate");
boolean uncommited = resultSet.getBoolean("smod") && !resultSet.getBoolean("moderate");
int pages = Topic.getPageCount(stat1, messagesInPage);
ImmutableList<String> tags;
tags = topicTagService.getTagsForTitle(msgid);
res.add(
new TrackerItem(
author,
msgid,
lastmod,
stat1,
groupId,
groupTitle,
title,
cid,
lastCommentBy,
resolved,
section,
groupUrlName,
postdate,
uncommited,
pages,
tags));
}
return res;
}
@Override
public Collection<SchedulingProgramObject> getSendMesssageEvents(String dateToCompare) {
String sql = " ( " + sendMessageToTrackedEntityInstanceSql(dateToCompare) + " ) ";
sql += " UNION ( " + sendMessageToOrgunitRegisteredSql(dateToCompare) + " ) ";
sql += " UNION ( " + sendMessageToUsersSql(dateToCompare) + " ) ";
sql += " UNION ( " + sendMessageToUserGroupsSql(dateToCompare) + " ) ";
SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
Collection<SchedulingProgramObject> schedulingProgramObjects = new HashSet<>();
while (rs.next()) {
String message = rs.getString("templatemessage");
int programInstanceId = rs.getInt("programinstanceid");
List<String> attributeUids = reminderService.getAttributeUids(message);
SqlRowSet attributeValueRow =
jdbcTemplate.queryForRowSet(
"select tea.uid ,teav.value from trackedentityattributevalue teav "
+ " INNER JOIN trackedentityattribute tea on tea.trackedentityattributeid=teav.trackedentityattributeid "
+ " INNER JOIN programinstance ps on teav.trackedentityinstanceid=ps.trackedentityinstanceid "
+ " INNER JOIN programstageinstance psi on ps.programinstanceid=psi.programinstanceid "
+ " where tea.uid in ( "
+ getQuotedCommaDelimitedString(attributeUids)
+ ") "
+ " and ps.programinstanceid="
+ programInstanceId);
while (attributeValueRow.next()) {
String uid = attributeValueRow.getString("uid");
String value = attributeValueRow.getString("value");
String key = "\\{(" + TrackedEntityInstanceReminder.ATTRIBUTE + ")=(" + uid + ")\\}";
message = message.replaceAll(key, value);
}
String organisationunitName = rs.getString("orgunitName");
String programName = rs.getString("programName");
String incidentDate = rs.getString("incidentdate").split(" ")[0];
String daysSinceIncidentDate = rs.getString("days_since_incident_date");
String erollmentDate = rs.getString("enrollmentdate").split(" ")[0];
String daysSinceEnrollementDate = rs.getString("days_since_erollment_date");
message =
message.replace(
TrackedEntityInstanceReminder.TEMPLATE_MESSSAGE_PROGRAM_NAME, programName);
message =
message.replace(
TrackedEntityInstanceReminder.TEMPLATE_MESSSAGE_ORGUNIT_NAME, organisationunitName);
message =
message.replace(
TrackedEntityInstanceReminder.TEMPLATE_MESSSAGE_INCIDENT_DATE, incidentDate);
message =
message.replace(
TrackedEntityInstanceReminder.TEMPLATE_MESSSAGE_ENROLLMENT_DATE, erollmentDate);
message =
message.replace(
TrackedEntityInstanceReminder.TEMPLATE_MESSSAGE_DAYS_SINCE_ENROLLMENT_DATE,
daysSinceEnrollementDate);
message =
message.replace(
TrackedEntityInstanceReminder.TEMPLATE_MESSSAGE_DAYS_SINCE_INCIDENT_DATE,
daysSinceIncidentDate);
SchedulingProgramObject schedulingProgramObject = new SchedulingProgramObject();
schedulingProgramObject.setProgramInstanceId(programInstanceId);
schedulingProgramObject.setPhoneNumber(rs.getString("phonenumber"));
schedulingProgramObject.setMessage(message);
schedulingProgramObjects.add(schedulingProgramObject);
}
return schedulingProgramObjects;
}
@Override
protected void buildExcelDocument(
Map<String, Object> model,
HSSFWorkbook workbook,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
// 取得模型数据
SqlRowSet table = (SqlRowSet) model.get("table");
String title = model.get("title").toString();
// 创建工作表和标题
HSSFSheet sheet = workbook.createSheet(title); // 创建工作区
HSSFRow row_title = sheet.createRow(0); // 创建一行引用对象
HSSFFont title_font = workbook.createFont(); // 创建标题的字体
title_font.setFontHeightInPoints((short) 8);
title_font.setFontHeight((short) HSSFFont.BOLDWEIGHT_NORMAL);
title_font.setColor((short) (HSSFFont.COLOR_RED));
HSSFCellStyle title_style = workbook.createCellStyle(); // 创建样式
title_style.setFont(title_font);
HSSFCell cell_title = row_title.createCell(1); // 创建单元格引用对象
cell_title.setCellStyle(title_style);
cell_title.setCellValue(title);
// 创建数据表头
String titles[] = {"学生姓名", "性别", "年龄", "身份证号", "出生日期", "政治面貌", "家庭电话", "家庭地址", "健康状况"};
HSSFRow row = sheet.createRow((short) 1);
HSSFCellStyle items_style = workbook.createCellStyle();
items_style.setAlignment((short) HSSFCellStyle.ALIGN_CENTER);
HSSFFont celltbnamefont = workbook.createFont();
celltbnamefont.setFontHeightInPoints((short) 10);
celltbnamefont.setColor((short) (HSSFFont.COLOR_RED));
items_style.setFont(celltbnamefont);
items_style.setWrapText(true);
for (int i = 0; i < titles.length; i++) {
HSSFCell cell = row.createCell(i);
if (i == 3 || i == 6 || i == 2) {
sheet.setColumnWidth(i, 5335);
} else {
sheet.setColumnWidth(i, 3335);
}
cell.setCellValue(titles[i]);
cell.setCellStyle(items_style);
}
HSSFCellStyle datestyle = workbook.createCellStyle();
HSSFDataFormat df = workbook.createDataFormat();
datestyle.setDataFormat(df.getFormat("yyyy-mm-dd"));
int i = 0;
while (table.next()) {
HSSFRow dataRow = sheet.createRow((short) (i + 2));
for (int j = 0; j < 9; j++) {
HSSFCell cell = dataRow.createCell(j);
String data = table.getString(j + 2);
cell.setCellStyle(datestyle);
cell.setCellValue(data);
}
i++;
}
}