Clarity

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

Easiest thing is to store the user in your foreach loop, and only send an email when the currently processing user differs from the 'last stored' user.

(If the detail in your email is referencing the open period, then the GEL gets a bit more complex)

Thanks for the Quick reply Dave.

I think I was not clear..

As I said earlier 7 rows for User 1 and 13 rows for the User 3

If the User 1 gets single Row, send Email only with set of details.

If the User 1 gets more than one row, Send email with different set of Details

Same set of rules for User 3


I've got nearly 500 users

How do I find the Row count just for the User 1 and Find the row count for User 3

Could you please just help me frame how the loop has to be designed

<core:forEach items="${results.rowsByIndex}" trim="true" var="objectrow">

<core:forEach items="${results.rowcount}" trim="true" var="count">

Count for user 1---what condition do I need to use


The Open I've mentioned in the Query is to find the unsubmitted Timesheet after particular duration.


thanks

I understand your problem; but you need to restructure the code to solve it - there is not going to be a simple one-line thing you can do.

--

Your code iterates around the missing timesheets.

You do not know when you process row 1 whether there is a row 2 for the same user.

You do not know until you have iterated around all rows whether you need to send a email with 1 timeperiod in it or an email with 50 timeperiods in it.

Therefore;

You could build up a list of "details" in the iteration, and then send an email with that list of details when you start processing a "new" user (and clear your details list).

OR

You could restructure your code completely; have 2 SQL statements that you iterate around; the first works out who (just the user) you need to send emails to (and selects no period information), then the second SQL retrieves the details for that user. Then you send the email.

--

No simple answer, you need to do some significant work to the code either way. :sad

Thanks dave..

Let me try using both logic..will update how it went


Regards

Sreeram

Dave...
Finally very long code was created...
I used splitting the sql query...
in First just got the name and the row count ...
For the Row count = 1, again queried and got details of the user;send a type of mail
For the Row count >1 again queried and got details of the user ;send a different mail.

Thanks for the logic

Cheers
Sreeram

Hi sreeram,

We currently send out timesheet notices via MS Access, but i generate the data from pl/sql code against clarity 12. If you are interested, I can send you the pl/sql code that we use for Overdue timesheets, timesheets pending approval, simple timesheet status reports, etc.

Perhaps they might be easier to use/manipulate than your code?

Thanks

That would be great thompson

Thanks in advance

Regards
Sreeram