1209 lines
47 KiB
PHP
1209 lines
47 KiB
PHP
<?php
|
|
require_once __DIR__ . '/../vendor/autoload.php';
|
|
require_once __DIR__ . '/callcenter_test_helpers.php';
|
|
|
|
function drive_test_normalize_header(string $header): string
|
|
{
|
|
$header = trim($header);
|
|
$header = preg_replace('/\s+/', ' ', $header ?? '');
|
|
$header = strtoupper((string) $header);
|
|
return $header;
|
|
}
|
|
|
|
function drive_test_available_stores(): array
|
|
{
|
|
$stores = [
|
|
// Hoja original que ya venía funcionando.
|
|
'flower' => [
|
|
'label' => 'Flower',
|
|
'spreadsheet_id' => '1SSmQuR9quxeQbMKNMDkRe8-n1gU7WuEfsFaJ3WKFO-c',
|
|
'sheet_gid' => null,
|
|
'startRow' => 8687, // Inicio solicitado para Flower
|
|
'status_sync_start_row' => 8687,
|
|
'status_sync_column' => 'V',
|
|
'advisor_sync_start_row' => 8687,
|
|
'advisor_sync_column' => 'X',
|
|
'delivery_sync_start_row' => 8687,
|
|
'delivery_sync_column' => 'W',
|
|
'min_codigo_number' => 37431,
|
|
'recoverables_store_key' => 'flower_recuperables',
|
|
'enforce_db_start_row' => true,
|
|
'incremental_sync' => true,
|
|
],
|
|
// Nueva bandeja recuperable de Flower desde la pestaña compartida.
|
|
'flower_recuperables' => [
|
|
'label' => 'Carritos recuperables Flower',
|
|
'sheet_title' => 'easysell_abandoneds',
|
|
'spreadsheet_id' => '1SSmQuR9quxeQbMKNMDkRe8-n1gU7WuEfsFaJ3WKFO-c',
|
|
'startRow' => 1503,
|
|
'status_sync_start_row' => 1503,
|
|
'status_sync_column' => 'V',
|
|
'advisor_sync_start_row' => 1503,
|
|
'advisor_sync_column' => 'X',
|
|
'delivery_sync_start_row' => 1503,
|
|
'delivery_sync_column' => 'W',
|
|
'main_store_key' => 'flower',
|
|
'enforce_db_start_row' => true,
|
|
'incremental_sync' => true,
|
|
],
|
|
// Segunda tienda (pedido principal de TUANI).
|
|
'otra_tienda' => [
|
|
'label' => 'TUANI',
|
|
'sheet_title' => 'TUANI PEDIDOS',
|
|
'spreadsheet_id' => '1QYKeBJIIqYm6yW6Ka-5gKdxhUHwXOAc0No7RjnimxTw',
|
|
'sheet_gid' => 1523126328,
|
|
'startRow' => 6804,
|
|
'recoverables_store_key' => 'tuani_recuperables',
|
|
'enforce_db_start_row' => true,
|
|
'incremental_sync' => true,
|
|
'status_sync_start_row' => 7207,
|
|
'status_sync_column' => 'V',
|
|
'advisor_sync_start_row' => 7207,
|
|
'advisor_sync_column' => 'X',
|
|
'delivery_sync_start_row' => 7291,
|
|
'delivery_sync_column' => 'W',
|
|
],
|
|
// Bandeja compartida de carritos recuperables Tuani/abandonados: misma estructura, pero desde la fila 2000.
|
|
'tuani_recuperables' => [
|
|
'label' => 'Carritos recuperables Tuani',
|
|
'sheet_title' => 'easysell_abandoneds',
|
|
'spreadsheet_id' => '1QYKeBJIIqYm6yW6Ka-5gKdxhUHwXOAc0No7RjnimxTw',
|
|
'sheet_gid' => 965530888,
|
|
'startRow' => 2000,
|
|
'status_sync_start_row' => 2000,
|
|
'status_sync_column' => 'V',
|
|
'advisor_sync_start_row' => 2000,
|
|
'advisor_sync_column' => 'X',
|
|
'delivery_sync_start_row' => 2000,
|
|
'delivery_sync_column' => 'W',
|
|
'main_store_key' => 'otra_tienda',
|
|
'enforce_db_start_row' => true,
|
|
'incremental_sync' => true,
|
|
],
|
|
];
|
|
|
|
if (function_exists('cc_test_filter_visible_drive_stores')) {
|
|
$stores = cc_test_filter_visible_drive_stores($stores);
|
|
}
|
|
|
|
return $stores;
|
|
}
|
|
|
|
function drive_test_store_navigation_groups(array $availableStores): array
|
|
{
|
|
$groups = [];
|
|
$childBuckets = [];
|
|
|
|
foreach ($availableStores as $key => $config) {
|
|
$parentKey = trim((string) ($config['parent_store_key'] ?? ''));
|
|
if ($parentKey !== '' && isset($availableStores[$parentKey])) {
|
|
if (!isset($childBuckets[$parentKey])) {
|
|
$childBuckets[$parentKey] = [];
|
|
}
|
|
$childBuckets[$parentKey][$key] = $config;
|
|
continue;
|
|
}
|
|
|
|
$groups[$key] = [
|
|
'key' => $key,
|
|
'config' => $config,
|
|
'children' => [],
|
|
];
|
|
}
|
|
|
|
foreach ($groups as $groupKey => &$group) {
|
|
if (isset($childBuckets[$groupKey])) {
|
|
$group['children'] = $childBuckets[$groupKey];
|
|
}
|
|
}
|
|
unset($group);
|
|
|
|
return $groups;
|
|
}
|
|
|
|
function drive_test_store_navigation_state(array $availableStores, string $storeKey): array
|
|
{
|
|
$storeKey = mb_strtolower(trim($storeKey));
|
|
$groups = drive_test_store_navigation_groups($availableStores);
|
|
$activeGroupKey = $storeKey;
|
|
$activeGroup = $groups[$storeKey] ?? null;
|
|
|
|
if ($activeGroup === null) {
|
|
foreach ($groups as $groupKey => $group) {
|
|
if (isset($group['children'][$storeKey])) {
|
|
$activeGroupKey = (string) $groupKey;
|
|
$activeGroup = $group;
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
if ($activeGroup === null) {
|
|
$activeGroup = [
|
|
'key' => $storeKey,
|
|
'config' => $availableStores[$storeKey] ?? [],
|
|
'children' => [],
|
|
];
|
|
}
|
|
|
|
$parentKey = trim((string) ($availableStores[$storeKey]['parent_store_key'] ?? ''));
|
|
$parentLabel = '';
|
|
if ($parentKey !== '' && isset($availableStores[$parentKey])) {
|
|
$parentLabel = (string) ($availableStores[$parentKey]['label'] ?? strtoupper($parentKey));
|
|
}
|
|
|
|
return [
|
|
'groups' => $groups,
|
|
'active_group_key' => $activeGroupKey,
|
|
'active_group' => $activeGroup,
|
|
'active_group_label' => (string) ($activeGroup['config']['label'] ?? strtoupper((string) $activeGroupKey)),
|
|
'active_group_children' => $activeGroup['children'] ?? [],
|
|
'parent_label' => $parentLabel,
|
|
];
|
|
}
|
|
|
|
function drive_test_get_store_config(string $storeKey): array
|
|
{
|
|
$storeKey = trim(mb_strtolower($storeKey));
|
|
$stores = drive_test_available_stores();
|
|
|
|
if (!isset($stores[$storeKey])) {
|
|
return $stores['flower'];
|
|
}
|
|
|
|
return $stores[$storeKey] + ['key' => $storeKey];
|
|
}
|
|
|
|
function drive_test_status_sync_start_row(string $storeKey): ?int
|
|
{
|
|
$storeConfig = drive_test_get_store_config($storeKey);
|
|
if (!array_key_exists('status_sync_start_row', $storeConfig)) {
|
|
return null;
|
|
}
|
|
|
|
$startRow = (int) $storeConfig['status_sync_start_row'];
|
|
return $startRow > 0 ? $startRow : null;
|
|
}
|
|
|
|
function drive_test_status_sync_start_date(string $storeKey): ?DateTimeImmutable
|
|
{
|
|
$storeConfig = drive_test_get_store_config($storeKey);
|
|
$rawDate = trim((string) ($storeConfig['status_sync_start_date'] ?? ''));
|
|
if ($rawDate === '') {
|
|
return null;
|
|
}
|
|
|
|
try {
|
|
return new DateTimeImmutable($rawDate);
|
|
} catch (Throwable $exception) {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
function drive_test_status_sync_column(string $storeKey): string
|
|
{
|
|
$storeConfig = drive_test_get_store_config($storeKey);
|
|
$column = strtoupper(trim((string) ($storeConfig['status_sync_column'] ?? 'V')));
|
|
|
|
return preg_match('/^[A-Z]+$/', $column) ? $column : 'V';
|
|
}
|
|
|
|
function drive_test_advisor_sync_start_row(string $storeKey): ?int
|
|
{
|
|
$storeConfig = drive_test_get_store_config($storeKey);
|
|
if (!array_key_exists('advisor_sync_start_row', $storeConfig)) {
|
|
return null;
|
|
}
|
|
|
|
$startRow = (int) $storeConfig['advisor_sync_start_row'];
|
|
return $startRow > 0 ? $startRow : null;
|
|
}
|
|
|
|
function drive_test_advisor_sync_column(string $storeKey): string
|
|
{
|
|
$storeConfig = drive_test_get_store_config($storeKey);
|
|
$column = strtoupper(trim((string) ($storeConfig['advisor_sync_column'] ?? 'R')));
|
|
|
|
return preg_match('/^[A-Z]+$/', $column) ? $column : 'R';
|
|
}
|
|
|
|
function drive_test_delivery_sync_start_row(string $storeKey): ?int
|
|
{
|
|
$storeConfig = drive_test_get_store_config($storeKey);
|
|
if (!array_key_exists('delivery_sync_start_row', $storeConfig)) {
|
|
return null;
|
|
}
|
|
|
|
$startRow = (int) $storeConfig['delivery_sync_start_row'];
|
|
return $startRow > 0 ? $startRow : null;
|
|
}
|
|
|
|
function drive_test_delivery_sync_column(string $storeKey): string
|
|
{
|
|
$storeConfig = drive_test_get_store_config($storeKey);
|
|
$column = strtoupper(trim((string) ($storeConfig['delivery_sync_column'] ?? 'W')));
|
|
|
|
return preg_match('/^[A-Z]+$/', $column) ? $column : 'W';
|
|
}
|
|
|
|
function drive_test_format_programmed_delivery_label(?string $deliveryDate): string
|
|
{
|
|
$deliveryDate = trim((string) $deliveryDate);
|
|
if ($deliveryDate === '') {
|
|
return '';
|
|
}
|
|
|
|
$parsedDeliveryDate = DateTimeImmutable::createFromFormat('Y-m-d', $deliveryDate);
|
|
if (!$parsedDeliveryDate) {
|
|
return '';
|
|
}
|
|
|
|
return 'PROGRAMADO ' . $parsedDeliveryDate->format('d');
|
|
}
|
|
|
|
function drive_test_format_sheet_w_label(string $estado, ?string $advisorName = null, ?string $deliveryDate = null): string
|
|
{
|
|
$estado = cc_test_normalize_state(trim($estado));
|
|
$advisorName = drive_test_compact_text($advisorName);
|
|
|
|
if ($estado === 'CONFIRMADO CONTRAENTREGA') {
|
|
return drive_test_format_programmed_delivery_label($deliveryDate);
|
|
}
|
|
|
|
$labelMap = [
|
|
'SE ENVIO NUMERO DE CUENTA' => 'SE ENVIO NUMERO DE CUENTA',
|
|
'DEVOLVER LLAMADA' => 'DEVOLVER LLAMADA',
|
|
'OBSERVADO' => 'OBSERVADO',
|
|
'CANCELADO' => 'CANCELADO',
|
|
'REPETIDO' => 'REPETIDO',
|
|
'CONFIRMADO ENVIO' => 'ENVIO',
|
|
];
|
|
|
|
if (!isset($labelMap[$estado])) {
|
|
return '';
|
|
}
|
|
|
|
$label = $labelMap[$estado];
|
|
if ($advisorName !== '') {
|
|
$label .= ' - ' . $advisorName;
|
|
}
|
|
|
|
return $label;
|
|
}
|
|
|
|
function drive_test_status_sync_reference_datetime(array $sourceOrder): ?DateTimeImmutable
|
|
{
|
|
$candidates = [
|
|
trim((string) ($sourceOrder['first_seen_at'] ?? '')),
|
|
trim((string) ($sourceOrder['drive_imported_at'] ?? '')),
|
|
trim((string) ($sourceOrder['updated_at'] ?? '')),
|
|
];
|
|
|
|
foreach ($candidates as $candidate) {
|
|
if ($candidate === '') {
|
|
continue;
|
|
}
|
|
|
|
try {
|
|
return new DateTimeImmutable($candidate);
|
|
} catch (Throwable $exception) {
|
|
continue;
|
|
}
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
function drive_test_should_sync_status_to_sheet(array $sourceOrder): bool
|
|
{
|
|
$storeKey = trim((string) ($sourceOrder['store_key'] ?? ''));
|
|
if ($storeKey === '') {
|
|
return false;
|
|
}
|
|
|
|
$sourceRow = (int) ($sourceOrder['source_row'] ?? 0);
|
|
if ($sourceRow <= 0) {
|
|
return false;
|
|
}
|
|
|
|
$cutoffRow = drive_test_status_sync_start_row($storeKey);
|
|
if ($cutoffRow !== null) {
|
|
return $sourceRow >= $cutoffRow;
|
|
}
|
|
|
|
$cutoff = drive_test_status_sync_start_date($storeKey);
|
|
if ($cutoff === null) {
|
|
return false;
|
|
}
|
|
|
|
$referenceDate = drive_test_status_sync_reference_datetime($sourceOrder);
|
|
if ($referenceDate === null) {
|
|
return false;
|
|
}
|
|
|
|
return $referenceDate->format('Y-m-d') >= $cutoff->format('Y-m-d');
|
|
}
|
|
|
|
function drive_test_sync_status_to_sheet(array $sourceOrder, string $estado, ?string $advisorName = null, ?string $deliveryDate = null): array
|
|
{
|
|
$storeKey = trim((string) ($sourceOrder['store_key'] ?? ''));
|
|
if ($storeKey === '') {
|
|
return [
|
|
'success' => false,
|
|
'skipped' => true,
|
|
'reason' => 'missing_store_key',
|
|
];
|
|
}
|
|
|
|
if (!drive_test_should_sync_status_to_sheet($sourceOrder)) {
|
|
return [
|
|
'success' => true,
|
|
'skipped' => true,
|
|
'reason' => 'before_cutoff',
|
|
];
|
|
}
|
|
|
|
$sourceRow = (int) ($sourceOrder['source_row'] ?? 0);
|
|
if ($sourceRow <= 0) {
|
|
return [
|
|
'success' => false,
|
|
'skipped' => true,
|
|
'reason' => 'missing_source_row',
|
|
];
|
|
}
|
|
|
|
$storeConfig = drive_test_get_store_config($storeKey);
|
|
$sheetTitle = trim((string) ($storeConfig['sheet_title'] ?? ''));
|
|
$spreadsheetId = trim((string) ($storeConfig['spreadsheet_id'] ?? ''));
|
|
if ($spreadsheetId === '') {
|
|
throw new RuntimeException('Configuración de Drive para la tienda no válida.');
|
|
}
|
|
|
|
$credentialsPath = __DIR__ . '/../google_credentials.json';
|
|
if (!file_exists($credentialsPath)) {
|
|
throw new RuntimeException('No se encontró el archivo de credenciales de Google.');
|
|
}
|
|
|
|
$statusColumn = drive_test_status_sync_column($storeKey);
|
|
$sheetGid = array_key_exists('sheet_gid', $storeConfig) ? ($storeConfig['sheet_gid'] === null ? null : (int) $storeConfig['sheet_gid']) : null;
|
|
|
|
$client = new Google\Client();
|
|
$client->setAuthConfig($credentialsPath);
|
|
$client->addScope(Google\Service\Sheets::SPREADSHEETS);
|
|
|
|
$service = new Google\Service\Sheets($client);
|
|
$statusRange = drive_test_resolve_sheet_a1_range($service, $spreadsheetId, $sheetGid, $statusColumn . $sourceRow, $sheetTitle);
|
|
$estadoNormalizado = cc_test_normalize_state(trim($estado));
|
|
|
|
$deliveryDate = trim((string) $deliveryDate);
|
|
if ($deliveryDate !== '') {
|
|
$parsedDeliveryDate = DateTimeImmutable::createFromFormat('Y-m-d', $deliveryDate);
|
|
$deliveryDate = $parsedDeliveryDate ? $parsedDeliveryDate->format('Y-m-d') : '';
|
|
}
|
|
|
|
$advisorName = drive_test_compact_text($advisorName);
|
|
$deliveryLabel = drive_test_format_sheet_w_label($estadoNormalizado, $advisorName, $deliveryDate);
|
|
|
|
$updates = [
|
|
new Google\Service\Sheets\ValueRange([
|
|
'range' => $statusRange,
|
|
'values' => [[ $estadoNormalizado ]],
|
|
]),
|
|
];
|
|
|
|
$deliveryRange = null;
|
|
$deliveryStartRow = drive_test_delivery_sync_start_row($storeKey);
|
|
if ($deliveryStartRow !== null && $sourceRow >= $deliveryStartRow) {
|
|
$deliveryColumn = drive_test_delivery_sync_column($storeKey);
|
|
$deliveryRange = drive_test_resolve_sheet_a1_range($service, $spreadsheetId, $sheetGid, $deliveryColumn . $sourceRow, $sheetTitle);
|
|
$updates[] = new Google\Service\Sheets\ValueRange([
|
|
'range' => $deliveryRange,
|
|
'values' => [[ $deliveryLabel ]],
|
|
]);
|
|
}
|
|
|
|
$advisorRange = null;
|
|
$advisorStartRow = drive_test_advisor_sync_start_row($storeKey);
|
|
if ($advisorName !== '' && $advisorStartRow !== null && $sourceRow >= $advisorStartRow) {
|
|
$advisorColumn = drive_test_advisor_sync_column($storeKey);
|
|
$advisorRange = drive_test_resolve_sheet_a1_range($service, $spreadsheetId, $sheetGid, $advisorColumn . $sourceRow, $sheetTitle);
|
|
$updates[] = new Google\Service\Sheets\ValueRange([
|
|
'range' => $advisorRange,
|
|
'values' => [[ $advisorName ]],
|
|
]);
|
|
}
|
|
|
|
$body = new Google\Service\Sheets\BatchUpdateValuesRequest([
|
|
'data' => $updates,
|
|
'valueInputOption' => 'RAW',
|
|
]);
|
|
$service->spreadsheets_values->batchUpdate($spreadsheetId, $body);
|
|
|
|
return [
|
|
'success' => true,
|
|
'skipped' => false,
|
|
'store_key' => $storeKey,
|
|
'source_row' => $sourceRow,
|
|
'range' => $statusRange,
|
|
'advisor_range' => $advisorRange,
|
|
'delivery_range' => $deliveryRange,
|
|
'estado' => $estadoNormalizado,
|
|
'advisor_name' => $advisorRange !== null ? $advisorName : null,
|
|
'delivery_date' => $deliveryRange !== null ? $deliveryDate : null,
|
|
'delivery_label' => $deliveryRange !== null ? $deliveryLabel : null,
|
|
];
|
|
}
|
|
|
|
function drive_test_resolve_sheet_a1_range($service, string $spreadsheetId, ?int $sheetGid, string $rangeA1, ?string $sheetTitle = null): string
|
|
{
|
|
$sheetTitle = trim((string) $sheetTitle);
|
|
if ($sheetGid === null) {
|
|
if ($sheetTitle !== '') {
|
|
$escapedTitle = str_replace("'", "''", $sheetTitle);
|
|
return "'{$escapedTitle}'!{$rangeA1}";
|
|
}
|
|
|
|
return $rangeA1;
|
|
}
|
|
|
|
static $cache = [];
|
|
$cacheKey = $spreadsheetId . '|' . (string) $sheetGid . '|' . $rangeA1;
|
|
if (isset($cache[$cacheKey])) {
|
|
return $cache[$cacheKey];
|
|
}
|
|
|
|
try {
|
|
$meta = $service->spreadsheets->get($spreadsheetId, ['fields' => 'sheets(properties(sheetId,title))']);
|
|
foreach (($meta->getSheets() ?? []) as $sheet) {
|
|
$props = $sheet->getProperties();
|
|
if (!$props) {
|
|
continue;
|
|
}
|
|
|
|
if ((int) $props->getSheetId() === (int) $sheetGid) {
|
|
$title = trim((string) ($props->getTitle() ?? ''));
|
|
if ($title !== '') {
|
|
// Sheet names with spaces/special chars must be quoted.
|
|
$escaped = str_replace("'", "''", $title);
|
|
$cache[$cacheKey] = "'{$escaped}'!{$rangeA1}";
|
|
return $cache[$cacheKey];
|
|
}
|
|
}
|
|
}
|
|
} catch (Throwable $exception) {
|
|
// Fallback to default range.
|
|
}
|
|
|
|
$cache[$cacheKey] = $rangeA1;
|
|
return $rangeA1;
|
|
}
|
|
|
|
function drive_test_get_cell(array $row, array $indexes, array $aliases, string $default = ''): string
|
|
{
|
|
foreach ($aliases as $alias) {
|
|
$normalized = drive_test_normalize_header($alias);
|
|
if (array_key_exists($normalized, $indexes)) {
|
|
return trim((string) ($row[$indexes[$normalized]] ?? $default));
|
|
}
|
|
}
|
|
|
|
return $default;
|
|
}
|
|
|
|
function drive_test_compact_text(?string $value): string
|
|
{
|
|
$value = trim((string) $value);
|
|
if ($value === '') {
|
|
return '';
|
|
}
|
|
|
|
$normalized = preg_replace('/\s+/u', ' ', $value);
|
|
return trim((string) ($normalized ?? $value));
|
|
}
|
|
|
|
function drive_test_extract_codigo_number(?string $codigo): ?int
|
|
{
|
|
$digits = preg_replace('/\D+/', '', trim((string) $codigo));
|
|
if ($digits === '') {
|
|
return null;
|
|
}
|
|
|
|
return (int) $digits;
|
|
}
|
|
|
|
function drive_test_fetch_orders(int $limit = 10, ?int $startRow = null, string $storeKey = 'flower'): array
|
|
{
|
|
$storeConfig = drive_test_get_store_config($storeKey);
|
|
$sheetTitle = trim((string) ($storeConfig['sheet_title'] ?? ''));
|
|
if ($startRow === null || $startRow <= 0) {
|
|
$startRow = (int) ($storeConfig['startRow'] ?? 0);
|
|
}
|
|
|
|
$credentialsPath = __DIR__ . '/../google_credentials.json';
|
|
$spreadsheetId = (string) ($storeConfig['spreadsheet_id'] ?? '');
|
|
$sheetGid = array_key_exists('sheet_gid', $storeConfig) ? ($storeConfig['sheet_gid'] === null ? null : (int) $storeConfig['sheet_gid']) : null;
|
|
$rangeA1 = 'A:Z';
|
|
|
|
if ($spreadsheetId === '') {
|
|
throw new RuntimeException('Configuración de Drive para la tienda no válida.');
|
|
}
|
|
|
|
if (!file_exists($credentialsPath)) {
|
|
throw new RuntimeException('No se encontró el archivo de credenciales de Google.');
|
|
}
|
|
|
|
$client = new Google\Client();
|
|
$client->setAuthConfig($credentialsPath);
|
|
$client->addScope(Google\Service\Sheets::SPREADSHEETS_READONLY);
|
|
|
|
$service = new Google\Service\Sheets($client);
|
|
|
|
$range = drive_test_resolve_sheet_a1_range($service, $spreadsheetId, $sheetGid, $rangeA1, $sheetTitle);
|
|
|
|
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
|
|
$values = $response->getValues();
|
|
|
|
if (empty($values)) {
|
|
return [
|
|
'headers' => [],
|
|
'orders' => [],
|
|
'total_rows' => 0,
|
|
];
|
|
}
|
|
|
|
$headers = $values[0] ?? [];
|
|
$headerIndexes = [];
|
|
foreach ($headers as $index => $header) {
|
|
$normalized = drive_test_normalize_header((string) $header);
|
|
if ($normalized === '') {
|
|
$normalized = $index === 0 ? 'CODIGO' : 'COL_' . $index;
|
|
}
|
|
$headerIndexes[$normalized] = $index;
|
|
}
|
|
|
|
$dataRows = array_slice($values, 1);
|
|
|
|
$startIndex = max(0, $startRow - 2);
|
|
$totalDataRows = count($dataRows);
|
|
|
|
if ($startIndex >= $totalDataRows) {
|
|
$dataRows = [];
|
|
} elseif ($startIndex > 0) {
|
|
$dataRows = array_slice($dataRows, $startIndex, null, true);
|
|
}
|
|
|
|
$previewRows = $limit > 0
|
|
? array_slice($dataRows, -$limit, null, true)
|
|
: $dataRows;
|
|
$previewRows = array_reverse($previewRows, true);
|
|
|
|
$orders = [];
|
|
$minCodigoNumber = isset($storeConfig['min_codigo_number']) ? (int) ($storeConfig['min_codigo_number'] ?? 0) : 0;
|
|
|
|
foreach ($previewRows as $rowIndex => $row) {
|
|
$codigo = trim((string) ($row[0] ?? ''));
|
|
$importId = drive_test_get_cell($row, $headerIndexes, ['ID']);
|
|
$nombre = drive_test_get_cell($row, $headerIndexes, ['NOMBRE']);
|
|
$celular = preg_replace('/\D+/', '', drive_test_get_cell($row, $headerIndexes, ['CELULAR']));
|
|
$producto = drive_test_get_cell($row, $headerIndexes, ['PRODUCTO']);
|
|
$cantidad = drive_test_get_cell($row, $headerIndexes, ['CANTIDAD']);
|
|
$precio = drive_test_get_cell($row, $headerIndexes, ['PRECIO']);
|
|
$pais = drive_test_get_cell($row, $headerIndexes, ['PAIS']);
|
|
$coordenadas = drive_test_get_cell($row, $headerIndexes, ['COORDENADAS']);
|
|
$ciudad = drive_test_compact_text(drive_test_get_cell($row, $headerIndexes, ['CIUDAD']));
|
|
$metodo = drive_test_compact_text(drive_test_get_cell($row, $headerIndexes, ['METODO']));
|
|
$sede = drive_test_compact_text(drive_test_get_cell($row, $headerIndexes, ['SEDE / ID', 'SEDE/ID']));
|
|
$dni = drive_test_compact_text(drive_test_get_cell($row, $headerIndexes, ['N° DNI', 'N° DNI ', 'NRO DNI', 'DNI']));
|
|
$observaciones = drive_test_compact_text(drive_test_get_cell($row, $headerIndexes, ['OBSERVACIONES', 'OBSERVACIONES ']));
|
|
$direccion = drive_test_compact_text(drive_test_get_cell($row, $headerIndexes, ['DIRECION', 'DIRECCION']));
|
|
$referencia = drive_test_compact_text(drive_test_get_cell($row, $headerIndexes, ['REFERENCIA']));
|
|
$distrito = drive_test_compact_text(drive_test_get_cell($row, $headerIndexes, ['DISTRITO']));
|
|
$sourceRow = (int) $rowIndex + 2;
|
|
$codigoNumber = drive_test_extract_codigo_number($codigo);
|
|
|
|
if ($minCodigoNumber > 0 && ($codigoNumber === null || $codigoNumber < $minCodigoNumber)) {
|
|
continue;
|
|
}
|
|
|
|
// Importante: para 'flower' mantenemos EXACTAMENTE el algoritmo anterior
|
|
// para no romper el historial/tracking ya existente.
|
|
$sourceKeyBase = implode('|', [$codigo, $importId, $nombre, $celular, $producto]);
|
|
if (($storeConfig['key'] ?? 'flower') === 'flower') {
|
|
$sourceKey = sha1($sourceKeyBase);
|
|
} else {
|
|
$sourceKey = sha1(($storeConfig['key'] ?? $storeKey) . '|' . $sourceKeyBase);
|
|
}
|
|
|
|
$orders[] = [
|
|
'source_key' => $sourceKey,
|
|
'source_row' => $sourceRow,
|
|
'codigo' => $codigo,
|
|
'import_id' => $importId,
|
|
'drive_imported_at' => drive_test_parse_datetime_mysql($importId),
|
|
'nombre' => $nombre,
|
|
'direccion' => $direccion,
|
|
'referencia' => $referencia,
|
|
'agencia' => '',
|
|
'sede_agencia' => '',
|
|
'distrito' => $distrito,
|
|
'celular' => $celular,
|
|
'producto' => $producto,
|
|
'cantidad' => $cantidad,
|
|
'precio' => $precio,
|
|
'pais' => $pais,
|
|
'coordenadas' => $coordenadas,
|
|
'ciudad' => $ciudad,
|
|
'metodo' => $metodo,
|
|
'sede' => $sede,
|
|
'dni' => $dni,
|
|
'observaciones' => $observaciones,
|
|
];
|
|
}
|
|
|
|
return [
|
|
'headers' => $headers,
|
|
'orders' => $orders,
|
|
'total_rows' => count($dataRows),
|
|
];
|
|
}
|
|
|
|
function drive_test_fetch_tracking(PDO $pdo, array $sourceKeys): array
|
|
{
|
|
if (empty($sourceKeys)) {
|
|
return [];
|
|
}
|
|
|
|
cc_test_ensure_tracking_table($pdo);
|
|
|
|
$placeholders = implode(',', array_fill(0, count($sourceKeys), '?'));
|
|
$stmt = $pdo->prepare("SELECT source_key, estado, nota_seguimiento, user_id, assigned_at, direccion, referencia, agencia, sede_agencia, sede, ciudad, distrito, dni, numero_cuenta_sede_id, numero_cuenta_dni, observaciones, coordenadas, producto, cantidad, precio, monto_adelantado, confirmacion_producto, confirmacion_cantidad, confirmacion_precio, confirmacion_producto_extra, confirmacion_cantidad_extra, confirmacion_precio_extra, proxima_llamada_at, fecha_entrega_programada, numero_cuenta_enviado_at, promo_final_evidencia_path, promo_final_evidencia_subido_at, promo_final_evidencia_subido_por, cancelado_evidencia_path, cancelado_evidencia_subido_at, cancelado_evidencia_subido_por, eliminado_at, eliminado_por, ruta_contraentrega_pedido_id, ruta_contraentrega_subido_at, ruta_contraentrega_subido_por, pedido_rotulado_pedido_id, pedido_rotulado_subido_at, pedido_rotulado_subido_por, ultima_gestion_at, updated_at FROM callcenter_test_tracking WHERE source_key IN ($placeholders)");
|
|
$stmt->execute($sourceKeys);
|
|
|
|
$tracking = [];
|
|
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
|
$tracking[$row['source_key']] = $row;
|
|
}
|
|
|
|
return $tracking;
|
|
}
|
|
|
|
|
|
function drive_test_get_existing_source_keys_by_row(PDO $pdo, string $storeKey): array
|
|
{
|
|
$storeKey = mb_strtolower(trim($storeKey));
|
|
if ($storeKey === '') {
|
|
return [];
|
|
}
|
|
|
|
drive_test_ensure_orders_table($pdo);
|
|
|
|
$stmt = $pdo->prepare('SELECT source_row, source_key FROM callcenter_test_orders WHERE store_key = ? AND source_row IS NOT NULL ORDER BY COALESCE(drive_imported_at, first_seen_at) DESC, id DESC');
|
|
$stmt->execute([$storeKey]);
|
|
|
|
$sourceKeysByRow = [];
|
|
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
|
$sourceRow = isset($row['source_row']) ? (int) $row['source_row'] : 0;
|
|
$sourceKey = trim((string) ($row['source_key'] ?? ''));
|
|
if ($sourceRow > 0 && $sourceKey !== '' && !isset($sourceKeysByRow[$sourceRow])) {
|
|
$sourceKeysByRow[$sourceRow] = $sourceKey;
|
|
}
|
|
}
|
|
|
|
return $sourceKeysByRow;
|
|
}
|
|
|
|
function drive_test_merge_tracking(array $orders, array $tracking): array
|
|
{
|
|
$editableFields = ['direccion', 'referencia', 'agencia', 'sede_agencia', 'sede', 'ciudad', 'distrito', 'dni', 'numero_cuenta_sede_id', 'numero_cuenta_dni', 'observaciones', 'coordenadas', 'producto', 'cantidad', 'precio', 'monto_adelantado', 'confirmacion_producto', 'confirmacion_cantidad', 'confirmacion_precio', 'confirmacion_producto_extra', 'confirmacion_cantidad_extra', 'confirmacion_precio_extra'];
|
|
|
|
foreach ($orders as &$order) {
|
|
$current = $tracking[$order['source_key']] ?? null;
|
|
$order['estado'] = $current['estado'] ?? 'POR LLAMAR';
|
|
$order['nota_seguimiento'] = $current['nota_seguimiento'] ?? '';
|
|
$userId = array_key_exists('user_id', (array) $current) ? $current['user_id'] : null;
|
|
$order['user_id'] = $userId !== null ? ((int) $userId > 0 ? (int) $userId : null) : null;
|
|
$order['seguimiento_actualizado'] = $current['updated_at'] ?? null;
|
|
$order['assigned_at'] = $current['assigned_at'] ?? null;
|
|
$order['proxima_llamada_at'] = $current['proxima_llamada_at'] ?? null;
|
|
$order['fecha_entrega_programada'] = $current['fecha_entrega_programada'] ?? null;
|
|
$order['numero_cuenta_enviado_at'] = $current['numero_cuenta_enviado_at'] ?? null;
|
|
$order['promo_final_evidencia_path'] = $current['promo_final_evidencia_path'] ?? null;
|
|
$order['promo_final_evidencia_subido_at'] = $current['promo_final_evidencia_subido_at'] ?? null;
|
|
$order['promo_final_evidencia_subido_por'] = isset($current['promo_final_evidencia_subido_por']) && (int) $current['promo_final_evidencia_subido_por'] > 0 ? (int) $current['promo_final_evidencia_subido_por'] : null;
|
|
$order['cancelado_evidencia_path'] = $current['cancelado_evidencia_path'] ?? null;
|
|
$order['cancelado_evidencia_subido_at'] = $current['cancelado_evidencia_subido_at'] ?? null;
|
|
$order['cancelado_evidencia_subido_por'] = isset($current['cancelado_evidencia_subido_por']) && (int) $current['cancelado_evidencia_subido_por'] > 0 ? (int) $current['cancelado_evidencia_subido_por'] : null;
|
|
$order['eliminado_at'] = $current['eliminado_at'] ?? null;
|
|
$order['eliminado_por'] = isset($current['eliminado_por']) && (int) $current['eliminado_por'] > 0 ? (int) $current['eliminado_por'] : null;
|
|
$order['eliminado'] = trim((string) ($order['eliminado_at'] ?? '')) !== '';
|
|
$order['ruta_contraentrega_pedido_id'] = isset($current['ruta_contraentrega_pedido_id']) && (int) $current['ruta_contraentrega_pedido_id'] > 0 ? (int) $current['ruta_contraentrega_pedido_id'] : null;
|
|
$order['ruta_contraentrega_subido_at'] = $current['ruta_contraentrega_subido_at'] ?? null;
|
|
$order['ruta_contraentrega_subido_por'] = isset($current['ruta_contraentrega_subido_por']) && (int) $current['ruta_contraentrega_subido_por'] > 0 ? (int) $current['ruta_contraentrega_subido_por'] : null;
|
|
$order['pedido_rotulado_pedido_id'] = isset($current['pedido_rotulado_pedido_id']) && (int) $current['pedido_rotulado_pedido_id'] > 0 ? (int) $current['pedido_rotulado_pedido_id'] : null;
|
|
$order['pedido_rotulado_subido_at'] = $current['pedido_rotulado_subido_at'] ?? null;
|
|
$order['pedido_rotulado_subido_por'] = isset($current['pedido_rotulado_subido_por']) && (int) $current['pedido_rotulado_subido_por'] > 0 ? (int) $current['pedido_rotulado_subido_por'] : null;
|
|
$order['ultima_gestion_at'] = $current['ultima_gestion_at'] ?? ($current['updated_at'] ?? null);
|
|
|
|
foreach ($editableFields as $field) {
|
|
$order[$field . '_drive'] = $order[$field] ?? '';
|
|
$trackedValue = $current[$field] ?? null;
|
|
$order[$field . '_editado'] = $trackedValue;
|
|
if ($trackedValue !== null && trim((string) $trackedValue) !== '') {
|
|
$order[$field] = $trackedValue;
|
|
}
|
|
}
|
|
|
|
$digits = preg_replace('/\D+/', '', $order['celular'] ?? '');
|
|
$order['whatsapp_url'] = $digits !== '' ? 'https://wa.me/' . $digits : '';
|
|
$order['telefono_url'] = $digits !== '' ? 'tel:' . $digits : '';
|
|
}
|
|
unset($order);
|
|
|
|
return $orders;
|
|
}
|
|
|
|
|
|
function drive_test_parse_datetime_mysql(?string $value): ?string
|
|
{
|
|
$value = trim((string) $value);
|
|
if ($value === "") {
|
|
return null;
|
|
}
|
|
|
|
$formats = [
|
|
"Y-m-d\\TH:i:sP",
|
|
"Y-m-d\\TH:i:s",
|
|
"Y-m-d\\TH:i",
|
|
"Y-m-d H:i:s",
|
|
"Y-m-d H:i",
|
|
DateTimeInterface::ATOM,
|
|
"Y-m-d",
|
|
];
|
|
|
|
foreach ($formats as $format) {
|
|
try {
|
|
$date = DateTimeImmutable::createFromFormat($format, $value);
|
|
if ($date instanceof DateTimeImmutable) {
|
|
return $date->format("Y-m-d H:i:s");
|
|
}
|
|
} catch (Throwable $exception) {
|
|
// ignore and try next format
|
|
}
|
|
}
|
|
|
|
try {
|
|
return (new DateTimeImmutable($value))->format("Y-m-d H:i:s");
|
|
} catch (Throwable $exception) {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
function drive_test_ensure_orders_table(PDO $pdo): void
|
|
{
|
|
static $checked = false;
|
|
if ($checked) {
|
|
return;
|
|
}
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS `callcenter_test_orders` (
|
|
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
`store_key` VARCHAR(50) NULL,
|
|
`source_key` CHAR(40) NOT NULL,
|
|
`codigo` TEXT NULL,
|
|
`import_id` TEXT NULL,
|
|
`source_row` INT NULL,
|
|
`is_agregado` TINYINT(1) NOT NULL DEFAULT 0,
|
|
`drive_imported_at` DATETIME NULL,
|
|
`nombre` TEXT NULL,
|
|
`direccion_drive` TEXT NULL,
|
|
`referencia_drive` TEXT NULL,
|
|
`sede_drive` TEXT NULL,
|
|
`ciudad_drive` TEXT NULL,
|
|
`distrito_drive` TEXT NULL,
|
|
`dni_drive` LONGTEXT NULL,
|
|
`observaciones_drive` TEXT NULL,
|
|
`celular` TEXT NULL,
|
|
`producto` TEXT NULL,
|
|
`cantidad` TEXT NULL,
|
|
`precio` TEXT NULL,
|
|
`pais` TEXT NULL,
|
|
`coordenadas` TEXT NULL,
|
|
`metodo` TEXT NULL,
|
|
`first_seen_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`last_seen_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY `uniq_callcenter_test_orders_source_key` (`source_key`),
|
|
KEY `idx_callcenter_test_orders_drive_imported_at` (`drive_imported_at`),
|
|
KEY `idx_callcenter_test_orders_last_seen_at` (`last_seen_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
|
|
|
|
// In case the existing table was created before we introduced store_key.
|
|
cc_test_ensure_column($pdo, "callcenter_test_orders", "store_key", "VARCHAR(50) NULL");
|
|
cc_test_ensure_column($pdo, "callcenter_test_orders", "source_row", "INT NULL AFTER `import_id`");
|
|
|
|
// Asegura columna para pedidos cargados manualmente (Agregados)
|
|
cc_test_ensure_column($pdo, "callcenter_test_orders", "is_agregado", "TINYINT(1) NOT NULL DEFAULT 0");
|
|
|
|
$textColumns = [
|
|
'codigo' => 'AFTER `source_key`',
|
|
'import_id' => 'AFTER `codigo`',
|
|
'nombre' => 'AFTER `import_id`',
|
|
'celular' => 'AFTER `observaciones_drive`',
|
|
'cantidad' => 'AFTER `producto`',
|
|
'precio' => 'AFTER `cantidad`',
|
|
'pais' => 'AFTER `precio`',
|
|
'coordenadas' => 'AFTER `pais`',
|
|
'metodo' => 'AFTER `coordenadas`',
|
|
];
|
|
|
|
foreach ($textColumns as $columnName => $afterClause) {
|
|
cc_test_ensure_column($pdo, 'callcenter_test_orders', $columnName, "TEXT NULL {$afterClause}");
|
|
}
|
|
|
|
// Asegurar que los campos de ubicación y DNI soporten valores largos desde Google Sheets.
|
|
try {
|
|
$colStmt = $pdo->prepare("
|
|
SELECT COLUMN_NAME, DATA_TYPE
|
|
FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'callcenter_test_orders'
|
|
AND COLUMN_NAME IN ('sede_drive', 'ciudad_drive', 'distrito_drive', 'dni_drive')
|
|
");
|
|
$colStmt->execute();
|
|
|
|
$dataTypes = [];
|
|
foreach ($colStmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
|
$columnName = strtolower((string) ($row['COLUMN_NAME'] ?? ''));
|
|
$dataTypes[$columnName] = strtolower((string) ($row['DATA_TYPE'] ?? ''));
|
|
}
|
|
|
|
foreach (['sede_drive', 'ciudad_drive', 'distrito_drive'] as $columnName) {
|
|
$dataType = $dataTypes[$columnName] ?? '';
|
|
if (!in_array($dataType, ['text', 'mediumtext', 'longtext'], true)) {
|
|
$pdo->exec("ALTER TABLE `callcenter_test_orders` MODIFY COLUMN `{$columnName}` TEXT NULL");
|
|
}
|
|
}
|
|
|
|
if (($dataTypes['dni_drive'] ?? '') !== 'longtext') {
|
|
$pdo->exec("ALTER TABLE `callcenter_test_orders` MODIFY COLUMN `dni_drive` LONGTEXT NULL");
|
|
}
|
|
} catch (Throwable $exception) {
|
|
// No bloqueamos el panel si por alguna razón no se puede ajustar el tipo.
|
|
error_log('drive_test_ensure_orders_table: ' . $exception->getMessage());
|
|
}
|
|
|
|
try {
|
|
$colStmt = $pdo->prepare("
|
|
SELECT COLUMN_NAME, DATA_TYPE
|
|
FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'callcenter_test_orders'
|
|
AND COLUMN_NAME IN ('codigo', 'import_id', 'nombre', 'celular', 'cantidad', 'precio', 'pais', 'coordenadas', 'metodo')
|
|
");
|
|
$colStmt->execute();
|
|
|
|
$dataTypes = [];
|
|
foreach ($colStmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
|
$columnName = strtolower((string) ($row['COLUMN_NAME'] ?? ''));
|
|
$dataTypes[$columnName] = strtolower((string) ($row['DATA_TYPE'] ?? ''));
|
|
}
|
|
|
|
foreach ($textColumns as $columnName => $afterClause) {
|
|
$dataType = $dataTypes[$columnName] ?? '';
|
|
if (!in_array($dataType, ['text', 'mediumtext', 'longtext'], true)) {
|
|
$pdo->exec("ALTER TABLE `callcenter_test_orders` MODIFY COLUMN `{$columnName}` TEXT NULL {$afterClause}");
|
|
}
|
|
}
|
|
} catch (Throwable $exception) {
|
|
error_log('drive_test_ensure_orders_table text columns: ' . $exception->getMessage());
|
|
}
|
|
|
|
$checked = true;
|
|
}
|
|
|
|
function drive_test_ensure_import_checkpoints_table(PDO $pdo): void
|
|
{
|
|
static $checked = false;
|
|
if ($checked) {
|
|
return;
|
|
}
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS `drive_test_import_checkpoints` (
|
|
`store_key` VARCHAR(50) NOT NULL,
|
|
`last_processed_row` INT NOT NULL DEFAULT 0,
|
|
`baseline_start_row` INT NOT NULL DEFAULT 0,
|
|
`last_sync_at` DATETIME NULL,
|
|
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`store_key`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
|
|
|
|
cc_test_ensure_column($pdo, 'drive_test_import_checkpoints', 'baseline_start_row', 'INT NOT NULL DEFAULT 0 AFTER `last_processed_row`');
|
|
|
|
$checked = true;
|
|
}
|
|
|
|
function drive_test_sync_orders_incremental(PDO $pdo, string $storeKey, int $overlapRows = 50, ?int $forcedStartRow = null): array
|
|
{
|
|
$storeKey = mb_strtolower(trim($storeKey));
|
|
$storeConfig = drive_test_get_store_config($storeKey);
|
|
$baselineStartRow = (int) ($storeConfig['startRow'] ?? 0);
|
|
$forcedStartRow = $forcedStartRow !== null && $forcedStartRow > 0 ? $forcedStartRow : null;
|
|
|
|
drive_test_ensure_orders_table($pdo);
|
|
drive_test_ensure_import_checkpoints_table($pdo);
|
|
|
|
$existingSourceKeysByRow = drive_test_get_existing_source_keys_by_row($pdo, $storeKey);
|
|
|
|
$stmtCheckpoint = $pdo->prepare('SELECT last_processed_row, baseline_start_row FROM drive_test_import_checkpoints WHERE store_key = ? LIMIT 1');
|
|
$stmtCheckpoint->execute([$storeKey]);
|
|
$checkpointData = $stmtCheckpoint->fetch(PDO::FETCH_ASSOC) ?: [];
|
|
$checkpointRow = (int) ($checkpointData['last_processed_row'] ?? 0);
|
|
$storedBaselineStartRow = (int) ($checkpointData['baseline_start_row'] ?? 0);
|
|
|
|
$overlapRows = max(0, $overlapRows);
|
|
$mustResetToBaseline = $forcedStartRow !== null || $storedBaselineStartRow !== $baselineStartRow;
|
|
$effectiveStartRow = $forcedStartRow !== null
|
|
? $forcedStartRow
|
|
: ($mustResetToBaseline
|
|
? $baselineStartRow
|
|
: ($checkpointRow > 0
|
|
? max($baselineStartRow, $checkpointRow - $overlapRows + 1)
|
|
: $baselineStartRow));
|
|
|
|
$preview = drive_test_fetch_orders(0, $effectiveStartRow, $storeKey);
|
|
$driveOrders = $preview['orders'] ?? [];
|
|
$driveTotalRows = (int) ($preview['total_rows'] ?? 0);
|
|
|
|
$sheetLastRow = ($effectiveStartRow - 1) + $driveTotalRows;
|
|
$newCheckpointRow = ($forcedStartRow !== null || $mustResetToBaseline)
|
|
? (int) $sheetLastRow
|
|
: max($checkpointRow, (int) $sheetLastRow);
|
|
|
|
if (!empty($driveOrders)) {
|
|
$stmtUpsert = $pdo->prepare(
|
|
"INSERT INTO callcenter_test_orders (
|
|
store_key,
|
|
source_key,
|
|
codigo,
|
|
import_id,
|
|
source_row,
|
|
drive_imported_at,
|
|
nombre,
|
|
direccion_drive,
|
|
referencia_drive,
|
|
sede_drive,
|
|
ciudad_drive,
|
|
distrito_drive,
|
|
dni_drive,
|
|
observaciones_drive,
|
|
celular,
|
|
producto,
|
|
cantidad,
|
|
precio,
|
|
pais,
|
|
coordenadas,
|
|
metodo
|
|
) VALUES (
|
|
:store_key,
|
|
:source_key,
|
|
:codigo,
|
|
:import_id,
|
|
:source_row,
|
|
:drive_imported_at,
|
|
:nombre,
|
|
:direccion_drive,
|
|
:referencia_drive,
|
|
:sede_drive,
|
|
:ciudad_drive,
|
|
:distrito_drive,
|
|
:dni_drive,
|
|
:observaciones_drive,
|
|
:celular,
|
|
:producto,
|
|
:cantidad,
|
|
:precio,
|
|
:pais,
|
|
:coordenadas,
|
|
:metodo
|
|
) ON DUPLICATE KEY UPDATE
|
|
store_key = VALUES(store_key),
|
|
codigo = VALUES(codigo),
|
|
import_id = VALUES(import_id),
|
|
source_row = VALUES(source_row),
|
|
is_agregado = 0,
|
|
drive_imported_at = VALUES(drive_imported_at),
|
|
nombre = VALUES(nombre),
|
|
direccion_drive = VALUES(direccion_drive),
|
|
referencia_drive = VALUES(referencia_drive),
|
|
sede_drive = VALUES(sede_drive),
|
|
ciudad_drive = VALUES(ciudad_drive),
|
|
distrito_drive = VALUES(distrito_drive),
|
|
dni_drive = VALUES(dni_drive),
|
|
observaciones_drive = VALUES(observaciones_drive),
|
|
celular = VALUES(celular),
|
|
producto = VALUES(producto),
|
|
cantidad = VALUES(cantidad),
|
|
precio = VALUES(precio),
|
|
pais = VALUES(pais),
|
|
coordenadas = VALUES(coordenadas),
|
|
metodo = VALUES(metodo),
|
|
last_seen_at = CURRENT_TIMESTAMP"
|
|
);
|
|
|
|
foreach ($driveOrders as $order) {
|
|
$driveImportedAt = drive_test_parse_datetime_mysql($order['import_id'] ?? null);
|
|
$sourceRow = isset($order['source_row']) && (int) $order['source_row'] > 0 ? (int) $order['source_row'] : null;
|
|
if ($sourceRow !== null && isset($existingSourceKeysByRow[$sourceRow])) {
|
|
$order['source_key'] = $existingSourceKeysByRow[$sourceRow];
|
|
}
|
|
|
|
$stmtUpsert->execute([
|
|
':store_key' => $storeKey,
|
|
':source_key' => $order['source_key'] ?? '',
|
|
':codigo' => ($order['codigo'] ?? '') !== '' ? ($order['codigo'] ?? null) : null,
|
|
':import_id' => ($order['import_id'] ?? '') !== '' ? ($order['import_id'] ?? null) : null,
|
|
':source_row' => $sourceRow,
|
|
':drive_imported_at' => $driveImportedAt,
|
|
':nombre' => ($order['nombre'] ?? '') !== '' ? ($order['nombre'] ?? null) : null,
|
|
':direccion_drive' => ($order['direccion'] ?? '') !== '' ? ($order['direccion'] ?? null) : null,
|
|
':referencia_drive' => ($order['referencia'] ?? '') !== '' ? ($order['referencia'] ?? null) : null,
|
|
':sede_drive' => ($order['sede'] ?? '') !== '' ? ($order['sede'] ?? null) : null,
|
|
':ciudad_drive' => ($order['ciudad'] ?? '') !== '' ? ($order['ciudad'] ?? null) : null,
|
|
':distrito_drive' => ($order['distrito'] ?? '') !== '' ? ($order['distrito'] ?? null) : null,
|
|
':dni_drive' => ($order['dni'] ?? '') !== '' ? ($order['dni'] ?? null) : null,
|
|
':observaciones_drive' => ($order['observaciones'] ?? '') !== '' ? ($order['observaciones'] ?? null) : null,
|
|
':celular' => ($order['celular'] ?? '') !== '' ? ($order['celular'] ?? null) : null,
|
|
':producto' => ($order['producto'] ?? '') !== '' ? ($order['producto'] ?? null) : null,
|
|
':cantidad' => ($order['cantidad'] ?? '') !== '' ? ($order['cantidad'] ?? null) : null,
|
|
':precio' => ($order['precio'] ?? '') !== '' ? ($order['precio'] ?? null) : null,
|
|
':pais' => ($order['pais'] ?? '') !== '' ? ($order['pais'] ?? null) : null,
|
|
':coordenadas' => ($order['coordenadas'] ?? '') !== '' ? ($order['coordenadas'] ?? null) : null,
|
|
':metodo' => ($order['metodo'] ?? '') !== '' ? ($order['metodo'] ?? null) : null,
|
|
]);
|
|
}
|
|
}
|
|
|
|
$stmtCheckpointUpsert = $pdo->prepare(
|
|
"INSERT INTO drive_test_import_checkpoints (store_key, last_processed_row, baseline_start_row, last_sync_at)
|
|
VALUES (:store_key, :last_processed_row, :baseline_start_row, CURRENT_TIMESTAMP)
|
|
ON DUPLICATE KEY UPDATE
|
|
last_processed_row = VALUES(last_processed_row),
|
|
baseline_start_row = VALUES(baseline_start_row),
|
|
last_sync_at = CURRENT_TIMESTAMP"
|
|
);
|
|
$stmtCheckpointUpsert->execute([
|
|
':store_key' => $storeKey,
|
|
':last_processed_row' => $newCheckpointRow,
|
|
':baseline_start_row' => $baselineStartRow,
|
|
]);
|
|
|
|
return [
|
|
'checkpoint_was' => $checkpointRow,
|
|
'last_processed_row' => $newCheckpointRow,
|
|
'next_start_row' => $newCheckpointRow + 1,
|
|
'effective_start_row' => $effectiveStartRow,
|
|
'drive_rows_total' => $driveTotalRows,
|
|
'orders_synced_count' => count($driveOrders),
|
|
];
|
|
}
|
|
|
|
function drive_test_fetch_orders_from_db(PDO $pdo, string $storeKey, ?bool $onlyAgregados = null): array
|
|
{
|
|
$storeKey = mb_strtolower(trim($storeKey));
|
|
$storeConfig = drive_test_get_store_config($storeKey);
|
|
drive_test_ensure_orders_table($pdo);
|
|
|
|
$where = 'WHERE store_key = ?';
|
|
$params = [$storeKey];
|
|
|
|
if ($onlyAgregados === true) {
|
|
$where .= ' AND is_agregado = 1';
|
|
} elseif ($onlyAgregados === false) {
|
|
$where .= ' AND (is_agregado = 0 OR is_agregado IS NULL)';
|
|
}
|
|
|
|
$minVisibleRow = !empty($storeConfig['enforce_db_start_row']) ? (int) ($storeConfig['startRow'] ?? 0) : 0;
|
|
if ($minVisibleRow > 0) {
|
|
$where .= ' AND (is_agregado = 1 OR (source_row IS NOT NULL AND source_row >= ?))';
|
|
$params[] = $minVisibleRow;
|
|
}
|
|
|
|
$stmt = $pdo->prepare(
|
|
"SELECT
|
|
id,
|
|
is_agregado,
|
|
source_key,
|
|
codigo,
|
|
import_id,
|
|
source_row,
|
|
drive_imported_at,
|
|
first_seen_at,
|
|
nombre,
|
|
celular,
|
|
producto,
|
|
cantidad,
|
|
precio,
|
|
pais,
|
|
coordenadas,
|
|
metodo,
|
|
direccion_drive,
|
|
referencia_drive,
|
|
sede_drive,
|
|
ciudad_drive,
|
|
distrito_drive,
|
|
dni_drive,
|
|
observaciones_drive
|
|
FROM callcenter_test_orders
|
|
$where
|
|
ORDER BY
|
|
COALESCE(drive_imported_at, first_seen_at) DESC,
|
|
source_row DESC,
|
|
id DESC"
|
|
);
|
|
$stmt->execute($params);
|
|
|
|
$orders = [];
|
|
$seenSourceRows = [];
|
|
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
|
$isAgregado = (int) ($row['is_agregado'] ?? 0);
|
|
$codigo = (string) ($row['codigo'] ?? '');
|
|
$sourceRow = isset($row['source_row']) && (int) $row['source_row'] > 0 ? (int) $row['source_row'] : null;
|
|
if ($sourceRow !== null) {
|
|
if (isset($seenSourceRows[$sourceRow])) {
|
|
continue;
|
|
}
|
|
$seenSourceRows[$sourceRow] = true;
|
|
}
|
|
|
|
$orders[] = [
|
|
'id' => (int) ($row['id'] ?? 0),
|
|
'is_agregado' => $isAgregado,
|
|
'source_key' => (string) ($row['source_key'] ?? ''),
|
|
'codigo' => $codigo,
|
|
'import_id' => (string) ($row['import_id'] ?? ''),
|
|
'source_row' => $sourceRow,
|
|
'drive_imported_at' => (string) ($row['drive_imported_at'] ?? ''),
|
|
'first_seen_at' => (string) ($row['first_seen_at'] ?? ''),
|
|
'nombre' => (string) ($row['nombre'] ?? ''),
|
|
'direccion' => (string) ($row['direccion_drive'] ?? ''),
|
|
'referencia' => (string) ($row['referencia_drive'] ?? ''),
|
|
'agencia' => '',
|
|
'sede_agencia' => '',
|
|
'distrito' => (string) ($row['distrito_drive'] ?? ''),
|
|
'celular' => (string) ($row['celular'] ?? ''),
|
|
'producto' => (string) ($row['producto'] ?? ''),
|
|
'cantidad' => (string) ($row['cantidad'] ?? ''),
|
|
'precio' => (string) ($row['precio'] ?? ''),
|
|
'monto_adelantado' => '',
|
|
'pais' => (string) ($row['pais'] ?? ''),
|
|
'coordenadas' => (string) ($row['coordenadas'] ?? ''),
|
|
'ciudad' => (string) ($row['ciudad_drive'] ?? ''),
|
|
'metodo' => (string) ($row['metodo'] ?? ''),
|
|
'sede' => (string) ($row['sede_drive'] ?? ''),
|
|
'dni' => (string) ($row['dni_drive'] ?? ''),
|
|
'observaciones' => (string) ($row['observaciones_drive'] ?? ''),
|
|
];
|
|
}
|
|
|
|
return $orders;
|
|
}
|