244 lines
12 KiB
Python
244 lines
12 KiB
Python
"""
|
||
Основные запросы для работы с VIN кодами
|
||
"""
|
||
import logging
|
||
from typing import Tuple, List, Dict
|
||
|
||
from .base import OracleDatabase
|
||
|
||
|
||
class VinQueries(OracleDatabase):
|
||
"""Класс для работы с VIN запросами"""
|
||
|
||
async def fetch_user(self, user_id: int):
|
||
"""Получает пользователя по ID"""
|
||
def _query():
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
cur.execute("SELECT id, name FROM users WHERE id = :id", {"id": user_id})
|
||
return cur.fetchone()
|
||
|
||
return await self._execute_query(_query)
|
||
|
||
async def fetch_vin_info(self, vin: str) -> Tuple[str, str, str, int]:
|
||
"""
|
||
Получает базовую информацию о VIN (make, model, year, count)
|
||
"""
|
||
def _query():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
query = """
|
||
select 'None',
|
||
COALESCE((select value from salvagedb.m_JSONS_FROM_NHTSA v3 where v3.svin =s.svin and v3.variableid ='26'),
|
||
(select val from salvagedb.vind2 where svin = substr(s.vin, 1, 8) || '*' || substr(s.vin, 10, 2) and varb = 'Make'),'UNKNOWN') make,
|
||
COALESCE((select value from salvagedb.m_JSONS_FROM_NHTSA v3 where v3.svin =s.svin and v3.variableid ='28'),
|
||
(select val from salvagedb.vind2 where svin = substr(s.vin, 1, 8) || '*' || substr(s.vin, 10, 2) and varb = 'Model'),'UNKNOWN') model,
|
||
COALESCE((select value from salvagedb.m_JSONS_FROM_NHTSA v3 where v3.svin =s.svin and v3.variableid ='29'),
|
||
(select val from salvagedb.vind2 where svin = substr(s.vin, 1, 8) || '*' || substr(s.vin, 10, 2) and varb = 'Model Year'),'UNKNOWN') year,
|
||
(select count(*) from salvagedb.m_JSONS_FROM_NHTSA v3 where v3.svin =s.svin) cnt
|
||
from (select substr(:vin,1,10) svin, :vin vin from dual) s
|
||
"""
|
||
logging.info(f"[DB DEBUG] Executing query for VIN: {vin}")
|
||
logging.info(f"[DB DEBUG] SVIN will be: {vin[:10]}")
|
||
cur.execute(query, {"vin": vin})
|
||
result = cur.fetchone()
|
||
logging.info(f"[DB DEBUG] Raw result from database: {result}")
|
||
if result:
|
||
make, model, year, cnt = result[1], result[2], result[3], result[4]
|
||
logging.info(f"[DB DEBUG] Parsed values - make: '{make}', model: '{model}', year: '{year}', cnt: {cnt}")
|
||
return make, model, year, cnt
|
||
logging.info(f"[DB DEBUG] No result found, returning defaults")
|
||
return "UNKNOWN", "UNKNOWN", "UNKNOWN", 0
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in fetch_vin_info:")
|
||
logging.error(f"Query: {query}")
|
||
logging.error(f"VIN: {vin}")
|
||
logging.error(f"Error: {e}")
|
||
raise
|
||
|
||
return await self._execute_query(_query)
|
||
|
||
async def count_salvage_records(self, vin: str) -> int:
|
||
"""
|
||
Подсчитывает количество записей в таблице salvagedb.salvagedb для данного VIN
|
||
"""
|
||
def _query():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
query = "SELECT COUNT(*) FROM salvagedb.salvagedb WHERE vin = :vin and svin = substr(:vin,1,10)"
|
||
cur.execute(query, {"vin": vin})
|
||
result = cur.fetchone()
|
||
return result[0] if result else 0
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in count_salvage_records:")
|
||
logging.error(f"Query: {query}")
|
||
logging.error(f"VIN: {vin}")
|
||
logging.error(f"Error: {e}")
|
||
raise
|
||
|
||
return await self._execute_query(_query)
|
||
|
||
async def fetch_salvage_detailed_info(self, vin: str) -> List[Dict]:
|
||
"""
|
||
Получает детальную информацию о повреждениях и истории из таблицы salvagedb.salvagedb
|
||
"""
|
||
def _query():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
query = """
|
||
SELECT
|
||
odo,
|
||
odos,
|
||
dem1,
|
||
dem2,
|
||
month||'/'||year as sale_date,
|
||
JSON_VALUE(jdata, '$.RepCost') AS j_rep_cost,
|
||
JSON_VALUE(jdata, '$.Runs_Drive') AS j_runs_drive,
|
||
JSON_VALUE(jdata, '$.Locate') AS j_locate,
|
||
(select count(*) from salvagedb.salvage_images si where si.vin = s.vin and fn =1) img_count
|
||
FROM salvagedb.salvagedb s
|
||
LEFT JOIN salvagedb.addinfo i ON s.num = i.numid
|
||
WHERE vin = :vin AND svin = substr(:vin, 1, 10)
|
||
ORDER BY year DESC, month DESC
|
||
"""
|
||
cur.execute(query, {"vin": vin})
|
||
results = cur.fetchall()
|
||
|
||
# Преобразуем результаты в список словарей
|
||
detailed_records = []
|
||
for row in results:
|
||
record = {
|
||
'odo': row[0],
|
||
'odos': row[1],
|
||
'dem1': row[2],
|
||
'dem2': row[3],
|
||
'sale_date': row[4],
|
||
'j_rep_cost': row[5],
|
||
'j_runs_drive': row[6],
|
||
'j_locate': row[7],
|
||
'img_count': row[8]
|
||
}
|
||
detailed_records.append(record)
|
||
|
||
return detailed_records
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in fetch_salvage_detailed_info:")
|
||
logging.error(f"Query: {query}")
|
||
logging.error(f"VIN: {vin}")
|
||
logging.error(f"Error: {e}")
|
||
raise
|
||
|
||
return await self._execute_query(_query)
|
||
|
||
async def fetch_photo_paths(self, vin: str) -> List[str]:
|
||
"""
|
||
Получает список путей к фотографиям для данного VIN
|
||
"""
|
||
def _query():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
query = "SELECT ipath FROM salvagedb.salvage_images WHERE fn = 1 AND vin = :vin"
|
||
cur.execute(query, {"vin": vin})
|
||
results = cur.fetchall()
|
||
return [row[0] for row in results if row[0]] if results else []
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in fetch_photo_paths:")
|
||
logging.error(f"Query: {query}")
|
||
logging.error(f"VIN: {vin}")
|
||
logging.error(f"Error: {e}")
|
||
raise
|
||
|
||
return await self._execute_query(_query)
|
||
|
||
async def count_photo_records(self, vin: str) -> int:
|
||
"""
|
||
Подсчитывает количество фотографий для данного VIN
|
||
"""
|
||
def _query():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
query = "SELECT COUNT(*) FROM salvagedb.salvage_images WHERE fn = 1 AND vin = :vin"
|
||
cur.execute(query, {"vin": vin})
|
||
result = cur.fetchone()
|
||
return result[0] if result else 0
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in count_photo_records:")
|
||
logging.error(f"Query: {query}")
|
||
logging.error(f"VIN: {vin}")
|
||
logging.error(f"Error: {e}")
|
||
raise
|
||
|
||
return await self._execute_query(_query)
|
||
|
||
async def fetch_detailed_vin_info(self, vin: str) -> Dict:
|
||
"""
|
||
Получает детальную техническую информацию о VIN из NHTSA
|
||
"""
|
||
def _query():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
query = """
|
||
select dp.parameter_name,
|
||
dp.category_name,
|
||
d.value,
|
||
dp.endesc
|
||
from salvagedb.m_JSONS_FROM_NHTSA d
|
||
left join salvagedb.decode_params dp
|
||
on d.variableid = dp.parameter_id
|
||
where svin = substr(:vin, 1, 10)
|
||
order by dp.category_level
|
||
"""
|
||
cur.execute(query, {"vin": vin})
|
||
results = cur.fetchall()
|
||
|
||
# Organize data by categories
|
||
detailed_info = {
|
||
'basic_characteristics': {},
|
||
'engine_and_powertrain': {},
|
||
'active_safety': {},
|
||
'transmission': {},
|
||
'passive_safety': {},
|
||
'dimensions_and_construction': {},
|
||
'brake_system': {},
|
||
'lighting': {},
|
||
'additional_features': {},
|
||
'manufacturing_and_localization': {},
|
||
'ncsa_data': {},
|
||
'technical_information_and_errors': {},
|
||
'all_params': {} # Flat dictionary for easy access
|
||
}
|
||
|
||
for row in results:
|
||
param_name, category_name, value, description = row
|
||
if param_name and value:
|
||
# Create key from parameter name (lowercase, spaces to underscores)
|
||
key = param_name.lower().replace(' ', '_').replace('(', '').replace(')', '').replace('/', '_').replace('-', '_')
|
||
|
||
# Add to flat dictionary
|
||
detailed_info['all_params'][key] = value
|
||
|
||
# Add to category-specific dictionary
|
||
if category_name:
|
||
category_key = category_name.lower().replace(' ', '_').replace('(', '').replace(')', '').replace('/', '_').replace('-', '_')
|
||
if category_key in detailed_info:
|
||
detailed_info[category_key][key] = {
|
||
'value': value,
|
||
'description': description,
|
||
'param_name': param_name
|
||
}
|
||
|
||
return detailed_info
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in fetch_detailed_vin_info:")
|
||
logging.error(f"Query: {query}")
|
||
logging.error(f"VIN: {vin}")
|
||
logging.error(f"Error: {e}")
|
||
raise
|
||
|
||
return await self._execute_query(_query) |