public function Login($user) { $sql = ' SELECT * ' . ' FROM sysuser U ' . ' WHERE U.username = "******"user"] . '" and U.password = "******"password"] . '"'; $db = new Database(); $result = $db->RunSQL($sql); // NOT LOGGED IN if (count($result) != 1) { return JSONHelper::GetJSON($result, 'user'); } $tables = array('user', 'menu', 'properties', 'fields', 'configurations', 'resources'); $tablesValues = array($result); //menu $sql = 'SELECT EV.*, E.name as entityname, SP.name as menuname, P.permissionid as menugroupid ' . ' FROM sysentityview EV ' . ' left JOIN syspermissionview PV ON PV.entityviewid = EV.entityviewid ' . ' left JOIN syspermission SP ON SP.permissionid = PV.permissionid ' . ' LEFT JOIN sysentity E ON EV.entityid = E.entityid ' . ' LEFT JOIN (SELECT P.permissionid ' . ' FROM sysuserrole UR ' . ' INNER JOIN sysrole R ON UR.roleid = R.roleid' . ' INNER JOIN sysrolepermission RP ON RP.roleid = R.roleid ' . ' INNER JOIN syspermission P ON RP.permissionid = P.permissionid ' . ' WHERE UR.userid = "' . $result[0]['userid'] . '" AND P.type = "menu"' . ' GROUP BY P.permissionid) P on P.permissionid = SP.permissionid' . ' ORDER BY SP.name, EV.name asc'; //echo $sql; $result = $db->RunSQL($sql); array_push($tablesValues, $result); //relationships proprieties $sql = 'SELECT R.*,PC.name as childname, P.name as parentname, E.name as entityname ' . ' FROM sysentityrelationship R ' . ' INNER JOIN sysentity E ON R.parententityid = E.EntityID' . ' INNER JOIN sysproperty P ON R.parentpropertyid = P.propertyid' . ' INNER JOIN sysproperty PC ON R.childpropertyid = PC.propertyid'; $result = $db->RunSQL($sql); array_push($tablesValues, $result); //fields $sql = 'SELECT * FROM sysproperty P ORDER BY priority ASC'; $result = $db->RunSQL($sql); array_push($tablesValues, $result); //fields $sql = 'SELECT * FROM sysconfiguration'; $result = $db->RunSQL($sql); array_push($tablesValues, $result); //fields $sql = 'SELECT * FROM sysresource'; $result = $db->RunSQL($sql); array_push($tablesValues, $result); $ret = JSONHelper::GetJsonFromTables($tablesValues, $tables); return $ret; }
public function GetEntityView($param, $table) { $db = new Database(); $result = $db->RunSP($param["entityViewID"], $param["filters"]); if ($table) { $tables = array('records', 'row'); $tablesValues = array($result); array_push($tablesValues, $table); $ret = JSONHelper::GetJsonFromTables($tablesValues, $tables); } else { $ret = JSONHelper::GetJSON($result, 'records'); } return $ret; }
public function BoatSeries($boatId) { $db = new Database(); $tables = array('boatentries', 'availableseries', 'registeredraces'); //boat entries $sql = 'select rsb.*, rs.name as raceseriesname,rs.earlieststarttime,' . ' b.name as boatname, rs.entrycost, rs.raceentrycost,fr.path, rs.description' . ' from raceseriesboat rsb' . ' inner join raceseries rs on rs.raceseriesid = rsb.raceseriesid' . ' left join sysfileresource fr on rs.fileresourceid = fr.fileresourceid' . ' inner join boat b on b.boatid = rsb.boatid' . ' where rsb.boatid = "' . $boatId . '"'; $result = $db->RunSQL($sql); $tablesValues = array($result); //series not applied for $sql = 'select rs.*,fr.path from raceseries rs ' . ' left join sysfileresource fr on rs.fileresourceid = fr.fileresourceid' . ' where rs.raceseriesid not in (select raceseriesid from raceseriesboat where boatid = "' . $boatId . '")'; $result = $db->RunSQL($sql); array_push($tablesValues, $result); //races $sql = 'select rb.*, r.raceseriesid, r.name as rname' . ' from raceboat rb' . ' inner join race r on r.raceid = rb.raceid' . ' where rb.boatid = "' . $boatId . '" order by r.startdate asc'; $result = $db->RunSQL($sql); array_push($tablesValues, $result); $ret = JSONHelper::GetJsonFromTables($tablesValues, $tables); return $ret; }