Claratte : /* Users with validities viewer */

2016-02-14T22:03:03Z

‎Users with validities viewer

Claratte : Page créée avec « =Users= SELECT id, last_name, first_name FROM person WHERE activated=1 =Users and = SELECT CONCAT(person.first_name, ' ', person.last_name, ' <',... »

2016-02-14T22:02:05Z

Page créée avec « =Users= <sql>SELECT id, last_name, first_name FROM person WHERE activated=1</sql> =Users and <e-mail>= <sql>SELECT CONCAT(person.first_name, ' ', person.last_name, ' <',... »

Nouvelle page

=Users=
<sql>SELECT id, last_name, first_name
FROM person
WHERE activated=1</sql>

=Users and <e-mail>=
<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>

=Users 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>

=Users by validity=
Following extra field required:
*validityTypeId (Type: dbObject:ValidityType)

<sql>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</sql>

=Users by validity obtained after the selected year=

* 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
ORDER BY 'Validity', Name, Firstname</sql>

=Users with address=
<sql>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</sql>

=Users with address and 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>

=Users with address and 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>

=Users with e-mails of selected validity type person equals given year=
Following extra field required:
*validityTypeId (Type: dbObject:ValidityType)
*year (Type: Year)

<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 YEAR(validity.expire_date) = $year AND validity_type.id = '$validityTypeId'
ORDER BY person.last_name, person.first_name</sql>

=Users with e-mails with up-to-date expire date of validity VVV=
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>

=Users with validity ending before a specific date=
Following extra field required:
*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 = '$validityTypeId'</sql>

=Users with up-to-date validity X and Y and user details=
Replace X and Y by the validity_type id (2 times).
<sql>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</sql>

=Users 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 "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</sql>

=Users 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>

=Users with validity grant date and profiles=
Following extra field required:
*validityTypeId (Type: dbObject:ValidityType)

<sql>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</sql>

=Users 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 with email=
<sql>SELECT last_name, first_name, email
FROM person
WHERE activated=1
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 = '$validityTypeId'</sql>

=Users with expire date validities inferior to the first day of the given month and year=

Following extra field required:
* month (Type: integer)
* year (Type: year)

<sql>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.activated=1
ORDER BY last_name, first_name, validity_type.name</sql>

=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

<sql>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</sql>

=Users without required validities=
<sql>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</sql>

=Users without up to date subscription=
Following extra field required:
*validityTypeId (Type: dbObject:ValidityType)
*year (Type: Year)

<sql>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</sql>

=Users without an account=
<SQL>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</SQL>

=Young users=
<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</SQL>

@@ Ligne 176 : / Ligne 176 : @@
   ORDER BY last_name, first_name, validity_type.name</sql>
-=Users with validities viewer=
+=Utilisateurs avec leurs validités=
 <sql>SELECT
-       last_name AS "Last name",
+       last_name AS "Nom",
-       first_name AS "First name",
+       first_name AS "Prenom",
-       validity_type.name AS "Validity",
+       validity_type.name AS "Validité",
-       DATE_FORMAT(expire_date,'%d/%m/%Y') AS "Expiration date",
+       DATE_FORMAT(expire_date,'%d/%m/%Y') AS "Date expiration",
-       IF((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), "Expired","") AS "Expired",
+       IF((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), "Expired","") AS "Perimee",
-       DATE_FORMAT(grant_date,'%d/%m/%Y') AS "Obtention date"
+       DATE_FORMAT(grant_date,'%d/%m/%Y') AS "Date obtention"
     FROM validity_type
     LEFT JOIN validity ON validity.validity_type_id = validity_type.id

Bibliothèque des rapports - Historique des versions

Claratte : /* Users with validities viewer */

Claratte : Page créée avec « =Users= SELECT id, last_name, first_name FROM person WHERE activated=1 =Users and = SELECT CONCAT(person.first_name, ' ', person.last_name, ' <',... »