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(-)
(limited to 'demos/time-tracker')
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