AnsweredAssumed Answered

Finding a Set of Identical Rows and sending Email accordingly

Question asked by sreeram.babu on Jan 27, 2011
Latest reply on Feb 4, 2011 by sreeram.babu
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

Outcomes