Difference between revisions of "Users exports 4"

Jump to: navigation, search
(Personal details (with profile X and/or validity Y expiring at a date posterior or equal to Z))
(3 intermediate revisions by the same user not shown)
Line 1: Line 1:
=Activated user list=
+
=Expired user validity given year=
<sql>SELECT id, last_name, first_name
+
<sql>SELECT person.id, person.first_name, person.last_name, validity_type.name AS 'Validity'
 
FROM person
 
FROM person
WHERE activated=1</sql>
+
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) )</sql>
  
=Activated user list with email=
+
=Expired user validity prior date=
<sql>SELECT last_name, first_name, email
+
<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
 
FROM person
WHERE activated=1
+
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>
 +
 
 +
=Obtained validity after year=
 +
<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>
 +
 
 +
=Obtained validity for year=
 +
<sql>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</sql>
 
ORDER BY last_name, first_name</sql>
  
=Personal details (with profile X and/or validity Y expiring at a date posterior or equal to Z)=
+
=User account import=
 
+
<sql>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</sql>
* 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.
+
  
 +
=User coordinate=
 
<sql>[OF_DYNAMIC_SQL]
 
<sql>[OF_DYNAMIC_SQL]
 
SELECT CONCAT(
 
SELECT CONCAT(
     'SELECT person.id AS \'_tr(ID)\', last_name AS \'_tr(LAST_NAME)\', first_name AS \'_tr(FIRST_NAME)\', (SELECT GROUP_CONCAT(name) FROM profile WHERE person.profile & profile.id ORDER BY profile.name) AS \'_tr(PROFILE)\',
+
     '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 \'_tr(EMAIL)\', DATE_FORMAT(birthdate, \'%d/%m/%Y\') AS \'_tr(BIRTHDATE)\', YEAR(birthdate) AS \'_tr(YEAR_OF_BIRTH)\',
+
             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\', ',
            CASE sex
+
                WHEN 0 THEN \'_tr(SEX_MALE_INITIAL)\'
+
                WHEN 1 THEN \'_tr(SEX_FEMALE_INITIAL)\'
+
                ELSE \'\'
+
            END AS \'_tr(SEX)\',
+
            address AS \'_tr(ADDRESS)\', zipcode AS \'_tr(ZIPCODE)\', city AS \'_tr(CITY)\', state AS \'_tr(STATE)\', country AS \'_tr(COUNTRY)\', home_phone AS \'_tr(HOME_PHONE)\', work_phone AS \'_tr(WORK_PHONE)\', cell_phone AS \'_tr(CELL_PHONE)\', ',
+
 
     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.business_field_id=',
+
             ' (SELECT extra_field_content.content FROM extra_field_content WHERE person.id=extra_field_content.category_id AND extra_field_content.extra_field_id=',
             business_field.id,
+
             extra_field.id,
 
             ') AS \'',
 
             ') AS \'',
             REPLACE(business_field.label, '\'', '\\\''),
+
             REPLACE(extra_field.label, '\'', '\\\''),
 
             '\''
 
             '\''
 
         )
 
         )
 
     ), '\'_\''),
 
     ), '\'_\''),
     IF('-' IN ($validityTypeId), '', ', validity_type.name AS \'_tr(VALIDITY)\',
+
     ', IF(grant_date IS NOT NULL, DATE_FORMAT(grant_date, \'%d/%m/%Y\'), \'-\') AS \'Date obtention\',
    IF(grant_date IS NOT NULL, DATE_FORMAT(grant_date, \'%d/%m/%Y\'), \'-\') AS \'_tr(VALIDITY_GRANT_DATE)\',
+
     IF(expire_date IS NOT NULL, DATE_FORMAT(expire_date, \'%d/%m/%Y\'), \'-\') AS \'Limite de validité\',
     IF(expire_date IS NOT NULL, DATE_FORMAT(expire_date, \'%d/%m/%Y\'), \'-\') AS \'_tr(VALIDITY_EXPIRATE_DATE)\',
+
     IF(ident_value IS NOT NULL, ident_value, \'-\') AS \'Code identifiant\'
     IF(ident_value IS NOT NULL, ident_value, \'-\') AS \'_tr(VALIDITY_REGISTRATION)\''), '
+
 
     FROM person
 
     FROM person
 
     LEFT JOIN profile ON (person.profile & profile.id)
 
     LEFT JOIN profile ON (person.profile & profile.id)
Line 44: Line 72:
 
     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 person.activated=1
 
     WHERE person.activated=1
       AND ( profile.id IN (', IF('-' IN ($profileId), '\'-\'', '$profileId'), ') OR \'-\' IN (', IF('-' IN ($profileId), '\'-\'', '$profileId'), ') )
+
       AND ( profile.id IN (', IF('-'=$profileId, '\'-\'', $profileId), ') OR \'-\' IN (', IF('-'=$profileId, '\'-\'', $profileId), ') )
 
       AND (
 
       AND (
           (validity.validity_type_id IN (', IF('-' IN ($validityTypeId), '\'-\'', '$validityTypeId'), ') AND validity_type.time_limitation=1 AND validity.expire_date >= \'$date\')
+
           (validity.validity_type_id IN (', IF('-'=$validityTypeId, '\'-\'', $validityTypeId), ') AND validity_type.time_limitation=1 AND validity.expire_date >= \'$date\')
 
           OR
 
           OR
           \'-\' IN (', IF('-' IN ($validityTypeId), '\'-\'', '$validityTypeId'), ')
+
           \'-\' IN (', IF('-'=$validityTypeId, '\'-\'', $validityTypeId), ')
 
       )
 
       )
     GROUP BY person.id', IF('-' IN ($validityTypeId), '', ', validity_type.id'), '
+
     GROUP BY person.id
 
     ORDER BY last_name, first_name'
 
     ORDER BY last_name, first_name'
 
)
 
)
FROM business_field
+
FROM extra_field
WHERE business_field.category='PERSON'</sql>
+
WHERE extra_field.category='PERSON'</sql>
  
=Under 21 years old on January 1st of year X=
+
=User email=
 
+
<sql>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</sql>
<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>
+
 
+
=User e-mails list=
+
<sql>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</sql>
+
 
+
=User e-mails list of selected validity type person equals given year=
+
Following extra field required:
+
*validityTypeId (Type: dbObject:ValidityType)
+
*year (Type: Year)
+
  
 +
=User mail with validity type equal year=
 
<sql>SELECT CONCAT(person.first_name, ' ', person.last_name, ' <', person.email, '>') AS emails
 
<sql>SELECT CONCAT(person.first_name, ' ', person.last_name, ' <', person.email, '>') AS emails
 
FROM person
 
FROM person
Line 87: Line 98:
 
ORDER BY person.last_name, person.first_name</sql>
 
ORDER BY person.last_name, person.first_name</sql>
  
=User e-mails list with up-to-date expire date of validity VVV=
+
=User validity=
Following symbols should be replace:
+
*VVV : validity type id
+
 
+
<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 validity.expire_date > NOW() AND validity_type.id = VVV
+
ORDER BY last_name,first_name</sql>
+
 
+
=User coordinates with registration date (using ExtraField)=
+
Extra field required :
+
#registrationDate
+
#*label : Registration date
+
#*category : User
+
#*value type : DateTime
+
 
+
Validity type required :
+
#Cotisation
+
 
+
<sql>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</sql>
+
 
+
=User coordinates with registration date (using Validity)=
+
<sql>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</sql>
+
 
+
=User list by profile=
+
 
+
Variable $profile should be defined first and should be of dbOject::Profile value type.
+
 
+
<sql>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</sql>
+
 
+
=User list ordered by member number=
+
<sql>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)</sql>
+
 
+
=User list with validities viewer=
+
<sql>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</sql>
+
 
+
=User list with age and birth date=
+
<sql>(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')
+
)</sql>
+
 
+
 
+
=User list with registration date, profiles and total flight time=
+
<sql>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</sql>
+
 
+
=Users by validity=
+
Following extra field required:
+
*validityTypeId (Type: dbObjectMulti::ValidityType)
+
 
+
 
<sql>SELECT validity_type.name AS 'Validity',
 
<sql>SELECT validity_type.name AS 'Validity',
 
     DATE_FORMAT(grant_date,'%d/%m/%Y') AS Date_d_obtention,
 
     DATE_FORMAT(grant_date,'%d/%m/%Y') AS Date_d_obtention,
Line 338: Line 111:
 
ORDER BY Name, Firstname</sql>
 
ORDER BY Name, Firstname</sql>
  
=Users having validity X and grant date greater or equal to year Y=
 
Following extra field required:
 
*validityTypeId (Type: dbObjectMulti::ValidityType)
 
*year (Type: Year)
 
  
 +
=User validity ending before date=
 +
<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'
 +
  AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )</sql>
 +
 +
=User validity superior year=
 
<sql>SELECT validity_type.name AS 'Validity',
 
<sql>SELECT validity_type.name AS 'Validity',
 
     DATE_FORMAT(grant_date, '%d/%m/%Y') AS grant_date,
 
     DATE_FORMAT(grant_date, '%d/%m/%Y') AS grant_date,
Line 359: Line 137:
 
ORDER BY Name, Firstname</sql>
 
ORDER BY Name, Firstname</sql>
  
=Users with address=
+
=User validity viewer=
 
+
 
<sql>SELECT  
 
<sql>SELECT  
last_name,  
+
      last_name AS "Last name",
first_name,
+
      first_name AS "First name",
name AS login,  
+
      validity_type.name AS "Validity",
email,
+
      DATE_FORMAT(expire_date,'%d/%m/%Y') AS "Expiration date",
address,
+
      IF((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), "Expired","") AS "Expired",
zipcode,
+
      DATE_FORMAT(grant_date,'%d/%m/%Y') AS "Obtention date"
city AS Ville,
+
  FROM validity_type
state AS etat_region,
+
  LEFT JOIN validity ON validity.validity_type_id = validity_type.id
country AS pays,
+
  LEFT JOIN person ON person.id=validity.person_id
home_phone AS tel_domicile,
+
WHERE person.activated = 1
work_phone AS tel_travail,
+
ORDER BY Last_name, First_name, validity_type.name</sql>
cell_phone AS tel_mobile
+
FROM person
+
WHERE activated=1
+
ORDER BY last_name,first_name
+
ORDER BY last_name,first_name</sql>
+
 
+
=Users with expire date validity equals a given year=
+
Following extra field required:
+
*validityTypeId (Type: dbObject:ValidityType)
+
*year (Type: Year)
+
 
+
<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 YEAR(validity.expire_date) = $year
+
    AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )</sql>
+
 
+
=Users with validity ending before a specific date=
+
 
+
*endDate (Type: Date)
+
*validityTypeId (Type: dbObject:ValidityType)
+
 
+
<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'
+
  AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )</sql>
+
 
+
=Users with validity expired date prior to 01/month/year=
+
Following extra field required:
+
*validityTypeId (Type: dbObjectMulti::ValidityType)
+
*month (Type: Month)
+
*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 validity without date=
+
=User validity without date=
Following extra field required:
+
<sql>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</sql>
*validityTypeId (Type: dbObjectMulti::ValidityType)
+
  
<sql>SELECT person.first_name, person.last_name, validity.*
+
=User validity without expired date=
FROM `validity`
+
<sql>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
 
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</sql>
+
WHERE ( validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
 
+
  AND expire_date IS NULL
=Users who have never owned at least one of the selected validity=
+
  AND person.activated=1</sql>
 
+
*Variable '''$validityTypeId''' should be of '''dbObjectMulti::ValidityType''' value type.
+
  
 +
=User without validity=
 
<sql>SELECT person.id AS '_tr(ID)', person.first_name AS '_tr(FIRST_NAME)', person.last_name AS '_tr(LAST_NAME)'
 
<sql>SELECT person.id AS '_tr(ID)', person.first_name AS '_tr(FIRST_NAME)', person.last_name AS '_tr(LAST_NAME)'
 
FROM person
 
FROM person
Line 450: Line 170:
 
FROM person
 
FROM person
 
LEFT JOIN validity ON person.id = validity.person_id
 
LEFT JOIN validity ON person.id = validity.person_id
WHERE validity.validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId)
+
WHERE validity.validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId)
 
)
 
)
 
ORDER BY person.last_name, person.first_name</sql>
 
ORDER BY person.last_name, person.first_name</sql>
  
=User list without an account=
+
=Young from this year=
<SQL>SELECT person.last_name, person.first_name FROM person
+
<sql>SELECT  
LEFT JOIN account ON account.owner_id=person.id
+
    last_name AS Last_name,
LEFT JOIN account_type ON account.account_type=account_type.id
+
    first_name AS First_name,  
WHERE person.activated=1 AND account.category=2 AND account_type.activated=1 AND account.activated=0</SQL>
+
     DATE_FORMAT(birthdate ,'%m-%d-%Y') AS Birthdate,  
 
+
     IF ( sex = 0, 'Male', 'Female' ) AS sex
=Validities (all) obtained after the selected year=
+
FROM person
 
+
WHERE ($year-YEAR(birthdate))<=21 AND activated=1</sql>
* 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>
+

Revision as of 14:55, 12 April 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)
)
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