Users reports 3
Personal details (with profile X and/or validity Y expiring at a date posterior or equal to Z)
- Variable $date should be defined first and should be of Date value type.
- Variable $profileId should be defined first and should be of dbObject::Profile value type.
- Variable $validityTypeId should be defined first and should be of dbObject::ValidityType value type.
<syntaxhighlight lang="sql">[OF_DYNAMIC_SQL] SELECT CONCAT(
'SELECT last_name AS Nom, first_name AS Prénom, (SELECT GROUP_CONCAT(name) FROM profile WHERE person.profile & profile.id ORDER BY profile.name) AS Profils, person.name AS Login, email AS Email, DATE_FORMAT(birthdate, \'%d/%m/%Y\') AS \'Date naissance\', address AS Adresse, zipcode AS \'Code postal\', city AS Ville, state AS Etat, country AS Pays, home_phone AS \'Téléphone domicile\', work_phone AS \'Téléphone travail\', cell_phone AS \'Téléphone mobile\', ', IFNULL(GROUP_CONCAT( CONCAT( ' (SELECT extra_field_content.content FROM extra_field_content WHERE person.id=extra_field_content.category_id AND extra_field_content.extra_field_id=', extra_field.id, ') AS \, REPLACE(extra_field.label, '\, '\\\), '\ ) ), '\'_\), ', IF(grant_date IS NOT NULL, DATE_FORMAT(grant_date, \'%d/%m/%Y\'), \'-\') AS \'Date obtention\', IF(expire_date IS NOT NULL, DATE_FORMAT(expire_date, \'%d/%m/%Y\'), \'-\') AS \'Limite de validité\', IF(ident_value IS NOT NULL, ident_value, \'-\') AS \'Code identifiant\' FROM person LEFT JOIN profile ON (person.profile & profile.id) LEFT JOIN validity ON (person.id=validity.person_id) LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id) WHERE person.activated=1 AND ( profile.id IN (', IF('-'=$profileId, '\'-\, $profileId), ') OR \'-\' IN (', IF('-'=$profileId, '\'-\, $profileId), ') ) AND ( (validity.validity_type_id IN (', IF('-'=$validityTypeId, '\'-\, $validityTypeId), ') AND validity_type.time_limitation=1 AND validity.expire_date >= \'$date\') OR \'-\' IN (', IF('-'=$validityTypeId, '\'-\, $validityTypeId), ') ) GROUP BY person.id ORDER BY last_name, first_name'
) FROM extra_field WHERE extra_field.category='PERSON'</sql>
User e-mails list
<syntaxhighlight lang="sql">SELECT CONCAT(person.first_name, ' ', person.last_name, ' <', person.email, '>') AS emails FROM person WHERE person.activated=1 ORDER BY person.last_name, person.first_name</sql>
Users by validity
Following extra field required:
- validityTypeId (Type: dbObject:ValidityType)
<syntaxhighlight lang="sql">SELECT validity_type.name AS 'Validity',
DATE_FORMAT(grant_date,'%d/%m/%Y') AS Date_d_obtention, last_name AS Name, first_name AS Firstname, ident_value AS Comment
FROM validity_type LEFT JOIN validity ON validity.validity_type_id =validity_type.id LEFT JOIN person ON person.id=validity.person_id WHERE ( validity_type.id IN ('$validityTypeId') OR ('-') IN ('$validityTypeId') )
AND person.activated=1
ORDER BY Name, Firstname</sql>
Validities (all) obtained after the selected year
- Variable $year should be defined first and should be of Year value type.
<syntaxhighlight lang="sql">SELECT validity_type.name AS 'Validity',
DATE_FORMAT(grant_date,'%d/%m/%Y') AS Date, last_name AS Name, first_name AS Firstname, ident_value AS Comment
FROM validity_type LEFT JOIN validity ON validity.validity_type_id=validity_type.id LEFT JOIN person ON person.id=validity.person_id WHERE YEAR(grant_date)>$year AND person.activated=1 ORDER BY Validity, Name, Firstname</sql>
Users with address
<syntaxhighlight lang="sql">SELECT last_name, first_name, name AS login, email, address, zipcode, city AS Ville, state AS etat_region, country AS pays, home_phone AS tel_domicile, work_phone AS tel_travail, cell_phone AS tel_mobile FROM person WHERE activated=1 ORDER BY last_name,first_name</sql>
Users with address and registration date (using ExtraField)
Extra field required :
- registrationDate
- label : Registration date
- category : User
- value type : DateTime
Validity type required :
- Cotisation
<syntaxhighlight lang="sql">SELECT
last_name, first_name, email, address, zipcode, city, state, country, home_phone, work_phone, cell_phone, sex, DATE_FORMAT(birthdate, '%Y-%m-%d') AS birthdate, nationality, validity.grant_date AS subscription_date, ( SELECT DATE_FORMAT(extra_field_content.content, '%Y-%m-%d') FROM extra_field LEFT JOIN extra_field_content ON extra_field.id=extra_field_content.extra_field_id WHERE extra_field.variable="registrationDate" and extra_field_content.category_id=person.id )AS registration_date
FROM person LEFT JOIN validity ON (validity.person_id = person.id) LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) WHERE activated = 1 AND validity_type.name = 'Cotisation' ORDER BY last_name, first_name</sql>
Users with address and registration date (using Validity)
<syntaxhighlight lang="sql">SELECT person.last_name, person.first_name, validity.grant_date AS registration_date FROM person LEFT JOIN validity ON validity.person_id=person.id LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id WHERE validity_type.name = "Date d'inscription" AND person.activated=1 GROUP BY last_name, first_name</sql>
User emails list of selected validity type person equals given year
Following extra field required:
- validityTypeId (Type: dbObject:ValidityType)
- year (Type: Year)
<syntaxhighlight lang="sql">SELECT CONCAT(person.first_name, ' ', person.last_name, ' <', person.email, '>') AS emails FROM person LEFT JOIN validity ON (validity.person_id = person.id) LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) WHERE person.activated=1
AND YEAR(validity.expire_date) = $year AND ( validity_type.id IN ('$validityTypeId') OR '-' IN ('$validityTypeId') )
GROUP BY person.id ORDER BY person.last_name, person.first_name</sql>
Users with e-mails with up-to-date expire date of validity VVV
Following symbols should be replace:
- VVV : validity type id
<syntaxhighlight lang="sql">SELECT CONCAT(person.first_name, ' ', person.last_name, ' <', person.email, '>') AS emails FROM person LEFT JOIN validity ON (validity.person_id = person.id) LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) WHERE person.activated=1 AND validity.expire_date > NOW() AND validity_type.id = VVV ORDER BY last_name,first_name</sql>
Users with validity ending before a specific date
Following extra field required:
- endDate (Type: Date)
- validityTypeId (Type: dbObject:ValidityType)
<syntaxhighlight lang="sql">SELECT person.id, person.first_name, person.last_name, validity_type.name AS 'Validity' FROM person LEFT JOIN validity ON (validity.person_id = person.id) LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) WHERE person.activated=1 AND validity.grant_date <= '$endDate'
AND validity_type.id = '$validityTypeId'</sql>
Users with up-to-date validity X and Y and user details
Replace X and Y by the validity_type id (2 times). <syntaxhighlight lang="sql">SELECT CONCAT(last_name, ' ', first_name) AS 'User', validity_type.name AS 'Validity name', expire_date AS 'Expire date', DATE_FORMAT(birthdate, '%Y/%m/%d') AS 'Birthdate', email, CONCAT (address, ' ', zipcode, ' ', city, ' ', state, ' ', country) AS 'Adress', home_phone AS 'Home phone', work_phone AS 'Work phone', cell_phone AS 'Cell phone' FROM person LEFT JOIN validity ON person.id=validity.person_id LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id WHERE validity_type.time_limitation=1 AND (validity_type.id=X OR validity_type.id=Y) AND person.id IN
( SELECT person.id FROM person LEFT JOIN validity ON person.id=validity.person_id LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id WHERE person.activated=1 AND (validity_type.id=X OR validity_type.id=Y) AND validity.expire_date >= UTC_DATE() ) ORDER BY last_name, first_name, validity_type.name</sql>
Users with validities viewer
<syntaxhighlight lang="sql">SELECT
last_name AS "Last name", first_name AS "First name", validity_type.name AS "Validity", DATE_FORMAT(expire_date,'%d/%m/%Y') AS "Expiration date", IF((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), "Expired","") AS "Expired", DATE_FORMAT(grant_date,'%d/%m/%Y') AS "Obtention date" FROM validity_type LEFT JOIN validity ON validity.validity_type_id = validity_type.id LEFT JOIN person ON person.id=validity.person_id
WHERE person.activated = 1 ORDER BY Last_name, First_name, validity_type.name</sql>
Users with age and birth date
<syntaxhighlight lang="sql">(SELECT last_name AS Last_name,
first_name AS First_name,
DATE_FORMAT(birthdate ,'%d-%m-%Y') AS Birth_date,
IF ( (sex = 0), 'Man', 'Woman' ) AS sex,
(DATE_FORMAT(DATE('$day'), '%Y') - DATE_FORMAT(birthdate, '%Y') - (SELECT DATE_FORMAT(DATE('$day'), '00-%m-%d') < DATE_FORMAT( birthdate, '00-%m-%d'))) AS 'age',
DATE_FORMAT(DATE('$day'),'%d-%m-%Y') AS on_date,
IF((DATE_FORMAT(DATE('$day'), '%Y') - DATE_FORMAT(birthdate, '%Y') - (SELECT DATE_FORMAT(DATE('$day'), '00-%m-%d') < DATE_FORMAT( birthdate, '00-%m-%d')))>=21, '>= 21', '< 21') AS Major,
-- inscription_date,
-- member.subscription,
-- entry.account_date as Adhesion DATE_FORMAT(entry.account_date,'%d-%m-%Y') as Grant_date
-- ,MAX(entry.account_date)
-- , $day -- , entry.flow_id -- , account.name
FROM person
RIGHT JOIN validity ON validity.person_id = person.id
RIGHT JOIN account acc2 on acc2.owner_id = person.id
RIGHT JOIN account_entry linked_entry on linked_entry.account_id = acc2.id
RIGHT JOIN account_entry entry on linked_entry.flow_id = entry.flow_id
RIGHT JOIN account on entry.account_id = account.id
WHERE year(validity.grant_date) = $year
AND account.name = 'Cotisations ACB'
AND (year(entry.account_date)=$year OR (year(entry.account_date)=$year -1 AND month(entry.account_date)=12) )
AND entry.account_date < DATE('$day')
ORDER BY Last_name LIMIT 999999 ) UNION ( SELECT COUNT(*) AS Last_name,
COUNT(*) AS First_name,
COUNT(*) AS Birth_date,
COUNT(*) AS sex,
COUNT(*) AS 'age',
COUNT(*) AS on_date,
COUNT(*) AS Major,
COUNT(*) as Grant_date
FROM person
RIGHT JOIN validity ON validity.person_id = person.id
RIGHT JOIN account acc2 on acc2.owner_id = person.id
RIGHT JOIN account_entry linked_entry on linked_entry.account_id = acc2.id
RIGHT JOIN account_entry entry on linked_entry.flow_id = entry.flow_id
RIGHT JOIN account on entry.account_id = account.id
WHERE year(validity.grant_date) = $year
AND account.name = 'Cotisations ACB'
AND (year(entry.account_date)=$year OR (year(entry.account_date)=$year -1 AND month(entry.account_date)=12) )
AND entry.account_date < DATE('$day') )</sql>
Users having validity X and grant date greater or equal to year Y
Following extra field required:
- validityTypeId (Type: dbObject:ValidityType)
<syntaxhighlight lang="sql">SELECT validity_type.name AS 'Validity',
DATE_FORMAT(grant_date, '%d/%m/%Y') AS grant_date, last_name AS Name, first_name AS Firstname, ident_value AS Comment, ( SELECT GROUP_CONCAT(profile.name SEPARATOR ', ') FROM profile WHERE (person.profile & profile.id) )AS Profile
FROM validity_type LEFT JOIN validity ON validity.validity_type_id=validity_type.id LEFT JOIN person ON person.id=validity.person_id WHERE YEAR(grant_date)>=$year AND validity_type.id='$validityTypeId' AND person.activated=1 ORDER BY Name, Firstname</sql>
Users with registration date, profiles and total flight time
<syntaxhighlight lang="sql">SELECT person.last_name, person.first_name, validity.grant_date AS registration_date, person.birthdate AS birthdate, IF ( (person.sex = 0), 'M', 'F' ) AS Gender, (
SELECT GROUP_CONCAT(profile.name SEPARATOR ', ') FROM profile WHERE (person.profile & profile.id)
)AS Profile, IFNULL((
SELECT CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total FROM flight_pilot fp LEFT JOIN flight ON fp.flight_id=flight.id WHERE fp.pilot_id=person.id AND fp.num=0
), 0) AS Total_flight_time FROM person LEFT JOIN validity ON validity.person_id=person.id LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id WHERE validity_type.name = "Date d'inscription" AND person.activated=1 GROUP BY last_name, first_name</sql>
Users with email
<syntaxhighlight lang="sql">SELECT last_name, first_name, email FROM person WHERE activated=1 ORDER BY last_name, first_name</sql>
Users with expire date validity equals a given year
Following extra field required:
- validityTypeId (Type: dbObject:ValidityType)
- year (Type: Year)
<syntaxhighlight lang="sql">SELECT person.id, person.first_name, person.last_name, validity_type.name AS 'Validity' FROM person LEFT JOIN validity ON (validity.person_id = person.id) LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) WHERE person.activated=1 AND YEAR(validity.expire_date) = $year AND validity_type.id = '$validityTypeId'</sql>
Users with validity expired date prior to 01/month/year
Following extra field required:
- validityTypeId (Type: dbObject::ValidityType)
- year (Type: Year)
<syntaxhighlight lang="sql">SELECT
last_name AS '_tr(LAST_NAME)', first_name AS '_tr(FIRST_NAME)', email AS '_tr(EMAIL)', home_phone AS '_tr(HOME_PHONE)', work_phone AS '_tr(WORK_PHONE)', cell_phone AS '_tr(CELL_PHONE)', validity_type.name AS '_tr(VALIDITY)', DATE_FORMAT(validity.expire_date,'%d/%m/%Y') AS '_tr(DUE_DATE)'
FROM person LEFT JOIN validity ON (person.id = validity.person_id) LEFT JOIN validity_type ON (validity.validity_type_id = validity_type.id) WHERE person.activated=1
AND validity.expire_date < '$year-$month-01' AND ( validity_type.id IN ($validityTypeId) OR ( '-' IN ($validityTypeId) AND validity_type.time_limitation=1 ) )
ORDER BY person.last_name, person.first_name, validity_type.name</sql>
Users with expire date validities inferior to the first day of the given month and year, with up-to-date expire date of validity XX
Following extra field required:
- month (Type: integer)
- year (Type: year)
Following symbols should be replace:
- XX : validity type id
<syntaxhighlight lang="sql">SELECT CONCAT(last_name, ' ', first_name) AS 'User', validity_type.name AS 'Validity name', expire_date AS 'Expire date' FROM person LEFT JOIN validity ON person.id=validity.person_id LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id WHERE validity_type.time_limitation=1 AND validity.expire_date < '$year-$month-01' AND person.id IN (
SELECT person.id FROM person LEFT JOIN validity ON person.id=validity.person_id LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id WHERE person.activated=1 AND validity_type.id=XX AND validity.expire_date >= UTC_DATE()
) ORDER BY last_name, first_name, validity_type.name</sql>
Users without an account
<SQL>SELECT person.last_name, person.first_name FROM person LEFT JOIN account ON account.owner_id=person.id LEFT JOIN account_type ON account.account_type=account_type.id WHERE person.activated=1 AND account.category=2 AND account_type.activated=1 AND account.activated=0</SQL>
Young users
<syntaxhighlight lang="sql">SELECT
last_name AS Last_name, first_name AS First_name, DATE_FORMAT(birthdate ,'%m-%d-%Y') AS Birthdate, IF ( sex = 0, 'Male', 'Female' ) AS sex
FROM person WHERE ($year-YEAR(birthdate))<=21 AND activated=1</sql>