def get_search_result(self, keyword, limit=20, offset=0):
"""
查询出搜索结果
"""
sql = '''
select * from (
select * from nowater_novel
where ( title like %s
or id in (
select distinct id from tags
where tag = %s
))
and %s
order by jointime desc
limit %s offset %s
) as t1
left join view_count as t2
using(id)
left join profile as t3
using(id)
'''
return self.db.query(sql % (
sqlquote("%" + keyword + "%"),
sqlquote(keyword),
self.BASIC_FILTER,
limit,
offset))
def count_search(self, keyword):
"""
统计搜索结果个数
"""
sql = """select count(*) from nowater_novel
where ( title like %s
or id in (
select distinct id from tags
where tag = %s
))
and %s"""
ret = self.db.query(sql % (sqlquote("%" + keyword + "%"), sqlquote(keyword), self.BASIC_FILTER))[0].count
return ret
def reportday(vehiculo_id, fecha):
"""
Genera un reporte de las posiciones de un vehiculo para un
día detreminado.
"""
from web.db import sqlquote
start_date = fecha + ' 00:00:00'
end_date = fecha + ' 23:59:60'
return config.DB.query("""SELECT v.id, v.gps_id, v.placa,
p.velocidad, p.fecha, p.ubicacion, p.position
FROM vehiculos v
INNER JOIN positions_gps AS p ON (p.gps_id=v.gps_id)
WHERE v.id=""" + sqlquote(vehiculo_id) +
""" and fecha between """ + sqlquote(start_date) + """ and """ + sqlquote(end_date))
def reportday(vehiculo_id, fecha):
"""
Genera un reporte de las posiciones de un vehiculo para un
día detreminado.
"""
from web.db import sqlquote
start_date = fecha + ' 00:00:00'
end_date = fecha + ' 23:59:60'
return config.DB.query("""SELECT v.id, v.gps_id, v.placa,
p.velocidad, p.fecha, p.ubicacion, p.position
FROM vehiculos v
INNER JOIN positions_gps AS p ON (p.gps_id=v.gps_id)
WHERE v.id=""" + sqlquote(vehiculo_id) +
""" and fecha between """ + sqlquote(start_date) +
""" and """ + sqlquote(end_date))
def count_search(self, keyword):
"""
统计搜索结果个数
"""
sql = '''select count(*) from nowater_novel
where ( title like %s
or id in (
select distinct id from tags
where tag = %s
))
and %s'''
ret = self.db.query(sql % (
sqlquote("%" + keyword + "%"),
sqlquote(keyword),
self.BASIC_FILTER))[0].count
return ret
def listingPhones(id):
"""
"""
from web.db import sqlquote
result = config.DB.query("""SELECT p.phone, tp.name
FROM phones_all pa, phones p, type_phone tp
WHERE pa.phone_id=p.id AND tp.id=p.type AND pa.client_id=""" + sqlquote(id))
return [tuple(i.values()) for i in result.list()]
def countEventClient(client_id):
"""
Retorna el numero de eventos sin gestionar.
"""
from web.db import sqlquote
return config.DB.query("""SELECT count(*)
FROM clientes_vehiculos cv, vehiculos v, gps g, eventos e
WHERE cv.vehiculo_id=v.id AND v.gps_id=g.id AND g.id=e.gps_id
AND user_state <> 't' AND cv.cliente_id=""" + sqlquote(client_id))
def listingPhones(id):
"""
"""
from web.db import sqlquote
result = config.DB.query("""SELECT p.phone, tp.name
FROM phones_all pa, phones p, type_phone tp
WHERE pa.phone_id=p.id AND tp.id=p.type AND pa.client_id=""" +
sqlquote(id))
return [tuple(i.values()) for i in result.list()]
def countEventClient(client_id):
"""
Retorna el numero de eventos sin gestionar.
"""
from web.db import sqlquote
return config.DB.query("""SELECT count(*)
FROM clientes_vehiculos cv, vehiculos v, gps g, eventos e
WHERE cv.vehiculo_id=v.id AND v.gps_id=g.id AND g.id=e.gps_id
AND user_state <> 't' AND cv.cliente_id=""" + sqlquote(client_id))
def eventsClient(client_id):
"""
Retorna el numero de eventos sin gestionar.
"""
from web.db import sqlquote
return config.DB.query("""SELECT v.id AS vehi_id, v.placa, g.id AS gps_id, g.name, p.position, p.ubicacion, p.fecha,
e.id AS event_id, p.id AS position_id, te.descrip
FROM clientes_vehiculos cv, vehiculos v, gps g, eventos e, positions_gps p, type_event te
WHERE cv.vehiculo_id=v.id AND v.gps_id=g.id AND g.id=e.gps_id
AND p.id=e.positions_gps_id AND e.type=te.codigo
AND user_state <> 't' AND cv.cliente_id=""" + sqlquote(client_id))
def listingVehicleClient(client_id):
"""
Retorna los vehiculos del cliente.
"""
from web.db import sqlquote
return config.DB.query("""SELECT v.id, lp.gps_id, v.placa,
lp.velocidad, lp.fecha, vs.motor, lp.ubicacion, lp.position
FROM vehiculos v
INNER JOIN clientes_vehiculos AS cv ON (cv.vehiculo_id = v.id)
INNER JOIN last_positions_gps AS lp ON (lp.gps_id = v.gps_id)
LEFT OUTER JOIN vehicle_state AS vs ON (vs.vehicle_id = v.id)
WHERE cv.cliente_id=""" + sqlquote(client_id))
def listingClients(id):
"""
Lista los clientes propietarios de un vehiculo.
"""
from web.db import sqlquote
result = config.DB.query("""
SELECT (c.nombre1 || ',' || COALESCE(c.nombre2, '') || ',' || c.apellido1 || ',' || COALESCE(c.apellido2,'')) AS nombre,
c.documento
FROM clientes_vehiculos cv, clientes c
WHERE cv.cliente_id=c.id AND cv.vehiculo_id=""" + sqlquote(id))
return [tuple(i.values()) for i in result.list()]
def listingClients(id):
"""
Lista los clientes propietarios de un vehiculo.
"""
from web.db import sqlquote
result = config.DB.query("""
SELECT (c.nombre1 || ',' || COALESCE(c.nombre2, '') || ',' || c.apellido1 || ',' || COALESCE(c.apellido2,'')) AS nombre,
c.documento
FROM clientes_vehiculos cv, clientes c
WHERE cv.cliente_id=c.id AND cv.vehiculo_id=""" + sqlquote(id))
return [tuple(i.values()) for i in result.list()]
def listingVehicleClient(client_id):
"""
Retorna los vehiculos del cliente.
"""
from web.db import sqlquote
return config.DB.query("""SELECT v.id, lp.gps_id, v.placa,
lp.velocidad, lp.fecha, vs.motor, lp.ubicacion, lp.position
FROM vehiculos v
INNER JOIN clientes_vehiculos AS cv ON (cv.vehiculo_id = v.id)
INNER JOIN last_positions_gps AS lp ON (lp.gps_id = v.gps_id)
LEFT OUTER JOIN vehicle_state AS vs ON (vs.vehicle_id = v.id)
WHERE cv.cliente_id=""" + sqlquote(client_id))
def eventsClient(client_id):
"""
Retorna el numero de eventos sin gestionar.
"""
from web.db import sqlquote
return config.DB.query(
"""SELECT v.id AS vehi_id, v.placa, g.id AS gps_id, g.name, p.position, p.ubicacion, p.fecha,
e.id AS event_id, p.id AS position_id, te.descrip
FROM clientes_vehiculos cv, vehiculos v, gps g, eventos e, positions_gps p, type_event te
WHERE cv.vehiculo_id=v.id AND v.gps_id=g.id AND g.id=e.gps_id
AND p.id=e.positions_gps_id AND e.type=te.codigo
AND user_state <> 't' AND cv.cliente_id=""" + sqlquote(client_id))
def get_search_result(self, keyword, limit=20, offset=0):
"""
查询出搜索结果
"""
sql = """
select * from (
select * from nowater_novel
where ( title like %s
or id in (
select distinct id from tags
where tag = %s
))
and %s
order by jointime desc
limit %s offset %s
) as t1
left join view_count as t2
using(id)
left join profile as t3
using(id)
"""
return self.db.query(sql % (sqlquote("%" + keyword + "%"), sqlquote(keyword), self.BASIC_FILTER, limit, offset))
def listingPhones(id):
"""
usage:
>>> from db import listingPhones
>>> a = listingPhones(15)
0.01 (1): SELECT p.phone, tp.name
FROM phones_all pa, phones p, type_phone tp
WHERE pa.phone_id=p.id AND tp.id=p.type AND pa.client_id=15
>>> for i in a:
... print i
...
<Storage {'phone': u'7844983', 'name': u'fijo'}>
<Storage {'phone': u'3126783452', 'name': u'celular'}>
>>>
"""
from web.db import sqlquote
result = config.DB.query("""SELECT p.phone, tp.name
FROM phones_all pa, phones p, type_phone tp
WHERE pa.phone_id=p.id AND tp.id=p.type AND pa.client_id=""" + sqlquote(id))
return [tuple(i.values()) for i in result.list()]
def listingClients(id):
"""
Lista los clientes propietarios de un vehiculo.
usage:
>>> from db import listingClients
>>> a = listingClients(5) # id del vehiculo
>>>
>>> for i in a:
... print i
...
(u'jorge,alonso,toro,hoyos', u'11814584')
>>>
"""
from web.db import sqlquote
result = config.DB.query("""
SELECT (c.nombre1 || ',' || COALESCE(c.nombre2, '') || ',' || c.apellido1 || ',' || COALESCE(c.apellido2,'')) AS nombre,
c.documento
FROM clientes_vehiculos cv, clientes c
WHERE cv.cliente_id=c.id AND cv.vehiculo_id=""" + sqlquote(id))
return [tuple(i.values()) for i in result.list()]
def insertPhone(storage, **sequence_id):
"""
"""
from web.db import sqlquote
for name in storage:
if storage[name]:
try:
typePhone_id = (config.DB.select('type_phone',
what="id",
where="name=" +
sqlquote(name)))[0].id
seqPhone_id = config.DB.insert('phones',
phone=storage[name],
type=typePhone_id)
seqPhone_all = config.DB.insert('phones_all',
phone_id=seqPhone_id,
**sequence_id)
except:
print "Error en insertPhone:"
print sys.exc_info()
def kwoteValue(v):
"""Hmm, I would prefer using '\'' instead of reverting to " quotes"""
return str(sqlquote(v))
def insertPhone(storage, **sequence_id):
"""
>>> from db import insertPhone
>>> telefonos = {'fijo':'44444444', 'celular':u'', 'pbx':u'', 'fax':u''}
>>> insertPhone(telefonos, client_id=1)
0.0 (1): SELECT id FROM type_phone WHERE name='fijo'
typePhone_id: 2
0.0 (2): SELECT c.relname FROM pg_class c WHERE c.relkind = 'S'
0.0 (3): INSERT INTO phones (phone, type) VALUES (44444444, 2); SELECT currval('phones_id_seq')
seqPhone_id: 4
0.0 (4): INSERT INTO phones_all (phone_id, client_id) VALUES (4L, 1)
>>>
"""
from web.db import sqlquote
for name in storage:
if storage[name]:
try:
typePhone_id = (config.DB.select('type_phone', what="id", where="name=" + sqlquote(name)))[0].id
#print "typePhone_id:", typePhone_id
# Insert public.phones
seqPhone_id = config.DB.insert('phones', phone=storage[name], type=typePhone_id)
#print "seqPhone_id:", seqPhone_id
# Insert puiblic.phones_all
seqPhone_all = config.DB.insert('phones_all', phone_id=seqPhone_id, **sequence_id)
#print "seqPhone_all", seqPhone_all
except:
print "Error en insertPhone:"
print sys.exc_info()
def insertPhone(storage, **sequence_id):
"""
"""
from web.db import sqlquote
for name in storage:
if storage[name]:
try:
typePhone_id = (config.DB.select('type_phone', what="id", where="name=" + sqlquote(name)))[0].id
seqPhone_id = config.DB.insert('phones', phone=storage[name], type=typePhone_id)
seqPhone_all = config.DB.insert('phones_all', phone_id=seqPhone_id, **sequence_id)
except:
print "Error en insertPhone:"
print sys.exc_info()