Users exports 4

From OpenFlyers Documentation
Jump to navigation Jump to search

Expired user validity given year

  • Variable $validityTypeId
  • Variable $year

<syntaxhighlight lang="sql">SELECT 

   person.id AS _tr(ID),
   CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
   validity_type.name AS _tr(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 validity_type.activated = 1 

 AND YEAR(validity.expire_date) = $year
 AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )</sql>

Expired user validity prior date

  • Variable $mount
  • Variable $validityTypeId
  • Variable $year

<syntaxhighlight lang="sql">SELECT 

   CONCAT(last_name, ' ', first_name) AS _tr(FULL_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 AND validity.is_current_validity = 1 LEFT JOIN validity_type ON (validity.validity_type_id = validity_type.id) WHERE person.activated = 1 

 AND validity_type.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 `_tr(FULL_NAME)`, validity_type.name</sql>

Obtained validity after year

  • Variable $year

<syntaxhighlight lang="sql">SELECT 

   validity_type.name AS _tr(VALIDITY),
   DATE_FORMAT(grant_date,'%d/%m/%Y') AS _tr(DATE),
   CONCAT(last_name, ' ', first_name) AS _tr(FULL_NAME),
   ident_value AS _tr(VALIDITY_REGISTRATION)

FROM validity_type LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1 LEFT JOIN person ON person.id=validity.person_id WHERE YEAR(grant_date)>$year 

 AND person.activated=1
 AND validity_type.activated = 1

ORDER BY validity_type.name, `_tr(FULL_NAME)`</sql>

Obtained validity for year

  • Variable $validityTypeId
  • Variable $year

<syntaxhighlight lang="sql">SELECT 

   CONCAT(last_name, ' ', first_name) AS _tr(FULL_NAME), 
   IF( ( $year - YEAR( birthdate ) >= 21) , _tr(NO), _tr(YES)) AS _tr(YOUNG),
   IF( person.sex=0, _tr(SEX_MALE_INITIAL), IF( person.sex=1, _tr(SEX_FEMALE_INITIAL), _tr(UNDEFINED)) ) AS _tr(SEX),
   DATE_FORMAT( grant_date, '%d/%m/%Y' ) AS _tr(DATE),
   validity_type.name AS _tr(VALIDITY)

FROM person LEFT JOIN validity ON person_id=person.id AND validity.is_current_validity = 1 LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id WHERE YEAR(grant_date)=$year 

 AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
 AND person.activated=1
 AND validity_type.activated = 1

ORDER BY `_tr(FULL_NAME)`</sql>

User account import

  • Variable $endDate

<syntaxhighlight lang="sql">SELECT person.id AS idopenflyers, person.last_name, person.first_name, account_type.name AS account_type_name, sumAccountEntry(account.id, $endDate) AS balance FROM account LEFT JOIN account_type ON (account.account_type=account_type.id) LEFT JOIN person ON (account.owner_id=person.id) WHERE account.category=2 AND account.activated=1 ORDER BY last_name, first_name</sql>

User coordinate

  • Variable $date
  • Variable $profileId
  • Variable $validityTypeId

<syntaxhighlight lang="sql">[OF_DYNAMIC_SQL] SELECT CONCAT( 

   'SELECT person.id AS \'_tr(ID)\', CONCAT(last_name, \' \', first_name) AS \'_tr(FULL_NAME)\', (SELECT GROUP_CONCAT(name) FROM profile WHERE person.profile & profile.id ORDER BY profile.name) AS \'_tr(PROFILE)\',
           person.name AS Login, email AS \'_tr(EMAIL)\', DATE_FORMAT(birthdate, \'%d/%m/%Y\') AS \'_tr(BIRTHDATE)\', YEAR(birthdate) AS \'_tr(YEAR_OF_BIRTH)\',
           CASE sex
               WHEN 0 THEN \'_tr(SEX_MALE_INITIAL)\'
               WHEN 1 THEN \'_tr(SEX_FEMALE_INITIAL)\'
               ELSE \'\'
           END AS \'_tr(SEX)\',
           address AS \'_tr(ADDRESS)\', zipcode AS \'_tr(ZIPCODE)\', city AS \'_tr(CITY)\', state AS \'_tr(STATE)\', country AS \'_tr(COUNTRY)\', home_phone AS \'_tr(HOME_PHONE)\', work_phone AS \'_tr(WORK_PHONE)\', cell_phone AS \'_tr(CELL_PHONE)\', ',
   IFNULL(GROUP_CONCAT(
       CONCAT(
           ' (SELECT business_field_content.content FROM business_field_content WHERE person.id=business_field_content.category_id AND business_field_content.business_field_id=',
           business_field.id,
           ') AS \,
           REPLACE(business_field.label, '\, '\\\),
           '\
       )
   ), '\'_\),
   IF('-' IN ($validityTypeId), , ', validity_type.name AS \'_tr(VALIDITY)\',
   IF(grant_date IS NOT NULL, DATE_FORMAT(grant_date, \'%d/%m/%Y\'), \'-\') AS \'_tr(VALIDITY_GRANT_DATE)\',
   IF(expire_date IS NOT NULL, DATE_FORMAT(expire_date, \'%d/%m/%Y\'), \'-\') AS \'_tr(VALIDITY_EXPIRATE_DATE)\',
   IF(ident_value IS NOT NULL, ident_value, \'-\') AS \'_tr(VALIDITY_REGISTRATION)\), '
   FROM person
   LEFT JOIN profile ON (person.profile & profile.id)
   LEFT JOIN validity ON person.id = validity.person_id AND validity.is_current_validity = 1
   LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id)
   WHERE person.activated = 1
     AND validity_type.activated = 1
     AND ( profile.id IN (', IF('-' IN ($profileId), '\'-\, $profileId), ') OR \'-\' IN (', IF('-' IN ($profileId), '\'-\, $profileId), ') )
     AND (
         (validity.validity_type_id IN (', IF('-' IN ($validityTypeId), '\'-\, $validityTypeId), ') AND validity_type.time_limitation=1 AND validity.expire_date >= '$date')
         OR
         \'-\' IN (', IF('-' IN ($validityTypeId), '\'-\, $validityTypeId), ')
     )
   GROUP BY person.id', IF('-' IN ($validityTypeId), , ', validity_type.id'), '
   ORDER BY last_name, first_name'

) FROM business_field WHERE business_field.category='PERSON'</sql>

User email

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

User mail with validity type equal year

  • Variable $validityTypeId
  • Variable $year

<syntaxhighlight lang="sql">SELECT 

   CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
   person.email AS _tr(EMAIL)

FROM person LEFT JOIN validity ON validity.person_id = person.id AND validity.is_current_validity = 1 LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) WHERE person.activated = 1 

 AND validity_type.activated = 1
 AND YEAR(validity.expire_date) = $year
 AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )

GROUP BY person.id ORDER BY `_tr(FULL_NAME)`</sql>

User validity

  • Variable $validityTypeId

<syntaxhighlight lang="sql">SELECT 

   validity_type.name AS _tr(VALIDITY),
   CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
   ident_value AS _tr(VALIDITY_REGISTRATION),
   DATE_FORMAT(expire_date,'%d/%m/%Y') AS _tr(VALIDITY_EXPIRATE_DATE),
   IF((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), _tr(YES), _tr(NO)) AS _tr(EXPIRED),
   DATE_FORMAT(grant_date,'%d/%m/%Y') AS _tr(VALIDITY_GRANT_DATE)

FROM validity_type LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1 LEFT JOIN person ON person.id=validity.person_id WHERE ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) ) 

 AND person.activated = 1
 AND validity_type.activated = 1

ORDER BY validity_type.name, `_tr(FULL_NAME)`</sql>

User validity ending before date

  • Variable $endDate
  • Variable $validityTypeId

<syntaxhighlight lang="sql">SELECT 

   person.id AS _tr(ID),
   CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
   validity_type.name AS _tr(VALIDITY)

FROM person LEFT JOIN validity ON validity.person_id = person.id AND validity.is_current_validity = 1 LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) WHERE person.activated = 1 

 AND validity_type.activated = 1
 AND validity.grant_date <= $endDate
 AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )</sql>

User validity superior year

  • Variable $validityTypeId
  • Variable $year

<syntaxhighlight lang="sql">SELECT 

   CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
   validity_type.name AS _tr(VALIDITY),
   DATE_FORMAT(grant_date, '%d/%m/%Y') AS _tr(VALIDITY_GRANT_DATE),
   ident_value AS _tr(VALIDITY_REGISTRATION),
   (
       SELECT GROUP_CONCAT(profile.name SEPARATOR ', ') 
       FROM profile
       WHERE (person.profile & profile.id)
   ) AS _tr(PROFILE)

FROM validity_type LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1 LEFT JOIN person ON person.id=validity.person_id WHERE YEAR(grant_date)>=$year 

 AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
 AND person.activated = 1
 AND validity_type.activated = 1

ORDER BY `_tr(FULL_NAME)`</sql>

User validity viewer

<syntaxhighlight lang="sql">SELECT 

   CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
   validity_type.name AS _tr(VALIDITY),
   DATE_FORMAT(expire_date,'%d/%m/%Y') AS _tr(VALIDITY_EXPIRATE_DATE),
   IF((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), _tr(YES), _tr(NO)) AS _tr(EXPIRED),
   DATE_FORMAT(grant_date,'%d/%m/%Y') AS _tr(VALIDITY_GRANT_DATE)

FROM validity_type LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1 LEFT JOIN person ON person.id=validity.person_id WHERE person.activated = 1 

 AND validity_type.activated = 1

ORDER BY `_tr(FULL_NAME)`, validity_type.name</sql>

User validity without date

  • Variable $validityTypeId

<syntaxhighlight lang="sql">SELECT 

   CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
   validity.*

FROM `validity` LEFT JOIN person ON person.id = validity.person_id LEFT JOIN validity_type ON validity_type.id = validity.validity_type_id WHERE ( validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId) ) 

 AND validity.expire_date IS NULL
 AND person.activated = 1
 AND validity_type.activated = 1
 AND validity.is_current_validity = 1</sql>

User validity without expired date

  • Variable $validityTypeId

<syntaxhighlight lang="sql">SELECT 

   CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
   validity_type.name AS _tr(VALIDITY)

FROM validity LEFT JOIN validity_type 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 expire_date IS NULL
 AND person.activated = 1
 AND validity_type.activated = 1
 AND validity.is_current_validity = 1</sql>

User without validity

  • Variable $validityTypeId

<syntaxhighlight lang="sql">SELECT 

   person.id AS _tr(ID),
   CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME)

FROM person WHERE person.id NOT IN ( 

   SELECT person.id
   FROM person
   LEFT JOIN validity ON person.id = validity.person_id AND validity.is_current_validity = 1
   WHERE validity.validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId)

) AND person.activated = 1 ORDER BY `_tr(FULL_NAME)`</sql>

Validity year young specific profile

  • Variable $profileId
  • Variable $year

<syntaxhighlight lang="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 LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1 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 (validity.person_id=personWithProfile.id) WHERE YEAR(validity.grant_date)=$year 

 AND personWithProfile.id IS NOT NULL
 AND validity_type.activated = 1

GROUP BY validity_type.id ORDER BY validity_type.name</sql>

Young from this year

  • Variable $year

<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-exports-4&oldid=300"