Difference between revisions of "Users exports 4"

Jump to: navigation, search
(→‎User without validity)
(→‎User account import)
(44 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
=Expired user validity given year=
 
=Expired user validity given year=
−
<sql>SELECT person.id, person.first_name, person.last_name, validity_type.name AS 'Validity'
+
 
 +
*Variable '''$validityTypeId'''
 +
*Variable '''$year'''
 +
 
 +
<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
 
FROM person
−
LEFT JOIN validity ON (validity.person_id = person.id)
+
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)
 
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
−
WHERE person.activated=1
+
WHERE person.activated = 1
−
    AND YEAR(validity.expire_date) = $year
+
  AND validity_type.activated = 1
−
    AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )</sql>
+
  AND YEAR(validity.expire_date) = $year
 +
  AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )</sql>
  
 
=Expired user validity prior date=
 
=Expired user validity prior date=
−
<sql>SELECT  
+
 
−
     last_name AS '_tr(LAST_NAME)',  
+
*Variable '''$mount'''
−
    first_name AS '_tr(FIRST_NAME)',
+
*Variable '''$validityTypeId'''
−
     email AS '_tr(EMAIL)',
+
*Variable '''$year'''
−
     home_phone AS '_tr(HOME_PHONE)',
+
 
−
     work_phone AS '_tr(WORK_PHONE)',
+
<sql>SELECT
−
     cell_phone AS '_tr(CELL_PHONE)',
+
     CONCAT(last_name, ' ', first_name) AS _tr(FULL_NAME),
−
     validity_type.name AS '_tr(VALIDITY)',
+
     Email AS _tr(EMAIL),
−
     DATE_FORMAT(validity.expire_date,'%d/%m/%Y') AS '_tr(DUE_DATE)'
+
     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
 
FROM person
−
LEFT JOIN validity ON (person.id = validity.person_id)
+
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)
 
LEFT JOIN validity_type ON (validity.validity_type_id = validity_type.id)
−
WHERE person.activated=1
+
WHERE person.activated = 1
 +
  AND validity_type.activated = 1
 
   AND validity.expire_date < '$year-$month-01'
 
   AND validity.expire_date < '$year-$month-01'
 
   AND (
 
   AND (
Line 28: Line 41:
 
       ( '-' IN ($validityTypeId) AND validity_type.time_limitation=1 )
 
       ( '-' IN ($validityTypeId) AND validity_type.time_limitation=1 )
 
   )
 
   )
−
ORDER BY person.last_name, person.first_name, validity_type.name</sql>
+
ORDER BY `_tr(FULL_NAME)`, validity_type.name</sql>
  
 
=Obtained validity after year=
 
=Obtained validity after year=
−
<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>
+
 
 +
*Variable '''$year'''
 +
 
 +
<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=
 
=Obtained validity for year=
−
<sql>SELECT  
+
 
−
     last_name AS Last_name,  
+
*Variable '''$validityTypeId'''
−
    first_name AS First_name,  
+
*Variable '''$year'''
−
     IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
+
 
−
     IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex', DATE_FORMAT( grant_date, '%d/%m/%Y' ) AS Date, validity_type.name AS 'Validity'
+
<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  
 
FROM person  
−
LEFT JOIN validity ON person_id=person.id  
+
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
 
LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id
 
WHERE YEAR(grant_date)=$year
 
WHERE YEAR(grant_date)=$year
 
   AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
 
   AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
 
   AND person.activated=1
 
   AND person.activated=1
−
ORDER BY last_name, first_name</sql>
+
  AND validity_type.activated = 1
 +
ORDER BY `_tr(FULL_NAME)`</sql>
  
 
=User account import=
 
=User account import=
−
<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>
+
 
 +
*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>
  
 
=User coordinate=
 
=User coordinate=
 +
 +
*Variable '''$date'''
 +
*Variable '''$profileId'''
 +
*Variable '''$validityTypeId'''
 +
 
<sql>[OF_DYNAMIC_SQL]
 
<sql>[OF_DYNAMIC_SQL]
 
SELECT CONCAT(
 
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,
+
     '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 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\', ',
+
             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(
 
     IFNULL(GROUP_CONCAT(
 
         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=',
+
             ' (SELECT business_field_content.content FROM business_field_content WHERE person.id=business_field_content.category_id AND business_field_content.business_field_id=',
−
             extra_field.id,
+
             business_field.id,
 
             ') AS \'',
 
             ') AS \'',
−
             REPLACE(extra_field.label, '\'', '\\\''),
+
             REPLACE(business_field.label, '\'', '\\\''),
 
             '\''
 
             '\''
 
         )
 
         )
 
     ), '\'_\''),
 
     ), '\'_\''),
−
     ', IF(grant_date IS NOT NULL, DATE_FORMAT(grant_date, \'%d/%m/%Y\'), \'-\') AS \'Date obtention\',
+
     IF('-' IN ($validityTypeId), '', ', validity_type.name AS \'_tr(VALIDITY)\',
−
     IF(expire_date IS NOT NULL, DATE_FORMAT(expire_date, \'%d/%m/%Y\'), \'-\') AS \'Limite de validité\',
+
    IF(grant_date IS NOT NULL, DATE_FORMAT(grant_date, \'%d/%m/%Y\'), \'-\') AS \'_tr(VALIDITY_GRANT_DATE)\',
−
     IF(ident_value IS NOT NULL, ident_value, \'-\') AS \'Code identifiant\'
+
     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
 
     FROM person
 
     LEFT JOIN profile ON (person.profile & profile.id)
 
     LEFT JOIN profile ON (person.profile & profile.id)
−
     LEFT JOIN validity ON (person.id=validity.person_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)
 
     LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id)
−
     WHERE person.activated=1
+
     WHERE person.activated = 1
−
       AND ( profile.id IN (', IF('-'=$profileId, '\'-\'', $profileId), ') OR \'-\' IN (', IF('-'=$profileId, '\'-\'', $profileId), ') )
+
      AND validity_type.activated = 1
 +
       AND ( profile.id IN (', IF('-' IN ($profileId), '\'-\'', $profileId), ') OR \'-\' IN (', IF('-' IN ($profileId), '\'-\'', $profileId), ') )
 
       AND (
 
       AND (
−
           (validity.validity_type_id IN (', IF('-'=$validityTypeId, '\'-\'', $validityTypeId), ') AND validity_type.time_limitation=1 AND validity.expire_date >= \'$date\')
+
           (validity.validity_type_id IN (', IF('-' IN ($validityTypeId), '\'-\'', $validityTypeId), ') AND validity_type.time_limitation=1 AND validity.expire_date >= '$date')
 
           OR
 
           OR
−
           \'-\' IN (', IF('-'=$validityTypeId, '\'-\'', $validityTypeId), ')
+
           \'-\' IN (', IF('-' IN ($validityTypeId), '\'-\'', $validityTypeId), ')
 
       )
 
       )
−
     GROUP BY person.id
+
     GROUP BY person.id', IF('-' IN ($validityTypeId), '', ', validity_type.id'), '
 
     ORDER BY last_name, first_name'
 
     ORDER BY last_name, first_name'
 
)
 
)
−
FROM extra_field
+
FROM business_field
−
WHERE extra_field.category='PERSON'</sql>
+
WHERE business_field.category='PERSON'</sql>
  
 
=User email=
 
=User email=
Line 88: Line 137:
  
 
=User mail with validity type equal year=
 
=User mail with validity type equal year=
−
<sql>SELECT CONCAT(person.first_name, ' ', person.last_name, ' <', person.email, '>') AS emails
+
 
 +
*Variable '''$validityTypeId'''
 +
*Variable '''$year'''
 +
 
 +
<sql>SELECT
 +
    CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
 +
    person.email AS _tr(EMAIL)
 
FROM person
 
FROM person
−
LEFT JOIN validity ON (validity.person_id = person.id)
+
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)
 
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
−
WHERE person.activated=1
+
WHERE person.activated = 1
 +
  AND validity_type.activated = 1
 
   AND YEAR(validity.expire_date) = $year
 
   AND YEAR(validity.expire_date) = $year
 
   AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
 
   AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
 
GROUP BY person.id
 
GROUP BY person.id
−
ORDER BY person.last_name, person.first_name</sql>
+
ORDER BY `_tr(FULL_NAME)`</sql>
  
 
=User validity=
 
=User validity=
−
<sql>SELECT validity_type.name AS 'Validity',
+
 
−
     DATE_FORMAT(grant_date,'%d/%m/%Y') AS Date_d_obtention,
+
*Variable '''$validityTypeId'''
−
     last_name AS Name,
+
 
−
    first_name AS Firstname,
+
<sql>SELECT
−
     ident_value AS Comment
+
    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
 
FROM validity_type
−
LEFT JOIN validity ON validity.validity_type_id =validity_type.id  
+
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
 
LEFT JOIN person ON person.id=validity.person_id
 
WHERE ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
 
WHERE ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
−
   AND person.activated=1
+
   AND person.activated = 1
−
ORDER BY Name, Firstname</sql>
+
  AND validity_type.activated = 1
−
 
+
ORDER BY validity_type.name, `_tr(FULL_NAME)`</sql>
  
 
=User validity ending before date=
 
=User validity ending before date=
−
<sql>SELECT person.id, person.first_name, person.last_name, validity_type.name AS 'Validity'
+
 
 +
*Variable '''$endDate'''
 +
*Variable '''$validityTypeId'''
 +
 
 +
<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
 
FROM person
−
LEFT JOIN validity ON (validity.person_id = person.id)
+
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)
 
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
−
WHERE person.activated=1 AND validity.grant_date <= '$endDate'
+
WHERE person.activated = 1
 +
  AND validity_type.activated = 1
 +
  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>
  
 
=User validity superior year=
 
=User validity superior year=
−
<sql>SELECT validity_type.name AS 'Validity',
+
 
−
     DATE_FORMAT(grant_date, '%d/%m/%Y') AS grant_date,
+
*Variable '''$validityTypeId'''
−
    last_name AS Name,
+
*Variable '''$year'''
−
    first_name AS Firstname,
+
 
−
     ident_value AS Comment,
+
<sql>SELECT
−
     ( SELECT GROUP_CONCAT(profile.name SEPARATOR ', ')  
+
    CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
−
      FROM profile WHERE (person.profile & profile.id)
+
    validity_type.name AS _tr(VALIDITY),
−
     ) AS Profile
+
     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
 
FROM validity_type
−
LEFT JOIN validity ON validity.validity_type_id=validity_type.id  
+
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
 
LEFT JOIN person ON person.id=validity.person_id
 
WHERE YEAR(grant_date)>=$year
 
WHERE YEAR(grant_date)>=$year
−
AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
+
  AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
−
AND person.activated=1
+
  AND person.activated = 1
−
ORDER BY Name, Firstname</sql>
+
  AND validity_type.activated = 1
 +
ORDER BY `_tr(FULL_NAME)`</sql>
  
 
=User validity viewer=
 
=User validity viewer=
−
<sql>SELECT  
+
<sql>SELECT
−
      last_name AS "Last name",
+
    CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
−
      first_name AS "First name",
+
    validity_type.name AS _tr(VALIDITY),
−
      validity_type.name AS "Validity",
+
    DATE_FORMAT(expire_date,'%d/%m/%Y') AS _tr(VALIDITY_EXPIRATE_DATE),
−
      DATE_FORMAT(expire_date,'%d/%m/%Y') AS "Expiration date",
+
    IF((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), _tr(YES), _tr(NO)) AS _tr(EXPIRED),
−
      IF((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), "Expired","") AS "Expired",
+
    DATE_FORMAT(grant_date,'%d/%m/%Y') AS _tr(VALIDITY_GRANT_DATE)
−
      DATE_FORMAT(grant_date,'%d/%m/%Y') AS "Obtention 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  
+
LEFT JOIN person ON person.id=validity.person_id
−
  LEFT JOIN person ON person.id=validity.person_id
+
 
WHERE person.activated = 1
 
WHERE person.activated = 1
−
ORDER BY Last_name, First_name, validity_type.name</sql>
+
  AND validity_type.activated = 1
 +
ORDER BY `_tr(FULL_NAME)`, validity_type.name</sql>
  
 
=User validity without date=
 
=User validity without date=
−
<sql>SELECT person.first_name, person.last_name, validity.* FROM `validity` 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</sql>
+
 
 +
*Variable '''$validityTypeId'''
 +
 
 +
<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=
 
=User validity without expired date=
−
<sql>SELECT person.first_name, person.last_name, validity_type.name AS 'Validite'
+
 
 +
*Variable '''$validityTypeId'''
 +
 
 +
<sql>SELECT
 +
    CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
 +
    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 161: Line 256:
 
WHERE ( validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
 
WHERE ( validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
 
   AND expire_date IS NULL
 
   AND expire_date IS NULL
−
   AND person.activated=1</sql>
+
   AND person.activated = 1
 +
  AND validity_type.activated = 1
 +
  AND validity.is_current_validity = 1</sql>
  
 
=User without validity=
 
=User without validity=
−
<sql>SELECT person.id AS '_tr(ID)', person.first_name AS '_tr(FIRST_NAME)', person.last_name AS '_tr(LAST_NAME)'
+
 
 +
*Variable '''$validityTypeId'''
 +
 
 +
<sql>SELECT
 +
    person.id AS _tr(ID),
 +
    CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME)
 
FROM person
 
FROM person
 
WHERE person.id NOT IN (
 
WHERE person.id NOT IN (
−
SELECT person.id
+
    SELECT person.id
−
FROM person
+
    FROM person
−
LEFT JOIN validity ON person.id = validity.person_id
+
    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)
+
    WHERE validity.validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId)
 
)
 
)
 
AND person.activated = 1
 
AND person.activated = 1
−
ORDER BY person.last_name, person.first_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=
 +
 +
*Variable '''$year'''
 +
 
<sql>SELECT  
 
<sql>SELECT  
 
     last_name AS Last_name,
 
     last_name AS Last_name,

Revision as of 16:07, 7 October 2021

Expired user validity given year

  • Variable $validityTypeId
  • Variable $year
SELECT