215 lines
8.7 KiB
Ruby
215 lines
8.7 KiB
Ruby
#!/usr/bin/env ruby
|
|
# frozen_string_literal: true
|
|
|
|
# ==========================================================
|
|
# Verificación específica de BONOS - FENIX vs DASU-SQL4
|
|
# ==========================================================
|
|
|
|
require 'tiny_tds'
|
|
require 'json'
|
|
require_relative 'drones/lib/dasu_executor'
|
|
|
|
puts "=== VERIFICACIÓN DE BONOS (venta de bonos) ==="
|
|
puts "Fecha: #{Time.now.strftime('%Y-%m-%d %H:%M:%S')}"
|
|
puts ""
|
|
|
|
# ==========================================
|
|
# 1. FENIX - BonosAfiliado y AutorizacionBonosExcepcion
|
|
# ==========================================
|
|
puts "\n[1] FENIX (PRODUCCIÓN) - BonosAfiliado..."
|
|
|
|
pass_cmd = `ruby adn/tools/candados/candados.rb get utnlarioja.intranet:monlaricardo 2>/dev/null`.strip
|
|
sql_user = 'UTNlarioja\\monlaricardo'
|
|
sql_host = '10.0.10.200'
|
|
|
|
client_fenix = TinyTds::Client.new(
|
|
username: sql_user,
|
|
password: pass_cmd,
|
|
host: sql_host,
|
|
timeout: 600,
|
|
database: 'sysdasuten'
|
|
)
|
|
|
|
# BonosAfiliado - verificar columna de fecha
|
|
puts " Buscando columna de fecha en BonosAfiliado..."
|
|
cols = client_fenix.execute("
|
|
SELECT COLUMN_NAME, DATA_TYPE
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_NAME = 'BonosAfiliado'
|
|
AND DATA_TYPE IN ('datetime', 'datetime2', 'date', 'smalldatetime')
|
|
").to_a
|
|
|
|
puts " Columnas fecha encontradas: #{cols.map { |c| "#{c['COLUMN_NAME']} (#{c['DATA_TYPE']})" }.join(', ')}"
|
|
|
|
# Usar la primera columna fecha encontrada
|
|
if cols.any?
|
|
col_fecha = cols.first['COLUMN_NAME']
|
|
puts " Usando columna: #{col_fecha}"
|
|
|
|
# Última fecha exacta
|
|
ultima = client_fenix.execute("SELECT MAX(#{col_fecha}) as ultima FROM BonosAfiliado").first['ultima']
|
|
puts " Última fecha: #{ultima}"
|
|
|
|
# Registros desde abril 13
|
|
count_13 = client_fenix.execute("SELECT COUNT(*) as c FROM BonosAfiliado WHERE #{col_fecha} >= '2026-04-13'").first['c']
|
|
puts " Registros desde 2026-04-13: #{count_13}"
|
|
|
|
# Registros por día desde abril 13
|
|
puts "\n Registros por día (desde 2026-04-13):"
|
|
por_dia = client_fenix.execute("
|
|
SELECT CAST(#{col_fecha} AS DATE) as dia, COUNT(*) as cantidad
|
|
FROM BonosAfiliado
|
|
WHERE #{col_fecha} >= '2026-04-13'
|
|
GROUP BY CAST(#{col_fecha} AS DATE)
|
|
ORDER BY dia DESC
|
|
")
|
|
por_dia.each { |r| puts " #{r['dia']}: #{r['cantidad']} registros" }
|
|
|
|
# Últimos 10 registros
|
|
puts "\n Últimos 10 registros de BonosAfiliado:"
|
|
ultimos = client_fenix.execute("SELECT TOP 10 * FROM BonosAfiliado ORDER BY #{col_fecha} DESC").to_a
|
|
ultimos.each_with_index do |r, i|
|
|
puts " #{i+1}. #{col_fecha}=#{r[col_fecha]} - #{r.select { |k,v| k != col_fecha }.to_a.first(3).map { |k,v| "#{k}: #{v}" }.join(', ')}"
|
|
end
|
|
end
|
|
|
|
# AutorizacionBonosExcepcion
|
|
puts "\n[2] FENIX (PRODUCCIÓN) - AutorizacionBonosExcepcion..."
|
|
|
|
cols = client_fenix.execute("
|
|
SELECT COLUMN_NAME, DATA_TYPE
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_NAME = 'AutorizacionBonosExcepcion'
|
|
AND DATA_TYPE IN ('datetime', 'datetime2', 'date', 'smalldatetime')
|
|
").to_a
|
|
|
|
if cols.any?
|
|
col_fecha = cols.first['COLUMN_NAME']
|
|
puts " Usando columna: #{col_fecha}"
|
|
|
|
ultima = client_fenix.execute("SELECT MAX(#{col_fecha}) as ultima FROM AutorizacionBonosExcepcion").first['ultima']
|
|
puts " Última fecha: #{ultima}"
|
|
|
|
count_13 = client_fenix.execute("SELECT COUNT(*) as c FROM AutorizacionBonosExcepcion WHERE #{col_fecha} >= '2026-04-13'").first['c']
|
|
puts " Registros desde 2026-04-13: #{count_13}"
|
|
|
|
puts "\n Registros por día (desde 2026-04-13):"
|
|
por_dia = client_fenix.execute("
|
|
SELECT CAST(#{col_fecha} AS DATE) as dia, COUNT(*) as cantidad
|
|
FROM AutorizacionBonosExcepcion
|
|
WHERE #{col_fecha} >= '2026-04-13'
|
|
GROUP BY CAST(#{col_fecha} AS DATE)
|
|
ORDER BY dia DESC
|
|
")
|
|
por_dia.each { |r| puts " #{r['dia']}: #{r['cantidad']} registros" }
|
|
|
|
puts "\n Últimos 10 registros:"
|
|
ultimos = client_fenix.execute("SELECT TOP 10 * FROM AutorizacionBonosExcepcion ORDER BY #{col_fecha} DESC").to_a
|
|
ultimos.each_with_index do |r, i|
|
|
puts " #{i+1}. #{col_fecha}=#{r[col_fecha]} - #{r.to_a.first(5).map { |k,v| "#{k}: #{v}" }.join(', ')}"
|
|
end
|
|
end
|
|
|
|
client_fenix.close
|
|
|
|
# ==========================================
|
|
# 2. DASU-SQL4 - Mismas consultas
|
|
# ==========================================
|
|
puts "\n[3] DASU-SQL4 - BonosAfiliado..."
|
|
|
|
ps_script = <<~PSCMD.strip
|
|
$ErrorActionPreference = "Stop"
|
|
$JSON_PATH = "C:\\temp\\bonos_dasu_output.json"
|
|
$null = New-Item -ItemType Directory -Force -Path "C:\\temp"
|
|
|
|
$connString = "Server=localhost;Database=sysdasuten;Integrated Security=true;TrustServerCertificate=yes;"
|
|
$conn = New-Object System.Data.SqlClient.SqlConnection($connString)
|
|
$conn.Open()
|
|
|
|
# BonosAfiliado
|
|
Write-Host " Buscando columna de fecha en BonosAfiliado..."
|
|
$cmdCol = New-Object System.Data.SqlClient.SqlCommand("SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'BonosAfiliado' AND DATA_TYPE IN ('datetime', 'datetime2', 'date', 'smalldatetime')", $conn)
|
|
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmdCol)
|
|
$ds = New-Object System.Data.DataSet
|
|
$adapter.Fill($ds)
|
|
$cols = $ds.Tables[0].Rows
|
|
|
|
if ($cols.Count -gt 0) {
|
|
$colFecha = $cols[0].COLUMN_NAME
|
|
Write-Host " Usando columna: $colFecha"
|
|
|
|
# Última fecha
|
|
$cmd = New-Object System.Data.SqlClient.SqlCommand("SELECT MAX([$colFecha]) as ultima FROM [BonosAfiliado]", $conn)
|
|
$ultima = $cmd.ExecuteScalar()
|
|
Write-Host " Última fecha: $ultima"
|
|
|
|
# Registros desde abril 13
|
|
$cmd = New-Object System.Data.SqlClient.SqlCommand("SELECT COUNT(*) FROM [BonosAfiliado] WHERE [$colFecha] >= '2026-04-13'", $conn)
|
|
$count13 = $cmd.ExecuteScalar()
|
|
Write-Host " Registros desde 2026-04-13: $count13"
|
|
|
|
# Por día
|
|
Write-Host " Registros por día:"
|
|
$cmd = New-Object System.Data.SqlClient.SqlCommand("SELECT CAST([$colFecha] AS DATE) as dia, COUNT(*) as cantidad FROM [BonosAfiliado] WHERE [$colFecha] >= '2026-04-13' GROUP BY CAST([$colFecha] AS DATE) ORDER BY dia DESC", $conn)
|
|
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
|
|
$ds = New-Object System.Data.DataSet
|
|
$adapter.Fill($ds)
|
|
$ds.Tables[0].Rows | ForEach-Object { Write-Host " $($_.dia): $($_.cantidad) registros" }
|
|
|
|
# Últimos 10
|
|
Write-Host " Últimos 10 registros:"
|
|
$cmd = New-Object System.Data.SqlClient.SqlCommand("SELECT TOP 10 * FROM [BonosAfiliado] ORDER BY [$colFecha] DESC", $conn)
|
|
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
|
|
$ds = New-Object System.Data.DataSet
|
|
$adapter.Fill($ds)
|
|
for ($i = 0; $i -lt $ds.Tables[0].Rows.Count; $i++) {
|
|
$row = $ds.Tables[0].Rows[$i]
|
|
Write-Host " $($i+1). $($colFecha)=$($row.$colFecha)"
|
|
}
|
|
}
|
|
|
|
# AutorizacionBonosExcepcion
|
|
Write-Host "`n[4] DASU-SQL4 - AutorizacionBonosExcepcion..."
|
|
$cmdCol = New-Object System.Data.SqlClient.SqlCommand("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AutorizacionBonosExcepcion' AND DATA_TYPE IN ('datetime', 'datetime2', 'date', 'smalldatetime')", $conn)
|
|
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmdCol)
|
|
$ds = New-Object System.Data.DataSet
|
|
$adapter.Fill($ds)
|
|
$cols = $ds.Tables[0].Rows
|
|
|
|
if ($cols.Count -gt 0) {
|
|
$colFecha = $cols[0].COLUMN_NAME
|
|
Write-Host " Usando columna: $colFecha"
|
|
|
|
$cmd = New-Object System.Data.SqlClient.SqlCommand("SELECT MAX([$colFecha]) as ultima FROM [AutorizacionBonosExcepcion]", $conn)
|
|
$ultima = $cmd.ExecuteScalar()
|
|
Write-Host " Última fecha: $ultima"
|
|
|
|
$cmd = New-Object System.Data.SqlClient.SqlCommand("SELECT COUNT(*) FROM [AutorizacionBonosExcepcion] WHERE [$colFecha] >= '2026-04-13'", $conn)
|
|
$count13 = $cmd.ExecuteScalar()
|
|
Write-Host " Registros desde 2026-04-13: $count13"
|
|
|
|
Write-Host " Registros por día:"
|
|
$cmd = New-Object System.Data.SqlClient.SqlCommand("SELECT CAST([$colFecha] AS DATE) as dia, COUNT(*) as cantidad FROM [AutorizacionBonosExcepcion] WHERE [$colFecha] >= '2026-04-13' GROUP BY CAST([$colFecha] AS DATE) ORDER BY dia DESC", $conn)
|
|
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
|
|
$ds = New-Object System.Data.DataSet
|
|
$adapter.Fill($ds)
|
|
$ds.Tables[0].Rows | ForEach-Object { Write-Host " $($_.dia): $($_.cantidad) registros" }
|
|
|
|
Write-Host " Últimos 10 registros:"
|
|
$cmd = New-Object System.Data.SqlClient.SqlCommand("SELECT TOP 10 * FROM [AutorizacionBonosExcepcion] ORDER BY [$colFecha] DESC", $conn)
|
|
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
|
|
$ds = New-Object System.Data.DataSet
|
|
$adapter.Fill($ds)
|
|
for ($i = 0; $i -lt $ds.Tables[0].Rows.Count; $i++) {
|
|
$row = $ds.Tables[0].Rows[$i]
|
|
Write-Host " $($i+1). $($colFecha)=$($row.$colFecha)"
|
|
}
|
|
}
|
|
|
|
$conn.Close()
|
|
PSCMD
|
|
|
|
DasuExecutor.execute_ps_on_dasu(ps_script)
|
|
|
|
puts "\n✅ Verificación completada"
|