Export generator 4 - Revision history

Claratte: /* Number of flights per instructor, month (for a flight type) */

2025-05-27T09:55:16Z

Number of flights per instructor, month (for a flight type)

Claratte: /* Number of flights per instructor, month (for a flight type) */

2025-05-27T09:54:32Z

Number of flights per instructor, month (for a flight type)

← Older revision		Revision as of 11:54, 27 May 2025
Line 1,246:		Line 1,246:
	==Number of flights per instructor, month (for a flight type)==		==Number of flights per instructor, month (for a flight type)==
	Following extrafields are needed :		Following extrafields are needed :
	*year (Type : Year)		*'''$year''' (Type : Year)
	*activityTypeId (Type : dbObject::FlightType)		*'''$activityTypeId''' (Type : dbObject::FlightType)
	<syntaxhighlight lang="sql">SELECT		<syntaxhighlight lang="sql">SELECT
	CONCAT(UPPER(person.last_name), ' ', person.first_name) AS _tr(LAST_NAME),		CONCAT(UPPER(person.last_name), ' ', person.first_name) AS _tr(LAST_NAME),

Claratte: /* Flight hours per activity type between date */

2025-05-27T09:53:20Z

Flight hours per activity type between date

@@ Line 1,243: / Line 1,243: @@
 FROM activity_type
 WHERE activity_type.activated=1</syntaxhighlight>
 =Flight reports=

Claratte: /* Flights with mechanic remark */

2025-04-14T10:36:44Z

Flights with mechanic remark

← Older revision		Revision as of 12:36, 14 April 2025
Line 1,462:		Line 1,462:

	==Flights with mechanic remark==		==Flights with mechanic remark==
	*See [[~~OF_doc4~~-fr:Suivi-des-remarques-de-maintenance\|Suivi des remarques de maintenance]]		*See [[OF_doc-fr:Suivi-des-remarques-de-maintenance\|Suivi des remarques de maintenance]]

	Following variables are needed:		Following variables are needed:

Claratte: /* Flights with mechanic remark */

2025-04-14T10:36:26Z

Flights with mechanic remark

← Older revision		Revision as of 12:36, 14 April 2025
Line 1,462:		Line 1,462:

	==Flights with mechanic remark==		==Flights with mechanic remark==
	*See [[~~OF_doc~~-fr:Suivi-des-remarques-de-maintenance\|Suivi des remarques de maintenance]]		*See [[OF_doc4-fr:Suivi-des-remarques-de-maintenance\|Suivi des remarques de maintenance]]

	Following variables are needed:		Following variables are needed:

Claratte: /* Flights with mechanic remark */

2025-04-14T10:35:40Z

Flights with mechanic remark

← Older revision		Revision as of 12:35, 14 April 2025
Line 1,462:		Line 1,462:

	==Flights with mechanic remark==		==Flights with mechanic remark==
			*See [[OF_doc-fr:Suivi-des-remarques-de-maintenance\|Suivi des remarques de maintenance]]

	Following variables are needed:		Following variables are needed:
	*Business field '''$maintenanceUserComment''' should be defined first and should be of '''Text multi line''' value type and '''Flight''' category.		*Business field '''$maintenanceUserComment''' should be defined first and should be of '''Text multi line''' value type and '''Flight''' category.

Claratte: /* Booking */

2025-04-10T07:22:01Z

Booking

← Older revision		Revision as of 09:22, 10 April 2025
Line 609:		Line 609:
	* Variable '''$resourceId''' of '''dbObject::Resource''' value type.		* Variable '''$resourceId''' of '''dbObject::Resource''' value type.

	<syntaxhighlight lang="sql">[OF_DYNAMIC_SQL]		<syntaxhighlight lang="sql">
			[OF_DYNAMIC_SQL]
	SELECT CONCAT(		SELECT CONCAT(
	'SELECT booking.id AS _tr(ID),		'SELECT booking.id AS _tr(ID),
Line 621:		Line 622:
	resource.name AS _tr(RESOURCE),		resource.name AS _tr(RESOURCE),
	left_booking_person.full_name AS _tr(LEFT_PLACE),		left_booking_person.full_name AS _tr(LEFT_PLACE),
	left_booking_person.email AS tr(EMAIL),		left_booking_person.email AS _tr(EMAIL),
	left_booking_person.cellPhone AS _tr(PHONE),		left_booking_person.cellPhone AS _tr(PHONE),
	left_booking_person.birthDate AS _tr(BIRTHDATE),		left_booking_person.birthDate AS _tr(BIRTHDATE),
Line 662:		Line 663:
	)		)
	FROM business_field		FROM business_field
	WHERE business_field.category='BOOKING' AND business_field.user_access_mode!=2</syntaxhighlight>		WHERE business_field.category='BOOKING' AND business_field.user_access_mode!=2
			</syntaxhighlight>

	==Cumulated maintenance hours on a period==		==Cumulated maintenance hours on a period==

Lelhidam: /* Cumulated maintenance hours on a period */

2025-02-24T14:24:09Z

Cumulated maintenance hours on a period

← Older revision		Revision as of 16:24, 24 February 2025
Line 670:		Line 670:
	*endDate (Type : Date or DateTime)		*endDate (Type : Date or DateTime)

	<syntaxhighlight lang="sql">SELECT IF(LEAST($startDate + INTERVAL 366 DAY, $endDate) <> $endDate, ~~' Attention la date de fin retenue correspond à la date de début + 366 jours'~~, '') AS ~~'Ressource'~~, '' AS ~~'Duree de maintenance en heures'~~, '' AS ~~'Duree en jours'~~		<syntaxhighlight lang="sql">SELECT IF(LEAST($startDate + INTERVAL 366 DAY, $endDate) <> $endDate,
			_tr(MAINTENANCE_SELECTED_END_DATE_ALERT) COLLATE utf8mb3_general_ci,
			'') AS _tr(RESOURCE),
			'' AS _tr(MAINTENANCE_DURATION_IN_HOURS),
			'' AS _tr(MAINTENANCE_DURATION_IN_DAYS)
	UNION ALL		UNION ALL
	SELECT resource.name ~~AS 'Ressource'~~, SUM(TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date)), SUM(TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date))		SELECT resource.name,
			SUM(TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date)),
			SUM(TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date))
	FROM booking		FROM booking
	LEFT JOIN booking_activity_type ON booking.id=booking_activity_type.booking_id		LEFT JOIN booking_activity_type ON booking.id=booking_activity_type.booking_id

Lelhidam: /* Maintenance hours per resource on a period */

2025-02-24T14:22:14Z

Maintenance hours per resource on a period

← Older revision		Revision as of 16:22, 24 February 2025
Line 695:		Line 695:
	*endDate (Type : Date or DateTime)		*endDate (Type : Date or DateTime)

	<syntaxhighlight lang="sql">SELECT IF(LEAST($startDate + INTERVAL 366 DAY, $endDate) <> $endDate, ~~' Attention la date de fin retenue correspond à la date de début + 366 jours'~~, '') AS ~~'Ressource'~~, '' AS ~~'Date debut maintenance'~~, '' AS ~~'Date fin maintenance'~~, '' AS ~~'Duree de maintenance en heures'~~, '' AS ~~'Duree en jours'~~		<syntaxhighlight lang="sql">SELECT IF(LEAST($startDate + INTERVAL 366 DAY, $endDate) <> $endDate,
			_tr(MAINTENANCE_SELECTED_END_DATE_ALERT) COLLATE utf8mb3_general_ci,
			'') AS _tr(RESOURCE),
			'' AS _tr(MAINTENANCE_START_DATE),
			'' AS _tr(MAINTENANCE_END_DATE),
			'' AS _tr(MAINTENANCE_DURATION_IN_HOURS),
			'' AS _tr(MAINTENANCE_DURATION_IN_DAYS)
	UNION ALL		UNION ALL
	SELECT resource.name, booking.start_date ~~AS 'Date debut maintenance'~~, booking.end_date, TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date), TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date)		SELECT resource.name,
			booking.start_date,
			booking.end_date,
			TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date),
			TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date)
	FROM booking		FROM booking
	LEFT JOIN booking_activity_type ON booking.id=booking_activity_type.booking_id		LEFT JOIN booking_activity_type ON booking.id=booking_activity_type.booking_id

Lelhidam: /* Test valid entries */

2025-02-21T12:36:17Z

Test valid entries

← Older revision		Revision as of 14:36, 21 February 2025
Line 72:		Line 72:
	For business fields that are text inputs, any entry can be submitted and added to SQL query. In order to test valid entries, it is recommended to use the '''IF''' statement in the '''WHERE''' statement.		For business fields that are text inputs, any entry can be submitted and added to SQL query. In order to test valid entries, it is recommended to use the '''IF''' statement in the '''WHERE''' statement.

	Per example, $occupiedSeat can have following values (0, 1 or ~~NULL~~) :		Per example, $occupiedSeat can have following values (0, 1 or -1) :

	<syntaxhighlight lang="sql">SELECT *		<syntaxhighlight lang="sql">SELECT *
Line 78:		Line 78:
	WHERE		WHERE
	(		(
	IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num		IF((($occupiedSeat=0)OR($occupiedSeat=-1)), 0, -1) = flight_pilot.num
	OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num		OR IF((($occupiedSeat=1)OR($occupiedSeat=-1)), 1, -1) = flight_pilot.num
	)</syntaxhighlight>		)</syntaxhighlight>

← Older revision		Revision as of 11:55, 27 May 2025
Line 1,250:		Line 1,250:
	<syntaxhighlight lang="sql">SELECT		<syntaxhighlight lang="sql">SELECT
	CONCAT(UPPER(person.last_name), ' ', person.first_name) AS _tr(LAST_NAME),		CONCAT(UPPER(person.last_name), ' ', person.first_name) AS _tr(LAST_NAME),
	(SELECT ~~sexa2HoursMinute~~( ~~SUM( duration )~~ ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_JANUARY),		(SELECT COUNT(*) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_JANUARY),
	(SELECT ~~sexa2HoursMinute( SUM~~( ~~duration )~~ ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_FEBRUARY),		(SELECT COUNT(*) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_FEBRUARY),
	(SELECT ~~sexa2HoursMinute~~( ~~SUM( duration )~~ ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_MARCH),		(SELECT COUNT(*) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_MARCH),
	(SELECT ~~sexa2HoursMinute~~( ~~SUM( duration )~~ ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_APRIL),		(SELECT COUNT(*) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_APRIL),
	(SELECT ~~sexa2HoursMinute~~( ~~SUM( duration )~~ ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_MAY),		(SELECT COUNT(*) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_MAY),
	(SELECT ~~sexa2HoursMinute( SUM~~( ~~duration )~~ ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_JUN),		(SELECT COUNT(*) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_JUN),
	(SELECT ~~sexa2HoursMinute( SUM~~( ~~duration )~~ ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_JULY),		(SELECT COUNT(*) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_JULY),
	(SELECT ~~sexa2HoursMinute( SUM~~( ~~duration )~~ ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_AUGUST),		(SELECT COUNT(*) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_AUGUST),
	(SELECT ~~sexa2HoursMinute~~( ~~SUM( duration )~~ ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_SEPTEMBER),		(SELECT COUNT(*) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_SEPTEMBER),
	(SELECT ~~sexa2HoursMinute~~( ~~SUM( duration )~~ ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_OCTOBER),		(SELECT COUNT(*) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_OCTOBER),
	(SELECT ~~sexa2HoursMinute~~( ~~SUM( duration )~~ ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_NOVEMBER),		(SELECT COUNT(*) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_NOVEMBER),
	(SELECT ~~sexa2HoursMinute( SUM~~( ~~duration )~~ ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_DECEMBER),		(SELECT COUNT(*) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_DECEMBER),
	~~sexa2HoursMinute~~( ~~SUM( duration )~~ ) AS _tr(TOTAL)		COUNT(*) AS _tr(TOTAL)
	FROM flight		FROM flight
	LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id		LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id