Difference between revisions of "Users exports 4"

Jump to: navigation, search
(User without validity)
Line 172: Line 172:
 
WHERE validity.validity_type_id IN ($validityTypeId)  OR '-' IN ($validityTypeId)
 
WHERE validity.validity_type_id IN ($validityTypeId)  OR '-' IN ($validityTypeId)
 
)
 
)
 +
AND person.activated = 1
 
ORDER BY person.last_name, person.first_name</sql>
 
ORDER BY person.last_name, person.first_name</sql>
  

Revision as of 09:14, 27 November 2018

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