private String sendMessageToUsersSql(String dateToCompare) {
return "SELECT pi.programinstanceid, uif.phonenumber, prm.templatemessage, org.name as orgunitName, pg.name as programName, pi.incidentDate ,"
+ "pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, "
+ "(DATE(now()) - DATE(pi.incidentDate) ) as days_since_incident_date "
+ "FROM trackedentityinstance p INNER JOIN programinstance pi "
+ " ON p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN program pg "
+ " ON pg.programid=pi.programid INNER JOIN organisationunit org "
+ " ON org.organisationunitid = p.organisationunitid INNER JOIN trackedentityinstancereminder prm "
+ " ON prm.programid = pi.programid INNER JOIN usermembership ums "
+ " ON ums.organisationunitid = p.organisationunitid INNER JOIN userinfo uif "
+ " ON uif.userinfoid = ums.userinfoid "
+ "WHERE pi.status= '"
+ EventStatus.ACTIVE.name()
+ "' and uif.phonenumber is not NULL and uif.phonenumber != '' "
+ " and prm.templatemessage is not NULL and prm.templatemessage != '' "
+ " and pg.type='"
+ ProgramType.WITH_REGISTRATION.name()
+ "' and prm.daysallowedsendmessage is not null "
+ " and ( DATE(now()) - DATE( "
+ dateToCompare
+ " ) ) = prm.daysallowedsendmessage "
+ " and prm.dateToCompare='"
+ dateToCompare
+ "' and prm.sendto = "
+ TrackedEntityInstanceReminder.SEND_TO_ALL_USERS_IN_ORGUGNIT_REGISTERED;
}
private String sendMessageToUserGroupsSql(String dateToCompare) {
return "select pi.programinstanceid, uif.phonenumber,prm.templatemessage, org.name as orgunitName ,"
+ " pg.name as programName, pi.incidentDate, pi.enrollmentdate, (DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, "
+ "(DATE(now()) - DATE(pi.incidentDate) ) as days_since_incident_date "
+ " from trackedentityinstance p INNER JOIN programinstance pi "
+ " ON p.trackedentityinstanceid=pi.trackedentityinstanceid "
+ " INNER JOIN program pg "
+ " ON pg.programid=pi.programid "
+ " INNER JOIN organisationunit org "
+ " ON org.organisationunitid = p.organisationunitid "
+ " INNER JOIN trackedentityinstancereminder prm "
+ " ON prm.programid = pg.programid "
+ " INNER JOIN usergroupmembers ugm "
+ " ON ugm.usergroupid = prm.usergroupid "
+ " INNER JOIN userinfo uif "
+ " ON uif.userinfoid = ugm.userid "
+ " WHERE pi.status= '"
+ EventStatus.ACTIVE.name()
+ "' and uif.phonenumber is not NULL and uif.phonenumber != '' "
+ " and prm.templatemessage is not NULL and prm.templatemessage != '' "
+ " and pg.type='"
+ ProgramType.WITH_REGISTRATION.name()
+ "' and prm.daysallowedsendmessage is not null "
+ " and ( DATE(now()) - DATE("
+ dateToCompare
+ ") ) = prm.daysallowedsendmessage "
+ " and prm.whentosend is null "
+ " and prm.sendto = "
+ TrackedEntityInstanceReminder.SEND_TO_USER_GROUP;
}
private String sendMessageToTrackedEntityInstanceSql(String dateToCompare) {
return "SELECT pi.programinstanceid, pav.value as phonenumber, prm.templatemessage, "
+ " org.name as orgunitName, "
+ " pg.name as programName, pi.incidentDate, "
+ " pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, "
+ " (DATE(now()) - DATE(pi.incidentDate) ) as days_since_incident_date "
+ " FROM trackedentityinstance p INNER JOIN programinstance pi "
+ " ON p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN program pg "
+ " ON pg.programid=pi.programid INNER JOIN organisationunit org "
+ " ON org.organisationunitid = p.organisationunitid INNER JOIN trackedentityinstancereminder prm "
+ " ON prm.programid = pi.programid INNER JOIN trackedentityattributevalue pav "
+ " ON pav.trackedentityinstanceid=p.trackedentityinstanceid INNER JOIN trackedentityattribute pa "
+ " ON pa.trackedentityattributeid=pav.trackedentityattributeid "
+ " WHERE pi.status= '"
+ EventStatus.ACTIVE.name()
+ "' and prm.templatemessage is not NULL and prm.templatemessage != '' "
+ " and pg.type='"
+ ProgramType.WITH_REGISTRATION.name()
+ "' and prm.daysallowedsendmessage is not null and pa.valuetype='phoneNumber' "
+ " and ( DATE(now()) - DATE(pi."
+ dateToCompare
+ ") ) = prm.daysallowedsendmessage "
+ " and prm.whenToSend is null and prm.dateToCompare='"
+ dateToCompare
+ "' and prm.sendto = "
+ TrackedEntityInstanceReminder.SEND_TO_TRACKED_ENTITY_INSTANCE;
}