Accounting exports - Revision history

Ozerhouni: /* Statement serving as invoice with flight details */

2025-10-22T15:52:17Z

Statement serving as invoice with flight details

← Older revision		Revision as of 15:52, 22 October 2025
Line 564:		Line 564:
	*Variable '''$occupiedSeat''' represent the seat number of the person in the flight, can have following values (0: left place, 1: right place, -1: both).		*Variable '''$occupiedSeat''' represent the seat number of the person in the flight, can have following values (0: left place, 1: right place, -1: both).

	<syntaxhighlight lang="sql">SELECT		<syntaxhighlight lang="sql"> SELECT
	(		(
	SELECT CONCAT(last_name,' ',first_name)		SELECT CONCAT(last_name,' ',first_name)
	FROM person		FROM person
	LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id		LEFT JOIN flight_pilot ON flight_pilot.pilot_id = person.id
	WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=0		WHERE flight_pilot.flight_id = flight.id AND flight_pilot.num = 0
			) AS _tr(LEFT_PLACE),

	~~) AS _tr(LEFT_PLACE),~~
	(		(
	SELECT CONCAT(last_name,' ',first_name)		SELECT CONCAT(last_name,' ',first_name)
	FROM person		FROM person
	LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id		LEFT JOIN flight_pilot ON flight_pilot.pilot_id = person.id
	WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=1		WHERE flight_pilot.flight_id = flight.id AND flight_pilot.num = 1
			) AS _tr(RIGHT_PLACE),

	~~) AS _tr(RIGHT_PLACE),~~
	flight.start_date AS _tr(START_DATE),		flight.start_date AS _tr(START_DATE),
	resource.name AS _tr(RESOURCE),		resource.name AS _tr(RESOURCE),
	sexa2HoursMinute(flight.duration) AS _tr(DURATION),		sexa2HoursMinute(flight.duration) AS _tr(DURATION),

	(		(
	SELECT GROUP_CONCAT( activity_type.name SEPARATOR ', ')		SELECT GROUP_CONCAT(activity_type.name SEPARATOR ', ')
	FROM flight AS tmp_flight		FROM flight AS tmp_flight
	LEFT JOIN activity_type ON (tmp_flight.activity_type_id & activity_type.id)		LEFT JOIN activity_type ON (tmp_flight.activity_type_id & activity_type.id)
	WHERE tmp_flight.id=flight.id		WHERE tmp_flight.id = flight.id
	) AS _tr(ACTIVITY_TYPE),		) AS _tr(ACTIVITY_TYPE),

	flight.landing_number AS _tr(FLIGHT_LANDING_NUMBER),		flight.landing_number AS _tr(FLIGHT_LANDING_NUMBER),
	~~SUM(account_entry~~.~~debit)-SUM(account_entry.credit)~~ AS _tr(AMOUNT),		flight_amount.amount AS _tr(AMOUNT),
	business_field_content.content AS _tr(COMMENT)		business_field_content.content AS _tr(COMMENT)

	FROM flight		FROM flight
	LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id
			LEFT JOIN (
			SELECT
			flight_account_entry.flight_id,
			SUM(account_entry.debit) - SUM(account_entry.credit) AS amount
			FROM flight_account_entry
			JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
			JOIN account ON account.id = account_entry.account_id
			WHERE account.category IN (11, 2)
			GROUP BY flight_account_entry.flight_id
			) AS flight_amount ON flight_amount.flight_id = flight.id

			LEFT JOIN flight_pilot ON (
			flight_pilot.flight_id = flight.id
			AND (
			IF((($occupiedSeat = 0) OR ($occupiedSeat = '')), 0, -1) = flight_pilot.num
			OR IF((($occupiedSeat = 1) OR ($occupiedSeat = '')), 1, -1) = flight_pilot.num
			)
			)
	LEFT JOIN person ON person.id = flight_pilot.pilot_id		LEFT JOIN person ON person.id = flight_pilot.pilot_id
	~~LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id~~
	~~LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id~~
	~~LEFT JOIN account ON account.id=account_entry.account_id~~
	LEFT JOIN resource ON resource.id = flight.aircraft_id		LEFT JOIN resource ON resource.id = flight.aircraft_id
	LEFT JOIN business_field_content ON business_field_content.category_id=flight.id
	LEFT JOIN business_field ON (business_field.id=business_field_content.business_field_id AND business_field.variable='activityComment')		LEFT JOIN business_field_content
	WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate		ON business_field_content.category_id = flight.id
	~~AND (IF((($occupiedSeat=0)OR($occupiedSeat='')), 0, -1) = flight_pilot.num~~		LEFT JOIN business_field
	~~OR IF((($occupiedSeat=1)OR($occupiedSeat='')), 1, -1) = flight_pilot.num)~~		ON (business_field.id = business_field_content.business_field_id
			AND business_field.variable = 'activityComment')

			WHERE $startDate <= flight.start_date
			AND flight.start_date <= $endDate

	AND EXISTS (		AND EXISTS (
	SELECT 1		SELECT 1
	FROM profile		FROM profile
	WHERE person.profile & profile.id		WHERE person.profile & profile.id
	AND (profile.id IN ($profileId) OR '-' IN ($profileId))		AND (profile.id IN (IFNULL(NULLIF($profileId, ''), '0'))
			OR '-' IN (IFNULL(NULLIF($profileId, ''), '0')))
	)		)
	~~AND (account.category = 11 OR account.category = 2)~~
	GROUP BY flight.id		GROUP BY flight.id
	ORDER BY flight.start_date</syntaxhighlight>		ORDER BY flight.start_date;</syntaxhighlight>

	=Supplier bill=		=Supplier bill=

Ozerhouni: /* Statement serving as invoice with flight details */

2025-10-20T14:02:25Z

Statement serving as invoice with flight details

← Older revision		Revision as of 14:02, 20 October 2025
Line 591:		Line 591:
	SUM(account_entry.debit)-SUM(account_entry.credit) AS _tr(AMOUNT),		SUM(account_entry.debit)-SUM(account_entry.credit) AS _tr(AMOUNT),
	business_field_content.content AS _tr(COMMENT)		business_field_content.content AS _tr(COMMENT)
	FROM ~~person~~		FROM flight
	~~LEFT JOIN profile ON person.profile&profile.id~~		LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id
	LEFT JOIN flight_pilot ON flight_pilot.~~pilot_id~~=~~person~~.id		LEFT JOIN person ON person.id = flight_pilot.pilot_id
	LEFT JOIN ~~flight~~ ON ~~flight~~.id=flight_pilot.~~flight_id~~
	LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id		LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
	LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id		LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
Line 600:		Line 599:
	LEFT JOIN resource ON resource.id = flight.aircraft_id		LEFT JOIN resource ON resource.id = flight.aircraft_id
	LEFT JOIN business_field_content ON business_field_content.category_id=flight.id		LEFT JOIN business_field_content ON business_field_content.category_id=flight.id
	LEFT JOIN business_field ON (business_field.id=business_field_content.business_field_id)		LEFT JOIN business_field ON (business_field.id=business_field_content.business_field_id AND business_field.variable='activityComment')
	WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate		WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate
	AND (IF((($occupiedSeat=0)OR($occupiedSeat=-1)), 0, -1) = flight_pilot.num		AND (IF((($occupiedSeat=0)OR($occupiedSeat='')), 0, -1) = flight_pilot.num
	OR IF((($occupiedSeat=1)OR($occupiedSeat=-1)), 1, -1) = flight_pilot.num)		OR IF((($occupiedSeat=1)OR($occupiedSeat='')), 1, -1) = flight_pilot.num)
	AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)		AND EXISTS (
	~~AND business_field.variable='activityComment'~~		SELECT 1
			FROM profile
			WHERE person.profile & profile.id
			AND (profile.id IN ($profileId) OR '-' IN ($profileId))
			)
			AND (account.category = 11 OR account.category = 2)
	GROUP BY flight.id		GROUP BY flight.id
	ORDER BY flight.start_date</syntaxhighlight>		ORDER BY flight.start_date</syntaxhighlight>

Lelhidam: /* Statement serving as invoice with flight details */

2025-04-25T11:43:45Z

Statement serving as invoice with flight details

← Older revision		Revision as of 11:43, 25 April 2025
Line 600:		Line 600:
	LEFT JOIN resource ON resource.id = flight.aircraft_id		LEFT JOIN resource ON resource.id = flight.aircraft_id
	LEFT JOIN business_field_content ON business_field_content.category_id=flight.id		LEFT JOIN business_field_content ON business_field_content.category_id=flight.id
	LEFT JOIN business_field ON (business_field.id=business_field_content.business_field_id ~~AND business_field.variable='activityComment'~~)		LEFT JOIN business_field ON (business_field.id=business_field_content.business_field_id)
	WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate		WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate
	AND (IF((($occupiedSeat=0)OR($occupiedSeat=-1)), 0, -1) = flight_pilot.num		AND (IF((($occupiedSeat=0)OR($occupiedSeat=-1)), 0, -1) = flight_pilot.num
	OR IF((($occupiedSeat=1)OR($occupiedSeat=-1)), 1, -1) = flight_pilot.num)		OR IF((($occupiedSeat=1)OR($occupiedSeat=-1)), 1, -1) = flight_pilot.num)
	AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)		AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
			AND business_field.variable='activityComment'
	GROUP BY flight.id		GROUP BY flight.id
	ORDER BY flight.start_date</syntaxhighlight>		ORDER BY flight.start_date</syntaxhighlight>

Lelhidam: /* Statement serving as invoice */

2025-03-04T15:21:33Z

Statement serving as invoice

@@ Line 555: / Line 555: @@
 GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
 GROUP BY person.id
-UNION
+</syntaxhighlight>
 =Statement serving as invoice with flight details=

Lelhidam: /* Statement serving as invoice with flight details */

2025-02-21T12:48:38Z

Statement serving as invoice with flight details

← Older revision		Revision as of 12:48, 21 February 2025
Line 636:		Line 636:
	*Variable '''$endDate''' should be of '''Date and time''' value type.		*Variable '''$endDate''' should be of '''Date and time''' value type.
	*Variable '''$profileId''' should be of '''Integer''' value type.		*Variable '''$profileId''' should be of '''Integer''' value type.
	*Variable '''$occupiedSeat''' ~~should be~~ of ~~'''dbOjectMulti~~::~~Profile''' value type~~.		*Variable '''$occupiedSeat''' represent the seat number of the person in the flight, can have following values (0: left place, 1: right place, -1: both).

	<syntaxhighlight lang="sql">SELECT		<syntaxhighlight lang="sql">SELECT
Line 676:		Line 676:
	LEFT JOIN business_field ON (business_field.id=business_field_content.business_field_id AND business_field.variable='activityComment')		LEFT JOIN business_field ON (business_field.id=business_field_content.business_field_id AND business_field.variable='activityComment')
	WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate		WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate
	AND (IF((($occupiedSeat=0)OR($occupiedSeat='')), 0, -1) = flight_pilot.num		AND (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)
	AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)		AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
	GROUP BY flight.id		GROUP BY flight.id

Lelhidam: /* Statement serving as invoice */

2025-02-21T12:44:06Z

Statement serving as invoice

Claratte at 08:10, 9 October 2024

2024-10-09T08:10:26Z

Show changes

Claratte: Claratte moved page Accounting exports 4 to Accounting exports without leaving a redirect

2024-08-31T10:12:43Z

Claratte moved page Accounting exports 4 to Accounting exports without leaving a redirect

← Older revision

Revision as of 10:12, 31 August 2024

imported>Claratte: Text replacement - "" to ""

2024-08-12T18:53:04Z

Text replacement - "<sql>" to "<syntaxhighlight lang="sql">"

Show changes

← Older revision		Revision as of 12:44, 21 February 2025
Line 535:		Line 535:
	*Variable '''$endDate''' should be of '''Date and time''' value type.		*Variable '''$endDate''' should be of '''Date and time''' value type.
	*Variable '''$profileId''' should be of '''Integer''' value type.		*Variable '''$profileId''' should be of '''Integer''' value type.
	*Variable '''$occupiedSeat''' ~~should be~~ of ~~'''dbOjectMulti~~::~~Profile''' value type.~~		*Variable '''$occupiedSeat''' represent the seat number of the person in the flight, can have following values (0: left place, 1: right place, -1: both)

	<syntaxhighlight lang="sql">SELECT CONCAT(person.last_name,' ',person.first_name) AS _tr(FULL_NAME),		<syntaxhighlight lang="sql">SELECT CONCAT(person.last_name,' ',person.first_name) AS _tr(FULL_NAME),
Line 550:		Line 550:
	LEFT JOIN account ON account.id=account_entry.account_id		LEFT JOIN account ON account.id=account_entry.account_id
	WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate		WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate
	AND (IF((($occupiedSeat=0)OR($occupiedSeat='')), 0, -1) = flight_pilot.num		AND (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)
	AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)		AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
	GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id		GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
Line 570:		Line 570:
	LEFT JOIN account ON account.id=account_entry.account_id		LEFT JOIN account ON account.id=account_entry.account_id
	WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate		WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate
	AND (IF((($occupiedSeat=0)OR($occupiedSeat='')), 0, -1) = flight_pilot.num		AND (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)
	AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)		AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
	GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id		GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
Line 588:		Line 588:
	LEFT JOIN account ON account.id=account_entry.account_id		LEFT JOIN account ON account.id=account_entry.account_id
	WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate		WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate
	AND (IF((($occupiedSeat=0)OR($occupiedSeat='')), 0, -1) = flight_pilot.num		AND (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)
	AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)		AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
	GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id		GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
Line 606:		Line 606:
	LEFT JOIN account ON account.id=account_entry.account_id		LEFT JOIN account ON account.id=account_entry.account_id
	WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate		WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate
	AND (IF((($occupiedSeat=0)OR($occupiedSeat='')), 0, -1) = flight_pilot.num		AND (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)
	AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)		AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
	GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id		GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
Line 625:		Line 625:
	LEFT JOIN account ON account.id=account_entry.account_id		LEFT JOIN account ON account.id=account_entry.account_id
	WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate		WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate
	AND (IF((($occupiedSeat=0)OR($occupiedSeat='')), 0, -1) = flight_pilot.num		AND (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)
	AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)		AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
	GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id		GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id