public static function loadFromFile($royaltyStreamFiles)
{
$query = "LOAD DATA LOCAL INFILE " . DB::connection()->getPdo()->quote($royaltyStreamFiles[0]->stream_file_name) . "\n INTO TABLE " . RoyaltyStream::table() . "\n CHARACTER SET 'utf8'\n FIELDS TERMINATED BY ','\n ENCLOSED BY '\"'\n LINES TERMINATED BY '\n'\n IGNORE 1 LINES\n (@col" . implode(",@col", array_keys(CsvFileService::getFileHeaders($royaltyStreamFiles[0]->stream_file_name))) . ")\n SET ";
foreach (self::fieldsToFileHeaderPositions() as $field => $position) {
if (array_key_exists($field, self::fieldFilters())) {
$query .= " {$field}=" . self::fieldFilter($field, "@col" . $position) . ", ";
} else {
$query .= " {$field}=TRIM(@col{$position}), ";
}
}
$query = rtrim($query, ", ");
$query .= ", created = now(), updated = now(), period_year = '" . $royaltyStreamFiles[0]->period_year . "',\n period_month = '" . $royaltyStreamFiles[0]->period_month . "', period_quarter = '" . $royaltyStreamFiles[0]->period_quarter . "' ";
//@col3 == payee code
$streamFileIdsFilter = "CASE @col3";
foreach ($royaltyStreamFiles as $royaltyStreamFile) {
$streamFileIdsFilter .= " WHEN '" . $royaltyStreamFile->deal()->first()->payee_code . "' THEN '" . $royaltyStreamFile->id . "' ";
}
$streamFileIdsFilter .= "ELSE NULL END";
$query .= ", stream_file_id = " . $streamFileIdsFilter;
// echo "<pre>";
// print_r($query);
// echo "</pre>";
// die("moare aici");
DB::connection()->getpdo()->exec($query);
}
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];
}
