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;
}
