Users exports 4 - Revision history

Claratte at 08:09, 9 October 2024

2024-10-09T08:09:13Z

← Older revision		Revision as of 10:09, 9 October 2024
Line 13:		Line 13:
	WHERE validity_type.activated = 1		WHERE validity_type.activated = 1
	AND YEAR(validity.expire_date) = $year		AND YEAR(validity.expire_date) = $year
	AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )</~~sql~~>		AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )</syntaxhighlight>

	=Expired user validity prior date=		=Expired user validity prior date=
Line 40:		Line 40:
	( '-' IN ($validityTypeId) AND validity_type.time_limitation=1 )		( '-' IN ($validityTypeId) AND validity_type.time_limitation=1 )
	)		)
	ORDER BY `_tr(FULL_NAME)`, validity_type.name</~~sql~~>		ORDER BY `_tr(FULL_NAME)`, validity_type.name</syntaxhighlight>

	=Obtained validity after year=		=Obtained validity after year=
Line 57:		Line 57:
	AND person.activated=1		AND person.activated=1
	AND validity_type.activated = 1		AND validity_type.activated = 1
	ORDER BY validity_type.name, `_tr(FULL_NAME)`</~~sql~~>		ORDER BY validity_type.name, `_tr(FULL_NAME)`</syntaxhighlight>

	=Obtained validity for year=		=Obtained validity for year=
Line 77:		Line 77:
	AND person.activated=1		AND person.activated=1
	AND validity_type.activated = 1		AND validity_type.activated = 1
	ORDER BY `_tr(FULL_NAME)`</~~sql~~>		ORDER BY `_tr(FULL_NAME)`</syntaxhighlight>

	=User account import=		=User account import=
Line 83:		Line 83:
	*Variable '''$endDate'''		*Variable '''$endDate'''

	<syntaxhighlight lang="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~~>		<syntaxhighlight lang="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</syntaxhighlight>

	=User coordinate=		=User coordinate=
Line 130:		Line 130:
	)		)
	FROM business_field		FROM business_field
	WHERE business_field.category='PERSON'</~~sql~~>		WHERE business_field.category='PERSON'</syntaxhighlight>

	=User email=		=User email=
	<syntaxhighlight lang="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~~>		<syntaxhighlight lang="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</syntaxhighlight>

	=User mail with validity type equal year=		=User mail with validity type equal year=
Line 151:		Line 151:
	AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )		AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
	GROUP BY person.id		GROUP BY person.id
	ORDER BY `_tr(FULL_NAME)`</~~sql~~>		ORDER BY `_tr(FULL_NAME)`</syntaxhighlight>

	=User validity=		=User validity=
Line 170:		Line 170:
	AND person.activated = 1		AND person.activated = 1
	AND validity_type.activated = 1		AND validity_type.activated = 1
	ORDER BY validity_type.name, `_tr(FULL_NAME)`</~~sql~~>		ORDER BY validity_type.name, `_tr(FULL_NAME)`</syntaxhighlight>

	=User validity ending before date=		=User validity ending before date=
Line 187:		Line 187:
	AND validity_type.activated = 1		AND validity_type.activated = 1
	AND validity.grant_date <= $endDate		AND validity.grant_date <= $endDate
	AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )</~~sql~~>		AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )</syntaxhighlight>

	=User validity superior year=		=User validity superior year=
Line 211:		Line 211:
	AND person.activated = 1		AND person.activated = 1
	AND validity_type.activated = 1		AND validity_type.activated = 1
	ORDER BY `_tr(FULL_NAME)`</~~sql~~>		ORDER BY `_tr(FULL_NAME)`</syntaxhighlight>

	=User validity viewer=		=User validity viewer=
Line 225:		Line 225:
	WHERE person.activated = 1		WHERE person.activated = 1
	AND validity_type.activated = 1		AND validity_type.activated = 1
	ORDER BY `_tr(FULL_NAME)`, validity_type.name</~~sql~~>		ORDER BY `_tr(FULL_NAME)`, validity_type.name</syntaxhighlight>

	=User validity without date=		=User validity without date=
Line 241:		Line 241:
	AND person.activated = 1		AND person.activated = 1
	AND validity_type.activated = 1		AND validity_type.activated = 1
	AND validity.is_current_validity = 1</~~sql~~>		AND validity.is_current_validity = 1</syntaxhighlight>

	=User validity without expired date=		=User validity without expired date=
Line 257:		Line 257:
	AND person.activated = 1		AND person.activated = 1
	AND validity_type.activated = 1		AND validity_type.activated = 1
	AND validity.is_current_validity = 1</~~sql~~>		AND validity.is_current_validity = 1</syntaxhighlight>

	=User without validity=		=User without validity=
Line 274:		Line 274:
	)		)
	AND person.activated = 1		AND person.activated = 1
	ORDER BY `_tr(FULL_NAME)`</~~sql~~>		ORDER BY `_tr(FULL_NAME)`</syntaxhighlight>

	=Validity year young specific profile=		=Validity year young specific profile=
Line 299:		Line 299:
	AND validity_type.activated = 1		AND validity_type.activated = 1
	GROUP BY validity_type.id		GROUP BY validity_type.id
	ORDER BY validity_type.name</~~sql~~>		ORDER BY validity_type.name</syntaxhighlight>

	=Young from this year=		=Young from this year=
Line 311:		Line 311:
	IF ( sex = 0, 'Male', 'Female' ) AS sex		IF ( sex = 0, 'Male', 'Female' ) AS sex
	FROM person		FROM person
	WHERE ($year-YEAR(birthdate))<=21 AND activated=1</~~sql~~>		WHERE ($year-YEAR(birthdate))<=21 AND activated=1</syntaxhighlight>

imported>Claratte: Text replacement - "" to ""

2024-08-12T18:53:06Z

Text replacement - "<sql>" to "<syntaxhighlight lang="sql">"

New page

=Expired user validity given year=

*Variable '''$validityTypeId'''
*Variable '''$year'''

<syntaxhighlight lang="sql">SELECT
person.id AS _tr(ID),
CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
validity_type.name AS _tr(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 validity_type.activated = 1
AND YEAR(validity.expire_date) = $year
AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )</sql>

=Expired user validity prior date=

*Variable '''$mount'''
*Variable '''$validityTypeId'''
*Variable '''$year'''

<syntaxhighlight lang="sql">SELECT
CONCAT(last_name, ' ', first_name) AS _tr(FULL_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 AND validity.is_current_validity = 1
LEFT JOIN validity_type ON (validity.validity_type_id = validity_type.id)
WHERE person.activated = 1
AND validity_type.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 `_tr(FULL_NAME)`, validity_type.name</sql>

=Obtained validity after year=

*Variable '''$year'''

<syntaxhighlight lang="sql">SELECT
validity_type.name AS _tr(VALIDITY),
DATE_FORMAT(grant_date,'%d/%m/%Y') AS _tr(DATE),
CONCAT(last_name, ' ', first_name) AS _tr(FULL_NAME),
ident_value AS _tr(VALIDITY_REGISTRATION)
FROM validity_type
LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1
LEFT JOIN person ON person.id=validity.person_id
WHERE YEAR(grant_date)>$year
AND person.activated=1
AND validity_type.activated = 1
ORDER BY validity_type.name, `_tr(FULL_NAME)`</sql>

=Obtained validity for year=

*Variable '''$validityTypeId'''
*Variable '''$year'''

<syntaxhighlight lang="sql">SELECT
CONCAT(last_name, ' ', first_name) AS _tr(FULL_NAME),
IF( ( $year - YEAR( birthdate ) >= 21) , _tr(NO), _tr(YES)) AS _tr(YOUNG),
IF( person.sex=0, _tr(SEX_MALE_INITIAL), IF( person.sex=1, _tr(SEX_FEMALE_INITIAL), _tr(UNDEFINED)) ) AS _tr(SEX),
DATE_FORMAT( grant_date, '%d/%m/%Y' ) AS _tr(DATE),
validity_type.name AS _tr(VALIDITY)
FROM person
LEFT JOIN validity ON person_id=person.id AND validity.is_current_validity = 1
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
AND validity_type.activated = 1
ORDER BY `_tr(FULL_NAME)`</sql>

=User account import=

*Variable '''$endDate'''

<syntaxhighlight lang="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>

=User coordinate=

*Variable '''$date'''
*Variable '''$profileId'''
*Variable '''$validityTypeId'''

<syntaxhighlight lang="sql">[OF_DYNAMIC_SQL]
SELECT CONCAT(
'SELECT person.id AS \'_tr(ID)\', CONCAT(last_name, \' \', first_name) AS \'_tr(FULL_NAME)\', (SELECT GROUP_CONCAT(name) FROM profile WHERE person.profile & profile.id ORDER BY profile.name) AS \'_tr(PROFILE)\',
person.name AS Login, email AS \'_tr(EMAIL)\', DATE_FORMAT(birthdate, \'%d/%m/%Y\') AS \'_tr(BIRTHDATE)\', YEAR(birthdate) AS \'_tr(YEAR_OF_BIRTH)\',
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(
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 \'_tr(VALIDITY)\',
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 \'_tr(VALIDITY_EXPIRATE_DATE)\',
IF(ident_value IS NOT NULL, ident_value, \'-\') AS \'_tr(VALIDITY_REGISTRATION)\''), '
FROM person
LEFT JOIN profile ON (person.profile & profile.id)
LEFT JOIN validity ON person.id = validity.person_id AND validity.is_current_validity = 1
LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id)
WHERE person.activated = 1
AND validity_type.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'</sql>

=User email=
<syntaxhighlight lang="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 mail with validity type equal year=

*Variable '''$validityTypeId'''
*Variable '''$year'''

<syntaxhighlight lang="sql">SELECT
CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
person.email AS _tr(EMAIL)
FROM person
LEFT JOIN validity ON validity.person_id = person.id AND validity.is_current_validity = 1
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
WHERE person.activated = 1
AND validity_type.activated = 1
AND YEAR(validity.expire_date) = $year
AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
GROUP BY person.id
ORDER BY `_tr(FULL_NAME)`</sql>

=User validity=

*Variable '''$validityTypeId'''

<syntaxhighlight lang="sql">SELECT
validity_type.name AS _tr(VALIDITY),
CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
ident_value AS _tr(VALIDITY_REGISTRATION),
DATE_FORMAT(expire_date,'%d/%m/%Y') AS _tr(VALIDITY_EXPIRATE_DATE),
IF((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), _tr(YES), _tr(NO)) AS _tr(EXPIRED),
DATE_FORMAT(grant_date,'%d/%m/%Y') AS _tr(VALIDITY_GRANT_DATE)
FROM validity_type
LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1
LEFT JOIN person ON person.id=validity.person_id
WHERE ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
AND person.activated = 1
AND validity_type.activated = 1
ORDER BY validity_type.name, `_tr(FULL_NAME)`</sql>

=User validity ending before date=

*Variable '''$endDate'''
*Variable '''$validityTypeId'''

<syntaxhighlight lang="sql">SELECT
person.id AS _tr(ID),
CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
validity_type.name AS _tr(VALIDITY)
FROM person
LEFT JOIN validity ON validity.person_id = person.id AND validity.is_current_validity = 1
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
WHERE person.activated = 1
AND validity_type.activated = 1
AND validity.grant_date <= $endDate
AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )</sql>

=User validity superior year=

*Variable '''$validityTypeId'''
*Variable '''$year'''

<syntaxhighlight lang="sql">SELECT
CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
validity_type.name AS _tr(VALIDITY),
DATE_FORMAT(grant_date, '%d/%m/%Y') AS _tr(VALIDITY_GRANT_DATE),
ident_value AS _tr(VALIDITY_REGISTRATION),
(
SELECT GROUP_CONCAT(profile.name SEPARATOR ', ')
FROM profile
WHERE (person.profile & profile.id)
) AS _tr(PROFILE)
FROM validity_type
LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1
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
AND validity_type.activated = 1
ORDER BY `_tr(FULL_NAME)`</sql>

=User validity viewer=
<syntaxhighlight lang="sql">SELECT
CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
validity_type.name AS _tr(VALIDITY),
DATE_FORMAT(expire_date,'%d/%m/%Y') AS _tr(VALIDITY_EXPIRATE_DATE),
IF((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), _tr(YES), _tr(NO)) AS _tr(EXPIRED),
DATE_FORMAT(grant_date,'%d/%m/%Y') AS _tr(VALIDITY_GRANT_DATE)
FROM validity_type
LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1
LEFT JOIN person ON person.id=validity.person_id
WHERE person.activated = 1
AND validity_type.activated = 1
ORDER BY `_tr(FULL_NAME)`, validity_type.name</sql>

=User validity without date=

*Variable '''$validityTypeId'''

<syntaxhighlight lang="sql">SELECT
CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
validity.*
FROM `validity`
LEFT JOIN person ON person.id = validity.person_id
LEFT JOIN validity_type ON validity_type.id = validity.validity_type_id
WHERE ( validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
AND validity.expire_date IS NULL
AND person.activated = 1
AND validity_type.activated = 1
AND validity.is_current_validity = 1</sql>

=User validity without expired date=

*Variable '''$validityTypeId'''

<syntaxhighlight lang="sql">SELECT
CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
validity_type.name AS _tr(VALIDITY)
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
AND validity_type.activated = 1
AND validity.is_current_validity = 1</sql>

=User without validity=

*Variable '''$validityTypeId'''

<syntaxhighlight lang="sql">SELECT
person.id AS _tr(ID),
CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME)
FROM person
WHERE person.id NOT IN (
SELECT person.id
FROM person
LEFT JOIN validity ON person.id = validity.person_id AND validity.is_current_validity = 1
WHERE validity.validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId)
)
AND person.activated = 1
ORDER BY `_tr(FULL_NAME)`</sql>

=Validity year young specific profile=

*Variable '''$profileId'''
*Variable '''$year'''

<syntaxhighlight lang="sql">SELECT
validity_type.name AS _tr(VALIDITY),
SUM( IF( ( $year - YEAR(personWithProfile.birthdate) < 21 ), 1, 0 ) ) AS _tr(YOUNG),
SUM( IF( ( $year - YEAR(personWithProfile.birthdate) >= 21 ), 1, 0 ) ) AS _tr(ADULT)
FROM validity_type
LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1
LEFT JOIN (
SELECT person.*
FROM person
LEFT JOIN profile ON person.profile&profile.id
WHERE ( profile.id IN ($profileId) OR '-' IN ($profileId) )
AND person.activated=1
GROUP BY person.id
) AS personWithProfile ON (validity.person_id=personWithProfile.id)
WHERE YEAR(validity.grant_date)=$year
AND personWithProfile.id IS NOT NULL
AND validity_type.activated = 1
GROUP BY validity_type.id
ORDER BY validity_type.name</sql>

=Young from this year=

*Variable '''$year'''

<syntaxhighlight lang="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>