How to use

executeSQL

method

in

org.openmrs.api.AdministrationService

  private List<ConceptClass> getConceptClassesOfOrderTypes() {
    List<ConceptClass> mappedClasses = new ArrayList<>();
    AdministrationService administrationService = Context.getAdministrationService();
    List<List<Object>> result = administrationService.executeSQL(
      "SELECT DISTINCT concept_class_id FROM order_type_class_map", true);
    for (List<Object> temp : result) {
      for (Object value : temp) {
        if (value != null) {
          mappedClasses.add(this.getConceptClass((Integer) value));
        }
      }
    }
    return mappedClasses;
  }
}

@Test
public void executeSQL_shouldExecuteSqlContainingGroupBy() {
  
  String sql = "select encounter1_.location_id, encounter1_.creator, encounter1_.encounter_type, encounter1_.form_id, location2_.location_id, count(obs0_.obs_id) from obs obs0_ right outer join encounter encounter1_ on obs0_.encounter_id=encounter1_.encounter_id inner join location location2_ on encounter1_.location_id=location2_.location_id inner join users user3_ on encounter1_.creator=user3_.user_id inner join person user3_1_ on user3_.user_id=user3_1_.person_id inner join encounter_type encountert4_ on encounter1_.encounter_type=encountert4_.encounter_type_id inner join form form5_ on encounter1_.form_id=form5_.form_id where encounter1_.date_created>='2007-05-05' and encounter1_.date_created<= '2008-05-05' group by encounter1_.location_id, encounter1_.creator , encounter1_.encounter_type , encounter1_.form_id";
  adminService.executeSQL(sql, true);
  
  String sql2 = "select encounter_id, count(*) from encounter encounter_id group by encounter_id";
  adminService.executeSQL(sql2, true);
}

@Test
public void executeSQL_shouldReturnNullGivenNull() {
  
  adminService.executeSQL(null, true);
  
  verify(adminDAO, never()).executeSQL(anyString(), anyBoolean());
}

@Test
public void executeSQL_shouldReturnNullGivenEmptyString() {
  
  adminService.executeSQL(" ", true);
  
  verify(adminDAO, never()).executeSQL(anyString(), anyBoolean());
}

for (String sqlStatement : sqlStatements) {
  if (sqlStatement.trim().length() > 0) {
    as.executeSQL(sqlStatement, false);

@Test
public void stopVisits_shouldCloseAllUnvoidedActiveVisitMatchingTheSpecifiedVisitTypes() {
  executeDataSet("org/openmrs/api/include/VisitServiceTest-includeVisitsAndTypeToAutoClose.xml");
  String[] visitTypeNames = StringUtils.split(Context.getAdministrationService().getGlobalProperty(
    OpenmrsConstants.GP_VISIT_TYPES_TO_AUTO_CLOSE), ",");
  
  String openVisitsQuery = "SELECT visit_id FROM visit WHERE voided = 0 AND date_stopped IS NULL AND visit_type_id IN (SELECT visit_type_id FROM visit_type WHERE NAME IN ('"
      + StringUtils.join(visitTypeNames, "','") + "'))";
  int activeVisitCount = Context.getAdministrationService().executeSQL(openVisitsQuery, true).size();
  //sanity check
  assertTrue("There should be some active visits for this test to be valid", activeVisitCount > 0);
  
  //close any unvoided open visits
  visitService.stopVisits(null);
  
  activeVisitCount = Context.getAdministrationService().executeSQL(openVisitsQuery, true).size();
  
  //all active unvoided visits should have been closed
  assertTrue("Not all active unvoided vists were closed", activeVisitCount == 0);
}

/**
 * @see EncounterService#unvoidEncounter(Encounter)
 */
@Test
public void saveEncounter_shouldNotCascadeToExistingOrder() {
  EncounterService es = Context.getEncounterService();
  
  TestOrder order = (TestOrder) Context.getOrderService().getOrder(7);
  order.setVoided(true);
  
  Encounter encounter = es.getEncounter(6);
  es.saveEncounter(encounter);
  
  String sql = "SELECT voided FROM orders WHERE order_id=7";
  Boolean voided = (Boolean) Context.getAdministrationService().executeSQL(sql, true).get(0).get(0);
  Assert.assertFalse(voided);
}

private int getEncounterProviderCountWithoutFlushingByUuid(String uuid) {
  List<List<Object>> temp = Context.getAdministrationService().executeSQL(
    "select count(*) from encounter_provider where uuid ='" + uuid + "'", true);
  return ((Number) temp.get(0).get(0)).intValue();
}

private int getEncounterProviderCountWithoutFlushing() {
  List<List<Object>> temp = Context.getAdministrationService().executeSQL(
    "select count(*) from encounter_provider where encounter_id = 3", true);
  return ((Number) temp.get(0).get(0)).intValue();
}

  private int getNonVoidedEncounterProviderCount() {
    List<List<Object>> temp = Context.getAdministrationService().executeSQL(
      "select count(*) from encounter_provider where encounter_id = 3 and voided = 0", true);
    return ((Number) temp.get(0).get(0)).intValue();
  }
}

  @Test
  public void shouldPurgeAConceptName() throws Exception {
    String conceptId = "5497";
    //using sql to be able to include voided names too
    Long before = (Long) Context.getAdministrationService()
        .executeSQL("select count(*) from concept_name where concept_id = " + conceptId, true).get(0).get(0);
    
    handle(newDeleteRequest("concept/" + conceptUuid2 + "/name/8230adbf-30a9-4e18-b6d7-fc57e0c23cab", new Parameter(
        "purge", "")));
    
    Long after = (Long) Context.getAdministrationService()
        .executeSQL("select count(*) from concept_name where concept_id = " + conceptId, true).get(0).get(0);
    Assert.assertEquals(before.longValue() - 1, after.longValue());
  }
}