dhis2-devs team mailing list archive

[Harsh Atal <harsh.atal@xxxxxxxxx>]

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