ntxuva / mopa-utils Goto Github PK

View Code? Open in Web Editor NEW

1.0 1.0 0.0 1.08 MB

Mopa Utilities - Reporting and Notificiation Messaging

Python 77.99% CSS 4.81% HTML 16.03% Shell 1.17%

flask mopa mopa-utilities ntxuva python xhtml2pdf

mopa-utils's Introduction

Ntxuva

A Participatory Monitoring System for cities in Developing Countries based on Drupal, Mark-a-Spot and VoIP Drupal.

mopa-utils's People

Contributors

Watchers

mopa-utils's Issues

Server is hosted in Portugal so set CRON jobs to equivalent time of Mozambique

Send daily report per district

Use the same template and structure as the current daily report, but break it down into many reports by district.

Pisa takes days to generate PDFs with large tables.. so break them down

Not that there's been more reports it was noticed that pisa takes "days" to generate PDFs with large tables.. so break them down into smaller tables

Limit monthly reports data to a year.

Above this reports become cluttered and unreadable

Error in email address validation

Error occurs sometimes when running the daily report job

Message type:       ERROR
        Location:           /srv/www/mopa-utils/vendor/flask/app.py:1587
        Module:             app
        Function:           log_exception
        Time:               2016-12-07 17:00:04

        Message:

        Exception on /tasks/send-daily-report [GET]

Traceback (most recent call last):
  File "/srv/www/mopa-utils/vendor/flask/app.py", line 1988, in wsgi_app
    response = self.full_dispatch_request()
  File "/srv/www/mopa-utils/vendor/flask/app.py", line 1641, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/srv/www/mopa-utils/vendor/flask/app.py", line 1544, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/srv/www/mopa-utils/vendor/flask/app.py", line 1639, in full_dispatch_request
    rv = self.dispatch_request()
  File "/srv/www/mopa-utils/vendor/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/srv/www/mopa-utils/mopa/tasks.py", line 346, in send_daily_report
    attachments=[config.REPORTS_DIR + '/' + f_name]
  File "/srv/www/mopa-utils/mopa/infrastructure.py", line 274, in send_mail
    raise Exception('Invalid Address "%s" in To' % str(mail_address))
Exception: Invalid Address "(False, u'[email protected]')" in To

Error running Weekly Report

Intermittently the weekly report fails generation with the following message

'dict object' has no attribute u'Contentor a Arder' <class 'jinja2.exceptions.UndefinedError'> tasks.py 263
Traceback (most recent call last):
  File "/home/psms1/sms-app/mopa/tasks.py", line 263, in send_weekly_report
    common.generate_pdf('weekly_report.html', context, f_name)
  File "/home/psms1/sms-app/mopa/common.py", line 104, in generate_pdf
    html = template.render(context)
  File "/home/psms1/.local/lib/python2.7/site-packages/jinja2/environment.py", line 989, in render
    return self.environment.handle_exception(exc_info, True)
  File "/home/psms1/.local/lib/python2.7/site-packages/jinja2/environment.py", line 754, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/home/psms1/sms-app/mopa/templates/weekly_report.html", line 107, in top-level template code
    <img src="{{ 'mopa/static/img/' + icons[details.neighbourhoods[neighbourhood]['most_frequent_problem']] + '.png' }}"/> <br/>
UndefinedError: 'dict object' has no attribute u'Contentor a Arder'

Make inlined SQL code into stored procedures or functions

This applies to code in models and tasks

Nofity of updates on requests job fails due to lack of description field

Error running job: Notify of updates on requests.
Error message:'description'.
Exception Type: <type 'exceptions.KeyError'>.\File name: tasks.py.
Line No: 33.
Traceback: Traceback (most recent call last):
  File "/home/psms1/sms-app/mopa/tasks.py", line 33, in wrapper
    return func(*args, **kwargs)
  File "/home/psms1/sms-app/mopa/tasks.py", line 520, in notify_updates_on_requests
    request['description'].
KeyError: 'description'

Error running job: Send daily survey, due to: ('Connection aborted.', gaierror(-2, 'Name or service not known'))

Error running job: Send daily survey.
Error message:('Connection aborted.', gaierror(-2, 'Name or service not known')).
Exception Type: <class 'requests.exceptions.ConnectionError'>.
File name: tasks.py.
Line No: 33.
Traceback: Traceback (most recent call last):
  File "/home/psms1/sms-app/mopa/tasks.py", line 33, in wrapper
    return func(*args, **kwargs)
  File "/home/psms1/sms-app/mopa/tasks.py", line 458, in send_daily_survey
    db_sms = SMS.static_send(phone, constants.SMS_INTRO)
  File "/home/psms1/sms-app/mopa/models.py", line 128, in static_send
    sms.send()
  File "/home/psms1/sms-app/mopa/models.py", line 106, in send
    response = requests.get(SMS_END_POINT, params=payload)
  File "/home/psms1/.local/lib/python2.7/site-packages/requests/api.py", line 69, in get
    return request('get', url, params=params, **kwargs)
  File "/home/psms1/.local/lib/python2.7/site-packages/requests/api.py", line 50, in request
    response = session.request(method=method, url=url, **kwargs)
  File "/home/psms1/.local/lib/python2.7/site-packages/requests/sessions.py", line 465, in request
    resp = self.send(prep, **send_kwargs)
  File "/home/psms1/.local/lib/python2.7/site-packages/requests/sessions.py", line 573, in send
    r = adapter.send(request, **kwargs)
  File "/home/psms1/.local/lib/python2.7/site-packages/requests/adapters.py", line 415, in send
    raise ConnectionError(err, request=request)
ConnectionError: ('Connection aborted.', gaierror(-2, 'Name or service not known'))

Error running job: Check if answers were received. Due to: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back


---
Error running job: Check if answers were received.
Error message:(sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: u'SELECT mopa_survey.id AS mopa_survey_id, mopa_survey.created_at AS mopa_survey_created_at, mopa_survey.updated_at AS mopa_survey_updated_at, mopa_survey.survey_id AS mopa_survey_survey_id, mopa_survey.survey_type AS mopa_survey_survey_type, mopa_survey.district AS mopa_survey_district, mopa_survey.neighbourhood AS mopa_survey_neighbourhood, mopa_survey.point AS mopa_survey_point, mopa_survey.question AS mopa_survey_question \nFROM mopa_survey \nWHERE mopa_survey.id = %s \n LIMIT %s'] [parameters: [immutabledict({})]].
Exception Type: <class 'sqlalchemy.exc.StatementError'>.
File name: tasks.py.
Line No: 33.
Traceback: Traceback (most recent call last):
  File "/home/psms1/sms-app/mopa/tasks.py", line 33, in wrapper
    return func(*args, **kwargs)
  File "/home/psms1/sms-app/mopa/tasks.py", line 470, in check_if_answers_were_received
    survey = Survey.todays()
  File "/home/psms1/sms-app/mopa/models.py", line 188, in todays
    survey = Survey.query.filter_by(id=str(survey_id)).first()
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2445, in first
    ret = list(self[0:1])
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2281, in __getitem__
    return list(res)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2516, in __iter__
    return self._execute_and_instances(context)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2531, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1078, in _execute_context
    None, None)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1071, in _execute_context
    conn = self._revalidate_connection()
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 391, in _revalidate_connection
    "Can't reconnect until invalid "
StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: u'SELECT mopa_survey.id AS mopa_survey_id, mopa_survey.created_at AS mopa_survey_created_at, mopa_survey.updated_at AS mopa_survey_updated_at, mopa_survey.survey_id AS mopa_survey_survey_id, mopa_survey.survey_type AS mopa_survey_survey_type, mopa_survey.district AS mopa_survey_district, mopa_survey.neighbourhood AS mopa_survey_neighbourhood, mopa_survey.point AS mopa_survey_point, mopa_survey.question AS mopa_survey_question \nFROM mopa_survey \nWHERE mopa_survey.id = %s \n LIMIT %s'] [parameters: [immutabledict({})]]

include libraries to requirements.txt in monthly-report branch

@jeanbarroca could you please include the listing of the libraries used in the requirements.txt in the monthly-repot branch. I need this so that when we merge I know what to install in the live server via pip.

Also I'd suggest you open a make a PR, that way I can easily add comments and reviews to the code.

Thanks

ditch xhtml2pdf in favor of weasy print

Broken notifications on new requests and status updates

According to the logs in karma.mopa.co.mz no SMS notification queing request has been received since 30/Nov/2016

127.0.0.1 - - [30/Nov/2016:21:46:02 +0200] "POST /api/sms-send HTTP/1.0" 200 617 "-" "python-requests/2.7.0 CPython/2.7.3 Linux/3.2.0-4-amd64" - time:0.24867
127.0.0.1 - - [30/Nov/2016:23:36:01 +0200] "POST /api/sms-send HTTP/1.0" 200 617 "-" "python-requests/2.7.0 CPython/2.7.3 Linux/3.2.0-4-amd64" - time:0.20097
127.0.0.1 - - [30/Nov/2016:23:41:01 +0200] "POST /api/sms-send HTTP/1.0" 200 617 "-" "python-requests/2.7.0 CPython/2.7.3 Linux/3.2.0-4-amd64" - time:0.17097

But on mopa.co.mz SMS are being registered on the database, which only happens after queuing has been done:

+-------+---------------------+---------------------+-----------+---------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id    | created_at          | updated_at          | direction | sent_by | sent_to   | text                                                                                                                                                             |
+-------+---------------------+---------------------+-----------+---------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 30258 | 2016-12-15 08:00:03 | 2016-12-15 08:00:03 | O         | mopa    | 844794747 | Novo problema reportado no mopa: No: 16506907 - Contentor est? cheio em Chamanculo B.  Distrito: Nlhamankulu, Bairro: Chamanculo B, Cape- Cape.                  |
| 30259 | 2016-12-15 08:05:01 | 2016-12-15 08:05:01 | O         | mopa    | 844794747 | Novo problema reportado no mopa: No: 16506907 - Contentor est? cheio em Chamanculo B.  Distrito: Nlhamankulu, Bairro: Chamanculo B, Cape- Cape.                  |
| 30260 | 2016-12-15 08:45:02 | 2016-12-15 08:45:02 | O         | mopa    | 822730820 | Novo problema reportado no mopa: No: 16506909 - Tchova n?o passou em Mahotas.  Distrito: KaMavota, Bairro: Mahotas, Quarteirao: 4.                               |
| 30261 | 2016-12-15 08:45:02 | 2016-12-15 08:45:02 | O         | mopa    | 844415339 | Novo problema reportado no mopa: No: 16506909 - Tchova n?o passou em Mahotas.  Distrito: KaMavota, Bairro: Mahotas, Quarteirao: 4.                               |
| 30262 | 2016-12-15 08:45:02 | 2016-12-15 08:45:02 | O         | mopa    | 825797342 | Novo problema reportado no mopa: No: 16506908 - Lixo fora do contentor em Mafalala.  Distrito: KaMaxaquene, Bairro: Mafalala, Av. Paulo Samuel Kankhomba. Ja ha  |
| 30263 | 2016-12-15 08:45:02 | 2016-12-15 08:45:02 | O         | mopa    | 826272070 | Novo problema reportado no mopa: No: 16506908 - Lixo fora do contentor em Mafalala.  Distrito: KaMaxaquene, Bairro: Mafalala, Av. Paulo Samuel Kankhomba. Ja ha  |

Investigate why requests are not reaching karma.mopa.co.mz and fix

Community monitors workflow is not working

The SMS inbox is currently not distinguishing if a user is re-opening a problem or responding to a survey question (both use the same keyword: N)

There are two possible solutions for this:

We change the response convention from the survey (something else instead of N)
We need to perform the validation ourselves, and distinguish when an N aims to reopen a case or to respond to a survey question.

{
"message_id": "74830",
"parent_message_id": "0",
"smpp_message_id": "8.1634957202535E+18",
"to": "11111",
"from": "MOPA",
"contents": "Caro municipe, nao conseguimos encontrar o seu caso para reabrir",
"created_at": "2017-05-24 14:27:40",
"updated_at": "2017-05-24 14:27:40",
"message_status": "accepted"
},
{
"message_id": "74829",
"parent_message_id": null,
"smpp_message_id": null,
"to": "MOPA",
"from": "111111",
"contents": "N",
"created_at": "2017-05-24 14:27:39",
"updated_at": "2017-05-24 14:27:39",
"message_status": "delivered"
},
{
"message_id": "66727",
"parent_message_id": "0",
"smpp_message_id": "8.1648956435154E+18",
"to": "111111",
"from": "MOPA",
"contents": "MOPA - O seu ponto critico tem algum problema neste momento? Responda S para sim e N para Nao.",
"created_at": "2017-05-10 17:23:43",
"updated_at": "2017-05-10 17:23:44",
"message_status": "accepted" },

Error in SMS message notifications when status = 'Reaberto'

If status = 'Reaberto', it should not trigger the status change message that applies to 'Resolvido', 'Invalido' and 'Arquivado'.

Currently, the user gets two SMS:

message_id 74990 confirms the status change,
message_id 74991 sends a status update with the reopen text, which does not apply.

{
"message_id": "74991",
"parent_message_id": "0",
"smpp_message_id": "368f84a",
"to": "111111111",
"from": "MOPA",
"contents": "Caro municipe, o problema 17218418 tem agora o estado Reaberto. O tchova passou por ai na segunda e passara por ai hoje.. Caso discorde responda N a esta SMS",
"created_at": "2017-05-25 10:01:07",
"updated_at": "2017-05-25 10:01:14",
"message_status": "delivered"
},
{
"message_id": "74990",
"parent_message_id": "0",
"smpp_message_id": "368f72b",
"to": "111111111",
"from": "MOPA",
"contents": "Caro municipe, o seu caso 17218418 foi reaberto",
"created_at": "2017-05-25 10:00:31",
"updated_at": "2017-05-25 10:00:36",
"message_status": "delivered"
},

Error running job: Send daily survey.
Error message:(_mysql_exceptions.OperationalError) (2006, 'MySQL server has gone away') [SQL: u'SELECT mopa_survey.id AS mopa_survey_id, mopa_survey.created_at AS mopa_survey_created_at, mopa_survey.updated_at AS mopa_survey_updated_at, mopa_survey.survey_id AS mopa_survey_survey_id, mopa_survey.survey_type AS mopa_survey_survey_type, mopa_survey.district AS mopa_survey_district, mopa_survey.neighbourhood AS mopa_survey_neighbourhood, mopa_survey.point AS mopa_survey_point, mopa_survey.question AS mopa_survey_question \nFROM mopa_survey ORDER BY mopa_survey.id DESC \n LIMIT %s'] [parameters: (1,)].
Exception Type: <class 'sqlalchemy.exc.OperationalError'>.
File name: tasks.py.
Line No: 33.
Traceback: Traceback (most recent call last):
  File "/home/psms1/sms-app/mopa/tasks.py", line 33, in wrapper
    return func(*args, **kwargs)
  File "/home/psms1/sms-app/mopa/tasks.py", line 452, in send_daily_survey
    survey = Survey(survey_type="G", question=constants.SMS_INTRO)
  File "<string>", line 4, in __init__
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/orm/state.py", line 306, in _initialize_instance
    manager.dispatch.init_failure(self, args, kwargs)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/orm/state.py", line 303, in _initialize_instance
    return manager.original_init(*mixed[1:], **kwargs)
  File "/home/psms1/sms-app/mopa/models.py", line 148, in __init__
    self.survey_id = self.get_next_survey_id()
  File "/home/psms1/sms-app/mopa/models.py", line 164, in get_next_survey_id
    last_survey = self.get_last_survey()
  File "/home/psms1/sms-app/mopa/models.py", line 172, in get_last_survey
    return self.query.order_by(desc(Survey.id)).first()
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2445, in first
    ret = list(self[0:1])
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2281, in __getitem__
    return list(res)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2516, in __iter__
    return self._execute_and_instances(context)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2531, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/home/psms1/.local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
  File "/home/psms1/.local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/home/psms1/.local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (_mysql_exceptions.OperationalError) (2006, 'MySQL server has gone away') [SQL: u'SELECT mopa_survey.id AS mopa_survey_id, mopa_survey.created_at AS mopa_survey_created_at, mopa_survey.updated_at AS mopa_survey_updated_at, mopa_survey.survey_id AS mopa_survey_survey_id, mopa_survey.survey_type AS mopa_survey_survey_type, mopa_survey.district AS mopa_survey_district, mopa_survey.neighbourhood AS mopa_survey_neighbourhood, mopa_survey.point AS mopa_survey_point, mopa_survey.question AS mopa_survey_question \nFROM mopa_survey ORDER BY mopa_survey.id DESC \n LIMIT %s'] [parameters: (1,)]

Use Three

Update code and use the three Python wrapper for the Open311 API

Notify of updates on requests job fails due to json.loads not being to decoding empty list

Error running job: Notify of updates on requests.
Error message:No JSON object could be decoded.
Exception Type: <type 'exceptions.ValueError'>.\File name: tasks.py.
Line No: 33.
Traceback: Traceback (most recent call last):
  File "/home/psms1/sms-app/mopa/tasks.py", line 33, in wrapper
    return func(*args, **kwargs)
  File "/home/psms1/sms-app/mopa/tasks.py", line 492, in notify_updates_on_requests
    requests = common.get_requests(start_date, end_date, True)
  File "/home/psms1/sms-app/mopa/common.py", line 80, in get_requests
    json_requests = json.loads(z_json)
  File "/usr/lib/python2.7/json/__init__.py", line 326, in loads
    return _default_decoder.decode(s)
  File "/usr/lib/python2.7/json/decoder.py", line 365, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/usr/lib/python2.7/json/decoder.py", line 383, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

TypeError: not all arguments converted during string formatting

        Message type:       ERROR
        Location:           /srv/www/mopa-utils/vendor/flask/app.py:1587
        Module:             app
        Function:           log_exception
        Time:               2016-11-27 17:00:04

        Message:

        Exception on /tasks/send-daily-report [GET]

Traceback (most recent call last):
  File "/srv/www/mopa-utils/vendor/flask/app.py", line 1988, in wsgi_app
    response = self.full_dispatch_request()
  File "/srv/www/mopa-utils/vendor/flask/app.py", line 1641, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/srv/www/mopa-utils/vendor/flask/app.py", line 1544, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/srv/www/mopa-utils/vendor/flask/app.py", line 1639, in full_dispatch_request
    rv = self.dispatch_request()
  File "/srv/www/mopa-utils/vendor/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/srv/www/mopa-utils/mopa/tasks.py", line 345, in send_daily_report
    attachments=[config.REPORTS_DIR + '/' + f_name]
  File "/srv/www/mopa-utils/mopa/infrastructure.py", line 274, in send_mail
    raise Exception('Invalid Address "%s" in To' % mail_address)
TypeError: not all arguments converted during string formatting

Send daily survey replies job fails when there's no answers due to unsafe deletion of first answer (column labels)

Error running job: Send daily survey replies.
Error message:list assignment index out of range.
Exception Type: <type 'exceptions.IndexError'>.\File name: tasks.py.
Line No: 33.
Traceback: Traceback (most recent call last):
  File "/home/psms1/sms-app/mopa/tasks.py", line 33, in wrapper
    return func(*args, **kwargs)
  File "/home/psms1/sms-app/mopa/tasks.py", line 413, in send_daily_survey_replies
    del answers[0]
IndexError: list assignment index out of range