Difference between revisions of "Users reports 3"
(→Young users) |
(→Validities (all) obtained after the selected year) |
||
Line 71: | Line 71: | ||
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 YEAR(grant_date)>$year | + | WHERE YEAR(grant_date)>$year AND person.activated=1 |
ORDER BY Validity, Name, Firstname</sql> | ORDER BY Validity, Name, Firstname</sql> | ||
Revision as of 18:07, 15 February 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 Users with e-mails 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 expire date validities inferior to the first day of the given month and year, with up-to-date expire date of validity XX
- 19 Users without required validities
- 20 Users with validity expired date prior to 01/month/year
- 21 Users without an account
- 22 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(\'-\'<>\'$validityTypeId\' AND grant_date IS NOT NULL, DATE_FORMAT(grant_date, \'%d/%m/%Y\'), \'-\') AS \'Date obtention\', IF(\'-\'<>\'$validityTypeId\' AND expire_date IS NOT NULL, DATE_FORMAT(expire_date, \'%d/%m/%Y\'), \'-\') AS \'Limite de validité\', IF(\'-\'<>\'$validityTypeId\' AND 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=\'$profileId\' OR \'-\'=\'$profileId\') AND ( ( validity.validity_type_id=\'$validityTypeId\' AND validity_type.time_limitation=1 AND validity.expire_date >= \'$date\' ) OR \'-\'=\'$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='$validityTypeId' 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
Users with e-mails 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 = '$validityTypeId' 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' 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 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 required validities
SELECT CONCAT(person.last_name, ' ', person.first_name) AS pilot, validity_type.name AS validity_name FROM flight_type_mandatory_validity_type LEFT JOIN flight_type ON flight_type.id = flight_type_mandatory_validity_type.flight_type_id LEFT JOIN flight ON flight.flight_type_id & flight_type.id LEFT JOIN resource ON resource.id = flight.aircraft_id LEFT JOIN aircraft_type ON resource.resource_type_id = aircraft_type.id LEFT JOIN aircraft_type_validity_type ON aircraft_type.id = aircraft_type_validity_type.aircraft_type_id LEFT JOIN flight_pilot ON flight.id = flight_pilot.flight_id LEFT JOIN person ON person.id = flight_pilot.pilot_id LEFT JOIN validity_type ON (validity_type.id = flight_type_mandatory_validity_type.validity_type_id OR validity_type.id = aircraft_type_validity_type.validity_type_id) WHERE flight.airborne = 0 AND ROW(person.id, validity_type.id) NOT IN (SELECT person_id, validity_type_id FROM validity) AND validity_type.experience_formula IS NULL GROUP BY person.id, validity_type.id ORDER BY pilot, validity_name
Users with validity expired date prior to 01/month/year
Following extra field required:
- validityTypeId (Type: dbObject:ValidityType)
- month (Type: Integer)
- year (Type: Year)
SELECT last_name AS Nom, first_name AS prénom, email, home_phone AS tel_domicile, work_phone AS tel_travail, cell_phone AS tel_mobile, DATE_FORMAT(validity.expire_date,'%d/%m/%Y') AS date_echeance 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 < '$year-$month-01' AND validity_type.id = '$validityTypeId' ORDER BY last_name,first_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