AnsweredAssumed Answered

Daily TimeSheet Reminders using GEL

Question asked by timalex on Feb 8, 2010
Latest reply on Jun 23, 2010 by timalex
Okay here's the deal. I've written a GEL script for the purposes of reminding our consulting staff at the beginning of each morning if the previous day's time entry is less than 7.0 hours, this is contained in a process and is scheduled to run Tuesday thru Saturday so that it picks up the time entries for Monday - Friday.  It works fantastic except for when one of the consultant's goes on vacation or is out sick and they submit their time in advance. When that occurs the notification is still generated to the consultant in question thinking they have 0.0 hours which is incorrect because they've already submitted time for that day.    My question is how can i get around this situation, or more specifically where can i 'grab' a particular day's time entry if the timesheet has already been submitted.    I will include my example code with includes the sql i'm using. Feel free to use / modify this code. I just need someone to help me figure out how to get around the issue above.                                                 SELECT  Convert(numeric(18,2),SUM(Slice)) as Hours,      r.Full_Name,r.Email as Res_Email,r1.Email as Mgr_Email,      r1.FULL_NAME MANAGER,  DATENAME (DW,dateadd(dd,-1, getdate())) +' '+ CONVERT(VARCHAR, dateadd(dd,-1, getdate()), 107) DayFROM          prj_blb_slices s inner join        prtimeentry te on  s.prj_object_id = te.prid inner join          prtimesheet ts on te.prtimesheetid = ts.prid inner join        prtimeperiod tp on ts.prtimeperiodid = tp.prid inner join        srm_resources r on ts.prresourceid = r.id inner join        srm_resources r1 on     r.MANAGER_ID = r1.USER_ID    inner join        prj_obs_associations g on  r.id = g.record_id inner join        nbi_dim_obs h  on g.unit_id = h.obs_unit_id  WHERE          slice_date = CONVERT(VARCHAR(10), dateadd(dd,-1, getdate()), 120)AND        ts.prstatus='0'AND    h.obs_type_id = 5000001  ANDg.table_name = 'SRM_RESOURCES'  ANDh.Level4_Name = 'Consulting - US'Group by r.full_name, r.email, r1.email,r1.full_name  HAVING Convert(numeric(18,2),SUM(Slice))                              Resource Name:  ${row[i+1]}Manager Name:   ${row[i+4]}    Yesterday’s timesheet (${row[i+5]}) was not complete.Total Hours for that day: ${row[i]}${bodyText}  You can access your timesheets by clicking on this link: ${timeLink}                             

Outcomes