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;
 }
Exemple #8
0
  @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);
  }
Exemple #9
0
  @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;
 }
Exemple #11
0
  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"));
  }
Exemple #17
0
  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;
  }
Exemple #28
0
  @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++;
    }
  }