sql.properties

collective_procrastination_for_iteration=set @projectId = ?; \
set @iterationId = ?;	 \
set @daysLookahead = 3; \
set @daysLookbehind = 6; \
set @silenceSteepnes = 0.1; \
set @cliffSteepnes = 2; \
set @endDate = (select \
					max(date_format(work_item.created, '%Y-%m-%d')) \
				from \
					ppicha.field_change, \
					ppicha.work_item_change, \
					ppicha.work_unit, \
					ppicha.configuration_change, \
					ppicha.work_item \
				where \
					field_change.name = 'status' \
					and (field_change.newValue = 'Closed' or newValue = 'Invalid') \
					and field_change.workItemChangeId = work_item_change.id \
					and work_item_change.workItemId = work_unit.id \
					and work_unit.iterationId = @iterationId \
					and work_item_change.id = configuration_change.changeId \
					and configuration_change.configurationId = work_item.id \
				); \
set @startDate = (select \
						min(date_format(work_item.created, '%Y-%m-%d')) \
					from \
						ppicha.work_unit, \
						ppicha.work_item \
					where \
						work_unit.id = work_item.id \
						and work_unit.iterationId = @iterationId \
					); \
set @issuesCount = (select count(id) from ppicha.work_unit where work_unit.iterationId = @iterationId); \
set @duration = (select datediff(@endDate, @startDate) + 1); \
set @startDateFormatted = date_format(@startDate, '%Y-%m-%d'); \
set @endDateFormatted = date_format(@endDate, '%Y-%m-%d'); \
set @dailyIdeal = (select @issuesCount / @duration); \
set @silenceThreshold = @silenceSteepnes * @issuesCount; \
set @cliffThreshold = @cliffSteepnes * @daysLookahead * @dailyIdeal; \
select \
	'closedOn', \
    'lookbehind', \
    'silenceThreshold', \
    'lookahead', \
    'cliffThreshold', \
    'detected' \
union \
select \
	results.closedOn, \
    results.lookbehind, \
    @silenceThreshold, \
    results.lookahead , \
    @cliffThreshold, \
    if(results.dayIndex >= @daysLookbehind and results.lookbehind <= @silenceThreshold and results.lookahead >= @cliffThreshold, true, false) detected \
from \
	( \
    select \
		@curRow := @curRow + 1 as dayIndex, \
		days.selected_date as closedOn, \
		ifnull(dataPoints.lookbehind, 0) as lookbehind, \
		ifnull(dataPoints.lookahead, 0) as lookahead \
	from \
		( \
        select \
			* \
		from \
			( \
			select \
				adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date \
			from \
				(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, \
				(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, \
				(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, \
				(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3 \
			) \
            v \
		where \
			v.selected_date between @startDateFormatted and @endDateFormatted \
		) \
        days \
	left join \
		( \
		select \
			ifnull(lookbehind.dataPoint, lookahead.dataPoint) as dataPoint, \
			ifnull(lookbehind.closedCount, 0) as lookbehind, \
			ifnull(lookahead.closedCount, 0) as lookahead \
		from \
			( \
			select \
				days.selected_date as dataPoint, \
				count(distinct work_unit.id) as closedCount \
			from \
				( \
				select \
					* \
				from \
					( \
					select \
						adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date \
					from \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3 \
					) \
                    v \
				where \
					v.selected_date between @startDateFormatted and @endDateFormatted \
				) \
                days, \
				ppicha.work_item, \
				ppicha.configuration_change, \
				ppicha.work_item_change, \
				ppicha.work_unit, \
				ppicha.field_change, \
				ppicha.status, \
				ppicha.project_instance, \
				ppicha.project, \
				ppicha.status_classification \
			where \
				date_format(work_item.created, '%Y-%m-%d') > days.selected_date \
				and date_format(work_item.created, '%Y-%m-%d') <= adddate(days.selected_date, interval @daysLookahead day) \
				and work_item.id = configuration_change.configurationId \
				and configuration_change.changeId = work_item_change.id \
				and work_item_change.workItemId = work_unit.id \
				and work_unit.iterationId = @iterationId \
				and work_item_change.id = field_change.workItemChangeId \
				and field_change.name = 'status' \
				and field_change.newValue = status.name \
				and status.projectInstanceId = project_instance.id \
				and project_instance.projectId = @projectId \
				and status.classId = status_classification.id \
				and status_classification.superClass = 'CLOSED' \
			group by \
				dataPoint \
			) \
            lookahead \
		left join \
			( \
			select \
				days.selected_date as dataPoint, \
				count(distinct work_unit.id) as closedCount \
			from \
				( \
				select \
					* \
				from \
					( \
					select \
						adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date \
					from \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3 \
					) \
                    v \
				where \
					v.selected_date between @startDateFormatted and @endDateFormatted \
				) \
                days, \
				ppicha.work_item, \
				ppicha.configuration_change, \
				ppicha.work_item_change, \
				ppicha.work_unit, \
				ppicha.field_change, \
				ppicha.status, \
				ppicha.project_instance, \
				ppicha.project, \
				ppicha.status_classification \
			where \
				date_format(work_item.created, '%Y-%m-%d') <= days.selected_date \
				and date_format(work_item.created, '%Y-%m-%d') > subdate(days.selected_date, interval @daysLookbehind day) \
				and work_item.id = configuration_change.configurationId \
				and configuration_change.changeId = work_item_change.id \
				and work_item_change.workItemId = work_unit.id \
				and work_unit.iterationId = @iterationId \
				and work_item_change.id = field_change.workItemChangeId \
				and field_change.name = 'status' \
				and field_change.newValue = status.name \
				and status.projectInstanceId = project_instance.id \
				and project_instance.projectId = @projectId \
				and status.classId = status_classification.id \
				and status_classification.superClass = 'CLOSED' \
			group by \
				dataPoint \
			) \
            lookbehind \
			on lookahead.dataPoint = lookbehind.dataPoint \
		union \
		select \
			ifnull(lookbehind.dataPoint, lookahead.dataPoint) as dataPoint, \
			ifnull(lookbehind.closedCount, 0) as lookbehind, \
			ifnull(lookahead.closedCount, 0) as lookahead \
		from \
			( \
			select \
				days.selected_date as dataPoint, \
				count(distinct work_unit.id) as closedCount \
			from \
				( \
				select \
					* \
				from \
					( \
					select \
						adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date \
					from \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3 \
					) \
					v \
				where \
					v.selected_date between @startDateFormatted and @endDateFormatted \
				) \
				days, \
				ppicha.work_item, \
				ppicha.configuration_change, \
				ppicha.work_item_change, \
				ppicha.work_unit, \
				ppicha.field_change, \
				ppicha.status, \
				ppicha.project_instance, \
				ppicha.project, \
				ppicha.status_classification \
			where \
				date_format(work_item.created, '%Y-%m-%d') > days.selected_date \
				and date_format(work_item.created, '%Y-%m-%d') <= adddate(days.selected_date, interval @daysLookahead day) \
				and work_item.id = configuration_change.configurationId \
				and configuration_change.changeId = work_item_change.id \
				and work_item_change.workItemId = work_unit.id \
				and work_unit.iterationId = @iterationId \
				and work_item_change.id = field_change.workItemChangeId \
				and field_change.name = 'status' \
				and field_change.newValue = status.name \
				and status.projectInstanceId = project_instance.id \
				and project_instance.projectId = @projectId \
				and status.classId = status_classification.id \
				and status_classification.superClass = 'CLOSED' \
			group by \
				dataPoint \
			) \
			lookahead \
		right join \
			( \
			select \
				days.selected_date as dataPoint, \
				count(distinct work_unit.id) as closedCount \
			from \
				( \
				select \
					* \
				from \
					( \
					select \
						adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date \
					from \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, \
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3 \
					) \
					v \
				where \
					v.selected_date between @startDateFormatted and @endDateFormatted \
				)  \
				days, \
				ppicha.work_item, \
				ppicha.configuration_change, \
				ppicha.work_item_change, \
				ppicha.work_unit, \
				ppicha.field_change, \
				ppicha.status, \
				ppicha.project_instance, \
				ppicha.project, \
				ppicha.status_classification \
			where \
				date_format(work_item.created, '%Y-%m-%d') <= days.selected_date \
				and date_format(work_item.created, '%Y-%m-%d') > subdate(days.selected_date, interval @daysLookbehind day) \
				and work_item.id = configuration_change.configurationId \
				and configuration_change.changeId = work_item_change.id \
				and work_item_change.workItemId = work_unit.id \
				and work_unit.iterationId = @iterationId \
				and work_item_change.id = field_change.workItemChangeId \
				and field_change.name = 'status' \
				and field_change.newValue = status.name \
				and status.projectInstanceId = project_instance.id \
				and project_instance.projectId = @projectId \
				and status.classId = status_classification.id \
				and status_classification.superClass = 'CLOSED' \
			group by \
				dataPoint \
			) \
			lookbehind \
			on lookahead.dataPoint = lookbehind.dataPoint \
		) \
		dataPoints \
		on days.selected_date = dataPoints.dataPoint \
    join \
		(select @curRow := 0) r \
	order by \
		days.selected_date \
	) \
    results

half_done_is_enough2=set @projectId = ?; \
set @notFixingThreshold = 0.1; \
set @bugExistingThreshold = 9; \
set @startDateFormatted = date_format((select startDate from ppicha.project where id = @projectId), '%Y-%m-%d'); \
set @endDateformatted = (select \
								max(date_format(work_item.created, '%Y-%m-%d')) \
							from \
								ppicha.configuration, \
								ppicha.work_item \
							where \
								configuration.id = work_item.id \
								and configuration.projectId = @projectId \
							); \
set @issuesCount = (select count(id) from ppicha.work_unit where work_unit.projectId = @projectId); \
set @featuresCount = (select  \
							count(work_unit.id) \
						from \
							ppicha.work_unit, \
							ppicha.wu_type, \
							ppicha.wu_type_classification \
						where \
							work_unit.projectId = @projectId \
							and work_unit.wuTypeId = wu_type.id \
							and wu_type.classId = wu_type_classification.id \
							and wu_type_classification.class in ('FEATURE', 'ENHANCEMENT') \
						); \
set @bugsCount = (select \
						count(work_unit.id) \
					from  \
						ppicha.work_unit, \
						ppicha.wu_type, \
						ppicha.wu_type_classification \
					where \
						work_unit.projectId = @projectId \
						and work_unit.wuTypeId = wu_type.id \
						and wu_type.classId = wu_type_classification.id \
						and wu_type_classification.class = 'BUG' \
					); \
select \
	'month', 'iCreated', 'iClosed', 'iExisting', 'iOpen', 'bCreated', 'bClosed', 'bExisting', 'bOpen', 'fCreated', 'fClosed', 'fExisting', 'fOpen' \
    union \
select \
	* \
from \
	( \
	select \
		monthRange.selected_date as month, \
		ifnull(issuesCreated.createdCount, 0) as iCreated, \
		ifnull(issuesClosed.closedCount, 0) as iClosed, \
        @iExisting := @iExisting + ifnull(issuesCreated.createdCount, 0) as iExisting, \
        @iOpen := @iOpen + ifnull(issuesCreated.createdCount, 0) - ifnull(issuesClosed.closedCount, 0) as iOpen, \
        ifnull(bugsCreated.createdCount, 0) as bCreated, \
		ifnull(bugsClosed.closedCount, 0) as bClosed, \
        @bExisting := @bExisting + ifnull(bugsCreated.createdCount, 0) as bExisting, \
        @bOpen := @bOpen + ifnull(bugsCreated.createdCount, 0) - ifnull(bugsClosed.closedCount, 0) as bOpen, \
        ifnull(featuresCreated.createdCount, 0) as fCreated, \
		ifnull(featuresClosed.closedCount, 0) as fClosed, \
        @fExisting := @fExisting + ifnull(featuresCreated.createdCount, 0) as fExisting, \
        @fOpen := @fOpen + ifnull(featuresCreated.createdCount, 0) - ifnull(featuresClosed.closedCount, 0) as fOpen \
	from \
		(select  \
			@iExisting := 0, \
			@iOpen := 0, \
            @bExisting := 0, \
			@bOpen := 0, \
            @fExisting := 0, \
			@fOpen := 0 \
		) \
        runningVars, \
		( \
		select \
			* \
		from \
			( \
			select \
				date_format(adddate(@startDateFormatted, interval (t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) month), '%Y-%m') selected_date \
			from \
				(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, \
				(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, \
				(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, \
				(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3 \
			) \
			v \
		where \
			v.selected_date <= @endDateformatted \
		) \
		monthRange \
		left join \
		( \
		select \
			date_format(work_item.created, '%Y-%m') as closedOn, \
			count(distinct work_unit.id) as closedCount \
		from \
			ppicha.field_change, \
			ppicha.work_item_change, \
			ppicha.work_unit, \
			ppicha.configuration_change, \
			ppicha.work_item, \
			ppicha.status, \
			ppicha.project_instance, \
			ppicha.project, \
			ppicha.status_classification, \
			ppicha.wu_type, \
			ppicha.wu_type_classification \
		where \
			field_change.name = 'status' \
			and field_change.newValue = status.name \
			and status.projectInstanceId = project_instance.id \
			and project_instance.projectId = @projectId \
			and status.classId = status_classification.id \
			and status_classification.superClass = 'CLOSED' \
			and field_change.workItemChangeId = work_item_change.id \
			and work_item_change.workItemId = work_unit.id \
			and work_item_change.id = configuration_change.changeId \
			and configuration_change.configurationId = work_item.id \
			and work_unit.projectId = @projectId \
			and work_unit.wuTypeId = wu_type.id \
			and wu_type.classId = wu_type_classification.id \
			and wu_type_classification.class = 'BUG' \
		group by \
			closedOn \
		) \
		bugsClosed \
		on bugsClosed.closedOn = monthRange.selected_date \
        left join \
		( \
		select \
			date_format(work_item.created, '%Y-%m') as closedOn, \
			count(distinct work_unit.id) as closedCount \
		from \
			ppicha.field_change, \
			ppicha.work_item_change, \
			ppicha.work_unit, \
			ppicha.configuration_change, \
			ppicha.work_item, \
			ppicha.status, \
			ppicha.project_instance, \
			ppicha.project, \
			ppicha.status_classification, \
			ppicha.wu_type, \
			ppicha.wu_type_classification \
		where \
			field_change.name = 'status' \
			and field_change.newValue = status.name \
			and status.projectInstanceId = project_instance.id \
			and project_instance.projectId = @projectId \
			and status.classId = status_classification.id \
			and status_classification.superClass = 'CLOSED' \
			and field_change.workItemChangeId = work_item_change.id \
			and work_item_change.workItemId = work_unit.id \
			and work_item_change.id = configuration_change.changeId \
			and configuration_change.configurationId = work_item.id \
			and work_unit.projectId = @projectId \
			and work_unit.wuTypeId = wu_type.id \
			and wu_type.classId = wu_type_classification.id \
			and wu_type_classification.class in ('FEATURE', 'ENHANCEMENT') \
		group by \
			closedOn \
		) \
		featuresClosed \
		on featuresClosed.closedOn = monthRange.selected_date \
        left join \
		( \
		select \
			date_format(work_item.created, '%Y-%m') as closedOn, \
			count(distinct work_unit.id) as closedCount \
		from \
			ppicha.field_change, \
			ppicha.work_item_change, \
			ppicha.work_unit, \
			ppicha.configuration_change, \
			ppicha.work_item, \
			ppicha.status, \
			ppicha.project_instance, \
			ppicha.project, \
			ppicha.status_classification \
		where \
			field_change.name = 'status' \
			and field_change.newValue = status.name \
			and status.projectInstanceId = project_instance.id \
			and project_instance.projectId = @projectId \
			and status.classId = status_classification.id \
			and status_classification.superClass = 'CLOSED' \
			and field_change.workItemChangeId = work_item_change.id \
			and work_item_change.workItemId = work_unit.id \
			and work_item_change.id = configuration_change.changeId \
			and configuration_change.configurationId = work_item.id \
			and work_unit.projectId = @projectId \
		group by \
			closedOn \
		) \
		issuesClosed \
		on issuesClosed.closedOn = monthRange.selected_date \
		left join \
		( \
		select \
			date_format(work_item.created, '%Y-%m') as createdOn, \
			count(distinct work_unit.id) as createdCount \
		from \
			ppicha.work_unit, \
			ppicha.work_item, \
			ppicha.wu_type, \
			ppicha.wu_type_classification \
		where \
			work_unit.id = work_item.id \
			and work_unit.projectId = @projectId \
			and work_unit.wuTypeId = wu_type.id \
			and wu_type.classId = wu_type_classification.id \
			and wu_type_classification.class = 'BUG' \
		group by \
			date_format(work_item.created, '%Y-%m') \
		) \
		bugsCreated \
		on bugsCreated.createdOn = monthRange.selected_date \
		left join \
		( \
		select \
			date_format(work_item.created, '%Y-%m') as createdOn, \
			count(distinct work_unit.id) as createdCount \
		from \
			ppicha.work_unit, \
			ppicha.work_item, \
			ppicha.wu_type, \
			ppicha.wu_type_classification \
		where \
			work_unit.id = work_item.id \
			and work_unit.projectId = @projectId \
			and work_unit.wuTypeId = wu_type.id \
			and wu_type.classId = wu_type_classification.id \
			and wu_type_classification.class in ('FEATURE', 'ENHANCEMENT') \
		group by \
			date_format(work_item.created, '%Y-%m') \
		) \
		featuresCreated \
		on featuresCreated.createdOn = monthRange.selected_date \
        left join \
        ( \
		select \
			date_format(work_item.created, '%Y-%m') as createdOn, \
			count(distinct work_unit.id) as createdCount \
		from \
			ppicha.work_unit, \
			ppicha.work_item \
		where \
			work_unit.id = work_item.id \
			and work_unit.projectId = @projectId \
		group by \
			date_format(work_item.created, '%Y-%m') \
		) \
		issuesCreated \
		on issuesCreated.createdOn = monthRange.selected_date \
	order by \
		monthRange.selected_date \
	) \
	dataPoints