From 63e66acd2ed45300d095d5428ebdfff7652070ea Mon Sep 17 00:00:00 2001
From: wei <>
Date: Tue, 18 Jul 2006 23:41:18 +0000
Subject: Add mappings for time-tracker

---
 .../protected/App_Data/mysql-maps/category.xml     |  93 +++++++++++++
 .../protected/App_Data/mysql-maps/projects.xml     | 154 +++++++++++++++++++++
 .../protected/App_Data/mysql-maps/reports.xml      |  45 ++++++
 .../protected/App_Data/mysql-maps/time-entry.xml   |  91 ++++++++++++
 .../protected/App_Data/mysql-maps/users.xml        | 137 ++++++++++++++++++
 5 files changed, 520 insertions(+)
 create mode 100644 demos/time-tracker/protected/App_Data/mysql-maps/category.xml
 create mode 100644 demos/time-tracker/protected/App_Data/mysql-maps/projects.xml
 create mode 100644 demos/time-tracker/protected/App_Data/mysql-maps/reports.xml
 create mode 100644 demos/time-tracker/protected/App_Data/mysql-maps/time-entry.xml
 create mode 100644 demos/time-tracker/protected/App_Data/mysql-maps/users.xml

(limited to 'demos/time-tracker/protected')

diff --git a/demos/time-tracker/protected/App_Data/mysql-maps/category.xml b/demos/time-tracker/protected/App_Data/mysql-maps/category.xml
new file mode 100644
index 00000000..f2b7b21c
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/mysql-maps/category.xml
@@ -0,0 +1,93 @@
+<?xml version="1.0" encoding="utf-8" ?>
+<sqlMap>
+
+<resultMap id="category-result" class="Category">
+	<result property="Name" column="Name" />
+	<result property="EstimateDuration" column="EstimateDuration" type="float" />
+	<result property="ProjectID" column="ProjectID" type="integer" />
+	<result property="Abbreviation" column="Abbreviation" />
+	<result property="ID" column="CategoryID" type="integer" />
+	<result property="ActualDuration" column="ActualDuration" type="float" />
+</resultMap>
+
+<insert ID="AddNewCategory" parameterClass="Category">
+	INSERT INTO categories 
+	(Name, ProjectID, Abbreviation, EstimateDuration)
+	VALUES(#Name#, #ProjectID#, #Abbreviation#, #EstimateDuration#)
+	<selectKey property="ID" type="post" resultClass="int">
+		select LAST_INSERT_ID() as value
+    </selectKey>
+</insert>
+
+<select ID="GetCategoryByID" parameterClass="integer" resultMap="category-result">
+	SELECT 
+		categories.*,
+		sum(time_entry.Duration) as ActualDuration
+	FROM
+		categories
+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID
+	WHERE
+		categories.CategoryID = #value#
+	GROUP BY
+		categories.CategoryID
+	ORDER BY
+		categories.Name
+</select>
+
+<select ID="GetAllCategories" resultMap="category-result">
+	SELECT 
+		categories.*,
+		sum(time_entry.Duration) as ActualDuration
+	FROM
+		categories
+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID
+	GROUP BY
+		categories.CategoryID
+	ORDER BY
+		categories.Name
+</select>
+
+<delete id="DeleteCategory" parameterClass="integer">
+	DELETE FROM categories WHERE CategoryID = #value#
+</delete>
+
+<select ID="GetCategoriesByProjectID" resultMap="category-result">
+	SELECT 
+		categories.*,
+		sum(time_entry.Duration) as ActualDuration
+	FROM
+		categories
+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID
+	WHERE
+		categories.ProjectID = #value#
+	GROUP BY
+		categories.CategoryID
+	ORDER BY
+		categories.Name
+</select>
+
+<select ID="GetCategoryByNameInProject" parameterClass="array" resultMap="category-result">
+	SELECT 
+		categories.*,
+		sum(time_entry.Duration) as ActualDuration
+	FROM
+		categories
+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID
+	WHERE
+			categories.ProjectID = #project#
+		AND categories.Name = #category#
+	GROUP BY
+		categories.CategoryID
+</select>
+
+<update ID="UpdateCategory" parameterClass="Category">
+	UPDATE categories SET
+		Abbreviation = #Abbreviation#,
+		EstimateDuration = #EstimateDuration#,
+		Name = #Name#,
+		ProjectId = #ProjectID#
+	WHERE 
+		CategoryID = #ID#
+</update>
+
+</sqlMap>
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/mysql-maps/projects.xml b/demos/time-tracker/protected/App_Data/mysql-maps/projects.xml
new file mode 100644
index 00000000..8fa28b64
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/mysql-maps/projects.xml
@@ -0,0 +1,154 @@
+<?xml version="1.0" encoding="utf-8" ?>
+<sqlMap>
+
+<resultMap ID="project-result" class="Project">
+	<result property="ID" column="ProjectID" type="integer"/>
+	<result property="Name" column="Name" />
+	<result property="Description" column="Description" />
+	<result property="DateCreated" column="CreationDate" typeHandler="DateTime" />
+	<result property="EstimateDuration" column="EstimateDuration" type="float" />
+	<result property="CompletionDate" column="CompletionDate" typeHandler="DateTime" />
+	<result property="CreatorUserName" column="CreatorID" />
+	<result property="ManagerUserName" column="ManagerID" />
+	<result property="ActualDuration" column="ActualDuration" type="float" />
+</resultMap>
+
+<select ID="ProjectNameExists" resultClass="boolean">
+	SELECT COUNT(Name) FROM project WHERE Name = #value#
+</select>
+
+<insert ID="CreateNewProject" parameterClass="Project">
+	INSERT INTO project
+	(Name, Description, CreationDate, EstimateDuration, CompletionDate, CreatorID, ManagerID)
+	VALUES
+	(#Name#, #Description#, NOW(), #EstimateDuration#,
+		#CompletionDate, typeHandler=DateTime#, 
+		#CreatorUserName#, #ManagerUserName#)
+	<selectKey property="ID" type="post" resultClass="int">
+		select LAST_INSERT_ID() as value
+    </selectKey>
+</insert>
+
+<select ID="GetProjectByID" parameterClass="integer" resultMap="project-result">
+	SELECT 
+		project.ProjectID as ProjectID,
+		project.Name as Name,
+		project.Description as Description,
+		project.CreationDate as CreationDate,
+		project.EstimateDuration as EstimateDuration,
+		project.CompletionDate as CompletionDate,
+		project.CreatorID as CreatorID,
+		project.ManagerID as ManagerID,
+		SUM(time_entry.Duration) as ActualDuration
+	FROM project
+		LEFT JOIN categories ON project.ProjectID = categories.ProjectID
+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID
+	WHERE 
+			project.ProjectID = #value# 
+		AND Disabled = 0
+	GROUP BY
+		project.ProjectID
+	ORDER BY
+		project.Name
+</select>
+
+<select ID="GetAllProjects" resultMap="project-result">
+	SELECT 
+		project.ProjectID as ProjectID,
+		project.Name as Name,
+		project.Description as Description,
+		project.CreationDate as CreationDate,
+		project.EstimateDuration as EstimateDuration,
+		project.CompletionDate as CompletionDate,
+		project.CreatorID as CreatorID,
+		project.ManagerID as ManagerID,
+		SUM(time_entry.Duration) as ActualDuration
+	FROM project
+		LEFT JOIN categories ON project.ProjectID = categories.ProjectID
+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID
+	WHERE 
+		Disabled = 0
+	GROUP BY
+		project.ProjectID
+	ORDER BY
+		project.Name
+</select>
+
+<select ID="GetProjectsByManagerName" resultMap="project-result">
+	SELECT 
+		project.ProjectID as ProjectID,
+		project.Name as Name,
+		project.Description as Description,
+		project.CreationDate as CreationDate,
+		project.EstimateDuration as EstimateDuration,
+		project.CompletionDate as CompletionDate,
+		project.CreatorID as CreatorID,
+		project.ManagerID as ManagerID,
+		SUM(time_entry.Duration) as ActualDuration
+	FROM project
+		LEFT JOIN categories ON project.ProjectID = categories.ProjectID
+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID
+	WHERE 
+			Disabled = 0 
+		AND project.ManagerID = #value#
+	GROUP BY
+		project.ProjectID
+	ORDER BY
+		project.Name
+</select>
+
+<select ID="GetProjectsByUserName" resultMap="project-result">
+	SELECT 
+		project.ProjectID as ProjectID,
+		project.Name as Name,
+		project.Description as Description,
+		project.CreationDate as CreationDate,
+		project.EstimateDuration as EstimateDuration,
+		project.CompletionDate as CompletionDate,
+		project.CreatorID as CreatorID,
+		project.ManagerID as ManagerID,
+		SUM(time_entry.Duration) as ActualDuration
+	FROM project
+		LEFT JOIN categories ON project.ProjectID = categories.ProjectID
+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID,
+		project_members
+	WHERE 
+			project_members.ProjectID = project.ProjectID
+		AND project_members.UserID = #value#
+		AND project.Disabled = 0
+	GROUP BY
+		project.ProjectID
+	ORDER BY
+		project.Name
+</select>
+
+<update ID="DeleteProject" parameterClass="integer">
+	UPDATE project SET Disabled = 1 WHERE ProjectID = #value#
+</update>
+
+<select ID="GetProjectMembers" parameterClass="integer">
+	SELECT UserID FROM project_members WHERE ProjectID = #value#
+</select>
+
+<insert ID="AddUserToProject" parameterClass="array">
+	INSERT INTO project_members (UserID, ProjectID)
+	VALUES(#username#, #project#)
+</insert>
+
+<delete ID="RemoveUserFromProject" parameterClass="array">
+	DELETE FROM project_members WHERE ProjectID = #project# AND UserID = #username#
+</delete>
+
+<update ID="UpdateProject" parameterClass="Project">
+	UPDATE project
+	SET 
+		CompletionDate = #CompletionDate, typeHandler=DateTime#,
+		Description = #Description#,
+		EstimateDuration = #EstimateDuration#,
+		ManagerId =#ManagerUserName#,
+		Name = #Name#
+	WHERE 
+		ProjectID = #ID#
+</update>
+
+</sqlMap>
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/mysql-maps/reports.xml b/demos/time-tracker/protected/App_Data/mysql-maps/reports.xml
new file mode 100644
index 00000000..83f922d0
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/mysql-maps/reports.xml
@@ -0,0 +1,45 @@
+<?xml version="1.0" encoding="utf-8" ?>
+<sqlMap>
+
+<select id="GetTimeReportByProjectID" resultClass="UserReport">
+	SELECT
+		categories.CategoryID as CategoryID,
+		time_entry.UserID as Username,
+		SUM(time_entry.Duration) as ActualDuration
+	FROM
+		time_entry, categories
+	WHERE
+		categories.CategoryID = time_entry.CategoryID
+	AND categories.ProjectID = #value#
+	GROUP BY
+		categories.ProjectID
+	ORDER BY
+		categories.ProjectID
+</select>
+
+<select id="GetTimeReportByCategoryID" resultClass="UserReport">
+	SELECT
+		categories.CategoryID as CategoryID,
+		time_entry.UserID as Username,
+		SUM(time_entry.Duration) as ActualDuration
+	FROM
+		time_entry, categories
+	WHERE
+		categories.CategoryID = time_entry.CategoryID
+	AND categories.CategoryID = #value#
+	GROUP BY
+		time_entry.UserID
+	ORDER BY
+		categories.CategoryID
+</select>	
+
+<select id="GetTimeReportByUsername" resultClass="float">
+	SELECT
+		SUM(Duration) as TotalDuration
+	FROM
+		time_entry
+	WHERE
+		UserID = #value#
+</select>
+
+</sqlMap>
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/mysql-maps/time-entry.xml b/demos/time-tracker/protected/App_Data/mysql-maps/time-entry.xml
new file mode 100644
index 00000000..2708a6c5
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/mysql-maps/time-entry.xml
@@ -0,0 +1,91 @@
+<?xml version="1.0" encoding="utf-8" ?>
+<sqlMap>
+
+<insert ID="AddNewTimeEntry" parameterClass="TimeEntry">
+	INSERT INTO time_entry(
+		EntryCreated, 
+		Duration, 
+		Description, 
+		CategoryID,
+		EntryDate, 
+		CreatorID, 
+		UserID
+	)
+	VALUES(
+		NOW(), 
+		#Duration#, 
+		#Description#, 
+		#Category.ID#,
+		#ReportDate, typeHandler=DateTime#, 
+		#CreatorUserName#, 
+		#Username#
+	)
+  <selectKey property="ID" type="post" resultClass="int">
+		select LAST_INSERT_ID() as value
+  </selectKey>
+</insert>
+
+<resultMap id="time-entry-result" class="TimeEntry">
+	<result property="ID" column="EntryID" type="integer" />
+	<result property="DateCreated" column="EntryCreated" typeHandler="DateTime" />
+	<result property="Duration" column="Duration" type="float" />
+	<result property="Description" column="Description" />
+	<result property="Category" column="CategoryID" />
+	<result property="ReportDate" column="EntryDate" typeHandler="DateTime" />
+	<result property="CreatorUserName" column="CreatorID" />
+	<result property="Username" column="UserID" />
+</resultMap>
+
+<select ID="GetTimeEntryByID" resultMap="time-entry-result">
+	SELECT 
+		*
+	FROM 
+		time_entry
+	WHERE
+		EntryID = #value#
+</select>
+
+<delete id="DeleteTimeEntry" parameterClass="integer">
+	DELETE FROM time_entry WHERE EntryID = #value#
+</delete>
+
+<select ID="GetAllTimeEntriesByProjectIdAndUser" resultMap="time-entry-result">
+	SELECT 
+		time_entry.*
+	FROM
+		time_entry, categories
+	WHERE
+			time_entry.UserID = #username#
+		AND time_entry.CategoryID = categories.CategoryID
+		AND categories.ProjectID = #project#
+	ORDER BY 
+		EntryID ASC
+</select>
+
+<update ID="UpdateTimeEntry" parameterClass="TimeEntry">
+	UPDATE time_entry SET
+		Duration = #Duration#,
+		Description = #Description#,
+		CategoryID = #Category.ID#,
+		EntryDate = #ReportDate, typeHandler=DateTime#,
+		UserID = #Username#
+	WHERE
+		EntryID = #ID#
+</update>
+
+<select id="GetTimeEntriesByDate" parameterClass="array" resultMap="time-entry-result">
+	SELECT 
+		time_entry.*
+	FROM
+		time_entry
+	WHERE
+			time_entry.UserID = #username#
+	AND
+		time_entry.EntryDate BETWEEN 
+			#startDate, typeHandler=DateTime# and 
+			#endDate, typeHandler=DateTime#
+	ORDER BY 
+		EntryID ASC
+</select>
+
+</sqlMap>
\ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/mysql-maps/users.xml b/demos/time-tracker/protected/App_Data/mysql-maps/users.xml
new file mode 100644
index 00000000..6053c7ea
--- /dev/null
+++ b/demos/time-tracker/protected/App_Data/mysql-maps/users.xml
@@ -0,0 +1,137 @@
+<?xml version="1.0" encoding="utf-8" ?>
+<sqlMap>
+
+<resultMap id="time-tracker-user" class="TimeTrackerUser" GroupBy="Name">
+	<result property="Name" column="Name" />
+	<result property="EmailAddress" column="EmailAddress" />
+	<result property="Roles" Type="array" resultMapping="user-roles-result" />
+</resultMap>
+
+<resultMap id="user-roles-result">
+	<result column="role" />
+</resultMap>
+
+<!--
+<select id="GetUserByName" parameterClass="string" resultMap="TimeTrackerUser">
+	SELECT 
+		users.Username as Name,
+		users.EmailAddress as EmailAddress,
+		user_roles.RoleType as role
+	FROM
+		users
+		LEFT JOIN user_roles ON users.Username = user_roles.UserID
+	WHERE
+		Username = #value#
+	AND 
+		Disabled = 0
+</select>
+-->
+
+<select id="UsernameExists" parameterClass="string" resultClass="boolean">
+	SELECT COUNT(Username) FROM users WHERE Username = #value#
+</select>
+
+<select id="GetUserByName" parameterClass="string" resultMap="time-tracker-user">
+	SELECT 
+		users.Username as Name,
+		users.EmailAddress as EmailAddress,
+		user_roles.RoleType as role
+	FROM
+		users
+		LEFT JOIN user_roles ON users.Username = user_roles.UserID
+	WHERE
+		Username = #value#
+	AND 
+		Disabled = 0
+</select>
+    
+<select id="GetAllUsers" resultMap="time-tracker-user">
+	SELECT
+		users.Username as Name,
+		users.EmailAddress as EmailAddress,
+		user_roles.RoleType as role
+	FROM
+		users
+		LEFT JOIN user_roles ON users.Username = user_roles.UserID
+	WHERE
+		Disabled = 0
+</select>
+
+<select id="ValidateUser" resultClass="boolean">
+	SELECT 
+		count(Username)
+	FROM
+		users
+	WHERE
+		username = #username# AND password = #password#
+	AND
+		Disabled = 0
+</select>
+
+<insert id="AddNewUser" parameterClass="array">
+	INSERT INTO 
+		users (Username, Password, EmailAddress)
+	VALUES
+		(#user.Name#, #password#, #user.EmailAddress#)
+</insert>
+
+<update id="DeleteUserByName">
+	UPDATE users SET Disabled = 1  WHERE username = #value#
+</update>
+
+<insert id="RegisterAutoSignon" parameterClass="array">
+	INSERT INTO 
+		signon (SessionToken, Username, LastSignOnDate)
+	VALUES
+		(#token#, #username#, NOW())
+</insert>
+
+<select id="ValidateAutoSignon" resultMap="time-tracker-user">
+	SELECT
+		users.Username as Name,
+		users.EmailAddress as EmailAddress,
+		user_roles.RoleType as role
+	FROM
+		users LEFT JOIN user_roles ON users.Username = user_roles.UserID,
+		signon
+	WHERE
+		users.Username = signon.Username
+		AND signon.SessionToken = #value#
+		AND users.Disabled = 0
+</select>
+
+<update id="UpdateSignon">
+	UPDATE signon SET LastSignOnDate = NOW() 
+	WHERE SessionToken = #value#
+</update>
+
+<delete id="DeleteUserRoles" parameterClass="TimeTrackerUser">
+	DELETE FROM user_roles WHERE UserID = #Name#
+</delete>
+
+<update id="AddUserRole" parameterClass="array">
+	INSERT INTO user_roles (UserID, RoleType)
+	VALUES(#username#, #role#)
+</update>
+
+<update id="UpdateUserDetails" parameterClass="TimeTrackerUser">
+	UPDATE users 
+	SET EmailAddress = #EmailAddress#
+	WHERE Username = #Name#
+</update>
+
+<update id="UpdateUserDetailsAndPassword" parameterClass="array">
+	UPDATE users 
+	SET EmailAddress = #user.EmailAddress#, Password=#password#
+	WHERE Username = #user.Name#
+</update>
+
+<delete id="DeleteAutoSignon">
+	DELETE FROM signon WHERE Username = #value#
+</delete>
+
+<delete id="DeleteAllSignon">
+	DELETE FROM signon
+</delete>
+
+</sqlMap>
\ No newline at end of file
-- 
cgit v1.2.3