Hi Everyone,
I've designed a gel script which would send email to the users whose timesheet is open for Particular duration. The client has come up with a new request asking , if a particular user's time sheet is open for more than 1 week, the mail has to go only once.
Ok heres my problem.The sql query gives the result set data for two users whose time sheet open differs
<gel:script
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="jelly:sql"
xmlns:mail ="jelly:email"
xmlns:xog="http://www.niku.com/xog"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ftp="jelly:com.niku.union.gel.FTPTagLibrary"
xmlns:file="jelly:com.niku.union.gel.FileTagLibrary">
<gel:setDataSource dbId="niku"/>
<!-- <sql:setDataSource
url="jdbc:microsoft:sqlserver://172.22.58.47:1433;DatabaseName=niku"
driver="com.microsoft.jdbc.sqlserver.SQLServerDriver"
user="niku"
password="niku"
/> -->
<sql:query escapeText="false" var="results">
<![CDATA[
SELECT TIME.FULL_NAME FULL_NAME ,
TIME.RES_EMAIL RES_EMAIL ,
TIME.MANAGER MANAGER ,
TIME.MGR_EMAIL MGR_EMAIL,
TIME.WEEK WEEK ,
TIME.STATUS STATUS,
TIME.NO_OF_DAYS NO_OF_DAYS,
TIME.USER_NAME USER_NAME,
TIME.TS_FINISH TS_FINISH
--COUNT(FULL_NAME) ROW_COUNT
FROM ( SELECT SRM.FULL_NAME FULL_NAME,
SRM.EMAIL RES_EMAIL,
MGR.FULL_NAME MANAGER ,
MGR.EMAIL MGR_EMAIL,
rtrim(convert(char,TP.PRSTART,111))+' - '+convert(char,(TP.PRFINISH -1),111) WEEK,
Case when STAT.NAME ='Unsubmitted' then 'Open' end STATUS,
DATEDIFF(d,TP.PRFINISH,getdate()) no_of_days,
CMN.USER_NAME USER_NAME,
rtrim(convert(char,(TP.PRFINISH -1),111)) TS_FINISH
FROM NIKU.PRTIMESHEET TS
INNER JOIN NIKU.SRM_RESOURCES SRM
ON TS.PRRESOURCEID = SRM.ID
INNER JOIN NIKU.PRTIMEPERIOD TP
ON TS.PRTIMEPERIODID = TP.PRID
AND TP.PRISOPEN = 1
INNER JOIN NIKU.CMN_LOOKUPS_V STAT
ON TS.PRSTATUS = STAT.LOOKUP_CODE
INNER JOIN NIKU.SRM_RESOURCES MGR
ON SRM.MANAGER_ID = MGR.USER_ID
INNER JOIN NIKU.CMN_SEC_USERS CMN
ON SRM.UNIQUE_NAME = CMN.USER_NAME
WHERE LANGUAGE_CODE = 'en'
AND LOOKUP_TYPE ='prTimeSheetStatus'
AND PRSTATUS IN (0,
2)
AND CMN.USER_STATUS_ID IN (200,
202)
/*AND PRFINISH > convert(char,getdate(),111)*/
/* AND PRFINISH = NEXT_DAY(TRUNC(SYSDATE-7), 'SATURDAY') */
UNION ALL
SELECT SRM.FULL_NAME FULL_NAME,
SRM.EMAIL RES_EMAIL,
MGR.FULL_NAME MANAGER ,
MGR.EMAIL MGR_EMAIL,
rtrim(convert(char,TP.PRSTART,111))+' - '+convert(char,(TP.PRFINISH -1),111) WEEK,
'Open' STATUS,
DATEDIFF(d,TP.PRFINISH,getdate()) no_of_days,
CMN.USER_NAME USER_NAME,
rtrim(convert(char,(TP.PRFINISH -1),111)) TS_FINISH
FROM NIKU.SRM_RESOURCES SRM
INNER JOIN NIKU.PRJ_RESOURCES PRJR
ON SRM.ID = PRJR.PRID
AND PRJR.PRISOPEN = 1
AND PRISROLE = 0
INNER JOIN NIKU.SRM_RESOURCES MGR
ON SRM.MANAGER_ID = MGR.USER_ID
INNER JOIN NIKU.PRTIMEPERIOD TP
ON SRM.IS_ACTIVE = TP.PRISOPEN
INNER JOIN NIKU.CMN_SEC_USERS CMN
ON SRM.UNIQUE_NAME = CMN.USER_NAME
WHERE CMN.USER_STATUS_ID IN (200,
202)
/*AND PRFINISH > convert(char,getdate(),111)*/
AND NOT EXISTS
(SELECT 'x'
FROM NIKU.PRTIMESHEET TS
WHERE TS.PRRESOURCEID = SRM.ID
AND TS.PRTIMEPERIODID = TP.PRID
)
)
TIME
where USER_NAME in('user1', 'user3')
--Group BY FULL_NAME,WEEK,RES_EMAIL,MANAGER,MGR_EMAIL,STATUS,NO_OF_DAYS,USER_NAME,TS_FINISH
order by FULL_NAME,TS_FINISH DESC
]]>
</sql:query>
<core:forEach items="${results.rowsByIndex}" trim="true" var="objectrow">
<mail:email from="testfrom" to="testto" CC="testmgrmail" subject="test" >
Test Body mail
</mail:email>
</core:forEach>
</gel:script>
The result of the Query is
FULL_NAME
RES_EMAIL
MANAGER
MGR_EMAIL
WEEK
STATUS
NO_OF_DAYS
USER_NAME
TS_FINISH
ROW_COUNT
user 1
testmail@test.com
user 2
testmail@test.com
2010/12/31 - 2011/01/06
Open
20
user1
1/6/2011
1
user 1
testmail@test.com
user 2
testmail@test.com
2010/12/24 - 2010/12/30
Open
27
user1
12/30/2010
1
user 1
testmail@test.com
user 2
testmail@test.com
2010/12/17 - 2010/12/23
Open
34
user1
12/23/2010
1
user 1
testmail@test.com
user 2
testmail@test.com
2010/12/10 - 2010/12/16
Open
41
user1
12/16/2010
1
user 1
testmail@test.com
user 2
testmail@test.com
2010/12/03 - 2010/12/09
Open
48
user1
12/9/2010
1
user 1
testmail@test.com
user 2
testmail@test.com
2010/11/26 - 2010/12/02
Open
55
user1
12/2/2010
1
user 1
testmail@test.com
user 2
testmail@test.com
2010/11/19 - 2010/11/25
Open
62
user1
11/25/2010
1
user 1
testmail@test.com
user 2
testmail@test.com
2010/11/12 - 2010/11/18
Open
69
user1
11/18/2010
1
user3
testmail@test.com
user4
testmail@test.com
2010/12/31 - 2011/01/06
Open
20
user3
1/6/2011
1
user3
testmail@test.com
user4
testmail@test.com
2010/12/24 - 2010/12/30
Open
27
user3
12/30/2010
1
user3
testmail@test.com
user4
testmail@test.com
2010/12/17 - 2010/12/23
Open
34
user3
12/23/2010
1
user3
testmail@test.com
user4
testmail@test.com
2010/12/10 - 2010/12/16
Open
41
user3
12/16/2010
1
user3
testmail@test.com
user4
testmail@test.com
2010/12/03 - 2010/12/09
Open
48
user3
12/9/2010
1
user3
testmail@test.com
user4
testmail@test.com
2010/11/26 - 2010/12/02
Open
55
user3
12/2/2010
1
user3
testmail@test.com
user4
testmail@test.com
2010/11/19 - 2010/11/25
Open
62
user3
11/25/2010
1
user3
testmail@test.com
user4
testmail@test.com
2010/11/12 - 2010/11/18
Open
69
user3
11/18/2010
1
user3
testmail@test.com
user4
testmail@test.com
2010/11/05 - 2010/11/11
Open
76
user3
11/11/2010
1
user3
testmail@test.com
user4
testmail@test.com
2010/10/29 - 2010/11/04
Open
83
user3
11/4/2010
1
user3
testmail@test.com
user4
testmail@test.com
2010/10/22 - 2010/10/28
Open
90
user3
10/28/2010
1
user3
testmail@test.com
user4
testmail@test.com
2010/10/15 - 2010/10/21
Open
97
user3
10/21/2010
1
You could see User 1 has 7 rows and User 3 has remaining 13 rows.
How could I group them using Gel Script or use Core:for each tag only with respect to Full_name
Needed here is the Logic to be used in the Gel script to manipulate the Sql results and send email accordingly..
Thanks in advance
Sreeram