55 lines
3.9 KiB
PHP
55 lines
3.9 KiB
PHP
<?php
|
|
// define constants for table names
|
|
// "data"
|
|
define("USERS_TABLE_NAME", "users");
|
|
define("SESSIONS_TABLE_NAME", "sessions");
|
|
define("PARTICIPATES_TABLE_NAME", "participates");
|
|
define("TRIGGEREVENTS_TABLE_NAME", "triggerevents");
|
|
define("REACTEVENTS_TABLE_NAME", "reactevents");
|
|
// types
|
|
define("TRIGGEREVENTTYPES_TABLE_NAME", "triggereventtypes");
|
|
define("REACTEVENTTYPES_TABLE_NAME", "reacteventtypes");
|
|
define("REACTEVENTMODES_TABLE_NAME", "reacteventmodes");
|
|
define("SESSIONTYPES_TABLE_NAME", "sessiontypes");
|
|
define("USERSTATUS_TABLE_NAME", "userstatus");
|
|
define("USERROLES_TABLE_NAME", "userroles");
|
|
// views
|
|
define("SESSIONDEBRIEFS_VIEW_NAME", "sessiondebriefs");
|
|
|
|
|
|
|
|
|
|
//////////////////////////////////////// query strings for SESSIONDEBRIEFS_VIEW ////////////////////////////////////////
|
|
// SELECT clause
|
|
define("SESSIONDEBRIEFS_VIEW_QUERY_SELECT",
|
|
"SELECT S.id AS SessionId, S.sessionType AS SessionTypeId, ST.displayName AS SessionType, S.sessionName AS SessionName, S.sessionDate AS SessionDate, " .
|
|
"S.mapName AS MapName, S.scenarioName AS ScenarioName, S.success AS SessionSuccessful, S.timeToFinish AS SessionDuration, " .
|
|
"COALESCE(T.type, -1) AS TriggerTypeId, COALESCE(TT.displayName, '') AS TriggerType, (SELECT IFNULL(T.srcUserId, PS.userId)) AS ShooterId, US.username AS ShooterName, " .
|
|
"(SELECT IFNULL(PS.role, 3)) AS ShooterRoleId, (SELECT R.displayName FROM userroles R WHERE R.id = ShooterRoleId) AS ShooterRole, " .
|
|
"COALESCE(T.indexCount, -1) AS ShotIndex, COALESCE(R.id, -1) AS ReactId, COALESCE(R.reactMode, 2) AS ReactModeId, COALESCE(RM.displayName, '') AS ReactMode, " .
|
|
"COALESCE(R.reactType, -1) AS ReactTypeId, COALESCE(RT.displayName, '') AS ReactType, COALESCE(R.hitUserId, -1) AS TargetUserId, " .
|
|
"COALESCE(UH.username, '') AS TargetUserName, (SELECT IFNULL(PH.role, 3)) AS TargetRoleId, (SELECT R.displayName FROM userroles R WHERE R.id = TargetRoleId) AS TargetRole, " .
|
|
"COALESCE(R.hitTargetName, '') AS TargetName, COALESCE(R.hitBoneName, '') AS TargetBoneName, COALESCE(R.targetKilled, 0) AS TargetKilled, " .
|
|
"COALESCE(R.objectHitLocationX, 0) AS HitLocationX, COALESCE(R.objectHitLocationY, 0) AS HitLocationY, COALESCE(R.objectHitTagLocation, '') AS HitLocationTag, " .
|
|
"COALESCE(R.hitPrecision, 0) AS HitPrecision, COALESCE(R.distance, 0) AS HitTargetDistance, COALESCE(R.reactTime, 0) AS ReactionTime, " .
|
|
"COALESCE(R.timeStamp, 0) AS TimeStamp, COUNT(DISTINCT R.id) AS NbHit, COUNT(DISTINCT RK.srcEventIndex,RK.hitTargetName) AS NbKilled");
|
|
|
|
// FROM clause
|
|
define ("SESSIONDEBRIEFS_VIEW_QUERY_FROM",
|
|
" FROM " . SESSIONS_TABLE_NAME . " S LEFT JOIN ". PARTICIPATES_TABLE_NAME . " PS ON (S.id = PS.sessionId) " .
|
|
"LEFT JOIN ". PARTICIPATES_TABLE_NAME . " PH ON (S.id = PH.sessionId) LEFT JOIN " . TRIGGEREVENTS_TABLE_NAME . " T ON (S.id = T.sessionId) " .
|
|
"LEFT JOIN " . TRIGGEREVENTTYPES_TABLE_NAME . " TT ON (TT.id = T.type) LEFT JOIN " . SESSIONTYPES_TABLE_NAME . " ST ON (ST.id = S.sessionType) " .
|
|
"LEFT JOIN " . REACTEVENTS_TABLE_NAME . " R ON ( T.indexCount = R.srcEventIndex AND T.sessionId = R.srcEventSessionId ) " .
|
|
"LEFT JOIN " . USERS_TABLE_NAME . " UH ON (UH.id = R.hitUserId) LEFT JOIN " . USERS_TABLE_NAME . " US ON (US.id = T.srcUserId OR US.id = PS.userId ) " .
|
|
"LEFT JOIN " . REACTEVENTTYPES_TABLE_NAME . " RT ON (RT.id = R.reactType) LEFT JOIN " . REACTEVENTMODES_TABLE_NAME . " RM ON (RM.id = COALESCE(R.reactMode, 2)) " .
|
|
"LEFT JOIN " . REACTEVENTS_TABLE_NAME . " RK ON (R.id = RK.id AND RK.targetKilled = 1)");
|
|
|
|
// GROUP BY clause
|
|
define("SESSIONDEBRIEFS_VIEW_QUERY_GROUPBY", " GROUP BY SessionId,ShooterId,ShotIndex,TargetName");
|
|
|
|
// ORDER BY clause
|
|
define("SESSIONDEBRIEFS_VIEW_QUERY_ORDERBY", " ORDER BY SessionId,ShooterId,ShotIndex,ReactId");
|
|
|
|
// overall query string for SESSIONDEBRIEFS_VIEW (concatenation of SELECT, FROM, GROUP BY, ORDER BY clauses)
|
|
define("SESSIONDEBRIEFS_VIEW_QUERY", SESSIONDEBRIEFS_VIEW_QUERY_SELECT . SESSIONDEBRIEFS_VIEW_QUERY_FROM . SESSIONDEBRIEFS_VIEW_QUERY_GROUPBY . SESSIONDEBRIEFS_VIEW_QUERY_ORDERBY);
|
|
?>
|