time_entries

time_entries.sql

SELECT
DATE_FORMAT(te.spent_on, '%d.%m.%y') AS Datum,
"" AS Von,
"" AS Bis,
REPLACE(FORMAT(te.hours,2),'.',',') AS Menge,
COALESCE	(
			NULLIF(cfv.value,''),
			NULLIF(cfv2.value,''),
			NULLIF(cfv3.value,'')
			)
			AS Projekt,
SUBSTRING_INDEX(e.name,' ', 1) AS Taetigkeit,
CASE te.comments
	WHEN "" THEN
		CONCAT('#', CAST(te.issue_id AS CHAR), ' - ', CAST(IFNULL(i.estimated_hours,0) AS CHAR), ' - ', i.subject)
	WHEN NULL THEN
		CONCAT('#', CAST(te.issue_id AS CHAR), ' - ', CAST(IFNULL(i.estimated_hours,0) AS CHAR), ' - ', i.subject)
	ELSE
		CONCAT('#', CAST(te.issue_id AS CHAR), ' - ', CAST(IFNULL(i.estimated_hours,0) AS CHAR), ' - ', te.comments)
END AS Beschreibung,
"" AS Anmerkung,
"" AS Uebernahme,
u.login AS user_login,
te.issue_id AS Ticket,
i.subject AS TicketSubject,
i.estimated_hours AS TicketEstimatedHours,
cfv.value AS TicketJobNummer,
cfv2.value AS VersionJobNummer,
cfv3.value AS ProjectJobNummer,
te.id AS time_entry_id,
te.comments AS time_entry_comments,
CONCAT('#', CAST(te.issue_id AS CHAR), ' - ', CAST(IFNULL(i.estimated_hours,0) AS CHAR), ' - ', CAST(te.comments AS CHAR)) AS time_entry_comments_sanitized,
te.user_id AS time_entry_user_id,
u.firstname AS user_firstname,
u.lastname AS user_lastname



FROM (
	`time_entries` AS te
		LEFT JOIN `users` AS u
			ON (
				u.id = te.user_id
			)
		LEFT JOIN `enumerations` AS e
			ON (
				e.id = te.activity_id
			)
		LEFT JOIN `issues` AS i
			ON (
				i.id = te.issue_id
			)
		LEFT JOIN `custom_values` AS cfv
			ON (
				cfv.customized_type='Issue' AND cfv.custom_field_id=1 AND cfv.customized_id=te.issue_id # find tickets jobnumber
			)
		LEFT JOIN `custom_values` AS cfv2
			ON (
				cfv2.customized_type='Version' AND cfv2.custom_field_id=11 AND cfv2.customized_id=i.fixed_version_id # find versions jobnumber
			)
		LEFT JOIN `custom_values` AS cfv3
			ON (
				cfv3.customized_type='Project' AND cfv3.custom_field_id=7 AND cfv3.customized_id=i.project_id # find projects jobnumber
			)
	)


WHERE te.user_id = 22 AND te.spent_on >= "2011-01-01 00:00:00" AND te.spent_on < "2012-01-01 00:00:00"
ORDER BY te.spent_on ASC

time_entries_into_outfile.sql

SET NAMES utf8;
SELECT
DATE_FORMAT(te.spent_on, '%d.%m.%y') AS Datum,
"" AS Von,
"" AS Bis,
REPLACE(FORMAT(te.hours,2),'.',',') AS Menge,
COALESCE  (
			NULLIF(cfv.value,''),
			NULLIF(cfv2.value,''),
			NULLIF(cfv3.value,'')
			)
			AS Projekt,
SUBSTRING_INDEX(e.name,' ', 1) AS Taetigkeit,
CASE te.comments
	WHEN "" THEN
		CONCAT('#', CAST(te.issue_id AS CHAR), ' - ', CAST(IFNULL(i.estimated_hours,0) AS CHAR), ' - ', i.subject)
	WHEN NULL THEN
		CONCAT('#', CAST(te.issue_id AS CHAR), ' - ', CAST(IFNULL(i.estimated_hours,0) AS CHAR), ' - ', i.subject)
	ELSE
		CONCAT('#', CAST(te.issue_id AS CHAR), ' - ', CAST(IFNULL(i.estimated_hours,0) AS CHAR), ' - ', te.comments)
END AS Beschreibung,
"" AS Anmerkung,
"" AS Uebernahme,
u.login AS user_login,
te.issue_id AS Ticket,
i.subject AS TicketSubject,
i.estimated_hours AS TicketEstimatedHours,
cfv.value AS TicketJobNummer,
cfv2.value AS VersionJobNummer,
cfv3.value AS ProjectJobNummer,
te.id AS time_entry_id,
te.comments AS time_entry_comments,
CONCAT('#', CAST(te.issue_id AS CHAR), ' - ', CAST(IFNULL(i.estimated_hours,0) AS CHAR), ' - ', CAST(te.comments AS CHAR)) AS time_entry_comments_sanitized,
te.user_id AS time_entry_user_id,
u.firstname AS user_firstname,
u.lastname AS user_lastname

INTO OUTFILE '/path/to/file.csv'
  FIELDS TERMINATED BY ';'
  OPTIONALLY ENCLOSED BY '"'
  ESCAPED BY '\'
  LINES TERMINATED BY '\n'

FROM (
	`time_entries` AS te
		LEFT JOIN `users` AS u
			ON (
				u.id = te.user_id
			)
		LEFT JOIN `enumerations` AS e
			ON (
				e.id = te.activity_id
			)
		LEFT JOIN `issues` AS i
			ON (
				i.id = te.issue_id
			)
		LEFT JOIN `custom_values` AS cfv
			ON (
				cfv.customized_type='Issue' AND cfv.custom_field_id=1 AND cfv.customized_id=te.issue_id # find tickets jobnumber
			)
		LEFT JOIN `custom_values` AS cfv2
			ON (
				cfv2.customized_type='Version' AND cfv2.custom_field_id=11 AND cfv2.customized_id=i.fixed_version_id # find versions jobnumber
			)
		LEFT JOIN `custom_values` AS cfv3
			ON (
				cfv3.customized_type='Project' AND cfv3.custom_field_id=7 AND cfv3.customized_id=i.project_id # find projects jobnumber
			)
	)


WHERE te.user_id = 22 AND te.spent_on >= "2011-01-01 00:00:00" AND te.spent_on < "2012-01-01 00:00:00"
ORDER BY te.spent_on ASC;