Users exports 4
Revision as of 15:31, 24 January 2017 by Jcheng (Talk | contribs) (→Active users without required validities)
Contents
- 1 Personal details (with profile X and/or validity Y expiring at a date posterior or equal to Z)
- 2 Users list with membership ending before a specific date
- 3 Activated user list
- 4 Activated user list with email
- 5 Young user list
- 6 User e-mails list
- 7 User e-mails list of selected validity type person equals given year
- 8 User e-mails list with up-to-date expire date of validity VVV
- 9 User list coordinates
- 10 User coordinates with registration date (using ExtraField)
- 11 User coordinates with registration date (using Validity)
- 12 User list by profile
- 13 User list by validity
- 14 User list by validity obtained after the selected year
- 15 User list with validities viewer
- 16 User list with age and birth date
- 17 User list ordered by member number
- 18 User list with validity grant date and profiles
- 19 User list with registration date, profiles and total flight time
- 20 User list with expire date validity equals a given year
- 21 User list without up to date subscription
- 22 User list without an account
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 business_field_content.content FROM business_field_content WHERE person.id=business_field_content.category_id AND business_field_content.extra_field_id=', business_field.id, ') AS \'', REPLACE(business_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 business_field WHERE business_field.category='PERSON'
Users list with membership 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'
Activated user list
SELECT id, last_name, first_name FROM person WHERE activated=1
Activated user list with email
SELECT last_name, first_name, email FROM person WHERE activated=1 ORDER BY last_name, first_name
Young user list
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
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
User e-mails 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 = '$validityTypeId' ORDER BY person.last_name, person.first_name
User e-mails list 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
User list coordinates
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
User coordinates with 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
User coordinates with 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 list by profile
Variable $profile should be defined first and should be of dbOject::Profile value type.
SELECT last_name AS Lastname, first_name AS Firstname, profile.name AS Profile FROM person LEFT JOIN profile ON (person.profile & profile.id) WHERE person.activated = 1 AND profile.id = $profile ORDER BY Profile, Lastname, Firstname
User list 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
User list by validity 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 ORDER BY 'Validity', Name, Firstname
User list 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 Expiry_date, if((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), "Expired","") AS Expired, -- ident_value AS Commentaire, DATE_FORMAT(grant_date,'%d/%m/%Y') AS Grant_date FROM validity_type LEFT JOIN validity ON validity.validity_type_id =validity_type.id LEFT JOIN person ON person.id=validity.person_id -- Validity recently expired or about to expire WHERE period_diff(DATE_FORMAT(expire_date,'%y%m'), DATE_FORMAT(Now(),'%y%m')) < 2 AND -- period_diff(DATE_FORMAT(expire_date,'%y%m'), -- DATE_FORMAT(Now(),'%y%m')) > -2 AND -- Deactivated members person.activated = 1 -- Exclude Night qualification AND validity_type.id <> 6 ORDER BY Last_name, First_name, validity_type.name
User list 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') )
User list ordered by member number
SELECT efc.content AS numero, last_name AS Nom, first_name AS prénom, name AS login, email, address AS adresse, zipcode AS code_postal, city AS Ville, state AS etat_region, country AS pays, home_phone AS tel_dommicile, work_phone AS tel_travail, cell_phone AS tel_mobile, activated AS actif FROM person LEFT JOIN extra_field_content AS efc ON person.id = efc.category_id LEFT JOIN extra_field AS ef ON efc.extra_field_id = ef.id WHERE ef.variable = 'memberNum' AND person.activated = 1 ORDER BY CAST(efc.content AS SIGNED)
User list with validity grant date and profiles
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
User list 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
User list 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'
User list without up to date subscription
Following extra field required:
- validityTypeId (Type: dbObject:ValidityType)
- year (Type: Year)
SELECT last_name AS Nom, first_name AS prénom, email, home_phone AS tel_dommicile, work_phone AS tel_travail, cell_phone AS tel_mobile, DATE_FORMAT(validity.expire_date,'%d/%m/%Y') AS date_cotisation 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 last_name,first_name
User list 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