| 1 |
lars |
1 |
<?xml version="1.0" encoding="utf-8" ?>
|
|
|
2 |
<sqlMap>
|
|
|
3 |
|
|
|
4 |
<resultMap id="time-tracker-user" class="TimeTrackerUser" GroupBy="Name">
|
|
|
5 |
<result property="Name" column="Name" />
|
|
|
6 |
<result property="EmailAddress" column="EmailAddress" />
|
|
|
7 |
<result property="Roles" Type="array" resultMapping="user-roles-result" />
|
|
|
8 |
</resultMap>
|
|
|
9 |
|
|
|
10 |
<resultMap id="user-roles-result">
|
|
|
11 |
<result column="role" />
|
|
|
12 |
</resultMap>
|
|
|
13 |
|
|
|
14 |
<!--
|
|
|
15 |
<select id="GetUserByName" parameterClass="string" resultMap="TimeTrackerUser">
|
|
|
16 |
SELECT
|
|
|
17 |
users.Username as Name,
|
|
|
18 |
users.EmailAddress as EmailAddress,
|
|
|
19 |
user_roles.RoleType as role
|
|
|
20 |
FROM
|
|
|
21 |
users
|
|
|
22 |
LEFT JOIN user_roles ON users.Username = user_roles.UserID
|
|
|
23 |
WHERE
|
|
|
24 |
Username = #value#
|
|
|
25 |
AND
|
|
|
26 |
Disabled = 0
|
|
|
27 |
</select>
|
|
|
28 |
-->
|
|
|
29 |
|
|
|
30 |
<select id="UsernameExists" parameterClass="string" resultClass="boolean">
|
|
|
31 |
SELECT COUNT(Username) FROM users WHERE Username = #value#
|
|
|
32 |
</select>
|
|
|
33 |
|
|
|
34 |
<select id="GetUserByName" parameterClass="string" resultMap="time-tracker-user">
|
|
|
35 |
SELECT
|
|
|
36 |
users.Username as Name,
|
|
|
37 |
users.EmailAddress as EmailAddress,
|
|
|
38 |
user_roles.RoleType as role
|
|
|
39 |
FROM
|
|
|
40 |
users
|
|
|
41 |
LEFT JOIN user_roles ON users.Username = user_roles.UserID
|
|
|
42 |
WHERE
|
|
|
43 |
Username = #value#
|
|
|
44 |
AND
|
|
|
45 |
Disabled = 0
|
|
|
46 |
</select>
|
|
|
47 |
|
|
|
48 |
<select id="GetAllUsers" resultMap="time-tracker-user">
|
|
|
49 |
SELECT
|
|
|
50 |
users.Username as Name,
|
|
|
51 |
users.EmailAddress as EmailAddress,
|
|
|
52 |
user_roles.RoleType as role
|
|
|
53 |
FROM
|
|
|
54 |
users
|
|
|
55 |
LEFT JOIN user_roles ON users.Username = user_roles.UserID
|
|
|
56 |
WHERE
|
|
|
57 |
Disabled = 0
|
|
|
58 |
</select>
|
|
|
59 |
|
|
|
60 |
<select id="ValidateUser" resultClass="boolean">
|
|
|
61 |
SELECT
|
|
|
62 |
count(Username)
|
|
|
63 |
FROM
|
|
|
64 |
users
|
|
|
65 |
WHERE
|
|
|
66 |
username = #username# AND password = #password#
|
|
|
67 |
AND
|
|
|
68 |
Disabled = 0
|
|
|
69 |
</select>
|
|
|
70 |
|
|
|
71 |
<insert id="AddNewUser" parameterClass="array">
|
|
|
72 |
INSERT INTO
|
|
|
73 |
users (Username, Password, EmailAddress)
|
|
|
74 |
VALUES
|
|
|
75 |
(#user.Name#, #password#, #user.EmailAddress#)
|
|
|
76 |
</insert>
|
|
|
77 |
|
|
|
78 |
<update id="DeleteUserByName">
|
|
|
79 |
UPDATE users SET Disabled = 1 WHERE username = #value#
|
|
|
80 |
</update>
|
|
|
81 |
|
|
|
82 |
<insert id="RegisterAutoSignon" parameterClass="array">
|
|
|
83 |
INSERT INTO
|
|
|
84 |
signon (SessionToken, Username, LastSignOnDate)
|
|
|
85 |
VALUES
|
|
|
86 |
(#token#, #username#, NOW())
|
|
|
87 |
</insert>
|
|
|
88 |
|
|
|
89 |
<select id="ValidateAutoSignon" resultMap="time-tracker-user">
|
|
|
90 |
SELECT
|
|
|
91 |
users.Username as Name,
|
|
|
92 |
users.EmailAddress as EmailAddress,
|
|
|
93 |
user_roles.RoleType as role
|
|
|
94 |
FROM
|
|
|
95 |
users LEFT JOIN user_roles ON users.Username = user_roles.UserID,
|
|
|
96 |
signon
|
|
|
97 |
WHERE
|
|
|
98 |
users.Username = signon.Username
|
|
|
99 |
AND signon.SessionToken = #value#
|
|
|
100 |
AND users.Disabled = 0
|
|
|
101 |
</select>
|
|
|
102 |
|
|
|
103 |
<update id="UpdateSignon">
|
|
|
104 |
UPDATE signon SET LastSignOnDate = NOW()
|
|
|
105 |
WHERE SessionToken = #value#
|
|
|
106 |
</update>
|
|
|
107 |
|
|
|
108 |
<delete id="DeleteUserRoles" parameterClass="TimeTrackerUser">
|
|
|
109 |
DELETE FROM user_roles WHERE UserID = #Name#
|
|
|
110 |
</delete>
|
|
|
111 |
|
|
|
112 |
<update id="AddUserRole" parameterClass="array">
|
|
|
113 |
INSERT INTO user_roles (UserID, RoleType)
|
|
|
114 |
VALUES(#username#, #role#)
|
|
|
115 |
</update>
|
|
|
116 |
|
|
|
117 |
<update id="UpdateUserDetails" parameterClass="TimeTrackerUser">
|
|
|
118 |
UPDATE users
|
|
|
119 |
SET EmailAddress = #EmailAddress#
|
|
|
120 |
WHERE Username = #Name#
|
|
|
121 |
</update>
|
|
|
122 |
|
|
|
123 |
<update id="UpdateUserDetailsAndPassword" parameterClass="array">
|
|
|
124 |
UPDATE users
|
|
|
125 |
SET EmailAddress = #user.EmailAddress#, Password=#password#
|
|
|
126 |
WHERE Username = #user.Name#
|
|
|
127 |
</update>
|
|
|
128 |
|
|
|
129 |
<delete id="DeleteAutoSignon">
|
|
|
130 |
DELETE FROM signon WHERE Username = #value#
|
|
|
131 |
</delete>
|
|
|
132 |
|
|
|
133 |
<delete id="DeleteAllSignon">
|
|
|
134 |
DELETE FROM signon
|
|
|
135 |
</delete>
|
|
|
136 |
|
|
|
137 |
</sqlMap>
|