Exemple #1
0
 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))
Exemple #2
0
 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
Exemple #3
0
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))
Exemple #4
0
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))
Exemple #5
0
 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
Exemple #6
0
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()]
Exemple #7
0
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))
Exemple #8
0
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()]
Exemple #9
0
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))
Exemple #10
0
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))
Exemple #11
0
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))
Exemple #12
0
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()]
Exemple #13
0
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()]
Exemple #14
0
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))
Exemple #15
0
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))
Exemple #16
0
 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))
Exemple #17
0
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()]
Exemple #18
0
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()]
Exemple #19
0
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()
Exemple #20
0
def kwoteValue(v):
    """Hmm, I would prefer using '\'' instead of reverting to " quotes"""
    return str(sqlquote(v))
Exemple #21
0
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()
Exemple #22
0
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()