File: /var/www/ivs.kaunokolegija.lt/laravel/app/Imports/ScientistsSheetThreeImport.php
<?php
namespace App\Imports;
use Carbon\Carbon;
use App\Models\ArtWork;
use App\Models\Country;
use App\Models\Publisher;
use App\Models\Scientist;
use App\Models\HonorsAndAward;
use App\Models\TranslationType;
use App\Models\ScienceDirection;
use App\Models\ScientificReview;
use App\Models\ConferenceAbstract;
use Illuminate\Support\Collection;
use App\Models\PeerReviewedArticle;
use App\Models\ScientificPublication;
use App\Models\ScientificTranslation;
use App\Models\ArtAndOtherPublication;
use App\Models\ProfessionalDevelopment;
use App\Models\ScientificConferenceType;
use App\Models\ProfessionalDevelopmentType;
use App\Models\AppliedScientificPublication;
use Maatwebsite\Excel\Concerns\ToCollection;
use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;
class ScientistsSheetThreeImport implements ToCollection
{
public function collection(Collection $rows)
{
// Optionally, skip the header row if needed.
foreach ($rows->skip(3) as $row) {
// Scientist
// $row[0] = name
// $row[1] = ???
// $row[2] = email
// Scientific Publications /scientific-publications
// $row[3] = year
// $row[4] = science-directions /science-directions
// $row[5] = title
// $row[6] = input_time
// $row[7] = authorship_field
// $row[8] = page_count
// $row[9] = /publishers firstorcreate
// $row[10] = isbn
// $row[11] = doi
// Peer Reviewed Articles /peer-reviewed-articles
// $row[12] = year
// $row[13] = science-directions /science-directions
// $row[14] = title
// $row[15] = input_time
// $row[16] = authorship_field
// $row[17] = page_count
// $row[18] = /publishers firstorcreate
// $row[19] = isbn
// $row[20] = doi
// Applied Scientific Publications /applied-scientific-publications
// $row[21] = year
// $row[22] = science-directions /science-directions
// $row[23] = title
// $row[24] = input_time
// $row[25] = authorship_field
// $row[26] = page_count
// $row[27] = /publishers firstorcreate
// $row[28] = isbn
// $row[29] = doi
// Art and Other Publications /art-and-other-publications
// $row[30] = year
// $row[31] = science-directions /science-directions
// $row[32] = title
// $row[33] = input_time
// $row[34] = authorship_field
// $row[35] = page_count
// $row[36] = /publishers firstorcreate
// $row[37] = isbn
// $row[38] = doi
// Scientific Reviews /scientific-reviews
// $row[39] = year
// $row[40] = science-directions /science-directions
// $row[41] = title
// $row[42] = input_time
// $row[43] = authorship_field
// $row[44] = page_count
// $row[45] = /publishers firstorcreate
// $row[46] = isbn
// $row[47] = doi
// Conference Abstracts /conference-abstracts
// $row[48] = year
// $row[49] = science-directions /science-directions
// $row[50] = natures[] /scientific-conference-types
// $row[51] = title
// $row[52] = authorship_field
// $row[53] = page_count
// $row[54] = /publishers firstorcreate
// $row[55] = isbn
// $row[56] = doi
// Scientific Translations /scientific-translations
// $row[57] = year
// $row[58] = science-directions /science-directions
// $row[59] = natures[] /translation-types
// $row[60] = title
// $row[61] = input_time
// $row[62] = authorship_field
// $row[63] = page_count
// $row[64] = /publishers firstorcreate
// $row[65] = isbn
// $row[66] = doi
//Mokslo kryptis/ Ar meno darbo požymis(kuri naudoti?)
// Art Works /art-works
// $row[67] = year
// $row[68] = science-directions /science-directions
// $row[69] = title
// $row[70] = number
// $row[71] = input_time
// Professional Developments /professional-developments
// $row[72] = period_from period_to (may be only period_from in that case it may be excel date format)
// $row[73] = title
// $row[74] = development_types[] /professional-development-types
// $row[75] = institution
// $row[76] = country_id /countries
// $row[77] = time_spent
// Honors and Awards /honors-and-awards
// $row[78] = year
// $row[79] = title
// $row[80] = activity_type_desc
// $row[81] = institution
// $row[82] = country_id /countries
$scientist = Scientist::where('name', trim($row[0]))->first();
if (!$scientist) {
continue;
}
$this->syncScientificPublications($scientist, $row);
$this->syncPeerReviewedArticles($scientist, $row);
$this->syncAppliedScientificPublications($scientist, $row);
$this->syncArtAndOtherPublications($scientist, $row);
$this->syncScientificReviews($scientist, $row);
$this->syncConferenceAbstracts($scientist, $row);
$this->syncScientificTranslations($scientist, $row);
$this->syncArtWorks($scientist, $row);
$this->syncProfessionalDevelopments($scientist, $row);
$this->syncHonorsAndAwards($scientist, $row);
}
}
/**
* Scientific Publications (/scientific-publications)
* $row[3] = year
* $row[4] = science-directions (comma-separated)
* $row[5] = title
* $row[6] = input_time
* $row[7] = authorship_field
* $row[8] = page_count
* $row[9] = publisher name (firstOrCreate)
* $row[10] = isbn
* $row[11] = doi
*/
protected function syncScientificPublications(Scientist $scientist, $row)
{
$yearRaw = trim($row[3] ?? '');
$sdNames = array_filter(array_map('trim', explode(',', $row[4] ?? '')));
$title = trim($row[5] ?? '');
$inputTime = trim($row[6] ?? '');
$authorshipField = trim($row[7] ?? '');
$pageCount = trim($row[8] ?? '');
$publisherName = trim($row[9] ?? '');
$isbn = trim($row[10] ?? '');
$doi = trim($row[11] ?? '');
if (empty($yearRaw) || empty($title) || empty($inputTime)) {
return;
}
$year = $this->parseDate($yearRaw, 'Y-m-d');
$publisher = Publisher::firstOrCreate(['name' => $publisherName]);
$record = ScientificPublication::updateOrCreate(
['isbn' => $isbn],
[
'year' => $year,
'title' => $title,
'input_time' => $inputTime,
'authorship_field' => $authorshipField,
'page_count' => $pageCount,
'publisher_id' => $publisher->id,
'doi' => $doi,
]
);
foreach ($sdNames as $sdName) {
$sd = ScienceDirection::firstOrCreate(['name' => $sdName]);
$record->science_directions()->syncWithoutDetaching($sd->id);
}
$scientist->scientistScientificPublications()->syncWithoutDetaching($record->id);
}
/**
* Peer Reviewed Articles (/peer-reviewed-articles)
* $row[12] = year
* $row[13] = science-directions (comma-separated)
* $row[14] = title
* $row[15] = input_time
* $row[16] = authorship_field
* $row[17] = page_count
* $row[18] = publisher name (firstOrCreate)
* $row[19] = isbn
* $row[20] = doi
*/
protected function syncPeerReviewedArticles(Scientist $scientist, $row)
{
$yearRaw = trim($row[12] ?? '');
$sdNames = array_filter(array_map('trim', explode(',', $row[13] ?? '')));
$title = trim($row[14] ?? '');
$inputTime = trim($row[15] ?? '');
$authorshipField = trim($row[16] ?? '');
$pageCount = trim($row[17] ?? '');
$publisherName = trim($row[18] ?? '');
$isbn = trim($row[19] ?? '');
$doi = trim($row[20] ?? '');
if (empty($yearRaw) || empty($title) || empty($inputTime)) {
return;
}
$year = $this->parseDate($yearRaw, 'Y-m-d');
$publisher = Publisher::firstOrCreate(['name' => $publisherName]);
$record = PeerReviewedArticle::updateOrCreate(
['isbn' => $isbn],
[
'year' => $year,
'title' => $title,
'input_time' => $inputTime,
'authorship_field' => $authorshipField,
'page_count' => $pageCount,
'publisher_id' => $publisher->id,
'doi' => $doi,
]
);
foreach ($sdNames as $sdName) {
$sd = ScienceDirection::firstOrCreate(['name' => $sdName]);
$record->science_directions()->syncWithoutDetaching($sd->id);
}
$scientist->scientistPeerReviewedArticles()->syncWithoutDetaching($record->id);
}
/**
* Applied Scientific Publications (/applied-scientific-publications)
* $row[21] = year
* $row[22] = science-directions (comma-separated)
* $row[23] = title
* $row[24] = input_time
* $row[25] = authorship_field
* $row[26] = page_count
* $row[27] = publisher name
* $row[28] = isbn
* $row[29] = doi
*/
protected function syncAppliedScientificPublications(Scientist $scientist, $row)
{
$yearRaw = trim($row[21] ?? '');
$sdNames = array_filter(array_map('trim', explode(',', $row[22] ?? '')));
$title = trim($row[23] ?? '');
$inputTime = trim($row[24] ?? '');
$authorshipField = trim($row[25] ?? '');
$pageCount = trim($row[26] ?? '');
$publisherName = trim($row[27] ?? '');
$isbn = trim($row[28] ?? '');
$doi = trim($row[29] ?? '');
if (empty($yearRaw) || empty($title) || empty($inputTime)) {
return;
}
$year = $this->parseDate($yearRaw, 'Y-m-d');
$publisher = Publisher::firstOrCreate(['name' => $publisherName]);
$record = AppliedScientificPublication::updateOrCreate(
['isbn' => $isbn],
[
'year' => $year,
'title' => $title,
'input_time' => $inputTime,
'authorship_field' => $authorshipField,
'page_count' => $pageCount,
'publisher_id' => $publisher->id,
'doi' => $doi,
]
);
foreach ($sdNames as $sdName) {
$sd = ScienceDirection::firstOrCreate(['name' => $sdName]);
$record->science_directions()->syncWithoutDetaching($sd->id);
}
$scientist->scientistAppliedScientificPublications()->syncWithoutDetaching($record->id);
}
/**
* Art and Other Publications (/art-and-other-publications)
* $row[30] = year
* $row[31] = science-directions (comma-separated)
* $row[32] = title
* $row[33] = input_time
* $row[34] = authorship_field
* $row[35] = page_count
* $row[36] = publisher name
* $row[37] = isbn
* $row[38] = doi
*/
protected function syncArtAndOtherPublications(Scientist $scientist, $row)
{
$yearRaw = trim($row[30] ?? '');
$sdNames = array_filter(array_map('trim', explode(',', $row[31] ?? '')));
$title = trim($row[32] ?? '');
$inputTime = trim($row[33] ?? '');
$authorshipField = trim($row[34] ?? '');
$pageCount = trim($row[35] ?? '');
$publisherName = trim($row[36] ?? '');
$isbn = trim($row[37] ?? '');
$doi = trim($row[38] ?? '');
if (empty($yearRaw) || empty($title) || empty($inputTime)) {
return;
}
$year = $this->parseDate($yearRaw, 'Y-m-d');
$publisher = Publisher::firstOrCreate(['name' => $publisherName]);
$record = ArtAndOtherPublication::updateOrCreate(
['isbn' => $isbn],
[
'year' => $year,
'title' => $title,
'input_time' => $inputTime,
'authorship_field' => $authorshipField,
'page_count' => $pageCount,
'publisher_id' => $publisher->id,
'doi' => $doi,
]
);
foreach ($sdNames as $sdName) {
$sd = ScienceDirection::firstOrCreate(['name' => $sdName]);
$record->science_directions()->syncWithoutDetaching($sd->id);
}
$scientist->scientistArtAndOtherPublications()->syncWithoutDetaching($record->id);
}
/**
* Scientific Reviews (/scientific-reviews)
* $row[39] = year
* $row[40] = science-directions (comma-separated)
* $row[41] = title
* $row[42] = input_time
* $row[43] = authorship_field
* $row[44] = page_count
* $row[45] = publisher name
* $row[46] = isbn
* $row[47] = doi
*/
protected function syncScientificReviews(Scientist $scientist, $row)
{
$yearRaw = trim($row[39] ?? '');
$sdNames = array_filter(array_map('trim', explode(',', $row[40] ?? '')));
$title = trim($row[41] ?? '');
$inputTime = trim($row[42] ?? '');
$authorshipField = trim($row[43] ?? '');
$pageCount = trim($row[44] ?? '');
$publisherName = trim($row[45] ?? '');
$isbn = trim($row[46] ?? '');
$doi = trim($row[47] ?? '');
if (empty($yearRaw) || empty($title)) {
return;
}
$year = $this->parseDate($yearRaw, 'Y-m-d');
$publisher = Publisher::firstOrCreate(['name' => $publisherName]);
$record = ScientificReview::updateOrCreate(
['isbn' => $isbn],
[
'year' => $year,
'title' => $title,
'input_time' => $inputTime,
'authorship_field' => $authorshipField,
'page_count' => $pageCount,
'publisher_id' => $publisher->id,
'doi' => $doi,
]
);
foreach ($sdNames as $sdName) {
$sd = ScienceDirection::firstOrCreate(['name' => $sdName]);
$record->science_directions()->syncWithoutDetaching($sd->id);
}
$scientist->scientistScientificReviews()->syncWithoutDetaching($record->id);
}
/**
* Conference Abstracts (/conference-abstracts)
* $row[48] = year
* $row[49] = science-directions (comma-separated)
* $row[50] = natures[] (comma-separated) /scientific-conference-types
* $row[51] = title
* $row[52] = authorship_field
* $row[53] = page_count
* $row[54] = publisher name
* $row[55] = isbn
* $row[56] = doi
*/
protected function syncConferenceAbstracts(Scientist $scientist, $row)
{
$yearRaw = trim($row[48] ?? '');
$sdNames = array_filter(array_map('trim', explode(',', $row[49] ?? '')));
$natureNames = array_filter(array_map('trim', explode(',', $row[50] ?? '')));
$title = trim($row[51] ?? '');
$authorshipField = trim($row[52] ?? '');
$pageCount = trim($row[53] ?? '');
$publisherName = trim($row[54] ?? '');
$isbn = trim($row[55] ?? '');
$doi = trim($row[56] ?? '');
if (empty($yearRaw) || empty($title)) {
return;
}
$year = $this->parseDate($yearRaw, 'Y-m-d');
$publisher = Publisher::firstOrCreate(['name' => $publisherName]);
$record = ConferenceAbstract::updateOrCreate(
['isbn' => $isbn],
[
'year' => $year,
'title' => $title,
'authorship_field' => $authorshipField,
'page_count' => $pageCount,
'publisher_id' => $publisher->id,
'doi' => $doi,
]
);
// Sync science directions
foreach ($sdNames as $sdName) {
$sd = ScienceDirection::firstOrCreate(['name' => $sdName]);
$record->science_directions()->syncWithoutDetaching($sd->id);
}
// Sync conference types (natures)
foreach ($natureNames as $natureName) {
$confType = ScientificConferenceType::firstOrCreate(['name' => $natureName]);
$record->natures()->syncWithoutDetaching($confType->id);
}
$scientist->scientistConferenceAbstracts()->syncWithoutDetaching($record->id);
}
/**
* Scientific Translations (/scientific-translations)
* $row[57] = year
* $row[58] = science-directions (comma-separated)
* $row[59] = natures[] (comma-separated) /translation-types
* $row[60] = title
* $row[61] = input_time
* $row[62] = authorship_field
* $row[63] = page_count
* $row[64] = publisher name
* $row[65] = isbn
* $row[66] = doi
*/
protected function syncScientificTranslations(Scientist $scientist, $row)
{
$yearRaw = trim($row[57] ?? '');
$sdNames = array_filter(array_map('trim', explode(',', $row[58] ?? '')));
$natureNames = array_filter(array_map('trim', explode(',', $row[59] ?? '')));
$title = trim($row[60] ?? '');
$inputTime = trim($row[61] ?? '');
$authorshipField = trim($row[62] ?? '');
$pageCount = trim($row[63] ?? '');
$publisherName = trim($row[64] ?? '');
$isbn = trim($row[65] ?? '');
$doi = trim($row[66] ?? '');
if (empty($yearRaw) || empty($title) || empty($inputTime)) {
return;
}
$year = $this->parseDate($yearRaw, 'Y-m-d');
$publisher = Publisher::firstOrCreate(['name' => $publisherName]);
$record = ScientificTranslation::updateOrCreate(
['isbn' => $isbn],
[
'year' => $year,
'title' => $title,
'input_time' => $inputTime,
'authorship_field' => $authorshipField,
'page_count' => $pageCount,
'publisher_id' => $publisher->id,
'doi' => $doi,
]
);
foreach ($sdNames as $sdName) {
$sd = ScienceDirection::firstOrCreate(['name' => $sdName]);
$record->science_directions()->syncWithoutDetaching($sd->id);
}
foreach ($natureNames as $natureName) {
$transType = TranslationType::firstOrCreate(['name' => $natureName]);
$record->natures()->syncWithoutDetaching($transType->id);
}
$scientist->scientistScientificTranslations()->syncWithoutDetaching($record->id);
}
/**
* Art Works (/art-works)
* $row[67] = year
* $row[68] = science-directions (comma-separated)
* $row[69] = title
* $row[70] = number
* $row[71] = input_time
*/
protected function syncArtWorks(Scientist $scientist, $row)
{
$yearRaw = trim($row[67] ?? '');
$sdNames = array_filter(array_map('trim', explode(',', $row[68] ?? '')));
$title = trim($row[69] ?? '');
$number = trim($row[70] ?? '');
$inputTime = trim($row[71] ?? '');
if (empty($yearRaw) || empty($title) || empty($number) || empty($inputTime)) {
return;
}
$year = $this->parseDate($yearRaw, 'Y-m-d');
$record = ArtWork::updateOrCreate(
['number' => $number],
[
'year' => $year,
'title' => $title,
'input' => $inputTime,
]
);
foreach ($sdNames as $sdName) {
$sd = ScienceDirection::firstOrCreate(['name' => $sdName]);
$record->science_directions()->syncWithoutDetaching($sd->id);
}
$scientist->scientistArtWorks()->syncWithoutDetaching($record->id);
}
/**
* Professional Developments (/professional-developments)
* $row[72] = period_from period_to (if only one value, treat it as period_from)
* $row[73] = title
* $row[74] = development_types[] (comma-separated) /professional-development-types
* $row[75] = institution
* $row[76] = country_id (find similar by name or short_code, do not create new)
* $row[77] = time_spent
*/
protected function syncProfessionalDevelopments(Scientist $scientist, $row)
{
$periodRaw = trim($row[72] ?? '');
$title = trim($row[73] ?? '');
$devTypeNames = array_filter(array_map('trim', explode(',', $row[74] ?? '')));
$institution = trim($row[75] ?? '');
$countryInput = trim($row[76] ?? '');
$timeSpent = trim($row[77] ?? '');
if (empty($periodRaw) || empty($title) || empty($institution) || empty($timeSpent)) {
return;
}
$periodParts = explode(' ', $periodRaw);
$rawFrom = trim($periodParts[0] ?? '');
$rawTo = trim($periodParts[1] ?? '');
$period_from = $rawFrom ? (is_numeric($rawFrom) ? ExcelDate::excelToDateTimeObject($rawFrom)->format('Y-m-d') : Carbon::parse($rawFrom)->format('Y-m-d')) : null;
$period_to = $rawTo ? (is_numeric($rawTo) ? ExcelDate::excelToDateTimeObject($rawTo)->format('Y-m-d') : Carbon::parse($rawTo)->format('Y-m-d')) : null;
// Look up the country (do not create a new one)
$country = $this->findCountry($countryInput);
$record = ProfessionalDevelopment::updateOrCreate(
[
'title' => $title,
'institution' => $institution,
'time_spent' => $timeSpent,
],
[
'period_from' => $period_from,
'period_to' => $period_to,
]
);
foreach ($devTypeNames as $devTypeName) {
$devType = ProfessionalDevelopmentType::firstOrCreate(['name' => $devTypeName]);
$record->development_types()->syncWithoutDetaching($devType->id);
}
if ($country) {
$record->country()->associate($country);
$record->save();
}
$scientist->scientistProfessionalDevelopments()->syncWithoutDetaching($record->id);
// Sync professional development types
}
/**
* Honors and Awards (/honors-and-awards)
* $row[78] = year
* $row[79] = title
* $row[80] = activity_type_desc
* $row[81] = institution
* $row[82] = country_id (find similar by name or short_code)
*/
protected function syncHonorsAndAwards(Scientist $scientist, $row)
{
$yearRaw = trim($row[78] ?? '');
$title = trim($row[79] ?? '');
$activityTypeDesc = trim($row[80] ?? '');
$institution = trim($row[81] ?? '');
$countryInput = trim($row[82] ?? '');
if (empty($yearRaw) || empty($title) || empty($institution)) {
return;
}
$year = $this->parseDate($yearRaw, 'Y-m-d');
$country = $this->findCountry($countryInput);
$record = HonorsAndAward::updateOrCreate(
['title' => $title, 'institution' => $institution],
[
'year' => $year,
'activity_type_desc'=> $activityTypeDesc,
]
);
if ($country) {
$record->country()->associate($country);
$record->save();
}
$scientist->scientistHonorsAndAwards()->syncWithoutDetaching($record->id);
}
/**
* Helper: Parse a date value that may be Excel serial or a formatted string.
*
* @param mixed $raw
* @param string $format
* @return string|null
*/
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);
} catch (\Exception $e) {
return null;
}
}
/**
* Helper: Find a country record by name or short_code, trying to match similar values.
*
* @param string $input
* @return \App\Models\Country|null
*/
protected function findCountry($input)
{
if (empty($input)) {
return null;
}
$country = Country::where('name', $input)
->orWhere('short_code', $input)
// ->orWhere('name', 'like', '%' . $input . '%')
->first();
return $country;
}
}