Users reports 3

From OpenFlyers Documentation
Jump to navigation Jump to search

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 :

  1. registrationDate
    • label : Registration date
    • category : User
    • value type : DateTime

Validity type required :

  1. 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>

Retrieved from "https://doc-en-mirror.openflyers.com/index.php?title=Users-reports-3&oldid=281"