PHP-Abfrage/Plugin für Sportverband

Alles, was nicht in die anderen Foren passt, kannst du hier loswerden.
Antworten
Gerhard0964
Beiträge: 129
Registriert: 15. Dez 2007, 17:53

PHP-Abfrage/Plugin für Sportverband

Beitrag von Gerhard0964 »

Hallo zusammen,

ich habe ein Problem, mit dem ich sicher nicht alleine bin. Wer sich damit einmal hat auseinandersetzten müssen, wird mich verstehen. :D

Folgende Problematik:

Anfang des Jahres wollen die Sportverbände die Mitgliederzahlen gemeldet haben. Wenn ich nur wenige Mitglieder habe, ist dies sicher unproblematisch. Wir sind mit rund 300 Mitgliedern zwar nicht riesengroß, der Sportbund Pfalz sorgt aber dafür, dass die Meldung recht umfangreich wird.

Wir müssen in jeder Sportart und im Gesamten die Mitglieder nach folgendem Schema melden:

<6 Jahre
7-14 Jahre
15-18 Jahre
19-26 Jahre
27-40 Jahre
41-60 Jahre
>60 Jahre

Und das nach Geschlechtern auch noch getrennt.

Zwar kann ich mir das über eigene Listen auch erstellen, muss aber jede Sportart/Geschlecht/Altersgruppe getrennt aufrufen. Das sind bei mir locker 80(!) Abfragen!! :|

Es wäre jetzt schön, wenn das mit einem Plugin oder auch mit einer PHP-Abfrage gelöst werden könnte. Ja ich weiß - mit PHP kein Problem - wenn man damit fit ist. Bin ich leider nicht. Daher meine Anfrage ob so etwas umgesetzt, oder für alle hier als Abfrage erstellt werden kann.

Danke
Viele Grüße
Gerhard
Nach oben
zettem
Plugin-Developer
Beiträge: 39
Registriert: 24. Nov 2012, 09:57

Re: PHP-Abfrage/Plugin für Sportverband

Beitrag von zettem »

Hallo Gerhard,

hab es zwar nicht ausprobiert, aber genau solche Auswertungen sollten meiner Ansicht nach mit dem neuen Statistik-Plugin mölich sein, oder liege ich da falsch?

Gruss zettem
Bild
A p p m i d i o - die App für Admidio
Nach oben
Jochen
Team
Beiträge: 1506
Registriert: 22. Feb 2006, 18:11

Re: PHP-Abfrage/Plugin für Sportverband

Beitrag von Jochen »

Hallo,
genau dafür ist es da. Ist allerdings noch nicht Final veröffentlicht. Schau mal hier:
viewtopic.php?f=10&t=5186
Gruß Jochen
Nach oben
suesemausw
Beiträge: 34
Registriert: 5. Apr 2012, 20:56
Wohnort: Steinbach
Kontaktdaten:
Kontaktdaten von suesemausw

Re: PHP-Abfrage/Plugin für Sportverband

Beitrag von suesemausw »

Vielleicht hilft dir ja das erstmal weiter:

Code: Alles auswählen

<?php

    require_once('../../system/common.php');
    require_once('../../system/classes/table_roles.php');


    {
    // nur eingeloggte Benutzer duerfen auf das Modul zugreifen
    require_once('../../system/login_valid.php');
    }


    // Html-Kopf Seitentitel ausgeben
    $g_layout['title']  = 'Statistiken';
    $g_layout['header'] = '
        <script type="text/javascript"><!--
            $(document).ready(function()
            {
                $("a[rel=\'lnkDelete\']").colorbox({rel:\'nofollow\', scrolling:false, onComplete:function(){$("#admButtonNo").focus();}});
            });
        //--></script>';

    require(SERVER_PATH. '/adm_program/system/overall_header.php');


    echo "<hr>";
    echo "<h3><strong>Hier werden Einige Statistiken useres Vereines ausgegeben </strong></h3>";
	



// Rollid festlegen

     $Rolid = 9 ;


	//Die Ältesten Männer berechnen
	$sql = 'SELECT DISTINCT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender, city.usd_value as city, birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' city
            ON city.usd_usr_id = mem_usr_id
            AND city.usd_usf_id = '.$gProfileFields->getProperty('CITY', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
            WHERE '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').' < \''.DATE_NOW.'\'
            AND mem_end > \''.DATE_NOW.'\'
			AND gender.usd_value = 1
			AND mem_rol_id  = '.$Rolid.'
			AND birthday.usd_value IS NOT NULL
            ORDER BY birthday ASC
            LIMIT 10';

    $erg_man_alt = $gDb->query($sql);


	//Den Ältesten Frauen berechnen
	$sql = 'SELECT DISTINCT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender, city.usd_value as city, birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' city
            ON city.usd_usr_id = mem_usr_id
            AND city.usd_usf_id = '.$gProfileFields->getProperty('CITY', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
            WHERE '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').' < \''.DATE_NOW.'\'
            AND mem_end > \''.DATE_NOW.'\'
			AND gender.usd_value = 2
			AND mem_rol_id  = '.$Rolid.'
			AND birthday.usd_value IS NOT NULL
            ORDER BY birthday ASC
            LIMIT 10';

    $erg_frau_alt = $gDb->query($sql);



	//Anzahl weiblicher Mitglieder
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    gender.usd_value = 2
	and
	mem_rol_id = '.$Rolid.'
	';
    $result = $gDb->query($sql);
    $mitglieder_wei = $gDb->num_rows($result);

	//Anzahl männliche Mitglieder
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    adm_members.mem_usr_id
	and
    gender.usd_value = 1
	and
	mem_rol_id = '.$Rolid.'
    ';
    $result = $gDb->query($sql);
    $mitglieder_men = $gDb->num_rows($result);


    //Anzahl maennlich 0-6
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    adm_members.mem_usr_id
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))<=6
    and
    gender.usd_value =1
	and
	mem_rol_id = '.$Rolid.'
    ';
    $result = $gDb->query($sql);
    $maennlich_0_bis_6 = $gDb->num_rows($result);

    //Anzahl maennlich 7-14
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    adm_members.mem_usr_id
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))>6
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))<=14
    and
    gender.usd_value =1
	and
	mem_rol_id = '.$Rolid.'
    ';
    $result = $gDb->query($sql);
    $maennlich_7_bis_14 = $gDb->num_rows($result);

    //Anzahl maennlich 15-18
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    adm_members.mem_usr_id
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))>14
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))<=18
    and
    gender.usd_value =1
	and
	mem_rol_id = '.$Rolid.'
    ';
    $result = $gDb->query($sql);
    $maennlich_15_bis_18 = $gDb->num_rows($result);

    //Anzahl maennlich 19-26
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    adm_members.mem_usr_id
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))>18
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))<=26
    and
    gender.usd_value =1
	and
	mem_rol_id = '.$Rolid.'
    ';
    $result = $gDb->query($sql);
    $maennlich_19_bis_26 = $gDb->num_rows($result);

    //Anzahl maennlich 27-40
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    adm_members.mem_usr_id
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))>26
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))<=40
    and
    gender.usd_value =1
	and
	mem_rol_id = '.$Rolid.'
    ';
    $result = $gDb->query($sql);
    $maennlich_27_bis_40 = $gDb->num_rows($result);

    //Anzahl maennlich 41-60
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    adm_members.mem_usr_id
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))>40
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))<=60
    and
    gender.usd_value =1
	and
	mem_rol_id = '.$Rolid.'
    ';
    $result = $gDb->query($sql);
    $maennlich_41_bis_60 = $gDb->num_rows($result);

    //Anzahl maennlich >60
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    adm_members.mem_usr_id
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))>60
    and
    gender.usd_value =1
	and
	mem_rol_id = '.$Rolid.'
    ';
    $result = $gDb->query($sql);
    $maennlich_ab_61 = $gDb->num_rows($result);


    //Anzahl weiblich 0-6
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    adm_members.mem_usr_id
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))<=6
    and
    gender.usd_value =2
	and
	mem_rol_id = '.$Rolid.'
    ';
    $result = $gDb->query($sql);
    $weiblich_0_bis_6 = $gDb->num_rows($result);

    //Anzahl weiblich 7-14
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    adm_members.mem_usr_id
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))>6
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))<=14
    and
    gender.usd_value =2
	and
	mem_rol_id = '.$Rolid.'
    ';
    $result = $gDb->query($sql);
    $weiblich_7_bis_14 = $gDb->num_rows($result);

    //Anzahl weiblich 15-18
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    adm_members.mem_usr_id
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))>14
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))<=18
    and
    gender.usd_value =2
	and
	mem_rol_id = '.$Rolid.'
    ';
    $result = $gDb->query($sql);
    $weiblich_15_bis_18 = $gDb->num_rows($result);

    //Anzahl weiblich 19-26
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    adm_members.mem_usr_id
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))>18
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))<=26
    and
    gender.usd_value =2
	and
	mem_rol_id = '.$Rolid.'
    ';
    $result = $gDb->query($sql);
    $weiblich_19_bis_26 = $gDb->num_rows($result);

    //Anzahl weiblich 27-40
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    adm_members.mem_usr_id
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))>26
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))<=40
    and
    gender.usd_value =2
	and
	mem_rol_id = '.$Rolid.'
    ';
    $result = $gDb->query($sql);
    $weiblich_27_bis_40 = $gDb->num_rows($result);

    //Anzahl weiblich 41-60
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    adm_members.mem_usr_id
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))>40
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))<=60
    and
    gender.usd_value = 2
	and
	mem_rol_id = '.$Rolid.'
    ';
    $result = $gDb->query($sql);
    $weiblich_41_bis_60 = $gDb->num_rows($result);

    //Anzahl weiblich >60
    $sql = 'SELECT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender,  birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
			where
    adm_members.mem_end > curdate()
    and
    adm_members.mem_usr_id
    and
    (year(curdate())-year(birthday.usd_value)) - (right(curdate(),5)<right(birthday.usd_value,5))>60
    and
    gender.usd_value =2
	and
	mem_rol_id = '.$Rolid.'
    ';
    $result = $gDb->query($sql);
    $weiblich_ab_61 = $gDb->num_rows($result);
	// Gesamtanzahl Ermitteln
	$anzahl_mitglieder = 0;
    $anzahl_maenner = 0;
    $anzahl_frauen = 0;
    $alter_mitglieder = 0;
    $alter_maenner = 0;
    $alter_frauen = 0;
    $juengster = 200;
    $aeltester = 0;

    function Geburtstag($gebdat)
    {
       $tag = substr($gebdat,8,2);
       $monat = substr($gebdat,5,2);
       $jahr = substr($gebdat,0,4);

       $jetzt = mktime(0,0,0,date("m"),date("d"),date("Y"));
       $gb = mktime(0,0,0,$monat,$tag,$jahr);
       $alter = intval(($jetzt - $gb) / (3600 * 24 * 365));

       return $alter;
    }
  //Durchschnitsalter berechnen
    $sql = 'SELECT DISTINCT
            surname.usd_value as surname, firstname.usd_value as firstname, gender.usd_value as gender, city.usd_value as city, birthday.usd_value as birthday
            FROM '.TBL_MEMBERS.'
            LEFT JOIN '. TBL_USER_DATA .' surname
            ON surname.usd_usr_id = mem_usr_id
            AND surname.usd_usf_id = '.$gProfileFields->getProperty('LAST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' firstname
            ON firstname.usd_usr_id = mem_usr_id
            AND firstname.usd_usf_id = '.$gProfileFields->getProperty('FIRST_NAME', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' gender
            ON gender.usd_usr_id = mem_usr_id
            AND gender.usd_usf_id = '.$gProfileFields->getProperty('GENDER', 'usf_id').'
			LEFT JOIN '. TBL_USER_DATA .' city
            ON city.usd_usr_id = mem_usr_id
            AND city.usd_usf_id = '.$gProfileFields->getProperty('CITY', 'usf_id').'
            LEFT JOIN '. TBL_USER_DATA .' birthday
            ON birthday.usd_usr_id = mem_usr_id
            AND birthday.usd_usf_id = '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').'
            WHERE '.$gProfileFields->getProperty('BIRTHDAY', 'usf_id').' < \''.DATE_NOW.'\'
            AND
            mem_end > \''.DATE_NOW.'\'
            AND
            mem_rol_id  = '.$Rolid.'
			AND
            birthday.usd_value IS NOT NULL
            ORDER BY birthday ASC
           ';

    $durch = $gDb->query($sql);

    while($row = $gDb->fetch_array($durch))
    {


       // Anzahl und Alter der Männer / Frauen
       $geschlecht = $row ['gender'];
       $geburtstag = Geburtstag($row ['birthday']);
       if($geschlecht == 1)
       {
          $anzahl_maenner++;
          $alter_maenner = $alter_maenner+$geburtstag;

       }
       else if($geschlecht == 2)
       {
          $anzahl_frauen++;
          $alter_frauen = $alter_frauen+$geburtstag;
       }
       if($geburtstag > $aeltester)
       {
          $aeltester = $geburtstag;
       }
       if($geburtstag < $juengster)
       {
          $juengster = $geburtstag;
       }
    }




	$Mitglieder_Gesamt = ($mitglieder_men+$mitglieder_wei);

	echo "<p>";
	echo "<p>";
	echo "<strong>In unserer Verein gibt es : </strong>".($Mitglieder_Gesamt);
	echo "<strong> Mitglieder</strong>";
	echo "<p>";
	echo "Durchschnittsalter aller Mitglieder liegt bei: ". round((($alter_maenner+$alter_frauen)/($anzahl_frauen+$anzahl_maenner)),0). " Jahren";
    echo "<br>";
    echo "Durchschnittsalter der Frauen liegt bei: ". round(($alter_frauen/$anzahl_frauen),0). " Jahren";
    echo "<br>";
    echo "Durchschnittsalter der M&auml;nner liegt bei: ". round(($alter_maenner/$anzahl_maenner),0). " Jahren";
    echo "<br>";

    echo "<p<>";
	echo "<table border= 1>";

    echo "<td>";
    echo "<table>";
    echo "<tr><td><strong>m&auml;nnlich</strong></td><td> </td><td> </td><td> </td></tr>";
    echo "<tr><td> </td><td>0 - 6 Jahre:</td><td>&nbsp&nbsp</td><td align=right>$maennlich_0_bis_6 </td></tr>";
    echo "<tr><td> </td><td>7 - 14 Jahre:</td><td>&nbsp&nbsp</td><td align=right>$maennlich_7_bis_14 </td></tr>";
    echo "<tr><td> </td><td>15 - 18 Jahre:</td><td>&nbsp&nbsp</td><td align=right>$maennlich_15_bis_18 </td></tr>";
    echo "<tr><td> </td><td>19 - 26 Jahre:</td><td>&nbsp&nbsp</td><td align=right>$maennlich_19_bis_26 </td></tr>";
    echo "<tr><td> </td><td>27 - 40 Jahre:</td><td>&nbsp&nbsp</td><td align=right>$maennlich_27_bis_40 </td></tr>";
    echo "<tr><td> </td><td>41 - 60 Jahre:</td><td>&nbsp&nbsp</td><td align=right>$maennlich_41_bis_60 </td></tr>";
    echo "<tr><td> </td><td>61+ Jahre:</td><td>&nbsp&nbsp</td><td align=right>$maennlich_ab_61 </td></tr>";
	echo "<tr><td> </td><td>Gesamt:</td><td>&nbsp&nbsp</td><td align=right>$mitglieder_men </td></tr>";
    echo "</table>";
    echo "</td><td>";
    echo "<table>";
    echo "<tr><td><strong>weiblich</strong></td><td> </td><td> </td><td> </td></tr>";
    echo "<tr><td> </td><td>0 - 6 Jahre:</td><td>&nbsp&nbsp</td><td align=right>$weiblich_0_bis_6 </td></tr>";
    echo "<tr><td> </td><td>7 - 14 Jahre:</td><td>&nbsp&nbsp</td><td align=right>$weiblich_7_bis_14 </td></tr>";
    echo "<tr><td> </td><td>15 - 18 Jahre:</td><td>&nbsp&nbsp</td><td align=right>$weiblich_15_bis_18 </td></tr>";
    echo "<tr><td> </td><td>19 - 26 Jahre:</td><td>&nbsp&nbsp</td><td align=right>$weiblich_19_bis_26 </td></tr>";
    echo "<tr><td> </td><td>27 - 40 Jahre:</td><td>&nbsp&nbsp</td><td align=right>$weiblich_27_bis_40 </td></tr>";
    echo "<tr><td> </td><td>41 - 60 Jahre:</td><td>&nbsp&nbsp</td><td align=right>$weiblich_41_bis_60 </td></tr>";
    echo "<tr><td> </td><td>61+ Jahre:</td><td>&nbsp&nbsp</td><td align=right>$weiblich_ab_61 </td></tr>";
	echo "<tr><td> </td><td>Gesamt:</td><td>&nbsp&nbsp</td><td align=right>$mitglieder_wei </td></tr>";
    echo "</table>";

    echo "</tr>";
    echo "</table>";
	echo "<p>";
	$n = 1;
	echo "<strong> Die  &Auml;ltesten M&auml;nner sind :</strong><p>";
	while ($oldestRoleMember_mann = $gDb->fetch_array($erg_man_alt))
	      {
		    $birthdayRolemember_mann = new DateTimeExtended($oldestRoleMember_mann['birthday'], 'Y-m-d', 'date');
	        echo $n++ .'. ';
		    echo $oldestRoleMember_mann['firstname']." ".$oldestRoleMember_mann['surname']." aus ".$oldestRoleMember_mann['city']." ".'(';
	        echo $birthdayRolemember_mann->getAge();
            echo ') <br>';

		   }
		   echo "<p>";
	$n = 1;

	echo "<strong> Die  &Auml;ltesten Frauen sind:</strong><p>";
	while ($oldestRoleMember_frau = $gDb->fetch_array($erg_frau_alt))
	      {
		    $birthdayRolemember_frau = new DateTimeExtended($oldestRoleMember_frau['birthday'], 'Y-m-d', 'date');
	        echo $n++ .'. ';
		    echo $oldestRoleMember_frau['firstname']." ".$oldestRoleMember_frau['surname']." aus ".$oldestRoleMember_frau['city']." ".'(';
	        echo $birthdayRolemember_frau->getAge();
            echo ') <br>';

		   }
		   echo "<p>";



    require(SERVER_PATH. '/adm_program/system/overall_footer.php');
?>
einfach als Statisitk.php speichern und per ftp ins Verzeichnis /Admidio/adm_program/modules/statistiken
must nur die Roll id anpassen
Nach oben
Antworten

Zurück zu „Sonstiges“