Difference between revisions of "Users exports 4"

Jump to: navigation, search
(User without validity)
(User account import)
 
(12 intermediate revisions by the same user not shown)
Line 5: Line 5:
  
 
<sql>SELECT
 
<sql>SELECT
     person.id AS '_tr(ID)',
+
     person.id AS _tr(ID),
     CONCAT(person.last_name, ' ', person.first_name) AS '_tr(FULL_NAME)',
+
     CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
     validity_type.name AS '_tr(VALIDITY)'
+
     validity_type.name AS _tr(VALIDITY)
 
FROM person
 
FROM person
 
LEFT JOIN validity ON validity.person_id = person.id AND validity.is_current_validity = 1
 
LEFT JOIN validity ON validity.person_id = person.id AND validity.is_current_validity = 1
Line 23: Line 23:
  
 
<sql>SELECT
 
<sql>SELECT
     CONCAT(last_name, ' ', first_name) AS '_tr(FULL_NAME)',
+
     CONCAT(last_name, ' ', first_name) AS _tr(FULL_NAME),
     Email AS '_tr(EMAIL)',
+
     Email AS _tr(EMAIL),
     home_phone AS '_tr(HOME_PHONE)',
+
     home_phone AS _tr(HOME_PHONE),
     work_phone AS '_tr(WORK_PHONE)',
+
     work_phone AS _tr(WORK_PHONE),
     cell_phone AS '_tr(CELL_PHONE)',
+
     cell_phone AS _tr(CELL_PHONE),
     validity_type.name AS '_tr(VALIDITY)',
+
     validity_type.name AS _tr(VALIDITY),
     DATE_FORMAT(validity.expire_date,'%d/%m/%Y') AS '_tr(DUE_DATE)'
+
     DATE_FORMAT(validity.expire_date,'%d/%m/%Y') AS _tr(DUE_DATE)
 
FROM person
 
FROM person
 
LEFT JOIN validity ON person.id = validity.person_id AND validity.is_current_validity = 1
 
LEFT JOIN validity ON person.id = validity.person_id AND validity.is_current_validity = 1
Line 48: Line 48:
  
 
<sql>SELECT
 
<sql>SELECT
     validity_type.name AS '_tr(VALIDITY)',
+
     validity_type.name AS _tr(VALIDITY),
     DATE_FORMAT(grant_date,'%d/%m/%Y') AS '_tr(DATE)',
+
     DATE_FORMAT(grant_date,'%d/%m/%Y') AS _tr(DATE),
     CONCAT(last_name, ' ', first_name) AS '_tr(FULL_NAME)',
+
     CONCAT(last_name, ' ', first_name) AS _tr(FULL_NAME),
     ident_value AS '_tr(VALIDITY_REGISTRATION)'
+
     ident_value AS _tr(VALIDITY_REGISTRATION)
 
FROM validity_type
 
FROM validity_type
 
LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1
 
LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1
Line 66: Line 66:
  
 
<sql>SELECT
 
<sql>SELECT
     CONCAT(last_name, ' ', first_name) AS '_tr(FULL_NAME)',  
+
     CONCAT(last_name, ' ', first_name) AS _tr(FULL_NAME),  
     IF( ( $year - YEAR( birthdate ) >= 21) , '_tr(NO)', '_tr(YES)') AS '_tr(YOUNG)',
+
     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)',
+
     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)',
+
     DATE_FORMAT( grant_date, '%d/%m/%Y' ) AS _tr(DATE),
     validity_type.name AS '_tr(VALIDITY)'
+
     validity_type.name AS _tr(VALIDITY)
 
FROM person  
 
FROM person  
 
LEFT JOIN validity ON person_id=person.id AND validity.is_current_validity = 1
 
LEFT JOIN validity ON person_id=person.id AND validity.is_current_validity = 1
Line 84: Line 84:
 
*Variable '''$endDate'''
 
*Variable '''$endDate'''
  
<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>
+
<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=
 
=User coordinate=
Line 142: Line 142:
  
 
<sql>SELECT
 
<sql>SELECT
     CONCAT(person.last_name, ' ', person.first_name) AS '_tr(FULL_NAME)',
+
     CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
     person.email AS '_tr(EMAIL)'
+
     person.email AS _tr(EMAIL)
 
FROM person
 
FROM person
 
LEFT JOIN validity ON validity.person_id = person.id AND validity.is_current_validity = 1
 
LEFT JOIN validity ON validity.person_id = person.id AND validity.is_current_validity = 1
Line 159: Line 159:
  
 
<sql>SELECT
 
<sql>SELECT
     validity_type.name AS '_tr(VALIDITY)',
+
     validity_type.name AS _tr(VALIDITY),
     CONCAT(person.last_name, ' ', person.first_name) AS '_tr(FULL_NAME)',
+
     CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
     ident_value AS '_tr(VALIDITY_REGISTRATION)',
+
     ident_value AS _tr(VALIDITY_REGISTRATION),
     DATE_FORMAT(expire_date,'%d/%m/%Y') AS '_tr(VALIDITY_EXPIRATE_DATE)',
+
     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)',
+
     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)'
+
     DATE_FORMAT(grant_date,'%d/%m/%Y') AS _tr(VALIDITY_GRANT_DATE)
 
FROM validity_type
 
FROM validity_type
 
LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1
 
LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1
Line 179: Line 179:
  
 
<sql>SELECT
 
<sql>SELECT
     person.id AS '_tr(ID)',
+
     person.id AS _tr(ID),
     CONCAT(person.last_name, ' ', person.first_name) AS '_tr(FULL_NAME)',
+
     CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
     validity_type.name AS '_tr(VALIDITY)'
+
     validity_type.name AS _tr(VALIDITY)
 
FROM person
 
FROM person
 
LEFT JOIN validity ON validity.person_id = person.id AND validity.is_current_validity = 1
 
LEFT JOIN validity ON validity.person_id = person.id AND validity.is_current_validity = 1
Line 187: Line 187:
 
WHERE person.activated = 1
 
WHERE person.activated = 1
 
   AND validity_type.activated = 1
 
   AND validity_type.activated = 1
   AND validity.grant_date <= '$endDate'
+
   AND validity.grant_date <= $endDate
 
   AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )</sql>
 
   AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )</sql>
  
Line 196: Line 196:
  
 
<sql>SELECT
 
<sql>SELECT
     CONCAT(person.last_name, ' ', person.first_name) AS '_tr(FULL_NAME)',
+
     CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
     validity_type.name AS '_tr(VALIDITY)',
+
     validity_type.name AS _tr(VALIDITY),
     DATE_FORMAT(grant_date, '%d/%m/%Y') AS '_tr(VALIDITY_GRANT_DATE)',
+
     DATE_FORMAT(grant_date, '%d/%m/%Y') AS _tr(VALIDITY_GRANT_DATE),
     ident_value AS '_tr(VALIDITY_REGISTRATION)',
+
     ident_value AS _tr(VALIDITY_REGISTRATION),
 
     (
 
     (
 
         SELECT GROUP_CONCAT(profile.name SEPARATOR ', ')  
 
         SELECT GROUP_CONCAT(profile.name SEPARATOR ', ')  
 
         FROM profile
 
         FROM profile
 
         WHERE (person.profile & profile.id)
 
         WHERE (person.profile & profile.id)
     ) AS '_tr(PROFILE)'
+
     ) AS _tr(PROFILE)
 
FROM validity_type
 
FROM validity_type
 
LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1
 
LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1
Line 216: Line 216:
 
=User validity viewer=
 
=User validity viewer=
 
<sql>SELECT
 
<sql>SELECT
     CONCAT(person.last_name, ' ', person.first_name) AS '_tr(FULL_NAME)',
+
     CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
     validity_type.name AS '_tr(VALIDITY)',
+
     validity_type.name AS _tr(VALIDITY),
     DATE_FORMAT(expire_date,'%d/%m/%Y') AS '_tr(VALIDITY_EXPIRATE_DATE)',
+
     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)',
+
     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)'
+
     DATE_FORMAT(grant_date,'%d/%m/%Y') AS _tr(VALIDITY_GRANT_DATE)
 
FROM validity_type
 
FROM validity_type
 
LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1
 
LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1
Line 233: Line 233:
  
 
<sql>SELECT
 
<sql>SELECT
     CONCAT(person.last_name, ' ', person.first_name) AS '_tr(FULL_NAME)',
+
     CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
 
     validity.*
 
     validity.*
 
FROM `validity`
 
FROM `validity`
Line 249: Line 249:
  
 
<sql>SELECT
 
<sql>SELECT
     CONCAT(person.last_name, ' ', person.first_name) AS '_tr(FULL_NAME)',
+
     CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
     validity_type.name AS '_tr(VALIDITY)'
+
     validity_type.name AS _tr(VALIDITY)
 
FROM validity
 
FROM validity
 
LEFT JOIN validity_type ON validity.validity_type_id=validity_type.id
 
LEFT JOIN validity_type ON validity.validity_type_id=validity_type.id
Line 276: Line 276:
 
AND person.activated = 1
 
AND person.activated = 1
 
ORDER BY `_tr(FULL_NAME)`</sql>
 
ORDER BY `_tr(FULL_NAME)`</sql>
 +
 +
=Validity year young specific profile=
 +
 +
*Variable '''$profileId'''
 +
*Variable '''$year'''
 +
 +
<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=
 
=Young from this year=

Latest revision as of 14:07, 7 October 2021

Expired user validity given year

  • Variable $validityTypeId
  • Variable $year
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 YEAR(validity.expire_date) = $year
  AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )

Expired user validity prior date

  • Variable $mount
  • Variable $validityTypeId
  • Variable $year
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

Obtained validity after year

  • Variable $year
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)`

Obtained validity for year

  • Variable $validityTypeId
  • Variable $year
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)`

User account import

  • Variable $endDate
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

User coordinate

  • Variable $date
  • Variable $profileId
  • Variable $validityTypeId
[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'

User email

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

User mail with validity type equal year

  • Variable $validityTypeId
  • Variable $year
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)`

User validity

  • Variable $validityTypeId
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)`

User validity ending before date

  • Variable $endDate
  • Variable $validityTypeId
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) )

User validity superior year

  • Variable $validityTypeId
  • Variable $year
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)`

User validity viewer

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

User validity without date

  • Variable $validityTypeId
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

User validity without expired date

  • Variable $validityTypeId
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

User without validity

  • Variable $validityTypeId
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)`

Validity year young specific profile

  • Variable $profileId
  • Variable $year
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

Young from this year

  • Variable $year
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