public Vector<Integer> getAllUrlForTheSameVisit(
      String ip, String configOs, String configBrowser) {
    SqlRowSet s =
        this.queryForRowSet(
            "SELECT *"
                + " FROM log_visit "
                + "WHERE ip= '"
                + ip
                + "'"
                + " AND config_os= '"
                + configOs
                + "'"
                + " AND config_browser_name= '"
                + configBrowser
                + "';");
    Vector<Integer> l = new Vector<Integer>();
    while (s.next()) {
      int id = s.getInt("idvisit");
      SqlRowSet s1 =
          this.queryForRowSet(
              "SELECT DISTINCT id_url "
                  + "FROM log_link_visit_url "
                  + "WHERE idvisit ='"
                  + id
                  + "';");
      while (s1.next()) {
        int idurl = s1.getInt("id_url");
        if (l.contains(idurl) == true) continue;
        else l.add(idurl);
      }
    }

    return l;
  }
예제 #2
0
 @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 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);
   }
 }
 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);
   }
 }
 public void saveOverviewVisitInfo() {
   SqlRowSet s =
       this.queryForRowSet(
           "SELECT COUNT(*) AS visit,COUNT(DISTINCT ip) AS unique_visit " + "FROM log_visit;");
   VisitOverviewInfo visitOverviewInfo = new VisitOverviewInfo();
   while (s.next()) {
     visitOverviewInfo.setVisit(s.getInt("visit"));
     visitOverviewInfo.setUniqueVisit(s.getInt("unique_visit"));
   }
   SqlRowSet s1 =
       this.queryForRowSet(
           "SELECT COUNT(A.visit) AS first_visit "
               + " FROM(SELECT COUNT(*) AS visit "
               + " FROM log_visit "
               + " GROUP BY ip "
               + " HAVING COUNT(*)=1)A ;");
   while (s1.next()) {
     visitOverviewInfo.setFirstVisit(s1.getInt("first_visit"));
   }
   SqlRowSet s2 =
       this.queryForRowSet(
           "SELECT COUNT(id_url) AS page,SUM(time_spent) AS total_time "
               + " FROM log_link_visit_url ;");
   while (s2.next()) {
     float page = 0, time = 0;
     page = s2.getFloat("page");
     time = s2.getFloat("total_time");
     visitOverviewInfo.setPageVisit(page / visitOverviewInfo.getVisit());
     visitOverviewInfo.setAvgTimeVisit(time / visitOverviewInfo.getVisit());
   }
   SqlRowSet s3 =
       this.queryForRowSet(
           "SELECT COUNT(A.visit) AS exit_visit "
               + " FROM(SELECT idvisit,COUNT(id_url) AS visit "
               + " FROM log_link_visit_url "
               + " GROUP BY idvisit "
               + " HAVING COUNT(*)=1)A ;");
   while (s3.next()) {
     float exit_visit = 0;
     exit_visit = s3.getFloat("exit_visit");
     visitOverviewInfo.setExitVisit(exit_visit / visitOverviewInfo.getVisit());
   }
   String sql =
       "INSERT INTO visit_overview_info(visit,unique_visit,first_visit,page_visit,avg_time_visit,exit_visit) "
           + " VALUES ('"
           + visitOverviewInfo.getVisit()
           + "','"
           + visitOverviewInfo.getUniqueVisit()
           + "','"
           + visitOverviewInfo.getFirstVisit()
           + "','"
           + visitOverviewInfo.getPageVisit()
           + "','"
           + visitOverviewInfo.getAvgTimeVisit()
           + "','"
           + visitOverviewInfo.getExitVisit()
           + "');";
   this.getJt().execute(sql);
 }
 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;
 }
 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);
   }
 }
 public LogLinkVisitUrl getLastestrRequestOfVisit(int idVisit) {
   // TODO Auto-generated method stub
   SqlRowSet s =
       this.queryForRowSet(
           "SELECT *" + " FROM log_link_visit_url " + "WHERE idvisit= '" + idVisit + "'");
   LogLinkVisitUrl l = new LogLinkVisitUrl();
   while (s.next()) {
     l.setIdLink(s.getInt("idlink"));
     l.setIdVisit(s.getInt("idVisit"));
     l.setStartTime(s.getTime("start_time"));
     l.setIdUrl(s.getInt("id_url"));
     l.setIdUrlRef(s.getInt("id_url_ref"));
   }
   return l;
 }
 public void updateBounceRateAndExitRateForUrl() {
   SqlRowSet s = this.queryForRowSet("SELECT * FROM log_url");
   while (s.next()) {
     int id = s.getInt("idurl");
     updateBounceRateAndExitRateForUrl(id);
   }
 }
  private void createOrgUnitUuids() {
    try {
      SqlRowSet resultSet =
          jdbcTemplate.queryForRowSet("SELECT * from organisationunit WHERE uuid IS NULL");
      int count = 0;

      while (resultSet.next()) {
        ++count;
        int id = resultSet.getInt("organisationunitid");
        String sql =
            "update organisationunit set uuid = '"
                + UUID.randomUUID().toString()
                + "' where organisationunitid = "
                + id;
        jdbcTemplate.update(sql);
      }

      if (count > 0) {
        log.info(count + " UUIDs updated on organisationunit");
      }
    } catch (Exception ex) // Log and continue
    {
      log.error("Problem updating organisationunit: ", ex);
    }
  }
 public int getIdOfVisit(LogInfo logInfo) {
   // TODO Auto-generated method stub
   // TODO Auto-generated method stub
   SqlRowSet s =
       this.queryForRowSet(
           "SELECT *"
               + " FROM log_visit "
               + "WHERE ip= '"
               + logInfo.getIp()
               + "'"
               + " AND visit_date= '"
               + DateUtil.handleDateFormat(logInfo.getDate())
               + "'"
               + " AND config_os= '"
               + logInfo.getOs()
               + "'"
               + " AND config_browser_name= '"
               + logInfo.getBrowser()
               + "';");
   int id = 0;
   while (s.next()) {
     id = s.getInt("idvisit");
   }
   return id;
 }
예제 #12
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 boolean checkUrlExistInVisit(int idUrl, int idvisit) {
   SqlRowSet s =
       this.queryForRowSet(
           "SELECT *" + " FROM log_link_visit_url " + "WHERE idvisit= '" + idvisit + "'");
   while (s.next()) {
     if (idUrl == s.getInt("id_url")) return true;
   }
   return false;
 }
 @Override
 public Integer getLineId(final int lineNo, final String file, final String project) {
   final String sql = "SELECT (lineid) FROM lines WHERE " + "project=? AND file=? AND lineno=?;";
   SqlRowSet lineRows = jdbcTemplate.queryForRowSet(sql, new Object[] {project, file, lineNo});
   if (lineRows.first()) {
     return lineRows.getInt("lineId");
   }
   return null;
 }
예제 #15
0
 @Override
 public int queryCountAll() {
   String sql = XormUtil.createSqlForSelectCountAll(User.class);
   SqlRowSet set = getJdbcTemplateRead().queryForRowSet(sql);
   if (set.next()) {
     int res = set.getInt(1);
     return res;
   }
   return 0;
 }
 public int getIdForUrl(String url) {
   // TODO Auto-generated method stub
   if (url.equals("-")) return -1;
   SqlRowSet s =
       this.queryForRowSet("SELECT * " + " FROM log_url " + "WHERE name= '" + url + "';");
   int id = 0;
   while (s.next()) {
     id = s.getInt("idurl");
   }
   return id;
 }
  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 saveVisitByYear() {
   SqlRowSet s =
       this.queryForRowSet(
           "SELECT year,COUNT(*) AS visit " + " FROM log_visit " + " GROUP BY year ;");
   while (s.next()) {
     VisitInfo visitInfo = new VisitInfo();
     visitInfo.setName(s.getString("year"));
     visitInfo.setVisitQuantity(s.getInt("visit"));
     visitInfo.setType("year");
     handleSaveVisitInfo(visitInfo);
   }
 }
 public void saveVisitPageInfo() {
   // TODO Auto-generated method stub
   SqlRowSet s =
       this.queryForRowSet(
           "SELECT idvisit,COUNT(id_url) AS page "
               + " FROM log_link_visit_url "
               + " GROUP BY idvisit;");
   while (s.next()) {
     VisitPage visitPage = new VisitPage();
     visitPage.setIdvisit(s.getInt("idvisit"));
     visitPage.setNumOfPage(s.getInt("page"));
     String sql =
         "INSERT INTO visit_with_page(idvisit,page_of_visit) "
             + "VALUES ('"
             + visitPage.getIdvisit()
             + "','"
             + visitPage.getNumOfPage()
             + "')";
     this.getJt().execute(sql);
   }
 }
예제 #20
0
  public int addValue(String userId, AddValueDTO addValueDTO) {

    String path = addValueDTO.getPath();
    String name = addValueDTO.getName();
    String value = addValueDTO.getValue();

    String notes = addValueDTO.getNotes();
    String idQuery = " select  max(id) as id from trackit_user_data";

    SqlRowSet idRowSet = jdbcTemplate.queryForRowSet(idQuery);

    idRowSet.beforeFirst();
    int id = -1;
    while (idRowSet.next()) {
      id = idRowSet.getInt("id");
    }
    if (id == -1) {
      return 0;
    }

    id = id + 1;
    String query =
        "insert into trackit_user_data(`id`,`user_id`, `name`, `path`, `last_updated`, `notes`) values(?,?,?,?,?, ?)";

    List<Object> args = new ArrayList<Object>();
    args.add(id);
    args.add(userId);
    args.add(name);
    args.add(path);

    if (addValueDTO.getTime() == -1) {
      java.sql.Timestamp sq = new java.sql.Timestamp(new Date().getTime());

      args.add(sq);
    } else {
      args.add(new java.sql.Timestamp(addValueDTO.getTime()));
    }
    args.add(notes);
    int response = jdbcTemplate.update(query, args.toArray());

    if (response == 1) {
      query =
          "insert into trackit_user_values(`user_data_id`, `name`, `numeric_value`) values(?,?,?)";
      args = new ArrayList<Object>();
      args.add(id);
      args.add("count");
      args.add(Double.parseDouble(value));
      return jdbcTemplate.update(query, args.toArray());
    }

    return 0;
  }
예제 #21
0
 private List<InstPlayerCard> listCacheCommonHandler(String sql, int instPlayerId) {
   List<InstPlayerCard> modelList = new ArrayList<InstPlayerCard>();
   PlayerMemObj playerMemObj = getPlayerMemObjByPlayerId(instPlayerId);
   SqlRowSet rsSet = this.getJdbcTemplate().queryForRowSet(sql.toString());
   while (rsSet.next()) {
     int id = rsSet.getInt("id");
     int dbVersion = rsSet.getInt("version");
     InstPlayerCard model = playerMemObj.instPlayerCardMap.get(id);
     if (model == null) {
       model = getModel(id, instPlayerId);
       model.result = "";
       modelList.add(model);
     } else {
       int cacheVersion = model.getVersion();
       if (cacheVersion != dbVersion) {
         model = getModel(id, instPlayerId);
       }
       model.result = "";
       modelList.add(model);
     }
   }
   return modelList;
 }
예제 #22
0
 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;
 }
  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();
  }
  @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;
  }
  public void execute() throws Exception {
    for (String table : tables) {
      try {
        log.debug("Checking table: " + table);

        int count = 0;

        SqlRowSet resultSet =
            jdbcTemplate.queryForRowSet("SELECT * from " + table + " WHERE uid IS NULL");

        while (resultSet.next()) {
          ++count;
          String idColumn = getIdColumn(table);
          int id = resultSet.getInt(idColumn);
          String sql =
              "update "
                  + table
                  + " set uid = '"
                  + CodeGenerator.generateCode()
                  + "' where "
                  + idColumn
                  + " = "
                  + id;
          jdbcTemplate.update(sql);
        }

        if (count > 0) {
          log.info(count + " uids set on " + table);
        }

        count = 0;

        resultSet =
            jdbcTemplate.queryForRowSet("SELECT * from " + table + " WHERE lastUpdated IS NULL");

        String timestamp = DateUtils.getLongDateString();

        while (resultSet.next()) {
          ++count;
          String idColumn = getIdColumn(table);
          int id = resultSet.getInt(idColumn);
          String sql =
              "update "
                  + table
                  + " set lastupdated = '"
                  + timestamp
                  + "' where "
                  + idColumn
                  + " = "
                  + id;
          jdbcTemplate.update(sql);
        }

        if (count > 0) {
          log.info(count + " last updated set on " + table);
        }

        count = 0;

        resultSet =
            jdbcTemplate.queryForRowSet("SELECT * from " + table + " WHERE created IS NULL");

        while (resultSet.next()) {
          ++count;
          String idColumn = getIdColumn(table);
          int id = resultSet.getInt(idColumn);
          String sql =
              "update "
                  + table
                  + " set created = '"
                  + timestamp
                  + "' where "
                  + idColumn
                  + " = "
                  + id;
          jdbcTemplate.update(sql);
        }

        if (count > 0) {
          log.info(count + " created timestamps set on " + table);
        }
      } catch (Exception ex) // Log and continue
      {
        log.error("Problem updating: " + table + ", id column: " + getIdColumn(table), ex);
      }
    }

    log.debug("Identifiable properties updated");

    createUidConstraints();

    log.debug("Identifiable constraints updated");

    createOrgUnitUuids();

    log.debug("Organisation unit uuids updated");

    updatePasswordLastUpdated();

    log.debug("UserCredential passwordLastUpdated updated");
  }
예제 #26
0
  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;
  }