Difference between revisions of "Users exports 4"
(→User e-mails list) |
(→Expired user validity given year) |
||
(101 intermediate revisions by 5 users not shown) | |||
Line 1: | Line 1: | ||
− | = | + | =Expired user validity given year= |
− | * Variable '''$ | + | *Variable '''$validityTypeId''' |
− | * Variable '''$ | + | *Variable '''$year''' |
− | * Variable '''$validityTypeId''' | + | |
+ | <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''' | ||
+ | |||
+ | <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''' | ||
+ | |||
+ | <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''' | ||
+ | |||
+ | <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''' | ||
+ | |||
+ | <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''' | ||
<sql>[OF_DYNAMIC_SQL] | <sql>[OF_DYNAMIC_SQL] | ||
SELECT CONCAT( | SELECT CONCAT( | ||
− | 'SELECT | + | '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 | + | 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( | 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. | + | ' (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 \'', | ||
Line 18: | Line 110: | ||
) | ) | ||
), '\'_\''), | ), '\'_\''), | ||
− | + | IF('-' IN ($validityTypeId), '', ', validity_type.name AS \'_tr(VALIDITY)\', | |
− | IF( | + | IF(grant_date IS NOT NULL, DATE_FORMAT(grant_date, \'%d/%m/%Y\'), \'-\') AS \'_tr(VALIDITY_GRANT_DATE)\', |
− | IF( | + | 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 | FROM person | ||
LEFT JOIN profile ON (person.profile & profile.id) | LEFT JOIN profile ON (person.profile & profile.id) | ||
− | LEFT JOIN validity ON | + | 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) | LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id) | ||
− | WHERE person.activated=1 | + | WHERE person.activated = 1 |
− | AND (profile.id | + | AND validity_type.activated = 1 |
+ | AND ( profile.id IN (', IF('-' IN ($profileId), '\'-\'', $profileId), ') OR \'-\' IN (', IF('-' IN ($profileId), '\'-\'', $profileId), ') ) | ||
AND ( | AND ( | ||
− | ( validity.validity_type_id | + | (validity.validity_type_id IN (', IF('-' IN ($validityTypeId), '\'-\'', $validityTypeId), ') AND validity_type.time_limitation=1 AND validity.expire_date >= '$date') |
OR | OR | ||
− | \'-\' | + | \'-\' IN (', IF('-' IN ($validityTypeId), '\'-\'', $validityTypeId), ') |
) | ) | ||
− | GROUP BY person.id | + | GROUP BY person.id', IF('-' IN ($validityTypeId), '', ', validity_type.id'), ' |
ORDER BY last_name, first_name' | ORDER BY last_name, first_name' | ||
) | ) | ||
Line 38: | Line 132: | ||
WHERE business_field.category='PERSON'</sql> | WHERE business_field.category='PERSON'</sql> | ||
− | = | + | =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> | |
− | + | ||
− | + | ||
− | + | =User mail with validity type equal year= | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | *Variable '''$validityTypeId''' | |
− | + | *Variable '''$year''' | |
− | + | ||
− | + | ||
− | + | <sql>SELECT | |
− | <sql | + | CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME), |
− | + | person.email AS _tr(EMAIL) | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
FROM person | FROM person | ||
− | LEFT JOIN validity ON | + | 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) | 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 | + | 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 | + | =User validity= |
− | + | ||
− | + | ||
− | + | *Variable '''$validityTypeId''' | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | = | + | <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''' | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | <sql>SELECT | |
− | + | person.id AS _tr(ID), | |
− | + | CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME), | |
− | <sql>SELECT | + | validity_type.name AS _tr(VALIDITY) |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
FROM person | FROM person | ||
− | LEFT JOIN validity ON | + | 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) | LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) | ||
− | WHERE activated = 1 AND validity_type. | + | 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 | + | =User validity superior year= |
− | + | ||
− | + | ||
− | + | ||
− | validity | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | *Variable '''$validityTypeId''' | |
− | + | *Variable '''$year''' | |
− | Variable $ | + | |
<sql>SELECT | <sql>SELECT | ||
− | last_name | + | 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) | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | DATE_FORMAT(grant_date,'%d/%m/%Y') AS | + | |
− | + | ||
− | + | ||
− | + | ||
FROM validity_type | FROM validity_type | ||
− | LEFT JOIN validity ON validity.validity_type_id =validity_type.id | + | 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 | LEFT JOIN person ON person.id=validity.person_id | ||
− | WHERE validity_type.id | + | WHERE YEAR(grant_date)>=$year |
− | ORDER BY | + | 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 | + | =User validity viewer= |
− | + | <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), | |
− | DATE_FORMAT( | + | 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 | FROM validity_type | ||
− | LEFT JOIN validity ON validity.validity_type_id=validity_type.id | + | 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 | LEFT JOIN person ON person.id=validity.person_id | ||
− | WHERE | + | WHERE person.activated = 1 |
− | ORDER BY | + | AND validity_type.activated = 1 |
+ | ORDER BY `_tr(FULL_NAME)`, validity_type.name</sql> | ||
− | =User | + | =User validity without date= |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | *Variable '''$validityTypeId''' | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | <sql>SELECT | |
− | <sql> | + | 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''' | |
− | + | <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''' | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
+ | <sql>SELECT | ||
+ | person.id AS _tr(ID), | ||
+ | CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME) | ||
FROM person | 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) | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | WHERE | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
) | ) | ||
− | + | AND person.activated = 1 | |
− | ( | + | ORDER BY `_tr(FULL_NAME)`</sql> |
− | + | ||
− | + | =Validity year young specific profile= | |
− | + | *Variable '''$profileId''' | |
+ | *Variable '''$year''' | ||
− | + | <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 | FROM validity_type | ||
− | LEFT JOIN validity ON validity.validity_type_id=validity_type.id | + | 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 | + | LEFT JOIN ( |
− | WHERE YEAR(grant_date) | + | SELECT person.* |
− | AND | + | FROM person |
− | ORDER BY | + | 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''' | |
− | + | ||
− | * | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
<sql>SELECT | <sql>SELECT | ||
− | last_name AS | + | last_name AS Last_name, |
− | first_name AS | + | first_name AS First_name, |
− | + | DATE_FORMAT(birthdate ,'%m-%d-%Y') AS Birthdate, | |
− | + | IF ( sex = 0, 'Male', 'Female' ) AS sex | |
− | + | ||
− | + | ||
− | + | ||
FROM person | FROM person | ||
− | + | WHERE ($year-YEAR(birthdate))<=21 AND activated=1</sql> | |
− | + | ||
− | WHERE | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | = | + | |
− | + | ||
− | + | ||
− | + | ||
− | + |
Latest revision as of 07:33, 24 April 2023
Contents
- 1 Expired user validity given year
- 2 Expired user validity prior date
- 3 Obtained validity after year
- 4 Obtained validity for year
- 5 User account import
- 6 User coordinate
- 7 User email
- 8 User mail with validity type equal year
- 9 User validity
- 10 User validity ending before date
- 11 User validity superior year
- 12 User validity viewer
- 13 User validity without date
- 14 User validity without expired date
- 15 User without validity
- 16 Validity year young specific profile
- 17 Young from this year
Expired user validity given year
- Variable $validityTypeId
- Variable $year
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) )
Expired user validity prior date
- Variable $mount
- Variable $validityTypeId
- Variable $year
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
Obtained validity after year
- Variable $year
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)`
Obtained validity for year
- Variable $validityTypeId
- Variable $year
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)`
User account import
- Variable $endDate
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
- Variable $date
- Variable $profileId
- Variable $validityTypeId
[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'
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
- Variable $validityTypeId
- Variable $year
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)`
User validity
- Variable $validityTypeId
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)`
User validity ending before date
- Variable $endDate
- Variable $validityTypeId
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) )
User validity superior year
- Variable $validityTypeId
- Variable $year
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)`
User validity viewer
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
User validity without date
- Variable $validityTypeId
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
User validity without expired date
- Variable $validityTypeId
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
User without validity
- Variable $validityTypeId
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)`
Validity year young specific profile
- Variable $profileId
- Variable $year
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
Young from this year
- Variable $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