I do not know which or how to do this. Have lengthy <cfquery> for MS SQL. Have <cfmail> tag to direct results of query to appropriate recipients.
Here's query:
<cfquery datasource="#Application.DSN2#" name="allinone">
SELECT
USER_PIN,
USER_LNM + ', ' + USER_FNM AS FullName,
TEAM_ABBR,
BRNCH_ABBR,
POSN_ABBR,
USER_EMAIL,
(
SELECT E1.USER_EMAIL
FROM USER_VIEW_3 AS E1
WHERE E1.TEAM_ABBR = USER_VIEW_3.TEAM_ABBR
AND E1.POSN_ABBR = 'TL'
) AS TL_EMAIL,
(
SELECT E3.POSN_ABBR
FROM USER_VIEW_3 AS E3
WHERE E3.TEAM_ABBR = USER_VIEW_3.TEAM_ABBR
AND E3.POSN_ABBR = 'TL'
) AS TL_POSITION,
(
SELECT E2.USER_EMAIL
FROM USER_VIEW_3 AS E2
WHERE E2.BRNCH_ABBR = USER_VIEW_3.BRNCH_ABBR
AND E2.POSN_ABBR = 'BC'
) AS BC_EMAIL,
(
SELECT E4.POSN_ABBR
FROM USER_VIEW_3 AS E4
WHERE E4.BRNCH_ABBR = USER_VIEW_3.BRNCH_ABBR
AND E4.POSN_ABBR = 'BC'
) AS BC_POSITION,
(
SELECT E5.POSN_ABBR
FROM USER_VIEW_3 AS E5
WHERE E5.POSN_ABBR = 'DD'
) AS DD_POSITION,
(
SELECT E6.USER_EMAIL
FROM USER_VIEW_3 AS E6
WHERE E6.POSN_ABBR = 'DD'
) AS DD_EMAIL
FROM USER_VIEW_3
WHERE USER_PIN = '#Form.EMPPIN#'
</cfquery>
And here's <cfmail>
<cfif cgi.SERVER_NAME is not "localhost">
<cfmail to="#allinone.TL_EMAIL#"
cc="#allinone.BC_EMAIL#"
from="#session.user.email#"
subject="Request for Additional Work Hours"
type="html">
<html><body>
<font size="+2">#EmpName# submitted a request for
#NoOfHours1# hours of #TypeHrs1# for
#startdate1#.
<br /><br />
Please click the link below to view pending requests.
<br /><br />
<a href=" Requests</a> </font></body></html>
</cfmail>
</cfif>
How do I start? The session user information is captured. The session user could be level 1; 2; 3; 4; 5; or, 6. If the session user is level 1 then for email
to = level 4
cc = level 5
For level 2
to = level 6
no cc
For level 3
to = level 6
no cc
For level 4
to = level 5
no cc
For level 5
to = level 6
no cc
Any suggestions? Thank you - JS
Here's query:
<cfquery datasource="#Application.DSN2#" name="allinone">
SELECT
USER_PIN,
USER_LNM + ', ' + USER_FNM AS FullName,
TEAM_ABBR,
BRNCH_ABBR,
POSN_ABBR,
USER_EMAIL,
(
SELECT E1.USER_EMAIL
FROM USER_VIEW_3 AS E1
WHERE E1.TEAM_ABBR = USER_VIEW_3.TEAM_ABBR
AND E1.POSN_ABBR = 'TL'
) AS TL_EMAIL,
(
SELECT E3.POSN_ABBR
FROM USER_VIEW_3 AS E3
WHERE E3.TEAM_ABBR = USER_VIEW_3.TEAM_ABBR
AND E3.POSN_ABBR = 'TL'
) AS TL_POSITION,
(
SELECT E2.USER_EMAIL
FROM USER_VIEW_3 AS E2
WHERE E2.BRNCH_ABBR = USER_VIEW_3.BRNCH_ABBR
AND E2.POSN_ABBR = 'BC'
) AS BC_EMAIL,
(
SELECT E4.POSN_ABBR
FROM USER_VIEW_3 AS E4
WHERE E4.BRNCH_ABBR = USER_VIEW_3.BRNCH_ABBR
AND E4.POSN_ABBR = 'BC'
) AS BC_POSITION,
(
SELECT E5.POSN_ABBR
FROM USER_VIEW_3 AS E5
WHERE E5.POSN_ABBR = 'DD'
) AS DD_POSITION,
(
SELECT E6.USER_EMAIL
FROM USER_VIEW_3 AS E6
WHERE E6.POSN_ABBR = 'DD'
) AS DD_EMAIL
FROM USER_VIEW_3
WHERE USER_PIN = '#Form.EMPPIN#'
</cfquery>
And here's <cfmail>
<cfif cgi.SERVER_NAME is not "localhost">
<cfmail to="#allinone.TL_EMAIL#"
cc="#allinone.BC_EMAIL#"
from="#session.user.email#"
subject="Request for Additional Work Hours"
type="html">
<html><body>
<font size="+2">#EmpName# submitted a request for
#NoOfHours1# hours of #TypeHrs1# for
#startdate1#.
<br /><br />
Please click the link below to view pending requests.
<br /><br />
<a href=" Requests</a> </font></body></html>
</cfmail>
</cfif>
How do I start? The session user information is captured. The session user could be level 1; 2; 3; 4; 5; or, 6. If the session user is level 1 then for email
to = level 4
cc = level 5
For level 2
to = level 6
no cc
For level 3
to = level 6
no cc
For level 4
to = level 5
no cc
For level 5
to = level 6
no cc
Any suggestions? Thank you - JS
