File: /var/www/ivs.kaunokolegija.lt/laravel/app/Imports/ScientistsSheetTwoImport.php
<?php
namespace App\Imports;
use Carbon\Carbon;
use App\Models\Patent;
use App\Models\PatentTag;
use App\Models\Publisher;
use App\Models\Scientist;
use App\Models\FinalWorkType;
use App\Models\IntSciencePaper;
use App\Models\SupervisedThesi;
use App\Models\ScienceDirection;
use App\Models\PatentApplication;
use App\Models\SciencePublication;
use Illuminate\Support\Collection;
use App\Models\FinalWorkInstitution;
use Maatwebsite\Excel\Concerns\ToCollection;
use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;
class ScientistsSheetTwoImport implements ToCollection
{
public function collection(Collection $rows)
{
// Optionally, skip the header row if needed.
foreach ($rows->skip(3) as $row) {
// Assume:
// Scientist
// $row[0] = name
// $row[1] = ???
// $row[2] = email
// Science Publications /science-publications
// $row[3] = year
// $row[4] = science-directions /science-directions
// $row[5] = title
// $row[6] = citation
// $row[7] = input_time
// $row[8] = authorship_field
// $row[9] = page_count
// $row[10] = /publishers firstorcreate
// $row[11] = isbn
// $row[12] = doi
//int-science-papers
// $row[13] = year
// $row[14] = science-directions /science-directions
// $row[15] = title
// $row[16] = citation
// $row[17] = input_time
// $row[18] = authorship_field
// $row[19] = page_count
// $row[20] = /publishers firstorcreate
// $row[21] = isbn
// $row[22] = doi
//Ar vadovauti darbai???
// Supervised Thesis /supervised-thesis
// $row[23] = year
// $row[24] = natures[] /final-work-types
// $row[25] = title
// $row[26] = person
// $row[27] = institutions[] /final-work-institutions
// Patents /patents
// $row[28] = year
// $row[29] = /patent-tags
// $row[30] = title
// $row[31] = number
// $row[32] = input
// Patent Applications /patent-applications
// $row[33] = year
// $row[34] = /patent-tags
// $row[35] = title
// $row[36] = number
// $row[37] = input
// Use updateOrCreate to create or update scientist
$scientist = Scientist::where('name', trim($row[0]))->first();
if (!$scientist) {
continue;
}
$this->syncSciencePublications($scientist, $row);
$this->syncIntSciencePapers($scientist, $row);
$this->syncSupervisedThesis($scientist, $row);
$this->syncPatents($scientist, $row);
$this->syncPatentApplications($scientist, $row);
}
}
/**
* Science Publications /science-publications
* $row[3] = year
* $row[4] = science-directions (comma-separated)
* $row[5] = title
* $row[6] = citation
* $row[7] = input_time
* $row[8] = authorship_field
* $row[9] = page_count
* $row[10] = publisher name (firstOrCreate)
* $row[11] = isbn
* $row[12] = doi
*/
protected function syncSciencePublications(Scientist $scientist, $row)
{
$yearRaw = trim($row[3] ?? '');
$scienceDirections = array_filter(array_map('trim', explode(',', $row[4] ?? '')));
$title = trim($row[5] ?? '');
$citation = trim($row[6] ?? '');
$inputTime = trim($row[7] ?? '');
$authorshipField = trim($row[8] ?? '');
$pageCount = trim($row[9] ?? '');
$publisherName = trim($row[10] ?? '');
$isbn = trim($row[11] ?? '');
$doi = trim($row[12] ?? '');
if (empty($yearRaw) || empty($title) || empty($inputTime)) {
return;
}
$year = $this->parseDate($yearRaw, 'Y-m-d');
$publisher = Publisher::firstOrCreate(['name' => $publisherName]);
$publication = SciencePublication::updateOrCreate(
['isbn' => $isbn],
[
'year' => $year,
'title' => $title,
'citation' => $citation,
'input_time' => $inputTime,
'authorship_field' => $authorshipField,
'page_count' => $pageCount,
'publisher_id' => $publisher->id,
'doi' => $doi,
]
);
try{
foreach ($scienceDirections as $sdName) {
$sd = ScienceDirection::firstOrCreate(['name' => $sdName]);
$publication->science_directions()->syncWithoutDetaching($sd->id);
}
$scientist->scientistSciencePublications()->syncWithoutDetaching($publication->id);
} catch (\Exception $e) {
dd($e);
}
}
/**
* International Science Papers /int-science-papers
* $row[13] = year
* $row[14] = science-directions (comma-separated)
* $row[15] = title
* $row[16] = citation
* $row[17] = input_time (as a fraction, e.g. 1 or 0.8)
* $row[18] = authorship_field
* $row[19] = page_count
* $row[20] = publisher name
* $row[21] = isbn
* $row[22] = doi
*/
protected function syncIntSciencePapers(Scientist $scientist, $row)
{
$yearRaw = trim($row[13] ?? '');
$scienceDirections = array_filter(array_map('trim', explode(',', $row[14] ?? '')));
$title = trim($row[15] ?? '');
$citation = trim($row[16] ?? '');
$inputTimeRaw = trim($row[17] ?? '');
$authorshipField = trim($row[18] ?? '');
$pageCount = trim($row[19] ?? '');
$publisherName = trim($row[20] ?? '');
$isbn = trim($row[21] ?? '');
$doi = trim($row[22] ?? '');
if (empty($yearRaw) || empty($title) || $inputTimeRaw === '') {
return;
}
$year = $this->parseDate($yearRaw, 'Y-m-d');
$inputTime = $inputTimeRaw;
$publisher = Publisher::firstOrCreate(['name' => $publisherName]);
$paper = IntSciencePaper::updateOrCreate(
['isbn' => $isbn],
[
'year' => $year,
'title' => $title,
'citation' => $citation,
'input_time' => $inputTime,
'authorship_field' => $authorshipField,
'page_count' => $pageCount,
'publisher_id' => $publisher->id,
'doi' => $doi,
]
);
foreach ($scienceDirections as $sdName) {
$sd = ScienceDirection::firstOrCreate(['name' => $sdName]);
$paper->science_directions()->syncWithoutDetaching($sd->id);
}
$scientist->scientistIntSciencePapers()->syncWithoutDetaching($paper->id);
}
/**
* Supervised Thesis /supervised-thesis
* $row[23] = year
* $row[24] = natures[] (comma-separated) /final-work-types
* $row[25] = title
* $row[26] = person
* $row[27] = institutions[] (comma-separated) /final-work-institutions
*/
protected function syncSupervisedThesis(Scientist $scientist, $row)
{
$yearRaw = trim($row[23] ?? '');
$natureNames = array_filter(array_map('trim', explode(',', $row[24] ?? '')));
$title = trim($row[25] ?? '');
$person = trim($row[26] ?? '');
$institutionNames = array_filter(array_map('trim', explode(',', $row[27] ?? '')));
if (empty($yearRaw) || empty($title)) {
return;
}
$year = $this->parseDate($yearRaw, 'Y-m-d');
$thesis = SupervisedThesi::updateOrCreate(
['title' => $title],
[
'year' => $year,
'person' => $person,
]
);
foreach ($natureNames as $natureName) {
$finalWorkType = FinalWorkType::firstOrCreate(['name' => $natureName]);
$thesis->natures()->syncWithoutDetaching($finalWorkType->id);
}
foreach ($institutionNames as $instName) {
$finalWorkInstitution = FinalWorkInstitution::firstOrCreate(['name' => $instName]);
$thesis->institutions()->syncWithoutDetaching($finalWorkInstitution->id);
}
$scientist->scientistSupervisedThesis()->syncWithoutDetaching($thesis->id);
}
/**
* Patents /patents
* $row[28] = year
* $row[29] = patent-tags (comma-separated)
* $row[30] = title
* $row[31] = number
* $row[32] = input (can be stored as-is)
*/
protected function syncPatents(Scientist $scientist, $row)
{
$yearRaw = trim($row[28] ?? '');
$tagNames = array_filter(array_map('trim', explode(',', $row[29] ?? '')));
$title = trim($row[30] ?? '');
$number = trim($row[31] ?? '');
$input = trim($row[32] ?? '');
if (empty($yearRaw) || empty($title)) {
return;
}
$year = $this->parseDate($yearRaw, 'Y-m-d');
$patent = Patent::updateOrCreate(
['number' => $number],
[
'year' => $year,
'title' => $title,
'input' => $input,
]
);
foreach ($tagNames as $tagName) {
$tag = PatentTag::firstOrCreate(['name' => $tagName]);
$patent->tags()->syncWithoutDetaching($tag->id);
}
$scientist->scientistPatents()->syncWithoutDetaching($patent->id);
}
/**
* Patent Applications /patent-applications
* $row[33] = year
* $row[34] = patent-tags (comma-separated)
* $row[35] = title
* $row[36] = number
* $row[37] = input
*/
protected function syncPatentApplications(Scientist $scientist, $row)
{
$yearRaw = trim($row[33] ?? '');
$tagNames = array_filter(array_map('trim', explode(',', $row[34] ?? '')));
$title = trim($row[35] ?? '');
$number = trim($row[36] ?? '');
$input = trim($row[37] ?? '');
if (empty($yearRaw) || empty($title)) {
return;
}
$year = $this->parseDate($yearRaw, 'Y-m-d');
$application = PatentApplication::updateOrCreate(
['number' => $number],
[
'year' => $year,
'title' => $title,
'input' => $input,
]
);
foreach ($tagNames as $tagName) {
$tag = PatentTag::firstOrCreate(['name' => $tagName]);
$application->tags()->syncWithoutDetaching($tag->id);
}
$scientist->scientistPatentApplications()->syncWithoutDetaching($application->id);
}
protected function parseDate($raw, $format = 'Y-m-d')
{
if (empty($raw)) {
return null;
}
try {
if (is_numeric($raw)) {
return ExcelDate::excelToDateTimeObject($raw)->format($format);
}
// return Carbon::parse($raw)->format($format);
return Carbon::parse($raw)->toDateString();
} catch (\Exception $e) {
return null;
}
}
}