Zeiterfassung und monatliche Auswertung

[Forumsgast]

Hallo Leute,

ich habe eine MySQL Datenbank, die in etwa Folgends beinhaltet:

In einer Tabelle ZEIT werden An- und Abmeldezeiten von Usern gespeichert.

In einer anderen Tabelle, nennen wir sie mal AKTION, werden vorgenomme Aktionen von Benutzern gespeichert.
Diese Tabelle enthält Informationen über die Aktionen, und zu welcher Zeit sie ausgeführt wurden.

Es gibt also in beiden Tabellen, Spalten mit Datum(Datentyp Date) und Zeit(Datentyp TIME).

In der Tabelle ZEIT gibt es die Spalte Benutzer, in der Tabelle Aktion jedoch nicht.

Mein Ziel ist es jetzt, die Aktionen aus der Tabelle AKTION den jeweiligen Benutzern zuzuweisen. Es muss also das Datum und die Zeit der Aktion mit der Anmeldezeit überprüft werden.
Der Benutzer, der zur Zeit der AKTION angemeldet war, bekommt die Aktion also zugewiesen.

Jetzt ist es aber so, das der Benutzer sich mehrmals täglich an- und abmelden kann, sodass sich für jeden Benutzer mehrere Zeitfenster ergeben.

Als erstes müssten ja die Zeitfenster ermittelt werden:

SELECT * FROM ZEIT WHERE user='benutzername' and art='Anmeldung'
sowie
SELECT * FROM ZEIT WHERE user='benutzername' and art='Abmeldung'

So jetzt hätte ich jeweils die An- und Abmeldezeiten. Da es mehrere An- und Abmeldungen gibt muss ich die Zeiten ja irgendwie speichern, oder?
Würdet ihr hier ein Array nehmen?

Im nächsten Schritt müssen die gespeicherten Zeitfensten nun mit der Tabelle AKTION verglichen werden.

SELECT * FROM AKTION WHERE Datum='gespeichertesDatum' AND Zeit BETWEEN 'gespeicherteStartzeit' AND 'gespeicherteEndzeit'

Und das für alle Einträge im Array.


Die Tabellenstrukturen sind festgelegt und können nicht verändert werden.
Ziel der ganzen Aktion soll eine Monatsauswertung sein.


Habt ihr eventuell TIPPS für mich, wie ich das sauber und effektiv realisieren kann?


MfG watchdogg

[Forumsgast]

Hat keiner ne Idee, oder liegts an meiner Beschreibung?

[Forumsgast]

Ja: Das Datenmodell ist be..scheiden.

Die Tabellenstrukturen sind festgelegt und können nicht verändert werden.

Habt ihr eventuell TIPPS für mich, wie ich das sauber und effektiv realisieren kann?

Unter diesen Voraussetzungen leider nicht.

Mein Vorschlag wäre gewesen, eine vernünftige Tabellenstruktur zu erstellen, in etwa so:

Tabelle "Benutzer"
ID | Name

Tabelle "Aktion"
ID | Benutzer_ID | Art_der_Aktion | von | bis

- Direkte Verbindung von Aktion zu Benutzer (über Fremdschlüssel)
- Komponente "Zeit" in Aktion verlagern, weg vom Benutzer
- Pro Aktion ein Eintrag in Tabelle "Aktion".

(Beitrag auf die Schnelle erstellt)

[Forumsgast]

Ich habe in meinem ersten Beitrag völligen Blödsinn geschrieben.
So passierts eben, wenn man rumdöst - also nochmal.


Ich habe folgende Tabellen:


Tabelle USER
------------
UserID | UserName


Tabelle USERLOG
---------------
UserlogID | UserlogDate | UserlogStartTime | FK_User_ID | UserlogArt


In der Tabelle Userlog werden die Anmeldezeit und die Abmeldezeit gespeichert.
Jede Anmeldung und jede Abmeldung ist ein eigener Userlog für sich, es wird immer nur die Zeit des Userlogs gespeichert.

Also z.B.:

1 | 2011-09-06 | 12:00:00 | 4 | Anmeldung
2 | 2011-09-06 | 12:30:00 | 4 | Abmeldung
3 | 2011-09-06 | 13:00:00 | 1 | Anmeldung
4 | 2011-09-06 | 13:10:00 | 1 | Abmeldung
5 | 2011-09-06 | 14:00:00 | 4 | Anmeldung
6 | 2011-09-06 | 14:30:00 | 4 | Abmeldung
...


Jetzt kommt meine dritte Tabelle ins Spiel:

Tabelle AKTION
--------------

AktionID | AktionDate | AktionStartTime | AktionWert

1 | 2011-09-06 | 12:05:12 | 1200
2 | 2011-09-06 | 12:13:44 | 3000
3 | 2011-09-06 | 13:04:18 | 1444
4 | 2011-09-06 | 14:15:22 | 400

...


Ich möchte nun gerne eine Monatsabrechnung erstellen.
Man gibt also einen Usernamen, einen Monat und das Jahr ein.

Anhand der Zeiträume der Tabelle USERLOG sollen nun die Aktionen ermittelt werden, die in die Zeiträume für die jeweilige UserID zwischen An- und Abmeldung fallen.
Die Werte in AktionWert sollen addiert werden.

Beispiel:
---------

User 4 bekommt die AktionsWerte 4600, User1 bekommt 1444 zugewiesen.


Wie erstelle ich nun die Abfrage?
Ich muss doch erstmal prüfen in welchen Zeiträumen User 4 angemeldet war und dann die zugehörigen Aktionen selektieren, die in diese Zeiträume fallen.
Dazu muss ich ja die einzelnen Zeiträume zwischenspeichern.

Wie geht man dabei am Besten vor? Es wäre echt gut wenn ihr mir TIPPs dazu geben könntet.

Ich hoffe, dass meine Beschreibung verständlich ist.


MfG watchdogg

[Forumsgast]

Hi.

Entweder ist deine BEschreibung fehlerhaft, oder ich sehe den Wald vor lauter Bäume nicht, aber

User 4 bekommt die AktionsWerte 4600, User1 bekommt 1444 zugewiesen.

wo kommen die Werte den genau her?? In deiner Beispieltabelle stehen doch andere (Verwirrung). Ansonsten kannst du alles über eine SQL Abfrage doch machen.

mfg Wolf29

[Forumsgast]

Trenne Datum und Uhrzeit NICHT voneinander, sondern benutz DATETIME. Es macht das Rechnen mit Zeiten wesentlich einfacher, vor allem wenn An- und Abmeldung über eine Tagesgrenze hinweggehen.

Ich muss doch erstmal prüfen in welchen Zeiträumen User 4 angemeldet war und dann die zugehörigen Aktionen selektieren, die in diese Zeiträume fallen.
Dazu muss ich ja die einzelnen Zeiträume zwischenspeichern.

Wie geht man dabei am Besten vor? Es wäre echt gut wenn ihr mir TIPPs dazu geben könntet.

Sehe ich genauso. Mit nur einer Query ist das nicht zu erreichen.

Anhand der Zeiträume der Tabelle USERLOG sollen nun die Aktionen ermittelt werden, die in die Zeiträume für die jeweilige UserID zwischen An- und Abmeldung fallen.
Die Werte in AktionWert sollen addiert werden.

Als erstes musst du die einzelnen Zeiträume bestimmen, das wird mit reinem SQL nicht ganz leicht werden, weil du immer nur die zueinander passenden An- und Abmeldungen betrachten darfst. Der erste Datensatz muss eine Anmeldung sein, der Startzeitpunkt UND der Endzeitpunkt (d.h. der passende Abmelde-Datensatz) muss innerhalb des Betrachtungszeitraums liegen.

Die Bedingungen für die zweite Query werden aus den Daten der ersten Query (Bestimmung der Zeiträume für den User) zusammenschustern und mit OR verknüpfen:

SELECT 
  SUM(AktionWert) 
FROM
  Aktion
WHERE 
  AktionsDatum BETWEEN Zeitraum1.Start AND Zeitraum1.Ende
OR
  AktionsDatum BETWEEN Zeitraum2.Start AND Zeitraum2.Ende
...

Zeitraum1, Zeitraum2 bezeichnet ein passendes Wertepaar aus der ersten Abfrage (Start=Anmeldung, Ende=Abmeldung)

[Forumsgast]

Hi.

Entweder ist deine BEschreibung fehlerhaft, oder ich sehe den Wald vor lauter Bäume nicht, aber



wo kommen die Werte den genau her?? In deiner Beispieltabelle stehen doch andere (Verwirrung). Ansonsten kannst du alles über eine SQL Abfrage doch machen.

mfg Wolf29

User 4:
1 | 2011-09-06 | 12:00:00 | 4 | Anmeldung
2 | 2011-09-06 | 12:30:00 | 4 | Abmeldung

5 | 2011-09-06 | 14:00:00 | 4 | Anmeldung
6 | 2011-09-06 | 14:30:00 | 4 | Abmeldung

User 4 war angemeldet am 06.09.2011 zwischen 12:00-12:30 und zwischen 14:00-14:30

Folgende Aktionen waren in den beiden Zeiträumen "aktiv":
1 | 2011-09-06 | 12:05:12 | 1200
2 | 2011-09-06 | 12:13:44 | 3000
4 | 2011-09-06 | 14:15:22 | 400

Die Punkte zusammengezählt: 4600

edit: Betrachtungszeitrum ist der 06.09.2011 (von 0 bis 23:59Uhr)

[Forumsgast]

Spar dir die 2 Spalten für Datum und Zeit und nutze eine die einen Timestamp ( Unix oder DateTime ) speichert.

SELECT a, b, c, timestamp FROM world WHERE timestamp BETWEEN CAST('2011-01-01' AS DATE) AND CAST('2011-01-01' AS DATE);

[Forumsgast]

@lstegelitz: thanx...manchmal ist man einfach blind

[Forumsgast]

Vielen Dank für eure Antworten.
Wie gesagt die Spalten mit Datum und Zeit sind vorgegeben, ich kann die nicht einfach ändern.

Kann man die Spalten Datum und Zeit nicht irgendwie bei einer Abfrage in ein DATETIME Format casten?

MfG watchdogg

[Forumsgast]

Ja, das geht. Schau mal in tr0y's Beitrag, da findest du so einen cast.

Schön ist aber was anderes

[Forumsgast]

Ich habe gerade mal ne Lösung in einer Abfrage ausprobiert, aber die Performance sollte nicht besonders gut sein, sobald die Datenbank eine gewisse Größe erreicht:

SELECT u1.log_userid, SUM( a.action_value )
FROM actions AS a, userlogs AS u1, userlogs AS u2
WHERE u1.log_userid = u2.log_userid
  AND u1.log_datetime < u2.log_datetime
  AND u1.log_type LIKE "Anmeldung"
  AND u2.log_type LIKE "Abmeldung"
  AND u2.log_datetime = (
    SELECT MIN( u3.log_datetime )
    FROM userlogs AS u3
    WHERE u3.log_userid = u1.log_userid
      AND u3.log_datetime > u1.log_datetime
      AND u3.log_type LIKE "Abmeldung"
  )
  AND a.action_datetime BETWEEN u1.log_datetime AND u2.log_datetime
GROUP BY u1.log_userid 

Ich denke die Felder sollten selbsterklärend sein, aber ich habe DateTime Typen benutzt, damit die Abfrage nicht so lang ist. Möchtest du das eben mit den einzelnen Date und Time Typen machen, musst du das ganze an jeder Stelle Casten, indem du folgende Funktion benutzt und natürlich auf die entsprechenden Felder anpasst:

CAST( CONCAT( u1.log_date, " ", u1.log_time ) AS DATETIME ) 

Solltest du diesen etwas komplizierteren Vorschlag nutzen, wäre es nett, wenn du mal was zu der Performance auf einer großen Datenmenge sagen könntest! Das ganze ist auch nicht in Bezug auf evtl auftretende Fehler getestet, wie z.B. fehlende Einträge o.ä.

[Forumsgast]

Hallo nochmals,

Meine Query hat jetzt folgenden Aufbau und funktioniert auch:

$sql="SELECT month(b_datum) AS Monat, year(b_datum) AS Jahr, b_aktion AS Aktion, SUM(b_AktionWert) AS Wert
      FROM tableB
      WHERE month(b_datum)='".$monat."' AND year(b_datum)='".$jahr."' 
      AND CAST(concat(b_datum, ' ', b_start) AS DATETIME)      
      BETWEEN '".date_format($arrAN[0], 'Y-m-d H:i:s')."'
      AND '".date_format($arrAB[0], 'Y-m-d H:i:s')."'"

      .$dynQry."

      GROUP BY b_Aktion" or die(mysql_error()); 

Wie zu erkennen kommen meine Vergleichswerte aus den Arrays $arrAN[] und $arrAB[].

Die Stringvariable "$dynQry" soll nun entsprechend der Arraygrösse zusammengebastelt werden.

So in der Art:

$dynQry='';

for($i=1; $i<count($arrAN); $i++)
{
  $dynQry += " OR month(b_datum)='".$monat."' AND year(b_datum)='".$jahr."' 
      AND CAST(concat(b_datum, ' ', b_start) AS DATETIME) BETWEEN '".date_format($arrAN[$i], 'Y-m-d H:i:s')."' AND '".date_format($arrAB[$i], 'Y-m-d H:i:s')
} 

Ich bekomme den Querystring aber nicht hin, könnt ihr mir sagen wo die Säge klemmt?



MfG watchdogg

[Forumsgast]

Habe es selber rausgefunden:

$dynQry = $dynQry." OR month(b_datum)='".$monat."' AND year(b_datum)='".$jahr."' 
      AND CAST(concat(b_datum, ' ', b_start) AS DATETIME) BETWEEN '".date_format($arrAN[$i], 'Y-m-d H:i:s')."' AND '".date_format($arrAB[$i], 'Y-m-d H:i:s')."'"; 

Sorry...

Achso, @Sirke...

habe es wie gesagt mit Arrays gemacht und ist Performance ist gut, die DB ist allerdings noch sehr klein, weil noch in der Testphase.

Danke trotzdem...