public static function apiStats($id) { $query = self::query(); $statsStartDate = new DateTime(); //get average if (sizeof(RoyaltyStreamFile::lastRoyaltyFile($id)->get()) > 0) { $lastRoyaltyStreamFile = RoyaltyStreamFile::lastRoyaltyFile($id)->get()[0]; $statsStartDate->setDate($lastRoyaltyStreamFile->period_year, $lastRoyaltyStreamFile->period_month, 1); } $last12monthDate = $statsStartDate->modify('-12 month'); $last12monthAmount = $query->select(DB::raw("SUM(royalty_stream.royalty_amount) as amount"))->join(RoyaltyStreamFile::table() . " as royalty_stream_file", 'royalty_stream_file.deal_id', '=', 'deal.id')->join(RoyaltyStream::table() . " as royalty_stream", 'royalty_stream_file.id', '=', 'stream_file_id')->where('deal_id', $id)->where(DB::raw("CONCAT(royalty_stream_file.period_year, '-',royalty_stream_file.period_month)"), '>=', $last12monthDate)->first()["amount"]; $query = self::query(); $last12monthAmountTop5Songs = $query->select('royalty_stream.song_title', DB::raw("SUM(royalty_stream.royalty_amount) as amount"))->join(RoyaltyStreamFile::table() . " as royalty_stream_file", 'royalty_stream_file.deal_id', '=', 'deal.id')->join(RoyaltyStream::table() . " as royalty_stream", 'royalty_stream_file.id', '=', 'stream_file_id')->groupBy('royalty_stream.song_title')->where('deal_id', $id)->orderBy('amount', 'desc')->limit(5)->where(DB::raw("CONCAT(royalty_stream_file.period_year, '-',royalty_stream_file.period_month)"), '>=', $last12monthDate)->get(); $sum = 0; foreach ($last12monthAmountTop5Songs as $song) { $sum += $song->amount; } return ['last_12_month_avg' => $last12monthAmount, 'top_5_song_avg' => $sum]; }