Subversion-Projekte lars-tiefland.prado

<?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>