public function SubmitScoreFinal(Requests\ScoreEntryFinalRequest $request) { $input = $request->all(); \DB::$user = '******'; \DB::$password = '******'; \DB::$dbName = 'Euro2016'; $row = \DB::queryFirstRow("select * from Scores where MatchID = %i and UserID = %i", $request->matchid, Auth::user()->id); $count = \DB::count(); //Log::info($count.' '.$request->matchid); \DB::startTransaction(); if ($count == 0) { \DB::insert('Scores', array('MatchID' => $request->matchid, 'UserID' => Auth::user()->id, 'CountryHome' => $request->CountryHomeScore, 'CountryAway' => $request->CountryAwayScore)); } else { \DB::update('Scores', array('CountryHome' => $request->CountryHomeScore, 'CountryAway' => $request->CountryAwayScore), "ID=%i", $row['ID']); } $affectedcount = \DB::affectedRows(); \DB::update('Matches', array('CountryHomeID' => $request->CountryHomeID, 'CountryAwayID' => $request->CountryAwayID), "MatchID=%i", $request->matchid); \DB::commit(); \DB::disconnect(); // retrieve country names and flag $countrynamehome = \DB::queryFirstRow("select CountryName, Flag from Countries where CountryID = %i", $request->CountryHomeID); $countrynameaway = \DB::queryFirstRow("select CountryName, Flag from Countries where CountryID = %i", $request->CountryAwayID); $input['CountryHomeID'] = $countrynamehome['CountryName']; $input['CountryAwayID'] = $countrynameaway['CountryName']; $input['CountryHomeImage'] = $countrynamehome['Flag']; $input['CountryAwayImage'] = $countrynameaway['Flag']; //$input['CountryHID'] = $request->CountryHomeID; //$input['CountryAID'] = $request->CountryAwayID; return json_encode($input); }
private function getMatches($MatchType) { \DB::$user = '******'; \DB::$password = '******'; \DB::$dbName = 'Euro2016'; $results = \DB::query("\n\t\t\tselect A.MatchID, A.CountryHomeID, A.CountryAwayID, A.TimeAndDate, B.CountryName CountryHomeName, B.Flag CountryHomeFlag, \n\t\t\tC.CountryName CountryAwayName, C.Flag CountryAwayFlag, B.CountryGroup, D.CountryHome CountryHomeScore, D.CountryAway CountryAwayScore \n\t\t\tfrom Matches A\n\t\t\tjoin Countries B on B.CountryID = A.CountryHomeID\n\t\t\tjoin Countries C on C.CountryID = A.CountryAwayID\n\t\t\tleft join Scores D on D.MatchID = A.MatchID and D.UserID = %i\n\t\t\twhere A.MatchType = %s \n\t\t\t;", Auth::user()->id, $MatchType); //where B.CountryGroup in ('A','B','C','D','E','F') //where D.UserID = %i $mArray = []; foreach ($results as $row) { $object = new MatchesModel(); $object->MatchID = $row['MatchID']; $object->CountryHomeName = $row['CountryHomeName']; $object->CountryAwayName = $row['CountryAwayName']; $object->TimeAndDate = $row['TimeAndDate']; $object->CountryHomeFlag = $row['CountryHomeFlag']; $object->CountryAwayFlag = $row['CountryAwayFlag']; $object->CountryHomeScore = $row['CountryHomeScore']; // == '' ? ' ' : $row['CountryHomeScore']; $object->CountryAwayScore = $row['CountryAwayScore']; // == '' ? ' ' : $row['CountryAwayScore']; $object->CountryHomeScoreLabel = 'CountryHomeScoreLabel' . $row['MatchID']; // == '' ? ' ' : $row['CountryAwayScore']; $object->CountryAwayScoreLabel = 'CountryAwayScoreLabel' . $row['MatchID']; // == '' ? ' ' : $row['CountryAwayScore']; $mArray[] = $object; } \DB::disconnect(); return $mArray; }