Difference between revisions of "Users exports 4"

Jump to: navigation, search
(Users with validity expired date prior to 01/month/year)
(Personal details (with profile X and/or validity Y expiring at a date posterior or equal to Z))
Line 11: Line 11:
 
     IFNULL(GROUP_CONCAT(
 
     IFNULL(GROUP_CONCAT(
 
         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=',
+
             ' (SELECT business_field_content.content FROM business_field_content WHERE person.id=business_field_content.category_id AND business_field_content.business_field_id=',
 
             business_field.id,
 
             business_field.id,
 
             ') AS \'',
 
             ') AS \'',

Revision as of 14:12, 7 November 2017

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 dbObjectMulti::Profile value type.
  • Variable $validityTypeId should be defined first and should be of dbObjectMulti::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.business_field_id=',
            business_field.id,
            ') AS \'',
            REPLACE(business_field.label, '\'', '\\\''),
            '\''
        )
    ), '\'_\''),
    ', ', IF('-' IN ($validityTypeId), '', 'validity_type.name AS \'Validité\','),'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('-' IN ($profileId), '\'-\'', '$profileId'), ') OR \'-\' IN (', IF('-' IN ($profileId), '\'-\'', '$profileId'), ') )
      AND (
          (validity.validity_type_id IN (', IF('-' IN ($validityTypeId), '\'-\'', '$validityTypeId'), ') AND validity_type.time_limitation=1 AND validity.expire_date >= \'$date\')
          OR
          \'-\' IN (', IF('-' IN ($validityTypeId), '\'-\'', '$validityTypeId'), ')
      )
    GROUP BY person.id', IF('-' IN ($validityTypeId), '', ', validity_type.id'), '
    ORDER BY last_name, first_name'
)
FROM business_field
WHERE business_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: dbObjectMulti::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
ORDER BY last_name,first_name

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 AND activated=1

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 IN ($validityTypeId) OR '-' IN ($validityTypeId) )
GROUP BY person.id
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 coordinates with registration date (using ExtraField)

Extra field required :

  1. registrationDate
    • label : Registration date
    • category : User
    • value type : DateTime

Validity type required :

  1. 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 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)

Users having validity X and grant date greater or equal to year Y

Following extra field required:

  • validityTypeId (Type: dbObjectMulti::ValidityType)
  • year (Type: 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 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

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 IN ($validityTypeId) OR '-' IN ($validityTypeId) )

Users with validity ending before a specific date

  • 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 IN ($validityTypeId) OR '-' IN ($validityTypeId) )

Users with validity expired date prior to 01/month/year

Following extra field required:

  • validityTypeId (Type: dbObjectMulti::ValidityType)
  • month (Type: Month)
  • 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,
    validity_type.name AS 'Validité',
    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 IN ($validityTypeId) OR '-' IN ($validityTypeId) )
ORDER BY last_name, first_name, validity_type.name

Users with validity without date

Following extra field required:

  • validityTypeId (Type: dbObjectMulti::ValidityType)
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 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