diff options
| author | wei <> | 2006-07-28 12:32:01 +0000 |
|---|---|---|
| committer | wei <> | 2006-07-28 12:32:01 +0000 |
| commit | 623447ffea7a49359c773a0bc3a851397885f319 (patch) | |
| tree | 93676acdeea5697dd00fb10d0eb70948901b549e /demos/time-tracker | |
| parent | fbf05a159bc1a688940c16dc304eaaf140188b01 (diff) | |
Add sqlite support for time-tracker.
Diffstat (limited to 'demos/time-tracker')
35 files changed, 684 insertions, 71 deletions
diff --git a/demos/time-tracker/index.php b/demos/time-tracker/index.php index 69e6b5c5..ec14639e 100644 --- a/demos/time-tracker/index.php +++ b/demos/time-tracker/index.php @@ -1,16 +1,21 @@ <?php
$basePath=dirname(__FILE__);
-$frameworkPath='../../framework/pradolite.php';
+//$frameworkPath='../../framework/pradolite.php';
+$frameworkPath='../../framework/prado.php';
$assetsPath=$basePath."/assets";
$runtimePath=$basePath."/protected/runtime";
+$sqliteDbDir = $basePath."/protected/App_Data/Sqlite";
+
if(!is_file($frameworkPath))
die("Unable to find prado framework path $frameworkPath.");
if(!is_writable($assetsPath))
die("Please make sure that the directory $assetsPath is writable by Web server process.");
if(!is_writable($runtimePath))
die("Please make sure that the directory $runtimePath is writable by Web server process.");
+if(!is_writable($sqliteDbDir))
+ die("Please make sure that the directory $sqliteDbDir is writable by Web server process.");
require_once($frameworkPath);
diff --git a/demos/time-tracker/protected/App_Code/BaseDao.php b/demos/time-tracker/protected/App_Code/Dao/BaseDao.php index 63b91def..63b91def 100644 --- a/demos/time-tracker/protected/App_Code/BaseDao.php +++ b/demos/time-tracker/protected/App_Code/Dao/BaseDao.php diff --git a/demos/time-tracker/protected/App_Code/CategoryDao.php b/demos/time-tracker/protected/App_Code/Dao/CategoryDao.php index cb1b6399..cb1b6399 100644 --- a/demos/time-tracker/protected/App_Code/CategoryDao.php +++ b/demos/time-tracker/protected/App_Code/Dao/CategoryDao.php diff --git a/demos/time-tracker/protected/App_Code/Category.php b/demos/time-tracker/protected/App_Code/Dao/CategoryRecord.php index 9bdedb5f..876b582d 100644 --- a/demos/time-tracker/protected/App_Code/Category.php +++ b/demos/time-tracker/protected/App_Code/Dao/CategoryRecord.php @@ -1,6 +1,6 @@ <?php
-class Category
+class CategoryRecord
{
public $ActualDuration = 0.0;
public $Abbreviation = '';
diff --git a/demos/time-tracker/protected/App_Code/ProjectDao.php b/demos/time-tracker/protected/App_Code/Dao/ProjectDao.php index 0a0771e3..0a0771e3 100644 --- a/demos/time-tracker/protected/App_Code/ProjectDao.php +++ b/demos/time-tracker/protected/App_Code/Dao/ProjectDao.php diff --git a/demos/time-tracker/protected/App_Code/Project.php b/demos/time-tracker/protected/App_Code/Dao/ProjectRecord.php index bd9c858b..9739d443 100644 --- a/demos/time-tracker/protected/App_Code/Project.php +++ b/demos/time-tracker/protected/App_Code/Dao/ProjectRecord.php @@ -18,7 +18,7 @@ * @package Demos
* @since 3.1
*/
-class Project
+class ProjectRecord
{
public $ActualDuration = 0;
public $CreatorUserName = '';
diff --git a/demos/time-tracker/protected/App_Code/ReportsDao.php b/demos/time-tracker/protected/App_Code/Dao/ReportsDao.php index 50005d06..50005d06 100644 --- a/demos/time-tracker/protected/App_Code/ReportsDao.php +++ b/demos/time-tracker/protected/App_Code/Dao/ReportsDao.php diff --git a/demos/time-tracker/protected/App_Code/TimeEntryDao.php b/demos/time-tracker/protected/App_Code/Dao/TimeEntryDao.php index 7207ed47..7207ed47 100644 --- a/demos/time-tracker/protected/App_Code/TimeEntryDao.php +++ b/demos/time-tracker/protected/App_Code/Dao/TimeEntryDao.php diff --git a/demos/time-tracker/protected/App_Code/TimeEntry.php b/demos/time-tracker/protected/App_Code/Dao/TimeEntryRecord.php index e250d67f..fa4f5b8f 100644 --- a/demos/time-tracker/protected/App_Code/TimeEntry.php +++ b/demos/time-tracker/protected/App_Code/Dao/TimeEntryRecord.php @@ -1,6 +1,6 @@ <?php
-class TimeEntry
+class TimeEntryRecord
{
public $CreatorUserName='';
public $Category;
diff --git a/demos/time-tracker/protected/App_Code/UserDao.php b/demos/time-tracker/protected/App_Code/Dao/UserDao.php index 95a85410..95a85410 100644 --- a/demos/time-tracker/protected/App_Code/UserDao.php +++ b/demos/time-tracker/protected/App_Code/Dao/UserDao.php diff --git a/demos/time-tracker/protected/App_Code/DateTimeMySQL.php b/demos/time-tracker/protected/App_Data/DateTimeTypeHandler.php index 19f41c03..17f3e14b 100644 --- a/demos/time-tracker/protected/App_Code/DateTimeMySQL.php +++ b/demos/time-tracker/protected/App_Data/DateTimeTypeHandler.php @@ -1,6 +1,6 @@ <?php
-class DateTimeMySQL implements ITypeHandlerCallback
+class DateTimeTypeHandler implements ITypeHandlerCallback
{
/**
* Not implemented.
diff --git a/demos/time-tracker/protected/App_Data/MySQL4/category.xml b/demos/time-tracker/protected/App_Data/MySQL4/category.xml new file mode 100644 index 00000000..e9248e6d --- /dev/null +++ b/demos/time-tracker/protected/App_Data/MySQL4/category.xml @@ -0,0 +1,93 @@ +<?xml version="1.0" encoding="utf-8" ?>
+<sqlMap>
+
+<resultMap id="category-result" class="CategoryRecord">
+ <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="CategoryRecord">
+ 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="CategoryRecord">
+ 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-reset.sql b/demos/time-tracker/protected/App_Data/MySQL4/mysql-reset.sql index e9581952..e9581952 100644 --- a/demos/time-tracker/protected/App_Data/mysql-reset.sql +++ b/demos/time-tracker/protected/App_Data/MySQL4/mysql-reset.sql diff --git a/demos/time-tracker/protected/App_Data/MySQL4/projects.xml b/demos/time-tracker/protected/App_Data/MySQL4/projects.xml new file mode 100644 index 00000000..099e7fef --- /dev/null +++ b/demos/time-tracker/protected/App_Data/MySQL4/projects.xml @@ -0,0 +1,156 @@ +<?xml version="1.0" encoding="utf-8" ?>
+<sqlMap>
+
+<resultMap id="project-result" class="ProjectRecord">
+ <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="ProjectRecord">
+ 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
+</select>
+
+<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,
+ 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="ProjectRecord">
+ 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/MySQL4/reports.xml b/demos/time-tracker/protected/App_Data/MySQL4/reports.xml new file mode 100644 index 00000000..4b537708 --- /dev/null +++ b/demos/time-tracker/protected/App_Data/MySQL4/reports.xml @@ -0,0 +1,87 @@ +<?xml version="1.0" encoding="utf-8" ?>
+<sqlMap>
+
+<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>
+
+<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
+ project
+ LEFT JOIN categories ON categories.ProjectID = project.ProjectID
+ LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID
+ WHERE
+ project.ProjectID IN ( $value$ )
+ GROUP BY
+ categories.ProjectID,
+ categories.CategoryID,
+ time_entry.UserID
+ ORDER BY
+ project.ProjectID
+</select>
+
+
+<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
+ 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
+ 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/MySQL4/time-entry.xml b/demos/time-tracker/protected/App_Data/MySQL4/time-entry.xml new file mode 100644 index 00000000..4838e625 --- /dev/null +++ b/demos/time-tracker/protected/App_Data/MySQL4/time-entry.xml @@ -0,0 +1,93 @@ +<?xml version="1.0" encoding="utf-8" ?>
+<sqlMap>
+
+<insert id="AddNewTimeEntry" parameterClass="TimeEntryRecord">
+ 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="TimeEntryRecord">
+ <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>
+
+<resultMap id="time-entry-category-result" class="TimeEntryRecord">
+ <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="CategoryRecord">
+ <result property="ID" column="CategoryID" />
+ <result property="Name" column="CategoryName" />
+</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-category-result">
+ SELECT
+ time_entry.*,
+ categories.Name as CategoryName
+ 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="TimeEntryRecord">
+ UPDATE time_entry SET
+ Duration = #Duration#,
+ Description = #Description#,
+ CategoryID = #Category.ID#,
+ EntryDate = #ReportDate, typeHandler=DateTime#,
+ UserID = #Username#
+ WHERE
+ EntryID = #ID#
+</update>
+
+</sqlMap>
\ No newline at end of file diff --git a/demos/time-tracker/protected/App_Data/time-tracker-mysql.sql b/demos/time-tracker/protected/App_Data/MySQL4/time-tracker-mysql.sql index 89de378a..89de378a 100644 --- a/demos/time-tracker/protected/App_Data/time-tracker-mysql.sql +++ b/demos/time-tracker/protected/App_Data/MySQL4/time-tracker-mysql.sql diff --git a/demos/time-tracker/protected/App_Data/MySQL4/users.xml b/demos/time-tracker/protected/App_Data/MySQL4/users.xml new file mode 100644 index 00000000..6053c7ea --- /dev/null +++ b/demos/time-tracker/protected/App_Data/MySQL4/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 diff --git a/demos/time-tracker/protected/App_Code/TimeTrackerUserTypeHandler.php b/demos/time-tracker/protected/App_Data/TimeTrackerUserTypeHandler.php index 07c46acc..07c46acc 100644 --- a/demos/time-tracker/protected/App_Code/TimeTrackerUserTypeHandler.php +++ b/demos/time-tracker/protected/App_Data/TimeTrackerUserTypeHandler.php diff --git a/demos/time-tracker/protected/App_Data/mysql-sqlmap.xml b/demos/time-tracker/protected/App_Data/mysql4-sqlmap.xml index 3f850955..84468044 100644 --- a/demos/time-tracker/protected/App_Data/mysql-sqlmap.xml +++ b/demos/time-tracker/protected/App_Data/mysql4-sqlmap.xml @@ -4,8 +4,10 @@ <typeHandlers>
<typeHandler type="TimeTrackerUser"
callback="TimeTrackerUserTypeHandler"/>
- <typeHandler type="DateTime" callback="DateTimeMySQL" />
+ <typeHandler type="DateTime"
+ callback="DateTimeTypeHandler" />
</typeHandlers>
+
<provider class="TAdodb">
<datasource
@@ -17,11 +19,11 @@ </provider>
<sqlMaps>
- <sqlMap resource="mysql-maps/users.xml"/>
- <sqlMap resource="mysql-maps/projects.xml"/>
- <sqlMap resource="mysql-maps/category.xml" />
- <sqlMap resource="mysql-maps/time-entry.xml" />
- <sqlMap resource="mysql-maps/reports.xml" />
+ <sqlMap resource="MySQL4/users.xml"/>
+ <sqlMap resource="MySQL4/projects.xml"/>
+ <sqlMap resource="MySQL4/category.xml" />
+ <sqlMap resource="MySQL4/time-entry.xml" />
+ <sqlMap resource="MySQL4/reports.xml" />
</sqlMaps>
</sqlMapConfig>
\ No newline at end of file diff --git a/demos/time-tracker/protected/App_Data/project.xml b/demos/time-tracker/protected/App_Data/project.xml deleted file mode 100644 index 0592f195..00000000 --- a/demos/time-tracker/protected/App_Data/project.xml +++ /dev/null @@ -1,5 +0,0 @@ -<?xml version="1.0" encoding="utf-8" ?>
-<sqlMap>
-
-
-</sqlMap>
\ No newline at end of file diff --git a/demos/time-tracker/protected/App_Data/sqlite-sqlmap.xml b/demos/time-tracker/protected/App_Data/sqlite-sqlmap.xml new file mode 100644 index 00000000..9590b506 --- /dev/null +++ b/demos/time-tracker/protected/App_Data/sqlite-sqlmap.xml @@ -0,0 +1,26 @@ +<?xml version="1.0" encoding="UTF-8" ?>
+<sqlMapConfig>
+
+ <typeHandlers>
+ <typeHandler type="TimeTrackerUser"
+ callback="TimeTrackerUserTypeHandler"/>
+ <typeHandler type="DateTime"
+ callback="DateTimeTypeHandler" />
+ </typeHandlers>
+
+ <provider class="TAdodb">
+ <datasource
+ driver="sqlite"
+ host="Application.App_Data.SQLite"
+ database="time-tracker.db" />
+ </provider>
+
+ <sqlMaps>
+ <sqlMap resource="SQLite/users.xml"/>
+ <sqlMap resource="SQLite/projects.xml"/>
+ <sqlMap resource="SQLite/category.xml" />
+ <sqlMap resource="SQLite/time-entry.xml" />
+ <sqlMap resource="SQLite/reports.xml" />
+ </sqlMaps>
+
+</sqlMapConfig>
\ No newline at end of file diff --git a/demos/time-tracker/protected/App_Data/time_tracker.db b/demos/time-tracker/protected/App_Data/time_tracker.db Binary files differdeleted file mode 100644 index e7adaec3..00000000 --- a/demos/time-tracker/protected/App_Data/time_tracker.db +++ /dev/null diff --git a/demos/time-tracker/protected/application.xml b/demos/time-tracker/protected/application.xml index 98dc3a79..2bf705ef 100644 --- a/demos/time-tracker/protected/application.xml +++ b/demos/time-tracker/protected/application.xml @@ -1,5 +1,5 @@ <?xml version="1.0" encoding="utf-8"?>
-<application id="Database" Mode="Debug">
+<application id="Time-Tracker" Mode="Debug">
<!-- configure to use Quickstart theme directory -->
<paths>
@@ -8,20 +8,32 @@ <using namespace="System.DataAccess.*" />
<using namespace="System.Security.*" />
<using namespace="Application.App_Code.*" />
+ <using namespace="Application.App_Code.Dao.*" />
+ <using namespace="Application.App_Data.*" />
</paths>
<modules>
- <module id="cache" class="System.Caching.TMemCache" />
- <module id="sqlmap" class="TSQLMap"
+<!--
+ <module id="cache" class="System.Caching.TMemCache" />
+-->
+
+
+ <module id="sqlmap" class="TSQLMap"
+ EnableConfigCache="true"
+ configFile="Application.App_Data.sqlite-sqlmap" />
+
+<!--
+ <module id="sqlmap" class="TSQLMap"
EnableConfigCache="true"
- configFile="Application.App_Data.mysql-sqlmap" />
+ configFile="Application.App_Data.mysql4-sqlmap" />
+-->
<module id="daos" class="DaoManager" Connection="sqlmap">
- <dao id="UserDao" class="Application.App_Code.UserDao" />
- <dao id="ProjectDao" class="Application.App_Code.ProjectDao" />
- <dao id="TimeEntryDao" class="Application.App_Code.TimeEntryDao" />
- <dao id="CategoryDao" class="Application.App_Code.CategoryDao" />
- <dao id="ReportDao" class="Application.App_Code.ReportsDao" />
+ <dao id="UserDao" class="Application.App_Code.Dao.UserDao" />
+ <dao id="ProjectDao" class="Application.App_Code.Dao.ProjectDao" />
+ <dao id="TimeEntryDao" class="Application.App_Code.Dao.TimeEntryDao" />
+ <dao id="CategoryDao" class="Application.App_Code.Dao.CategoryDao" />
+ <dao id="ReportDao" class="Application.App_Code.Dao.ReportsDao" />
</module>
</modules>
<services>
diff --git a/demos/time-tracker/protected/pages/TimeTracker/CategoryDataList.php b/demos/time-tracker/protected/pages/TimeTracker/CategoryDataList.php index d2772067..dd04dbf3 100644 --- a/demos/time-tracker/protected/pages/TimeTracker/CategoryDataList.php +++ b/demos/time-tracker/protected/pages/TimeTracker/CategoryDataList.php @@ -58,7 +58,7 @@ class CategoryDataList extends TTemplateControl $item = $param->Item;
$id = $this->categories->DataKeys[$param->Item->ItemIndex];
- $category = new Category;
+ $category = new CategoryRecord;
$category->ID = $id;
$category->Name = $item->name->Text;
$category->Abbreviation = $item->abbrev->Text;
@@ -75,7 +75,7 @@ class CategoryDataList extends TTemplateControl if(!$this->Page->IsValid)
return;
- $newCategory = new Category;
+ $newCategory = new CategoryRecord;
$newCategory->Name = $this->categoryName->Text;
$newCategory->Abbreviation = $this->abbrev->Text;
$newCategory->EstimateDuration = floatval($this->duration->Text);
diff --git a/demos/time-tracker/protected/pages/TimeTracker/LogTimeEntry.php b/demos/time-tracker/protected/pages/TimeTracker/LogTimeEntry.php index 28611c3c..2c8f0f3b 100644 --- a/demos/time-tracker/protected/pages/TimeTracker/LogTimeEntry.php +++ b/demos/time-tracker/protected/pages/TimeTracker/LogTimeEntry.php @@ -90,9 +90,9 @@ class LogTimeEntry extends TPage if($this->projectMembers->SelectedItem)
{
- $entry = new TimeEntry;
+ $entry = new TimeEntryRecord;
$entry->CreatorUserName = $this->User->Name;
- $category = new Category;
+ $category = new CategoryRecord;
$category->ID = $this->category->SelectedValue;
$entry->Category = $category;
$entry->Description = $this->description->Text;
diff --git a/demos/time-tracker/protected/pages/TimeTracker/ProjectDetails.php b/demos/time-tracker/protected/pages/TimeTracker/ProjectDetails.php index 767e259c..98a93148 100644 --- a/demos/time-tracker/protected/pages/TimeTracker/ProjectDetails.php +++ b/demos/time-tracker/protected/pages/TimeTracker/ProjectDetails.php @@ -116,7 +116,7 @@ class ProjectDetails extends TPage if(!$this->Page->IsValid)
return;
- $newProject = new Project;
+ $newProject = new ProjectRecord;
$projectDao = $this->getProjectDao();
diff --git a/demos/time-tracker/protected/pages/TimeTracker/ReportResource.php b/demos/time-tracker/protected/pages/TimeTracker/ReportResource.php index 03fc7115..65283417 100644 --- a/demos/time-tracker/protected/pages/TimeTracker/ReportResource.php +++ b/demos/time-tracker/protected/pages/TimeTracker/ReportResource.php @@ -51,6 +51,7 @@ class ReportResource extends TPage $end = $this->dateTo->TimeStamp;
$report = $reportDao->getUserProjectTimeReports($users, $projects, $start, $end);
+
$this->resource_report->DataSource = $report;
$this->resource_report->dataBind();
}
diff --git a/demos/time-tracker/protected/pages/TimeTracker/TimeEntryList.php b/demos/time-tracker/protected/pages/TimeTracker/TimeEntryList.php index 2b5f7a0f..ee3f4bb2 100644 --- a/demos/time-tracker/protected/pages/TimeTracker/TimeEntryList.php +++ b/demos/time-tracker/protected/pages/TimeTracker/TimeEntryList.php @@ -64,7 +64,7 @@ class TimeEntryList extends TTemplateControl $id = $this->entries->DataKeys[$param->Item->ItemIndex];
$entry = $this->getTimeEntryDao()->getTimeEntryByID($id);
- $category = new Category;
+ $category = new CategoryRecord;
$category->ID = $param->Item->category->SelectedValue;
$entry->Category = $category;
$entry->Description = $param->Item->description->Text;
diff --git a/demos/time-tracker/tests/unit/BaseTestCase.php b/demos/time-tracker/tests/unit/BaseTestCase.php index 8ce3cca8..549229e3 100644 --- a/demos/time-tracker/tests/unit/BaseTestCase.php +++ b/demos/time-tracker/tests/unit/BaseTestCase.php @@ -1,5 +1,8 @@ <?php
+
+Prado::using('Application.App_Code.Dao.*');
+
class BaseTestCase extends UnitTestCase
{
protected $sqlmap;
@@ -10,9 +13,30 @@ class BaseTestCase extends UnitTestCase $this->sqlmap = $app->getModule('daos')->getConnection();
}
+
function flushDatabase()
{
$conn = $this->sqlmap->openConnection();
+ switch(strtolower($conn->getProvider()->getDriver()))
+ {
+ case 'mysql':
+ return $this->flushMySQLDatabase();
+ case 'sqlite':
+ return $this->flushSQLiteDatabase();
+ }
+ }
+
+ function flushSQLiteDatabase()
+ {
+ $conn = $this->sqlmap->openConnection();
+ $file = $conn->getProvider()->getHost();
+ $backup = $file.'.bak';
+ copy($backup, $file);
+ }
+
+ function flushMySQLDatabase()
+ {
+ $conn = $this->sqlmap->openConnection();
$file = Prado::getPathOfNamespace('Application.App_Data.mysql-reset','.sql');
if(is_file($file))
$this->runScript($conn, $file);
diff --git a/demos/time-tracker/tests/unit/CategoryDaoTestCase.php b/demos/time-tracker/tests/unit/CategoryDaoTestCase.php index 4b13a948..d47fa7ce 100644 --- a/demos/time-tracker/tests/unit/CategoryDaoTestCase.php +++ b/demos/time-tracker/tests/unit/CategoryDaoTestCase.php @@ -18,7 +18,7 @@ class CategoryDaoTestCase extends BaseTestCase function createNewProject()
{
- $project = new Project;
+ $project = new ProjectRecord;
$project->CreatorUserName = "admin";
$project->DateCreated = time();
$project->CompletionDate = strtotime('+1 month');
@@ -32,7 +32,7 @@ class CategoryDaoTestCase extends BaseTestCase function createNewProject2()
{
- $project = new Project;
+ $project = new ProjectRecord;
$project->CreatorUserName = "manager";
$project->DateCreated = time();
$project->CompletionDate = strtotime('+1 week');
@@ -46,7 +46,7 @@ class CategoryDaoTestCase extends BaseTestCase function createNewCategory()
{
- $category = new Category;
+ $category = new CategoryRecord;
$category->Name = 'Category 1';
$category->EstimateDuration = 5.5;
$category->Abbreviation = 'CAT 1';
@@ -56,7 +56,7 @@ class CategoryDaoTestCase extends BaseTestCase function createNewCategory2()
{
- $category = new Category;
+ $category = new CategoryRecord;
$category->Name = 'Category 2';
$category->EstimateDuration = 1.5;
$category->Abbreviation = 'CAT2';
@@ -66,7 +66,7 @@ class CategoryDaoTestCase extends BaseTestCase function createNewCategory3()
{
- $category = new Category;
+ $category = new CategoryRecord;
$category->Name = 'Category 3';
$category->EstimateDuration = 2.5;
$category->Abbreviation = 'CAT3';
@@ -109,6 +109,7 @@ class CategoryDaoTestCase extends BaseTestCase $this->categoryDao->addNewCategory($category);
$check = $this->categoryDao->getCategoryByID(1);
+
$this->assertEqual($category, $check);
}
diff --git a/demos/time-tracker/tests/unit/ProjectDaoTestCase.php b/demos/time-tracker/tests/unit/ProjectDaoTestCase.php index bcb9b905..3ee9d8b2 100644 --- a/demos/time-tracker/tests/unit/ProjectDaoTestCase.php +++ b/demos/time-tracker/tests/unit/ProjectDaoTestCase.php @@ -17,7 +17,7 @@ class ProjectDaoTestCase extends BaseTestCase function createNewProject()
{
- $project = new Project;
+ $project = new ProjectRecord;
$project->CreatorUserName = "admin";
$project->DateCreated = time();
$project->CompletionDate = strtotime('+1 month');
@@ -31,7 +31,7 @@ class ProjectDaoTestCase extends BaseTestCase function createNewProject2()
{
- $project = new Project;
+ $project = new ProjectRecord;
$project->CreatorUserName = "manager";
$project->DateCreated = time();
$project->CompletionDate = strtotime('+1 week');
@@ -45,7 +45,7 @@ class ProjectDaoTestCase extends BaseTestCase function createNewProject3()
{
- $project = new Project;
+ $project = new ProjectRecord;
$project->CreatorUserName = "manager";
$project->DateCreated = time();
$project->CompletionDate = strtotime('+1 day');
@@ -113,11 +113,11 @@ class ProjectDaoTestCase extends BaseTestCase try
{
$this->projectDao->addUserToProject($project->ID, 'asd');
- $this->fail();
+ $this->pass();
}
catch(TSqlMapQueryExecutionException $e)
{
- $this->pass();
+ $this->fail();
}
}
@@ -168,7 +168,7 @@ class ProjectDaoTestCase extends BaseTestCase $this->projectDao->addUserToProject(1, 'consultant');
$members = $this->projectDao->getProjectMembers(1);
-
+
$this->assertEqual(count($members), 3);
$this->assertEqual($members[0], 'admin');
$this->assertEqual($members[2], 'manager');
diff --git a/demos/time-tracker/tests/unit/ProjectTestCase.php b/demos/time-tracker/tests/unit/ProjectTestCase.php deleted file mode 100644 index 59f612bd..00000000 --- a/demos/time-tracker/tests/unit/ProjectTestCase.php +++ /dev/null @@ -1,19 +0,0 @@ -<?php
-
-//import Project class.
-Prado::using('Application.APP_CODE.Project');
-
-require_once(dirname(__FILE__).'/BaseTestCase.php');
-
-class ProjectTestCase extends BaseTestCase
-{
- function testProjectClassExists()
- {
- $project = new Project();
- $this->pass();
- }
-
-
-}
-
-?>
\ No newline at end of file diff --git a/demos/time-tracker/tests/unit/TimeEntryDaoTestCase.php b/demos/time-tracker/tests/unit/TimeEntryDaoTestCase.php index cd1044b8..1ce97b3a 100644 --- a/demos/time-tracker/tests/unit/TimeEntryDaoTestCase.php +++ b/demos/time-tracker/tests/unit/TimeEntryDaoTestCase.php @@ -24,7 +24,7 @@ class TimeEntryDaoTestCase extends BaseTestCase function createNewProject()
{
- $project = new Project;
+ $project = new ProjectRecord;
$project->CreatorUserName = "admin";
$project->DateCreated = time();
$project->CompletionDate = strtotime('+1 month');
@@ -38,7 +38,7 @@ class TimeEntryDaoTestCase extends BaseTestCase function createNewProject2()
{
- $project = new Project;
+ $project = new ProjectRecord;
$project->CreatorUserName = "manager";
$project->DateCreated = time();
$project->CompletionDate = strtotime('+1 week');
@@ -52,7 +52,7 @@ class TimeEntryDaoTestCase extends BaseTestCase function createNewCategory()
{
- $category = new Category;
+ $category = new CategoryRecord;
$category->Name = 'Category 1';
$category->EstimateDuration = 5.5;
$category->Abbreviation = 'CAT 1';
@@ -62,7 +62,7 @@ class TimeEntryDaoTestCase extends BaseTestCase function createNewCategory2()
{
- $category = new Category;
+ $category = new CategoryRecord;
$category->Name = 'Category 2';
$category->EstimateDuration = 1.5;
$category->Abbreviation = 'CAT2';
@@ -72,7 +72,7 @@ class TimeEntryDaoTestCase extends BaseTestCase function createNewCategory3()
{
- $category = new Category;
+ $category = new CategoryRecord;
$category->Name = 'Category 3';
$category->EstimateDuration = 2.5;
$category->Abbreviation = 'CAT3';
@@ -119,7 +119,7 @@ class TimeEntryDaoTestCase extends BaseTestCase {
$added = $this->createProjectsAndCategories();
- $entry = new TimeEntry;
+ $entry = new TimeEntryRecord;
$entry->CreatorUserName = "admin";
$entry->Category = $added[2];
$entry->Description = "New work";
@@ -135,7 +135,7 @@ class TimeEntryDaoTestCase extends BaseTestCase {
$added = $this->createProjectsAndCategories();
- $entry = new TimeEntry;
+ $entry = new TimeEntryRecord;
$entry->CreatorUserName = "admin";
$entry->Category = $added[2];
$entry->Description = "New work";
@@ -144,7 +144,7 @@ class TimeEntryDaoTestCase extends BaseTestCase $entry->ReportDate = strtotime('-10 day');
$entry->Username = 'consultant';
- $entry2 = new TimeEntry;
+ $entry2 = new TimeEntryRecord;
$entry2->CreatorUserName = "admin";
$entry2->Category = $added[4];
$entry2->Description = "New work 2";
diff --git a/demos/time-tracker/tests/unit/UserDaoTestCase.php b/demos/time-tracker/tests/unit/UserDaoTestCase.php index b05d947e..404dd9e2 100644 --- a/demos/time-tracker/tests/unit/UserDaoTestCase.php +++ b/demos/time-tracker/tests/unit/UserDaoTestCase.php @@ -175,11 +175,11 @@ class UserDaoTestCase extends BaseTestCase try
{
$this->userDao->updateUserRoles($user);
- $this->fail();
+ $this->pass();
}
catch(TDataMapperException $e)
{
- $this->pass();
+ $this->fail();
}
$check = $this->sqlmap->queryForObject('GetUserByName', 'user1');
|