Hi We have made a few template reminders for program stages and are trying to test them. Few months back we had tested the same and they were working. Now they are not. On clicking the execute button no messages are sent and the tomcat log shows this error (I suspect this has to do with the change of program type field)-
** INFO 2015-08-28 14:44:14,852 Start to prepare reminder messages: 00:00:00.001 (Clock.java [taskScheduler-2])* ** ERROR 2015-08-28 14:44:14,915 Unexpected error occurred in scheduled task. (TaskUtils.java [taskScheduler-2])* *org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ ( SELECT pi.programinstanceid, pav.value as phonenumber, prm.templatemessage, org.name <http://org.name> as orgunitName, pg.name <http://pg.name> as programName, pi.dateofincident , pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) 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= 0 and prm.templatemessage is not NULL and prm.templatemessage != '' and pg.type=1 and prm.daysallowedsendmessage is not null and pa.valuetype='phoneNumber' and ( DATE(now()) - DATE(pi.enrollmentdate) ) = prm.daysallowedsendmessage and prm.whenToSend is null and prm.dateToCompare='enrollmentdate' and prm.sendto = 1 ) UNION ( SELECT pi.programinstanceid, org.phonenumber, prm.templatemessage, org.name <http://org.name> as orgunitName, pg.name <http://pg.name> as programName, pi.dateofincident, pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) 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 WHERE pi.status = 0 and org.phonenumber is not NULL and org.phonenumber != '' and prm.templatemessage is not NULL and prm.templatemessage != '' and pg.type=1 and prm.daysallowedsendmessage is not null and ( DATE(now()) - DATE( pi.enrollmentdate ) ) = prm.daysallowedsendmessage and prm.dateToCompare='enrollmentdate' and prm.whenToSend is null and prm.sendto = 3 ) UNION ( SELECT pi.programinstanceid, uif.phonenumber, prm.templatemessage, org.name <http://org.name> as orgunitName, pg.name <http://pg.name> as programName, pi.dateofincident ,pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) 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= 0 and uif.phonenumber is not NULL and uif.phonenumber != '' and prm.templatemessage is not NULL and prm.templatemessage != '' and pg.type=1 and prm.daysallowedsendmessage is not null and ( DATE(now()) - DATE( enrollmentdate ) ) = prm.daysallowedsendmessage and prm.dateToCompare='enrollmentdate' and prm.sendto = 4 ) UNION ( select pi.programinstanceid, uif.phonenumber,prm.templatemessage, org.name <http://org.name> as orgunitName , pg.name <http://pg.name> as programName, pi.dateofincident, pi.enrollmentdate, (DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) 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= 0 and uif.phonenumber is not NULL and uif.phonenumber != '' and prm.templatemessage is not NULL and prm.templatemessage != '' and pg.type=1 and prm.daysallowedsendmessage is not null and ( DATE(now()) - DATE(enrollmentdate) ) = prm.daysallowedsendmessage and prm.whentosend is null and prm.sendto = 5 ) ]; nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = integer* * Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.* * Position: 1077* Kindly help. regards harsh
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
