Codex
62220186af
- New ExternalImportSource and ExternalWorkHistoryEntry models, controller and migrations - Screening fields on external profile imports - Admin and provider profile views, routes and workflow tests updated - Phase 1 validation, route inventory, gap report template and roadmap docs - Add the Google Sheets work-order parser tool (tools/google-sheets-wo-parser)
449 lines
20 KiB
PHP
449 lines
20 KiB
PHP
<?php
|
|
|
|
namespace App\Http\Controllers;
|
|
|
|
use App\Models\AuditLog;
|
|
use App\Models\ExternalImportSource;
|
|
use App\Models\ExternalProfileImport;
|
|
use App\Models\ExternalWorkHistoryEntry;
|
|
use Carbon\Carbon;
|
|
use Illuminate\Http\RedirectResponse;
|
|
use Illuminate\Http\Request;
|
|
use Illuminate\Support\Facades\Storage;
|
|
use Illuminate\Support\Str;
|
|
|
|
class ExternalImportSourceController extends Controller
|
|
{
|
|
public function store(Request $request): RedirectResponse
|
|
{
|
|
$profile = $request->user()->providerProfile;
|
|
|
|
abort_unless($request->user()->hasRole('provider') && $profile, 403);
|
|
|
|
$maxKb = (int) config('provider-exchange.import_sources.max_kb');
|
|
$mimeTypes = (array) config('provider-exchange.import_sources.allowed_mime_types');
|
|
|
|
$data = $request->validate([
|
|
'platform' => ['required', 'string', 'in:'.implode(',', array_keys(ExternalImportSource::PLATFORMS))],
|
|
'source_type' => ['required', 'string', 'in:'.implode(',', array_keys(ExternalImportSource::SOURCE_TYPES))],
|
|
'external_profile_import_id' => ['nullable', 'integer', 'exists:external_profile_imports,id'],
|
|
'provider_notes' => ['nullable', 'string', 'max:4000'],
|
|
'source_file' => ['required', 'file', 'max:'.$maxKb, 'mimetypes:'.implode(',', $mimeTypes)],
|
|
]);
|
|
|
|
$import = null;
|
|
if (! empty($data['external_profile_import_id'])) {
|
|
$import = $profile->externalImports()->findOrFail((int) $data['external_profile_import_id']);
|
|
}
|
|
|
|
$file = $request->file('source_file');
|
|
$disk = (string) config('provider-exchange.import_sources.disk');
|
|
$root = trim((string) config('provider-exchange.import_sources.root'), '/');
|
|
$directory = implode('/', array_filter([
|
|
$root ?: 'external-import-sources',
|
|
Str::slug($data['platform']),
|
|
now()->format('Y'),
|
|
now()->format('m'),
|
|
]));
|
|
|
|
$source = $profile->externalImportSources()->create([
|
|
'external_profile_import_id' => $import?->id,
|
|
'uploaded_by_id' => $request->user()->id,
|
|
'platform' => $data['platform'],
|
|
'source_type' => $data['source_type'],
|
|
'parse_status' => 'pending',
|
|
'review_status' => 'needs_review',
|
|
'disk' => $disk,
|
|
'path' => $file->store($directory, $disk),
|
|
'original_name' => $file->getClientOriginalName(),
|
|
'mime_type' => $file->getClientMimeType(),
|
|
'size' => $file->getSize() ?: 0,
|
|
'provider_notes' => $data['provider_notes'] ?? null,
|
|
'uploaded_at' => now(),
|
|
]);
|
|
|
|
AuditLog::record($request, 'external_import_source.uploaded', $source, [
|
|
'platform' => $source->platform,
|
|
'source_type' => $source->source_type,
|
|
'external_profile_import_id' => $source->external_profile_import_id,
|
|
]);
|
|
|
|
return redirect()->route('providers.edit')->with('status', 'Import source uploaded privately.');
|
|
}
|
|
|
|
public function parse(Request $request, ExternalImportSource $source): RedirectResponse
|
|
{
|
|
$this->authorizeSource($request, $source);
|
|
|
|
if (! $this->isTextLike($source)) {
|
|
$source->update([
|
|
'parse_status' => 'not_parseable',
|
|
'parse_warnings' => ['This source is stored as private proof. Upload copied profile text, CSV, JSON, or HTML for automatic draft parsing.'],
|
|
]);
|
|
|
|
return redirect()->route('providers.edit')->with('status', 'Source stored as proof only. Upload text-like source material for parsing.');
|
|
}
|
|
|
|
$contents = Storage::disk($source->disk)->get($source->path);
|
|
$parsed = $source->source_type === 'csv_export'
|
|
? $this->parseFieldNationWorkHistoryCsv($contents, $source->platform)
|
|
: $this->parseFieldNationLikeText($contents, $source->platform);
|
|
|
|
$source->update([
|
|
'parse_status' => empty($parsed['fields']) ? 'failed' : 'parsed',
|
|
'parsed_payload' => $parsed['fields'],
|
|
'parse_warnings' => $parsed['warnings'],
|
|
]);
|
|
|
|
AuditLog::record($request, 'external_import_source.parsed', $source, [
|
|
'platform' => $source->platform,
|
|
'field_count' => count($parsed['fields']),
|
|
'warning_count' => count($parsed['warnings']),
|
|
]);
|
|
|
|
return redirect()->route('providers.edit')->with('status', empty($parsed['fields']) ? 'No import fields could be parsed.' : 'Import source parsed into a draft.');
|
|
}
|
|
|
|
public function approve(Request $request, ExternalImportSource $source): RedirectResponse
|
|
{
|
|
$this->authorizeSource($request, $source);
|
|
|
|
abort_unless($source->parse_status === 'parsed' && is_array($source->parsed_payload), 422, 'Parse this source before approving it.');
|
|
|
|
$data = $request->validate([
|
|
'visibility' => ['required', 'string', 'in:'.implode(',', array_keys(ExternalProfileImport::VISIBILITIES))],
|
|
'notes' => ['nullable', 'string', 'max:4000'],
|
|
]);
|
|
|
|
$payload = $source->parsed_payload;
|
|
$import = $source->externalProfileImport ?: $source->providerProfile->externalImports()->create([
|
|
'platform' => $source->platform,
|
|
'status' => 'parsed_source',
|
|
'visibility' => $data['visibility'],
|
|
'verification_status' => 'provider_attested',
|
|
'imported_at' => now(),
|
|
]);
|
|
|
|
if (! empty($payload['background_check_status']) || ! empty($payload['drug_test_status'])) {
|
|
$import->forceFill([
|
|
'background_check_status' => $payload['background_check_status'] ?? $import->background_check_status,
|
|
'background_check_date' => $payload['background_check_date'] ?? $import->background_check_date,
|
|
'drug_test_status' => $payload['drug_test_status'] ?? $import->drug_test_status,
|
|
'drug_test_date' => $payload['drug_test_date'] ?? $import->drug_test_date,
|
|
])->save();
|
|
}
|
|
|
|
$workHistoryCount = 0;
|
|
if (($payload['import_kind'] ?? null) === 'work_history_csv') {
|
|
$workHistoryCount = $this->importWorkHistoryEntries($source, $import);
|
|
}
|
|
|
|
$metrics = array_filter([
|
|
'client_count' => $payload['client_count'] ?? null,
|
|
'on_time_rate' => $payload['on_time_rate'] ?? null,
|
|
'backout_rate' => $payload['backout_rate'] ?? null,
|
|
], fn ($value): bool => $value !== null && $value !== '');
|
|
|
|
$import->update([
|
|
'platform' => $source->platform,
|
|
'external_id' => $payload['external_id'] ?? $import->external_id,
|
|
'profile_url' => $payload['profile_url'] ?? $import->profile_url,
|
|
'status' => 'parsed_source',
|
|
'visibility' => $data['visibility'],
|
|
'verification_status' => 'provider_attested',
|
|
'rating' => $payload['rating'] ?? $import->rating,
|
|
'review_count' => $payload['review_count'] ?? $import->review_count,
|
|
'completed_jobs' => $payload['completed_jobs'] ?? $import->completed_jobs,
|
|
'work_categories' => $payload['work_categories'] ?? $import->work_categories,
|
|
'endorsements' => $payload['endorsements'] ?? $import->endorsements,
|
|
'operational_metrics' => $metrics ?: $import->operational_metrics,
|
|
'metrics' => $metrics ?: $import->metrics,
|
|
'selected_reviews' => $payload['selected_reviews'] ?? $import->selected_reviews,
|
|
'review_snapshots' => $payload['selected_reviews'] ?? $import->review_snapshots,
|
|
'notes' => ($data['notes'] ?? null) ?: ($payload['summary_notes'] ?? $import->notes),
|
|
'imported_at' => now(),
|
|
]);
|
|
|
|
$source->update([
|
|
'external_profile_import_id' => $import->id,
|
|
'review_status' => 'provider_reviewed',
|
|
]);
|
|
|
|
AuditLog::record($request, 'external_import_source.approved', $source, [
|
|
'external_profile_import_id' => $import->id,
|
|
'visibility' => $data['visibility'],
|
|
'external_work_history_entries' => $workHistoryCount,
|
|
]);
|
|
|
|
return redirect()->route('providers.edit')->with('status', $workHistoryCount > 0
|
|
? "Parsed import draft approved and {$workHistoryCount} external work-history rows imported."
|
|
: 'Parsed import draft approved into imported history.');
|
|
}
|
|
|
|
private function authorizeSource(Request $request, ExternalImportSource $source): void
|
|
{
|
|
$user = $request->user();
|
|
|
|
abort_unless($source->providerProfile->user_id === $user->id || $user->hasRole('admin'), 403);
|
|
}
|
|
|
|
private function isTextLike(ExternalImportSource $source): bool
|
|
{
|
|
return str_contains((string) $source->mime_type, 'text/')
|
|
|| in_array($source->mime_type, ['application/json'], true)
|
|
|| in_array($source->source_type, ['csv_export', 'json_export', 'html_export', 'plain_text', 'email_export'], true);
|
|
}
|
|
|
|
private function parseFieldNationLikeText(string $contents, string $platform): array
|
|
{
|
|
$text = html_entity_decode(strip_tags($contents));
|
|
$normalized = preg_replace('/[ \t]+/', ' ', str_replace(["\r\n", "\r"], "\n", $text)) ?? $text;
|
|
$fields = [];
|
|
$warnings = [];
|
|
|
|
$patterns = [
|
|
'rating' => '/^(?:.*\b(?:rating|average rating|overall rating)\b)\s*:\s*([0-5](?:\.\d{1,2})?)/mi',
|
|
'review_count' => '/^(?:.*\b(?:review count|reviews?)\b)\s*:\s*([\d,]{1,8})/mi',
|
|
'completed_jobs' => '/^(?:.*\b(?:completed jobs?|jobs completed|work orders completed|completed work orders?)\b)\s*:\s*([\d,]{1,8})/mi',
|
|
'client_count' => '/^(?:.*\b(?:clients?|buyers?|customers?)\b)\s*:\s*([\d,]{1,8})/mi',
|
|
'on_time_rate' => '/^(?:.*\b(?:on[- ]time|on time rate)\b)\s*:\s*(\d{1,3}(?:\.\d{1,2})?)\s*%/mi',
|
|
'backout_rate' => '/^(?:.*\b(?:backout|back out|cancellation)\b)\s*:\s*(\d{1,3}(?:\.\d{1,2})?)\s*%/mi',
|
|
];
|
|
|
|
foreach ($patterns as $field => $pattern) {
|
|
if (preg_match($pattern, $normalized, $match)) {
|
|
$value = str_replace(',', '', $match[1]);
|
|
$fields[$field] = in_array($field, ['rating', 'on_time_rate', 'backout_rate'], true) ? (float) $value : (int) $value;
|
|
}
|
|
}
|
|
|
|
if (! isset($fields['rating'], $fields['review_count'])
|
|
&& preg_match('/\bAll time\s+([0-5](?:\.\d{1,2})?)\s+Marketplace\s+([\d,]{1,8})\s+ratings\b/i', $normalized, $match)) {
|
|
$fields['rating'] ??= (float) $match[1];
|
|
$fields['review_count'] ??= (int) str_replace(',', '', $match[2]);
|
|
}
|
|
|
|
if (! isset($fields['completed_jobs'])
|
|
&& preg_match('/\b([\d,]{1,8})\s+jobs completed\b/i', $normalized, $match)) {
|
|
$fields['completed_jobs'] = (int) str_replace(',', '', $match[1]);
|
|
}
|
|
|
|
if (! isset($fields['client_count'])
|
|
&& preg_match('/\b([\d,]{1,8})\s+clients\b/i', $normalized, $match)) {
|
|
$fields['client_count'] = (int) str_replace(',', '', $match[1]);
|
|
}
|
|
|
|
if (! isset($fields['on_time_rate'])
|
|
&& preg_match('/\bTimeliness Score\s+(\d{1,3}(?:\.\d{1,2})?)\s*%/i', $normalized, $match)) {
|
|
$fields['on_time_rate'] = (float) $match[1];
|
|
}
|
|
|
|
if (! isset($fields['backout_rate'])
|
|
&& preg_match('/\bBackout Score\s+(\d{1,3}(?:\.\d{1,2})?)\s*%/i', $normalized, $match)) {
|
|
$fields['backout_rate'] = (float) $match[1];
|
|
}
|
|
|
|
if (preg_match('/https?:\/\/[^\s"]*fieldnation[^\s"]*/i', $contents, $match)) {
|
|
$fields['profile_url'] = rtrim($match[0], '.,)');
|
|
}
|
|
|
|
if (preg_match('/(?:provider id|profile id|field nation id|external id)\D+([A-Za-z0-9-]+)/i', $normalized, $match)) {
|
|
$fields['external_id'] = $match[1];
|
|
} elseif (preg_match('/ID\s+(\d{3,})\b/i', $normalized, $match)) {
|
|
$fields['external_id'] = $match[1];
|
|
}
|
|
|
|
if (preg_match('/Background Check\s+(Passed|Failed|Expired|Pending)\s+on\s+([A-Za-z]{3,9}\s+\d{4})/i', $normalized, $match)) {
|
|
$fields['background_check_status'] = strtolower($match[1]);
|
|
$fields['background_check_date'] = $match[2];
|
|
}
|
|
|
|
if (preg_match('/Drug Test\s+(Passed|Failed|Expired|Pending)\s+on\s+([A-Za-z]{3,9}\s+\d{4})/i', $normalized, $match)) {
|
|
$fields['drug_test_status'] = strtolower($match[1]);
|
|
$fields['drug_test_date'] = $match[2];
|
|
}
|
|
|
|
$categoryCandidates = ['POS', 'Point of Sale', 'Networking', 'Wireless', 'Cabling', 'Low Voltage', 'Fiber', 'Printer', 'Server', 'Telecom', 'Digital Signage', 'Camera', 'Kiosk'];
|
|
$fields['work_categories'] = collect($categoryCandidates)
|
|
->filter(fn (string $category): bool => stripos($normalized, $category) !== false)
|
|
->values()
|
|
->all();
|
|
|
|
$endorsementCandidates = [
|
|
'communication',
|
|
'professionalism',
|
|
'problem_solving',
|
|
'preparedness',
|
|
'work_quality',
|
|
'responsiveness',
|
|
];
|
|
$fields['endorsements'] = collect($endorsementCandidates)
|
|
->filter(fn (string $endorsement): bool => stripos($normalized, str_replace('_', ' ', $endorsement)) !== false)
|
|
->values()
|
|
->all();
|
|
|
|
$fields['selected_reviews'] = collect(explode("\n", $normalized))
|
|
->map(fn (string $line): string => trim($line, " \t\n\r\0\x0B-\"'"))
|
|
->filter(fn (string $line): bool => strlen($line) >= 20 && preg_match('/\b(reliable|professional|prepared|communicat|excellent|great|quality|responsive|recommend)\b/i', $line))
|
|
->take(5)
|
|
->values()
|
|
->all();
|
|
|
|
$fields = array_filter($fields, fn ($value): bool => ! ($value === [] || $value === null || $value === ''));
|
|
|
|
foreach (['rating', 'review_count', 'completed_jobs'] as $expected) {
|
|
if (! array_key_exists($expected, $fields)) {
|
|
$warnings[] = "Could not confidently parse {$expected}.";
|
|
}
|
|
}
|
|
|
|
if ($platform !== 'Field Nation') {
|
|
$warnings[] = 'Parser uses the first-pass generic marketplace parser until a platform-specific adapter is added.';
|
|
}
|
|
|
|
return ['fields' => $fields, 'warnings' => $warnings];
|
|
}
|
|
|
|
private function parseFieldNationWorkHistoryCsv(string $contents, string $platform): array
|
|
{
|
|
$rows = $this->parseCsvRows($contents);
|
|
$warnings = [];
|
|
|
|
if ($rows === []) {
|
|
return [
|
|
'fields' => [],
|
|
'warnings' => ['Could not parse any CSV rows from this work-history source.'],
|
|
];
|
|
}
|
|
|
|
$historyRows = collect($rows)
|
|
->map(fn (array $row): array => $this->normalizeFieldNationWorkHistoryRow($row))
|
|
->filter(fn (array $row): bool => (bool) ($row['service_title'] ?? null))
|
|
->values();
|
|
|
|
if ($historyRows->isEmpty()) {
|
|
return [
|
|
'fields' => [],
|
|
'warnings' => ['CSV rows were readable, but no Field Nation work-history rows could be recognized.'],
|
|
];
|
|
}
|
|
|
|
if ($platform !== 'Field Nation') {
|
|
$warnings[] = 'CSV parser currently expects the Field Nation work-history column layout.';
|
|
}
|
|
|
|
return [
|
|
'fields' => [
|
|
'import_kind' => 'work_history_csv',
|
|
'row_count' => $historyRows->count(),
|
|
'sample_rows' => $historyRows->take(5)->all(),
|
|
],
|
|
'warnings' => $warnings,
|
|
];
|
|
}
|
|
|
|
private function importWorkHistoryEntries(ExternalImportSource $source, ExternalProfileImport $import): int
|
|
{
|
|
$contents = Storage::disk($source->disk)->get($source->path);
|
|
$rows = collect($this->parseCsvRows($contents))
|
|
->map(fn (array $row): array => $this->normalizeFieldNationWorkHistoryRow($row))
|
|
->filter(fn (array $row): bool => (bool) ($row['service_title'] ?? null))
|
|
->values();
|
|
|
|
$count = 0;
|
|
foreach ($rows as $row) {
|
|
$externalWorkOrderId = $row['external_work_order_id'] ?: md5(json_encode($row));
|
|
ExternalWorkHistoryEntry::updateOrCreate([
|
|
'external_profile_import_id' => $import->id,
|
|
'platform' => $source->platform,
|
|
'external_work_order_id' => $externalWorkOrderId,
|
|
], [
|
|
'provider_profile_id' => $source->provider_profile_id,
|
|
'external_import_source_id' => $source->id,
|
|
'service_title' => $row['service_title'],
|
|
'performed_on' => $row['performed_on'],
|
|
'customer_name' => $row['customer_name'],
|
|
'work_type' => $row['work_type'],
|
|
'location' => $row['location'],
|
|
'rating' => $row['rating'],
|
|
'status' => $row['status'],
|
|
'source_url' => $row['source_url'],
|
|
'visibility' => 'private',
|
|
'raw_payload' => $row['raw_payload'],
|
|
]);
|
|
$count++;
|
|
}
|
|
|
|
return $count;
|
|
}
|
|
|
|
private function parseCsvRows(string $contents): array
|
|
{
|
|
$lines = preg_split('/\r\n|\r|\n/', trim($contents));
|
|
if (! $lines || count($lines) < 2) {
|
|
return [];
|
|
}
|
|
|
|
$headers = array_map(fn (string $header): string => Str::of($header)->trim()->lower()->replace(' ', '_')->toString(), str_getcsv(array_shift($lines), ',', '"', '\\'));
|
|
|
|
return collect($lines)
|
|
->map(fn (string $line): array => str_getcsv($line, ',', '"', '\\'))
|
|
->filter(fn (array $cells): bool => collect($cells)->filter(fn ($cell): bool => trim((string) $cell) !== '')->isNotEmpty())
|
|
->map(fn (array $cells): array => collect($headers)
|
|
->mapWithKeys(fn (string $header, int $index): array => [$header => $cells[$index] ?? null])
|
|
->all())
|
|
->all();
|
|
}
|
|
|
|
private function normalizeFieldNationWorkHistoryRow(array $row): array
|
|
{
|
|
$sourceUrl = trim((string) ($row['work_order_url'] ?? $row['source_url'] ?? ''));
|
|
$serviceTitle = trim(preg_replace('/\s+none$/i', '', (string) ($row['service_title'] ?? '')) ?? '');
|
|
|
|
return [
|
|
'external_work_order_id' => $this->extractWorkOrderId($sourceUrl),
|
|
'service_title' => $serviceTitle,
|
|
'performed_on' => $this->parseFieldNationDate((string) ($row['date'] ?? '')),
|
|
'customer_name' => trim((string) ($row['customer'] ?? '')) ?: null,
|
|
'work_type' => trim((string) ($row['type_of_work'] ?? '')) ?: null,
|
|
'location' => trim((string) ($row['location'] ?? '')) ?: null,
|
|
'rating' => $this->parseRating((string) ($row['rating'] ?? '')),
|
|
'status' => trim((string) ($row['status'] ?? '')) ?: null,
|
|
'source_url' => $sourceUrl ?: null,
|
|
'raw_payload' => $row,
|
|
];
|
|
}
|
|
|
|
private function extractWorkOrderId(string $sourceUrl): ?string
|
|
{
|
|
if (preg_match('/\/wo\/(\d+)/', $sourceUrl, $match)) {
|
|
return $match[1];
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
private function parseFieldNationDate(string $date): ?string
|
|
{
|
|
$clean = trim(preg_replace('/\b(\d{1,2})(st|nd|rd|th)\b/i', '$1', $date) ?? '');
|
|
if ($clean === '') {
|
|
return null;
|
|
}
|
|
|
|
try {
|
|
return Carbon::createFromFormat('M j, Y', $clean)->toDateString();
|
|
} catch (\Throwable) {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
private function parseRating(string $rating): ?float
|
|
{
|
|
if (preg_match('/([0-5](?:\.\d{1,2})?)\s*$/', $rating, $match)) {
|
|
return (float) $match[1];
|
|
}
|
|
|
|
return null;
|
|
}
|
|
}
|