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;
}
@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;
}
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;
}
@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);
}
}
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;
}
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;
}
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");
}
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;
}