Update time-tracker

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