summaryrefslogtreecommitdiff
path: root/demos/time-tracker/protected/App_Data/Sqlite
diff options
context:
space:
mode:
Diffstat (limited to 'demos/time-tracker/protected/App_Data/Sqlite')
-rw-r--r--demos/time-tracker/protected/App_Data/Sqlite/category.xml109
-rw-r--r--demos/time-tracker/protected/App_Data/Sqlite/projects.xml159
-rw-r--r--demos/time-tracker/protected/App_Data/Sqlite/reports.xml87
-rw-r--r--demos/time-tracker/protected/App_Data/Sqlite/time-entry.xml100
-rw-r--r--demos/time-tracker/protected/App_Data/Sqlite/time-tracker.dbbin32768 -> 0 bytes
-rw-r--r--demos/time-tracker/protected/App_Data/Sqlite/time-tracker.db.bakbin32768 -> 0 bytes
-rw-r--r--demos/time-tracker/protected/App_Data/Sqlite/users.xml137
7 files changed, 0 insertions, 592 deletions
diff --git a/demos/time-tracker/protected/App_Data/Sqlite/category.xml b/demos/time-tracker/protected/App_Data/Sqlite/category.xml
deleted file mode 100644
index d89a517f..00000000
--- a/demos/time-tracker/protected/App_Data/Sqlite/category.xml
+++ /dev/null
@@ -1,109 +0,0 @@
-<?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_ROWID() as value
- </selectKey>
-</insert>
-
-<select id="GetCategoryByID" parameterClass="integer" resultMap="category-result">
- SELECT
- categories.Name as Name,
- categories.EstimateDuration as EstimateDuration,
- categories.ProjectID as ProjectID,
- categories.Abbreviation as Abbreviation,
- categories.CategoryID as CategoryID,
- 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.Name as Name,
- categories.EstimateDuration as EstimateDuration,
- categories.ProjectID as ProjectID,
- categories.Abbreviation as Abbreviation,
- categories.CategoryID as CategoryID,
- 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.Name as Name,
- categories.EstimateDuration as EstimateDuration,
- categories.ProjectID as ProjectID,
- categories.Abbreviation as Abbreviation,
- categories.CategoryID as CategoryID,
- 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.Name as Name,
- categories.EstimateDuration as EstimateDuration,
- categories.ProjectID as ProjectID,
- categories.Abbreviation as Abbreviation,
- categories.CategoryID as CategoryID,
- 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/Sqlite/projects.xml b/demos/time-tracker/protected/App_Data/Sqlite/projects.xml
deleted file mode 100644
index e5f52887..00000000
--- a/demos/time-tracker/protected/App_Data/Sqlite/projects.xml
+++ /dev/null
@@ -1,159 +0,0 @@
-<?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 projects WHERE Name = #value#
-</select>
-
-<insert id="CreateNewProject" parameterClass="ProjectRecord">
- INSERT INTO projects
- (Name, Description, CreationDate, EstimateDuration, CompletionDate, CreatorID, ManagerID)
- VALUES
- (#Name#, #Description#, php('date', 'Y-m-d H:i:s'), #EstimateDuration#,
- #CompletionDate, typeHandler=DateTime#,
- #CreatorUserName#, #ManagerUserName#)
- <selectKey property="ID" type="post" resultClass="int">
- select LAST_INSERT_ROWID() as value
- </selectKey>
-</insert>
-
-<select id="GetProjectByID" parameterClass="integer" resultMap="project-result">
- SELECT
- projects.ProjectID as ProjectID,
- projects.Name as Name,
- projects.Description as Description,
- projects.CreationDate as CreationDate,
- projects.EstimateDuration as EstimateDuration,
- projects.CompletionDate as CompletionDate,
- projects.CreatorID as CreatorID,
- projects.ManagerID as ManagerID,
- SUM(time_entry.Duration) as ActualDuration
- FROM projects
- LEFT JOIN categories ON projects.ProjectID = categories.ProjectID
- LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID
- WHERE
- projects.ProjectID = #value#
- AND Disabled = 0
- GROUP BY
- projects.ProjectID
- ORDER BY
- projects.Name
-</select>
-
-<select id="GetAllProjects" resultMap="project-result">
- SELECT
- projects.ProjectID as ProjectID,
- projects.Name as Name,
- projects.Description as Description,
- projects.CreationDate as CreationDate,
- projects.EstimateDuration as EstimateDuration,
- projects.CompletionDate as CompletionDate,
- projects.CreatorID as CreatorID,
- projects.ManagerID as ManagerID,
- SUM(time_entry.Duration) as ActualDuration
- FROM projects
- LEFT JOIN categories ON projects.ProjectID = categories.ProjectID
- LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID
- WHERE
- Disabled = 0
- GROUP BY
- projects.ProjectID
- ORDER BY
- projects.ProjectID ASC
-</select>
-
-<select id="GetAllProjectsOrdered" resultMap="project-result" extends="GetAllProjects">
- ORDER BY $sort$ $order$
-</select>
-
-<select id="GetProjectsByManagerName" resultMap="project-result">
- SELECT
- projects.ProjectID as ProjectID,
- projects.Name as Name,
- projects.Description as Description,
- projects.CreationDate as CreationDate,
- projects.EstimateDuration as EstimateDuration,
- projects.CompletionDate as CompletionDate,
- projects.CreatorID as CreatorID,
- projects.ManagerID as ManagerID,
- SUM(time_entry.Duration) as ActualDuration
- FROM projects
- LEFT JOIN categories ON projects.ProjectID = categories.ProjectID
- LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID
- WHERE
- Disabled = 0
- AND projects.ManagerID = #value#
- GROUP BY
- projects.ProjectID
- ORDER BY
- projects.Name
-</select>
-
-<select id="GetProjectsByUserName" resultMap="project-result">
- SELECT
- projects.ProjectID as ProjectID,
- projects.Name as Name,
- projects.Description as Description,
- projects.CreationDate as CreationDate,
- projects.EstimateDuration as EstimateDuration,
- projects.CompletionDate as CompletionDate,
- projects.CreatorID as CreatorID,
- projects.ManagerID as ManagerID,
- SUM(time_entry.Duration) as ActualDuration
- FROM projects
- LEFT JOIN categories ON projects.ProjectID = categories.ProjectID
- LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID,
- project_members
- WHERE
- project_members.ProjectID = projects.ProjectID
- AND project_members.UserID = #value#
- AND projects.Disabled = 0
- GROUP BY
- projects.ProjectID
- ORDER BY
- projects.Name
-</select>
-
-<update id="DeleteProject" parameterClass="integer">
- UPDATE projects SET Disabled = 1 WHERE ProjectID = #value#
-</update>
-
-<select id="GetProjectMembers" parameterClass="integer">
- SELECT UserID FROM project_members WHERE ProjectID = #value#
- ORDER BY UserID
-</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 projects
- 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/Sqlite/reports.xml b/demos/time-tracker/protected/App_Data/Sqlite/reports.xml
deleted file mode 100644
index b5997f1f..00000000
--- a/demos/time-tracker/protected/App_Data/Sqlite/reports.xml
+++ /dev/null
@@ -1,87 +0,0 @@
-<?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,
- projects.ProjectID as ProjectID,
- categories.EstimateDuration as CategoryEstimate,
- projects.Name as ProjectName,
- projects.EstimateDuration as ProjectEstimate,
- projects.CompletionDate as ProjectCompletion,
- time_entry.UserID as Username,
- SUM(time_entry.Duration) as ActualDuration
- FROM
- projects
- LEFT JOIN categories ON categories.ProjectID = projects.ProjectID
- LEFT JOIN time_entry ON categories.CategoryID = time_entry.CategoryID
- WHERE
- projects.ProjectID IN ( $value$ )
- GROUP BY
- categories.ProjectID,
- categories.CategoryID,
- time_entry.UserID
- ORDER BY
- projects.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 as Username,
- projects.Name as ProjectName,
- categories.Name as CategoryName,
- time_entry.Duration as Duration,
- time_entry.Description as Description,
- time_entry.EntryDate as 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 projects ON categories.ProjectID = projects.ProjectID
- AND projects.ProjectID in ($projects$)
- WHERE
- users.Username in ($members$)
- ORDER BY
- users.Username ASC,
- time_entry.EntryDate ASC,
- projects.Name ASC,
- categories.Name ASC
-</select>
-
-</sqlMap> \ No newline at end of file
diff --git a/demos/time-tracker/protected/App_Data/Sqlite/time-entry.xml b/demos/time-tracker/protected/App_Data/Sqlite/time-entry.xml
deleted file mode 100644
index 1d7fec37..00000000
--- a/demos/time-tracker/protected/App_Data/Sqlite/time-entry.xml
+++ /dev/null
@@ -1,100 +0,0 @@
-<?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(
- php('date', 'Y-m-d H:i:s'),
- #Duration#,
- #Description#,
- #Category.ID#,
- #ReportDate, typeHandler=DateTime#,
- #CreatorUserName#,
- #Username#
- )
- <selectKey property="ID" type="post" resultClass="int">
- select LAST_INSERT_ROWID() 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.EntryID as EntryID,
- time_entry.EntryCreated as EntryCreated,
- time_entry.Duration as Duration,
- time_entry.Description as Description,
- time_entry.EntryDate as EntryDate,
- time_entry.CreatorID as CreatorID,
- time_entry.UserID as UserID,
- time_entry.CategoryID as CategoryID,
- 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/Sqlite/time-tracker.db b/demos/time-tracker/protected/App_Data/Sqlite/time-tracker.db
deleted file mode 100644
index 124bbeba..00000000
--- a/demos/time-tracker/protected/App_Data/Sqlite/time-tracker.db
+++ /dev/null
Binary files differ
diff --git a/demos/time-tracker/protected/App_Data/Sqlite/time-tracker.db.bak b/demos/time-tracker/protected/App_Data/Sqlite/time-tracker.db.bak
deleted file mode 100644
index 124bbeba..00000000
--- a/demos/time-tracker/protected/App_Data/Sqlite/time-tracker.db.bak
+++ /dev/null
Binary files differ
diff --git a/demos/time-tracker/protected/App_Data/Sqlite/users.xml b/demos/time-tracker/protected/App_Data/Sqlite/users.xml
deleted file mode 100644
index 74b33b2c..00000000
--- a/demos/time-tracker/protected/App_Data/Sqlite/users.xml
+++ /dev/null
@@ -1,137 +0,0 @@
-<?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#, php('date', 'Y-m-d H:i:s'))
-</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 = php('date', 'Y-m-d H:i:s')
- 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
generated by cgit v1.2.3 (git 2.46.0) at 2024-11-14 02:18:59 +0000