Esempio n. 1
0
 def make_grouping(cls, logger, grouping_info):
     group_type, group_args, group_name = grouping_info
     grouping = None
     if group_type == "extract":
         subfield, field_name = group_args
         real_field = getattr(cls, field_name, None)
         if real_field:
             if subfield == 'ampm':
                 grouping = case(whens = [(cast(extract('hour', real_field), Integer()) > 12, 1),], else_ = 0).label(group_name)
             else:
                 grouping = cast(extract(subfield, real_field), Integer()).label(group_name)
         else:
             logger.error("Invalid grouping %s (%s)", grouping_info, cls)
     elif group_type == "date_trunc":
         subfield, field_name = group_args
         real_field = getattr(cls, field_name, None)
         if real_field:
             grouping = func.date_trunc(subfield, real_field)
             grouping = grouping.label(group_name)
         else:
             logger.error("Invalid grouping %s (%s)", grouping_info, cls)
     elif group_type == "func":
         logger.error("Grouping by func not implemented yet")
     elif group_type == 'coalesce_trim':
         # trim(coalesce(field_name, ''))
         field_name = group_args.get('field', group_name)
         real_field = getattr(cls, field_name, None)
         if real_field:
             grouping = func.coalesce(real_field, group_args.get('coalesce_to', ''))
             if group_args.get('trim', True):
                 grouping = func.trim(grouping)
             grouping = grouping.label(group_name)
         else:
             logger.error("Invalid grouping %s (%s)", grouping_info, cls)
     else:
         logger.error("Unknown grouping type %s", group_type)
     return grouping
Esempio n. 2
0
def list_ecriture(db, id=None, nom=None, compte_id=None, sum=None, month=None):
    """ List compte
        Filter to use :
        filter = [1-9]+ / sum : give number or sum
        sort = field1,[field2,...] : sort by field
        field = field1,[field2,...] : field to return
        page = [1-9]+ / first / last : print by page
        valide = yes / no : print valide or not valide
    """

    # return sums of account
    if sum:
        ecritures = db.query(func.count(Ecriture.nom).label("nombre"),
                             func.sum(Montant.montant/100.0).label("somme")
                            ).\
                       join(Ecriture.montant).\
                       filter(Ecriture.compte_id == compte_id).\
                       one()
        return dumps({'somme': "%0.2f" % ecritures.somme if ecritures.somme is not None else 0,
                      'nombre': "%d" % ecritures.nombre,
                     }
                    )
    if month:
        ecriture = aliased(Ecriture, name="ecriture_full")
        debit = db.query(func.sum(Montant.montant/100.0)).\
                   select_from(Ecriture).\
                   join(Ecriture.montant).\
                   filter(Ecriture.compte_id == compte_id).\
                   filter(extract('year', Ecriture.date) == 2015).\
                   filter(extract('month', Ecriture.date) == extract('month', ecriture.date)).\
                   filter(Montant.montant < 0).\
                   label("debit")
        credit = db.query(func.sum(Montant.montant/100.0)).\
                   select_from(Ecriture).\
                   join(Ecriture.montant).\
                   filter(Ecriture.compte_id == compte_id).\
                   filter(extract('year', Ecriture.date) == 2015).\
                   filter(extract('month', Ecriture.date) == extract('month', ecriture.date)).\
                   filter(Montant.montant > 0).\
                   label("credit")
        ecritures = db.query(extract('month', ecriture.date).label("date"),
                             debit,
                             credit
                            ).\
                       join(ecriture.montant).\
                       filter(ecriture.compte_id == compte_id).\
                       filter(extract('year', ecriture.date) == 2015).\
                       group_by(extract('month', ecriture.date).label("date")).\
                       all()
        list_month_debit = [0 for number in range(12)]
        list_month_credit = [0 for number in range(12)]
        cpt = 0
        for ecriture in ecritures:
            cpt += 1
            print ecriture, cpt
            list_month_debit[ecriture.date-1] = float(ecriture.debit) if ecriture.debit != None else 0
            list_month_credit[ecriture.date-1] = float(ecriture.credit) if ecriture.credit != None else 0
        return dumps([list_month_debit,list_month_credit])
    
    filter = {}
    if id:
        filter['id'] = id
    elif nom:
        filter['nom'] = nom
    elif compte_id:
        filter['compte_id'] = compte_id
    else:
        filter = App.get_filter(request.query.filter)

    sort = App.get_sort(request.query.sort)

    ecritures = db.query(Ecriture.id.label("id"),
                         func.trim(Ecriture.nom).label("nom"),
                         Ecriture.date.label("date"),
                         Montant.montant.label("montant"),
                         Ecriture.type.label("type"),
                         Categorie.nom.label("categorie"),
                         Categorie.id.label("categorie_id"),
                         Montant.description.label("description"),
                         Montant.id.label("montant_id"),
                         Ecriture.valide.label("valide"),
                         Ecriture.compte_id.label("compte_id"),
                        ).\
                   join(Ecriture.montant).\
                   join(Montant.categorie)


    if filter:
        for column, value in filter.iteritems():
            if not isinstance(value, list):
                ecritures = ecritures.filter(getattr(Ecriture, column) == value)
            else:
                ecritures = ecritures.filter(getattr(Ecriture, column).in_(value))
    if sort:
        for column in sort:
            ecritures = ecritures.order_by(getattr(Ecriture, column))
    else:
        ecritures = ecritures.order_by(desc(Ecriture.date))
    try:
        ecritures = ecritures.all()
        #if re.match(r"^\d+$", filter):
        #    ecritures = ecritures[:int(filter):]
    except NoResultFound:
        abort(404, "ID not found")
    if not ecritures:
        abort(404, "ID not found")
    list_ecritures = []
    attributs = App.get_attribut(request.query.attribut)
    if attributs:
        for ecriture in ecritures:
            dict_attributs = {}
            for attribut in attributs:
                dict_attributs[attribut] = getattr(ecriture, attribut)
            list_ecritures.append(dict_attributs)
    else:
        for ecriture in ecritures:
            list_ecritures.append({'id': ecriture.id,
                                   'nom': ecriture.nom,
                                   'date': datetime.strftime(ecriture.date, "%Y/%m/%d"),
                                   'type': ecriture.type,
                                   'valide': ecriture.valide,
                                   'categorie': ecriture.categorie,
                                   'categorie_id': ecriture.categorie_id,
                                   'montant': "%0.2f" % (ecriture.montant/100.0,),
                                   'description': ecriture.description,
                                   'montant_id': ecriture.montant_id,
                                   'compte_id': ecriture.compte_id,
                                  })
    if request.query.get('skip') and request.query.get('top'):
        return dumps({'count': len(list_ecritures),
                      'values': list_ecritures[request.query.get('skip',type=int):request.query.get('skip', type=int) + request.query.get('top', type=int)]
                     }
                    )
    else:
        return dumps(list_ecritures)
Esempio n. 3
0
c = [

    ##  date/time functions  ##

    #func.timeofday(), # for postgresql
    func.localtime(),
    func.current_timestamp(),
    #func.date_part("month", func.now()),        # for postgresql
    func.now(),

    ##  mathematical functions  ##
    func.pow(4, 2),
    func.sqrt(441),
    func.pi(),
    func.floor(func.pi()),
    func.ceil(func.pi()),

    ##  string functions  ##
    func.lower("ABC"),
    func.upper("abc"),
    func.length("abc"),
    func.trim("  ab c  "),
    #func.chr(65),        # for postgresql
]

s = select(c)
rs = conn.execute(s)
print(rs.keys())
print(rs.fetchall())
Esempio n. 4
0
    def build_filter( 
        field_name, data_type, filter_type, inverted, value ):

        if DEBUG_FILTERS:
            logger.info('build filter: %r, %r, %r, %r, %r', 
                field_name, data_type, filter_type, inverted, value)
        
        expression = None
        col = column(field_name)
        if data_type in ['integer', 'float', 'decimal']:
            col = cast(col, sqlalchemy.sql.sqltypes.Numeric)
        elif data_type == 'boolean':
            col = cast(col, sqlalchemy.sql.sqltypes.Boolean)
        if data_type == 'string':
            col = cast(col, sqlalchemy.sql.sqltypes.Text)
        if filter_type in ['exact', 'eq']:
            if data_type == 'string':
                value = str(value)
            expression = col == value
            if data_type == 'list':
                expression = text(
                    "'%s'=any(string_to_array(%s,'%s'))" % (value, field_name, LIST_DELIMITER_SQL_ARRAY))
        elif filter_type == 'about':
            decimals = 0
            if '.' in value:
                decimals = len(value.split('.')[1])
            expression = func.round(col, decimals) == value
            if DEBUG_FILTERS:
                logger.info(
                    'create "about" expression for: %r, %r, decimals %r', 
                    field_name, value, decimals)
        elif filter_type == 'contains':
            if data_type == 'string':
                value = str(value)
            expression = col.contains(value)
        elif filter_type == 'icontains':
            if data_type == 'string':
                value = str(value)
            expression = col.ilike('%{value}%'.format(value=value))
        elif filter_type == 'lt':
            expression = col < value
        elif filter_type == 'lte':
            expression = col <= value
        elif filter_type == 'gt':
            expression = col > value
        elif filter_type == 'gte':
            expression = col >= value
        elif filter_type == 'is_blank':
            if data_type == 'string':
                col = func.trim(col)
            if value and str(value).lower() == 'true':
                expression = col == None
                if data_type == 'string':
                    expression = col == ''
            else:
                expression = col != None
                if data_type == 'string':
                    col = func.trim(col)
                if (data_type == 'string' or 
                    data_type == 'list'):
                    expression = col != ''
        elif filter_type == 'is_null':
            if value and str(value).lower() == 'true':
                expression = col == None
            else:
                expression = col != None
            # TODO: test that col <> '' expression is created
        elif filter_type == 'in':
            if data_type == 'list': # NOTE: for the list type, interpret "in" as any of the
                # given values are in the field
                temp_expressions = []
                for _val in value:
                    temp_expressions.append(col.ilike('%{value}%'.format(value=_val)))
                
                expression = or_(*temp_expressions)
            else:
                expression = col.in_(value)
        elif filter_type == 'ne':
            if data_type == 'string':
                value = str(value)
            expression = col != value
        elif filter_type == 'range':
            if len(value) != 2:
                logger.error('field: %r, val: %r, '
                    'range expression must be list of length 2', 
                    field_name, value)
            else:
                expression = col.between(value[0], value[1], symmetric=True)
        else:
            logger.error(
                'field: %r, unknown filter type: %r for value: %r', 
                field_name, filter_type, value)
        if inverted:
            expression = not_(expression)
        return expression
Esempio n. 5
0
 def fullname(cls):
     return func.trim(cls.firstname + " " + cls.webi_lastname)
Esempio n. 6
0
 def fullname(cls):
     return func.trim(cls.firstname + " " + cls.webi_lastname)