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