Difference between revisions of "Export generator 4"
(→Flight hours per instructor, month (for a flight type)) |
(→Flight hours per activity type between date) |
||
(37 intermediate revisions by 3 users not shown) | |||
Line 92: | Line 92: | ||
WHERE YEAR(flight.start_date)=$year | WHERE YEAR(flight.start_date)=$year | ||
AND flight.airborne=0 | AND flight.airborne=0 | ||
− | AND ( activity_type.id IN ('$activityTypeId') OR ' | + | AND ( activity_type.id IN ('$activityTypeId') OR '' IN ('$activityTypeId') ) |
) AS flightWithActivityType | ) AS flightWithActivityType | ||
LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=1) | LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=1) | ||
Line 99: | Line 99: | ||
FROM person | FROM person | ||
LEFT JOIN profile ON person.profile&profile.id | LEFT JOIN profile ON person.profile&profile.id | ||
− | WHERE ( profile.id IN ($profileId) OR ' | + | WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) ) |
AND person.activated=1 | AND person.activated=1 | ||
GROUP BY person.id | GROUP BY person.id | ||
Line 133: | Line 133: | ||
WHERE YEAR(flight.start_date)=$year | WHERE YEAR(flight.start_date)=$year | ||
AND flight.airborne=0 | AND flight.airborne=0 | ||
− | AND ( activity_type.id IN ('$activityTypeId') OR ' | + | AND ( activity_type.id IN ('$activityTypeId') OR '' IN ('$activityTypeId') ) |
AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1) | AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1) | ||
) AS flightWithActivityType | ) AS flightWithActivityType | ||
Line 141: | Line 141: | ||
FROM person | FROM person | ||
LEFT JOIN profile ON person.profile&profile.id | LEFT JOIN profile ON person.profile&profile.id | ||
− | WHERE ( profile.id IN ($profileId) OR ' | + | WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) ) |
AND person.activated=1 | AND person.activated=1 | ||
GROUP BY person.id | GROUP BY person.id | ||
Line 167: | Line 167: | ||
<sql>SELECT SUM(movement) AS 'Movement' | <sql>SELECT SUM(movement) AS 'Movement' | ||
− | FROM | + | FROM ( |
− | ( | + | |
SELECT COUNT(*) AS movement | SELECT COUNT(*) AS movement | ||
− | FROM flight | + | FROM flight, structure |
− | + | WHERE ( ( flight.departure_icao_id != structure.icao ) OR( flight.arrival_icao_id != structure.icao ) ) AND( flight.departure_icao_id != structure.icao ) AND YEAR(start_date) = $year | |
− | WHERE ( | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
UNION | UNION | ||
− | SELECT SUM(landing_number)*2 AS movement | + | SELECT SUM(landing_number) * 2 AS movement |
− | FROM flight | + | FROM flight, structure |
− | + | WHERE YEAR(flight.start_date) = $year AND flight.departure_icao_id = structure.icao AND flight.arrival_icao_id = structure.icao | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
) AS tmp_movement</sql> | ) AS tmp_movement</sql> | ||
Line 199: | Line 183: | ||
<sql>SELECT SUM(movement) AS 'Movement' | <sql>SELECT SUM(movement) AS 'Movement' | ||
− | FROM ( | + | FROM ( |
− | + | SELECT COUNT(*) AS movement | |
− | + | FROM structure, flight | |
− | + | LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id) | |
− | + | LEFT JOIN ( | |
− | + | SELECT person.* | |
− | + | FROM person | |
− | + | LEFT JOIN profile ON person.profile&profile.id | |
− | + | WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) ) | |
− | + | AND person.activated=1 | |
− | + | GROUP BY person.id | |
− | + | ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id AND flight_pilot.num=0) | |
− | + | WHERE ( | |
− | + | ( flight.departure_icao_id != structure.icao) | |
− | + | OR | |
− | + | ( flight.arrival_icao_id != structure.icao) | |
− | + | ) | |
− | + | AND ( flight.departure_icao_id != structure.icao) | |
− | + | AND YEAR(flight.start_date) = 2019 | |
− | + | AND personWithProfile.id IS NOT NULL | |
− | + | UNION | |
− | + | SELECT SUM(flight.landing_number)*2 AS movement | |
− | + | FROM structure, flight | |
− | + | LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id) | |
− | + | LEFT JOIN ( | |
− | + | SELECT person.* | |
− | + | FROM person | |
− | + | LEFT JOIN profile ON person.profile&profile.id | |
− | + | WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) ) | |
− | + | AND person.activated=1 | |
− | + | GROUP BY person.id | |
− | + | ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id AND flight_pilot.num=0) | |
− | + | WHERE YEAR(flight.start_date) = $year | |
− | + | AND flight.departure_icao_id = structure.icao | |
− | + | AND flight.arrival_icao_id = structure.icao | |
− | + | AND personWithProfile.id IS NOT NULL | |
− | + | ) AS tmp_movement</sql> | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
==Number of take-off and landings on based airfield== | ==Number of take-off and landings on based airfield== | ||
− | <SQL>SELECT SUM( landing_number )*2 AS nb_mouvement | + | <SQL>SELECT SUM(flight.landing_number) * 2 AS nb_mouvement |
− | FROM flight | + | FROM flight, structure |
− | WHERE ( | + | WHERE ( flight.departure_icao_id = structure.icao ) AND ( flight.arrival_icao_id = structure.icao ) AND YEAR(start_date) = $year </SQL> |
− | + | ||
− | + | ||
==Number of take-off and landings on based airfield on specific profile== | ==Number of take-off and landings on based airfield on specific profile== | ||
Line 271: | Line 232: | ||
* Variable '''$year''' should be defined first and should be of '''Year''' value type. | * Variable '''$year''' should be defined first and should be of '''Year''' value type. | ||
− | <sql>SELECT SUM(landing_number)*2 AS 'Movement' | + | <sql>SELECT SUM(flight.landing_number) * 2 AS 'Movement' |
− | FROM | + | FROM structure, flight |
− | + | LEFT JOIN flight_pilot ON ( flight_pilot.flight_id = flight.id ) | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id) | + | |
LEFT JOIN ( | LEFT JOIN ( | ||
SELECT person.* | SELECT person.* | ||
FROM person | FROM person | ||
− | LEFT JOIN profile ON person.profile&profile.id | + | LEFT JOIN profile ON person.profile & profile.id |
− | WHERE ( profile.id IN ($profileId) OR '-' IN ($profileId) ) | + | WHERE (profile.id IN($profileId) OR '-' IN($profileId)) AND person.activated = 1 |
− | + | ||
GROUP BY person.id | GROUP BY person.id | ||
− | ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id AND flight_pilot.num=0) | + | ) AS personWithProfile ON ( personWithProfile.id = flight_pilot.pilot_id AND flight_pilot.num = 0 ) |
− | WHERE personWithProfile.id IS NOT NULL | + | WHERE personWithProfile.id IS NOT NULL AND YEAR(flight.start_date) = $year AND flight.departure_icao_id = structure.icao AND flight.arrival_icao_id = structure.icao</sql> |
− | + | ||
− | + | ||
==List of movements on based airfield== | ==List of movements on based airfield== | ||
− | <sql>SELECT | + | <sql>SELECT location.icao_name AS ICAO, location.name AS NAME, COUNT(location.icao_name) AS nb_flight |
− | FROM flight | + | FROM structure, flight |
− | LEFT JOIN location | + | LEFT JOIN location ON flight.departure_icao_id = location.icao_name |
− | WHERE ( | + | WHERE ( flight.departure_icao_id = structure.icao ) OR( flight.arrival_icao_id = structure.icao ) AND departure_icao_id != arrival_icao_id AND YEAR(start_date) = $year |
− | + | ||
− | + | ||
− | + | ||
GROUP BY icao_name | GROUP BY icao_name | ||
− | ORDER BY nb_flight DESC</sql> | + | ORDER BY nb_flight DESC </sql> |
− | == | + | ==User flying without validity== |
− | <sql>SELECT CONCAT(person.last_name, ' ', person.first_name) AS | + | <sql>SELECT |
+ | CONCAT(person.last_name, ' ', person.first_name) AS '_tr(PILOT)', | ||
+ | validity_type.name AS '_tr(VALIDITY)' | ||
FROM flight_type_mandatory_validity_type | FROM flight_type_mandatory_validity_type | ||
LEFT JOIN activity_type ON activity_type.id = flight_type_mandatory_validity_type.activity_type_id | LEFT JOIN activity_type ON activity_type.id = flight_type_mandatory_validity_type.activity_type_id | ||
Line 316: | Line 266: | ||
LEFT JOIN person ON person.id = flight_pilot.pilot_id | LEFT JOIN person ON person.id = flight_pilot.pilot_id | ||
LEFT JOIN validity_type ON (validity_type.id = flight_type_mandatory_validity_type.validity_type_id OR validity_type.id = aircraft_type_validity_type.validity_type_id) | LEFT JOIN validity_type ON (validity_type.id = flight_type_mandatory_validity_type.validity_type_id OR validity_type.id = aircraft_type_validity_type.validity_type_id) | ||
− | LEFT JOIN validity ON | + | LEFT JOIN validity ON validity_type.id = validity.validity_type_id AND person.id = validity.person_id AND validity.is_current_validity = 1 |
WHERE flight.airborne = 0 | WHERE flight.airborne = 0 | ||
AND validity_type.id IS NOT NULL | AND validity_type.id IS NOT NULL | ||
AND validity_type.experience_formula IS NULL | AND validity_type.experience_formula IS NULL | ||
AND validity.validity_type_id IS NULL | AND validity.validity_type_id IS NULL | ||
− | AND person.activated=1 | + | AND person.activated = 1 |
+ | AND validity_type.activated = 1 | ||
GROUP BY person.id, validity_type.id | GROUP BY person.id, validity_type.id | ||
− | ORDER BY | + | ORDER BY `_tr(PILOT)`, validity_type.name</sql> |
==List of pilots who have flown less than X hours during last Y days== | ==List of pilots who have flown less than X hours during last Y days== | ||
Line 378: | Line 329: | ||
ORDER BY person.last_name, person.first_name</sql> | ORDER BY person.last_name, person.first_name</sql> | ||
− | ==Number of men, women over and under X years for profile P, validity V up to date for year A== | + | ==Total user per age profile validity: Number of men, women over and under X years for profile P, validity V up to date for year A== |
* Variable '''$age''' should be of '''integer''' value type. | * Variable '''$age''' should be of '''integer''' value type. | ||
* Variable '''$profileId''' should be of '''dbOjectMulti::Profile''' value type. | * Variable '''$profileId''' should be of '''dbOjectMulti::Profile''' value type. | ||
Line 385: | Line 336: | ||
<sql>SELECT | <sql>SELECT | ||
− | IF( personWithProfile.sex=0, ' | + | IF( personWithProfile.sex=0, '_tr(SEX_MALE_INITIAL)', IF( personWithProfile.sex=1, '_tr(SEX_FEMALE_INITIAL)', '_tr(UNDEFINED)') ) AS '_tr(SEX)', |
− | IF( ( $year - YEAR(birthdate) >= '$age' ) , ' | + | IF( ( $year - YEAR(birthdate) >= '$age' ) , '_tr(YES)', '_tr(NO)') AS '_tr(ADULT)', |
− | COUNT(DISTINCT personWithProfile.id) AS NUMBER | + | COUNT(DISTINCT personWithProfile.id) AS '_tr(NUMBER)' |
FROM ( | FROM ( | ||
SELECT person.* | SELECT person.* | ||
Line 395: | Line 346: | ||
AND person.activated=1 | AND person.activated=1 | ||
) AS personWithProfile | ) AS personWithProfile | ||
− | LEFT JOIN validity ON | + | LEFT JOIN validity ON personWithProfile.id = validity.person_id AND validity.is_current_validity = 1 |
LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id) | LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id) | ||
− | WHERE (validity_type.id IN ($validityTypeId) AND validity.expire_date >= '$year-12-31') | + | WHERE ((validity_type.id IN ($validityTypeId) AND validity.expire_date >= '$year-12-31') OR '-' IN ($validityTypeId)) |
− | + | AND validity_type.activated = 1 | |
− | GROUP BY | + | GROUP BY `_tr(SEX)`, `_tr(ADULT)`</sql> |
==Number of landings per pilot, per resource== | ==Number of landings per pilot, per resource== | ||
Line 478: | Line 429: | ||
==Flight hours without up to date validities== | ==Flight hours without up to date validities== | ||
− | <sql>SELECT DATE_FORMAT(start_date, '%d %m %Y' ) AS '_tr(DATE)', last_name | + | |
− | + | *Variable '''$year''' | |
− | + | ||
− | + | <sql>SELECT | |
− | + | DATE_FORMAT(start_date, '%d %m %Y' ) AS '_tr(DATE)', | |
− | + | CONCAT(person.last_name, ' ', person.first_name) AS '_tr(FULL_NAME)', | |
+ | IF ( (SELECT COUNT(*) FROM flight_pilot AS tmp_flight_pilot WHERE flight_pilot.flight_id=tmp_flight_pilot.flight_id AND tmp_flight_pilot.num=1)=0, 'Solo', 'DC' ) AS DC, | ||
+ | resource.name AS '_tr(RESOURCE)', | ||
+ | sexa2HoursMinute(duration) AS '_tr(DURATION)', | ||
+ | activity_type.name AS '_tr(ACTIVITY_TYPE)', | ||
+ | validity_type.name AS '_tr(VALIDITY)' | ||
FROM flight | FROM flight | ||
LEFT JOIN flight_type_mandatory_validity_type ON flight_type_mandatory_validity_type.activity_type_id & flight.activity_type_id | LEFT JOIN flight_type_mandatory_validity_type ON flight_type_mandatory_validity_type.activity_type_id & flight.activity_type_id | ||
Line 489: | Line 445: | ||
LEFT JOIN person ON flight_pilot.pilot_id = person.id | LEFT JOIN person ON flight_pilot.pilot_id = person.id | ||
LEFT JOIN validity_type ON flight_type_mandatory_validity_type.validity_type_id = validity_type.id | LEFT JOIN validity_type ON flight_type_mandatory_validity_type.validity_type_id = validity_type.id | ||
− | LEFT JOIN validity ON flight_type_mandatory_validity_type.validity_type_id = validity.validity_type_id | + | LEFT JOIN validity ON flight_type_mandatory_validity_type.validity_type_id = validity.validity_type_id AND validity.is_current_validity = 1 |
LEFT JOIN resource ON flight.aircraft_id = resource.id | LEFT JOIN resource ON flight.aircraft_id = resource.id | ||
LEFT JOIN activity_type ON flight_type_mandatory_validity_type.activity_type_id = activity_type.id | LEFT JOIN activity_type ON flight_type_mandatory_validity_type.activity_type_id = activity_type.id | ||
Line 497: | Line 453: | ||
AND validity.person_id = flight_pilot.pilot_id | AND validity.person_id = flight_pilot.pilot_id | ||
AND flight.start_date > validity.expire_date | AND flight.start_date > validity.expire_date | ||
− | AND person.activated=1 | + | AND person.activated = 1 |
− | ORDER BY flight.start_date, | + | AND validity_type.activated = 1 |
+ | ORDER BY flight.start_date, `_tr(FULL_NAME)`, validity_type.name</sql> | ||
==Number of men, women over and under 21 years for profile X, validity Y up to date for year Z== | ==Number of men, women over and under 21 years for profile X, validity Y up to date for year Z== | ||
Line 551: | Line 508: | ||
GROUP BY tmp_flight.activity_type_id</sql> | GROUP BY tmp_flight.activity_type_id</sql> | ||
− | == | + | ==Validity year young specific profile: Total of youngs/adults with specific profile== |
* Variable '''$profileId''' should be defined first and should be of '''dbOjectMulti::Profile''' value type. | * Variable '''$profileId''' should be defined first and should be of '''dbOjectMulti::Profile''' value type. | ||
Line 557: | Line 514: | ||
* Note: "21" is the limit of age to categorize a young and adult person. Change it according to your need. | * Note: "21" is the limit of age to categorize a young and adult person. Change it according to your need. | ||
− | <sql>SELECT validity_type.name AS ' | + | <sql>SELECT |
− | + | validity_type.name AS '_tr(VALIDITY)', | |
− | + | SUM( IF( ( $year - YEAR(personWithProfile.birthdate) < 21 ), 1, 0 ) ) AS '_tr(YOUNG)', | |
+ | SUM( IF( ( $year - YEAR(personWithProfile.birthdate) >= 21 ), 1, 0 ) ) AS '_tr(ADULT)' | ||
FROM validity_type | FROM validity_type | ||
− | LEFT JOIN validity ON | + | LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1 |
LEFT JOIN ( | LEFT JOIN ( | ||
SELECT person.* | SELECT person.* | ||
Line 571: | Line 529: | ||
) AS personWithProfile ON (validity.person_id=personWithProfile.id) | ) AS personWithProfile ON (validity.person_id=personWithProfile.id) | ||
WHERE YEAR(validity.grant_date)=$year | WHERE YEAR(validity.grant_date)=$year | ||
− | + | AND personWithProfile.id IS NOT NULL | |
+ | AND validity_type.activated = 1 | ||
GROUP BY validity_type.id | GROUP BY validity_type.id | ||
ORDER BY validity_type.name</sql> | ORDER BY validity_type.name</sql> | ||
Line 591: | Line 550: | ||
AND person.activated=1 | AND person.activated=1 | ||
ORDER BY last_name, first_name</sql> | ORDER BY last_name, first_name</sql> | ||
+ | |||
+ | ==Visited airfields== | ||
+ | |||
+ | <sql>SELECT location.icao_name AS ICAO, location.name AS NAME, COUNT(location.icao_name) AS Visit | ||
+ | FROM structure, flight | ||
+ | LEFT JOIN location ON ( flight.departure_icao_id = location.icao_name ) | ||
+ | WHERE ( ( flight.departure_icao_id != structure.icao ) OR ( flight.arrival_icao_id != structure.icao ) ) AND YEAR(start_date) = $year | ||
+ | GROUP BY location.icao_name | ||
+ | ORDER BY Visit | ||
+ | DESC</sql> | ||
+ | |||
+ | ==Visited outsider airfield== | ||
+ | |||
+ | <sql>SELECT location.icao_name AS ICAO, location.name AS Name, COUNT(location.icao_name) AS nb_visite | ||
+ | FROM structure, flight | ||
+ | LEFT JOIN location ON ( flight.departure_icao_id = location.icao_name ) | ||
+ | WHERE ( ( flight.departure_icao_id !=structure.icao ) OR( flight.arrival_icao_id !=structure.icao ) ) AND YEAR(start_date) = $year | ||
+ | GROUP BY location.icao_name | ||
+ | ORDER BY nb_visite DESC</sql> | ||
=[[Accounting-exports-4|Accounting]]= | =[[Accounting-exports-4|Accounting]]= | ||
Line 609: | Line 587: | ||
' (SELECT business_field_content.content FROM business_field_content WHERE booking.id=business_field_content.category_id AND business_field_content.business_field_id=', | ' (SELECT business_field_content.content FROM business_field_content WHERE booking.id=business_field_content.category_id AND business_field_content.business_field_id=', | ||
business_field.id, | business_field.id, | ||
− | ') AS \'', | + | ' LIMIT 1) AS \'', |
REPLACE(business_field.label, '\'', '\\\''), | REPLACE(business_field.label, '\'', '\\\''), | ||
'\'' | '\'' | ||
Line 632: | Line 610: | ||
) AS right_booking_person ON (booking.id=right_booking_person.booking_id) | ) AS right_booking_person ON (booking.id=right_booking_person.booking_id) | ||
WHERE booking.start_date >= \'$startDate\' AND booking.end_date < \'$endDate\' | WHERE booking.start_date >= \'$startDate\' AND booking.end_date < \'$endDate\' | ||
− | AND (booking_resource.resource_id=\'$resourceId\' OR \' | + | AND (booking_resource.resource_id=\'$resourceId\' OR \'\'=\'$resourceId\') |
AND ( | AND ( | ||
− | left_booking_person.person_id=\'$personId\' OR right_booking_person.person_id=\'$personId\' OR \' | + | left_booking_person.person_id=\'$personId\' OR right_booking_person.person_id=\'$personId\' OR \'\'=\'$personId\' |
) | ) | ||
ORDER BY booking.start_date, booking.end_date' | ORDER BY booking.start_date, booking.end_date' | ||
Line 789: | Line 767: | ||
LEFT JOIN resource ON resource.id = aircraft.id | LEFT JOIN resource ON resource.id = aircraft.id < |