Difference between revisions of "Users reports 3"
m (→User list with expire date validity inferior to the first day of given month and year) |
(→Users with validity expired date prior to 01/month/year) |
||
(55 intermediate revisions by 3 users not shown) | |||
Line 1: | Line 1: | ||
− | = | + | =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. |
− | *validityTypeId | + | |
− | <sql>SELECT person.id, person. | + | <sql>[OF_DYNAMIC_SQL] |
− | FROM person | + | SELECT CONCAT( |
− | LEFT JOIN | + | '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, |
− | LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) | + | 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\', ', |
− | WHERE person.activated=1 | + | 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= | =User e-mails list= | ||
Line 54: | Line 44: | ||
ORDER BY person.last_name, person.first_name</sql> | ORDER BY person.last_name, person.first_name</sql> | ||
− | = | + | =Users by validity= |
Following extra field required: | Following extra field required: | ||
*validityTypeId (Type: dbObject:ValidityType) | *validityTypeId (Type: dbObject:ValidityType) | ||
− | |||
− | <sql>SELECT | + | <sql>SELECT validity_type.name AS 'Validity', |
− | FROM | + | DATE_FORMAT(grant_date,'%d/%m/%Y') AS Date_d_obtention, |
− | LEFT JOIN validity ON | + | last_name AS Name, |
− | LEFT JOIN | + | first_name AS Firstname, |
− | WHERE | + | 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. | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | = | + | <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= |
+ | <sql>SELECT | ||
last_name, | last_name, | ||
first_name, | first_name, | ||
Line 94: | Line 91: | ||
FROM person | FROM person | ||
WHERE activated=1 | WHERE activated=1 | ||
− | ORDER BY last_name,first_name</ | + | ORDER BY last_name,first_name</sql> |
− | = | + | =Users with address and registration date (using ExtraField)= |
Extra field required : | Extra field required : | ||
#registrationDate | #registrationDate | ||
Line 134: | Line 131: | ||
ORDER BY last_name, first_name</sql> | ORDER BY last_name, first_name</sql> | ||
− | = | + | =Users with address and registration date (using Validity)= |
<sql>SELECT | <sql>SELECT | ||
person.last_name, | person.last_name, | ||
Line 145: | Line 142: | ||
GROUP BY last_name, first_name</sql> | GROUP BY last_name, first_name</sql> | ||
− | =User list | + | =User emails list of selected validity type person equals given year= |
+ | Following extra field required: | ||
+ | *validityTypeId (Type: dbObject:ValidityType) | ||
+ | *year (Type: Year) | ||
− | + | <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> | ||
− | <sql>SELECT | + | =Users with e-mails with up-to-date expire date of validity VVV= |
− | + | Following symbols should be replace: | |
− | + | *VVV : validity type id | |
− | + | ||
+ | <sql>SELECT CONCAT(person.first_name, ' ', person.last_name, ' <', person.email, '>') AS emails | ||
FROM person | FROM person | ||
− | LEFT JOIN | + | LEFT JOIN validity ON (validity.person_id = person.id) |
− | WHERE person.activated = 1 AND | + | LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) |
− | ORDER BY | + | 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: | Following extra field required: | ||
+ | *endDate (Type: Date) | ||
*validityTypeId (Type: dbObject:ValidityType) | *validityTypeId (Type: dbObject:ValidityType) | ||
− | <sql>SELECT validity_type.name AS 'Validity' | + | <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' | |
− | FROM | + | AND validity_type.id = '$validityTypeId'</sql> |
− | LEFT JOIN validity ON validity. | + | |
− | LEFT JOIN | + | |
− | WHERE validity_type.id='$validityTypeId' | + | |
− | + | ||
− | = | + | =Users with up-to-date validity X and Y and user details= |
+ | Replace X and Y by the validity_type id (2 times). | ||
+ | <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= | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
<sql>SELECT | <sql>SELECT | ||
− | last_name AS | + | last_name AS "Last name", |
− | first_name AS | + | first_name AS "First name", |
− | validity_type.name AS | + | validity_type.name AS "Validity", |
− | DATE_FORMAT(expire_date,'%d/%m/%Y') AS | + | 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" | |
− | DATE_FORMAT(grant_date,'%d/%m/%Y') AS | + | |
− | + | ||
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 |
LEFT JOIN person ON person.id=validity.person_id | LEFT JOIN person ON person.id=validity.person_id | ||
− | + | WHERE person.activated = 1 | |
− | WHERE | + | ORDER BY Last_name, First_name, validity_type.name</sql> |
− | + | ||
− | + | ||
− | person.activated = 1 | + | |
− | + | ||
− | + | ||
− | + | ||
− | = | + | =Users with age and birth date= |
<sql>(SELECT last_name AS Last_name, | <sql>(SELECT last_name AS Last_name, | ||
Line 303: | Line 309: | ||
)</sql> | )</sql> | ||
− | + | =Users having validity X and grant date greater or equal to year Y= | |
− | = | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
Following extra field required: | Following extra field required: | ||
*validityTypeId (Type: dbObject:ValidityType) | *validityTypeId (Type: dbObject:ValidityType) | ||
Line 342: | Line 325: | ||
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='$validityTypeId' | + | AND validity_type.id='$validityTypeId' |
+ | AND person.activated=1 | ||
ORDER BY Name, Firstname</sql> | ORDER BY Name, Firstname</sql> | ||
− | = | + | =Users with registration date, profiles and total flight time= |
<sql>SELECT | <sql>SELECT | ||
person.last_name, | person.last_name, | ||
Line 369: | Line 353: | ||
GROUP BY last_name, first_name</sql> | GROUP BY last_name, first_name</sql> | ||
− | = | + | =Users with email= |
+ | <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: | Following extra field required: | ||
*validityTypeId (Type: dbObject:ValidityType) | *validityTypeId (Type: dbObject:ValidityType) | ||
Line 380: | Line 370: | ||
WHERE person.activated=1 AND YEAR(validity.expire_date) = $year AND validity_type.id = '$validityTypeId'</sql> | 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) | ||
+ | |||
+ | <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: | Following extra field required: | ||
* month (Type: integer) | * month (Type: integer) | ||
* year (Type: year) | * year (Type: year) | ||
− | + | Following symbols should be replace: | |
+ | * XX : validity type id | ||
<sql>SELECT CONCAT(last_name, ' ', first_name) AS 'User', validity_type.name AS 'Validity name', expire_date AS 'Expire date' | <sql>SELECT CONCAT(last_name, ' ', first_name) AS 'User', validity_type.name AS 'Validity name', expire_date AS 'Expire date' | ||
Line 392: | Line 409: | ||
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 validity_type.time_limitation=1 | 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> | ORDER BY last_name, first_name, validity_type.name</sql> | ||
− | = | + | =Users without an account= |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
<SQL>SELECT person.last_name, person.first_name FROM person | <SQL>SELECT person.last_name, person.first_name FROM person | ||
LEFT JOIN account ON account.owner_id=person.id | LEFT JOIN account ON account.owner_id=person.id | ||
LEFT JOIN account_type ON account.account_type=account_type.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> | WHERE person.activated=1 AND account.category=2 AND account_type.activated=1 AND account.activated=0</SQL> | ||
+ | |||
+ | =Young users= | ||
+ | |||
+ | <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> |
Latest revision as of 17:31, 26 December 2017
Contents
- 1 Personal details (with profile X and/or validity Y expiring at a date posterior or equal to Z)
- 2 User e-mails list
- 3 Users by validity
- 4 Validities (all) obtained after the selected year
- 5 Users with address
- 6 Users with address and registration date (using ExtraField)
- 7 Users with address and registration date (using Validity)
- 8 User emails list of selected validity type person equals given year
- 9 Users with e-mails with up-to-date expire date of validity VVV
- 10 Users with validity ending before a specific date
- 11 Users with up-to-date validity X and Y and user details
- 12 Users with validities viewer
- 13 Users with age and birth date
- 14 Users having validity X and grant date greater or equal to year Y
- 15 Users with registration date, profiles and total flight time
- 16 Users with email
- 17 Users with expire date validity equals a given year
- 18 Users with validity expired date prior to 01/month/year
- 19 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
- 20 Users without an account
- 21 Young users
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.
[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 e-mails list
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
Users by validity
Following extra field required:
- validityTypeId (Type: dbObject:ValidityType)
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
Validities (all) obtained after the selected year
- Variable $year should be defined first and should be of Year value type.
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
Users with address
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
Users with address and registration date (using ExtraField)
Extra field required :
- registrationDate
- label : Registration date
- category : User
- value type : DateTime
Validity type required :
- Cotisation
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
Users with address and registration date (using Validity)
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
User emails list of selected validity type person equals given year
Following extra field required:
- validityTypeId (Type: dbObject:ValidityType)
- year (Type: 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
Users with e-mails with up-to-date expire date of validity VVV
Following symbols should be replace:
- VVV : validity type id
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
Users with validity ending before a specific date
Following extra field required:
- endDate (Type: Date)
- validityTypeId (Type: dbObject:ValidityType)
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'
Users with up-to-date validity X and Y and user details
Replace X and Y by the validity_type id (2 times).
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
Users with validities 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
Users with age and birth date
(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') )
Users having validity X and grant date greater or equal to year Y
Following extra field required:
- validityTypeId (Type: dbObject:ValidityType)
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
Users with registration date, profiles and total flight time
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
Users with email
SELECT last_name, first_name, email FROM person WHERE activated=1 ORDER BY last_name, first_name
Users with expire date validity equals a given year
Following extra field required:
- validityTypeId (Type: dbObject:ValidityType)
- year (Type: 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 = '$validityTypeId'
Users with validity expired date prior to 01/month/year
Following extra field required:
- validityTypeId (Type: dbObject::ValidityType)
- year (Type: Year)
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
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
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
Users without an account
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
Young users
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