Revize b95b30bc

#6 Allow for String AP parameters

- added parametrization into query business as usual
- minor refactor

src/main/webapp/queries/business_as_usual.sql

@@ -20 +20 @@
 /* Number of iterations for given project */
 select COUNT(id) as 'numberOfIterations' from iteration where superProjectId = @projectId and name like '%itera%';
 /* Select all iteration with detected retrospective activities */
-select iterationName as 'iterationName', count(name) as 'numberOfIssues' from workUnitView where projectId = @projectId and (lower(name) like lower('%retr%') or lower(name) like lower('%revi%') or lower(name) like lower('%week%scrum%')) group by iterationName;
+select iterationName as 'iterationName', count(name) as 'numberOfIssues' from workUnitView where projectId = @projectId and (lower(name) like lower('§0§') or lower(name) like lower('§1§') or lower(name) like lower('§2§') or lower(name) like lower('§3§') or lower(name) like lower('§4§') or lower(name) like lower('§5§') or lower(name) like lower('§6§') or lower(name) like lower('§7§') or lower(name) like lower('§8§') or lower(name) like lower('§9§')) group by iterationName;
 /* Select all wikipages that were created or updated in iteration and have name with retr or revi*/
-select iteration.name as 'iterationName', count(distinct(artifactView.name)) as 'numberOfWikiPages' from artifactView inner join fieldChangeView on artifactView.id = fieldChangeView.itemId inner join iteration on (fieldChangeView.created between iteration.startDate and iteration.endDate) and iteration.superProjectId = @projectId where artifactView.projectId = @projectId and artifactView.artifactClass like 'WIKIPAGE' and (lower(artifactView.name) like lower('%retr%') or lower(artifactView.description) like lower('%retr%') or lower(artifactView.description) like lower('%week%scrum%')) group by iteration.id order by iteration.name;
+select iteration.name as 'iterationName', count(distinct(artifactView.name)) as 'numberOfWikiPages' from artifactView inner join fieldChangeView on artifactView.id = fieldChangeView.itemId inner join iteration on (fieldChangeView.created between iteration.startDate and iteration.endDate) and iteration.superProjectId = @projectId where artifactView.projectId = @projectId and artifactView.artifactClass like 'WIKIPAGE' and (lower(artifactView.name) like lower('§0§') or lower(artifactView.name) like lower('§1§') or lower(artifactView.name) like lower('§2§') or lower(artifactView.name) like lower('§3§') or lower(artifactView.name) like lower('§4§') or lower(artifactView.name) like lower('§5§') or lower(artifactView.name) like lower('§6§') or lower(artifactView.name) like lower('§7§') or lower(artifactView.name) like lower('§8§') or lower(artifactView.name) like lower('§9§')) group by iteration.id order by iteration.name;

src/main/webapp/queries/long_or_non_existent_feedback_loops.sql

@@ -23 +23 @@
 /* Average iteration length */
 select avg(abs(dateDiff(iteration.endDate, iteration.startDate))) as 'averageIterationLength' from iteration where superProjectId = @projectId;
 /* Select number of iterations which contains at least one feedback activity */
-select count(*) over () as 'totalCountOfIterationsWithFeedbackActivity' from workUnitView as wuv where wuv.projectId = @projectId and (wuv.name like "§0§" or wuv.name like "§1§" or wuv.name like "§2§" or wuv.name like "§3§" or wuv.name like "§4§" or wuv.name like "§5§" or wuv.name like "§6§" or wuv.name like "§7§" or wuv.name like "§8§" or wuv.name like "§9§") group by wuv.iterationName order by wuv.activityEndDate;
+select count(*) over () as 'totalCountOfIterationsWithFeedbackActivity' from workUnitView as wuv where wuv.projectId = @projectId and (wuv.name like '§0§' or wuv.name like '§1§' or wuv.name like '§2§' or wuv.name like '§3§' or wuv.name like '§4§' or wuv.name like '§5§' or wuv.name like '§6§' or wuv.name like '§7§' or wuv.name like '§8§' or wuv.name like '§9§') group by wuv.iterationName order by wuv.activityEndDate;
 /* Select all activities for feedback loop with last modified date as end date */
-select wuv.id, wuv.iterationName, wuv.name, cast(max(fieldChangeView.created) as date) as 'endDate' from workUnitView as wuv inner join fieldChangeView on wuv.id = fieldChangeView.itemId where wuv.projectId = @projectId and (wuv.name like "§0§" or wuv.name like "§1§" or wuv.name like "§2§" or wuv.name like "§3§" or wuv.name like "§4§" or wuv.name like "§5§" or wuv.name like "§6§" or wuv.name like "§7§" or wuv.name like "§8§" or wuv.name like "§9§") GROUP by id order by fieldChangeView.created;
+select wuv.id, wuv.iterationName, wuv.name, cast(max(fieldChangeView.created) as date) as 'endDate' from workUnitView as wuv inner join fieldChangeView on wuv.id = fieldChangeView.itemId where wuv.projectId = @projectId and (wuv.name like '§0§' or wuv.name like '§1§' or wuv.name like '§2§' or wuv.name like '§3§' or wuv.name like '§4§' or wuv.name like '§5§' or wuv.name like '§6§' or wuv.name like '§7§' or wuv.name like '§8§' or wuv.name like '§9§') GROUP by id order by fieldChangeView.created;
 /* Get project start date */
 select startDate as 'projectStartDate' from iteration where superProjectId = @projectId order by startDate limit 1;
 /* Get project end date */
 select endDate as 'projectEndDate' from iteration where superProjectId = @projectId order by endDate desc limit 1;
 /* Select all iterations that contains wiki pages which were created or updated in iteration and have name or description that mentions some key words for customer demo*/
-select iteration.name as 'iterationWithCustomerFeedback', cast(max(fieldChangeView.created) as date) as 'appointmentDate' from artifactView inner join fieldChangeView on artifactView.id = fieldChangeView.itemId inner join iteration on (fieldChangeView.created between iteration.startDate and iteration.endDate) and iteration.superProjectId = @projectId where artifactView.artifactClass like "WIKIPAGE" and artifactView.projectId = @projectId and length(fieldChangeView.newValue) > length(fieldChangeView.oldValue) and (artifactView.name like "§0§" or artifactView.name like "§1§" or artifactView.name like "§2§" or artifactView.name like "§3§" or artifactView.name like "§4§" or artifactView.name like "§5§" or artifactView.name like "§6§" or artifactView.name like "§7§" or artifactView.name like "§8§" or artifactView.name like "§9§") group by iteration.name order by iteration.name;
+select iteration.name as 'iterationWithCustomerFeedback', cast(max(fieldChangeView.created) as date) as 'appointmentDate' from artifactView inner join fieldChangeView on artifactView.id = fieldChangeView.itemId inner join iteration on (fieldChangeView.created between iteration.startDate and iteration.endDate) and iteration.superProjectId = @projectId where artifactView.artifactClass like 'WIKIPAGE' and artifactView.projectId = @projectId and length(fieldChangeView.newValue) > length(fieldChangeView.oldValue) and (artifactView.name like '§0§' or artifactView.name like '§1§' or artifactView.name like '§2§' or artifactView.name like '§3§' or artifactView.name like '§4§' or artifactView.name like '§5§' or artifactView.name like '§6§' or artifactView.name like '§7§' or artifactView.name like '§8§' or artifactView.name like '§9§') group by iteration.name order by iteration.name;