Difference between revisions of "Users exports 4"
(→Personal details (with profile X and/or validity Y expiring at a date posterior or equal to Z)) |
|||
(3 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
− | = | + | =Expired user validity given year= |
− | <sql>SELECT id, last_name, | + | <sql>SELECT person.id, person.first_name, person.last_name, validity_type.name AS 'Validity' |
FROM person | FROM person | ||
− | WHERE activated=1</sql> | + | 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) )</sql> | ||
− | = | + | =Expired user validity prior date= |
− | <sql>SELECT last_name, first_name, email | + | <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 | FROM person | ||
− | WHERE activated=1 | + | 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> | ||
+ | |||
+ | =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> | ||
+ | |||
+ | =Obtained validity for year= | ||
+ | <sql>SELECT | ||
+ | last_name AS Last_name, | ||
+ | first_name AS First_name, | ||
+ | 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' | ||
+ | FROM person | ||
+ | LEFT JOIN validity ON person_id=person.id | ||
+ | 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 | ||
ORDER BY last_name, first_name</sql> | ORDER BY last_name, first_name</sql> | ||
− | = | + | =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> | |
− | + | ||
− | + | ||
− | + | ||
+ | =User coordinate= | ||
<sql>[OF_DYNAMIC_SQL] | <sql>[OF_DYNAMIC_SQL] | ||
SELECT CONCAT( | SELECT CONCAT( | ||
− | 'SELECT | + | '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 | + | 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( | IFNULL(GROUP_CONCAT( | ||
CONCAT( | CONCAT( | ||
− | ' (SELECT | + | ' (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 \'', | ') AS \'', | ||
− | REPLACE( | + | 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(expire_date IS NOT NULL, DATE_FORMAT(expire_date, \'%d/%m/%Y\'), \'-\') AS \' | + | IF(ident_value IS NOT NULL, ident_value, \'-\') AS \'Code identifiant\' |
− | IF(ident_value IS NOT NULL, ident_value, \'-\') AS \' | + | |
FROM person | FROM person | ||
LEFT JOIN profile ON (person.profile & profile.id) | LEFT JOIN profile ON (person.profile & profile.id) | ||
Line 44: | Line 72: | ||
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('-' | + | AND ( profile.id IN (', IF('-'=$profileId, '\'-\'', $profileId), ') OR \'-\' IN (', IF('-'=$profileId, '\'-\'', $profileId), ') ) |
AND ( | AND ( | ||
− | (validity.validity_type_id IN (', IF('-' | + | (validity.validity_type_id IN (', IF('-'=$validityTypeId, '\'-\'', $validityTypeId), ') AND validity_type.time_limitation=1 AND validity.expire_date >= \'$date\') |
OR | OR | ||
− | \'-\' IN (', IF('-' | + | \'-\' IN (', IF('-'=$validityTypeId, '\'-\'', $validityTypeId), ') |
) | ) | ||
− | GROUP BY person.id | + | GROUP BY person.id |
ORDER BY last_name, first_name' | ORDER BY last_name, first_name' | ||
) | ) | ||
− | FROM | + | FROM extra_field |
− | WHERE | + | WHERE extra_field.category='PERSON'</sql> |
− | + | =User email= | |
− | + | <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 | + | |
− | <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= | ||
<sql>SELECT CONCAT(person.first_name, ' ', person.last_name, ' <', person.email, '>') AS emails | <sql>SELECT CONCAT(person.first_name, ' ', person.last_name, ' <', person.email, '>') AS emails | ||
FROM person | FROM person | ||
Line 87: | Line 98: | ||
ORDER BY person.last_name, person.first_name</sql> | ORDER BY person.last_name, person.first_name</sql> | ||
− | =User | + | =User validity= |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
<sql>SELECT validity_type.name AS 'Validity', | <sql>SELECT validity_type.name AS 'Validity', | ||
DATE_FORMAT(grant_date,'%d/%m/%Y') AS Date_d_obtention, | DATE_FORMAT(grant_date,'%d/%m/%Y') AS Date_d_obtention, | ||
Line 338: | Line 111: | ||
ORDER BY Name, Firstname</sql> | ORDER BY Name, Firstname</sql> | ||
− | |||
− | |||
− | |||
− | |||
+ | =User validity ending before date= | ||
+ | <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 IN ($validityTypeId) OR '-' IN ($validityTypeId) )</sql> | ||
+ | |||
+ | =User validity superior year= | ||
<sql>SELECT validity_type.name AS 'Validity', | <sql>SELECT validity_type.name AS 'Validity', | ||
DATE_FORMAT(grant_date, '%d/%m/%Y') AS grant_date, | DATE_FORMAT(grant_date, '%d/%m/%Y') AS grant_date, | ||
Line 359: | Line 137: | ||
ORDER BY Name, Firstname</sql> | ORDER BY Name, Firstname</sql> | ||
− | = | + | =User validity viewer= |
− | + | ||
<sql>SELECT | <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> | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | < | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | FROM | + | |
− | LEFT JOIN validity ON | + | |
− | LEFT JOIN | + | |
− | WHERE person.activated=1 | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | ORDER BY | + | |
− | = | + | =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> | |
− | *validityTypeId ( | + | |
− | <sql>SELECT person.first_name, person.last_name, | + | =User validity without expired date= |
− | FROM | + | <sql>SELECT person.first_name, person.last_name, validity_type.name AS 'Validite' |
+ | FROM validity | ||
+ | LEFT JOIN validity_type 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 ( validity_type_id IN ($validityTypeId) | + | WHERE ( validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId) ) |
− | + | AND expire_date IS NULL | |
− | + | AND person.activated=1</sql> | |
− | + | ||
− | + | ||
+ | =User without validity= | ||
<sql>SELECT person.id AS '_tr(ID)', person.first_name AS '_tr(FIRST_NAME)', person.last_name AS '_tr(LAST_NAME)' | <sql>SELECT person.id AS '_tr(ID)', person.first_name AS '_tr(FIRST_NAME)', person.last_name AS '_tr(LAST_NAME)' | ||
FROM person | FROM person | ||
Line 450: | Line 170: | ||
FROM person | FROM person | ||
LEFT JOIN validity ON person.id = validity.person_id | LEFT JOIN validity ON person.id = validity.person_id | ||
− | WHERE validity.validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId) | + | WHERE validity.validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId) |
) | ) | ||
ORDER BY person.last_name, person.first_name</sql> | ORDER BY person.last_name, person.first_name</sql> | ||
− | = | + | =Young from this year= |
− | < | + | <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> | |
− | + | ||
− | + | ||
− | + | ||
− | DATE_FORMAT( | + | |
− | + | ||
− | + | ||
− | + | ||
− | FROM | + | |
− | + | ||
− | + | ||
− | WHERE YEAR( | + | |
− | + |
Revision as of 14:55, 12 April 2018
Contents
- 1 Expired user validity given year
- 2 Expired user validity prior date
- 3 Obtained validity after year
- 4 Obtained validity for year
- 5 User account import
- 6 User coordinate
- 7 User email
- 8 User mail with validity type equal year
- 9 User validity
- 10 User validity ending before date
- 11 User validity superior year
- 12 User validity viewer
- 13 User validity without date
- 14 User validity without expired date
- 15 User without validity
- 16 Young from this year
Expired user validity given year
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 IN ($validityTypeId) OR '-' IN ($validityTypeId) )
Expired user validity prior date
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
Obtained validity after year
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
Obtained validity for year
SELECT last_name AS Last_name, first_name AS First_name, 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' FROM person LEFT JOIN validity ON person_id=person.id 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 ORDER BY last_name, first_name
User account import
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
[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'
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
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
User validity
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
User validity ending before date
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 IN ($validityTypeId) OR '-' IN ($validityTypeId) )
User validity superior year
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 IN ($validityTypeId) OR '-' IN ($validityTypeId) ) AND person.activated=1 ORDER BY Name, Firstname
User validity viewer
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
User validity without date
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
User validity without expired date
SELECT person.first_name, person.last_name, validity_type.name AS 'Validite' 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
User without validity
SELECT person.id AS '_tr(ID)', person.first_name AS '_tr(FIRST_NAME)', person.last_name AS '_tr(LAST_NAME)' FROM person WHERE person.id NOT IN ( SELECT person.id FROM person LEFT JOIN validity ON person.id = validity.person_id WHERE validity.validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId) ) ORDER BY person.last_name, person.first_name
Young from this 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