From ff6db29dff9b4778b97d2234d08cdcc10770562a Mon Sep 17 00:00:00 2001
From: wei <>
Date: Fri, 28 Jul 2006 06:26:05 +0000
Subject: Update time-tracker

---
 .../protected/App_Data/mysql-maps/category.xml     | 12 +--
 .../protected/App_Data/mysql-maps/projects.xml     | 30 +++----
 .../protected/App_Data/mysql-maps/reports.xml      | 94 ++++++++++++++++------
 .../protected/App_Data/mysql-maps/time-entry.xml   | 42 +++++-----
 4 files changed, 112 insertions(+), 66 deletions(-)

diff --git a/demos/time-tracker/protected/App_Data/mysql-maps/category.xml b/demos/time-tracker/protected/App_Data/mysql-maps/category.xml
index f2b7b21c..66b0ae5f 100644
--- a/demos/time-tracker/protected/App_Data/mysql-maps/category.xml
+++ b/demos/time-tracker/protected/App_Data/mysql-maps/category.xml
@@ -10,7 +10,7 @@
 	<result property="ActualDuration" column="ActualDuration" type="float" />
 </resultMap>
 
-<insert ID="AddNewCategory" parameterClass="Category">
+<insert id="AddNewCategory" parameterClass="Category">
 	INSERT INTO categories 
 	(Name, ProjectID, Abbreviation, EstimateDuration)
 	VALUES(#Name#, #ProjectID#, #Abbreviation#, #EstimateDuration#)
@@ -19,7 +19,7 @@
     </selectKey>
 </insert>
 
-<select ID="GetCategoryByID" parameterClass="integer" resultMap="category-result">
+<select id="GetCategoryByID" parameterClass="integer" resultMap="category-result">
 	SELECT 
 		categories.*,
 		sum(time_entry.Duration) as ActualDuration
@@ -34,7 +34,7 @@
 		categories.Name
 </select>
 
-<select ID="GetAllCategories" resultMap="category-result">
+<select id="GetAllCategories" resultMap="category-result">
 	SELECT 
 		categories.*,
 		sum(time_entry.Duration) as ActualDuration
@@ -51,7 +51,7 @@
 	DELETE FROM categories WHERE CategoryID = #value#
 </delete>
 
-<select ID="GetCategoriesByProjectID" resultMap="category-result">
+<select id="GetCategoriesByProjectID" resultMap="category-result">
 	SELECT 
 		categories.*,
 		sum(time_entry.Duration) as ActualDuration
@@ -66,7 +66,7 @@
 		categories.Name
 </select>
 
-<select ID="GetCategoryByNameInProject" parameterClass="array" resultMap="category-result">
+<select id="GetCategoryByNameInProject" parameterClass="array" resultMap="category-result">
 	SELECT 
 		categories.*,
 		sum(time_entry.Duration) as ActualDuration
@@ -80,7 +80,7 @@
 		categories.CategoryID
 </select>
 
-<update ID="UpdateCategory" parameterClass="Category">
+<update id="UpdateCategory" parameterClass="Category">
 	UPDATE categories SET
 		Abbreviation = #Abbreviation#,
 		EstimateDuration = #EstimateDuration#,
diff --git a/demos/time-tracker/protected/App_Data/mysql-maps/projects.xml b/demos/time-tracker/protected/App_Data/mysql-maps/projects.xml
index 8fa28b64..f087b864 100644
--- a/demos/time-tracker/protected/App_Data/mysql-maps/projects.xml
+++ b/demos/time-tracker/protected/App_Data/mysql-maps/projects.xml
@@ -1,7 +1,7 @@
 <?xml version="1.0" encoding="utf-8" ?>
 <sqlMap>
 
-<resultMap ID="project-result" class="Project">
+<resultMap id="project-result" class="Project">
 	<result property="ID" column="ProjectID" type="integer"/>
 	<result property="Name" column="Name" />
 	<result property="Description" column="Description" />
@@ -13,11 +13,11 @@
 	<result property="ActualDuration" column="ActualDuration" type="float" />
 </resultMap>
 
-<select ID="ProjectNameExists" resultClass="boolean">
+<select id="ProjectNameExists" resultClass="boolean">
 	SELECT COUNT(Name) FROM project WHERE Name = #value#
 </select>
 
-<insert ID="CreateNewProject" parameterClass="Project">
+<insert id="CreateNewProject" parameterClass="Project">
 	INSERT INTO project
 	(Name, Description, CreationDate, EstimateDuration, CompletionDate, CreatorID, ManagerID)
 	VALUES
@@ -29,7 +29,7 @@
     </selectKey>
 </insert>
 
-<select ID="GetProjectByID" parameterClass="integer" resultMap="project-result">
+<select id="GetProjectByID" parameterClass="integer" resultMap="project-result">
 	SELECT 
 		project.ProjectID as ProjectID,
 		project.Name as Name,
@@ -52,7 +52,7 @@
 		project.Name
 </select>
 
-<select ID="GetAllProjects" resultMap="project-result">
+<select id="GetAllProjects" resultMap="project-result">
 	SELECT 
 		project.ProjectID as ProjectID,
 		project.Name as Name,
@@ -70,11 +70,13 @@
 		Disabled = 0
 	GROUP BY
 		project.ProjectID
-	ORDER BY
-		project.Name
 </select>
 
-<select ID="GetProjectsByManagerName" resultMap="project-result">
+<select id="GetAllProjectsOrdered" resultMap="project-result" extends="GetAllProjects">
+	ORDER BY $sort$ $order$
+</select>
+
+<select id="GetProjectsByManagerName" resultMap="project-result">
 	SELECT 
 		project.ProjectID as ProjectID,
 		project.Name as Name,
@@ -97,7 +99,7 @@
 		project.Name
 </select>
 
-<select ID="GetProjectsByUserName" resultMap="project-result">
+<select id="GetProjectsByUserName" resultMap="project-result">
 	SELECT 
 		project.ProjectID as ProjectID,
 		project.Name as Name,
@@ -122,24 +124,24 @@
 		project.Name
 </select>
 
-<update ID="DeleteProject" parameterClass="integer">
+<update id="DeleteProject" parameterClass="integer">
 	UPDATE project SET Disabled = 1 WHERE ProjectID = #value#
 </update>
 
-<select ID="GetProjectMembers" parameterClass="integer">
+<select id="GetProjectMembers" parameterClass="integer">
 	SELECT UserID FROM project_members WHERE ProjectID = #value#
 </select>
 
-<insert ID="AddUserToProject" parameterClass="array">
+<insert id="AddUserToProject" parameterClass="array">
 	INSERT INTO project_members (UserID, ProjectID)
 	VALUES(#username#, #project#)
 </insert>
 
-<delete ID="RemoveUserFromProject" parameterClass="array">
+<delete id="RemoveUserFromProject" parameterClass="array">
 	DELETE FROM project_members WHERE ProjectID = #project# AND UserID = #username#
 </delete>
 
-<update ID="UpdateProject" parameterClass="Project">
+<update id="UpdateProject" parameterClass="Project">
 	UPDATE project
 	SET 
 		CompletionDate = #CompletionDate, typeHandler=DateTime#,
diff --git a/demos/time-tracker/protected/App_Data/mysql-maps/reports.xml b/demos/time-tracker/protected/App_Data/mysql-maps/reports.xml
index 83f922d0..4b537708 100644
--- a/demos/time-tracker/protected/App_Data/mysql-maps/reports.xml
+++ b/demos/time-tracker/protected/App_Data/mysql-maps/reports.xml
@@ -1,45 +1,87 @@
 <?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>
+<resultMap id="project-category-user" class="ProjectReport" GroupBy="ProjectID">
+	<result property="ProjectName" column="ProjectName" />
+	<result property="EstimateHours" column="ProjectEstimate" type="float"/>
+	<result property="EstimateCompletion" column="ProjectCompletion" type="DateTime" />
+	<result property="Categories" type="TList" resultMapping="category-user-report" />
+</resultMap>
 
-<select id="GetTimeReportByCategoryID" resultClass="UserReport">
+<resultMap id="category-user-report" class="CategoryReport" GroupBy="CategoryID" >
+	<result property="CategoryName" column="CategoryName" />
+	<result property="EstimateHours" column="CategoryEstimate" type="float" />
+	<result property="members" type="array" resultMapping="member-report" />
+</resultMap>
+
+<resultMap id="member-report" class="array">
+	<result property="username" column="Username" />
+	<result property="hours" column="ActualDuration" type="float" />
+</resultMap>
+
+<select id="GetTimeReportByProjectIDs" resultMap="project-category-user">
 	SELECT
+		categories.Name as CategoryName,
 		categories.CategoryID as CategoryID,
+		project.ProjectID as ProjectID,
+		categories.EstimateDuration as CategoryEstimate,
+		project.Name as ProjectName,
+		project.EstimateDuration as ProjectEstimate,
+		project.CompletionDate as ProjectCompletion,
 		time_entry.UserID as Username,
 		SUM(time_entry.Duration) as ActualDuration
 	FROM
-		time_entry, categories
+		project
+		LEFT JOIN categories ON categories.ProjectID = project.ProjectID
+		LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID
 	WHERE
-		categories.CategoryID = time_entry.CategoryID
-	AND categories.CategoryID = #value#
+		project.ProjectID IN ( $value$ )
 	GROUP BY
+		categories.ProjectID,
+		categories.CategoryID,
 		time_entry.UserID
 	ORDER BY
-		categories.CategoryID
-</select>	
+		project.ProjectID
+</select>
+
 
-<select id="GetTimeReportByUsername" resultClass="float">
+<resultMap id="time-entry-user-report" class="UserReport" GroupBy="Username">
+	<result property="Username" column="Username" />
+	<result property="Projects" resultMapping="project-user-report" />
+</resultMap>
+
+<resultMap id="project-user-report" class="UserProjectReport">
+	<result property="ProjectName" column="ProjectName" />
+	<result property="CategoryName" column="CategoryName" />
+	<result property="Duration" column="Duration" type="float" />
+	<result property="Description" column="Description" />
+	<result property="ReportDate" column="EntryDate" type="DateTime" />
+</resultMap>
+
+<select id="GetTimeReportByUsername" resultMap="time-entry-user-report">
 	SELECT
-		SUM(Duration) as TotalDuration
-	FROM
-		time_entry
+		users.Username,
+		project.Name as ProjectName,
+		categories.Name as CategoryName,
+		time_entry.Duration,
+		time_entry.Description,
+		time_entry.EntryDate
+	FROM 
+   		users 
+   		LEFT JOIN time_entry ON time_entry.UserID = users.Username
+			AND time_entry.EntryDate BETWEEN 
+				#startDate, typeHandler=DateTime# AND 
+				#endDate, typeHandler=DateTime#
+   		LEFT JOIN categories ON time_entry.CategoryID = categories.CategoryID
+   		LEFT JOIN project ON categories.ProjectID = project.ProjectID 
+			AND project.ProjectID in ($projects$) 
 	WHERE
-		UserID = #value#
+   		users.Username in ($members$)
+	ORDER BY
+  		users.Username ASC,
+  		time_entry.EntryDate ASC,
+  		project.Name ASC,
+  		categories.Name ASC
 </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
index 2708a6c5..841bd03e 100644
--- a/demos/time-tracker/protected/App_Data/mysql-maps/time-entry.xml
+++ b/demos/time-tracker/protected/App_Data/mysql-maps/time-entry.xml
@@ -1,7 +1,7 @@
 <?xml version="1.0" encoding="utf-8" ?>
 <sqlMap>
 
-<insert ID="AddNewTimeEntry" parameterClass="TimeEntry">
+<insert id="AddNewTimeEntry" parameterClass="TimeEntry">
 	INSERT INTO time_entry(
 		EntryCreated, 
 		Duration, 
@@ -36,7 +36,23 @@
 	<result property="Username" column="UserID" />
 </resultMap>
 
-<select ID="GetTimeEntryByID" resultMap="time-entry-result">
+<resultMap id="time-entry-category-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" resultMapping="entry-category" />
+	<result property="ReportDate" column="EntryDate" typeHandler="DateTime" />
+	<result property="CreatorUserName" column="CreatorID" />
+	<result property="Username" column="UserID" />
+</resultMap>
+
+<resultMap id="entry-category" class="Category">
+	<result property="ID" column="CategoryID" />
+	<result property="Name" column="CategoryName" />
+</resultMap>
+
+<select id="GetTimeEntryByID" resultMap="time-entry-result">
 	SELECT 
 		*
 	FROM 
@@ -49,9 +65,10 @@
 	DELETE FROM time_entry WHERE EntryID = #value#
 </delete>
 
-<select ID="GetAllTimeEntriesByProjectIdAndUser" resultMap="time-entry-result">
+<select id="GetAllTimeEntriesByProjectIdAndUser" resultMap="time-entry-category-result">
 	SELECT 
-		time_entry.*
+		time_entry.*,
+		categories.Name as CategoryName
 	FROM
 		time_entry, categories
 	WHERE
@@ -62,7 +79,7 @@
 		EntryID ASC
 </select>
 
-<update ID="UpdateTimeEntry" parameterClass="TimeEntry">
+<update id="UpdateTimeEntry" parameterClass="TimeEntry">
 	UPDATE time_entry SET
 		Duration = #Duration#,
 		Description = #Description#,
@@ -73,19 +90,4 @@
 		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
-- 
cgit v1.2.3