示例#1
0
def build_entity_list_context(context, GET, form_cls=EntitySelectorForm):
    """
    Uses parameters GET (from a request) to modify the context
    of entities lists.

    Validates GET using ``form_cls``, and uses the ``cleaned_data``
    to apply search and ordering to ``context['entities']``.

    Returns the modified context.
    """
    page = GET.get(_('page'))

    context['selector'] = form_cls(GET)
    if context['selector'].is_valid():
        GET = context['selector'].cleaned_data
    else:
        GET = {}

    key = 'type'
    if key in GET and GET[key]:
        context[key] = GET[key]
        if context['selector'].LISTS_MAP[GET[key]] == 'municipality':
            from pt_regions import municipalities
            mun = municipalities()
            context['entities'] = context['entities'].filter(
                nif__in=[m['NIF'] for m in mun])
        elif context['selector'].LISTS_MAP[GET[key]] == 'county':
            from pt_regions import counties
            mun = counties()
            context['entities'] = context['entities'].filter(
                nif__in=[m['NIF'] for m in mun])

    key = 'search'
    if key in GET and GET[key]:
        context[key] = GET[key]
        try:
            nif = int(GET[key])
            context['entities'] = context['entities'].filter(nif__contains=nif)
        except ValueError:
            context['entities'] = context['entities'].search(GET[key])

    key = 'sorting'
    if key in GET and GET[key] in context['selector'].SORTING_LOOKUPS:
        context['entities'] = context['entities'].order_by(
            context['selector'].SORTING_LOOKUPS[GET[key]])\
            .search_order_by()  # always ignore search weight

    paginator = Paginator(context['entities'], 20)
    try:
        context['entities'] = paginator.page(page)
    except PageNotAnInteger:
        # If page is not an integer, deliver first page.
        context['entities'] = paginator.page(1)
    except EmptyPage:
        # If page is out of range (e.g. 9999), deliver last page of results.
        context['entities'] = paginator.page(paginator.num_pages)

    return context
示例#2
0
def build_entity_list_context(context, GET, form_cls=EntitySelectorForm):
    """
    Uses parameters GET (from a request) to modify the context
    of entities lists.

    Validates GET using ``form_cls``, and uses the ``cleaned_data``
    to apply search and ordering to ``context['entities']``.

    Returns the modified context.
    """
    page = GET.get(_('page'))

    context['selector'] = form_cls(GET)
    if context['selector'].is_valid():
        GET = context['selector'].cleaned_data
    else:
        GET = {}

    key = 'type'
    if key in GET and GET[key]:
        context[key] = GET[key]
        if context['selector'].LISTS_MAP[GET[key]] == 'municipality':
            from pt_regions import municipalities
            mun = municipalities()
            context['entities'] = context['entities'].filter(nif__in=[m['NIF'] for m in mun])
        elif context['selector'].LISTS_MAP[GET[key]] == 'county':
            from pt_regions import counties
            mun = counties()
            context['entities'] = context['entities'].filter(nif__in=[m['NIF'] for m in mun])

    key = 'search'
    if key in GET and GET[key]:
        context[key] = GET[key]
        try:
            nif = int(GET[key])
            context['entities'] = context['entities'].filter(nif__contains=nif)
        except ValueError:
            context['entities'] = context['entities'].search(GET[key])

    key = 'sorting'
    if key in GET and GET[key] in context['selector'].SORTING_LOOKUPS:
        context['entities'] = context['entities'].order_by(
            context['selector'].SORTING_LOOKUPS[GET[key]])\
            .search_order_by()  # always ignore search weight

    paginator = Paginator(context['entities'], 20)
    try:
        context['entities'] = paginator.page(page)
    except PageNotAnInteger:
        # If page is not an integer, deliver first page.
        context['entities'] = paginator.page(1)
    except EmptyPage:
        # If page is out of range (e.g. 9999), deliver last page of results.
        context['entities'] = paginator.page(paginator.num_pages)

    return context
示例#3
0
def municipalities_procedure_types_time_series():
    from pt_regions import municipalities

    return _entities_procedure_types_time_series(
        'contracts_entity.nif IN (%s)' %
        ','.join(["'%d'" % m['NIF'] for m in municipalities()]))
示例#4
0
def exclude_municipalities_contracts_time_series():
    from pt_regions import municipalities

    return _entities_contracts_time_series(
        'contracts_entity.nif NOT IN (%s)' %
        ','.join(["'%d'" % m['NIF'] for m in municipalities()]))
示例#5
0
def municipalities_ranking():
    """
    Computes multiple time-series of annotations of all municipalities:
    - number of contracts
    - price of contracts
    - Mean delta time
    - Mean category depth
    - Mean number of contracts with wrong/invalid descriptions

    Returns a dictionary entity_id -> time series
    """
    from pt_regions import municipalities

    NIF_TO_NAME = {}
    for m in municipalities():
        NIF_TO_NAME[str(m['NIF'])] = m['name']

    query = '''
SELECT contracts_entity.base_id, contracts_entity.nif,
  EXTRACT(YEAR FROM contracts_contract.signing_date)                   AS s_year,

  COUNT(contracts_contract.id),
  SUM(contracts_contract.price)                                        AS value,
  AVG(ABS(contracts_contract.added_date -
      contracts_contract.signing_date))                                AS avg_deltat,
  AVG(COALESCE(contracts_category.depth,
               0))                                                     AS avg_depth,
  AVG(CASE WHEN
    contracts_contract.description = contracts_contract.contract_description OR
    contracts_contract.description IS NULL OR
    contracts_contract.contract_description IS NULL
    THEN 0
      ELSE 1 END)                                                      AS count_empty_text
FROM contracts_contract
  INNER JOIN contracts_contract_contractors
    ON (contracts_contract.id = contracts_contract_contractors.contract_id)
  LEFT OUTER JOIN contracts_category
    ON (contracts_contract.category_id = contracts_category.id)
  INNER JOIN contracts_entity
    ON (contracts_contract_contractors.entity_id = contracts_entity.id)
WHERE EXTRACT(YEAR FROM contracts_contract.signing_date) > 2009 AND
      contracts_entity.nif IN (%s)
GROUP BY contracts_entity.base_id, contracts_entity.nif, s_year
HAVING COUNT(contracts_contract.id) > 0
ORDER BY contracts_entity.base_id, s_year
    ''' % ','.join(list(map(lambda x: "'%s'" % x, NIF_TO_NAME.keys())))

    def raw_to_python(cursor):
        data = {}
        for row in cursor.fetchall():
            base_id, nif, year, count, value, \
                avg_deltat, avg_specificity, avg_good_text = row

            if base_id not in data:
                data[base_id] = []

            entry = {
                'name': NIF_TO_NAME[nif],
                'date': datetime.date(int(year), 1, 1),
                'count': int(count),
                'value': float(value) / 100,
                'avg_deltat': float(avg_deltat),
                'avg_specificity': float(avg_specificity),
                'avg_good_text': float(avg_good_text)
            }
            data[base_id].append(entry)

        return data

    cursor = connection.cursor()
    cursor.execute(query)

    return raw_to_python(cursor)
def municipalities_procedure_types_time_series():
    from pt_regions import municipalities

    return _entities_procedure_types_time_series(
        'contracts_entity.nif IN (%s)' %
        ','.join(["'%d'" % m['NIF'] for m in municipalities()]))
def exclude_municipalities_contracts_time_series():
    from pt_regions import municipalities

    return _entities_contracts_time_series(
        'contracts_entity.nif NOT IN (%s)' %
        ','.join(["'%d'" % m['NIF'] for m in municipalities()]))
def municipalities_ranking():
    """
    Computes multiple time-series of annotations of all municipalities:
    - number of contracts
    - price of contracts
    - Mean delta time
    - Mean category depth
    - Mean number of contracts with wrong/invalid descriptions

    Returns a dictionary entity_id -> time series
    """
    from pt_regions import municipalities

    NIF_TO_NAME = {}
    for m in municipalities():
        NIF_TO_NAME[str(m['NIF'])] = m['name']

    query = '''
SELECT contracts_entity.base_id, contracts_entity.nif,
  EXTRACT(YEAR FROM contracts_contract.signing_date)                   AS s_year,

  COUNT(contracts_contract.id),
  SUM(contracts_contract.price)                                        AS value,
  AVG(ABS(contracts_contract.added_date -
      contracts_contract.signing_date))                                AS avg_deltat,
  AVG(COALESCE(contracts_category.depth,
               0))                                                     AS avg_depth,
  AVG(CASE WHEN
    contracts_contract.description = contracts_contract.contract_description OR
    contracts_contract.description IS NULL OR
    contracts_contract.contract_description IS NULL
    THEN 0
      ELSE 1 END)                                                      AS count_empty_text
FROM contracts_contract
  INNER JOIN contracts_contract_contractors
    ON (contracts_contract.id = contracts_contract_contractors.contract_id)
  LEFT OUTER JOIN contracts_category
    ON (contracts_contract.category_id = contracts_category.id)
  INNER JOIN contracts_entity
    ON (contracts_contract_contractors.entity_id = contracts_entity.id)
WHERE EXTRACT(YEAR FROM contracts_contract.signing_date) > 2009 AND
      contracts_entity.nif IN (%s)
GROUP BY contracts_entity.base_id, contracts_entity.nif, s_year
HAVING COUNT(contracts_contract.id) > 0
ORDER BY contracts_entity.base_id, s_year
    ''' % ','.join(list(map(lambda x: "'%s'" % x, NIF_TO_NAME.keys())))

    def raw_to_python(cursor):
        data = {}
        for row in cursor.fetchall():
            base_id, nif, year, count, value, \
                avg_deltat, avg_specificity, avg_good_text = row

            if base_id not in data:
                data[base_id] = []

            entry = {
                'name': NIF_TO_NAME[nif],
                'date': datetime.date(int(year), 1, 1),
                'count': int(count),
                'value': float(value)/100,
                'avg_deltat': float(avg_deltat),
                'avg_specificity': float(avg_specificity),
                'avg_good_text': float(avg_good_text)
            }
            data[base_id].append(entry)

        return data

    cursor = connection.cursor()
    cursor.execute(query)

    return raw_to_python(cursor)