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++; } }